Как найти максимум графика в экселе

Как найти максимум графика в экселе


Download Article


Download Article

This wikiHow will teach you how to show the max value in an Excel graph with a formula. First, you’ll need to create a line chart with markers with your data, then you can add a formula to find the maximum of your data set, then you can apply that max to your chart in a new color.

  1. Image titled Show the Max Value in an Excel Graph Step 1

    1

    Open your project in Excel. If you’re in Excel, you can go to File > Open or you can right-click the file in your file browser.

  2. Image titled Show the Max Value in an Excel Graph Step 2

    2

    Create a line graph with your data. You’ll need to create a line graph for this method to work. To do this, select your data, then go to Insert > Line Graph icon > Line with Markers. It’s the line graph with dots.

    Advertisement

  3. Image titled Show the Max Value in an Excel Graph Step 3

    3

    Create a new column next to your data set labeled “Max.“ Since this information will be included in the line graph, keep it close to your original data set so you can easily add it to the data range represented by the graph.

  4. Image titled Show the Max Value in an Excel Graph Step 4

    4

    Enter the following formula: =IF(B5=MAX($B$5:$B$16),B5,””). In this example, B5 represents the first cell in your range, while B16 represents the last. Replace these cell addresses with the actual cells in your data. This formula will make the highest value in your dataset repeat in this new column, but none of the other values will appear.

    • Fill the rest of the column with that formula and you’ll see the highest value in your data set repeats in that column.

  5. Image titled Show the Max Value in an Excel Graph Step 5

    5

    Add the “Max” column to your chart. Click your chart to select it, then drag the box highlighting the data it’s representing to include the extra column.[1]

    6. Advertisement

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Advertisement

Thanks for submitting a tip for review!

References

About This Article

Article SummaryX

1. Open your project in Excel.

2. Create a line graph with your data.
3. Create a new column next to your data set labeled “Max.”

4. Enter the following formula:=IF(B5=MAX($B$5:$B$16),B5,””)).

5. Add the “Max” column to your chart.

Did this summary help you?

Thanks to all authors for creating a page that has been read 35,761 times.

Is this article up to date?

Источник
history 14 января 2021 г.
    Группы статей

  • Надстройка “Поиск решения”

Пусть дана функция с несколькими переменными F(x1, x2, …)=a1*x1+a2*x2+… Также даны граничные условия в виде b1*x1+b2*x2+…<=c (несколько условий). Нужно найти экстремум функции F (минимум или максимум). Это классическая задача для Поиска решения MS EXCEL, кроме того это линейная модель. Сделаем удобную форму для таких задач и покажем как настроить Поиск решения.

Задача

Пусть дана явная функция с 4 переменными:

Также даны несколько (семь) граничных условий (англ: restrictions, constraints):

Требуется найти максимум функции F.

Создание модели

Решим задачу с помощью инструмента MS EXCEL

Поиск решения

, хотя можно ее решить и другим способом, например аналитически.

На рисунке ниже приведена модель, созданная для решения задачи (см.

файл примера

).


Переменные (выделено зеленым)

. В качестве переменных модели, очевидно, выступают x1, x2, x3, x4. Эта задача хороша тем, что переменные задаются однозначно, не требуется осмысливать житейскую задачу, например как с

оптимизацией затрат

. Хотя математически – это эквивалентные задачи, только количество переменных разное.

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


Ограничения (выделено серым)

. Ограничения модели – это ограничения на область изменения переменных. Они могут задаваться как простыми выражениями для одной переменной, например х1>=0, так и для некой комбинации переменных 5*x1+4*x2-x3-2*x4<=-3. В первом случае х1>=0 ограничения можно ввести прямо в окне Поиска решения (будет показано ниже), для более сложных зависимостей удобно подготовить вспомогательную таблицу (С26:Е29).

Составить модель, особенно первую, непросто. Может помочь такой подход: считать, что переменные (зеленые ячейки) уже содержат некие значения, пусть даже не оптимальные. Так легче составлять огграничения. В нашем случае ограниечение 5*x1+4*x2-x3-2*x4 можно записать с помощью формулы =

СУММПРОИЗВ($D$19:$D$22;C26:C29)

. В диапазоне D19:D22 содержатся коэффициенты 5; 4; -1; -2. Кроме того, если значения переменных заданы, то и значение целевой функции также автоматически рассчитано (тоже не оптимальное пока, до запуска Поиска решения).


