Rumus VLOOKUP dan HLOOKUP

Fungsi HLOOKUP dan VLOOKUP digunakan untuk membaca suatu table secara horizontal (VLOOKUP) atau secara vertical (VLOOKUP). Bentuk umum penulisan fungsi ini adalah:

=HLOOKUP(Lookup_value, Table_array, Row_index_num,….) 

=VLOOKUP(Lookup_value, Table_array, Col_Index_num,….) 

Fungsi HLOOKUP dan VLOOKUP Dari rumus diatas, dapat dilihat bahwa bedanya hanya pada nomor indeksnya saja, kalau kita pakai HLOOKUP, maka digunakan nomor indeks baris (Row_index_num),
tapi kalau pakai VLOOKUP digunakan nomor indeks kolom (Col_index_num).

Nomor indeks adalah angka untuk menyatakan posisi suatu kolom/baris dalam table yang dimulai dengan nomor 1 untuk kolom/baris pertama dalam range data tersebut. Secara umum bentuk yang dipakai adalah tabel vertikal 


Perhatikan contoh tabel berikut:

 

Bagian yang akan dijadikan table_array adalah Range B4 s.d E8 Langkah yang harus dilakukan adalah:

• Block dari B4 s.d E8
• Pada tab Formulas pilih Define Name

• 

• Pada kolom Name: isikan nama tabel misalnya datanama sementara bagian yang lain biarkan saja karena sudah menyesuaikan dengan hal yang sudah kita lakukan sebelumnya 
• Maka, Range kolom pertama (paling kiri) akan menjadi data yang disesuaikan pada Lookup_value, sedangkan yang digunakan sebagai Col_Index_num adalah range kolom ke 2, 3 dan 4. 

Contoh penggunaan VLOOKUP:

=VLOOKUP(B11;datanama;3;0) 

Jika data pada kolom B11 adalah Bravo maka fungsi ini akan memunculkan data pada kolom ke 3 pada baris data Bravo yaitu 70, Jika B11 adalah Echo maka nilai yang muncul 75

Memisahkan Tanggal Bulan dan Tahun

Memisahkan Tanggal Bulan dan Tahun

Cara memisahkan Tanggal, Bulan dan Tahun dari suatu Format data tanggal ada beberapa macam bergantung dari jenis data tersebut. Tujuan yang dilakukan paling banyak adalah untuk mendapatkan kelompok data, misalnya pengelompokan Tahun kelahiran untuk mendapatkan data statistik kelompok usia. Atau pengelompokan bulan untuk persiapan pengiriman ucapan selamat pada hari kelahiran.

Untuk mendapatkan data tersebut perlu kita ketahui terlebih dahulu Format Teks yang digunakan pada Data Tanggal tersebut. Terdapat 2 macam:

Data Teks berupa data Data Teks Format Tanggal

Dengan kondisi ini akan jauh lebih mudah untuk mendapatkan atau memisahkan bagian-bagian Format Tanggal ini yaitu dengan cara:

• DAY: dengan Rumus Excel ini kita bisa mendapatkan data Tanggal saja dari Data Tanggal Lengkap C2=DAY(B2)
• MONTH: dengan Rumus Excel ini kita bisa mendapatkan data Bulan saja dari Data Tanggal Lengkap, namun berupa angka.D2=MONTH(B2)
• YEAR: dengan Rumus Excel ini kita bisa mendapatkan data Tahun saja dari Data Tanggal Lengkap. E2=YEAR(B2)

Jikalau data bulan yang kita inginkan bukan angka, namun kita ingin bentuk teks bulan maka harus digunakan cara manual yaitu gunakan Rumus Excel sebagai berikut:

F2=CHOOSE(MONTH(data_tanggal);
"Januari";"Februari";"Maret";"April";"Mei";"Juni";
"Juli";"Agustus";"September";"Oktober";
"Nopember";"Desember")

Data Teks berupa data Data Teks Biasa

Jika kondisinya demikian, misalnya karena kita mengambilnya atau memisahkan dari data Tempat & Tanggal Lahir. Maka hal pertama yang harus dilakukan adalah dengan cara merubah data tersebut ke dalam Format Tanggal.

