Тест по Excel: проверь свои навыки работы с таблицами

Чтобы быстро оценить уровень владения Excel, нужно проверить знание абсолютных ссылок, современных функций поиска (XLOOKUP/ИНДЕКС+ПОИСКПОЗ) и инструментов анализа (сводные таблицы). Этот тест поможет выявить пробелы в знаниях: от базовой навигации до сложных массивных формул. Ниже представлены вопросы с вариантами ответов и подробным разбором, чтобы вы могли не просто проверить себя, но и понять логику работы программы.

Блок 1. Основы и ссылки на ячейки

Понимание того, как Excel обрабатывает адреса ячеек при копировании, — фундамент грамотной работы. Ошибки здесь приводят к неверным расчетам во всей таблице.

Вопрос 1

У вас есть формула =A1+B1 в ячейке C1. Что произойдет с формулой, если скопировать её в ячейку C2?

1. Она останется =A1+B1
2. Она изменится на =A2+B2
3. Она изменится на =B1+C1
4. Появится ошибка #ССЫЛКА!

✅ Правильный ответ: 2

Разбор: По умолчанию в Excel используются относительные ссылки. При копировании формулы вниз на одну строку номер строки в адресах ячеек увеличивается на 1. Столбцы не меняются, так как мы не двигались влево или вправо.

Вопрос 2

Какая запись фиксирует (делает абсолютной) только столбец A, но оставляет строку изменяемой?

1. $A$1
2. A$1
3. $A1
4. A1$

✅ Правильный ответ: 3

Разбор: Знак доллара $ ставится перед той частью адреса, которую нужно зафиксировать.

• $A$1 — абсолютная ссылка (не меняется ни столбец, ни строка).
• A$1 — зафиксирована строка.
• $A1 — зафиксирован столбец. Это удобно, например, при протягивании формулы вправо, когда нужно всегда ссылаться на первый столбец таблицы.

Вопрос 3

Как быстро переключать типы ссылок (относительная/абсолютная) при редактировании формулы?

1. Клавиша F4
2. Комбинация Ctrl+Shift+A
3. Правая кнопка мыши -> Формат ячеек
4. Клавиша Tab

✅ Правильный ответ: 1

Разбор: Клавиша F4 циклически меняет тип ссылки: $A$1 → A$1 → $A1 → A1. Это самый быстрый способ зафиксировать нужные части адреса.

Блок 2. Функции поиска и подстановки

Это самая частая тема на собеседованиях. Важно понимать разницу между устаревшими методами и современными инструментами.

Вопрос 4

В чем главное ограничение функции ВПР (VLOOKUP)?

1. Она не работает с текстовыми данными
2. Она может искать значение только слева от искомого столбца
3. Она может искать значение только справа от искомого столбца
4. Она не поддерживает точное совпадение

✅ Правильный ответ: 3

Разбор: ВПР ищет искомое значение в первом (левом) столбце указанного диапазона и возвращает значение из столбца справа. Если нужный результат находится левее искомого ключа, ВПР не справится без вспомогательных столбцов или функции ПРОСМОТР.

Вопрос 5

Какая комбинация функций является классической альтернативой ВПР и позволяет искать данные в любом направлении?

1. СУММ + ЕСЛИ
2. ИНДЕКС + ПОИСКПОЗ (INDEX + MATCH)
3. ЛЕВСИМВ + ПРАВСИМВ
4. ДАТА + ГОД

✅ Правильный ответ: 2

Разбор: Связка ИНДЕКС(диапазон_результатов; ПОИСКПОЗ(искомое_значение; диапазон_поиска; 0)) гибче ВПР. ПОИСКПОЗ находит номер строки, а ИНДЕКС возвращает значение из этой строки в нужном столбце. Это работает быстрее на больших объемах данных и не ломается при добавлении новых столбцов.

Вопрос 6

Что делает функция ПРОСМОТРX (XLOOKUP), доступная в Excel 2021 и Microsoft 365?

1. Заменяет только ВПР
2. Заменяет ВПР, ГПР и частично ИНДЕКС+ПОИСКПОЗ, позволяя искать в любом направлении и задавать значение, если ничего не найдено
3. Работает только с числовыми данными
4. Автоматически строит графики по найденным данным

✅ Правильный ответ: 2

