Dewi Mariana Munthe

Jika sebelumnya saya pernah posting tentang cara menggabungkan fungsi if vlookup di excel, maka dalam postingan ini akan dibahas tentang cara menggabungkan fungsi IF HLOOKUP di excel 2003. Kombinasi fungsi IF dan HLOOKUP bisa digunakan jika dalam sebuah tabel kerja mengacu pada dua buah kriteria referensi. Dalam kasus ini variabel tabel referensi akan disusun secara horisontal.

Dalam contoh ini akan dibuat tabel kerja yang berisi  tahun penjualan, kode kertas, jenis kertas dan harga penjualan kertas. Jenis kertas dan harga kertas akan mengacu pada data yang tercantum pada tabel referensi dengan menggunakan penggabungan fungsi IF dan hlookup.

Agar lebih mudahnya buat tabel seperti di bawah ini :

Kosongkan kolom D dan E karena akan diisi dengan formula

Selanjutnya di sel D4 masukkan formula berikut :
=HLOOKUP(C4,$G$3:$K$6,2)
Di sel E4 masukkan formula berikut:
=IF(B4=2010,(HLOOKUP(C4,$G$3:$K$6,3,0)),(HLOOKUP(C4,$G$3:$K$6,4,0)))
Hasilnya terlihat seperti di bawah  ini :



File excelnya bisa didownload disini fungsi if hlookup excel 2003

Salahsatu fungsi yang tersedia di excel adalah fungsi logika IF, fungsi IF dapat digabungkan dengan berbagai fungsi-fungsi excel lainnya. Dalam contoh ini akan dibahas tentang cara menggabungkan fungsi IF dan VLOOKUP di excel 2003.

Postingan ini sebagai jawaban buat teman yang pernah menanyakan tentang cara menggabungkan fungsi IF VLOOKUP, mudah-mudahan bisa sedikit memberikan gambaran tentanng penggunaan fungsi if di excel.

Dalam kasus ini saya menganggap di tabel referensi ada 2 jenis varibel yaitu variable yang relatif tetap dan variable yang berubah bergantung pada tahun yang berlaku.

Agar lebih mudahnya bisa buat 2 buah tabel seperti di bawah ini :

1. Buat tabel kerja dan tabel referensi berikut:
Kosongkan sel D4 dan sel E4 , karena akan diisi dengan formula yang berisi fungsi IF dan VLOOKUP

2. Selanjutnya di sel D4 ketik formula :
=VLOOKUP(C4,$G$4:$J$7,2)
Formula pada D4 hanyalah formula yang menggunakan fungsi VLOOKUP saja, hanya digunakan sebagai bahan pembanding terhadap formula IF+VLOOKUP.  Formula pada sel D4 tidak berkaitan dengan formula pada sel E4
Catatan:
Tanda $ pada range $G$4:$J$7 menandakan alamat / range absolut, untuk mengatifkannya bisa gunakan tombol F4 . Jika kesulitan ketik saja formula $-nya secara manual.

Di sel E4 ketik formula
=IF(B4=2010,(VLOOKUP(C4,$G$4:$J$7,3,0)),(VLOOKUP(C4,$G$4:$J$7,4,0)))

Hasilnya akan terlihat seperti di bawah ini:



Jika ingin mendownload file excelnya bisa klik disini fungsi IF VLOOKUP

Fungsi Hlookup hampir sama dengan vlooukup, tetapi tabel referensinya disusun secara horisontal.

Berikut contoh penggunaan hlookup

1. Buat tabel kerja dan tabel referensi

2. Masukkan formula berikut:
a. Pada sel B5 ketik:
=HLOOKUP(A5,$F$4:$I$6,2,0)
b. Pada sel C5 ketik:
=HLOOKUP(A5,$F$4:$I$6,3,0)
c. Copy formula dari baris 5 ke 16
3. Hasilnya seperti gambar di bawah




Buat teman-teman pengguna microsoft excel 2007. Penggunaan fungsi hlookup di bisa dilihat panduannya disini fungsi hlookup excel 2007

