Note: The other languages of the website are Google-translated. Back to English

Jak shrnout pouze filtrované nebo viditelné buňky v aplikaci Excel?

Možná je pro vás snadné sečíst sloupec čísel v aplikaci Excel, ale někdy musíte filtrovat nebo skrýt některá data, abyste splnili svá kritéria. Po skrytí nebo filtrování a nyní chcete přidat pouze filtrované nebo viditelné hodnoty. Pokud použijete funkci Součet v aplikaci Excel, budou přidány všechny hodnoty včetně skrytých dat, v tomto případě, jak byste mohli sečíst pouze filtrované nebo viditelné hodnoty buněk v aplikaci Excel?


Součet pouze filtrovaných nebo viditelných hodnot buněk se vzorcem

S tím SUBTOTAL funkce, která ignoruje řádky, které byly vyloučeny filtrem, můžete snadno přidat pouze viditelné buňky. Můžete to udělat takto:

Předpokládejme, že máte řadu dat a byla filtrována podle potřeby, viz screenshot:

1. Do prázdné buňky, například C13, zadejte tento vzorec: = Mezisoučet (109, C2: C12) (109 označuje, když sečtete čísla, skryté hodnoty budou ignorovány; C2: C12 je rozsah, jehož součet ignorujete filtrované řádky.) a stiskněte vstoupit klíč.

Poznámka: Tento vzorec vám také může pomoci sečíst pouze viditelné buňky, pokud jsou v listu skryté řádky. Tento vzorec však nelze shrnout ignorováním buněk ve skrytých sloupcích.

Součet / počet / průměr viditelných buněk pouze v určeném rozsahu s ignorováním skrytých nebo filtrovaných buněk / řádků / sloupců

Normálně funkce SUM / Počet / Průměr spočítá všechny buňky ve specifikovaném rozsahu na buňkách hmoty, které jsou skryté / filtrované nebo ne. Zatímco funkce Mezisoučet může součet / počet / průměr pouze ignorovat skryté řádky. Kutools pro Excel PODROBNÉ / VIDITELNÉ / PRŮMĚRNĚ VIDITELNÉ funkce snadno vypočítají zadaný rozsah s ignorováním skrytých buněk, řádků nebo sloupců. Plná verze zkušební verze zdarma 30 dní!
počet reklamních součtů pouze průměrné viditelné buňky

Součet pouze filtrovaných nebo viditelných hodnot buněk pomocí funkce definované uživatelem

Pokud vás zajímá následující kód, může vám také pomoci sečíst pouze viditelné buňky.

1. Podržte ALT + F11 klíče a otevře se Microsoft Visual Basic pro aplikace okno.

2, klikněte Vložit > Modula vložte následující kód do okna modulu.

Function SumVisible(WorkRng As Range) As Double
'Update 20130907
Dim rng As Range
Dim total As Double
For Each rng In WorkRng
    If rng.Rows.Hidden = False And rng.Columns.Hidden = False Then
        total = total + rng.Value
    End If
Next
SumVisible = total
End Function

3. Uložte tento kód a zadejte vzorec = SumVisible (C2: C12) do prázdné buňky. Viz screenshot:

4. Pak stiskněte tlačítko vstoupit klíč a získáte požadovaný výsledek.


Součet / počet / průměr pouze filtrovaných nebo viditelných buněk s programem Kutools pro Excel

Pokud máte nainstalovaný program Kutools pro Excel, můžete rychle vypočítat součet / počet / průměr pouze viditelných nebo odfiltrovaných buněk v aplikaci Excel.

Kutools pro Excel - Zahrnuje více než 300 užitečných nástrojů pro Excel. Plná funkce 30denní bezplatná zkušební verze, není nutná kreditní karta! Get It Now

Chcete-li například sečíst pouze viditelné buňky, vyberte buňku, do které umístíte výsledek sčítání, zadejte vzorec = SUMVISIBLE (C3: C12) (C3: C13 je rozsah, ve kterém budete sčítat pouze viditelné buňky) a stiskněte vstoupit klíč.

A poté se spočítá výsledek s ignorováním všech skrytých buněk. Viz screenshot:

Pro počítání pouze viditelných buněk použijte tento vzorec = COUNTVISIBLE (C3: C12); Pro průměrování pouze viditelných buněk použijte tento vzorec = PRŮMĚRNĚ VIDITELNÉ (C3: C12).

