Odzyskiwanie danych w Microsoft Excel

Podczas pracy z arkuszami kalkulacyjnymi programu Excel często konieczne jest wybranie ich zgodnie z pewnym kryterium lub kilkoma warunkami. W programie możesz to zrobić na różne sposoby, korzystając z wielu narzędzi. Zobaczmy, jak dokonać wyboru w Excelu, korzystając z wielu opcji.

Przykładowe wykonanie

Próbkowanie danych polega na procedurze wyboru z ogólnej tablicy wyników spełniających określone warunki, a następnie wyprowadzenia ich na arkuszu na osobnej liście lub w oryginalnym zakresie.

Metoda 1: Zastosuj zaawansowany autofiltr

Najprostszym sposobem wyboru jest użycie rozszerzonego autofiltra. Zastanówmy się, jak to zrobić na konkretnym przykładzie.

1. Wybierz obszar na arkuszu, spośród których musisz wybrać. Na karcie "Strona główna" kliknij przycisk "Sortuj i filtruj" . Jest umieszczony w bloku ustawień "Edytuj" . Na liście, która zostanie otwarta po tym, kliknij przycisk "Filtr" .

   Istnieje możliwość zrobienia tego samego w inny sposób. Aby to zrobić, po wybraniu obszaru na arkuszu przejdź do zakładki "Dane" . Klikamy przycisk "Filtr" , który znajduje się na wstążce w grupie "Sortuj i filtruj" .

2. Po tej czynności ikony pojawiają się w nagłówku tabeli, aby rozpocząć filtrowanie w postaci małych odwróconych trójkątów na prawej krawędzi komórek. Klikamy na tę ikonę w tytule kolumny, dla której chcemy wybrać. W wyświetlonym menu przejdź do pozycji "Filtry tekstowe" . Następnie wybierz pozycję "Niestandardowy filtr ..." .
3. Okno filtrowania użytkownika jest aktywne. W nim można określić ograniczenie, według którego zostanie dokonany wybór. Na liście rozwijanej dla kolumny zawierającej komórkę formatu liczbowego, której używamy w przykładzie, można wybrać jeden z pięciu rodzajów warunków:
   • jest równy;
   • nie równy;
   • więcej;
   • większy lub równy;
   • mniej.

   Ustawmy warunek jako przykład, abyśmy tylko wybierali wartości, o które przychód przekracza 10 000 rubli. Ustaw przełącznik w pozycji "Więcej" . W prawym polu wpisz wartość "10000" . Aby wykonać akcję, kliknij przycisk "OK" .

4. Jak widać, po filtracji były tylko linie, w których kwota przychodu przekracza 10 000 rubli.
5. Ale w tej samej kolumnie możemy dodać drugi warunek. W tym celu ponownie wracamy do okna filtrowania użytkownika. Jak widać, w jego dolnej części znajduje się jeszcze jeden przełącznik warunku i odpowiadające mu pole wejściowe. Ustawmy teraz górny limit selekcji na 15 000 rubli. Aby to zrobić, ustaw przełącznik w pozycji "Mniej" , a w polu po prawej wpisujemy wartość "15000" .

   Ponadto istnieje również przełącznik warunków. Ma dwie pozycje "I" i "OR" . Domyślnie jest ustawiony na pierwszą pozycję. Oznacza to, że w próbce będą tylko linie spełniające oba ograniczenia. Jeśli jest ustawione na pozycję "OR" , pozostaną wartości odpowiednie dla jednego z dwóch warunków. W naszym przypadku musisz ustawić przełącznik w pozycji "AND" , czyli pozostaw to ustawienie jako domyślne. Po wprowadzeniu wszystkich wartości, kliknij przycisk "OK" .

