За хорошего эксельщика двух питонистов дают
Продолжаем рассказывать про неочевидные штуки в Экселе, которые могут пригодиться для работы с данными. Сегодня будет про аналитику — как собрать, подготовить или получить дополнительные данные, чтобы анализ получился более полный.
Если интересно, что мы уже делали в Экселе, то вот:
МИНЕСЛИ и МАКСЕСЛИ
В английской версии: MINIF, MAXIF.
Что делают: находят минимальное и максимальное значение по какому-то условию в заданных диапазонах.
Допустим, у нас есть таблица доходов и расходов, причём доходы получены из разных источников:
С помощью функций МИНЕСЛИ и МАКСЕСЛИ мы можем найти минимальные и максимальные значения по заданному параметру. Например, найдём минимальный доход, который мы получили с внешних заказов. Для этого напишем формулу:
=MINIFS (B2:B13; C2:C13;"заказ»)
Первый параметр — это диапазон, где ищем минимальное значение, второй — диапазон, по которому мы будем проверять наше условие, и третий — само условие. Получается, что формула возьмёт слово «заказ», найдёт в столбце C все совпадения с ним, а потом найдёт в столбце B минимальное значение:
Точно так же можно найти максимальный доход на работе — вдруг работать только с заказами выгоднее:
ВПР
В английской версии: VLOOKUP
Что делает: сопоставляет данные из одного столбца с другим.
Это одна из самых популярных функций при анализе данных в Экселе — с ней можно быстро находить данные в одном столбце и смотреть, чему они соответствуют в другом. Например, если мы хотим посмотреть, сколько потратили в марте, то используем такую формулу:
=VLOOKUP («Март»; A2:D13; 4; FALSE)
Функция возьмёт наш «Март», проверит весь диапазон, который мы указали, найдёт в нём наше слово и выведет результат из четвёртого столбца, который соответствует марту. Параметр FALSE означает, что нам нужно точное совпадение, — если хватит примерного, поставьте TRUE.
Это выглядит просто на нашей таблице — тут значения можно найти и без функций. Но когда у тебя не 12 строк, а 12 тысяч, то с формулами получается проще.
СУММЕСЛИМН
В английской версии: SUMIFS
Что делает: складывает значения, которые подходят сразу к нескольким параметрам.
Бывает так, что нам нужно найти сумму значений сразу по нескольким параметрам — когда они все выполняются, то мы складываем между собой те ячейки, где есть такое полное совпадение. Например, найдём, сколько мы заработали на удалёнке на основной работе — используем для этого формулу:
=SUMIFS (B2:B13; C2:C13;"работа»; E2:E13;"удалёнка»)
Здесь мы первым параметром задаём, из какого столбца будем брать числа для суммы, потом два параметра — фильтр по источнику, и последние два — выбираем только те, где вид стоит «удалёнка»:
СЧЁТЕСЛИМН
В английской версии: COUNTIFS
Что делает: то же самое, что и СУММЕСЛИМН, только не складывает значения, а считает совпадения.
Если нам нужно выяснить, сколько раз мы брались за внешние заказы и получали за это меньше ста тысяч рублей, то можем использовать такую формулу:
=COUNTIFS (C2:C13;"заказ»; B2:B13;»<100000»)
Здесь всё то же самое, что и в предыдущей формуле, только без первого столбца для суммы. Обратите внимание, что знак сравнения входит в кавычки.
СЦЕПИТЬ
В английской версии: CONCATENATE
Что делает: склеивает несколько ячеек в одну.
Если нам нужно подготовить данные для выгрузки или привести их в более понятный вид, иногда используют склейку ячеек. Смысл в том, что можно задать правила объединения на примере одной ячейки, потом протянуть её вниз, сколько нужно, а Эксель сам заполнит их новыми склеенными данными.
Допустим, нам нужно вывести для отчёта такую фразу для каждого месяца: «Январь: заработано столько-то рублей, потрачено столько-то, остаток вот такой». Чтобы не собирать это всё вручную, пишем формулу:
=CONCATENATE (A2;»: заработано «; B2;», потрачено «; D2;», остаток: «; E2)
Здесь мы просто через точку с запятой указываем как ячейки, так и текстовые значения, которые хотим добавить в итоговую строчку. Кроме текста, туда можно добавлять что угодно — например результаты вычислений или текст из других ячеек.
СЖПРОБЕЛЫ
В английской версии: TRIM
Что делает: убирает лишние пробелы между словами, оставляя по одному пробелу.
Иногда данные для анализа попадают в таблицу в непотребном виде — например, с кучей пробелов между словами. Если это наш случай, используем функцию СЖПРОБЕЛЫ — она удалит лишнее и сделает красивый текст:
=TRIM (A1:A4)
В функции можно указать сразу весь диапазон для обработки (и тогда она сама добавит нужные ячейки ниже) или указать только одну ячейку.