Excel(17/18). Магия визуализации: строим динамические диаграммы и дашборды в полночь.

Цифры в таблицах полезны, но руководителю проще увидеть картинку: стрелки растут — хорошо, падают — нужно разбираться. Динамическая диаграмма подключается к вашим данным и меняется сама — стоит обновить значения или применить фильтр. В статье я объясню, как связать график со сводной таблицей или слайсером, добавить кнопки‑фильтры и получить живой дашборд, который обновляется без ручной перерисовки.

📌Навигация по материалам в Telegram

Для практики я разберу тестовое задание продвинутого уровня

Вопрос 1

В ячейке должно отображаться текущее дата и время в формате 31.01.2020 00:01. Какую формулу следует использовать?

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

1. =ДАТАЗНАЧ(СЕГОДНЯ();"ДД.ММ.ГГ ЧЧ:ММ") или =DATE(TODAY();"DD.MM.YY HH:MM")

2. =ФОРМАТ(СЕЙЧАС();"MM.ДД.ГГГГ ЧЧ:ММ") или =FORMAT(NOW();"MM.DD.YYYY HH:MM")

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

4. =ФОРМАТ(ТДАТА();"ДД-ММ-ГГГГ ЧЧ:ММ") или =FORMAT(NOW();"DD-MM-YYYY HH:MM")

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

Объяснение:

Функция СЕЙЧАС() / NOW() возвращает текущее системное время и дату.
Функция ТЕКСТ() / TEXT() переводит число даты‑времени в строку по указанному формату.
Шаблон ДД.ММ.ГГГГ ЧЧ:ММ (или DD.MM.YYYY HH:MM) выводит именно «день.месяц.год часы:минуты» четырёхзначным годом — как требуется.
Других правильных комбинаций здесь нет:

1. ДАТАЗНАЧ, FORMAT, «Ф.ТЕКСТ» или «ТДАТА» — либо несуществующие, либо дают неверный результат.

2. Шаблоны с YY отобразят лишь «20», а не «2020».

Выбранный ответ:

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

Вопрос 2

Какое действие необходимо выполнить, чтобы настроить многоуровневую сортировку в Excel?

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

1. Выполнить команду «Сгруппировать» на вкладке «Данные»

2. Воспользоваться командой «Фильтр» на вкладке «Данные»

3. В окне «Сортировка» на вкладке «Данные» добавить уровни сортировки и порядок сортировки

4. Выделить несколько столбцов и выбрать «Сортировать всё»

5. Использовать функцию «СОРТИРОВКА» в ячейке с перечислением уровней сортировки

Объяснение:

Многоуровневая сортировка — это когда сначала сортируем, например, по отделу, а внутри отдела — по зарплате.
Для этого в Excel есть специальное окно «Сортировка» (кнопка «Сортировка» на вкладке «Данные»).

1. Нажимаем кнопку «Сортировка».

2. Выбираем первый столбец и порядок (например, «Отдел» → А→Я).

3. Нажимаем «Добавить уровень».

4. Задаём второй столбец (например, «Зарплата» → по возрастанию).

Именно там можно добавить несколько уровней в нужной очередности.
Остальные варианты либо группируют/фильтруют данные, либо сортируют только одним щелчком без возможности уровней, либо требуют формул.

Выбранный ответ:

3. В окне «Сортировка» на вкладке «Данные» добавить уровни сортировки и порядок сортировки

Вопрос 3

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

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

1. =C$2-B2

2. =C2-B2

3. =$C2-$B2

4. =C$2-B$2

5. =C2-$B2

Объяснение:

Разница = Февраль – Январь → ячейка C2 минус B2.
При копировании формулы вниз строки должны сменяться автоматически (C3–B3, C4–B4 …).
Поэтому ссылки на строку должны оставаться относительными, без знаков $.

Разбор:

Выбранный ответ:

2. =C2-B2

Вопрос 4

Выберите правильный вариант синтаксиса формулы HYPERLINK в Excel для создания гиперссылки на ячейку A1 на листе «Отчёт».

Объяснение:

Формула HYPERLINK (рус. ГИПЕРССЫЛКА) имеет вид:

=HYPERLINK(адрес; [дружественный_текст])

Для ссылки внутри той же книги:

1. Перед адресом ставим решётку # — Excel понимает, что это внутренняя ссылка.

2. После решётки пишем имя листа, затем восклицательный знак ! и адрес ячейки.

3. Если имя листа содержит пробелы или русские буквы, его безопасно брать в одинарные кавычки '...'.

Итог:
=ГИПЕРССЫЛКА("#'Отчет'!A1";"Перейти к Отчету")

(или в англоязычном Excel
=HYPERLINK("#'Отчет'!A1","Перейти к Отчету"))

Выбранный ответ:

