Kaip sukurti dinaminį diagramos diapazoną „Excel“

Kai kuriate diagramą „Excel“ ir keičiasi šaltinio duomenys, turite atnaujinti diagramos duomenų šaltinį, kad įsitikintumėte, jog jis atspindi naujus duomenis.

Jei dirbate su dažnai atnaujinamomis diagramomis, geriau sukurti dinaminį diagramų diapazoną.

Kas yra dinaminis diagramos diapazonas?

Dinaminis diagramos diapazonas yra duomenų diapazonas, kuris automatiškai atnaujinamas keičiant duomenų šaltinį.

Tada šis dinaminis diapazonas naudojamas kaip šaltinio duomenys diagramoje. Keičiantis duomenims, dinaminis diapazonas akimirksniu atnaujinamas, o tai atnaujina diagramą.

Žemiau pateikiamas diagramos, kurioje naudojamas dinaminis diagramos diapazonas, pavyzdys.

Atminkite, kad diagrama atnaujinama naujais gegužės ir birželio mėnesio duomenų taškais, kai tik įvedami duomenys.

Kaip sukurti dinaminį diagramos diapazoną „Excel“?

Yra du būdai sukurti dinaminį diagramos diapazoną „Excel“:

• „Excel“ lentelės naudojimas
• Naudojant formules

Daugeliu atvejų „Excel“ lentelės naudojimas yra geriausias būdas sukurti dinaminius diapazonus „Excel“.

Pažiūrėkime, kaip veikia kiekvienas iš šių metodų.

Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą.

„Excel“ lentelės naudojimas

„Excel“ lentelės naudojimas yra geriausias būdas sukurti dinaminius diapazonus, nes jis automatiškai atnaujinamas, kai prie jo pridedamas naujas duomenų taškas.

„Excel“ lentelės funkcija buvo įdiegta „Excel 2007“ „Windows“ versijoje ir, jei naudojate ankstesnes versijas, jos naudoti negalėsite (žr. Kitą skyrių apie dinaminio diagramos diapazono kūrimą naudojant formules).

Pro patarimas: Norėdami konvertuoti langelių diapazoną į „Excel“ lentelę, pasirinkite langelius ir naudokite sparčiuosius klavišus - „Control“ + T (laikykite nuspaudę „Control“ klavišą ir paspauskite klavišą T).

Žemiau pateiktame pavyzdyje matote, kad kai tik pridedu naujų duomenų, „Excel“ lentelė išsiplečia ir įtraukia šiuos duomenis į lentelės dalį (atkreipkite dėmesį, kad kraštinė ir formatavimas išplečiami ir įtraukiami į lentelę).

Dabar, kurdami diagramas, turime naudoti šią „Excel“ lentelę.

Štai tikslios dinaminės linijinės diagramos kūrimo naudojant „Excel“ lentelę veiksmai:

• Pasirinkite visą „Excel“ lentelę.
• Eikite į skirtuką Įterpti.
• Diagramų grupėje pasirinkite diagramą „Linija su žymekliais“.

Viskas!

Pirmiau minėti veiksmai įterptų linijinę diagramą, kuri būtų automatiškai atnaujinama, kai į „Excel“ lentelę įtraukiate daugiau duomenų.

Atminkite, kad pridėjus naujų duomenų automatiškai atnaujinama diagrama, ištrynus duomenis, taškai nebus visiškai pašalinti. Pvz., Jei pašalinsite 2 duomenų taškus, diagramoje bus rodoma tuščia vieta dešinėje. Norėdami tai ištaisyti, vilkite mėlyną ženklą „Excel“ lentelės apačioje dešinėje, kad pašalintumėte iš lentelės ištrintus duomenų taškus (kaip parodyta žemiau).

Nors aš paėmiau linijinės diagramos pavyzdį, naudodami šią techniką galite sukurti ir kitų tipų diagramas, pvz., Stulpelių/juostų diagramas.

Naudojant „Excel“ formules

Kaip jau minėjau, „Excel“ lentelės naudojimas yra geriausias būdas sukurti dinaminius diagramų diapazonus.

Tačiau jei dėl kokių nors priežasčių negalite naudoti „Excel“ lentelės (galbūt, jei naudojate „Excel 2003“), yra dar vienas (šiek tiek sudėtingas) būdas sukurti dinaminius diagramų diapazonus naudojant „Excel“ formules ir pavadintus diapazonus.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:

Norėdami sukurti dinaminį diagramos diapazoną iš šių duomenų, turime:

