Cândva, să scriu singură o formulă în Excel a fost ceva incredibil pentru mine. Și chiar, în ciuda faptului că a trebuit de multe ori să lucrez în acest program, nu am tastat altceva decât text...
După cum sa dovedit, majoritatea formulelor nu sunt complicate și sunt ușor de lucrat, chiar și pentru un utilizator începător de computer. În articol, aș vrea să vă dezvălui cele mai necesare formule cu care trebuie să lucrați adesea...
Și așa, să începem...
Toate acțiunile din articol vor fi afișate în Excel versiunea 2007.
După pornirea programului Excel, apare o fereastră cu multe celule - tabelul nostru. Caracteristica principală a programului este că poate număra (ca un calculator) formulele pe care le scrieți. Apropo, puteți adăuga o formulă la fiecare celulă!
Formula trebuie să înceapă cu semnul „=". Aceasta este o condiție obligatorie. Apoi, scrieți ceea ce trebuie să calculați: de exemplu, „=2+3” (fără ghilimele) și apăsați tasta Enter - ca urmare, veți vedea că rezultatul „5” a apărut în celulă. Vedeți captura de ecran de mai jos.
Important! În ciuda faptului că numărul „5” este scris în celula A1, acesta este calculat conform formulei („=2+3”). Dacă scrieți pur și simplu „5” în celula alăturată, atunci când mutați cursorul în această celulă - în editorul de formule, linia de sus, Fx) - veți vedea numărul simplu „5”.
Și acum imaginați-vă că într-o celulă puteți scrie nu doar valoarea 2+3, ci și numerele celulelor ale căror valori trebuie adăugate. Să presupunem că „=B2+C2”.
Desigur, trebuie să existe niște numere în B2 și C2, altfel Excel ne va arăta rezultatul egal cu 0 în celula A1.
Și încă o notă importantă...
Când copiați o celulă care are o formulă în ea, cum ar fi A1, și o lipiți într-o altă celulă, aceasta nu se copiazăvaloarea „5”, și formula în sine!
Mai mult, formula se va schimba direct proporțional: adică dacă A1 este copiat în A2, formula din celula A2 va fi egală cu „=B3+C3”. Excel vă schimbă automat formula: dacă A1=B2+C2, atunci logic, A2=B3+C3 (toate numerele au crescut cu 1).
Rezultatul, apropo, este A2=0, deoarece celulele B3 și C3 nu sunt specificate, ceea ce înseamnă că sunt egale cu 0.
În acest fel, puteți scrie formula o dată, apoi copiați-o în toate celulele coloanei dorite - iar Excel însuși va face calculul în fiecare rând al tabelului dvs.!
Dacă nu doriți ca B2 și C2 să se schimbe atunci când copiați și să fie întotdeauna legat de aceste celule, atunci adăugați doar un semn „$”. Un exemplu este mai jos.
În acest fel, oriunde copiați celula A1, se va referi întotdeauna la celula legată.
2. Adăugarea valorilor în rânduri (formula SUM și SUMIFS)
Puteți, desigur, să adăugați fiecare celulă făcând formula A1+A2+A3, etc. Dar pentru a nu suferi așa, există o formulă specială în Excel care va aduna toate valorile din celule. pe care îl alegi tu!
Să luăm un exemplu simplu. Există mai multe nume de produse în stoc și știm cât de mult din fiecare produs separat, în kg. este in stoc. Să încercăm să calculăm cât este în kg. marfă în depozit.
Pentru a face acest lucru, mergeți în celula în care va fi afișat rezultatul și scrieți formula: =SUM(C2:C5)”. Vedeți captura de ecran de mai jos.
Ca rezultat, toate celulele din interval vor fi însumate și veți vedea rezultatul.
2.1. Compoziție cu o condiție (cu condiții)
Și acum să ne imaginăm că avem anumite condiții, adică trebuie să adunăm nu toate valorile din celule (Kg, în stoc), ci doar anumite, să zicem, cu un preț (1 kg) mai putin de 100.
Există o formulă excelentă pentru acest „ SUMIFS“.Un exemplu urmat imediat de o explicație a fiecărui simbol din formulă.
=SUMIFS(C2:C5;B2:B5;”, unde:
C2:C5– coloana (celule) care vor fi adăugate;
B2:B5– coloana pe care se va verifica condiția (adică prețul, de exemplu, este mai mic de 100);
“ este condiția în sine, rețineți că condiția este scrisă între ghilimele.
Nu este nimic complicat în această formulă, principalul lucru este să respectați corespondența C2:C5;B2:B5 - corect; C2:C6;B2:B5 este incorect. Adică, intervalul de însumare și intervalul de condiții trebuie să fie proporționale, altfel formula va returna o eroare.
Important! Pot fi multe condiții pentru suma, adică puteți verifica nu după prima coloană, ci cu 10 deodată, punând multe condiții.
3. Numărarea numărului de linii care îndeplinesc condițiile (formula COUNTIFS)
O sarcină destul de comună este calcularea sumei valorilor celulelor și a numărului de astfel de celule care îndeplinesc anumite condiții. Uneori sunt prea multe condiții.
Și așa... să începem.
În același exemplu, vom încerca să numărăm numărul de nume de produse cu un preț mai mare de 90 (dacă te uiți în jur, poți spune că există 2 astfel de produse: mandarine și portocale).
Pentru a număra mărfurile în celula dorită, scrieți următoarea formulă (vezi mai sus):
=COUNTIFS(B2:B5;”>90″)unde:
B2:B5– intervalul care va fi verificat, conform condiției stabilite de noi;
„>90”– condiția în sine este cuprinsă între ghilimele.
Acum să încercăm să facem exemplul nostru puțin mai complicat și să adăugăm o factură conform încă o condiție: cu un preț mai mare de 90 + cantitatea din stoc este mai mică de 20 kg.
Formula ia forma:
=COUNTIFS(B2:B6;”>90″;C2:C6;”
Totul este lăsat aicila fel, cu excepția încă o condiție ( C2:C6;”). Apropo, pot fi multe astfel de condiții!
Este clar că nimeni nu va scrie astfel de formule pentru un tabel atât de mic, dar pentru un tabel cu câteva sute de rânduri, este cu totul altă chestiune. De exemplu, acest tabel este mai mult decât vizual.
4. Căutarea și înlocuirea valorilor dintr-un tabel în altul (formula BVR)
Să ne imaginăm că am primit un tabel nou cu noi etichete de preț pentru produs. Este bine dacă există 10-20 de nume - le poți „reumple” pe toate manual. Și dacă există sute de astfel de nume? Este mult mai rapid dacă Excel a găsit independent nume care se potrivesc dintr-un tabel în altul și apoi a copiat noile etichete de preț în tabelul nostru vechi.
Formula BVReste utilizată pentru această problemă. La un moment dat, el însuși „se zguduia” cu formule logice „DAC” până a întâlnit acest lucru minunat!
Și așa, să începem...
Iată exemplul nostru + un tabel nou cu etichete de preț. Acum trebuie să înlocuim automat noile etichete de preț din noul tabel în cel vechi (noile etichete de preț sunt roșii).
Plasați cursorul în celula B2 - adică în prima celulă în care trebuie să schimbăm automat eticheta de preț. În continuare, scriem formula, ca în captura de ecran de mai jos (după captura de ecran, va exista o explicație detaliată a acesteia).
=VPR(A2;$D$2:$E$5;2), unde
A2este valoarea pe care o vom căuta pentru a lua o nouă etichetă de preț. În cazul nostru, căutăm cuvântul „mere” în noul tabel.
$D$2:$E$5– selectați complet noul nostru tabel (D2:E5, selecția merge din colțul din stânga sus în dreapta jos în diagonală), adică unde se va efectua căutarea. Semnul „$” din această formulă este necesar, astfel încât atunci când copiați formula în alte celule, D2:E5 nu se va schimba!
Important!Căutarea cuvântului „mere” va fi efectuată numai în primulcoloana tabelului selectat, în acest exemplu, „mere” vor fi căutate în coloana D.
2– Când este găsit cuvântul „mere”, funcția trebuie să știe din ce coloană a tabelului selectat (D2:E5) să copieze valoarea dorită. În exemplul nostru, copiați din coloana 2 (E), deoarece am căutat în prima coloană (D). Dacă tabelul dvs. de căutare dedicat va fi format din 10 coloane, atunci prima coloană este căutată și de la 2 la 10 coloane puteți selecta să copiați.
Astfel încâtformula =VPR(A2;$D$2:$E$5;2)să înlocuiască valori noi și pentru alte nume de produse - doar copiați-o în alte celule ale coloana cu etichete de preț pentru produse (în exemplul nostru, copiați în celulele B3:B5). Formula va căuta și copia automat valoarea din coloana tabelului de care aveți nevoie.
5. Concluzie
În articol, am luat în considerare elementele de bază ale lucrului cu Excel, începând cu modul de a începe să scrieți formule. Am dat exemple cu cele mai comune formule cu care majoritatea oamenilor care lucrează în Excel trebuie să lucreze adesea.
Sper că cineva va avea nevoie de exemplele demontate și va ajuta să-și accelereze munca. Experimente bune!
PS
Si ce formule folositi, este posibil sa simplificati cumva formulele date in articol? De exemplu, pe computerele slabe, când unele valori sunt modificate în tabele mari în care calculele sunt făcute automat, computerul se blochează pentru câteva secunde, recalculând și arătând rezultate noi...