Как да намерите минималната стойност по условие. Изчислете минимална, максимална и средна стойност в Microsoft Excel Изчислете минимална стойност в excel

Ако трябва да работите с големи таблици, определено ще намерите дублиращи се суми в тях, разпръснати по цяла колона. В същото време може да се наложи да изберете данни от таблицата с първата най-малка числова стойност, която има свои собствени дубликати. Нуждаем се от автоматичен избор на данни по условие. В Excel можете успешно да използвате формула за масив за тази цел.

Как да направите селекция в Excel по условие

За да се определи съответната стойност, първото най-малко число се нуждае от избор от таблицата по условие. Да кажем, че искаме да знаем първия най-евтин продукт на пазара от тази ценова листа:

Автоматичният избор се осъществява чрез формула, която ще има следната структура:

INDEX (диапазон_данни за_извличане; MIN (IF (диапазон = MIN (диапазон); ROW (диапазон) -ROW (заглавка на колона); ””)))

На мястото "data_range_for_fetch" трябва да посочите диапазона от стойности A6: A18 за избор от таблица (например текст), от която функцията INDEX ще избере една получена стойност. Диапазонът се отнася до диапазона от числови клетки, от които да изберете първото най-ниско число. В аргумента column_heading за втората функция ROW трябва да посочите препратка към клетка със заглавие на колона, което съдържа диапазон от числови стойности.

Естествено, тази формула трябва да се изпълнява в масив. Следователно, за да потвърдите въвеждането му, трябва да натиснете не само клавиша Enter, а цялата клавишна комбинация CTRL + SHIFT + Enter. Ако всичко е направено правилно, къдравите скоби ще се появят в лентата с формули.

Обърнете внимание на фигурата по-долу, където тази формула е въведена в масив в клетка B3:

Извадка на съответната стойност с първото най-малко число:


С тази формула успяхме да изберем минималната стойност спрямо числата. След това ще анализираме принципа на формулата и стъпка по стъпка ще анализираме целия ред на всички изчисления.



Как работи условното извличане

Функцията INDEX играе ключова роля тук. Номиналната му задача е да избере от изходната таблица (посочена в първия аргумент - A6: A18) стойности, съответстващи на определени числа. INDEX работи, като се вземат предвид критериите, определени във втория (номер на ред в таблицата) и третия (номер на колоната в таблицата) аргументи. Тъй като нашата оригинална таблица A6: A18 има само 1 колона, ние не указваме третия аргумент във функцията INDEX.

Няколко изчислителни функции се използват за изчисляване на номера на реда в таблицата спрямо най-малкото число в съседния диапазон B6: B18 и го използват като стойност за втория аргумент.

Функцията IF ви позволява да изберете стойност от списък условно. Първият му аргумент указва къде всяка клетка в диапазона B6: B18 се проверява за най-малката числова стойност: IF B6: B18 = MIN B6: B18. По този начин в паметта на програмата се създава масив от логически стойности TRUE и FALSE. В нашия случай 3 елемента от масива ще съдържат стойността TRUE, тъй като минималната стойност от 8 съдържа още 2 дубликата в колона B6: B18.

Следващата стъпка е да се определи в кои редове от диапазона се намира всяка минимална стойност. Имаме нужда от това поради дефиницията на първата най-малка стойност. Тази задача се изпълнява с помощта на функцията LINE, тя запълва елементите на масива в паметта на програмата с номерата на редовете на листа. Но първо от всички тези числа се изважда числото срещу първия ред на таблицата - B5, тоест числото 5. Това се прави, защото функцията INDEX работи с числа вътре в таблицата, а не с числа в Работен лист на Excel. В същото време функцията LINE може да връща само номерата на редовете на лист. За да не получите изместване, е необходимо да сравните реда на номерата на редовете на листа и таблицата, като използвате изваждането на разликата. Например, ако таблицата е на 5-ия ред на листа, тогава всеки ред от таблицата ще бъде с 5 по-малък от съответния ред на листа.

След като бъдат избрани всички минимални стойности и се сравнят всички номера на редовете в таблицата, функцията MIN ще избере най-малкия номер на ред. Същият този ред ще съдържа първото най-малко число, което се среща в колона B6: B18. Въз основа на този номер на ред, функцията INDEX ще избере подходящата стойност от таблица A6: A18. В резултат на това формулата връща тази стойност в клетка B3 като резултат от изчислението.

