Rumus Microsoft Excel yang Paling Sering Digunakan Bagian 2

July 28, 2013 at 11:25 am 2 comments

Tulisan ini merupakan bagian dari artikel sebelumnya yang khusus membahas rumus microsoft excel yang paling sering digunakan, dan untuk kali ini akan saya fokuskan membahas sebagian rumus microsoft excel yang paling sering digunakan yang termasuk pada kelompok  Lookup & Reference.  Contoh kasus pada artikel ini sengaja saya berikan yang sangat dasar, agar pembaca dapat lebih mudah memahaminya, akan tetapi untuk contoh-contoh yang lebih kompleks silahkan anda lihat pada berkas yang akan saya sertakan pada akhir tulisan ini.

OK, langsung saja berikut ini sebagian dari fungsi-fungsi yang tergolong dalam kelompok Lookup & Reference:

FUNGSI CHOOSE
Fungsi CHOOSE digunakan untuk memilih nilai dari daftar nilai, fungsi ini mirip dengan fungsi IF, hanya saja fungsi CHOOSE tidak berdasar suatu logical_test tertentu sebagaimana fungsi IF, tetapi berdasar urutan angka (mulai dari 1 sampai dengan 254).

Sintaks
CHOOSE(index_num, value1, [value2], ….)

dimana:

  • index_num adalah angka dari 1 sampai dengan 254
  • value 1, value2 adalah argumen 1 sampai dengan 254. Argumen bisa berupa nilai tertentu, teks, formula, fungsi dan referensi sel.

Contoh

fungsi choose excel

Buatlah sebuah tabel seperti contoh diatas masing-masing seperti pada Gambar A dan Gambar B dengan mengosongkan pada cell C2 untuk Gambar A dan Cell F2 untuk Gambar B, Lalu tuliskan rumus sebagai berikut, lalu Copy kan untuk baris berikutnya:
Gambar A = CHOOSE(B2,”Pusat”,”Cabang”,”RS”)
Gambar B = CHOOSE(E2,”Sangat Puas”,”Puas”,”Tidak Puas”,”Sangat Tidak Puas”)

Penjelasan Rumus:

  • Gambar A  –>> Akan mengklasifikasikan (mengelompokkan) nama outlet yang terdiri dari roxy, condet dan seterusnya berdasarkan no. kantor (cell B2) dengan ketentuan secara berurutan jika 1 = Pusat, 2 = Cabang, 3 = RS (“Pusat”,”Cabang”,”RS”).
  • Gambar B –>>  Menentukan label atau kategori nilai berdasarkan hasil rating (E2) dengan ketentuan Range nilai jika 1-1.9 = Sangat Puas, 2-2.9 = Puas, 3-3.9 = Tidak Puas, =/>4 = Sangat Tidak Puas (“Sangat Puas”,”Puas”,”Tidak Puas”,”Sangat Tidak Puas”).

FUNGSI ADDRESS
Fungsi Address digunakan untuk mengambil representasi teks dari referensi alamat cell berdasarkan posisi kolom dan baris atau Mengembalikan referensi sebagai teks ke satu cell dalam lembar kerja. Tipe data dari nilai kolom dan baris adalah angka / integer.

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
dimana :

  • row_num : adalah posisi baris (harus diisi)
  • column_num : adalah posisi kolom (harus diisi)
  • abs_num : adalah isian yang menentukan tipe pengembalian alamat – nilai-nilainya adalah sebagai berikut :
    • 1 : alamat dalam format absolut (sama dengan jika nilai abs_num tidak diisi)
    • 2 : alamat dalam format absolut untuk baris / row, sedangkan kolom / column tetap relatif
    • 3 : alamat dalam format absolut untuk kolom / column, sedangkan baris / row tetap relatif
    • 4 : alamat dalam format relatif
  • a1 : jika TRUE maka akan mengembalikan format kolom berdasarkan alfabet (A, B, C… AA, …) dan baris berdasarkan angka (1,2,3,…). Sedangkan jika FALSE maka akan mengembalikan berdasarkan angka namun dengan prefix R untuk baris dan prefix C untuk kolom.
  • sheet_text : penambahan teks di depan alamat

