Tutorial Menggunakan HLOOKUP di Excel 2010 atau 2013

5th Mei 2015

Tutorial terdahulu membahas mengenai VLOOKUP. Definisi dan cara menggunakan VLOOKUP bisa dibaca pada link artikel dibawah ini

Tutorial Menggunakan VLOOKUP di Excel

HLOOKUP merupakan fungsi dengan cara kerja yang mirip dengan VLOOKUP, hanya saja proses pencarian nilainya dilakukan secara horisontal, bukan vertikal.

Sintaks untuk HLOOKUP adalah

HLOOKUP(lookup_value, table_array, index_num, [range_lookup])

lookup_value adalah nilai yang dicari

tabel_array adalah tabel yang di dalamnya kita mencari nilai lookup_valueNilai yang dicari ini harus ada dalam baris pertama di table_array

index_num adalah baris keberapa yang nilainya akan di print/dikembalikan ke cell. Nomor baris dihitung dari baris pertama di tabel_array, bukan baris dari filenya. Misalnya baris pertama di table_array dimulai dari baris ke 2, maka baris ketiga memiliki nilai index_num 2.

[range_lookup] adalah input berupa logika TRUE atau FALSE. Input ini bersifat opsional. Jika diisi dengan TRUE, maka fungsi ini akan mencari nilai yang sama persis dengan yang dicari. Jika tidak ada, maka akan dicari nilai yang paling mendekati dengan nilai yang dicari. Jika diisi dengan logika FALSE, maka fungsi ini hanya akan mencari nilai yang sama persis dengan nilai yang dicari. Jika nilai yang dicari ada dua atau lebih, maka fungsi ini akan mengembalikan nilai yang pertama kali ditemukan.

Misalnya kita ingin mencoba menggunakan fungsi HLOOKUP pada dengan dengan struktur seperti dibawah ini

HLOOKUP1

Cell B6 dan C6 adalah nilai yang dicari, sedangkan cell B7 dan C7 adalah nilai yang ingin dikembalikan jika pencarian nilai di B6 dan C6 berhasil. Misalnya kita ingin mencari harga dari ID yang ada di cell B6, maka pada cell B7 sintaksnya adalah seperti berikut ini

HLOOKUP(B6,B1:F3,2,FALSE)

HLOOKUP2

Untuk mendapatkan nilai di C7, rumus yang ada di B7 tinggal di kopi ke cell C7 dengan menggeser tanda + di kanan bawah cell B7 atau dengan CTRL+C cell B7 kemudian CTRL+V ke cell C7. Akan tetapi harus hati-hati, karena copy paste secara langsung akan menggeser juga range dari table_array. Agar rangenya tidak bergeser, maka range dari table_array  harus di freeze/dibekukan dengan menambahkan tanda dolar, dari B1:F3 menjadi $B$1:$F$3. Tanda dolar ini bisa dimunculkan dengan mengetik secara manual atau dengan menekan F4 ketika kursor berada di teks B1:F3.

HLOOKUP3

Copy paste  ke cell C7 berhasil dan tidak bermasalah seperti pada gambar dibawah

HLOOKUP4

Memisahkan Teks Menjadi Kolom Untuk Data Berupa Wrapped Text di Excel

3rd Mei 2015

Tutorial Excel ini terkait dengan tutorial sebelumnya

Memisahkan Teks Menjadi Kolom Berdasarkan Koma atau Spasi di Excel 2010 atau 2013

Pada postingan sebelumnya, telah dijelaskan tentang merubah text menjadi kolom dengan pemisah yang jelas, misalnya spasi atau koma. Kalau data berupa “wrapped text“, maka merubah menjadi kolom akan cukup sulit, karena format pemisah data wrapped text tidak tersedia di pilihan pemisah.

Akan tetapi ternyata di excel sudah ada fungsi tersebut. Sebagai contoh, kita ingin memisahkan data dalam format wrapped text seperti dibawah ini

wrappedcol1

