[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数行から同じグループのみを１列に』（オレンジ）
Sheet1のA列に名前が、B列～G列には0～500までの数字がランダムに入っています。
このような組み合わせが20行ほど続いているのですが、別シートでA列の名前が同じものを0を飛ばして１行にまとめたいです。
可能なら例えば別シートのA1に名前を入力したらB1以降にずらっと表示されるようにしたいのですが、どんな式を使用したらよいでしょうか？
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
仮にSheet1のようなデータでSheet2のようなデータを得たい場合
Sheet1
|[A]|[B]|[C]|[D]|[E]|[F]|[G]
[1]|A |247| 0| 0| 0| 0| 0
[2]|B | 48| 0| 0| 0| 0| 22
[3]|C | 0|340| 0| 0| 0|309
[4]|D | 0| 0| 0| 0| 0| 0
[5]|E |349| 0| 35| 0|258|426
Sheet2
|[A]|[B]|[C]|[D]|[E]|[F]|[G]
[1]|A |247| | | | |
[2]|B | 48| 22| | | |
[3]|C |340|309| | | |
[4]|D | | | | | |
[5]|E |349| 35|258|426| |
Sheet2の B1=IFERROR(INDEX(Sheet1!$B:$G,MATCH($A$2,Sheet1!$A:$A,0),SMALL(IF(INDEX(Sheet1!$B:$G,MATCH($A$2,Sheet1!$A:$A,0),)>0,COLUMN($A$1:$F$1),""),COLUMN(A$1))),"")
配列数式として確定 B1をコピーして、必要範囲に張り付け
でいかがでしょうか？ （稲葉） 2020/12/14(月) 18:06
VBAなら回答できますが、私では関数での回答は難しいですねぇ。
（稲葉） 2020/12/15(火) 10:04
別シートにまとめる場合、数値の順番は出現順なのだろうか？ 値の大きさの昇順あるいは降順でも構わないのだろうか？ （ねむねむ） 2020/12/15(火) 10:20
ねむねむさんのつなぎで、ユーザー定義関数
１）標準モジュールに以下のコード
Function MargeNames(strFind As String, rngData As Range, cnt As Long) As Variant
Dim r As Long
Dim c As Long
Dim tmp As Variant
Dim ans As String
Dim app As Application
Set app = Application
For r = 1 To rngData.Rows.Count
tmp = app.Transpose(app.Transpose(WorksheetFunction.Index(rngData, r, 0)))
If tmp(1) = strFind Then
For c = 2 To rngData.Columns.Count
If tmp(c) <> 0 Then
ans = ans & Chr(2) & tmp(c)
End If
Next c
End If
Next r
MargeNames = Split(Mid(ans, 2), Chr(2))(cnt - 1)
End Function
２）H列に手入力の名前 ３）I1=IFERROR(MargeNames($H1,$A$1:$G$5,COLUMN(A1)),"") ４）I1を必要範囲コピー COLUMN(A1)のA1は、左端の範囲が変わっても（I列始まりが、B列始まりになっても）A1を指定してください。
結果図
|[A]|[B]|[C]|[D]|[E]|[F]|[G]|[H]|[I]|[J]|[K]|[L]|[M]|[N]
[1]|A |247| 0| 0| 0| 0| 0|A |247|340|309| | |
[2]|B | 48| 0| 0| 0| 0| 22|B | 48| 22|349| 35|258|426
[3]|A | 0|340| 0| 0| 0|309| | | | | | |
[4]|C | 0| 0| 0| 0| 0| 0| | | | | | |
[5]|B |349| 0| 35| 0|258|426| | | | | | |
（稲葉） 2020/12/15(火) 10:51
＞＞稲葉様
VBAはまったく知識が無いのですが、結果を別シートに表示することは可能でしょうか？
（オレンジ） 2020/12/15(火) 14:06
昇順なり降順の方が式が短くなる。 (最初に思い浮かんだ式が昇順・降順のものだったということもあるが） 出現順の場合。 （ねむねむ） 2020/12/15(火) 14:23
Sheet1に元のデータがあり、別シートのA1セルに名前を入れるとB1セルから右へ表示する。 =IFERROR(INDEX(Sheet1!$A$1:$G$20,INT(AGGREGATE(15,6,ROW($1:$20)*100/(Sheet1!$A$1:$A$20=$A1)+COLUMN($B:$G)/(Sheet1!$B$1:$G$20>0),COLUMN(A1))/100),MOD(AGGREGATE(15,6,ROW($1:$20)*100/(Sheet1!$A$1:$A$20=$A1)+COLUMN($B:$G)/(Sheet1!$B$1:$G$20>0),COLUMN(A1)),100)),"")
（ねむねむ） 2020/12/15(火) 14:26
ねむねむさんがいらしゃったので蛇足になりますが、
＞３）I1=IFERROR(MargeNames($H1,$A$1:$G$5,COLUMN(A1)),"") 普通の関数みたいに使えるので、↑の式を別シートに置けば大丈夫ですよ。 （稲葉） 2020/12/14(月) 18:06の投稿のSheet2の表に当てはまるように調整してみてください。 Sheet2!B1=IFERROR(MargeNames($A1,Sheet1!$A$1:$G$5,COLUMN(A1)),"") で大丈夫なはず
（稲葉） 2020/12/15(火) 14:28
式を入力後、右へフィルコピーしてみてくれ。 なお、Sheet1のデータは最大20行目までに対応している。 もっと行がある場合は式中の$20をすべて同じ値で大きくしてくれ。 （ねむねむ） 2020/12/15(火) 14:29
ああ、こちらでもいいか。 =IFERROR(MOD(AGGREGATE(15,6,(ROW($1:$20)*100/(Sheet1!$A$1:$A$20=$A1)+COLUMN($B:$G)/(Sheet1!$B$1:$G$20>0))*1000+Sheet1!$B$1:$G$20,COLUMN(A1)),1000),"") （ねむねむ） 2020/12/15(火) 14:36
＞＞ねむねむ様
いただいた式で望んだ結果になりました。ありがとうございました。
（オレンジ） 2020/12/15(火) 15:19
>元データSheet１の表示されている数値の一つ上のセル 数値セルと同じ列の1行目ではなく一つ上？ 例えば340の場合、C1セルの値ではなく340の一つ上、0を表示したいということだろうか？
（ねむねむ） 2020/12/15(火) 17:11
私の 2020/12/15(火) 14:26の式で INT(AGGREGATE(15,6,ROW($1:$20)*100/(Sheet1!$A$1:$A$20=$A1)+COLUMN($B:$G)/(Sheet1!$B$1:$G$20>0),COLUMN(A1))/100) 部分が対象の行番号になるのでこれを INT(AGGREGATE(15,6,ROW($1:$20)*100/(Sheet1!$A$1:$A$20=$A1)+COLUMN($B:$G)/(Sheet1!$B$1:$G$20>0),COLUMN(A1))/100)-1 とすると一行上を求めることになる。 （ねむねむ） 2020/12/17(木) 09:07
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.