Dalam bahasan kali ini kita akan mencoba mempelajari tentang fungsi text sederhana, yaitu LEFT, MIDDLE, dan RIGHT.
LEFT = memilih karakter text dimulai dari kiri ke kanan dengan range tertentu
MIDDLE = memilih karakter text dimulai dari tengah (dari kiri ke kanan)dengan range tertentu
RIGHT = memilih karakter text dimulai dari kanan ke kiri dengan range tertentu

Untuk lebih kongkritnya kita mulai saja dengan contoh berikut:


Fungsi-fungsi text akan kita terapkan pada kolom C, D dan E
Pada sel C5 ketik formula berikut:
=LEFT(B5,3)
Pada sel D5 ketik formula berikut:
=MID(B5,4,3)
Pada sel E5 ketik formula berikut:
=RIGHT(B5,2)

Hasilnya akan tampak seperti di bawah ini



Jika anda ingin mendownload contoh file di atas dalam format xls, silahkan klik disini

Fungsi Vlookup sangat berguna jika anda mempunyai tabel referensi (lihat sheet2)yang sering digunakan pada tabel kerja (lihat sheet1). Jika anda mengubah nilai pada tabel referensi maka anda tidak perlu mengubah nilai pada tabel kerja, karena otomatis akan berubah sendiri mengacu pada tabel referensinya. Hal ini sangat efektif jika anda mempunyai banyak baris (lebih dari puluhan baris).

Berikut ini prosedur membuat formula Vlookup di excel 2003:

1. Buat tabel referensi



2. Buat tabel kerja


Pada sel B3 ketik rumus
=VLOOKUP(A3,Sheet2!$A$3:$C$6,2,0)
Pada sel C3 ketik rumus
=VLOOKUP(A3,Sheet2!$A$3:$C$6,3,0)

Jika data lebih dari baris ke 14, copy formula pada kolom B3 dan C3 sesuai kebutuhan.

Jika anda ingin mendownload contoh file di atas dalam format xls, silahkan klik disini VLOOKUP Excel

Freeze panes adalah sebuah fitur atau tools yang disediakan oleh Microsoft excel. Kegunaan fitur ini adalah membekukan sel atau baris tertentu, sehingga jika digeser maka sel yang di freeze (debekukan) tersebut akan tidak bergerak (statis) sedangkan baris (sell lainnya tidak bisa bergerak), Selain itu freeze juga dapat digunakan untuk membagi ruang kerja menjadi 4 bagian.

Fitur ini sangat berguna jika anda mempunyai data (tabel) yang panjang, sehingga dengan membuat judulnya statis (tidak bergerak) / membekukan judul tabel, bisa membantu anda melihat judul dari isi sel yang digeser.

Untuk membuat freeze pane di excel 2003, bisa lakukan prosedur berikut:


1. Pilih sel uang akan anda bekukan, dalam contoh ini saya akan membekukan baris 1 -3, jadi kursor (penunjuk mouse) diletakkan di sel baris 4 atau sel A4


2. Di Menu Bar, pilih Window -> Freeze Panes


3. Akan terlihat sebuah garis hitam di bagian bawah baris ke 3, jika baris di bawahnya digeser maka baris 1 sampai 3 tetap tidak bergerak




selain membuat freeze pane secara horizontal, bisa juga mengunci sel secara vertikal (mengunci kolom)

4. Blok beberapa kolom


5. Di Menu Bar, pilih Window -> Freeze Panes
akan muncul sebuah kolom yang statis seperti di bawah ini



6. untuk menormalkan
Di Menu Bar, pilih Window ->Unfreeze Panes

Selain menggunakan software excel word recovery, Anda dapat memperbaiki file excel yang rusak menggunakan Corrupt Excel Recovery. Corrupt Excel Recovery merupakan sebuah sofware gratis dengan lisensi GNU/Public, software yang bersifat opensource ini bisa anda pelajari source code-nya. Berdasarkan informasi dari pembuat software Corrupt Excel Recovery, aplikasi ini bisa digunakan untuk recovery file excel yang rusak, file excel yang didukung excel 2003, excel 2007.

