Kako bojom istaknuti najmanje i najveće vrijednosti u Excelu. Funkcije u Excelu Minimalna, maksimalna i srednja vrijednost Minimalna vrijednost uz uvjet u Excelu

prosj.(raspon1; raspon2;...)

Prosjek brojeva u navedenom rasponu vraća se u trenutnu ćeliju.

Primjer 5. U rasponu ćelija A1:A5 od primjer 1 odrediti prosječnu vrijednost.

Rezultat bi trebao biti dobiven u ćeliji A7. Akcije korak po korak Čarobnjaci funkcija U ćeliju A7 trebate unijeti formulu: =prosjek(A1:A5).

Određivanje maksimalne vrijednosti

max(raspon1; raspon2;...)- skupina statističkih funkcija.

Najveći broj iz zadanog raspona vraća se u trenutnu ćeliju.

Primjer 6. U rasponu ćelija A1:A5 od primjer 1 odrediti maksimalnu vrijednost.

Rezultat bi trebao biti dobiven u ćeliji A8.

Akcije korak po korak Čarobnjaci funkcija U ćeliju A8 trebate unijeti formulu:

=max(A1:A5).

Ćelija A8 će sadržavati broj 2000.

Određivanje minimalne vrijednosti

min(raspon1; raspon2;...)- skupina statističkih funkcija.

Minimalni broj iz zadanog raspona vraća se u trenutnu ćeliju.

Primjer 7. U rasponu ćelija B1:B5 od primjer 2 odrediti minimalnu vrijednost.

Rezultat bi trebao biti dobiven u ćeliji B8.

Akcije korak po korak Čarobnjaci funkcija U ćeliju B8 trebate unijeti formulu:

=min(B1:B5).

Ćelija B8 će sadržavati broj 800.

Određivanje ranga broja

rang (adresa ćelije; raspon)- skupina statističkih funkcija.

Vrijednost koja odgovara položaju (rangu) broja određenog adresom ćelije u navedenom rasponu vraća se u trenutnu ćeliju.

Primjer 8. Kopirajte podatke iz odgovarajućih ćelija u stupcu A u ćelije D1, D2, D3, D4, D5. Za svaku ćeliju u rasponu D1: D5 odredite rang broja.

Rezultat bi trebao biti dobiven u ćelijama E1:E5. Funkcija rangiranja prvo se unosi u ćeliju E1, zatim se kopira za sve ćelije do E5.

Akcije korak po korak Čarobnjaci funkcija u ćeliju E1 unesite formulu:

=rank(D1; $ D$1:$ D$5) - skupovi znakova $ apsolutne adrese, tako da se raspon ćelija ne mijenja prilikom kopiranja.

Nakon kopiranja formule za sve ćelije do E5, dobivamo rangove za svaku vrijednost u rasponu. Rang broja s maksimalnom vrijednošću u rasponu D1:D5 bit će 1, a s minimalnom –5.

Funkcije predviđanja

trend (poznate x-vrijednosti, poznate y-vrijednosti, nova y-vrijednost) - grupa statističkih funkcija

Nova vrijednost_X vraća se u trenutnu ćeliju, izračunata na temelju poznatih vrijednosti. Izvodi se linearna aproksimacija.

rast (poznate x-vrijednosti; poznate y-vrijednosti; nova y-vrijednost) - grupa statističkih funkcija.

Nova vrijednost_X vraća se u trenutnu ćeliju, izračunata na temelju poznatih vrijednosti. Provodi se eksponencijalna aproksimacija Djelovanje funkcije je slično funkciji trend, samo se izračun izvodi u skladu s eksponencijalnim trendom.

Funkcije za rad s matricama

mobr(niz)- skupina matematičkih funkcija.

Vraća inverznu matricu matrice pohranjene u nizu u odabranom rasponu.

