Fungsi Choose di excel 2003

Fungsi Choose di excel 2003

Fungsi CHOOSE bisa digunakan untuk memilih salah satu dari ke 29 nilai-nilai didasarkan pada nomor indeks. Sebagai contoh, jika value1 hingga value7 adalah hari dalam seminggu, Dengan Fungsi CHOOSE kita dapat memilih salah satu dari hari-hari ketika angka antara 1 dan 7 digunakan sebagai index_num.

Sintaks

CHOOSE(index_num, value1, value2 ,...)

Catatan: (index_num biasa juga disebut nilai indeks), kita menggunakan index_num, dan value agar memudahkan menyesuaikan dengan format default excel

Pertama tentukan nilai Index_num argumen yang dipilih. Index_num harus angka antara 1 dan 29, atau formula atau referensi ke sebuah sel berisi angka antara 1 dan 29.

Jika index_num adalah 1, CHOOSE kembali value1, jika itu adalah 2, CHOOSE kembali value2; dan sebagainya.
Jika index_num kurang dari 1 atau lebih besar daripada jumlah nilai yang terakhir dalam daftar, CHOOSE mengembalikan #VALUE! sebagai nilai kesalahan.
Jika index_num adalah fraksi (bilangan pecahan),maka index_num tersebut otomatis dipotong ke integer terendah sebelum digunakan.

Value1, value2, ... adalah 1-29 nilai argumen , CHOOSE memilih nilai atau tindakan untuk melakukan berdasarkan index_num. Argumen dapat angka, referensi sel, nama-nama yang didefinisikan, rumus, fungsi, atau teks.


Contoh:
Buat tabel seperti gambar di bawah




1. pada sel C3 masukkan formula
=WEEKDAY(B3) ,
sel C3 sampai C9 akan menghasilkan nilai indeks (index_num) 1 sampai 7
Pada sel C12 masukkan formula
=CHOOSE(WEEKDAY(B12),"Minggu","Senin","Selasa","Rabu","Kamis","Jumat","Sabtu")
Catatan:
"Minggu","Senin","Selasa","Rabu","Kamis","Jumat","Sabtu" adalah nama-nama yang didefenisikan berdasarkan nilai  index_num
Sebenarnya tabel pertama bisa saja tidak dibuat, namun untuk menjelaskan tentang fungsi CHOOSE
Tabel baris 2 sampai 9 bisa saja anda hapus, tidak akan mempengaruhi tabel di bawahnya.

Hasilnya akan tampak seperti gambar di bawah ini

Read More

Fungsi ABS,MODPRODUCT,ROUND,ROUNDOWN,ROUNDUP

Fungsi ABS,MODPRODUCT,ROUND,ROUNDOWN,ROUNDUP

• Fungsi ABS

Fungsi ABS( number) di gunakan untuk mencari nilai absolut dari suatu angka

Number adalah angka real yang akan di cari nilai absolutnya Contoh

• Fungsi MOD

Fungsi MOD(number;divisor) di gunakan untuk mencari sisa hasil pembagian suatu angka

Number adalah angka yang akan dicari sisa hasil baginya

Divisor adalah angka pembagi Contoh

  

Rumus akan membagi dengan pembagi dan sisanya di tampilkan. Dalam contoh tabel di atas 64/4 sisanya 0 dan 34/4 sisanya 2 dst.

• Fungsi PRODUCT

Fungsi PRODUCT(number1;number2...) di gunakan untuk mengalikan seluruh angka dan jumlah maksimal sampai 255 angka

number1;number2...)adalah angka yang di kalikan Contoh

  

Rumus akan mengalikan angka di Nilai 1 Nilai2 dan nilai 3

• Fungsi ROUND

Fungsi ROUND(number;number_digits) di gunakan untuk mencari pembulatan kebawah menuju ke arah Nol.

Number adalah angka yang ingin di cari nilai pembulatanya

number_digits adalah jumlah digit yang di inginkan Contoh

Rumus akan membulatkan sesuai digit yang di inginkan. Dalam contoh tabel diatas 1,25 akan di bulatkan satu angka menjadi 1,3 dan 3,56 menjadi 3,6 dst.

• Fungsi ROUNDOWN

Fungsi ROUNDOWN(number;number_digits) di gunakan untuk mencari pembulatan ke bawah menuju kearah Nol

Number adalah angka yang ingin di cari nilai pembulatan kebawahnya

