Kaley dari Nashville sedang mengusahakan hamparan tiket. Untuk setiap acara, dia memilih rancangan tiket. Pelan tiket itu dapat menunjukkan di mana saja dari 4 hingga 16 jenis tiket untuk acara tersebut. Kaley mahukan formula yang akan masuk ke jadual pencarian dan mengembalikan * semua * pertandingan, memasukkan baris baru yang sesuai.
Walaupun saya tidak mempunyai VLOOKUP yang dapat menyelesaikannya, alat Kuasa Kuasa baru yang dibina di Excel 2016 dapat menyelesaikannya.
Catatan
Sekiranya anda mempunyai versi Windows Excel 2010 atau Excel 2013, anda boleh memuat turun Kueri Kuasa secara percuma dari Microsoft. Malangnya, Kueri Kuasa belum tersedia untuk Excel untuk Android, Excel untuk iOS atau Excel untuk Mac.
Untuk menggambarkan tujuannya: Mike McCann dan the Mechanics muncul di Allen Theatre dengan rancangan tiket C. Oleh kerana terdapat empat baris yang sesuai di meja pencarian, Kaley mahukan empat baris yang mengatakan Mike McCann dan the Mechanics, masing-masing dengan pertandingan yang berbeza dari jadual carian.
Pilih sel dalam jadual asal. Tekan Ctrl + T untuk menandakan data tersebut sebagai jadual. Pada tab Alat Meja, ganti nama jadual dari Jadual1 ke Tunjukkan. Ulangi untuk jadual carian, memanggilnya Tiket.
Pilih sel di jadual Rancangan. Dari tab Data, pilih Dari Jadual / Julat.
Setelah editor Power Query dibuka, buka drop-down Tutup & Muat dan pilih Tutup dan Muat Ke….
Dalam dialog Import Data, pilih Hanya Buat Sambungan.
Pergi ke jadual Tiket. Ulangi langkah-langkah untuk Hanya Membuat Sambungan ke Tiket. Anda harus melihat kedua-dua sambungan di panel Pertanyaan:
Pilih mana-mana sel kosong. Pilih Data, Dapatkan Data, Gabungkan Pertanyaan, Gabungan.
Terdapat enam langkah dalam dialog Gabungan. Yang ke-3 dan ke-4 tidak kelihatan intuitif bagi saya.
- Pilih Rancangan dari menu lungsur bawah
- Pilih Tiket dari drop-down kedua.
- Klik pada tajuk untuk Pelan Tiket di bahagian atas untuk memilih lajur itu sebagai kunci asing dalam jadual Tunjukkan.
- Klik pada tajuk untuk Pelan Tiket di bahagian bawah untuk memilih lajur itu sebagai medan utama dalam jadual carian.
- Buka jenis Join dan pilih Inner (hanya baris yang sepadan).
- Klik OK
Hasilnya pada awalnya mengecewakan. Anda melihat semua medan dari jadual 1 dan lajur yang menyatakan Jadual, Jadual, Jadual.
Klik ikon Kembangkan di bahagian atas lajur Tiket.
Batalkan pilihan Pelan Tiket kerana anda sudah mempunyai bidang itu. Bidang yang tinggal akan dipanggil Tiket. Jenis Tiket kecuali anda mencentang tanda Gunakan Nama Asal sebagai Awalan.
Berjaya! Setiap baris untuk setiap pertunjukan meletup menjadi beberapa baris.
Saya tidak begitu senang dengan penyusunan data. Menyusun mengikut Tarikh menyebabkan Jenis Tiket disusun dengan cara yang ganjil.
Tonton Video
Dalam kes hari ini, video tersebut ditembak setelah artikel itu ditulis. Saya cadangkan menambah lajur urutan ke Jenis Tiket untuk mengawal urutan urutan.
Transkrip Video
Belajar Excel dari Podcast, Episod 2204: Kembalikan Semua VLOOKUP.
Hei, selamat datang kembali ke netcast, saya Bill Jelen. Soalan hari ini dari Nashville Music City. Saya berada di sana di Nashville, seseorang bertanggungjawab menjadualkan memuat tiket ke sistem tiket dan jadi inilah yang kami ada: Kami mempunyai senarai acara - acara akan datang - kami mempunyai tarikh, tempat, dan rancangan tiket. Jadi, seperti, walaupun ada sesuatu yang diadakan di Istana, mungkin ada rancangan tiket yang berbeza - seperti, mungkin lantai dikonfigurasikan, anda tahu, dengan tempat duduk atau mungkin itu hanya ruang berdiri sahaja, bukan?
Oleh itu, bergantung pada jenis rancangan tiket apa, anda harus datang ke sini untuk mencari senarai dan mencari semua peristiwa yang sesuai, dan pada dasarnya kita akan melakukan apa yang saya sebut sebagai letupan VLOOKUP. Jadi jika ada sesuatu di Hannah C, mereka akan turun ke Hannah C dan jika ada-- 1, 2, 3, 4, 5, 6-7 item di Hannah C, kita akan mempunyai untuk mengembalikan tujuh baris - yang bermaksud anda perlu memasukkan enam baris lagi dan menyalin data tersebut ke bawah. Baiklah.
Sekarang, kami sama sekali tidak akan melakukan ini dengan VLOOKUP, tetapi anda mendapat konsep - kami melakukan VLOOKUP dan kami mengembalikan semua jawapan sebagai baris baru. Baiklah, jadi, saya akan mengambil kedua-dua jadual ini dan menjadikannya jadual sebenar dengan Ctrl + T. Yang pertama disebut Jadual 1 nama mengerikan, mari kita panggil Acara atau Pertunjukan ini, mari kita panggilnya Rancangan, seperti itu-- dan yang kedua, sekarang, hei, inilah yang saya pelajari kerana saya mempraktikkan ini - kita mesti mempunyai medan urutan di sini. Jadi = ROW (A1), klik dua kali, dan salin ke bawah dan kemudian salin dan tampal nilai khas. Baiklah. Sekarang kita membuatnya yang akan menjadi jadual - Ctrl + T, dan kita akan memanggilnya satu Tiket.
Baiklah. Jadi kita ada pertunjukan, kita ada tiket. Saya akan pergi ke tab Data, dan saya di sini menunjukkan perkara, saya ingin mengatakan bahawa saya ingin mendapatkan data saya dari Jadual atau Julat - ini adalah Kueri Kuasa, by the way. Sekiranya anda kembali menggunakan Excel 2010 atau 2013, anda boleh memuat turunnya secara percuma dari Microsoft, muat turun alat Kuasa Kuasa. Sekiranya anda menggunakan Mac atau iOS atau Android, maaf, tiada Kuasa Kuasa untuk anda. Baiklah, jadi dari Jadual atau Julat … cari seseorang yang mempunyai-- cari rakan yang mempunyai - Windows PC dan minta mereka menyiapkannya. Baiklah. Inilah jadual, kami tidak akan melakukan perkara ini, hanya Tutup & Muatkan, Tutup & Muatkan, dan kemudian katakan "Hanya Buat Sambungan", sempurna. Kami akan datang ke sini ke jadual kedua kami: Dapatkan Data, Dari Jadual atau Julat, kami tidak melakukan apa-apa untuk ini, Tutup & Muatkan,Tutup & Muat ke, "Hanya Buat Sambungan", OK. Jadi apa yang kita ada sekarang, ialah kita mempunyai sambungan ke meja pertama dan sambungan ke meja kedua. Kami tidak akan menggabungkan kedua-duanya, yang pada dasarnya seperti melakukan VLOOKUP, atau Gabungan Pangkalan Data, saya rasa, benar-benar seperti apa. Gabungkan Pertanyaan, kita akan Gabungkan. Baiklah.
Sekarang, tujuh perkara yang harus anda lakukan dalam kotak dialog ini - dan agak membingungkan - kami akan memilih Rancangan sebagai jadual pertama; pilih Tiket sebagai jadual kedua; pilih bidang apa yang mereka miliki, dan ini boleh menjadi beberapa bidang - anda boleh mengawal-klik-- tetapi dalam kes ini hanya ada satu rancangan tiket; dan kemudian Pelan Tiket; dan kemudian kita akan menukar jenis Gabung menjadi gabung dengan "hanya baris yang sepadan". Baiklah. Sekarang, anda klik OK dan anda fikir bahawa keseluruhan masalah anda akan diselesaikan, tetapi anda hancur kerana inilah semua data dari A - mereka belum memasukkan baris baru sama sekali - dan di sini, hanya medan bodoh yang membosankan yang disebut Tiket yang hanya mempunyai Jadual, Jadual, Jadual, hah.
Tetapi, syukurlah, di bahagian atasnya adalah ikon Perluas, dan kita akan memperluasnya - Saya tidak perlu mengambil rancangan, saya sudah memilikinya - Jenis Tiket dan Urutan. Saya tidak mahu ia dipanggil Tiket. Jenis Tiket, itulah yang ingin dilakukan Kuasa Kuasa - jadi saya hapus centang kotak ini. Baiklah. Sekarang ini kami mempunyai 17 baris data; apabila saya klik OK, BAM! Ada letupan. Jadi, Michael Seeley dan Starlighter muncul dengan semua jenis tiket yang berbeza, seperti ini. Baiklah, dan melihat jenis tiket ini muncul mengikut urutan, itu bagus. Tetapi Michael Seeley bukan rancangan seterusnya, rancangan seterusnya adalah pada 5 Jun. Oleh itu, apabila saya mencuba dan menyusunnya mengikut Tarikh - ini menjadikan saya tidak waras, saya tidak dapat menjelaskannya. Urutkan mengikut Tarikh, dan Mike Man and the Mechanics mencapai 65, tetapi kemudian semua tiketnya habis. Mereka 'kembali pada urutan yang salah, dan oleh sebab itu saya terpaksa melakukan urutan ini - terasa seperti itu. Saya boleh menyusun mengikut Urutan. Jadi sekarang, 6, 5, cantik, dan kemudian dalam masa itu, Tiket betul. Dan sebenarnya, pada ketika ini, kita tidak memerlukan ruangan ini lagi. Oleh itu, saya boleh klik kanan dan hapus, dan kemudian Tutup & Muat - kali ini saya akan benar-benar Tutup & Muat, bukan Tutup & Muat ke-- dan kita mempunyai hasilnya. Baiklah.
Oleh itu, kami beralih dari senarai acara ke senarai besar ini, tetapi inilah bahagian yang mengagumkan: Saya mengetepikannya, Mike Man dan Mekanik bukan Istana B, Istana C. Oleh itu, saya kembali kepada yang asal di sebelah kanan atas - sudut tangan untuk maklumat lebih lanjut mengenai buku.
Baiklah. Topik dalam Episod ini: Kaley di Nashville perlu melakukan VLOOKUP untuk mengembalikan semua perlawanan, biasanya memasukkan baris baru. Dan itu pangkalan data tiket, baiklah? Oleh itu, saya akan menyebutnya Letupan VLOOKUP kerana setiap rancangan akan meletup hingga 16 baris. Kami akan menggunakan Kuasa Kuasa untuk menyelesaikannya, dan saya telah mengetahui bahawa Tarikh akan muncul pada Urutan yang salah kecuali jika kami menambah medan Urutan pada jenis tiket. Buat kedua-dua set ke dalam Jadual dengan Ctrl + T; namakan mereka menjadi Tayangan dan Tiket; dan kemudian dari setiap jadual, Dapatkan Data, Dari Jadual, Tutup & Muat, untuk Membuat sambungan sahaja; ulangi untuk jadual yang lain; kemudian Data, Dapatkan Data, Gabungkan Pertanyaan, Gabungkan; dan kemudian kotak dialog itu, cukup membingungkan bagi saya - pilih Acara, pilih Tiket, klik pada Jenis Tiket di kedua-duanya, ubah sendi menjadi penyambung dalaman,klik OK, dan kemudian anda akan mendapat hasil yang sangat mengecewakan di mana hanya lajur yang menyatakan Jadual, Jadual, Jadual, Jadual; klik ikon Kembangkan di bahagian atas; pilih bidang Urutan Tiket; jangan awalan dengan nama jadual; dan anda boleh Susun mengikut Tarikh, Urutkan mengikut Urutan; Tutup & Muat ke hamparan. Perkara yang indah ialah jika data yang mendasari berubah - Segarkan sahaja dan anda mendapat hasilnya.
Sekarang, hei, untuk memuat turun buku kerja yang digunakan dari video hari ini, lawati URL di sana dalam keterangan YouTube. Juga senarai seminar yang akan datang - Saya ingin melihat anda di salah satu seminar Power Excel langsung saya.
Saya ingin mengucapkan terima kasih kepada Kaley kerana muncul di Nashville dan memberi saya soalan yang hebat. Saya mahu anda kerana berhenti. Sampai jumpa lagi untuk siaran net lain dari.
Muat turun Fail Excel
Untuk memuat turun fail excel: return-all-vlookups.xlsx
Kuasa Kuasa terus memukau saya. Ini adalah yang kedua dari siri tiga hari di mana jawapannya adalah Power Query:
- Selasa: Tukar lajur Tarikh / Masa ke tarikh sahaja
- Hari ini: Kembalikan Semua VLOOKUP
- Khamis: Buat Tinjauan untuk Setiap 1100 item
Saya mempunyai keseluruhan senarai main YouTube mengenai perkara yang akhirnya saya selesaikan dengan Power Query.
Pemikiran Excel Hari Ini
Saya telah meminta nasihat rakan Excel saya mengenai Excel. Pemikiran hari ini untuk merenungkan:
"Ketika dalam Keraguan, gunakan Fungsi ROUND!"
Mike Girvin
