Fungsi AVERAGEIF Ms Excel

Fungsi AVERAGEIF digunakan untuk mencari rata-rata jika kondisi yang diinginkan terpenuhi.

Deskripsi
Average merupakan fungsi Microsoft Excel untuk mencari rata-rata nilai angka tanpa memperhatikan kondisi. Misalnya, menentukan rata-rata bonus belanja setiap pelanggan. Dengan AVERAGEIF, Anda dapat menambahkan kondisi, misalnya mencari rata-rata bonus belanja di atas 500.000. Itu artinya, bonus di bawah 500.000 diabaikan.

Sintak AVERAGEIF
=AVERAGEIF(range, "kondisi")
-> range yaitu sejumlah sel yang akan dicari rata-ratanya. Misalnya dari sel B2 sampai B5.
-> kondisi yaitu kondisi yang harus dipenuhi jika rata-rata bonus belanja (dalam contoh ini) di atas 500.000.

Operator
Untuk menguji kondisi dibutuhkan operator pembanding. Di antaranya, > (lebih besar), = (sama dengan), < (lebih kecil), <= (kecil sama dengan) serta operator lainnya.

Anda perhatikan penulisan kondisi diapit tanda " (kutip dua). Jadi, >500.000 maka ditulis ">500000".

Contoh: Cari rata-rata bonus belanja jika di atas 500.000.

Cari bonus belanja di atas 500.000.

Maka rumusnya adalah, letakkan kursor di sel B8 dan ketik rumus : =AVERAGEIF(B2:B7;">500000") dan tekan Enter.

Penulisan rumus.

