[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『Index,Match,Offsetなどを使い、各行から値を抽出してある行にまとめたい』（Index）
Index,Match,Offsetなどを使い、各列から値を抽出してある行にまとめたい
以下のように並んでいるデータがあります。
A B C D 1 a e h 2 b f I 3 c g j 4 d k 5 l 6 m 7 n 8 o 9 10 11 12 13 14 15
次のように、B,C,Dの各列から値を抽出して、A行にまとめたいと思っています。
A B C D 1 a a e h 2 b b f I 3 c c g j 4 d d k 5 e l 6 f m 7 g n 8 h o 9 I 10 j 11 k 12 l 13 m 14 n 15 o
A列の各セルに入る数式は、かなり長い数式になってもかまいません。
作業列はないほうがよいですが、どうしても使わないとできない場合や、使ったほうがはるかに簡単であるならば、作業列ありでもかまいません。
実際は、B,C,Dの各列には、数百のデータが入っております。B,C,D,E,F,G...とたくさんの行からA行にまとめたい場合もよくあります。
各列には何行までデータが入っているかもとくに決まっていませんが、基本的には最大でも1000行までです。
自分なりに調べたところ、Index,Match,Offsetなどを使えば、理論的にはExcelでできそうな気もしますが、非常に複雑な数式になりそうですし、これらの関数はほとんど使って事ないため、初心者にはお手上げです。
よろしくお願いします。
＜ 使用 Excel：Excel2013、使用 OS：Windows8 ＞
A1 =IFERROR(INDIRECT(TEXT(MOD(AGGREGATE(15,6,(COLUMN(B$1:D$99)*10000+COLUMN(B$1:D$99)+ROW(B$1:D$99)*100)/(B$1:D$99<>""),ROW(A1)),10000),"!R0!C00"),FALSE),"") 下へコピー。 （GobGob） 2017/01/24(火) 13:14
1000行までなんですな。
修正。
=IFERROR(INDIRECT(TEXT(MOD(AGGREGATE(15,6,(COLUMN(B$1:D$1000)*10000000+COLUMN(B$1:D$1000)+ROW(B$1:D$1000)*100)/(B$1:D$1000<>""),ROW(A1)),10000000),"!R0!C00"),FALSE),"")
※列は2桁まで。 （GobGob） 2017/01/24(火) 13:16 → 13:36 間違い訂正＆加筆
作業行を使う場合。
１行目を作業行として使う。
Ａ1セルに1と入力、Ｂ1セルに =COUNTA($B$2:B$1000)+1 と入力して右へフィルコピー。
A2セルに =IFERROR(INDEX(B$2:D$1000,ROW(A1)-LOOKUP(ROW(A1),A$1:D$1)+1,MATCH(ROW(A1),A$1:D$1)),"") と入力して下へフィルコピーで。 （ねむねむ） 2017/01/24(火) 13:20
mmさんの式の場合は >実際は、B,C,Dの各列には、数百のデータが入っております。B,C,D,E,F,G...とたくさんの行からA行にまとめたい場合もよくあります。 と列が増えた場合は &IFERROR(OFFSET(D1,-1*(COUNTA(B:B)+COUNTA(C:C)),,,),""),"0","") この単位で式を追加していく必要あり。 (内部の「COUNTA(B:B)+COUNTA(C:C)」部分も増やしてく必要あり)
GobGobさんと私の式の場合は式内のセル範囲の変更のみ。 （私の場合は1行目の式をさらに右へコピーしていく必要はあるが） （ねむねむ） 2017/01/24(火) 13:46
GobGob様のように作業列を使わないのは理想ですが、IFERRORで設定している通り、エラーになってしまい、何も表示されませんました。
コピペして使うだけでもいいのですが、可能であれば理解しておきたいので、日本語でどのようなことをしているのか説明して頂けないでしょうか？
知らない関数もたくさんありますが、関数については自分で調べます。
よろしくお願いします。
（Index） 2017/01/24(火) 20:37
このレベルになると最上級レベルと言えるでしょうか？
（Index） 2017/01/24(火) 20:57
日本語でしか説明できないのでw
今回はindirectでデータを返す。（R1C1形式） んでセル位置をAGGREGATE、MOD、TEXTで計算で返してま。
返す位置の優先度を行番号と列番号で計算し、小さい順に返すんだけど 元データの優先度は列>行の順番になるので （aはR1C2、bはR2C2、cはR3C2、dはR4C2、eはR1C3....） eの位置がbより優先度が上になってしまう。
なので、列×10000000＋行×100＋列 で優先度を決める。 それをAGGREGATEで小さい順に返す。
しかし返された数値を使ってR1C1形式にするには 最初の列×10000000が邪魔なのでMODで処理し、行×100＋列だけの 数値にする。
その結果をTEXTでR1C1形式の文字列に変換し、INDIRECT。
ですわ。 （GobGob） 2017/01/24(火) 23:09
上級かどうか知りまへんが 作業セル無しだとどうしても
・データの有無を調べる ・データ有りの位置を調べる ・データ有りを「順番」で処理する
これを一セルでやるので配列数式になりますわな。 なのでややこしい計算になりますわ。
今回はINDIRECTでやったけど、INDEXとかOFFSETでもできるよ。 けど、それだと優先の計算を二回やらないとダメだと思う。 （行、列それぞれで優先計算）
それがイヤで今回はR1C1形式の特性を使いましたとさ。
あと、CTRL+SHIFT+ENTER確定でもいいなら AGGREGATEはSMALLとIFでもいいと思うよん。
※ちなみに作業セル案もその後考えたけどねむねむさんと 全く同じだったので回答しませんでしたw （GobGob） 2017/01/24(火) 23:23 → 23:37加筆
GobGob様の式は、IFERRORが反応して何も表示されないのですが、みなさんの環境ではきちんと表示されるんでしょうか？
ねむねむ様の式もエラーになってしまいます。
mm様の式は後で修正が大変そうなので、恐縮ですが今回は使わない方針にしました。
Offsetのほうが個人的にはわかりやすい気がして、なんとか理解できるかもしれないので、配列になっても構いませんので、どなたか、Offsetのバージョンの式も教えていただけないのでしょうか？
以下の式において
=IFERROR(INDIRECT(TEXT(MOD(AGGREGATE(15,6,(COLUMN(B$1:D$1000)*10000000+COLUMN(B$1:D$1000)+ROW(B$1:D$1000)*100)/(B$1:D$1000<>""),ROW(A1)),10000000),"!R0!C00"),FALSE),"")
以下の部分まではなんとか理解しましたが、
=IFERROR(INDIRECT(TEXT(MOD(
ここが「15,6,」以外、全くわかりません。
AGGREGATE(15,6,(COLUMN(B$1:D$1000)*10000000+COLUMN(B$1:D$1000)+ROW(B$1:D$1000)*100)/(B$1:D$1000<>""),ROW(A1))
・配列というものは、なんとなくしかわからないのですが、なぜ配列を足し算しているのでしょうか？足し算になっているのは配列の条件式「または」でしょうか？
・昇順に並べるようですが、何を基準に照準に並べるのでしょうか？ROW(A1)はどのように関係しているのでしょうか？
・この優先度の計算のロジックについて、よくわかりません。
返す位置の優先度を行番号と列番号で計算し、
・なぜ3項の足し算を擦るのかも善くわかりません。3項のうちなぜ最後の項だけ割り算をしているのでしょうか？
列×10000000＋行×100＋列
全体像のイメージがつかめないのですが、そのへんを重点的に教えて頂けると助かります。
長くなってしまい恐縮ですが、よろしくお願いします。
（Index） 2017/01/25(水) 15:49
>GobGob様の式は、IFERRORが反応して何も表示されないのですが、みなさんの環境ではきちんと表示されるんでしょうか？ >ねむねむ様の式もエラーになってしまいます。
あらためて新しいシートに掲示板でIndexさんが例として挙げられているデータを入力し、それに対して式を入れてみてくれ。
それでうまくいくかどうかをまずは確認してくれ。
（ねむねむ） 2017/01/25(水) 15:55
ちゃんと機能しないのは、実際のデータがA:D列じゃないとか、1行目からでないとかでないの？ コッチはちゃんと機能してます。
＞ここが「15,6,」以外、全くわかりません。 ＞AGGREGATE(15,6,(COLUMN(B$1:D$1000)*10000000+COLUMN(B$1:D$1000)+ROW(B$1:D$1000)*100)/ ＞(B$1:D$1000<>""),ROW(A1))
これ、(COLUNN(***)*10000000+COLUMN(***)+ROW(***)*100)/(***<>"") で出た配列データから ROW(*)番目を返すって意味ですわ。
→ (COLUMN(B$1:D$1000)*10000000+COLUMN(B$1:D$1000)+ROW(B$1:D$1000)*100)/(B$1:D$1000<>"")
これは 「データ有り」セルの行列番号を計算してま。 複雑な計算をするのは「順番に抽出しないといけない」からっす。
A B C D 1 a e h 2 b f I 3 c g j 4 d k 5 l 6 m 7 n 8 o
この表で「データ有り」のセルの行列位置は以下になる ・・・ R(ROW)：行 / C(COLUMN)：列
A B C D 1 R1C2 R1C3 R1C4 2 R2C2 R2C3 R2C4 3 R3C2 R3C3 R3C4 4 R4C2 R4C4 5 R5C4 6 R6C4 7 R7C4 8 R8C4
んで、この「データ有り」セルをA列に返すとき、順番が
A B C D 1 1番目 5番目 8番目 2 2番目 6番目 9番目 3 3番目 7番目 10番目 4 4番目 11番目 5 12番目 6 13番目 7 14番目 8 15番目
ここまで書くと分かるように、行列番号を抽出番目ごとにA列に出すために 行列番号を用いて数値を出して順番に抽出するんですわ。
しかし、今回の番手計算は まず列（C）が小さい順が優先され行（R）が小さい順がさらに優先 されますんで、まず、この考えで行列計算をし、順番を決めてみると
A B C D 1 201 301 401 2 202 302 402 3 203 303 403 4 204 404 5 405 6 406 7 407 8 408
※データありセルの 列番号(C)×100＋行番号(R) の結果。（列が優先されるんで列の桁を大きくしてる）
・・・ おぉ！ このデータを小さい数値順にA列に返すとデータがならぶやん！ となります。
けど、今回は「R1C1」形式（行→列の順番）で返すんで 列→行の順番で返されたセル位置数値は 使いモンになりまへん。
じゃあ、行番号（R）×100 + 列番号（C） で計算すると。。。
A B C D 1 102 103 104 2 202 203 204 3 302 303 304 4 402 404 5 504 6 604 7 704 8 804
こうなっちゃいますわ。 ・・・B2セルよりC1セルのほうが数値ちっさくなっちゃうやん！ 使いモンになりまへんな。
ん〜。行×100+列 を使って、なんとか計算でけへんもんか・・・
・・・あっ！ 行→列であかんのなら、列→行→列にしたったらええやん！
・・・ 列×10000000+行×100+列 → これやと数値は以下になりますわ。
A B C D 1 20000102 30000103 40000104 2 20000202 30000203 40000204 3 20000302 30000303 40000304 4 20000402 40000404 5 40000504 6 40000604 7 40000704 8 40000804
これなら 行→列のデータが残ったまま返せるやん！
ということですわ。
→ /(***<>"") の部分は計算した結果をデータの有無で割り算し
・データあり → TRUE → 1で割り算 (列→行→列）のデータを返す ・データなし → FALSE→ 0で割り算 → エラー。
を返してるんですな。 AGGREGATEの第二引数6はエラーを無視できるので こんな処理してます。
あと 最後のROW(A1)は「n番目」を指定してますわ。上記の列→行→列で返された数値を 小さい順に返すので A1セルは「1番目」、A2セルは「2番目」・・・ にせなあきまへん。 その「n番目」をROW(A1）のところで設定してますわ。
要約すると。
AGGREGATE(SMALL処理,エラー無視,(列→行→列 計算データ)÷（データあり無し？）,n番目）
の処理になってるってことです。
MODは列→行→列で返されたデータの最初の「列」は順番のためだけの計算なのでいらないので 除去する処理。
TEXTはR1C1形式に変換する （例：TEXT(102,"!R0!C00") → "R1C02"の文字列で返す）
んでINDIRECT
ってことっす。
・・・・長文ｗ （GobGob） 2017/01/25(水) 17:15
一応、なじみある関数（？）案。
INDEX案
A1 =IFERROR(
INDEX(A$1:D$1000,
MOD(SMALL(IF(B$1:D$1000<>"",COLUMN(B$1:D$1000)*100+ROW(B$1:D$1000)),ROW(A1)),100),
INT(SMALL(IF(B$1:D$1000<>"",COLUMN(B$1:D$1000)*100+ROW(B$1:D$1000)),ROW(A1))/100)),"")
OFFSET案
A1 =IFERROR(
OFFSET(A$1,
MOD(SMALL(IF(B$1:D$1000<>"",COLUMN(B$1:D$1000)*100+ROW(B$1:D$1000)),ROW(A1)),100)-1,
INT(SMALL(IF(B$1:D$1000<>"",COLUMN(B$1:D$1000)*100+ROW(B$1:D$1000)),ROW(A1))/100)-1),"")
※両方ともCTRL+SHIFT+ENTERで数式確定後、下へコピー。 （GobGob） 2017/01/25(水) 17:26
なんとか動きましたが、配列自体をイマイチ理解していないため、漠然と理解できたという感じです。「なじみある関数（？）案」のほうがより分かりやすかったです。
今後のもう少しレベルアップしたら、何回も読み返して勉強させていただきたいと思いますので、大切に保存しておきます。
しかし、今回は仕様を間違えていたため、もっと単純な設定でした・・・・・
（Index） 2017/01/25(水) 20:52
A B C D 1 太郎 2 2 次郎 5 3 三郎 4
Sheet2
A B C D 1 太郎 2 太郎 3 次郎 4 次郎 5 次郎 6 次郎 7 次郎 8 三郎 9 三郎 10 三郎 11 三郎
Sheet1のA列に対応するD列の番号だけSheet2のA列に順番に取り出すという仕様です。
実際は、太郎,次郎,三郎以外にも、数百から数千列のデータがあり、D列も2,5,4のような小さい値ではなく、数百から数千という大きな値です。
さらに、作業列なしでできるかわかりませんが、Sheet2のA列に順番に取り出すのではなく、Sheet3のA列にランダムに取り出すという計算もできればと思っています。Sheet2からSheet3への変化は、作業列や作業シートを使ったやり方なら、調べればどうにかなりそうですので、どうしても今すぐに必要なわけではありません。
Sheet3
A B C D 1 次郎 2 次郎 3 次郎 4 太郎 5 三郎 6 三郎 7 次郎 8 三郎 9 太郎 10 三郎 11 次郎
たびたび長文で恐縮ですが、もしよろしければお願いします。
（Index） 2017/01/25(水) 20:56
ランダム案は置いといて、とりあえずくり返しは
『指定回数、行を展開するには？』（excel初心者） [[20121115152918]]
ご参考にどうぞ。 ※配列数式案はSmall処理はAGGREGATEで、 IFはIFERRORでやればもっとシンプルになるかもねー。
※けど数千のくり返しは配列数式案はキツイかもねー。 （GobGob） 2017/01/25(水) 22:08
ありがとうございました。
（Index） 2017/01/26(木) 11:37
'Sheet3のA列にランダムに取り出し
Dim dt, r As Range, d As Long, a As Long, i As Long, m As Long, x As Long
Sheets("Sheet3").Cells.Clear
With Sheets("Sheet1")
d = Application.WorksheetFunction.Sum(.Range("D:D"))
a = Application.WorksheetFunction.CountA(.Range("A:A"))
ReDim dt(d)
Set r = Sheets("Sheet1").Range("A1")
For i = 1 To d
m = Application.WorksheetFunction.Sum(Range(.Range("D1"), .Range("D" & r.Row)))
If m < i Then Set r = r.Offset(1)
dt(i) = r.Value
Next i
End With
For i = 1 To d
Do
x = Int(Rnd() * d) + 1
If Sheets("Sheet3").Cells(x, 1) = "" Then Sheets("Sheet3").Cells(x, 1) = dt(i): Exit Do
Loop
Next i
End Sub
（mm） 2017/01/26(木) 13:34
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.