UNIC din coloane non-adiacente - sfaturi Excel

Zilele trecute, eram pe punctul de a crea o combinație unică de două coloane neadiacente în Excel. De obicei, fac asta cu Remove Duplicates sau cu Advanced Filter, dar m-am gândit că voi încerca să o fac cu noua funcție UNIQUE care vine la Office 365 în 2019. Am încercat mai multe idei și niciuna nu ar funcționa. Așadar, m-am dus la masterul din Dynamic Arrays, Joe McDaid, pentru asistență. Răspunsul este destul de mișto și sunt sigur că îl voi uita, așa că îl documentez pentru dvs. și pentru mine. Sunt sigur că, peste doi ani, voi face Google cum să fac acest lucru și să realizez „Oh, uite! Eu sunt cel care a scris articolul despre asta!”

Înainte de a ajunge la funcția UNIQUE, aruncați o privire la ceea ce încerc să fac. Doresc fiecare combinație unică de reprezentant de vânzări din coloana B și produs din coloana C. În mod normal, aș urma acești pași:

Copiați titlurile din B1 și D1 într-o secțiune goală a foii de lucru
Din B1, alegeți Date, Filtru, Avansat
În dialogul Filtru avansat, alegeți Copiere într-o locație nouă
Specificați titlurile de la pasul 1 ca interval de ieșire
Alegeți caseta pentru Numai valori unice
Faceți clic pe OK

Copiați cele două titluri într-o secțiune goală care devine domeniul de ieșire

Rezultatul este fiecare combinație unică a celor două câmpuri. Rețineți că filtrul avansat nu sortează articolele - acestea apar în secvența originală.

Obținerea unei liste unice este una dintre utilizările mele preferate pentru Advanced Filter

Acest proces a devenit mai ușor în Excel 2010 datorită comenzii Eliminare duplicate de pe fila Date din panglică. Urmați acești pași:

Selectați B1: D227 și Ctrl + C pentru a copia
Lipiți într-o secțiune goală a foii de lucru.

Faceți o copie a datelor, deoarece Eliminarea duplicatelor este distructivă
Alegeți Date, eliminați duplicatele
În caseta de dialog Eliminare duplicate, deselectați Data. Aceasta îi spune Excel să se uite doar la Rep și Produs.
Faceți clic pe OK

Spuneți Eliminare duplicate să ia în considerare numai Rep și Data

Rezultatele sunt aproape perfecte - trebuie doar să ștergeți coloana Data.

Ștergeți coloana suplimentară

Întrebarea: Există vreo modalitate prin care funcția UNIQUE să privească doar coloanele B & D? (Dacă nu ați văzut încă noua funcție UNIQUE, citiți: Funcția UNIQUE în Excel.)

Solicitarea =UNIQUE(B2:D227)vă va oferi fiecare combinație unică de Rep, Data și Produs, care nu este ceea ce căutăm.

Cum putem trece două coloane neadiacente la funcția UNIQUE?

Când au fost introduse Dynamic Arrays în septembrie, am spus că nu vom mai fi nevoiți să ne facem griji cu privire la complexitățile formulelor Ctrl + Shift + Enter. Dar pentru a rezolva această problemă, veți folosi un concept numit Lifting. Sperăm că acum mi-ați descărcat cartea electronică Excel Dynamic Arrays Direct to the Point. Accesați paginile 31-33 pentru o explicație completă a ridicării.

Vedeți cartea mea pentru o explicație completă a ridicării (și mai târziu, atunci când mergeți la sortarea rezultatelor, ridicare în perechi)

Luați o funcție Excel care așteaptă o singură valoare. De exemplu, =CHOOSE(Z1,"Apple","Banana")ar returna fie Apple, fie Banana, în funcție de dacă Z1 conține 1 (pentru Apple) sau 2 (pentru Banana). Funcția ALEGE așteaptă un scalar ca prim argument.

Dar, în schimb, veți trece o constantă matrice de (1,2) ca primul argument pentru ALEGE. Excel va efectua operația de ridicare și va calcula ALEGE de două ori. Pentru valoarea 1, doriți reprezentanții vânzărilor în B2: B227. Pentru valoarea 2, doriți produsele din D2: D227.

Spuneți CHOOSE să returneze două răspunsuri

În mod normal, în vechiul Excel, intersecția implicită ar fi dat peste cap rezultatele. Dar acum, când Excel poate vărsa rezultate în multe celule, formula de mai sus returnează cu succes o serie de răspunsuri în B și D:

Succes! Totul este în jos de aici

Simt că aș insulta inteligența ta pentru a scrie restul articolului, pentru că de aici este super-simplu.

Înfășurați formula din captura de ecran anterioară în UNIQUE și veți obține doar combinațiile unice de reprezentant de vânzări și produs folosind =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Încă nu sunt sortate

Pentru a vă înțelege, încercați să modificați formula de mai sus pentru a returna toate combinațiile unice de trei coloane: reprezentant de vânzări, produs, culoare.

Mai întâi, modificați constanta matricei pentru a vă referi la (1,2,3).

Apoi, se adaugă un al patrulea argument pentru a alege să se întoarcă de la culoarea E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Returnează combinația unică de trei coloane

Ar fi frumos să sortăm aceste rezultate, așa că ne îndreptăm spre Sortare cu o formulă folosind SORT și SORTBY.

În mod normal, funcția de sortat după prima coloană crescătoare ar fi =SORT(Array)sau =SORT(Array,1,1).

Pentru a sorta după trei coloane, trebuie să faceți câteva ridicări în perechi cu =SORT(Array,(1,2,3),(1,1,1)). În această formulă, când ajungeți la al doilea argument al SORT, Excel vrea să știe după ce coloană să sortați. În loc de o singură valoare, trimiteți trei coloane în interiorul unei constante de matrice: (1,2,3). Când ajungeți la al treilea argument în care specificați 1 pentru Ascendent sau -1 pentru Descendent, trimiteți o constantă de matrice cu trei 1 pentru a indica Ascendent, Ascendent, Ascendent. Următoarea captură de ecran arată =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Pentru mai multe despre ridicarea în perechi, consultați pagina 34 din Excel Dynamic Arrays Direct to the Point.

Cel puțin până la sfârșitul anului 2018, puteți descărca gratuit cartea Excel Dynamic Arrays folosind linkul din partea de jos a acestei pagini.

Sunt încurajat să constat că răspunsul la întrebarea de astăzi este cam complicat. Când au apărut Dynamic Arrays, m-am gândit instantaneu la toate formulele uimitoare postate pe forumul de mesaje de Aladin Akyurek și alții și la modul în care aceste formule vor deveni mult mai simple în noul Excel. Dar exemplul de astăzi arată că va fi în continuare nevoie de genii formule pentru a crea noi modalități de utilizare a matricelor dinamice.