Caranya adalah dengan teknik berikut, bisa dipilih sesuai kebutuhan:

• =TEXT((data_tanggal);"dd mmmm yyyy") ====> Hasilnya: 01 April 2015
• =TEXT((data_tanggal);"dd mm yyyy") ====> Hasilnya: 01 04 2015
• =TEXT((data_tanggal);"dd-mm-yyyy") ====> Hasilnya: 01-04-2015
• =TEXT((data_tanggal);"dd mm yy") ====> Hasilnya: 01 04 15

Format Tanggal bisa dilakukan sesuai keinginan.

Memisahkan data Tempat dan Tanggal Lahir

Mangambil Karakter dari Data Entri

Fungsi yang digunakan dalam hal ini adalah fungsi Text yang terdiri dari:

• LEFT
• RIGHT
• MID
• LEN
• FIND

Dengan metode ini kita bisa mengambil beberapa karakter tertentu dari suatu teks yang terdapat pada suatu kolom. Contohnya misal untuk mengambil kode Tahun pada suatu format tempat dan tanggal lahir (kalau format tanggal lahir saja cukup menggunakan fungsi YEAR saja)

Perhatikan tabel berikut:

Untuk mengambil tempat kelahiran ada beberapa hal yang harus kita perhatikan di sini, yaitu:

1. Posisi pada Teks, yaitu sebelah kiri. Maka menggunakan fungsi LEFT
2. Panjang Tempat Lahir tidaklah sama untuk masing-masing kolom. Maka langkah yang harus dilakukan adalah menghitung panjang karakter yang dimaksud tersebut. Rumus di sini membutuhkan teknik bermain dengan hitungan angka.

pada kolom B2 tersebut menggunakan Rumus Excel sebagai berikut:

=LEFT(A2;FIND(",";A2;1)-1)

Mengapa demikian?
Bagian yang berwarna biru bisa dinyatakan cukup jelas, yaitu fungsi LEFT untuk mengambil data dari kolom A2, sementara jumlah karakternya ditentukan pada bagian yang berwarna merah tersebut.

FIND: Berfungsi untuk menemukan karakter spesial dari data yang dimaksud, yang merupakan pemisah antara data "Tempat Lahir" serta data "Tanggal Lahir"

• Disini kita mengambil karakter "," sebagai pemisahnya dan tanda tersebut memang berulang dilakukan.
• Posisi karakter "," tepat berada didepan Tempat Lahir yang berarti 1 karakter di depan Tanggal Lahir. 
• Maka panjang karakter Kota Kelahiran pastilah 1 karakter di belakang tanda "," yaitu -1

Karena Fungsi Rumus Excel FIND menghasilkan Angka maka dengan mudah kita bisa memakainya sebagai dasar FIND(",";A2;1) lalu ditambahkan dengan -1

Dengan cara ini panjang teks dari kiri yang dimunculkan akan berbeda-beda sesuai panjang karakter yang kita inginkan.

MENGAMBIL DATA TANGGAL KELAHIRAN

Karena panjangnya text masing-masing kolom berbeda maka kita bisa menggunakan Rumus Excel sebagai berikut:

=MID(A2;(FIND(",";A2;1)+2);LEN(A2)-(FIND(",";A2;1)+1))

atau

=RIGHT(A2;LEN(A2)-(FIND(",";A2;1)+1))

hasilnya pasti sama, hanya saja metode yang digunakan hampir sama:

Menggunakan Fungsi MID atau Middle

=MID(A2;(FIND(",";A2;1)+2);LEN(A2)-(FIND(",";A2;1)+1))

1. Bentuk Rumus Excel nya sebenarnya seperti ini: =MID(A2;[teks_dimulai_dari_huruf_ke?];[panjang_teks])  
2. Karena bentuknya adalah "Kota, 00-Month-YEAR" maka yang harus dicari adalah posisi teks tanggal dimulai yaitu dengan (FIND(",";A2;1)+2) dikarenakan posisinya adalah 2 karakter setelah tanda ","
3. [panjang_teks], namun bukan panjang teks total, namun teks yang diambil. Menggunakan Rumus Excel sebagai berikut: LEN(A2)-(FIND(",";A2;1)+1) 
4. LEN(A2) merupakan panjang total dari teks pada kolom A2
5. dan supaya panjangnya sesuai dengan data tanggal yang dimaksud maka jumlahnya ditentukan dengan cara menemukan posisi karakter yang berada di depan tanggal. Bisa diwakilkan dengan menemukan tanda "," yaitu (FIND(",";A2;1)+1)

