1. Format Cell pada Microsoft Excel

Number

Fungsi Number digunakan untuk mengatur format angka dari cell. Dalam menu Number terdapat 12 pilihan kategori, yaitu:

Kategori

Format

General

Umum/Normal tanpa format (contoh : 50)

Number

Angka (contoh : 50, 15.5)

Currency

Mata uang (contoh : Rp.50.000)

Accounting

Akuntansi/Keuangan (contoh : Rp. 50.000)

Date

Tanggal (contoh : 29/10/2013)

Time

Waktu (contoh : 11:00:00

Percentage

Persentase (contoh : 50%)

Fraction

Pecahan (contoh : ¾ , ½ )

Scientific

Scientific (contoh : 3,E+01)

Text

Teks (angka akan dianggap sebagai teks)

Special

Format khusus

Custom

Format dengan pengaturan pribadi/manual

Alignment

Fungsi Alignment adalah untuk mengatur perataan dari cell. Dalam Alignment terdapat 4 pengaturan, yaitu:

1. Text alignment

Text alignmen digunakan untuk mengatur perataan teks,terdapat 2 opsi pengaturan yaitu:

Horizontal : Perataan secara horizontalVertical : Perataan secara vertical

2. Text control

Wrap text : Menjadikan teks mengikuti ukuran lebar cellShrink to fit : Menjadikan teks mengecil mengikuti lebar cellMerge cells : Menggabungkan cell

3. Right-to-left

Text Direction : Mengatur arah penulisan teks

4. Orientation

Orientation digunakan untuk mengatur derajat kemiringan dari teks.

Font

Fungsi Font adalah untuk mengatur karakter huruf dari cell. Dalam Font terdapat 6 opsi pengaturan, yaitu:

1. Font
Font digunakan untuk memilih jenis huruf yang diinginkan. Seperti jenis font Arial, Times New Roman dan lain-lain.2. Font Style

Font Style digunakan untuk mengatur gaya penulisan, yaitu:

RegularItalic (cetak miring) = Ctrl+IBold (cetak tebal) = Ctrl+BBold + Italic (cetak tebal dan miring) = Ctrl+I tambah Ctril+B

3. Size
Untuk mengubah ukuran huruf.4. Underline
Underline digunakan untuk membuat garis bawah huruf (Ctrl+U)5. Color
Color digunakan untuk mengganti warna huruf dengan warna yang diinginkan.6. Effects
Effects digunakan untuk memberi efek pada huruf, efek tersebut yaitu:

· Strikethrough : efek coretan, contoh: seperti ini· Superscript : huruf berukuran kecil diatas karakter umum (seperti pangkat), contoh: seperti iniSubscript : huruf berukuran kecil dibawah karakter umum, contoh: seperti ini

Border

Fungsi Border adalah untuk mengatur bingkai/garis luar dari cell. Dalam Border terdapat 4 opsi pengaturan, yaitu

1. Line
Line digunakan untuk mengatur jenis garis.2. Color
Color digunakan untuk mengatur warna garis.3. Presets

None : tanpa bingkaiOutline : memberi garis luar cell/rangeInside : memberi garis dalam cell/range

4. Border
Border digunakan untuk memberikan garis sesuai arah yang diinginkan.

Fill

Fill digunakan untuk memberikan warna pada cell. Pada Fill terdapat 3 opsi pengaturan, yaitu:

1. Background Color

Background Color digunakan untuk memilih warna background belang cell

2. Pattern Color

Pattern Color digunakan untuk memilih warna arsidari dari cell

3. Pattern Style

Pattern Style digunakan untuk memilih jenis arsiran untuk cell

Protection

Protection berfungsi untuk melindungi isi cell, dengan cara mengunci sekaligus menyembunyikan rumus di dalam cell.

2. Fungsi Logika Microsoft Excel 2007

''Fungsi Logika Microsoft Excel 2007- Dalam melakukan perhitungan, seringkali ditemukan adanya beberapa pilihan yang harus ditetukan. Sebagai contoh, dari nilai mahasiswa akan ditentukan apakah mahasiswa tersebut lulus atau tidak, dan jika lulus apakah predikat dari nilainya tersebut. Dalam menangani hal ini telah disediakan fungsi untuk percabangan, yaitu dengan mengunakan fungsi IF. Fungsi ini digunakan untuk menguji suatu kondisi atau logika, yang akan menentukan nilai kembali ke-1 jika kondisi tersebut bernilai benar dan menentukan nilai kembali ke-2 jika kondisi tersebut salah. Cara penggunaan:

=IF(Kondisi;[Nilai_Jika_Benar];[Nilai_Jika_Salah])

Keterangan:

Kondisi

Suatu ungkapan logika yang berupa perbandingan.

Nilai_Jika_Benar

Nilai yang dihasilkan jika pengujian kondisi bernilai benar.

Nilai_Jika_Salah

Nilai yang dihasilkan jika pengujian kondisi bernilai salah.

Sebagai contoh, dalam menampilkan status kelulusan mahasiswa dengan melihat nilai ujian yang sudah dijalaninya sebagai berikut.

Gambar 5.8 Data awal untuk mencari status kelulusan

Untuk mendapatkan status “LULUS” mahasiswa harus mempunyai nilai lebih besar dari 50. Jika nilainya kurang dari 50, maka akan diberi status “TIDAK LULUS”.

a. Penggunaan Fungsi IF

Untuk dapat menyelesaikannya dibutuhkan fungsi logika, yaitu fungsi IF. Untuk lebih jelasnya, berikut disampaikan beberapa paparan tentang penggunaan fungsi IF. Fungsi IF dengan format lengkap adalah sebagai berikut.

IF(logical_test;value_if_true;value_if_false)

Keterangan:

logical_test merupakan syarat dari percabangan.

value_if_true merupakan nilai jika syarat percabangan terpenuhi.

value_if_false merupakan nilai jika syarat percabangan tidak terpenuhi.

Langkah-langkah untuk menyelesaikannya melalui function wizard adalah sebagai berikut.

1) Klik pada sel D3.

