Выделите выше, и ниже среднего в Excel Line Chart
Чтобы визуально увидеть, когда линия на графике находится выше или ниже среднего значения ряда, мы можем просто вставить среднюю линию на график. Это будет выглядеть примерно так.
Это привлекательно? Или этот?
Я могу угадать твой ответ.
Итак, как же сделать эту диаграмму, которая выделяет область, когда линия выше или ниже среднего? Давайте изучим это шаг за шагом.
Выделите область, когда линия выше или ниже среднего. Лучший способ научиться — это учиться на примере. Итак, начнем с примера.
Здесь у меня есть некоторые данные, которые говорят об общих продажах отдельных продавцов. Я хочу визуализировать эти данные в виде диаграммы.
Шаг 1: Добавьте три вспомогательных столбца
Для создания такой диаграммы нам понадобятся три вспомогательных столбца. Первые вспомогательные столбцы будут иметь средние данные. Во втором столбце помощника будут все точки, в которых продажи выше среднего. А в третьем столбце будут точки, где продажи ниже среднего.
Таким образом, формула в столбце средних значений будет выглядеть следующим образом:
|
=AVERAGE($B$2:$B$7) |
Запишите эту формулу в первую ячейку среднего столбца и перетащите ее вниз. Убедитесь, что все ячейки имеют одинаковые значения. Мы использовали link: / excel-range-name-what-is-an-absolute-reference-in-excel [absolute referencing], чтобы ссылки не менялись при копировании формулы.
В следующем столбце нам нужны все значения UP. Это будет положительная разница между средними данными и данными о продажах. Другими словами, нам нужно вычесть средние данные из продаж, только если продажи больше среднего.
Формула в D2 будет:
|
=IF(B2>C2,B2-C2,»») |
Перетащите его вниз.
В следующем столбце нам нужны все значения Down. Это будет отрицательная разница между средним значением и данными о продажах. Другими словами, нам нужно вычесть средние данные из продаж, только если продажи меньше среднего.
Формула для E2 будет:
|
=IF(B2<C2,B2-C2,»») |
Перетащите его вниз.
Шаг 2: выберите «Консультант по продажам» и «Продажи» и вставьте линейную диаграмму: в нашем примере мы выбираем диапазон A1: C7. Перейдите в раздел Вставка → ссылка: / tips-best-charts-in-excel-and-how-to-use-them [Графики] → Линия и область → Линия с маркерами. У нас есть диаграмма, которая будет выглядеть так.
Шаг 3: Добавьте средний ряд еще раз
Щелкните диаграмму правой кнопкой мыши и выберите параметр «Выбрать данные». Здесь нажмите кнопку «Добавить» в разделе «Легенда и записи». Выберите средний диапазон C2: C7 в качестве значений ряда. Он вставит линию в исходную среднюю линию. Он нам понадобится при форматировании. Это важно.
Шаг 4: Добавьте к данным ряды вверх и вниз
Когда вы снова добавляете среднюю серию, аналогичным образом добавляйте к ней серии Up и Down одну за другой. Наконец, у вас будет диаграмма, которая выглядит так. это совсем не похоже на то, что мы хотим, верно? Подождите, оно будет развиваться.
Шаг 5. Измените второй средний ряд, ряды вверх и вниз на диаграмму с накоплением с областями. Щелкните правой кнопкой мыши любой ряд на диаграмме и выберите параметр «Изменить тип диаграммы ряда данных». Это сильно изменит диаграмму, и она начнет принимать форму, которой мы хотим, чтобы эта линейная диаграмма Excel была.
Шаг 6: Выберите «Нет заливки» для диаграммы с накоплением средней площади:
Щелкните правой кнопкой мыши область средней детали и выберите ряд данных формата. В соответствии с параметром заполнения перейдите к заполнению и выберите параметр без заполнения.
Теперь диаграмма выглядит именно так, как мы хотели. Нам просто нужно немного отформатировать, чтобы оно соответствовало нашей теме панели инструментов / презентации.
Шаг 7: Отформатируйте диаграмму в соответствии с темой.
Удалите из таблицы все менее необходимые вещи. Я удалил с графика среднюю легенду, так как она мне не нужна. Я удалил линии сетки, потому что хочу, чтобы моя диаграмма выглядела чище. С тех пор я изменил цвет верхней области на зеленый, а нижнюю — на красный. Я назвал диаграмму «Продажи против среднего».
Наконец, у меня есть эта диаграмма.
Примечание. * Эту диаграмму можно использовать как диаграмму «цель против достижения». Просто измените значения среднего ряда на цели и бум, у вас будет диаграмма творческой цели и достижений в Excel.
Загрузите файл шаблона ниже:
| Это еще одна важная функция приборной панели. Это поможет вам суммировать значения для конкретных условий.