number_digits adalah jumlah digit yang di inginkan Contoh

  

Rumus akan membulatkan sesuai digit ke bawah yang di inginkan. Dalam contoh tabel diatas 1,257 akan di bulatkan dua angka  kebawah menjadi 1,25 dan 3,567 menjadi 35,6 dst.

 Untuk Fungsi ROUNDUP sama penggunaanya dengan Fungsi Rounddown. Hanya pembulatanya saja yang di arahkan ke atas menjauh dari Nol Contoh

Jika di Fungsi ROUNDDOWN Nilai 1,257 akan di bulatkan dua angka ke bawah menjadi 1,25 dan 3,567 menjadi 35,6 dst. Maka di ROUNDUP Nilai 1,257 di bulatkan ke atas menjadi 1,26 dan 3,567 menjadi 3,57 dst.

Read More

Penggunaan Fungsi Index dan Match pada Excel 2007

Penggunaan Fungsi Index dan Match pada Excel 2007

Pasangan Match dan Index sering digunakan untuk mencari data secara dinamis

Apa itu Fungsi Index dan Match ?

Dalam keseharian pengolahan data dalam Excel hampir dipastikan Anda akan terlibat dalam dua kondisi berikut :

• Melakukan pencarian dari suatu nilai terhadap range data tertentu (referensi).
• Pencarian terhadap referensi tersebut harus cukup dinamis, ini dalam arti dapat mencari dan mengambil data dari kolom ataupun baris manapun yang kita tentukan.

Untuk keperluan hal tersebut, kita dapat melakukannya dengan mudah dari penggunaan dua fungsi Excel, yaitu INDEX dan MATCH.

Fungsi INDEX

Fungsi INDEX adalah fungsi yang cukup sederhana, digunakan untuk mendapatkan nilai dari suatu cell berdasarkan pencarian pada suatu definisi table / data range worksheet kita.

Pencarian digunakan berdasarkan informasi posisi kolom dan baris, dengan acuan berupa kolom dan baris pertama table / data range tersebut.

Syntax fungsi INDEX adalah sebagai berikut :

INDEX(array, row_num, [column_num])

Keterangan :

• array : adalah table / range data yang terdiri dari satu atau beberapa kolom dan baris.
• row_num : adalah angka yang menunjukkan posisi baris dengan acuan dari cell pertama ( kolom / baris ujung kiri atas ) dari array.
• column_num :  adalah angka yang menunjukkan posisi kolom dengan acuan dari kolom / baris pertama dari array. Argumen ini bersifat opsional (boleh digunakan atau tidak).

Penjelasan mengenai fungsi INDEX ini dapat diilustrasikan pada Gambar 1 di bawah ini.

Gambar 1. Ilustrasi Penggunaan Fungsi Index

Dengan formula "=INDEX(B3:B8, 4, 2)", kita mengambil jarak data dari posisi acuan B3 sebesar 2 kolom ke kanan dan 4 baris ke bawah. Kenapa kita mengambil B3 sebagai acuan ? Karena B3 adalah  cell pertama dari array / data range B3:D4.

Hasil dari "navigasi cell" pada fungsi ini adalah nilai dari cell C6, yaitu angka 20.

Gambar berikutnya (gambar 2) menunjukkan hasil penggunaan fungsi INDEX pada worksheet Excel.

Gambar 2. Contoh Penggunaan Fungsi Index (klik untuk memperbesar)

Fungsi MATCH

Fungsi MATCH adalah fungsi yang digunakan untuk mencari suatu nilai dari suatu range yang terdapat pada suatu kolom atau baris, tapi tidak kedua-duanya.

Syntax fungsi MATCH adalah sebagai berikut :

MATCH(lookup_value, lookup_array, [match_type])

Keterangan :

• lookup_value : adalah nilai yang ingin dicari pada lookup_array.
• lookup_array : adalah range data dari suatu kolom ataupun baris.
• match_type :  adalah angka yang menunjukkan tipe pencocokan sebagai berikut :
• 1 : jenis pencocokan dimana lookup_array dalam keadaan terurut secara ascending (kecil ke besar). Pencocokan dilakukan dengan mengambil nilai terbesar dari range data yang lebih kecil atau sama dari lookup_value.
• 0 :  jenis pencocokan dimana pada lookup_array dicari data yang sama persis dengan lookup_value. Urutan data tidak menjadi masalah. Jika diketemukan lebih dari satu data yang sama, maka akan diambil data yang pertama kali diketemukan secara sekuensial.
• -1 :  jenis pencocokan dimana lookup_array dalam keadaan terurut secara descending (besar ke kecil).  Pencocokan dilakukan dengan mengambil nilai terkecil dari range data yang lebih besar atau sama dari lookup_value.