2) Klik Formulas, pilih Logical, kemudian klik fungsi IF.

Gambar 5.9 Pemilihan Fungsi IF melalui Category Logical.

3) Ubah setting pada kotak dialog fungsi IF seperti berikut.

Gambar 5.10 Setting melalui function wizard

Pada Logical Test ditulis C3 > 50 adalah karena di sel C3-lah letak dari nilai yang akan dilakukan penyeleksian. Ketikkan syaratnya pada isian logical_test, misalnya C3>50 yang artinya jika data di sel C3 lebih besar atau sama dengan 50 maka bernilai benar dan jika kurang dari 50 maka bernilai salah. Ketikkan teks “Lulus” pada isian value_if_true, yang artinya jika pada logical_test bernilai benar maka teks ini yang akan dihasilkan/dikeluarkan. Ketikkan teks “Tidak Lulus” pada isian value_if_false, yang artinya jika pada logical_test bernilai salah maka teks ini yang akan dihasilkan/dikeluarkan.

4) Klik OK. Copy-kan formula ke sel di bawahnya.

Pemberian tanda “ ” merupakan tambahan jika ingin menambahkan statement berupa kalimat atau string. Didapatkan hasil akhir seperti gambar berikut.

Gambar 5.11 Hasil akhir pemberian status kelulusan

b. Percabangan beberapa Tingkat

Percabangan tidak hanya pemisahan menjadi dua kemungkinan saja, namun juga bisa menjadi banyak kemungkinan. Untuk percabangan yang memisahkan ke banyak kemungkinan harus menggunakan IF secara bertingkat

Tabel 5.4. Fungsi Logika

Fungsi

Keterangan

IF

Menentukan suatu tes logika untuk dikerjakan, dan mempunyai bentuk:=IF(tes logika, nilai jika benar, nilai jika salah)

AND, OR, dan NOT

Merupakan fungsi tambahan untuk mengembangkan tes kondisi. Fungsi AND dan OR maksinal berisi 30 argumen logika, sedangkan NOT hanya mempunyai satu argumen logika, mempunyai bentuk:AND (logika1,logika2, …,logika30)OR (logika1,logika2, …,logika30)NOT (logika)

Pembahasan fungsi IF di atas dengan Tes Logika Tunggal, Tes Logika dapat dikembangkan dengan tambahan salah satu fungsi AND, OR, atau NOT. Bentuk fungsi IF dengan tes logika yang dikembangkan adalah sebagai berikut.

= IF (OR(Tes Logika1;Tes Logika2);Nilai jika benar;Nilai jika salah)

Contoh:

Sebuah perusahaan akan merekrut tenaga satuan pengaman (satpam) dengan ketentuan pengalaman kerja minimal empat tahun dan usia maksimal 35 tahun. Perusahaan melakukan seleksi administrasi dengan kriteria tersebut. Pelamar yang memenuhi syarat akan mengikuti seleksi selanjutnya, sedangkan yang tidak memenuhi syarat dinyatakan gugur. Kasus tersebut dapat diterjemahkan ke dalam fungsi IF seperti berikut ini.

= IF(AND(Kerja>=4;Usia<=35);Wawancara;Gugur)

Fungsi tambahan adalah AND karena kedua tes logika merupakan kriteria yang harus terpenuhi, perhatikan penerapan fungsi tersebut dalam baris rumus worksheet.

Gambar 5.12 Fungsi IF dengan 2 tes logika

Istilah fungsi IF bercabang adalah kasus yang mempunyai banyak tingkat pengujian tes logika yang diselesaikan dengan fungsi IF. Sebagai contoh sebuah lembar kerja berisi data hasil ujian statistik. Berdasarkan nilai ujian akan dikonversikan dalam bentuk huruf dengan ketentuan sebagai berikut.

Nilai

Huruf

0 – 59

E

60 – 74

D

75 – 84

C

85 – 94

B

95 – 100

A

Perhatikan penyelesaian dengan fungsi IF dalam lembar kerja seperti pada gambar berikut.

Gambar 5.13 Contoh fungsi IF bercabang

Sel E5 diisi dengan rumus:

=IF(D5<60;”E”;IF(D5<75;”D”;IF(D5<85;”C”;IF(D5<95;”B”;”A”))))

c. Mencari Jumlah

Kadangkala diperlukan sebuah informasi untuk menampilkan berapa jumlah data yang memenuhi kriteria tertentu. Misalnya, dalam sebuah daftar nilai ingin diketahui berapa orang yang mendapat nilai “A”. Untuk itu telah disediakan sebuah fungsi sebagai berikut.

=COUNTIF(range,criteria)

Di mana pada area yang disebutkan di range akan dicari berapa jumlah sel yang sesuai dengan kriteria. Contoh =COUNTIF(B2:B57,”A”) artinya dicari berapa jumlah sel yang berisi “A” pada range B2 sampai B57. Pada contoh sebelumnya, dikembangkan untuk mencari jumlah lulus dan tidak lulus, sehingga nantinya data akan menjadi seperti di bawah ini.

Gambar 5.14 Hasil akhir penambahan fungsi COUNTIF

Untuk dapat menambahkan hasil tersebut, lakukan penambahan fungsi COUNTIF pada C9 sebagai berikut melalui function wizard.

Gambar 5.15 Pengubahan setting fungsi COUNTIF untuk sel C9

Sedangkan untuk mendapatkan jumlah yang tidak lulus, lakukan penambahan fungsi COUNTIF pada C10 sebagai berikut melalui function wizard.

ss

Gambar 5.16 Pengubahan setting fungsi COUNTIF untuk sel C10

Nilai yang kita olah melalui Excel sebenarnya dapat dibagi menjadi dua bagian, yaitu nilai formula dan nilai acuan. Materi yang selama ini dijelaskan pada bab-bab sebelumnya adalah nilai formula, di mana semua nilai yang diolah menjadi satu dengan formula yang dihitung, misal =A1*20. Angka 20 merupakan nilai formula, sedangkan pada beberapa keadaan di mana nilai tersebut sering berubah dapat kita gunakan nilai acuan agar tidak perlu mengubah melalui formula.

Untuk memudahkan menggunakan nilai acuan, Excel menyediakan fasilitas Fungsi Lookup, fungsi ini akan melihat nilai pada tabel yang lain apakah nilai yang dicocokan ada pada tabel tersebut, untuk kemudian diambil nilainya. Latihan berikut ini mencoba menggunakan logika IF untuk menentukan Jenis Barang, Nama Barang, dan Harga Barang, berdasarkan Kode Barang yang telah ditentukan. Di sini Anda juga akan menggunakan beberapa fungsi String.

Langkah Awal Buatlah data sebagai berikut.

Gambar 6.24 Data awal

Langkah Penyelesaian

(1) Isi sel B5 dengan formula:

=IF(LEFT(A5;1)=”A”;”Air Mineral”;””)

Pengertian: Jika 1 huruf pertama dari sel A5 adalah “A”, maka isi sel B5, dengan teks “Air Mineral”, jika tidak maka kosongkan sel B5.

(2) Gandakan sampai sel B10. Apa yang Anda lihat? Sel B7 sampai B10 berisi nilai FALSE. Kenapa? Ya, karena Anda baru melakukan satu pengujian untuk huruf “A”, saja.

(3) Klik kembali sel B5, lanjutkan penulisan formula dengan diawali menekan tombol F2 pada keyboard, hapus sebagian formula (sisi kanan), sampai batas simbil semicolon (;) lengkapi hingga formula menjadi: =IF(LEFT(A5;1)=”A”;”Air

Mineral”;IF(LEFT(A5;1)=”S”;”Susu”))

Saat Anda menekan tombol Enter untuk mengakhiri pengetikan formula, Anda akan mendapatkan pesan seperti gambar ilustrasi di bawah ini:

Gambar 6.25 Peringatan kesalahan pemasukan formula

Tekan saja tombol Enter sekali lagi, atau klik tombol Yes. Kotak pesan tersebut adalah pesan yang memberitahu kepada Anda bahwa Anda melakukan kesalahan penulisan formula. Tetapi perlu diingat, kesalahan yang Anda lakukan bukanlah kesalahan struktural dari penggunaan fungsi IF, ini hanyalah kesalahan karena Anda lupa atau malas untuk mengetikkan tanda kurung tutup (“)”) untuk mengakhiri penggunaan fungsi IF. Lain halnya jika Anda mendapatkan kotak pesan berikut ini:

Gambar 6.26 Peringatan kesalahan pemasukan formula yang cukup fatal

Ini adalah kesalahan yang cukup fatal, kemungkinan karena Anda lupa mengisikan parameter yang diperlukan atau kesalahan menuliskan simbol.

(4) Gandakan kembali sampai sel B5. Sekarang tinggal B9 dan B10 saja yang berisi nilai FALSE.

(5) Terakhir lengkapi formula hingga:

=IF(LEFT(A5;1)=”A”;”Air Mineral”;IF(LEFT(A5;1)=”S”;”Susu”;”Kopi”)) Atau:

=IF(LEFT(A5;1)=”A”;”Air Mineral”;IF(LEFT(A5;1)=”S”;”Susu”;IF(LEFT(A5;1)=”K”; “Kopi”;”Tidak terdata”)))

(6) Gandakan kembali sampai sel B10. Ya, itulah cara efektif untuk melakukan pengujian. Jangan langsung menguji secara lengkap, lakukan secara bertahap bertahap.

(7) Klik sel C5, ketikkan formula: =IF(MID(A5;2;2)=”BL”;CONCATENATE(B5;” “;”Botol”);””)

Pengertian: Jika mulai huruf ke dua dari sel A5 sebanyak dua huruf adalah “BL”, maka isi sel C5, dengan menggabungkan (Concatenate) isi dari sel B5, spasi (“ ”) dan “Botol”. Jika tidak, maka kosongkan sel C5.

(8) Gandakan sampai sel C10.

(9) Lakukanlah cara pengujian seperti contoh langkah pengujian untuk sel B5 di atas, hingga pada formula C5 berisi formula:

=IF(MID(A5;2;2)=”BL”;CONCATENATE(B5;”“;”Botol”);IF(MID(A5;2;2)=”GL”;CONCATENATE(B5;”“;”Gelas”);IF(MID(A5;2;2)=”SC”;CONCATENATE(B5;” “;”Sachet”);IF(MID(A5;2;2)=”KL”;CONCATENATE(B5;” “;”Kaleng”);IF(MID(A5;2;2)=”MN”;CONCATENATE(B5;” “;”Murni”);IF(MID(A5;2;2)=”SU”;CONCATENATE(B5;” “;”Susu”);”Tidak dikenal”))))))

(10) Gandakan sampai sel C10.

Gambar 6.27 Hasil akhir yang akan Anda Dapatkan

(11) Klik sel D5, ketikkan formula berikut:

=IF(RIGHT(A5;1)=”1”;500;0)

Pengertian: Jika 1 huruf dari sisi kanan A5 adalah teks 1 (satu), maka isi sel D5, dengan nilai angka 1500. Jika tidak, maka isi sel D5 dengan nilai angka 0 (nol).

(12) Gandakan hingga sel D10.

(13) Lakukan cara pengujian bertahap, hingga akhir formula untuk sel D5 adalah, sebagi berikut:=IF(RIGHT(B6;1)=”1”;500;IF(RIGHT(B6;1)=”2”;1000;IF(RIGHT(B6;1)=”8”;4000;0)))

(14) Gandakan hingga sel D10.

(15) Bingkailah hingga tampak menarik.

