Excel на собеседовании аналитика: 13 задач с решениями

Коротко: что проверяют по Excel

Проверяют не скорость клавиш, а то, получится ли у вас правильное число и сможете ли вы объяснить, почему ему можно верить. Задачи ниже идут по уровням: на базовом — ссылки, доли и логика ЕСЛИ, на рабочем — поиск, условные суммы, даты, дубли и сводная, на продвинутом — воронка и когорта по различным пользователям.

• Закрепляйте знаменатель знаком $: =B2/B$4, иначе формула врёт со второй строки.
• Разница процентов и процентное изменение — разные числа: 4% → 5% это +1 п.п. и +25%.
• СУММЕСЛИМН с датами пишите полуинтервалом >= начало и < начало следующего месяца.
• СЧЁТЕСЛИМН считает строки, а не клиентов.
• «Удалить дубликаты» оставляет первое вхождение, и оно не обязательно правильное.
• Номер недели без года и без договорённости о начале недели ломает отчёт на стыке лет.
• Воронку и когорту считайте по различным пользователям, а когорту делите на её размер, а не на итог строки.

Три уровня: базовый, рабочий, продвинутый

Уровни — авторская карта, а не стандарт работодателей. Требования зависят от роли: стажёру хватит базового уровня и части рабочего, BI-аналитику, который живёт в отчётах, полезен весь рабочий и сводные с моделью данных. Встречаются три формата: файл с заданиями и таймером, задача при интервьюере с демонстрацией экрана и устные вопросы «как бы вы посчитали». Во всех трёх ценят одно: вы проговариваете, что проверили, прежде чем назвать число.

Отдельный вопрос — версия Excel. ПРОСМОТРX нет в Excel 2016 и 2019, УНИК и ФИЛЬТР появились в Excel 2021 и Microsoft 365. Если не знаете, в какой версии будет тест, держите в голове запасной путь: ИНДЕКС+ПОИСКПОЗ вместо ПРОСМОТРX, сводная или «Удалить дубликаты» вместо УНИК.

Учебные данные: лист «Заказы»

Почти все задачи идут на одном листе. Он короткий, чтобы каждую формулу можно было проверить глазами, и в нём специально оставлены две проблемы выгрузки: заказ 1005 пришёл двумя строками с разными статусами, а заказ 1009 — точной копией. У заказа 1006 в дате есть время 18:40. Таблица — учебная, числа заданы автором.

Второй лист «Клиенты» хранит город в столбце A, client_id в B и дату регистрации в C: c01 — Москва, 01.08.2026; c02 — Казань, 20.07.2026; c03 — Москва, 10.08.2026; c04 — Пермь, 15.08.2026; c05 — Казань, 28.08.2026; c06 — Москва, 14.09.2026.

Задача 1. Доля канала: относительная и абсолютная ссылка

Условие: оплаченная выручка без дублей — ads 5 750 ₽ в B2, organic 2 450 ₽ в B3, итог 8 200 ₽ в B4. Посчитайте долю каждого канала в C2:C3.

Кандидат пишет в C2 =B2/B4 и тянет вниз. В C3 Excel сдвигает обе ссылки и получает =B3/B5, а B5 пустая. Решение — закрепить строку итога: =B2/B$4. Полное $B$4 тоже сработает; смешанная ссылка B$4 удобнее, если формулу потом скопируют вправо на другой месяц. Хороший ответ на интервью — назвать, что именно должно двигаться при копировании, а что нет.

Задача 2. Проценты: п.п., рост и средняя из долей

Условие первое: конверсия была 4%, стала 5%. На сколько она выросла? Ответ «на 1%» неполный. Разница — 1 процентный пункт, относительный рост — =(5%-4%)/4% = 25%. Говорите оба числа и подписывайте единицы.

Условие второе: у ads 30 заказов на 600 визитов (5%), у organic 10 заказов на 1 000 визитов (1%). Какая общая конверсия? =СРЗНАЧ(5%;1%) даёт 3%, но это средняя из долей, где маленький канал весит столько же, сколько большой. Общая конверсия — сумма заказов на сумму визитов: =(30+10)/(600+1000) = 2,5%. Та же ловушка встречается и в SQL — разбор в статье о процентах и долях.