=ГИПЕРССЫЛКА("#'Отчет'!A1";"Перейти к Отчету") / =HYPERLINK("#'Отчет'!A1","Перейти к Отчету")

Вопрос 5

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

Объяснение:

В Excel есть специальная системная установка «Использовать фиксированное число десятичных разрядов».
Находится в Файл → Параметры → Дополнительно → раздел «Параметры редактирования».
Достаточно поставить галочку и указать 2 разряда — после этого любое введённое число (123) будет автоматически отображаться как 1,23; уже введённые данные не тронутся, но для всего нового правило действует «само по себе».
Это именно глобальная настройка редактирования, а не локальное форматирование ячеек или стиль, поэтому она соответствует условию «без вмешательства пользователя» при каждой новой записи.

Выбранный ответ:

В диалоговом окне «Параметры Excel» на вкладке «Дополнительно» настроить формат отображения чисел в разделе «Параметры редактирования»

Вопрос 6

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

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

1. Создать пользовательскую функцию VBA

2. Использовать функции ЛЕВСИМВ (LEFT) и ПРАВСИМВ (RIGHT) с ПОИСК (SEARCH)

3. Применить функцию РАЗДЕЛИТЬ (SPLIT) для разделения строки на массив слов и использовать ИНДЕКС (INDEX) для доступа к первому и последнему элементу

4. Использовать функцию ТЕКСТОВЫЕ.ЧАСТИ (TEXTJOIN)

5. Использовать массивную формулу с РАЗДЕЛИТЬ + ОБЪЕДИНИТЬ (CONCATENATE)

Объяснение:

Современный Excel (365/2021) умеет динамические массивы.
РАЗДЕЛИТЬ / SPLIT (или TEXTSPLIT в англ. версии) превращает строку в массив отдельных слов.

=РАЗДЕЛИТЬ(A1;" ")

→ получаем массив {слово1; слово2; … ; последнее}.

С массивом удобно работать через ИНДЕКС / INDEX:

=ИНДЕКС(…;1) → первое слово,

=ИНДЕКС(…;ДЛСТРК(…)) → последнее слово.

В итоге одной формулой можно собрать нужный результат, например:

=ИНДЕКС( X ;1 ) & " " & ИНДЕКС( X ;ДЛСТРК( X ))

где X — вложенный РАЗДЕЛИТЬ.

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

Выбранный ответ:

3. Применить функцию РАЗДЕЛИТЬ (SPLIT) для разделения строки на массив слов и использовать ИНДЕКС (INDEX) для доступа к первому и последнему элементу

Вопрос 7

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

Выбранный ответ:

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

Вопрос 8

Какой элемент диаграммы отсутствует на изображённом графике?

Выбранный ответ: Название осей

Вопрос 9

Нужно подсчитать строки, где один и тот же числовой код в столбце A встречается более двух раз. В исходной таблице всего 10 строк × 8 столбцов. Какой способ проще всего использовать?

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

1. Применение условного форматирования с использованием формулы

2. Использование функции СЧЁТЕСЛИ(МН) (SUMIFS) во вспомогательном столбце

3. Создание массивной формулы для детекции и маркировки дубликатов

4. Настройка сводной таблицы для подсчёта вхождений каждого кода

5. Запуск макроса VBA для анализа и маркировки строк

Объяснение:

Цель — быстро выяснить, в каких строках код из A встречается > 2 раз.
Самое короткое решение без сводных и макросов:

1. Во вспомогательной колонке ввести формулу

=СЧЁТЕСЛИ($A:$A; A2) // или COUNTIF($A:$A, A2)

2. Скопировать вниз → получаем количество повторов для каждой строки.

3. Далее можно применить автофильтр или секундную формулу =СЧЁТЕСЛИ(пом_столбец;">2") — и мы знаем, сколько строк удовлетворяют условию.

Сводная и Power Query тоже помогут, но это лишние шаги для крошечной таблицы.
Условное форматирование и массивные формулы лишь подсветят или усложнят процесс, а VBA — совсем избыточен.

Выбранный ответ:

2. Использование функции СЧЕТЕСЛИМН (SUMIFS) во вспомогательном столбце

Вопрос 10

В ячейке C5 нужно получить московское время, прибавив (или вычтя) часовое смещение из столбца B к местному времени из столбца A. Формула =A5+B5 даёт ошибочный результат. Как её поправить, чтобы корректно копировать вниз?

Объяснение:

В Excel время хранится как дробная часть суток:

1 час = 1/24, 30 минут = 1/48 и т.д.

Смещение в столбце B записано целым числом часов.
Чтобы прибавить часы к времени, надо преобразовать часы в долю суток, т.е. поделить на 24.

=A5 + B5/24

При копировании формула автоматически станет =A6 + B6/24, =A7 + B7/24 и т.д.

