Excel Trick za povzemanje števila celic s spremenljivim dosegom

Anonim

V Excelu lahko s funkcijo INDIRECT sumi več celic s spremenljivim dosegom. Funkcija INDIRECT samodejno posodablja obseg celic, na katere ste sklicevali, ne da bi ročno urejali formulo. Funkcijo INDIRECT lahko uporabite z vsemi funkcijami Excel, najpogostejša (in uporabna) pa je, če uporabljate funkcijo SUM.

Opomba: Informacije v članku veljajo za Excelove različice 2019, 2016, 2013, 2010 in Excel za Mac.

01 od 05

Kako povzamemo število celic s spremenljivim dosegom

Funkcijo INDIRECT lahko vdelate kot argument znotraj funkcije SUM, da ustvarite spremenljivko obseg referenc celic za dodajanje funkcije SUM. Funkcija INDIRECT to naredi tako, da se indirektno sklicuje na obseg celic s pomočjo vmesne referenčne celice.

Format obeh funkcij, ki se uporabljajo skupaj, je videti takole:

= SUM (INDIRECT ("D1": D4)

To zaklene obseg, ki se začne pri D1, in omogoča spreminjanje D4, če vstavite ali izbrišete poljubne celice v stolpcu D.

Za spreminjanje referenc celice lahko uporabite tudi druge celice v preglednici. Na primer, če uporabljate E1 za sklicevanje na prvo celico območja in E2 za sklicevanje na zadnjo celico razpona, formula izgleda takole:

= SUM (INDIRECT ("D" & E1 & ": D" & E2))

S spreminjanjem številk v celicah E1 in E2 lahko spremenite obseg v formuli, ne da bi morali ročno urediti formulo.

02 od 05

Preizkusite funkcije SUM in INDIRECT

Ustvarite preglednico za samodejno testiranje funkcij SUM in INDIRECT. Začnite z ustvarjanjem prazne preglednice in v stolpce D in E vnesite naslednje podatke:

Celica Podatki D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4

Nato ustvarite formulo v celici F1. Evo kako:

  1. Izberite celico F1. Tu se prikaže rezultat tega primera.
  2. Izberite Formule.
  3. Izberite Math & Trig da odprete spustni seznam funkcij.
  4. IzberiteSUM na seznamu, da odprete pogovorno okno SUM Function Arguments.
03 od 05

Gnezdite funkcijo INDIRECT v funkcijo SUM

Nato v to pogovorno okno vnesite funkcijo INDIRECT v funkcijo SUM.

  1. V Ljubljani Številka 1 polje, vnesite naslednjo funkcijo INDIRECT:

    INDIRECT ("D" & E1 & ": D" & E2)

  2. Izberite v redu da dokončate funkcijo in zaprete pogovorno okno.
  3. Številka 50 se pojavi v celici F1. To je vsota celic D1 do D4.
  4. Ko izberete celico F1, polje s formulo prikazuje:

    = SUM (INDIRECT ("D" & E1 & ": D" & E2))

Ko razumete, kako oblikovati funkcije SUM in ugnezdene funkcije INDIRECT, boste lahko v celico rezultata vnesli celotno funkcijo, prikazano v 4. koraku (v tem primeru celico F1).

04 od 05

Ustvarite argument za funkcijo INDIRECT

V zgornjem primeru so sklicevanja na celice E1 in E2 izven narekov. To, v povezavi z & združeni simbol, vgrajuje vse, kar je v notranjosti celic E1 in E2 v sami formuli.

Elementi formule delujejo tako:

  • & se uporablja za združevanje besedilnih podatkov (v tem primeru črke D) s sklicem celice (E1 in E2), ki vsebuje številsko vrednost.
  • ' ' mora obdržati podatke o besedilu znotraj referenčnih celic (v tem primeru črka D).
  • : je ločnica med prvo celico in zadnjo celico v območju SUM.

V zgornjem primeru začetno točko območja določajo znaki: "D" in E1.

Končno točko območja določajo znaki: ": D" & E2

Dvopičje mora biti vključeno v narekovaje.

Tretji ampersand in sredi se uporabljajo za združitev dveh segmentov v en argument:

"D" & E1 & ": D" & E2 05 od 05

Variabilno območje funkcije SUM

Funkcija INDIRECT, vgrajena v funkcijo SUM, omogoča preprosto spremembo začetka in konca območja, ki ga ustvari funkcija SUM, ne da bi morali urediti samo funkcijo.

Z uporabo tehnike konceniranja v 3. koraku uporabite številke, shranjene v celicah E1 in E2 v preglednici, da spremenite obseg celic za funkcijo SUM.

To preizkusite v svoji preglednici tako, da spremenite celice E1 in E2 in pazite, kako se spreminjajo rezultati funkcije SUM.

  1. Izberite celico E1.
  2. Vnesite številko 3.
  3. Pritisnite Vnesite.
  4. Izberite celico E2.
  5. Vnesite številko 6.
  6. Pritisnite Vnesite.
  7. Odgovor v celici F1 se spremeni na 90. To je vsota številk v celicah D3 do D6.

Če želite videti funkcijo INDIRECT v akciji, vstavite novo celico v celico D3. To prestavlja vse ostale celice navzdol. Nova vsota je celica celic D3 do D7. Funkcija INDIRECT dinamično spreminja celotno območje in vključi celoten seznam, ki ste ga prvotno vnesli, ter novo vstavljeno celico.

#REF! Vrednost napake

#REF! vrednost napake se pojavi v celici F1, če je funkcija INDIRECT:

  • Vsebuje neveljavno sklicevanje na celico.
  • Vsebuje zunanje sklicevanje na drug delovni list, ki ne obstaja.
  • Nanaša se na obseg celic zunaj meja delovnega lista.
  • Se nanaša na celico, ki je prazna.

Izbira Urednika