Задача 3. ЕСЛИ: порядок условий решает

Условие: разметьте заказы — от 2 000 ₽ «крупный», от 500 ₽ «средний», иначе «мелкий». Частый первый вариант: =ЕСЛИ(E2>=500;"средний";ЕСЛИ(E2>=2000;"крупный";"мелкий")). Заказ на 2 300 ₽ проходит первое условие и получает «средний»: до второй проверки Excel не доходит.

Вложенный ЕСЛИ проверяет условия сверху вниз и останавливается на первом истинном. Поэтому пороги идут от большего к меньшему: =ЕСЛИ(E5>=2000;"крупный";ЕСЛИ(E5>=500;"средний";"мелкий")). Проверка на интервью — подставить значение у каждой границы: 2 000, 1 999, 500, 499.

Задача 4. ЕСЛИОШИБКА прячет проблему, а не решает её

Условие: дневная конверсия за три дня. Визиты 400, 0, 380, заказы 16, 15, 19. Во второй день сломался счётчик визитов. Формула =C2/B2 во второй строке даёт #ДЕЛ/0!, и кандидат оборачивает её: =ЕСЛИОШИБКА(C2/B2;0). Средняя за три дня падает до 3,0%: день со сбоем стал днём с нулевой конверсией.

Вариант =ЕСЛИОШИБКА(C2/B2;"") лучше: СРЗНАЧ пропускает текст, включая пустую строку, а нули учитывает, — так написано в справке Microsoft. Средняя получится 4,5%. Ещё точнее посчитать конверсию по суммам дней без сбоя: 35 / 780 = 4,49%. И главное — назвать сбой в выводе, а не замазать его. Учтите, что ЕСЛИОШИБКА ловит любую ошибку, включая #ИМЯ? от опечатки в названии функции. Для поиска значений точнее ЕСНД, которая перехватывает только #Н/Д.

Задача 5. ВПР, ПРОСМОТРX и столбец левее ключа

Условие: подтяните к заказам город клиента с листа «Клиенты». Ключ client_id стоит в столбце B, город — в A, левее ключа. ВПР ищет только в первом столбце диапазона и возвращает значения правее него, поэтому город она не достанет. Дату регистрации из C — достанет: =ВПР(C2;Клиенты!$B$2:$C$7;2;ЛОЖЬ).

ПРОСМОТРX принимает столбец поиска и столбец результата отдельно и сам выводит текст, если ключ не нашёлся. В Excel 2016 и 2019 её нет — тогда =ИНДЕКС(Клиенты!$A$2:$A$7;ПОИСКПОЗ(C2;Клиенты!$B$2:$B$7;0)). Остальные подвохи ВПР — пропущенный ЛОЖЬ, число как текст, дубли ключа — подробно разобраны в статье про ВПР; на интервью их почти всегда спрашивают.

Задача 6. СУММЕСЛИМН за месяц: граница и кавычки

Условие: оплаченная выручка канала за август. В H1 — 01.08.2026, в H2 — 01.09.2026. Формула: =СУММЕСЛИМН($E$2:$E$12;$D$2:$D$12;"ads";$F$2:$F$12;"paid";$B$2:$B$12;">="&H1;$B$2:$B$12;"<"&H2). Для ads получится 3 950 ₽, для organic — 1 750 ₽.

Подвоха три. Первый — порядок аргументов: в СУММЕСЛИМН диапазон суммирования идёт первым, а в СУММЕСЛИ — третьим. Второй — граница. Условие "<="&ДАТА(2026;8;31) означает «до полуночи 31 августа», и заказ 1006 в 18:40 выпадает: organic за август становится 800 ₽ вместо 1 750 ₽. Полуинтервал «от начала месяца включительно до начала следующего не включая» от времени в дате не зависит. Третий — ">=H1" в кавычках: это сравнение с текстом «H1», а не со значением ячейки, и формула вернёт 0. Ссылку присоединяют через &.

Задача 7. СЧЁТЕСЛИМН: заказы — не клиенты

Условие: сколько клиентов оплатили заказ в канале organic? =СЧЁТЕСЛИМН($D$2:$D$12;"organic";$F$2:$F$12;"paid") возвращает 3. Но это три строки: клиент c02 заплатил дважды (1002 и 1006), c01 — один раз. Клиентов двое.