Untuk kejelasan apa yang dimaksud dengan  match_type ini, perhatikan ilustrasi pada Gambar 3 di bawah ini.

Pada contoh tersebut nilai lookup adalah 3, yang kemudian dicari pada data array dengan tiga kelompok susunan data seperti tampak pada gambar.

Gambar 3. Ilustrasi Contoh Penggunaan Match Type - Skema Pertama

Terlihat dengan pilhan match_type mulai dengan nilai  -1, 0 dan 1 didapatkan hasil posisi yang berbeda dari fungsi match, masing-masing yaitu 5, 2, dan 4.


Contoh lainnya terlihat pada Gambar 4 di bawah ini. Pada kasus ini nilai lookupnya adalah 4 yang dicari pada data array dengan nilai 1, 2, 3, 3, 5, 5 dan 6 (sama dengan contoh sebelumnya). Dengan pilhan tiap  tipe mulai dari -1, 0 dan 1 didapatkan posisi dari fungsi match masing-masing adalah 3, NA (Not Available / Tidak Ditemukan), dan 4.

Gambar 4. Ilustrasi Contoh Penggunaan Match Type - Skema Kedua

Screenshot berikut menunjukkan beberapa contoh lebih lanjut penggunaan match pada Excel 2007 (klik pada gambar untuk memperbesar).

Gambar 5. Contoh Penggunaan Match pada Excel (1)

Gambar 6. Contoh Penggunaan Match pada Excel 2007 (2)

Gambar 7. Contoh Penggunaan Match pada Excel 2007 (3)

Penggunaan dari Gabungan Fungsi INDEX dan MATCH

Seperti dijelaskan sebelumnya, penggabungan fungsi INDEX dan MATCH akan menghasilkan solusi pencarian data yang cukup powerful dimana kita dapat mencari dari referensi berdasarkan kolom / baris yang kita inginkan.

Syntax dari penggabungan fungsi ini tampak seperti berikut :

INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), column_num)

jika yang dicari adalah data range baris pada suatu kolom,

atau...

INDEX(array, row_num, MATCH(lookup_value, lookup_array, [match_type]))

jika yang dicari adalah data range kolom pada suatu baris.

Sekilas solusi ini mirip dengan fungsi VLOOKUP yang telah kita bahas sebelumnya. Namun dengan fungsi VLOOKUP kita terbatas pada pencarian pada kolom pertama pada data range referensi dan harus terurut, sedangkan dengan penggabungan fungsi ini kita bisa mencari dari kolom manapun dan tidak perlu dalam keadaan terurut (sesuai match_type tentunya).

Berikut adalah dua gambar contoh penggunaan dari gabungan kedua fungsi INDEX dan MATCH.

Gambar 8. Contoh Penggunaan Index dan Match (1)

Gambar 9. Contoh Penggunaan Index dan Match (2)

Kesimpulan

Fungsi MATCH dan INDEX masing-masing merupakan fungsi untuk melakukan pencarian dan navigasi dari suatu table / data range. Bedanya fungsi MATCH mengembalikan nilai posisi sedangkan fungsi INDEX mengembalikan nilai dari suatu posisi cell.

Penggabungan kedua fungsi tersebut menjadi solusi yang sangat baik sebagai alternatif dari fungsi VLOOKUP yang telah dikenali sebagai fungsi untuk mencari / lookup suatu nilai referensi.

Read More

Fungsi (V,H)LOOKUP Vs. Fungsi MATCH

FUNGSI HLOOKUP, VLOOKUP dan IF

Mungkin anda sudah sangat mengenal fungsi IF karena fungsi ini merupakan fungsi standar yang harus dikuasai dalam menggunakan perhitungan di program Datasheet seperti Excel. Jika hanya menggunakan dua 'kemungkinan', fungsi ini terlihat begitu mudah. Tapi barangkali anda akan menemukan kesulitan jika anda memiliki beberapa 'kemungkinan'. Meskipun batas menggunakan nested fungsi IF hingga lebih dari 64, namun akan sangat tidak efisien jika kita menggunakan fungsi ini untuk kondisi dimana ada banyak 'kemungkinan' yang akan dicek karena kita harus mengetikkan deretan fungsi yang begitu panjang.