Как да изберете стойността с най-голямо число в Excel

След като разбрахте принципа на формулата, сега можете лесно да я модифицирате и персонализирате за други условия. Например, формулата може да бъде променена, за да изберете първата максимална стойност в Excel:


Ако трябва да промените условията на формулата, така че да можете да изберете първия максимум в Excel, но по-малко от 70:

!}

Как да изберете първата минимална стойност, различна от нула в Excel:


Лесно е да се види, че тези формули се различават една от друга само по функциите MIN и MAX и техните аргументи.

Сега нищо не ви ограничава. След като разберете принципите на работа на формулите в масив, можете лесно да ги модифицирате за много условия и бързо да решите много изчислителни проблеми.

Намиране на максимума / минимална стойност- проста задача, но става малко по-сложна, ако MAX / MIN се намира не сред всички стойности на диапазона, а само сред тези, които отговарят на определено условие.

Да предположим, че имате таблица с две колони: текст и число.

За удобство на разбирането на формулите, нека създадем две за всяка от колоните: Текст (А 6: А 30 ) и Числа (B6: B30 ). (вижте примерния файл).

Нека разгледаме няколко задачи:

А.Намерете максималната стойност сред онези числа, които отговарят на стойността Текст1(въвеждаме критерия в клетката E6 ).
Тези. ще търсим максималната стойност не сред всички стойности на колоната Числови стойности, но само сред тези, в които в същия ред в колоната А текстовата стойност е Текст1... Ще пишем (не забравяйте да натиснете CTRL + SHIFT + ENTER):
= ГОЛЯМ (АКО (A6: A30 = E6, B6: B30, ""), 1)

или с Именувани диапазони:

=ГОЛЯМ (АКО (Текст = E6; Числа; ""); 1)

Част от формулата Text = E6, ще върне (ВЯРНО: НЕПРАВНО: НЕПРАВНО: НЕПРАВНО: ВЯРНО: НЕПРАВНО: ЛОШНО: НЕПРАВНО: ВЯРНО: НЕПРАВНО: НЕПРАВНО: НЕПРАВНО: ВЯРНО: НЕПРАВНО: НЕПРАВНО: НЕПРАВНО: ВЯРНО: НЕПРАВНО: ЛОШНО: ЛОЖНО FALSE: FALSE: FALSE: FALSE) (за да видите резултата, маркирайте тази част от формулата и натиснете клавиша). TRUE съответства на редове, които имат Текстови стойностисъдържа смисъла Текст1.

Част от формулата АКО (Текст = E6; Числа; ""), ще върне (10: "": "": "": - 66: "": "": "": -37: "": "": "": - 5: "": "": "" : 4: "": "": "": 8: "": "": "": ""), където TRUE се заменя със стойност от цифрова колона, а FALSE се заменя със стойност. Вместо "" може да се използва всеки текстов символ (буква) или да се пропусне напълно (в този случай масивът ще изглежда така (10: FALSE: FALSE: FALSE: -66: FALSE: FALSE: FALSE: -37: FALSE: FALSE: FALSE) : -5: FALSE: FALSE: FALSE: 4: FALSE: FALSE: FALSE: 8: FALSE: FALSE: FALSE: FALSE)).

Функцията LARGE () с втория параметър = 1 се използва вместо функцията MAX (), т.к ако нито един ред не отговаря на критерия, тогава формулата = МАКС (("": "": "": "": "": "": "": "": "": "": "": "": "": "": "": "" : "": "": "": "": "": "": "": "": ""))ще върне 0 !, което може да бъде объркващо. Функцията LARGE () ще върне грешката #NUM! в този случай.

Б.Ще намерим максималната стойност само сред числата, принадлежащи към определен диапазон от стойности, например от 5 до 50. В клетките могат да се въвеждат граници аз 14 и J14 ... Решението е:
=ГОЛЯМ (АКО ((Числа> = I14) * (Числа<=J14);Числа);1)

V.Намерете с формули за масивиминималната стойност сред тези, които отговарят на стойността Текст3:
=МИН (АКО ((Текст = E7); Числа; ""); 1)