Agar software ini dapat terinstal di komputer dibutuhkan Microsoft .NET Framework 3.0, jika di komputer belum terinstal bisa download disini Microsoft .NET Framework 3.0 untuk windows xp 32 bit gunakan Microsoft .NET Framework 3.0-  X86 Redist Package (50.3 MB)

Jika saat menginstal Corrupt Excel Recovery muncul pesan :
unable to install or run the app.the app requires that assembly windowsbase version 3.0.0.0 be installed in the global assembly cache (GAC) first

Pesan error di atas diakibatkan oleh installer Corrupt Excel Recovery membutuhkan file dinamyc library windowsbase.dll
Maka solusinya coba copy file windowsbase.dll ke direktori c:\windows\assembly
File windowsbase.dll bisa dicari di direktori C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0
Informasi lebih lengkap bisa dilihat disini  assembly windowsbase

Setelah semua terinstal normal di komputer maka bisa memulai proses recovery file excel yang rusak /corrupt

Silahkan dicoba mudah-mudahan sukses. Buat teman-teman yang senang dengan programming bisa belajar utak atik source codenya mudah-mudahan bisa mengemngembangkan menjadi software yang lebih sempurna.

Jika file excel yang corrupt tidak bisa di recovery hanya ada 2 solusi:
1. Beli software excel recovery komersil atau mencari software excel recovery gratis
2. Mengetik ulang file excel yang rusak secara manual.

Untuk download aplikasi diatas bisa klik disini Corrupt Excel Recovery

Mengubah huruf besar menjadi huruf kecil, atau mengganti huruf kecil menjadi huruf besar di excel 2003 bisa dilakukan dengan menggunakan fungsi text yang disediakan oleh excel 2003. Fungsi ini sama dengan fungsi "change case" di word 2003.

Fungsi Text di Microsoft excel 2003  yang akan digunakan untuk mengubah huruf (change case) adalah:
1. PROPER (Mengubah semua huruf awal setiap kata menjadi huruf besar)
2. UPPER (Membuat semua huruf menjadi huruf besar)
3. LOWER (Mengganti semua huruf menjadi huruf kecil)



Berdasarkan data dari help offline excel 2003, ada beberapa fungsi text seperti:
ASC Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters
BAHTTEXT Converts a number to text, using the ß (baht) currency format
CHAR Returns the character specified by the code number
CLEAN Removes all nonprintable characters from text
CODE Returns a numeric code for the first character in a text string
CONCATENATE Joins several text items into one text item
DOLLAR Converts a number to text, using the $ (dollar) currency format
EXACT Checks to see if two text values are identical
FIND Finds one text value within another (case-sensitive)
FIXED Formats a number as text with a fixed number of decimals
JIS Changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters
LEFT Returns the leftmost characters from a text value
LEN Returns the number of characters in a text string
LOWER Converts text to lowercase
MID Returns a specific number of characters from a text string starting at the position you specify
PHONETIC Extracts the phonetic (furigana) characters from a text string
PROPER Capitalizes the first letter in each word of a text value
REPLACE Replaces characters within text
REPT Repeats text a given number of times
RIGHT Returns the rightmost characters from a text value
SEARCH Finds one text value within another (not case-sensitive)
SUBSTITUTE Substitutes new text for old text in a text string
T Converts its arguments to text
TEXT Formats a number and converts it to text
TRIM Removes spaces from text
UPPER Converts text to uppercase
VALUE Converts a text argument to a number

Agar lebih mudahnya bisa dilihat contoh di bawah ini:
Di selA3 ketik sebuah kalimat yang berisi kombinasi huruf besar dan kecil yang tidak beraturan, misalnya:
tuLIsan ini Awalnya kacau baLau

Selanjutnya kita coba terapkan fungsi text :
a. Di sel B6 ketik formula
=UPPER(A3)
b. Di sel B7 ketik formula
=LOWER(A3)
c. b. Di sel B8 ketik formula
=PROPER(A3)