1. FUNGSI IF DENGAN DUA KEMUNGKINAN

=IF(AND(A2>0;A2=1);"Tunggal";"Jamak")

. FUNGSI IF DENGAN LEBIH DARI DUA(9) KEMUNGKINAN 


2=IF(A2=1;"Satu";IF(A2=2;"Dua";IF(A2=3;"Tiga";IF(A2=4;"Empat";IF(A2=5;"Lima";IF(A2=6;"Enam";IF(A2=7;"Tujuh"

;IF(A2=8;"Delapan";"Lebih Dari Delapan"))))))))

Anda dapat melihat bagaimana panjangnya fungsi IF yang kita gunakan jika ada lebih dari dua 'kemungkinan'. Karena itulah kita membutuhkan fungsi VLOOKUP dan HLOOKUP untuk menggantikan fungsi IF yang begitu panjang tersebut. Seperti yang dapat dilihat dari nama fungsi yang digunakan, kedua fungsi (VLOOKUP dan HLOOKUP) ini adalah (mungkin) turunan dari fungsi LOOKUP. Kita tidak perlu membahas fungsi LOOKUP karena terus terang saya tidak begitu suka menggunakan fungsi ini :). VLOOKUP adalah fungsi yang mencari nilai pada kolom pertama dari suatu tabel dan menghasilkan sebuah nilai yang memiliki baris yang sama dari kolom pertama dalam tabel tersebut. Seperti yang anda duga, huruf V pada VLOOKUP adalah singkatan dari vertical. Sedangkan HLOOKUP mencari suatu nilai pada baris atas suatu tabel atau array dan kemudian menghasilkan nilai yang memiliki kolom yang sama dengan kolom yang anda maksudkan pada tabel atau array. Konsep array mungkin sudah sangat diakrabi oleh programmer karena hampir dalam semua program konsep array akan selalu ada. Array adalah kumpulan data yang terikat dalam satu variabel. Kalau dimisalkan, array adalah kumpulan lidi yang bergabung menjadi sapu.

Seperti yang anda lihat, tabel sebelah kiri merupakan tabel data yang disusun secara vertikal sedangkan yang sebelah kanan disusun secara horisontal. Penyusunan tabel akan menentukan fungsi apakah yang akan digunakan, menggunakan VLOOKUP atau HLOOKUP. Misalkan kita ingin mengisikan sel 'B3' dengan harga sepatu maka fungsi yang digunakan adalah:

=VLOOKUP(LEFT(A3;LEN(A3)-1);$D$3:$E$7;2;FALSE)
-------1---------  ----2---- 3 --4--

FT(
--atau--
 
=HLOOKUP(L
EA3;LEN(A3)-1);$G$3:$K$4;2;FALSE)

--- 3 --4--

-------1---------  ----2
-

Untuk fungsi VLOOKUP kita menggunakan tabel yang kiri sedangkan fungsi HLOOKUP menggunakan tabel yang kanan. Konsep dasarnya baik fungsi VLOOKUP dan HLOOKUP memiliki tiga parameter penting dan satu parameter tambahan yang dipisahkan tanda titik koma ';' yaitu:

