Mengakses data dari Sheet lain pada file yang berbeda

Mengakses data dari Sheet lain pada file yang berbeda

Pada Excel kita dapat dengan mudah untuk mengambil data dari sheet maupun dari file lain yang tersimpan dalam bentuk Excel juga. Ini tentu sangan berguna sekali pada saat kita membutuhkan sejumlah data tertentu dari pengolahan data dari sheet ataupun file lainnya. Selain itu kita dapat mengelompokkan data dalam kriterian yang lebih sistematis dan memanggilnya sesuai kebutuhan pada file ata sheet yang baru.

Caranya untuk mengambil data dari sheet lain:

1. Misalnya sheet yang anda aktivkan saat ini adalah Sheet2, dan anda membutuhkan data dariSheet1 pada range G3 maka yang perlu dilakukan adalah pilih range (D2) yang ingin ditampilkan datanya (pada Sheet2) lalu ketik =Sheet1!G3, maka data pada Sheet1 di range G3 akan sama dengan data di Sheet2 range D2
2. Dan jika anda ingin memindahkan sejumlah range dari Sheet1 ke Sheet2 anda tinggal melakukan cara diatas atau copy paste atau klik fill handlenya di D2 kemudian klik dan drag hingga range yang diinginkan (misalnya I2)
3. Data pada Sheet2 akan selalu terhubung dengan Sheet1, sehingga setiap perubahan yang terjadi pada Sheet1 maka secara otomatis akan berubah pada Sheet2

Caranya untuk mengambil data dari file lain:

1. Untuk mengambil data dari file lain sebenarnya hampir sama dengan penjelasan diatas (mengambil data dari sheet lain) yaitu letakkan kursor pada range yang diinginkan misalZ24, lalu ketik rumus =[data1.xlsx]Sheet1!S5. *)Selanjutnya tekan enter. penjelasanya [data1.xlsx] merupakan nama file, Sheet1 adalah nama Sheet yang kita ambil sedangkan S5merupakan range datanya.
2. Jika anda kesulitan untuk menentukan nama file yang akan dambil jika namanya relatif panjang maka pertama yang perlu anda lakukan adalah aktivkan kedua file (file yang akan diambil datanya dan file tempat menempatkan data baru), pada file, sheet dan rangetempat dimana data akan ditempatkan anda ketik =, selanjutnya pindah kursor dan aktivkan jendela file yang akan diabil datanya lalu tentukan klik sheetnya dan range tempat data berada, kemudian tekan Enter, maka data di data2.xlsx akan sama dengan data di file data1.xlsx sesuai sheet dan range yg diinginkan.
3. Data di file data2.xlsx ini juga terhubung langsung dengan file di data1.xlsx Jadi jika terjadi perubahan di data1.xlsx maka data di file data2.xlsx juga akan berubah

Cara mengambil data dari sheet lain

        Pada Excel kita dapat dengan mudah untuk mengambil data dari sheet maupun lain yang tersimpan dalam bentuk Excel juga. Ini tentu sangan berguna sekali pada saat kita membutuhkan sejumlah data tertentu dari pengolahan data dari sheet lain. Selain itu kita dapat mengelompokkan data dalam kriterian yang lebih sistematis dan memanggilnya sesuai kebutuhan pada file atau sheet yang baru.

Caranya untuk mengambil data dari sheet lain:

1. Misalnya sheet yang anda aktivkan saat ini adalah Sheet2, dan anda membutuhkan data dari Sheet1 pada rangeG3 maka yang perlu dilakukan adalah pilih range (D2) yang ingin ditampilkan datanya (pada Sheet2) lalu ketik=Sheet1!G3, maka data pada Sheet1 di range G3 akan sama dengan data di Sheet2 range D2
2. Dan jika anda ingin memindahkan sejumlah range dari Sheet1 ke Sheet2 anda tinggal melakukan cara diatas atau copy paste atau klik fill handlenya di D2 kemudian klik dan drag hingga range yang diinginkan (misalnya I2)
3. Data pada Sheet2 akan selalu terhubung dengan Sheet1, sehingga setiap perubahan yang terjadi pada Sheet1 maka secara otomatis akan berubah pada Sheet2.