Poznámka: Pokud si vzorec nepamatujete přesně, můžete pomocí následujících kroků snadno sčítat / počítat / průměrovat pouze viditelné buňky:

1. Vyberte buňku, do které umístíte výsledek sčítání, a klikněte Kutools > Funkce > Statistické a matematické > PODROBNÉ (nebo PRŮMĚRNÁ VIDITELNOST, VIDITELNÉ jak potřebujete). Viz screenshot:

2. V úvodním dialogovém okně Funkce Argumenty zadejte rozsah, ve kterém budete součet ignorovat skryté buňky, a klikněte na OK knoflík. Viz screenshot:

Kutools pro Excel - Zahrnuje více než 300 užitečných nástrojů pro Excel. Plná funkce 30denní bezplatná zkušební verze, není nutná kreditní karta! Get It Now

A poté se spočítá výsledek s ignorováním všech skrytých buněk.


Ukázka: Součet / počet / průměr pouze filtrovaných nebo viditelných buněk


Kutools pro Excel obsahuje více než 300 užitečných nástrojů pro Excel, můžete si je vyzkoušet bez omezení do 30 dnů. Stáhněte si a vyzkoušejte zdarma hned!

Snadno sčítat / počítat pouze odfiltrované / viditelné buňky odstraněním skrytých řádků v aplikaci Excel

Při sčítání / počítání odfiltrovaných buněk v aplikaci Excel nebude funkce SUM nebo funkce Počet ignorovat skryté buňky. Pokud jsou odstraněny skryté / filtrované řádky, můžeme viditelné buňky jednoduše sečíst nebo spočítat. Můžete zkusit Kutools pro Excel Odstranit skryté (viditelné) řádky a sloupce nástroj k jeho vyřešení. Plná verze zkušební verze zdarma 30 dní!
reklama smazat skryté řádky sloupce 3

Související články:


Nejlepší kancelářské nástroje produktivity

Kutools pro Excel řeší většinu vašich problémů a zvyšuje vaši produktivitu o 80%

  • Opakované použití: Rychle vložte složité vzorce, grafy a cokoli, co jste dříve používali; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
  • Super Formula Bar (snadno upravit více řádků textu a vzorce); Rozložení pro čtení (snadno číst a upravovat velké množství buněk); Vložit do filtrovaného rozsahu...
  • Sloučit buňky / řádky / sloupce bez ztráty dat; Rozdělit obsah buněk; Zkombinujte duplicitní řádky / sloupce... Zabraňte duplicitním buňkám; Porovnat rozsahy...
  • Vyberte možnost Duplikovat nebo Jedinečný Řádky; Vyberte prázdné řádky (všechny buňky jsou prázdné); Super hledání a fuzzy hledání v mnoha sešitech; Náhodný výběr ...
  • Přesná kopie Více buněk beze změny odkazu na vzorec; Automaticky vytvářet reference do více listů; Vložte odrážky, Zaškrtávací políčka a další ...
  • Extrahujte text, Přidat text, Odebrat podle pozice, Odebrat mezeru; Vytváření a tisk mezisoučtů stránkování; Převod mezi obsahem buněk a komentáři...
  • Super filtr (uložit a použít schémata filtrů na jiné listy); Rozšířené řazení podle měsíce / týdne / dne, frekvence a dalších; Speciální filtr tučnou kurzívou ...
  • Kombinujte sešity a pracovní listy; Sloučit tabulky na základě klíčových sloupců; Rozdělte data do více listů; Dávkový převod xls, xlsx a PDF...
  • Více než 300 výkonných funkcí. Podporuje Office / Excel 2007-2019 a 365. Podporuje všechny jazyky. Snadné nasazení ve vašem podniku nebo organizaci. Kompletní funkce 30denní bezplatná zkušební verze. 60denní záruka vrácení peněz.