Jika dijelaskan secara rinci maka proses penulisan rumus di atas adalah:
1. Klik sel B8.
2. Ketik =AVERAGEIF( kemudian blok sel B2 sampai B7, beri titik koma, ketik ">500000", beri tutup kurung kemudian Enter.

Referensi tulisan:

1. AVERAGEIF function; ditulis oleh: Tim Support Office, support.office.com, diakses 05 Mei 2015.

Fungsi ABS Ms Excel

Fungsi ABS digunakan untuk mengembalikan nilai absolut dari suatu angka. Tanda yang melekat pada angka itu dihilangkan.

Deskripsi
Untuk angka negatif ditandai dengan '-', misalnya -4. Untuk angka positif cukup dengan 4 saja atau (+4). Jika menggunakan fungsi ABS, -4 atau 4 maka dianggap 4, begitu juga dengan angka lain. Fungsi ABS berlaku untuk angka bulat atau berkoma.

Sintak ABS
=ABS(angka)
angka : mengetikkan angka secara langsung atau letak sel angka berada.

Contoh
Gunakan fungsi ABS untuk angka-angka berikut:

Contoh soal untuk fungsi ABS.

Untuk menambahkan tanda + atau () gunakan tanda ' di awal.

Penyelesaian:
1. Untuk angka pertama, kli sel B2 dan ketik rumus: =ABS(A2) dan Enter.
1. Untuk angka kedua, kli sel B3 dan ketik rumus: =ABS(A3) dan Enter.
1. Untuk angka ketiga, kli sel B4 dan ketik rumus: =ABS(A4) dan Enter.
1. Untuk angka keempat, kli sel B5 dan ketik rumus: =ABS(A5) dan Enter.
1. Untuk angka kelima, kli sel B6 dan ketik rumus: =ABS(A6) dan Enter.

Hasil:

Hasil akhir.

Referensi tulisan:

1. ABS function; ditulis oleh: Tim Support Office, support.office.com, diakses 25 April 2015.

Fungsi ROUND Ms Excel

Fungsi ROUND digunakan untuk pembulatan angka dengan menentukan batasan angka di belakang koma.
Kegunaan
Fungsi ROUND sangat berguna untuk mengolah data angka berkoma. Contohnya angka; 5,5777 dan 8,9368. Angka pertama jika dibulatkan dengan batasan 2 angka di belakang koma menjadi 5,58 sedangkan angka kedua dengan jumlah koma sama menjadi 8,94. Ini contoh sederhananya, contoh lain pembulatan angka uang atau nilai rapor atau perhitungan matematika lainnya.

Sintak penulisan ROUND
Rumusnya: =ROUND(angka_desimal; jml_koma)
angka_desimal: Letak angka desimal berada (alamat selnya).
jml_koma: Jumlah angka di belakang koma.

Contoh
Gunakan fungsi ROUND untuk angka-angka berkoma berikut:

Contoh data untuk fungsi ROUND.

Ketentuan soal:
1. Soal pertama, 2 angka di belakang koma.
2. Soal kedua 2 angka di belakang koma.
3. Soal ketiga 3 angka di belakang koma.
4. Soal keempat 3 angka di belakang koma.
5. Soal kelima dibulatkan saja tanpa ada koma.
6. Soal keenam juga dibulatkan saja, hilangkan koma.
7. Soal terakhir 2 angka di belakang koma.

Penyelesaian:
1. Untuk soal pertama, klik sel B2 dan ketik rumus: =ROUND(A2;2) kemudian Enter.
Langkah pengetikan rumus: Klik sel B2, ketik =ROUND( kemudian klik sel A2, beri titik koma, ketik 2, beri tutup kurung dan Enter.
2. Soal kedua, klik sel B3 dan ketik rumus: =ROUND(A3;2)
3. Soal ketiga, klik sel B4 dan ketik rumus: =ROUND(A4;3)
4. Soal keempat, klik sel B5 dan ketik rumus: =ROUND(A5;3)
5. Soal kelima, klik sel B6 dan ketik rumus: =ROUND(A6;0)
6. Soal keenam klik sel B7 dan ketik rumus: =ROUND(A7;0)
7. Soal terakhir, klik sel B8 dan ketik rumus: =ROUND(A8;2)

Hasil akhir:

Hasil akhir.

Referensi tulisan:

1. ROUND function; ditulis oleh: Tim Support Ms Office, support.office.com, diakses 13 Januari 2015.

Fungsi-fungsi Konversi Sistem Bilangan di Ms Excel

Microsoft Excel menyediakan fungsi-fungsi untuk mengkonversi sistem bilangan yang umum digunakan; bilangan desimal, bilangan heksadesimal, bilangan oktal dan bilangan biner.

Sistem bilangan
Sistem bilangan desimal menggunakan basis 10 angka dimulai dari 0,1 sampai 9. Setelah angka 9, diganti dengan 0, 1, 2,..9. Contohnya 1 0, 1 1. Sistem bilangan desimal ditemukan oleh Al-Kashi, ilmuwan dari Persia.

Sistem bilangan heksadesimal menggunakan 16 simbol. Simbol yang digunakan adalah 0 sampai 9, ditambah 6 simbol lainnya dengan huruf A hingga F, hingga totalnya 16 simbol. Sistem bilangan ini digunakan dalam format warna heksa, yang diawali dengan #.

Sistem bilangan oktal menggunakan basis 8 angka, dimulai dari 0 sampai 7.

Sistem bilangan biner ditemukan oleh Gottfried Wilhelm Leibniz pada abad ke-17. Sistem bilangan ini menggunakan dua simbol yaitu 0 dan 1. Sistem ini merupakan dasar semua sistem bilangan berbasis digital, disebut juga istilah bit atau binary digit.

Konversi sistem bilangan di Ms Excel
Berikut konversi masing-masing sistem bilangan dan rumusnya:

Desimal
Desimal ke Biner : =DEC2BIN(angka)
Desimal ke Heksadesimal :  =DEC2HEX(angka)
Desimal ke Oktal : =DEC2OCT(angka)

Heksadesimal
Heksadesimal ke Biner : =HEX2BIN(angka)
Heksadesimal ke Desimal : =HEX2DEC(angka)
Heksadesimal ke Oktal : =HEX2OCT (angka)

Oktal
Oktal ke Biner : =OCT2BIN(angka)
Oktal ke Desimal : =OCT2DEC(angka)
Oktal ke Heksadesimal : =OCT2HEX(angka)

Biner
Biner ke Desimal : =BIN2DEC(angka)
Biner ke Heksadesimal : =BIN2HEX(angka)
Biner ke Oktal : =BIN2OCT(angka)

Sintak penulisan rumus:
Pilih salah satu rumus, misalnya mengkonversi angka desimal 88 ke biner. Maka rumusnya adalah: =DEC2BIN(88)

Contoh demo
Berapa angka biner dari angka desimal 88 di Microsoft Excel? Jawabannya adalah:
1. Buka Microsoft Excel.
2. Klik sembarang sel, misal sel A1.
3. Ketik =DEC2BIN(88) kemudian Enter.
4. Angka binernya: 1011000.

Screenshot:

Langkah 3.

Hasil.

Referensi tulisan:

1. Sistem bilangan desimal; ditulis oleh: Kontributor Wikipedia, id.wikipedia.org, diakses 04 April 2015.

Fungsi COUNTBLANK Ms Excel

Fungsi COUNTBLANK menghitung jumlah sel yang bernilai kosong pada sekumpulan sel tertentu. Kosong di sini artinya sel tidak ada data, jika sel berisi 0 (nol) maka tidak berarti kosong.

Deskripsi
Untuk mengetahui berapa sel yang masih kosong dalam satu lembar kerja (worksheet) Anda tidak perlu menghitungnya secara manual (satu persatu). COUNTBLANK menghitung sel dengan range (batas), secara vertikal atau horizontal.


Penulisan sintak
=COUNTBLANK(range)

range : sekumpulan sel, apakah ada datanya kosong.

Penulisan rumus COUNTBLANK dengan contoh

Contoh data untuk COUNTBLANK.

Maka rumusnya adalah:

1. Perhatikan range sel yang akan diberi fungsi COUNTBLANK. Contoh di atas dari sel B2 sampai sel B7.
2. Tentukan posisi hasil (jumlah sel yang kosong) diletakkan di sel berapa. Misalnya di sel B8, maka rumus diketik di sel B8. Rumusnya: =COUNTBLANK(B2:B7)
3. Tekan Enter.

Penulisan rumus.

Fungsi CELL Ms Excel

Fungsi CELL pada Microsoft Excel menampilkan informasi sel sesuai dengan tipe info.

Deskripsi
Dengan fungsi CELL Anda bisa mengetahui di mana lembar kerja Anda disimpan (lengkap dengan letak drive-nya), mengetahui apakah suatu cell berisi huruf atau angka, mengetahui posisi baris cell dan posisi kolom suatu cell.

Penulisan rumus
=CELL("tipe_info"; sel)

tipe_info: kata kunci yang dikenal Ms. Excel disebut sebagai referensi.
sel: sel dimaksud.
; (titik koma sebagai pembatas rumus).

Tipe info
Berikut beberapa tipe info dan informasi yang dihasilkan:
address : mengembalikan informasi posisi sel.
col : mengembalikan informasi posisi sel dalam kolom.
prefix : mengetahui rata sel, apakah rata kiri, tengah atau kanan. Rata kiri disimbolkan dengan ('), tengah (^), kanan (").
row : mengembalikan informasi posisi baris sel.
type : mengetahui tipe data dalam sel, jika angka (v), huruf-angka (I), jika sel kosong (b).
filename : mengetahui lembar kerja tersimpan di folder (drive) mana.
content : mengambil isi sel (hasil rumus), bukan rumusnya.

Contoh
Gunakan fungsi CELL sesuai tipe info pada data berikut:

Contoh data untuk fungsi CELL.

Ket: sel A8 bukan diketik "BANGUN PAGI", tetapi dengan rumus: =UPPER(bangun pagi)

Maka rumusnya adalah:

1. Rumus pertama, klik sel C2 dan ketik rumus: =CELL("address";A2) kemudian Enter.
2. Rumus kedua, klik sel C3, rumus: =CELL("col";A3)
3. Rumus ketiga, klik sel C4, rumus: =CELL("prefix";A4)
4. Rumus keempat, klik sel C5, rumus: =CELL("row";A5)
5. Rumus kelima, klik sel C6, rumus: =CELL("type";A6)
6. Rumus keenam, klik sel C7, rumus: =CELL("filename";A7)
7. Rumus ketujuh, klik sel C8, rumus: =CELL("contents";A8)

Ket 2: khusus rumus keenam sel A7 boleh diganti dengan sel manapun.

Hasil akhir:

Tampilan akhir.

Referensi tulisan:

1. CELL function; support.office.com, diakses 18 Maret 2015.

Fungsi IF NOT Ms Excel

Fungsi NOT pada Microsoft Excel mengubah nilai TRUE menjadi FALSE atau FALSE menjadi TRUE. NOT artinya tidak (bukan). Fungsi NOT sering dikaitkan dengan fungsi IF karena berkaitan dengan pengujian logika. Misalnya, jika total belanja tidak lebih besar dari 100.000 maka tidak ada diskon, jika lebih diskon 5%.

Penulisan rumus NOT (murni): =NOT(sel)

sel: letak sel. Jika di sel A3 diketik TRUE kemudian di sel B4 diketik rumus =NOT(A3) kemudian hasilnya FALSE, begitu sebaliknya.

Fungsi IF NOT
Sejalan dengan diskon belanja di paragraf pertama berikut contoh penyelesainnya dengan fungsi IF NOT. Rumus: =IF(NOT(sel; pembanding; syarat); hasil_true; hasil_false)

- sel: letak sel.
- pembanding: menggunakan salah satu operator pembanding, misalnya lebih besar (>).
- syarat: dari contoh diskon di atas total belanja harus tidak kecil dari 100000, berarti harus di atas 100000.
- hasil_true: jika syarat terpenuhi. Kasih diskon 5% dari total belanja.
- hasil_false: jika syarat tidak terpenuhi, diskon tidak ada (0). hasil_false bersifat opsional, tidak harus ada.
- ; (titik koma): pembatas rumus, dalam beberapa versi bisa juga menggunakan , (koma).

Contoh data untuk fungsi IF NOT.

Rumus penyelesaiannya adalah:

1. Klik sel B2 dan ketik rumus: =IF(NOT(A2>100000);0;5%*A2) kemudian Enter. Hasilnya 0 (tidak ada diskon).

Keterangan pengetikan rumus: klik sel B2 dan ketik =IF(NOT( kemudian klik sel A2, beri tanda >, ketik 100000, tutup kurung. Beri titik koma, ketik 0, titik koma lagi, ketik 5%*, klik sel A2, beri tutup kurung dan Enter.

2. Diskon baris kedua, klik sel B3 dan ketik rumus: =IF(NOT(A2>100000);0;5%*A2)
3. Untuk baris ketiga dan selanjutnya tak perlu ketik rumus lagi, gunakan Copy Cells..., caranya: arahkan kursor ke pinggir bawah kanan sel B3 sehingga kursor berubah menjadi tanda plus hitam.

Tanda plus hitam.

4. Klik kiri di atasnya dan tahan kemudian geser sampai baris terakhir (sel B5), setelah itu lepaskan. Hasil akhirnya:

Tampilan akhir.

5. Selesai.

Fungsi UPPER Ms Excel

Fungsi UPPER pada Microsoft Excel mengubah huruf kecil ke huruf besar (huruf kapital). Huruf bisa berupa satu karakter, kata atau kalimat panjang. Huruf termasuk angka Romawi. Contoh: kata fungsi akan menjadi FUNGSI, angka Romawi xii menjadi XII.

Penulisan rumus UPPER: =UPPER(letak_sel)

letak_sel: di sel berapa letak huruf berada. Misalnya sel B2, C4 atau lainnya.

Contoh kasus: Ubah kata atau kalimat berikut menjadi huruf besar!

Contoh kata atau kalimat untuk fungsi UPPER.

Keterangan:
- Kalimat pertama diketik di sel A3, kalimat berikutnya di sel A4, A5 dan A6.
- Hasil fungsi UPPER di letakkan di sel A10, A11, A12 dan A13.

Maka rumusnya adalah:

- Ubah kalimat pertama ke huruf besar, klik sel A10 dan ketik rumus: =UPPER(A3) kemudian Enter. Screenshot:

Penulisan rumus UPPER.

- Kalimat kedua, klik sel A11 dan rumusnya: =UPPER(A4)
- Untuk kata berikutnya (misalnya datanya banyak) tidak perlu menulis ulang rumus berulang karena itu akan menghabiskan banyak waktu, manfaatkan Copy Cells...
Caranya:
a. Arahkan kursor ke baris terakhir - tepat di kanan bawah sel, pada contoh berarti sel A11 - sehingga kursor berubah menjadi plus hitam (tanda +).

Tanda plus hitam.

b. Klik kiri dan tahan kemudian geser ke bawah (sejauh mana diinginkan). Padah contoh ini sampai sel A13 (karena datanya cuma 4 baris pada soal), kemudian lepaskan. Hasil akhir:

Hasil akhir.

Selesai!

Fungsi LOWER Ms Excel

Fungsi LOWER pada Microsoft Excel adalah mengubah huruf besar (capital case) ke huruf kecil, termasuk angka Romawi. Kata seperti: EXCEL, Excel, eXcEL atau ExCEL akan diubah menjadi excel. Angka Romawi XXIV menjadi xxiv.

Selain kata, bisa juga hanya satu huruf atau berupa kalimat panjang akan dikonversi Ms Excel dalam sekejap.

Format penulisan rumus LOWER: =LOWER(alamat_sel)

alamat_sel: tempat kata atau kalimat berada (di sel berapa).

Contoh:

Contoh kata atau kalimat untuk fungsi LOWER.

Keterangan:
- Kalimat pertama diketik di sel A3.
- Kalimat kedua diketik di sel A4.
- Kalimat ketiga diketik di sel A5.

Maka rumusnya adalah:

- Letakkan kursor misalnya di sel A9 dan ketik rumus: =LOWER(A3) kemudian Enter.

Screenshot penulisan rumus LOWER.

- Begitu juga dengan kalimat kedua, kursor ada di sel A10 dan rumus: =LOWER(A4)
- Kalimat ketiga, kursor di sel A11, rumus: =LOWER(A5)

Hasil akhir:

Hasil akhir.

Fungsi LEN Ms Excel

Jika Anda ingin mengetahui berapa jumlah karakter yang terdapat pada kata atau kalimat di Microsoft Excel maka gunakan fungsi LEN. Karakter yang dihitung bisa angka saja, huruf saja, huruf dan angka, juga karakter khusus dan tanda baca.

Contoh penggunaan fungsi LEN adalah seperti mengetahui jumlah karakter pada suatu nama, email, NIP, NIK, alamat atau data lainnya yang dianggap perlu. Penulisan fungsi ini pun sangat mudah.

Sintak: =LEN(sel_target)

Contoh kasus:

Contoh data untuk fungsi LEN.

Maka rumusnya:

1. Fungsi LEN pertama, klik sel B2 dan ketik rumus: =LEN(A2) kemudian Enter.

keterangan: Mengapa A2? Karena letak sel Kata/kalimat pertama ada di sel A2.

2. Kedua, klik sel B3, rumus: =LEN(A3)

Untuk baris di bawah tidak perlu ketik rumus lagi (jika Anda sudah paham), tapi gunakan Copy Cells...

- Arahkan kursor ke sel B3 di bagian kanan bawah sehingga kursor berubah menjadi plus hitam (+).

Plus hitam.

- Klik kiri dan tahan dan geser sampai ke sel B6 dan lepaskan. Tampilan akhir:

Hasil akhir dengan Copy Cells.

Fungsi CONCATENATE VLOOKUP Ms Excel

CONCATENATE digunakan untuk menggabungkan karakter string. VLOOKUP digunakan untuk menampilkan data berdasarkan kode dari tabel bantu dengan data orientasi vertikal. CONCATENATE VLOOKUP berarti menggabungkan karakter string hasil seleksi VLOOKUP.

Format penulisan rumus: =CONCATENATE(VLOOKUP(fungsi(seleksi); seleksi_tbl_bntu; klom_data); karakter_string_lain...)

fungsi: fungsi seleksi yang digunakan, misalnya LEFT, MID atau RIGHT.
seleksi: karakter yang diambil merujuk ke salah satu fungsi yang digunakan.
seleksi_tbl_bntu: seleksi tabel bantu untuk mendapatkan data yang diinginkan.
klom_data: letak kolom data yang diinginkan.
karakter_string_lain...: string selanjutnya dalam fungsi CONCATENATE.

Contoh kasus: Tentukan Tujuan dari jurusan penerbangan dari Kode sebagai berikut:

Data untuk CONCATENATE VLOOKUP.

Perhatikan Kode tujuan pada soal, kemudian perhatikan Kode pada Tabel bantu. 3 karakter pertama diambil setelah karakter FL- pada Kode tujuan. 3 karakter selanjutnya yang menjadi patokan 3 karakter sebelah kanan dari Kode tujuan pada soal. Disimpulkan, fungsi seleksi yang digunakan adalah fungsi MID dan fungsi RIGHT.

Maka rumusnya adalah:

1. Tujuan pertama, klik sel B2 dan ketik rumus: =CONCATENATE(VLOOKUP(MID(A2;4;3);C9:D13;2;FALSE);"-";VLOOKUP(RIGHT(A2;3);C9:D13;2;FALSE)) kemudian Enter.

tata cara penulisan rumus: klik sel B2 dan ketik =CONCATENATE(VLOOKUP(MID( kemudian klik sel A2, beri titik koma, ketik 4, titik koma, ketik 3, beri tutup kurung. Titik koma lagi, seleksi sel C9 sampai D13, titik koma, ketik 2, titik koma, ketik FALSE, titik koma dan selanjutnya.

penjelasan: Angka 4;3 pada MID artinya mengambil karakter ke-4 dari kiri dan mengambil 3 karakter setelahnya. FALSE artinya memastikan Kode tujuan yang diseleksi sama persis dengan Kode di Tabel bantu. Karakter "-" berfungsi sebagai karakter sambung, rumus di atas menghasilkan: Padang-Jakarta. Karakter "-" sebagai penghubung kota Padang dan Jakarta.

2. Jika masih dalam tahap belajar rumus Tujuan kedua klik B3 dan ketik rumus: =CONCATENATE(VLOOKUP(MID(A3;4;3);C9:D13;2;FALSE);"-";VLOOKUP(RIGHT(A3;3);C9:D13;2;FALSE))

Namun, mengetik rumus satu persatu sampai ke bawah sungguh melelahkan apalagi jumlah data sampai ratusan baris, Anda dapat memanfaatkan fitur Copy Cells... (rumus otomatis).

Caranya: klik sel B3 dan ketik =CONCATENATE(VLOOKUP(MID(A3;4;3);C9:D13 kemudian tekan F4 pada keyboard selanjutnya ketik lagi: ;2;FALSE);"-";VLOOKUP(RIGHT(A3;3);C9:D13 dan ketik lagi: ;2;FALSE)) dan Enter, hasilnya: Padang-Medan.

Selanjutnya, arahkan kursor ke pinggir kanan bawah sel B3 sampai kursor berubah menjadi plus hitam (+).

Tanda plus hitam.

Klik kiri dan tahan kemudian geser sampai sel B5. Hasilnya:

Klik tahan dan geser.

Hasil akhir:

Hasil akhir.

Jika Anda merasa kesulitan dengan cara di atas berikut rumus manual untuk tujuan ketiga dan keempat, penulisannya dengan rumus pada nomor 1:

3. Tujuan ketiga, klik sel B4 dan ketik rumus: =CONCATENATE(VLOOKUP(MID(A4;4;3);C9:D13;2;FALSE);"-";VLOOKUP(RIGHT(A4;3);C9:D13;2;FALSE))

4. Tujuan keempat, klik sel B5 dan ketik rumus: =CONCATENATE(VLOOKUP(MID(A5;4;3);C9:D13;2;FALSE);"-";VLOOKUP(RIGHT(A5;3);C9:D13;2;FALSE))

Selamat mencoba!

Fungsi CONCATENATE Ms Excel

CONCATENATE pada Microsoft Excel digunakan untuk menggabung 2 atau lebih karakter string, objek yang digabung di sini sel di mana karakter berada. Karakter string artinya sembarang karakter yang bisa berupa huruf saja, angka saja atau gabugan huruf dan angka.

Format penulisan rumus: =CONCATENATE(sel_1; sel_2; sel_3; sel_n)

sel_1 dan seterusnya: sel-sel yang akan digabung.
sel_n: sel seterusnya (tak hingga).
; (titik koma): penanda setiap sel.

Contoh: Gunakan fungsi CONCATENATE untuk membentuk kalimat-kalimat pendek:

Contoh data untuk fungsi CONCATENATE.

Soal:

1. SayaSukaPecel
2. SayaSukaMie goreng2Porsi.
3. Saya Suka Rendang.

Maka rumusnya adalah:

1. Klik sembarang sel (sel di mana diletakkan hasil) dan ketik rumus =CONCATENATE(A2;A3;A4)

Contohnya seperti berikut, hasil akan diletakkan di sel C8.

Sel yang digabung: A2, A3 dan A4.

2. Rumus jawaban nomor 2: =CONCATENATE(A2;A3;A5;A7;A8)
3. Untuk jawaban nomor 3 (Saya Suka Rendang), kalimat ini memakai spasi. Maka di rumus juga tambahkan karakter spasi. Rumusnya menjadi: =CONCATENATE(A2;" ";A3;" ";A6)

Hasil akhir:

Tampilan akhir.

Catatan: Anda dapat mengganti karakter spasi (" ") dengan karakter lain, misalnya tanda penghubung ("-") atau lainnya.

Fungsi IF RIGHT Ms Excel

IF dalam bahasa Indonesia artinya jika. Jika di sini merujuk kepada kondisi apakah terpenuhi, maka nyatakan sesuatu atau tampilkan hasil, jika tidak tampilkan juga hasilnya (jika dibutuhkan). RIGHT merupakan fungsi untuk mengambil karakter dari sembarang sel yang berada di bagian kanan ke kiri. Jika ingin mengambil 2 karakter maka fungsi RIGHT-nya 2. IF RIGHT berarti menguji kondisi terpenuhi atau tidak dengan fungsi IF berdasarkan kondisi hasil fungsi RIGHT.

Penulisan rumus: =IF(RIGHT(sel_kode; jml_char)=kondisi; terpnuhi; tdk_trpenuhi)

sel_kode: sel yang akan diuji dan diambil beberapa karakter darinya oleh fungsi RIGHT.
jml_char: jumlah karakter yang diambil dari sel kode dengan fungsi RIGHT.
= (sama dengan): sebagai operator penguji. Bisa juga yang lain seperti >, <, >= dan sebagainya.
kondisi: ketentuan yang menjadi patokan untuk dipenuhi.
terpnuhi: hasil (pernyataan) jika kondisi terpenuhi.
tdk_trpenuhi: hasil (pernyataan jika tidak terpenuhi, tidak harus ada. Ini tidak harus ada, bisa dikosongkan. Di bagian ini juga Anda bisa membuat perbandingan baru.

Contoh kasus: Gunakan fungsi IF RIGHT untuk menentukan Kota dari sel Kode, 3 karakter diambil dari kanan. Misal: JKT berarti Jakarta.

Contoh data IF RIGHT.

Rumus:

1. Versi sederhana: klik sel B2 dan ketik rumus: =IF(RIGHT(A2;3)="JKT";"Jakarta") kemudian Enter. Hasilnya Jakarta. Sekilas rumus ini terlihat sudah benar, tetapi tidak efektif sebab kode 3 karakter (seperti JKT, MDN) tidak sama. Artinya, di setiap baris harus ketik rumus lagi. Misalnya baris kedua: =IF(RIGHT(A2;3)="BDG";"Bandung")

2. Solusinya adalah dengan menguji Kode sekaligus (disebut juga rumus multi IF). Rumusnya berubah menjadi: =IF(RIGHT(A2;3)="JKT";"Jakarta";IF(RIGHT(A2;3)="BDG";"Bandung";IF(RIGHT(A2;3)="MDN";"Medan";IF(RIGHT(A2;3)="JYP";"Jayapura";"Makassar"))))

Tata cara penulisan rumus: Klik sel B2 dan ketik =IF(RIGHT( kemudian klik sel A2, titik koma, ketik 3, titik koma, ketik 3, tutup kurung. Beri sama dengan (=), nyatakan kondisi ("JKT"), titik koma, nyatakan hasil/pernyataan ("Jakarta"). Setelah itu titik koma, ketik IF, buka kurung...dan selanjutnya. Lanjutkan pengujian Kode berikutnya seperti rumus kedua.

Jika Kode ada 5 buah (seperti soal di atas; JKT, MDN dan lainnya) maka fungsi IF cukup 4, karena jika bukan yang 4 pasti yang kelima. Langsung beri hasil, "Makassar", perhatikan lagi rumus kedua.

Untuk data berikutnya, Anda tidak perlu lagi mengetik ulang rumus, tetapi cukup memanfaatkan Copy Cells.., caranya:

- Arahkan kursor ke pinggir kanan bawah sel B2, sehingga kursor berubah menjadi + (plus hitam).

Plus hitam.

- Klik kiri tanda plus dan tahan kemudian geser sampai ke sel B6.

Klik-tahan-geser.

-Hasil akhir:

Hasil akhir.

- Silahkan dicoba.

Fungsi IF MID Ms Excel

Fungsi IF digunakan untuk menguji sel di Microsof Excel dengan suatu kondisi apakah terpenuhi atau tidak. Fungsi MID digunakan untuk mengambil karakter dari sel yang berada di antara kiri dan kanan karakter pada suatu sel. Jadi, fungsi IF MID berarti menguji kondisi apakah terpenuhi atau tidak dari hasil fungsi MID.

Penulisan rumus: =IF(MID(sel_kode; awal_char; jml_char)=kondisi; terpnuhi; tdk_trpenuhi)

sel_kode: sel yang akan diuji dan diambil beberapa karakter darinya dengan fungsi MID.
awal_char: tentukan dimulai karakter ke berapa dari kiri diambil dari sel kode.
jml_char: berapa jumlah karakter yang diambil dari sel kode.
= (sama dengan): sebagai operator pembanding. Bisa juga yang lain seperti >, <, >= dan sebagainya.
kondisi: ketentuan yang menjadi patokan untuk dipenuhi.
terpnuhi: hasil (pernyataan) jika kondisi terpenuhi.
tdk_trpenuhi: hasil (pernyataan jika tidak terpenuhi. Ini tidak harus ada, bisa dikosongkan. Di bagian ini juga Anda bisa membuat perbandingan baru.

Contoh kasus: Tentukan nama provider dari Kode yang diambil dari 2 karakter, dimulai dari karakter ke-3.

Data untuk fungsi IF MID.

Untuk fungsi MID: Diambil 2 karakter dimulai dari karakter ke-3 dari kiri. Maka fungsinya adalah: 3;2. Maka rumusnya:

1. Versi sederhana: klik sel B2 dan ketik rumus: =IF(MID(A2;3;2)="TS";"TELKOMSEL") kemudian Enter. Hasilnya TELKOMSEL. Rumus ini sudah berfungsi tetapi tidak efektif, karena Kode berbeda-beda. Seperti TS, XL, MT dan AX dari hasil seleksi fungsi MID.

2. Rumus yang disarankan: Menguji semua kondisi Kode sekaligus. Rumusnya berubah menjadi: =IF(MID(A2;3;2)="TS";"TELKOMSEL";IF(MID(A2;3;2)="XL";"XL";IF(MID(A2;3;2)="MT";"MENTARI";"AXIS")))

Tata cara penulisan rumus: Klik sel B2 dan ketik =IF(MID( kemudian klik sel A2, titik koma, ketik 3, titik koma, ketik 2, tutup kurung. Beri sama dengan (=), nyatakan kondisi ("TS"), titik koma, nyatakan hasil/pernyataan ("TELKOMSEL"). Karena ada 4 macam kode (TS, XL, MT dan AX) maka uji rumus kembali sampai MT.

Logikanya, jika ketiga kode (TS, XL dan MT) tidak pernuhi (dalam pengujian) maka tentu AX yang dieksekusi Microsoft Excel. Maka, tak perlu diuji lagi, langsung saja beri pernyataan/hasil "AXIS".

Di rumus yang disarankan ini terdapat 3 penutup kurung, itu karena tiga IF yang digunakan.

Screen shot pengetikan rumus:

Penulisan rumus, Enter.

Untuk data berikutnya, Anda tidak perlu lagi mengetik ulang rumus, tetapi cukup memanfaatkan Copy Cells.., caranya:

Arahkan kursor ke pinggir kanan bawah sel B2, sehingga kursor berubah menjadi + (plus hitam).

Plus hitam.

Kemudian klik kiri dan tahan dan geser sampai ke sel B5.

Geser sampai sel B5.

Hasil akhir:

Hasil akhir.

Selamat mencoba!

Fungsi IF LEFT Ms Excel

Fungsi IF digunakan untuk menguji suatu kondisi apakah terpenuhi atau tidak, sedangkan fungsi LEFT digunakan untuk mengambil satu atau beberapa karakter dari kiri. Jadi fungsi IF LEFT adalah menguji kondisi dari hasil fungsi LEFT dengan fungsi IF dan menentukan pernyataan (hasil/tindakan) yang diinginkan.

Format penulisan rumus: =IF(LEFT(sel_kode; jml_char)=kondisi; terpenuhi; tdk_terpenuhi)

sel_kode: letak sel kode yang menjadi patokan untuk fungsi IF.
jml_char: jumlah karakter yang diambil dari kiri dari sel kode.
= (sama dengan): sebagai operator pembanding. Bisa juga yang lain, misal <, >, !=, <= dan >=
kondisi: yang menjadi patokan untuk diuji apakah terpenuhi atau tidak.
terpenuhi: hasil atau pernyataan jika kondisi terpenuhi.
tdk_terpenuhi: hasil atau pernyataaan jika kondisi tidak terpenuhi. Kondisi ini bersifat opsional tidak harus ada.

Catatan: Jika kondisi dan hasil yang diinginkan berupa huruf saja atau gabungan huruf dan angka (string) maka diapit tanda kutip dua, jika angka saja tidak perlu.

Contoh kasus: Tentukan ukuran pakaian dan harga dari data berikut:

Contoh data untuk fungsi IF LEFT.

Perhatikan "Ketentuan", ada yang diambil satu karakter dan ada yang dua dari kiri pada sel Kode. Maka fungsi LEFT disesuaikan saja. Rumusnya adalah:

1. Untuk Ukuran pertama, klik sel B2 dan ketik rumus: =IF(LEFT(A2;1)="S";"Kecil") kemudian Enter.

Proses pengetikan rumus: klik sel B2 dan ketik =IF(LEFT( kemudian klik sel A2, beri titik koma, tentukan berapa karakter yang diambil dari kiri (ketik 1), beri tutup kurung. Selanjutnya beri tanda pembanding (=), tentukan kondisi ("S"), beri titik koma, tentukan hasil/pernyataan yang diinginkan ("Kecil").

2. Ukuran kedua, klik sel B3 dan ketik rumus: =IF(LEFT(A3;2)="XL";"Extra Large")
3. Ukuran ketiga, klik sel B4 dan ketik rumus: =IF(LEFT(A4;1)="L";"Besar")
4. Ukuran keempat, klik sel B5 dan ketik rumus: =IF(LEFT(A5;1)="M";"Sedang")

Konsep yang sama berlaku untuk menentukan harga, sehingga rumusnya:

1. Harga pertama, klik sel C2 dan ketik rumus: =IF(LEFT(A2;1)="S";150000) kemudian Enter.
2. Harga kedua, klik sel C3 dan ketik rumus: =IF(LEFT(A3;2)="XL";50000)
3. Harga ketiga, klik sel C4 dan ketik rumus: =IF(LEFT(A4;1)="L";200000)
4. Harga keempat, klik sel C5 dan ketik rumus: =IF(LEFT(A5;1)="M";70000)

Contoh pengetikan rumus:

Pengetikan rumus.

Terakhir, blok sel C2 sampai C5 dan beri format rupiah seperti gambar berikut:

Format rupiah.

Jika perlu, hilangkan 2 angka di belakang koma dengan Decrease Decimal.

Decrease Decimal.

Selesai.

Fungsi VLOOKUP VALUE Ms Excel

Pada contoh sebelumnya, penulis menjelaskan tentang HLOOKUP VALUE dengan mencontohkan data pemain Arsenal untuk menentukan nama pemain dan posisi berdasarkan nomor punggung dari tabel bantu yang datanya berorientasi ke kanan (horizontal). VLOOKUP VALUE sama konsepnya dengan HLOOKUP VALUE, bedanya kalau VLOOKUP menggunakan tabel bantu dengan data orientasi vertikal.

Fungsi VALUE digunakan jika kode di tabel bantu berupa angka murni. Misalnya, 1,2,3 atau lainnya. Jika datanya 1A, 2b, 7d32s atau lainnya maka itu dinamakan string, maka tak perlu menggunakan VALUE. Dalam contoh ini kita menggunakan VLOOKUP VALUE LEFT.

Format penulisan rumus: =VLOOKUP(VALUE(LEFT(sel_kode; jml_char)); seleksi_tbl_bntu; klom_data)

sel_kode: letak sel kode yang menentukan data lain, misalnya data bulan berdasarkan angka. 1 menandakan Januari, 2 menandakan Februari dan seterusnya.
jml_char: jumlah karakter yang diambil dari sel kode, ini berkaitan dengan fungsi LEFT.
seleksi_tbl_bntu: seleksi tabel bantu (seleksi) untuk mendapatkan data yang diinginkan.
klom_data: di kolom berada data yang diinginkan berada, misalnya data bulan dan target.

Contoh kasus: Tentukan bulan dan target dari data berikut:

Contoh data untuk VLOOKUP VALUE LEFT.

Perhatikan Tabel bantu, yang menjadikan patokan adalah Kode berupa angka 1 digit. Berarti fungsi LEFT-nya adalah 1, ambil satu karakter dari kiri. Maka rumusnya adalah:

1. Bulan pertama, klik sel B2 dan ketik rumus: =VLOOKUP(VALUE(LEFT(A2;1));A9:C12;2;FALSE) kemudian Enter.

Tata cara penulisan rumus: Klik sel B2 dan ketik =VLOOKUP(VALUE(LEFT( kemudian klik sel A2, beri titik koma, ketik 1, tutup kurung dua kali. Beri titik koma lagi, sorot (seleksi) sel A9 sampai C12, titik koma, ketik 2, ketik FALSE, beri tutup kurung, Enter.

Penjelasan: A2;1 artinya ambil 1 karakter dari sel A2 (sebelah kiri). Tutup kurung  dua kali )), satu untuk fungsi LEFT dan satu lagi untuk VALUE. Angka 2 setelah A9:C12 artinya mengambil data Bulan di tabel bantu yang terletak di kolom 2 (dari kiri). FALSE artinya memastikan Kode di soal sama dengan Kode di Tabel bantu.

2. Bulan kedua, klik sel B3 dan ketik rumus: =VLOOKUP(VALUE(LEFT(A3;1));A9:C12;2;FALSE)
3. Bulan ketiga, klik sel B4 dan ketik rumus: =VLOOKUP(VALUE(LEFT(A4;1));A9:C12;2;FALSE)
4. Bulan keempat, klik sel B5 dan ketik rumus: =VLOOKUP(VALUE(LEFT(A5;1));A9:C12;2;FALSE)

Untuk mencari Target tinggal mengganti anka 2 dengan angka 3 setelah A9:C12, karena data Target ada di kolom ke-3 di Tabel bantu. Jika Anda sudah paham dengan penulisan rumus, tidak perlu mengetik rumus di setiap baris, tetapi dengan menerapkan Copy cells. Berikut contohnya:

Untuk Target pertama, klik sel C2 ketik =VLOOKUP(VALUE(LEFT( kemudian klik sel A2, beri titik koma, ketik 1, tutup kurung 2 kali. Beri titik koma, sorot sel A9:C12, tekan F4 di keyboard, beri titik koma, ketik 3, ketik FALSE, beri tutup kurung, kemudian Enter.

Screenshot:

Penulisan rumus.

Untuk data Target berikutnya Anda tidak perlu mengetik rumus lagi, arahkan kursor ke bagian kanan bawah sel C2 sampai muncul tanda + kemudian klik kiri dan tahan, kemudian geser sampai sel C5.

Screenshot:

Muncul tanda +

Hasil akhir.

Selamat mencoba!

Fungsi HLOOKUP VALUE Ms Excel

HLOOKUP pada Microsot Excel digunakan untuk mendapatkan data dari tabel bantu yang sifatnya horizontal (ke kanan) dengan paramater kode unik (id). VALUE digunakan untuk mengkonversi sebuah karakter dalam data string ke dalam bentuk angka. Untuk contoh, kita gunakan HLOOKUP VALUE RIGHT.

Format penulisan rumus: =HLOOKUP(VALUE(RIGHT(sel_kode; jml_char)); seleksi_tbl_bantu; brs_data)

sel_kode: letak sel kode unik (id) yang menentukan data setelahnya. Misalnya, NIP menentukan nama pegawai. Contoh lain dalam sepakbola, nomor punggung 1 biasa dipakai kiper, nomor 7 sering dipakai gelandang serang.
jml_char: berapa karakter yang diambil dari kanan (ke kiri).
seleksi_tbl_bantu: Seleksi tabel bantu untuk mendapatkan data yang diinginkan.
brs_data: Di baris ke berapa data yang diinginkan berada.

Contoh kasus: Tentukan nama pemain dan posisi berdasarkan nomor punggung sebagai berikut:

Contoh data untuk HLOOKUP VALUE RIGHT.

Anda perhatikan Tabel bantu, yang menjadi parameter nama dan posisi adalah satu karakter angka berupa nomor punggung yang dianggap sebagai Kode. Untuk mencari Nama Pemain dan Posisi di soal tidak cukup dengan HLOOKUP RIGHT, tetapi perlu fungsi VALUE untuk mengkonversi angka 1 atau lainnya di karakter Arsenal-1 atau lainnya ke dalam bentuk angka.

Jika angka 1 atau lainnya diganti dengan karakter string maka tak perlu penggunaan fungsi VALUE. Karakter string artinya gabungan huruf dan angka. Angka 1 atau lainnya (setelah Arsenal- di soal) bukan string tetapi murni angka, maka perlu disertakan fungsi VALUE. Angka 1 atau lainnya didapat dengan fungsi RIGHT.

Maka rumusnya adalah:

1. Nama pemain pertama, klik sel B2 (di soal) dan ketik rumus: =HLOOKUP(VALUE(RIGHT(A2;1));B8:E10;2;FALSE) kemudian Enter.

Tata cara pengetikan rumus: Klik sel B2 dan ketik =HLOOKUP(VALUE(RIGHT( kemudian klik sel A2, beri titik koma, ketik 1, beri tutup kurang dua kali. Beri titik koma, sorot (seleksi) Tabel bantu mulai dari sel B8 sampai E10, beri titik koma, ketik 2, beri tutup kurung.

Penjelasan rumus: Tutup kurung dua kali; satu penutup untuk VALUE dan satu lagi untuk RIGHT. Angka 1 pada A2;1 berarti mengambil 1 karakter dari kanan. Angka 2 pada B8:E10;2 artinya mengambil data di baris ke-2 di Tabel bantu. FALSE untuk memastikan karakter Kode di soal yang diambil sama persis dengan di tabel bantu.

2. Nama pemain kedua, klik sel B3 dan ketik: =HLOOKUP(VALUE(RIGHT(A3;1));B8:E10;2;FALSE)
2. Nama pemain ketiga, klik sel B4 dan ketik: =HLOOKUP(VALUE(RIGHT(A4;1));B8:E10;2;FALSE)
2. Nama pemain keempat, klik sel B5 dan ketik: =HLOOKUP(VALUE(RIGHT(A5;1));B8:E10;2;FALSE)

Untuk mencari Posisi tinggal merubah angka 2 dengan angka 3 setelah menyorot tabel Tabel bantu, karena data posisi di Tabel bantu ada di baris ke-3. Maka rumusnya adalah:

1. Posisi pemain pertama, klik sel C2 dan ketik rumus: =HLOOKUP(VALUE(RIGHT(A2;1));B8:E10;3;FALSE)
2. Posisi pemain kedua, klik sel C3 dan ketik rumus: =HLOOKUP(VALUE(RIGHT(A3;1));B8:E10;3;FALSE)
3. Posisi pemain ketiga, klik sel C4 dan ketik rumus: =HLOOKUP(VALUE(RIGHT(A4;1));B8:E10;3;FALSE)
4. Posisi pemain keempat, klik sel C5 dan ketik rumus: =HLOOKUP(VALUE(RIGHT(A5;1));B8:E10;3;FALSE)

Contoh penulisan rumus Posisi pemain pertama:

Contoh penulisan rumus.

Selamat mencoba!