Как создать диаграмму‑термометр в Excel для отслеживания финансовой цели

Что такое диаграмма‑термометр и зачем она нужна

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

Определение в одну строку: диаграмма, визуализирующая текущую сумму как долю от целевого значения (0–100%).

Важно: диаграмма не хранит данные — она визуализирует их. Корректность зависит от формул в вашей таблице.

Подготовка таблицы: шаги и формулы

Перед созданием диаграммы решите, что вы отслеживаете (пожертвования, сбережения, продажи). В примере мы отслеживаем сбор средств на поездку команды с целевым значением $115,000.

1. Откройте новый лист Excel.
2. Создайте таблицу с колонками Month (Месяц) и Amount Deposited (Внесённая сумма).
3. Под таблицей добавьте строки для Target (Цель), Total (Итого) и Percent (Процент).

4. В ячейке рядом с Target введите целевое значение, например: $115,000.

5. В ячейке Total используйте формулу суммирования столбца с суммами. Например:

=SUM(B5:B16)

(Вместо диапазона подставьте соответствующий вашему листу.)

6. Рассчитайте процент выполнения цели:

=B20/B19

где B20 — сумма, B19 — цель.

7. Отформатируйте ячейку с процентом: правый клик → Формат ячеек → Процент → выберите количество десятичных знаков → OK.

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

Создание диаграммы‑термометра в Excel — пошагово

1. Выделите ячейку с процентом или диапазон, включающий процент.
2. Перейдите на вкладку Insert (Вставка).
3. В разделе Charts (Диаграммы) выберите Insert Column or Bar Chart → 2‑D Clustered Column (2D сгруппированная столбчатая).

4. Диаграмма появится рядом с таблицей. Нажмите правой кнопкой по диаграмме и выберите Select Data (Выбрать данные). Добавьте серию, если нужно, укажите значение процентной ячейки и нажмите OK.

5. Упростите внешний вид: удалите заголовок диаграммы, подписи осей и горизонтальные линии — правый клик → Delete или используйте форматирование.

6. Двойной клик по оси Y (процент) → Format Axis (Формат оси). Установите Minimum = 0.0 и Maximum = 1.0. В разделе Number (Число) установите десятичных знаков = 0, чтобы ось показывала 0%/100% без дробей.

7. Правая кнопка по столбцу → Format Data Series (Формат ряда данных). Установите Gap Width (Ширина промежутка) = 0 — столбец займёт всю ширину и будет напоминать термометр.

8. На вкладке Insert (Вставка) выберите Shapes (Фигуры) → Oval (Овал). Нарисуйте овал и поместите его под столбцом, чтобы он образовал «колбу» термометра. Отрегулируйте размеры, чтобы он вписался аккуратно.

9. Чтобы поменять цвет заливки столбца (например, сделать его красным), щёлкните по столбцу правой кнопкой → Format Data Series → Fill → Solid fill → выберите цвет.

Совет: если хотите избежать наложения фигур на диаграмму, отрегулируйте размеры области диаграммы и положения элементов.

Добавление деталей: даты, доноры и динамические диапазоны

Для анализа пожертвований по датам и донорам сделайте таблицу с колонками: Date (Дата), Donor (Донор) и Amount (Сумма). Это позволит агрегировать и фильтровать данные по диапазонам дат.

Преобразование диапазона в таблицу

1. Выделите весь диапазон с данными.
2. Format as Table (Форматировать как таблицу) → выберите стиль → отметьте My table has headers (У моей таблицы есть заголовки) → OK.

Таблица автоматически расширяется при добавлении строк — это упрощает работу с динамическими данными.

Динамические именованные диапазоны через Name Manager

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

1. Выделите колонку Amount в таблице (например, C26:C41).
2. Перейдите на вкладку Formulas (Формулы) → Name Manager (Диспетчер имён) → New (Создать).
3. В поле Refers to (Ссылка на) вставьте формулу:

=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C),1)

Эта формула расширяет диапазон вниз на количество непустых ячеек в столбце C.

Изображение: Name Manager с именованным диапазоном

Заметка: современный, более надёжный подход — использовать структурированные ссылки таблицы (Table1[Amount]). Таблица автоматически обновляет диапазон, и формулы выглядят понятнее:

=SUM(Table1[Amount])

Агрегация по датам с помощью SUMIFS

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

1. Введите начальную дату в ячейку G26.
2. В G27 укажите формулу для конечной даты (например, через 14 дней):

=G26+14

3. В ячейке, где хотите увидеть сумму за период, введите:

=SUMIFS($C$26:$C$95,$A$26:$A$95,">="&$G$26,$A$26:$A$95,"<="&$G$27)

Расшифровка:

• $C$26:$C$95 — диапазон сумм, которые нужно сложить.
• $A$26:$A$95, “>=”&$G$26 — проверяет даты >= стартовой.
• $A$26:$A$95, “<=”&$G$27 — проверяет даты <= конечной.

Используйте структурированные ссылки, если у вас Excel Table:

=SUMIFS(Table1[Amount], Table1[Date], ">=" & $G$26, Table1[Date], "<=" & $G$27)

Это читабельнее и надёжнее при изменении размеров таблицы.

Проверка и отладка: распространённые проблемы и решения