Niz je numerički niz s jednakim brojem redaka i stupaca. Niz se može navesti kao raspon ćelija, kao što je A1:C3, ili kao naziv raspona ili polja. Ako je bilo koja ćelija u nizu prazna ili sadrži tekst, ili ako niz ima nejednak broj redaka i stupaca, funkcija MOBR vraća vrijednost pogreške #VALUE!.

mopr(niz) - skupina matematičkih funkcija.

Vraća determinantu matrice (matrica je pohranjena u nizu).

Determinanta matrice je broj izračunat iz vrijednosti elemenata niza. Niz je numerički niz s jednakim brojem redaka i stupaca. Ako je bilo koja ćelija u nizu prazna ili sadrži tekst, funkcija MOPRED vraća vrijednost pogreške #VALUE!. MOPRED također vraća vrijednost pogreške #VALUE! ako niz ima nejednak broj redaka i stupaca.

1) 10 nedavno korištenih, 2) Potpuni abecedni popis, 3) Financijski, 4) Datum i vrijeme, 5) Matematički, 6) Statistički, 7) Logički, 8) Tekst, 9) rad s bazom podataka, 10) provjera vrijednosti

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 navedeno, koristit će se ćelije navedene parametrom RANGE.

Primjer 1

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

  1. Razmotrimo 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 navedete 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.

Stvoriti nova datoteka klikom na gumb Kreiraj na alatnoj traci za brzi pristup. Preimenujmo List 1 u Analiza troškova. Razvijmo tablicu za analizu investicijskih troškova.

Slika 23. Analiza troškova ulaganja

Vježba: Izračunajte stupac troškova pomoću formule. Za stupce Jedinična cijena i Trošak koristite format valute.

U ćeliji F12 izračunajte ukupne investicijske troškove. To se može učiniti na 2 načina:

1 metoda:

Aktivirajte ćeliju F12;

Unesite formulu u nju: =F5+F6+F7+F8+F9+F10+F11 i pritisnite Enter.

2 Metoda pomoću funkcije:

Funkcija u MS Excelu je formula u kojoj su određene matematičke operacije zamijenjene nazivima funkcija. Na primjer, za zbrajanje više vrijednosti upotrijebite funkciju SUM.

Zamijenimo uobičajenu formulu u ćeliji F12 formulom koja koristi SUM:

Vježba: Odredite maksimalnu cijenu investicijskih troškova. Za ovo:

Aktivirajte ćeliju u kojoj želite dobiti rezultat (F13);

Desno od gumba Iznos u grupi Uređivanje na kartici Dom kliknite padajuću strelicu funkcije i odaberite funkciju Maksimum(odabire najveću vrijednost iz odabranog raspona);

Formula = MAX(F5:F11) pojavit će se u ćeliji F13, ali F12 mora biti isključen iz ovog raspona. Postavite pokazivač miša u obliku bijelog podebljanog križa preko sredine prve ćelije u rasponu (F5), povucite odabir na ćeliju F11;

Pritisni enter.

Vježba: Odredite minimalnu i prosječnu vrijednost investicijskih troškova.

Funkcija Minimum (=MIN)– identificira najmanju vrijednost raspona;

Funkcija Prosječna vrijednost (=AVERAGE)– izračunajte prosječnu vrijednost raspona, tj. zbraja sve elemente i dijeli s brojem elemenata u rasponu zbrajanja.

Funkcija "Ako"

Uprava poduzeća, nakon što je razmotrila analizu investicijskih troškova, ovog je mjeseca odlučila platiti samo one stavke čiji je trošak manji od 45.000 rubalja.

Dodajmo u tablicu stupac "Plaćanje" u kojem morate navesti:

Ako je trošak veći od 45.000 rubalja. – “Odgoditi”;

Ako je trošak manji od 45.000 rubalja. - "U plaćanju."

Aktivirajte ćeliju G5, otvorite karticu Formule, pritisni gumb Funkcija umetanja.

U dijaloškom okviru odaberite kategoriju mozgalica, na popisu funkcija – AKO.

Slika 24. IF funkcija

