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!

Bună ziua tuturor fanilor Excel. Am pentru voi o provocare, spun eu interesantă, dar probabil și utilă pentru mulți.

Mulțumesc tuturor celor care au participat la rezolvarea problemei sau sunt interesați de ea. Sper să fie utile. Soluțiile primite sunt prezentate în partea de jos a articolului.

În modelul de date din imaginea de mai jos, se cere ca Task-urile (sau alte tipuri de înregistrări de nivel 1, sau Heading 1 pentru cei familiarizați cu aplicațiile Word) trebuie să fie numerotate automat consecutiv. Condiția ca numele din coloana B să înceapă cu Task este obligatorie de respectat în date. Sau oricare alt identificator pentru înregistrările de nivel 1.

În celula A2 este singura care are scris pur și simplul nr. 1. Puteam face și dinamic acel număr cu funcția ROW()-1 în cazul meu. Restul numerelor sunt prin formulă.

Tot ceea ce nu este introdus ca Task (cele de nivel 2 sau Heading 2) trebuie să aibă numere în mod dinamic cu 1.1, 1.2 în funcție de numărul lor. În soluția mea, acel 1.1 nu este chiar un număr.

Pentru cei care vor să participe la această provocare, vă rog să trimiteți soluția prin e-mail la adresa: valy.greavu@outlook.com până la data de 22.05.2020 ora 14:00. Dacă sunt studenți de ai mei de la Master AA care rezolvă problema în termenul specificat le echivalez proba practică de laborator, de săptămâna viitoare cu 10 (primul care trimite cu soluția).

Aștept cu interes! :)

Update ora 22.05.2020 H16:00

Soluția mea

Chiar dacă pare o problemă simplă eu m-am complicat destul de mult în formulă.

Formula mea din celula A3 este:

=IF(LEFT(B3;4)=”Task”;MAX($A$2:A2)+1;MAX($A$2:A2)&”.”&(COUNTA(INDIRECT(CONCATENATE(ADDRESS(MATCH(MAX($A$2:A2);$A$2:A2;0)+1;1);”:”;ADDRESS(ROW()-1;1;4))))))

În această formulă compar dacă începutul textului din coloana B este cu Task, dacă da, adun valoarea 1 la maximul tuturor coloanelor de mai sus.

Dacă nu este task, scrie orice altceva fac concatenare cu & cu numărul maxim de mai sus, semnul „.” și rezultatul numărării (COUNTA) a celulelor de pe blocul dinamic de mai sus, format din adresa celulei in care se află valoarea maximă de până acum ADDRESS(MATCH(MAX($A$2:A2);$A$2:A2;0)+1;1) cu adresa celulei de mai sus de poziția curentă (ADDRESS(ROW()-1). Ca să pot face COUNTA() folosesc funcția INDIRECT() pentru a compune dinamic blocul de celule de căutare.

Avantajul este că funcționează și la mai mult de 10 task-uri și mai mult de 10 subtask-uri.

Dezavantajul este la sortare unde apare eroare dacă faci sortare după coloana de numere curente, sau coloana de activități.

Soluția este funcțională și în Foi de calcul Google, cu mențiunea că având un format regional diferit a trebuit să mai adaug un punct la finalul formulei ca să nu apară rezultatul de tip dată calendaristică.

Rezultatul formulei din Google.

Soluție Aurora

Mă refer la un prenume nu la un cod de soluție. :)

Aurora o fostă studentă de pe la începutul mileniului acesta, mi-a trimis prima soluție.

Formula ei din A3 este:

=IF(LEFT(B3;3)=”Sub”;IF(ISERROR(FIND(„.”;A2));A2&”.1″;LEFT(A2;FIND(„.”;A2))&(VALUE(RIGHT(A2;LEN(A2)-FIND(„.”;A2)))+1));IF(ISERROR(FIND(„.”;A2));A2+1;VALUE(LEFT(A2;FIND(„.”;A2)-1))+1))

Soluția propune folosirea repetitivă a funcțiilor FIND(), LEFT() și RIGHT() cu adunări de numere consecutive. Testat 100% funcțional la valori de peste 10 task-uri și subtask-uri.

Dezavantajul modelului este că dacă un subtask nu începe cu valoarea Sub, atunci nu mai merge formula.

Soluție Adrian B

Soluția lui Adrian folosește o paletă mai variată de funcții, dar rezultatul este identic cu soluțiile de mai sus.

=IF(B3<>””;IF(LEFT($B3;4)=”Task”;MAX($A$2:$A2)+1;IF(NOT(ISERROR(FIND(„.”;A2)));MID(A2;1;FIND(„.”;A2)-1)&”.”&MID(A2;FIND(„.”;A2)+1;LEN(A2)-FIND(„.”;A2))+1;A2&”.1″));””)

Formula este testată pentru mai mult de 10 task-uri și subtasuri. De asemenea, poate funcționa și pentru subtask-uri care nu au aceasta etichetă.

Soluție Alexandru N

Alexandru a trimis o soluție la fel de funcțională ca și celelalte doar că folosește altă abordare și altă combinație de formule. Îmi place la formula lui Alexandru faptul că are o complexitate mai redusă și poate fi mai ușor de înțeles și replicat. Asta dacă reușiți să vă „adaptați” la SUMPRODUCT().

=(IF(LEFT(B3;4)=”Task”;SUMPRODUCT((LEFT($B$2:B3;4)=”Task”)*1);SUMPRODUCT((LEFT($B$2:B3;4)=”Task”)*1)&”.”&IFERROR(RIGHT(A2;LEN(A2)-FIND(„.”;A2;1));0)+1))

Formula testată și funcțională.

Soluție Ciprian S

Ciprian, un vechi prieten al acestui gen de probleme și care a venit permanent cu soluții surprinzătoare, a trimis o soluție foarte interesantă ca primă versiune:

=IF(LEFT(B2;4)=”Task”; FLOOR.MATH(SUM(A1))+1; SUM(A1)+0,1)

Formula se scrie în B2 și funcționează pentru versiunile peste 10 task-uri dar cu maxim 9 task-uri.

Formula este testată și funcționează și în Google Sheets.

Pentru versiunea cu peste 10 subtaskuri Ciprian a folosit o variantă asemănătoare cu cele de mai sus:

Formula din A2:

=IF(LEFT(B2;4)=”task”;TEXT(IFERROR(VALUE(LEFT(A1;(FIND(„.”;A1)-1)))+1;IFERROR(VALUE(A1);0)+1);”0″);IFERROR(LEFT(A1;(FIND(„.”;A1)));A1&”.”)&IFERROR(MID(A1;(FIND(„.”;A1)+1);100);0)+1)

Este o abordare mult mai corectă decât cea prezentată în soluția mea, pentru că începe cu calculul fix din prima celulă de date (A2) și nu doar din celula A3.

O soluție cu VBA

Pentru cei care mă cunosc știu că nu sunt adeptul VBA. Totuși soluția este foarte interesantă și merită exploatată.

În principiu se creează o funcție în VBA cu Range.IndentLevel. Mai multe detalii puteți consulta aici: https://professor-excel.com/how-to-return-the-indentation-of-a-cell-in-excel/

Frumusețea acestei probleme este diversitatea soluțiilor și combinațiilor de formule plus ingeniozitatea oamenilor în a le combina în vederea obținerii rezultatelor asemănătoare. Mulțumesc frumos și ținem legătura! :)

Sper să fie util cuiva.

Categorie: Excel

Excel – Un alt VLOOKUP()

Un VLOOKUP aproximativ

Problemă de #Excel – Numerotare automată linii [Updated]