Carta mengikut sel yang dipilih

Katakan anda dan saya perlu memvisualisasikan data daripada jadual berikut dengan nilai jualan kereta​​ oleh negara yang berbeza pada tahun 2021 (data sebenar diambil dari sini, dengan cara):

Memandangkan bilangan siri data (negara) adalah besar, cuba menjejalkan kesemuanya ke dalam satu graf sekaligus sama ada akan membawa kepada "carta spageti" yang dahsyat atau membina carta berasingan untuk setiap siri, yang sangat menyusahkan.

Penyelesaian yang elegan untuk masalah ini adalah dengan memplot carta hanya pada data dari baris semasa, iaitu baris di mana sel aktif terletak:

Melaksanakan ini adalah sangat mudah - anda hanya memerlukan dua formula dan satu makro kecil dalam 3 baris.

Langkah 1. Nombor baris semasa

Perkara pertama yang kami perlukan ialah julat bernama yang mengira nombor baris pada helaian di mana sel aktif kami berada sekarang. Membuka pada tab Formula – Pengurus Nama (Formula - Pengurus nama), klik pada butang Buat (Buat) dan masukkan struktur berikut di sana:

Langkah 2. Pautan ke tajuk

Untuk memaparkan negara yang dipilih dalam tajuk dan legenda carta, kita perlu mendapatkan rujukan kepada sel dengan nama (negara) dari lajur pertama. Untuk melakukan ini, kami membuat satu lagi tempatan (cth Kawasan = helaian semasa, bukan Buku!) julat bernama dengan formula berikut:

Di sini, fungsi INDEX memilih daripada julat tertentu (lajur A, tempat negara menandatangani kami terletak) sel dengan nombor baris yang kami tentukan sebelum ini.

Langkah 3. Pautan ke data

Sekarang, dengan cara yang sama, mari dapatkan pautan ke julat dengan semua data jualan daripada baris semasa, di mana sel aktif kini berada. Buat julat bernama lain dengan formula berikut:

Di sini, hujah ketiga, iaitu sifar, menyebabkan INDEX tidak mengembalikan nilai tunggal, tetapi keseluruhan baris sebagai hasilnya.

Langkah 4. Menggantikan Pautan dalam Carta

Sekarang pilih pengepala jadual dan baris pertama dengan data (julat) dan bina carta berdasarkan penggunaannya Sisipkan – Carta (Sisipkan — Carta). Jika anda memilih baris dengan data dalam carta, maka fungsi tersebut akan dipaparkan dalam bar formula ROW (SIRI) ialah fungsi khas yang Excel gunakan secara automatik apabila membuat sebarang carta untuk merujuk kepada data dan label asal:

Mari kita ganti dengan teliti argumen pertama (tandatangan) dan ketiga (data) dalam fungsi ini dengan nama julat kami dari langkah 2 dan 3:

Carta akan mula memaparkan data jualan daripada baris semasa.

Langkah 5. Makro Pengiraan Semula

Sentuhan terakhir kekal. Microsoft Excel mengira semula formula hanya apabila data pada helaian berubah atau apabila kekunci ditekan F9, dan kami mahu pengiraan semula berlaku apabila pemilihan berubah, iaitu apabila sel aktif dialihkan merentasi helaian. Untuk melakukan ini, kami perlu menambah makro ringkas pada buku kerja kami.

Klik kanan pada tab helaian data dan pilih arahan Source (Kod sumber). Dalam tetingkap yang terbuka, masukkan kod pengendali makro untuk acara perubahan pemilihan:

Seperti yang anda boleh bayangkan dengan mudah, semua yang dilakukannya ialah mencetuskan pengiraan semula helaian apabila kedudukan sel aktif berubah.

Langkah 6. Menyerlahkan Garis Semasa

Untuk kejelasan, anda juga boleh menambah peraturan pemformatan bersyarat untuk menyerlahkan negara yang sedang dipaparkan pada carta. Untuk melakukan ini, pilih jadual dan pilih Laman Utama — Pemformatan Bersyarat — Buat Peraturan — Gunakan Formula untuk Menentukan Sel untuk Diformat (Laman Utama — Pemformatan bersyarat — Peraturan baharu — Gunakan formula untuk menentukan sel yang hendak diformatkan):

Di sini formula menyemak untuk setiap sel dalam jadual bahawa nombor barisnya sepadan dengan nombor yang disimpan dalam pembolehubah TekRow, dan jika terdapat padanan, maka isian dengan warna yang dipilih akan dicetuskan.

Itu sahaja – ringkas dan cantik, bukan?

Nota

• Pada jadual besar, semua keindahan ini boleh menjadi perlahan - pemformatan bersyarat adalah perkara yang intensif sumber, dan pengiraan semula untuk setiap pilihan juga boleh menjadi berat.
• Untuk mengelakkan data daripada hilang pada carta apabila sel dipilih secara tidak sengaja di atas atau di bawah jadual, anda boleh menambah semakan tambahan pada nama TekRow menggunakan fungsi IF bersarang dalam borang:

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

• Menyerlahkan lajur yang ditentukan dalam carta
• Cara membuat carta interaktif dalam Excel
• Pemilihan Koordinat