Как выделить максимальные и минимальные точки данных на диаграмме?

Динамическая диаграмма в Excel

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

Исходные данные

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

Есть некие значения, характеризующие определенный месяц. Это могут быть продажи, производство товаров, да что угодно. Необходимо на основе этих данных построить гистограмму с группировкой. Если делать как обычно, то есть выделить весь диапазон, или просто встав на ячейке с данными выбрать «Вставка» — «Гистограмма» — «Гистограмма с группировкой». Диаграмма будет вставлена, но это будет диаграмма на основе данных. Диаграммы такого типа не поддерживают первый необходимый элемент динамичности, а именно динамическое добавление колонок при внесении новых строк в таблицу.

Динамическое добавление колонок

Только диаграммы, основанные на таблицах Excel, поддерживают динамическое изменение количества строк. Для того что бы из диапазона ячеек В2:С8 сделать таблицу, так же достаточно встать на любой ячейки и выбрать меню «Вставка» — «Таблица», или нажать комбинацию клавиш Ctrl+T.

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

Перейдите к любой ячейке таблицы и создайте из нее гистограмму.

Пока что все идет как обычно. Где же динамизм? На самом деле, он уже есть. В нашем случае, если вы введете «Июль» в ячейку B9, которая не является частью таблицы, новая строка будет включена в таблицу, благодаря атрибуту таблиц. Таблица автоматически расширится. В диаграмму будет добавлен новый столбец, поскольку она связана не с диапазоном ячеек, а с таблицей. Например:

Мы еще не ввели значение для июля, но вы можете видеть, что столбец «Июль» уже пуст. Значение равно 60.

Выделение столбцов, максимальное-минимальное

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

Прежде всего, как определить максимальную или минимальную колонку? Просто визуально, чтобы столбец с максимальным значением был выделен другим цветом в данных, добавим в таблицу еще один столбец. Назовем столбец «Максимум» и запишем в нем следующую формулу:

Не забывайте об особенности авторасширяющихся таблиц. Столбец можно добавить в таблицу, просто введя значение рядом с последним столбцом. В данном случае в поле D3 введено значение «Максимум». В первой строке таблицы мы введем формулу, а нажав на всплывающее меню, растянем формулу на весь столбец (но можно растянуть формулу на весь столбец и обычным способом).

Обратите внимание, что в формуле используются специальные табличные ссылки, а не привычные вида «столбец-строка». Если табличная ссылка начинается с символа «@» — это означает текущую строку, столбец с указанным именем. Если имя столбца указанно просто в квадратных скобках – это означает ссылку на столбец целиком. Таким образом, наша формула проверяет если значение в текущей строке, столбца «Показатель» равно максимальному значению столбца «Показатель», то выводится значение текущей строки столбца «Показатель», если значение не равно, выводится «#Н/Д» — нет данных. Специальное значение «#Н/Д» используем, что бы все столбцы, где значение не максимальное отсутствовали в диаграмме. После ввода формулы наша таблица и диаграмма будут иметь вид:

Минимальное значение

Сейчас это выглядит немного странно, но это нормально. Добавив новый столбец в таблицу, мы фактически добавили новый столбец и в диаграмму, что вполне логично. У нас есть два одинаковых максимальных значения в таблице, и поэтому есть два красных столбца. Гистограммы обладают тем свойством, что значения в одной группе (одном столбце) имеют один цвет, а значения в другом столбце — другой цвет. Если вы хотите изменить цвет, лучше сделать это на данном этапе. Как изменить цвет и настроить гистограмму, выходит за рамки данной статьи, поэтому мы не будем рассматривать этот аспект.

Теперь добавьте в таблицу еще одну колонку, назовите ее «Минимум» и введите в нее формулу:

Формула такая же, как и выше, за исключением того, что вместо функции «MAX» мы используем «MIN», потому что мы проверяем минимальное значение. В результате таблица и график выглядят следующим образом.

Да, элемент «Январь» имеет еще один столбец на диаграмме. На нашей диаграмме каждый столбец прижат вплотную к своему соседу, на каждый элемент приходится по три столбца. Для элементов, чьи значения не являются ни максимальными, ни минимальными, просто нет данных, поэтому на диаграмме нет столбцов. То, что существует три колонки, можно увидеть, взглянув на минимальное значение зеленого цвета, с промежутком между синим и зеленым пиком. В то время как в максимальных элементах нет разницы между синим и красным. Это связано с тем, что для элемента «Январь» не существует «максимального» значения, т.е. «#N/D».

Параметры ряда диаграммы

А теперь самое интересное. На графике щелкните по каждому синему столбцу. Быстрее — щелкнуть правой кнопкой мыши по столбцам и выбрать в контекстном меню пункт «Формат строки данных».

В разделе «Параметры строк» первый ползунок по умолчанию помечен как «Перекрытие строк», и его значение равно 0%. Переместите его вправо до надписи «С перекрытием» или введите 100 в поле ввода. Например:

Нажмите кнопку «Закрыть», чтобы избавиться от диалогового окна. Почему закрыть, а не просто «OK», потому что изменения, сделанные в этом диалоге, немедленно отражаются на графике, и если у вас был график, видимый в этом диалоге, вы уже видели, что вместо трех столбцов появился только один. Два других просто накладываются друг на друга. Каждая последующая колонка перекрывала предыдущую, причем одна колонка выходила из синей колонки. Там, где были синий и красный цвета, следующий красный столбец перекрывал синий столбец, а поскольку зеленого не было, красный столбец был виден на диаграмме. Для наименьшего значения мы обнаруживаем, что синий должен перекрывать следующий, но следующий красный не имеет значения, пуст и невидим, но есть третий зеленый, который перекрывает первый. Таким образом, наша диаграмма приобретает законченный вид:

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

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

Создание спарклайнов

Выделить диапазон данных распределителя.

Нажмите кнопку Линия сбережений на вкладке Вставка и выберите нужный тип линии сбережений.

В диалоговом окне Вставка линий искр вы можете видеть, что первое поле уже заполнено тем, которое вы выбрали в шаге 1.

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

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

Нажмите на кнопку ОК.

При изменении данных на листе спарклайны обновляются автоматически.

Уравнение линии тренда в Excel

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

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

Линейная аппроксимация

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

Рассмотрим условное количество договоров, заключенных менеджером за период 10 месяцев:

На основе данных в таблице Excel построим точечный график (он будет иллюстрировать линейный вид):

Выберите график — «добавить линию тренда». В списке параметров выберите линейный тип. Добавить надежность аппроксимации и уравнение линии тренда в Excel очень просто (достаточно установить флажки, перечисленные на вкладке «Параметры»).

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

y = 4,503x + 6,1333

• Где 4,503 — индекс наклона;
• 6,1333 — смещение;
• Y — последовательность значений;
• X — номер периода.

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

Чтобы предсказать количество подписанных контрактов, например, в период 11, подставьте в уравнение вместо x число 11. Расчет покажет, что этот менеджер подпишет 55-56 контрактов в период 11.

Экспоненциальная линия тренда

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

Давайте построим экспоненциальную линию тренда в Excel. Например, возьмем условные значения производства электроэнергии в регионе X:

Построение графика. Добавление экспоненциальной линии.

Это уравнение имеет следующий вид:

• Где 7,6403 и -0,084 — постоянные;
• E — основание натурального логарифма.

Точность аппроксимации составляет 0,938 — кривая соответствует данным, ошибка минимальна, и предсказание будет точным.

Логарифмическая линия тренда в Excel

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

На начальном этапе задача производителя — увеличить клиентскую базу. Когда у продукта появляется свой покупатель, его необходимо удерживать и обслуживать.

Построить график и добавить логарифмическую линию тренда для прогнозирования продаж условного товара:

Наблюдается высокая степень согласия (0,9633), что указывает на минимальную ошибку аппроксимации. Давайте спрогнозируем будущие объемы продаж. Для этого подставим в уравнение номер периода вместо x.

Для расчета прогнозных показателей мы использовали следующую формулу: =272,14*LN(B18)+287,21. Где B18 — номер периода.

Полиномиальная линия тренда в Excel

Переменные на этой кривой увеличиваются и уменьшаются. Степень полинома (по количеству максимумов и минимумов) определяется его полиномом (многочленом). Например, один экстремум (минимум и максимум) — это вторая степень, два экстремума — третья степень, три экстремума — четвертая степень.

