În aproape orice birou există un fișier pe care cineva îl actualizează de mână în fiecare săptămână. Prețurile se caută unul câte unul în lista de prețuri, adresele de email se copiază pe rând într-un mesaj, clienții care se repetă se găsesc cu ochiul. Nimic din toate astea nu e greu, dar se duce o după-amiază, iar fiecare pas făcut de mână e încă o ocazie să copiați rândul greșit.
Pentru cele mai multe dintre aceste treburi Excel are deja o formulă. Mai jos sunt cinci, fiecare cu un mic exemplu, cu ce rezultat dă și cu greșeala care apare cel mai des. Datele din exemple sunt inventate.
Înainte să începeți: versiunea, numele funcțiilor și separatorul
- XLOOKUP, UNIQUE și FILTER există în Excel 2021, Excel 2024, Microsoft 365 și Excel pentru web. În Excel 2019 și în versiunile mai vechi nu există, iar celula arată
#NAME?. - TEXTJOIN există din Excel 2019, IFERROR din Excel 2007.
- Toate cele cinci funcționează și în Google Sheets.
- Dacă Excel-ul Dvs. e în rusă sau în altă limbă, numele funcțiilor sunt traduse: XLOOKUP devine ПРОСМОТРX, IFERROR devine ЕСЛИОШИБКА. Scrieți primele litere și alegeți funcția din lista pe care v-o propune Excel.
- În exemple, argumentele sunt despărțite prin punct și virgulă, așa cum le cere Excel cu setările regionale de pe cele mai multe calculatoare din Moldova. Dacă Excel-ul Dvs. așteaptă virgulă, vă va spune că formula are o problemă; înlocuiți punctul și virgula cu virgulă.
XLOOKUP: prețul după codul produsului, din altă foaie
Într-o comandă sunt doar codurile produselor, iar prețurile stau în altă foaie. Cineva caută fiecare cod și copiază prețul.
Foaia „Produse” are codul în coloana A, denumirea în B și prețul în lei în C:
- P-101, hârtie A4 (500 de coli), 85
- P-102, toner pentru imprimantă laser, 640
- P-103, biblioraft, 32
În foaia comenzii, codurile încep din A2. În B2 scrieți:
=XLOOKUP(A2; Produse!A:A; Produse!C:C; "lipsă")
Pentru P-102 celula arată 640. Pentru un cod care nu e în lista de prețuri, de pildă P-150, apare „lipsă” în loc de #N/A. Trageți formula în jos pe coloană și fiecare rând își primește prețul.
Spre deosebire de VLOOKUP, nu mai numărați coloanele, iar coloana cu prețul poate sta oriunde, chiar și în stânga codului. XLOOKUP caută potrivirea exactă, dacă nu-i cereți altceva.
Greșeala frecventă. Un cod pe care îl vedeți în lista de prețuri iese totuși „lipsă”. De obicei, unul dintre cele două are un spațiu la sfârșit sau codul e număr într-o foaie și text în cealaltă. Verificați cu =LEN(A2): dacă lungimea e mai mare decât numărul de caractere pe care le vedeți, sunt spații în plus, iar funcția TRIM le șterge.
TEXTJOIN: o coloană întreagă într-o singură celulă
Un mesaj trebuie trimis tuturor celor dintr-o listă, iar adresele sunt într-o coloană. Dacă le copiați pe rând în câmpul destinatarului, durează, și mereu scapă câte una.
Coloana B, de la rândul 2:
- ana@exemplu.md
- office@firma-a.md
- (celulă goală)
- contabil@firma-b.md
=TEXTJOIN(", "; TRUE; B2:B50)
Primiți o singură celulă cu ana@exemplu.md, office@firma-a.md, contabil@firma-b.md, gata de lipit în câmpul destinatarului. Primul argument e ce se pune între valori, aici virgulă și spațiu, iar TRUE îi spune să sară peste celulele goale. Dacă mesajul pleacă la clienți, lipiți lista în câmpul Bcc, ca să nu vadă unii adresele altora.
Greșeala frecventă. Cu FALSE în loc de TRUE, fiecare celulă goală lasă o virgulă dublă, iar unele programe de poștă refuză toată lista. În plus, într-o celulă încap cel mult 32 767 de caractere; peste această limită TEXTJOIN dă #VALUE!.
UNIQUE: lista clienților fără dubluri
În registrul de vânzări e câte un rând pentru fiecare factură, așa că același client apare de zece ori. Ca să aibă lista clienților, cineva copiază coloana și șterge repetările, iar luna viitoare o ia de la capăt.
Coloana C, de la rândul 2 la 500:
- Alfa SRL
- Beta Grup
- Alfa SRL
- Gamma Trade
- Beta Grup
=UNIQUE(C2:C500)
Primiți Alfa SRL, Beta Grup, Gamma Trade, fiecare o singură dată, în ordinea în care apar în coloană. Scrieți formula într-o celulă, iar rezultatele umplu singure celulele de dedesubt (în engleză acest lucru se numește spill). Adăugați un client nou în interval și lista se actualizează singură. Pentru ordine alfabetică, puneți-o în SORT: =SORT(UNIQUE(C2:C500)).
Greșeala frecventă. #SPILL!. Formula are nevoie de celule goale sub ea, iar o singură valoare rămasă acolo blochează tot rezultatul. Goliți celulele sau mutați formula. Încă două lucruri: UNIQUE nu corectează scrierea, deci „Alfa SRL” și „SRL Alfa” rămân doi clienți, iar rândurile goale din interval apar ca un 0 la sfârșitul listei.
FILTER: doar facturile de peste 1 000 de lei
Directorul vrea să vadă doar facturile mari. Cineva pune un filtru, copiază rândurile rămase într-o foaie nouă și repetă totul de fiecare dată când vin facturi noi.
Facturile sunt în coloanele de la A la D (număr, dată, client, sumă în lei):
- F-0145, 03.11, Alfa SRL, 850
- F-0146, 04.11, Beta Grup, 2400
- F-0147, 05.11, Gamma Trade, 1150
- F-0148, 06.11, Alfa SRL, 600
=FILTER(A2:D500; D2:D500>1000)
Primiți rândurile cu F-0146 și F-0147, toate patru coloanele, începând din celula cu formula. O factură nouă de peste 1 000 de lei apare singură în rezultat. Condiția poate fi orice comparație: D2:D500>=5000 sau C2:C500="Alfa SRL".
Greșeala frecventă. Când nicio factură nu îndeplinește condiția, FILTER dă #CALC!. Al treilea argument spune ce să apară în acest caz: =FILTER(A2:D500; D2:D500>1000; "nimic"). Și aveți grijă ca intervalul condiției să aibă tot atâtea rânduri cât tabelul: A2:D500 cu D2:D400 dă eroare.
IFERROR: un raport fără #N/A și #DIV/0!
În raportul pentru director e o coloană cu prețul mediu, iar unde cantitatea e zero sau codul nu s-a găsit, celula arată #DIV/0! sau #N/A. Înainte de tipărire, cineva le șterge de mână.
Vânzările în lei sunt în coloana B, bucățile vândute în C:
- 12000, 40
- 3500, 0
- 7200, 30
=IFERROR(B2/C2; 0)
Pe primul rând iese 300, pe al doilea 0 în loc de #DIV/0!, pe al treilea 240. Al doilea argument e ce apare în locul erorii: 0, o celulă goală ("") sau un cuvânt scurt.
Greșeala frecventă. IFERROR ascunde toate erorile, și pe cele pe care ar trebui să le vedeți. Dacă formula trimite la o foaie ștearsă (#REF!) sau are numele funcției scris greșit (#NAME?), celula arată pur și simplu 0 și raportul pare în regulă. Verificați întâi că formula funcționează și abia apoi o puneți în IFERROR. Gândiți-vă și ce face 0 cu totalurile: media unei coloane în care erorile au devenit zerouri iese mai mică. De multe ori "" e alegerea mai corectă, pentru că AVERAGE nu ia în calcul astfel de celule.
Se pot combina
Formulele se pot pune una în alta. Toate numerele facturilor de peste 1 000 de lei, într-o singură celulă:
=TEXTJOIN(", "; TRUE; FILTER(A2:A500; D2:D500>1000))
Lista sortată a clienților de mai sus se face la fel: =SORT(UNIQUE(C2:C500)).
Pe scurt
- O valoare din alt tabel, după cod: XLOOKUP.
- O coloană într-o singură celulă: TEXTJOIN.
- O listă fără dubluri: UNIQUE.
- Doar rândurile care îndeplinesc o condiție: FILTER.
- Fără coduri de eroare în raport: IFERROR, cu grijă.
Când tabelul devine baza de date a firmei
Formulele de mai sus vă ajută cât timp un singur om se ocupă de un singur fișier. Lucrurile se schimbă când tabelul ajunge baza de date a firmei: trei oameni lucrează în copii ale aceluiași fișier, cineva trimite pe email „clienti_final_2.xlsx” și nimeni nu mai știe în care copie e plata de azi-dimineață. Aici nicio formulă nu ajută, pentru că întrebarea e cine a schimbat ce și unde.
Atunci merită un CRM sau o mică automatizare: clienții, facturile și istoricul stau într-un singur loc, fiecare vede partea lui, iar listele de mai sus devin un filtru salvat sau un raport. Am scris despre cum mutați baza de clienți din Excel în CRM fără să pierdeți nimic pe drum. Dacă vreți să discutăm despre tabelele Dvs., vedeți pagina despre sisteme CRM sau scrieți-ne.