Langkah yang dilakukan sama mirip dengan tutorial sebelumnya, yang berbeda adalah langkah ke empat. Pada langkah ini, “pembatas”/”delimiter” yang dipiliah adalah “Others”/”Lainnya” seperti tampak pada gambar di bawah.

wrappedcol2

Pada kolom isian “lainnya” ini, tekan dan tahan tombol ALT, kemudian masukkan angka 010. Yang perlu dicatat dan sangat penting adalah, angka 010 ini harus dimasukkan dari tombol numeric yang ada keyboard, perhatikan gambar dibawah ini

wrapped3

Jika anda menggunakan laptop, maka tombol angka numeric bisa ditemukan biasanya pada huruf M,J,K,L,U,I,O,7,8 dan 9. Selengkapnya bisa di baca disini.

Seteleh memasukkan angka 010, tekan tombol “Next”, dan pilih “General”/”Umum” dan tekan tombol “Finish”/”Selesai”

wrappedcol4

Hasil akhirnya tampak pada gambar di bawah ini.

wrappedcol5

Memisahkan Teks Menjadi Kolom Berdasarkan Koma atau Spasi di Excel 2010 atau 2013

Ketika mengolah data, data-data yang kita dapatkan belum tentu sudah dalam format yang baik dan siap diolah. Kadang data masih dalam bentuk teks dipisahkan dengan koma atau titik koma untuk membedakan kolom. Jika data dalam jumlah besar, tentu akan lebih mudah jika data-data tersebut diubah formatnya menjadi kolom-kolom. Excel memiliki fitur untuk mengubah format teks menjadi kolom-kolom. Misalnya kita ingin membuat teks yang berisi jadwal seperti dibawah ini menjadi kolom-kolom

ttc1

Langkah-langkahnya adalah sebagai berikut:

Langkah 1

Blok kolom lokasi diletakkannya teks. Karena semua teks terdapat dalam kolom A, maka yang di blok adalah kolom A.

ttc2

Langkah 2

Pilih tab “Data”, kemudian pilih fitur “Text to Column” seperti gambar dibawah yang diberi warna merah.ttc3

Langkah 3

Sebuah jendela akan muncul yang memberikan panduan bagaimana cara memisahkan teks. Ada dua pilihan, apakah dengan membuat kolom dengan lebar yang sama (dalam hal ini jumlah teksnya), ataukah berdasarkan batas tertentu. Pilih opsi kedua, yaitu berdasarkan batas tertentu (“Dibatasi”/”Delimited”). Kemudian pilih tombol “Next”/”Berikutnya”

ttc4

Langkah 4

Langkah selanjutnya adalah, pembatas apa yang akan digunakan untuk memisahkan teks. Karena dalam contoh ini kita memisahkan teks berdasarkan koma, maka pilih “koma”. Kemudian pilih tombol “Berikutnya”/”Next”

ttc5

Langkah 5

Langkah ini menanyakan tipe data yang akan disimpan dalam tiap kolom. Karena tipe data bisa kita ganti kapan saja, pilih “Umum”/”General”, kemudian pilih tombol “Finish”/”Selesai”.

ttc6

Langkah 6

Teks sudah diubah menjadi bentuk kolom berdasarkan pembatas koma.

ttc7

Agar tampilan lebih rapi, tekan tombol pembatas antar kolom. Lebar kolom akan menyesuaikan dengan lebar teks yang ada dalam kolom

ttc8

 

Tutorial selanjutanya membicarakan mengenai memisahkan teks menjadi kolom jika data berupa wrapped text

Memisahkan Teks Menjadi Kolom Untuk Data Berupa Wrapped Text di Excel

Tutorial Menggunakan VLOOKUP di Excel

30th April 2015

VLOOKUP sering disebut oleh pengguna spreadsheet. Sebenarnya apa itu VLOOKUP? Definisi VLOOKUP akan lebih jelas jika tahu penggunaanya dan mempraktekkannya secara langsung. VLOOKUP atau Vertikal Lookup adalah sebuah fungsi yang mencari suatu nilai tertentu dari sebuah kolom, atau pencarian secara vertikal, kemudian mengembalikan nilai tertentu dari baris yang sama.

