Određivanje maksimalne vrijednosti. Maksimum i minimum po uvjetu u MS EXCEL-u. Funkcije za rad s matricama

Pronalaženje maksimalne/minimalne vrijednosti jednostavan je zadatak, ali postaje nešto kompliciranije ako MAX/MIN ne treba pronaći među svim vrijednostima u rasponu, već samo među onima koje zadovoljavaju određeni uvjet.

Neka postoji tablica s dva stupca: tekstualni i numerički.

Da bismo lakše razumjeli formule, stvorimo dvije za svaki stupac: Tekst (A 6: A 30 ) I Brojke (B6:B30 ). (pogledajte datoteku primjera).

Razmotrimo nekoliko zadataka:

A. Pronađimo najveću vrijednost među onim brojevima koji odgovaraju vrijednosti Tekst1(unesite kriterij u ćeliju E6 ).
Oni. tražit ćemo maksimalnu vrijednost ne među svim vrijednostima stupca Numeričke vrijednosti, ali samo među onima koji imaju u istom redu u stupcu A tekstualna vrijednost je Tekst1. Napišimo (ne zaboravite pritisnuti prilikom unosa formule CTRL+SHIFT+ENTER):
=NAJVEĆI(AKO(A6:A30=E6,B6:B30,"");1)

ili sa Imenovani rasponi:

=LARGE(IF(Tekst=E6,Brojevi,""),1)

Dio formule Text=E6 vratit će (TRUE:FALSE:FALSE:FALSE:TRUE:FALSE:FALSE:FALSE:TRUE:FALSE:FALSE:FALSE: TRUE:FALSE:FALSE:FALSE:TRUE:FALSE:FALSE:FALSE :TRUE: FALSE:FALSE:FALSE:FALSE) (za prikaz rezultata odaberite ovaj dio formule i pritisnite tipku). TRUE odgovara recima koji imaju Tekstualne vrijednosti sadrži vrijednost Tekst1.

Dio formule IF(Tekst=E6;Brojevi;""), vratit će se (10:"":"":"":-66:"":"":"": -37:"":"":"":-5:"": "":"" : 4:"":"":"":8:"":"":"":""), gdje je vrijednost iz numeričkog stupca zamijenjena za TRUE, a vrijednost zamijenjena za FALSE. Umjesto "" možete koristiti bilo koji tekstualni znak (slovo) ili ga potpuno izostaviti (u ovom slučaju niz bi izgledao ovako (10: FALSE: FALSE: FALSE: -66: FALSE: FALSE: FALSE: -37: FALSE: LAŽ: LAŽ :-5: LAŽ: LAŽ: LAŽ: 4: LAŽ: LAŽ: LAŽ: 8: LAŽ: LAŽ: LAŽ: LAŽ)).

Umjesto funkcije MAX() koristi se funkcija LARGE() s drugim parametrom =1 jer ako nijedan redak ne zadovoljava kriterij, onda formula = MAX(("":"":"":"":"":"":"": "":"":"":"": "":"":"":"":"" :"":"": "":"":"":"":"":"":"")) vratit će 0!, što može dovesti u zabludu. Funkcija LARGE() u ovom će slučaju vratiti pogrešku #NUM!

B. Pronađimo najveću vrijednost samo među brojevima koji pripadaju određenom rasponu vrijednosti, na primjer od 5 do 50. Granice se mogu unijeti u ćelije ja 14 I J14 . Rješenje je:
=LARGE(IF((Brojevi>=I14)*(Brojevi<=J14);Числа);1)

U. Naći ćemo ga uz pomoć formule polja minimalnu vrijednost među onima koji odgovaraju vrijednosti Tekst3:
=MIN(IF((Tekst=E7);Brojevi;"");1)

Oni. ako je u stupcu A vrijednost = Tekst3, tada se u obzir uzima vrijednost u stupcu B , ako vrijednost<> Tekst3, tada se uzima u obzir najveća vrijednost +1, tj. svakako NIJE minimalan. Zatim, funkcija MIN() vraća minimalnu vrijednost iz rezultirajućeg niza i jasno je da nijedna od vrijednosti, gdje<> Tekst3, neće iskriviti rezultat (vidi problem A).

