Kako napisati formulu u Excelu? Trening. Najpotrebnije formule

Dobar dan

Nekada davno sam samostalno pisanje formule u Excel-u bilo nešto nevjerovatno. I iako sam često morao raditi u ovom programu, nisam napunio ništa osim teksta ...

Kako se pokazalo, većina formula nije ništa komplicirano i s njima lako možete raditi čak i za početnika računala. U članku bih, samo, želio otkriti najpotrebnije formule, s kojima najčešće moram raditi ...

Dakle, krenimo ...

1. Osnovne operacije i osnove. Naučite osnove Excela.

Sve radnje u članku bit će prikazane u Excelu 2007.

Nakon pokretanja programa Excel - pojavit će se prozor s mnogim ćelijama - naša tablica. Glavna značajka programa je da može čitati (kao kalkulator) vaše formule koje pišete. Usput, možete dodati formulu u svaku ćeliju!

Formula mora početi znakom "=". To je preduvjet. Zatim napišete ono što trebate izračunati: na primjer, "= 2 + 3" (bez navodnika) i pritisnite tipku Enter - kao rezultat, vidjet ćete da se u ćeliji pojavljuje rezultat "5". Pogledajte snimak ekrana u nastavku.

Važno! Unatoč činjenici da je broj "5" napisan u ćeliji A1, izračunava se formulom ("= 2 + 3"). Ako u sljedeću ćeliju jednostavno napišete "5" u tekstu - onda kada pokažite pokazivač miša iznad te ćelije u uređivaču formula (redak iznad, Fx) - vidjet ćete glavni broj "5".

Sada zamislite da u ćeliju možete napisati ne samo vrijednost 2 + 3, već i brojeve ćelija čije vrijednosti morate dodati. Recimo "= B2 + C2".

Naravno, mora biti nekih brojeva u B2 i C2, inače će nam Excel pokazati u ćeliji A1 rezultat je 0.

I još jedna važna tačka ...

Kad kopirate ćeliju u kojoj postoji formula, na primjer A1 - i zalijepite je u drugu ćeliju - ne kopira se vrijednost "5", već sama formula!

Štaviše, formula će se izmijeniti u izravnom odnosu: tj. ako je A1 kopiran u A2, tada će formula u ćeliji A2 biti "= B3 + C3". Excel automatski mijenja 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, uzgred, u A2 = 0, jer ćelije B3 i C3 nisu definirane i stoga su jednake 0.

Tako možete jednom napisati formulu, a zatim je kopirati u sve ćelije željenog stupca - a sam Excel će izračunati u svakom retku vaše tablice!

Ako ne želite da se B2 i C2 mijenjaju tijekom kopiranja i da su uvijek pričvršćeni na ove ćelije, jednostavno im dodajte ikonu "$". Primjer je u nastavku.

Na ovaj način, gdje god kopirate ćeliju A1, uvijek će se odnositi na povezane ćelije.

2. Dodavanje vrijednosti u redove (SUMM i SUMMESLIMN formule)

Naravno, svaku ćeliju možete dodati dodavanjem formule A1 + A2 + A3 itd. Ali da ne patite, u Excelu postoji posebna formula koja zbraja sve vrijednosti u ćelijama koje odaberete!

Uzmite jednostavan primer. Na lageru je nekoliko vrsta robe, a znamo koliko je svaki proizvod pojedinačno u kg. je na lageru. Pokušajmo izračunati, ali koliko je sve u kg. teret na zalihama.

Da biste to učinili, idite do ćelije u kojoj će se prikazati rezultat i napišite formulu: "= SUM (C2: C5)". Pogledajte snimak ekrana u nastavku.

Kao rezultat, sve ćelije u odabranom rasponu zbrajaju se i vidjet ćete rezultat.

2.1. Dodatak stanju (uz uslove)

Zamislite sada kako imamo određene uvjete, tj. zbrojite ne sve vrijednosti u ćelijama (Kg, na lageru), već samo određene, recimo, s cijenom (1 kg) manjom od 100.

Za to postoji sjajna formula. "SUMMESLIMN". Odmah primjer, a zatim objašnjenje svakog simbola u formuli.

= SUMMES (C2: C5; B2: B5; "<100")gde:

C2: C5 - onaj stupac (one ćelije) koji će se zbrojiti;

B2: B5 - stupac u kojem će se provjeriti uvjet (tj. cijena, na primjer, manja od 100);

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

