Wiele formuł Excela, których używam do list, wymaga nadzoru. Czasami podczas dodawania nowego wiersza pojawia się błąd lub trzeba go zmodyfikować. Ten problem nie występuje w przypadku funkcji TAKE and DROP. Te dwie funkcje pozwalają... Dynamiczna macierz, dzięki której tabele rozszerzają się automatyczniePrzeciągając lub usuwając określone wiersze i kolumny ze zbioru danych, zestaw danych jest automatycznie aktualizowany w miarę zmian.
Połącz je z funkcjami takimi jak SORT i FILTER, a otrzymasz listy, które będą się same aktualizować bez żadnego dodatkowego wysiłku. Oto, jak ich używać, aby tworzyć listy, które będą naprawdę aktualne.
Szybkie linki
TAKE przechwytuje dokładnie te wiersze lub kolumny, których potrzebujesz.
Skieruj go do swoich danych, podaj liczbę wierszy, a on je dostarczy.
Funkcja TAKE robi jedną rzecz dobrze. Pobiera określoną liczbę wierszy lub kolumn z zakresu, zaczynając od dowolnego zdefiniowanego końca. Nie potrzebuje żadnych kolumn pomocniczych ani Zakłócenie INDEX-MATCH jest złożoneSkieruj go do swoich danych, podaj liczbę wierszy, których chcesz użyć, a on je dostarczy.
Poniżej przedstawiono strukturę zdania:
=TAKE(tablica, wiersze, [kolumny])
- szyk: Zakres źródłowy lub tablica, z której został wyodrębniony.
- wydziwianie: Liczba wierszy do zwrócenia. Pobierz liczbę dodatnią z góry; pobierz liczbę ujemną z dołu.
- kolumny (opcjonalnie): Działa w ten sam sposób – przyciąga dodatnie z lewej strony, a ujemne z prawej.
Załóżmy, że masz dane sprzedażowe w komórkach A2:D20 i chcesz uzyskać pierwsze pięć wierszy. Użyjesz następującej formuły:
=WEŹ(A2:D20, 5)
Potrzebujesz trzech ostatnich? Zastąp je wartością -3. Wynik jest automatycznie rozprowadzany do sąsiednich komórek i przeliczany za każdym razem, gdy dane źródłowe ulegają zmianie.
DROP usuwa niechciane wiersze lub kolumny.
Powiedz jej, co ma ignorować, a ona przypomni sobie wszystko inne.
Funkcja DROP jest przeciwieństwem funkcji TAKE. Zamiast określać, co chcesz zachować, wskazujesz funkcji, co chcesz odrzucić, a ona zwraca całą resztę. Jest to dla mnie o wiele łatwiejsze w sytuacjach, gdy dokładnie wiem, ile niechcianych wierszy znajduje się na górze lub dole zbioru danych.
Składnia odzwierciedla funkcję TAKE:
=DROP(tablica, wiersze, [kolumny])
- szyk: Domena lub tablica źródłowa.
- wydziwianie: Liczba wierszy do wykluczenia. Wartości dodatnie są usuwane od góry, a wartości ujemne od dołu.
- kolumny (opcjonalnie): Wartości dodatnie są usuwane z lewej strony, a wartości ujemne z prawej.
Załóżmy, że zbiór danych w zakresie A2:D20 zawiera dwa wiersze podsumowania, które przypominają nagłówek i są zbędne. Poniższa formuła usuwa te dwa wiersze i przywraca resztę.
=USUŃ(A2:D20, 2)
Kolumny można również usuwać za pomocą:
=USUŃ(A2:D20, 0, 1)
Usuwa to pierwszą kolumnę, co jest przydatne, gdy pole ID utrudnia uzyskanie danych. Podobnie jak w przypadku funkcji TAKE, wynik rozszerza się dynamicznie i aktualizuje wraz ze wzrostem danych.
Połączenie funkcji TAKE i DROP w celu precyzyjnej segmentacji danych
Umieść je jeden w drugim, aby wyciągnąć dowolną część ze środka
Funkcje TAKE i DROP oddzielnie pobierają dane z krawędzi zbioru danych. Jeśli jednak umieścisz je jedna w drugiej, możesz wyodrębnić dowolny fragment ze środka, czego żadna z tych funkcji nie potrafi wykonać samodzielnie.
Chodzi o to, aby użyć funkcji DROP do usunięcia niepotrzebnych wierszy na górze, a następnie zawinąć wynik w funkcję TAKE, aby zmniejszyć liczbę zachowanych wierszy. Na przykład, biorąc pod uwagę dane sprzedaży, załóżmy, że chcesz pominąć pierwsze cztery wpisy i wziąć kolejne osiem, aby objąć transakcje od 12 stycznia do 3 lutego. Wzór wyglądałby następująco:
=WEŹ(OPUŚĆ(A2:D20, 4), 8)
Funkcja DROP usuwa pierwsze cztery wiersze (wpisy z 5 stycznia), a funkcja TAKE zwraca kolejne osiem wierszy z tego, co pozostało. Wynik obejmuje wszystkie cztery kolumny, od daty do sprzedawcy, więc otrzymujesz kompletny slajd bez potrzeby dodawania kolumn pomocniczych lub ręcznego definiowania zakresów.
Jest to szczególnie przydatne, gdy zbiór danych rozrasta się z czasem, ponieważ obie funkcje dostosowują się dynamicznie po dodaniu nowych wierszy.
Utwórz samoaktualizującą się listę „Najlepsze N” przy użyciu funkcji TAKE i SORT
Lista jest aktualizowana automatycznie za każdym razem, gdy Twoje dane ulegną zmianie.
W tym miejscu funkcja TAKE zaczyna udowadniać swoją wagę. Połączenie jej z funkcją SORT pozwala na utworzenie listy, która zawsze wyświetla najwyższe (lub najniższe) wartości w zbiorze danych – i aktualizuje się automatycznie przy każdej zmianie danych.
Ponownie wykorzystując dane sprzedażowe, załóżmy, że chcesz uzyskać listę pięciu najbardziej dochodowych transakcji według wartości sprzedaży. Wzór wygląda następująco:
=WEŹ(SORTUJ(A2:J20, 7, -1), 5)
Funkcja SORTUJ sortuje wszystkie 19 wierszy według kolumny 7 (kwota sprzedaży) w kolejności malejącej, a funkcja POBIERZ pobiera pięć pierwszych wpisów z tego posortowanego wyniku. Otrzymujesz pełny wiersz dla każdego wpisu, w tym datę, region, sprzedawcę i wszystkie inne dane, dzięki czemu kontekst pozostaje nienaruszony. Jeśli nie znasz funkcji SORTUJ, nasz przewodnik po… Sortowanie danych w programie Excel Obejmuje podstawy.
Najlepsze jest to, co się dzieje, gdy pojawiają się nowe dane sprzedażowe. Gdy dodasz wiersz z wyższą wartością sprzedaży, automatycznie znajdzie się on w pierwszej piątce. Jeśli... Konwertuj zakres źródłowy do arkusza kalkulacyjnego ExcelAsortyment będzie się również rozszerzał automatycznie, dzięki czemu cała konfiguracja stanie się całkowicie automatyczna.
Utwórz dynamiczną listę „Najnowsze wpisy” za pomocą funkcji DROP i COUNTA
Zawsze prezentujemy najnowsze rzędy
Lista „Top N” jest przydatna, ale czasami po prostu chcesz zobaczyć najnowsze elementy dodane do zbioru danych. Funkcja DROP w połączeniu z funkcją COUNTA dobrze sobie z tym radzi – zlicza liczbę wierszy i usuwa wszystko poza kilkoma ostatnimi.
Oto jak zawsze wyświetlać pięć ostatnich wpisów na podstawie danych sprzedażowych:
=DROP(A2:A20; LICZBA(A2:A20) - 5)
Funkcja COUNTA zlicza wszystkie wypełnione komórki w kolumnie A – w tym przypadku 19. Odejmując 5, funkcja DROP zwraca 14, więc usuwa pierwsze 14 wierszy i zwraca pozostałe pięć. Na razie byłyby to transakcje z marca, od przedstawicieli handlowych, takich jak Mike Wilson, do Toma Rodrigueza.
Jeśli dodasz nowy wiersz za kwiecień, COUNTA wzrośnie do 20, DROP zostanie skorygowane do 15, a wynik przełączy się na wyświetlanie ostatnich pięciu wpisów. Możesz również użyć poniższego wzoru, aby uzyskać ten sam wynik, ale metoda DROP i COUNTA jest bardziej elastyczna, gdy zakres nie jest stały.
=WEŹ(A2:A20, -5)
Funkcje TAKE i DROP najlepiej działają w połączeniu z innymi funkcjami.
Wypróbuj te kombinacje później
Widziałem już funkcje TAKE z funkcją SORT i DROP z funkcją COUNTA, ale jest jeszcze wiele do odkrycia. Użyj funkcji FILTER w funkcji TAKE, aby utworzyć listy, które wyświetlają na przykład sprzedaż elektroniki powyżej 3000 USD — uporządkowaną i określoną według podanej liczby. Możesz też użyć funkcji DROP z funkcją UNIQUE, aby usunąć zduplikowane nagłówki podczas scalania danych z wielu arkuszy. Funkcja CHOOSECOLS w połączeniu z funkcją TAKE może również zredukować duże zbiory danych do tylko istotnych kolumn. Po rozpoczęciu zagnieżdżania tych funkcji, połączenia stają się całkiem praktyczne.