6. Teraz w tabeli pozostały tylko linie, w których kwota przychodu jest nie mniejsza niż 10 000 rubli, ale nie przekracza 15 000 rubli.
7. Podobnie możesz skonfigurować filtry w innych kolumnach. W tym przypadku możliwe jest również zapisanie filtrowania w poprzednich warunkach, które zostały ustawione w kolumnach. Zobaczmy więc, jak filtr jest tworzony dla komórek w formacie daty. Kliknij ikonę filtra w odpowiedniej kolumnie. Konsekwentnie klikaj elementy na liście "Filtruj według daty" i "Filtr niestandardowy" .
8. Okno Custom AutoFilter zostanie uruchomione ponownie. Dokonamy wyboru wyników w tabeli od 4 do 6 maja 2016 r. Włącznie. W selektorze stanu, jak widać, jest jeszcze więcej opcji niż w przypadku formatu liczbowego. Wybierz pozycję "Po lub Równa". W polu po prawej ustaw wartość na "04.05.2016" . W dolnym bloku ustaw przełącznik na pozycję "Przed lub Równa" . W prawym polu wpisujemy wartość "06.05.2016" . Przełącznik zgodności warunków pozostaje w domyślnej pozycji - "AND" . Aby zastosować filtrowanie w akcji, kliknij przycisk "OK" .
9. Jak widać, nasza lista została jeszcze bardziej ograniczona. Teraz pozostawia jedynie linie, w których kwota przychodów waha się od 10 000 do 15 000 rubli w okresie od 04.05 do 06.05.2016 włącznie.
10. Możemy zresetować filtrowanie w jednej z kolumn. Zróbmy to dla danych o przychodach. Kliknij ikonę automatycznego filtrowania w odpowiedniej kolumnie. Z rozwijanej listy kliknij pozycję "Usuń filtr" .
11. Jak widać, po tych działaniach próbka według kwoty przychodu zostanie wyłączona, a dostępny będzie tylko wybór według dat (od 04.05.2016 do 06.05.2016).
12. W tej tabeli znajduje się jeszcze jedna kolumna - "Nazwa" . Zawiera dane w formacie tekstowym. Zobaczmy, jak wygenerować próbkę, filtrując te wartości.

    Kliknij ikonę filtra w nazwie kolumny. Przejdźmy przez nazwy listy "Filtry tekstu" i "Filtr niestandardowy ..." .

13. Ponownie pojawi się okno Custom AutoFilter. Wybierzmy nazwy "Ziemniak" i "Mięso" . W pierwszym bloku ustaw przełącznik stanu w pozycji "Równy" . W polu po prawej stronie wpisujemy słowo "Ziemniak" . Przełącznik dolnego bloku jest również ustawiony w pozycji "Równy" . W polu naprzeciw niej robimy notatkę - "Mięso" . A potem wykonujemy to, czego wcześniej nie robiliśmy: ustaw przełącznik zgodności warunków na pozycję "OR" . Zostanie wyświetlony wiersz zawierający któryś z określonych warunków. Klikamy przycisk "OK" .
14. Jak widać, w nowej próbie obowiązują ograniczenia dotyczące daty (od 04.05.2016 r. Do 06.05.2016 r.) Oraz nazwy (ziemniaki i mięso). Wysokość przychodów nie jest ograniczona.
15. Całkowicie usuń filtr może być taki sam, jak został użyty do jego instalacji. I nie ma znaczenia, która metoda została użyta. Aby zresetować filtrowanie, w zakładce "Dane" kliknij przycisk "Filtr" , który znajduje się w grupie "Sortuj i filtruj" .

    Druga opcja polega na przełączeniu się na kartę Strona główna . Tam klikamy na wstążkę na przycisku "Sortowanie i filtrowanie" w bloku "Edycja" . Na aktywowanej liście kliknij przycisk "Filtruj" .

Jeśli użyjesz jednej z powyższych dwóch metod, filtrowanie zostanie usunięte, a wyniki wyboru zostaną usunięte. Oznacza to, że tabela pokaże cały zestaw danych, które ma.

Lekcja: Funkcja AutoFilter w programie Excel

Metoda 2: Zastosuj formułę tablicową

Możesz również dokonać wyboru, stosując złożoną formułę tablicową. W przeciwieństwie do poprzedniej wersji, ta metoda dostarcza wynik wyniku w oddzielnej tabeli.

