Baru-baru ini, seorang teman saya mendekati saya dengan permintaan untuk membantu membuat semua kemungkinan frasa yang terdiri dari serangkaian kata yang diberikan. Masalah semacam ini dapat muncul ketika menyusun daftar kata kunci dan frasa untuk iklan online dan promosi SEO, ketika Anda harus melalui semua kemungkinan permutasi kata dalam permintaan pencarian:
Dalam matematika, operasi ini disebut produk kartesius. Definisi resminya adalah sebagai berikut: produk Cartesian dari himpunan A dan B adalah himpunan semua pasangan, komponen pertama termasuk dalam himpunan A, dan komponen kedua termasuk dalam himpunan B. Selain itu, elemen-elemen himpunan dapat berupa keduanya angka dan teks.
Diterjemahkan ke dalam bahasa manusia, ini berarti bahwa jika di himpunan A kita memiliki, misalnya, kata "putih" dan "merah", dan di himpunan B "BMW" dan "Mercedes", maka setelah produk Cartesian dari dua set ini kita get on the output adalah kumpulan semua kemungkinan varian frasa, terdiri dari kata-kata dari kedua daftar:
- bmw putih
- bmw merah
- Mercedes putih
- mercedes merah
… yaitu hanya apa yang kita butuhkan. Mari kita lihat beberapa cara untuk menyelesaikan tugas ini di Excel.
Metode 1. Rumus
Mari kita mulai dengan rumus. Mari kita asumsikan bahwa sebagai data awal kita memiliki tiga daftar kata asli di kolom A, B dan C, masing-masing, dan jumlah elemen dalam setiap daftar dapat bervariasi:
Pertama, mari kita buat tiga kolom dengan indeks, yaitu nomor urut kata dari setiap daftar dalam semua kemungkinan kombinasi. Baris pertama unit (E2:G2) akan dimasukkan secara manual, dan sisanya akan menggunakan rumus berikut:
Logikanya di sini sederhana: jika indeks di sel superior sebelumnya telah mencapai akhir daftar, yaitu sama dengan jumlah elemen dalam daftar yang dihitung oleh fungsi COUNT (JUMLAH), lalu kita restart penomorannya. Jika tidak, kami meningkatkan indeks sebesar 1. Berikan perhatian khusus pada penetapan rentang yang cerdik dengan tanda dolar ($) sehingga Anda dapat menyalin rumus ke bawah dan ke kanan.
Sekarang kita memiliki nomor urut kata yang kita butuhkan dari setiap daftar, kita dapat mengekstrak kata-kata itu sendiri menggunakan fungsi INDEKS (INDEKS) menjadi tiga kolom terpisah:
Jika Anda belum pernah menemukan fungsi ini dalam pekerjaan Anda sebelumnya, maka saya sangat menyarankan Anda untuk mempelajarinya setidaknya secara diagonal – ini membantu dalam banyak situasi dan berguna tidak kurang (dan bahkan lebih!) VPR (VLOOKUP).
Nah, setelah itu, tinggal merekatkan fragmen yang dihasilkan baris demi baris menggunakan simbol gabungan (&):
… atau (jika Anda memiliki versi Excel terbaru) dengan fungsi praktis MENGGABUNGKAN (TEKS BERGABUNG), yang dapat merekatkan seluruh isi sel yang ditentukan melalui karakter pemisah (spasi):
Metode 2. Melalui Power Query
Power Query adalah add-in canggih untuk Microsoft Excel yang melakukan dua tugas utama: 1. memuat data ke Excel dari hampir semua sumber eksternal, dan 2. semua jenis transformasi tabel yang dimuat. Power Query sudah ada di Excel 2016-2019, dan untuk Excel 2010-2013 diinstal sebagai add-in terpisah (Anda bisa mengunduhnya dari situs web resmi Microsoft secara gratis). Jika Anda belum mulai menggunakan Power Query dalam pekerjaan Anda, maka inilah saatnya untuk memikirkannya, karena transformasi seperti yang dijelaskan di atas dilakukan di sana dengan mudah dan alami, hanya dalam beberapa gerakan.
Pertama, mari muat daftar sumber sebagai kueri terpisah di Power Query. Untuk melakukannya, untuk setiap tabel, lakukan langkah-langkah berikut:
- Mari kita ubah tabel menjadi tabel "pintar" dengan tombol Format sebagai tabel tab Beranda (Beranda — Format sebagai Tabel) atau pintasan keyboard Ctrl+T. Setiap tabel akan otomatis diberi nama Tabel1,2,3…, yang, bagaimanapun, dapat diubah jika diinginkan pada tab Pembina (Rancangan).
- Setelah mengatur sel aktif di tabel, tekan tombol Dari meja (Dari Tabel) tab Data (Tanggal) atau di tab Kueri Daya (jika Anda menginstalnya sebagai add-in terpisah untuk Excel 2010-2013).
- Di jendela editor kueri yang terbuka, pilih perintah Beranda — Tutup dan Muat — Tutup dan Muat di… (Beranda — Tutup&Muat — Tutup&Muat ke..) dan kemudian opsi Buat saja koneksi (Buat koneksi saja). Ini akan meninggalkan tabel yang dimuat dalam memori dan memungkinkannya untuk diakses di masa mendatang.
Jika Anda melakukan semuanya dengan benar, maka output di panel kanan harus berupa tiga permintaan dalam mode Hanya koneksi dengan nama tabel kami:
Sekarang klik kanan pada kueri pertama dan pilih perintah Link (Referensi)untuk membuat salinan yang dapat diperbarui, dan kemudian menambahkan kolom tambahan ke data melalui perintah Menambahkan kolom – Kolom khusus (Tambahkan Kolom - Kolom Kustom). Di jendela masukan rumus, masukkan nama kolom baru (misalnya, Fragmen2) dan ekspresi yang sangat sederhana sebagai rumus:
=Tabel2
… yaitu, dengan kata lain, nama kueri kedua:
Setelah mengklik OK kita akan melihat kolom baru, di setiap sel yang akan ada tabel bersarang dengan frasa dari tabel kedua (Anda dapat melihat isi tabel ini jika Anda mengklik di latar belakang sel di sebelah kata tabel):
Tetap memperluas semua konten tabel bersarang ini menggunakan tombol dengan panah ganda di tajuk kolom yang dihasilkan dan hapus centang Gunakan nama kolom asli sebagai awalan (Gunakan nama kolom asli sebagai awalan):
… dan kami mendapatkan semua kemungkinan kombinasi elemen dari dua set pertama:
Selanjutnya, semuanya serupa. Tambahkan kolom terhitung lainnya dengan rumus:
=Tabel3
…, dan kemudian perluas tabel bersarang lagi – dan sekarang kita sudah memiliki semua opsi yang memungkinkan untuk mengubah kata dari tiga set, masing-masing:
Tetap memilih ketiga kolom dari kiri ke kanan, menahan Ctrl, dan gabungkan isinya yang dipisahkan oleh spasi menggunakan perintah Gabungkan kolom (Gabungkan Kolom) dari tab Transformasi (Mengubah):
Hasil yang dihasilkan dapat diturunkan kembali ke lembar dengan perintah yang sudah dikenal Beranda — Tutup dan Muat — Tutup dan Muat di… (Beranda — Tutup&Muat — Tutup&Muat ke..):
Jika di masa depan sesuatu berubah dalam tabel sumber kami dengan fragmen, maka cukup memperbarui kueri yang dihasilkan dengan mengklik kanan pada tabel yang dihasilkan dan memilih perintah Perbarui & Simpan (Menyegarkan) atau dengan menekan pintasan keyboard Ctrl+lain+F5.
- Apa itu Power Query, Power Pivot, Power Map, dan Power BI dan mengapa mereka membutuhkan pengguna Excel
- Membuat Bagan Gantt di Power Query
- 5 cara menggunakan fungsi INDEX