Note: The other languages of the website are Google-translated. Back to English

Excelで可能なすべての組み合わせを一覧表示または生成するにはどうすればよいですか?

たとえば、次のXNUMX列のデータがあり、左のスクリーンショットに示すように、XNUMXつの値のリストに基づいて、考えられるすべての組み合わせのリストを生成したいとします。 値が少ない場合はすべての組み合わせをXNUMXつずつ一覧表示できますが、可能な組み合わせを一覧表示する必要がある複数の値を持つ列が複数ある場合は、Excelでこの問題に対処するのに役立つ簡単なトリックをいくつか紹介します。 。

式を使用して、XNUMXつのリストからすべての可能な組み合わせをリストまたは生成します

VBAコードを使用してXNUMXつ以上のリストからすべての可能な組み合わせをリストまたは生成します

強力な機能を備えた複数のリストから可能なすべての組み合わせをリストまたは生成します


式を使用して、XNUMXつのリストからすべての可能な組み合わせをリストまたは生成します

次の長い式は、XNUMXつのリスト値のすべての可能な組み合わせをすばやくリストするのに役立ちます。次のようにしてください。

1。 以下の数式を空白のセルに入力またはコピーします。この場合は、セルD2に入力して、を押します。 入力します 結果を取得するためのキー。スクリーンショットを参照してください。

=IF(ROW()-ROW($D$2)+1>COUNTA($A$2:$A$5)*COUNTA($B$2:$B$4),"",INDEX($A$2:$A$5,INT((ROW()-ROW($D$2))/COUNTA($B$2:$B$4)+1))&"-"&INDEX($B$2:$B$4,MOD(ROW()-ROW($D$2),COUNTA($B$2:$B$4))+1))

注意:上記の式では、 $ A $ 2:$ A $ 5 は最初の列の値の範囲であり、 $ B $ 2:$ B $ 4 可能なすべての組み合わせをリストするXNUMX番目のリスト値の範囲です。 $ D $ 2 数式を入力するセルです。必要に応じてセル参照を変更できます。

2。 次に、セルD2を選択し、空白のセルが表示されるまで塗りつぶしハンドルをセルまでドラッグします。XNUMXつのリスト値に基づいて、可能なすべての組み合わせがリストされています。 スクリーンショットを参照してください:


VBAコードを使用してXNUMXつ以上のリストからすべての可能な組み合わせをリストまたは生成します

上記の式を適用するのは少し難しいかもしれません。複数の列のデータがある場合、変更するのは面倒です。 ここでは、すぐに対処できるVBAコードを紹介します。

1。 を押し続けます Alt + F11 キーを押して アプリケーション向け Microsoft Visual Basic 窓。

2に設定します。 OK をクリックします。 インセット > モジュール、次のコードをに貼り付けます モジュール 窓。

VBAコード:3列または複数列のすべての組み合わせを生成します

Sub ListAllCombinations()
'Updateby Extendoffice
Dim xDRg1, xDRg2, xDRg3 As Range
Dim xRg  As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3 As Integer
Dim xSV1, xSV2, xSV3 As String
Set xDRg1 = Range("A2:A5")  'First column data
Set xDRg2 = Range("B2:B4")  'Second column data
Set xDRg3 = Range("C2:C4")  'Third column data
xStr = "-"   'Separator
Set xRg = Range("E2")  'Output cell
For xFN1 = 1 To xDRg1.Count
    xSV1 = xDRg1.Item(xFN1).Text
    For xFN2 = 1 To xDRg2.Count
        xSV2 = xDRg2.Item(xFN2).Text
      For xFN3 = 1 To xDRg3.Count
        xSV3 = xDRg3.Item(xFN3).Text
        xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3
        Set xRg = xRg.Offset(1, 0)
       Next
    Next
Next
End Sub
注意:上記のコードでは、 A2:A5, B2:B4, C2:C4 使用するデータ範囲は、 E2 結果を検索する出力セルです。 より多くの列のすべての組み合わせを取得する場合は、必要に応じて他のパラメーターを変更してコードに追加してください。

3 次にを押します F5 このコードを実行するためのキーを押すと、3つの列のすべての組み合わせが一度に生成されます。スクリーンショットを参照してください。


強力な機能を備えた複数のリストから可能なすべての組み合わせをリストまたは生成します

