【Excel】該当データ表示方法について

質問

Excelの表で、(A)列にデータ名、(B)列にデータ値が入力されているとして、
1.(C)列に(B)列のデータ値の種類を昇順で表示、
2.(D)列に(C)列のデータ値を持つ(A)列の文字を表示、
3.(E)列に(D)列のデータ種類の数を表示
という３つの内容を実現したいのですが、(１つでも)
ご存知の方、教えて下さい。よろしくお願いしますm(__)m

【イメージ】
　 |　(A) (B)　(C) 　(D)　　　(E)
―――――――――――――――――――
(1)|　A　10　10　A、D、E　　 3
(2)|　B　20　20　B、G　　　　2
(3)|　C　30　30　C、F　　　　2
(4)|　D　10
(5)|　E　10
(6)|　F　30
(7)|　G　20

回答No.3

imogasi · ベストアンサー

(データ）A1:
A列　　　B列　　　C列　　　　D列　　　E列　　　G列
（空白行）
A 10 1 10 A、D、E 3
B 20 2 20 B、G 2
C 30 3 30 C、F 2
D 10 50 H 1
E 10
F 30
G 20
H 50 4
（関数式）C列
C2に=IF(COUNTIF($B$2:B2,B2)=1,MAX($C$1:C1)+1,"")
といれて、C3以下最下行まで式を複写する。
結果は上記C列の通り
（関数式）D列
D2に=INDEX($B$2:$B$100,MATCH(ROW()-1,$C$2:$C$100,0),1)といれて、D3以下C列の最大値まで式を複写する。$B$100の１００はデータ数より多ければよい。
結果上記D列の通り。
（関数式）F列
F2に=COUNTIF($B$2:$B$100,D2)といれｆ３以下にD列の最下行まで式を複写する。
結果上記F列の通り。
問題はE列だが、複雑になりそうなので、考えて出来れば、別回答にします。

補足

ご回答有難う御座います。もう一つ知りたいのですが、D列のデータは昇順に表示することは、出来ないのでしょうか。よろしくお願いします。m(__)m

回答No.5

DoragonFang

ANo.2の続きです。
Ｅ列の１つのセルにＡ、Ｄ、Ｅなどと表示することはやっぱりマクロを使わないと出来ないようですが、Ｇ列以降に１列に１つずつ該当するデータの種類を表示することは可能です。

Ｆ１セルに以下の式を入力して、Ｆ２以下にコピーします。

=IF(E1>0,INDEX($A$1:$A$20,MATCH($C1,$B$1:$B$20,0),1),"")

ifでE1セルの個数が１以上の場合に表示する判定をしています。あとはMATCH関数でC列の数値に合致するA列の種類を表示します。これで、最初の１つ目の種類は表示されます。上記の式では範囲を１～２０行までのしていますので、必要に応じて設定してください。

次にＧ1セルに以下の式を入力し、Ｇ２以下および、Ｈ，Ｉ・・・と以降の列に予測される種類の数分だけコピーします。

=IF($E1-COUNTA($F1:F1)>0,INDEX(INDIRECT("$A$"&TEXT(MATCH(F1,$A$1:$A$20,0)+1,"###")&":$A$20"),MATCH($C1,INDIRECT("$B$"&TEXT(MATCH(F1,$A$1:$A$20,0)+1,"###")&":$B$20"),0),1),"")

少々式が複雑ですが、INDIRECT関数で自分より前にある種類をのぞいた残りの行でのＦ１セルと同じことをしています。
例えば、Ｇ１せるでは、F1セルにすでに"A"が表示されていますので、このAがある１行目＋１から範囲をさだめて、

=IF($E1-COUNTA($F1:F1)>0,INDEX($A$2:$A$20,MATCH($C1,$B$2:$B$20,0),1),"")

という式になります。
あとどうしてもＥ列に表示したいときは、Ｆ列以降の文字を連結する式をＥ列に書いておけば（例えば =F1&G1&H1&I1 とか）、ほぼ質問の内容になるかと思います。

回答No.4

imogasi

＃３です。
＃３で予告した、質問のD列の回答を上げます。
式が長くなって、いっそVBAでやりたい誘惑に駈られますが。
（データ）
A列　　　B列　　　C列　　　D列　　E列
（空白行）＃３と合わす為でなくても良いが式が変わる

A 10 10001 10 A D E
B 20 20001 20 B G
C 30 30001 30 C F
D 10 10002 50 H
E 10 10003
F 30 30002
G 20 20002
H 50 50001
（関数式）C列
C2に=B2&LEFT("00",3-LEN(COUNTIF($B$2:B2,B2)))&COUNTIF($B$2:B2,B2)
といれて最下行まで式を複写する。結果は上記C列の通り。
（D列）
＃３で回答した方法で出す。
（関数式）E列以右
E2に=IF(ISERROR(INDEX($A$1:$A$100,MATCH($D2&TEXT(COLUMN()-4,"000"),$C$1:$C$100,0),1)),"",INDEX($A$1:$A$100,MATCH($D2&TEXT(COLUMN()-4,"000"),$C$1:$C$100,0),1))
といれる。適当に右方向に式を複写する。Z列まで複写した
とすると、E2:Z2を範囲指定して、Z2で＋ハンドルを出し
D列の最下行まで引っ張る。
結果は上記のE,F、G列・・の通り。
B列の１０，２０，３０・・の出現回数は９９９回以下の仮定となっています。００１とかの連番を振る仕組みのため。９９９９回とかまでにも容易に変えられます。

補足

ご回答有難う御座います。教えて頂いた『＃３』はできたのですが、『＃４』のD列の設定で、D2に【=INDEX($B$2:$B$100,MATCH(ROW()-1,$C$2:$C$100,0),1)】を入力したのですが、＃N/Aと表示されてしまいます。勝手なお願いで申し訳ないですが、原因を教えて頂けないでしょうかm(__)m

回答No.2

DoragonFang

質問の１．および３．は下記の方法で可能です。
２．については再帰できる関数というものがないので、関数ではかなり難しいと思います。これだけは＃１で回答されているようにマクロを使うしかないと思いますが。

３．について
参照の都合上、こちらを先に書きます。

まず、Ｅ１セルにsmall関数を使って最小値を探し、それと同じ数値をcountif関数で数えます。
具体的には、

=COUNTIF([範囲],SMALL([範囲],1))

です。[範囲]にはデータのあるＢ列を指定してください。例→ B1:B7　とか、

Ｅ２は、

=COUNTIF([範囲],SMALL([範囲],SUM($E$1:E1)+1))

として、これをデータ数分だけ下にコピーしてください。small関数は第２引数で指定した順位のデータを返す関数なので、第２引数にすでにカウントした分＋１を設定しています。

１．について
Ｃ１セルについては、最小値を得ればいいので、

=SMALL([範囲],1)

とします。
Ｃ２セルは、

=IF(E2=0,"",SMALL([範囲],SUM($E$1:E1)+1))

として、Ｃ３以下にコピーします。
if関数はエラーを表示させないためだけに使っています。

Ｄ列については、先に書きましたように関数では難しいと思います。

回答No.1

BLUEPIXY

マクロを作ってみました
Public Sub 処理()
Dim base As Range, i, pos
Dim NumList, max, min, wk
Set NumList = CreateObject("Scripting.Dictionary")
max = -999999999
min = 999999999
Set base = Range("A1")
i = 0
Do While (base.Offset(i).Value <> "")
If Not NumList.Exists(base.Offset(i, 1).Value) Then '始めてでてくる数値を登録
NumList.Add base.Offset(i, 1).Value, base.Offset(i).Value
If max < base.Offset(i, 1).Value Then max = base.Offset(i, 1).Value
If min > base.Offset(i, 1).Value Then min = base.Offset(i, 1).Value
Else '重複する場合、連結する
NumList.Item(base.Offset(i, 1).Value) = NumList.Item(base.Offset(i, 1).Value) & "," & base.Offset(i).Value
End If
Range(base.Offset(i, 2), base.Offset(i, 4)).ClearContents
i = i + 1
Loop
pos = 0
For i = min To max
Do Until NumList.Exists(i)
i = i + 1
If i > max Then Exit Sub
Loop
base.Offset(pos, 2).Value = i
base.Offset(pos, 3).Value = NumList.Item(i)
wk = Split(NumList.Item(i), ",")
base.Offset(pos, 4).Value = UBound(wk) + 1
pos = pos + 1
i = i + 1
Next
End Sub