Коллега спросил меня, есть ли способ создать в Excel динамическую гистограмму со 100% накоплением в ячейках для трех продаж продуктов.
Как обычно, были тысячи строк данных. Она никак не могла использовать встроенные в Excel диаграммы, поскольку на создание диаграмм для каждого набора точек данных у нее ушел бы возраст.
Это заставило меня задуматься, и, к счастью, на помощь пришло условное форматирование. Мне удалось быстро создать что-то изящное, отвечающее всем требованиям.
Создание гистограммы с накоплением 100% в Excel
Предположим, у вас есть данные о продажах за 12 месяцев для трех продуктов (P1, P2 и P3). Теперь вы хотите создать в Excel столбчатую диаграмму с накоплением на 100% для каждого месяца, чтобы каждый продукт был выделен другим цветом.
Что-то вроде того, что показано ниже:
Загрузите файл примера
Как создать это:
Во-первых, вам нужно рассчитать процентное соотношение для каждого продукта за каждый месяц (Я пытался запомнить 100% диаграмму с накоплением !!).
Для этого сначала создайте три вспомогательных столбца (каждый для P1, P2 и P3) для всех 12 месяцев. Теперь просто рассчитайте процентное значение для каждого продукта. Я использовал следующую формулу:
= (C4 / СУММ ($ C4: $ E4)) * 100)
Как только у вас будут эти данные, давайте сразу же приступим к составлению диаграммы с накоплением.
- Выберите 100 столбцов и установите для них ширину столбца 0.1.
- В этом случае выберите эти 100 ячеек в первой строке данных (K4: DF4).
- Перейдите на главную -> Условное форматирование -> Новое правило.
- В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
- В поле «Редактировать описание правила» введите следующую формулу и установите для форматирования значение «Синий» (на вкладке «Заливка»).
= КОЛОННЫ ($ K $ 4: K4) <= $ G4
- Теперь снова выберите тот же набор ячеек и перейдите на главную - Условное форматирование - Управление правилами. Перейдите на вкладку «Новое правило» и снова перейдите к параметру «Использовать формулу, чтобы определить, какие ячейки нужно форматировать». Теперь введите формулу, указанную ниже, и установите форматирование на зеленый цвет.
= И (КОЛОННЫ ($ K $ 4: K4)> $ G4, КОЛОННЫ ($ K $ 4: K4) <= ($ G4 + $ H4))
- И, наконец, снова повторите тот же процесс и добавьте третье условие со следующей формулой и установите форматирование на оранжевый цвет.
= И (КОЛОННЫ ($ K $ 4: K4)> ($ G4 + $ H4), КОЛОННЫ ($ K $ 4: K4) <= 100)
- Теперь нажмите ОК, и вы получите что-то, как показано ниже:
Скройте вспомогательные столбцы, и ваша динамическая гистограмма с накоплением на 100% готова к вашим услугам.
Пришло время погреться в лучах славы и выделить время, чтобы похвастаться этим. 
Попробуйте сами… Загрузите файл примера отсюда
Если вам понравился этот урок, вам также могут понравиться следующие:
- Создание диаграммы воронки продаж в Excel.
- Создание диаграммы Ганта в Excel.
- Создание динамической диаграммы Парето в Excel.
- Пошаговая диаграмма в Excel - пошаговое руководство.
- Создание тепловой карты в Excel с использованием условного форматирования.
- Как быстро создать вафельную диаграмму в Excel.
- Спарклайны Excel - полное руководство