2. Fungsi TEXT pada MS EXCEL

Fungsi ini digunakan untuk mengubah data numerik menjadi data teks, sekaligus menampilkannya dengan format angka tertentu.

Bentuk : TEXT(angka,format)

angka adalah data numerik, alamat sel, atau sebuah rumus yang menghasilkan data numerik.format adalah format yang akan digunakan untuk memformat data numerik (angka). Penulisan format angka diapit dengan tanda petik (""). Teks format tidak dapat diisi dengan tanda (*).

Contoh :

Data yang akan diformat dengan fungsi TEXT

Rumus

Keterangan

Hasil

=TEXT(A2,”$0.00)

Menghasilkan data teks dari nilai 3500 dengan format $0.00.

$3500.00

=TEXT(A4,”dd-mmm-yy”)

Menghasilkan data tanggal dengan format dd-mmm-yy.

10-Mar-07

=TEXT(A5,”0 \b\u\a\h”)

Menghasilkan data teks dari nilai 4 dengan format 0 buah.

4buah

Catatan:

Jika Anda ingin menambahkan format angka yang dikombinasi dengan teks, maka di depan masing-masing huruf teks dalam format harus diawali dengan tanda "\". Sebagai contoh, perhatikan rumus pada tabel di atas yaitu pada baris terakhir. Hal ini untuk menghindari teks format dibaca sebagai salah satu fungsi dalam Microsoft Excel.

Salah satu kategori fungsi pada Microsoft Excel adalah fungsi TEXT, yang berisi fungsi-fungsi yang dapat digunakan untuk mengolah data berupa teks/STRING/label/kata/kalimat bahkan dapat digunakan untuk mengolah data numerik tetapi tentunya akan menghasilkan data text/label.

Beberapa fungsi TEXT adalah:

Nama Fungsi

Pengertian

Bentuk Umum

Contoh

CHAR

Menghasilkan karakter spesifik sesuai dengan nilai parameter yang diberikan. Parameter tersebut adalah kode ASCII dari karakter yang dihasilkan.

CHAR(number)

=CHAR(48) hasil 0
=CHAR(65) hasil A
=CHAR(122) hasil z

CODE

Menghasilkan nilai numerik dari karakter pertama STRING Teks pada parameter diberikan

CODE(text)

=CODE(“SMA NEGERI 1 BAUBAU”) hasil 83
=CODE(“sma negeri 1 baubau”) hasil 115

CONCATENATE

Menggabungkan semua teks dari parameter-parameter yang diberikan

CONCATENATE(text1;text2;…)

=CONCATENATE(“SMA”;”NEGERI”;”1″;”BAUBAU”) hasil “SMANEGERI1BAUBAU”
=CONCATENATE(“SMA”;” “;”NEGERI”;” “;”1″;” “;”BAUBAU”) hasil “SMA NEGERI 1 BAUBAU”

FIND

Menghasilkan posisi teks P1 dalam teks P2 setelah karakter ke-P3 dihitung dari karakter pertama dari teks P2. Fungsi FIND membedakan huruf kapital dengan huruf kecil (case sensitif).

FIND(find_text;within_text; start_num)

=FIND(“A”;”SMA NEGERI 1 BAUBAU”;1) hasil 3
=FIND(“A”;”SMA NEGERI 1 BAUBAU”;4) hasil 15
=FIND(“a”;”SMA NEGERI 1 BAUBAU”;1) hasil #VALUE

LEFT

Menghasilkan karakter terkiri dari teks P1, atau beberapa karakter berdasarkan nilai P2 yang diberikan

LEFT(text,num_chars)

=LEFT(“SMAN 1 BAUBAU”) hasil “S”
=LEFT(“SMAN 1 BAUBAU”;3) hasil “SMA”

LEN

Menghasilkan nilai yang menyatakan panjang teks yang diberikan pada P1

LEN(text)

=LEN(“SMAN 1 BAUBAU”) hasil 13
=LEN(“SMA NEGERI 1 BAUBAU “) hasil 20

LOWER

Mengubah semua huruf kapital dari P1 menjadi huruf kecil

LOWER(text)

=LOWER(“SMA NEGERI 1 BAUBAU”) hasil “sma negeri 1 baubau”
=LOWER(“SMA Negeri 1 Baubau”) hasil “sma negeri 1 baubau”

MID

Menghasilkan sejumlah P3 karakter dari teks P1 dimulai dari karakter ke-P2

MID(text,start_num,num_chars)

