JEDINSTVENO iz susjednih stupaca - Excel savjeti

Neki dan sam htio stvoriti jedinstvenu kombinaciju dvaju susjednih stupaca u Excelu. Obično to radim s Ukloni duplikate ili s naprednim filtrom, ali mislio sam da ću to pokušati s novom UNIQUE funkcijom koja dolazi u Office 365 2019. Isprobao sam nekoliko ideja i nijedna ne bi uspjela. Pa sam otišao po pomoć za majstora Dynamic Arraysa, Joea McDaida. Odgovor je prilično cool i siguran sam da ću ga zaboraviti pa ga dokumentiram za vas i za mene. Siguran sam da ću za dvije godine Google proguglati kako to učiniti i shvatiti "Oh, vidi! Ja sam taj koji je napisao članak o ovome!"

Prije nego što prijeđete na funkciju UNIQUE, pogledajte što pokušavam učiniti. Želim svaku jedinstvenu kombinaciju prodajnog predstavnika iz stupca B i proizvoda iz stupca C. Uobičajeno bih slijedio ove korake:

Kopirajte naslove iz B1 i D1 u prazan odjeljak radnog lista
Iz B1 odaberite Podaci, Filtriraj, Napredno
U dijaloškom okviru Napredni filtar odaberite Kopiraj na novo mjesto
Navedite naslove iz koraka 1 kao raspon izlaza
Označite okvir Samo za jedinstvene vrijednosti
Kliknite U redu

Kopirajte dva naslova u prazan odjeljak koji postaje izlazni opseg

Rezultat je svaka jedinstvena kombinacija dva polja. Napredni filtar ne sortira stavke - oni se pojavljuju u izvornom slijedu.

Dobivanje jedinstvenog popisa jedna je od mojih najdražih namjena naprednog filtra

Ovaj je postupak postao lakši u programu Excel 2010 zahvaljujući naredbi Ukloni duplikate na kartici Podaci na vrpci. Prati ove korake:

Odaberite B1: D227 i Ctrl + C za kopiranje
Zalijepite u prazan odjeljak radnog lista.

Napravite kopiju podataka jer je uklanjanje duplikata destruktivno
Odaberite Data, Remove Duplicates
U dijaloškom okviru Ukloni duplikate poništite odabir datuma. To govori Excelu da gleda samo Rep i Product.
Kliknite U redu

Recite Remove Duplicates da uzima u obzir samo Rep i Date

Rezultati su gotovo savršeni - samo trebate izbrisati stupac Datum.

Izbrišite dodatni stupac

Pitanje: Postoji li neki način da funkcija UNIQUE gleda samo stupce B & D? (Ako još niste vidjeli novu UNIQUE funkciju, pročitajte: UNIQUE funkcija u Excelu.)

Traženje =UNIQUE(B2:D227)bi vam pružilo svaku jedinstvenu kombinaciju replike, datuma i proizvoda koja nije ono što tražimo.

Kako možemo proslijediti dva susjedna stupca u funkciju UNIQUE?

Kad su u rujnu predstavljeni dinamički nizovi, rekao sam da se više nećemo morati brinuti o složenosti formula Ctrl + Shift + Enter. Ali da biste riješili ovaj problem, upotrijebit ćete koncept nazvan Podizanje. Nadamo se da ste do sada preuzeli moju e-knjigu Dynamic Arrays Straight To The Point. Za cjelovito objašnjenje podizanja okrenite se na stranice 31-33.

Pogledajte moju knjigu za cjelovito objašnjenje dizanja (i kasnije, kada krenete sortirati rezultate, Dvostruko podizanje)

Uzmite Excel funkciju koja očekuje jednu vrijednost. Na primjer, =CHOOSE(Z1,"Apple","Banana")vratilo bi Apple ili Banana, ovisno o tome sadrži li Z1 1 (za Apple) ili 2 (za Banana). Funkcija CHOOSE očekuje skalar kao prvi argument.

Ali umjesto toga, kao prvi argument za CHOOSE proslijedit ćete konstantu niza od (1,2). Excel će izvršiti operaciju podizanja i dvaput izračunati IZABERI. Za vrijednost 1 želite prodajne predstavnike u B2: B227. Za vrijednost 2 želite proizvode u D2: D227.

Recite CHOOSE da vrati dva odgovora

Obično bi u starom Excelu implicitno presijecanje zeznulo rezultate. Ali sada, kada Excel može preliti rezultate u mnoge stanice, gornja formula uspješno vraća niz svih odgovora u B i D:

Uspjeh! Odavde je sve nizbrdo

Osjećam da bih vrijeđao vašu inteligenciju da napišem ostatak članka, jer je odavde super jednostavno.

Umotajte formulu s prethodnog snimka zaslona u UNIQUE i dobit ćete samo jedinstvene kombinacije prodajnog predstavnika i proizvoda koji koriste =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Još uvijek nije sortirano

Da biste provjerili razumijevanje, pokušajte promijeniti gornju formulu da biste vratili sve jedinstvene kombinacije od tri stupca: prodajni zastupnik, proizvod, boja.

Prvo promijenite konstantu niza kako bi se odnosila na (1,2,3).

Zatim dodajte četvrti argument odabrati da se vrati boja sa E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Vrati jedinstvenu kombinaciju od tri stupca

Bilo bi lijepo sortirati te rezultate, pa se okrećemo Sortiranju s formulom pomoću SORT i SORTBY.

Obično bi funkcija za sortiranje po prvom uzlaznom stupcu bila =SORT(Array)ili =SORT(Array,1,1).

Da biste sortirali po tri stupca, trebate napraviti podizanje u paru =SORT(Array,(1,2,3),(1,1,1)). U ovoj formuli, kada dođete do drugog argumenta SORT, Excel želi znati po kojem stupcu sortirati. Umjesto jedne vrijednosti, pošaljite tri stupca unutar konstante niza: (1,2,3). Kad dođete do trećeg argumenta gdje navedite 1 za Rastući ili -1 za Silazni, pošaljite konstantu niza s tri jedinice 1 kako bi naznačili Rastući, Rastući, Rastući. Sljedeći snimak zaslona prikazuje =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Za više informacija o podizanju u paru, pogledajte stranicu 34 Excel-ovih dinamičkih nizova Ravno do točke.

Barem do kraja 2018. knjigu s dinamičkim nizovima Excel možete besplatno preuzeti pomoću veze na dnu ove stranice.

Potaknut sam otkrićem da je odgovor na današnje pitanje pomalo složen. Kad su izašli Dynamic Arrays, odmah sam pomislio na sve nevjerojatne formule koje je na oglasnoj ploči objavio Aladin Akyurek i drugi i kako će te formule postati mnogo jednostavnije u novom Excelu. Ali današnji primjer pokazuje da će i dalje biti potrebno da geniji formule izrade nove načine za upotrebu dinamičkih nizova.