Diagramma pēc atlasītās šūnas

Pieņemsim, ka jums un man ir jāvizualizē dati no šīs tabulas ar automašīnu pārdošanas vērtībām dažādās valstīs 2021. gadā (starp citu, reālie dati ņemti no šejienes):

Diagramma pēc atlasītās šūnas

Tā kā datu sēriju (valstu) skaits ir liels, mēģinot tās visas vienlaikus sabāzt vienā grafikā vai nu radīsies briesmīga “spageti diagramma”, vai arī katrai sērijai tiks izveidota atsevišķa diagramma, kas ir ļoti apgrūtinoši.

Elegants risinājums šai problēmai var būt diagrammas zīmēšana tikai uz datiem no pašreizējās rindas, ti, rindā, kurā atrodas aktīvā šūna:

To ieviest ir ļoti vienkārši — jums ir nepieciešamas tikai divas formulas un viens mazs makro 3 rindiņās.

1. darbība. Pašreizējā līnijas numurs

Pirmā lieta, kas mums ir nepieciešama, ir nosaukts diapazons, kas aprēķina rindas numuru lapā, kurā tagad atrodas mūsu aktīvā šūna. Atvēršana cilnē Formulas – vārdu pārvaldnieks (Formulas — nosaukumu pārvaldnieks), noklikšķiniet uz pogas izveidot (Izveidot) un ievadiet tur šādu struktūru:

Diagramma pēc atlasītās šūnas

Šeit:
  • Vārds - jebkurš piemērots nosaukums mūsu mainīgajam (mūsu gadījumā tas ir TekString)
  • Platība – turpmāk ir jāizvēlas pašreizējā lapa, lai izveidotie nosaukumi būtu lokāli
  • Diapazons - šeit mēs izmantojam funkciju ŠŪNA (ŠŪNA), kas var izdot virkni dažādu parametru konkrētai šūnai, tostarp mums nepieciešamo rindas numuru – par to ir atbildīgs arguments “rinda”.

2. darbība. Saite uz virsrakstu

Lai diagrammas nosaukumā un leģendā parādītu atlasīto valsti, no pirmās kolonnas jāiegūst atsauce uz šūnu ar tās (valsts) nosaukumu. Lai to izdarītu, mēs izveidojam citu lokālo (t Platība = pašreizējā lapa, nevis grāmata!) nosaukts diapazons ar šādu formulu:

Diagramma pēc atlasītās šūnas

Šeit funkcija INDEX no noteiktā diapazona (A sleja, kur atrodas mūsu parakstītās valstis) atlasa šūnu ar rindas numuru, kuru mēs iepriekš noteicām.

3. darbība. Saite uz datiem

Tagad līdzīgā veidā iegūsim saiti uz diapazonu ar visiem pārdošanas datiem no pašreizējās rindas, kurā tagad atrodas aktīvā šūna. Izveidojiet citu nosaukumu diapazonu ar šādu formulu:

Diagramma pēc atlasītās šūnas

Šeit trešais arguments, kas ir nulle, liek INDEX atgriezt nevis vienu vērtību, bet gan visu rindu.

4. darbība. Saišu aizstāšana diagrammā

Tagad atlasiet tabulas galveni un pirmo rindu ar datiem (diapazons) un izveidojiet diagrammu, pamatojoties uz tiem, izmantojot Ievietot - diagrammas (Ievietot — diagrammas). Ja diagrammā atlasāt rindu ar datiem, funkcija tiks parādīta formulas joslā ROW (SĒRIJA) ir īpaša funkcija, ko programma Excel automātiski izmanto, veidojot diagrammu, lai atsauktos uz sākotnējiem datiem un etiķetēm:

Diagramma pēc atlasītās šūnas

Rūpīgi aizstāsim pirmo (paraksts) un trešo (dati) argumentu šajā funkcijā ar mūsu diapazonu nosaukumiem no 2. un 3. darbības:

Diagramma pēc atlasītās šūnas

Diagrammā tiks parādīti pārdošanas dati no pašreizējās rindas.

5. darbība. Pārrēķināšanas makro

Pēdējais pieskāriens paliek. Microsoft Excel pārrēķina formulas tikai tad, kad mainās dati lapā vai tiek nospiests taustiņš F9, un mēs vēlamies, lai pārrēķins notiktu, kad atlase mainās, ti, kad aktīvā šūna tiek pārvietota pa lapu. Lai to izdarītu, mūsu darbgrāmatai jāpievieno vienkāršs makro.

Ar peles labo pogu noklikšķiniet uz datu lapas cilnes un atlasiet komandu avots (Avota kods). Atvērtajā logā ievadiet atlases maiņas notikuma makro apstrādātāja kodu:

Diagramma pēc atlasītās šūnas

Kā jūs viegli varat iedomāties, viss, ko tas dara, ir aktivizē lapas pārrēķinu ikreiz, kad mainās aktīvās šūnas pozīcija.

6. darbība. Pašreizējās līnijas iezīmēšana

Skaidrības labad varat pievienot arī nosacījumformatēšanas kārtulu, lai iezīmētu valsti, kas pašlaik tiek rādīta diagrammā. Lai to izdarītu, atlasiet tabulu un atlasiet Sākums — Nosacījuma formatēšana — Izveidot kārtulu — Izmantojiet formulu, lai noteiktu formatējamās šūnas (Sākums — nosacījumformatēšana — jauna kārtula — izmantojiet formulu, lai noteiktu, kuras šūnas formatēt):

Diagramma pēc atlasītās šūnas

Šeit formula pārbauda katrai tabulas šūnai, vai tās rindas numurs atbilst TekRow mainīgajā saglabātajam skaitlim, un, ja ir atbilstība, tiek aktivizēta aizpildīšana ar atlasīto krāsu.

Tā tas ir – vienkārši un skaisti, vai ne?

Piezīmes

  • Uz lieliem galdiem viss šis skaistums var nobremzēt – nosacītā formatēšana ir resursietilpīga lieta, un arī pārrēķins katrai atlasei var būt smags.
  • Lai novērstu datu pazušanu diagrammā, kad virs tabulas vai zem tabulas nejauši tiek atlasīta šūna, varat pievienot papildu atzīmi TekRow nosaukumam, izmantojot formas ligzdotās IF funkcijas:

    =IF(CELL("rinda")<4,IF(CELL("rinda")>4,CELL("rinda")))

  • Diagrammā norādīto kolonnu izcelšana
  • Kā izveidot interaktīvu diagrammu programmā Excel
  • Koordinātu izvēle

Atstāj atbildi