Pertanyaan hari ini dari Quentin yang menghadiri seminar Atlanta Power Excel saya. Quentin harus membuat 7 pertanyaan survei yang sama untuk setiap 1000+ pelanggan di Excel.
Seperti yang terlihat pada gambar ini, pelanggan ada di A. Pertanyaan yang harus diulang ada di kolom D.
Anda bisa menyelesaikan ini dengan VBA atau rumus, tapi ini minggu Power Query di, jadi saya akan menggunakan trik keren di Power Query.
Jika Anda menginginkan baris kosong di antara setiap survei, tambahkan nomor urut dan tambahkan nomor 7 setelah pertanyaan terakhir.
Tekan Ctrl + T dari kedua kumpulan data. Beri nama kumpulan data kedua dengan nama yang dapat Anda ingat, seperti Pertanyaan atau Survei.
Dari kumpulan data kedua, gunakan Data, Dari Tabel.
Editor Power Query terbuka. Dari tab Home, pilih Close & Load drop-down dan pilih Close & Load To…. Di dialog berikutnya, pilih Only Create a Connection.
Anda sekarang kembali ke Excel. Pilih sel mana pun di tabel pelanggan di kolom A. Data, Dari Tabel. Setelah Editor Kueri terbuka, klik pada tab Tambahkan Kolom di Pita lalu pilih Kolom Kustom. Rumusnya adalah =#"Questions"(termasuk # dan tanda kutip).
Kolom baru muncul di editor dengan tabel nilai diulang di setiap baris. Klik ikon Perluas di tajuk kolom.
Pilih kedua bidang di Tabel. Dari tab Beranda, pilih Tutup & Muat.
Lembar kerja baru akan muncul dengan 7 pertanyaan yang diulang untuk masing-masing dari 1000+ pelanggan.
Menonton video
Transkrip Video
Pelajari Excel Dari Podcast Episode 2205: Ledakan Survei Pinjaman.
Hai, selamat datang kembali di netcast, saya Bill Jelen. Sekarang, baru kemarin di episode 2204, Kaylee dari Nashville yang harus melakukan ledakan VLOOKUP-- untuk setiap item di sini di kolom D, kami memiliki sekumpulan item yang cocok di kolom G dan perlu meledakkannya. Jadi, jika Istana C punya 8 item, kita akan mendapat 8 baris.
Sekarang, hari ini, kami memiliki Quentin. Sekarang, Quentin mengikuti seminar saya di Atlanta, tetapi sebenarnya dia dari Florida, dan Quentin memiliki hampir 1000 pelanggan di sini - yah, lebih dari 1000 pelanggan - di kolom A, dan untuk setiap pelanggan, dia perlu membuat survei ini- - survei 1, 2, 3, 4, 5, 6 pertanyaan ini. Dan yang akan saya lakukan di sini adalah saya akan menambahkan nomor Urutan hanya dengan angka 1 hingga 7, sehingga saya dapat membuat baris kosong yang bagus di antaranya. Saya akan membuat kedua kumpulan data ini menjadi sebuah tabel; jadi, kami mencoba membuat 7 baris ini meledak untuk setiap dari 1000 pelanggan ini. Itulah tujuannya.
Sekarang, saya bisa melakukan ini dengan VPA; Saya bisa melakukan ini dengan rumus; tapi ini semacam "Power Query Week", kami sedang menjalankan ini adalah contoh Power Query ketiga kami berturut-turut, jadi saya akan menggunakan Power Query. Saya akan membuat yang kiri ini menjadi sebuah meja. Saya akan sangat berhati-hati untuk menamai ini, bukan Tabel 1. Saya akan memberinya nama. Kita harus menggunakan kembali nama itu nanti, jadi saya akan menyebutnya Pertanyaan-- seperti itu. Dan ini akan menjadi Tabel 2, tapi saya akan mengganti namanya menjadi Pelanggan-- tidak begitu penting sehingga saya mengganti nama ini karena ini yang kedua harus diberi nama. Jadi, kami akan memilih ini; Data; dan kami akan mengatakan From Table / Range. Dapatkan dan Transformasikan Data-- ini dikenal sebagai Power Query. Ini dibangun ke dalam Excel 2016. Jika Anda memiliki 2010 atau 2013, di Windows,bukan Mac, bukan iOS, atau Android, Anda dapat mengunduh Power Query secara gratis dari Microsoft.
Jadi, kita akan mendapatkan data From Table / Range; inilah tabel kami-- kami tidak akan melakukan apa pun untuk itu, cukup Tutup dan Muat; Tutup & Muat Ke; hanya Buat Koneksi; baiklah, dan lihat, nama Kueri itu adalah Pertanyaan. Ini menggunakan nama yang sama seperti di sini. Dan kemudian kita kembali ke yang satu ini, dan, Data; Dari Tabel / Rentang; jadi, ada daftar 1000 atau lebih pelanggan kami.
Hai sekarang, ini teriakan untuk Miguel Escobar, teman saya, rekan penulis M Is For (DATA) MONKEY). Saya akan memasang tautan ke sana di video-- buku hebat tentang Power Query - membantu saya dalam hal ini. Kita akan memasukkan Kolom Kustom baru, dan rumus Kolom Kustom ada di sini: = # "nama kueri". Saya tidak akan pernah tahu tanpa Miguel, jadi terima kasih kepada Miguel untuk itu.
Dan ketika saya mengklik Oke, ya, sepertinya itu tidak berhasil - kami hanya mendapatkan meja, meja, meja, tapi itulah yang kami miliki kemarin dengan Kaylee dan tiketnya. Dan yang harus saya lakukan adalah memperluas ini, dan saya benar-benar akan mengatakan bahwa saya mungkin tidak membutuhkan Urutan… baik, mari kita jaga-jaga. Kita bisa mengeluarkannya setelah kita melihatnya. Saat ini, kami memiliki 1000 baris, dan sekarang kami memiliki 7000 baris-- indah. Saya dapat melihat sekarang bahwa ini muncul di Sequence, jadi saya tidak membutuhkannya. Saya akan klik kanan dan Hapus hanya satu kolom itu. Dan kemudian saya bisa pulang; Tutup & Muat; dan BAM! - sekarang kami memiliki lebih dari 7000 baris dengan 6 pertanyaan dan ruang kosong untuk setiap pelanggan. Quentin sangat senang dengan hal itu di seminar. Trik keren dan keren-- hindari VBA, hindari sejumlah besar rumus menggunakan Indeks,dan hal-hal seperti itu-- cara yang bagus untuk melakukannya.
Tapi, hei, hari ini, izinkan saya mengirim Anda dengan M Is For (DATA) MONKEY. Ken Puls dan Miguel Escobar menulis buku terhebat tentang Power Query. Saya suka buku itu; dalam 2 jam Anda akan menjadi profesional dengan buku itu.
Baiklah, jadi, selesaikan hari ini-- Quentin perlu membuat survei yang identik untuk 1000 pelanggan yang berbeda. Ada 6 atau 7 atau 8 pertanyaan untuk setiap pelanggan. Sekarang, kita bisa melakukan ini dengan VBA atau makro, tapi, karena kita menjalankan Power Query di sini, mari kita lakukan Power Query. Saya menambahkan pertanyaan kosong ekstra ke Pertanyaan; Saya menambahkan Nomor Urutan, untuk memastikan bahwa kosong tetap di sana; buat pelanggan menjadi meja; buat pertanyaan menjadi tabel; sangat penting bagi Anda untuk menamai Pertanyaan dengan sesuatu yang dapat Anda ingat-- saya menyebut pertanyaan saya "Pertanyaan". Tambahkan pertanyaan ke Power Query, Sebagai Koneksi Saja; lalu, saat Anda menambahkan pelanggan ke Power Query, buat kolom kustom baru dengan rumus: # "nama kueri pertama" lalu Perluas kolom tersebut di editor Power Query; Menutup &Muat kembali ke spreadsheet, dan selesai. Trik luar biasa-- Saya suka Power Query-- hal terbesar yang terjadi pada Excel dalam 20 tahun.
Saya ingin berterima kasih kepada Quentin karena telah tampil di seminar saya. Dia pernah ke seminar saya beberapa kali sebelumnya-- pria hebat. Saya ingin mengucapkan terima kasih telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh File Excel
Untuk mengunduh file excel: pinjaman-survei-ledakan.xlsx
Power Query terus membuat saya takjub. Lihat buku M untuk Data Monkey untuk mempelajari Power Query selengkapnya.
Pemikiran Excel Hari Ini
Saya telah meminta saran dari teman-teman Master Excel saya tentang Excel. Hari ini pemikiran untuk direnungkan:
"Anda dapat melakukan apa saja dengan AGGREGATE kecuali memahaminya."
Liam Bastick