Тези. ако колоната А стойност = Текст3, тогава се взема предвид стойността в колоната Б ако стойността<> Текст3, тогава се взема предвид максималната стойност + 1, т.е. очевидно НЕ минимално. Освен това функцията MIN () връща минималната стойност от получения масив и е ясно, че нито една от стойностите, където<> текст3,няма да изкриви резултата (виж проблем А).

Друго решение, използващо формулата DMIN (), което не е така формула на масива.
= DMIN (A5: B30; B5; I8: I9)

Г.Нека намерим минималната стойност сред тези, които са по-големи:
= DMIN (A5: B30; B5; I10: I11)
къде в диапазона I10: I11 съдържа критерий = B6> СРЕДНО (Числа)

Д.Нека намерим максималната стойност по модул. От снимката по-горе можете да видите, че е -99. За да направите това, използвайте формула на масива:

IF (MAX (ABS (числа)) = MAX (числа); MAX (числа); - MAX (ABS (числа)))

Е.Намерете минималното положително число:

=SMALL (Числа; COUNTIF (Числа; "<=0")+1) - обичайната формула!

=МАЛЪК (АКО (Числа> 0; Числа); 1) - формула на масива.

СЪВЕТ:

Всички горепосочени задачи могат да бъдат решени без използване на функцията DMIN (). За да направите това, ще трябва да създадете допълнителна колона, която ще показва само тези стойности, които отговарят на критериите. След това, сред избраните стойности с помощта на функциите MAX () или MIN (), определете съответно максималната или минималната стойност (вижте примерния файл Sheet без формули за масив).

Множество условия

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

В този случай ще трябва да напишете по-сложен формула на масива:

=МАЛЪК (АКО (($ A $ 6: $ A $ 16 = E6) * ($ B $ 6: $ B $ 16 = F6); $ C $ 6: $ C $ 16; ""); 1)

Примерният файл е конфигуриран за яснота. Освен това се използва за избор на критерии (вижте жълти клетки).

По същия начин можете да персонализирате формули, за да намерите минималната стойност за редове, които попадат в конкретен диапазон.

Excel има функция за определяне на минималната стойност. Въпреки това, намирането на минималната стойност по условие е проблематично. Функция от добавката може да се справи с тази задача. = МИНСЕСЛИ

(подобно на стандартната функция на Excel SUMIF).

Във версия на Excel 2016 а отгоре има вградена функция МИНСЛИ, можете да го използвате. Ако вашият Excel е по-ранна версия, тогава тази функция може да се използва чрез инсталиране на добавката VBA-Excel.

Функцията има следните аргументи = МИНИ ( RANGE; CRITERION; [RANGE_SEARCH])

    ОБХВАТ- Обхватът на клетките, които трябва да бъдат проверени.

  • КРИТЕРИЙ- Условие във формат на число, израз или текст, което определя дали минималната стойност е проверена.
  • [RANGE_SEARCH]- Действителен диапазон за определяне на минималната стойност. Ако този параметър не е посочен, ще се използват клетките, посочени от параметъра. ОБХВАТ.

Пример 1

Като критерий могат да бъдат посочени стойности и логически изрази:

  1. Помислете за следния пример, в който се определя минималният резултат за литературата. За да направите това, в параметъра КРИТЕРИЙе посочена стойността "Литература", а параметърът ОБХВАТ- списък с артикули.
  2. Ако посочите логически израз като критерий «<>Руски", тогава ще се определи минималната оценка по всички предмети с изключение на руския език.

Пример 2

В следващия пример параметърът RANGE_SEARCHне е посочено, следователно минималната стойност се определя между клетките, посочени в параметъра ОБХВАТ.

Този процесор за електронни таблици може да се справи с почти всички изчисления. Идеален е за счетоводство. Има специални инструменти за изчисления - формули. Те могат да бъдат приложени към диапазон или към отделни клетки. За да разберете минималния или максималния брой в група клетки, не е необходимо сами да ги търсите. По-добре е да използвате предоставените опции за това. Също така ще бъде полезно да разберете как да изчислите средната стойност в Excel.

Това е особено вярно в таблици с голямо количество данни. Ако в колона например са посочени цените за продуктите на търговски център. И трябва да разберете кой продукт е най-евтиният. Ако го търсите "ръчно", това ще отнеме много време. Но в Excel това може да се направи само с няколко щраквания. Помощната програма изчислява и средноаритметичната стойност. В крайна сметка това са две прости операции: събиране и деление.

