Тестирование навыков: подтверждаем продвинутый уровень Excel

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

Каждый вопрос теста представляет собой практическую задачу с несколькими вариантами ответов. Задачи подобраны так, чтобы оценить вашу способность:

• работать с формулами и функциями;
• строить и настраивать диаграммы;
• анализировать данные с помощью сводных таблиц;
• использовать мощные инструменты, такие как Power Query.

Вопрос 1:

Вы хотите, чтобы в ячейке отображались текущие дата и время. Пример отображения: 31.01.20 00:01. Какая формула должна стоять в ячейке?

Варианты ответа:

1. =ДАТАЗНАЧ(СЕГОДНЯ();"ДД.ММ.ГГ ЧЧ:ММ") или =DATE(TODAY();"DD.MM.YY HH:MM")
2. =ТЕКСТ(ТДАТА();"ДД.ММ.ГГ ЧЧ:ММ") или =TEXT(NOW();"DD.MM.YY HH:MM")
3. =ФОРМАТ(ТДАТА();"ДД.ММ-ГГГГ ЧЧ:ММ") или =FORMAT(NOW();"DD-MM-YYYY HH:MM")
4. =Ф.ТЕКСТ(СЕЙЧАС();"ДД.ММ.ГГ ЧЧ:ММ") или =F.TEXT(NOW();"DD.MM.YY HH:MM")
5. =ФОРМАТ(СЕЙЧАС();"ММ.ДД.ГГГГ ЧЧ:ММ") или =FORMAT(NOW();"MM.DD.YYYY HH:MM")

Обоснование:

Функция ТЕКСТ или TEXT позволяет преобразовать значение времени и даты в текстовый формат с заданным шаблоном отображения. Использование функции NOW (или СЕЙЧАС) добавляет в ячейку текущую дату и время, а формат "DD.MM.YY HH:MM" задаёт нужный формат отображения.

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

2. =ТЕКСТ(ТДАТА();"ДД.ММ.ГГ ЧЧ:ММ") или =TEXT(NOW();"DD.MM.YY HH:MM").

Вопрос 2:

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

Варианты ответа:

1. В диалоговом окне «Сортировка» во вкладке «Данные» > «Сортировка и фильтр» добавить к сортировке столбцы с помощью кнопки «Добавить уровень».
2. Выделить один столбец, во вкладке «Данные» > «Сортировка и фильтр» выполнить сортировку, аналогично выполнить выделение и сортировку других столбцов.
3. Выделить все столбцы, по которым нужно выполнить сортировку, во вкладке «Данные» > «Сортировка и фильтр» выполнить сортировку.
4. Во вкладке «Разметка страницы» > «Упорядочение» в диалоговом окне «Группировать» настроить сортировку по группам.
5. Такую сортировку можно выполнить только вручную.

Обоснование:

Сортировка по нескольким столбцам выполняется через диалоговое окно «Сортировка», где можно добавлять уровни сортировки для нескольких столбцов и задавать приоритет каждого уровня. Это позволяет выполнить последовательную сортировку данных.

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

1. В диалоговом окне «Сортировка» во вкладке «Данные» > «Сортировка и фильтр» добавить к сортировке столбцы с помощью кнопки «Добавить уровень».

Вопрос 3:

Какая формула должна находиться в ячейке B7, чтобы, растягивая это значение в ячейки справа, вы получили корректный результат?

Варианты ответа:

1. =B6/$E6
2. =B6/E6
3. =B6/E$6
4. =$B6/E6
5. =B6/E$6

Обоснование:

Для корректного вычисления доли от общего дохода в каждой колонке (B, C, D) необходимо закрепить ссылку на общий доход (ячейка E6) с помощью абсолютной ссылки. Абсолютная ссылка на строку $6 позволяет сохранить привязку к строке 6 при растягивании формулы по горизонтали.

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

1. =B6/$E6

Вопрос 4:

Внутри формулы суммирования вы хотите создать ссылку на диапазон ячеек C1:C5, который находится на другом листе с названием Инвентаризация товара. Как будет выглядеть формула?

Варианты ответа:

1. =СУММ('Инвентаризация товара'!C1:C5) или =SUM('Инвентаризация товара'!C1:C5)
2. =СУММ(Инвентаризация товара;C1:C5) или =SUM(Инвентаризация товара;C1:C5)
3. =СУММ(Инвентаризация товара:C1:C5) или =SUM(Инвентаризация товара:C1:C5)
4. =СУММ('Инвентаризация товара';C1:C5) или =SUM('Инвентаризация товара';C1:C5)
5. =СУММ('Инвентаризация товара'C1:'Инвентаризация товара'C5) или =SUM('Инвентаризация товара'C1:'Инвентаризация товара'C5)

Обоснование:

Чтобы корректно указать диапазон ячеек на другом листе, нужно:

• Название листа взять в одинарные кавычки (' '), если оно содержит пробелы.
• Добавить восклицательный знак (!) перед диапазоном ячеек, чтобы Excel понимал, что ссылка идет на другой лист.

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

1. =СУММ('Инвентаризация товара'!C1:C5) или =SUM('Инвентаризация товара'!C1:C5)

Вопрос 5:

Когда вы допускаете опечатки (например, случайное нажатие CAPS LOCK), MS Excel исправляет правописание. Вы хотите отключить эту возможность. Как это можно сделать?

Варианты ответа:

1. Нажать правой кнопкой мыши на любую ячейку, во вкладке «Формат ячеек…» в поле «Защита» снять галочку напротив функции исправления опечаток.
2. Нажать правой кнопкой мыши на любую ячейку и поставить галочку напротив команды «Отключить автозамену».
3. Использовать комбинацию клавиш Ctrl+Shift+O, чтобы отключить исправление опечаток.
4. Во вкладке «Рецензирование» > «Правописание» в диалоговом окне «Орфография» выбрать отключение функции правописания.
5. В диалоговом окне «Параметры Excel» во вкладке «Параметры автозамены…» снять галочки напротив функций исправления опечаток.

Обоснование:

Чтобы отключить автоматическое исправление правописания, необходимо:

• Перейти в настройки Excel.
• Найти раздел, отвечающий за автозамену, и отключить соответствующие функции.

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

5. В диалоговом окне «Параметры Excel» во вкладке «Параметры автозамены…» снять галочки напротив функций исправления опечаток.

Вопрос 6:

Какое утверждение НЕВЕРНО?

Варианты ответа:

1. Попытка применения функции СЦЕПИТЬ (CONCATENATE) на числовых строках вызовет ошибку.
2. Для суммирования содержимого ячеек можно использовать знак «+», а для склеивания — знак «&».
3. Функция СЦЕП (CONCAT), в отличие от функции СЦЕПИТЬ (CONCATENATE), может принимать в качестве аргумента диапазон ячеек.
4. В функции СЦЕП (CONCAT) нельзя задать разделитель.
5. Функция ОБЪЕДИНИТЬ (TEXTJOIN) позволяет пропускать пустые ячейки при объединении.

Обоснование:

1. Функция СЦЕПИТЬ (CONCATENATE) может работать как с текстом, так и с числами, а числовые строки (если формат ячейки текстовый) будут обработаны корректно.
2. Для суммирования используется «+», а для объединения текстов — «&», это правильное утверждение.
3. Функция СЦЕП (CONCAT) действительно поддерживает диапазон ячеек.
4. В функции СЦЕП (CONCAT) нельзя задать разделитель — это отличие от TEXTJOIN.
5. TEXTJOIN позволяет пропускать пустые ячейки — это корректное утверждение.

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

1. Попытка применения функции СЦЕПИТЬ (CONCATENATE) на числовых строках вызовет ошибку.

Вопрос 7:

Данные, с которыми вы хотите работать, представлены на нескольких листах Excel-файла. Что из перечисленного НЕ является способом объединения таких данных на одном листе?

Варианты ответа:

1. Команда «Консолидация» на листе «Данные»
2. Функция ВПР (VLOOKUP)
3. Команда «Группировать» на листе «Данные»
4. Сводные таблицы
5. Функция Power Query

Обоснование:

1. Команда «Консолидация» используется для объединения данных с разных листов в Excel.
2. Функция ВПР может использоваться для поиска и объединения данных с разных источников, но не напрямую для автоматического объединения данных.
3. Команда «Группировать» применяется для создания групп и упрощения представления данных, но не для объединения данных с разных листов.
4. Сводные таблицы позволяют обрабатывать данные с нескольких источников, включая разные листы.
5. Power Query — мощный инструмент для объединения данных с разных листов и внешних источников.

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

3. Команда «Группировать» на листе «Данные».