Menggunakan Fungsi RIGHT

=RIGHT(A2;LEN(A2)-(FIND(",";A2;1)+1))

1. Cara ini lebih pendek daripada fungsi MID. Fungsi RIGHT bisa digunakan karena posisinya memang sebelah kanan dari teks pada kolom
2. Yang harus dilakukan adalah menentukan jumlah teks yang diambil. Menggunakan Rumus Excel sebagai berikut: LEN(A2)-(FIND(",";A2;1)+1) 
3. LEN(A2) merupakan panjang total dari teks pada kolom A2
4. dan supaya panjangnya sesuai dengan data tanggal yang dimaksud maka jumlahnya ditentukan dengan cara menemukan posisi karakter yang berada di depan tanggal. Bisa diwakilkan dengan menemukan tanda "," yaitu (FIND(",";A2;1)+1)

Fungsi Count pada Excel

Salah satu rumus yang paling banyak dicari adalah rumus untuk menghitung jumlah Data yang sudah dimasukkan. Bukan menghitung jumlah nilainya akan tetapi data Entri yang sudah dimasukkan, baik berupa angka atau karakter bebas.

Fungsi COUNT pada Excel

Count memang fungsi yang secara khusus digunakan untuk menghirung jumlah data Entry yang berupa Angka. perhatikan data di tabel berikut:

 

Dikarenakan Range (K4:K23) merupakan data Angka maka rumus yang dipakai adalah:

=COUNT(K4:K23)

Apabila yang digunakan adalah range (M4:M23) yang mana isinya adalah bukan angka, maka rumus COUNT berubah menjadi COUNTA

=COUNTA(M4:M23)  

Fungsi COUNTIF pada Excel

Sedangkan apabila kita menghitung Jumlah Data Entri tertentu dari sekian banyak Entri yang dimasukkan maka kita bisa menggunakan fungsi COUNTIF alias "Hitung Jika"

Contoh dari tabel di atas adalah cara menghitung jumlah peserta Laki-laki dan Jumlah peserta Perempuan.

=COUNTIF(M4:M23;"L")

=COUNTIF(M4:M23;"P")  

Fungsi IF pada Excel

Fungsi Rumus Logika IF pada Microsoft Excel

IF merupakan salah satu Rumus Fungsi Logika pada Microsoft Excel. Dari namanya saja sudah bisa kita pahami bahwa IF adalah bentuk bersyarat "JIKA"

Bentuk Rumusnya sebenarnya sangatlah mudah:

=IF(logical_test;[value_if_true];[value_if_false])

Keterangan:
logical_test: pada bagian ini kita masukkan bentuk logika yang dicoba untuk digunakan
Contoh: Apakah 100 lebih besar daripada 500? Jawabannya pasti Salah
=IF(100>500;"Benar";"Salah")

value_if_true: merupakan tempat memasukkan fungsi ataupun argumen yang akan dimunculkan / dijalankan jika logical_test memiliki hasil yang bernilai "true" atau benar

value_if_false: merupakan tempat memasukkan fungsi ataupun argumen yang akan dimunculkan / dijalankan jika logical_test memiliki hasil yang bernilai "false" atau salah

Perhatikan Contoh berikut:

 

Perhatikan Contoh berikut: 

FUNGSI IF BERTINGKAT

Fungsi IF Bertingkat bagi para excel-mania bukanlah hal yang luar biasa, namun bagi para Newbie Excel mungkin cukup bikin sakit kepada. So, mari kita sharing aja teknik dasarnya untuk mempermudah.

Perhatikan contoh berikut:

 

Yang dibentuk pada Rumus tersebut adalah:

 =IF(B3>=70;"Lulus";IF(B3>=30;"Mengulang";"GAGAL"))

