最近、私の友人が、与えられた単語のセットからなるすべての可能なフレーズを生成するのを手伝ってほしいという依頼で私に近づきました。 この種の問題は、オンライン広告や SEO プロモーション用のキーワードやフレーズのリストをコンパイルするときに発生する可能性があります。検索クエリ内の単語の可能な順列をすべて調べる必要がある場合です。
数学では、この操作は デカルト積. 公式の定義は次のとおりです。セット A と B のデカルト積はすべてのペアのセットであり、その最初のコンポーネントはセット A に属し、XNUMX 番目のコンポーネントはセット B に属します。さらに、セットの要素は両方である可能性があります。数字とテキスト。
人間の言語に翻訳すると、これは、たとえばセット A に「白」と「赤」という単語があり、セット B に「BMW」と「メルセデス」という単語がある場合、これら XNUMX つのセットのデカルト積の後に、出力の get は、両方のリストの単語で構成された、可能なすべてのバリエーションのフレーズのセットです。
- 白bmw
- 赤bmw
- 白いメルセデス
- 赤メルセデス
…つまり、必要なものだけです。 Excel でこのタスクを解決する方法をいくつか見てみましょう。
方法 1. 数式
式から始めましょう。 初期データとして、列 A、B、C にそれぞれ元の単語の XNUMX つのリストがあり、各リストの要素数が異なる場合があるとします。
まず、インデックス付きの 2 つの列を作成しましょう。つまり、可能なすべての組み合わせの各リストからの単語の序数です。 単位の最初の行 (E2:GXNUMX) は手動で入力し、残りは次の式を使用します。
ここでの論理は単純です: 上位の前のセルのインデックスが既にリストの最後に達している場合、つまり、関数によって計算されたリスト内の要素の数と等しい場合 COUNT (カウンター)、次に番号付けを再開します。 それ以外の場合は、インデックスを 1 増やします。数式を下と右にコピーできるように、ドル記号 ($) で範囲を巧妙に固定することに特に注意してください。
各リストから必要な単語の序数を取得したので、関数を使用して単語自体を抽出できます。 INDEX （索引） 次の XNUMX つの列に分けます。
これまで仕事でこの機能に出くわしたことがない場合は、少なくとも斜めに勉強することを強くお勧めします。これは多くの状況で役立ち、それ以上に便利です! VPR （VLOOKUP）.
その後、連結記号 (&) を使用して、結果のフラグメントを行ごとに接着するだけです。
… または (最新バージョンの Excel を使用している場合) 便利な関数を使用して 組み合わせる (テキスト結合)、指定された区切り文字 (スペース) を介して指定されたセルの内容全体を接着できます。
方法 2. Power Query を使用する
Power Query は、Microsoft Excel 用の強力なアドインであり、1. ほぼすべての外部ソースから Excel にデータを読み込む、2. 読み込まれたテーブルのあらゆる種類の変換という 2016 つの主なタスクを実行します。 Power Query は Excel 2019-2010 に既に組み込まれており、Excel 2013-XNUMX の場合は別のアドインとしてインストールされます (Microsoft の公式 Web サイトから無料でダウンロードできます)。 仕事で Power Query の使用をまだ開始していない場合は、考えてみてください。上記のような変換は、数回の操作で簡単かつ自然に行われるからです。
まず、ソース リストを Power Query の個別のクエリとして読み込みましょう。 これを行うには、テーブルごとに次の手順を実行します。
- ボタンでテーブルを「スマート」テーブルに変えましょう 表としてフォーマットする タブ ホーム (ホーム — テーブルとしてフォーマット) またはキーボードショートカット Ctrlキー+T. 各テーブルには自動的に名前が付けられます 表1,2,3、XNUMX、XNUMX…、ただし、必要に応じてタブで変更できます コンストラクタ （設計）.
- テーブルにアクティブセルを設定したら、 ボタンを押します テーブルから (表より) タブ 且つ （日にち） またはタブで パワークエリ (Excel 2010-2013 用の別のアドインとしてインストールしている場合)。
- 開いたクエリ エディター ウィンドウで、コマンドを選択します。 ホーム — 閉じてロード — 閉じてロード… (ホーム — クローズ & ロード — クローズ & ロードへ..) そして、オプション 接続を作成するだけ (接続のみ作成). これにより、ロードされたテーブルがメモリに残り、将来アクセスできるようになります。
すべてを正しく行うと、右側のパネルの出力は、モードの XNUMX つの要求になります。 接続のみ 私たちのテーブル名で：
最初のクエリを右クリックして、コマンドを選択します リンク （参照）その更新可能なコピーを作成し、コマンドを使用してデータに追加の列を追加します 列の追加 ž – カスタム列 (列を追加 -ž カスタム列). 式入力ウィンドウで、新しい列の名前 (Fragment2 など) と非常に単純な式を式として入力します。
=表2
… つまり、XNUMX 番目のクエリの名前:
をクリックした後 OK 新しい列が表示されます。各セルには、XNUMX 番目のテーブルのフレーズを含むネストされたテーブルがあります (単語の横にあるセルの背景をクリックすると、これらのテーブルの内容を確認できます) 表):
結果の列のヘッダーにある二重矢印の付いたボタンを使用し、チェックを外して、これらのネストされたテーブルのすべてのコンテンツを展開する必要があります。 元の列名をプレフィックスとして使用する (元の列名をプレフィックスとして使用):
…そして、最初の XNUMX つのセットから要素のすべての可能な組み合わせを取得します。
さらに、すべてが似ています。 次の式を使用して別の計算列を追加します。
=表3
…そして、ネストされたテーブルを再び展開します。これで、XNUMX つのセットから単語を並べ替えるためのすべての可能なオプションがそれぞれ用意されました。
押したまま、左から右に XNUMX つの列すべてを選択する必要があります。 Ctrlキー、およびコマンドを使用してスペースで区切られた内容を連結します 列を結合 (列を結合) タブから 変換 （変身）:
得られた結果は、使い慣れたコマンドを使用してシートにアンロードできます。 ホーム — 閉じてロード — 閉じてロード… (ホーム — クローズ & ロード — クローズ & ロードへ..):
将来、フラグメントを含むソース テーブルで何かが変更された場合は、結果のテーブルを右クリックしてコマンドを選択し、生成されたクエリを更新するだけで十分です。 更新して保存 （更新） または、キーボード ショートカットを押して Ctrlキー+他の+F5.
- Power Query、Power Pivot、Power Map、Power BI とは何ですか? Excel ユーザーが必要な理由
- Power Query でガント チャートを作成する
- INDEX関数の5つの使い方