Chart ayon sa napiling cell

Ipagpalagay na kailangan mo at ako na mag-visualize ng data mula sa sumusunod na talahanayan na may mga halaga ng benta ng sasakyan ng iba't ibang bansa sa 2021 (totoong data na kinuha mula rito, nga pala):

Dahil ang bilang ng mga serye ng data (mga bansa) ay malaki, ang pagsisikap na i-cram ang lahat ng mga ito sa isang graph nang sabay-sabay ay maaaring humantong sa isang kakila-kilabot na "spaghetti chart" o sa pagbuo ng hiwalay na mga chart para sa bawat serye, na napakahirap.

Ang isang eleganteng solusyon sa problemang ito ay maaaring magplano ng tsart sa data lamang mula sa kasalukuyang hilera, ibig sabihin, ang hilera kung saan matatagpuan ang aktibong cell:

Napakadali ng pagpapatupad nito – kailangan mo lang ng dalawang formula at isang maliit na macro sa 3 linya.

Hakbang 1. Kasalukuyang numero ng linya

Ang unang bagay na kailangan namin ay isang pinangalanang hanay na kinakalkula ang row number sa sheet kung saan matatagpuan ang aming aktibong cell. Pagbubukas sa isang tab Mga Formula – Name Manager (Mga Formula — Tagapamahala ng pangalan), i-click ang button Lumikha (Lumikha) at ipasok ang sumusunod na istraktura doon:

Hakbang 2. Mag-link sa pamagat

Upang ipakita ang napiling bansa sa pamagat at alamat ng chart, kailangan nating kumuha ng reference sa cell na may pangalan nito (bansa) mula sa unang column. Upang gawin ito, lumikha kami ng isa pang lokal (hal Pook = kasalukuyang sheet, hindi Book!) isang pinangalanang hanay na may sumusunod na formula:

Dito, pumipili ang function na INDEX mula sa isang ibinigay na hanay (column A, kung saan matatagpuan ang aming mga bansang pumipirma) ng cell na may row number na dati naming natukoy.

Hakbang 3. Mag-link sa data

Ngayon, sa katulad na paraan, kumuha tayo ng link sa isang hanay na may lahat ng data ng benta mula sa kasalukuyang row, kung saan matatagpuan ang aktibong cell. Lumikha ng isa pang pinangalanang hanay na may sumusunod na formula:

Dito, ang ikatlong argumento, na zero, ay nagiging sanhi ng INDEX na hindi magbalik ng isang halaga, ngunit ang buong row bilang isang resulta.

Hakbang 4. Pagpapalit ng Mga Link sa Tsart

Ngayon piliin ang table header at ang unang row na may data (range) at bumuo ng chart batay sa mga ito gamit Ipasok – Mga Tsart (Ipasok — Mga Chart). Kung pipili ka ng row na may data sa chart, ipapakita ang function sa formula bar ROW (SERYE) ay isang espesyal na function na awtomatikong ginagamit ng Excel kapag gumagawa ng anumang tsart upang sumangguni sa orihinal na data at mga label:

Maingat nating palitan ang una (pirma) at pangatlong (data) na argumento sa function na ito ng mga pangalan ng ating mga hanay mula sa hakbang 2 at 3:

Ang tsart ay magsisimulang magpakita ng data ng mga benta mula sa kasalukuyang hilera.

Hakbang 5. Recalculation Macro

Nananatili ang huling ugnayan. Muling kinakalkula ng Microsoft Excel ang mga formula kapag nagbago ang data sa sheet o kapag pinindot ang isang key F9, at gusto naming maganap ang muling pagkalkula kapag nagbago ang pagpili, ibig sabihin, kapag ang aktibong cell ay inilipat sa sheet. Para magawa ito, kailangan naming magdagdag ng simpleng macro sa aming workbook.

Mag-right-click sa tab ng data sheet at piliin ang command pinagmulan (source code). Sa window na bubukas, ilagay ang code ng macro-handler para sa kaganapan ng pagbabago sa pagpili:

Tulad ng madali mong maiisip, ang ginagawa lang nito ay nagti-trigger ng muling pagkalkula ng sheet sa tuwing nagbabago ang posisyon ng aktibong cell.

Hakbang 6. Pag-highlight sa Kasalukuyang Linya

Para sa kalinawan, maaari ka ring magdagdag ng kondisyonal na tuntunin sa pag-format upang i-highlight ang bansang kasalukuyang ipinapakita sa chart. Upang gawin ito, piliin ang talahanayan at piliin Home — Conditional Formatting — Lumikha ng Panuntunan — Gumamit ng Formula upang Matukoy ang Mga Cell na Ipo-format (Home — Conditional formatting — Bagong panuntunan — Gumamit ng formula upang matukoy kung aling mga cell ang ipo-format):

Dito, sinusuri ng formula para sa bawat cell sa talahanayan kung ang numero ng hilera nito ay tumutugma sa numerong nakaimbak sa variable ng TekRow, at kung may tugma, ma-trigger ang punan na may napiling kulay.

Iyon lang – simple at maganda, tama ba?

Mga Tala

• Sa malalaking talahanayan, ang lahat ng kagandahang ito ay maaaring bumagal - ang kondisyong pag-format ay isang bagay na masinsinang mapagkukunan, at ang muling pagkalkula para sa bawat pagpili ay maaari ding maging mabigat.
• Upang maiwasang mawala ang data sa chart kapag ang isang cell ay hindi sinasadyang napili sa itaas o sa ibaba ng talahanayan, maaari kang magdagdag ng karagdagang check sa pangalan ng TekRow gamit ang mga nested IF function ng form:

  =IF(CELL(“row”)<4,IF(CELL("row")>4,CELL(“row”)))
  

• Pag-highlight ng mga tinukoy na column sa isang chart
• Paano lumikha ng isang interactive na tsart sa Excel
• Pagpili ng Coordinate