Terdapat 3 argumen yang berbeda yaitu: LULUS, MENGULANG dan GAGAL. Jumlah atau banyaknya fungsi IF yang ada adalah Jumlah Argumen dikurangi 1.

Jika terdapat 3 argumen maka jumlah fungsinya adalah 3 - 1 = 2 fungsi

Bentuk tersebut terdiri dari 2 kelompok Fungsi IF yaitu:

Fungsi_ke_1=IF(B3>=70;"Lulus";[Fungsi_ke_2])

Fungsi_ke_2=IF(B3>=30;"Mengulang";"GAGAL"))

Jika digabungkan maka tekniknya seperti berikut:

=IF(B3>=70;"Lulus";IF(B3>=30;"Mengulang";"GAGAL")) 

Simpel sekali kan?! Jadi dari pada pusing mikirin fungsi bertingkatnya secara langsung, mending pecah-pecah saja dulu rumusnya di kolom-kolom yang berbeda kemudian jika sudah sesuai dengan keinginan maka segera disatukan. 

Rumus Fungsi Excel

Fungsi SUM pada Microsoft Excel

Bagian yang paling mendasar dalam menggunakan Microsoft Excel ialah mengolah database yang kebanyakan terbentuk dalam format angka. Kali ini yang kita bahasa adalah salah satu fungsi Microsoft Excel dalam menjumlahkan data ( Data Summary )

SUM yang berasal dari kata Summary atu jumlah merupakan rumus yang digunakan untuk menjumlahkan (penambahan dan penambahan) data dari range-range yang dipilih. Tidak harus dalam satu kolom ataupun dalam satu baris, namun boleh saja dari range lainnya dalam satu sheet yang sama, beda sheet ataupun beda workbook ( beda file ). perhatikan contoh berikut:

 

Pada tabel tersebut di atas fungsi SUM digunakan untuk menjumlahkan beberapa Range yang dipilih, mulai dari Range Biru (C5:C11) berbentuk kolom, Range Hijau (E5:J5) berbentuk baris, serta Range Oranye (J10:K11) yang menyatukan bentuk kolom dan baris secara bersamaan. 

=SUM (C5:C11;E5:J5;J10:K11)

Teknik tersebut di atas bisa juga digunakan untuk menentukan hal-hal berikut:

• AVERAGE: Mencari Nilai Rata-Rata
• MIN: Mencari Nilai Terendah
• MAX: Mencari Nilai Tertinggi

Perhatikan Contoh Berikut:

 

Salah satu teknik untuk menjumlahkan dengan cara cepat khusus untuk Range dalam bentuk kolom adalah dengan cara menekan ALT + ENTER namun terbatas pada range diatasnya saja, dan jika terdapat kolom yang kosong maka range hanya sampai pada range sebelumnya saja.

Tabel Matematika pada Excel

Membuat Tabel Matematika pada Microsoft Excel 

( bisa dipakai di berbagai versi berapapun )

Tabel Penjumlahan dan lainnya seperti yang ada di gambar bawah ini biasanya dipakai oleh adik-adik SD. Pembuatannya sebenarnya tidaklah sulit, hanya saja perlu strategi untuk bisa menghasilkan rumus yang tepat pada tiap kolom-kolomnya. 

Rumus Absolute

Rumus cuma perlu dibuat satu kali saja lalu diduplikasikan pada kolom yang lain. Dalam hal ini kita perlu menggunakan teknik dari Rumus Absolute yang mana fungsi utamanya adalah mengunci bagian rumus supaya pada saat diduplikasi / di-copy tidak akan berubah.

Tanda "$" merupakan bagian dari Rumus Absolute, diletakkan didepan nomor kolom (misal $A1) atau nomor baris (misal A$1) atau keduanya (misal $A$1)

Contoh:

Tabel Penjumlahan 1 s.d 10

Rumus yang digunakan pada kolom B2 adalah =B$1+$A2

Tabel Pengurangan 1 s.d 10

Rumus yang digunakan pada kolom B2 adalah =B$1-$A2

Apabila pada kolom B2 menggunakan =$A2-B$1, maka hasilnya antara posisi minus dan plus akan bertukar posisi.