Hasilnya akan terlihat seperti di bawah ini

Beberapa postingan lain yang membahas tentang excel bisa dilihat disini panduan excel 2003

Menggabung  text (teks)dari beberapa sel di excel 2003 ,akan sangat mudah buat teman-teman yang sudah familiar dengan excel 2003. Namun buat teman-teman yang masih pemula mungkin akan kesulitan menggabung isi dari beberapa sel menjadi satu kesatuan di dalam sebuah sel.

Postingan ini sebenarnya saya buat untuk mendukung tutorial customize browser Firefox, tutorial di excel 2003 yang berkaitan dengan customize mozilla ada 4 tahapan, jadi saya cicil postingannya agar tidak terlalu banyak pertanyaan dari teman-teman yang masih pemula saat saya membuat postingan tentang firefox tersebut.

Dalam postingan ini kita akan mencoba menggabungkan isi dua sel ke dalam sebuah sel.
Agar lebih mudah bisa lihat gambar di bawah ini:



Buat tabel seperti di bawah ini
1. Di kolom A isi dengan http://
Di kolom B isi dengan kompas.com , detik.com , dst
Kolom C di biarkan kosong karena akan akan diisi dengan formula yang bisa menggabungkan isi kolom A dan B..

2. Di sel C2 isi dengan formula
 A2&""&B2

Catatan:
Jika sel yang akan digabungkan ada 3 , misalkan sel A2 berisi  http:// , B2 berisi www. dan sel C2 berisi kompas.com
Maka untuk menghasilkan http://www.kompas.com di sel D2 , isi D2 dengan formula :

=A2&""&B2&""&C2

Kembali ke topik menggabung isi dua sel, setelah memasukkan formula  A2&""&B2 di atas, maka kita akan menghasilkan nilai di kolom C seperti gambar di bawah ini:


Silahkan dicoba.Beberapa postingan lain yang membahas tentang excel bisa dilihat disini panduan excel 2003

Buat teman-teman yang masih pemula. Terkadang kita perlu memproteksi file (dokumen) excel kita untuk menjaga agar file tersebut tidak diubah oleh orang lain. Secara default kita dapat memanfaatkan fasilitas proteksi dokumen yang telah disediakan oleh Microsoft Excel 2003.

Berikut cara membuat password file excel:

1. Buka dokumen excel yang akan diproteksi
2. Pada Menu bar pilih save , atau save as


3. Pada kotak dialog save as, pilih Tools => General Options..


4. Masukkan password pada:
kotak teks passord to open:
Klik Ok
5. pada kotak konfirm password ketik ulang password yang diketik pada langkah 4
Klik Ok
Klik Save

6. Pada saat akan membuka file akan muncul kotak dialog password berikut:


Untuk pengguna excel 2007, untuk membuat sandi file excel bisa dilihat panduannya disini membuat password excel 2007

Terkadang jika suatu saat suatu file excel yang telah kita password mungkin tidak perlu lagi di password. Maka anda bisa memanfaatkan fasilitas remove password excel (hal ini hanya berlaku jika anda masih ingat password (kode sandinya), karena jika tidak ingat passwornya anda harus menngunakan aplikasi lain untuk menghilangkan passwordnya)

Dalam bahasan kali ini saya hanya membahas cara menghilangkan password file excel jika anda masih ingat passwordnya, karena untuk membuka filenya harus memasukkan password.

1. Buka file excel anda yang terpassword
2. Akan muncul pesan konfirmasi password sebagai berikut:


Masukkan password,klik OK

3.Setelah file terbuka pada menu bar pilih Tools => Options


4. Pada kotak dialog option pilih tab Security
hapus password yang ada (klik pada kotak teks password, tekan tombol delete)
Jika pada bagian password to modify ada passwordnya,anda harus menghapus juga

5. pastikan semua kotak text password bersih (seperti gambar di bawah ini)
Klik OK