Mengakses Format Yang Sama di Beberapa Worksheet Secara Cepat

Pada workbook di Excel sebenarnya worksheet-worksheet yang ada saling terkait antara satu dengan yang lainnya, walau worksheet tersebut dapat memiliki struktur yang berbeda.

Berikut ini akan dicontohkan cara membuat form secara sederhana yang dibuat untuk beberapa worksheet sekaligus, sehingga Anda tidak melakukan berulang-ulang pada sheet yang berbeda atau tidak harus mengcopy dari sheet yang satu ke sheet yang lain.

Kali ini saya akan membuatkan contoh membuat beberapa sheet dengan satu form yang sama secara mudah dan sebagai contoh adalah cara membuat daftar nilai siswa dengan beberapa kelas dengan sekali buat.

Pertama siapkan worksheet sesuai jumlah kelas yang ada misalnya lima sheet.

Kemudian beri nama masing-masing worksheet tersebut, misalnya kelas X MM, Kelas X TKJ, Kelas X DKV, Kelas X AK dan Kelas X TB. Seperti contoh berikut

Kemudian klik pada salah satu sheet, lalu tekan tombol SHIFT dan pilih sheet lainnya yang akan dibuatkan format yang sama.

Setelah itu buatlah format yang Anda inginkan seperti contoh berikut:

Apabila Anda selesai melakukan pemformatan sheet tersebut, maka Anda akan mendapatkan bahwa semua sheet yang Anda klik tadi telah memiliki format yang sama.

Anda dapat mengklik satu persatu sheetnya untuk membuktikan bahwa setiap sheet yang Anda aktifkan secara bersamaan tadi memiliki format yang sama.

HLOOKUP

Fungsi HLOOKUP Pada Excel 2010

oleh Usman pada 10 Maret 2010

Excel fungsi HLOOKUP dapat digunakan ketika Anda berurusan dengan datasheet besar dan kebutuhan untuk mencari nilai-nilai dari catatan tertentu. HLOOKUP singkatan lookup Horisontal, yang digunakan untuk mencari data tertentu dari datasheet. Tidak seperti VLOOKUP fungsi, mencari nilai-nilai horizontal yang memungkinkan pengguna untuk mengekstrak catatan lengkap. Posting ini menjelaskan bagaimana & di mana Anda dapat menggunakan fungsi HLOOKUP.

Peluncuran Excel 2010, dan membuka datasheet di mana Anda ingin menerapkan fungsi HLOOKUP.
Sebagai contoh, kami telah menyertakan siswa lembar penilaian yang berisi bidang kursus nama dan nama field yang berisi para siswa seperti yang ditunjukkan pada gambar di bawah.

Sekarang kita ingin menerapkan fungsi HLOOKUP, untuk ini kita akan mulai dengan menambahkan baris baru di mana kita akan mencari untuk data siswa tertentu. Misalkan kita berhadapan dengan datasheet raksasa yang mengandung banyak catatan mahasiswa, dalam skenario ini jika kita ingin mencari untuk catatan siswa Jack, maka kita akan menambahkan baris label dengan nama mahasiswa dan mulai menarik tanda ia diamankan dari setiap kolom Kursus masing-masing; Engg Software misalnya, Se II dll

Sekarang kita akan menulis rumus berdekatan dengan sel Jack, seperti fungsi VLOOKUP, tampaknya untuk data horizontal, Sintaks fungsi HLOOKUP adalah;

= HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