karta kte 201905

Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50% a omezuje stovky kliknutí myší každý den!
officetab dno
Komentáře (24)
Zatím žádné hodnocení. Buďte první, kdo ohodnotí!
Tento komentář byl moderátorem webu minimalizován
Ahoj všem, rád bych přidal dvě hodnoty v různých sloupcích, ale pouze s viditelnými buňkami, když používám filtr. např. B3=B2+A3 Zkoušel jsem použít funkci SUBTOTAL, ale nefunguje to. Prosím o radu, předem děkuji! pozdravy,
Tento komentář byl moderátorem webu minimalizován
Vytvořte řádek v horní části řádku, který se rozhodnete použít filtr. Do sloupce, pro který potřebujete součet, napište vzorec =subtotal(9,{počáteční sloupec č.},{koncový sloupec č.}) Například pokud pro sloupec A potřebuji mezisoučty založené na hodnotě ve sloupci b, pak pokud řádek záhlaví pro data je v řádku 2 a řádek 1 je prázdný, pak vložte vzorec do sloupce A1 jako =subtotal(9,A2:a999) Když změníte podmínku filtru ve sloupci B, uvidíte celkovou změnu.
Tento komentář byl moderátorem webu minimalizován
Jak přidám kritéria k 2. možnosti. Např. pokud mám rozsah hodnot, chci, aby vybral hodnoty, které mají ve sloupci kritéria "X".
Tento komentář byl moderátorem webu minimalizován
Když zobrazíte řádky, pak "=SumVisible()" odráží chybu. Je možné změnit "Funkci" tak, aby odrážela buď "Nula" nebo "Žádné skryté řádky"?
Tento komentář byl moderátorem webu minimalizován
Díky Milindo - modul jsem vložil do "Knihy 1" i "Personal.xlsb", ale když otevřu nový soubor, nezdá se mi, že bych měl přístup k funkci. Prosím, co jsem udělal špatně?
Tento komentář byl moderátorem webu minimalizován
ahoj, když žaluji tuto funkci a filtr, součty se nezobrazují. proč?
Tento komentář byl moderátorem webu minimalizován
Měl jsem podobný problém, kdy jsem chtěl vypočítat rozdíly ze dvou buněk v různých řádcích, kde používám datový filtr k rozlišení, které řádky se zobrazí. Problém jsem vyřešil následujícím způsobem: 1. Vytvořte nový sloupec [nebo řádek podle toho, co je skryté] a označte jej jako Viditelný. 2. Vytvořte nový sloupec, který bude použit k zachování hodnoty z posledního „viditelného“ řádku, který chcete snížit. 3. Ve sloupci "Viditelné" použijte vzorec: =+SUBTOTAL(102,cell_in_row_or_column) Toto bude mít 1, jakmile to zadáte, ale bude to 0, pokud buňka není viditelná. 4. Ve sloupci vytvořeném pro krok 2 potřebujete jednoduchý vzorec: +IF(A,B,C); kde A=buňka vrací stav Viditelný, B hodnota, se kterou chcete pracovat ze stejného řádku [pouze kopie], C hodnota ze stejné buňky v předchozím řádku, protože buňka není viditelná. Tímto způsobem se poslední „viditelná“ položka šíří za scénou do řádku nad viditelnými řádky. To vám umožní vytvářet jednoduché vzorce provádějící operace na předchozím řádku. To funguje v mnoha situacích, kdy se potřebujete vypořádat s jednotlivými datovými body, na rozdíl od rozsahů, které jsou lepší s mezisoučet.
Tento komentář byl moderátorem webu minimalizován
Díky moc!! Bylo to velmi užitečné :)
Tento komentář byl moderátorem webu minimalizován
Řešení se mi osvědčilo. :D
Tento komentář byl moderátorem webu minimalizován
Jsem schopen zajistit, aby vaše VBA pro =SUMVISIBLE výše fungovalo dobře. Pokud však moje data běží vertikálně a skrývám řádky. Existuje způsob, jak to naprogramovat, abyste mohli mít data spuštěna vodorovně a stále fungovala, když skryjete sloupce? DÍK!
Tento komentář byl moderátorem webu minimalizován
Omlouvám se, že jsem popletl bezpečnostní kód příspěvků dole a zdá se, že moje otázka mohla být smazána, protože mě požádal, abych zkusil jiný. Mohu získat kód VBA pro SUMVISIBLE, aby fungoval dobře, pokud jsou moje data svislá a skryji řádky. Neplatí to však, pokud moje data běží horizontálně a chci skrýt sloupce. Jde to nějak naprogramovat? Dík!
Tento komentář byl moderátorem webu minimalizován
To je úžasné! Díky moc.
Tento komentář byl moderátorem webu minimalizován
Snažím se použít funkci AVERAGEVISIBLE k zprůměrování 12 největších hodnot ve sloupci, to funguje v normální funkci AVERAGE jako =AVERAGE(LARGE(E971:E1540,{1,2,3,4,5,6,7,8,9,10,11,12 ,XNUMX})), když se však pokusím použít průměrnou viditelnou funkci, vrátí #HODNOTA!, nějaké nápady?
Tento komentář byl moderátorem webu minimalizován
jak mohu přeskočit skryté buňky ze vzorců aplikace Excel ve filtrovaném listu
Tento komentář byl moderátorem webu minimalizován
jak přeskočit skryté buňky z excelových vzorců ve filtrovaném listu....

