メインコンテンツへスキップ

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))

Note:上記の式では、 $ 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
Note:上記のコードでは、 A2:A5, B2:B4, C2:C4 使用するデータ範囲は、 E2 結果を検索する出力セルです。 より多くの列のすべての組み合わせを取得する場合は、必要に応じて他のパラメーターを変更してコードに追加してください。

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


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

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

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

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

1. クツール > インセット > すべての組み合わせを一覧表示、スクリーンショットを参照してください:

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でリストタスクをすばやく解決するために、いくつかのトリックを紹介します。

 

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

人気の機能: 重複を検索、強調表示、または識別する   |  空白行を削除する   |  データを失わずに列またはセルを結合する   |   数式なしのラウンド ...
スーパールックアップ: 複数の基準の VLookup    複数の値の VLookup  |   複数のシートにわたる VLookup   |   ファジールックアップ ....
詳細ドロップダウン リスト: ドロップダウンリストを素早く作成する   |  依存関係のドロップダウン リスト   |  複数選択のドロップダウンリスト ....
列マネージャー: 特定の数の列を追加する  |  列の移動  |  Toggle 非表示列の表示ステータス  |  範囲と列の比較 ...
注目の機能: グリッドフォーカス   |  デザインビュー   |   ビッグフォーミュラバー    ワークブックとシートマネージャー   |  リソースライブラリ (自動テキスト)   |  日付ピッカー   |  ワークシートを組み合わせる   |  セルの暗号化/復号化    リストごとにメールを送信する   |  スーパーフィルター   |   特殊フィルター (太字/斜体/取り消し線をフィルター...) ...
上位 15 のツールセット12 テキスト 工具 (テキストを追加, 文字を削除する、...)   |   50+ チャート 種類 (ガントチャート、...)   |   40+ 実用的 (誕生日に基づいて年齢を計算する、...)   |   19 挿入 工具 (QRコードを挿入, パスから画像を挿入、...)   |   12 変換 工具 (数字から言葉へ, 通貨の換算、...)   |   7 マージ&スプリット 工具 (高度な結合行, 分割セル、...)   |   ... もっと

Kutools for Excel で Excel スキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、生産性を向上させ、時間を節約するための300以上の高度な機能を提供します。  最も必要な機能を入手するにはここをクリックしてください...

kteタブ201905


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

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

 

Comments (42)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
como que poderia gerar uma lista de combinações de nomes no LibreOffice?
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hello, Johansson

Sorry, the methods in this article are only applied for Microsoft Excel.
Thank you!
This comment was minimized by the moderator on the site
Hej alle sammen

Jeg håber at i kan hjælpe med dette.

Jeg har en af de gamle Bastalåse kodelåse med 6 knapper, hvor man kan trykke knapperne ind, lade dem være i neutral eller trække dem ud.

Låsen var åben da jeg købte den, så jeg låste den i håb om at jeg kunne gå kombinationerne igennem.

Men jeg har lidt problemer med at få nedskrevet samtlige kombinationer (729 så vidt jeg kan regne ud).

Er der en der vil hjælpe mig med med dette? Evt. kan man kalde de 3 indstillingsmuligheder pr. knap for: 1-2-3, 1-0-2, eller I-N-U (ind-neutral-ud).


Krydser finger og siger på forhånd mange tak hvis jeg kan få låsen op, det er sådan lidt nostalgi.

Mvh

Nicolaj
This comment was minimized by the moderator on the site
No 1º Exemplo de captura de tela , está faltando em All combinations Jan - KTE
This comment was minimized by the moderator on the site
Hello friend,
How are you. Thank you for your notice. We changed the first ficture. Please check now. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Hi,
I have a project with excel (including Kutools) that I am stuck on.
Can anyone tell me how to get a list of all combinations of 6 different numbers, without repeating any of the numbers?
I know there should be 6! = 720 combinations but I need the list.
Thanks
This comment was minimized by the moderator on the site
Hi,
I have a project with excel (including Kutools) that I am stuck on.
Can anyone tell me how to get a list of all combinations of 6 different numbers, without repeating any digit?
I know there should be 6! = 720 combinations but I need the list.
Thanks
This comment was minimized by the moderator on the site
Hello Extend Office,
I want to extend the VBA code in "List Or Generate All Possible Combinations From Three Or More Lists With VBA Code article"
I want to show only the one's whose total of the combinations is equal to 9. Can you please show the additional extension/lines in the code? sorry, I'm barely new in VBA.
For example, I only want to show the combinations like the following:
1-2-2-2-2 (this is equal to 1+2+2+2+2 = 9) so, it will show the ones whose total is 9, the rest won't show.
1-2-3-1-3 (=9)
1-3-2-1-2 (=9) and so on...

Thanks very much for your help
Dalesimplest.becky15.08.21

This comment was minimized by the moderator on the site
edited formula to randomize 5 columns instead of 3 but still wont work,why?
Sub ListAllCombinations()
'Updateby Extendoffice
Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5 As Range
Dim xRg As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3, xFN4, xF5 As Integer
Dim xSV1, xSV2, xSV3, xSV4, xSV5 As String
Set xDRg1 = Range("A2:A10") 'First column data
Set xDRg2 = Range("B2:B10") 'Second column data
Set xDRg3 = Range("C2:C10") 'Third column data
Set xDRg2 = Range("D2:D10") 'Fourth column data
Set xDRg3 = Range("E2:E10") 'Fifth column data
xStr = "," 'Separator
Set xRg = Range("G2") '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
For xFN4 = 1 To xDRg4.Count
xSV4 = xDRg4.Item(xFN4).Text
For xFN5 = 1 To xDRg5.Count
xSV5 = xDRg5.Item(xFN5).Text
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5
Set xRg = xRg.Offset(1, 0)
Next
Next
Next
End Sub
This comment was minimized by the moderator on the site
Hello, jericho,You should modify your code as below code, please try it, thank you!<div data-tag="code">Sub ListAllCombinations()
'Updateby Extendoffice
Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5 As Range
Dim xRg As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3, xFN4, xFN5 As Integer
Dim xSV1, xSV2, xSV3, xSV4, xSV5 As String
Set xDRg1 = Range("A2:A10") 'First column data
Set xDRg2 = Range("B2:B10") 'Second column data
Set xDRg3 = Range("C2:C10") 'Third column data
Set xDRg4 = Range("D2:D10") 'Fourth column data
Set xDRg5 = Range("E2:E10") 'Fifth column data
xStr = "-" 'Separator
Set xRg = Range("H2") '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
For xFN4 = 1 To xDRg4.Count
xSV4 = xDRg4.Item(xFN4).Text
For xFN5 = 1 To xDRg5.Count
xSV5 = xDRg5.Item(xFN5).Text
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5
Set xRg = xRg.Offset(1, 0)
Next
Next
Next
Next
Next
End Sub
This comment was minimized by the moderator on the site
How to remove the repetition if I have color 1, color 2, color 3 in different orders(i.e. color2, color 3, color 1) but they are the same for my need? tks!
This comment was minimized by the moderator on the site
Excelentes soluciones, muchas gracias, lo logré con Kutools
This comment was minimized by the moderator on the site
NON FUNZIONA. HO SEGUITO GLI STESSI PASSAGGI MA MI DICE CHE C'E' UN ERRORE
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations