32
widoki
Wszystkie tajemnice Excel-funkcyjnego CDF (WYSZUKAJ.PIONOWO), aby wyszukać dane w tabeli i pobierać je do innego
Wskazówki Technologii / / December 19, 2019
Batyanov Denis na prawach gościa autor wyjaśnia w tym poście na temat wyszukiwania danych w jednym arkuszu kalkulacyjnym Excel i wyodrębnić je do drugiego, i odkrywa wszystkie sekrety funkcji widokowej pionowej.
Podczas pracy w przewyższać bardzo często istnieje potrzeba znalezienia danych w tabeli i usunąć je do innego. Jeśli nie wiesz jak to zrobić, a potem czytałem artykuł, będziesz nie tylko uczyć, ale także dowiedzieć się, w jakich warunkach będziesz w stanie wycisnąć maksymalną wydajność systemu. Uważane za najbardziej skutecznych technik, które powinny być stosowane w połączeniu z funkcją CDF.
Nawet jeśli masz latach użytkowania funkcję CDF, z dużym prawdopodobieństwem ten artykuł będzie przydatna i nie pozostawi obojętnym. Ja, na przykład, jak to-specjalisty, a następnie lidera w branży IT, używane VLOOKUP 15 lat, ale Aby poradzić sobie z wszystkimi niuansami się właśnie teraz, kiedy jestem zawodowo Zacząłem uczyć ludzie Excel.
CDF - skrót wpionowe, itd.inspekcja. Podobnie VLOOKUP - pionowy wyszukiwania. Sama nazwa funkcji sugeruje nam, że przeszukuje wiersze tabeli (w pionie - linię do sortowania i mocowania osi), a nie w kolumnach (poziomo - sortowanie kolumny i ustalania linia). Należy zauważyć, że CDF ma siostrę - brzydkie kaczątko, że nigdy nie będzie łabędź - jest funkcją PGR (HLOOKUP). PGR, w przeciwieństwie do CDF, produkuje wyszukiwania poziomy, ale pojęcie Excel (i rzeczywiście koncepcji organizowania danych) oznacza, że tabele mają niewielką liczbę kolumn i wiele więcej linie. Dlatego poszukiwanie strun, musimy wielokrotnie więcej niż kolumn. Jeśli
przewyższać zbyt często używać WYSZUKAJ.POZIOMO, jest całkiem prawdopodobne, że istnieje coś, czego nie rozumiesz w tym życiu.składnia
WYSZUKAJ.PIONOWO ma cztery parametry:
PPS = (
Założę się, że wielu z tych, którzy znają funkcję CDF jako peeling, zapoznania się z opisem czwartego parametru może czuć się nieswojo, ponieważ stosuje się go zobaczyć w nieco inny sposób: zwykle jest mowa o dokładnie zgodnie z wyszukiwania (fałszywych lub = 0), lub o tym samym zakresie widzenia (prawda czy 1).
Teraz konieczne jest zaostrzenie i przeczytaj następny akapit kilka razy, aż pojawi się poczucie sensu tego, co zostało powiedziane do końca. Jest ważne, aby każde słowo. Przykłady pomogą zrozumieć.
Jako tego samego wzoru CDF działa szczególnie
- Rodzaj wzorze I. Jeśli ostatni parametr jest pominięty lub określony jako 1, oczekuje CDF, że pierwsza kolumna jest sortowane w porządku rosnącym, więc poszukiwanie zatrzyma się na tej linii bezpośrednio poprzedzający rząd, w którym większa niż wartość pożądana. Jeśli linia ta nie zostanie znaleziony, to zwraca ostatni wiersz zakresu.
- Rodzaj wzorze II. Jeżeli ostatnia opcja jest określony jako 0, CDF sekwencyjnie skanuje się z pierwszej kolumny tablicy i natychmiast zatrzymuje szukaj po raz pierwszy znaleziono dokładnego odpowiednika z parametrem
, w przeciwnym razie zwracany jest kod błędu # N / A (# N / A). 
formuły programu
CDF typu I
CDF Type II
Implikacje dla gatunku o wzorze I
- Te preparaty można stosować do rozprowadzania w zakresie wartości.
- Jeśli pierwsza kolumna zawiera
duplikaty i sortowane prawidłowo, zostanie on zwrócony ostatniego z wierszy z zduplikowane wartości. - Jeśli spojrzymy na wartości oczywiście bardziej niż można zawierać pierwszą kolumnę, można łatwo znaleźć ostatni wiersz tabeli, która może być bardzo cenne.
- Ten widok zwraca błąd # N / A tylko wtedy, gdy znajduje się wartość mniejszą niż lub równą żądanej.
- Zrozumieć, że formuła zwraca niepoprawną wartość, jeśli tablica nie jest posortowana, dość trudne.
Implikacje dla gatunku o wzorze II
Jeżeli wartość znajduje się w pierwszej kolumnie tablicy kilkakrotnie wzór wybierze pierwszego rzędu w celu późniejszego odzyskania.
Produktywność VLOOKUP
Osiągnięto kulminacyjny miejscu artykułu. Wydaje się również, jaka jest różnica, jeśli wskazać jako ostatni parametr zero lub jeden? Zasadniczo, wszystko chodzi, oczywiście, zero, ponieważ jest to bardzo praktyczne: nie ma potrzeby martwić się o sortowaniu pierwszą kolumnę tablicy widać, okazało się, że wartość lub nie. Ale jeśli masz listę kilku tysięcy wzorów CDF (WYSZUKAJ.PIONOWO), można zauważyć, że od typu CDF II przebiega powoli. Jest to zwykle wszyscy zaczynają myśleć:
- Muszę mocniejszy komputer;
- Muszę szybko formułę, na przykład, wiele osób wie o indeksie + Match (INDEX + MATCH), który jest podobno szybciej nędznym 5-10%.
I kilka osób uważa, że jak najszybciej rozpocząć korzystanie z CDF typu I i dostarczyć w dowolny sposób sortowania pierwszej kolumny jak szybkość operacji CDF wzrośnie 57 razy. Piszę słowa - W pięćdziesiąt siedem razy! Na 57%, a 5700%. Fakt ten Sprawdziłem dość wiarygodnie.
Sekret leży takich szybkich prac w tym, że można zastosować do posortowanej tablicy jest niezwykle skuteczny algorytm wyszukiwania, który jest znany jako sposób przeszukiwanie binarne (bisekcji, dychotomia). Więc niby CDF używa go, a typ CDF II dąży bez optymalizacji w ogóle. To samo stosuje się do dopasowania (dopasowanie), który zawiera te same parametry, jak również z funkcją Oglądając (WYSZUKAJ), który działa tylko na posortowanych tablic i aktywowane w programie Excel dla kompatybilności z Lotosem 1-2-3.
Wzór wady
Wady CDF są oczywiste: po pierwsze, ma ona na celu jedynie pierwszą kolumnę tej tablicy, a po drugie, tuż na prawo od tej kolumnie. A jak wiadomo, to może okazać się, że kolumna, która zawiera informacje niezbędne do lewej kolumny, w której szukamy. Pozbawione tych niedociągnięć już wspomniano kilka receptur główna + MATCH (główna + dopasowania), co sprawia, że jest to najbardziej elastyczne rozwiązanie pobiera dane z tabeli w porównaniu z CDF (WYSZUKAJ.PIONOWO).
Niektóre aspekty zastosowania wzoru w prawdziwym życiu
Zakres wyszukiwania
Klasyczna ilustracja zakresu wyszukiwania - problem określania wielkości zniżki rzędu.
Szukaj ciągów tekstowych
Oczywiście, CDF dąży nie tylko liczby, ale również tekst. Należy pamiętać, że formuła wielkość liter nie rozróżnia. Jeśli używać symboli wieloznacznych, możliwe jest zorganizowanie wyszukiwanie rozmyte. Istnieją dwa wieloznaczny „?” - zastępuje dowolny pojedynczy znak w ciągu tekstowym „*” - zastępuje dowolną ilość dowolnych znaków.
Walcz ze spacjami
Często powstaje pytanie, jak rozwiązać problem z dodatkowych miejsc w poszukiwaniach. Jeśli tabela przeglądowa jest nadal możliwe, aby je wyczyścić, pierwszy parametr o wzorze CDF nie zawsze zależy od Ciebie. Dlatego też, jeśli ryzyko zatkania komórkom dodatkową przestrzeń jest obecna, możliwe jest korzystanie z funkcji TRIM (TRIM) do czyszczenia.
Odmienny Format danych
Jeżeli pierwsza funkcja parametrów CDF odnosi się do komórki, która zawiera liczne, ale które są przechowywane w komórki jako tekst, a pierwsza kolumna macierzy zawiera numery w odpowiednim formacie, wyszukiwanie będzie niepowodzeniem. Odwrotna sytuacja. Problemem jest to rozwiązywane za pomocą parametru transferowej 1 w wymaganym formacie:
PPS = (- d7; Produkty $ A $ 2: $ C $ 5;! 3; 0) - jeżeli D7 zawiera tekst i tabela - numery;
= CDF (D7 & «»); Produkty $ A $ 2: $ C $ 5;! 3; 0) - i odwrotnie.
Nawiasem mówiąc, aby przetłumaczyć tekst w liczbie może być kilka sposobów, wybierz:
- Double Negative -D7.
- Mnożenie jednostka D7 * 1.
- Dodatek z zerowym D7 + 0.
- Budowa pierwszego stopnia D7 ^ 1.
Tłumaczenie tekstu produkowane przez sprzęganie z pustym ciągiem znaków, który powoduje Excel do konwersji typu danych.
Jak stłumić wystawienia # N / A
Jest to bardzo wygodne do czynienia z funkcją IFERROR (IFERROR).
Na przykład: = IFERROR (CDF (D7; Produkty $ A $ 2: $ C $ 5;! 3; 0); «»).
Jeśli CDF zwróci kod błędu # N / A, a następnie będzie przechwytywać JEŻELI.BŁĄD substytut i parametr 2 (w tym przypadku ciąg pusty), a Jeśli nie wystąpił błąd, funkcja ta będzie udawać, że nie ma w ogóle, lecz tylko CDF, przywrócony do normy wynik.
Array
Często pomijany referencja tablica jest bezwzględne, a przy wyciąganiu tablicę „pływa”. Należy pamiętać, że zamiast A2: C5 należy stosować $ 2 $: $ C $. 5
Dobrym pomysłem jest umieszczenie tablicy odniesienia na osobnej kartce w zeszycie. Nie ma się pod nogami, a nie zapisane.
Jeszcze bardziej dobrym pomysłem ogłoszeniu tablicy jako nazwanego zakresu.
Wielu użytkowników są wykorzystywane przy określaniu struktury typu macierz A: C, wskazując kolumny całkowicie. Podejście to ma prawo do istnienia, ponieważ jesteś oszczędził z konieczności śledzić fakt, że tablica zawiera wszystkie sznurki. Jeśli dodać wiersz na kawałku oryginalnej tablicy, zakres określony jako A: C, nie będzie musiał dostosować. Oczywiście, ta składnia powoduje Excel wydać nieco więcej pracy niż z dokładnym wskazaniem zakresu, ale narzut danych jest znikoma. Mówimy o setnych sekundy.
Cóż, na granicy geniuszu - do wystawienia w postaci tablicy SMART table.
Za pomocą funkcji kolumn dla określenia kolumny ekstrakcyjnej
Jeśli tabela, do której można wyodrębnić dane za pomocą CDF, ma taką samą strukturę jak w tabeli przeglądowej, ale tylko zawiera minimalną ilość rzędów w domenie WRT można stosować kolumnowej () funkcji automatycznego wyliczania liczby możliwej do odzyskania Kolumny. W tym przypadku wszystkie CDF-formuła będzie taka sama (nastawione dla pierwszego parametru, który zmienia się automatycznie)! Należy zauważyć, że pierwszy parametr jest absolutnie współrzędnych kolumny.
Tworzenie klucza kompozytowego przez & »|» &
Jeśli istnieje potrzeba, aby spojrzeć na więcej niż jednej kolumny naraz, konieczne jest, aby klucz kompozytowe do wyszukiwania. Jeśli zwrócona wartość nie jest tekst (jak tutaj w przypadku pola „Kod”), a numeryczna, dla Nastąpiło to w wygodnym SUMA.WARUNKÓW wzorze (SUMA.WARUNKÓW) i kolumny klucza integralną nie jest wymagane w ogóle.
Jest to mój pierwszy artykuł na Layfhakera. Jeśli podoba Ci się to, to zapraszam do odwiedzenia moja stronaI chętnie czytać swoje uwagi na temat sekretów użyciu VLOOKUP i tym podobne. Dziękuję. :)
SubskrybowaĆ