複数のリスト値があり、可能な組み合わせをリストする必要がある場合は、コードを変更するのが難しい場合があります。 ここでは、強力なツールをお勧めします- Kutools for Excel、便利な機能が含まれています すべての組み合わせを一覧表示 これにより、特定のデータリストに基づいて可能なすべての組み合わせをすばやく一覧表示できます。

ヒント:これを適用する すべての組み合わせを一覧表示 機能、まず、ダウンロードする必要があります Kutools for Excel、次に機能をすばやく簡単に適用します。

インストールした後 Kutools for Excel、次のようにしてください。

1. 【送信】ボタンをクリックします。販売者は原則としてXNUMX日以内に回答を返信します。XNUMX日を過ぎても回答がない場合は、Artisanaryまでお問い合わせください。 クツール > インセット > すべての組み合わせを一覧表示、スクリーンショットを参照してください:

2。 の中に すべての組み合わせを一覧表示 ダイアログボックスで、以下のデモのように操作を行います。

3。 次に、指定したすべての値と区切り記号がダイアログボックスに一覧表示されます。スクリーンショットを参照してください。

4。そしてクリック Ok ボタンをクリックすると、結果を出力するセルを選択するように促すプロンプトボックスが表示されます。スクリーンショットを参照してください。

5に設定します。 OK をクリックします。 OK、指定されたリストに基づくすべての可能な組み合わせは、次のスクリーンショットに示すようにワークシートに生成されています。

クリックしてKutoolsfor Excelを今すぐダウンロードしてください!


より相対的な記事:

  • 3列または複数列のすべての組み合わせを生成する
  • 3列のデータがあるとすると、次のスクリーンショットに示すように、これらの3列のデータのすべての組み合わせを生成または一覧表示します。 Excelでこのタスクを解決するための良い方法はありますか?
  • 与えられた合計に等しいすべての組み合わせを見つける
  • たとえば、次の番号のリストがありますが、リスト内の番号の合計が480になるかどうかを知りたいのですが、次のスクリーンショットでは、合計が等しい480つの可能な組み合わせのグループがあることがわかります。 300 + 60 + 120、300 + 60 + 40 + 80などのXNUMXまで。この記事では、Excelで特定の値になるセルを見つけるためのいくつかの方法について説明します。
  • 可能なすべての順列を生成または一覧表示
  • たとえば、XYZというXNUMXつの文字がありますが、XYZ、XZY、YXZ、YZX、ZXY、ZYXのXNUMXつの異なる結果を得るために、これらXNUMXつの文字に基づいて可能なすべての順列を一覧表示したいと思います。 Excelで、さまざまな文字数に基づいてすべての順列をすばやく生成または一覧表示するにはどうすればよいでしょうか。
  • 考えられるすべての4桁の組み合わせのリストを生成する
  • 場合によっては、4から0までの可能な9桁の組み合わせすべてのリストを生成する必要があります。これは、0000、0001、0002…9999のリストを生成することを意味します。 Excelでリストタスクをすばやく解決するために、いくつかのトリックを紹介します。

 


最高のオフィス生産性ツール

Kutools for Excelはほとんどの問題を解決し、生産性を80%向上させます

  • 再利用: すばやく挿入 複雑な数式、チャート および以前に使用したものすべて。 セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
  • スーパーフォーミュラバー (複数行のテキストと数式を簡単に編集できます); 読書レイアウト (多数のセルを簡単に読み取って編集する); フィルター範囲に貼り付け...
  • セル/行/列をマージする データを失うことなく; 分割セルコンテンツ; 重複する行/列を組み合わせる...重複セルを防止します。 範囲を比較する...
  • [複製]または[一意]を選択します 行; 空白行を選択 (すべてのセルは空です); スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
  • 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
  • スーパーフィルター (フィルタースキームを保存して他のシートに適用します); 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
  • ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
  • 300 以上の強力な機能. Office / Excel 2007-2021 および 365 をサポートします。すべての言語をサポートします。 企業や組織に簡単に導入できます。 フル機能の 30 日間無料トライアル。 60日間の返金保証。
kteタブ201905

Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
officetab下部

 

