Kako napisati formulu u Excelu? Trening Najvažnije formule

Dobar dan

Jednom davno, pisanje formule u Excelu bilo je nešto neverovatno za mene. Čak i unatoč činjenici da sam često morao raditi u ovom programu, nisam ništa napisao osim teksta ...

Kako se ispostavilo, većina formula nije ništa komplikovano i lako je raditi s njima, čak i za korisnika početničkog računala. U članku samo želim da otkrijem najnužnije formule kojima se često mora raditi ...

I tako, počnimo ...

1. Osnovne operacije i osnove. Excel trening.

Sve radnje u članku će biti prikazane u Excel verziji 2007.

Nakon pokretanja programa Excel - pojavljuje se prozor sa mnogo ćelija - naš stol. Glavna karakteristika programa je da može da čita (kao kalkulator) vaše formule koje pišete. Inače, svakoj ćeliji možete dodati formulu!

Formula mora početi znakom "=". To je preduvjet. Zatim pišete ono što vam je potrebno za izračunavanje: na primjer, "= 2 + 3" (bez navodnika) i pritisnite Enter - kao rezultat ćete vidjeti da se rezultat pojavio u ćeliji "5". Pogledajte sliku ispod.

Važno je! Uprkos činjenici da se broj "5" piše u ćeliji A1, izračunava se po formuli ("= 2 + 3"). Ako u sledećoj ćeliji pišete “5” sa tekstom - onda kada lebdite kursor na ovu ćeliju - u editoru formula (linija iznad, Fx) - videćete prost broj "5".

Sada zamislite da u ćeliji možete napisati ne samo vrijednost od 2 + 3, već i brojeve ćelija čije vrijednosti želite dodati. Pretpostavimo da je "= B2 + C2".

Naravno, treba da postoje neki brojevi u B2 i C2, inače će nam Excel pokazati u ćeliji A1 rezultat jednak 0.

I još jedna važna napomena ...

Kada kopirate ćeliju u kojoj se nalazi formula, na primer A1 - i nalepite je u drugu ćeliju, ne kopira se vrednost "5", već sama formula!

Štaviše, formula će se direktno promijeniti: ako se A1 kopira u A2 - tada će formula u ćeliji A2 biti jednaka "= B3 + C3". Excel automatski menja vašu formulu: ako je A1 = B2 + C2, onda je logično da je A2 = B3 + C3 (svi brojevi su povećani za 1).

Rezultat je, inače, A2 = 0, jer ćelije B3 i C3 nisu postavljene i stoga su jednake 0.

Na taj način možete jednom napisati formulu, a zatim je kopirati u sve ćelije željene kolone - a Excel će sam izračunati u svakom redu vašeg stola!

Ako ne želite da se B2 i C2 mijenjaju prilikom kopiranja i uvijek su priključeni na te ćelije, samo im dodajte ikonu "$". Primer ispod.

Dakle, gdje god kopirate ćeliju A1, ona će se uvijek odnositi na povezane ćelije.

2. Dodavanje vrijednosti u nizove (formula SUM i SUMMESLIMN)

Možete, naravno, dodati svaku ćeliju, čineći formulu A1 + A2 + A3, itd. Ali da ne bi toliko patili, u Excelu postoji posebna formula koja će zbrojiti sve vrednosti u ćelijama koje odaberete!

Uzmite jednostavan primer. Postoji nekoliko stvari na zalihi, a znamo koliko je svaka stavka u kg. je na lageru. Pokušajmo izračunati koliko u kg. teret na lageru.

Da biste to uradili, idite u ćeliju u kojoj će se prikazati rezultat i upišite formulu: "= SUM (C2: C5)". Pogledajte sliku ispod.

Kao rezultat, sve ćelije u izabranom opsegu će se sabrati i videćete rezultat.

2.1. Dodatak uz uvjet (s uvjetima)

Sada zamislite da imamo određene uslove, tj. nije potrebno dodati sve vrijednosti u ćelije (Kg, na zalihama), već samo one koje su definirane, recimo, s cijenom (1 kg) manje od 100.

Za ovo postoji divna formula "SUMMESLIMNOdmah primer, a zatim objašnjenje svakog simbola u formuli.

= SUMMESLIMN (C2: C5; B2: B5; "<100")gdje:

C2: C5 - Ta kolona (te ćelije), koja će biti dodata;

B2: B5 - kolona na kojoj će se provjeravati stanje (tj. cijena, na primjer, manja od 100);

"<100" - uslov sam, imajte na umu da je uslov napisan u navodnicima.

Ne postoji ništa komplikovano u ovoj formuli, glavna stvar je da se poštuje proporcionalnost: C2: C5, B2: B5 je ispravna; C2: C6; B2: B5 je pogrešan. Ie opseg zbrajanja i raspon uvjeta moraju biti proporcionalni, inače će formula vratiti pogrešku.

Važno je! Može biti mnogo uslova za iznos, tj. Možete provjeriti ne od 1. stupca, već od 10 odjednom, navođenjem skupa uvjeta.