6. Simpan file yang telah anda hilangkan passwordnya.
Close file tersebut.

7. Coba buka kembali file dokumen excel yang sudah dihilangkan passwordnya. jika prosesnya benar maka file tersebut langsung terbuka tanpa ada lagi kotak konfirmasi password.

Terkadang dalam suatu penulisan teks diperlukan arah penulisan secara vertikal atau miring dengan sudut tertentu terhadap arah horisontal.

Dalam bahasan kali ini kita akan mencoba mengatur arah teks pada sel B3

1. Klik sel B3, pada menu bar pilih Cells...


2. Pada kotak dialog Format cells,Pada bagian Orientation klik titik merah dan geser ke arah yang diinginkan


Misalnya arahnya seperti berikut:
Klik tombol OK

3. Hasilnya akan tampak seperti di bawah ini. (teks No berubah arahnya)

Background tabel dapat diatur warnanya, demikian pula dengan teks sehingga bisa menonjolkan kolom atau baris tertentu.

1. Blok sel A3 sampai G4
Pada toolbar klik tombol Fill Color, pilih salah satu warna


2. hasilnya akan tampal seperti gambar di bawah
3. Untuk mengatur warna teks, misalnya blok sel B5 sampai B15, kemudian pilih tombol Font C0lor, pilih salah satu warna, maka teks akan berubah warnanya seperti di bawah ini

4. Untuk mengatur perataan teks , blok sel yang akan diatur, kemudian pilih tombol aligment (left, center, right)

Sebuah tabel akan tampak lebih menarik jika dilengkapi dengan bingkai atau garis tepi.
Di bawah ini adalah panduan untuk membuat bingkai tabel

1. Blok semua sel yang akan dibuatkan bingkai (sel A3 sampai F15)


Pilih toolbar border

2. Hasilnya akan tampak seperti di bawah ini


3. Jika ingin melihat bentuk tabel sebenarnya, bisa lakukan print preview,
Pada toolbar pilih tombol Print Preview


4. Hasilnya akan tampak seperti di bawah ini


Anda bis amengatur zoom, margin, atau setup untuk mengatur tampilan sebelum di print
Klik tombol close untuk kembali ke lembar kerja

Merger sel sering digunakan untuk mengatur tampilan tabel agar rapi dan bagus tampilannya

Dalam latihan kali ini kita akan coba membuat merger cell B3 dan B4

1. Blok sel B3 dan B4, pada toolbar klik tombol merger (lihat tanda panah merah)


2. Tampilannya akan menjadi satu sel seperti di bawah ini,
Karena tampilan (posisi) teks nama masih rata bawah, maka perlu diatur perataannya supaya rata tengah.
Klik kanan pada sel hasil merger, kemudian pilih Format Cells..



3. Setelah muncul kotak dialog Format Cells, pada frame Text alignment, di bagian Vertical pilih center (klik ujung panah merah untuk memunculkan pilihan), klik Ok



4. Hasilnya akan tampak seperti di bawah ini


Teks Nama pada kolom B sudah rata tengah

Autonumber sangat penting dalam menghemat waktu mengetik data, jika data hanya sepuluh item mungkin autonumber tidak terlalu berguna tetapi jika data ada ratusan atau ribuan, maka autonumber sangat berguna

Contoh pembuatan autonumber

1. Buat angka yang berurut, misalnya 1 pada sel A5 dan 2 pada sel A6, atau 1 dan 3 (untuk autonumber kelipatan)
Keterangan: sel A5 berarti (sel yang ada atau beralamat di kolom A baris ke 5), penamaan sel dimulai dengan kolom kemudian baris.

2. Blok (sorot) dengan cara klik pada sel A5 kemudian geser mouse ke sel A6
3. Arahkan mouse pointer ke pojok kanan bawah sel A6, tunggu hingga pointer mouse berubah menjadi tanda plus warna hitam, kemudian lakukan duoble klik



4. Jika berhasil, maka hasilnya akan nampak seperti gambar di bawah, excel akan otomatis mengisi sel baris sampai sejajar dengan dengan data yang ada di sel (kolom B)

