Визначення максимального значення. Максимальний та Мінімальний за умовою у MS EXCEL. Функції для роботи з матрицями

Знаходження максимального/мінімального значення - просте завдання, але вона дещо ускладнюється, якщо МАКС/МІН потрібно знайти не серед усіх значень діапазону, а лише серед тих, що задовольняють певну умову.

Нехай є таблиця з двома стовпцями: текстовим та числовим.

Для зручності розуміння формул створимо два для кожного зі стовпців: Текст (A 6: A 30 ) та Числа (B6: B30 ). (Див. файл прикладу).

Розглянемо кілька завдань:

А.Знайдемо максимальне значення серед тих чисел, які відповідають значенню Текст1(Критерій введемо в осередок E6 ).
Тобто. будемо шукати максимальне значення не серед усіх значень стовпця Числові значення, а лише серед тих, у яких у тому ж рядку у стовпці А текстове значення дорівнює Текст1. Напишемо (не забудьте під час введення формули натиснути CTRL+SHIFT+ENTER):
=НАБІЛЬШИЙ(ЯКЩО(A6:A30=E6;B6:B30;"");1)

або з Іменованими діапазонами:

=НАЙБІЛЬШИЙ(ЯКЩО(Текст=E6;Числа;"");1)

Частина формули Текст=E6 , поверне (ІСТИНА:БРЕХНЯ:БРЕХНЯ:БРЕХНЯ:ІСТИНА:БРЕХНЯ:БРЕХНЯ:ІСТИНА:БРЕХНЯ:БРЕХНЯ:БРЕХНЯ: ІСТИНА:БРЕХНЯ:БРЕХНЯ:БРАВА:БРЕХНЯ:БРЕХНЯ:БРЕЖДА: Брехня: Брехня: Брехня: Брехня) (для перегляду результату виділіть цю частину формули в і натисніть клавішу ). ІСТИНА відповідає рядкам, у яких у стовпці Текстові значенняміститься значення Текст1.

Частина формули ЯКЩО(Текст=E6;Числа;""), поверне (10:"":"":"":-66:"":"":"": -37:"":"":"":-5:"": "":"": 4:"":"":"":8:"":"":"":""), де замість ІСТИНА підставлено значення з числового стовпця, а замість БРЕХНЯ - значення . Замість "" можна було б використовувати будь-який текстовий символ (літеру) або взагалі опустити (у цьому випадку масив буде виглядати так (10:БРЕХНЯ:БРЕХНЯ:БРЕХНЯ:-66: БРЕХНЯ:БРЕХНЯ:-37:БРЕХНЯ: БРЕХНЯ) :-5:БРЕХНЯ:БРЕХНЯ:БРЕХНЯ:4: БРЕХНЯ:БРЕХНЯ:БРЕХНЯ:8: БРЕХНЯ:БРЕХНЯ:БРЕХНЯ:БРЕХНЯ)).

Функція НАЙБІЛЬШИЙ з другим параметром =1 використана замість функції МАКС() , т.к. якщо критерію не задовольняє жодна рядок, то формула = МАКС(("":"":"":"":"":"":"": "":"":"":"": "":"":"":"":"" :"":"": "":"":"":"":"":"":""))поверне 0!, що може ввести в оману. Функція НАЙБІЛЬШИЙ() у цьому випадку поверне помилку #ЧИСЛО!

Б.Знайдемо максимальне значення лише серед чисел, що належать певному інтервалу значень, наприклад, від 5 до 50. Кордони можна ввести в комірки I 14 і J14 . Рішенням є:
=НАЙБІЛЬШИЙ(ЯКЩО((Числа>=I14)*(Числа<=J14);Числа);1)

Ст.Знайдемо за допомогою формули масивумінімальне значення серед тих, що відповідають значенню Текст3:
=МІН(ЯКЩО((Текст=E7);Числа;"");1)

Тобто. якщо у стовпці А значення = Текст3, то враховується значення у стовпці B , якщо значення<> Текст3, Враховується максимальне значення+1, тобто. свідомо не мінімальне. Далі функція МІН() повертає мінімальне значення з отриманого масиву, причому зрозуміло, що жодне з значень, де<> Текст3,не спотворить результат (див. завдання А).

Інше рішення за допомогою формули ДМІН() , яка не є формулою масиву.
=ДМІН(A5:B30;B5;I8:I9)

р.Знайдемо мінімальне значення, серед тих, які більше:
=ДМІН(A5:B30;B5;I10:I11)
де в діапазоні I10:I11 міститься критерій =B6>СРЗНАЧ(Числа)

Д.Знайдемо максимальне значення за модулем. З малюнка вище видно, що це -99. Для цього використовуйте формулу масиву:

ЯКЩО(МАКС(ABS(Числа))=МАКС(Числа);МАКС(Числа);-МАКС(ABS(Числа))))

е.Знайдемо мінімальне позитивне число:

=НАЙМЕНШИЙ(Числа;РАХУНКИ(Числа;"<=0")+1) - Звичайна формула!

=НАЙМЕНШИЙ(ЯКЩО(Числа>0;Числа);1) - формула масиву.

ПОРАДА:

Всі вищезазначені завдання можна вирішити без використання та функції ДМІН(). Для цього потрібно створити додатковий стовпець, в якому будуть виведені ті значення, які задовольняють критеріям. Потім, серед відібраних значень за допомогою функцій МАКС() або МІН(), визначити відповідно максимальне або мінімальне значення (див. файл прикладу Аркуш без формул масиву).

Множинні умови

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

В цьому випадку доведеться записати складнішу формулу масиву:

=НАЙМЕНШИЙ(ЯКЩО(($A$6:$A$16=E6)*($B$6:$B$16=F6);$C$6:$C$16;"");1)

У прикладному файлі для наочності налаштовано . Крім того, для вибору критеріїв використано (див. жовті комірки).

Аналогічно можна налаштувати формули для знаходження мінімального значення для рядків, значення яких потрапляють у певний діапазон.

Вихідні дані
Вихідними даними може бути будь-який числовий набір даних, таких як горизонтальний або вертикальний діапазон, двовимірний масив або навіть суміжні діапазони або окремо заповнені осередки. Для зручності я взяв одновимірний вертикальний масив А1: А5

Для вирішення цього завдання можна використовувати як додатковий стовпець з проміжною формулою, так і одну формулу масиву

1. Рішення з додатковим стовпцем
У В1 напишіть формулу

ЯКЩО(A1<>0;A1;"")

У С1 напишіть формулу

Вона і поверне шуканий результат - число 3

ЯКЩО(A1<>0;A1;"")

ЯКЩО(A1>0;A1;"")

МІН(ЯКЩО(A1:A5)<>0; A1: A5))

Як це працює:Ось ця частина формули A1:A5<>0 формує масив (ІСТИНА:ІСТИНА:БРЕХНЯ:БРЕХНЯ:ІСТИНА), після чого застосовуючи функцію ЯКЩО() ми перетворимо масив на наступний: (5:7:БРЕХНЯ:БРЕХНЯ:3). Оскільки функція МІН()ігнорує текстові та логічні значення, то в результаті отримуємо шукане - число 3

Якщо у діапазоні є негативні числа, то замість формули

МІН(ЯКЩО(A1:A5)<>0; A1: A5))

МІН(ЯКЩО(A1:A5>0;A1:A5))

НАЙМЕНШИЙ(A1:A5;РАХУНКИ(A1:A5;0)+1)

Як це працює: За допомогою функції ЗЛІЧИЛИми підраховуємо кількість нулів у діапазоні та додавши до цієї кількості одиницю ми повертаємо перше найменше число більше нуля за допомогою функції НАЙМЕНШИЙ

Якщо у діапазоні є негативні числа, то замість формули

Excel має функцію визначення мінімального значення. Проте знайти мінімальне значення за умовою проблематично. Упоратися з цим завданням може функція надбудови =МІНСЕСЛІ

(аналогічна стандартної функції Excel СУМІСЛІ).

В Excel версії 2016 і вище з'явилася вбудована функція МІНЕСЛІ, можете скористатися нею. Якщо ваш Excel більш ранньої версії, то цю функцію можна використовувати встановивши надбудову VBA-Excel.

У функції такі аргументи =МІНЕСЛІ(ДІАПАЗОН; КРИТЕРІЙ; [ДІАПАЗОН_ПОШУКУ ])

ДІАПАЗОН- Діапазон осередків, що перевіряються.

• КРИТЕРІЙ- Умова у форматі числа, виразу чи тексту, що визначає перевірку мінімального значення.
• [ ДІАПАЗОН_ПОШУКУ ]- Фактичний діапазон для визначення мінімального значення. Якщо цей параметр не заданий, буде використовуватися комірки, що задаються параметром ДІАПАЗОН.

Приклад 1

Як критерій можна вказувати значення та логічні вирази:

1. Розглянемо наступний приклад у якому визначається мінімальна оцінка з літератури. Для цього в параметр КРИТЕРІЙвказано значення «Література», а параметр ДІАПАЗОН- Перелік предметів.
2. Якщо як критерій вказати логічний вираз «<>Російська», то визначиться мінімальна оцінка з усіх предметів крім російської.