Drugo rješenje je korištenje formule DMIN(), koja nije formula polja.
=DMIN(A5:B30;B5;I8:I9)

G. Pronađimo minimalnu vrijednost među onima koje su veće:
=DMIN(A5:B30;B5;I10:I11)
gdje u rasponu I10:I11 sadrži kriterij =B6>PROSJEK(Brojevi)

D. Nađimo najveću apsolutnu vrijednost. Na gornjoj slici možete vidjeti da je -99. Da biste to učinili koristite formula polja:

IF(MAX(ABS(Brojevi))=MAX(Brojevi),MAX(Brojevi),-MAX(ABS(Brojevi)))

E. Nađimo minimalni pozitivni broj:

=SMALL(Brojevi;COUNTIF(Brojevi;"<=0")+1) - uobičajena formula!

=SMALL(IF(Brojevi>0,Brojevi),1) - formula polja.

SAVJET:

Svi gore navedeni problemi mogu se riješiti bez upotrebe funkcije DMIN(). Da biste to učinili, morat ćete stvoriti dodatni stupac u kojem će se prikazati samo one vrijednosti koje zadovoljavaju kriterije. Zatim među odabranim vrijednostima pomoću funkcija MAX() ili MIN() odredite maksimalnu odnosno minimalnu vrijednost (pogledajte primjer datoteke Sheet bez formule niza).

Višestruki uvjeti

Gore navedeni pristup može se proširiti na slučajeve kada je potrebno pronaći maksimum ili minimum za nekoliko tekstualnih uvjeta.

U ovom slučaju, morat ćete napisati složeniji formula polja:

=MALO(IF(($A$6:$A$16=E6)*($B$6:$B$16=F6),$C$6:$C$16;"");1)

U datoteci primjera, radi jasnoće, . Osim toga, kriteriji se koriste za odabir (vidi žute ćelije).

Slično tome, možete postaviti formule za pronalaženje minimalne vrijednosti za retke čije su vrijednosti unutar određenog raspona.

Početni podaci
Izvor podataka može biti bilo koji numerički skup podataka, kao što je vodoravni ili okomiti raspon, dvodimenzionalni niz ili čak nesusjedni rasponi ili pojedinačno popunjene ćelije. Radi praktičnosti, uzeo sam jednodimenzionalni vertikalni niz A1:A5

Da biste riješili ovaj problem, možete koristiti ili dodatni stupac s posrednom formulom ili jednu formulu polja

1. Rješenje s dodatnim stupcem
U B1 upiši formulu

AKO (A1<>0;A1;"")

U C1 upiši formulu

Vratit će željeni rezultat - broj 3

AKO (A1<>0;A1;"")

IF(A1>0;A1;"")

MIN(IF(A1:A5<>0;A1:A5))

Kako radi: Ovo je dio formule A1:A5<>0 tvori niz (TRUE:TRUE:FALSE:FALSE:TRUE), nakon čega pomoću funkcije IF() transformiramo niz u sljedeće: (5:7:LAŽ:LAŽ:3). Budući da funkcija MIN() zanemaruje tekst i logičke vrijednosti, tada na kraju dobijemo željeni broj - broj 3

Ako u rasponu postoje negativni brojevi, tada umjesto formule

MIN(IF(A1:A5<>0;A1:A5))

MIN(IF(A1:A5>0,A1:A5))

SMALL(A1:A5,COUNTIF(A1:A5,0)+1)

Kako radi: Korištenje funkcije COUNTIF brojimo broj nula u rasponu i dodavanjem jedinice ovom broju vraćamo prvi najmanji broj veći od nule pomoću funkcije NAJMANJE

Ako u rasponu postoje negativni brojevi, tada umjesto formule

Excel ima funkciju za određivanje minimalne vrijednosti. Međutim, pronalaženje minimalne vrijednosti prema uvjetu je problematično. Funkcija iz dodatka može se nositi s ovim zadatkom =MINSIF

(slično standardnoj Excel funkciji SUMIF).

U Excel verziji 2016 a iznad je ugrađena funkcija MINESLEY, možete ga koristiti. Ako je vaš Excel starija verzija, tada se ova funkcija može koristiti instaliranjem dodatka VBA-Excel.

Funkcija ima sljedeće argumente =MINESLI( RASPON;KRITERIJI;[RASPONS_PRETRAŽIVANJA ])

RANGE- Raspon ćelija koje treba provjeriti.

• KRITERIJ- Uvjet u obliku broja, izraza ili teksta koji navodi provjeru minimalne vrijednosti.
• [SEARCH_RANGE]- Stvarni raspon za određivanje minimalne vrijednosti. Ako ovaj parametar nije naveden, koristit će se ćelije navedene u parametru RANGE.

Primjer 1

Možete navesti vrijednosti i logičke izraze kao kriterije:

1. Razmotrite sljedeći primjer koji određuje minimalnu ocjenu iz književnosti. Da biste to učinili, u parametru KRITERIJ navedena je vrijednost “Literatura”, a parametar RANGE- popis predmeta.
2. Ako kao kriterij odredite logički izraz «<>Ruski", tada će se minimalna ocjena odrediti iz svih predmeta osim ruskog jezika.

Primjer 2

U sljedećem primjeru, parametar SEARCH_RANGE nije navedeno, pa se minimalna vrijednost određuje između ćelija navedenih u parametru RANGE.

U procesu pripreme raznih vrsta ljestvica ili analize pokazatelja uspješnosti, vrlo je zgodno da najbolji ili najlošiji rezultat označite bojom. Kako bojom istaknuti maksimalnu vrijednost u Excelu? Ovo je mjesto gdje uvjetno oblikovanje dobro dolazi. Prednost ovog pristupa je u tome što automatsko označavanje najboljih/najgorih pokazatelja ostaje operativno čak i kada se dodaju ili uklanjaju podaci iz tablice.

Kako istaknuti maksimalnu vrijednost u Excelu

Na primjer, uzmite tablicu troškova:

Kako biste brzo identificirali najveće i najniže troškove, učinite sljedeće:

Kao rezultat toga, istaknuli smo ćeliju s maksimalnom numeričkom vrijednošću.

Kako odabrati minimalnu vrijednost u Excelu

Da biste istaknuli minimalnu vrijednost u Excelu, ponovite cijeli gore opisani postupak. Samo umjesto funkcije MAX treba postojati funkcija MIN. Ova vam funkcija omogućuje odabir najmanje vrijednosti u Excel tablici. I umjesto zelene boje ispune, odaberite crvenu.

Moraju postojati 2 pravila uvjetnog oblikovanja primijenjena na isti raspon. Za provjeru odaberite alat: “HOME” - “Styles” - “Conditional Formatting” - “Manage Rules”

Konačni učinak primjene dva pravila uvjetnog oblikovanja za isticanje najveće i najmanje vrijednosti u stupcu B (Troškovi):

Princip rada oba pravila je isti. Razlikuju se samo u funkcijama =MAX() i =MIN(). Štoviše, argumenti funkcije su identični. Prva funkcija =MAX() pretražuje nepromjenjivi raspon s apsolutnim referencama $B$2:$B$10 za najveću vrijednost. Nakon funkcije nalazi se operator usporedbe s trenutnom ćelijom koja se mijenja i koja ima relativnu referencu počevši od B2. Pravilo uvjetnog oblikovanja glasi ovu formulu na sljedeći način: ako je broj najveći, označite ga bojom. Čim maksimalni broj (koji vraća funkcija MAX) odgovara vrijednosti trenutne ćelije, formula vraća Booleovu vrijednost TRUE i odmah se primjenjuje format s odgovarajućom bojom ispune. Pravilo za pametno oblikovanje ćelija stupca također funkcionira s funkcijom MIN.

Označite tri najmanje vrijednosti u nizu

U raznim situacijama korisno je automatsko označavanje tri ćelije s najnižim vrijednostima. Uvjetno oblikovanje s odgovarajućim formulama to odlično čini. Primjer tablice podataka:

Zakomplicirajmo malo zadatak. Ćelija s najnižom vrijednošću trebala bi automatski imati crvenu boju ispune. Sljedeća ćelija s drugom najnižom vrijednošću imat će narančastu boju ispune. I treća najniža vrijednost je žuta.

Da biste postigli ovaj učinak, slijedite ove korake:

Tri najmanje vrijednosti automatski su označene različitim bojama.

Pažnja! Ako raspon koji se gleda sadrži nekoliko identičnih minimalnih vrijednosti, sve će one biti označene bojom.

Na sličan način možete istaknuti najveće vrijednosti u boji. Jednostavno promijenite funkciju SMALL u MAXIMUM u formuli. Na primjer: =NAJVEĆI($B$2:$B$9,3)=B2

Najmanja i najveća funkcija u Excelu

Funkcija =SMALL() traži sljedeću najmanju vrijednost u navedenom rasponu (u prvom argumentu). Red je naveden u drugom argumentu funkcije. To jest, ako smo naveli broj 3 u drugom argumentu, tada funkcija vraća treću najmanju vrijednost u zadanom rasponu $B$2:$B$9. Ako je rezultat izračuna funkcije jednak trenutnoj ćeliji, tada joj se dodjeljuje odgovarajući format (boja ispune). Važno je napomenuti da u formuli za pregled raspona koristimo apsolutne referentne adrese, a nakon operatora usporedbe =B2 koristimo relativne, jer se vrijednost funkcije uspoređuje sa svakom ćelijom odabranog raspona. A LARGE funkcija radi u obrnutom razmjeru.

Koristan savjet! Ako nema potrebe za dijeljenjem 3 najmanje vrijednosti u različite boje, tada nije potrebno stvoriti 3 pravila uvjetnog oblikovanja za isti raspon. Dovoljno je samo malo promijeniti formulu dodavanjem samo jednog simbola operatora: =SMALL($B$2:$B$9;3)>=B2. Odnosno, veće ili jednako.

Sve opisane metode su dobre jer kada promijenite vrijednosti u ćelijama, one će i dalje raditi automatski.

Ovaj procesor proračunskih tablica može obraditi gotovo sve izračune. Idealan je za računovodstvo. Postoje posebni alati za izračune - formule. Mogu se primijeniti na raspon ili na pojedinačne ćelije. Da biste saznali minimalni ili maksimalni broj u grupi ćelija, ne morate ih sami tražiti. Bolje je koristiti opcije predviđene za to. Također će biti korisno razumjeti kako izračunati prosjek u Excelu.

To se posebno odnosi na tablice s velikom količinom podataka. Ako stupac, na primjer, prikazuje cijene proizvoda iz trgovačkog centra. I morate saznati koji je proizvod najjeftiniji. Ako ga tražite ručno, trebat će vam puno vremena. Ali u Excelu se to može učiniti u samo nekoliko klikova. Uslužni program također izračunava aritmetičku sredinu. Uostalom, to su dvije jednostavne operacije: zbrajanje i dijeljenje.

Maksimum i minimum

Evo kako pronaći maksimalnu vrijednost u Excelu:

1. Postavite pokazivač ćelije bilo gdje.
2. Idite na izbornik "Formule".
3. Pritisnite Umetni funkciju.
4. Odaberite "MAX" s popisa. Ili napišite ovu riječ u polje "Traži" i kliknite "Pronađi".
5. U prozoru "Argumenti" unesite adrese raspona čiju najveću vrijednost trebate znati. U Excelu nazivi ćelija sastoje se od slova i broja (“B1”, “F15”, “W34”). A naziv raspona je prva i zadnja ćelija koje su uključene u njega.
6. Umjesto adrese možete napisati nekoliko brojeva. Tada će sustav prikazati najveći od njih.
7. Pritisnite OK. Rezultat će se pojaviti u ćeliji u kojoj se nalazi kursor.

