Как создать динамическую временную шкалу в Excel за 10 минут

В Microsoft Excel можно преобразовать данные в различные типы графиков, однако стандартного графика временной шкалы нет. Чтобы обойти это ограничение, можно использовать базовый линейный график и создать динамическую временную шкалу всего за 10 минут. В этой статье мы рассмотрим, как это сделать.

Часть 1: Подготовка динамической таблицы данных

Нужны данные для построения временной шкалы!

Предположим, вы хотите преобразовать список мест, которые вы посетили в 2025 году, в временную шкалу. Даты в первом столбце должны быть в формате даты и отсортированы по времени.

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

Когда появится диалоговое окно, убедитесь, что установлен флажок "Моя таблица содержит заголовки", и нажмите "ОК".

Далее в ячейке C1 введите Helper и нажмите Enter, чтобы добавить третий столбец. Все графики требуют чисел по оси Y, поэтому в этом вспомогательном столбце будут находиться числа.

В первую ячейку столбца Helper (ячейка C2) введите или скопируйте следующую формулу и нажмите Enter:

=CHOOSE(MOD(ROW()-ROW(Table1[#Headers])-1,6)+1,10,-10,20,-20,30,-30)

Если вы дали вашей таблице имя, замените "Table1" в формуле на имя вашей таблицы.

Эта формула использует функции ROW и MOD для генерации повторяющейся последовательности 10, -10, 20, -20, 30 и -30. Эти чередующиеся положительные и отрицательные значения обеспечивают четкое расположение ваших данных выше и ниже центральной линии временной шкалы, предотвращая наложение текстовых меток, которые мы добавим позже.

Часть 2: Вставка и настройка графика временной шкалы

Потребуется несколько шагов

Теперь пора вставить линейный график, который вы адаптируете в график временной шкалы. Выберите столбец с датами (включая заголовок), удерживая Ctrl, выберите также столбец Helper (тоже включая заголовок). Затем на вкладке "Вставка" нажмите опцию "Линейный график" и выберите "Линия с маркерами".

Теперь вам нужно превратить маркеры в вертикальные линии. Выберите график, нажмите на "+", который появляется при наведении, и отметьте "Погрешности". Затем нажмите стрелку рядом с Погрешностями и выберите "Дополнительные параметры".

В панели Форматировать погрешности внесите три важных изменения:

1. В разделе Направление установите "Минус".
2. В разделе Конечный стиль установите "Без колпачка".
3. В разделе Количество погрешностей установите "Процент", введите 100 в текстовое поле и нажмите Enter.

Этот шаг продлевает вертикальную линию от каждого маркера до оси X, формируя вертикальные деления вашей временной шкалы.

Далее щелкните один из маркеров на графике, чтобы выбрать все, и в панели Форматировать ряд данных отметьте "Без линии".

Не забудьте настроить маркеры так, как вам нужно. Нажмите "Маркеры" в той же панели и в разделе Параметры маркеров выберите один из стилей. Вы также можете изменить цвет, расширив опцию "Заливка".

Дважды щелкните один маркер, чтобы настроить его индивидуально.

Теперь нужно исправить ось X. В моем случае я хочу, чтобы временная шкала начиналась 1 января и заканчивалась 31 декабря. Для этого щелкните по оси один раз, чтобы выбрать ее, и в области Параметры оси панели Форматировать ось установите минимальное значение на вашу дату начала и максимальное значение на дату окончания. Нажмите Enter для подтверждения.

В области Деления той же панели убедитесь, что оба типа (основной и вторичный) установлены на "Нет", а в области Метки установите положение меток на "Нет".

Теперь настройте ось X. Перейдите в раздел форматирования панели Форматировать ось, щелкнув по ведру с краской, и внесите следующие три изменения в разделе Линия:

1. Установите "Сплошная линия" и выберите цвет линии.
2. Для типа стрелки в начале выберите ромб или другую стилистическую форму.
3. Для типа стрелки в конце выберите стрелку.

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

Теперь у вас должна получиться временная шкала, похожая на эту:

Последнее, что нужно сделать, это обработать метки данных.

Часть 3: Добавление меток и финализация временной шкалы

Завершите оформление

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

Теперь выберите все маркеры, щелкнув по ним один раз, затем щелкните правой кнопкой мыши на одном из них и выберите "Добавить метки данных".

На данный момент метки данных содержат числа из вспомогательного столбца. Чтобы изменить это, щелкните по одному из этих чисел, чтобы выбрать все, и в разделе Параметры меток данных панели Форматировать метки данных выполните три действия в этом порядке:

1. Установите "Имя категории".
2. Уберите галочку с "Значение".
3. Установите "Значение из ячеек".

В результате останутся только "Значение из ячеек", "Имя категории" и "Показать линии соединения".

В диалоговом окне диапазона меток данных, которое появится после установки "Значение из ячеек", поместите курсор в текстовое поле, выберите диапазон, содержащий названия полей, исключая заголовок (например, в моем случае ячейки B2 до B21), и нажмите "ОК".

Вернитесь в панель Форматировать метки данных и в разделе Параметры меток установите разделитель как "Новая строка" — это добавит перенос строки между метками и датами.

Метки по умолчанию расположены справа от маркера, и это хорошо подходит для создаваемой вами временной шкалы.

Чтобы завершить оформление меток данных, щелкните по одной из них, чтобы выделить все, и на вкладке "Главная" нажмите "Выравнивание по левому краю".

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

Вот и все! Ваша временная шкала готова!

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

Не бойтесь экспериментировать

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