Fungsi VLOOKUP memerlukan empat buah input

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value adalah nilai yang dicari

tabel_array adalah tabel yang di dalamnya kita mencari nilai lookup_value. Nilai yang dicari ini harus ada dalam kolom pertama di table_array

col_index_num adalah kolom keberapa yang nilainya akan di print/dikembalikan ke cell. Nomor kolom dihitung dari kolom pertama di tabel_array, bukan kolom dari filenya. Misalnya kolom pertama di table_array dimulai dari index kolom B, maka kolom C memiliki nilai col_index_num 2.

[range_lookup] adalah input berupa logika TRUE atau FALSE. Input ini bersifat opsional. Jika diisi dengan TRUE, maka fungsi ini akan mencari nilai yang sama persis dengan yang dicari. Jika tidak ada, maka akan dicari nilai yang paling mendekati dengan nilai yang dicari. Jika diisi dengan logika FALSE, maka fungsi ini hanya akan mencari nilai yang sama persis dengan nilai yang dicari. Jika nilai yang dicari ada dua atau lebih, maka fungsi ini akan mengembalikan nilai yang pertama kali ditemukan.

Untuk lebih jelasnya, perhatikan tabel berikut ini

VLOOKUP

Kita ingin mengembalikan harga minyak dan jumlah stoknya. Maka formulanya adalah sebagai berikut.

VLOOKUP(G1,B2:D6,2,FALSE)

G1 berisi nilai yang dicari yaitu minyak.

B2:D6 adalah tabel untuk pencarian minyak. Tabel ini dimulai dari kolom B, bukan A, karena minyak ada di kolom B. Jadi B harus menjadi kolom pertama.

Harga minyak ada dikolom C. Dalam tabel pencarian, kolom C adalah kolom ke 2, sehingga col_index_num adalah 2.

Nilai range_lookup adalah FALSE karena ingin mencari nilai yang sama persis dengan “Minyak”

VLOOKUP1

Dengan logika yang sama, nilai stok bisa dicari dengan fungsi seperti berikut ini

VLOOKUP(G1,B2:D6,3,FALSE)

VLOOKUP2

 

Selain VLOOKUP, Excel juga menyediakan pencarian secara horisontal dengan HLOOKUP. Selanjutnya bisa dibaca di sini  Tutorial Menggunakan HLOOKUP di Excel 2010 atau 2013

Mengambil Kata di Tengah Kalimat Dengan Menggabungkan Fungsi MID dan FIND Menggunakan Microsoft Excel

26th April 2015

Postingan ini berkaitan dengan dua postingan sebelumnya

Mengambil Sebagian Text (substring) dengan Fungsi LEFT, MID dan RIGHT di Excel 2010 dan 2013

Menemukan Letak Kata atau Huruf Dalam Kalimat Menggunakan Microsoft Excel Dengan Fungsi FIND

Misalnya ada kalimat sebagai berikut “Solusi Excel Bermanfaat Sekali”. Kita ingin mengambil kata “Excel Bermanfaat”. Kalau kita menggunakan fungsi MID tanpa menggunakan FIND, kita harus tahu posisi huruf E dengan menghitung secara manual, kemudian menghitung juga jumlah huruf dalam kata “Excel Bermanfaat”.

Dengan menggabungkan antara fungsi MID dan FIND, maka kita tidak perlu lagi menghitung posisi huruf E dan juga jumlah huruf di “Excel Bermanfaat”, semuanya bisa ditulis dalam satu fungsi. Untuk lebih mudahnya, maka berikut ini saya tunjukkan langkah-langkah logika menulis formula nya. Masukan fungsi MID ada 3 yaitu

=MID(“kalimat”,posisi awal teks, jumlah karakter/huruf yang diambil)

Posisi awal teks adalah huruf “E”, jadi kita menggunakan fungsi FIND

=FIND(“E”,A1)

MIDFIND1

