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

  • Про ВПР и замены множества ячеек при сохранении старых см. отдельные статьи
  • 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” и сделать тип ячеек “Процентный”.

  • Промежуточные итоги: Если хотим обновить сводку в соответствии с таблицей, не удаляем его, а просто нажимаем по любому значению в таблице и выбираем “Обновить”
  • Промежуточные итоги: Если нам нужно отсортировать по столбцу- нажимаем по любому значению в столбце и выбираем сортировку. Не обязательно для сортировки копировать данные в отдельную таблицу.
  • Промежуточные итоги: Очень полезные штуки при необходимости полноценной таблицы в результате сводного отчета, а не в виде иерархии.
    • В параметрах сводной (Анализ -> Сводная таблица -> Свойства) во вкладке “вывод” поставьте галку на против “Классический макет сводной таблицы”.
    • Далее если нужно в “конструкторе” избавьтесь от промежуточных итогов: промежуточные итоги – не показывать промежуточные суммы.
    • Там же. Макет отчета – повторять записи элементов.
  • Отстой полагаться на номер недели:
1) русский Excel считает что новая неделя начинается с вскр
2) Годы как не странно не заканчиваются всегда на последнем дне недели (вскр у нас и в сб на западе), поэтому для статистики последняя неделя всегда будет “хуже” по показателям в сравнении с другими неделями года
  • Сортировка по двум полям может помочь когда есть проблема с сортировкой текста и чисел в одной ячейке одновременно (когда 2016_2 больше чем 2016_11 потому что идет сортировка по тексту). Если у нас текст и число разграничено каким то делиметром – то просто копируем наш столбец в новый, разбиваем новый столбец по столбцам на основе делиметра, сортируем как нам хочется.
  • Делать условия просто – для этого используем функцию ЕСЛИ. К примеру смотрим, что в именнованой таблице col3 >= col1 <=col2 т.е. значение col1 в диапазоне значений между col2 и col3.
=ЕСЛИ(И([@[col1]]<=[@[col2]];[@[col1]]>=[@[col3]]);"yes";"no")
  • Крутая функция вместо копипаста в блокнот, подмены и прочего. Год_Неделя;Год_месяц;Позиция
=СЦЕПИТЬ(C2;"_";B2)

Leave a Reply