Целевая функция (выделено красным)

.

Целевая функция – это то, что требуется оптимизировать, т.е. F. Формула для ее вычисления задана в явном виде – не нужно догадываться из условий обычной задачи как ее подсчитать. Это не всегда очевидно (см., например, статью про

пропускную способность трубопровода

).

Ниже приведено окно Поиска решения с заполненными полями: целевая функция, переменные и ограничения.

После запуска Поиска решения ответ будет вычислен за доли секунды: F=3.

Источник

Поиск наименьшего или наибольшего числа в диапазоне

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

Если ячейки находятся в соединимой строке или столбце

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

На вкладке Главная в группе Редактирование щелкните стрелку рядом с кнопкой , выберите min (вычисляет наименьшее) или Max (вычисляет наибольшее) и нажмите клавишу ВВОД.

Если ячейки не находятся в подрядной строке или столбце

Для этого используйте функции МИН, МАКС, МАЛЫЙ или БОЛЬШОЙ.

Пример

Скопируйте следующие данные на пустой лист.

Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.

Долгое время в Excel не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.

Пусть имеются данные

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

Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:

Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.

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

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

Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.

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

На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.

Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.

Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение.

Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)

В MS Excel добавили новые статистические функции — МАКСЕСЛИ и МИНЕСЛИ. Обе функции имеют возможность учитывать несколько условий и некоторое время в их названиях в конце были буквы -МН. Потом убрали, хотя в скриншотах ниже используется вариант названий с -МН.

Есть ряд значений, каждое из которых входит в некоторую группу. Нужно рассчитать максимальное значение по группе А. Используем формулу МАКСЕСЛИ.

Функция МАКСЕСЛИМН

Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.

Функция МИНЕСЛИМН

Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.

Источник
 

merks

Пользователь

Сообщений: 2
Регистрация: 20.08.2013

Здравствуйте! Из массива данных строятся два графика, которые аппроксимируются линией тренда полиномиального типа. Необходимо определить максимум у каждой из двух линий тренда, а затем разность максимумов. Проблема в том, что линии тренда не имеют под собой числовых данных. Можно ли это как-то обойти? В данный момент я делаю так – для каждой линии тренда вывожу уравнение на графике, затем копирую его в ячейку, подставляю нужную переменную в уравнение и затем строю график (который повторяет линию тренда), из которого уже и нахожу максимальное значение. Просто данный способ довольно трудоемкий, да еще с учетом того, что надо обработать несколько сотен графиков. Буду благодарен любым идеям)

Изменено: merks – 06.10.2016 11:29:49
 

MCH

Пользователь

Сообщений: 3885
Регистрация: 22.12.2012

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

Приложите таблицу с исходными данными, можно будет что-то придумать
 

merks

Пользователь

Сообщений: 2
Регистрация: 20.08.2013

Спасибо!Буду пробовать.MCH, в прикрепленном файле – исходная таблица, график с двумя функциями и линиями тренда; в строках 45 и 47 значения, вычисленные по уравнениям тренда. Из них уже считал максимальное значение и разность.

Изменено: merks – 20.08.2013 12:47:33
 

MCH

Пользователь

Сообщений: 3885
Регистрация: 22.12.2012

Пока данных не было, нарисовал свой файл
Приложено два варианта:
Вариант1 – по имеющимся значениям x, с помощью ТЕНДЕНЦИЯ находим значения аппроксимирующей функции полиномом второго порядка и определяем максимальное значение

Вариант2
Определяем коэффициенты полинома через функцию ЛИНЕЙН для уравнения
y=a*x^2+b*x+c

для нахождения экстремума, берем первую производную, и находим x для y’=0
y’=2*a*x+b
x=-b/(2*a)
подставляем найденный x в аппроксимирующую функцию и находим y через функцию ТЕНДЕНЦИЯ
 

Rateastwest

Пользователь

Сообщений: 4
Регистрация: 06.10.2016

Доброго времени суток!
Построила линию тренда, вывела формулу. Чисто визуально линия тренда очень близка к самому графику. R2=0.9854. Но когда по этой формуле считаю… цифры вообще получаются другие…Даже близко не похожи на саму линию тренда…
Прикладываю сам файл. В желтой таблице – исходные данные, в синий – то, что получается по полученной формуле, ну а в зеленой % отклонения… Аж до 30%.
Может быть я вообще принцип аппроксимации представляю себе неправильно?!!!