• 1. Sel atau teks yang ingin diuji atau dicek. Pada contoh diatas saya menggunakan fungsi tambahan LEFT dan LEN untuk memotong teks di sel A3 yaitu membuang karakter ':'. Fungsi LEN "terpaksa" digunakan karena panjang karakter di sel A3 tidak pasti. Baik VLOOKUP dan HLOOKUP bersifat incase sensitif alias tidak memperdulikan huruf kapital maupun huruf biasa. Jika range lookup diset FALSE anda dapat menggunakan tanda wildcard seperti '?' atau '*'. Tanda ? menggantikan semua karakter sedangkan tanda * menggantikan beberapa urutan karakter. Misalnya: teks 'mala*' akan sesuai dengan data malang, malas, malam, malaysia, dsb. Sedangkan teks 'pa?i' akan sesuai dengan data padi, pagi, pati, dsb.
• 2. Tabel sumber. Sebenarnya ada cara khusus untuk memberi nama tertentu pada tabel sumber tapi saya lebih suka menggunakan tanda dollar '$' (kebiasaan waktu menggunakan Lotus 123) yang diberikan pada tiap sel agar tabel sumber menjadi absolut atau tetap sehingga ketika rumus dicopykan kebawah tabel sumber tidak berubah atau bergeser. Jika anda lupa memberi tanda dollar tersebut maka bisa berakibat fatal dan fungsi akan menghasilkan data yang salah atau bahkan error.
• 3. Index kolom atau baris yang akan diambil datanya dimulai dari 1 sampai ... berapa banyak kolom yang berada pada range tabel yang anda masukkan. Jika nilai index kolom kurang dari satu, fungsi akan menghasilkan error #VALUE! dan jika lebih besar dari kolom yang anda berikan maka fungsi akan menghasilkan error #REF!.
• 4. Range Lookup yang berisi nilai logical "TRUE" atau "FALSE". Bagian ini dapat dihilangkan karena akan otomatis dianggap TRUE. Jika anda memberi nilai TRUE maka fungsi mencari nilai yang sama dan jika tidak ketemu akan mencari nilai tertinggi dibawah nilai yang diuji. Pastikan data pada tabel sumber berurutan, misalnya -2,-1,0,1,2. Jika data tidak berurutan maka kadang fungsi menghasilkan nilai yang tidak sesuai. Jika diisi FALSE maka hanya data yang sama yang dihasilkan. Data dalam tabel tidak harus berurutan. Jika ada lebih dari satu data yang sesuai maka data pertama yang akan digunakan. Jika data tidak ditemukan maka akan menghasilkan error #N/A

Fungsi MATCH

Fungsi ini akan menghasilkan posisi relatif dari suatu item dalam suatu array yang sesuai dengan nilai yang telah ditentukan dalam urutan tertentu. Anda harus menggunakan fungsi ini saat anda membutuhkan posisi dari suatu item dalam suatu range daripada menggunakan fungsi (V,H)LOOKUP yang menghasilkan nilai item itu sendiri. Mungkin ada pertanyaan, untuk apa sih sebenarnya fungsi ini?. Mungkin sekilas fungsi ini tidak begitu penting tapi dalam beberapa kasus mungkin saja fungsi ini lebih berguna dibanding fungsi yang lain. Sebagaimana konsep array yang sudah saya jelaskan, fungsi ini menghasilkan nilai posisi suatu item dalam array tersebut. Misalnya ada array= (10, 15, 20, 25, 35, 40, 45, 50), dan kita hanya ingin tahu posisi angka 20 pada array tersebut yaitu 3 maka fungsi MATCH dapat dipergunakan untuk maksud ini. Dalam excel posisi array dimulai dari angka 1 dan bukan dari angka 0 ( doh ... )

Dari gambar diatas kita tahu ada semacam form yang digunakan untuk mencari data siswa dilengkapi dengan keterangan peringkat di kelas, keterangan tambahan dan nilai siswa yang bersangkutan. Anda lihat bahwa tabel sumber di sebelah kanan sengaja saya buat menggunakan background warna-warni agar lebih mudah dilihat penggolongan siswa berdasarkan nilai yang dimilikinya. Selain itu data dalam tabel tersebut sudah berurutan dari nilai tertinggi hingga nilai terendah. Tentang bagaimana mengurutkan data tabel silakan anda tanya teman anda :). Saya berusaha agar dalam memasukkan nama siswa anda dapat menggunakan karakter wildcard seperti * atau ?, jadi untuk mencari siswa dengan nama Andi Susilo maka anda dapat memasukkan kata 'andi s*', dsb. Dalam mengisi kolom hasil pencarian ada beberapa kriteria antara lain:

• 1. Nama siswa dan Nilai rapor pada hasil pencarian dicari dengan menggunakan fungsi VLOOKUP
• 2. Peringkat peroleh dengan mencari baris keberapa dari tabel yang mengandung nama siswa yang bersangkutan.
• 3. Keterangan akan saya isikan dengan menggolongkan siswa berdasarkan nilai rapor yang diperoleh. Lima siswa teratas digolongkan 'Sangat Pandai' dikelas, lima berikutnya 'Cukup Pandai', lima siswa dibawahnya 'Agak Pandai' dan terakhir 'Kurang Pandai'. Tentu saja kategori saya buat tanpa ada maksud tertentu karena ini hanya contoh saja. Jadi anda tidak perlu risau jika Joko Budoyo dengan nilai rapor 85,8 digolongkan 'Kurang Pandai' :)

FUNGSI VLOOKUP UNTUK NAMA SISWA (sel B6)
=VLOOKUP(D3;G3:H22;1;FALSE)


