Zaawansowane wprowadzenie filtrów Excel

3 lutego, 2021 admin 0 Comments

użyj zaawansowanego filtra w programie Excel, aby utworzyć listę unikalnych elementów lub wyodrębnić określone elementy do innego arkusza roboczego. Możesz również użyć złożonych kryteriów z zaawansowanym filtrem, więc jest to przydatne, gdy prosty AutoFilter nie może zrobić tego,czego potrzebujesz.

Utwórz zaawansowany filtr programu Excel

obejrzyj ten film, aby zobaczyć kroki konfiguracji zakresu kryteriów i uruchomienia filtra Zaawansowanego. Pisemne instrukcje znajdują się poniżej filmu.,

Utwórz zaawansowany filtr Excela

oto kroki konfiguracji danych i utworzenia Zaawansowanego filtra.

— Ustaw bazę danych

— Ustaw zakres kryteriów (opcjonalnie)

— Ustaw zakres wyodrębniania (opcjonalnie)

— Zastosuj zaawansowany filtr Excel

1. Ustaw bazę danych

  1. pierwszy wiersz (A1:D1) ma nagłówki. Każda kolumna musi mieć unikalny nagłówek — zduplikowane nagłówki będą powodować problemy podczas uruchamiania Zaawansowanego filtra.
  2. kolejne wiersze zawierają dane.
  3. w bazie danych nie ma pustych wierszy.,
  4. na końcu bazy danych znajduje się pusty wiersz, a po prawej pusta kolumna.

2. Ustaw zakres kryteriów (opcjonalnie)

w zakresie kryteriów dla Zaawansowanego filtra programu Excel możesz ustawić reguły dla danych, które powinny pozostać widoczne po zastosowaniu filtra. Możesz użyć jednego kryterium lub kilku.

ostrzeżenie: gdy używasz kryteriów tekstowych, program Excel interpretuje je jako „zaczyna się od”. Jeśli potrzebujesz dokładnego dopasowania, zobacz przykład dokładnego dopasowania poniżej.,

  1. w tym przykładzie komórki F1: F2 są zakresem kryteriów.
  2. nagłówek w F1 dokładnie pasuje do nagłówka (D1) w bazie danych.
  3. Komórka F2 zawiera kryterium. Używany jest operator> (większy niż) o numerze 500(nie zawiera znaku$).

Po zastosowaniu zaawansowanego filtra Excel zamówienia o łącznej wartości powyżej 500 $pozostają widoczne.,

Inne operatory to:
< mniej niż
<= mniej lub równo
>= więcej lub równo
<> nie równa się

3. Skonfiguruj zakres wyodrębniania (opcjonalnie)

jeśli planujesz skopiować dane do innej lokalizacji (na tym samym arkuszu lub innym arkuszu), możesz określić kolumny, które chcesz wyodrębnić.,

UWAGA: jeśli chcesz wyodrębnić wszystkie kolumny, możesz pozostawić zakres wyodrębniania pusty dla zaawansowanego filtra Excel.

  1. wybierz komórkę w lewym górnym rogu zakresu dla wyodrębnionych danych.
  2. wpisz nagłówki kolumn, które chcesz wyodrębnić.
    1. muszą być dokładnie dopasowane do nagłówków kolumn w danych, w pisowni i interpunkcji
    2. kolejność kolumn może być inna
    3. Dowolna lub wszystkie kolumny mogą być uwzględnione.

4., Zastosuj zaawansowany filtr Excel

wykonaj następujące kroki, aby zastosować zaawansowany filtr