Parameter pertama dari lookup_value rumus mendefinisikan nilai dari jumlah yang kita akan melihat di baris yang ditentukan. Kita perlu lookup tanda dalam sel yang baru dibuat. Parameter selanjutnya, table_array mendefinisikan array tabel kita akan mengacu dalam kasus kami akan bagian dari datasheet label kursus mengandung, dengan data yang sesuai (program dengan tanda dijamin). r ow_index_num mendefinisikan, yang berturut-turut kami ingin mengekstrak nilai untuk menunjukkan, dalam kasus kami itu adalah baris kedua yang berisi tanda kursus masing-masing. [range_lookup] memungkinkan Anda untuk memilih opsi baik TRUE (sekitar pencocokan nilai) atau FALSE (pencocokan Exact nilai-nilai).
Kami akan menulis fungsi ini dalam sel yang berdekatan dengan sel Jack, ia akan pergi seperti ini;

= HLOOKUP (B1, $ B $ 1: $ H $ 11,2, FALSE)

Dalam parameter rumus, B1 adalah sel Engg kolom Software yang berisi tanda dijamin oleh Jack mahasiswa, B1: H11 adalah array data yang berisi semua label kursus dan nilai masing-masing (menggunakan referensi mutlak dengan tanda $), 2 di Rumus berarti bahwa nilai-nilai dari baris kedua akan muncul, dan FALSE mendefinisikan bahwa kita perlu sama persis.
Seperti yang dapat Anda lihat pada screen shot di bawah itu, rumus akan menghasilkan 18 tanda dijamin oleh Jack dalam kursus Engg Software (di lokasi B1)

Sekarang jika Anda ingin tahu tanda dijamin oleh Jack di setiap kursus, hanya tarik tanda plus di seluruh baris, seperti yang ditunjukkan pada gambar di bawah.

Ada cara lain yang menarik di mana fungsi HLOOKUP dapat digunakan, dalam hal ini kita akan membuat tabel eksklusif untuk mahasiswa jack dan mencari untuk tanda, dengan menyebutkan nama saja, seperti yang ditunjukkan pada gambar di bawah.

Rumus keseluruhan adalah sama kecuali parameter pertama (A44), sebenarnya mengacu ke sel di mana akan menulis nama saja. Sekarang ketika kita akan menentukan nama saja, sesuai tanda nilai otomatis akan muncul.

Sekarang menerapkannya di atas meja dan membuat satu eksklusif untuk mahasiswa Jack.

Anda juga dapat memeriksa panduan kami sebelumnya ditinjau Fungsi VLOOKUP Pada Excel & Cara embed video di Excel 2010 .

VLOOKUP

Menggunakan VLOOKUP di Excel

VLOOKUP adalah salah satu fungsi Excel yang paling berguna, dan itu juga salah satu yang paling sedikit dipahami. Pada artikel ini, kita demystify VLOOKUP dengan cara contoh kehidupan nyata. Kami akan membuat Faktur Template digunakan untuk sebuah perusahaan fiktif.
Jadi apa VLOOKUP? Yah, tentu saja itu fungsi Excel. Artikel ini akan mengasumsikan bahwa pembaca sudah memiliki pemahaman yang lewat fungsi Excel, dan dapat menggunakan fungsi dasar seperti SUM, AVERAGE, dan TODAY. Dalam penggunaan yang paling umum, VLOOKUP adalah fungsi database, yang berarti bahwa ia bekerja dengan tabel database - atau lebih sederhana, daftar hal-hal dalam lembar kerja Excel. Hal apa saja? Nah, apapun hal. Anda mungkin memiliki worksheet yang berisi daftar karyawan, atau produk, atau pelanggan, atau CD dalam koleksi CD Anda, atau bintang-bintang di langit malam. Itu tidak terlalu penting.
Berikut ini adalah contoh dari daftar, atau database. Dalam hal ini adalah daftar produk yang perusahaan fiktif kami menjual:

Biasanya daftar seperti ini memiliki semacam pengenal unik untuk setiap item dalam daftar. Dalam kasus ini, pengenal unik ada di kolom "Item Code". Catatan: Untuk fungsi VLOOKUP untuk bekerja dengan database / list, daftar yang harus memiliki kolom yang berisi pengenal unik (atau "kunci", atau "ID"), dan kolom yang harus kolom pertama dalam tabel. Sampel Database kami atas memenuhi kriteria ini.
Bagian tersulit dari menggunakan VLOOKUP adalah memahami apa itu untuk. Jadi mari kita lihat apakah kita bisa mendapatkan yang jelas pertama:

VLOOKUP mengambil informasi dari daftar database / berdasarkan contoh yang diberikan dari identifier yang unik.

Dengan kata lain, jika Anda menempatkan fungsi VLOOKUP ke dalam sel dan menyebarkannya salah satu pengidentifikasi unik dari database Anda, itu akan kembali Anda salah satu dari potongan-potongan informasi yang terkait dengan identifier unik. Dalam contoh di atas, Anda akan melewati VLOOKUP kode item, dan itu akan kembali kepada Anda baik deskripsi item yang sesuai, harga, atau ketersediaan (nya "Stok" kuantitas). Manakah dari potongan-potongan informasi itu akan melewati Anda kembali? Nah, Anda bisa memutuskan ini ketika Anda sedang menciptakan formula.
Jika semua yang Anda butuhkan adalah salah satu bagian dari informasi dari database, akan banyak kesulitan untuk pergi ke untuk membangun formula dengan fungsi VLOOKUP di dalamnya. Biasanya Anda akan menggunakan fungsi semacam ini dalam spreadsheet dapat digunakan kembali, seperti template. Setiap kali seseorang memasuki kode item yang valid, sistem akan mengambil semua informasi yang diperlukan tentang item yang sesuai.
Mari kita membuat contoh ini: Sebuah Template Faktur bahwa kita dapat menggunakan kembali berulang di perusahaan fiktif kami.
Pertama kita mulai Excel ...

... Dan kita menciptakan diri kita faktur kosong:

Ini adalah bagaimana itu akan bekerja: Orang menggunakan template faktur akan mengisi serangkaian kode item dalam kolom "A", dan sistem akan mengambil deskripsi setiap item dan harga, yang akan digunakan untuk menghitung total baris untuk masing-masing item (dengan asumsi kita memasuki jumlah valid).
Untuk tujuan menjaga contoh sederhana ini, kita akan menemukan database produk pada lembar terpisah dalam buku kerja yang sama:

Pada kenyataannya, itu lebih mungkin bahwa database produk akan berada dalam sebuah kerja yang terpisah. Itu membuat sedikit perbedaan dengan fungsi VLOOKUP, yang tidak benar-benar peduli jika database terletak pada lembar yang sama, lembaran yang berbeda, atau buku kerja yang sama sekali berbeda.

Untuk menguji rumus VLOOKUP kita akan menulis, pertama-tama kita masukkan kode item yang valid ke dalam sel A11:

Selanjutnya, kami memindahkan sel yang aktif ke sel di mana kita ingin informasi yang diambil dari database dengan VLOOKUP untuk disimpan. Menariknya, ini adalah langkah yang kebanyakan orang bisa salah. Untuk menjelaskan lebih lanjut: Kami akan membuat rumus VLOOKUP yang akan mengambil deskripsi yang sesuai dengan kode item dalam sel A11. Kita ingin penjelasan ini menempatkan ketika kita mendapatkannya? Dalam sel B11, tentu saja. Jadi, di sanalah kita menulis rumus VLOOKUP - dalam sel B11.
Pilih cell B11:

Kita perlu untuk menemukan daftar semua fungsi yang tersedia yang ditawarkan Excel, sehingga kita bisa memilih VLOOKUP dan mendapatkan beberapa bantuan dalam menyelesaikan formula. Hal ini ditemukan pertama dengan mengklik tab Rumus, dan kemudian klik Insert Fungsi:


Sebuah kotak akan muncul yang memungkinkan kita untuk memilih salah satu fungsi yang tersedia di Excel. Untuk menemukan yang kita cari, kita bisa mengetik istilah pencarian seperti "lookup" (karena fungsi kita tertarik adalah fungsi lookup). Sistem ini akan kembali kita daftar semua pencarian yang berhubungan dengan fungsi dalam Excel. VLOOKUP adalah yang kedua dalam daftar. Pilih itu suatu OK klik ...