=MID(“SMAN 1 BAUBAU”;2;3) hasil “MAN”
=MID(“SMAN 1 BAUBAU”;8;4) hasil “AUBA”

REPLACE

Mengganti teks pada P1 sebanyak P3 karakter dimulai dari karakter ke-P2 dengan teks P4

REPLACE(old_text,start_num, num_chars,new_text)

=REPLACE(“SMAN 1 BAUBAU”;8;3;”KENDARI”) hasil “SMAN 1 KENDARIBAU”
=REPLACE(“SMAN 1 BAUBAU”;4;3;”S MUTIARA”) hasil “SMAS MUTIARA BAUBAU”

REPT

Mengulang teks P1 sebanyak P2 kali

REPT(text,number_times)

=REPT(“TIK”;3) hasil “TIKTIKTIK”
=REPT(“%”;10) hasil “%%%%%%%%%%”

SEARCH

Menghasilkan posisi teks P1 dalam teks P2 setelah karakter ke-P3 dihitung dari karakter pertama dari teks P2. Fungsi SEARCH tidak membedakan huruf kapital dengan huruf kecil (not case sensitif).

SEARCH(find_text,within_text, start_num)

=SEARCH(“A”;”SMA NEGERI 1 BAUBAU”;1) hasil 3
=SEARCH(“A”;”SMA NEGERI 1 BAUBAU”;4) hasil 15
=SEARCH(“a”;”SMA NEGERI 1 BAUBAU”;1) hasil 3

SUBSTITUTE

Mengganti teks P2 dalam teks P1 dengan teks P3 hanya pada urutan ke-P4. Jika P4 tidak ditentukan, maka semua teks P2 akan digantikan dengan teks P3

SUBSTITUTE(text,old_text, new_text,instance_num)

=SUBSTITUTE(“SMAN 1 BAUBAU”;”A”;”W”;3) hasil “SMAN 1 BAUBWU”
=SUBSTITUTE(“SMAN 1 BAUBAU”;”A”;”W”) hasil “SMWN 1 BWUBWU”

TRIM

Menghapus semua spasi yang tidak perlu, terutama di kedua ujung teks, kecuali spasi pemisah antar kata

TRIM(text)

=TRIM(” SMAN 1 BAUBAU “) hasil “SMAN 1 BAUBAU”

UPPER

Mengubah semua huruf kecil menjadi huruf kapital

UPPER(text)

=UPPER(“SMA Negeri 1 Baubau”) hasil “SMA NEGERI 1 BAUBAU”

VALUE

Mengubah data label berupa angka menjadi data numerik

VALUE(text)

=VALUE(“7″) hasil 7

3. Fungsi Referensi (Look Up) pada ms. Excel

Fungsi VLOOKUP dan HLOOKUP mempunyai fungsi dan prinsip yang sama. Perbedaanya terletak pada pembacaan arah tabel. Dimana Fungsi VLOOKUP digunakan untuk membaca nilai tabel dalam arah vertical (dalam satu kolom), sedangkan Fungsi HLOOKUP digunakan untuk membaca nilai tabel dalam arah horizontal (dalam satu baris).

Rumus umum untuk kedua fungsi ini adalah sebagai berikut:

=VLOOKUP(lookup_value;table_array;col_index_num;lookup_range)

=HLOOKUP(lookup_value;table_array;col_index_num;lookup_range)

Keterangan:

- lookup_value adalah nilai yang akan menjadi acuan pencarian di table yang digunakan. Ia merupakan kunci berupa alamat sel yang digunakan sebagai kunci pembacaan table.

- table_array adalah range (rentang nilai) dimana tabel tempat nilai-nilai yang ingin dibaca disimpan. Pada range ini biasanya ada beberapa kolom. Kolom pertama berisi nilai pembanding yang akan dibandingkan dengan nilai kunci.

- col_index_num adalah nomor baris atau kolom di tabel acuan.

- lookup_range adalah nilai logika yang menentukan apakah fungsi mencari sebuah nilai yang benar-benar sesuai atau menggunakan nilai pendekatan. Jika diabaikan, maka akan menggunakan nilai TRUE yang dengan pendekatan. Sebaliknya, jika menggunakan FALSE, fungsi akan menggunakan pendekatan nilai yang benar-benar sesuai.

Catatan: Range table data yang akan dibaca dengan fungsi VLOOKUP harus dalam keadaan urut secara Ascending (secara menaik)

PEMBUATAN TABEL REFERENSI DAN PENGGUNAANNYA