コメントを並べ替える
コメント (42)
5の5を評価 · 1の評価
このコメントは、サイトのモデレーターによって最小化されました
これは素晴らしい式です! それがどのように機能するかはわかりませんが、リストの長さに合わせて列AとBを変更し、出力をD1にします。
このコメントは、サイトのモデレーターによって最小化されました
例:私の値のリストは1,2,3,4,5,6 ...... 80です。kutoolに2つの数値のセットのすべての組み合わせを表示させたいです。例:1-2、1-3、 1-4、1-5、..................79-80。 これはKUTOOLで可能ですか?
このコメントは、サイトのモデレーターによって最小化されました
この式を機能させる方法はありますか?それは、2つの別々の列でまだ分離されているが、互いに隣接しているさまざまな組み合わせを生成しますか?
このコメントは、サイトのモデレーターによって最小化されました
これを行う方法を理解する運がありましたか? 私はまったく同じことをしようとしており、すべての確率をExcelの2つの別々の列に入れています。
このコメントは、サイトのモデレーターによって最小化されました
これでもう運がありましたか? 私は同じことをしようとしていて、2つの別々の列にすべての可能な組み合わせを並べています。
このコメントは、サイトのモデレーターによって最小化されました
5 * 5行列(5行5列)のすべての組み合わせの数式を取得できますか?試しましたが、取得できませんでした。助けてください......。
このコメントは、サイトのモデレーターによって最小化されました
非常に役に立ちました。これで、組み合わせを非常に簡単に生成できるようになりました。
このコメントは、サイトのモデレーターによって最小化されました
月ヘッダーのすべての順列が必要な場合、KuTools、または数式をどのように使用できますか。 XNUMX月、XNUMX月とXNUMX月、XNUMX月とXNUMX月、XNUMX月とXNUMX月、XNUMX月とXNUMX月、XNUMX月など
このコメントは、サイトのモデレーターによって最小化されました
6列ではなくXNUMX列のデータを反映するようにこれを変更する方法を知っている人はいますか?
このコメントは、サイトのモデレーターによって最小化されました
同じことを探しています。 誰か知ってる?
このコメントは、サイトのモデレーターによって最小化されました
6列のデータを反映するようにこれを変更する方法を知っている人はいますか?
このコメントは、サイトのモデレーターによって最小化されました
その他の列の場合:
式の最初の部分は、すべての可能性を乗算するように変更する必要があります。たとえば、これは6列の場合です。
COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9)
だからあなたは追加しています
* COUNTA(YourRangeHere)
各列について
数式のXNUMX番目の部分は、次のように列ごとに変更する必要があります。
INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1)
だからあなたは追加しています
&INDEX(YourRangeHere、MOD(ROW()-ROW($ I $ 2)、COUNTA(YourRangeHere))+ 1)

したがって、それらをすべてまとめると、6列の次の例が得られます。

=IF(ROW()-ROW($I$2)+1>COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9),"",INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1))

この数式を$I$ 2に貼り付けると、$ A $ 2:$ A $ 9 $ B $ 2:$ B $ 9 $ C $ 2:$ C $9...最大$F$ 2:$ F$9になります。
このコメントは、サイトのモデレーターによって最小化されました
これは機能しません:(これよりも複雑です
このコメントは、サイトのモデレーターによって最小化されました
その他の列の場合:
式の最初の部分は、すべての可能性を乗算するように変更する必要があります。たとえば、これは6列の場合です。

COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9)
だからあなたは追加しています
* COUNTA(YourRangeHere)
各列について

数式のXNUMX番目の部分は、次のように列ごとに変更する必要があります。

INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1)

だからあなたは追加しています

&INDEX(YourRangeHere、MOD(ROW()-ROW($ I $ 2)、COUNTA(YourRangeHere))+ 1)

したがって、それらをすべてまとめると、6列の次の例が得られます。

=IF(ROW()-ROW($I$2)+1>COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9),"",INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1))