Sljedeći korak - odredite raspon vrijednosti

Sada će biti lakše shvatiti kako pronaći minimalnu vrijednost u Excelu. Algoritam akcija je potpuno identičan. Samo zamijenite "MAX" s "MIN".

Prosjek

Aritmetička sredina se izračunava na sljedeći način: zbrojite sve brojeve iz skupa i podijelite s njihovim brojem. U Excelu možete izračunati iznose, saznati koliko je ćelija u nizu i tako dalje. Ali to je previše teško i dugotrajno. Morat ćete koristiti mnogo različitih funkcija. Držite informacije u glavi. Ili čak zapišite nešto na komad papira. Ali algoritam se može pojednostaviti.

Evo kako pronaći prosjek u Excelu:

1. Postavite pokazivač ćelije u bilo koji slobodan prostor u tablici.
2. Idite na karticu "Formule".
3. Kliknite na "Insert Function".
4. Odaberite PROSJEČNO.
5. Ako ove stavke nema na popisu, otvorite je pomoću opcije "Traži".
6. U područje Number1 unesite adresu raspona. Ili napišite nekoliko brojeva u različita polja "Broj2", "Broj3".
7. Pritisnite OK. U ćeliji će se pojaviti tražena vrijednost.

Na ovaj način možete izvršiti izračune ne samo s pozicijama u tablici, već i s proizvoljnim skupovima. Excel u biti igra ulogu naprednog kalkulatora.

druge metode

Maksimum, minimum i prosjek mogu se pronaći na druge načine.

1. Pronađite funkcijsku traku s oznakom "Fx". Nalazi se iznad glavnog radnog područja stola.
2. Postavite kursor u bilo koju ćeliju.
3. Unesite argument u polje "Fx". Počinje znakom jednakosti. Zatim dolazi formula i adresa raspona/ćelije.
4. Trebali biste dobiti nešto poput "=MAX(B8:B11)" (maksimum), "=MIN(F7:V11)" (minimum), "=PROSJEK(D14:W15)" (prosjek).
5. Pritisnite kvačicu pored polja funkcija. Ili jednostavno pritisnite Enter. Željena vrijednost pojavit će se u odabranoj ćeliji.
6. Formula se može kopirati izravno u samu ćeliju. Učinak će biti isti.

Alat Excel AutoFunctions pomoći će vam pronaći i izračunati.

1. Postavite kursor u ćeliju.
2. Pronađite gumb čije ime počinje s "Auto". To ovisi o zadanoj opciji odabranoj u Excelu (AutoSum, AutoNumber, AutoOffset, AutoIndex).
3. Kliknite na crnu strelicu ispod njega.
4. Odaberite MIN (minimalna vrijednost), MAX (maksimalna) ili AVERAGE (prosjek).
5. Formula će se pojaviti u označenoj ćeliji. Pritisnite bilo koju drugu ćeliju - bit će dodana u funkciju. "Razvucite" okvir oko njega da pokrijete raspon. Ili kliknite na rešetku dok držite pritisnutu tipku Ctrl da odaberete jedan po jedan element.
6. Kada završite, pritisnite Enter. Rezultat će biti prikazan u ćeliji.

U Excelu je izračunavanje prosjeka prilično jednostavno. Nema potrebe zbrajati pa dijeliti iznos. Za to postoji posebna funkcija. Također možete pronaći minimum i maksimum u kompletu. To je puno lakše od brojanja rukom ili traženja brojeva u ogromnoj tablici. Stoga je Excel popularan u mnogim područjima djelovanja gdje se traži točnost: poslovanje, revizija, ljudski resursi, financije, trgovina, matematika, fizika, astronomija, ekonomija, znanost.