Pelajaran autonumber cukup sampai di sini, bisa anda terapkan dengan kombinasi angka tertentu pada sel A5 dan A6

Mungkin ada diantara teman-teman yang belum mengerti tentang Microsoft excel 2003, mari bersama kita belajar microsoft excel.

Mungkin ada teman-teman yang bertanya apa kegunaan excel,yaa begitulah pertanyaan pertama terlintas di fikiran saya sewaktu pertama kali melihat excel, yang tampilannya mirip mainan saja karena terbuat dari kotak-kotak.

Microsoft Excel 2003, untuk selanjutnya disingkat Excel (untuk memudahkan membaca tutorial ini), adalah program aplikasi yang bisa digunakan untuk menghitung, memproyeksikan, menganalisa, dan mempresentasikan data. Disini kita akan banyak bersinggungan dengan metode pembuatan tabel, formula dan grafik yang sangat dibutuhkan sekali dalam penyusunan data2 penelitian, data usaha dan atau hal-hal lain yang berhubngab dengan fungsi excel dalam kehidupan sehari-hari.

Untuk memulai excel klik shortcut excel di desktop komputer anda, atau masuk ke startmenu =>Programs => Microsoft Office => Microsoft Excel 2003

Untuk memudahkan dalam pembahasan selanjutnya saya akan mencoba memberi gambaran tentang lembar Excel. (Untuk memperbesar gambar silahkan klik gambar)



Tombol Perintah di excel




Untuk memulai mengetik (memasukkan data)
Klik atau arahkan kursor ke sel (cell) yang diinginkan (pakai tombol panah yang ada di keyboard), jika teksnya panjang melebihi ukuran sel maka lebar cellnya bisa diubah dengan melakukan drag (geser) pada tepi judul kolom. Setelah selesai mengetik data selalu diakhiri dengan menekan enter

Cara Memperbaiki Kesalahan Pengetikan
Bila ada kesalahan pengetikan data, anda dapat memperbaikinya dengan mengikuti langkah berikut ini :
1. Pilih sel yang datanya ingin diperbaiki, lalu tekan F2. Atau double klik tombol kiri mouse
pada sel yang datanya ingin diperbaiki.
2. Selanjutnya perbaiki data yang salah tersebut kemudian tekan tombol Enter bila sudah selesai.

Untuk menyimpan data
Pada Mmenu bar pilih File=>Save

Untuk keluar dari excel, bisa dengan cara:
a. Pada menu bar pilih File => Exit, atau
b. Pada title bar di pojok sebelah kanan bagian atas klik tombol tanda X

Keyboard shorcut bisa membantu dalam menghemat waktu pengetikan, karena tangan anda tidak perlu lagi bolak-balik dari keyboard ke mouse. Jika mengetahui kombinasi tombol keyboard untuk melakukan perintah-perintah yang ada di Word, maka akan sangat membantu jika anda mengetahui kode shorcut di keyboard.
Untuk mencari kode shortcut di word bisa anda gunakan prosedur berikut:

1. Pada Menubar pilih Tools => Customize


2. Pada kotak dialog Customize, pilih tab Commands
Klik tombol Keyboard


3. Pada tombol CustomizeKeyboard pilih item dari Categories
Pilih item yang ada dalam categories, contoh : File => FileCloseOrExit



Pada bagian Current keys muncul kombinasi tombol keybord yang bisa anda gunakan
Contoh : jika menekan tombol Alt+F4 maka Word akan mengeksekusi perintah menutup file

Beberapa tombol shorcut yang biasa saya gunakan:
Ctrl+S = Menyimpan file
Ctrl+C = Copy file
Ctrl+X = Cut file
Ctrl V = Paste

Anda bisa melihat kode tombol shorcut yang lain dengan menggunakan cara di atas.
(Catatan: Jika anda kuat menghapal sebaiknya anda gunakan kombinasi tombol diatas untuk mempercepat pengetikan dalam Word)