Разбор: ПРОСМОТРX — это универсальный инструмент. Синтаксис: ПРОСМОТРX(искомое; где_ищем; что_возвращаем; [если_не_найдено]; [тип_совпадения]). Она умеет искать слева направо и справа налево, а также встроенной опцией обрабатывает ошибки (#Н/Д), заменяя их на заданный текст (например, "Нет данных").

Блок 3. Работа с данными и сводные таблицы

Сводные таблицы (Pivot Tables) — мощный инструмент для быстрой агрегации тысяч строк данных.

Вопрос 7

Как обновить сводную таблицу, если в исходных данных появились новые строки?

1. Пересоздать сводную таблицу заново
2. Нажать «Обновить» на вкладке «Анализ сводной таблицы» или правой кнопкой мыши -> Обновить
3. Сохранить и закрыть файл
4. Изменить формат ячеек исходной таблицы

✅ Правильный ответ: 2

Разбор: Сводные таблицы не обновляются автоматически при изменении данных (если не настроен макрос или параметр автообновления при открытии). Нужно явно нажать кнопку Обновить.

Совет: Чтобы новые строки попадали в сводную автоматически, преобразуйте исходный диапазон в «Умную таблицу» (Ctrl+T). Тогда источник данных для сводной будет динамическим.

Вопрос 8

Что такое «Срез» (Slicer) в контексте сводных таблиц?

1. Инструмент для обрезки лишних символов в тексте
2. Визуальный фильтр для удобного отбора данных в сводной таблице
3. Функция для разделения текста по столбцам
4. Способ удаления дубликатов

✅ Правильный ответ: 2

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

Блок 4. Логические и статистические функции

Вопрос 9

Какая функция подсчитывает количество ячеек, соответствующих нескольким условиям (например, продажи менеджера Иванова в январе)?

1. СЧЁТЕСЛИ (COUNTIF)
2. СЧЁТЕСЛИМН (COUNTIFS)
3. СУММЕСЛИ (SUMIF)
4. ПОДСЧЁТ (COUNT)

✅ Правильный ответ: 2

Разбор:

• СЧЁТЕСЛИ работает только с одним условием.
• СЧЁТЕСЛИМН позволяет задать множество пар «диапазон-условие».
• СУММЕСЛИ суммирует значения, а не считает количество.
• ПОДСЧЁТ считает любые числа, игнорируя условия.

Вопрос 10

Что вернет формула =ЕСЛИ(A1>100; "Премиум"; "Стандарт"), если в A1 находится число 100?

1. "Премиум"
2. "Стандарт"
3. Ошибку #ЗНАЧ!
4. Пустую ячейку

✅ Правильный ответ: 2

Разбор: Условие A1>100 строгое. 100 не больше 100, оно равно. Поэтому условие ложно, и функция возвращает значение для случая «ЛОЖЬ» — "Стандарт". Для включения границы нужно использовать >=.

Блок 5. Продвинутые инструменты и горячие клавиши

Вопрос 11

Какая горячая клавиша вызывает окно «Специальная вставка» (где можно вставить только значения, форматы или транспонировать данные)?

1. Ctrl+V
2. Ctrl+Alt+V
3. Shift+Insert
4. Alt+Enter

✅ Правильный ответ: 2

Разбор: Ctrl+Alt+V открывает диалоговое окно специальной вставки. Также часто используется последовательность Ctrl+C -> Alt+Е+С (старый стиль) или Ctrl+C -> Menu Key -> V. Но Ctrl+Alt+V — стандартный современный хоткей.

Вопрос 12

Для чего нужна функция «Текст по столбцам»?

1. Для объединения нескольких ячеек в одну
2. Для разделения данных из одного столбца на несколько (например, ФИО или Дата.Время)
3. Для перевода текста на другой язык
4. Для удаления пробелов

✅ Правильный ответ: 2

Разбор: Этот инструмент (вкладка «Данные») позволяет разделить содержимое ячейки по разделителю (запятая, пробел, точка с запятой) или по фиксированной ширине. Незаменим при очистке импортированных данных.

Вопрос 13

Что означает ошибка #ДЕЛ/0! (#DIV/0!)?

1. Ячейка пуста
2. Деление на ноль или на пустую ячейку
3. Неверный формат даты
4. Ссылка на несуществующий лист

✅ Правильный ответ: 2

Разбор: Математически деление на ноль невозможно. В Excel пустая ячейка в знаменателе также считается нулем. Чтобы избежать этой ошибки в отчетах, используют конструкцию =ЕСЛИОШИБКА(A1/B1; 0) или =ЕСЛИ(B1=0; 0; A1/B1).

Сравнительная таблица функций поиска

Для наглядности различий между основными инструментами поиска используйте эту шпаргалку:

Частые ошибки при работе в Excel

Даже опытные пользователи допускают эти промахи. Проверьте себя:

1. Хранение чисел как текста. Если в углу ячейки зеленый треугольник, число может не участвовать в расчетах (СУММ его игнорирует).
   • Решение: Использовать «Текст по столбцам» -> «Готово» или функцию ЗНАЧЕН.
2. Жесткая прописка диапазонов. Использование A1:A100 вместо «Умной таблицы» (Таблица1[Столбец1]).
   • Проблема: При добавлении новых данных внизу формулы и сводные таблицы их не увидят.
3. Игнорирование абсолютных ссылок. Копирование формулы с относительной ссылкой на константу (например, курс валюты в одной ячейке) приводит к ошибке в остальных строках.
4. Сложные вложенные ЕСЛИ. Более 3-4 уровней вложенности делают формулу нечитаемой.
   • Решение: Использовать функцию ПРОСМОТР или ВПР для замены сложных логических конструкций.

FAQ: Вопросы о тестировании навыков Excel

В: Достаточно ли знать только ВПР для работы аналитиком? О: Нет. В современных вакансиях часто требуют знание сводных таблиц, базового понимания Power Query и умения работать с функциями типа СУММЕСЛИМН. Знание ПРОСМОТРX будет преимуществом.

В: Как проверить свой уровень объективно? О: Попробуйте решить практическую задачу: возьмите выгрузку продаж, очистите её от дубликатов, приведите даты к единому формату, посчитайте итоги по категориям через сводную таблицу и визуализируйте динамику графиком. Если вы делаете это менее чем за 15 минут — ваш уровень выше среднего.

В: Чем Excel отличается от Google Таблиц в контексте этих функций? О: Логика ссылок и базовые функции (СУММ, ЕСЛИ, ВПР) идентичны. Однако в Google Таблицах нет нативного аналога ПРОСМОТРX (хотя есть свои уникальные функции вроде IMPORTRANGE), а интерфейс сводных таблиц отличается.

В: Что учить после освоения формул? О: Освойте Power Query (вкладка «Данные» -> «Получить данные»). Это инструмент для автоматической обработки и очистки данных без написания сложных формул. Он экономит часы рутинной работы.