Приклад 2

У наступному прикладі параметр ДІАПАЗОН_ПОШУКУне заданий, тому мінімальне значення визначається серед осередків, зазначених у параметрі ДІАПАЗОН.

У процесі підготовки різного роду ранжування або аналізу показників успішності, дуже зручно, якщо виділити кольором найкращий або найгірший результат. Як виділити максимальне значення в Excel кольором? Тут суттєво допоможе умовне форматування. Перевагою такого підходу є збереження працездатності автоматичного виділення кольором кращих/найгірших показників навіть при додаванні або видаленні даних таблиці.

Як виділити кольором максимальне значення в Excel

Наприклад візьмемо таблицю витрат:

Щоб блискавично виділити найбільші та найменші витрати робимо таке:

В результаті ми виділили кольором комірку з максимальним числовим значенням.

Як вибрати мінімальне значення в Excel

Щоб виділити кольором мінімальне значення в Excel, повторіть порядок дій, описаних вище. Тільки замість функції МАКС, має бути функція МІН. Ця функція дозволяє вибрати найменше значення у таблиці Excel. А замість зеленого заливки виберіть червоний колір.

До того самого діапазону повинні бути застосовані 2 правила умовного форматування. Щоб перевірити, виберіть інструмент: «ГОЛОВНА»-«Стилі»-«Умовне форматування»-«Управління правилами»

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

Принцип дій обох правил є однаковим. Вони відрізняються лише функціями =МАКС() і =МІН(). Більше того, аргументи функцій ідентичні. Перша функція =МАКС() шукає в незмінному діапазоні з абсолютними посиланнями $B$2:$B$10 максимальне значення. Після функції стоїть оператор порівняння із змінним поточним осередком яка має відносне посилання починаючи від B2. Правило умовного форматування читає цю формулу так: якщо число більше за всіх, то виділити кольором. Як тільки збігається максимальне число (яке повертає функція МАКС) зі значенням поточної комірки, формула повертає логічне значення ІСТИНА і відразу застосовується формат із відповідним кольором заливки. Також спрацьовує правило для розумного форматування осередків стовпця і з функцією МІН.

Виділення кольором трьох найменших значень поспіль

У різних ситуаціях корисно застосовується автоматичне виділення кольором трьох осередків із найменшими значеннями. Відносне форматування з відповідними формулами добре справляється з цим завданням. Таблиця з даними для прикладу:

Ускладнимо трохи завдання. Комірка з найменшим значенням має автоматично отримати червоний колір заливки. Наступний осередок з другим найменшим значенням отримає колір заливки помаранчевий. А третє найменше значення – жовте.

Щоб досягти цього ефекту, слід виконати такі дії:

Три найменші значення автоматично виділилися різними кольорами.

Увага! Якщо діапазон, що переглядається, містить кілька однакових мінімальних значень всі вони будуть виділені кольором.

Аналогічним способом можна виділяти кольором найбільші значення. Просто змінити у формулі функцію найменший на найбільший. Наприклад: =найбільший($B$2:$B$9;3)=B2

Функція найменший та найбільший у Excel

Функція = НАЙМЕНШИЙ() шукає в заданому (у першому аргументі) діапазоні чергове найменше значення. Черга вказана у другому аргументі функції. Тобто якщо у другому аргументі ми вказали число 3, то функція повертає третє по черзі найменше значення заданого діапазону $B$2:$B$9. Якщо результат обчислення функції дорівнює поточній комірці, значить до неї присвоюється відповідний формат (колір заливки). Важливо відзначити, що у формулі для перегляду діапазону ми використовуємо абсолютні адреси посилання, а після оператора порівняння =B2 – відносні, оскільки значення функції порівнюється з кожним осередком виділеного діапазону. А функція НАЙБІЛЬШИЙ працює назад пропорційно.

Корисна порада! Якщо немає необхідності розбивати 3 найменші значення на різні кольори, тоді необов'язково створювати 3 правила умовного форматування для одного і того ж діапазону. Досить просто трохи змінити формулу додавши всього один символ оператора: = Найменший ($ B $ 2: $ B $ 9; 3)> = B2. Тобто – більше чи одно.

Всі описані способи хороші тим, що при зміні значень у осередках вони все одно працюватимуть автоматично.

Цей табличний процесор упорається практично з усіма розрахунками. Він ідеально підходить для бухгалтерського обліку. Для обчислень існують спеціальні інструменти – формули. Їх можна застосовувати до діапазону або до окремих осередків. Щоб дізнатися мінімальну чи максимальну цифру групи клітин, необов'язково шукати їх самостійно. Найкраще скористатися призначеними для цього опціями. Також корисно буде розібратися, як порахувати середнє значення Excel.