POR (sel E6)
=VLOOKUP(D3;G3:H22;2;FALSE)

FUNGSI VLOOKUP UNTUK NILAI R
A
FUNGSI MATCH UNTUK MENCARI PERINGKAT (sel C6)
=MATCH(D3;G3:G22;0)

ILAI DI SEL C6 (se
-1 ---2-- 3

FUNGSI IF UNTUK MENCARI KETERANGAN BERDASARKAN 
Nl D6)

=5;"Sangat Pandai";IF(C6<=10;"Cukup Pandai";IF(C6<=15;"Agak Pandai";"Kurang Pandai")))

=IF(C6
<

Fungsi MATCH memiliki dua parameter penting dan satu paramater tambahan. Bagian tersebut yaitu:

• 1. Nilai yang diuji. Nilai ini bisa berupa sel, teks, angka atau nilai logikal (TRUE/FALSE).
• 2. Array sumber yaitu berupa range beberapa sel yang tidak terputus.
• 3. Jenis pencarian yang berupa angka -1, 0 atau 1. Jika ditentukan jenis 1 maka fungsi akan menghasilkan nilai tertinggi yang lebih kecil atau sama dengan nilai yang dites. Array sumber harus disusun secara berurutan dari data terkecil: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. Jika ditetapkan 0 maka fungsi akan mencari data yang sama persis dan susunan array sumber tidak harus berurutan. Nilai -1 akan membuat fungsi mencari data yang lebih paling kecil yang lebih besar atau sama dengan nilai yang dites. Array sumber harus berurutan secara descending atau dari terbesar ke terkecil. Jika anda tidak memberi nilai pada jenis pencarian maka fungsi akan menganggapnya menjadi 1.

Pada gambar diatas, saya mencoba memasukkan input menggunakan wildcard '*' sehingga untuk mencari nama siswa 'Gunawan Wibisono' saya tidak harus mengetikkan semuanya. Form pencarian telah dapat dipergunakan, hingga kita tidak perlu mencari peringkat seorang siswa secara manual. Anda tinggal mengganti nama siswa di sel D3, maka data siswa akan muncul pada hasil pencarian. Sekarang kita kosongkan sel D3 dan.... ups!

Kita melihat error #N/A pada semua kolom hasil pencarian. Sebenarnya error #N/A juga akan muncul ketika nama yang kita masukkan tidak ditemukan pada tabel sumber. Karena itu kita coba menggunakan fungsi-fungsi tambahan yang mungkin jadi kelihatan agak rumit tapi jika anda merasa tidak terganggu dengan munculnya error #N/A maka anda tidak perlu merubah fungsi yang telah diberikan sebelumnya. Pada prinsipnya kita coba menggunakan fungsi logika tambahan untuk menangani error #N/A tersebut. Diantaranya seperti fungsi ISNA, ISBLANK, atau ISNUMBER ditambah fungsi NOT. Fungsi logika IF jelas sangat berperan disini. Sebenarnya ada fungsi khusus untuk menangani error yaitu fungsi IFERROR tapi karena menurut saya ada beberapa bagian yang tidak bisa dihandle dengan fungsi IFERROR maka saya lebih suka menggunakan kombinasi fungsi logika lainnya. Untuk pembahasan fungsi logika seperti ISBLANK, NOT, AND dsb. akan kita bahas kapan-kapan kalau ada kesempatan :)

FUNGSI VLOOKUP UNTUK NAMA SISWA (sel B6)

=IF(ISBLANK(D3);"";IF(ISNA(VLOOKUP(D3;G3:H22;1;FALSE)) = TRUE;"Tidak Ada Data";VLOOKUP(D3;G3:H22;1;FALSE)))
FUNGSI VLOOKUP UNTUK NILAI RAPOR (sel E6)

2;2;FALSE)) = TRUE;"";VLOOKUP(D3;G3:H22;2;FALSE)))

