Zilele trecute am publicat articolul: Determinarea numelor unice dintr-un tabel #Excel dar, chiar dacă soluția propusă funcționează, ceva nu mi-a dat pace și revin cu un update în acest articol.

Soluția propusă de mine este oarecum greu de înțeles și replicat, plus că utilizează prea multe formule.

Mi-a plăcut soluția propusă de Ciprian în comentarii prin care propune utilizarea unui Power Query elegant de uniune a mai multor coloane. Soluția lui implică transformarea tabelului într-un Table (Table1) și apelarea coloanelor după denumirea lor, transformarea într-o listă de valori și combinarea coloanelor cu List.Combine, determinarea numelor unice cu List.Distinct, retransformarea în tabel, sortarea tabelului și eliminarea celulelor cu valoarea null.

let

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

#”Changed Type” = Table.TransformColumnTypes(Source,{{„Echipa 1”, type text}, {„Echipa 2”, type text}, {„Echipa 3”, type text}, {„Echipa 4”, type text}}),

#”Lista cu toti” = List.Combine({Table.Column(#”Changed Type”, „Echipa 1″),Table.Column(#”Changed Type”, „Echipa 2″),Table.Column(#”Changed Type”, „Echipa 3″),Table.Column(#”Changed Type”, „Echipa 4”)}),

#”Lista cu unici” = List.Distinct(#”Lista cu toti”),

#”Tabel cu unici” = Table.FromList(#”Lista cu unici”, null, {„Unici”}),

#”Unici sortati” = Table.Sort(#”Tabel cu unici”,{{„Unici”, Order.Ascending}}),

#”Unici reali” = Table.SelectRows(#”Unici sortati”, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {„”, null})))

in

#”Unici reali”

O mică problemă cu soluția lui Ciprian este că de fiecare dată când ai o sursă de date nouă, trebuie să refaci mulți din pași și trebuie mult edit manual în Advanced Editorul din Power Query.

 

Funcția vStack()

Funcția este o funcție nouă de tip dynamic array care permite unirea mai multor coloane sau linii într-una singură. Funcția are ca parametrii coloanele, blocurile de celule sau tabelele pe care dorim să le consolidăm într-o singură coloană.

În exemplu nostru ca să aducem numele unice din tabelul cu date, fără nici un cap de tabel, formula foarte simplă ar fi:

L1: =SORT(UNIQUE(VSTACK(A2:A8;B2:B8;C2:C8;D2:D8)))

În care vstack() adduce datele de pe cele 4 coloane, unique extrage valorile unice iar sort le aranjează în ordine alfabetică.

Din considerente „artistice” nu putem permite valori 0 în rezultatul unei formule de tip dynamic array.

Pentru a elimina rezultatul cu 0, în celula I1 am utilizat formula:

=VSTACK({„Nume Unic”};LET(vNume;SORT(UNIQUE(VSTACK(A2:A8;B2:B8;C2:C8;D2:D8)));FILTER(vNume;vNume<>””)))

În care:

• VSTACK({„Nume unic”}; – îmi permite definirea „capului de tabel”. Nu poate fi folosit în următorul VSTACK pentru că sortarea din SORT mi-ar pune capul de tabel la mijlocul șirului.
• LET(vNume;SORT(UNIQUE(VSTACK(A2:A8;B2:B8;C2:C8;D2:D8))); – Îl folosesc pentru definirea variabilei vZero ca rezultat al unicității de VSTACK. Variabila o folosesc ulterior în partea de filtrare pentru eliminarea valorilor zero.
• FILTER(vNume;vNume<>””) – este folosit pentru eliminarea din rezultatul execuției a valorilor rezultat 0 corespondente celulelor goale din blocul de valori.

De menționat că toate funcțiile sunt disponibile și în Google Spreadsheets. Pur și simplu am copiat formula și tabelul din Excel și le-am introdus într-o foaie nouă.

 

Simplificarea operațiunilor de Consolidare

 

Ceea ce văd eu cu adevărat puternic în utilizarea VStack este reprezentat de simplificarea operațiunilor de consolidare. Avem mai multe tabele în mai multe foi de calcul. Pentru a centraliza toate datele într-un singur tabel, avem destul de multe variante. Dintre toate acum Vstack devine mai simplu chiar decât operațiunile cu Power Query

Exemplificare vizuală:

În centralizatorul toate aducem toate datele din toate foile de calcul cu o singură formulă:

=VSTACK(Iasi!A2:E25;Vaslui!A3:E17;Bacau!A3:E17;Neamt!A3:E17;Suceava!A3:E17)

Formula ar arăta și mai bine dacă am defini tabelele ca Tables. Doar că în acest caz trebuie să definim capul de tabel înainte de realizarea formulei.

 

Sper să fie util cuiva!

 

Referințe suplimentare:

https://exceljet.net/functions/vstack-function

https://www.ablebits.com/office-addins-blog/combine-ranges-arrays-excel-vstack-hstack/