[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『５行毎に横に並べたい(関数）』（サクラ）
以下の相談で回答を受けて汎用の関数を作成できないか？と考えました。
『５行毎に横に並べたい』
https://www.excel.studio-kazu.jp/kw/20230403125946.html
Range("B1").Formula2 = "=INDEX(A:A,SEQUENCE(COUNTA(A:A)/5,5))"
Range("B1#").Value = Range("B1#").Value
Columns(1).Delete
以下のように変更してみましたが、
Option Explicit
Function 行分割(出力列 As Long, 指定行数 As Long)
Cells(1, 出力列).Formula2 = "=INDEX(A:A,SEQUENCE(COUNTA(A:A)/指定行数,指定行数))"
Cells(1, 出力列).Value = Cells(1, 出力列).Value
'Columns(1).Delete
End Function
行分割(2,5)では、B1に＃VALUE!と表示されます。
どのように変更したらエラーが無くなりますか？
＜ 使用 Excel：Excel2021、使用 OS：Windows10 ＞
変数まで""でくくってはいけません。 （MK） 2023/04/04(火) 13:01:44
以下に変更してみると
コードとして見なしてくれません。
Cells(1, 出力列).Formula2 = "=INDEX(A:A,SEQUENCE(COUNTA(A:A)/" & 指定行数,指定行数))
Cells(1, 出力列).Formula2 = "=INDEX(A:A,SEQUENCE(COUNTA(A:A)/" 指定行数,指定行数))
（サクラ） 2023/04/04(火) 13:32:42
B1 に =行分割(A1,5)
Cells(1, 出力列).Formula2 = "=INDEX(A:A,SEQUENCE(COUNTA(A:A)/" & 指定行数 & "," & 指定行数 & "))"
（サクラ） 2023/04/04(火) 14:18:29
=行分割(A1,5)
Function 行分割(指定行数 As Long)
Range("B1").Formula2 = "=INDEX(A:A,SEQUENCE(COUNTA(A:A)/" & 指定行数 & "," & 指定行数 & "))"
'Range("B1#").Value = Range("B1#").Value
'Columns(1).Delete
End Function
以下は、結果を関数で出たのを文字列に変換しているのだと思いますが、合ってますか？
Range("B1#").Value = Range("B1#").Value
"B1#"の意味はどう言うことか教えて下さい。
＃の意味が判らない
（サクラ） 2023/04/04(火) 16:01:09
スピル範囲演算子 らしいです。 （稲葉） 2023/04/04(火) 16:40:46
＃の意味は、スピル範囲演算子。
Range("B1#")でB列で使用されているセルまでの範囲。
具体的には、B24まで使用されていれば、Range("B1:B24")相当
少しコードを変更して以下のようにすると
Function 行分割(出力列 As Long, 指定行数 As Long)
Cells(1, 出力列).Formula2 = "=INDEX(A:A,SEQUENCE(COUNTA(A:A)/" & 指定行数 & "," & 指定行数 & "))"
End Function
出力範囲以外のセル(例えばB1)をセレクト状態で関数を呼び出してパラメーター3,5を指定してOKをクリックすると
B1セルに「=行分割(3,5)」のように関数が表示されて出力範囲に結果が出力されますが
B1セルに#VALUE!と表示されます。
逆に出力範囲内のセルをセレクトして関数を呼び出すとのエラー表示(#SPILL!,#VALUE!)が出て
何も出力されませんでした。
これを修正することは出来ますか？
修正できないようならこの関数の利用は諦めて他の方法を考えます。
（サクラ） 2023/04/04(火) 17:20:36
それってB1セルに=行分割(3,5)って入れてます？
おそらくFunctionの使い方を勘違いされてます。
Functionは値を戻す関数ですので、例えば下記のコードを入れて、
A1=test(1,2)
とすると、A1セルに3が表示されます。
Function test(x As Long, y As Long) As Long
test = x + y
'~~~~~~~~~~~~
'この部分でFunction testに値を戻し入れている
End Function
サクラさんのコードに戻ります。 セルに書き出す処理は記載されていますが、Function 行分割になんの値を返すか記載してません。 ですので、#Valueエラーが帰ります。
Function 行分割(出力列 As Long, 指定行数 As Long)
Cells(1, 出力列).Formula2 = "=INDEX(A:A,SEQUENCE(COUNTA(A:A)/" & 指定行数 & "," & 指定行数 & "))"
'行分割 = hogehoge
'~~~~~~~~~~~~~~~~~
'この部分がない
End Function
考え方は二通りあって、Functionを使わず、SubプロシジャとINPUTBOXなどで出力するか
Functionプロシジャの戻り値として配列を入れるかになると思います。
スピルする製品を持ってないので、何とも言えないですが
Function 行分割(対象列 As Range, 指定行数 As Long) As Variant()
Dim f As String
Dim v As Variant
f = "=INDEX(♪,SEQUENCE(COUNTA(♪)/◆,◆))"
f = Replace(f, "♪", 対象列.Address)
f = Replace(f, "◆", 指定行数)
v = Evaluate(f)
行分割 = v
End Function
B1=行分割(A:A,5)
ってやると勝手にスピルしませんかね・・・？
（稲葉） 2023/04/04(火) 18:47:49
＞おそらくFunctionの使い方を勘違いされてます。
全くそのとうりです。
関数なので出力されるセルを指定（選択、アクティブ）にして
fxの手順 (fx＞関数挿入＞関数の引数でパラメター入力＞OK）
にしないと#Valueエラーになる。
つまり、
= 行分割(出力列 As Long, 指定行数 As Long)
で
出力列が３(C列)の場合は、C１をセルを指定（選択、アクティブ）にしてfxしないとダメで
C1以外のセルを指定してfxしても#Valueエラーとなる。
なので出力列のパラメーターを指定する意味は無いと言うことになります。
C1を指定（選択、アクティブ）した時点で出力先はC列（C1)と決定。
（３ならC1を指定（選択、アクティブ）する＝ここから出力される事になる）
'--------------------------------------------
アドバイスの以下を試してみました。
Option Explicit
Function 行分割(出力列 As Range, 指定行数 As Long)
Dim f As String
Dim v As Variant
f = "=INDEX(♪,SEQUENCE(COUNTA(♪)/◆,◆))"
f = Replace(f, "♪", 出力列.Address)
f = Replace(f, "◆", 指定行数)
v = Evaluate(f)
行分割 = v
End Function
出力先はC:CだけどB1を指定（選択、アクティブ）にしてfxの手順を実施
残念ながら
B1=行分割(C:C,5) −−−−−＞ #VALUE!
勝手にスピルせずにエラーになります。
'-----------------------------------------------
＞Functionを使わず、SubプロシジャとINPUTBOXなどで出力するか
処理列がA:Aと固定していますが、
隠居Zさんの以前のコードを拝借して以下で処理しています。
Sub 行分割2()
Dim i As Long
Dim r As Range
Dim j As Long
Dim x As Long
Dim cOlp As Long
Dim rOwp As Long
Dim v() As Variant
Dim w() As Variant
With ActiveSheet
Set r = Intersect(.Range("A:A"), .UsedRange)
If r.Cells.Count = 1 Then
ReDim v(1 To 1, 1 To 1)
v(1, 1) = r(1, 1).Value
Else
v = r.Value
End If
End With
Dim 指定行数 As String
指定行数 = InputBox("何個ずつ取り出しますか ?", "指定行数")
If StrPtr(指定行数) = 0 Then
MsgBox "Cancelが選択されました。" & vbCrLf & _
"処理を中止します。"
Exit Sub
End If
指定行数 = Val(指定行数)
If 指定行数 < 0 Then
MsgBox "指定行数がマイナスは、有りえません。" & vbCrLf & _
"処理を中止します。"
Exit Sub
End If
x = IIf(UBound(v, 1) Mod 指定行数 > 0, (UBound(v, 1) \ 指定行数) + 1, UBound(v, 1) \ 指定行数)
ReDim w(1 To x, 1 To 指定行数)
rOwp = 1
For i = 1 To UBound(v, 1)
cOlp = cOlp + 1
If cOlp > 指定行数 Then
cOlp = 1
rOwp = rOwp + 1
End If
w(rOwp, cOlp) = v(i, 1)
Next
Dim ws As Worksheet, flag As Boolean
'書き出し前に「New」シートの存在をチェック > 存在していれば削除
For Each ws In Worksheets
If ws.Name = "New" Then flag = True
Next ws
If flag = True Then 'メッセージを表示せずシートを削除
Application.DisplayAlerts = False 'メッセージを非表示
Sheets("New").Delete
Application.DisplayAlerts = True 'メッセージを表示
End If
'「New」シートに結果を書き出す。「New」は、シートの最後に追加
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "New"
'書き出し
With Worksheets("New")
.UsedRange.Clear
.Cells(1).Resize(UBound(w, 1), UBound(w, 2)) = w
End With
End Sub
（サクラ） 2023/04/05(水) 06:30:45
第1引数は出力元を指定するんだけど、、、 （稲葉） 2023/04/05(水) 06:50:51
もし汎用的に使いたいのであれば、Office365であれば
=LET(範囲,A:A,列数,5,INDEX(範囲,SEQUENCE(COUNTA(範囲)/列数,列数)))
~~~~~~~~~~~~~~~
LET関数使っておけばいいんじゃないですかね？
Office365以前だとVBAに軍配上がる気はしますが・・・。
いくつかやり方あると思うけど Subプロシジャから出力する場合、 Functionプロシジャをシート上で配列として使う場合をそれぞれ列記しました。
出力例
|[A]|[B]|[C] |[D] |[E] |[F]|[G]|[H]
[1] |a | | | | | | |SubプロシジャからC2を指定して出力
[2] |b | |a |b |c | | |GetColToSEQUENCE(Range("A:A"), 3, True)
[3] |c | |d |e |f | | |
[4] |d | |i |l | | | |
[5] |e | | | | | | |SubプロシジャからC7を指定して出力
[6] |f | | | | | | |GetColToSEQUENCE(Range("A:A"), 3, False)
[7] | | |a |b |c | | |
[8] | | |d |e |f | | |
[9] |i | | | |i | | |
[10]| | | | |l | | |
[11]| | | | | | | |SubプロシジャからC13を指定して出力
[12]|l | | | | | | |GetColToSEQUENCE(Range("A:A"), 5, True)
[13]| | |a |b |c |d |e |
[14]| | |f |i |l | | |
[15]| | | | | | | |
[16]| | | | | | | |C18:E23に配列数式で確定
[17]| | | | | | | |GetColToSEQUENCE(Range("A:A"), 3, True)
[18]| | |a |b |c | | |
[19]| | |d |e |f | | |
[20]| | |i |l | 0| | |
[21]| | | 0| 0| 0| | |
[22]| | |#N/A|#N/A|#N/A| | |
[23]| | |#N/A|#N/A|#N/A| | |
標準モジュールのコード
Function GetColToSEQUENCE(RowRng As Range, ColCnt As Long, Optional IgnoreSpace As Boolean = False) As Variant()
'GetColToSEQUENCE(変換対象範囲,横方向の列数指定,空白を無視するか)
Dim v As Variant, RowCnt As Long
Dim ans As Variant
Dim r As Long, c As Long
Dim cnt As Long
'
'//指定した列を配列vに取り込む
v = Range(RowRng(1), Cells(Rows.Count, RowRng.Column).End(xlUp)).Value
'
'//vが配列になっているか確認し、処理を分岐
If IsArray(v) Then
'//二次配列を一次配列に変換
v = Application.Transpose(v)
'
'//データ数÷列数で必要行数を確保し、出力用二次配列ansのサイズを確保
RowCnt = WorksheetFunction.RoundUp(UBound(v, 1) / ColCnt, 0)
ReDim ans(1 To RowCnt, 1 To ColCnt)
cnt = 0
For r = 1 To RowCnt
For c = 1 To ColCnt
continue:
cnt = cnt + 1
'
'//配列がcntを上回ったら処理を抜ける
If UBound(v) < cnt Then Exit For
'
'//空白を無視する場合、vが空白以外までcntを増加する
If IgnoreSpace = True Then
If v(cnt) = "" Then GoTo continue
End If
ans(r, c) = v(cnt)
Next c
Next r
Else
'//vが配列以外なら、ans(1,1)にvの値を入れる
ReDim ans(1 To 1, 1 To 1)
ans(1, 1) = v
End If
'
'//結果をFunctionプロシジャに返す
GetColToSEQUENCE = ans
End Function
出力用Subプロシジャ
Sub test()
Dim a As Variant
'変数aに、A列のデータを横3列で折り返すように指示
a = GetColToSEQUENCE(Range("A:A"), 3, True)
'結果をC1を先頭に出力
Range("C1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
MsgBox "出力しました"
'[使い方例]
' '変数aに、B列のデータを横3列で折り返すように指示
' a = GetColToSEQUENCE(Range("B:B"), 3, True)
'
' '変数aに、A列のデータを横5列で折り返すように指示
' a = GetColToSEQUENCE(Range("B:B"), 5, True)
End Sub
（稲葉） 2023/04/05(水) 10:03:36
＞第1引数は出力元を指定するんだけど、
失礼しました。
自分の仕様に固執してちゃんと内容を見ていませんでした。
Function 行分割(出力列 As Range, 指定行数 As Long)
Dim f As String
Dim v As Variant
f = "=INDEX(♪,SEQUENCE(COUNTA(♪)/◆,◆))"
f = Replace(f, "♪", 出力列.Address)
f = Replace(f, "◆", 指定行数)
v = Evaluate(f)
行分割 = v
End Function
もう一度
A1:A17が出力元(対象列)でC列（指定行数）に出力させるとして
（コードでは、「指定行数」と記載されていますが列番号の指定で良いのですよね ？）
B1を指定（選択、アクティブ）にしてfxの手順を実施
B1 =行分割(A1:A17,3) −−−−−＞ #VALUE!
勝手にスピルせずにエラーになります。
=行分割(A:A,3) でも同じエラーでした。
'-------------------------------------------------
GetColToSEQUENCE関数をありがとうございます。
出力用Subプロシジャと直接ワークシートでfx手順で試してみました。
どちらも指定の列に出力されました。
疑問に思った点ですが、第3パラメーターですが
無指定（第3パラメーターのところを空白で指定しない）でも
TrueもしくはFalseでも下記３つの出力結果が同じでした。
=GetColToSEQUENCE(A1:A17,3)
=GetColToSEQUENCE(A1:A17,3,True)
=GetColToSEQUENCE(A1:A17,3,False)
第3パラメーターも存在意義は何でしょうか？
（利用すべき事例を教えてください。）
（サクラ） 2023/04/05(水) 14:11:25
第3パラメーターの件、理解できました。
（サクラ） 2023/04/05(水) 14:47:33
OFFICE365であれば、VBA使わず ＞ =LET(範囲,A:A,列数,5,INDEX(範囲,SEQUENCE(COUNTA(範囲)/列数,列数))) が一番無難かと・・・ 繰り返しますが、自分は365持ってないのでスピルもSQUENCEもテストできてないです。 365以前では C1=INDEX($A:$A,(ROW(A1)-1)*5+MOD(COLUMN(A1),6)) 下と右方向にコピーで同じ動作（空白の無視はできないが）ができます。
本件に関しましては、もう少し基本（エクセル標準機能や3行マクロ）などを勉強されてから 再度トライされてもよいような学習度とお見受けしました。
（稲葉） 2023/04/05(水) 15:09:07
＞OFFICE365であれば、VBA使わず
ダメ元でExcel2021で試したら
=LET(範囲,A:A,列数,5,INDEX(範囲,SEQUENCE(COUNTA(範囲)/列数,列数)))
でスピルされて出力されました。
勉強不足なので頑張ります。
お付き合い願いありがとうございました。
（サクラ） 2023/04/05(水) 17:22:13
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.