В Excel 2021 и Microsoft 365 различных клиентов считает пара УНИК и ФИЛЬТР: =СЧЁТЗ(УНИК(ФИЛЬТР($C$2:$C$12;($D$2:$D$12="organic")*($F$2:$F$12="paid")))) → 2. В более старых версиях — сводная с числом различных элементов (задача 12) или копия столбца после «Удалить дубликаты». На интервью полезно сразу спросить, что считается единицей: заказ, клиент или клиенто-день.

Задача 8. Дубли: какую строку оставить

Условие: очистите лист перед расчётом выручки. С задвоенным 1009 оплаченная выручка — 8 500 ₽, без него — 8 200 ₽. Команда «Данные → Удалить дубликаты» по всем столбцам убирает строку 12, потому что она полностью совпадает со строкой 11. Две строки заказа 1005 остаются: у них разный статус.

Если выбрать один столбец order_id, Excel оставит первое вхождение. Для 1005 это created, хотя заказ отменён. По справке Microsoft строки удаляются окончательно — их можно вернуть только отменой действия, — а сравнивается отображаемое значение, поэтому одна дата в разных форматах дублем не считается. Безопаснее сначала пометить повторы: =СЧЁТЕСЛИ($A$2:A2;A2) в столбце G. Диапазон растёт вместе со строкой, и у каждого повтора будет 2. Дальше решите, какой статус главный — обычно последний по времени, — и только потом удаляйте. Чистку, которая повторяется каждую неделю, удобнее вынести в Power Query.

Задача 9. Даты: дни до заказа и номер недели

Условие первое: сколько дней прошло от регистрации до первого заказа? Даты в Excel — числа, поэтому хватает вычитания: клиент c01 зарегистрирован 01.08, первый заказ сделал 03.08 — 2 дня, у c02 — 16 дней. Если в дате есть время, разность будет дробной: оборачивайте даты в ЦЕЛОЕ. РАЗНДАТ с единицей "D" даст то же число. Справка Microsoft оговаривает, что функция оставлена для совместимости со старыми книгами Lotus 1-2-3, что единицу "MD" использовать не рекомендуется и что при начальной дате позже конечной результат — ошибка. Для полных месяцев и лет РАЗНДАТ с "M" и "Y" удобна, для дней проще вычитать.

Условие второе: сгруппируйте заказы по неделям. НОМНЕДЕЛИ без второго аргумента начинает неделю с воскресенья, тип 2 — с понедельника, тип 21 считает недели по ISO 8601. Воскресенье 02.08.2026 при типе 1 попадает в одну неделю с понедельником 03.08, при типе 2 — в предыдущую. На стыке лет хуже: четверг 31.12.2026 и пятница 01.01.2027 лежат в одной неделе с понедельника, но тип 2 даёт им номера 53 и 1, а ISO — 53 и 53, причём пятница относится уже к 2027 году. Надёжный ключ недели — дата её понедельника: =ЦЕЛОЕ(B2)-ДЕНЬНЕД(B2;2)+1.

Задача 10. Сводная: выручка по каналам и месяцам

Условие: постройте по очищенному листу таблицу «канал × месяц» с выручкой и долей канала в месяце. Строки — channel, столбцы — date, сгруппированная по месяцам, значения — amount дважды, фильтр — status = paid. Второе поле amount переключите в «Дополнительные вычисления → % от суммы по столбцу».

Что проверяют. Первое — фильтр статуса: без него в август organic попадают обе строки заказа 1005, ещё 1 200 ₽. Второе — тип поля: если amount пришёл текстом, сводная по умолчанию считает количество, а не сумму. Третье — группировка по месяцам: она недоступна, пока в столбце дат есть текст. И сверка: общий итог сводной должен совпасть с =СУММЕСЛИМН по тем же условиям — 8 200 ₽. Пошагово построение разобрано в статье о сводных таблицах.

Задача 11. Условное форматирование: правило формулой

Условие: в таблице недельной конверсии (A — неделя, C — конверсия, строки 2–7: 5,0; 4,8; 3,6; 3,7; 2,9; 3,1%) подсветите целую строку, если конверсия упала больше чем на 20% к прошлой неделе. Выделите A3:C7 и создайте правило «Использовать формулу для определения форматируемых ячеек»: =$C3<0,8*$C2.