Contoh
fungsi address excel

Buatlah tabel seperti pada contoh gambar diatas, lalu tuliskan rumus masing-masing pada :
Cell A6 = ADDRESS(B2,B3,4) –>> Maksud dari rumus ini adalah ingin mengetahui letak sebuah cell yang keberadaannya sesuai dengan nilai yang ada pada cell referensibari, untuk cell refensi B2 nilainya 1, artinya adalah Baris ke 1, untuk cell refensi B3 nilainya adalah 1 artinya kolom ke 1, dan apabila ditemukan tampilkan dalam format Relatif. Hasilnya adalah Cell yang berada pada cell referensi B2 dan B3 tersebut adalah Cell “A1”.
Cell A7 = ADDRESS(1,1,4) –>> Rumus ditulis secara manual. Maksud dari rumus ini adalah ingin mengetahui letak sebuah cell yang berada pada baris ke 1 dan kolom ke 1, dan apabila ditemukan tampilkan dalam format Relatif. Hasilnya adalah Cell yang berada pada baris ke 1 dan kolom ke 1 tersebut adalah Cell “A1”.

FUNGSI FORMULATEXT
Fungsi FORMULATEXT digunakan untuk menampilkan rumus yang berada pada Cell referensi sebagai teks.

Sintaks
=FORMULATEXT(reference)
dimana :
reference adalah referensi ke satu sel atau rentang cell

Contoh
fungsi formulatext excel

Tabel diatas adalah tabel yang saya gunakan pada pembahasan fungsi address, sehingga kita dapat secara langsung menerapkan fungsi berikutnya.

cell A6 = FORMULATEXT(B6) –>> Maksudnya adalah kita ingin menampilkan rumus yang diterapkan pada cell B6 dalam bentuk Teks, dan rumus yang berada pada cell B6 tersebut adalah =ADDRESS(1,1,4).

FUNGSI INDIRECT
Fungsi Indirect adalah untuk menampilkan referensi yang ditunjukkan dengan nilai teks, referensi dievaluasi untuk menampilkan isinya.

Sintaks
INDIRECT(ref_tex, [a1])
dimana:

  • ref_text: Sebuah referensi ke cell
  • A1 : Sebuah nilai logika yang menentukan jenis referensi apa yang terdapat di dalam ref_text.

Contoh
fungsi indirect excel

Tabel diatas adalah tabel yang saya gunakan pada pembahasan fungsi address, sehingga kita dapat secara langsung menerapkan fungsi berikutnya.

Cell A9 = INDIRECT(B5) –>> Pada cell B6, bahwa kolom A baris ke 1 (A1) itu jika ditampilkan nilai yang terdapat pada referensi cell A1 tersebut isinya adalah teks “Contoh”.

FUNGSI VLOOKUP, LOOKUP, INDEX, MATCH
Untuk Fungsi-fungsi ini sudah pernah dibahas sebelumnya silahkan saja baca artikel berikut : iferor dan vlookupPenerapan Fungsi if dan LookupFungsi Index, Match dan Vlookup.

FUNGSI OFFSET
Mengembalikan referensi ke rentang yang merupakan jumlah baris dan kolom tertentu dari sel atau rentang sel. Referensi yang dikembalikan dapat berupa sel tunggal atau rentang sel. Anda dapat menentukan jumlah baris dan jumlah kolom yang dikembalikan.