Jumlah karakter yang diambil dihitung dengan cara posisi huruf “S” yang kedua dikurangi dengan posisi huruf “E”. Posisi huruf “S” yang kedua (atau posisinya setelah huruf E) dicari dengan start posisi awal setelah huruf E yang posisinya telah ditemukan dengan formula FIND di atas.

=FIND(“S”,A1,FIND(“E”,A1))

MIDFIND2

Kemudian hasil posisi “S” dan “E” saling dikurangkan.

=FIND(“S”,A1,FIND(“E”,A1)) – FIND(“E”,A1)

MIDFIND3

Hasil akhirnya, formulanya adalah sebagai berikut

=MID(A1, FIND(“E”,A1), FIND(“S”,A1,FIND(“E”,A1)) – FIND(“E”,A1))

dimana

Cell A1 adalah lokasi teksnya

FIND(“E”,A1) adalah posisi awal teks

FIND(“E”,A1)) – FIND(“E”,A1) adalah jumlah karakter yang diambil

MIDFIND4

Tags

Menemukan Letak Kata atau Huruf Dalam Kalimat Menggunakan Microsoft Excel Dengan Fungsi FIND

25th April 2015

Excel mendukung regular expression, yaitu berbagai macam operasi untuk data berupa String (huruf, teks, kata atau kalimat). Untuk menemukan letak sebuah kata, huruf atau rangkaian huruf dalam sebuah kalimat panjang di Excel, fungsi yang digunakan adalah FIND. Fungsi FIND memiliki tiga buah input

=FIND(find_text,within_text,[start_num])

find_text=teks yang dicari

within_text=kalimat yang didalamnya ada text yang dicari

[start_num]=indeks memulai pencarian. Input ini sifatnya opsional, kalau dikosongkan secara otomatis dimulai dari satu.

Perhatikan contoh, kita ingin mencari kata “lusi” dari kalimat “Solusi excel”, maka penulisan formula di excel dapat dilihat dibawah ini

Find1

Pada gambar di atas, kata “lusi” dimulai dari kata ke 3. Input ketiga, [start_num], dikosongkan sehingga fungsi ini akan mencari dimulai dari huruf pertama.