1. Sukurkite du dinaminius pavadintus diapazonus naudodami formulę OFFSET (po vieną stulpeliuose „Vertės“ ir „Mėnesiai“). Pridėjus/ištrinant duomenų tašką, šie pavadinti diapazonai bus automatiškai atnaujinami.
2. Įterpkite diagramą, kurioje kaip duomenų šaltinis naudojami nurodyti diapazonai.

Leiskite man dabar išsamiai paaiškinti kiekvieną žingsnį.

1 žingsnis - dinaminių pavadintų diapazonų kūrimas

Toliau pateikiami dinaminių pavadintų diapazonų kūrimo veiksmai.

• Eikite į skirtuką „Formulės“.
• Spustelėkite „Vardų tvarkyklė“.
• Dialogo lange Vardų tvarkyklė nurodykite pavadinimą kaip „ChartValues“ ir įveskite šią formulę, nurodančią dalį: = OFFSET (formulė! $ B $ 2 ,,, COUNTIF (formulė! $ B $ 2: $ B $ 100, ””))
• Spustelėkite Gerai.
• Dialogo lange Vardų tvarkyklė spustelėkite Naujas.
• Dialogo lange Vardų tvarkyklė nurodykite pavadinimą kaip Diagrama Mėnesiai ir įveskite šią formulę, nurodančią dalį: = OFFSET (formulė! $ A $ 2 ,,, COUNTIF (formulė! $ A $ 2: $ A $ 100 “))
• Spustelėkite Gerai.
• Spustelėkite Uždaryti.

Atlikus aukščiau nurodytus veiksmus, darbaknygėje buvo sukurti du pavadinti diapazonai - „ChartValue“ ir „ChartMonth“ (jie atitinkamai nurodo duomenų rinkinio reikšmes ir mėnesių intervalą).

Jei atnaujinsite vertės stulpelį pridėdami dar vieną duomenų tašką, dabar „ChartValue“ pavadintas diapazonas bus automatiškai atnaujinamas, kad jame būtų rodomas papildomas duomenų taškas.

Magiją čia atlieka funkcija OFFSET.

„ChartValue“ pavadinto diapazono formulėje mes nurodėme B2 kaip atskaitos tašką. OFFSET formulė prasideda ten ir apima visas užpildytas stulpelio langelius.

Ta pati logika veikia ir „ChartMonth“ pavadinto diapazono formulėje.

2 veiksmas - sukurkite diagramą naudodami šiuos pavadintus diapazonus

Dabar viskas, ką jums reikia padaryti, yra įterpti diagramą, kurioje kaip duomenų šaltinis bus naudojami nurodyti diapazonai.

Štai veiksmai, kaip įterpti diagramą ir naudoti dinaminius diagramų diapazonus:

• Eikite į skirtuką Įterpti.
• Spustelėkite „Įterpti linijos arba srities diagramą“ ir įterpkite diagramą „Linija su žymekliais“. Tai įterps diagramą į darbalapį.
• Pasirinkę diagramą, eikite į skirtuką Dizainas.
• Spustelėkite Pasirinkti duomenis.
• Dialogo lange „Pasirinkti duomenų šaltinį“ spustelėkite mygtuką Pridėti, esantį skiltyje „Legendos įrašai (serija)“.
• Lauke Serijos reikšmė įveskite = Formulė! Diagramos vertės (atkreipkite dėmesį, kad norint, kad tai veiktų, prieš nurodytą diapazoną turite nurodyti darbalapio pavadinimą).
• Spustelėkite Gerai.
• Spustelėkite Redaguoti mygtuką „Horizontalios (kategorijos) ašies etiketės“.
• Dialogo lange „Ašių etiketės“ ​​įveskite = Formulė! ChartMonths
• Spustelėkite Gerai.

Viskas! Dabar jūsų diagrama naudoja dinaminį diapazoną ir bus atnaujinta, kai diagramoje pridėsite/ištrinsite duomenų taškus.

Keletas svarbių dalykų, kuriuos reikia žinoti naudojant diagramose pavadintus diapazonus:

• Diagramos duomenyse neturėtų būti tuščių langelių. Jei yra tuščias, įvardytas diapazonas nenurodys teisingo duomenų rinkinio (nes bendras skaičius reikštų, kad jis nurodo mažesnį ląstelių skaičių).
• Naudodami lapo pavadinimą diagramos šaltinyje, turite vadovautis pavadinimo sutartimi. Pvz., Jei lapo pavadinimas yra vienas žodis, pvz., Formulė, galite naudoti = Formulė! Diagramos vertė. Bet jei yra daugiau nei vienas žodis, pvz., „Formulės diagrama“, turite naudoti = „Formulės diagrama“! ChartValue.