UWAGA: jeśli chcesz wysłać dane do innego arkusza, wykonaj następujące kroki: Wyślij dane do innego arkusza

  1. wybierz komórkę w bazie danych.
  2. na karcie Dane wstążki programu Excel kliknij przycisk Zaawansowane, aby otworzyć okno dialogowe Filtr zaawansowany

  1. możesz wybrać filtrowanie listy w miejscu lub skopiować wyniki do innej lokalizacji.
  2. Excel powinien automatycznie wykryć zakres listy. Jeśli nie, możesz wybrać komórki w arkuszu.,
  3. Wybierz zakres kryteriów w arkuszu
  4. jeśli kopiujesz do nowej lokalizacji, wybierz początkową komórkę do kopii
  • Uwaga: jeśli skopiujesz do innej lokalizacji, wszystkie komórki poniżej zakresu wyodrębniania zostaną wyczyszczone po zastosowaniu filtra Zaawansowanego.
  • kliknij OK

    • Filtruj unikalne rekordy

    dzięki zaawansowanemu filtrowi programu Excel możesz filtrować dane w miejscu lub w innej lokalizacji. Stosując filtr zaawansowany, możesz określić, że chcesz widzieć tylko unikalne elementy., Po wybraniu tej opcji wszystkie duplikaty są usuwane z filtrowanej listy.

    pisemne instrukcje dotyczące tej techniki znajdują się poniżej filmu.

    Filtruj unikalne rekordy

    Możesz użyć zaawansowanego filtra Excel, aby wyodrębnić listę unikalnych pozycji w bazie danych. Na przykład uzyskaj listę klientów z listy zamówień lub skompiluj listę sprzedanych produktów. W tym przykładzie unikalna lista jest kopiowana do innej lokalizacji, a oryginalna lista pozostaje niezmieniona.

    Uwaga: lista musi zawierać nagłówek, w przeciwnym razie pierwszy element może zostać powielony w wynikach.,

    1. wybierz komórkę w bazie danych.
    2. na karcie Dane wstążki programu Excel kliknij przycisk Zaawansowane.
    3. w oknie dialogowym Filtr zaawansowany wybierz opcję „Kopiuj do innej lokalizacji”.
    4. dla zakresu listy wybierz kolumny,z których chcesz wyodrębnić unikalne wartości.
    5. pozostaw zakres kryteriów pusty.
    6. wybierz komórkę startową do skopiowania do lokalizacji.
    7. Dodaj znacznik wyboru do pola tylko unikalne rekordy.
    8. kliknij OK.

    wideo: Usuń duplikaty

    w programie Excel 2007 i nowszych wersjach istnieje narzędzie Usuń duplikaty., W przeciwieństwie do opcji tylko unikalne rekordy filtra Zaawansowanego, nie pozostawia to niezmienionej oryginalnej listy – całkowicie usuwa wszystkie zduplikowane elementy z listy. Pozostało tylko pierwsze wystąpienie każdego elementu.

    możesz pobrać przykładowy plik, który został użyty w tym filmie.

    Wyodrębnij dane do innego arkusza

    za pomocą zaawansowanego filtra możesz wyodrębnić dane do innego arkusza. Obejrzyj ten film, aby zobaczyć kroki, a pisemne instrukcje znajdują się poniżej filmu.,

    Wyodrębnij dane do innego arkusza

    Jeśli dane programu Excel znajdują się na jednym arkuszu, możesz wyodrębnić dane do innego arkusza za pomocą zaawansowanego filtra. W tym przykładzie dane znajdują się w arkuszu 1 i zostaną skopiowane do arkusza 2.

    1. przejdź do Sheet2 (zobacz kroki na wideo powyżej)
    2. wybierz komórkę w nieużywanej części arkusza (komórka C4 w tym przykładzie).
    3. na karcie Dane wstążki programu Excel kliknij Zaawansowane
    4. wybierz Kopiuj do innej lokalizacji.
    5. kliknij w polu Zakres listy
    6. Wybierz Arkusz1 i wybierz bazę danych.,
    7. (opcjonalnie) kliknij w polu Zakres kryteriów.
    8. Wybierz zakres kryteriów
    9. kliknij w pole Kopiuj do.
    10. wybierz komórkę Sheet2, w której chcesz rozpocząć wyniki, lub wybierz nagłówki, które wpisałeś w Sheet2.
    11. (opcjonalnie) zaznacz pole tylko dla unikalnych wartości
    12. kliknij OK

    Ustawianie zakresu kryteriów

    te przykłady pokazują, jak ustawić zakresy kryteriów dla jednego lub wielu kryteriów, używając tekstu, liczb i wildcards., Możesz również używać formuł w zakresie kryteriów — patrz przykłady na tej stronie: filtry zaawansowane — kryteria złożone

    ostrzeżenie: gdy wprowadzasz kryteria tekstowe, program Excel interpretuje je jako „zaczyna się od”. Jeśli potrzebujesz dokładnego dopasowania, zobacz przykład dokładnego dopasowania poniżej.

    lokalizacja zakresu kryteriów

    przykłady na tej stronie pokazują zakres kryteriów w pobliżu danych, ale nie jest to wymagane.

    • zakres kryteriów może znajdować się na tym samym arkuszu co dane lub na innym arkuszu.
    • zakres kryteriów może znajdować się bezpośrednio nad danymi lub w kolumnach po lewej lub prawej stronie danych.,

    Uwaga:

    Jeśli zakres kryteriów znajduje się w tym samym arkuszu i w tych samych wierszach Co dane, komórki kryteriów mogą być ukryte po zastosowaniu zaawansowanego filtra „na miejscu”.

    To nie wpłynie na Filtr zaawansowany – po prostu nie będziesz mógł zobaczyć niektórych kryteriów.

    I vs lub

    Jeśli rekord spełnia wszystkie kryteria w jednym wierszu w obszarze kryteria, przejdzie przez zaawansowany filtr Excel.,

    i reguła

    w tym przykładzie istnieje jeden wiersz reguł kryteriów:

    • nazwa klienta musi zaczynać się od „MegaMart”
    • , A Nazwa produktu musi zaczynać się od „Cookies”
    • a suma musi być większa niż 500

    lub reguły

    w tym przykładzie są 3 Wiersze z regułami kryteriów.

    kryteria dla różnych wierszy są łączone za pomocą operatora OR., W drugim przykładzie —

    • nazwa klienta musi zaczynać się od „MegaMart”
    • lub Nazwa produktu musi zaczynać się od „Cookies”
    • lub suma musi być większa niż 500.

    i/lub reguły

    używając wielu wierszy, możesz łączyć operatory AND I OR, jak w tym przykładzie:

    • nazwa klienta musi zaczynać się od „MegaMart”, A Nazwa produktu musi zaczynać się od „Cookies”
      • lub
    • nazwa produktu musi zaczynać się od „Cookies”
      • zacznij od „plików cookie”, a suma musi być większa niż 500.,

      używanie symboli wieloznacznych w kryteriach

      użyj znaków wieloznacznych do filtrowania ciągu tekstowego w komórce.

      — the * Wildcard

      — The ? wildcard

      — ~ wildcard

      * wildcard

      znak gwiazdki (*) wildcard reprezentuje dowolną liczbę znaków w tej pozycji, w tym zero znaków.

      UWAGA: Ponieważ program Excel interpretuje kryteria tekstowe jako „zaczyna się od”, nie musisz dodawać * wildcard na końcu łańcucha

      w tym przykładzie kryterium klienta jest: *o*r

      The ?, wildcard

      znak zapytania (?) symbol wieloznaczny reprezentuje jeden znak w tej pozycji.

      w tym przykładzie każda nazwa produktu zaczynająca się od c, po której następuje dowolny pojedynczy znak, a następnie ke, przejdzie przez zaawansowany filtr Excel. Zarówno Cola, jak i ciasto znajdują się w filtrowanych wynikach.

      ~ wildcard

      tylda (~) wildcard umożliwia wyszukiwanie znaków, które są używane jako symbole wieloznaczne.,

      w pierwszym przykładzie gwiazdka znajduje się w komórce kryteriów — Good*Eats — więc każda nazwa klienta zaczynająca się od Good, a następnie dowolne znaki, po których następuje Eats, przechodzą przez zaawansowany filtr Excel.,

      aby znaleźć tylko nazwę klienta zaczynającą się od Good*Eats, użyj znaku tyldy (~) przed gwiazdką w komórce critereia: Good~*Eats

      przykłady kryteriów

      Zobacz więcej kryteriów przykłady na tej stronie: filtry zaawansowane — złożone kryteria

      — wyodrębnij elementy z zakresu

      — wyodrębnij elementy z określonym tekstem

      wyodrębnij elementy z zakresu

      aby wyodrębnić listę elementów z zakresu, możesz użyć dwóch kolumn dla jednego z pól., W tym przykładzie dla pola Total używane są dwie kolumny.

      Jeśli w tym samym wierszu wpisujesz dwa kryteria, tworzysz instrukcję AND. W tym przykładzie każdy wyodrębniony rekord musi być:

      • większy lub równy 500
        • I
      • mniejszy lub równy 750

      Wyodrębnij elementy z określonym tekstem

      gdy używasz tekstu jako kryteriów z zaawansowanym filtrem programu Excel, Excel znajduje wszystkie elementy zaczynające się od tego tekstu.,

      na przykład, jeśli wpiszesz „lód” jako kryterium, Excel znajdzie „lód”,” lody ” i „mleko lodowe”, ponieważ wszystkie zaczynają się od”lód”.

      aby wyodrębnić tylko rekordy, które są dokładnie zgodne z tekstem kryteriów, użyj następującego formatu w zakresie kryteriów (duże lub małe litery nie wpływają na wyniki):
      =”=ice”

      Pobierz przykładowy skoroszyt

      Pobierz spakowany skoroszyt Excel advanced filter z przykładowymi danymi i kryteriami.,

      For Excel 2003 instructions, see Excel 2003 Advanced Filter Introduction

      More Tutorials

      Advanced Filters — Complex Criteria

      Advanced Filter Criteria Slicers

      Advanced Filter Macros

    Dodaj komentarz

    Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *