Excel и Google Sheets глазами аналитика

Бран · продюсер «Хроник Пограничья»

Программисты выгрузили мне покупки за пятницу, 27 марта, в Excel. Я сложил столбец с суммами, вышло 8759 рублей. Это и есть выручка? И ещё: хотел подтянуть названия офферов через ВПР, а у меня половина ячеек #Н/Д. Почини, пожалуйста.

Доступа к базе у Брана нет, зато есть файл. Так начинается половина задач в первый рабочий день: заказчик уже что-то выгрузил в таблицу и уже что-то в ней посчитал. В прошлом уроке таблицы промелькнули в обзоре инструментов. Сегодня откроем их как следует: формулы, ссылки, ВПР, сводные и ловушки, на которых спотыкается каждый. В конце ответим Брану, сколько студия заработала в пятницу.

Выгрузка Брана

Вот лист «Покупки» целиком. Первый столбец это номер строки в Excel, он понадобится для формул. Время в UTC, как и в базе игры.

Строка	A Покупка	B Время	C Почта	D Оффер	E Платформа	F Сумма	G Статус	H Чек
2	525	09:23	frostfire4752@gmail.com	9	android	599	paid	and-3b5293e98930
3	526	09:29	nightstorm@bk.ru	1	android	99	paid	and-6ac133780453
4	527	11:05	fireraven@mail.ru	6	android	149	paid	and-99da6ce635ca
5	528	14:46	thul14@mail.ru	4	android	1990	paid	and-95a618f7724b
6	529	17:06	nightstorm@bk.ru	4	android	1990	paid	and-4c745f186051
7	530	17:07	bearstone283@mail.ru	7	ios	299	paid	ios-4852d84cc734
8	531	17:16	qa.nikita@studio.dev	1	android	99	paid	and-bfe8f3236dc4
9	532	17:22	qa.gleb@studio.dev	2	android	499	paid	and-0c13f52a6e63
10	533	17:22	qa.gleb@studio.dev	2	android	499	paid	and-0c13f52a6e63
11	534	18:16	nightstorm@bk.ru	11	android	349	paid	and-1624bf3a853b
12	535	18:48	blade_torul@gmail.com	7	android	299	paid	and-ec59afaa6782
13	536	19:04	bearstone283@mail.ru	1	ios	99	paid	ios-a70e1a1e595e
14	537	19:09	ghoststorm3698@mail.ru	2	ios	499	paid	ios-e83b31466a0b
15	538	20:33	stormwolf@gmail.com	15	android	1290	refunded	and-a14c542bc2e7

Присмотрись, прежде чем читать дальше. Тут спрятаны три вещи, из-за которых 8759 не равно выручке. Одну мы уже обсуждали в уроке про данные.

Ячейка, формула, ссылка

Таблица похожа на шахматную доску: столбцы названы буквами, строки цифрами, и у каждой клетки есть адрес. F5 это сумма покупки 528, 1990 рублей.

В клетку можно положить значение, а можно формулу. Бран сделал так:

=СУММ(F2:F15)        английский Excel: =SUM(F2:F15)

F2:F15 это диапазон: все клетки от F2 до F15. Формула ответила 8759, и арифметически она права. Неправ вопрос, который она решает, но об этом чуть позже.

Названия функций зависят от языка программы. В русском Excel пишут СУММ, в английском SUM, а аргументы в русской настройке разделяют точкой с запятой, в английской запятой. Дальше в уроке русское имя идёт первым, английское рядом.

Доллар в адресе

Теперь ВПР Брана. Он хотел видеть рядом с номером оффера его название. Названия лежат на втором листе, «Офферы». Вот его кусок с теми офферами, что встретились в пятницу:

Строка	A id	B Название	C Тип	D Цена
2	1	Горсть гемов	gems	99
3	2	Мешок гемов	gems	499
5	4	Гора гемов	gems	1990
7	6	Набор новичка	bundle	149
8	7	Сундук героя	bundle	449
10	9	Боевой пропуск: Весна	pass	599
12	11	Премиум на месяц	subscription	349
16	15	Ездовой волк	cosmetic	1290

Всего на листе 18 офферов, в строках со 2-й по 19-ю. Бран написал в ячейку I2 формулу и протянул её мышкой вниз до I15:

=ВПР(D2;Офферы!A2:B19;2;ЛОЖЬ)       =VLOOKUP(D2,Офферы!A2:B19,2,FALSE)

ВПР читается так: найди значение из D2 в первом столбце диапазона A2:B19 на листе «Офферы» и верни второй столбец той же строки. ЛОЖЬ значит «только точное совпадение».

В I2 всё работает: оффер 9 нашёлся, «Боевой пропуск: Весна». А вот что стало, когда формулу протянули вниз. Таблица считает адреса относительными: «через одну строку вниз от меня». Скопировал формулу на строку ниже, и все адреса сдвинулись на строку ниже:

I2:  =ВПР(D2;Офферы!A2:B19;2;ЛОЖЬ)
I3:  =ВПР(D3;Офферы!A3:B20;2;ЛОЖЬ)    оффер 1 живёт в строке 2, а поиск начался с 3-й: #Н/Д
I6:  =ВПР(D6;Офферы!A6:B23;2;ЛОЖЬ)    оффер 4 в строке 5, снова мимо

Номер оффера D3 сдвинулся правильно, а справочник уехал вместе с ним. В итоге #Н/Д вышел в восьми строках из четырнадцати, причём вперемешку: где оффер лежит ниже начала сдвинутого диапазона, там повезло.

Лечится долларом. Знак $ перед буквой или цифрой запрещает ей сдвигаться при копировании:

• $A$2 не двигается никуда, это абсолютная ссылка;
• $A2 держит столбец, а строку двигает;
• A$2 держит строку, а столбец двигает.

В Excel на Windows и в Google Sheets доллары расставляет клавиша F4: нажимай её, стоя курсором на адресе, и она перебирает все четыре варианта. Правильная формула Брана:

=ВПР(D2;Офферы!$A$2:$B$19;2;ЛОЖЬ)

Почему ВПР ломается и чем его заменить

У ВПР есть вторая слабость, хитрее первой. Номер столбца 2 это просто число. Допустим, кто-то вставил на лист «Офферы» новый столбец B «Гемы», между id и названием. Таблица честно растянет диапазон до $A$2:$C$19, но двойку не тронет. Теперь второй столбец диапазона это гемы, и в колонке «Название» у Брана молча появятся числа. Ошибки нет, есть неправильный ответ.

В Excel 2021 и Microsoft 365, а ещё в Google Sheets есть функция ПРОСМОТРX, по-английски XLOOKUP. Она берёт два столбца отдельно: где искать и что вернуть.

=ПРОСМОТРX(D2;Офферы!$A$2:$A$19;Офферы!$B$2:$B$19;"нет в справочнике")
=XLOOKUP(D2,Офферы!$A$2:$A$19,Офферы!$B$2:$B$19,"нет в справочнике")

Вставят новый столбец, и ссылка $B$2:$B$19 уедет вместе с названиями. Нет оффера в справочнике, и вместо #Н/Д будет понятная фраза. Если у коллеги старый Excel без ПРОСМОТРX, связка ИНДЕКС и ПОИСКПОЗ (INDEX и MATCH) делает то же самое.

Последняя тонкость про справочник. У «Сундука героя» в листе «Офферы» цена 449, а в покупке 530 он стоит 299. Справочник хранит текущую цену: сундук подорожал 14 мая. Подтягивать из справочника то, что меняется со временем, опасно, и это та же история, что с продовой базой из урока про данные.