Kaley dari Nashville sedang mengerjakan spreadsheet tiket. Untuk setiap acara, dia memilih rencana tiket. Rencana tiket itu dapat menunjukkan dari 4 hingga 16 jenis tiket untuk acara tersebut. Kaley menginginkan rumus yang akan masuk ke tabel pencarian dan mengembalikan * semua * cocok, memasukkan baris baru yang sesuai.
Meskipun saya tidak memiliki VLOOKUP yang bisa mengatasi ini, alat Power Query baru yang ada di dalam Excel 2016 bisa menyelesaikannya.
Catatan
Jika Anda memiliki versi Windows Excel 2010 atau Excel 2013, Anda dapat mengunduh Power Query secara gratis dari Microsoft. Sayangnya, Power Query belum tersedia untuk Excel untuk Android, Excel untuk ios atau Excel untuk Mac.
Untuk mengilustrasikan tujuan: Mike McCann dan Mekanika muncul di Teater Allen dengan rencana tiket C. Karena ada empat baris yang cocok di tabel pencarian, Kaley menginginkan empat baris yang bertuliskan Mike McCann dan Mekanika, masing-masing dengan kecocokan berbeda dari tabel pencarian.
Pilih sel di tabel asli. Tekan Ctrl + T untuk menandai data tersebut sebagai tabel. Pada tab Alat Tabel, ganti nama tabel dari Tabel1 menjadi Pertunjukan. Ulangi untuk tabel pencarian, menyebutnya Tickets.
Pilih sel di tabel Perlihatkan. Dari tab Data, pilih Dari Tabel / Rentang.
Setelah editor Power Query terbuka, buka drop-down Tutup & Muat dan pilih Tutup dan Muat Ke….
Dalam dialog Impor Data, pilih Hanya Buat Koneksi.
Buka tabel Tiket. Ulangi langkah-langkah untuk Hanya Membuat Koneksi ke Tiket. Anda akan melihat kedua koneksi di panel Queries:
Pilih sel kosong mana saja. Pilih Data, Dapatkan Data, Gabungkan Kueri, Gabungkan.
Ada enam langkah dalam dialog Gabung. Tanggal 3 dan 4 sepertinya tidak intuitif bagi saya.
- Pilih Acara dari menu drop-down atas
- Pilih Tiket dari menu drop-down kedua.
- Klik pada tajuk Rencana Tiket di atas untuk memilih kolom itu sebagai kunci asing di tabel Pertunjukan.
- Klik pada tajuk Rencana Tiket di bagian bawah untuk memilih kolom tersebut sebagai bidang kunci di tabel pencarian.
- Buka tipe Gabung dan pilih Batin (hanya baris yang cocok).
- Klik OK
Hasilnya awalnya mengecewakan. Anda melihat semua bidang dari tabel 1 dan kolom yang bertuliskan Tabel, Tabel, Tabel.
Klik ikon Perluas di bagian atas kolom Tiket.
Batalkan pilihan Paket Tiket karena Anda sudah memiliki bidang itu. Bidang yang tersisa akan disebut Jenis Tiket.Tiket kecuali Anda menghapus centang Gunakan Nama Asli sebagai Awalan.
Keberhasilan! Setiap baris untuk setiap pertunjukan meledak menjadi beberapa baris.
Saya tidak terlalu senang dengan penyortiran data. Menyortir menurut Tanggal menyebabkan Jenis Tiket diurutkan dengan cara yang aneh.
Menonton video
Dalam kasus hari ini, video diambil setelah artikel ditulis. Saya sarankan menambahkan kolom urutan ke Jenis Tiket untuk mengontrol urutan sortir.
Transkrip Video
Belajar Excel dari Podcast, Episode 2204: Kembalikan Semua VLOOKUP.
Hai, selamat datang kembali di netcast, saya Bill Jelen. Pertanyaan hari ini dari Nashville Music City. Saya berada di sana di Nashville, seseorang bertanggung jawab untuk menjadwalkan pemuatan tiket ke dalam sistem tiket dan inilah yang kami miliki: Kami memiliki daftar acara - acara mendatang - kami memiliki tanggal, tempat, dan rencana tiket. Jadi, seperti, meskipun ada sesuatu yang diadakan di Istana, mungkin ada rencana tiket yang berbeda - seperti, mungkin lantainya dikonfigurasi, Anda tahu, dengan kursi atau mungkin hanya ruang berdiri saja, bukan?
Jadi, tergantung pada jenis paket tiket apa, Anda harus datang ke sini ke tabel Pencarian dan menemukan semua acara yang cocok, dan pada dasarnya kita akan melakukan apa yang saya sebut ledakan VLOOKUP. Jadi jika ada sesuatu di Hannah C, mereka akan pergi ke Hannah C dan jika ada-- 1, 2, 3, 4, 5, 6-- 7 item di Hannah C, kami akan memiliki untuk mengembalikan tujuh baris-- yang berarti Anda harus memasukkan enam baris lagi dan menyalin datanya. Baik.
Sekarang, kami tidak akan melakukan ini sama sekali dengan VLOOKUP, tetapi Anda mendapatkan konsepnya-- kami melakukan VLOOKUP dan kami mengembalikan semua jawaban sebagai baris baru. Baiklah, jadi, saya akan mengambil kedua tabel ini dan membuatnya menjadi tabel nyata dengan Ctrl + T. Yang pertama disebut Tabel 1-- nama yang mengerikan, sebut saja Acara atau Pertunjukan ini, sebut saja Acara, seperti itu-- dan yang kedua, sekarang, hei, inilah yang saya pelajari karena saya berlatih ini-- kita harus memiliki bidang urutan di sini. Jadi = ROW (A1), klik dua kali, dan salin ke bawah lalu salin dan tempel nilai khusus. Baik. Sekarang kita membuatnya menjadi tabel-- Ctrl + T, dan kita akan menyebutnya Tickets.
Baik. Jadi kami punya pertunjukan, kami punya tiket. Saya akan pergi ke tab Data, dan saya di sini dalam hal acara, saya ingin mengatakan bahwa saya ingin mendapatkan data saya dari Tabel atau Rentang-- ini adalah Power Query. Jika Anda kembali menggunakan Excel 2010 atau 2013, Anda bisa mengunduh ini secara gratis dari Microsoft, unduh alat Power Query. Jika Anda menggunakan Mac atau iOS atau Android, maaf, tidak ada Power Query untuk Anda. Baiklah, jadi dari Tabel atau Rentang … temukan seseorang yang memiliki-- temukan teman yang memiliki-- PC Windows dan minta mereka mengaturnya. Baik. Ini tabelnya, kita tidak akan melakukan apa pun untuk ini, cukup Tutup & Muat, Tutup & Muat ke, lalu katakan "Hanya Buat Koneksi", sempurna. Kami akan datang ke sini ke tabel kedua kami: Dapatkan Data, Dari Tabel atau Rentang, kami tidak melakukan apa pun untuk yang ini, Tutup & Muat,Tutup & Muat ke, "Hanya Buat Koneksi", OK. Jadi apa yang kita miliki sekarang, adalah kita memiliki koneksi ke tabel pertama dan koneksi ke tabel kedua. Kami tidak akan menggabungkan keduanya, yang pada dasarnya seperti melakukan VLOOKUP, atau Database Joint, saya kira, seperti apa rasanya. Gabungkan Kueri, kita akan Gabungkan. Baik.
Sekarang, tujuh hal yang harus Anda lakukan dalam kotak dialog ini - dan ini sedikit membingungkan - kita akan memilih Shows as the first table; pilih Tiket sebagai tabel kedua; pilih bidang apa yang sama-sama mereka miliki, dan ini bisa berupa beberapa bidang-- Anda dapat mengontrol-klik-- tetapi dalam kasus ini hanya ada satu paket tiket; dan kemudian Rencana Tiket; dan kemudian kita akan mengubah tipe Gabung menjadi gabungan dalam dengan "hanya baris yang cocok". Baik. Sekarang, Anda mengeklik OK dan Anda berpikir bahwa seluruh masalah Anda akan terpecahkan, tetapi Anda hancur karena inilah semua data dari A-- mereka belum memasukkan baris baru sama sekali-- dan di sini, hanya bidang bodoh yang membosankan disebut Tiket yang hanya memiliki Meja, Meja, Meja, hah.
Tapi, untungnya, di bagian atasnya ada ikon Perluas, dan kami akan memperluas itu-- Saya tidak perlu mengambil rencana, saya sudah punya-- Jenis Tiket dan Urutan. Saya tidak ingin itu disebut Tickets.TicketType, yang ingin dilakukan Power Query-- jadi saya hapus centang pada kotak ini. Baik. Saat ini kami memiliki 17 baris data; ketika saya mengklik OK, BAM! Ada ledakannya. Jadi, Michael Seeley dan Starlighter muncul dengan semua tipe tiket yang berbeda, seperti ini. Baiklah, dan lihat jenis tiket ini muncul secara berurutan, itu bagus. Tapi Michael Seeley bukanlah pertunjukan berikutnya, pertunjukan berikutnya adalah pada 5 Juni. Jadi ketika saya mencoba dan mengurutkan ini berdasarkan Tanggal-- ini membuat saya gila, saya tidak dapat menjelaskan ini. Urutkan berdasarkan Tanggal, dan Mike Man dan Mekanik muncul menjadi 65, tapi kemudian semua tiket kacau. Mereka'berada di urutan yang salah, dan itulah mengapa saya harus melakukan urutan ini - terasa seperti itu. Saya dapat mengurutkan berdasarkan Urutan. Jadi sekarang, 6, 5, cantik, dan kemudian di dalamnya, Tiket sudah benar. Dan sebenarnya, saat ini, kita tidak membutuhkan kolom ini lagi. Jadi saya dapat mengklik kanan dan menghapus, lalu Tutup & Muat-- kali ini saya akan benar-benar Menutup & Memuat, bukan Menutup & Muat ke-- dan kami mendapatkan hasilnya. Baik.
Jadi kami beralih dari daftar peristiwa ke daftar besar ini, tetapi inilah bagian yang mengagumkan: Saya mengacaukannya, Mike Man dan Mekanik bukanlah Istana B, ini adalah Istana C. Jadi saya kembali ke aslinya di kanan atas pojok tangan untuk informasi lebih lanjut tentang buku.
Baik. Topik dalam Episode ini: Kaley di Nashville perlu melakukan VLOOKUP untuk mengembalikan semua pertandingan, biasanya memasukkan baris baru. Dan ini database tiket, oke? Jadi saya akan menyebutnya Ledakan VLOOKUP karena setiap pertunjukan akan meledak hingga 16 baris. Kita akan menggunakan Power Query untuk menyelesaikan ini, dan saya telah mengetahui bahwa Tanggal akan muncul di Urutan yang salah kecuali kita menambahkan bidang Urutan ke tipe tiket. Buat kedua set menjadi Tabel dengan Ctrl + T; eename mereka menjadi Shows and Tickets; lalu dari setiap tabel, Dapatkan Data, Dari Tabel, Tutup & Muat, ke Hanya buat koneksi; ulangi untuk tabel lainnya; lalu Data, Dapatkan Data, Gabungkan Kueri, Gabungkan; lalu kotak dialog itu, itu cukup membingungkan saya-- pilih Acara, pilih Tiket, klik Jenis Tiket di keduanya, ubah sambungan menjadi gabungan dalam,klik OK, dan kemudian Anda mendapatkan hasil yang sangat mengecewakan di mana itu hanya kolom yang bertuliskan Tabel, Tabel, Tabel, Tabel; klik ikon Perluas di bagian atas itu; pilih bidang Urutan Tiket; jangan awali dengan nama tabel; dan Anda dapat Mengurutkan berdasarkan Tanggal, Mengurutkan berdasarkan Urutan; Tutup & Muat ke spreadsheet. Hal yang indah adalah jika data yang mendasarinya berubah-- Segarkan saja dan Anda akan mendapatkan hasilnya.
Sekarang, hei, untuk mengunduh buku kerja yang digunakan dari video hari ini, kunjungi URL di bawah sana di deskripsi YouTube. Juga daftar seminar yang akan datang-- Saya ingin melihat Anda di salah satu seminar Power Excel saya secara langsung.
Saya ingin berterima kasih kepada Kaley karena telah muncul di Nashville dan memberi saya pertanyaan yang bagus itu. Saya ingin Anda mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh File Excel
Untuk mengunduh file excel: return-all-vlookups.xlsx
Power Query terus membuat saya takjub. Ini adalah seri tiga hari kedua di mana jawabannya adalah Power Query:
- Selasa: Mengonversi kolom Tanggal / Waktu menjadi tanggal saja
- Hari ini: Kembalikan Semua VLOOKUP
- Kamis: Buat Survei untuk Masing-masing dari 1100 item
Saya memiliki seluruh Daftar Putar YouTube hal-hal yang akhirnya saya selesaikan dengan Power Query.
Pemikiran Excel Hari Ini
Saya telah meminta saran dari teman-teman Master Excel saya tentang Excel. Hari ini pemikiran untuk direnungkan:
"Saat Ragu, gunakan Fungsi ROUND!"
Mike Girvin