1. Na tym samym arkuszu tworzymy pustą tabelę z tymi samymi nazwami kolumn w nagłówku, co źródło.
2. Wybierz wszystkie puste komórki z pierwszej kolumny nowej tabeli. Ustaw kursor na pasku formuły. W tym miejscu zostanie wprowadzona formuła, dzięki której próbka będzie zgodna z określonymi kryteriami. Wybieramy linie, których przychód przekracza 15 000 rubli. W naszym konkretnym przykładzie formuła wejściowa będzie wyglądać następująco:

   =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Oczywiście w każdym przypadku adres komórek i zakresów będzie taki sam. W tym przykładzie możesz dopasować wzór do współrzędnych na ilustracji i dostosować go do swoich potrzeb.

3. Ponieważ jest to formuła tablicowa, aby zastosować ją w akcji, należy nacisnąć klawisz Enter i kombinację klawiszy Ctrl + Shift + Enter . Robimy to.
4. Wybierz drugą kolumnę z datami i umieść kursor w linii formuły, wprowadź następujące wyrażenie:

   =ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Naciśnij kombinację klawiszy Ctrl + Shift + Enter .

5. Podobnie w kolumnie z przychodami wpisujemy następującą formułę:

   =ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Ponownie wpisz Ctrl + Shift + Enter .

   We wszystkich trzech przypadkach zmienia się tylko pierwsza wartość współrzędnych, w przeciwnym razie formuły są całkowicie identyczne.

6. Jak widać, tabela jest wypełniona danymi, ale jej wygląd nie jest zbyt atrakcyjny, ponadto wartości daty są w niej niepoprawnie wypełnione. Konieczne jest skorygowanie tych niedociągnięć. Nieprawidłowa data wynika z faktu, że format komórek w odpowiedniej kolumnie jest ogólny i musimy ustawić format daty. Wybierz całą kolumnę, w tym komórki z błędami, i kliknij wybraną opcję prawym przyciskiem myszy. Na wyświetlonej liście przejdź do pozycji "Formatuj komórkę ..." .
7. W otwartym oknie formatu otwórz zakładkę "Numer" . W bloku "Formaty liczbowe" wybierz wartość "Data" . W prawej części okna możesz wybrać żądany typ wyświetlania daty. Po ustawieniu ustawień kliknij przycisk "OK" .
8. Teraz data jest wyświetlana poprawnie. Ale, jak widać, cała dolna część tabeli jest wypełniona komórkami zawierającymi błędną wartość "# NUMBER!" . W rzeczywistości są to komórki, dane z próbki, dla których było za mało. Byłoby atrakcyjniejsze, gdyby były wyświetlane w ogóle puste. W tym celu używajmy formatowania warunkowego. Wybierz wszystkie komórki w tabeli, z wyjątkiem limitu. Na karcie "Strona główna" kliknij przycisk "Formatowanie warunkowe" , który znajduje się w oknie narzędzi "Style" . Na wyświetlonej liście wybierz pozycję "Utwórz regułę ..." .
9. W oknie, które zostanie otwarte, wybierz typ reguły "Formatuj tylko komórki, które zawierają" . W pierwszym polu pod tekstem "Formatuj tylko komórki, dla których spełniony jest następujący warunek", wybierz element "Błędy" . Następnie kliknij przycisk "Formatuj ..." .
10. W otwartym oknie formatowania przejdź do zakładki "Czcionka" i wybierz biały kolor w odpowiednim polu. Po tych czynnościach kliknij przycisk "OK" .
11. Na przycisku o tej samej nazwie kliknij po powrocie do okna Create Condition.

Teraz mamy gotową próbkę dla określonego ograniczenia w osobnej prawidłowo zaprojektowanej tabeli.

Lekcja: Formatowanie warunkowe w programie Excel

Metoda 3: Wiele warunków przy użyciu formuły

Podobnie jak przy użyciu filtra, możesz użyć formuły do ​​próbkowania według kilku warunków. Na przykład, weź tę samą tabelę źródłową, a także pustą tabelę, w której będą wyświetlane wyniki, z już wykonanym formatowaniem numerycznym i warunkowym. Ustalamy pierwsze ograniczenie dolnego limitu selekcji dla przychodów 15 000 rubli, a drugim warunkiem jest górny limit 20 000 rubli.