• Проблема: диаграмма показывает 100% независимо от суммы.

  • Проверьте, что ось Y настроена от 0 до 1, а не от 0 до 100.
  • Убедитесь, что в процентной ячейке хранится дробь (например, 0.42 для 42%), а не текст “42%”.

• Проблема: диаграмма не обновляется при добавлении строк.

  • Если используете обычные диапазоны — обновите ссылки или переключитесь на Excel Table.
  • Для именованных диапазонов проверьте формулу OFFSET/COUNTA.

• Проблема: овал скрывает столбец или не выровнен.

  • Уменьшите размер области диаграммы и измените порядок слоёв: правый клик → Bring to Front / Send to Back.

• Проблема: цвета выглядят неравномерно при экспорте в PNG/PDF.

  • Установите одинаковую заливку столбца и овала, используйте сглаживание краёв.

Альтернативные способы визуализации прогресса

1. Прогресс‑бар в ячейке при помощи условного форматирования (Data Bars) — быстрый и лёгкий.
2. Круговая диаграмма или doughnut (пончиковая диаграмма) с заливкой части кольца по значению.
3. Gauge chart (спидометр) — требует нескольких серий, сложнее в настройке, но эффектно смотрится.
4. Power BI / Google Data Studio — для интерактивных дашбордов и большого объёма данных.

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

Мини‑методология: как поддерживать диаграмму в актуальном состоянии

1. Храните исходные данные в Excel Table (Table1).
2. Ссылки диаграммы делайте на структурированные имена (Table1[Amount], Named Ranges).
3. Автоматизируйте импорт данных (Power Query) если данные приходят из внешних источников.
4. Периодически проверяйте формулы и форматирование оси.
5. Создайте лист «Readme» с описанием ячеек, откуда берутся значения.

Чеклисты по ролям

Организатор сбора средств:

• Проверить корректность целевого значения.
• Внести стартовые данные доноров и сумм.
• Запланировать период обновления (ежедневно / еженедельно).

Аналитик:

• Настроить таблицу и структурированные ссылки.
• Настроить именованные диапазоны или Power Query.
• Подготовить сводный отчёт по датам и донорам.

Презентующий (команде/партнёрам):

• Убедиться в корректном отображении процентов.
• Подготовить слайд с диаграммой и ключевыми комментариями.
• Добавить пометки о самых успешных днях и уроках.

Критерии приёмки

1. Диаграмма корректно отображает текущий процент (проверка на трёх тестовых данных: 0%, 50%, 100%).
2. При добавлении новой строки таблицы итоги автоматически обновляются.
3. Ось Y нормирована от 0 до 1 и отображает 0% и 100% корректно.
4. Внешний вид «термометра» читаем и может использоваться в презентации.

Тестовые случаи / Acceptance criteria

1. Тест: ввод суммы, равной нулю → ожидание: термометр пуст (0%).
2. Тест: ввод суммы равной половине цели → ожидание: термометр показывает ~50%.
3. Тест: ввод суммы, превышающей цель → ожидание: при необходимости диаграмма может показывать >100% или ограничиваться максимумом; проверьте поведение и документируйте.

Безопасность и конфиденциальность

Если таблица содержит персональные данные доноров (имена, адреса, суммы), соблюдайте локальные требования по защите данных. Для Европейских граждан — учитывайте GDPR: храните минимум данных, используйте анонимизацию и защищённые хранилища.

Быстрый шаблон — чеклист для создания диаграммы

• Установить цель (Target).
• Заполнить исходные данные (Date, Donor, Amount).
• Создать Excel Table.
• Рассчитать Total =SUM(Table1[Amount]).
• Рассчитать Percent =Total/Target.
• Вставить 2D Clustered Column → настроить ось Y 0–1.
• Уменьшить Gap Width до 0.
• Добавить овал внизу и настроить цвет.
• Проверить обновление при добавлении строк.

Решение «что выбрать»: схема принятия решения

mermaid
flowchart TD
  A[Нужно ли визуализировать одну метрику?] -->|Да| B[Термометр в Excel]
  A -->|Нет| C[Рассмотрите дашборд или несколько KPI]
  B --> D{Данные приходят из внешнего источника?}
  D -->|Да| E[Используйте Power Query и таблицы]
  D -->|Нет| F[Используйте Excel Table и структурированные ссылки]
  C --> G[Power BI / Google Data Studio / несколько диаграмм]

Справочная шпаргалка по функциям (1‑строчная глоссарий)

• SUM — суммирует диапазон.
• SUMIFS — суммирует с несколькими условиями (например, по датам).
• OFFSET — задаёт динамический диапазон от опорной ячейки.
• COUNTA — считает непустые ячейки в диапазоне.
• Table1[Amount] — структурированная ссылка на столбец таблицы.

Заключение

Диаграмма‑термометр в Excel — простой и наглядный инструмент для отслеживания финансовых целей. Используя структурированные таблицы, правильные формулы и минимальную настройку диаграммы, вы получите гибкий и легко обновляемый визуальный индикатор прогресса. Для больших данных и интерактивных отчётов рассмотрите перенос визуализации в Power BI или Google Data Studio.

Кратко: настройте таблицу, посчитайте Total и Percent, постройте 2D столбец, нормируйте ось, уменьшите Gap Width, добавьте овал — и ваш термометр готов.