Contents [show]
Baru-baru ini, seorang kawan saya mendekati saya dengan permintaan untuk membantu dengan penjanaan semua frasa yang mungkin terdiri daripada satu set perkataan yang diberikan. Masalah seperti ini boleh timbul apabila menyusun senarai kata kunci dan frasa untuk pengiklanan dalam talian dan promosi SEO, apabila anda perlu melalui semua pilih atur perkataan yang mungkin dalam pertanyaan carian:
Dalam matematik, operasi ini dipanggil Produk Cartesian. Takrif rasmi adalah seperti berikut: hasil darab Cartesian bagi set A dan B ialah set semua pasangan, komponen pertama yang dimiliki oleh set A, dan komponen kedua tergolong dalam set B. Selain itu, unsur-unsur set boleh menjadi kedua-duanya. nombor dan teks.
Diterjemahkan ke dalam bahasa manusia, ini bermakna jika dalam set A kita mempunyai, sebagai contoh, perkataan "putih" dan "merah", dan dalam set B "BMW" dan "Mercedes", maka selepas produk Cartesian kedua-dua set ini kita get on the output ialah set semua kemungkinan variasi frasa, terdiri daripada perkataan kedua-dua senarai:
- bmw putih
- bmw merah
- Mercedes putih
- mercedes merah
… iaitu hanya apa yang kita perlukan. Mari lihat beberapa cara untuk menyelesaikan tugasan ini dalam Excel.
Kaedah 1. Formula
Mari kita mulakan dengan formula. Mari kita anggap bahawa sebagai data awal kita mempunyai tiga senarai perkataan asal dalam lajur A, B dan C, masing-masing, dan bilangan elemen dalam setiap senarai boleh berbeza-beza:
Mula-mula, mari kita buat tiga lajur dengan indeks, iaitu nombor ordinal perkataan daripada setiap senarai dalam semua kombinasi yang mungkin. Baris pertama unit (E2:G2) akan dimasukkan secara manual, dan untuk selebihnya kami akan menggunakan formula berikut:
Logik di sini adalah mudah: jika indeks dalam sel sebelumnya yang unggul telah mencapai penghujung senarai, iaitu sama dengan bilangan elemen dalam senarai yang dikira oleh fungsi COUNT (COUNTA), kemudian kita mulakan semula penomboran. Jika tidak, kami meningkatkan indeks sebanyak 1. Beri perhatian khusus kepada penetapan julat yang bijak dengan tanda dolar ($) supaya anda boleh menyalin formula ke bawah dan ke kanan.
Sekarang kita mempunyai nombor ordinal perkataan yang kita perlukan daripada setiap senarai, kita boleh mengekstrak perkataan itu sendiri menggunakan fungsi tersebut INDEKS (INDEX) kepada tiga lajur berasingan:
Jika anda tidak pernah menjumpai fungsi ini dalam kerja anda sebelum ini, maka saya sangat menasihati anda untuk mengkajinya sekurang-kurangnya secara menyerong – ia membantu dalam banyak situasi dan berguna tidak kurang (dan lebih banyak lagi!) VPR (TAMPILAN).
Nah, selepas itu, ia kekal hanya untuk melekatkan serpihan yang terhasil baris demi baris menggunakan simbol penggabungan (&):
… atau (jika anda mempunyai versi terkini Excel) dengan fungsi yang berguna GABUNG (TEXTJOIN), yang boleh melekatkan keseluruhan kandungan sel yang ditentukan melalui aksara pemisah yang diberikan (ruang):
Kaedah 2. Melalui Power Query
Power Query ialah add-in yang berkuasa untuk Microsoft Excel yang melaksanakan dua tugas utama: 1. memuatkan data ke dalam Excel daripada hampir mana-mana sumber luaran dan 2. semua jenis transformasi jadual yang dimuatkan. Power Query telah dibina ke dalam Excel 2016-2019, dan untuk Excel 2010-2013 ia dipasang sebagai tambahan berasingan (anda boleh memuat turunnya daripada tapak web rasmi Microsoft secara percuma). Jika anda masih belum mula menggunakan Power Query dalam kerja anda, maka sudah tiba masanya untuk memikirkannya, kerana transformasi seperti yang diterangkan di atas dilakukan di sana dengan mudah dan semula jadi, hanya dalam beberapa pergerakan.
Mula-mula, mari muatkan senarai sumber sebagai pertanyaan berasingan dalam Power Query. Untuk melakukan ini, bagi setiap jadual, lakukan langkah berikut:
- Mari tukar jadual menjadi "pintar" dengan butang Format sebagai jadual tab Laman Utama (Laman Utama — Format sebagai Jadual) atau pintasan papan kekunci Ctrl+T. Setiap jadual secara automatik akan diberi nama Jadual1,2,3…, yang, bagaimanapun, boleh diubah jika dikehendaki pada tab Pembina (Reka Bentuk).
- Setelah menetapkan sel aktif dalam jadual, tekan butang Dari meja (Dari Jadual) tab Tarikh (Tarikh) atau pada tab Kuasa Kuasa (jika anda telah memasangnya sebagai tambahan berasingan untuk Excel 2010-2013).
- Dalam tetingkap editor pertanyaan yang terbuka, pilih arahan Laman Utama — Tutup dan Muatkan — Tutup dan Muatkan dalam… (Laman Utama — Tutup&Muatkan — Tutup&Muat ke..) dan kemudian pilihan Buat sambungan sahaja (Buat sambungan sahaja). Ini akan meninggalkan jadual yang dimuatkan dalam ingatan dan membolehkannya diakses pada masa hadapan.
Jika anda melakukan semuanya dengan betul, maka output dalam panel kanan hendaklah tiga permintaan dalam mod Sambungan sahaja dengan nama jadual kami:
Sekarang klik kanan pada pertanyaan pertama dan pilih arahan pautan (Rujukan)untuk membuat salinan yang boleh dikemas kini, dan kemudian menambah lajur tambahan pada data melalui arahan Menambah lajur ž – Lajur tersuai (Tambah Lajur -ž Lajur Tersuai). Dalam tetingkap input formula, masukkan nama lajur baharu (contohnya, Fragment2) dan ungkapan yang sangat mudah sebagai formula:
=Jadual2
… iaitu, dengan kata lain, nama pertanyaan kedua:
Selepas mengklik OK kita akan melihat lajur baharu, dalam setiap sel yang mana terdapat jadual bersarang dengan frasa daripada jadual kedua (anda boleh melihat kandungan jadual ini jika anda mengklik di latar belakang sel di sebelah perkataan Jadual):
Ia kekal untuk mengembangkan semua kandungan jadual bersarang ini menggunakan butang dengan anak panah berganda dalam pengepala lajur yang terhasil dan nyahtanda Gunakan nama lajur asal sebagai awalan (Gunakan nama lajur asal sebagai awalan):
… dan kita mendapat semua kemungkinan kombinasi unsur daripada dua set pertama:
Tambahan pula, semuanya serupa. Tambahkan satu lagi lajur yang dikira dengan formula:
=Jadual3
…, dan kemudian kembangkan jadual bersarang sekali lagi – dan kini kita sudah mempunyai semua pilihan yang mungkin untuk mengubah suai perkataan daripada tiga set, masing-masing:
Ia kekal untuk memilih ketiga-tiga lajur dari kiri ke kanan, sambil menahan Ctrl, dan gabungkan kandungannya dipisahkan oleh ruang menggunakan arahan Gabungkan lajur (Gabung Lajur) daripada tab Transformasi (Transformasi):
Keputusan yang terhasil boleh dipunggah kembali ke helaian dengan arahan yang sudah biasa Laman Utama — Tutup dan Muatkan — Tutup dan Muatkan dalam… (Laman Utama — Tutup&Muatkan — Tutup&Muat ke..):
Jika pada masa hadapan sesuatu berubah dalam jadual sumber kami dengan serpihan, maka sudah cukup untuk mengemas kini pertanyaan yang dijana dengan mengklik kanan pada jadual yang terhasil dan memilih arahan Kemas kini & Simpan (Segarkan semula) atau dengan menekan pintasan papan kekunci Ctrl+Alt+F5.
- Apakah itu Power Query, Power Pivot, Power Map dan Power BI dan mengapa mereka memerlukan pengguna Excel
- Mencipta Carta Gantt dalam Power Query
- 5 cara untuk menggunakan fungsi INDEX