Utwórz sumy ad hoc dla zestawu danych programu Excel, w którym będziesz filtrować dane. Również w tym filmie: Używanie SUBTOTAL (109,) do sumowania danych, które zostały ręcznie ukryte. Użyj Ctrl + Ditto, aby skopiować dokładną formułę o jeden wiersz w dół.
Po zastosowaniu filtra byłoby wspaniale zobaczyć sumę widocznych komórek.
Wybierz pustą komórkę poniżej każdej kolumny liczbowej. Kliknij przycisk Autosumowanie.
Zamiast wstawiać formuły SUMA, program Excel wstawia =SUBTOTAL(9,… )formuły. Poniższy wzór pokazuje sumę tylko widocznych komórek.
Wstaw kilka pustych wierszy nad danymi. Wytnij formuły spod danych i wklej do wiersza 1 z etykietą Total Visible.
Teraz, gdy zmienisz filtry, nawet jeśli dane wypełniają więcej niż jeden pełny ekran danych, zobaczysz sumy u góry arkusza.
Podziękowania dla Sama Radakovitza z zespołu Excela za filtrowanie według wyboru - nie za sugerowanie opcji Filtruj według wyboru, ale za sformalizowanie funkcji Filtruj według wyboru!
Obejrzyj wideo
- Utwórz sumy ad hoc dla zestawu danych programu Excel, w którym będziesz filtrować dane. Również w tym filmie: Używanie SUBTOTAL (109,) do sumowania danych, które zostały ręcznie ukryte. Użyj Ctrl + Ditto, aby skopiować dokładną formułę o jeden wiersz w dół.
Transkrypcja wideo
Podcast jest sponsorowany przez „MrExcel XL”, książkę zawierającą ponad 40 wskazówek dotyczących programu Excel, komiksy, koktajle, tweety i dowcipy w programie Excel.
Naucz się programu Excel z podcastu, odcinek 1976 - Total the Visible Cells!
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Oczywiście dla wszystkich pierwszą formułą, którą tworzy większość ludzi, jest Autosumowanie. Jesteś tutaj, poniżej kolumny liczb, dostajesz przycisk Autosumowanie, który użyje funkcji sumowania do zsumowania liczb powyżej, dopóki nie napotka formuły lub tekstu. Dobrze? Lub Alt + = robi dokładnie to samo. Lub, jeszcze lepiej, wybierz wiele komórek, a następnie naciśnij Autosumowanie, a umieści wszystkie te formuły bez konieczności potwierdzania formuł.
Ale oto fajna sztuczka, w porządku, więc włączmy AutoFilter. Użyjemy więc sztuczki Filtruj według wyboru z wczorajszego podcastu. Dane są filtrowane. Mam zamiar przejść do pierwszego pustego wiersza pod danymi, a następnie nacisnąć przycisk Autosumowanie. Ale widzisz, dzieje się coś zupełnie innego. Zamiast funkcji SUMA otrzymujemy = SUMA CZĘŚCIOWA (9! A co to jest, to jest Suma widocznych komórek. Dobra, bardzo fajnie, że działa.
Chodzi o to, że prawie dziesięć lat temu prowadziłem seminarium na żywo w programie Excel w Fort Wayne, IN, dla Instytutu Audytorów Wewnętrznych. Ktoś pokazał mi tę sztuczkę i naprawdę ją lubię. Nigdy wcześniej nie widziałem tej sztuczki, była fajna, powiedziałem, że jest świetna, dodam to do seminarium. A potem w następnym roku wróciłem do Fort Wayne i pojawił się menedżer tego gościa, prawda, nie był na pierwszym roku, ponieważ myślał, że wie wszystko o Excelu. Ale wrócił, aby zgłosić tę sztuczkę, i mówi: „Słuchaj, słyszałem, że mój analityk finansowy powiedział ci tę sztuczkę, ale nie powiedział ci najlepszej części, idź w górę nad swoimi danymi i wstaw tam dwa wiersze. wtedy, gdy masz już wszystkie widoczne komórki na dole, wytnij, CTRL + X i wklej, CTRL + V, w porządku, co to jest,jeśli przełączysz się na innego klienta, „W porządku, więc wybiorę tutaj kilku klientów więcej”. I teraz mam więcej klientów, niż zmieści się na ekranie! I nie muszę przewijać w dół, aby zobaczyć sumy, sumy są zawsze na górze! ”
W porządku, ta sztuczka, używając Autosumowania poniżej danych filtru, działa, gdy ukrywasz wiersze za pomocą filtru. A co, jeśli ręcznie ukrywasz wiersze? Więc tutaj wybrałem kilka wierszy, już przeszedłem do formatowania, ukrywania i odkrywania, ukrywania wierszy! Więc teraz mamy ukryte wiersze. Schodzę na sam dół i wciskam Alt + = lub Autosumowanie, a to nie daje mi funkcji SUMA CZĘŚCIOWA, daje mi funkcję SUMA.
W porządku, w tym momencie zrobię specjalną rzecz, zrobię CTRL + '(jak wyżej, apostrof). Spowoduje to obniżenie tej samej formuły bez zmiany odwołania do komórki. Więc to E2: E8 zmieniło się lub nie zmieniło się, zwykle, jeśli chcesz skopiować i wkleić do zmiany na E3: E9. Zamierzam to zmienić na SUMA CZĘŚCIOWA (9, a zobaczysz, że to nie działa, nadal daje nam wszystkie komórki. 1000 + 400 + 800 to nie 4500, obejmuje ukryte wiersze. Ale oto co musisz to zrobić, wciśnij CTRL + 'i zmień to 9 na 109, a otrzymasz: Prawdziwie całkowita widoczność oparta na ręcznie ukrytych wierszach!
Dobra, proszę bardzo, więc kilka różnych sztuczek, AutoSum, Alt + = po prostu daje nam funkcję SUMA. Jeśli jesteś poniżej danych i naciśniesz Autosumowanie, otrzymasz SUMA CZĘŚCIOWĄ (9, która działa świetnie do filtrowania, lub możesz po prostu ręcznie wpisać SUMA CZĘŚCIOWA (109, aby uzyskać sumę widoczną, kiedy ręcznie przefiltrowałeś.
W porządku, oto podsumowanie koncepcji z dzisiejszego wideo: Nawiasem mówiąc, to nie jest nawet wskazówka nr 3 w książce, to jest wskazówka bonusowa nr 2b, prawda, więc między wskazówką 2 a wskazówką 3. Książka obejmuje wszystkie te kwestie przedmiotów. Cóż, dzięki wszystkim, którzy to zasugerowali, ludziom z oddziału IIA w Fort Wayne Indiana, usłyszałem to po raz pierwszy. To świetna książka, możesz ją kupić w formie drukowanej lub e-booka, kliknij „i” w prawym górnym rogu. Zostaniesz przeniesiony do dalszych informacji o książce.
OK, chcę ci podziękować za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem od!
