Excel trik sumu počet buniek s variabilným rozsahom

Anonim

V programe Excel môžete zhrnúť počet buniek pomocou variabilného rozsahu s funkciou INDIRECT. Funkcia INDIRECT automaticky aktualizuje rozsah buniek, na ktoré ste odkazovali, bez manuálnej úpravy samotného vzorca. Môžete použiť funkciu INDIRECT s ľubovoľným počtom funkcií programu Excel, ale najobvyklejšie (a užitočné) je, keď používate funkciu SUM.

Poznámka: Informácie v tomto článku sa vzťahujú na verzie programu Excel 2019, 2016, 2013, 2010 a Excel pre Mac.

01 z 05

Ako zhrnúť počet buniek pomocou variabilného rozsahu

Funkciu INDIRECT môžete vložiť ako argument vnútri funkcie SUM na vytvorenie variabilného rozsahu bunkových odkazov pre funkciu SUM, ktorú chcete pridať. Funkcia INDIRECT to robí tak, že nepriamo odkazuje na rozsah buniek prostredníctvom odkazu medzi bunkami.

Formát obidvoch funkcií použitých spoločne vyzerá takto:

= SUM (nepriamy ( "D1": D4)

Tým sa uzamkne rozsah, ktorý začína na D1 a dovolí zmenu D4, ak vložíte alebo vymažete všetky bunky v stĺpci D.

Môžete použiť aj iné bunky v tabuľke na úpravu odkazov na bunky. Napríklad, ak použijete E1 na označenie prvej bunky rozsahu a E2 odkazuje na poslednú bunku rozsahu, vzorec vyzerá takto:

= SUM (NEPRIAME ("D" a E1 & ": D" & E2))

Zmenou čísiel umiestnených v bunkách E1 a E2 môžete upraviť rozsah vo vzore bez toho, aby ste museli manuálne upraviť vzorec.

02 z 05

Vyskúšajte funkcie SUM a INDIRECT

Vytvorte tabuľku na overenie funkcií SUM a INDIRECT sami. Začnite vytvorením prázdnej tabuľky a zadaním nasledujúcich údajov do stĺpcov D a E:

Bunka údaje D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4

Ďalej vytvorte vzorec v bunke F1. Tu je postup:

  1. Vyberte bunku F1, Tu sa zobrazí výsledok tohto príkladu.
  2. vybrať vzorca.
  3. zvoliť Math & Trig na otvorenie rolovacieho zoznamu funkcií.
  4. vybraťSUM v zozname otvorte dialógové okno SUM Function Arguments.
03 z 05

Nest funkciu INDIRECT do funkcie SUM

Ďalej zadajte funkciu INDIRECT do funkcie SUM pomocou tohto dialógového okna.

  1. V Číslo 1 zadajte nasledujúcu funkciu INDIRECT:

    NEPRIAMA ( "D" a E1 a ": D" a E2)

  2. vybrať OK dokončite funkciu a zatvorte dialógové okno.
  3. Číslo 50 sa zobrazí v bunke F1. Toto je súčet buniek D1 až D4.
  4. Keď vyberiete bunku F1, pole vzorec zobrazí:

    = SUM (nepriamy ( "D" a E1 a ": D" a E2))

Keď pochopíte, ako formátovať SUM a vnorené funkcie INDIRECT, budete môcť napísať celú funkciu zobrazenú v kroku 4 priamo do výslednej bunky (v tomto príklade bunka F1).

04 z 05

Vytvorte argument pre funkciu INDIRECT

Vo vyššie uvedenom príklade sú odkazy na bunky E1 a E2 mimo úvodzoviek. Toto v kombinácii s & spojiť čokoľvek, čo sa nachádza vo vnútri buniek E1 a E2, do samotného vzorca.

Tu je postup, ako fungujú prvky vzorca:

  • & sa používa na spojenie textových dát (v tomto prípade písmena D) s odkazom na bunku (E1 a E2), ktorý obsahuje číselnú hodnotu.
  • ' ' musí obklopovať textové údaje vo vnútri odkazov na bunky (v tomto príklade písmeno D).
  • : je oddeľovač medzi prvou bunkou a poslednou bunkou v rozsahu SUM.

Vo vyššie uvedenom príklade je začiatočný bod rozsahu definovaný znakmi: "D" a E1.

Koncový bod rozsahu je definovaný znakmi: ": D" a E2

Hrubé črevo musí byť zahrnuté do úvodzoviek.

Tretí ampersand v strede sa používa na spojenie dvoch segmentov do jedného argumentu:

"D" & E1 & ": D" & E2 05 z 05

Rozsah funkcií SUM

Funkcia INDIRECT vnorená vo vnútri funkcie SUM umožňuje ľahko zmeniť začiatok a koniec rozsahu, ktorý je celkom nastavený funkciou SUM, bez toho, aby ste museli upravovať samotnú funkciu.

Pomocou metódy zreťazenia v kroku 3 použite čísla v bunkách E1 a E2 v tabuľke na zmenu rozsahu buniek pre funkciu SUM.

Otestujte to vo svojej vlastnej tabuľke úpravou buniek E1 a E2 a sledujte, ako sa zmenia výsledky funkcie SUM.

  1. Vyberte bunku E1.
  2. Zadajte číslo 3.
  3. lis vstúpiť.
  4. Vyberte bunku E2.
  5. Zadajte číslo 6.
  6. lis vstúpiť.
  7. Odpoveď v bunke F1 sa zmení na 90. Toto je súčet čísel obsiahnutých v bunkách D3 až D6.

Ak chcete vidieť funkciu INDIRECT v akcii, vložte novú bunku do bunky D3. Toto posúva všetky ostatné bunky nadol. Nová suma predstavuje celkový počet buniek D3 až D7. Funkcia INDIRECT dynamicky zmení celý rozsah tak, aby zahŕňala celý zoznam, ktorý ste pôvodne zadali, a novú bunku, ktorú ste vložili.

#REF! Hodnota chyby

#REF! hodnota chyby sa zobrazí v bunke F1, ak je funkcia INDIRECT:

  • Obsahuje neplatnú referenciu bunky.
  • Obsahuje externý odkaz na iný pracovný hárok, ktorý neexistuje.
  • Vzťahuje sa na rozsah buniek mimo limitu pracovného hárka.
  • Vzťahuje sa na prázdne miesto.

Redakcia Choice