FUNGSI MATCH UNTUK MENCARI PERINGKAT (sel
=IF(ISBLANK(D3);"";IF(ISNA(VLOOKUP(D3;G3:H
2 C6)
=IF(ISBLANK(D3);"";IF(ISNA(MATCH(D3;G3:G22;0))=TRUE;"";MATCH(D3;G3:G22;0)))


UMBER(C6))=TRUE;"";IF(C6<=5;"Sangat Pandai";IF(C6<=10;"Cukup Pandai";IF
FUNGSI IF UNTUK MENCARI KETERANGAN BERDASARKAN NILAI DI SEL C6 (sel D6)
=IF(NOT(IS
N(C6<=15;

ai";"Kurang Pandai"))))

"Agak Pan
d

Kira-kira cukup begitu pembahasan kita tentang fungsi IF, (V,H)LOOKUP dan MATCH. Sebenarnya saya ingin membuat contoh yang lebih "keren" seperti penggolongan bahan kimia kedalam asam atau basa atau penggolongan hewan berdasarkan kelas ordo, species dsb. Tapi berhubung pengetahuan kimia dan biologi saya agak mepet, maka rencana itu gagal dikerjakan. Tapi anda dapat berimprovisasi menggunakan fungsi-fungsi tersebut sesuai dengan bidang yang anda tekuni. Saya pribadi cuma berharap agar pembahasan kali ini bisa bermanfaat dan Insyaallah lain waktu kita membahas fungsi lain yang belum kita bahas. Terima kasih.

Read More

Scanning Jawaban Pilihan Ganda Pake Excel

Scanning Jawaban Pilihan Ganda Pake Excel

Sebagai guru, saya seringkali mengadakan ujian bagi siswa dan siswi. Nah, pekerjaan tambahan kalau lagi musim ujian adalah mengkoreksi pekerjaan siswa. Kali ini saya ingin membagi pengalaman koreksi pilihan ganda dengan Excel (semacam scanning LJK), tapi jawaban siswa kita masukkan secara manual. Sangat cocok untuk digunakan sebagai analisis butir soal nantinya.Misal kita buat seperti gambar di bawah ini:

Tugas kita adalah meghitung Jumlah Soal, berdasarkan kunci jawaban yang ada, lalu menghitung jumlah jawaban tiap siswa yang benar.
Untuk menghitung jumlah soal tinggal menuliskan rumus di sel E11. Rumusnya :=LEN(D11)-(LEN(D11)-LEN(SUBSTITUTE(D11,"-",""))).
Lalu untuk Skor tiap Soal dan Nilai Maksimal silahkan kita isi sendiri sesuai keinginan kita.
Tahap inti dari tugas kita adalah menghitung berapa jumlah jawaban benar tiap siswa, dimulai dari sel E14. Sebenarnya banyak sekali kombinasi rumus yang bisa kita gunakan, baik menggunakan sel bantuan maupun tanpa sel bantuan. Salah satu kombinasi rumus yang bisa kita tulis di sel E14 adalah :
=SUM((MID(SUBSTITUTE(D14,"-",""),ROW(INDIRECT("1:"&$E$11)),1)=MID(SUBSTITUTE($D$11,"-",""),ROW(INDIRECT("1:"&$E$11)),1))*1)
Ingat...!Rumus di atas adalah rumus Array. Kalau rumus biasa, setelah selesai nulis kita tekan Enter, nah, untuk rumus Array, setelah selesai nulis kita tekanCtrl+Shift+Enter secara bersamaan.Rumus ditulis langsung dalam satu baris tanpa spasi. 
Lalu kita kopikan ke bawah sampai sejumlah siswa.
Rumusnya panjang amat ya...? berikut penjelasan satu persatu dari rumus di atas.
MIDmengambil sebagian karakter tertentu dalam satu sel. Jadi kita ambil satu per satu masing-masing huruf dari jawaban siswa. MID dalam rumus di atas ada dua, yakni untuk sel D14 dan sel D11. Karena kita ingin membandingkan apakah masing-masing huruf jawaban siswa sama dengan kunci jawaban.
SUBSTITUTEFungsi ini untuk mengganti karakter tertentu dari teks. Dalam rumus di atas, kita akan mengganti karakter "-" dengan karakter "". Artinya kita menghilangkan karakter "-" dari jawaban siswa.

INDIRECTRumus ini untuk mengatasi banyaknya soal yang mungkin tidak sama. Pembahasan tentang rumus INDIRECT bisa dilihat di siniROWFungsi ROW untuk menampilkan nomor baris. ROW(A1) hasilnya adalah 1. ROW(A2) hasilnya ya 2. ROW(1:2) hasilnya adalah data array {1,2}.
*1Mengapa harus dikalikan satu?Ya, karena dengan fungsi MID di atas, kita mengambil satu persatu huruf mulai nomor 1 sampai nomor 25, dan kita membandingkan dengan Kunci Jawaban satu per satu juga. Hasil perbandingan akan bernilai TRUE atau FALSE. Jika jawaban siswa "A" dan kunci "A" maka hasilnya TRUE. Ternyata di Excel TRUE sama dengan 1, sedang FALSE sama dengan 0. Agar data Bolean TRUE dan FALSE tadi berubah jadi angka biasa, maka kita kalikan dengan 1. Sehingga untuk jawaban siswa absen 1, akan menghasilkan data array{1,1,1,1,1,1,0,0,0,0,0,1,0,1,1,1,1,1,0,0,0,0,1,1,1}.
SUMMenjumlah data array di atas. Yakni 15.
Untuk Jumlah jawaban salah, kita tinggal mengurangi jumlah soal dengan jumlah jawaban benar : =$E$11-E14.
Untuk jumlah nilai, tinggal mengalikan Jumlah jawaban benar dengan skor tiap item : =E11*$F$11.
Masih bingung?Bagi yang agak pemula, penggunaan rumus Array memang sedikit membuat pusyiiingggg. Gak majalah, eh Gak Masalah, seiring dengan waktu sambil terus kita belajar, segalanya akan makin mudah. Keuntungan menggunakan array formula ini, kita bisa melakukan sesuatu yang tidak bisa dilakukan dengan menggunakan rumus biasa. Penjelasan secara rinci tentang Array Formula bisa dilihat di sini

Read More

Cara Menggunakan Fungsi Ofset Pada Excel

Pemuda Pakarti. Cara Menggunakan Fungsi Ofset Pada Excel

Mempostingkan Tulisan ini salah satu bentuk dari Bela Negara Pemuda Pakarti YGNI Purwojati 

Fungsi offset adalah fitur yang sangat sedikit sekali yang mengetahuinya, fungsi ini sangat berguna sekali bila kita mau membuat rumus sendiri(User Define Formula/UDF). nah sekarang saya akan coba share sedikit pemahaman mengenai offset sendiri


Rumus untuk OFFSET

Nah sekarang bagaimana sih contoh penggunaannya, coba perhatikan gambar dibawah, sedikit contoh
Contoh 1 :  Disini misalkan kita mau mengambil nilai "2" yang tertera digambar bawah, dengan fungsi offset..

Contoh 2 :  Nah berikut saya coba gambarkan apa sih kegunaan dari offset ini, masa cuman untuk ambil nilai saja, nah berikut saya coba berikan sedikit contoh
mudah-mudahan bisa untuk dipahami, jadi jelaskan. dengan offset ini, kalian bisa mengembagkan fungsi-fungsi lain yang lelbih banyak. Selamat Mencoba.

Read More

Cara Mengcopy Rumus di MS Excel Tanpa Merubah Referensi Cell

Pemuda Pakarti Cara Mengcopy Rumus di MS Excel Tanpa Merubah Referensi Cell

Mempostingkan Tulisan ini salah satu bentuk dari Bela Negara Pemuda Pakarti YGNI Purwojati 

(Cara men-copy rumus di microsoft excel tanpa merubah format yang ada) – Ketika kita bekerja dengan halaman microsoft excel dan kamu ingin mengkopi sebuah rumus untuk ditaruh ke tempat lain, maka Excel akan otomatis mengatur rumus referensi sell pada lokasi yang baru. Jika kita tidak ingin hal itu terjadi, maka kita dapat merubahnya ke dalam posisi absolut dengan cara meletakkan tanda [$] disetiap depan rumus, contoh: A1+A2 dirubah menjadi absolut $A$1+$A$2 atau dapat melakukan langkah-langkah berikut ini:

• Klik pada bagian cell yang memiliki rumus untuk di copy
• Tekan [F2]
• Klik dan geser untuk memblok semua rumus yanga da di sell tersebut
• Klik pada menu Copy di standard toolbar
• Tekan [Enter]
• Pilih sell lain kemana rumus tersebut ingin dipaste (di tempelkan)
• Klik pada menu paste di bagian standard toolbar

Ketika kamu melakukan langkah-langkah mengcopy rumus di mikrosoft excel diatas, maka formula akan dicopy ke dalam format text dan tidak akan otomatis mengatur referensi cell.
Kita juga dapat menyalin bagian dari rumus dengan cara ini. Sebagai contoh, Kita mungkin ingin memasukkan rumus sebagai bagian dari pernyataan Jika ada di bagian lain dari lembar kerja microsoft excel.

Read More