Proširite vrijednost dobivenu u ćeliji G5 do kraja tablice (do G11).

Korištenje višestrukih uvjeta pri korištenju funkcije IF

Idite na drugi list svoje radne bilježnice i preimenujte ga u Plata. Unesite sljedeće podatke:

Slika 25. Primjer izračuna bonusa za količinu prodaje

Funkcija IF sastoji se od sljedećih elemenata: naziv funkcije, uvjet, vrijednosti ako je uvjet istinit, vrijednosti ako je uvjet lažan. Unos funkcije u ćeliju izgleda ovako:

If(uvjet; vrijednost ako su uvjeti istiniti; vrijednost ako su uvjeti lažni)

Razmotrimo primjer kada postoje dva uvjeta: ako je obujam prodaje zaposlenika veći od 50.000 rubalja, tada će bonus biti 10% obujma prodaje, inače 5%.

Ovaj će unos izgledati ovako za naš primjer:

Slika 26. Primjer izračuna bonusa za količinu prodaje uz jedan uvjet

Razmotrimo drugi primjer, kada će se bonus zaposlenicima za količine prodaje izračunati na sljedeći način:

Ako je zaposlenik prodao robu za iznos veći ili jednak 90.000 rubalja, njegov bonus će biti 10% količine prodaje, ako je količina prodane robe veća ili jednaka 30.000 rubalja, bonus će biti 5% količine prodaje , ako je iznos manji od 30 000 rubalja, bonus će biti 3%.

Slika 27. Primjer izračuna bonusa za količinu prodaje uz nekoliko uvjeta

U ovom slučaju postoji nekoliko uvjeta i unos funkcije će izgledati ovako:

Uvjetno oblikovanje

Uvjetno oblikovanje omogućuje vam da ćeliji date različite formate pod različitim uvjetima. Na primjer, punjenje ćelija čiji je obujam prodaje veći od 30.000 rubalja. mora biti crvena, manje od 30.000 rubalja. – žuto.

Odaberite raspon C2:C5;

Na kartici Dom u grupa stilova kliknite na gumb Uvjetno oblikovanje i odaberite tim Pravila za odabir ćelija...Ostala pravila.

Postavite format, ako je vrijednost ćelije veća od 30 000, kliknite U redu.

Slika 28. Uvjetno oblikovanje

Izvršite istu radnju i postavite format za ćelije čija je vrijednost manja od 30 000 rubalja.

Isprobajte naredbe: Histogrami, ljestvice boja i skupovi ikona od točke "Uvjetno oblikovanje"

Kako biste uklonili format koji je primijenjen tijekom uvjetnog oblikovanja, morate kliknuti gumb Uvjetno oblikovanje...Ukloni pravila.

Obračun otplate kredita

Izračunajmo iznos otplate kredita pomoću PMT funkcije.

Idite na list 2 i preimenujte ga u "Kredit". Unesite početne podatke za izračun. Imajte na umu da ćelija B2 prikazuje godišnji iznos kamate na kredit. U ćeliji B5 nalazi se formula.

Slika 29. Obračun otplate kredita

Za izračun mjesečne rate kredita potrebno je:

    Aktivirajte ćeliju B6;

    Na kartici Formule pritisni gumb Funkcija umetanja. Odaberite u kategoriji Potpuni abecedni popis, a ispod pronađite funkciju PLT.

Slika 30. Umetanje PMT funkcije

U polju Ponuda navesti mjesečnu stopu, tj. B2/B4;

U polju Nper– potrebno je navesti ukupan broj perioda, tj. NA 5;

U polju Ps (sadašnja vrijednost)– morate navesti traženi iznos kredita;

BC – stanje gotovine, koja mora biti ostvarena nakon zadnje uplate, u našem slučaju to je 0;

Tip– 0 – plaćanja se vrše na kraju razdoblja, 1 – plaćanja se vrše na početku razdoblja.

Rezultat koji dobijete mora imati predznak minus, jer Radi se o isplatama, a ne o novčanim primicima.