Вопрос 8:

Какой элемент диаграммы ОТСУТСТВУЕТ на этом графике?

Варианты ответа:

1. Название диаграммы
2. Линия тренда
3. Легенда
4. Линия сетки
5. Метка данных

Обоснование:

На диаграмме представлены следующие элементы:

• Название диаграммы: "Прибыль по кварталам года" указано сверху.
• Линия тренда: отображает общую тенденцию изменения прибыли.
• Линия сетки: помогает визуально интерпретировать значения по осям.
• Метки данных: отображают конкретные значения прибыли для каждого квартала.

Однако легенда отсутствует, так как диаграмма не имеет категорий, для которых она была бы необходима.

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

3. Легенда

Вопрос 9:

Вы хотите выделить дублирующиеся данные в столбце определенным цветом. Как вы можете это сделать?

Варианты ответов:

1. Нажать на столбец с дублирующимися данными с зажатой клавишей Shift.
2. Установить инструмент «Фильтр» на нужный столбец и выбрать «Показать только уникальные значения» в выпадающем списке.
3. На главной вкладке в поле «Условное форматирование» выбрать «Правила выделения ячеек» > «Повторяющиеся значения…».
4. Нажать правой кнопкой мыши на столбец с дублирующимися данными и выбрать «Выделить дублирующиеся значения».
5. На главной вкладке в поле «Редактирование» нажать «Найти и выделить» > «Поиск дубликатов».

Обоснование:

Для выделения дублирующихся значений в Excel используется инструмент «Условное форматирование», который позволяет применять цветовое выделение для повторяющихся данных. В данном случае нужно выбрать пункт «Повторяющиеся значения…» в меню «Условного форматирования». Другие перечисленные методы либо не подходят для выделения дублирующихся данных цветом (например, инструмент «Фильтр»), либо вообще не существуют в Excel.

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

3. На главной вкладке в поле «Условное форматирование» выбрать «Правила выделения ячеек» > «Повторяющиеся значения…».

Вопрос 10:

Вы хотите преобразовать местное время на московское для каждого из случаев в столбце A. Для этого вы указали разницу в часах между местным и московским временем в столбце «Смещение» и использовали формулу, пример которой приведен в столбце C5. В столбцах A и C настроен формат времени, в столбце B — числовой формат.

Но применение формулы не дало нужных результатов. Как нужно изменить формулу в столбце C5, чтобы получить верное вычисление времени по Москве?

Варианты ответов:

1. =A5+B5/24
2. =A5+ЧАС(B5) или =A5+HOUR(B5)
3. =A5+ВРЕМЯ(B5) или =A5+TIME(B5)
4. =A5+ТЕКСТ(B5,"ЧЧ:ММ") или =A5+TEXT(B5,"HH:MM")
5. =A5+B5*60

Обоснование:

