Bagan menurut sel yang dipilih

Misalkan Anda dan saya perlu memvisualisasikan data dari tabel berikut dengan nilai penjualan mobil menurut negara yang berbeda pada tahun 2021 (omong-omong, data sebenarnya diambil dari sini):

Karena jumlah seri data (negara) besar, mencoba menjejalkan semuanya ke dalam satu grafik sekaligus akan mengarah ke "bagan spageti" yang mengerikan atau untuk membangun bagan terpisah untuk setiap seri, yang sangat tidak praktis.

Solusi elegan untuk masalah ini adalah dengan memplot bagan hanya pada data dari baris saat ini, yaitu baris tempat sel aktif berada:

Menerapkan ini sangat mudah – Anda hanya perlu dua rumus dan satu makro kecil dalam 3 baris.

Langkah 1. Nomor baris saat ini

Hal pertama yang kita butuhkan adalah rentang bernama yang menghitung nomor baris pada lembar tempat sel aktif kita sekarang berada. Membuka di tab Rumus – Manajer Nama (Rumus — Manajer nama), klik tombol membuat (Membuat) dan masukkan struktur berikut di sana:

Langkah 2. Tautan ke judul

Untuk menampilkan negara yang dipilih dalam judul dan legenda bagan, kita perlu mendapatkan referensi ke sel dengan nama (negara) dari kolom pertama. Untuk melakukan ini, kami membuat lokal lain (mis Daerah = lembar saat ini, bukan Buku!) rentang bernama dengan rumus berikut:

Di sini, fungsi INDEX memilih dari rentang tertentu (kolom A, tempat negara penandatangan kami terletak) sel dengan nomor baris yang telah kami tentukan sebelumnya.

Langkah 3. Tautkan ke data

Sekarang, dengan cara yang sama, mari dapatkan tautan ke rentang dengan semua data penjualan dari baris saat ini, tempat sel aktif sekarang berada. Buat rentang bernama lain dengan rumus berikut:

Di sini, argumen ketiga, yaitu nol, menyebabkan INDEX tidak mengembalikan nilai tunggal, tetapi seluruh baris sebagai hasilnya.

Langkah 4. Mengganti Tautan di Bagan

Sekarang pilih tajuk tabel dan baris pertama dengan data (rentang) dan buat bagan berdasarkan mereka menggunakan Sisipkan – Bagan (Sisipkan — Bagan). Jika Anda memilih baris dengan data di bagan, maka fungsinya akan ditampilkan di bilah rumus BARIS (SERI) adalah fungsi khusus yang digunakan Excel secara otomatis saat membuat bagan apa pun untuk merujuk ke data dan label asli:

Mari kita ganti dengan hati-hati argumen pertama (tanda tangan) dan ketiga (data) dalam fungsi ini dengan nama rentang kita dari langkah 2 dan 3:

Bagan akan mulai menampilkan data penjualan dari baris saat ini.

Langkah 5. Perhitungan Ulang Makro

Sentuhan terakhir tetap ada. Microsoft Excel menghitung ulang rumus hanya ketika data pada lembar berubah atau ketika tombol ditekan F9, dan kami ingin penghitungan ulang terjadi saat pemilihan berubah, yaitu saat sel aktif dipindahkan melintasi lembar. Untuk melakukan ini, kita perlu menambahkan makro sederhana ke buku kerja kita.

Klik kanan pada tab lembar data dan pilih perintah sumber (Kode sumber). Di jendela yang terbuka, masukkan kode pengendali makro untuk peristiwa perubahan pilihan:

Seperti yang dapat Anda bayangkan dengan mudah, yang dilakukannya hanyalah memicu perhitungan ulang lembar setiap kali posisi sel aktif berubah.

Langkah 6. Sorot Garis Saat Ini

Untuk kejelasan, Anda juga dapat menambahkan aturan pemformatan bersyarat untuk menyorot negara yang saat ini ditampilkan di bagan. Untuk melakukan ini, pilih tabel dan pilih Beranda — Pemformatan Bersyarat — Buat Aturan — Gunakan Rumus untuk Menentukan Sel yang Akan Diformat (Beranda — Pemformatan bersyarat — Aturan baru — Gunakan rumus untuk menentukan sel mana yang akan diformat):

Di sini rumus memeriksa setiap sel dalam tabel bahwa nomor barisnya cocok dengan nomor yang disimpan dalam variabel TekRow, dan jika ada kecocokan, maka isian dengan warna yang dipilih dipicu.

Itu saja – sederhana dan indah, bukan?

Catatan

• Pada tabel besar, semua keindahan ini dapat melambat – pemformatan bersyarat adalah hal yang membutuhkan banyak sumber daya, dan perhitungan ulang untuk setiap pilihan juga bisa menjadi berat.
• Untuk mencegah data menghilang pada bagan saat sel secara tidak sengaja dipilih di atas atau di bawah tabel, Anda dapat menambahkan tanda centang tambahan ke nama TekRow menggunakan fungsi IF bersarang dari formulir:

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

• Menyoroti kolom tertentu dalam bagan
• Cara membuat grafik interaktif di Excel
• Koordinat Seleksi