Cara Membuat Rumus Terstruktur Pada Tabel Excel Sederhana

Dengan menggunakan Tabel Bernama, Anda bisa membuat Rumus / Fungsi / Formula Microsoft Excel lebih terstruktur (lebih mudah dipahami).

Sebagai contoh, silahkan Anda bandingkan kedua Fungsi Average berikut =AVERAGE(A1:A12) dan =AVERAGE(Penjualan[Harga]). Rumus pertama menggunakan Range A₁:A₁₂ sebagai referensi, sementara yang kedua menggunakan Nama Tabel (Penjualan) dan Nama Kolom (Harga) sebagai referensi rumus.

Tentu saja Fungsi AVERAGE yang kedua lebih mudah Anda pahami. Terlebih lagi jika Anda menggunakan rumus bertumpuk seperti Rumus IF berganda atau penggabungan dua atau lebih fungsi Excel. Fitur ini pasti sangat membantu.

Lalu gimana cara membuat rumus terstruktur tersebut ?

Silahkan download file Excel Online berikut untuk bahan latihan kita kali ini. Data yang Saya gunakan memiliki 219 baris dan 10 kolom seperti berikut:

Rumus / Fungsi Terstruktur Dalam Satu Tabel

Sebagai contoh silahkan buka Sheet Tabel Bernama pada File Excel Online. Saya akan menambahkan kolom baru (kolom Potongan 10%) antara kolom Harga dan Total. Maka tampilan kolom Potongan 10% seperti gambar berikut:

Penting! Jika Anda belum tahu cara menambahkan kolom, silahkan kunjungi Panduan Range, Cell, Column dan Row M Jurnal.

Dalam contoh ini, Syarat mendapatkan Potongan 10% adalah Minimal Pembelian Rp 4.000.000. Saya menggunakan Fungsi IF untuk menghitung Potongan 10%. Silahkan ikuti tahap berikut untuk membuat rumus terstruktur pada Tabel bernama:

1. Ketik rumus =IF( pada Cell J₄.
2. Ketik tanda kurung siku [ (seperti gambar).
3. Silahkan double-click nama kolom yang Anda inginkan. Berhubung dalam contoh ini kriteria rumus IF adalah Total Pembelian, maka Saya pilih kolom Total. Kemudian ketik tutup kurung siku ]
4. Ketik >=4000000,10%*[ . Kemudian pilih kembali Kolom Total seperti tahap 3.
5. Selesaikan rumus if dengan cara ketik ,0). Berikut hasilnya:

Pada Tabel Bernama, sekali Anda mengisi Rumus / Fungsi, secara otomatis Excel akan mengisi baris selanjutnya dengan rumus / fungsi yang sama. Ini juga salah satu kelebihan format tabel bernama.

Sebetulnya ada cara lain yang lebih cepat daripada cara ini. Saya akan tunjukkan dengan contoh yang berbeda sebagai berikut:

Rumus / Fungsi Terstruktur Diluar Tabel

Sebagai contoh, Saya akan menghitung Rata-Rata Potongan menggunakan Fungsi AVERAGE pada Cell N₅.

Sementara itu, Cell N₅ tidak berada dalam Tabel Bernama, maka silahkan ikuti tahap-tahap berikut:

1. Ketik rumus =AVERAGE( pada Cell N₅
2. Arahkan kursor ke judul kolom Potongan 10% sampai kursor berubah menjadi tanda panah kebawah (seperti gambar). Kemudian klik.
3. Ketik tanda tutup kurung ) untuk menutup rumus kemudian tekan Enter. Berikut hasilnya;

Lihat struktur rumus IF menjadi =AVERAGE(Pembelian[Potongan 10%]) yang bisa diartikan menjadi: Hitung Rata-Rata Kolom Potongan pada Tabel Pembelian.

Rumus / Fungsi Terstruktur Per Item

Per item maksud nya adalah rumus / fungsi menggunakan referensi per cell. Agak bingung Saya menjelaskannya. Mendingan langsung ke contoh aja.

Sebagai contoh, Saya akan menambahkan kolom baru (Total Pembelian Setelah Potongan / TPSP) disampaing kolom Total. Kolom ini akan Saya isi dengan rumus Total – Potongan. Silahkan ikuti tahap-tahap berikut:

1. Ketik tanda sama dengan “=” pada Cell L₄
2. Klik Cell K₄ yaitu nilai Total Pembelian pada Tanggal 01/01/2015. Kemudian ketik tanda kurang (-) seperti gambar.
3. Klik Cell J₄ kemudian tekan Enter. Berikut hasilnya:

Anda bisa lihat, jika Anda menggunakan referensi per item, maka Excel secara otomatis menambahkan nama kolom tabel menggunakan tanda @ seperti =[@TOTAL].

Sementara untuk item pada Kolom Potongan 10% seperti ini: [@[Potongan 10%]]. Berbeda dengan kolom Total.

Kenapa ? Ini sudah sistem default dari Excel. Anda tinggal klik-klik saja.

Saya yakin banyak sekali contoh lain yang belum Saya sebutkan diatas.

Jika Anda kesulitan dengan kasus yang berbeda misalnya Tabel bernama beda sheets atau beda file Excel dll, silahkan coret-coret kolom komentar. Saya akan senang hati membalasnya. 🙂