Выбранный ответ:

=A5+B5/24

Вопрос 11

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

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

1. =СУММ(A2) или =SUM(A2)

2. =СУММ(МАССИВ(A2*B2;A6)) или =SUM(ARRAY(A2*B2;A6))

3. =СУММПРОИЗВ(Количество*Средний_чек) или =SUMPRODUCT(Количество*Средний_чек)

4. =СУММ(Количество*Средний_чек) или =SUM(Количество*Средний_чек)

5. =СУММ(A*B) или =SUM(A*B)

Объяснение:

СУММПРОИЗВ / SUMPRODUCT специально создана, чтобы

1. умножить соответствующие элементы двух (или более) диапазонов;

2. сразу сложить получившиеся произведения.

С именованными диапазонами формула читается супер‑ясно и не требует Ctrl + Shift + Enter.
Вариант 4 или 5 тоже возможны, но требуют ввода как старой формулы массива; для простоты и переносимости выбирают SUMPRODUCT.

Выбранный ответ:

3. =СУММПРОИЗВ(Количество*Средний_чек) / =SUMPRODUCT(Количество*Средний_чек)

Вопрос 12

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

Объяснение:

Сводные таблицы позволяют не только агрегировать данные, но и создавать внутри себя калькулированные поля и элементы (Calculated Fields & Items).
Это уникальная для сводных функция: формулы хранятся в структуре сводной, пересчитываются автоматически при обновлении источника и не влияют на исходные данные.
Остальные опции (машинное обучение, внешние БД, мультиязычный ввод) — или относятся к Power Query / Power BI, или к общим возможностям Excel, а не к сводным конкретно.

Выбранный ответ:

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

Вопрос 13

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

Объяснение:

Слайсеры (Slicer) — это визуальные фильтры, которые одним щелчком перестраивают сводную таблицу.
Меняем значение параметра (например, «Проект», «Год», «Сценарий расходов») → сводная пересчитывает и вычисляемое поле ROI тоже обновляется.
Не нужно создавать десятки дополнительных вычислений, сценариев или макросов; всё работает «из коробки» и наглядно.

Выбранный ответ:

Использовать слайдеры (Slicer) для фильтрации данных по различным параметрам расходов и доходов

Вопрос 14

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

Утверждение 5 противоречит базовым принципам безопасности, поэтому оно явно неверно и является правильным выбором.

Выбранный ответ:

Файлы Excel, содержащие макросы, не могут быть заражены вирусами

Вопрос 15

Как правильно настроить параметры «Подбор параметра», чтобы вычислить требуемую цену за единицу (ячейка B4) и при этом получить заданную прибыль 30 000 (ячейка B5)?

Варианты ответа (кратко):

1. Изменять фиксированные затраты

2. Изменять прибыль

3. Изменять цену за единицу, задать целевую прибыль 30 000 и указать ячейку прибыли как целевую

4. Изменять количество продаж

5. Изменять переменные затраты

Объяснение:

В диалоге «Подборпараметра» («What‑If Analysis → Goal Seek»):

Именно этот порядок (Set Cell → To Value → By Changing Cell) даст нужную цену.

Выбранный ответ:

3. Установить ячейку с ценой за единицу в качестве изменяемой ячейки, установить целевую прибыль 30 000 и задать значение ячейки прибыли

Вопрос 16

Какие ограничения имеют спарклайны в Excel?

Выбранный ответ:

2. Не поддерживают включение легенды или меток данных

Вопрос 17

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

Объяснение:

Сводная таблица и построенный на ней сводный график (Pivot Chart) «живут» одной жизнью:

1. меняем срез / фильтр сводной таблицы → она моментально пересчитывается;

2. связанная диаграмма обновляется автоматически.

Такой механизм встроен в Excel, не требует макросов и работает даже после сохранения/открытия файла.
Динамические именованные диапазоны с OFFSET() реагируют на добавление/удаление строк, но не на «фильтр» скрытием строк; VBA тоже бы сработал, но требует кода и прав на макросы.

Выбранный ответ:

Создание сводной таблицы, к которой привязана диаграмма, которая автоматически обновляется при изменении фильтров сводной таблицы

Вопрос 18

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

Анализ вариантов

Выбранный ответ:

2. Сценарии не могут использовать более 32 изменяемых ячеек одновременно

Заключение

Теперь вы можете превращать сухие цифры в наглядные истории:

отчёт о продажах сразу показывает, где просели, а где выросли;
финансовый дашборд ежедневно тянет новые данные и не требует «рисовать заново»;
презентация становится понятной даже тем, кто боится Excel.
Достаточно один раз настроить связи — потом диаграммы будут обновляться автоматически. Это значит, что вместо рутины вы сможете сфокусироваться на выводах и решениях, а не на перепостроении графиков.