https://cloud.mail.ru/public/NAFk/X3fJMErhU

Изменено: Rateastwest – 06.10.2016 10:07:05
 

С.М.

Пользователь

Сообщений: 936
Регистрация: 22.12.2012
Rateastwest

,
1. У Вас неравномерное распределение значений по оси X, поэтому Вы не имеете права использовать
тип диаграммы “График”, в этом случае тип должен быть – “Точечная”. То, что у Вас на диаграмме получилось
“красиво” – это случайность (см. Диаграмма2 в моём файле).
2. В формулах расчёта новых Y-значений лучше брать коэффициенты полученные именно расчётом , например
функцией ЛИНЕЙН , а не те, которые показаны на диаграмме, или сразу применить ф. ТЕНДНЦИЯ.
3. Но показатели – отклонения Y-ков, R2 (настоящий), да и график на Диаграмме 2 – получились тоже не очень хорошие.
Для улучшения (показателей) нужно подобрать более подходящую модель уравнения аппроксимации:
возможно использовать в формулах модели не сами исходные значения, а какие то их функции.
В моём файле X-ы “заменены” на обратные значения =1/X (см. Диаграмма3 и Диаграмма4),
и, в последнем  варианте модели, значения Y прологарифмированы (=Ln(Y)),
итого, три модели:
Y = A + B*X + C*X^2
Y = A + B*1/X + C*(1/X)^2
Ln(Y) = A + B*1/X + C*(1/X)^2
      Y = Exp(Ln(Y))

Изменено: С.М. – 06.10.2016 16:51:19
 

Rateastwest

Пользователь

Сообщений: 4
Регистрация: 06.10.2016

Гм… По ходу я еще неделю буду обдумывать Ваш файл…
С.М.! Огромное спасибо!!! Теперь я знаю, куда копать!!!

Изменено: Rateastwest – 06.10.2016 17:49:10
 

Rateastwest

Пользователь

Сообщений: 4
Регистрация: 06.10.2016

Копаю вторую неделю…
С.М.! Если Вы поможете мне разобраться более конкретно, буду очень признательна…
1. В данном случае мы пытаемся получить функциональную зависимость с помощью аппроксимации методом наименьших квадратов;
2. Форма нашего многочлена выглядит следующим образом: a0+a1*x+a2*x^2+a3*x^3+a4*x^4+a5*x^5+a6*x^6… на этом и остановимся…
3.Так что на данный момент наша цель – получить значения переменных а0, а1 и т.д.
4.Я создала таблицу, где указала исходые X  и Y;
5. Потом создала графы 1/x (судя по Вашему файлу, это где-то между х-сами а0 и а1), x^2, x^3 и т.д.
6. Коллениарно им должны быть переменные а1, а2 и т.д.
7. Как я могу с помощью Экселя найти переменные а0, а1, а2 и т.д.?
8. Или алгоритм моих размышлений априори неверный?
9. Файл прикладываю
10. Если вопрос мой совсем глупый, приношу свои извинения…(((((((((((((

https://cloud.mail.ru/public/7XvZ/Mpcz1Fpsj
 

Doober

Пользователь

Сообщений: 2222
Регистрация: 09.04.2013

Возможно вы не совсем правильно назвали свой файл
В таких случаях я использую алгоритмы ALGLIB .
Используйте функцию  UDF ReCalck, кнопка считает с шагом 0,01
5% на краю диапазона это нормально,обычно край не принимается в расчет
 

см. вложение
на рис. изображены 3 графика исходный, полином 5 степени и полином 8 степени.
видно, что полином 8-й степени практически повроряет все изгибы исходного графика
известно что при наличии К пар точек значения полинома степени К-1 точно вычисляют У для любого из значений Х

Программисты – это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

 

Rateastwest

Пользователь

Сообщений: 4
Регистрация: 06.10.2016

#12

15.10.2016 14:18:00

Добер и Игорь!!!
БОЛЬШОЕ ЧЕЛОВЕЧЕСКОЕ СПАСИБО!!!
Пошла копать дальше… ))))))))))))))
Источник

Добавить комментарий