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

• Про ВПР и замены множества ячеек при сохранении старых см. отдельные статьи
• Поиск по неплоской таблице ячеек когда слева и справа идентификаторы

Отдельные три таблицы
=ИНДЕКС($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-адресов, удобно пользоваться цифровым блоком на клавиатуре справа. Но вместо точек в экселе в России запятые. Исправить это можно здесь: 
1) Войти в меню файл и нажать на пункт «Параметры» -> Дополнительно
2) Снять галочку напротив « Использовать системные разделители», и поставить точку вместо запятой напротив поля «Разделитель целой и дробной части»

• CSV по сути стандарт. В нем легко сливать данные и читать/анализировать их. Например, его используют в продукте Cisco Umbrella (cloud DNS).

  Umbrella logs are CSV formatted, compressed (gzip), and saved every ten minutes.

• Excel монстр в плане аналитики/расчетов – коэф. корреляции, линейная регрессия, Фурье, тенденция (прогнозирование) и проч проч
• Hotkey (смотрим тут)
  • используя ctrl+shift+стрелку можно быстро выделить область, очень полезно, когда в одной большой таблице нам нужно сделать несколько по именам (одна таблица включает все столбцы, а другая/другие – лишь часть)
• При генерации Excel файла нежелательно использовать запятую как разделитель между двумя цифрами т.к. Excel будет это рассматривать как одну цифру и убирать “лишние” нули. Лучше использовать разделителем что-то другое – пробел/точка с запятой/etc.
• Как переключить вид нумерации (узнать номер столбца) – в Excel можно переключить вид отображения адреса ячейки. Сделать это можно, открыв Файл-Параметры, вкладка «Формулы»
• Преобразование unix timestamp в date excel (Convert Timestamp To Date).

  =(((A2/60)/60)/24)+ДАТА(1970;1;1)

• Если форматирование активируется только после щелка по ячейке и/или возле ячейки зеленый аларм – значит в ней лежит какая-то невидимая дрянь. Например, число взято в “”. Значит, при формировании данных (лучше) нужно убрать обрамляющие скобки “” или убрать их при генерации excel (хуже). Делаем с помощью ruby (в первом элементе массива, который будем класть в excel, меняем “123123” на 123123):

  row[0] = row[0].to_i

• Лучше генерировать XLSX вместо CSV. К примеру, если отправить csv по почте с русскими символами, компы с Windows покажут символы в cp1251, а Outlook в IOS в cp1252. Excel же файлы могут хранить кодировку, в которой будет отображены символы при открытии файла. Это позволяет избежать проблем, когда кодировка системы, в которой читается файл, отличается от кодировки системы, в которой этот файл создан.
• Лучше генерировать XLSX, а не XLS.  В xls ограничение в 65к строк, а в xlsx более 1кк.
• Если нужно данные из одной книги скопировать без формул в другую – можно использовать сохранение в CSV или промежуточное копирование в блокнот.
• Закрепление областей. Чтобы закрепить первые две строки нужно выделить три, далее выбрать Вид -> Закрепить области -> Закрепить области.
• Для подмены одного произвольного символа нужно использовать знак “?”, а не “.”, как в Linux
• Excel может выгружать таблицу из интернета/БД/текста: Данные -> Из интернета/текста/других источников. Штука прикольная, но не везде работает, особенно если как то замешан js (авторизация, формирование таблицы).
• Разница в процентах между двумя числами. Ввести формулу “=(C2-B2)/B2” и сделать тип ячеек “Процентный”.

• Промежуточные итоги: Если хотим обновить сводку в соответствии с таблицей, не удаляем его, а просто нажимаем по любому значению в таблице и выбираем “Обновить”
• Промежуточные итоги: Если нам нужно отсортировать по столбцу- нажимаем по любому значению в столбце и выбираем сортировку. Не обязательно для сортировки копировать данные в отдельную таблицу.
• Промежуточные итоги: Очень полезные штуки при необходимости полноценной таблицы в результате сводного отчета, а не в виде иерархии.
  • В параметрах сводной (Анализ -> Сводная таблица -> Свойства) во вкладке “вывод” поставьте галку на против “Классический макет сводной таблицы”.
  • Далее если нужно в “конструкторе” избавьтесь от промежуточных итогов: промежуточные итоги – не показывать промежуточные суммы.
  • Там же. Макет отчета – повторять записи элементов.
• Отстой полагаться на номер недели:

• Сортировка по двум полям может помочь когда есть проблема с сортировкой текста и чисел в одной ячейке одновременно (когда 2016_2 больше чем 2016_11 потому что идет сортировка по тексту). Если у нас текст и число разграничено каким то делиметром – то просто копируем наш столбец в новый, разбиваем новый столбец по столбцам на основе делиметра, сортируем как нам хочется.
• Делать условия просто – для этого используем функцию ЕСЛИ. К примеру
  • производим какую-то математическую операцию и строим отображаемую логику на основе результата операции

=ЕСЛИ(E18/E17>0;E18/E17>0;"МЕНЕЕ НУЛЯ")

• • смотрим, что в именнованой таблице col3 >= col1 <=col2 т.е. значение col1 в диапазоне значений между col2 и col3.

=ЕСЛИ(И([@[col1]]<=[@[col2]];[@[col1]]>=[@[col3]]);"yes";"no")

• Крутая функция вместо копипаста в блокнот, подмены и прочего. Год_Неделя;Год_месяц;Позиция

=СЦЕПИТЬ(C2;"_";B2)

• Стандартное отклонение. Ноль это идеально. Отличное от нуля – присутствует отклонение.

=СТАНДОТКЛОН.В(E3:E10)

• На графиках добавлять выноску данных – зачастую очень удобно и наглядно.
• Если выбрать правильный тип данных будет больше диаграмм предложено Excel как рекомендованных. Напр. если Excel знает, что в столбце с датами и временем находятся именно они, а в столбце с цифрами находятся цифры. Может помочь custom format, например 2023-05-29 16-09-34 -> ДД-ММ-ГГГГ ч-мм-cc