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

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

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

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

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

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

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

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

Средно аритметично

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

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

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

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

други методи

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

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

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

1. Поставете курсора в клетка.
2. Намерете бутон, чието име започва с „Автоматично“. Това зависи от опцията по подразбиране, избрана в Excel (AutoSum, AutoNumber, AutoOffset, AutoIndex).
3. Кликнете върху черната стрелка под него.
4. Изберете MIN (минимална стойност), MAX (максимална) или AVERAGE (средна).
5. Формулата ще се появи в маркираната клетка. Кликнете върху всяка друга клетка - тя ще бъде добавена към функцията. „Разтегнете“ кутията около нея, за да покриете диапазона. Или щракнете върху мрежата, докато държите натиснат клавиша Ctrl, за да изберете един елемент наведнъж.
6. Когато приключите, натиснете Enter. Резултатът ще се покаже в клетката.

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

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

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

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

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

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

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

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

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

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

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

Маркирайте трите най-малки стойности в един ред

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

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

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

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

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

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

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

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

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

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

Excel има функция за определяне на минималната стойност. Намирането на минималната стойност по условие обаче е проблематично. Функцията от добавката може да се справи с тази задача =MINSIF(същото като стандартното Функции на Excel SUMIF).

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

Функцията има следните аргументи =MINESLI(ДИАПАЗОН;КРИТЕРИИ;[ДИАПАЗОН_ТЪРСЕНЕ ])

ОБХВАТ- Диапазон от клетки за проверка.

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

Пример 1

Можете да посочите стойности и логически изрази като критерии:

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

Пример 2

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

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

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

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

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

ИНДЕКС(диапазон_данни_за_извадка, MIN(АКО(диапазон=MIN(диапазон),РЕД(диапазон)-РЕД(заглавка_на_колона);””)))

В мястото “data_range_for_sampling” трябва да посочите диапазона от стойности A6:A18 за вземане на проби от таблица (например текст), от която функцията INDEX ще избере една резултатна стойност. Аргументът диапазон се отнася до областта на клетките с числови стойности, от които трябва да се избере първото най-малко число. В аргумента „column_header“ за втората функция ROW трябва да посочите препратка към клетка със заглавка на колона, която съдържа диапазон от числови стойности.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Изходни данни
Изходните данни могат да бъдат всеки числов набор от данни, като хоризонтален или вертикален диапазон, двуизмерен масив или дори несъседни диапазони или индивидуално попълнени клетки. За удобство взех едномерен вертикален масив A1:A5

За да разрешите този проблем, можете да използвате или допълнителна колона с междинна формула, или една формула за масив

1. Решение с допълнителна колона
В B1 напишете формулата

АКО(A1<>0;A1;"")

В C1 напишете формулата

Ще върне желания резултат - число 3

АКО(A1<>0;A1;"")

АКО(A1>0;A1;"")

МИН(АКО(A1:A5<>0;A1:A5))

Как работи:Това е частта от формулата A1: A5<>0 образува масив (ВЯРНО:ВЯРНО:НЕВЯРНО:ВЯРНО:ВЯРНО), след което с помощта на функцията IF() трансформираме масива в следното: (5:7:ЛЪЖА:ЛЪЖА:3). Тъй като функцията MIN()игнорира текст и логически стойности, тогава в крайна сметка получаваме желаното число - число 3

Ако има отрицателни числа в диапазона, тогава вместо формулата

МИН(АКО(A1:A5<>0;A1:A5))

МИН(АКО(A1:A5>0;A1:A5))

МАЛКО(A1:A5;БРОЯЩО(A1:A5;0)+1)

Как работи: Използване на функцията COUNTIFпреброяваме броя на нулите в диапазона и като добавяме единица към това число, връщаме първото най-малко число, по-голямо от нула, използвайки функцията НАЙ-МАЛКО

Ако има отрицателни числа в диапазона, тогава вместо формулата