エクセルで数字の組み合わせを列記したい

Question

お世話になっております。

例えばA1セルの4桁の数字で出来る組み合わせの４桁の数字をB列～列記するようにしたいのですが。
A２，A3と下のセルにも４桁の数字が入っているので、式のコピーでB列～に表記されるようにしたのですが関数が思いつきませんので宜しくお願いします。

tatsumaru77 · Accepted Answer

VBAになります。
A列～Y列の書式設定は文字列の前提です。
（A1に0123のように入力します。123はNGです）
重複した組み合わせは１度だけ出力されます。
以下のマクロを標準モジュールに登録してください。

Option Explicit
Public Sub 組み合わせ()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim maxrow As Long
    Dim wrow As Long
    Dim wcol As Long
    Dim str As String
    Dim dicT As Object
    
    Set dicT = CreateObject("Scripting.Dictionary")
    maxrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    For wrow = 1 To maxrow
        str = ws.Cells(wrow, 1).Value
        ws.Cells(wrow, 2).Resize(1, 24).Value = ""
        wcol = 2
        Call combi("", str, ws, wrow, wcol, dicT)
        dicT.RemoveAll
    Next
End Sub

Private Sub combi(ByVal pstr As String, ByVal str As String, ws As Worksheet, wrow As Long, wcol As Long, dicT As Object)
    Dim i As Long
    Dim ll As Long
    Dim str0 As String
    Dim str1 As String
    ll = Len(str)
    For i = 1 To ll
        str0 = pstr & Mid(str, i, 1)
        str1 = WorksheetFunction.Replace(str, i, 1, "")
        If str1 = "" Then
            If dicT.exists(str0) = False Then
                ws.Cells(wrow, wcol).Value = str0
                wcol = wcol + 1
                dicT(str0) = True
            End If
            Exit Sub
        End If
        Call combi(str0, str1, ws, wrow, wcol, dicT)
    Next
End Sub

tatsumaru77 · Answer

>A1～D1までの1桁の数字（文字）をランダムに組み合わせて、重複しない24個を抽出するような関数orVBAが無いかと期待したのですが。

VBAでなら提供可能です。その際の確認事項です。
下記の件、補足をお願いします。

１．４つの数字の中に０がある場合がありますか。
もし、０があると、"0123"と表示すると123と表示されてしまいます。
その為、事前に各セルの書式設定を文字列にしておく必要があります。

２．重複があった場合でも、２４ケースを出力しますか。
（例元の数字が1111なら1111を24個出力する）
それとも、1回だけ出力すれば良いのでしょうか。

３．元の数字はA1に4文字あり、B列以降に24個の組み合わせを出力するのでしょうか。
それとも、A1,B1,C1,D1に１文字づつあり、D列以降に24個の組み合わせを出力するのでしょうか。

銀鱗 · Answer

なるほど、そういうことですか。

４×３×２×１＝24の列が必要になるわけですね。
（16個じゃねえぞ）

１　1234
　 ２　1243
　 ３　1324
　 ４　1342
　 ５　1423
　 ６　1432
　 ７　2134
　 ８　2143
　 ９　2314
　10　2341
　11　2413
　12　2431
　13　3124
　14　3142
　15　3214
　16　3241
　17　3412
　18　3421
　19　4123
　20　4132
　21　4213
　22　4231
　23　4312
　24　4321

を表示させたいって事だね。

自分なら面倒なんで、この表を【参照】させる。
このアルゴリズムを数式で示すとスゲー面倒で冗長になるんだ。

【参照】方法は、
　INDEX関数に表を配列としてCOLUMN関数で得た値で選択するとか、
　COLUMN関数で得た値をINDIRECT関数に入れて参照範囲にするとか、
　COLUMN関数で得た値をOFFSET関数に入れて参照範囲にするとか、
やり方はいろいろある。
で、各桁の数字を参照するセルの値の桁として使う。

1234→ 左から1文字目 ＆ 左から2文字目 ＆ 左から3文字目 左から4文字目
　4321→ 左から4文字目 ＆ 左から1文字目 ＆ 左から2文字目 左から1文字目
のようになるよう、

＝MID(A1, MID(表参照+0 ,1,1)+0,1) & 
　MID(A1, MID(表参照+0 ,2,1)+0,1) & 
　MID(A1, MID(表参照+0 ,3,1)+0,1) & 
　MID(A1, MID(表参照+0 ,4,1)+0,1)

こんな感じになります。
（分かりやすいように改行しています）

＝MID(RIGHT("0000"&A1,4), MID(表参照+0 ,1,1)+0,1) & 
　MID(RIGHT("0000"&A1,4), MID(表参照+0 ,2,1)+0,1) & 
　MID(RIGHT("0000"&A1,4), MID(表参照+0 ,3,1)+0,1) & 
　MID(RIGHT("0000"&A1,4), MID(表参照+0 ,4,1)+0,1)  
必ず4桁の数字が入力されるとは限らないから、こんな感じにした方が良いのかな。

MID(表参照+0 ,1,1)+0
などの「+0」は文字列を数値に変換するための横着です。
本来は
　N(MID(N(表参照) ,1,1))
のようにすべき。

’表参照’ の部分は上記の ’【参照】方法は、’ を参考にしても良いし、質問者さん独自のアルゴリズムでも良い。

まあ、こんだけでもスゲー面倒な数式になるので、24種類のパターンを書き出すアルゴリズムを含めると意味不明なものになることは理解してもらえると思います。

・・・

なお、「1111」がずらっと並ぶという事ですので、重複を省略するアルゴリズムは考えていません。
A1セルに
　1123
と入力されていた場合、1123 1132 1213 1231 2113 2131 2311 3112 3121 3211 などは重複表示されます。

銀鱗 · Answer

文字列にすればいいじゃない。

＝”0000”＆A1

これで3桁でも2桁でも問答無用で4桁以上の文字列になります。
そしてこれを右から4文字切り取って、そこから1文字ずつ拾えばいい。
RIGHT関数とMID関数の組み合わせです。
切り取る位置はCOLUMN関数を使って決めてやればいい
　＝COLUNM(A1)
なら「１」が返ってくる。
式を横方向にフィルコピーして
　＝COLUNM(B1)
になれば「2」が返る。

ということで、ちょっと考えてみてください。
試行錯誤することで、自分で数式を組み立てる訓練になります。
がんばれ。

サクラノキ · Answer

A1に1234　だったら、B1に1　B2に2　...
という感じにしたいということですか？
今のところ、完成形がよく分かりません。

エクセルで数字の組み合わせを列記したい

VBAになります。

>A1～D1までの1桁の数字（文字）をランダムに組み合わせて、重複しない24個を抽出するような関数orVBAが無いかと期待したのですが。

なるほど、そういうことですか。

文字列にすればいいじゃない。

A1に1234 だったら、B1に1 B2に2 ...

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

A1に1234　だったら、B1に1　B2に2　...