Sebelum menggunakan rumus VLOOKUP dan HLOOKUP kita harus memahami pembuatan tabel acuan dan tabel pencariannya terlebih dahulu. Berikut langkah-langkah yang harus diperhatikan.

Salah satu yang populer permintaan tutorial Excel adalah bagaimana Anda mencari nilai pada satu lembar kerja Excel dan menggunakannya pada lembar kerja Excel yang lain. Misalnya, Anda perlu untuk menerjemahkan sejumlah produk menjadi nama produk. Salah satu fungsi Excel favorit saya adalah fungsi VLOOKUP dan dapat membantu dengan tugas ini. (Termasuk Excel VLOOKUP Contoh berkas dan video dalam bagian referensi.)

Salah satu cara untuk memecahkan masalah ini adalah untuk menciptakan lembar kerja dengan pcode dan penerjemahan dan memiliki Excel menggunakan fungsi VLOOKUP untuk nama partai. Anda mungkin berpikir VLOOKUP sebagai penerjemah Excel. Saya kemudian bisa menambahkan kolom yang disebut "Partai Politik" ke worksheet asli saya untuk menampilkan informasi dari tabel.

Membuat Tabel Lookup

Sebuah tabel termasuk nilai-nilai yang Anda ingin "lookup" seperti pcode kami dan terjemahan seperti partai politik. Anda dapat menempatkan tabel pada lembar kerja yang sama, tetapi untuk tutorial ini Excel saya akan menambahkan lembar kerja yang disebut "Partai Politik".

Cara Membuat Tabel Lookup,

1. Klik kanan tab spreadsheet Anda dan pilih Insert…

2. Pada Insert dialog, klik dua kali Worksheet. Pada tab General.

3. Ubah nama tab ini lembar kerja baru dengan nama deskriptif seperti "Party Codes"

4. Dalam Kolom A, masukkan nilai-nilai unik yang ada pada lembar kerja utama Anda. Dalam contoh saya, ini adalah kode yang menunjukkan di kolom pcode di thumbnail. Nilai-nilai ini harus dalam urutan menaik.

5. Di Kolom B, masukkan nilai diterjemahkan. Anda dapat memiliki nilai lebih di kolom A daripada muncul di spreadsheet utama Anda. Sebagai contoh, saya memiliki entri untuk "Partai Citizen" meskipun saya tidak menunjukkan pemilih terdaftar dengan afiliasi itu.

Menggunakan Fungsi VLOOKUP

Fungsi VLOOKUP Excel menggunakan 4 buah informasi. Fungsi panel mungkin tampak menakutkan dengan istilah, tetapi lebih sederhana daripada yang terlihat. (Catatan:. Jika Anda memiliki Excel 2007 atau 2010, Anda dapat menemukan file PDF tambahan cetakan layar diperbarui di bagian sumber daya di bawah)

Untuk pencarian nilai menggunakan VLOOKUP,

1. Tambahkan kolom baru pada lembar kerja asli Anda yang akan menampilkan info menarik dari tabel Lookup. Dalam contoh saya, saya menambahkan kolom yang disebut Partai Politik di Kolom D. Ini adalah di mana saya akan memasukkan fungsi Excel.

2. Tempatkan kursor di sel kosong pertama dalam kolom tersebut. Dalam contoh saya, ini adalah sel D2.

3. Dari menu Insert, pilih Function .... Insert Function dialog akan muncul.

4. Dalam Mencari fungsi: text box, ketik "vlookup" dan klik Go.

5. Sorot VLOOKUP dan klik OK.

Mendefinisikan Nilai VLOOKUP

Setelah Anda mengklik OK, dialog Argumen Fungsi Excel muncul dan memungkinkan Anda untuk menentukan empat nilai. Anda akan melihat bahwa sel awal dan formula bar menampilkan bagian awal fungsi = VLOOKUP (). Fungsi Argumen dialog menambahkan elemen data yang diperlukan yang akan menampilkan antara ().

Untuk tujuan ilustrasi, saya telah dilapisi Partai Kode worksheet di atas untuk menunjukkan hubungan.

1. Lookup_value - Pikirkan bidang ini sebagai titik awal Anda. Dalam contoh saya, saya akan klik sel C2 jadi nilai diisi dialog. Saya meminta Excel mengambil nilai C2, yang menampilkan sebagai pcode dari "A", dan menemukan partai politik pencocokan pada tabel saya di Partai Kode worksheet.

