Содержание
В отпуск? Бегать марафон? Строим этот дом мечты? Следите за своей финансовой целью
с таблицей термометров Excel. Это простой и эффективный способ отслеживать одну переменную, которой вы можете поделиться со своей командой
, партнер, друзья или просто держись. Пусть ваш собственный прогресс вдохновит вас на большее!
Мы будем строить в Excel 2013, но вы сможете без проблем следить за предыдущими версиями. В этом руководстве объясняется, как составить базовую диаграмму термометра с указанием общего процента и общей суммы поставленной цели. Затем мы рассмотрим, как использовать функции SUM и SUMIFS.
отслеживать наш прогресс в течение определенного периода.
Настройка электронной таблицы
Прежде чем мы построим наш термометр, мы должны установить нашу цель. В этом случае я собираю средства из виртуального офиса MakeUseOf для нашей долгожданной поездки по созданию команды в Андорру, но вы можете использовать свои собственные финансовые цели и маркеры.
Откройте Excel 2013 и откройте новый рабочий лист. В этой части руководства мы будем использовать только два столбца: один для месяцев и один для суммы депозита. Моя выглядит так:
Вы заметите целевые, общие и процентные ячейки под нашей таблицей. Здесь мы создадим формулы для нашего термометра.
В ячейку B20 вам нужно будет написать = Сумма (В5: В16), используя эквивалент для вашей позиции в таблице. Эта формула дает нам сумму для чисел в столбце B. После того, как вы установили текущую сумму, мы можем вычислить ваш прогресс в процентах, используя другую очень простую формулу.
В ячейке B21 я наберу = B20 / B19 и нажмите ввод. Это даст вам десятичную сумму. Мы можем легко изменить его на желаемый формат — в процентах — щелкнув правой кнопкой мыши по соответствующей ячейке и выбрав Формат ячеек. Это должно немедленно открыть контекстное окно Формат ячеек на вкладке чисел. Изменить категорию на процент, а также нажмите ОК. Ваша рабочая таблица должна выглядеть примерно так:
Настройка термометра
Теперь мы позаботились о таблице, итогах и процентах, и настроили наши формулы
Мы можем сосредоточиться на термометре.
Голова к Вставить> Столбец> 2D-столбец> Кластерный столбец. Это откроет пустой график рядом с нашей таблицей. Добавить данные на график
с помощью Выберите данные. Выберите ячейку, содержащую процент от вашего общего количества. Для меня это ячейка B21. Нажмите OK, чтобы заполнить график, и вы попадете на этот экран:
Теперь мы можем убрать график обратно. Щелкните правой кнопкой мыши заголовок диаграммы и удалите. Сделайте то же самое для заголовка столбца и горизонтальных линий. Дважды щелкните по оси Y (проценты), чтобы открыть диалоговое окно. Отсюда вы можете изменить минимальные и максимальные границы графика на 0.0 и 1.0 соответственно. Пока вы здесь, прокрутите вниз и выберите чисел. Измените десятичные разряды на 0.
Щелкните правой кнопкой мыши столбец и выберите Формат данных серии. Отрегулируйте ширину промежутка до 0. Это обеспечит заполнение вашего столбца области диаграммы, вместо того, чтобы пытаться спрятаться в углу. Теперь вы можете уменьшить график до размеров, похожих на термометр. Наконец, возвращайтесь к Вставить выберите вкладку формы, и найти хороший овал. Нарисуйте овал и добавьте его в нижнюю часть диаграммы термометра, затем измените размер области диаграммы. Он должен хорошо подходить к колоколу термометра, вот так:
Мы сделали это! Вы можете изменить свой термометр на красный, щелкнув правой кнопкой мыши и изменив цвет заливки.
Расширяя свой термометр
Если вы отслеживаете большую сумму денег в течение длительного периода времени, может быть полезно посмотреть, в какие дни вы собрали больше всего денег. Это может быть особенно полезно для благотворительных акций — вы можете проанализировать, что ваша команда сделала по-другому в тех случаях, и связать это с вашим следующим мероприятием по сбору средств!
Во-первых, мы собираемся изменить нашу таблицу данных
, Нам нужна более подробная таблица, включая даты. Мы также включим имена, потому что отслеживать наших дарителей — это весело. Я не буду вдаваться в подробности преобразования из маленького стола в большой, но в итоге вы должны получить таблицу, похожую на таблицу ниже. Я покажу вам, как получить этот аккуратный формат таблицы в ближайшее время.
Помните: вам нужны отдельные столбцы для даты и полученной суммы. Таким образом, мы можем отслеживать каждую переменную. Нам также нужно установить динамический именованный диапазон. Именованные диапазоны удобны тем, что дают нам силу набора ячеек без необходимости постоянно обновлять наши формулы
, Мы можем автоматически запросить нашу формулу для учета любых дополнений в нашей таблице.
Динамический именованный диапазон
Чтобы потом нам было легче, я превратил наш основной стол в официальный. Сделайте это, выбрав всю область вашего стола. Выберите Вставить вкладка, и вы должны сразу увидеть таблицу. Выберите ОК. Вы создали таблицу для поиска с заголовками. Заголовки пригодятся позже!
Помните наши целевые, итоговые и процентные ячейки из предыдущих? Теперь мы можем связать нашу таблицу с нашей общей. В вашей общей ячейке введите = СУММ (Таблица1 [Сумма]). Эта формула просит ячейку суммировать столбец Amount. Информация о процентах все еще может быть найдена путем деления общего количества на цель, и она по-прежнему связана с нашим термометром.
Выберите содержимое вашего столбца Сумма. Для меня это С26:С38. Выберите Формулы вкладка и найдите Имя менеджера. Нажмите новый. Обратите внимание на ссылку. Если вы выбрали столбец Сумма, = Таблица1 [Сумма], должен появиться. Нам нужно добавить к этой формуле:
OFFSET (Лист1 $ C $ 1,0,0, COUNTA (Лист1 $ C:! $ C), 1)
Каждый раз, когда вы добавляете значение в столбец Сумма, ваш итог будет автоматически увеличиваться.
Ваша формула должна быть очень похожа на это:
= Table1 [Amount], OFFSET (Лист1 $ C $ 1,0,0, COUNTA (Лист1 $ C:! $ C), 1)
Возможно, вам придется изменить букву столбца; вот оно С.
Добавление дат с помощью SUMIFS
SUMIFS — это мощная формула, которая позволяет сопоставлять информацию из двух или более источников. Мы собираемся использовать SUMIFS, чтобы узнать, сколько пожертвований мы взяли в течение 14 дней, между двумя указанными датами. Вот как будет выглядеть конечный продукт:
Введите необходимую дату начала. Для меня это ячейка B10. В ячейке B11 введите = В10 + 14. Excel автоматически вставит для вас дату и будет обновлять ее на основе ячейки B10. Отрегулируйте период времени, изменив +14 на любое нужное вам число.
Ячейка B12 будет содержать нашу формулу SUMIFS. В ячейке введите:
= СУММЕСЛИМН ($ C $ 26: $ C $ 95, $ A $ 26: $ A $ 95,»> =» $ B $ 10, $ A $ 26: $ A $ 95,»<=”&$B$11)
Ого — это много цифр, что они делают? Я покажу тебе!
- $ C $ 26: $ C $ 95: Диапазон ячеек, которые мы хотим включить. Я попросил включить ячейку C95 на случай, если наши пожертвования простираются так далеко.
- $ A $ 26: $ A $ 95,»> =» $ B $ 10: Сообщает SUMIFS, чтобы проверить столбец A на любые даты в или после
- $ A $ 26: $ A $ 95,»<=”&$B$11: Говорит SUMIFS, чтобы проверить столбец A для любых дат или раньше
В ячейке B12 теперь должна отображаться стоимость пожертвований, полученных между указанными вами датами.
Округлять
Мы добились некоторых хороших вещей сегодня. Вы использовали основные формулы, отредактировали область и ось диаграммы, создали таблицы, узнали о динамических именованных диапазонах и почувствовали вкус удивительной силы SUMIFS.
Хотя, как часто говорит мой друг, SUMIFS — просто SUMPRODUCT для бедных; но это еще одна история Excel, для другого дня.
Отслеживание вашей благотворительной цели? Надеюсь, это помогло? Какую формулу или функцию Excel вы бы хотели, чтобы MakeUseOf объяснил для вас? Дайте нам знать ниже!
Кредиты изображений: термометр через Shutterstock