Максимум и минимум

Ето как да намерите максималната стойност в Excel:

  1. Поставете курсора на клетката навсякъде.
  2. Отидете в менюто Формули.
  3. Щракнете върху Вмъкване на функция.
  4. Изберете "MAX" от списъка. Или напишете тази дума в полето за търсене и щракнете върху Намери.
  5. В прозореца "Аргументи" въведете адресите на диапазона, чиято максимална стойност искате да знаете. В Excel имената на клетките се състоят от буква и цифра („B1“, „F15“, „W34“). И името на диапазона е първата и последната клетка, която включва.
  6. Вместо адрес можете да напишете няколко числа. Тогава системата ще покаже повечето от тях.
  7. Щракнете върху OK. Резултатът ще се появи в клетката, където е бил курсорът.

Следващата стъпка е да посочите диапазона от стойности

Сега ще бъде по-лесно да разберете как да намерите минималната стойност в Excel. Алгоритъмът на действията е напълно идентичен. Просто изберете MIN вместо MAX.

Средното

Средноаритметичната стойност се изчислява по следния начин: добавете всички числа от множеството и разделете на техния брой. В Excel можете да изчислите сумите, да разберете колко клетки са в един ред и т.н. Но това е твърде сложно и отнема много време. Ще трябва да използвате много различни функции. Имайте предвид информацията. Или дори запишете нещо на лист хартия. Но алгоритъмът може да бъде опростен.

Ето как да намерите средната стойност в Excel:

  1. Поставете курсора на клетката навсякъде в таблицата.
  2. Отидете в раздела Формули.
  3. Кликнете върху „Вмъкване на функция“.
  4. Изберете "СРЕДНО".
  5. Ако този елемент не е в списъка, отворете го с опцията „Намери“.
  6. В областта Number1 въведете адреса на диапазона. Или напишете няколко числа в различни полета "Number2", "Number3".
  7. Щракнете върху OK. Желаната стойност се появява в клетката.

Така че можете да извършвате изчисления не само с позиции в таблицата, но и с произволни набори. Excel по същество е усъвършенстван калкулатор.

други методи

Максимумът, минимумът и средната стойност могат да бъдат намерени по други начини.

  1. Намерете функционалната лента с надпис "Fx". Той е над основната работна зона на масата.
  2. Поставете курсора във всяка клетка.
  3. Въведете аргумент в полето "Fx". Започва със знак за равенство. След това идва формулата и адресът на диапазона / клетката.
  4. Трябва да получите нещо като “= МАКС (B8: B11)” (максимум), “= MIN (F7: V11)” (минимум), “= СРЕДНО (D14: W15)” (средно).
  5. Кликнете върху отметката до полето за функция. Или просто натиснете Enter. Желаната стойност се появява в избраната клетка.
  6. Формулата може да бъде копирана директно в самата клетка. Ефектът ще бъде същият.

Инструментът за автоматични функции на Excel ще ви помогне да намерите и изчислите.

  1. Поставете курсора в клетка.
  2. Намерете бутона, чието име започва с "Auto". Зависи от опцията по подразбиране, избрана в Excel („AutoSum“, „Autonumber“, „Autoshift“, „Autoindex“).
  3. Кликнете върху черната стрелка под него.
  4. Изберете MIN (минимум), MAX (максимум) или AVERAGE (средно).
  5. В маркираната клетка се появява формула. Щракнете върху всяка друга клетка - тя ще бъде добавена към функцията. Плъзнете кутия около него, за да покриете диапазона. Или щракнете върху мрежата, докато държите натиснат клавиш Ctrl, за да изберете един елемент в даден момент.
  6. Натиснете Enter, когато сте готови. Резултатът ще се покаже в клетката.

В Excel изчисляването на средната стойност е достатъчно лесно. Няма нужда да добавяте и след това да разделяте сумата. За това има отделна функция. Можете също да намерите минимума и максимума в комплект. Това е много по-лесно от броенето на ръка или изкопаването на числа в огромна електронна таблица. Ето защо Excel е популярен в много области на дейност, където се изисква точност: бизнес, одит, HR администрация, финанси, търговия, математика, физика, астрономия, икономика, наука.