В Excel полиномиальный тренд используется для анализа большого количества данных о нестабильной величине. В качестве примера мы будем использовать первый набор значений (цены на нефть).

Чтобы получить такое значение надежности аппроксимации (0,9256), мне пришлось возвести в 6-ю степень.

Поиск минимального или максимального значения по условию

В Microsoft Excel уже давно существуют функции COUNTIF, SUMIF и AVERAGEIF и их эквиваленты, которые позволяют находить количество, сумму и среднее значение в таблице в соответствии с одним или несколькими условиями. Но что, если вы хотите найти не сумму или среднее, а минимум или максимум условия (условий)?

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

Так, условием будет название товара (бумага, карандаши, ручки), а интервалом выборки — столбец с ценами.

Если в дальнейшем вам понадобится исходный диапазон цен, вы можете преобразовать его в «умную таблицу». Для этого выделите ее и выберите Главная — Формат как таблица (Home — Format as Table) или нажмите Ctrl+T. В «умной» таблице автоматически будет присвоено имя Table1, и вы сможете ссылаться на столбцы по их именам, используя выражения типа Table1[Products] или Table1[Price]. При желании вы можете изменить имя по умолчанию Table1 на вкладке Design, которая появляется при щелчке в любой ячейке нашей «умной» таблицы. Более подробную информацию об этих таблицах можно найти здесь.

Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016

Начиная с Excel 2016, в наборе функций Microsoft Excel наконец-то появились функции, которые могут легко решить нашу проблему: функции MINIFS и MAXIFS. Синтаксис этих функций очень похож на синтаксис SUMIFS :

=МИНЕСЛИ( Диапазон_чисел ; Диапазон_проверки1 ; Условие1 ; Диапазон_проверки2 ; Условие2 . )

• Диапазон_чисел — диапазон чисел, из которого выбирается минимальное или максимальное число
• Диапазон_чек — диапазон, для которого проверяется
• Условие — критерий отбора

Просто, красиво, элегантно. Жаль, что MINESLY и MAXESLY доступны только начиная с Excel 2016. Если у вас более старая версия (или у тех, кто будет работать с файлом), возможно, вам придется возиться с ним другими способами.

Способ 2. Формула массива

В английской версии это будет =MIN(IF(Table1[Goods]=F4;Table1[Price]))

Не забудьте нажать Ctrl + Shift + Enter вместо Enter после ввода этой формулы в первую зеленую ячейку G4, чтобы ввести ее как матричную формулу. Затем формулу можно скопировать в другие продукты в ячейки G5:G6.

Давайте подробнее рассмотрим логику, лежащую в основе этой формулы. Функция ЕСЛИ проверяет каждую ячейку массива в столбце Товары на равенство текущему товару (Бумага). Если да, то отображается соответствующее значение из столбца Цена. В противном случае выводится логическое значение FALSE.

Таким образом, внешняя функция MIN (МИН) выбирает минимум не из всех значений цены, а только из тех, для которых товар был бумажным, так как MIN не учитывает ЛОЖНОСТЬ. С помощью мыши можно выделить всю функцию ЕСЛИ(.) в строке формул

. и нажмите F9 на клавиатуре, чтобы увидеть результирующий массив, из которого функция MIN затем выбирает минимальное значение:

Способ 3. Функция баз данных ДМИН

В этом варианте мы реализуем малоизвестную (и, к сожалению, недооцененную некоторыми) функцию DMIN из категории Базы данных и вносим небольшое изменение в исходную таблицу:

Как вы видите, зеленые ячейки результатов были перемещены из столбца в строку, а над ними была добавлена мини-таблица (F4:H5) с условиями. Логика работы этой функции следующая:

• Base_data — вся наша таблица, включая заголовки.
• Поле — имя столбца из заголовка таблицы, из которого выбирается минимальное значение.
• Критерий — таблица условий выбора, состоящая из (как минимум) двух ячеек: название проверяемого столбца (Товары) и критерий (Бумага, Карандаши, Ручки).

