Istnieje zabójcza formuła wyodrębniania unikalnych wartości pasujących do kryteriów. Wymaga Ctrl + Shift + Enter, ale jest potężny i fajny.
Ten przykład wykracza daleko poza ramy tej książki. W programie Excel istnieje tajny typ formuły, który wymaga naciśnięcia Ctrl + Shift + Enter w celu odblokowania uprawnień formuły.
Jeśli ty lub ja potrzebowaliśmy uzyskać unikalną listę wartości z kolumny B, zrobilibyśmy coś takiego, jak użycie zaawansowanego filtru lub tabeli przestawnej lub skopiowanie danych i użycie opcji Usuń duplikaty. Te metody trwają pięć sekund i są łatwe dla Ciebie lub dla mnie.
Problem pojawia się, gdy potrzebujesz przełożonego Twojego menedżera do korzystania z arkusza kalkulacyjnego. Nie możesz mieć nadziei, że wiceprezes ds. Sprzedaży opanuje umiejętność kopiowania i usuwania duplikatów. Potrzebujesz sposobu, aby mieć aktywne formuły, które zawsze będą wyodrębniać unikalne listy wartości.
Formuły na to są absolutnie szalone. Ale działają. Na poniższym rysunku długa formuła w D2 pokazuje, ile unikalnych wartości znajduje się na liście. Jeszcze dłuższa formuła w D5, która została skopiowana, wyodrębnia unikalną listę.
Oto wzór. Nie będę próbował ci tego wyjaśniać.
Ale zrobię następną najlepszą rzecz. Przedstawię cię komuś, kto może ci to wyjaśnić. Mike Girvin wyprodukował tysiące filmów Excel na YouTube w ramach kanału ExcelisFun. Napisał także kilka książek Excel, w tym Ctrl + Shift + Enter - kompletny przewodnik po tych niesamowitych formułach. W książce Mike wyjaśnia szczegółowo tę formułę i wiele innych formuł, abyś mógł zrozumieć, jak działają, i napisać własną.
Jeśli kiedykolwiek zamierzasz zrezygnować z formuły, ponieważ nie można tego zrobić, istnieje duża szansa, że formuły z książki Mike'a ją rozwiążą.
Ctrl + Shift + Enter »
Podziękowania dla Mike'a Girvina, Olgi Kryuchkovej i @canalyze_it za zasugerowanie tej funkcji.
Podczas gdy promuję książkę Mike'a Girvina, powinienem wspomnieć, że powinieneś zajrzeć na kanał ExcelisFun na YouTube, na którym ma tysiące darmowych niesamowitych filmów. Mike i ja nagraliśmy serię zabawnych filmów wideo o pojedynkach w programie Excel, w których pokazujemy różne sposoby rozwiązywania problemów w programie Excel.
Można powiedzieć, że Mike to Elvis z Excela.
Obejrzyj wideo
- Jak uzyskać listę unikalnych wartości
- Filtr zaawansowany tylko z unikalnymi wartościami
- Stół obrotowy
- Formuła formatowania warunkowego
=COUNTIF(G$1:G1,G2)=0 - Usuń duplikaty
- Formuła tablicowa z książki Mike'a Girvina
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2045 - Wyodrębnij unikaty!
Kliknij „i” w prawym górnym rogu, aby przejść do listy odtwarzania zawierającej wszystkie moje wskazówki w tej książce!
W porządku, więc naszym celem na dziś jest baza danych klientów i chcę uzyskać unikalną listę klientów, tylko klientów z tej listy. A stary, stary sposób na zrobienie tego polega na użyciu zaawansowanego filtru, wybraniu danych, przejściu do opcji Dane, Filtr, Zaawansowane, powiedzmy, że chcemy je skopiować do lokalizacji. Miejsce, do którego chcemy skopiować, ma nagłówek, który chcemy, i chcę tylko unikalnych rekordów, kliknij OK, i są klienci, unikalna lista klientów znaleziona na tej liście, super, prawda? Szybszy sposób, Wstaw, Tabela przestawna, Istniejący arkusz roboczy, kliknij OK i zaznacz Klient, BAM, jest unikalna lista klientów.
Hej, formatowanie warunkowe, formatowanie warunkowe rzekomo to robi. Formatowanie warunkowe, podświetlanie komórek, zduplikowane wartości, wybierz wartości unikatowe, kliknij OK i absolutnie nic się nie dzieje. Dzieje się tak, ponieważ ktokolwiek stworzył tę funkcję, nie używa tej samej wersji języka angielskiego, co ja. Dla nich unikalna wartość to wartość, która pojawia się dokładnie raz i tylko raz, w porządku, wtedy ją zaznaczą, jeśli pojawi się 2-3-4-5 razy, w ogóle jej nie zaznaczają, to nie jest przydatne w wszystko. Ale hej, nacisnę tutaj Tab, jeśli naprawdę chcesz to zrobić z formatowaniem warunkowym, a nie wiem, dlaczego byś to zrobił, mogliśmy zrobić to bardzo łatwo. alt = "" OD, utwórz nową regułę, użyj formuły, a formuła mówi: „Policz wszystko od wiersza 1 do wiersza tuż nad nami, sprawdź, czy to równa się temu,jeśli ten element nigdy wcześniej się nie pojawił, zaznacz go na czerwono. ” a na szczycie otrzymujemy unikalne wartości. Następnie możesz przejść do Dane - właściwie tutaj, po prostu kliknij prawym przyciskiem myszy, Sortuj i powiedz Umieść wybrany kolor komórki na górze, a ta unikalna lista zostanie przeniesiona na górę. Wszystko to blednie w porównaniu z tym, co dali nam w Excelu 2010. Upewnij się, że wykonałem kopię danych, pozwól mi zrobić kopię danych, Ctrl + C, podejdź tutaj, Ctrl + V, a następnie usuń duplikaty kliknij OK, BAM, nic nie jest szybsze niż to!pozwól mi zrobić kopię danych, Ctrl + C, podejdź tutaj, Ctrl + V, a następnie usuń duplikaty, kliknij OK, BAM, nic nie jest szybsze niż to!pozwól mi zrobić kopię danych, Ctrl + C, podejdź tutaj, Ctrl + V, a następnie usuń duplikaty, kliknij OK, BAM, nic nie jest szybsze niż to!
Ale o to chodzi, niezależnie od tego, czy chcesz korzystać z filtru zaawansowanego, tabel przestawnych, twardego formatowania warunkowego czy usuwania duplikatów, wszystko to wykracza poza możliwości kierownika. Racja, nigdy nie będziesz w stanie ich do tego zmusić. Więc czasami musisz być w stanie zrobić to za pomocą formuły, a ta formuła pochodzi z mojej aktualnej książki „Ctrl + Shift + Enter”, wymaga naciśnięcia Ctrl + Shift + Enter, aby to zadziałało, jest to niesamowicie niesamowita formuła. W mojej książce „MrExcel XL” chciałem tylko zaznaczyć, że jest w niej niesamowity zestaw formuł. Jeśli musisz coś zrobić, na przykład wyodrębnić unikalną listę klientów lub wyodrębnić wszystkich klientów, którzy pasują do jakiejś określonej listy, w książce Mike'a jest cały rozdział o tym, jak to zrobić. Normalnie prosiłbym cię o zakup mojej książki dzisiaj, nie robię tegoMyślę, że powinieneś kupić moją książkę, myślę, że powinieneś kupić książkę Mike'a, kliknij „i” w prawym górnym rogu, aby się do nich dostać.
Dzisiaj rozmawialiśmy o tym, jak uzyskać unikalne wartości, jest to filtr zaawansowany, tabela przestawna, formuła formatowania warunkowego lub najłatwiejsza metoda usuwania duplikatów. Ale jeśli chcesz uzyskać listę unikatowych wartości bez wykonywania jakichkolwiek czynności przez menedżera w programie Excel, istnieje formuła tablicowa, która rozwiąże ten problem.
Hej, chcę ci podziękować za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem!
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2045.xlsm
