[[20170811023826]] 『セル内の文字列をランダムに並び替える』(すのこ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『セル内の文字列をランダムに並び替える』(すのこ)

A列に表示されている文字列をB列にランダムに並び替えて(シャッフルして)表示させるにはどうすればいいでしょうか?
(例・A1セル:あいうえお、A2セル:かきくけこ、A3セル:さしすせそ→B1セル:えいうあお、B2セル:こくかけき、B3セル:すせそさし)
マクロ機能を使わなければ無理だとは思うのですが…。

< 使用 Excel:Excel2013、使用 OS:Windows7 >


 作業列を使わないと無理のようだった。
 参考 色々あるもんですね。

https://search.yahoo.co.jp/search;_ylt=A2RA1CHYooxZoUoA4MSJBtF7?p=%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB%E3%80%80%E6%95%B0%E5%BC%8F%E3%80%80%E3%82%B7%E3%83%A3%E3%83%83%E3%83%95%E3%83%AB&search.x=1&fr=top_ga1_sa&tid=top_ga1_sa&ei=UTF-8&aq=-1&oq=%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB+%E6%95%B0%E5%BC%8F+%E3%82%B7%E3%83%A3%E3%83%83%E3%83%95%E3%83%AB&at=&aa=&ai=pgwythJdSNOZUdWuiTBjfA&ts=17737

http://www4.synapse.ne.jp/yone/excel2013/excel2013_sort_random.html
http://d.hatena.ne.jp/fyts/20080222/excel
http://www.relief.jp/docs/001798.html
(BJ) 2017/08/11(金) 03:26


A1セルに
あいうえお
B1〜B5セルに
=RAND()
C1セルに
=RANK(B1,$B$1:$B$5)
を入力してC5セルまでフィルコピー
D1セルに
=MID($A$1,C1,1)
を入力してD5セルまでフィルコピー
E1セルに
=CONCATENATE(D1,D2,D3,D4,D5)
と入力すれば、E1セルにA1の文字を並び替えた文字が出てきます。
もっといい方法があるかもしれませんが。
(/) 2017/08/11(金) 11:53

 ユーザー定義関数だとこんなカンジでいかがでしょう?
 B1=WDShuffle(A1)

    Function WDShuffle(Word As String) As String
        Randomize
        Dim n As Long
        n = WorksheetFunction.RandBetween(1, Len(Word))
        If Len(Word) = 1 Then
            WDShuffle = Word
        Else
            WDShuffle = Mid(Word, n, 1) & WDShuffle(WorksheetFunction.Replace(Word, n, 1, ""))
        End If
    End Function
(稲葉) 2017/08/11(金) 12:35

 衝突...

 UDF
 B1:
 =RandCells(A1)

 標準モジュールへ

 Function RandCells(ByVal txt As String) As String
     Dim i As Long, x As Long, temp
     Randomize
     For i = 1 To Len(txt)
         x = Application.RandBetween(i, Len(txt))
         temp = Mid$(txt, i, 1)
         Mid$(txt, i, 1) = Mid$(txt, x, 1)
         Mid$(txt, x, 1) = temp
     Next
     RandCells = txt
 End Function

 注:この関数はシートが再計算される度に再計算されてしまうので、必要なら値に変換する。
( seiya) 2017/08/11(金) 12:40

 おおお
 Midステートメントと繰り返しに挑戦して失敗したので、勉強になります。
 >RandBetween(i, Len(txt))
 こうすればできるんですねぇ。

 勉強ついでなのですが・・・
 >Mid$
      ~
 は明示したほうが良いのでしょうか?
 最近は読みやすいコードの勉強中で、変数名等できるところからはじめようと努力中です。
(稲葉) 2017/08/11(金) 12:57

 返される値を文字列型と指定することで、Variant/Stringよりも処理速度が若干速くなるようです。
( seiya) 2017/08/11(金) 13:10

 こんにちわ。

 [f9]で再計算される回答は沢山出てるので、私からはちょっと違う動作の回答を。
 以下のユーザー定義関数では[f9]で再計算はされません。
 元の値を変更した時のみ更新されます。

 Function RndStr(s As String) As String
    Dim i As Long

    For i = 1 To Len(s)
        RndStr = RndStr & Mid(s, Int(Rnd * Len(s)) + 1, 1)
        s = Replace(s, Right(RndStr, 1), "", 1, 1)
    Next i

 End Function

(sy) 2017/08/11(金) 13:18


 再計算無しで...

 Function RandCells(ByVal txt As String) As String
     Dim i As Long, x As Long, temp
     With CreateObject("System.Random")
        For i = 1 To Len(txt)
            x = .Next_2(1, Len(txt))
            temp = Mid$(txt, i, 1)
            Mid$(txt, i, 1) = Mid$(txt, x, 1)
            Mid$(txt, x, 1) = temp
        Next
     End With
     RandCells = txt
 End Function
( seiya) 2017/08/11(金) 13:26

 seiyaさん
 ありがとうございます。
 少しずつ精進致します。

 syさんのめっちゃ早いですね。
 自分のが遅すぎて泣けます。
(稲葉) 2017/08/11(金) 14:00

 稲葉さん

 早さは全然気にしてなかったですね。
 おそらくWorksheetFunctionがかなりの負荷になってると思いますので、
 [f9]で再計算はされなくなりますが、以下のようにしたら早くなると思います。

    Function WDShuffle2(Word As String) As String
        Randomize

        Dim n As Long

        n = Int(Rnd * Len(Word)) + 1

        If Len(Word) = 1 Then
            WDShuffle2 = Word
        Else
            WDShuffle2 = Mid(Word, n, 1) & WDShuffle2(Replace(Word, Mid(Word, n, 1), "", 1, 1))
        End If

    End Function

(sy) 2017/08/11(金) 14:35


 syさん
 お返事遅くなってすみません。

 かなり早くなりました・・・
 必ずしもWorksheet関数が高速とは限らないのですね。

 ありがとうございました。

(稲葉) 2017/08/11(金) 16:18


早さだけのことから言えば、文字列の連結よりも
Midステートメントで書き込んだ方が幾分速くなりますね。
 
また、Randomizeを再帰関数の中で何度も実行すると
正確な?乱数を逆に乱して?しまう懼れがあるかもしれません。
気持ちだけの問題かもしれませんが。

(γ) 2017/08/11(金) 17:32


 γさん
 ご指導ありがとうございます。

 >Midステートメントで書き込んだ方が幾分速くなりますね。 
 分かってはいたのですが、再帰でやるにはどうすればいいだろう・・・と悩んだ末
 順番に取り出す感じになってしまいました。

 乱数については、Optionalで初回だけに限定するとして、Midステートメントを使った場合に
 再帰処理しようとすると、どのようにすればよいのか思いつきません・・・。

 お手数お掛けしますが、お手本があれば教えて頂けないでしょうか?

(稲葉) 2017/08/11(金) 17:40


 参照型でお茶を濁すしか思いつきません・・・

    Function WDShuffle3(ByRef Word As String, Optional ByRef tmpWord As String = "") As String
        If Len(tmpWord) = 0 Then
            Randomize
            tmpWord = Space(Len(Word))
        End If
        Dim n As Long
        n = Int(Rnd * Len(Word)) + 1
        If Len(Word) = 1 Then
            Mid$(tmpWord, Len(Word)) = Word
        Else
            Dim dummy As String
            dummy = WDShuffle3(Replace(Word, Mid(Word, n, 1), "", 1, 1), tmpWord)
            Mid$(tmpWord, Len(Word)) = Mid(Word, n, 1)
            WDShuffle3 = tmpWord
        End If
    End Function
(稲葉) 2017/08/11(金) 18:29

 散歩から戻りました。
 再帰は考えていませんでした。
 こんなものでした。

 Function wdShuffle3(word As String) As String
     Dim k As Long, p As Long
     Dim s As String

     'Randomize
     s = word
     wdShuffle3 = word
     For k = Len(s) To 1 Step -1
         If k = 1 Then
             Mid(wdShuffle3, 1, 1) = s
         Else
             p = Int(Rnd * Len(s)) + 1
             Mid(wdShuffle3, k, 1) = Mid(s, p, 1)
             s = Replace(s, Mid(s, p, 1), "", 1, 1)
         End If
     Next
 End Function

(γ) 2017/08/11(金) 21:34


 γさん
 ありがとうございます。

 なぜか自分の中でこの課題について執着してしまって、ずっと考え込んでしまいました。
 以下二つ思いついて、納得してしまったので、これで投稿終わりにします。
 すのこさん、お邪魔いたしました。
    Function WDShuffle4(ByVal Word As String) As String
        'ループ版
        Dim i As Long
        Dim n As Long
        WDShuffle4 = Word
        Randomize
        For i = Len(Word) To 1 Step -1
            n = Int(Rnd * i) + 1
            Mid$(WDShuffle4, i, 1) = Mid$(Word, n, 1)
            Mid$(Word, n, 1) = Mid$(Word, i, 1)
        Next i
    End Function
    Function WDShuffle5(ByVal Word As String, Optional ByVal cnt As Long = 0) As String
        '再帰版
        If cnt = 0 Then
            Randomize
            cnt = Len(Word)
            Word = Word & Word
        End If
        Dim n As Long
        n = Int(Rnd * cnt) + 1
        Mid$(Word, cnt + Len(Word) / 2, 1) = Mid$(Word, n, 1)
        Mid$(Word, n, 1) = Mid$(Word, cnt, 1)
        If cnt = 1 Then
            WDShuffle5 = Right(Word, Len(Word) / 2)
        Else
            WDShuffle5 = WDShuffle5(Word, cnt - 1)
        End If
    End Function
(稲葉) 2017/08/11(金) 21:55

皆さんご返信ありがとうございました。
思ったより専門的な議論が進んでいてびっくりしました。

/さんの返信を参考に最終的に関数だけで処理してしまったのですが、
まず、「=RAND()」を全て(無論本当に全てに入れる必要はないですが)のセルに入力した作業用シートを別に用意し、1行目に

A1:あいうえお、
B1:=CONCATENATE(H1,I1,J1,K1,L1)、
C1:=RANK(作業用!C1,作業用!$C1:作業用!$G1)、
D1:=RANK(作業用!D1,作業用!$C1:作業用!$G1)、
E1:=RANK(作業用!E1,作業用!$C1:作業用!$G1)、
F1:=RANK(作業用!F1,作業用!$C1:作業用!$G1)、
G1:=RANK(作業用!G1,作業用!$C1:作業用!$G1)、
H1:=MID($A1,C1,1)、
I1:=MID($A1,D1,1)、
J1:=MID($A1,E1,1)、
k1:=MID($A1,F1,1)、
L1:=MID($A1,G1,1)、

と入力した後、2行目以降に引っ張っていくことで、望んでいたことはできました。
文字数が揃っていないとできませんが、そこは関数の限界ですかね。
今後のためにも、マクロにも少しは触れてみたいと思います。
皆さんどうもありがとうございました。
(すのこ) 2017/08/12(土) 00:11


稲葉さん、コードの提示ありがとうございました。勉強になりました。
(γ) 2017/08/12(土) 13:31

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.