3. Brojanje redova koji zadovoljavaju uslove (formula COUNTIFSLIMN)

Vrlo čest zadatak je izračunati ne sumu vrijednosti u ćelijama, već broj takvih ćelija koje zadovoljavaju određene uvjete. Ponekad, puno uslova.

I tako ... počnimo.

U istom primeru pokušaćemo da izračunamo količinu naziva proizvoda sa cenom većom od 90 (ako pogledate oko sebe, možete reći da postoje dva takva proizvoda: mandarine i narandže).

Da bismo prebrojali robu u željenoj ćeliji, napisali smo sledeću formulu (vidi gore):

= DRŽAVA (B2: B5; "> 90")gdje:

B2: B5 - opseg nad kojim će se proveravati prema stanju koje smo postavili;

">90" - uslov je u navodnicima.

Sada ćemo pokušati malo zakomplicirati naš primjer i dodati još jednu fakturu prema još jednom uvjetu: s cijenom većom od 90 + količina na zalihama je manja od 20 kg.

Formula dobija oblik:

= COUNTIFS (B2: B6; "> 90"; C2: C6; "<20")

Ovdje sve ostaje isto, osim još jednog uvjeta (C2: C6; "<20"). Usput, takvih uslova može biti puno!

Jasno je da za tako malim stolom niko neće napisati takve formule, već za stol od nekoliko stotina redova - to je sasvim druga stvar. Na primer, ova tabela je više nego jasna.

4. Pretraživanje i supstitucija vrijednosti iz jedne tablice u drugu (CDF formula)

Zamislite da nam je došla nova tabela, sa novim cenovnim oznakama za robu. Pa, ako imena 10-20 - i možete ručno "zaboraviti" ih sve. A ako postoje stotine takvih imena? Mnogo brže ako je Excel nezavisno pronašao odgovarajuća imena iz jedne tablice u drugu, a zatim kopirao nove oznake cijena u našu staru tablicu.

Za ovaj zadatak se koristi formula Vpr. U jednom trenutku, on sam "mudro" sa logičkim formulama "IF" još nije upoznao ovu divnu stvar!

I tako, počnimo ...

Evo našeg primera + nova tabela sa oznakama cena. Sada moramo automatski zamijeniti nove oznake cijena iz nove tablice u staru (nove oznake cijena su crvene).

Stavite kursor u ćeliju B2 - tj. u prvoj ćeliji gdje moramo automatski promijeniti cijenu. Zatim formulu napišemo kao na slici ispod (nakon snimka ekrana biće detaljno objašnjenje za nju).

= CDF (A2; $ D $ 2: $ E $ 5; 2)gdje

A2 - vrijednost koju ćemo tražiti kako bismo dobili novu cijenu. U našem slučaju tražimo riječ "jabuke" u novoj tablici.

$ D $ 2: $ E $ 5 - u potpunosti odabiremo našu novu tablicu (D2: E5, selekcija ide od gornje lijeve do donje desne dijagonalno), tj. gdje će se izvršiti pretraga. Znak "$" u ovoj formuli je neophodan tako da se prilikom kopiranja ove formule u druge ćelije - D2: E5 ne mijenja!

Važno je! Pretraživanje riječi "jabuke" će se vršiti samo u prvoj koloni odabrane tablice, u ovom primjeru će se pretraživati ​​"jabuke" u stupcu D.

2 - Kada se pronađe riječ "jabuke", funkcija mora znati iz koje stupca odabrane tablice (D2: E5) kopirati željenu vrijednost. U našem primjeru, kopirajte iz stupca 2 (E), jer u prvoj koloni (D) smo pretražili. Ako se odabrana tablica za pretraživanje sastoji od 10 stupaca, tada će se tražiti prva kolona, ​​a od 2 do 10 stupaca - možete odabrati broj koji želite kopirati.

To formula = CDF (A2; $ D $ 2: $ E $ 5; 2) zameni nove vrednosti za druga imena proizvoda - samo kopiraj u druge ćelije kolone sa oznakama cena proizvoda (u našem primeru kopiraj u ćelije B3: B5). Formula će automatski pretraživati ​​i kopirati vrijednost iz stupca nove tablice koja vam je potrebna.

5. Zaključak

U ovom članku smo pogledali osnove rada sa Excelom kako da počnemo da pišemo formule. Oni su dali primjere najčešćih formula s kojima najčešće rade sa većinom onih koji rade u Excelu.

Nadam se da će primjeri koji su analizirani biti korisni nekome i pomoći će ubrzati njegov rad. Uspešni eksperimenti!

PS

I koje formule koristite, da li je moguće nekako pojednostaviti formule date u članku? Na primjer, na slabim računalima, kada se neke vrijednosti mijenjaju u velikim tablicama, gdje se proračuni obavljaju automatski, računalo se zamrzne na nekoliko sekundi, preračunavajući i prikazujući nove rezultate ...

Pogledajte video: Suspense: Man Who Couldn't Lose Dateline Lisbon The Merry Widow (Novembar 2024).