Tabel Perkalian 1 s.d 10

Rumus yang digunakan pada kolom B2 adalah =B$1*$A2

Tabel Pembagian 1 s.d 10

Rumus yang digunakan pada kolom B2 adalah =B$1/$A2

Apabila pada kolom B2 menggunakan =$A2/B$1, maka hasilnya akan bertukar posisi seperti pada tabel pengurangan. Area bayang-bayang yang sengaja dikosongkan karena angka berapapun jika dibagi dengan angka 0 maka nilainya tak terdefinisikan oleh Microsoft Excel.

Rumus Excel Penjumlahan

Salah satu bentuk dari Rumus Excel yang paling banyak digunakan adalah Penjumlahan, Pengurangan, Perkalian dan Pembagian, yang menjadi fungsi matematika dalam Microsoft Excel.

Operator Aritmatika

Operator Aritmatika dipakai untuk penghitungan dasar-dasar matematika, misalnya penjumlahan, pengurangan, perkalian, pembagian dan lain lain.

Macam macam operator aritmatika:

Operator	Keterangan	Contoh
+	Penambahan	4 + 3 = 7
-	Pengurangan / Minus	4 - 3 = 1
*	Perkalian	4 * 3 = 12
/	Pembagian	4 / 3 = 1,3
^	Pangkat	4^3 = 64
%	Prosentase	4% = 0,04

Kadang seolah tidak mudah menemukan rumus yang tepat yang mana sesuai dengan yang kita inginkan, karena Aplikasi ini memiliki urutan cara perhitungan tersendiri yang biasa disebut hierarki. Namun daripada pusing menghafalkan hierarki sebaiknya kita atur dan kelompokkan saja tahap perhitungan atau rumus yang kita gunakan.

 

Perhatikan contoh Rumus Matematika berikut:

Contoh-1:

Rumus Matematika pada Microsoft Excel adalah sebagai berikut:
misalnya:
a = A1
b = B1
c = C1
d = D1
e = E1
maka, A letakkanlah di kolom selain tersebut di atas misalnya kolom F1, lalu ketiklah perintah sebagai berikut:

=((A1+B1)^2)-((C1*D1)/E1)

Rasanya kalau dilihat seolah panjang sekali dan rumit, benarkah? tidak sepenuhnya, ada triknya untuk memudahkan kita mengerjakan suatu rumus. Langkah mudah dari membuat rumus excel perhitungan di atas adalah seperti berikut:

Yang pasti didahului oleh tanda "sama dengan"	=
Buatlah kurung untuk kuadrat ( a + b ) dan kurunglah juga polanya supaya aman	(()^2) ((A1+B1)^2)
Lanjutkan dengan tanda "minus" dan pola pembagiannya	-() -(/) -(()/E1) -((C1*D1)/E1)

Contoh-2:

Rumus Matematika pada Microsoft Excel adalah sebagai berikut:
misalnya:
a = A1
b = B1
c = C1
maka, x letakkanlah di kolom selain tersebut di atas misalnya kolom D1, lalu ketiklah perintah sebagai berikut:

=(-B1±(((B1^2)-(4*A1*C1))^(1/2))/(2*A1)

Tanda "±" tidak ada dalam perhitungan rumus, gunakan sesuai kebutuhan yaitu pilih "+" atau "-"

Rasanya rumit juga ya? Sama saja seperti teknik yang di atas. Langkah mudah dari membuat rumus excel perhitungan di atas adalah seperti berikut:

Yang pasti didahului oleh tanda "sama dengan"	=
Pastikan pola dasar rumusnya, cari bentuk yang paling besar dan utama. Pembagian dengan 2a	()/() ()/(2*A1)
Masukkan bentuk rumus -b±	(-B1±())/(2*A1)
Masukkan bentuk rumus akar pangkat	(-B1±(()^(1/2))/(2*A1)
Masukkan bentuk rumus b kuadrat dan -4ac	(-B1±(((B1^2) -(4*A1*C1))^(1/2))/(2*A1)

Masih banyak bentuk perhitungan yang tidak kalah rumitnya, yang bisa saja kita bahas bersama disini. So, jangan lupa tinggalkan komentar yach...