A. Rumus Excel Referensi

Rumus Excel Referensi 

1. LOOKUP (Fungsi LOOKUP)

• 
  
  Terapkan Ke: Excel untuk Office 365 Excel untuk Office 365 untuk Mac Selengkapnya...
  
  Gunakan fungsi LOOKUP untuk mencari satu baris atau satu kolom. Dalam contoh di atas, kami sedang mencari harga di kolom D.
  Gunakan VLOOKUP untuk mencari satu baris atau kolom, atau untuk mencari beberapa baris dan kolom (seperti tabel). Ini merupakan versi LOOKUP yang disempurnakan. Simak video ini tentang cara menggunakan VLOOKUP. Gunakan LOOKUP, salah satu fungsi pencarian dan referensi, saat Anda harus mencari di dalam satu baris atau satu kolom dan menemukan nilai dari posisi yang sama dalam baris atau kolom kedua.
  Misalnya, katakanlah Anda mengetahui nomor komponen untuk sebuah komponen mobil namun Anda tidak mengetahui harganya. Anda bisa menggunakan fungsi LOOKUP untuk mengembalikan harga di sel H2 ketika Anda memasukkan nomor komponen mobil di H1.

  Ada dua cara menggunakan LOOKUP: Formulir vektor dan formulir array

  • Formulir vektor: Gunakan formulir LOOKUP ini untuk sebuah nilai di satu baris atau satu kolom. Gunakan formulir vektor saat Anda ingin menentukan rentang yang memuat nilai-nilai yang ingin Anda cocokkan. Misalnya, jika Anda ingin mencari nilai dalam kolom A hingga ke baris 6.
  • Formulir array: Kami sangat menyarankan menggunakan VLOOKUP atau HLOOKUP sebagai ganti formulir array. Simak video ini tentang cara menggunakan VLOOKUP. Formulir array disediakan untuk kompatibilitas dengan program lembar bentang lainnya, namun fungsionalitasnya terbatas.
    Array adalah kumpulan nilai di baris dan kolom (seperti tabel) yang ingin Anda cari. Misalnya, jika Anda ingin mencari kolom A dan B, hingga ke baris 6. LOOKUP akan mengembalikan kecocokan terdekat. Untuk menggunakan formulir array, data Anda harus diurutkan.

  Bentuk vektor

  Formulir vektor LOOKUP mencari sebuah nilai dalam rentang satu baris atau satu kolom (yang disebut vektor) dan mengembalikan nilai dari posisi yang sama dalam rentang satu baris atau satu kolom kedua

  Sintaks

  LOOKUP(lookup_value, lookup_vector, [result_vector])
  Sintaks fungsi LOOKUP membentuk vektor yang memiliki argumen ini:

  • lookup_value    Diperlukan. Nilai yang dicari LOOKUP dalam vektor pertama. Lookup_value bisa berupa angka, teks, nilai logika, atau nama atau referensi yang merujuk ke sebuah nilai.
  • lookup_vector    Diperlukan. Sebuah rentang yang hanya berisi satu baris atau satu kolom. Nilai-nilai di dalam lookup_vector dapat berupa teks, angka, atau nilai logika.
    Penting: Nilai-nilai dalam lookup_vector harus ditempatkan dalam urutan naik: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; jika tidak, LOOKUP mungkin tidak mengembalikan nilai yang tepat. Teks huruf besar dan huruf kecil sama saja.
  • result_vector    Opsional. Sebuah rentang yang hanya berisi satu baris atau satu kolom. Argumen result_vector harus berukuran sama dengan lookup_vector. Harus memiliki ukuran yang sama.

  Keterangan

  • Jika fungsi LOOKUP tidak dapat menemukan lookup_value, fungsinya cocok dengan nilai terbesar dalam lookup_vector yang lebih kecil dari atau sama dengan lookup_value.
  • Jika lookup_value lebih kecil daripada nilai yang paling kecil dalam lookup_vector, maka LOOKUP mengembalikan nilai kesalahan #N/A
     
    2. Belajar Vlookup dengan tipe data FALSE Pada Excel

    Mas Edi Excel, Excel Medium, Excel Referensi 11 Komentar
    Pin It
    Setelah kita belajar Microsoft Excel 2010 dasar/pemula, kita lanjutkan ke level yang sedikit lebih rumit atau medium level, Admin mencoba belajar dan kemudian berbagi apa yang didapatkan, disini kita sama-sama belajar.
    Langsung saja pada postingan kali ini akan dibahas fungsi lookup. Fungsi lookup yaitu digunakan untuk mencari data-data atau nilai otomatis yang sesuai dengan pasangannya  pada referensi yang kita buat terlebih dahulu.
    Fungsi lookup ada dua yaitu Vlookup dan Hlookup letak perbedaanya hanya pada range data referensi yang kita buat dengan arah kolom V (vertikal) atau H (Horizontal) saja.
    Masih bingung untuk lebih jelasnya akan kita berikan contohnya:
    Pertama kita akan membahas Vlookup dengan tipe data FALSE (tetap)

    Vlookup dengan tipe data FALSE

    Contoh diatas yaitu data penjualan/pemesanan kaos dengan berbagai ukuran yang memiliki harga yang bebeda-beda semakin besar harganya  lebih mahal. Rumus Vlookup berfungsi supaya ketika data ukuran dimasukan maka harga akan muncul secara otomatis sesuai data referensinya/acuannya.
    Langkah-langkahnya sebagai berikut :
  • 1. Pertama yaitu buat table data referensi harga kaos berdasarkan ukuran
  • 2. Kemudian buat tablel pemesan kaos seperti diatas
  • 3. Rumusnya yaitu :

  Ketikan pada kolom harga cell  D9  =VLOOKUP(C9;$B$4:$C$6;2;FALSE)

  Penjelasannya

  C9 merupakan nilai kunci yang dijadikan acuan untuk membaca tabel referensi yaitu kolom size$B$4:$C$6  merupakan range dari tabel referensi untuk mendapatkan hasil yang sesuai yaitu tabel ukuran kaos dan harganya.

  2  merupakan nomor urut data dalam tabel referensi. yaitu pada kolom dua (harga)

  FALSE tipe data ada dua pilihan yaitu TRUE dan FALSE. Nilai TRUE Anda gunakan jika nilai datanya tidak pasti atau berada pada range tertentu dan nilai FALSE Anda gunakan jika nilai data berharga pasti/tetap.

  • 4.Selanjutnya untuk mempercepat penyelesaian arahkan mouse ponter sampai berubah tanda + , kemudian tinggal tarik/drag ke bawah.

  Berikut contoh Hasilnya :

  Vlookup dengan tipe data FALSE

  3. Belajar Vlookup dengan tipe data TRUE Pada Excel Pada postingan sebelumnya sudah dibahas Vlookup dengan tipe data FALSE (tetap), pada postingan kali ini akan dibahas fungsi Vlookup dengan tipe data TRUE. Tipe data True yaitu digunakan jika nilai datanya tidak tetap atau berada pada range tertentu. Untuk lebih jelasnya dan mudah dipahami berikut akan diberikan contohnya : Vlookup dengan tipe data True (tidak tetap) Contoh ini yaitu menentukan nilai kuliah secara otomatis dari angka menjadi poin A+,B,C,D,E  sesuai dengan rentang nilai yang ditentukan. Langkah-langkahnya sebagai berikut : Pertama yaitu buat table data referensi Nilai angka menjadi point seperti contoh diatas, yang diperlu diperhatikan data harus berurut dari yang terkecil sampai yang terbesar. Kemudian buat tabel data Mahasiswa:  Nama, Nilai dan Point Rumusnya yaitu : Ketikan pada kolom point yaitu cell  D11  =VLOOKUP(C11;$B$4:$C$8;2;TRUE) Penjelasannya: C11 merupakan nilai kunci yang dijadikan acuan untuk membaca tabel referensi yaitu kolom Nilai. $B$4:$C$8  merupakan range dari tabel referensi untuk mengisi hasil yang diharapkan yaitu tabel referensi nilai dan point. 2  merupakan nomor urut data dalam tabel referensi. yaitu pada kolom 2 (point referensi) TRUE tipe yang pilihan yaitu TRUE karena nilai datanya tidak tetap atau berubah pada rentang data tertentu. Selanjutnya untuk mempercepat penyelesaian arahkan mouse ponter sampai berubah tanda + , kemudian tinggal tarik/drag ke bawah. Berikut contoh hasilnya : 4. Fungsi Hlookup dengan tipe data False pada Excel   Pada postingan sebelumnya sudah dibahas Vlook FALSE dan Vlookup TRUE pada postingan kali ini yaitu akan dibahas fungsi Hlookup dengan tipe data FALSE (tetap). Tipe data FALSE yaitu digunakan jika nilai datanya tetap atau pasti artinya harus kriterianya sama dengan referensinya atau acuannya. Pada prinsipnya penggunaan fungsi Vlookup dengan Hlookup masih sama, perbedaanya yaitu terletak pada data referensi/acuannya  disusun secara H (Horizontal) Langsung saja untuk lebih jelasnya akan kita berikan contoh sebagai berikut : Fungsi Hlookup dengan tipe data FALSE (tetap)  Fungsi Hlookup Contoh diatas sama dengan contoh kemaren yaitu data penjualan/pemesanan kaos dengan berbagai ukuran yang memiliki harga yang bebeda-beda semakin besar harganya  dianggap lebih mahal. Perbedaanya terletak pada penulisan ukuran dengan tabel kearah Horizontal  Langkah-langkahnya sebagai berikut : 1. Pertama yaitu buat table data referensi harga kaos berdasarkan ukuran 2. Kemudian buat tablel pemesan kaos seperti diatas 3. Rumusnya yaitu : Ketikan pada kolom harga cell D7  =HLOOKUP(C7; B3: D4;2;FALSE) Sedikit tambahan pada contoh Hlookup kali ini yaitu harus memberikan alamat cell absolute ($) pada referensi data supaya tetap ketika kita drag rumusnya. Menjadi D7  =HLOOKUP(C7; $B$3: $D$4;2;FALSE) Penjelasannya C7  merupakan nilai kunci yang dijadikan acuan untuk membaca tabel referensi yaitu kolom size $B$3: $D$4 merupakan range dari tabel referensi untuk mengisi hasil yang diharapkan yaitu tabel ukuran kaos dan harganya. 2  merupakan nomor urut data dalam tabel referensi. yaitu pada baris dua (harga). FALSE tipe data ada dua pilihan yaitu TRUE dan FALSE. Nilai TRUE Anda gunakan jika nilai datanya tidak pasti atau berada pada range tertentu dan nilai FALSE Anda gunakan jika nilai data berharga pasti/tetap. 4.Selanjutnya untuk mempercepat penyelesaian arahkan mouse ponter sampai berubah tanda + , kemudian tinggal tarik/drag ke bawah. Berikut contoh hasilnya : Fungsi Hlookup 5. Fungsi Hlookup dengan tipe data TRUE pada Excel   Pada postingan sebelumnya sudah dibahas Vlook FALSE, Vlookup TRUE dan Hlookup FALSE pada postingan kali ini akan dibahas fungsi Hlookup dengan tipe data TRUE.  Tipe data True yaitu digunakan jika nilai datanya tidak tetap atau berada pada range tertentu. Pada prinsipnya masih sama dengan fungsi Vlookup perbedaanya yaitu terletak pada data referensi yang disusun secara H (Horizontal) Langsung saja, untuk lebih jelas dan mudah dipahami berikut akan diberikan contohnya : Hlookup dengan tipe data True (tidak tetap). Hlookup dengan tipe data TRUE Contoh diatas yaitu menentukan nilai kuliah secara otomatis dari angka menjadi poin A+,B,C,D,E  sesuai dengan rentang nilai yang ditentukan dengan fungsi Hlookup dengan tipe data True. Langkah-langkahnya sebagai berikut : 1. Pertama yaitu buat table data referensi Nilai angka menjadi point seperti contoh diatas dengan arah  Horizontal, yang diperlu diperhatikan data harus berurut dari yang terkecil sampai yang terbesar. 2. Kemudian buat tabel data Mahasiswa:  Nama, Nilai dan Point.    Rumusnya yaitu : 3. Ketikan pada kolom point yaitu cell  D8   =HLOOKUP(C8;B3:F4;2;TRUE) Sedikit perbedaan pada contoh Hlookup kali ini yaitu harus memberikan alamat cell absolute ($) pada referensi data supaya tetap ketika kita drag rumusnya. Yaitu Menjadi  cell  D8   =HLOOKUP(C8;$B$3:$F$4;2;TRUE) Penjelasannya C8: merupakan nilai kunci yang dijadikan acuan untuk membaca tabel referensi yaitu kolom Nilai $B$3:$F$4 merupakan range dari tabel referensi untuk mengisi hasil yang diharapkan yaitu tabel referensi nilai dan point. 2  merupakan nomor urut data dalam tabel referensi. yaitu pada baris 2 (point referensi)   TRUE tipe yang pilihan yaitu TRUE karena nilai datanya tidak tetap atau berubah pada rentang data tertentu. 4.Selanjutnya untuk mempercepat penyelesaian arahkan mouse ponter sampai berubah tanda + , kemudian tinggal tarik/drag ke bawah. Berikut contoh hasilnya : Hlookup dengan tipe data TRUE 6. Cara menggunakan fungsi indeks untuk menemukan data pada tabel di Excel Berlaku untuk: Microsoft Office Excel 2007Microsoft Office Excel 2003Excel 2010 PENTING: Artikel ini diterjemahkan menggunakan perangkat lunak mesin penerjemah Microsoft dan bukan oleh seorang penerjemah. Microsoft menawarkan artikel yang diterjemahkan oleh seorang penerjemah maupun artikel yang diterjemahkan menggunakan mesin sehingga Anda akan memiliki akses ke seluruh artikel baru yang diterbitkan di Pangkalan Pengetahuan (Knowledge Base) dalam bahasa yang Anda gunakan. Namun, artikel yang diterjemahkan menggunakan mesin tidak selalu sempurna. Artikel tersebut mungkin memiliki kesalahan kosa kata, sintaksis, atau tata bahasa, hampir sama seperti orang asing yang berbicara dalam bahasa Anda. Microsoft tidak bertanggung jawab terhadap akurasi, kesalahan atau kerusakan yang disebabkan karena kesalahan penerjemahan konten atau penggunaannya oleh para pelanggan. Microsoft juga sering memperbarui perangkat lunak mesin penerjemah. Lihat artikel asli berbahasa Inggris: {artikel} Ringkasan Artikel ini menjelaskan cara menggunakan fungsi indeks untuk menemukan data pada tabel di titik potong dari khusus baris dan kolom dalam Microsoft Excel. Ada dua bentuk fungsi indeks Array dan referensi. Perbedaan utama antara dua bentuk adalah sebagai berikut: Bentuk Array dapat mengembalikan nilai lebih dari satu per satu. Bentuk referensi mengembalikan referensi sel di titik potong dari tertentu baris dan kolom. Bentuk Array masuk menggunakan CTRL + SHIFT + ENTER, bukan hanya ENTER, dengan referensi. Informasi lebih lanjut Masukkan data berikut ke lembar kerja Excel yang kosong. Anda akan menggunakan data ini untuk formula contoh dalam artikel ini. A B C 1 Nama Bagian Usia 2 Henry 501 28 3 Stan 201 19 4 Mary 101 22 5 Larry 301 29 6 7 Harry 401 21 8 Joe 101 23 9 Lynn 301 30 Bentuk referensi indeks Masukkan rumus berikut dalam sel E2 (atau setiap sel kosong yang tersedia): =INDEX((A2:C5,A7:C9),2,3,2) (A2:C5, A7:C9) adalah kisaran mana nilai yang ingin ditemukan. 2 adalah nomor baris di mana nilai adalah kisaran. 3 adalah jumlah kolom dalam kisaran yang mana nilai ini. Karena ada tiga kolom (diawali dengan kolom A), kolom ketiga adalah kolom C. 2 adalah area, A2:C5 atau A7:C9, di mana nilai. Karena ada dua area yang ditetapkan untuk kisaran, kisaran kedua adalah A7:C9. Tekan ENTER. Dalam contoh rumus, fungsi indeks mengembalikan nilai pada titik potong dari baris kedua (2) dan kolom ketiga (C) dari berbagai A7:C9. Nilai dalam sel C8 adalah 23. Oleh karena itu, formula =INDEX((A2:C5,A7:C9),2,3,2) akan mengembalikan nilai 23. Bentuk array indeks Masukkan rumus berikut dalam sel E3 (atau setiap sel kosong yang tersedia): =INDEX(A2:C5,2,3) A2:C5 adalah rentang mana nilai yang ingin ditemukan. 2 adalah nomor baris di mana nilai adalah kisaran. 3 adalah jumlah kolom dalam kisaran yang mana nilai ini. Karena ada tiga kolom (diawali dengan kolom A), kolom ketiga adalah kolom C. Tekan CTRL + SHIFT + ENTER untuk memasukkan rumus sebagai array formula. Dalam contoh rumus, fungsi indeks mengembalikan nilai pada titik potong dari baris kedua (2) dan kolom ketiga (C). Nilai dalam sel C2 adalah 19. Oleh karena itu, formula =INDEX(A2:C5,2,3) akan mengembalikan nilai 19. 7. Belajar Menggunakan Fungsi Match Pada Excel Fungsi Match digunakan untuk menampilkan suatu posisi cell pada rentang data yang telah ditetapkan sebelumnya. Untuk lebih jelasnya berikut contohnya : Fungsi Match Pada Excel Pada contoh diatas kita akan mencari posisi pada huruf tertentu  Pada cell B10 ketikan rumus =MATCH(A10;A1:A7;0) hasilnya posisi dibaris 2 Pada cell B11 ketikan rumus =MATCH(A11;A1:A7;0)  hasilnya posisi dibaris 7 Ket: A10 >> lookup_value = nilai yang akan dicari posisinya C3:C9 >>lookup_array = range data  0  >> match_type = tipe match ada 3 yaitu  1 jika range data dari kecil ke besar 0 tidak berpengaruh urutan -1 jika range data dari besar ke kecil Berikut contoh hasilnya  Hasil Fungsi Match Pada Excel Selamat mencoba, semoga bermanfaat pada artikel selanjutnya akan dibahas contoh penerapan dari pengabungan fungsi INDEX dan MATCH. 8. Rumus Excel COLUMN dan COLUMNS COLUMN dan COLUMNS digunakan untuk menentukan kolom. Rumus excel COLUMN yaitu digunakan untuk mengetahui pada kolom nomer berapa data referensi yang kita pilih. Sintak penulisan rumusnya : =COLUMN(reference) Contohnya =COLUMN(D5) maka hasilnya 4, karena pada kolom D atau kolom ke 4 Sedangkan Rumus Excel COLUMNS yaitu digunakan untuk mengetahui atau menghitung berapa banyaknya kolom pada array rentang yang kita tentukan. Sintak penulisa rumusnya : = COLUMNS (array) contohnya =COLUMNS(A5:A9) Hasilnya yaitu 1, karena dari array A5 sampai A9 masih dalam kolom sama, jumlanya ada 1 kolom. Berikut ini contoh dalam tabel COLUMN dan COLUMNS Pada contoh diatas kita  tulis rumusnya untuk : Cell B11 ketikan rumus =COLUMN(C7), maka didapat hasil 3 karena terletak pada kolom C atau kolom ke 3 Cell B12 ketikan rumus =COLUMNS(A5:D9) maka didapat hasil 4 karena jumlah kolom pada range antara A5 sampai D9 adalalah 4 kolom Jadi kesimpulannya : Rumus Excel COLUMN digunakan untuk menghasilkan nomer kolom referensi, sedangkan rumus Excel COLUMNS digunakan untuk menghasilkan jumlah kolom dalam referensi. 9. Rumus Excel Row dan Rows rumus ROW dan ROWS. Rumus excel ROW yaitu digunakan untuk mengetahui pada baris nomer keberapa data referensi yang kita pilih. Sintak penulisan rumusnya : =ROW(Reference) Contohnya =ROW(C5) maka hasilnya 5, yaitu pada baris ke 5 Sedangkan Rumus Excel ROWS yaitu digunakan untuk mengetahui atau menghitung berapa banyaknya baris pada array rentang yang kita tentukan. Sintak penulisan rumusnya : =ROW(array) contohnya =ROWS(B1:B7) Hasilnya yaitu 7 karena dari baris B1 sampai B7 jumlanya ada 7 baris. Untuk lebih jelas, kita simak lagi dalam tabel berikut ini Rumus Excel ROW DAN ROWSRo Pada contoh diatas kita tulis rumusnya untuk : Cell B11 ketikan rumus =ROW(B7) maka didapat hasil 7 karena terletak pada baris ke 7 Cell B12 ketikan rumus =ROWS(B5:B9) maka didapat hasil 5 karena jumlah baris pada range antara B5 sampai B9 adalalah 5 baris. Jadi kesimpulannya : Rumus Excel Row digunakan untuk menghasilkan nomer baris referensi, sedangkan rumus Excel ROWS digunakan untuk menghasilkan jumlah baris dalam referensi. 10. Rumus Excel Choose Rumus Excel Choose yaitu digunakan menampilkan hasil pilihan dari nilai-nilai atau data-data berdasarkan nomer urutan indexnya. Sintak penulisan rumusnya : =CHOOSE(index_number; value1; value2; dst) Ket : Index_number : Nomer urut index data yang akan ditampilkan Value1 : Data untuk nilai urutan index pertama, Value2 : Data untuk nilai urutan index kedua dst Contohnya =CHOOSE(1;”Low”;”Medium”;”Hight”) hasilnya adalah Low Kerena value urutan no 1 adalah Low Jika kita gantik index_numbernya menjadi 2 =CHOOSE(2;”Low”;”Medium”;”Hight”) Maka hasilnya akan tampil Medium. Berikut ini contoh dalam tabel excel Rumus Excel Choose Pada tabel diatas ini kita akan buat data yang digunakan untuk menampilkan nama bulan dari Januari, Februari sampai Desember secara berururutan. Jadi ketika kita ketikan index_number pada cell bulan ke maka akan muncul nama bulan sesuai urutannya Misalnya index_number 3 akan menghasilkan nama bulan Maret Rumusnya yaitu, pada cell A5 ketikan rumus : =CHOOSE(B4;”Januari”;”Februari”;”Maret”;”April”;”Mei”; “Juni”; “Juli”; “Agustus”; “September”; “Oktober”; “November”; “Desember”) Atau bisa juga seperti tabel dibawah ini nama-nama bulannya sudah kita ketikan pada cell-cell excel maka valuenya kita ganti menjadi urutan cel-cellnya. Rumus Excel Choose 2 rumusnya menjadi =CHOOSE(B4;A7;A8;A9;A10;A11;A12;B7;B8;B9; B10; B11; B12) 11. Rumus Excel Offset Rumus Excel OFFSET digunakan untuk mengambil data yang terdapat pada range berdasarkan cell awal dengan menetapkan jarak kolom dan baris posisi data yang diambil. Sintak penulisan rumusnya : =OFFSET(reference;rows;cols;[height];[width]) Ket : reference : merupakan cell yang dijadikan sebagai patokan awal rows : pergeseran baris dari patokan referencenya. cols: pergeseran kolom dari patokan referencenya. height : banyaknya data tinggi atau baris yang akan diambil dari rentang data (optional) width : banyaknya data lebar atau kolom yang akan diambil dari rentang data (optional) Contoh penggunaannya pada tabel dibawah ini. RUMUS EXCEL OFFSET =OFFSET(A4;0;0;1;1) hasilnya 6, patokan awal pada cell A4 tetap tidak bergeser karena rows 0 dan cols 0 artinya hasilnya tetap di cell A4 atau 6. =OFFSET(A4;2;1;1;1) hasilnya 13, patokan awal pada cell A4 baris bergeser 2 kebawah dan cols bergese r 1 ke kanan maka didapat hasil cell B6 atau 13. =OFFSET(A4;3;2;1;1) hasilnya 19, patokan pada cell A4 baris bergeser 3 kebawah dan row bergeser 2 kekanan maka didapat hasil pada cell C7 atau 19. Selanjutnya coba kita gabungkan rumus OFFSET dengan rumus lain, berikut ini contohnya : PENGGABUNGAN RUMUS EXCEL OFFSET Average digabung offset =AVERAGE(OFFSET(A4;0;0;3;1)) menghitung rata-rata dengan rentang offset, patokan awal A4 pergeseran 0,0 dengan rentang 3 baris satu kolom. didapat hasil 7 6 +7 +8 = 21/3 =7 Sum digabung offset =SUM(OFFSET(A4;0;1;3;2)) menjumlahkan data dengan rentang offset, patokan awal A4 pergeseran baris 0,kolom 1 dengan rentang 3 baris 2 kolom, didapat hasil 87 11+12+13+16+17+18 = 87