Anda disini: M Jurnal » Analisis Data Excel » Cara Membuat Rumus Terstruktur Pada Tabel Excel Sederhana

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 A1:A12 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 simak beberapa contoh berikut:

Contoh Soal Menggunakan Excel Online

Silahkan download atau gunakan 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.

Tambahkan kolom excel
Tambahkan Kolom Excel

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:

Cara Membuat Rumus Otomatis Pada Tabel Excel - Rumus Terstruktur
Cara Membuat Rumus Pada Tabel Excel
  1. Pertama, ketik =IF( pada Cell J4.
  2. Kedua, ketik tanda kurung siku “[” (seperti gambar).
  3. Ketiga, 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. Keempat, ketik >=4000000,10%*[ . Kemudian pilih kembali Kolom Total seperti tahap 3.
  5. Terakhir, selesaikan rumus if dengan cara ketik ,0). Berikut hasilnya:
Rumus Terstruktur Excel
Formula Terstruktur Excel

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 N5. Sementara, Cell N5 tidak berada dalam Tabel Bernama, maka silahkan ikuti tahap-tahap berikut:

Cara Membuat Rumus Otomatis Pada Tabel Excel - Rumus Terstruktur
Cara Membuat Rumus Pada Tabel
  1. Pertama, ketik =AVERAGE( pada Cell N5
  2. Kedua, arahkan kursor ke judul kolom Potongan 10% sampai kursor berubah menjadi tanda panah kebawah (seperti gambar). Kemudian klik.
  3. Ketiga, ketik tanda tutup kurung “)” untuk menutup rumus kemudian tekan Enter. Berikut hasilnya;
Rumus Terstruktur Excel
Formula Terstruktur Diluar Tabel

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:

Cara Membuat Rumus Otomatis Pada Tabel Excel - Rumus Terstruktur
Cara Membuat Rumus pada Tabel
  1. Pertama, Ketik tanda sama dengan “=” pada Cell L4
  2. Kedua, klik Cell K4 yaitu nilai Total Pembelian pada Tanggal 01/01/2015. Kemudian ketik tanda kurang (-) seperti gambar.
  3. Ketiga, klik Cell J4 kemudian tekan enter. Berikut hasilnya:
Formula Terstruktur
Rumus Terstruktur Per Item

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. 🙂

Pintasan Panduan Cell Reference Excel

  1. Cell References: Pentingnya Cell References pada Excel dalam menggunakan Rumus / Fungsi. Pelajari semua tentang Referensi Relatif, Absolut Kolom / Baris, dan Absolut ( Lambang Dollar $ ).
  2. 3D-Reference: Cara menggunakan Rumus / Fungsi untuk Beda Sheet serta 5+ Contoh Mendalam (Bonus Studi Kasus membuat Rekap Data Antar Sheet).
  3. Structured References: (Anda Disini).
  4. Union & Intersect: Belajar Operator Rumus / Fungsi Excel. Seperti koma (,) atau titik koma (;), dan spasi ( ).
  5. External References: Cara menggunakan Rumus / Fungsi pada File Excel (WorkBook) berbeda.
  6. Hyperlinks: Cara menghubungkan (tersinkron) File Excel ke File lainnya atau ke Situs Website dll.
  7. Copy Exact Formula: Cara copy rumus / fungsi otomatis menyesuaikan referensi.
  8. Percent Change: Contoh menggunakan Nilai Absolut / Referensi Absolut / Symbol $ lengkap.

Komentar Paling Lambat di Balas Pukul 23:59.