Inainte de orice indraznesc sa-ti dau un sfat: pe langa invatarea de formule si VBA incearca sa acorzi si ceva timp pentru a intelege cum sa-ti organizezi mai bine datele.
Cum bine spuneai si tu, majoritatea utilizatorilor de Excel de nivel mediu se limiteaza la un tabel cu niste calcule bazate pe formule simple fie pentru ca doar de atat au nevoie, fie pentru ca nu stiu ca se pot face mult mai multe prelucrari folositoare ale datelor.
Exista mai multe tipuri de aplicatii pentru care se foloseste Excel-ul, dar pentru toate regula de baza e sa faci mai intai o analiza simpla a aplicatiei pe care vrei s-o construiesti plecand de la doua intebari:
- de unde plec (ce date am la dispozitie)?
- unde vreau sa ajung (care vor fi prelucrarile finale ale acestor date)?
Intre raspunsurile la aceste doua intrebari sta solutia aplicatiei tale, inclusiv modul de organizare a datelor de care ai nevoie. Daca nu ai nevoie de masive mari de date de prelucrat pentru a-ti atinge scopul final, partea mai delicata a aplicatiei respective rezida in gasirea formulelor necesare unor calcule mai sofisticate (poate), dar daca apliacatia ta se bazeaza in buna masura doar pe prelucrarea unor volume de informatii mai mari, e musai sa te gandesti cum sa-ti organizezi acele date - care sunt sculele pe care le ai la dispozitie pentru aduce acele date in formele de care ai nevoie pentru prelucrarile pe care le doresti. Mai e un lucru important de semnalat aici, dupa parerea mea: multi utilizatori neexperimentati sunt tentati sa transforme baza de date in raport de tiparit. In cazul prelucrarii unor volume de date mai importante acest aspect trebuie transat clar, de la inceput: datele sunt date, iar formele de tiparit sunt extrasele necesare din acele date.
Analiza despre care vorbeam mai sus se continua parcurgand drumul de la punctul de pornire la cel de sosire
in sens invers: se imagineaza formele sub care trebuie prezentate datele in final, iar apoi care e pasul anterior necesar de facut pentru a aduce datele in forma respectiva - si tot asa pana ajung la punctul de pornire cand ar tebui sa am clarificat modul in care e bine sa-mi organizez datele. Dupa cum ai sesizat si tu, datele de prelucrat de obicei au si ele o structura - in cazul tau structura e determinata fizic pe "curse" si de aceea ai optat pentru solutia cursa/foaie de calcul. Daca analizezi putin primul fisier pe care l-am postat la acest subiect vei vedea ca in loc de o foaie de calcul pentru fiecare cursa exista un tabel care tine aceasta evidenta (in foia "Comenzi" - un achivalent al acestei denumiri ar putea fi "Curse"), iar datele de detaliu pentru toate cursele sunt toate intr-o singura foaie ("Detalii_Comenzi"). In afara de usurinta de a prelucra datele intr-o astfel de organizare mai apare si avantajul ca anumite informatii sunt de inregistrat la nivelul "cursei", adica al unui "pachet de date" din foaia "Detalii_Comenzi", fara a mai fi nevoie sa fie repetate in fiecare inregistrare din foaia "Detalii_Comenzi". Folosind tabele (sau doar filtre) pe baza de date din foaia "Detalii_Comenzi" poti vizualiza in orice moment detaliile in legatura cu o anumita cursa, o anumita firma, un anume produs etc. - asta insa numai ca vizualizare. Prelucrarea acestor date este insa usor de realizat pentru ca intotdeuna se cauta in acelasi loc. Ar mai fi multe de spus pe tema asta, dar poate lamurim si alte aspecte din discutiile ce vor urma.
Revenind la formula despre care spuneai ca da eroare, e in primul rand vina mea ca n-am luat in calcul faptul ca denumirile foilor pot contine si spatii - de aici rezulta eroarea.
In esenta formula returneaza o valoare pe care trebuie sa stie unde s-o caute si cum sa o afiseze in functie de rezultatul cautarii.
Corectata, formula arata asa:
- Cod: Selectaţi tot
=IF(LEN(L$10)=0,"",IF(ISNA(MATCH($H$8,INDIRECT("'"&L$10&"'!$D$1:$P$1"),0)),"",INDEX(INDIRECT("'"&L$10&"'!$D$3:$P$50"),MATCH($A11,INDIRECT("'"&L$10&"'!$A$3:$A$50"),0),MATCH($H$8,INDIRECT("'"&L$10&"'!$D$1:$P$1"),0))))
Sunt folosite 6 functii mari si late: IF(), LEN(), ISNA(), INDEX(), MATCH() si INDIRECT(). Testul din primul IF() este legat de existenta unei valori inscrise pe aceeasi coloana in randul 10 - acolo unde trebuie scrise numele foilor de calcul unde trebuie cautata valoarea - daca pe aceeasi coloana, in randul 10 nu e nimic scris, formula returneaza sirul vid (""). LEN() returneaza numarul de caractere dintr-un sir de caractere (se poate folosi si cu numere ca argument si va returna numarul de cifre din numarul respectiv, eventual +1, daca e un numar zecmal - conversia din numeric in sir de caractere se face automat). ISNA() e o functie care returneaza TRUE sua FALSE in functie de evaluarea expresiei furnizata ca argument. Cand o functie de cautare nu gaseste elementul de cautat aceasta returneaza eroarea #N/A - ISNA() semnaleaza prezenta acestei erori returnand TRUE. In al doilea IF se testeaza existenta valorii din celula $H$8 intr-un anume range ($D$1:$P$1) din foaia al carui nume e inscris pe coloana respectiva in randul 10. Daca acea valoare nu e gasita, expresia MATCH($H$8,INDIRECT("'"&L$10&"'!$D$1:$P$1"),0) va returna eroarea #N/A, iar ISNA() va returna TRUE, drept care formula va returna din nou sirul vid (""). Pentru descrierea functiilor INDEX() si MATCH() poti sa vezi si articolul
asta. Mai ramane de explicat INDIRECT(), care e o functie de adresare returnand referinta la un range scris intre parantezele functiei ca un sir de caractere. =INDIRECT("A1") e echivalent cu =A1. INDIRECT() e folosit aici pentru a compune referinta prin concatenarea unor siruri de caractere pentru care se foloseste operatorul &. & poate fi folosit in loc de CONCATENATE(). Pentru ca in cazul foilor de calcul cu nume care contin spatii acest nume trebuie pus intre ghilimele, sau apostrofuri, argumentul lui INDIRECT() e construit folosind si apostroful ca sir de caractere - portiunea "'" reprezinta, desi nu se vede bine pe ecran, un apostrof intre doua ghilimele, iar portiunea care incepe cu "'!... e de fapt: " ' ! (scris fara spatii intre semne).
Daca ai nevoie de referinte la mai multe foi de calcul in "pachetul" ala de formule, poti extinde "pachetul" prin copierea formulelor. Daca vrei sa faci referinta la o foaie de calcul din alt fisier va trebui ca denumirea respectiva sa fie scrisa pe randul 10 incluzand denumirea fisierului (cu extensia .xls) intre paranteze drepte si daca vrei ca referinta sa fie gasita si cu fisierul respectiv inchis, va trebui sa fie prefixata si de calea unde se afla fisierul. De exemplu daca fisierul tau se numeste Dan.xls, se afla in folderul C:\Excel\DrExcel\ si vrei referinta la foaia X, pe randul 10, in coloana respectiva va trebui sa scrii:
C:\Excel\DrExcel\[Dan.xls]X
In orice caz nu va trebui sa folosesti paranteze drepte in denumirea unei foi de calcul!