Тест на знание Excel при приёме на работу: типовые задания и решения

Проверка навыков обычно проходит на одном файле: таблица продаж, справочник цен и несколько вопросов к ним. Справка Microsoft напоминает главное правило поиска: искомое значение для ВПР должно стоять в первом столбце диапазона^[1], и это первое, на чём спотыкаются кандидаты.

Задания по Excel удобнее решать по порядку. Сначала понять, что спрашивают, затем выбрать функцию, освоить базу — ВПР, ЕСЛИ, СУММЕСЛИ, ссылки, — попробовать решение в тренажёре и только потом применить его к своей таблице, протянув формулу на все строки.

Ключевые факты: задания по Excel

• Типовой набор состоит из 8 задач на одной таблице продаж: ВПР, ЕСЛИ, СУММЕСЛИ, СЧЁТЕСЛИ, доля, копирование ссылок, ОКРУГЛ и ИНДЕКС с ПОИСКПОЗ^[2].
• ВПР ищет искомое значение только в первом столбце диапазона^[1].
• Формула =A1*$B$1 из C1 после копирования в D2 становится =B2*$B$1^[2].
• Точный поиск с нулём отличается от поиска без последнего аргумента тем, что при промахе возвращает #Н/Д, а не ближайшее значение^[1].
• F4 переключает тип выделенной ссылки по кругу: абсолютная, смешанная, относительная^[3].

Какие задания по Excel дают на проверке навыков

Задания по Excel обычно проверяют поиск по справочнику, условия, суммы по условию и ссылки. Ниже — набор из 8 задач на одной таблице продаж, как в уроке тренажёра^[2].

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

Как решить типовые задачи: ВПР, ЕСЛИ и СУММЕСЛИ на продажах

Тестовое задание обычно строится на одной таблице продаж со справочником цен рядом. Сделки стоят в A2:C9, прайс — в A12:B16, сводка по менеджерам — под прайсом. Каждая формула ниже пересчитана в движке тренажёра^[2].

1. Сумма сделки: формула в D2 даёт 84000 — два холодильника по цене из прайса^[2].
2. Крупные сделки: =ЕСЛИ(D2>=50000;"крупная";"") помечает 84000 и 50000, а 12995 оставляет пустым.
3. Выручка и число сделок: СУММЕСЛИ даёт Котову 109495, СЧЁТЕСЛИ — 3 сделки.
4. Премия при ставке 3 %: =ОКРУГЛ(B18*$B$22;0) возвращает 3285, 4650 и 1893^[2].
5. Лучший менеджер: =ИНДЕКС(A18:A20;ПОИСКПОЗ(МАКС(B18:B20);B18:B20;0)) возвращает «Лисина».

Решите эти задачи в тренажёре: урок меняет данные и проверяет, что формулы работают после протягивания. Однако главная ловушка теста — не функции, а ссылки.

Что станет со ссылками при копировании формулы: ловушка теста

В задачах на копирование формула меняет относительные адреса и сохраняет адреса со знаком $. Ссылка на ячейку по умолчанию относительная, поэтому адрес в скопированной формуле сдвигается — так описано в справке Microsoft^[3].

Задача на копирование проверяет понимание адресов, а не память. Типичное условие: в C1 стоит =A1*$B$1, её скопировали в D2. Формула =A1*$B$1, скопированная на столбец правее и строку ниже, становится =B2*$B$1: относительный адрес сдвинулся на столбец и строку, а абсолютный остался на месте^[2]. Переключать тип ссылки удобно клавишей F4: выделите адрес в строке формул и нажимайте её по кругу^[3].

Подробнее о знаке доллара — на странице об абсолютных ссылках и клавише F4. Остаётся понять, где заученные решения перестают работать.

Когда заучивание типовых задач не поможет

Заученное решение задачи не поможет, если в задании изменили структуру таблицы или условие. Функция ВПР может находить значение только слева направо, так что перестановка столбцов ломает привычное решение^[4].

• Если столбец ответа стоит левее столбца поиска, ВПР не найдёт значение — нужна связка ИНДЕКС и ПОИСКПОЗ для поиска в любую сторону.
• Если в прайсе нет товара, точный поиск вернёт #Н/Д: значение не найдено^[1].
• При пропущенном последнем аргументе ВПР на несортированном прайсе может вернуть чужую цену.
• Когда в формуле доли нет знака $ у итога, после протягивания знаменатель сползёт вниз.
• При тесте в Google Таблицах ЕСЛИ записывается так же, с точкой с запятой между аргументами^[5], но условие и диапазоны стоит перепроверить.

Справка относит правила ВПР к Excel 2016, Excel 2019, Excel 2021, Excel 2024 и Microsoft 365, в том числе для Mac^[1], так что решение не зависит от версии. Хороший кандидат умеет объяснить каждое действие. Проверка навыков ценит не память, а понимание: почему стоит ноль в ВПР, зачем доллар и что вернёт формула на другой строке. Дальше по порядку тем — раздел первые шаги в Excel: ячейки и формулы, а подробный разбор поиска по прайсу — на странице о функции ВПР и ошибке #Н/Д.