U ovoj formuli nema ništa komplicirano, glavno je promatrati proporcionalnost: C2: C5; B2: B5 - tačno; C2: C6; B2: B5 - nije u redu. I.e. raspon zbrajanja i raspon uvjeta moraju biti proporcionalni, inače će formula vratiti grešku.

Važno! Za sumu može biti puno uvjeta, tj. Možete provjeriti ne u prvom stupcu, već do 10 odmah, postavljajući puno uvjeta.

3. Prebrojavanje broja redaka koji zadovoljavaju uvjete (formula je COUNTIFLY)

Prilično uobičajen zadatak: izračunati ne zbroj vrijednosti u ćelijama, već broj takvih ćelija koji zadovoljavaju određene uvjete. Ponekad ima puno uslova.

I tako ... krenimo.

U istom primjeru pokušajmo izračunati broj predmeta s cijenom većom od 90 (ako pogledate, možete reći da postoje 2 takva proizvoda: mandarine i naranče).

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

= RAČUNOVODSTVO (B2: B5; "> 90")gde:

B2: B5 - opseg u kojem će se provjeravati, u skladu s uvjetom koji smo postavili;

">90" - sam uvjet je priložen u navodnicima.

Sada pokušajmo malo zakomplicirati naš primjer, pa dodajmo račun u skladu s još jednim uvjetom: s cijenom većom od 90 + količina u skladištu manjoj od 20 kg.

Formula ima oblik:

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

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

Jasno je da nitko neće pisati takve formule za tako malu tablicu, ali za stol od nekoliko stotina redaka, to je potpuno drugačija stvar. Na primjer, ova tablica je više nego vizualna.

4. Pretraživanje i zamjena vrijednosti iz jedne tablice u drugu (formula VLOOKUP)

Zamislite da nam se pridružila nova tablica s novim oznakama cijena proizvoda. Pa, ako je predmeta 10-20, možete ih ručno resetirati. A ako ima stotine takvih predmeta? Mnogo je brže ako se Excel samostalno nađe u odgovarajućim imenima iz jedne tablice u drugu, a zatim kopira nove oznake cijena u našu staru tablicu.

Za takav se zadatak koristi formula VPR. Svojevremeno je bio "mudar" s logičkim formulama "AKO" sve dok nije upoznao ovu divnu stvar!

Dakle, krenimo ...

Evo našeg primjera + nova tablica s oznakama cijena. 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 oznaku cijena. Zatim pišemo formulu kao u donjoj slici (nakon snimke zaslona pojavit će se detaljno objašnjenje iste).

= VLOOKUP (A2; $ D $ 2: $ E $ 5; 2)gde

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

$ D $ 2: $ E $ 5 - potpuno odaberite našu novu tablicu (D2: E5, izbor ide od gornjeg lijevog ugla do donje desne dijagonale), tj. gde će se izvršiti pretraga. Znak "$" u ovoj formuli neophodan je kako se, kada kopirate ovu formulu u druge ćelije - D2: E5 ne mijenja!

Važno! Pretraživanje riječi "jabuke" izvršit će se samo u prvom stupcu vaše odabrane tablice, u ovom će primjeru biti pretraženi "jabuke" u stupcu D.

2 - Kad se pronađe riječ "jabuke", funkcija mora znati iz kojeg stupca odabrane tablice (D2: E5) kopirati željenu vrijednost. U našem primjeru kopiraj iz stupca 2 (E), jer u prvoj koloni (D) koju smo pretražili. Ako će se vaša odabrana tablica za pretraživanje sastojati od 10 stupaca, tada će pretražiti prvi stupac, a od 2 do 10 stupaca - možete odabrati broj koji ćete kopirati.

Za formula = VLOOKUP (A2; $ D $ 2: $ E $ 5; 2) zamijenite nove vrijednosti za druga imena proizvoda - samo kopirajte ih u druge ćelije u stupcu s oznakama cijena proizvoda (u našem primjeru kopirajte 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 smo članku ispitali osnove rada s Excelom, kako započeti pisati formule. Dali su primjere najčešćih formula s kojima većina ljudi koji rade u Excelu često mora raditi.

Nadam se da će rastavljeni primjeri nekome biti korisni i pomoći će ubrzati njegov rad. Dobar eksperiment!

PS

I koje formule koristite? Je li moguće na neki način pojednostaviti formule date u članku? Na primjer, na slabim računarima, kada se neke vrijednosti promijene u velikim tablicama gdje se proračuni automatski izvode, računalo zamrzava na nekoliko sekundi, prepričavajući i pokazujući nove rezultate ...