Cum semestrul acesta predau Excel la facultate o să vă mai scriu tot felul de funcții și probleme mai interesante.

Ieri am primit de la un prieten o problemă de clasa a IX-a. Parcă aș vrea să mai fiu eu elev în clasa a IX-a și să am profi care să mă învețe așa ceva.

„Să scrie o formulă în care să utilizeze o singură funcție, o singură dată, dar care să dea rezultatele de mai jos atunci când parametrul ia valorile:

0-99 lei    0%

100-299    2%

300-999    5%

1000-oricât    10.00%”

După mintea mea, aceasta este o problemă care se rezolvă simplu cu IF()-uri imbricate așa cum am explicat în acest articol.

Doar că cerința problemei este o singură funcție, o singură dată!

Soluția în acest caz poate fi:

Formula din B2:

=VLOOKUP(A2;{0\0;100\0,02;300\0,05;1000\0,1};2;TRUE)

în care:

{0\0;100\0,02;300\0,05;1000\0,1} este o matrice cu două coloane și 4 linii.

Varianta pentru Excel locale engleză: =VLOOKUP(A2,{0,0;100,0.02;300,0.05;1000,0.1},2,TRUE)

De ce (încă) VLOOKUP odată cu apariția magicului XLOOKUP() ? Pentru că XLOOKUP este disponibil doar în variantele MS Office 365 și mobile, tablete nu și pentru versiunile Retail sau mai vechi. De asemenea, folosește mai mulți parametri ceea ce „complică viața” utilizatorului începător.

Mai multe despre formulele matrice în acest articol: https://support.microsoft.com/ro-ro/office/reguli-%c8%99i-exemple-pentru-formulele-matrice-7d94a64e-3ff3-4686-9372-ecfd5caa57c7?ui=ro-RO&rs=ro-RO&ad=RO#ID0EAAFAAA=Office_2010_-_Office_2019

Sper să vă fie util.

Am primit o întrebare de la un cunoscut, legată de un fel de „VLOOKUP” aproximativ. Știu că nu există așa ceva, dar regăsirea datelor după coduri parțiale este posibilă.

În exemplul de mai jos avem un tabel cu date cu coduri alfanumerice, iar în zona de căutare doar prima parte din cod. În G5 avem rezultatul căutării.

Pentru rezolvare am folosit o combinație de funcții INDEX, MATCH și FIND.

=INDEX($B$3:$B$8;MATCH(1;FIND(F5;$A$3:$A$8);0))

în care:

• $B$3:$B$8 reprezintă blocul de celule cu denumirea care trebuie extrasă în funcție de codul din F5;
• Funcția MATCH identifică numărul liniei pe care se află codul regăsit prin Find;
• Funcția FIND, caută după textul din F5 pe blocul de celule $A$3:$A$8 returnând valoarea 1 după care se face MATCH-ul;

Valoarea 1 din FIND este echivalentă începutului de cod din blocul de căutare $A$3:$A$8. Dacă acel cod parțial se regăsește în interiorul coloanei ID și nu începe cu prima literă atunci FIND-ul returnează altă valoare decât 1, și formula nu mai funcționează ca în model.

În cazul în care cele două coduri sunt scrise cu caractere diferite, trebuie menționat că FIND este o funcție case-sesitive. Contează dacă este scris cu litere mari sau nu. Dacă aveți cazuri cu diferite caractere, atunci se recomandă utilizarea funcției SEARCH().

În cazul în care dorim să căutăm informații pe baza unor date parțiale din interiorul codului problema se complică puțin, pentru că trebuie să pornim căutarea dintr-o locație diferită de 1, iar funcția MATCH() folosește în căutare maximul valorilor de căutare din șirul de pe care se realizează căutarea.

În continuare un exemplu de problemă și funcția utilizată.

Pentru rezolvare s-a utilizat funcția:

=INDEX($B$17:$B$22;MATCH(MAX(IFERROR(SEARCH(F18;$A$17:$A$22;IFERROR(SEARCH(F18;$A$17:$A$22);));));SEARCH(F18;$A$17:$A$22);0))

În care:

• MAX(IFERROR(SEARCH(F18;$A$17:$A$22;IFERROR(SEARCH(F18;$A$17:$A$22);));)) este echivalentul valorii 1 din funcția prezentată în exemplul anterior.

Aceste modele de implementare funcționează în acest fel doar în versiunile de Excel Online din Office 365.

Pentru alte versiuni, funcția FIND nu mai funcționează ca un Dynamic Array și trebuie scrisă ca o formulă CSE (Ctrl+Shift+Enter).

Sper să vă fie util!