Mengakses data pada sheet lain, file sama

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.

Cara lain 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

Fungsi HLookUp Microsoft Excel

Fungsi HLookUp Dan VLookUp seyogyanya berguna untuk mengambil data dari tabel dengan range tertentu. Fungsi HLookUp berguna untuk mengambil data dalam tabel secara Horizontal dengan rumus umum sebagai berikut :

=HLookUp(lookup_value;table_array;row_index_num;[range_lookup])

atau diterjemahkan sebagai berikut:

=Hlookup(data yang dicari;tabel;baris_yang_diingikan;[range])

Dengan pengertian bahwa kita akan mencari / mengambil data dari sebuah tabel secara Horizontal, horizontal disini maksudnya adalah kita mengambil data berdasarkan nilai baris dalam Microsoft Excel. Untuk lebih jelasnya perhatikan contoh dibawah ini :

Buatlah tabel seperti contoh di atas dengan data entri 5 saja, kemudian buat percobaan pengambilan data dengan membuat bagian Baris pada F10 dan isi baris pada G10. Untuk melihat hasilnya kita buat “Hasil yang ditemukan” pada D12, dan hasilnya pada E12. E12 di isi dengan rumus sebagai berikut :

E12=HLOOKUP(C4;C4:D8;G10)

Maksud dari rumus diatas adalah kita akan mengambil data dimulai dari C4, dengan data tabel dari  C4 s/d D8. G10 dalam rumus adalah baris yang akan kita ambil datanya. Di G10 bisa kita ubah datanya untuk melihat hasilnya, dalam contoh diatas G10 diisi dengan nilai 2 dan hasilnya adalah Rio.
Perlu di ingat bahwa kita tidak hanya dapat mengambil data tabel dalam satu sheet saja tetapi juga bisa berbeda sheet dengan menambahkan

namasheet!Data

Contoh :

Sheet2!C4

Cara Menggunakan Fungsi VLOOKUP Excel

Bagi sebagian orang bekerja dengan menggunakan komputer adalah sebuah tuntutan dan harus dijalani. Ada banyak sekali pekerjaan yang menggunakan komputer, bahkan kebanyakan sekarang ini semua pekerjaan yang bertempat di dalam ruangan atau sebuah kantor pasti akan menggunakan perangkat komputer. Sebuah komputer dapat membantu pekerjaan Anda dengan mudah bahkan dengan komputer pekerjaan yang sangat banyak bisa terselesaikan dengan cepat.
Jika Anda sering melakukan pendataan pada pekerjaan Anda, maka penggunaan program Microsoft Excel mungkin tidak asing lagi bagi Anda karena excel telah dipercaya untuk menangani berbagai jenis pekerjaan seperti mendata barang, keuangan, database atau laporan penjualan hari ini. Ada banyak fungsi di Microsoft excel yang bisa membantu Anda, seperti Anda dapat dengan mudah melakukan penjumlahan dengan angka yang sangat banyak atau proses pencocokan data, semuanya dapat dikerjakan secara otomatis dengan berbagai fungsi yang tersedia di Microsoft excel.

Kenapa Menggunakan Fungsi VLOOKUP?

Salah satu fungsi yang sangat bermanfaat di Microsoft excel adalah VLOOKUP. Vlookup adalah fungsi untuk mengetahui nilai dalam sebuah index. Sebagai contoh sederhana Anda memiliki beberapa data tentang harga-harga produk, kolom pertama berisi nama produk, kolom kedua berisi harga produk.
Ketika Anda ingin mengetahui harga dari sebuah produk Anda tinggal menggunakan fungsi vlookup ini. Mungkin terlihat sepele, tetapi bagaimana jika data yang Anda miliki berjumlah ratusan atau bahkan ribuan, vlookup akan sangat berguna karena dapat mengetahui sebuah nilai data dengan cepat dibandingkan jika Anda harus mengecek table satu persatu yang pastinya akan memakan waktu yang sangat lama. Untuk mempelajarinya lebih lanjut berikut ini adalah contoh penggunaan fungsi vlookup secara sederhana:

Contoh Cara Penggunaan Fungsi VLOOKUP (Video):

Contoh Cara Penggunaan Fungsi VLOOKUP (Pics):

Pertama-tama, perhatikan contoh gambar berikut ini :

Dari data diatas dapat dilihat yaitu data nilai ujian dari beberapa anak, sebagai contoh Anda ingin mengetahui nilai dari anak yang bernama wendi, maka yang harus Anda lakukan selanjutnya menulis rumusan =VLOOKUP(A11,A2:B15,2,FALSE). Untuk penjelasan dari rumusan tersebut adalah sebagai berikut, sebagai contoh Anda ingin menulis nilai wendi pada kolom lainnya seperti pada gambar berikut:

Berarti Anda akan menuliskan nilai wendi pada kolom E15, ini berarti Anda harus menuliskan rumusan pada kolom E15, selanjutnya ketik =VLOOKUP(A11,A2:B15,2,FALSE) pada kolom E15, setelah itu Anda akan langsung mendapatkan nilai wendi pada kolom E15 seperti berikut:

Penjelasan Fungsi VLOOKUP Pada Contoh

Selanjutnya kita membahas nilai dari rumusan tadi yaitu =VLOOKUP(A11,A2:B15,2,FALSE). =VLOOKUP adalah kata kunci dari perintah vlookup jadi ini merupakan hal yang paling penting. Selanjutnya A11, yaitu kolom dimana nama Wendi berada itu berarti nama data yang kita akan cari. Lalu A2:B15 yang berarti kolom A2 sampai B15 dari kiri ke kanan dan dari atas ke bawah, tempat seluruh database nama dan nilai terdapat, dalam hal ini dimulai dari budi hingga yana dan 85 hingga 60.
Lalu selanjutnya arti angka 2 yaitu merupakan kolom ke dua atau kolom B tempat dimana terdapat nilai wendi. Dan terakhir FALSE, jika data yang terdapat pada table nilai merupakan angka yang berurutan seperti 50, 60, 75, 85, 90 dst maka Anda bisa mengetikkan kata TRUE, tetapi jika angkanya tidak berurutan maka Anda harus menggunakan kata FALSE.
Selain itu vlookup juga dapat digunakan antar sheet yang berbeda. Yaitu Anda bisa mendapatkan data dari referensi database yang berada di sheet lain, jelas fungsi vlookup akan sangat membantu pekerjaan mendata Anda menjadi lebih mudah dan tentunya jika data Anda sangat banyak. Jadi silahkan mempelajarinya dan menggunakannya, selamat mencoba.

Cara Menggunakan Fungsi VLOOKUP Excel – Penutup

Demikian penjelasan cara dan contoh singkat tentang cara menggunakan VLOOKUP di Microsoft Excel. Selamat mencoba dan semoga bermanfaat.

Sumber: http://ahlikompie.com/vlookup-cara-menggunakan-fungsi-vlookup-excel-775.html

Mengakses data pada sheet lain, pada file 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 dari Sheet1 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 misal Z24, lalu ketik rumus =[data1.xlsx]Sheet1!S5. *)Selanjutnya tekan enter. penjelasanya [data1.xlsx] merupakan nama file, Sheet1 adalah nama Sheet yang kita ambil sedangkan S5 merupakan 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 range tempat 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

Katakan kita punya file Excel yang ada chartnya, Chartnya mau kita share ke orang lain.

File Excel tsb ada di site internal, sedangkan kita mau Chartnya ada di publishing site atau home page supaya mudah dilihat orang lain.

Atau kita mau tampilkan Chart tersebut di file Word yang ada di local computer kita.

Solusi gampangnya, pakai Excel REST Services.

REST kurang lebih seperti web services untuk mengambil data luar dengan tinggal memanggil URL addressnya.

Untuk contoh tadi,

Katakan file excelnya di http://contoso/Shared%20Documents/Clock.xlsx .

Maka untuk mengakses Chart yang namanya Clock juga , tinggal mengakses URL address sbb: http://contoso/_vti_bin/ExcelRest.aspx/Shared%20Documents/Clock.xlsx/Model/Charts('Clock') . Lalu tempelkan URL di tempat yang kita mau.

Gambaran lengkapnya ada di : http://blogs.office.com/b/microsoft-excel/archive/2009/11/09/excel-services-in-sharepoint-2010-rest-api-examples.aspx .

Variasi lain, kita juga bisa melakukan perubahan nilai terhadap apa yang ditampilkan URL tadi, namun perubahan itu bersifat local, tidak mempengaruhi file Excel aslinya, bahkan tidak mempengaruhi session lain yang sama-sama mengakses data (Excell Services memang ditujukan untuk multi akses jadi tidak perlu kuatir jika diakses bareng dalam waktu bersamaan).

Yang perlu diingat, permission dari file tersebut tetap dibawa, jadi hanya user yang punya permission terhadap file Excel nya yang bisa mengakses.

Filter Ms. Excel

 Memasukkan data setiap harinya kedalam tabel di Ms Excel, tentunya lambat laun data tersebut akan berjumlah sangat banyak. Akibatnya kita akan merasa kesulitan untuk memilah dan memilih serta mengelompokkan data berdasarkan kriteria tertentu. Seperti halnya mengelompokkan data berdasarkan alamat, mengelompokkan data berdasarkan jabatan, mengelompokkan data berdasarkan pendidikan atau lulusan dan sebagainya.

Anda bisa saja melakukan hal tersebut diatas secara manual, namun pastinya akan memakan waktu yang tidak sebentar dan energi yang tidak sedikit. Malah terkadang karena keterbatasan sebagai manusia, masih saja ada yang tertinggal tidak ikut dikelompokkan. Nah hasilnya bisa ditebak, …. Tidak maksimal.

Untuk mengatasi hal tersebut, Ms Excel terdapat fitur atau fasilitas untuk memfilter data yaitu fasilitas Filter. Dengan menggunakan fasilitas filter tersebut anda bisa saja dengan mudah mengelompokkan data berdasarkan kriteria tertentu dengan mudah dan hasilnya juga lebih akurat. Andapun bisa saja menambahkan data baru pada baris terakhir tanpa harus menampilkan data secara keseluruhan. Cara memfilter data di ms excel bisa anda ikuti langkah-langkahnya sebagai berikut :

1. Blok kepala tabel atau judul kolom yang akan difilter. Pada gambar dibawah ini blok sel A2 sampai E2.
   
   


2. Pada ribbon home, group menu editing, klik Short & Filter kemudian pilih Filter. Atau tekan Ctrl+Shift+L
   
   


3. Maka pada tiap sel kepala tabel tersebut akan terlihat tanda dropdown. Klik pada salah satu sel drop down tersebut. Dalam hal ini sebagai contoh kita pilih kolom alamat, maka klik tombol drop down pada sel alamat.
4. Pada text filter klik select all untuk menghilangkan semua tanda centang yang ada, kemudian pilih salah satu kategori yang akan anda filter. Sebagai contoh kita pilih Madiun, kemudian klik OK.
   
   


5. Maka pada tabel yang nampak hanyalah data yang beralamatkan kota madiun saja, sedangkan data yang lain tidak muncul.
   
   

Untuk memunculkan semua data ulangi langkah nomor 4 kemudian beri tanda centang pada semua pilihan di text filter dan klik OK. Pada saat data difilter coba perhatikan tanda dropdown pada sel atau kolom yang difilter akan berbeda dengan kolom atau sel yang lain yang tidak difilter.

Data Validation Ms. Excel

Jika anda adalah pengguna Microsoft Excel, tentu salah satu tugas kita sebagai pembuat project-nya tentu kita akan membuat project tersebut berisi dengan data-data yang akurat. Tetapi sayangnya secara default, Excel tidak menjaga keakuratan data anda tersebut. Dan hal itu akan berpengaruh pada hasil analisa laporan anda karena banyak data input yang tidak sesuai yang anda harapkan. Oya, saya mencoba tip ini di Microsoft Excel 2007 tetapi anda bisa menggunakan cara yang sama pada versi-versi sebelumnya.
Tetapi jangan kuatir, Excel juga telah menyediakan cara untuk anda. Fitur tersebut bernama Data Validation. Untuk penggunaannya sebagai berikut:

1. Setelah Microsoft Excel anda terbuka, ketikan daftar isian yang anda inginkan.
   
2. Arahkan ke cell dimana anda ingin isian dilakukan.
3. Klik menu Data -> Data Validation.
4. Di tab Settings, beri combo Allow: nilai List,  klik isian Source lalu blok data yang sudah anda buat di langkah 1.
   
5. Klik tab Error Alert. Pastikan radio check Show error after invalid data is entered dalam keadaan tercentang. Untuk Style pilih Stop lalu tulis Title kotak peringatan dan Error Message: dengan isi kesalahan. Lalu klik tombol OK.
   
6. Nah, sekarang jika kita akan memasukan nilai di cell tersebut, akan muncul tombol anak panah kebawah yang jika di klik akan muncul nilai-nilai yang sudah di tentukan.
   
7. Dan jika anda memaksa mengisi dengan nilai di luar data tersebut, kotak peringatan yang telah anda konfigurasi tersebut akan muncul.
   

Dengan demikian data yang dimasukan bisa valid dan sesuai dengan yang anda harapkan.

sorting ms.excel

Filter adalah perintah untuk menyaring data dengan kriteria tertentu, dimana kriteria tersebut kita tentukan sendiri sesuai dengan yang kita butuhkan. Fasilitas ini sangat berguna, terutama jika kita bermaksud untuk mencari beberapa data saja dari sebuah data yang kompleks.

Contoh penggunaan perintah ini adalah sebagai berikut :
Buka tabel data yang akan disaring datanya, misalnya Daftar Nama Karyawan berikut :

Klik menu Data lalu pada ribbon-bar klik Filter.



Sekarang perhatikan tabel data kita. Akan tampak tombol panah kecil di setiap nama field. Tombol panah kecil tersebut digunakan untuk menyaring data pada field yang bersangkutan (misalnya field Divisi). Pilih divisi data yang ingin Anda saring melalui tombol panah kecil tersebut (misalnya data yang akan ditampilkan Divisi Sekretaris Perusahaan saja)

 
Contoh hasil yang didapat sebagai berikut.

Pada sub bab ini, kita akan membuat dokumen sebagai contoh pengelolaan data
sederhana. Dari contoh pengelolaan data sederhana kita akan belajar tentang cara
pengetikan di excel, alignment, cara memformat huruf, cara memformat tabel dan cell, dan
memanfaatkan fasilitas Autofill. Berikut contoh pengelolaan data yang pertama seperti pada
Gambar 6.2.


Langkah-langkah membuat dokumen daftar unit kerja seperti pada Gambar 6.2 adalah
sebagai berikut:
1. Buat Judul dan Sub Judul Laporan
• Arahkan kursor pada Cell A1 (Kolom A dan Baris 1), kemudian ketikkan tulisan
“Daftar Unit Kerja” pada Cell aktif tersebut.
• Kemudian ketikkan sub judul laporan pada Cell A3, A4, A5, D4 dan D5 seperti
pada tampilan dibawah ini:

• Buat data tabel pada Laporan dengan memulai pengetikan pada A7 sampai F7
untuk judul tabel dan A8, A9, B8, B9, dst untuk data pada tabel.

2. Penggunaan alignment, format font dan format Cell
• Untuk membuat Judul Laporan letaknya di tengah-tengah tabel maka arahkan
kursor ke A1 kemudian select cell mulai A1 sampai F1. Kemudian klik tombol
Merge Cell agar judul laporan terletak ditengah-tengah tabel.
• Agar huruf pada judul laporan dan tabel tebal maka arahkan kursor pada cell
kemudian klik tombol Bold atau dapat juga melakukan format font lainnya
seperti underline atau italic.
3. Penggunaan AutoFill
Pada kolom No, KODE dan NAMA UNIT dapat digunakan Autofill agar lebih
cepat dalam memasukkan data yang berulang. Fasilitas ini diaktifkan ketika kotak
hitam kecil yang disebut fill handle digeser di atas cell-cell baru. Fill handle berada di
ujung kanan bawah cell aktif atau range cell yang dipilih. Ketika menempatkan
pointer cell di atas fill handle, pointer cell berubah menjadi tanda tambah,
menunjukkan bahwa fasilitas AutoFill telah diaktifkan. Untuk membuat seri label,
angka, atau tanggal, geserlah pointer di atas cell-cell yang ingin diisi dengan
informasi, lalu lepaskan tekanan tombol mouse. Seperti sulap, muncullah sederetan
nilai.

Pada Kolom NO dan KODE, Autofill digunakan untuk membuat seri number secara
terurut sedangkan pada kolom Nama Unit digunakan untuk mengcopy data yang
sama ke beberapa cell secara cepat.
4. Penggunaan format Tabel dan Cell

• Microsoft Excel menyediakan fasilitas format table dengan beberapa template
seperti memberi border, warna border, warna cell dan style font dengan klik
tombol format table seperti ini , kemudia akan muncul pilihan style format
tabel seperti berikut ini.