2. Table_array - ini adalah range untuk tabel Anda. Rentang ini bisa berada di lembar kerja yang ada atau worksheet lain seperti kami "Partai Codes". Ketika Anda mengklik tab lain dan menentukan jangkauan, Excel prepends bahwa nama tab untuk rentang seperti Partai Codes '.

Aturan & Peringatan

Ada beberapa peraturan yang harus diingat tentang hal ini

Aturan 1 - Kolom kiri harus mengandung nilai-nilai yang direferensikan. Dengan kata lain, saya tidak bisa memiliki kolom pertama kami menjadi Partai Politik.

Aturan 2 - Anda tidak dapat memiliki nilai ganda dalam kolom paling kiri dari kisaran lookup. Aku tidak bisa memiliki dua entri dengan nilai "A" dengan satu menjadi partai "Demokrat" dan satu lagi "A" untuk pesta "Humanis".

Aturan 3 - Ketika referensi tabel pencarian, Anda tidak ingin sel referensi Anda untuk berubah ketika Anda drag dan mengisi untuk mengisi sel-sel lain dengan fungsi VLOOKUP. Sebagai contoh, jika saya ingin menggunakan fungsi yang sama dalam sel D3 melalui D7, aku tidak ingin referensi lookup saya untuk menggeser setiap kali aku pindah ke sel berikutnya. Aku butuh referensi sel harus sama. Setelah Anda mendefinisikan jangkauan Anda, Anda perlu menekan F4 yang akan siklus melalui referensi absolut dan relatif. Anda ingin memilih opsi yang mencakup $ sebelum Kolom dan Row. (Partai Kode '$ A $ 2:!. $ B $ 45) Anda bisa mendapatkan sekitar ini jika Anda tahu bagaimana menggunakan rentang nama Excel.

Col_index_num - ini adalah jumlah kolom pada tabel Anda yang memiliki informasi yang Anda butuhkan. Dalam contoh kita, kita ingin kolom 2 dari Kode Partai worksheet yang memiliki nama partai politik.

Range-lookup - bidang ini mendefinisikan seberapa dekat pertandingan harus ada antara lookup_value Anda (C2) dan nilai di kolom paling kiri pada tabel kami. Dalam kasus kami, kami ingin yang sama persis jadi kita akan menggunakan "FALSE".

Setelah mengklik berbagai sel, dialog saya terlihat seperti ini:

Anda dapat melihat di dilingkari formula bar di atas, saya sekarang memiliki informasi lebih lanjut berdasarkan masukan saya di kotak dialog Function Arguments.

Item lain yang menarik adalah bahwa ketika Anda membangun fungsi-fungsi ini, Excel akan menampilkan hasilnya dalam hasil Formula = baris teks. Ini adalah tanggapan yang bagus yang dapat menunjukkan apakah fungsi Anda pada target. Dalam contoh kita, kita dapat melihat Excel mendongak pcode dari "A" dan kembali Partai Politik "Demokrasi".

Menyalin Fungsi VLOOKUP untuk Sel Lain

Ini tidak masuk akal untuk menggunakan VLOOKUP untuk satu sel dalam spreadsheet Excel. Sebaliknya, saya ingin menyalin fungsi sel-sel lain dalam kolom yang sama.

Untuk menyalin VLOOKUP ke sel-sel kolom lainnya,

1. Klik sel yang berisi argumen VLOOKUP. Dalam contoh kita, ini akan menjadi D2.

2. Ambil menangani sel yang menampilkan di sudut kanan bawah.

3. Klik-kiri dan tarik ke bawah pegangan sel untuk mencakup rentang kolom Anda.

Catatan: Jika saya tidak berubah menjadi referensi mutlak sebagaimana dimaksud dalam Peraturan 3, saya akan pernah melihat saya tabel Array pergeseran masuk oleh satu sel seperti yang kita terseret melalui sel-sel lain.

VLOOKUP adalah fungsi Excel yang kuat yang dapat memanfaatkan data spreadsheet dari sumber lain. Ada banyak cara Anda bisa mendapatkan keuntungan dari fungsi ini. Dalam contoh ini, saya menggunakan kode terjemahan 1:1, tetapi Anda juga bisa menggunakannya untuk tugas kelompok. Misalnya, Anda bisa menetapkan kode negara ke daerah seperti CT, VT, dan MA ke wilayah yang disebut "New England". Dan untuk para petualang, Anda dapat menggunakan VLOOKUP di Excel formula Anda.