この数式を$I$ 2に貼り付けると、$ A $ 2:$ A $ 9 $ B $ 2:$ B $ 9 $ C $ 2:$ C $9...最大$F$ 2:$ F$9になります。
このコメントは、サイトのモデレーターによって最小化されました
これは機能しません:(これよりも複雑です
このコメントは、サイトのモデレーターによって最小化されました
間にスペースを追加する方法はありますか?
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、エオイン、
この記事の数式では、セルの値の間にスペースを追加することはできませんが、Kutools for Excelを適用すると、結合された結果を区切るために任意の区切り文字を入力できます。スクリーンショットを参照してください。
このコメントは、サイトのモデレーターによって最小化されました
列Bのインデックスを作成する前、既存の&の直後に「」&を追加することで、スペースを追加できます。

=IF(ROW()-ROW($D$1)+1>COUNTA($A$1:$A$4)*COUNTA($B$1:$B$3),"",INDEX($A$1:$A$4,INT((ROW()-ROW($D$1))/COUNTA($B$1:$B$3)+1))&" "&INDEX($B$1:$B$3,MOD(ROW()-ROW($D$1),COUNTA($B$1:$B$3))+1))


....またはその他の好きな区切り文字!
このコメントは、サイトのモデレーターによって最小化されました
KuToolsは、「単純な組み合わせ」の方法でセルを行に並べ替えることができますか?
つまり、これらのデータがある場合:
_________________________
ジョンジャックポールメイシー

マーク・ラリー

ジェリーポールメアリー

サムジェフピータールーカス
_________________________



そして、私はそれらを次のように出力したいと思います:
___________
ジョンジャック

ジョン・ポール

ジョンメイシー

ジャックポール

ジャックメイシー

ポールメイシー

マーク・ラリー

ジェリーポール

ジェリー結婚

ポールメアリー

サムジェフ

サムピーター

サム・ルーカス

ジェフピーター

ジェフ・ルーカス

ピータールーカス
____________


これどうやってするの? KuToolsはこのようにできますか?

ありがとうございます!
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、アーサー、
申し訳ありませんが、Kutoolsはあなたが言ったようにあなたがこの仕事を解決するのを助けることができません。
コメントありがとうございます。
このコメントは、サイトのモデレーターによって最小化されました
5セットのデータに対してこれを行うための公式をまだ理解している人はいますか? 私はこれまでこれに困惑してきました。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、マルコ、
Kutoolsは、問題をすばやく解決するのに役立ちます。次のスクリーンショットを確認してください。
ぜひお試しください。お役に立てば幸いです。ありがとうございます。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、
1,4百万を超える組み合わせを生成したいのに、Excelの行数の制限を超えた場合の対処方法を知りたいですか?
次の列で反復を継続するためにKudosを配置する方法はありますか?
このコメントは、サイトのモデレーターによって最小化されました
列Eに25の組み合わせを作成するために数式が機能しなかったのはなぜですか(列Aから5要素*列Bから5要素)? この記事の数式は、25セルドラッグしたときに列Dで機能しましたか? スクリーンショット- https://prnt.sc/ihwr18
このコメントは、サイトのモデレーターによって最小化されました
Hola me arroja una referencia circula laformula
=SI(FILA()-FILA($D$1)+1>(CONTARA($A$1:$A$4)*CONTARA($B$1:$B$3));"";INDICE($A$1:$A$4;RESIDUO((FILA()-FILA($D$1));COUNTA($B$1:$B$3)+1))
&INDICE($B$1:$B$3;RESIDUO(FILA()-FILA($D$1);CONTARA($B$1:$B$3))+1))
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、そしてこれらの組み合わせのテキストが数字である場合、どうすれば結果の計算を行うことができますか???? 「+」を追加しようとしましたが、それがシンボルであることがわかりません。これをどのように解決しますか?
このコメントは、サイトのモデレーターによって最小化されました
私はextionsを使用して素晴らしい動作をしましたが、問題が発生したため、合計を計算する必要がありますが、結果はテキストになります。simbol+である区切り文字を入力しますが、最後にテキストがあり、操作で変換することはできません。
このコメントは、サイトのモデレーターによって最小化されました
ほとんど非常に便利です。 セル($ A $ 1)にあるものを実際に使用し、セルをテキストに変換しないとよいでしょう。 次に、必要なものをA1に貼り付けて、何も変更せずに再実行できます。
このコメントは、サイトのモデレーターによって最小化されました
これをtxtファイルとして出力する方法はありますか? D列に何百万もの結果がある場合、塗りつぶしハンドルをドラッグすることは実際的ではありません。
ここにはまだコメントが投稿されていません
もっと読む

フォローする

著作権©の2009 - WWW。extendoffice.com。 | | 全著作権所有。 搭載 ExtendOffice。 | サイトマップ
MicrosoftおよびOfficeのロゴは、米国MicrosoftCorporationの米国およびその他の国における商標または登録商標です。
SectigoSSLで保護