ТОП-5 функций Excel и Google Таблиц

Большинство людей пользуются довольно небольшим набором функций Excel и Google Таблиц. Они могут что-то отфильтровать, что-то сложить, посчитать — в общем, совершить некие базовые действия.

Ни для кого не секрет, что Excel и Google Таблицы умеют делать намного больше. Разберем несколько функций, которые пригодятся тем, кому часто приходится сводить вместе данные из разрозненных таблиц или исхитряться с фильтрами, чтобы вытащить из большой таблицы нужный кусочек информации. У Excel и Google Таблиц есть возможность сильно упростить эти задачи.

VLOOKUP или ВПР (вертикальный просмотр)

С помощью этой функции можно быстро и с минимумом усилий объединить данные из нескольких источников по заданному условию. Как это работает, проще всего объяснить на примерах.

Есть две таблицы — в одной список клиентов с контактными данными, в другой — список этих же клиентов с номерами заключенных с ними договоров. Нужно перенести номера договоров из второй таблицы в первую.

Вместо людей и телефонов могут быть каталог товаров с ценами и данные о поставщиках, у которых вы их покупаете. Либо перечень сотрудников с какой-то информацией о них: номерами телефонов и отделами, где они работают.

В общем, две таблицы, которые слишком сложно и долго объединять вручную. Намного быстрее сделать это через ВПР: выбрать какое-то значение, общее для обеих, и указать, какие данные, относящиеся к нему, надо найти и скопировать.

Как пользоваться?

ВПР работает с таблицами на разных листах в одном файле в Excel и Google Таблицах. Но есть нюанс: в Excel можно использовать конструктор формул, а в Google такого нет, и всю формулу надо будет писать самому ручками. Но это не так уж и сложно.

В таблице, куда вы переносите данные, надо выбрать столбец для них и в верхней ячейке начать вводить формулу любым удобным способом.

Вам надо заполнить 4 значения.

1. «Искомое значение» — это ячейка или столбец в первой таблице (куда переносим) со значениями, общими для обоих документов. По ним функция будет искать данные.
2. «Таблица» — это диапазон во второй таблице, где хранятся искомые значения и данные для переноса. Диапазон надо закрепить (выделить и нажать на F4), чтобы функция работала корректно для всего столбца. Если этого не сделать, то диапазон съедет и вся формула поломается.
3. «Номер столбца» — порядковый номер столбец внутри «Таблицы» (то есть выделенного диапазона!), в котором хранятся данные для переноса. Считается слева направо.
4. «Интервальный просмотр» — определяет, нужно ли точное совпадение с искомым значением. В разных версиях Excel может задаваться по-разному: ЛОЖЬ/ИСТИНА, 0/1 и так далее. В любом случае выскочит подсказка программы, ориентируйтесь на нее.

Когда формула заполнена, протяните ее вниз до конца столбца. Вот и все.

INDEX + MATCH или ИНДЕКС + ПОИСКПОЗ

ВПР позволяет переносить данные только из столбца правее столбца с искомым значением. Если у вас искомое значение — ФИО клиентов, а переносимые данные — номера договоров с ними, то номера должны идти после ФИО. В противном случае надо либо менять столбцы местами, либо использовать сочетание функций ИНДЕКС и ПОИСКПОЗ.

Оно применяется ровно для тех же целей, что и ВПР, и тоже поддерживается и в Excel, и Google Таблицах.

Как пользоваться?

ПОИСКПОЗ возвращает номер ячейки, соответствующей заданным параметрам, и выступает как «Искомое значение» в ВПР. То есть она задает, по какому условию мы сравниваем две таблицы.

ИНДЕКС — возвращает значение ячейки, то есть то, что в ней написано. Она находит нужные нам данные в одной таблице и записывает их в другую.

Это может пока звучать не очень понятно, но, когда в первый раз самостоятельно напишете формулу, поймете, как это все работает.

1. Начинайте с функции ИНДЕКС и задайте «Массив» — столбец, в котором надо искать данные для переноса. Его надо закрепить.
2. Вместо «Номера строки» вызовите функцию ПОИСКПОЗ и выберите ячейку с искомым значением из одной таблицы и «Просматриваемый массив», в котором надо ее искать, в другой. На этом шаге мы говорим функции: «Найди значение, которое есть в таблице 1, в столбце таблицы 2». Просматриваемый массив тоже надо закрепить.
3. «Интервальный просмотр» работает так же, как в ВПР.

Когда формула написана, так же, как в прошлый раз, протяните ее вниз до конца столбца.

SUMIF и SUMIF или СУММЕСЛИ и СУММЕСЛИМН