Jika pencarian kata “lusi” dimulai dari huruf ke empat seperti contoh dibawah, Excel akan mengembalikan nilai error (#VALUE atau #NILAI) yang artinya kata tersebut tidak ditemukan.

Find2

Fungsi FIND bisa juga digunakan untuk mencari huruf. Pada contoh berikut, kita mencari huruf “l”. Karena huruf “l” pada kata “Solusi Excel” ada dua, maka Excel hanya akan mengambalikan posisi huruf “l” yang pertama kali ditemukan, dalam hal ini adalah 3.

Find3

Tags

Mengambil Sebagian Text (substring) dengan Fungsi LEFT, MID dan RIGHT di Excel 2010 dan 2013

24th April 2015

Banyak dijumpai dalam pengolahan data untuk memisahkan atau mengambil sebagian text dari sebuah text utuh (operasi substring). Di Excel, operasi substring ini didukung dengan tiga buah fungsi untuk data text yaitu LEFT, MID dan JOIN. Sintaks dari ketiga fungsi itu cukup sederhana, yaitu

LEFT(text,[jumlah karakter yang diambil])

substring1

MID(text,[indeks karakter mulai diambil],[jumlah karakter yang diambil])

substring2

RIGHT(text,[jumlah karakter yang diambil])

substring3

Tags

6 Langkah Mudah Mengaktifkan SOLVER Add-In di Excel 2010 dan 2013 untuk Optimisasi

22nd April 2015

Excel dilengkapi dengan fitur untuk optimisasi. Secara default fitur ini tidak aktif sehingga untuk menggunakannya, pengguna harus mengaktifkan fitur ini terlebih dahulu.

Cara mengaktifkannya mudah, mirip dengan cara mengaktifkan add-in untuk analisa statistik.

Langkah 1

ActVBA1

Klik tab “File” yang ada di sebelah kiri atas.

Langkah 2

ActVBA2

Klik tombol “Opsi” atau “Options” yang ada di bar sebelah kiri bawah.

Langkah 3

Solver1

Di jendela baru yang muncuk, klik “Add-in” yang berada di bar kiri, kedua dari bawah.

Langkah 4

Solver2

Di “Add-in aplikasi non aktif” atau “Inactive Application Add-In”  pilih “Solver Add-In”/”Pemecah Add-In”, kemudian klik “Go” atau dalam versi bahasa Indonesia adalah “Ke”, yang berada di bagian bawah.

Langkah 5

Solver3

 

Jendela baru muncul. Di jendela ini, pilihlah “Pemecah Add-In” atau “Solver Add-In”

Langkah 6

Ini langkah paling mudah, yaitu klik OK seperti pada gambar langkah lima. Add-in untuk optimisasi telah diaktifkan.

Untuk memeriksa apakah add-in telah di tambahkan, maka yang perlu dilakukan adalah klik tab “Data”

Solver4

Di sebelah kanan menu akan ada menu “Solver” seperti gambar yang dilingkari merah di atas.

Tips Unik: Mengubah Indeks Kolom dari Huruf Menjadi Angka dengan R1C1 di Excel 2010 dan 2013

Ini mungkin salah satu fitur excel yang jarang terdengar. Meskipun kurang bermanfaat, tetapi fitur ini tetap disediakan disetiap rilis excel yang terbaru. Fitur ini digunakan untuk mengubah indeks kolom dari huruf menjadi angka. Di tampilan layar excel yang biasa muncul, maka indeks kolom berupa huruf seperti dibawah ini.

R1C1_1

Jika fitur ini diaktifkan, maka tampilannya menjadi seperti ini.

R1C1_3

Nama fitur in adalah R1C1. Jika pada suatu ketika anda ingin mengerjai rekan anda, coba saja untuk mengaktifkannya di excel rekan ada. Untuk mengaktifkannya cukup mudah, tinggal ikuti langkah-langkah berikut

Langkah 1

Klik “File” kemudian piliah menu “Opsi” atau “Options” yang berada di bar kiri bawah.

ActVBA2

Langkah 2

Pilih “Formula” atau “Rumus” seperti pada gambar dibawah.

Langkah 3

Kemudian pada “Working with formulas”/”Bekerja dengan rumus”, tick Gaya referensi R1C1

R1C1_2

Tags

6 Langkah Mengaktifkan Add-Ins Statistik untuk Data Analisis di Excel 2013

21st April 2015

Analisis data dan statistik sangat dibutuhkan di banyak profesi dan keahlian. Excel sudah menyediakan add-in atau fungsi tambahan untuk membantu pekerjaan, terutama untuk membuat model multivariat statistik. Ada enam langkah yang sangat mudah di ikuti untuk mengaktifkan add-in ini. Silahkan mencoba.

Langkah 1

ActVBA1

Klik tab “File” yang ada di sebelah kiri atas.

Langkah 2

ActVBA2

 

Klik tombol “Opsi” atau “Options” yang ada di bar sebelah kiri bawah.

Langkah 3

Solver1

Di jendela baru yang muncuk, klik “Add-in” yang berada di bar kiri, kedua dari bawah.

Langkah 4

DataAddIn2

Di “Add-in aplikasi non aktif” atau “Inactive Application Add-In”  pilih “Analysis Toolpak”, kemudian klik “Go” atau dalam versi bahasa Indonesia adalah “Ke”, yang berada di bagian bawah.

Langkah 5

DataAddIn3

Jendela baru muncul. Di jendela ini, pilihlah “Analisis ToolPak” atau dalam versi bahasa Inggrisnya “Analysis Toolpak”.

Langkah 6

Ini langkah paling mudah, yaitu klik OK seperti pada gambar langkah lima. Add-in untuk data analisis statistik telah diaktifkan.

Untuk memeriksa apakah add-in telah di tambahkan, maka yang perlu dilakukan adalah klik tab “Data”

DataAddIn4

Di sebelah kanan menu akan ada menu “Data Analysis” seperti gambar yang dilingkari merah di atas.