Это обычная формула (не формула массива), т.е. ее можно вводить и использовать обычным способом. К этой же категории относятся функции DSUM, DMAX и DCOUNT, которые используются точно так же, но могут находить не только минимум, но и сумму, максимум и количество значений в условии.

Способ 4. Сводная таблица

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

Поместите активную ячейку в любое место нашей интеллектуальной таблицы и выберите Вставка — Поворотная таблица. Появится следующее окно: Нажмите OK:

Чтобы создать конструктор сводной таблицы, перетащите поле «Товар» в область строк, а поле «Цена» — в область значений. Щелкните правой кнопкой мыши любое число и выберите в контекстном меню пункт Итого по — Минимум. Это заменит сумму (или количество) минимальным значением:

Как выделить максимальные и минимальные точки данных на диаграмме?

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

• Выделите точки максимальных и минимальных данных на графике с помощью вспомогательного столбца
• Выделите точки максимальных и минимальных данных на графике с помощью функции питания

Выделите максимальные и минимальные точки данных на диаграмме с помощью вспомогательного столбца

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

1. Следующая формула должна быть введена или скопирована в ячейку рядом с данными:

2. Щелкните дескриптор заполнения и перетащите его вниз к ячейкам, которые вы хотите заполнить формулой, при этом будут показаны наибольшее и наименьшее значения в столбце, см. скриншот:

Обратите внимание, что B2:B10 — это данные столбцов в приведенной выше формуле.

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

4. После того как график вставлен, щелкните правой кнопкой мыши на любом столбце графика и выберите Формат данных строки, см. скриншот :

5. В панели Формат серии данных на вкладке Параметры серии измените значение параметра Перекрытие серии на 100%, и две серии данных наложились друг на друга, создав выделенное представление максимальных и минимальных значений, см. снимок экрана :

6. Кроме того, вы можете отформатировать самые высокие и самые низкие точки данных разными цветами в соответствии с вашими предпочтениями.

Выделите максимальные и минимальные точки данных на диаграмме с помощью мощной функции

С помощью Kutools for Excel вы можете окрасить столбик диаграммы в зависимости от значений с помощью функции Color Chart by Value.

Примечание: Чтобы применить эту таблицу цветов по значению, необходимо сначала загрузить Kutools for Excel, а затем быстро и просто применить эту функцию.

После установки Kutools for Excel выполните следующие действия.

1. Сначала поместите данные в столбчатую или столбцовую диаграмму, затем выделите диаграмму и нажмите Cthulz > Charts > Chart Tools > Color Chart by Value, как показано на скриншоте:

2. В диалоге Заливка цветовой схемы в соответствии со значением в диалоговом окне выполните следующие действия:

• Выберите Largest (X) из раскрывающегося списка Data и введите число в текстовое поле X (например, если вы введете 1, будет окрашена самая большая точка данных; если вы введете 2, будут окрашены две самые большие точки данных).
• Затем выберите соответствующий цвет из раскрывающегося списка Fill Color.
• Наконец, нажмите кнопку Заполнить.

3. для выбора наименьшей точки данных выполните следующее

• Выберите наименьшие значения (X) из раскрывающегося списка Данные и укажите число в текстовом поле X; (Например, если вы введете число 1, будет окрашена наименьшая точка данных; если вы введете число 2, будут окрашены две наименьшие точки данных).
• Далее выберите нужный цвет в раскрывающемся списке Цвет заливки.
• Наконец, нажмите кнопку Заполнить.

4. После заполнения цветом определенной точки данных закройте диалоговое окно.

Как построить спарклайн в таблицах Google

Процесс построения спарклайнов в таблицах Google несколько сложнее, чем в Excel, но по функциональности идентичный. При построении диаграмм в Google Spreadsheets вам доступен тип «Спарклайн». Но это не то, что нам надо. На самом деле это всего лишь тип диаграммы, который позволяет добавить много графиков, но разместить их в одной конкретной ячейке нельзя.

Вы можете построить эквивалент спарклинов, описанных в этой статье, с помощью функции SPARKLINE в таблицах Google. Синтаксис функции SPARKLINE:

• данные — диапазон или массив, содержащий данные для построения спарклайна;
• опции — диапазон или массив, содержащий данные о различных параметрах спарклайна.

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