Elakkan VLOOKUP menggunakan Model Data. Oleh itu, anda mempunyai dua jadual yang perlu digabungkan dengan VLOOKUP sebelum anda dapat melakukan jadual pangsi. Sekiranya anda mempunyai Excel 2013 atau yang lebih baru pada PC Windows, kini anda boleh melakukannya dengan mudah dan senang.
Katakan bahawa anda mempunyai kumpulan data dengan maklumat produk, pelanggan, dan penjualan.
Jabatan IT lupa meletakkan sektor di sana. Berikut adalah jadual carian yang memetakan pelanggan ke sektor. Masa untuk VLOOKUP, bukan?
Tidak perlu melakukan VLOOKUP untuk menyertai set data ini jika anda mempunyai Excel 2013 atau Excel 2016. Kedua-dua versi Excel ini telah memasukkan enjin Power Pivot ke dalam inti Excel. (Anda juga boleh melakukan ini menggunakan add-in Power Pivot untuk Excel 2010, tetapi ada beberapa langkah tambahan.)
Dalam kumpulan data asal dan jadual carian, gunakan Laman Utama, Format sebagai Jadual. Pada tab Alat Meja, ganti nama jadual dari Jadual1 menjadi sesuatu yang bermakna. Saya telah menggunakan Data dan Sektor.
Pilih satu sel dalam jadual data. Pilih Masukkan, Jadual Pangsi. Bermula di Excel 2013, ada kotak tambahan Tambahkan Data ini ke Model Data yang harus anda pilih sebelum mengklik OK.
Senarai Medan Jadual Pangsi muncul dengan medan dari jadual Data. Pilih Hasil. Kerana anda menggunakan Model Data, baris baru muncul di bahagian atas senarai, menawarkan Aktif atau Semua. Klik Semua.
Anehnya, senarai Medan Pangsi menawarkan semua jadual lain dalam buku kerja. Ini adalah pecah tanah. Anda belum melakukan VLOOKUP. Kembangkan jadual Sektor dan pilih Sektor. Dua perkara berlaku untuk memberi amaran bahawa terdapat masalah.
Pertama, jadual pangsi muncul dengan nombor yang sama di semua sel.
Mungkin amaran yang lebih halus adalah kotak kuning muncul di bahagian atas senarai Medan Pangsi menunjukkan bahawa anda perlu menjalin hubungan. Pilih Buat. (Sekiranya anda berada di Excel 2010 atau 2016, beruntunglah dengan Auto-Detect.)
Dalam dialog Buat Hubungan, anda mempunyai empat menu lungsur. Pilih Data di bawah Jadual, Pelanggan di bawah Lajur (Asing), dan Sektor di bawah Jadual Berkaitan. Power Pivot secara automatik akan mengisi ruangan yang sesuai di bawah Related Column (Primer). Klik OK.
Jadual pangsi yang dihasilkan adalah susunan data asal dan jadual carian. Tidak diperlukan VLOOKUP.
Tonton Video
- Bermula di Excel 2013, dialog Pivot Table menawarkan Model Data
- Ini adalah kata kod untuk Power Pivot Engine
- Untuk menggunakan model data, buat jadual Ctrl + T dari setiap jadual di buku kerja
- Bina jadual pangsi dari jadual pertama
- Dalam Senarai Medan Jadual Pangsi, ubah dari Aktif ke Semua
- Pilih medan dari jadual carian
- Sama ada mewujudkan hubungan atau Auto-Detect
- Auto-Detect tidak ada pada 2013
- Terima kasih kepada Colin Michael dan Alejandro Quiceno kerana mencadangkan Power Pivot secara umum.
Transkrip Video
Belajar Excel dari podcast, episod 2014 - Hapuskan VLOOKUP!
Podcast keseluruhan buku ini, klik “i” di sudut kanan atas untuk senarai main!
Hei, selamat datang kembali ke netcast, saya Bill Jelen, ini sebenarnya dipanggil Eliminate VLOOKUP dengan Model Data! Sekarang saya minta maaf, ini adalah Excel 2013 dan yang lebih baru, jika anda kembali ke Excel 2010, anda harus memuat turun add-in Power Pivot, yang tentunya percuma pada tahun 2010. Jadi apa yang kita ada di sini adalah kita mempunyai kumpulan data utama, ada bidang Pelanggan di sini, dan kemudian saya mempunyai meja kecil yang memetakan pelanggan ke sektor, saya perlu membuat jumlah pendapatan mengikut sektor, bukan? Ini adalah VLOOKUP, lakukan saja VLOOKUP, tapi hei, terima kasih kepada Excel 2013, kita tidak perlu melakukan VLOOKUP! Saya membuat kedua-duanya menjadi meja, dan pada Alat Meja, Reka Bentuk, saya menamakan semula jadual, saya memanggil Sektor yang satu ini, dan saya memanggil Data ini, untuk menjadikannya satu jadual, pilih saja satu sel, tekan Ctrl + T. Jadi, jika kita mempunyai beberapa tajuk dan beberapa nombor, ketika anda menekan Ctrl + T,mereka bertanya "Di mana data untuk meja anda?", Meja saya mempunyai tajuk, dan kemudian mereka memanggilnya Jadual3, anda menyebutnya sesuatu yang lain. Baiklah, begitulah cara saya membuat dua jadual, saya akan menyingkirkan jadual ini, baiklah.
Oleh itu, agar helah ini dapat dilaksanakan, semua data mesti ditulis dalam jadual. Kami pergi ke tab Insert, pilih PivotTable, dan sampai di sini di bahagian bawah, Tambahkan data ini ke Model Data. Ini terdengar sangat tidak berbahaya, bukan? Tidak ada titik kilat yang mengatakan "Hei, ini akan membolehkan anda melakukan perkara yang menakjubkan!" Dan apa yang mereka katakan di sini, apa yang mereka cuba tidak katakan ialah - Oh, omong-omong, setiap salinan Excel 2013 mempunyai enjin Power Pivot di belakangnya. Anda tahu, jika anda berada di Office 365, anda membayar $ 10 sebulan, dan mereka mahu anda membayar $ 12 atau $ 15 sebulan untuk mendapatkan Power Pivot, dua atau lima dolar tambahan. Baiklah, hei, jangan katakan, anda sebenarnya sudah mempunyai sebahagian besar Power Pivot di Excel 2013. Baiklah, jadi saya klik OK, memerlukan sedikit masa lagi untuk memuatkan model data, baiklah, tapi tidak mengapa, dan betul di sini,di medan PivotTable, saya mempunyai senarai semua medan. Oleh itu, saya mahu menunjukkan Pendapatan, tetapi apa yang berbeza ada di sini dengan Aktif dan Semua. Apabila saya memilih Semua, saya mendapat semua jadual dalam buku kerja. Baiklah, jadi saya pergi ke Sektor, dan saya mengatakan bahawa saya mahu meletakkan sektor di kawasan Rows. Sekarang, pada mulanya, laporan itu akan menjadi salah, lihat 6.7 juta hingga habis, dan amaran kuning ini di sini akan mengatakan bahawa anda harus menjalin hubungan.dan amaran kuning ini di sini akan mengatakan bahawa anda harus menjalin hubungan.dan amaran kuning ini di sini akan mengatakan bahawa anda harus menjalin hubungan.
Baiklah sekarang, pada tahun 2010 dengan Power Pivot, ia hanya menawarkan AutoDetect, pada tahun 2013 mereka mengeluarkan AutoDetect, dan pada tahun 2016 mereka membawa AutoDetect kembali, baiklah? Saya harus menunjukkan kepada anda bagaimana CREATE kelihatan, tetapi apabila saya mengklik butang CREATE ini, oh yeah, itu sahaja, baiklah, bagus. Oleh itu dari Data jadual pertama kami, saya mempunyai bidang yang disebut Pelanggan, dari Sektor jadual yang berkaitan, saya mempunyai bidang yang disebut Pelanggan, dan kemudian anda klik OK, baiklah. Tetapi izinkan saya menunjukkan kepada anda betapa hebatnya AutoDetect, jika anda berada pada tahun 2016, mereka tahu, betapa hebatnya itu, bukan? Anda tidak perlu bimbang tentang VLOOKUP, dan koma jatuh pada akhirnya, jika VLOOKUP membuat kepala anda sakit, anda akan menyukai Model Data. Mengambil kedua-dua jadual itu, menyatukannya, anda tahu, seperti yang akan dilakukan Access, saya rasa, dan membuat jadual Pivot, sangat mengagumkan.Oleh itu, periksa model data lain kali anda perlu melakukan VLOOKUP antara dua jadual. Baiklah ini dan semua 40 petua lain ada dalam buku ini, Klik “i” di sudut kanan atas. Anda boleh membeli buku ini, mempunyai rujukan silang yang lengkap untuk keseluruhan siri video ini, sepanjang bulan Ogos, sepanjang bulan September, ya, kita mungkin akan meneruskannya hingga bulan Oktober untuk menyelesaikan semuanya.
Baiklah, rujuk hari ini: bermula pada Excel 2013, dialog Pivot Table menawarkan sesuatu yang disebut Model Data, ini adalah kata kod untuk mesin Power Pivot. Sebelum anda membuat jadual Pivot anda, lakukan Ctrl + T untuk membuat jadual dari setiap buku kerja, saya mengambil masa tambahan untuk menamakan masing-masing. Bina jadual Pivot dari jadual pertama, dan kemudian dalam senarai medan, naik ke atas dan ubah dari Aktif ke Semua. Pilih medan dari jadual carian, dan kemudian akan memberi amaran kepada anda bahawa anda mesti membuat hubungan, atau AutoDetect, pada tahun 2013, anda harus mengklik BUAT. Tetapi itulah, 4 klik untuk membuatnya, 5 jika anda mengira butang OK, sangat mudah dilakukan.
Baiklah, Colin, Michael, dan Alejandro Quiceno mencadangkan Power Pivot secara umum untuk buku-buku itu, terima kasih kepada mereka, terima kasih kerana berhenti, kami akan berjumpa anda di lain kali untuk siaran lain!
Muat turun fail
Muat turun fail contoh di sini: Podcast2014.xlsx
