WYJĄTKOWE z nieprzylegających kolumn - wskazówki dotyczące programu Excel

Niedawno miałem zamiar utworzyć unikalną kombinację dwóch nieprzylegających kolumn w programie Excel. Zwykle robię to za pomocą opcji Usuń duplikaty lub filtru zaawansowanego, ale pomyślałem, że spróbuję to zrobić z nową funkcją UNIQUE, która pojawi się w Office 365 w 2019 r. Wypróbowałem kilka pomysłów i żaden nie zadziałał. Poszedłem więc po pomoc do mistrza Dynamic Arrays, Joe McDaida. Odpowiedź jest całkiem fajna i na pewno o niej zapomnę, więc dokumentuję ją dla Ciebie i dla mnie. Jestem pewien, że za dwa lata wyszukam w Google, jak to zrobić i zorientuję się: „O, spójrz! To ja napisałem artykuł na ten temat!”

Zanim przejdę do funkcji UNIQUE, spójrz na to, co próbuję zrobić. Chcę każdej unikalnej kombinacji przedstawiciela handlowego z kolumny B i produktu z kolumny C. Zwykle wykonałbym następujące kroki:

Skopiuj nagłówki z B1 i D1 do pustej sekcji arkusza
W B1 wybierz Dane, Filtr, Zaawansowane
W oknie dialogowym Filtr zaawansowany wybierz opcję Kopiuj do nowej lokalizacji
Określ nagłówki z kroku 1 jako zakres wyjściowy
Wybierz pole Tylko wartości unikalne
Kliknij OK

Skopiuj dwa nagłówki do pustej sekcji, która stanie się zakresem wyjściowym

Rezultatem jest każda unikalna kombinacja dwóch pól. Zwróć uwagę, że filtr zaawansowany nie sortuje elementów - pojawiają się one w oryginalnej kolejności.

Uzyskanie unikalnej listy to jedno z moich ulubionych zastosowań filtru zaawansowanego

Ten proces stał się łatwiejszy w programie Excel 2010 dzięki poleceniu Usuń duplikaty na karcie Dane na Wstążce. Wykonaj następujące kroki:

Wybierz B1: D227 i Ctrl + C, aby skopiować
Wklej do pustej sekcji arkusza.

Wykonaj kopię danych, ponieważ usuwanie duplikatów jest destrukcyjne
Wybierz dane, usuń duplikaty
W oknie dialogowym Usuń duplikaty usuń zaznaczenie opcji Data. To mówi programowi Excel, aby patrzył tylko na przedstawiciela i produkt.
Kliknij OK

Powiedz Usuń duplikaty, aby brać pod uwagę tylko Rep i datę

Wyniki są prawie doskonałe - wystarczy usunąć kolumnę Data.

Usuń dodatkową kolumnę

Pytanie: czy istnieje sposób, aby funkcja UNIQUE patrzyła tylko na kolumny B i D? (Jeśli nie widziałeś jeszcze nowej funkcji UNIQUE, przeczytaj: funkcja UNIQUE w programie Excel).

Prośba o =UNIQUE(B2:D227)uzyskanie każdej unikalnej kombinacji przedstawiciela, daty i produktu nie jest tym, czego szukamy.

Jak możemy przekazać dwie niesąsiadujące kolumny do funkcji UNIQUE?

Kiedy we wrześniu wprowadzono tablice dynamiczne, powiedziałem, że już nigdy nie będziemy musieli martwić się o złożoność formuł Ctrl + Shift + Enter. Ale aby rozwiązać ten problem, użyjesz koncepcji zwanej podnoszeniem. Mam nadzieję, że już pobrałeś mój e-book Excel Dynamic Arrays Straight to the Point. Przejdź do stron 31-33, aby uzyskać pełne wyjaśnienie podnoszenia.

Zobacz moją książkę, aby uzyskać pełne wyjaśnienie podnoszenia (i później, kiedy przejdziesz do sortowania wyników, Lifting parami)

Weź funkcję programu Excel, która oczekuje jednej wartości. Na przykład =CHOOSE(Z1,"Apple","Banana")zwróci wartość Jabłko lub Banan w zależności od tego, czy Z1 zawiera 1 (dla Apple) czy 2 (dla Banana). Funkcja WYBIERZ oczekuje wartości skalarnej jako pierwszego argumentu.

Zamiast tego przekażesz stałą tablicową (1,2) jako pierwszy argument funkcji WYBIERZ. Excel wykona operację podnoszenia i dwukrotnie obliczy WYBIERZ. Dla wartości 1 chcesz, aby przedstawiciele handlowi znajdowali się w B2: B227. Dla wartości 2 chcesz otrzymać produkty w D2: D227.

Powiedz WYBIERZ, aby zwrócić dwie odpowiedzi

Zwykle w starym Excelu niejawne przecięcie zepsułoby wyniki. Ale teraz, gdy program Excel może rozlać wyniki na wiele komórek, powyższa formuła pomyślnie zwraca tablicę wszystkich odpowiedzi w B i D:

Sukces! Stąd wszystko jest w dół

Czuję, że obraziłbym twoją inteligencję, pisząc resztę artykułu, ponieważ stąd jest to bardzo proste.

Owiń formułę z poprzedniego zrzutu ekranu w UNIQUE, a otrzymasz tylko unikalne kombinacje przedstawiciela handlowego i produktu =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Nadal nie posortowane

Aby sprawdzić, czy rozumiesz, spróbuj zmienić powyższą formułę, aby zwracała wszystkie unikalne kombinacje trzech kolumn: Przedstawiciel handlowy, produkt, kolor.

Najpierw zmień stałą tablicową, aby odnosiła się do (1,2,3).

Następnie dodać czwarty argument, aby wybrać kolor, aby powrócić z E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Zwróć unikalną kombinację trzech kolumn

Byłoby miło posortować te wyniki, więc zwracamy się do Sortuj według formuły przy użyciu SORTUJ i SORTUJ WEDŁUG.

Zwykle funkcja sortowania rosnąco według pierwszej kolumny to =SORT(Array)lub =SORT(Array,1,1).

Aby posortować według trzech kolumn, musisz wykonać podnoszenie parami =SORT(Array,(1,2,3),(1,1,1)). W tej formule, gdy dojdziesz do drugiego argumentu SORTUJ, program Excel chce wiedzieć, według której kolumny sortować. Zamiast pojedynczej wartości wyślij trzy kolumny wewnątrz stałej tablicowej: (1,2,3). Gdy dojdziesz do trzeciego argumentu, w którym określasz 1 dla Rosnąco lub -1 dla Malejąco, wyślij stałą tablicową z trzema 1, aby wskazać Rosnąco, Rosnąco, Rosnąco. Poniższy zrzut ekranu przedstawia =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Aby uzyskać więcej informacji na temat podnoszenia parami, zobacz stronę 34 w Excel Dynamic Arrays Straight to the Point.

Przynajmniej do końca 2018 r. Możesz bezpłatnie pobrać książkę Excel Dynamic Arrays, korzystając z łącza u dołu tej strony.

Zachęca mnie, że odpowiedź na dzisiejsze pytanie jest nieco skomplikowana. Kiedy pojawiły się tablice dynamiczne, od razu pomyślałem o wszystkich niesamowitych formułach opublikowanych w Message Board przez Aladina Akyurka i innych oraz o tym, jak te formuły staną się znacznie prostsze w nowym Excelu. Jednak dzisiejszy przykład pokazuje, że geniusze formuł nadal będą potrzebować nowych sposobów korzystania z tablic dynamicznych.