Dynamic Array - Unique Sort
Plecăm de la articolul de data trecută privind XLOOKUP (https://bit.ly/39dN8yq) și încercăm să rezolvăm problema extragerii de date unice și sortarea acestora folosind noile funcții dynamic array. Bonus, o optimizare de data validation. Articol dedicat doar utilizatorilor de Office 365.
Aici aveți fișierul folosit pentru exemplificare:
http://bit.ly/2UZAEGT
Problema 1. Extragere date unice. De exemplu, lista produselor.
Vrem să realizăm un formular de extragere de date (XLOOKUP) unde să aflăm cantitatea livrată pentru un anumit produs.
În G4 vom introduce o listă de validare cu denumirile de produse.
Pentru lista respectivă putem folosi (în variantă clasică): Copiere coloană Den_P – Paste într-o locație apropiată (ex. coloana K) – din Data, Remove Duplicates
Din Data – Data Validation – Allow – List, Source – selectăm datele extrase.
Pentru o variantă ceva mai profesională, în loc să selectăm datele introducem formula:
=OFFSET($K$2;0;0;COUNTA($K:$K);1)
Sau, în varianta Dynamic Array, în M2 introducem formula:
=UNIQUE(Table1[Den_P])
Rezultatul obținut diferă puțin ca aspect (la selectarea unei celule, aria este delimitată și afișată umbrit). Acest mod se numește Spill. Această arie nu poate fi editată decât în prima celulă. Ștergerea acestei celule atrage după sine ștergerea întregii arii.
După cum se observă, ambele liste obținute nu sunt sortate pentru o utilizare mai facilă. Asta generează cea de a doua problemă: sortarea datelor.
Problema 2. Sortarea datelor
În varianta clasică sortarea s-ar face manual.
În varianta Dynamic Array, vom folosi funcția SORT. Implicit sortarea este în ordine crescătoare.
=SORT(UNIQUE(Table1[Den_P]))
Sort_index, Sort_order și By_col sunt opționale.
Sort_index – sortare după o anumită coloană, dacă aria este un tabel de date. Implicit este 1.
Sort_order – sortare crescător (implicit, valoare 1), respectiv descrescător, valoare -1
By_col – sortare după linii (implicit) sau după coloane
Ex. Funcția anterioară sortată descrescător =SORT(UNIQUE(Table1[Den_P]);1;-1)
Bonus! Anterior foloseam un OFFSET pentru o listă de validare dinamică. Odată cu Dynamic Array utilizați =$M$2# unde M2 este prima celulă din spill-ul obținut anterior.
Mult mai simplu! Mult mai rapid!
Pentru data următoare funcția FILTER (extragerea datelor pe baza unei condiții).
Aici aveți fișierul folosit pentru exemplificare:
http://bit.ly/2UZAEGT
Problema 1. Extragere date unice. De exemplu, lista produselor.
Vrem să realizăm un formular de extragere de date (XLOOKUP) unde să aflăm cantitatea livrată pentru un anumit produs.
În G4 vom introduce o listă de validare cu denumirile de produse.
Pentru lista respectivă putem folosi (în variantă clasică): Copiere coloană Den_P – Paste într-o locație apropiată (ex. coloana K) – din Data, Remove Duplicates
Din Data – Data Validation – Allow – List, Source – selectăm datele extrase.
Pentru o variantă ceva mai profesională, în loc să selectăm datele introducem formula:
=OFFSET($K$2;0;0;COUNTA($K:$K);1)
Sau, în varianta Dynamic Array, în M2 introducem formula:
=UNIQUE(Table1[Den_P])
Rezultatul obținut diferă puțin ca aspect (la selectarea unei celule, aria este delimitată și afișată umbrit). Acest mod se numește Spill. Această arie nu poate fi editată decât în prima celulă. Ștergerea acestei celule atrage după sine ștergerea întregii arii.
După cum se observă, ambele liste obținute nu sunt sortate pentru o utilizare mai facilă. Asta generează cea de a doua problemă: sortarea datelor.
Problema 2. Sortarea datelor
În varianta clasică sortarea s-ar face manual.
În varianta Dynamic Array, vom folosi funcția SORT. Implicit sortarea este în ordine crescătoare.
=SORT(UNIQUE(Table1[Den_P]))
Sort_index, Sort_order și By_col sunt opționale.
Sort_index – sortare după o anumită coloană, dacă aria este un tabel de date. Implicit este 1.
Sort_order – sortare crescător (implicit, valoare 1), respectiv descrescător, valoare -1
By_col – sortare după linii (implicit) sau după coloane
Ex. Funcția anterioară sortată descrescător =SORT(UNIQUE(Table1[Den_P]);1;-1)
Bonus! Anterior foloseam un OFFSET pentru o listă de validare dinamică. Odată cu Dynamic Array utilizați =$M$2# unde M2 este prima celulă din spill-ul obținut anterior.
Mult mai simplu! Mult mai rapid!
Pentru data următoare funcția FILTER (extragerea datelor pe baza unei condiții).