Формула пишется для левой верхней ячейки выделения и сдвигается на остальные, как обычная. $ перед C держит столбец, поэтому подсвечивается вся строка, а не одна ячейка. Без $ формула для ячеек столбца A сравнивала бы значения столбца A, и строка подсветилась бы не целиком. Если же закрепить $C$2, каждая неделя сравнивается с первой, и подсвечиваются четыре недели вместо двух. Подсветка должна вести к действию: в дашборде в Excel разобрано, как не превратить её в раскраску.

Задача 12. Воронка в сводной: пользователи, а не события

Условие: лист событий из 15 строк — user_id и шаг visit, cart, checkout, purchase. Пять пользователей: u1 прошёл всю воронку, дважды заходя на сайт, u2 дошёл до корзины и добавил в неё товар дважды, u3 только зашёл, u4 дошёл до оформления, u5 заходил трижды. Посчитайте воронку.

Сводная с user_id в значениях по умолчанию даёт «Количество» — число событий: визитов 8, покупок 1, конверсия 12,5%. Пользователей с визитом пятеро, конверсия — 20%. Чтобы считать людей, при создании сводной отметьте «Добавить эти данные в модель данных», тогда в параметрах поля появится «Число различных элементов». В официальной справке Microsoft отдельной статьи об этом нет, шаги подтверждены в ответах Microsoft Q&A; флажка нет у CSV, сохраните файл как .xlsx, а в Excel для Mac пункт может отсутствовать. Запасной путь — УНИК или «Удалить дубликаты» по паре «пользователь + шаг». Как читать такую воронку, разобрано в статье о конверсии и воронке.

Задача 13. Простая когорта: делите на размер когорты

Условие: сколько клиентов августовской регистрации платили в месяц регистрации и в следующий? Добавьте к оплаченным заказам без дублей столбец H с датой регистрации (ПРОСМОТРX с листа «Клиенты»), столбец I с её месяцем и номер месяца жизни =(ГОД(B2)-ГОД(H2))*12+МЕСЯЦ(B2)-МЕСЯЦ(H2). Сводная: строки — месяц регистрации, столбцы — месяц жизни, значения — число различных client_id.

Для августа сводная покажет 2 покупателя в месяце 0 (c01, c03) и 2 в месяце 1 (c01, c05). Подвох в процентах. «% от суммы по строке» делит на итог строки, а итог различных клиентов — 3, а не 4: c01 платил в обоих месяцах. Получится 66,7% и 66,7%, в сумме больше 100%. Когорта — это все зарегистрированные в августе, их четверо, включая c04, чей заказ отменён. Значит, 2 из 4 = 50% в каждом месяце. Размер когорты берите с листа «Клиенты» через СЧЁТЕСЛИМН, а не из сводной заказов. На четырёх клиентах выводы делать нельзя — задача проверяет только логику. Как это выглядит на реальных объёмах, показано в когортном анализе.

Сводка: задача, функция, частая ошибка

Эту таблицу удобно повторить вечером перед тестом. Все ошибки из правого столбца дают правдоподобное число, а не сообщение об ошибке, поэтому их и проверяют.

Как готовиться и что делать на самом тесте

Соберите себе такой же маленький лист и решите все 13 задач без подсказок, потом проверьте каждое число вторым способом: сводной против СУММЕСЛИМН, фильтром против СЧЁТЕСЛИМН. На тесте начинайте с осмотра данных: сколько строк, есть ли повторы ключа, какие статусы, нет ли текста в числах и времени в датах. Вслух назовите единицу счёта и период. Если задание открытое, сдайте рядом с ответом две-три строки о том, что проверили и что исключили.

Если вакансия про отчёты и BI, посмотрите отдельный разбор собеседования BI-аналитика. Если после Excel ждёт SQL — те же единицы счёта, дубли и границы дат там проверяются запросами; потренироваться можно в задачах с проверкой. Общий план по этапам отбора — в статье как подготовиться к собеседованию аналитика.

Частые вопросы

Если после Excel хочется перейти к тем же задачам на SQL, начните с курса «SQL с нуля» или с Симулятора SQL-аналитика, где выручку, конверсию и когорты считают на базе продукта.