Как сделать шаблон термометра в Excel

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

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

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

Настройка электронной таблицы

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

1. Откройте новый лист в Excel.

2. Создайте таблицу Excel с двумя столбцами: один для месяца и один для суммы депозита .

3. Под таблицей обратите внимание на ячейки: Цель , Итог и Процент . Здесь мы создадим формулы для термометров.

4. Затем перейдите к «Цель» и введите целевую сумму в столбце B. Цель здесь — 115 000 долларов США.

5. Рядом с Total напишите формулу:

=Sum(B5:B16)

Эта формула отображает собранное количество в столбце B.

6. Наконец, мы можем рассчитать процент по формуле:

=B20/B19

Введите его в столбец B21.

7. Измените формат на проценты, щелкнув ячейку правой кнопкой мыши и выбрав «Формат ячеек» . Измените каталог на «Процент» , выберите количество десятичных знаков, которое вы хотите отобразить, и нажмите «ОК» .

Создайте тепловую диаграмму в Excel

1. Откройте вкладку «Вставка» .

2. В разделе диаграмм нажмите «Вставить столбец или гистограмму» и выберите «Двумерный кластеризованный столбец» .

Это действие создаст диаграмму рядом с вашей таблицей.

3. Затем добавьте таблицу в диаграмму, используя «Выбрать данные» . Выберите ячейку, содержащую процент от общей суммы. Нажмите ОК , чтобы заполнить диаграмму.

4. Теперь можно разложить диаграмму. Щелкните правой кнопкой мыши по названию диаграммы и удалите ее. Аналогично поступайте с заголовками столбцов и горизонтальными линиями.

Примечание. Если вы не хотите их удалять, вы можете скрыть оси диаграммы в Excel.

5. Дважды щелкните ось Y, чтобы открыть диалоговое окно. Отсюда вы можете изменить минимальный и максимальный пределы диаграммы на 0,0 и 1,0 соответственно. Прежде чем закрыть меню, выберите «Числа» и измените десятичный знак на 0.

6. Щелкните столбец правой кнопкой мыши и выберите «Форматировать ряд данных» . Отрегулируйте ширину зазора на 0 .

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

7. Наконец, вернитесь на вкладку «Вставка» , выберите «Фигуры» и найдите красивую овальную форму. Нарисуйте овал и добавьте его внизу термограммы, затем измените размер области графика. Он плотно прилегает к закругленному концу термометра, например:

Примечание. Вы можете изменить цвет термометра на красный, щелкнув правой кнопкой мыши по цветной части и изменив цвет заливки.

Добавление деталей в тепловую диаграмму в Excel

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

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

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

Установить динамический именованный диапазон

Чтобы упростить задачу в дальнейшем, сделайте доску официальной. Сделайте это, выделив всю площадь стола. Выберите «Форматировать как таблицу» , затем выберите нужный стиль. Установите флажок рядом с «Моя таблица имеет заголовки» и нажмите «ОК» .

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

1. В поле «Итого» введите:

=SUM(Table1[Amount])

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

2. Выберите содержимое столбца Сумма (C26:C41) .

3. Выберите вкладку «Формулы» и найдите «Диспетчер имен» . Нажмите Создать .

4. В поле «Относится к » вы увидите =Table1[Amount] . Вам необходимо добавить формулу ниже, чтобы каждый раз, когда вы добавляете значение в столбец «Сумма» , ваша общая сумма автоматически увеличивалась.

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

Формула будет выглядеть следующим образом:

Добавьте даты, используя SUMIFS

SUMIFS — это мощная формула, позволяющая сравнивать информацию из двух или более источников. Используйте функцию СУММЕСЛИМ, чтобы подсчитать количество пожертвований за 14 дней. Результат будет выглядеть следующим образом:

1. Введите дату начала (ячейка G26).

2. В ячейке G27 введите:

=G26+14

Excel автоматически вставит дату и продолжит ее обновлять на основе ячейки G26.

3. G28 будет содержать формулу SUMIFS. В этом поле введите:

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

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

Готово!

Выше описано, как нарисовать диаграмму термометра в Excel . Надеюсь, статья окажется для вас полезной.