Це особливо актуально в таблицях з великим обсягом даних. Якщо у стовпці, наприклад, вказано ціни на продукцію торгового центру. І вам треба дізнатися, який товар найдешевший. Якщо шукати його вручну, піде дуже багато часу. Але в Екселі це можна зробити буквально за кілька кліків. Утиліта також обчислює середнє арифметичне. Адже це дві прості операції: додавання та поділ.

Максимальне та мінімальне

Ось як знайти максимальне значення в Excel:

1. Поставте курсор-осередок у будь-яке місце.
2. Перейдіть до меню "Формули".
3. Натисніть кнопку «Вставити функцію».
4. У списку оберіть «МАКС». Або напишіть це слово у полі «Пошук» та натисніть «Знайти».
5. У вікні «Аргументи» введіть адреси діапазону, максимальне значення якого потрібно дізнатися. В Excel імена клітин складаються з літери та цифри («B1», «F15», «W34»). А назва діапазону - це перший і останній осередки, які до нього входять.
6. Замість адреси можна написати кілька чисел. Тоді система покаже найбільше їх.
7. Натисніть "OK". У клітці, де стояв курсор, з'явиться результат.

Наступний крок – вкажіть діапазон значень

Тепер буде легко розібратися, як знайти мінімальне значення в Excel. Алгоритм дій цілком ідентичний. Просто замість "МАКС" виберіть "МІН".

Середнє

Середнє арифметичне обчислюється так: скласти всі цифри з множини і поділити на їх кількість. В Екселі можна порахувати суми, дізнатися, скільки осередків у рядку і таке інше. Але це надто складно та довго. Прийде використовувати багато різних функцій. Пам'ятайте інформацію. Або навіть щось записувати на листочок. Але можна спростити алгоритм.

Ось як знайти середнє значення в Excel:

1. Поставте комірку курсор у будь-яке вільне місце таблиці.
2. Перейдіть на вкладку Формули.
3. Натисніть кнопку «Вставити функцію».
4. Виберіть «ЗНАЧ».
5. Якщо цього пункту немає у списку, відкрийте його за допомогою опції «Знайти».
6. В області «Число1» введіть адресу діапазону. Або напишіть кілька цифр у різних полях «Число2», «Чісло3».
7. Натисніть "OK". У комірці з'явиться необхідне значення.

Так можна проводити розрахунки не тільки з позиціями в таблиці, але і з довільними множинами. Excel, власне, грає роль просунутого калькулятора.

Інші способи

Максимальне, мінімальне та середнє можна дізнатися й іншими способами.

1. Знайдіть панель функцій із позначкою «Fx». Вона над основною робочою областю таблиці.
2. Поставте курсор у будь-яку комірку.
3. Введіть аргумент у полі «Fx». Він починається зі знаку рівності. Потім йде формула та адреса діапазону/клітини.
4. Повинно вийти щось на кшталт «=МАКС(B8:B11)» (максимальне), «=МІН(F7:V11)» (мінімальне), «=СРЗНАЧ(D14:W15)» (середнє).
5. Клацніть на «галочку» поруч із полем функцій. Або просто натисніть клавішу Enter. У виділеному осередку з'явиться потрібне значення.
6. Формулу можна скопіювати безпосередньо до самої клітини. Ефект буде той самий.

Знайти та обчислити допоможе Excel-інструмент «Автофункції».

1. Поставте курсор у комірку.
2. Знайдіть кнопку, назва якої починається на "Авто". Це залежить від обраної в Excel налаштування за замовчуванням («Автосума», «Авточисло», «Автозміщ», «Автоіндекс»).
3. Натисніть на чорну стрілку під нею.
4. Виберіть "МІН" (мінімальне значення), "МАКС" (максимальне) або "СРЗНАЧ" (середнє).
5. У зазначеній клітині з'явиться формула. Клацніть на будь-яку іншу комірку - вона буде додана в функцію. Розтягніть рамку навколо неї, щоб охопити діапазон. Або клацайте по сітці із затиснутою клавішею Ctrl, щоб виділяти по одному елементу.
6. Коли закінчите, натисніть клавішу Enter. Результат відобразиться у клітці.

В Excel вирахувати середнє значення досить легко. Не треба складати, а потім ділити суму. І тому існує окрема функція. Також можна знайти мінімум і максимум у множині. Це набагато легше, ніж рахувати вручну або шукати цифри у величезній таблиці. Тому Ексель популярний у багатьох сферах діяльності, де потрібна точність: бізнес, аудит, кадрове діловодство, фінанси, торгівля, математика, фізика, астрономія, економіка, наука.