Столбец B содержит смещение во времени, выраженное в часах, которое нужно преобразовать в формат, совместимый с временем в Excel. Поскольку Excel интерпретирует время как доли суток, необходимо разделить значение в часах на 24, чтобы корректно добавить смещение ко времени. Простое сложение (A5+B5) не учитывает правильное преобразование и вызывает ошибку отображения (############).

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

1. =A5+B5/24

Вопрос 11:

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

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

Варианты ответов:

1. =СРЗНАЧ(A:AB:B) или =AVERAGE(A:AB:B)
2. =СРЗНАЧ(A2:B6) или =AVERAGE(A2:B6)
3. =СРЗНАЧ(МАССИВ(A2B2;A6B6)) или =AVERAGE(ARRAY(A2B2;A6B6))
4. =СРЗНАЧ(КоличествоСредний_чек) или =AVERAGE(КоличествоСредний_чек)
5. =СУММПРОИЗВ(КоличествоСредний_чек) или =SUMPRODUCT(КоличествоСредний_чек)

Обоснование:

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

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

4. =СРЗНАЧ(КоличествоСредний_чек) или =AVERAGE(КоличествоСредний_чек)

Вопрос 12:

Что НЕЛЬЗЯ сделать при создании новой сводной таблицы в Excel, используя готовые функции мастера сводных таблиц?

Варианты ответов:

1. Заменить существующий лист на созданную сводную таблицу.
2. Создать сводную таблицу из нескольких листов.
3. Использовать внешний источник данных при создании сводной таблицы.
4. Сделать из сводной таблицы обычную.
5. Создать пустой макет сводной таблицы без заполнения данными.

Обоснование:

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

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

1. Заменить существующий лист на созданную сводную таблицу.

Вопрос 13:

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

Варианты ответов:

1. В таблицу с исходными данными добавить столбец, где доля выполнения плана будет посчитана для каждого товара — после обновления столбец появится в сводной таблице.
2. На вкладке в поле «Работа со сводными таблицами» > «Анализ» выбрать «Поля, элементы и наборы» > «Вычисляемое поле...».
3. Сохранить сводную таблицу как статичную и вручную добавить в нее необходимый расчет.
4. Во вкладке «Сводные таблицы» > «Анализ данных» выбрать «Вычислить долю или процент» и настроить параметры расчета.
5. Нажать правой кнопкой мыши на сводную таблицу и выбрать «Добавить расчет по формуле».

Обоснование:

Для добавления вычисляемого столбца с расчетом доли выполнения плана в сводной таблице можно использовать функцию «Вычисляемое поле», доступную через «Работа со сводными таблицами» > «Анализ» > «Поля, элементы и наборы» > «Вычисляемое поле». Эта функция позволяет добавить дополнительные вычисления в существующую сводную таблицу без изменения исходных данных. Другие методы, такие как сохранение таблицы как статичной, менее предпочтительны, поскольку не сохраняют динамичность данных.

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

2. На вкладке в поле «Работа со сводными таблицами» > «Анализ» выбрать «Поля, элементы и наборы» > «Вычисляемое поле...».

Вопрос 14:

Какое утверждение о макросах НЕВЕРНО?

Варианты ответов:

1. Нельзя внести изменения в уже записанный макрос.
2. Файл книги с поддержкой макросов Excel сохраняется в формате .xlsm.
3. Обычно макросы пишутся на языке программирования VBA.
4. Можно настроить автоматический запуск макроса при открытии книги.
5. Макросы позволяют автоматизировать выполнение повторяющихся операций.

Обоснование:

1. Нельзя внести изменения в уже записанный макрос — это утверждение неверно. Записанный макрос можно редактировать, если открыть редактор VBA (Visual Basic for Applications), который доступен в Excel.
2. Файл книги с поддержкой макросов Excel сохраняется в формате .xlsm — это утверждение верно. Формат .xlsm поддерживает макросы в Excel.
3. Обычно макросы пишутся на языке программирования VBA — верное утверждение. VBA — это стандартный язык программирования для создания макросов в Microsoft Office.
4. Можно настроить автоматический запуск макроса при открытии книги — верно. Это можно сделать, используя процедуру Workbook_Open в модуле VBA.
5. Макросы позволяют автоматизировать выполнение повторяющихся операций — это основная цель макросов, так что утверждение верно.

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

1. Нельзя внести изменения в уже записанный макрос.

Вопрос 15:

Вы знаете сумму займа и процентную ставку. Сумма ежегодной выплаты определяется функцией ПЛТ() в ячейке B4. Вы хотите определить оптимальный срок займа (для которого предназначена ячейка B2), зная комфортный ежегодный платеж. Какой инструмент Excel вам в этом поможет?

Варианты ответов:

1. Создание листа прогноза
2. Математическая функция ФАКТР() или FACT()
3. Кнопка «Влияющие ячейки»
4. Финансовая функция СТАВКА() или RATE()
5. Средство подбора параметров

Обоснование:

1. Создание листа прогноза — инструмент предназначен для анализа данных и создания прогнозов, но он не используется для подбора параметров.
2. Математическая функция ФАКТР() или FACT() — предназначена для вычисления факториалов чисел, что не имеет отношения к финансовым расчетам.
3. Кнопка «Влияющие ячейки» — позволяет отследить зависимости ячеек, но не решает задачу определения параметров.
4. Финансовая функция СТАВКА() или RATE() — используется для вычисления процентной ставки, а не для подбора срока займа.
5. Средство подбора параметров — инструмент Excel, который автоматически подбирает значение переменной (например, срок займа), чтобы достигнуть заданного результата (комфортного ежегодного платежа).

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

5. Средство подбора параметров.

Вопрос 16:

Что такое спарклайн в Excel?

Варианты ответов:

1. Небольшая диаграмма, помещенная в одну ячейку
2. Инструмент для создания интерактивных графиков
3. Метод сжатия Excel-файлов
4. Функция для автоматического удаления дубликатов
5. Операция для защиты ячеек от изменений

Обоснование:

1. Небольшая диаграмма, помещенная в одну ячейку — это правильное определение спарклайнов. Они визуализируют данные прямо внутри ячейки, занимая минимум места.
2. Инструмент для создания интерактивных графиков — спарклайны не являются интерактивными графиками, хотя они предоставляют быстрый визуальный анализ.
3. Метод сжатия Excel-файлов — не относится к спарклайнам. Сжатие файлов — это отдельная функция.
4. Функция для автоматического удаления дубликатов — функция удаления дубликатов существует в Excel, но это не связано со спарклайнами.
5. Операция для защиты ячеек от изменений — защита ячеек в Excel реализуется через блокировку ячеек и пароли, а не с помощью спарклайнов.

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

1. Небольшая диаграмма, помещенная в одну ячейку.

Вопрос 17:

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

Варианты ответов:

1. СМЕЩ() или OFFSET()
2. ДМИН(), ДМАКС() или DMIN(), DMAX()
3. ОТРЕЗОК() или INTERCEPT()
4. ЕСЛИМН() или IFS()
5. ВЫБОР() или CHOOSE()

Обоснование:

1. СМЕЩ() или OFFSET() — это наиболее подходящие функции для создания динамических диапазонов. Они позволяют задавать диапазон ячеек, определяя начальную точку, количество строк и столбцов.
2. ДМИН(), ДМАКС() или DMIN(), DMAX() — функции используются для работы с базами данных, но они не предназначены для создания диапазонов.
3. ОТРЕЗОК() или INTERCEPT() — это функции анализа данных, используемые в регрессионном анализе, а не для работы с диапазонами.
4. ЕСЛИМН() или IFS() — предназначены для работы с условиями, но не для формирования диапазонов.
5. ВЫБОР() или CHOOSE() — позволяет выбирать значение из списка, но не подходит для создания диапазонов.

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

1. СМЕЩ() или OFFSET().

Вопрос 18:

Какой основной недостаток есть у сценариев в Excel?

Варианты ответов:

1. В Диспетчере сценариев нет функционала для создания отчетов по сценариям.
2. Сценарии не могут использоваться для прогнозирования, а только для описания текущих данных.
3. Нет функционала, позволяющего сравнить несколько сценариев между собой.
4. Значения исходных данных не находятся на листе, а скрыты в Диспетчере сценариев.
5. Работа с Диспетчером сценариев требует навыков программирования.

Обоснование:

1. В Диспетчере сценариев нет функционала для создания отчетов по сценариям. — Это неверно, так как Excel позволяет создавать сводные отчеты по сценариям.
2. Сценарии не могут использоваться для прогнозирования, а только для описания текущих данных. — Это утверждение не совсем точное. Сценарии могут использоваться для анализа "что-если", что подразумевает прогнозирование.
3. Нет функционала, позволяющего сравнить несколько сценариев между собой. — Это основное ограничение сценариев в Excel. В Диспетчере сценариев нельзя удобно сравнить данные нескольких сценариев одновременно без создания дополнительного отчета вручную.
4. Значения исходных данных не находятся на листе, а скрыты в Диспетчере сценариев. — Это неверно, так как исходные данные всегда остаются на листе, а сценарии лишь изменяют значения.
5. Работа с Диспетчером сценариев требует навыков программирования. — Это утверждение неверно, так как использование сценариев в Excel не требует программирования.

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

3. Нет функционала, позволяющего сравнить несколько сценариев между собой.

Готово! Дальнейшие шаги:

После успешного прохождения теста вы сможете уверенно подтвердить свои навыки и повысить свои шансы на карьерный рост.

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

📌 Тест на базовые навыки Excel — Как подтвердить базовые навыки Excel

📌 Тест на средний уровень навыков Excel — Подтверждаем средний уровень навыков Excel

Теперь настало время проверить свои знания! Вы готовы к тесту на продвинутый уровень? Тогда вперед! 💪

Примечание

Дорогие читатели! Если материалы данной статьи помогли вам успешно пройти тест, буду признателен, если вы поставите лайк 👍🏻 именно той статье, которая соответствовала вашему уровню подготовки. Также, если тестирование оказалось неудачным ❌, пожалуйста, оставьте комментарий 📝 с указанием количества ошибок допущенных в тесте.

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