Soalan hari ini dari Quentin yang berada di seminar Atlanta Power Excel saya. Quentin harus menghasilkan 7 soalan tinjauan yang sama untuk setiap 1000+ pelanggan di Excel.
Seperti yang anda lihat dalam gambar ini, pelanggan berada di A. Soalan yang akan diulang ada di ruangan D.
Anda boleh menyelesaikannya dengan VBA atau formula, tetapi ini adalah minggu Power Query pada, jadi saya akan menggunakan trik hebat dalam Power Query.
Sekiranya anda mahukan baris kosong di antara setiap tinjauan, tambahkan urutan nombor dan tambahkan nombor 7 selepas soalan terakhir.
Tekan Ctrl + T dari kedua-dua set data. Namakan set data kedua dengan nama yang anda ingat, seperti Soalan atau Tinjauan.
Dari set data kedua, gunakan Data, Dari Jadual.
Penyunting Kuasa Kuasa dibuka. Dari tab Laman Utama, pilih drop-down Tutup & Muat dan pilih Tutup & Muat Ke…. Dalam dialog seterusnya, pilih Hanya Buat Sambungan.
Anda kini kembali menggunakan Excel. Pilih mana-mana sel dalam jadual pelanggan di lajur A. Data, Dari Jadual. Setelah Editor Pertanyaan dibuka, klik pada tab Tambah Lajur di Pita dan kemudian pilih Lajur Tersuai. Rumusnya adalah =#"Questions"(termasuk tanda # dan tanda petik).
Lajur baru muncul di editor dengan nilai Jadual diulang di setiap baris. Klik ikon Kembangkan di tajuk lajur.
Pilih kedua-dua medan dalam Jadual. Dari tab Laman Utama, pilih Tutup & Muatkan.
Lembaran kerja baru akan muncul dengan 7 soalan diulang untuk setiap 1000+ pelanggan.
Tonton Video
Transkrip Video
Belajar Excel Dari Podcast Episode 2205: Ledakan Tinjauan Pinjaman.
Hei, selamat datang kembali ke netcast, saya Bill Jelen. Sekarang, baru semalam dalam episod 2204, Kaylee dari Nashville yang harus melakukan letupan VLOOKUP - untuk setiap item di lajur D, kami mempunyai banyak item yang sesuai di lajur G dan perlu meledaknya. Jadi, jika Palace C mempunyai 8 item, kita akan mendapat 8 baris.
Sekarang, hari ini, kita mempunyai Quentin. Sekarang, Quentin berada di seminar saya di Atlanta, tetapi dia sebenarnya dari Florida, dan Quentin mempunyai hampir 1000 pelanggan di sini - baik, lebih dari 1000 pelanggan - di lajur A, dan untuk setiap pelanggan, dia perlu membuat tinjauan ini- - tinjauan ini terhadap 1, 2, 3, 4, 5, 6 soalan. Dan apa yang akan saya lakukan di sini ialah saya akan menambah nombor Urutan hanya dengan nombor 1 hingga 7, jadi dengan cara itu, saya dapat membuat baris kosong yang bagus di antara. Saya akan menjadikan kedua-dua set data ini menjadi jadual; jadi, kami berusaha agar 7 baris ini meletup untuk setiap daripada 1000 pelanggan ini. Itulah matlamatnya.
Sekarang, saya boleh melakukan ini dengan VPA; Saya boleh melakukan ini dengan formula; tetapi ia seperti "Power Query Week" di sini, kami sedang menjalankan ini adalah contoh Power Query ketiga kami berturut-turut, jadi saya akan menggunakan Power Query. Saya akan menjadikan yang kiri ini menjadi meja. Saya akan berhati-hati untuk menyebutnya bukan Jadual 1. Saya akan memberikannya nama. Kita mesti menggunakan semula nama itu kemudian, jadi saya akan menyebutnya Soalan - seperti itu. Dan kemudian ini akan menjadi Jadual 2, tetapi saya akan menamakannya menjadi Pelanggan - tidak begitu penting sehingga saya menamakan semula ini kerana yang kedua harus mempunyai nama. Oleh itu, kita akan memilih ini; Data; dan kita akan katakan Dari Jadual / Julat. Dapatkan dan Transform Data - ini dikenali sebagai Power Query. Ia dibina ke dalam Excel 2016. Sekiranya anda mempunyai 2010 atau 2013, pada Windows,bukan Mac, bukan iOS, bukan Android, anda boleh memuat turun Kueri Kuasa secara percuma dari Microsoft.
Oleh itu, kita akan mendapatkan data Dari Jadual / Julat; inilah jadual kami - kami tidak akan melakukan apa-apa, hanya Tutup dan Muatkan; Tutup & Muat Ke; hanya Buat Sambungan; baiklah, dan lihat, nama Pertanyaan itu ialah Soalan. Ia menggunakan nama yang sama seperti di sini. Dan kemudian kita kembali ke yang satu ini, dan, Data; Dari Jadual / Julat; jadi, ada senarai 1000 pelanggan kami atau lebih.
Hei sekarang, inilah teriakan kepada Miguel Escobar, kawan saya, yang merupakan pengarang M Is For (DATA) MONKEY). Saya akan meletakkan pautan ke dalam video - buku hebat mengenai Kuasa Kuasa - membantu saya dalam hal ini. Kami akan memasukkan Column Custom baru, dan formula Custom Column ada di sini: = # "nama pertanyaan". Saya tidak akan pernah memikirkannya tanpa Miguel, jadi terima kasih kepada Miguel untuk itu.
Dan apabila saya mengklik OK, ya, nampaknya tidak berfungsi - kami hanya mendapat meja, meja, meja, tetapi itulah yang kami miliki semalam dengan Kaylee dan tiket. Dan yang harus saya lakukan ialah memperluas ini, dan saya sebenarnya akan mengatakan bahawa saya mungkin tidak memerlukan Urutan … baiklah, mari kita letakkan untuk berjaga-jaga. Kita boleh mengeluarkannya setelah kita melihatnya. Sekarang, kita mempunyai 1000 baris, dan sekarang kita mempunyai 7000 baris - cantik. Saya dapat melihat sekarang ia muncul dalam Urutan, jadi saya tidak memerlukannya. Saya akan klik kanan dan Buang satu lajur itu. Dan kemudian saya dapat Rumah; Tutup & Muatkan; dan BAM! - kita sekarang harus mempunyai lebih dari 7000 baris dengan 6 soalan dan ruang kosong untuk setiap pelanggan. Quentin sangat gembira dengan seminar itu. Tipu daya yang hebat - mengelakkan VBA, mengelakkan sebilangan besar formula menggunakan Indeks,dan perkara-perkara seperti itu - cara yang baik untuk pergi.
Tapi, hei, hari ini, izinkan saya menghantar anda dengan M Is For (DATA) MONKEY. Ken Puls dan Miguel Escobar menulis buku terhebat mengenai Power Query. Saya suka buku itu; dalam 2 jam anda akan menjadi profesional dengan buku itu.
Baiklah, jadi, selesaikan hari ini - Quentin perlu membuat tinjauan serupa untuk 1000 pelanggan yang berbeza. Terdapat 6 atau 7 atau 8 soalan untuk setiap pelanggan. Sekarang, kami dapat melakukan ini dengan VBA atau makro, tetapi, kerana kami menjalankan Power Query di sini, mari lakukan Power Query. Saya menambah soalan kosong pada Soalan; Saya menambah Nombor Urutan, untuk memastikan bahawa kosong kekal di sana; menjadikan pelanggan menjadi meja; jadikan soalan menjadi jadual; sangat penting untuk anda menamakan Soalan sesuatu yang anda boleh ingat - saya memanggil saya "Soalan". Tambahkan soalan ke Kuasa Kuasa, Sebagai Sambungan Sahaja; dan kemudian, semasa anda menambahkan pelanggan ke Kuasa Kuasa, buat lajur khusus baru dengan rumus: # "nama pertanyaan pertama" dan kemudian Kembangkan lajur itu di editor Kueri Kuasa; Tutup &Muat semula ke hamparan, dan anda sudah selesai. Satu muslihat yang luar biasa - Saya suka Power Query - perkara paling hebat berlaku pada Excel dalam 20 tahun.
Saya ingin mengucapkan terima kasih kepada Quentin kerana menunjukkan pada seminar saya. Dia pernah ke seminar saya beberapa kali sebelumnya - lelaki hebat. Saya ingin mengucapkan terima kasih kerana berhenti. Kami akan berjumpa anda di lain kali untuk siaran net lain dari.
Muat turun Fail Excel
Untuk memuat turun fail excel: loan-survey-explosion.xlsx
Kuasa Kuasa terus memukau saya. Lihat buku M adalah untuk Data Monkey untuk mengetahui lebih lanjut Power Query.
Pemikiran Excel Hari Ini
Saya telah meminta nasihat rakan Excel saya mengenai Excel. Pemikiran hari ini untuk merenungkan:
"Anda boleh melakukan apa sahaja dengan AGGREGATE kecuali memahaminya."
Liam Bastick