Apsolutno i relativno adresiranje

Idite na list 3 u radnoj bilježnici i imenujte ga Potrošnja vode. Unesite podatke o očitanju brojila izradom tablice:

Slika 31. Primjer tablice

Pažnja! Vrijednosti troškova za m3 hladne i tople vode moraju biti smještene u ćelije odvojene od teksta, jer oni će se koristiti u formulama za izračun dospjelog iznosa.

Potrošnja vode izračunava se kao razlika između očitanja brojila tekućeg i prethodnog mjeseca:

Aktivirajte D9, unesite formulu: =B9-B8 i pritisnite Enter.

Koristite točku automatskog popunjavanja za izračun potrošnje hladne vode za preostale mjesece.

Izračunajte na isti način Potrošnja Vruća voda .

Iznos koji treba platiti za HB u siječnju izračunava se kao umnožak količine potrošene vode i troška m3. Formula za naš primjer:

    Aktivirajte ćeliju F9;

    Unesite formulu: =D9*E4 i pritisnite Enter%

    Pokušajmo ispuniti formulu pomoću točke samodovršavanja:

Vratite se na ćeliju F9, pomaknite miš u donji desni kut ćelije, uhvatite pokazivač miša u obliku crnog križa, držite lijevu lijevu tipku miša i povucite formulu do kraja tablice.

Slika 32. Potreba za korištenjem apsolutnog adresiranja

Uvjerite se da automatsko dovršavanje nije radilo, shvatimo zašto:

Aktivirajte ćeliju F9, formula bi se trebala prikazati u traci formule: =D9*E4;

Aktivirajte ćeliju F10. Excel je pomoću automatskog popunjavanja promijenio formulu i sada izračunava vrijednost ćelije F10 kao =D10*E5. To jest, sve ćelije koje se koriste u formuli kada se formula kopira s automatskim popunjavanjem prema dolje također se pomiču prema dolje. U našem slučaju potrebno je pomicanje stupca D prema dolje jer za svaki mjesec postoji različita vrijednost potrošnje vode, ali kretanje duž stupca E nije potrebno jer morate pomnožiti s E4.

Riješenje:

    Vratite se na ćeliju F9, pogledajte traku formule: =D9*E4.

    U formuli morate pronaći ćeliju koja se ne bi trebala mijenjati kada se kopira. Ovo je E4.

    U traci s formulama postavite kursor u formulu između E i 4. Zatim pritisnite tipku F4 na tipkovnici, formula će izgledati kao =D*$E$4. Za Excel, znakovi $ znače zamrzavanje ove ćelije ili apsolutno adresiranje; kada kopirate ovu formulu s točkom automatskog popunjavanja, ova ćelija se neće promijeniti.

    Pritisnite Enter, vratite se na ćeliju F9 i povucite prema dolje točku automatskog popunjavanja. Odaberite ćeliju F10 - obratite pozornost na formulu.

Vježba: Ispunite stupac Iznos koji se plaća za GW koristeći apsolutno adresiranje i stupac Ukupno plativo– kao zbroj stupaca F i G.

Spremite datoteku u svoju mapu pod imenom "Funkcije".

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, sadrži cijene proizvoda šoping centar. 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 bilo gdje slobodno mjesto stolovi.
  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.

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;"")


i kopirajte ga u raspon B2:B5. Ova srednja formula u dodatnom stupcu "uklonit će" nule iz izvornih podataka, zamjenjujući ih praznim nizom ""

U C1 upiši formulu

Vratit će željeni rezultat - broj 3

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


morate koristiti formulu

IF(A1>0;A1;"")


2. Rješavanje s formulom polja
Da biste riješili ovaj problem, upotrijebite ovu formulu polja:

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, onda na kraju dobijemo željeni broj - broj 3

Ako u rasponu postoje negativni brojevi, tada umjesto formule

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


morate koristiti formulu

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


3. Rješenje s jednostavnom formulom
Koristite ovu formulu:

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