1. W oddzielnej kolumnie wpisujemy warunki brzegowe dla próbki.
2. Podobnie jak w poprzedniej metodzie, jeden po drugim wybiera puste kolumny nowej tabeli i wstawia do nich odpowiednie trzy formuły. W pierwszej kolumnie wpisujemy następujące wyrażenie:

   =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(($D$2=C2:C29);СТРОКА(C2:C29);"");СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($C$1))

   W poniższych kolumnach wprowadzamy dokładnie te same formuły, zmieniając tylko współrzędne bezpośrednio po nazwie operatora INDEKS na kolumnach odpowiadających potrzebnym kolumnom, analogicznie do poprzedniej metody.

   Za każdym razem po wprowadzeniu nie zapomnij wpisać Ctrl + Shift + Enter .

3. Zaletą tej metody w stosunku do poprzedniej jest to, że jeśli chcemy zmienić granice próbkowania, nie musimy zmieniać samej formuły tablicowej, co samo w sobie jest dość problematyczne. Wystarczy w kolumnie warunków na arkuszu zmienić numery granic na te, których potrzebuje użytkownik. Wyniki wyboru zostaną automatycznie zmienione.

Metoda 4: losowe pobieranie próbek

W Excelu za pomocą specjalnej formuły RAND można również użyć losowego wyboru. Wymagane jest, aby był on produkowany w niektórych przypadkach podczas pracy z dużą ilością danych, gdy trzeba przedstawić ogólny obraz bez kompleksowej analizy wszystkich danych tablicy.

1. Po lewej stronie stołu mijamy jedną kolumnę. W komórce następnej kolumny, która jest przeciwna pierwszej komórce z danymi tabeli, wprowadzamy formułę:

   =СЛЧИС()

   Ta funkcja wyświetla losową liczbę. Aby go aktywować, naciśnij przycisk ENTER .

2. Aby utworzyć całą kolumnę liczb losowych, ustaw kursor w prawym dolnym rogu komórki, która już zawiera formułę. Pojawi się uchwyt do wypełniania. Pociągnij w dół lewym przyciskiem myszy wciśniętym równolegle do tabeli danych do końca.
3. Teraz mamy zakres komórek wypełnionych liczbami losowymi. Ale zawiera formułę RAND . Musimy również pracować z czystymi wartościami. Aby to zrobić, skopiuj do pustej kolumny po prawej stronie. Wybieramy zakres komórek z liczbami losowymi. Po zlokalizowaniu na karcie "Strona główna" kliknij ikonę "Kopiuj" na wstążce.
4. Wybierz pustą kolumnę i kliknij prawym przyciskiem myszy, wywołując menu kontekstowe. W grupie narzędzi "Parametry wstawiania" wybierz element "Wartości" , reprezentowany jako piktogram z liczbami.
5. Następnie w zakładce "Strona główna" kliknij znaną ikonę "Sortuj i filtruj" . Z rozwijanej listy wybierz "Sortowanie niestandardowe" .
6. Okno sortowania jest aktywne. Pamiętaj, aby zaznaczyć pole "Moje dane zawiera nagłówki" , jeśli istnieje nagłówek i nie ma zaznaczenia. W polu "Sortuj według" określ nazwę kolumny, w której znajdują się skopiowane wartości liczb losowych. W polu "Sortuj" pozostawiamy ustawienia domyślne. W polu "Zamówienie" możesz wybrać opcję "Rosnąco" lub "Malejąco" . W przypadku losowego próbkowania nie ma to znaczenia. Po wprowadzeniu ustawień kliknij przycisk "OK" .
7. Następnie wszystkie wartości tabeli są uporządkowane w kolejności rosnącej lub malejącej liczby losowej. Możesz pobrać dowolną liczbę pierwszych linii z tabeli (5, 10, 12, 15 itd.) I można je uznać za wynik losowej próbki.

Lekcja: Sortowanie i filtrowanie danych w programie Excel

Jak widać, wybór w arkuszu kalkulacyjnym Excel można wykonać za pomocą automatycznego filtra lub za pomocą specjalnych formuł. W pierwszym przypadku wynik zostanie wyświetlony w tabeli źródłowej, a w drugim - w osobnym obszarze. Można wybrać jeden lub kilka warunków. Ponadto można losowo wybierać za pomocą funkcji RANDOM .

• ← Korzystanie z interpolacji w programie Microsoft Excel
• Korzystanie z funkcji tabelarycznej w programie Microsoft Excel →