Category: Excel

Excel: как заменить множество ячеек сохранив значения старых

Задача по подобной замене возникает довольно часто: получили отчет с результатами, но нужно изменить/добавить данные в большом количестве ячеек, оставив все в одном столбце и не трогая старые данные.

Просто выставить фильтр и вставить новые данные не получится т.к. вставка в Excel происходит построчно во все ячейки, а не только в зафильтрованные.

Читать дальше

Excel: разные хинты

  • Про ВПР и замены множества ячеек при сохранении старых см. отдельные статьи
  • Очень полезная функция СЧЕТЕСЛИ, пример считаем все строки, в которых  в начале содержится да (только в начале т.к. * в конце):
    • =СЧЁТЕСЛИ(D2:D167;”да*”)
  • Поиск по неплоской таблице ячеек когда слева и справа идентификаторы

Отдельные три таблицы
=ИНДЕКС($M$10:$P$13;ПОИСКПОЗ(B13;$L$10:$L$13;0);ПОИСКПОЗ(C13;$M$9:$P$9;0))
=ИНДЕКС($W$7:$AM$23;ПОИСКПОЗ(A13;$V$7:$V$23;0);ПОИСКПОЗ(B13;$W$6:$AM$6;0))
ИНДЕКС($AQ$7:$BO$31;ПОИСКПОЗ(A29;$AP$7:$AP$31;0);ПОИСКПОЗ(B29;$AQ$6:$BO$6;0))

Объединение всех трех через 2 ЕСЛИОШИБКА
=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС($K$7:$S$15;ПОИСКПОЗ(A5;$J$7:$J$15;0);ПОИСКПОЗ(B5;$K$6:$S$6;0));ИНДЕКС($W$7:$AM$23;ПОИСКПОЗ(A5;$V$7:$V$23;0);ПОИСКПОЗ(B5;$W$6:$AM$6;0)));ИНДЕКС($AQ$7:$BO$31;ПОИСКПОЗ(A5;$AP$7:$AP$31;0);ПОИСКПОЗ(B5;$AQ$6:$BO$6;0)))
  • Excel полезные украшательства:
    • Форматировать как таблицу
    • Формат – ширина столбца – 10 и не нужно потенциально выравнивать каждый столбец
  • Заменяем запятые на точки в числах
Часто при вводе технических данных, например ip-адресов, удобно пользоваться цифровым блоком на клавиатуре справа.


Читать дальше

Excel: использование функции ВПР

Общее

В Excel есть полезная функция – ВПР. Позволяет на основе списка неких идентификаторов извлечь из таблицы значения атрибутов этих идентификаторов.

Сравнение ВПР с другими вариантами решения задачи:

  • ВПР – подходит для небольших объемов данных. По опыту попытка получения данных по 100к значениям в таблице из 300к строк требовала неимоверное время для расчета и в результате пришлось решать задачу по-другому. 


Читать дальше

Полезные Hotkey Shortcuts (бинды)

Windows (Word/Excel/etc)
  • win + Ж # эмодзи 💙
  • win + arrow – позволяет удобно размещать окно приложения на экране, не занимаясь раздвиганиями границ окна
    • left – to the left
    • right – to the right
    • up – full screen
    • down – small screen
  • HOME – переход к началу строки (hotkey для Linux ниже)
  • END – переход к концу строки (hotkey для Linux ниже)
  • ctrl+shift+esc – открыть диспетчер задач
  • ctrl+shift+home (в notepad точно работает)- выделить всё от текущего места до начала файла.


Читать дальше