Căutarea în Matrice – Problemă de #Excel [Updated]

Articolul a fost actualizat pe data de 22.03.2019 Ora 22:00. Soluțiile mai jos.

Salutare tuturor fanilor Excel … care au mai rămas pe aici. Astăzi am primit o problemă interesantă de Excel pe care v-o supun atenției și vouă.

Se dă următoarea matrice:

Realizați un raport de sinteză cu următoarea structură și care să aibă rezultatul în formatul următor:

Soluția trebuie transmisă prin e-mail la adresa: valy.greavu@outlook.com până pe 22.03.2019 ora 22:00. Dacă sunt studenți ai mei în anul I care rezolvă și știu să-mi explice rezolvarea vor primi 10 la examenul de săptămâna viitoare!

Fișierul cu rezolvarea mea poate fi descărcat de aici.

Aștept cu interes!

[UPDATE] Soluții

În decursul celor 30 de ore cât a fost disponibilă problema spre rezolvare, am primit 4 soluții de la oameni extraordinari de buni în Excel, cu unii dintre ei am mai reacționat în timp. Nu știu dacă mai sunt și alții care au mai încercat, dar pe baza numărului de vizualizări, tind să cred că au fost destul de puțini. Îi încurajez pe toți cei care vor să încerce să îmi ceară feedback pe parcurs…

Problema a fost expusă doar pe jumătate. Jumătatea în care fiecare valoare apare doar odată pe coloană. Pentru a rezolva această problemă lucrurile sunt simple. Se folosește o funcție Index combinată cu un match pe un offset dinamic generat de valorile corespunzătoare coloanei pe care se află valoarea.

Soluția mea

În exemplul meu, formula pentru celula B15, echivalentă combinației Z1/A este:

=IFNA(INDEX($A$1:$A$11;MATCH(B$14;OFFSET($A$1;0;MATCH($A15;$A$1:$K$1;0)-1;COUNTA($A$1:$A$11);1);0));"")

în care:

formula OFFSET() se folosește pentru a determina coloana de căutare a MATCH-ului pe B$14. Acest offet este determinat de MATCH-ul său intern ($A15) și are numărul echivalent al liniilor determinate de COUNTA.

Pentru varianta cu duplicate (și aici m-am oprit cu formulele… adică nu am formulă pentru mai mult de două valori) treaba se complică foarte mult. Pe lângă faptul că trebuie să continue căutarea trebuie adăugat și un separator de valori (în cazul meu virgulă) care trebuie eliminat din valorile nule sau singulare.

În fișierul care poate fi descărcat mai sus, formula pentru duplicate este de forma:

=IF(
RIGHT(CONCATENATE(IFNA(
INDEX($A$1:$A$11;MATCH(B$14;OFFSET($A$1;0;MATCH($A15;$A$1:$K$1;0)-1;COUNTA($A$1:$A$11);1);0));"");",";
IFNA(INDEX(OFFSET($A$1;MATCH(B$14;OFFSET($A$1;0;MATCH($A15;$A$1:$K$1;0)-1;COUNTA($A$1:$A$11);1);0);0;COUNTA($A$1:$A$11);1);
MATCH(B$14;OFFSET($A$1;MATCH(B$14;OFFSET($A$1;0;MATCH($A15;$A$1:$K$1;0)-1;COUNTA($A$1:$A$11);1);0);MATCH($A15;$A$1:$K$1;0)-1;COUNTA($A$1:$A$11);1);0));"") ))=",";

IFNA(INDEX($A$1:$A$11;MATCH(B$14;OFFSET($A$1;0;MATCH($A15;$A$1:$K$1;0)-1;COUNTA($A$1:$A$11);1);0));"");

CONCATENATE(IFNA(INDEX($A$1:$A$11;MATCH(B$14;OFFSET($A$1;0;MATCH($A15;$A$1:$K$1;0)-1;COUNTA($A$1:$A$11);1);0));"");",";IFNA(INDEX(OFFSET($A$1;MATCH(B$14;OFFSET($A$1;0;MATCH($A15;$A$1:$K$1;0)-1;COUNTA($A$1:$A$11);1);0);0;COUNTA($A$1:$A$11);1);MATCH(B$14;OFFSET($A$1;MATCH(B$14;OFFSET($A$1;0;MATCH($A15;$A$1:$K$1;0)-1;COUNTA($A$1:$A$11);1);0);MATCH($A15;$A$1:$K$1;0)-1;COUNTA($A$1:$A$11);1);0));""))
)

Problema acestei căutări este dată de determinarea celei de-a doua părți a coloanei de căutare care este diferită de la coloană la coloană în funcție de poziția căutării primei valori din coloană.

Sfat: Dacă vi se pare o virtute să scrii formule ca cea de mai sus în Excel, aflați că răspunsul este NU. Important este să găsești o soluție elegantă și corectă, nu una în care folosești multe litere.

Dacă doriți să revedeți articolul despre funcția OFFSET() vă rog să consultați articolul: https://valygreavu.com/2017/05/08/excel-functia-offset/

Soluțiile primte, nu în ordine.

Soluția lui George

Voi începe cu soluția lui George (fost student al meu) care mi s-a părut deosebit de suprinzătoare prin simplitate și corectitudinea rezolvării pentru valori unice.  Dezavantajul că nu rezolvă duplicatele, dar mie mi-a plăcut.

George folosește o funcție array pentru transpose-ul matricii inițiale, după care folosește un concatenate cu Match() pentru a obține matricea de valori.

Fișierul poate fi descărcat și analizat la adresa: https://1drv.ms/x/s!ApGubfWFh8Nuq_teh5GWrX7vs2A2QQ