В процеса на изготвяне на различни видове класиране или анализиране на показателите за успех е много удобно да подчертаете най-добрия или най-лошия резултат в цвят. Как да маркирате максимална стойност в Excel с цвят? Това е мястото, където условното форматиране помага много. Предимството на този подход е, че автоматичното подчертаване на най-добрите / най-лошите показатели остава функционално дори когато данни се добавят или премахват от таблицата.

Как да подчертаете с цвят максималната стойност в Excel

Да вземем например таблица с разходите:

За да подчертаете най-високите и най-ниските разходи при светкавична скорост, направете следното:


В резултат на това сме маркирали клетката с максималната числова стойност.

Как да изберем минималната стойност в Excel

За да маркирате минималната стойност в Excel, повторете цялата процедура, описана по-горе. Само вместо функцията MAX трябва да има функция MIN. Тази функция ви позволява да изберете най-малката стойност в таблицата на Excel. Изберете червено вместо зелено.


Към същия диапазон трябва да се прилагат 2 правила за условно форматиране. За да проверите, изберете инструмента: "НАЧАЛО" - "Стилове" - "Условно форматиране" - "Управление на правилата"


Крайният ефект от прилагането на две правила за условно форматиране за подчертаване на високите и ниските стойности в колона B (Разходи) е:

Принципът на действие и на двете правила е един и същ. Те се различават само по функциите = MAX () и = MIN (). Освен това аргументите на функцията са идентични. Първата функция = MAX () търси незаменим диапазон с абсолютни препратки $ B $ 2: $ B $ 10 за максималната стойност. Функцията е последвана от оператор за сравнение с променлива текуща клетка, която има относителна препратка, започваща от B2. Правилото за условно форматиране чете тази формула по следния начин: ако числото е по-голямо от всички, маркирайте с цвят. Веднага щом максималното число (който връща функцията MAX) съвпадне със стойността на текущата клетка, формулата връща булевата стойност TRUE и форматът незабавно се прилага с подходящия цвят на запълване. Правилото за интелигентно форматиране на клетките на колоните работи и с функцията MIN.



Открояване на трите най-ниски стойности подред

В различни ситуации е полезно автоматично да маркирате трите клетки с най-ниски стойности. Условното форматиране с подходящи формули върши отлична работа за това. Примерна таблица с данни:

Нека да усложним малко задачата. Клетката с най-малка стойност трябва автоматично да се запълни с червено. Следващата клетка с втората най-ниска стойност ще получи оранжев цвят на запълване. И третата най-ниска стойност е жълтото.

За да постигнете този ефект, изпълнете следните стъпки:


Трите най-ниски стойности се маркират автоматично в различни цветове.

Внимание! Ако диапазонът, който разглеждате, съдържа няколко еднакви минимални стойности, всички те ще бъдат подчертани в цвят.

По същия начин можете да подчертаете най-високите стойности с цвят. Просто променете функцията SMALL на LARGE във формулата. Например: = ГОЛЯМ ($ B $ 2: $ B $ 9,3) = B2


Най-малката и най-голямата функция в Excel

Функция = SMALL () търси в дадения (в първия аргумент) диапазон за следващата най-малка стойност. Опашката е посочена във втория аргумент на функцията. Тоест, ако във втория аргумент сме посочили числото 3, тогава функцията връща третата на свой ред най-малка стойност от посочения диапазон $ B $ 2: $ B $ 9. Ако резултатът от изчисляването на функцията е равен на текущата клетка, тогава й се присвоява съответният формат (цвят на запълване). Важно е да се отбележи, че във формулата за преглед на диапазона използваме абсолютни референтни адреси, а след оператора за сравнение = B2 - относителен, тъй като стойността на функцията се сравнява с всяка клетка от избрания диапазон. А функцията LARGE работи в обратна пропорция.

Полезен съвет! Ако няма нужда да разделяте 3-те най-малки стойности в различни цветове, тогава не е необходимо да създавате 3 правила за условно форматиране за същия диапазон. Достатъчно е само леко да промените формулата, като добавите само един символ на оператор: = SMALL ($ B $ 2: $ B $ 9; 3)> = B2. Тоест повече или равни.


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

Хареса ли ви статията? Сподели го
Печатна статия
До горе