Excel 2007: Użyj tych trzech technik do filtrowania ładunków z turbodoładowaniem

przez Davida Ringstroma, CPA

Czy wiesz, że program Excel 2007 posiada ukryte polecenie, które umożliwia filtrowanie listy za pomocą tylko jednego kliknięcia myszką? I że istnieją dwa sposoby na zsumowanie tylko widocznych wierszy w przefiltrowanej liście? Przeczytaj dalej, aby odkryć te techniki oszczędzania czasu.

Nie wiesz, jak się filtruje? Odczytaj Excel Filtrowanie 101 .

Wielkość
Kolor
Ilość
Mały
Niebieski
8,038
Medium
Niebieski
4,245
XL
Niebieski
5,241
XXL
Niebieski
8,797
XXXL
Niebieski
5,145
Mały
Orange
2,571
Medium
Orange
5,595
XL
Orange
8,825
XXL
Orange
1,997
XXXL
Orange
6,169
Mały
Zielony
6,192
Medium
Zielony
3,380
XL
Zielony
4,024
XXL
Zielony
2,020
XXXL
Zielony
8,295
Mały
Czarny
2,841
Medium
Czarny
4,705
XL
Czarny
4,202
XXL
Czarny
4,846
XXXL
Czarny
2,431
Tabela 1: Użyj tej próbki danych dotyczących sprzedaży koszulki do zbadania Filtrowanie w programie Excel 2007.

Filtr kontra filtr automatyczny

Jak zapewne wiesz, polecenie Filtr pojawia się zarówno na zakładkach Dom i Dane na wstążce programu Excel 2007. Jednak przejście tej drogi w celu przefiltrowania listy oznacza zazwyczaj wielokrotne kliknięcie myszą. Zamiast tego zalecam dodanie polecenia AutoFilter do paska narzędzi szybkiego dostępu:

  1. Kliknij prawym przyciskiem myszy w dowolnym miejscu na wstążce programu Excel 2007, a następnie wybierz opcję Dostosuj pasek narzędzi szybkiego dostępu.
  2. Jak pokazano na Rysunku 1, należy wybrać opcję Polecenia nie na wstążce, a następnie dodać filtr AutoFilter do paska narzędzi szybkiego dostępu. Zamknij okno dialogowe pokazane na Rysunku 1.

Rysunek 1 : AutoFilter jest jednym z wielu tajnych poleceń, które można dodać do paska narzędzi szybkiego dostępu. (Kliknij na obrazek, aby rozwinąć.)

Nowy w QAT? Dowiedz się więcej o pasku narzędzi szybkiego dostępu za pośrednictwem sieci księgowych firmy Microsoft Professional Accountant’s Network.

Mimo, że nazwa jest podobna do polecenia Filtr, AutoFilter ma specjalną funkcję: natychmiast filtruje Twoją listę na podstawie zawartości aktualnie wybranej komórki:

  1. Skopiować i wkleić dane próbki z Tabeli 1 do pustego arkusza.
  2. Kliknij komórkę B14 – która zawiera słowo “zielony” – a następnie kliknij przycisk AutoFilter, który został dodany do paska narzędzi szybkiego dostępu. Jak pokazano na Rysunku 2, twoja lista powinna teraz pokazywać tylko zieloną sprzedaż koszulki.

Caveat : Funkcja AutoFilter pozwala na filtrowanie tylko jednej wartości, np. słowa zielonego, więc nadal trzeba będzie korzystać z list rozwijanych filtrów w celu bardziej złożonego filtrowania.

Rysunek 2 : Użyj AutoFiltera, aby utworzyć podsumowanie za pomocą tylko dwóch kliknięć myszką. :

Ważne : Mimo że funkcja AutoFilter włącza strzałki, nadal należy wybrać opcję Sort & Filter, a następnie Filter, aby wyłączyć strzałki filtrujące.

Analizuj widoczne rzędy

Teraz przyjrzyjmy się dwóm sposobom analizy danych podczas ich filtrowania. Po pierwsze, wielu użytkowników nie zwraca uwagi na fakt, że program Excel 2007 automatycznie wyświetla sumę wybranych komórek na pasku stanu u dołu ekranu, jak pokazano na Rysunku 3. Jeśli nie widzisz tej sumy, kliknij prawym przyciskiem myszy na pasku stanu i włącz opcję Suma. Program Excel zazwyczaj wyświetla również te statystyki: Średnia, Licznik, Licznik liczbowy, Minimalny i Maksymalny.

Rysunek 3 : Pasek stanu generalnie daje natychmiastową sumę dla wybranych komórek. (Kliknij na obrazek, aby rozwinąć.)

Można to również osiągnąć za pomocą funkcji SUBTOTAL. Aby to zrobić, wprowadź ten wzór w komórce C23:

=SUBTOTAL(9,C1:C21)

9 instruuje SUBTOTAL, że chcemy sumować. Jak pokazano na rysunku 4, program Excel automatycznie wyświetla dodatkowe opcje podczas wpisywania formuły. SUBTOTAL jest podobny do SUM, ale ma specjalną możliwość tylko zliczania widocznych wierszy.

Rysunek 4 : Funkcja SUBTOTAL ma kilka możliwości. :

Dla porównania należy wpisać ten wzór w komórce C24:

=Suma(C1:C21)

Teraz przefiltruj listę dla pomarańczowych koszulek. Jak pokazano na rysunku 5, SUBTOTAL powinien zwracać 25,157, natomiast SUM zawsze zwraca 99,559, niezależnie od sposobu filtrowania listy. SUM zawsze równa się sumie wszystkich wierszy, niezależnie od ich ukrytego statusu. SUBTOTAL posiada inne ciekawe możliwości, które wkrótce omówię w osobnym artykule.

Rysunek 5 : SUBTOTAL wyrównuje tylko widoczne rzędy, natomiast SUM wyrównuje wszystkie rzędy.

Przeczytaj więcej artykułów Davida Ringstroma.

O autorze:

David H. Ringstrom, CPA kieruje firmą Accounting Advisors, Inc. z siedzibą w Atlancie, świadczącą usługi szkoleniowe i doradcze w zakresie baz danych w całym kraju. Skontaktuj się z Davidem pod adresem [chronione pocztą elektroniczną] lub śledź go na Twitter . David przemawia na konferencjach o programie Microsoft Excel i prezentuje webcasty dla kilku dostawców CPE, w tym dla partnera AccountingWEB .