Sintaks
OFFSET(reference, rows, cols, [height], [width])
dimana:
  • Reference : Referensi yang dijadikan dasar offset. Referensi harus merujuk ke sebuah sel atau rentang sel yang berdekatan, jika tidak, OFFSET mengembalikan nilai kesalahan #VALUE!.
  • Rows : Jumlah baris, ke atas atau ke bawah, yang Anda inginkan untuk dirujuk oleh sel kiri atas. Menggunakan 5 sebagai argumen baris menentukan bahwa sel kiri atas dalam referensi adalah lima baris di bawah referensi. Baris bisa berupa positif (yang berarti di bawah referensi awal) atau negatif (yang berarti di atas referensi awal).
  • Cols : Jumlah kolom, ke kiri atau ke kanan, yang Anda inginkan untuk dirujuk oleh sel kiri atas. Menggunakan 5 sebagai argumen cols menentukan bahwa sel kiri atas dalam referensi adalah lima kolom ke kanan referensi. Cols bisa berupa positif (yang berarti ke kanan referensi awal) atau negatif (yang berarti ke kiri referensi awal).
  • Height : Tinggi, dalam jumlah baris, yang merupakan hasil yang Anda inginkan. Tinggi harus berupa bilangan positif.
  • Width : Lebar, dalam jumlah kolom, yang merupakan hasil yang Anda inginkan. Lebar harus berupa bilangan positif.

Keterangan

  • Jika referensi baris dan kolom melebihi tepi lembar kerja, maka OFFSET mengembalikan nilai kesalahan #REF!.
  • Jika tinggi atau lebar dikosongkan, diasumsikan tinggi atau lebarnya sama dengan referensi.

Contoh

fungsi offset excel

Penulisan dan penjelasan rumus untuk contoh diatas adalah :

  • Cell C7 = OFFSET(D6,-4,-2) –>> Maksudnya adalah dengan menggunakan cell referensi D6, kemudian akan dicari suatu nilai yang berada pada 4 baris keatas dari cell D6 (-4) dan 2 kolom ke kiri dari cell D6 tersebut (-2), dan hasilnya sesuai dengan rujukan baris dan kolom tersebut adalah tepat pada cell B2 yang di dalam Cell B2 tersebut terdapat angka 4.
  • Cell C8 = SUM(OFFSET(D6:E8,-4,-2,3,2)) –>> Maksudnya adalah dengan menggunakan cell referensi D6:E8 atau bisa juga D6:E8, kemudian akan dicari suatu nilai yang berada pada 4 baris keatas dari cell D6:E8 (-4) dan 2 kolom ke kiri dari cell D6:E8 tersebut (-2), dan hasilnya sesuai dengan rujukan range baris dan kolom tersebut adalah tepat pada range cell B2:C4 yang di dalam range cell B2:C4 tersebut terdapat beberapa angka yang apabila keseluruhan angka-angka yang berada pada range cell tersebut dijumlahkan, hasilnya = 34. Range yang diinginkan adalah tingginya 3 (3 baris) dan lebarnya 2 (2 kolom).
  • Cell C9 = OFFSET(D6,-5,-5) –>> Karena 5 baris ke atas dari D6 dan 5 Kolom ke kiri dari cell D6 itu diluar lembar kerja, maka akan ditampilkan pesan kesalahan yang berupa #REF!.

OK……………. untuk sementara waktu itu saja dulu ya, lain kali akan saya jelaskan lebih detail lagi, atau mungkin kawan-kawan punya pertanyaan terkait tulisan ini Feel Free to ask me ! hehehehe. dan bagi siapa saja yang tertarik untuk melihat berkas kerja dari artikel ini silahkan download disini rumus microsoft excel.

That’s All, Thanks.

 

Referensi : http://office.microsoft.com/id-id/excel-help/

Entry filed under: Categories, Excel, Ms. Office. Tags: , , , .

Rumus Microsoft Excel yang Paling Sering Digunakan

2 Comments Add your own

  • 1. zozo  |  March 11, 2014 at 4:10 pm

    Bisa minta tolong gak mas?

    Reply
    • 2. iferor  |  March 13, 2014 at 8:27 am

      tolong apa ya? sorry baru sempat buka

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Recent Posts

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 34 other followers


%d bloggers like this: