Hindari VLOOKUP menggunakan Model Data. Jadi, Anda memiliki dua tabel yang perlu digabungkan dengan VLOOKUP sebelum Anda dapat membuat tabel pivot. Jika Anda memiliki Excel 2013 atau yang lebih baru di PC Windows, Anda sekarang dapat melakukannya dengan sederhana dan mudah.
Misalnya Anda memiliki kumpulan data dengan informasi produk, pelanggan, dan penjualan.
Departemen IT lupa menempatkan sektor di sana. Berikut adalah tabel pencarian yang memetakan pelanggan ke sektor. Waktunya untuk VLOOKUP, bukan?
Tidak perlu melakukan VLOOKUP untuk menggabungkan kumpulan data ini jika Anda memiliki Excel 2013 atau Excel 2016. Kedua versi Excel ini telah memasukkan mesin Power Pivot ke dalam inti Excel. (Anda juga bisa melakukan ini menggunakan add-in Power Pivot untuk Excel 2010, tetapi ada beberapa langkah tambahan.)
Di kumpulan data asli dan tabel pencarian, gunakan Beranda, Format sebagai Tabel. Pada tab Alat Tabel, ganti nama tabel dari Tabel1 menjadi sesuatu yang bermakna. Saya telah menggunakan Data dan Sektor.
Pilih satu sel di tabel data. Pilih Sisipkan, Tabel Pivot. Mulai di Excel 2013, ada kotak tambahan Tambahkan Data Ini ke Model Data yang harus Anda pilih sebelum mengklik OK.
Daftar Bidang Tabel Pivot muncul dengan bidang dari tabel Data. Pilih Pendapatan. Karena Anda menggunakan Model Data, baris baru muncul di bagian atas daftar, menawarkan Aktif atau Semua. Klik Semua.
Anehnya, daftar Bidang PivotTable menawarkan semua tabel lain di buku kerja. Ini terobosan. Anda belum melakukan VLOOKUP. Perluas tabel Sektor dan pilih Sektor. Dua hal terjadi untuk memperingatkan Anda bahwa ada masalah.
Pertama, tabel pivot muncul dengan angka yang sama di semua sel.
Mungkin peringatan yang lebih halus adalah kotak kuning yang muncul di bagian atas daftar Bidang PivotTable yang menunjukkan bahwa Anda perlu membuat hubungan. Pilih Buat. (Jika Anda menggunakan Excel 2010 atau 2016, ambillah keberuntungan Anda dengan Deteksi Otomatis.)
Dalam dialog Buat Hubungan, Anda memiliki empat menu tarik-turun. Pilih Data di bawah Tabel, Pelanggan di bawah Kolom (Asing), dan Sektor di bawah Tabel Terkait. Power Pivot akan secara otomatis mengisi kolom yang cocok di bawah Kolom Terkait (Utama). Klik OK.
Tabel pivot yang dihasilkan adalah gabungan dari data asli dan tabel pemeta. Tidak diperlukan VLOOKUP.
Menonton video
- Mulai di Excel 2013, dialog Tabel Pivot menawarkan Model Data
- Ini adalah kata kode untuk Power Pivot Engine
- Untuk menggunakan model data, buat tabel Ctrl + T dari setiap tabel di buku kerja
- Buat tabel pivot dari tabel pertama
- Dalam Daftar Bidang Tabel Pivot, ubah dari Aktif ke Semua
- Pilih bidang dari tabel pencarian
- Buat hubungan atau Deteksi Otomatis
- Deteksi Otomatis tidak ada di tahun 2013
- Terima kasih kepada Colin Michael dan Alejandro Quiceno karena telah menyarankan Power Pivot secara umum.
Transkrip Video
Pelajari Excel dari podcast, episode 2014 - Hilangkan VLOOKUP!
Untuk membuat podcast untuk seluruh buku ini, klik "i" di pojok kanan atas untuk playlist!
Hai, selamat datang kembali di netcast, saya Bill Jelen, ini sebenarnya yang disebut Hilangkan VLOOKUP dengan Model Data! Sekarang saya minta maaf, ini adalah Excel 2013 dan yang lebih baru, jika Anda kembali menggunakan Excel 2010, Anda harus mengunduh add-in Power Pivot, yang tentu saja gratis pada tahun 2010. Jadi yang kami miliki di sini adalah kami memiliki kumpulan data utama, ada bidang Pelanggan di sini, dan kemudian saya memiliki tabel kecil yang memetakan pelanggan ke sektor, saya perlu membuat pendapatan total per sektor, bukan? Ini adalah VLOOKUP, cukup lakukan VLOOKUP, tapi hei, berkat Excel 2013, kita tidak perlu melakukan VLOOKUP! Saya membuat keduanya menjadi tabel, dan pada Alat Tabel, Desain, saya mengganti nama tabel, saya menyebutnya Sektor, dan saya menyebutnya Data, untuk membuatnya menjadi tabel, cukup pilih satu sel, tekan Ctrl + T. Jadi jika kami memiliki beberapa judul dan beberapa angka, saat Anda menekan Ctrl + T,mereka bertanya "Di mana data untuk tabel Anda?", Tabel saya memiliki header, dan kemudian mereka menyebutnya Tabel3, Anda menyebutnya dengan nama lain. Baiklah, begitulah cara saya membuat dua tabel itu, saya akan menyingkirkan tabel ini, oke.
Jadi agar trik ini berfungsi, semua data harus berada dalam tabel. Kami pergi ke tab Sisipkan, pilih PivotTable, dan di sini di bagian bawah, Tambahkan data ini ke Model Data. Ini terdengar sangat tidak berbahaya, bukan? Tidak ada titik berkedip yang mengatakan "Hei, ini akan membuatmu melakukan hal-hal menakjubkan!" Dan apa yang mereka katakan di sini, yang mereka coba untuk tidak katakan adalah- Oh, omong-omong, setiap salinan Excel 2013 memiliki mesin Power Pivot di belakangnya. Anda tahu, jika Anda menggunakan Office 365, Anda membayar $ 10 sebulan, dan mereka ingin Anda membayar $ 12 atau $ 15 sebulan untuk mendapatkan Power Pivot, dua atau lima dolar tambahan. Nah, hei, sst, jangan bilang, Anda sebenarnya sudah memiliki sebagian besar Power Pivot di Excel 2013. Baiklah, jadi saya klik OK, butuh waktu lebih lama untuk memuat model data, oke, tapi tidak apa-apa, dan segera selesai sini,di bidang PivotTable, saya memiliki daftar semua bidang. Jadi, saya ingin menunjukkan Pendapatan, tapi yang berbeda ada di sini dengan Aktif dan Semua. Saat saya memilih Semua, saya mendapatkan semua tabel di buku kerja. Baiklah, jadi saya pergi ke Sektor, dan saya bilang saya ingin meletakkan sektor di area Baris. Sekarang, awalnya, laporannya akan salah, lihat 6,7 juta seluruhnya turun, dan peringatan kuning ini di sini akan mengatakan bahwa Anda harus menjalin hubungan.dan peringatan kuning di sini akan mengatakan bahwa Anda harus menjalin hubungan.dan peringatan kuning di sini akan mengatakan bahwa Anda harus menjalin hubungan.
Baiklah sekarang, pada tahun 2010 dengan Power Pivot, itu hanya akan, itu menawarkan Deteksi Otomatis, pada tahun 2013 mereka mengeluarkan Deteksi Otomatis, dan pada tahun 2016 mereka mengembalikan Deteksi Otomatis, oke? Saya harus menunjukkan seperti apa CREATE itu, tetapi ketika saya mengklik tombol BUAT ini, oh ya, itu saja, baiklah, bagus. Jadi dari data tabel pertama kami, saya memiliki bidang yang disebut Pelanggan, dari tabel terkait Sektor, saya memiliki bidang yang disebut Pelanggan, dan kemudian Anda klik OK, oke. Tapi izinkan saya menunjukkan betapa kerennya AutoDetect itu, jika Anda kebetulan berada di tahun 2016, di sana, mereka tahu, betapa hebatnya itu, bukan? Anda tidak perlu khawatir tentang VLOOKUP, dan koma jatuh di bagian akhir, jika VLOOKUP membuat kepala Anda sakit, Anda akan menyukai Model Data. Mengambil dua tabel itu, menggabungkannya, Anda tahu, seperti yang akan dilakukan Access, saya kira, dan membuat tabel Pivot, benar-benar luar biasa.Jadi periksa model datanya saat Anda harus melakukan VLOOKUP antara dua tabel. Nah ini dan semua 40 tip lainnya ada di buku, Klik "i" di pojok kanan atas. Anda dapat membeli bukunya, memiliki referensi silang lengkap ke seluruh rangkaian video ini, sepanjang Agustus, sepanjang September, heck, kita bahkan mungkin melanjutkannya hingga Oktober untuk menyelesaikan semuanya.
Baiklah, rekap hari ini: mulai di Excel 2013, dialog Tabel Pivot menawarkan sesuatu yang disebut Model Data, yaitu kata kode untuk mesin Power Pivot. Sebelum Anda membuat tabel Pivot, lakukan Ctrl + T untuk membuat tabel dari setiap buku kerja, saya meluangkan waktu ekstra untuk memberi nama masing-masing. Buat tabel Pivot dari tabel pertama, lalu di daftar bidang, naik ke atas dan ubah dari Aktif ke Semua. Pilih bidang dari tabel pencarian, dan kemudian akan memperingatkan Anda bahwa Anda harus membuat hubungan, atau Deteksi Otomatis, pada 2013, Anda harus mengklik BUAT. Tapi itu apa, 4 klik untuk membuatnya, 5 jika Anda menghitung tombol OK, jadi sangat, sangat mudah dilakukan.
Baiklah, Colin, Michael, dan Alejandro Quiceno menyarankan Power Pivot secara umum untuk buku-bukunya, terima kasih kepada mereka, terima kasih telah mampir, sampai jumpa di lain waktu untuk netcast lain dari!
Unduh berkas
Unduh file contoh di sini: Podcast2014.xlsx