Эти функции возвращают сумму значений, которые соответствуют заданному критерию. Критерий может быть больше, меньше или равен определенному значению. СУММЕСЛИМН используется, когда критериев несколько.

Например, у вас есть таблица с данными о продажах, разбитых по категориям товаров. Вы хотите узнать, на какую сумму напродавали в категории «Фрукты». С помощью СУММЕСЛИ вы можете отобрать нужные данные и сложить.

Таким же образом можно сложить сумму заказов определенного клиента, посчитать продажи определенного продажника.

Как пользоваться?

Надо последовательно заполнить несколько значений. Они все опять же должны храниться в одном файле.

1. «Диапазон» — столбец, в котором вы ищете нужные значения.
2. «Критерий» — условие поиска, которое всегда пишется в скобках. Может быть числом или текстом. Даже если вам надо сложить все числа, к примеру, больше пяти, вы пишете «>5». Без скобок формула не сработает.  
3. «Диапазон суммирования» — задается не всегда. Иногда может быть надо найти значения в одном столбце, а посчитать значения тех же строк в другом столбце. В нашей таблице пример: чтобы посчитать, сколько денег потратил Петя, «Диапазон» у нас в столбце с именами, а «Диапазон суммирования» — с суммами. В качестве критерия выступает имя «Петя».
4. СУММЕСЛИМН работает так же, но можно перечислить несколько критериев через «;»

Как видите, главное — уловить разницу между «Диапазоном» и «Диапазоном суммирования». В первом функция просто ищет нужные значения — то есть фильтрует по нему, а во втором уже складывает. Иногда достаточно только «Диапазона», если вы и ищете, и складываете значения в одном столбце. Но чаще нужно воспользоваться обоими. 

GOOGLEFINANCE

А вот эксклюзив Google Таблиц — функция, которая подтягивает данные о ценных бумагах за заданный период.

Можно подтянуть архивные данные за прошлые месяцы или годы, а можно актуальные, которые обновляются довольно оперативно. Скорее всего, большинству пригодится именно второй вариант. С его помощью вы можете вывести текущие значения в какую-нибудь рабочую таблицу и мониторить, как они меняются.

Либо можно обратиться к данным прошлых периодов и посмотреть, как они менялись в динамике

Как пользоваться?

Написать в ячейке функцию по шаблону ниже:

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

• ticker — код с названием биржи,
• attribute — нужное вам значение (цена, объем торгов, дата последней сделки, капитализация и так далее),
• start_date, end_date или num_days, interval — это даты, за которые вам нужны данные.

Возможных атрибутов слишком много, чтобы описывать каждый. К счастью, если решите использовать эту функцию, Google Таблицы подскажут, как правильно ее написать. У них есть объемная справка с пояснениями про каждый элемент формулы.  

QUERY

Еще один эксклюзив Google — и какой!

Эта функция позволяет писать SQL-подобные запросы. 

С помощью QUERY можно сортировать и группировать данные, настроить, сколько строк или столбцов из исходной таблицы должны отображаться в результате запроса. Так можно «скрыть» лишнюю информацию или сделать таблицу менее громоздкой.

Функция выглядит очень просто:

QUERY(data, query, [headers])

• data — это ячейки,  столбцы и строки, откуда вы берете данные.
• query — собственно, запрос, что надо с ними сделать: выбрать, сгруппировать, как-то пометить и так далее. Вот справка, где описано, какие варианты есть.
• headers — это необязательный атрибут, в котором можно задать заголовки столбуов таблицы, которую создаст функция. 

Как вы понимаете, на практике надо будет хорошо вникнуть, как этим пользоваться и хотя бы базово познакомиться с SQL. Небольшая рекламная пауза: изучить основы SQL поможет бесплатный курс команды LEFT JOIN «Марафон данных».

Когда пора осваивать QUERY?

QUERY — это уже продвинутый уровень для тех, кому обычных возможностей табличных редакторов не хватает. Например, если вы работаете с очень большими таблицами и очень конкретными запросами. Что-то в духе:  «Среднее значение по параметру деньги для мальчиков старше 24 лет».

Это можно и обычными функциями вытащить, но намного проще будет написать вот такой запрос: «SELECT AVG(column_name) WHERE sex=’M’ and age>24».

Если вам часто приходится решать задачи такого рода, то чтобы не мучиться с формулами, разумно будет научиться основам SQL.

Вот такой топ-5 функций, которые могут облегчить жизнь продажникам, маркетологам, эйчарам бухгалтерам и вообще всем, кто работает в Excel и Google Таблицах. Если у вас остались вопросы, пишите в комментариях на моем канале. Буду рад помочь!