žlutě zbarvené buňky obsahují skryté buňky kvůli filtru....
Tento komentář byl moderátorem webu minimalizován
Dobrý den,
Jaké druhy výpočtů musíte udělat? Kutools pro Excel podporuje tři funkce pro počítání/součet/průměr ignorování všech skrytých buněk.
Tento komentář byl moderátorem webu minimalizován
Když aplikuji vzorec G3-K2 ve filtrovaném listu aplikace Excel a přetahuji vzorec, obsahuje skryté buňky

například



Platnost Lifting Množ
27054.59 123.41
26931.18 330.98
26600.20 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06


nyní hodnota platnosti přichází po odečtení zvednutého množství (jako 2. hodnota 26931.18=27054.59-123.41)

3. hodnota v platnosti=2. hodnota v platnosti-2. hodnota ve zvednutém množství. a tak dále.....

nyní je tento list filtrován a když přetáhnu vzorce ve sloupci platnosti, obsahuje skryté buňky kvůli filtru.

což mi dává špatný výsledek
Tento komentář byl moderátorem webu minimalizován
Dobrý den,
Normální =G3-K2 nebude ignorovat žádné skryté buňky/řádky/sloupce přetažením ke kopírování. Je mi líto, že pro vás nemohu najít správný vzorec. Všechny vzorce nebo metody uvedené v tomto článku jsou pouze o součtu/počtu/průměru ignorování skrytých buněk.
Tento komentář byl moderátorem webu minimalizován
Platnost Lifting Množ
27054.59 123.41
26931.18 330.98
26600.20 (26600.2=26931.18-330.98) 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06
Tento komentář byl moderátorem webu minimalizován
Když aplikuji vzorec G3-K2 ve filtrovaném listu aplikace Excel a přetahuji vzorec, obsahuje skryté buňky

například



Platnost Lifting Množ
27054.59 123.41
26931.18 330.98
26600.20 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06


nyní hodnota platnosti přichází po odečtení zvednutého množství (jako 2. hodnota 26931.18=27054.59-123.41)

3. hodnota v platnosti=2. hodnota v platnosti-2. hodnota ve zvednutém množství. a tak dále.....

nyní je tento list filtrován a když přetáhnu vzorce ve sloupci platnosti, obsahuje skryté buňky kvůli filtru.

což mi dává špatný výsledek
Tento komentář byl moderátorem webu minimalizován
nebo takto



sloupec a sloupec d

100 10

90 10

80 10



kde 90=100-10, 80=90-10 a tak dále.........

přetažení vzorce zahrnuje skryté buňky ve vzorci
Tento komentář byl moderátorem webu minimalizován
Dobrý den,

Quiero sumar dentro de un rango de fila que al ocultar columnas sume sólo las viditelných.
Ne, logrado hacerlo, alguna fórmula? o tendría que hacerse con una marcro?

Velice vám děkuji !!!
Tento komentář byl moderátorem webu minimalizován
Dobrý den,

Žádné výsledky nebyly nalezeny v žádném souboru, který je zvažován jako viditelné sloupce.

Alguna vzorec nebo alguna makro?

Díky !!!
Tento komentář byl moderátorem webu minimalizován
Dobrý den, Cristobale,
Promiňte, nerozumím vašemu problému jasně, můžete vysvětlit svůj problém v angličtině?
Nebo můžete vložit snímek obrazovky nebo soubor s popisem vašeho problému.
Děkuji!
Zatím zde nejsou žádné komentáře
Zanechat své připomínky
Odesílání jako host
×
Ohodnoťte tento příspěvek:
0   Postavy
Doporučená umístění