Fungsi Argumen kotak muncul, mendorong kami untuk semua argumen (atau parameter) yang diperlukan untuk melengkapi fungsi VLOOKUP. Anda dapat menganggap ini sebagai kotak fungsi ini meminta kami pertanyaan-pertanyaan berikut:

1. Apa pengenal unik yang Anda cari dalam database?
2. Dimana database?
3. Yang sepotong informasi dari database, yang berkaitan dengan identifier unik, apakah Anda ingin memiliki diambil untuk Anda?

Tiga pertama argumen yang ditampilkan dalam huruf tebal, menunjukkan bahwa mereka adalah argumen wajib (fungsi VLOOKUP tidak lengkap tanpa mereka dan tidak akan kembali nilai yang valid). Argumen keempat adalah tidak berani, yang berarti bahwa itu opsional:

Kami akan menyelesaikan argumen dalam rangka, atas ke bawah.
Argumen pertama yang perlu kita menyelesaikan adalah argumen lookup_value. Fungsi kebutuhan kita untuk mengatakan itu di mana untuk menemukan identifikasi unik (item kode dalam kasus ini) bahwa itu harus retuning deskripsi. Kita harus memilih kode barang yang kita masukkan sebelumnya (dalam A11).
Klik pada ikon pemilih di sebelah kanan argumen pertama:

Kemudian klik sekali pada sel yang berisi kode barang (A11), dan tekan Enter:

Nilai "A11" dimasukkan ke dalam argumen pertama.
Sekarang kita harus memasukkan nilai untuk argumen table_array. Dengan kata lain, kita perlu memberitahu VLOOKUP di mana untuk menemukan database / list. Klik pada ikon pemilih di samping argumen kedua:

Sekarang cari database / daftar dan pilih seluruh daftar - tidak termasuk baris header. Database terletak pada lembar kerja yang terpisah, jadi kami pertama klik pada tab lembar kerja:

Selanjutnya kita pilih seluruh database, tidak termasuk baris header:

... Dan tekan Enter. Kisaran sel yang mewakili database (dalam hal ini "'Produk Database' A2:! D7") yang dimasukkan secara otomatis untuk kita ke argumen kedua.
Sekarang kita harus memasukkan argumen ketiga, Col_index_num. Kami menggunakan argumen ini untuk menentukan ke mana VLOOKUP sepotong informasi dari database, kaitkan dengan kode barang kami di A11, kami ingin telah kembali kepada kami. Dalam contoh khusus ini, kami ingin memiliki deskripsi item kembali kepada kami. Jika Anda melihat pada lembar kerja database, Anda akan melihat bahwa "Keterangan" kolom adalah kolom kedua dalam database. Ini berarti bahwa kita harus memasukkan nilai "2" ke dalam kotak Col_index_num:

Penting untuk dicatat bahwa kita tidak memasuki "2" di sini karena "Keterangan" kolom di kolom B pada lembar kerja tersebut. Jika database terjadi mulai di K kolom worksheet, kita masih akan memasukkan "2" di bidang ini.
Akhirnya, kita perlu memutuskan apakah akan memasukkan nilai ke dalam VLOOKUP argumen range_lookup akhir,. Argumen ini membutuhkan baik nilai benar atau salah, atau harus dibiarkan kosong. Bila menggunakan VLOOKUP dengan database (seperti yang berlaku 90% dari waktu), maka cara untuk memutuskan apa yang akan dimasukkan ke dalam argumen ini dapat dianggap sebagai berikut:

Jika kolom pertama dari database (kolom yang berisi pengidentifikasi unik) yang diurutkan sesuai abjad / numerik dalam urutan, maka mungkin untuk memasukkan nilai yang benar dalam argumen ini, atau biarkan kosong.
Jika kolom pertama dari database tidak diurutkan, atau itu diurutkan dalam urutan, maka Anda harus memasukkan nilai palsu ke dalam argumen ini

Sebagai kolom pertama dari database kami tidak diurutkan, kita memasuki palsu ke dalam argumen ini:

Itu saja! Kami telah memasukkan semua informasi yang diperlukan untuk VLOOKUP untuk mengembalikan nilai yang kita butuhkan. Klik tombol OK dan perhatikan bahwa deskripsi yang sesuai dengan kode barang "R99245" telah dimasukkan dengan benar ke dalam sel B11:

Rumus yang diciptakan bagi kita terlihat seperti ini:

Jika kita memasukkan kode item yang berbeda ke dalam sel A11, kita akan mulai melihat kekuatan fungsi VLOOKUP: Perubahan deskripsi Sel untuk mencocokkan kode item baru:

Kita dapat melakukan satu set sama langkah-langkah untuk mendapatkan harga item kembali ke dalam sel E11. Perhatikan bahwa formula baru harus dibuat dalam sel E11. Hasilnya akan terlihat seperti ini:

... Dan rumus akan terlihat seperti ini:

Perhatikan bahwa satu-satunya perbedaan antara keduanya adalah rumus argumen ketiga (Col_index_num) telah berubah dari "2" ke "3" (karena kita ingin data diambil dari kolom ke-3 dalam database).
Jika kita memutuskan untuk membeli 2 item ini, kita akan memasukkan "2" ke dalam sel D11. Kami kemudian akan memasukkan rumus sederhana ke dalam sel F11 untuk mendapatkan total baris:

= D11 * E11

... Yang terlihat seperti ini ...

Melengkapi Template Faktur

Kami telah belajar banyak tentang VLOOKUP sejauh ini. Bahkan, kami telah belajar semua kita akan belajar dalam artikel ini. Sangat penting untuk dicatat bahwa VLOOKUP dapat digunakan dalam situasi lain selain database. Hal ini kurang umum, dan dapat dilindungi di masa depan How-To Geek artikel.
Template faktur kami belum lengkap. Untuk menyelesaikannya, kita akan melakukan hal berikut:

1. Kami akan menghapus kode item sampel dari sel A11 dan "2" dari sel D11. Hal ini akan menyebabkan rumus VLOOKUP baru dibuat kami untuk menampilkan pesan error:
   
   Kita bisa memperbaiki ini dengan bijaksana penggunaan Excel IF () dan ISBLANK () fungsi. Kami mengubah formula kami dari ini ... = VLOOKUP (A11, 'Produk Database' A2:! D7, 2, FALSE)
   ... Ini ...
   = IF (ISBLANK (A11), "", VLOOKUP (A11, 'Produk Database' A2:! D7, 2, FALSE))
   
2. Kami akan menyalin formula dalam sel B11, E11 dan F11 ke sisa baris item faktur. Perhatikan bahwa jika kita melakukan hal ini, rumus yang dihasilkan akan tidak lagi benar merujuk ke tabel database. Kita bisa memperbaikinya dengan mengubah referensi sel untuk database untuk referensi sel absolut. Atau - dan bahkan lebih baik - kita bisa membuat nama range untuk database seluruh produk (seperti "Produk"), dan menggunakan nama rentang bukan referensi sel. Rumus akan berubah dari ini ... = IF (ISBLANK (A11), "", VLOOKUP (A11, 'Produk Database' A2:! D7, 2, FALSE))
   ... Ini ...
   = IF (ISBLANK (A11), "", VLOOKUP (A11, Produk, 2, FALSE))
   ... Dan kemudian salin rumus ke seluruh baris barang faktur.
3. Kami mungkin akan "mengunci" sel-sel yang mengandung formula kita (atau lebih tepatnya membuka sel lain), dan kemudian melindungi worksheet, dalam rangka untuk memastikan bahwa formula kami hati-hati dibangun tidak sengaja ditimpa ketika seseorang datang untuk mengisi faktur.
4. Kami akan menyimpan file sebagai template, sehingga bisa digunakan kembali oleh setiap orang di perusahaan kami

Jika kita merasa benar-benar pintar, kita akan membuat sebuah database dari semua pelanggan kami dalam worksheet lain, dan kemudian menggunakan ID pelanggan dimasukkan dalam sel F5 untuk secara otomatis mengisi nama pelanggan dan alamat dalam sel B6, B7 dan B8.

Jika Anda ingin berlatih dengan VLOOKUP, atau hanya melihat Template Faktur yang dihasilkan kami, dapat didownload dari sini .

DATA VALIDATION

Mencari Data Tertentu Menggunakan Data List Validation

Dalam aplikasi Excel, mungkin Anda sudah tahu dan sering menggunakan fasilitas pencarian data yang biasa Anda akses dengan menekan kombinasi tombol Ctrl+F, namun hasil pencarian tersebut hanya sekedar memindahkan penunjuk cell pada cell yang mengandung data yang dicari tersebut. Nah berdasar dari itu, tulisan saya kali ini akan berbagi tentang pencarian data menggunakan Data Validation dan sedikit sentuhan Conditional Formatting yang mana hasil dari pencarian tersebut ditampilkan lebih “terlihat”. Cara membuatnya adalah sebagai berikut :
1. Sebagai contoh, saya menggunakan data seperti berikut :













2. Di bawah judul tabel tersebut, saya akan menambahkan Data Validation berbentuk dropdown list yang nantinya digunakan untuk pencarian data dari tabel  dibawahnya. namun sebelumnya tambahkan tabel bantu untuk Data Validation yang berisi Nama Barang seperti berikut :

3. Pada cell A3 isikan dengan “Cari Barang”, lalu tempatkan penunjuk cell di cell B3. Pada tab Data kategori Data Tools pilih Data Validation sehingga muncul kotak dialog Data Validation.
4. Pada list box Allow di tab Setting bagian Validation Criteria, pilih List.
5. Pada kotak isian Source isikan alamat tabel bantu yang telah dibuat tadi, yaitu H3:H11, Anda dapat mengisikan alamat tersebut dengan klik ikon disebelah kanan kotak isian Source, lalu sorot isi tabelnya dan klik kembali ikonnya. Alamat yang tertulis yaitu $H$3:$H$11 artinya itu merupakan alamat Absolut. Jika sudah Klik OK, sehingga kini cell B3 berbentuk dropdown list.

6. Pada tab Home kategori Sytle pilih Conditional Formatting > Manage Rules, sehingga muncul kotak dialog Conditional Formatting Rules Manager.

7. Klik New Rule, sehingga muncul kotak dialog New Formatting Rule, dan isikan seperti gambar berikut :

8. Arti dari format di atas adalah jika isi cell B3 sama dengan isi cell B6 maka cell B6 akan diberi fill warna orange. Jika sudah klik OK.
9. Pada kotak dialog Conditional Formatting Rules Manager, kotak isian Applies to isikan alamat tabel dengan cara yang sama seperti sebelumnya, seperti gambar berikut :

10. Klik Apply dan OK.
Sekarang kita coba buktikan, pilih salah satu nama barang pada cell B3, maka yang terjadi adalah pada Record yang isi yang sama dengan cell B3 akan ditandai dengan warna orange, seperti gambar berikut :

Oke, Sekian tip untuk kali ini, mudah-mudahan bermanfaat dan dapat diaplikasikan sesuai kebutuhan Anda
Sebagai bahan pembelajaran, saya lampirkan file yang dicontohkan tadi dan dapat di download disini.
Posted: Jun 07 2011, 12:34 PM by Raddini Gusti Rahayu | with 3 comment(s) Filed under: Excel, dropdown list, find, conditional formatting, data validation

FILTER

Cepat start: Data Filter dengan menggunakan AutoFilter

Dengan menyaring informasi dalam worksheet, Anda dapat menemukan nilai-nilai dengan cepat. Anda dapat menyaring pada satu atau lebih kolom data. Dengan penyaringan, Anda dapat mengontrol tidak hanya apa yang ingin Anda lihat, tapi apa yang ingin Anda kecualikan. Anda dapat menyaring berdasarkan pilihan yang Anda buat dari daftar, atau Anda dapat membuat filter khusus untuk fokus pada persis data yang ingin Anda lihat.
Anda dapat mencari teks dan angka ketika Anda menyaring dengan menggunakan kotak Cari dalam antarmuka filter.
Bila Anda menyaring data, baris seluruh tersembunyi jika nilai-nilai dalam satu atau lebih kolom tidak memenuhi kriteria penyaringan. Anda dapat menyaring pada nilai-nilai numerik atau teks, atau menyaring dengan warna untuk sel yang memiliki format warna yang diterapkan pada latar belakang atau teks.

Bagaimana?

Pilih data yang ingin Anda filter Pada tab Data, di grup Sortir & Filter, klik Filter. Klik panah di header kolom untuk menampilkan daftar di mana Anda dapat membuat pilihan filter. Catatan Tergantung pada jenis data dalam kolom, Microsoft Excel menampilkan Filter Nomor baik atau Filter Teks dalam daftar.

Filter dengan memilih nilai-nilai atau mencari Nilai memilih dari daftar dan pencarian adalah cara tercepat untuk menyaring. Ketika Anda klik tanda panah di kolom yang telah penyaringan diaktifkan, semua nilai dalam kolom yang muncul dalam daftar. Gunakan kotak Cari untuk memasukkan teks atau angka yang untuk mencari Pilih dan mengosongkan kotak centang untuk menunjukkan nilai-nilai yang ditemukan dalam kolom data Gunakan kriteria canggih untuk menemukan nilai-nilai yang memenuhi kondisi tertentu Untuk memilih oleh nilai-nilai, dalam daftar, kosongkan kotak centang (Pilih Semua). Ini menghilangkan tanda cek dari semua kotak centang. Kemudian, pilih hanya nilai-nilai yang ingin Anda lihat, dan klik OK untuk melihat hasilnya. Untuk mencari teks dalam kolom, memasukkan teks atau angka dalam kotak pencarian. Opsional, Anda dapat menggunakan karakter wildcard, seperti tanda bintang (*) atau tanda tanya (?). Tekan ENTER untuk melihat hasilnya.

Filter data dengan kondisi menentukan Dengan kondisi menentukan, Anda dapat membuat filter kustom yang mempersempit data dalam cara yang tepat yang Anda inginkan. Anda melakukan ini dengan membangun filter. Jika Anda pernah bertanya data dalam database, hal ini akan tampak akrab bagi Anda. Arahkan ke salah satu nomor atau Filter Filter Teks dalam daftar. Sebuah menu akan muncul yang memungkinkan Anda untuk menyaring pada berbagai kondisi. Pilih kondisi dan kemudian pilih atau masukkan kriteria. Klik tombol Dan untuk menggabungkan kriteria (yaitu, dua atau lebih kriteria yang harus dipenuhi baik), dan Atau tombol untuk hanya memerlukan satu dari beberapa kondisi yang harus dipenuhi. Klik OK untuk menerapkan filter dan mendapatkan hasil yang Anda harapkan.

Langkah selanjutnya

• Menonton video untuk melihat penyaringan dalam tindakan: Video: Filter data dengan menggunakan AutoFilter suatu
• Percobaan dengan filter pada teks dan data numerik dengan mencoba built-in berbagai kondisi pengujian, seperti Setara, Apakah Tidak Equal, Berisi, Greater Than, dan Less Than. Untuk informasi lebih lanjut, lihat data yang Filter dalam berbagai atau tabel .

Perhatikan Beberapa dari kondisi ini hanya berlaku untuk teks, dan lain-lain hanya berlaku untuk nomor.

• Buat filter kustom yang menggunakan beberapa kriteria. Untuk informasi lebih lanjut, lihat Filter dengan menggunakan kriteria yang canggih .
• Pelajari cara Filter untuk nilai-nilai yang unik atau menghapus nilai-nilai duplikat .