CoLegal Indonesia : Dashboard SuperDinamis di Excel - Dari Dropdown Otomatis hingga Fungsi Kustom Tanpa VBA - Trik Bisnis Indonesia

Di era di mana data menjadi “mata uang” bisnis, ketangkasan dalam mengolah informasi menentukan kecepatan dan ketepatan keputusan. Bayangkan Anda memiliki dashboard penjualan yang bisa langsung menyesuaikan diri setiap kali produk baru diluncurkan atau transaksi baru terekam—tanpa perlu repot mengubah rentang dropdown, memperbaiki rumus lookup, atau menulis ulang logika klasifikasi.

Dalam panduan ini, kita akan membangun Dashboard Penjualan Dinamis di Excel yang bukan hanya responsif terhadap perubahan data, tetapi juga ringkas dan mudah dipelihara. Dengan empat “trik ninja” modern—Dropdown Dinamis (OFFSET + COUNTA), Lookup Multi-Kriteria (FILTER/XLOOKUP), LET untuk merapikan dan memacu kinerja rumus, serta LAMBDA untuk menciptakan fungsi kustom tanpa VBA—Anda akan melihat bagaimana sebuah file Excel dapat menjadi pusat analisis yang lincah dan skalabel.

Siapkan data Anda, dan mari kita mulai mengubah Excel biasa menjadi dashboard serbaguna yang siap menghadapi pertumbuhan data apa pun.

1. Mempersiapkan Data Sumber

Langkah pertama adalah menyiapkan worksheet yang rapi:

Sheet “DaftarProduk”
- Kolom A berisi daftar produk: mulai dari sel A2 ke bawah (A1 untuk header “Produk”).
- Pastikan tidak ada baris kosong di sela entri.
Sheet “DataPenjualan”
- Kolom A: Tanggal (format tanggal Excel).
- Kolom B: Produk (harus sesuai persis nama di “DaftarProduk”).
- Kolom C: Jumlah (unit terjual).
- Kolom D: Harga Satuan (mata uang).
- Data mulai dari baris 2; baris 1 untuk header.

Contoh sampel “DataPenjualan”:

Tanggal	Produk	Jumlah	Harga Satuan
2025-04-01	Kopi Robusta	120	10.000
2025-04-01	Teh Hijau	80	12.000
2025-04-02	Kopi Robusta	150	10.000
…	…	…	…

Pastikan nama produk di kolom B sama persis dengan yang di “DaftarProduk” agar lookup dan dropdown bekerja sempurna.

2. Teknik 1: Dropdown Dinamis dengan OFFSET & COUNTA

Dropdown dinamis memungkinkan daftar pilihan selalu ter-update sesuai data di “DaftarProduk”. Jika Anda menambah produk baru di daftar, dropdown otomatis menampilkan opsi baru.

2.1 Cara Kerja OFFSET + COUNTA

COUNTA(range) menghitung jumlah sel tidak kosong dalam suatu rentang.
OFFSET(reference, rows, cols, height, width) menghasilkan referensi range yang bergeser dari reference, dengan tinggi height dan lebar width.

Dengan mengombinasikan keduanya:

=OFFSET(DaftarProduk!$A$2, 0, 0, COUNTA(DaftarProduk!$A:$A)-1, 1)

Reference: sel A2 di sheet “DaftarProduk”.
Height: COUNTA(A:A)-1 (dikurangi header pada A1).
Width: 1 kolom.

2.2 Membuat Named Range Dinamis

Buka Formulas → Name Manager → New…
Isi:
- Name: DaftarProduk
- Refers to: =OFFSET(DaftarProduk!$A$2, 0, 0, COUNTA(DaftarProduk!$A:$A)-1, 1)
Klik OK.

2.3 Menggunakan di Data Validation

Pergi ke sheet Dashboard (misal “Ringkasan”), pilih sel tempat dropdown—misalnya B2.
Data → Data Validation → Allow: List
Source: ketik =DaftarProduk
Klik OK.

Dropdown di B2 kini selalu menunjukkan seluruh produk yang ada, tanpa perlu edit manual ketika menambah produk.

3. Teknik 2: Lookup Multi-Kriteria dengan FILTER (dan Alternatif XLOOKUP)

Setelah pengguna memilih produk di dropdown B2, kita ingin menampilkan semua transaksi penjualan untuk produk tersebut.

3.1 Menggunakan FILTER

FILTER (Excel 365) adalah cara paling elegan untuk mengambil seluruh baris yang memenuhi kondisi:

=FILTER(
  DataPenjualan!A2:D100, 
  DataPenjualan!B2:B100 = $B$2, 
  "Tidak Ada Data"
)

Array: A2:D100 (tanggal, produk, jumlah, harga).
Include: B2:B100 = $B$2 (hanya baris dengan produk sama dropdown).
If empty: teks “Tidak Ada Data”.

Formula ini akan “spill” otomatis ke sel-sel di bawah/kanan, menampilkan tabel detail penjualan.

3.2 Alternatif: XLOOKUP untuk Satu Hasil

Jika Anda hanya butuh satu baris (misal transaksi pertama), bisa pakai XLOOKUP:

=XLOOKUP(
  $B$2, 
  DataPenjualan!$B$2:$B$100, 
  DataPenjualan!C2:D100, 
  {"-","-"}
)

Namun untuk dashboard dinamis, FILTER jauh lebih fleksibel karena menampilkan seluruh dataset.

4. Teknik 3: Merapikan dan Mempercepat Rumus dengan LET

Bayangkan Anda ingin menghitung Total, Rata-rata, dan Standar Deviasi dari kolom Jumlah pada produk terpilih. Tanpa LET, Anda harus menulis FILTER(...) berulang kali di tiap cell. Dengan LET, cukup panggil sekali.

4.1 Sintaks LET

LET(
  nama1, ekspresi1,
  nama2, ekspresi2,
  …,
  hasil
)

Anda boleh mendefinisikan beberapa nama sesuai kebutuhan, lalu hasil adalah nilai akhir yang dikembalikan.

4.2 Contoh Implementasi

Di sel F2 (Total Penjualan):

=LET(
  data, FILTER(DataPenjualan!C2:C100, DataPenjualan!B2:B100 = $B$2),
  IFERROR(SUM(data), 0)
)

data: rentang nilai “Jumlah” yang sudah difilter.
SUM(data): total unit terjual.
IFERROR(...,0): memastikan tampil 0 jika tidak ada data.

Di sel F3 (Rata-rata):

=LET(
  data, FILTER(DataPenjualan!C2:C100, DataPenjualan!B2:B100 = $B$2),
  IFERROR(AVERAGE(data), 0)
)

Di sel F4 (Standar Deviasi):

=LET(
  data, FILTER(DataPenjualan!C2:C100, DataPenjualan!B2:B100 = $B$2),
  IFERROR(STDEV.P(data), 0)
)

Dengan LET, FILTER hanya dipanggil sekali per rumus, sehingga workbook responsif meski data ratusan atau ribuan baris.

5. Teknik 4: Fungsi Kustom dengan LAMBDA

Misalkan Anda ingin mengklasifikasikan rata-rata penjualan:

< 50 → “Buruk”
50–99 → “Cukup”
>=100 → “Bagus”

Alih-alih menulis IF(AVERAGE(data)<50,…,IF(...)) setiap kali, Anda bisa bikin fungsi KlasifikasiPenjualan.

5.1 Menambah LAMBDA di Name Manager

Formulas → Name Manager → New…
Name: KlasifikasiPenjualan
Refers to: =LAMBDA(x, IF(x < 50, "Buruk", IF(x < 100, "Cukup", "Bagus") ) )
OK.

5.2 Cara Memanggil

Di sel F5:

=KlasifikasiPenjualan(F3)

Fungsi ini mengambil input nilai rata-rata di F3, lalu mengembalikan klasifikasi teks sesuai kriteria.

6. Merakit Dashboard Penjualan Dinamis

Berikut ringkasan layout sheet “Ringkasan”:

B	C		F	G
Produk	B2: Dropdown Dinamis		Metrik Ringkasan
			Total Penjualan	=LET(…) di F2
			Rata-rata Penjualan	=LET(…) di F3
			Deviasi Standar	=LET(…) di F4
			Klasifikasi	=KlasifikasiPenjualan(F3) di F5
Detail Data		Tabel Transaksi
A5:D5: Header tabel		A6: =FILTER(DataPenjualan!A2:D100, B2=…)	spill otomatis

Dropdown Dinamis di B2: =DaftarProduk
Tabel Detail di A6: =FILTER(…) untuk melihat semua transaksi produk terpilih.
Metrik Ringkasan di F2–F4: menggunakan LET + FILTER.
Klasifikasi di F5: panggil KlasifikasiPenjualan(F3).

Anda juga bisa menambahkan Chart:

Pilih rentang jumlah & tanggal hasil FILTER, lalu Insert → Line/Column Chart.
Atur judul dinamis: di Chart Title, ketik formula ="Tren " & B2.

7. Tips Lanjutan & Best Practices

Table vs OFFSET: bila Anda nyaman dengan Excel Table (Ctrl + T), sumber dropdown bisa langsung =Table1[Produk] tanpa OFFSET, tapi Table tidak se-fleksibel MATCH untuk kontrol baris kosong.
Cascading Dropdown: gabungkan teknik dropdown dinamis + INDIRECT untuk dropdown bertingkat (kategori → sub-kategori).
Error Handling: bungkus FILTER dengan IFERROR atau pakai argumen ketiga pada FILTER agar tampilan rapi saat data kosong.
Performance: pada workbook besar, batasi FILTER/OFFSET ke baris realistik (misal A2:A1000, bukan seluruh kolom), kecuali benar-benar dibutuhkan.
Documentasi: tambahkan komentar sel (Right-click → Insert Comment) untuk menjelaskan rumus LET atau LAMBDA kepada rekan kerja.

8. Kesimpulan

Dengan menguasai:

Dropdown Dinamis (OFFSET + COUNTA)
Lookup Multi-Kriteria (FILTER / XLOOKUP)
LET (perhitungan sekali panggil, keterbacaan tinggi)
LAMBDA (fungsi kustom tanpa VBA)

…Anda mampu membangun dashboard input dan laporan yang fleksibel, mudah dirawat, dan skalabel. Setiap kali ada produk baru atau transaksi bertambah, cukup Refresh (F9) atau buka kembali workbook—seluruh elemen akan mengikutinya secara real time.

Selamat mencoba, eksplorasi, dan terus tingkatkan efisiensi Anda dengan trik-trik ninja ini!

Be the first to comment

Leave a Reply Batalkan balasan