Soluții cu Power Query

PowerQuery devine din ce în ce mai prezent în rezolvarea problemelor de Excel legate de manipulare a datelor. Ca exemplu:

• Soluția de la Alexandru (din București) a fost bazată pe PowerQuery și corectă pentru valori unice pe coloană.
• Ciprian (din Iași) a propus o soluție bazată pe formula mea inițială și pe PowerQuery care respectă valorile multiple pe coloană.

Explicațiile lui Ciprian pentru rezolvarea sa:

Câtă vreme nu ai spus de la început că vrei și asta nu am pierdut timp să fac și asta…. am respectat 100% ce ai enunțat în problemă, chiar și la faza cu înregistrările unice :)

Oricum asta cu numele coloanelor e simplă:

în pasul 2, înlocuiești lista cu numele coloanelor în clar {„Z1”, „Z2”, „Z3”, „Z4”, „Z5”, „Z6”, „Z7”, „Z8”, „Z9”, „Z10″}) cu numele TUTUROR coloanelor: Table.ColumnNames(Source)

dacă vrei toate coloanele în afară de prima,  atunci înlocuiești cu Text.Split(Text.AfterDelimiter(Text.Combine(Table.ColumnNames(Source),”,”),”,”,1),”,”)

algoritmul e simplu:

let

//Pas.1 – Preiau datele de intare

Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],

//Pas2. – Opțional – conserv zilele care nu au valori, altfel se pierd la repivotare

#”Replaced Value” = Table.ReplaceValue(Source,null,”0″,Replacer.ReplaceValue,Text.Split(Text.AfterDelimiter(Text.Combine(Table.ColumnNames(Source),”,”),”,”,1),”,”)),

//Pas3. – evident

#”Unpivoted Columns” = Table.UnpivotOtherColumns(#”Replaced Value”, {„Valori/Ziua”}, „Ziua”, „Contor”),

//Pas4. Opțional – … mă deranja numele neconform conținutului :)

#”Renamed Columns” = Table.RenameColumns(#”Unpivoted Columns”,{{„Valori/Ziua”, „Valori”}}),

//Pas5. grupez înregistrările multiple. după dorință se poate renunța și în pivot iei primul, ultimul, minim, max, media, mediana, etc.

#”Grouped Rows” = Table.Group(#”Renamed Columns”, {„Contor”, „Ziua”}, {{„Valori_Combinate”, each Text.Combine(_[Valori],”,”), type text}}),

//Pas6. repivotez datele

#”Pivoted Column” = Table.Pivot(#”Grouped Rows”, List.Distinct(#”Grouped Rows”[Contor]), „Contor”, „Valori_Combinate”),

//Pas7. opțional de două ori: elimin coloana pentru null. 1. Poate fi arătată dacă interesează pe cineva. 2. dacă se renunță la 2 dispare și ea

#”Removed Columns” = Table.RemoveColumns(#”Pivoted Column”,{„0”}),

//Pas8. Opțional pentru că denumirea coloanelor nu permite ordonarea lexicografică, creez o coloană care să poată fi sortată după cum mă aștept

#”Added Custom” = Table.AddColumn(#”Removed Columns”, „Sort”, each Text.Combine({Text.At([Ziua],0),Text.End(Text.Combine({„0”,Text.Middle([Ziua],1)}),2)})),

//Pas9. opțional – sort

#”Sorted Rows” = Table.Sort(#”Added Custom”,{{„Sort”, Order.Ascending}}),

//Pas10. opțional – elimin coloana de sortare

#”Removed Columns1″ = Table.RemoveColumns(#”Sorted Rows”,{„Sort”})

in

#”Removed Columns1″

Practic pentru a obține același lucru ca în formulele din Excel ai nevoie de 3 pași, pentru a aduce valorile multiple 4, iar restul sunt de floricele

Soluția poate fi descărcată și analizată aici: https://1drv.ms/x/s!ApGubfWFh8Nuq_tgh5GWrX7vs2A2QQ

Soluția cu DAX

Am alocat un subcapitol pentru soluția lui Cristian din Timișoara. Asta pentru că folosește DAX (Data Analysis Expressions) în rezolvarea problemei, pe lângă faptul că o rezolvă și cu PowerQuery. Recunosc că m-am simțit puțin încurcat de povestea cu DataModel și limbajul specific:

Data:=VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT(‘DAX'[Value])
VAR __MAX_VALUES_TO_SHOW = 10
RETURN
IF(
__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW;
CONCATENATE(
CONCATENATEX(
TOPN(__MAX_VALUES_TO_SHOW; VALUES(‘DAX'[Value]); ‘DAX'[Value]; ASC);
‘DAX'[Value];
„, „;
‘DAX'[Value];
ASC
);
„, etc.”
);
CONCATENATEX(VALUES(‘DAX'[Value]); ‘DAX'[Value]; „, „; ‘DAX'[Value]; ASC)
)

Deci cum?! :)

Soluția lui Cristian poate fi descărcată aici: https://1drv.ms/x/s!ApGubfWFh8Nuq_tih5GWrX7vs2A2QQ

Fișierul conține și un mic macro, dar e safe.

Concluzii

Problema rămâne deschisă. Cred că e prea faină ca să nu mai continuăm să căutăm soluții. Folosiți partea de comentarii pentru asta.

Dacă v-au plăcut soluțiile nu ezitați să ne oferiți feedback în partea de comentarii.

Dacă mai aveți probleme faine spre rezolvare nu ezitați să le trimiteți pe mail.

Sper să fie util cuiva.