エクセル検索

質問

A　　B　　　　　　　　　C

111　佐藤　　　　　　【111】
111　岩本　　　　　　佐藤
111　榎元　　　　　　岩本
222　仙田　　　　　　榎本
222　飯田
222　斉藤
333　飯原
333　佐野　　　　　　【222】
444　鳥島　　　　　　仙田
555　凛田　　　　　　飯田
　　　　　　　　　　　　斉藤

Aの列にクラスの名が表示してあり、B列に名前が表示してあります。
A列B列ともに人数は増える可能性はあります。
それをリストにしたいので　C列もしくは別シートにクラス別に縦で表示させるためには
どのような数式を使えば表示可能になるでしょうか？

回答No.4

bunjii · ベストアンサー

>C列もしくは別シートにクラス別に縦で表示させるためには
>どのような数式を使えば表示可能になるでしょうか？
Excel 2013で検証してみました。Excel 2007以降のバージョンで再現できると思います。
貼付画像のA列は組の番号として数値を入力しました。
A列は昇順になっていることが必要条件です。
A1は0で表示書式をユーザー定義で 0"組" としました。
C列に111組から順に組番号と名前を抽出しました。
C2=IF(ROW()=2,$A$2,IF(COUNTA(B$2:B$100)-SUMPRODUCT((C1:C$2<>"")*1,ISTEXT(C1:C$2)*1),IF(C1="",INDEX(A:A,MATCH(MAX(C1:C$2,A$1),A:A)+1),IFERROR(INDEX(B:B,LARGE(INDEX((A$2:A$100=MAX(C1:C$2))*ROW(A$2:A$100),,1),COUNTIF(A:A,MAX(C1:C$2))-ROW(#REF!)+MATCH(MAX(C1:C$2),C1:C$2,0))),"")),""))
C2を下へ必要数コピーします。
今回が組番号と名前の列は最大100行としましたが中間で行の挿入をすれば検索範囲は自動的に増加します。

お礼

ありがとうございます！

助かりました！！

ここまでは考えられませんでした。

回答No.3

MackyNo1

クラス名が添付画像のようにクラスごとにまとまってA2セル以下に入力されており、抽出したいクラスがD1セルに入力されているなら、以下の式を入力して下方向にオートフィルするのが簡単です。

=IF(COUNTIF(A:A,$D$1)<ROW(1:1),"",INDEX(B:B,MATCH($D$1,A:A,0)+ROW(1:1)-1))

お礼

ちょっと思った感じと違ったので
ごめんなさい。

でも今後数式参考にさせていただきます。

回答No.2

tom04

こんばんは！
VBAでの一例です。
↓の画像のように左側Sheet1のデータをSheet2に表示するとします。
Sheet1の1行目は項目行となっているとして・・・

Alt＋F11キー　→　メニューの「挿入」　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, lastRow As Long, wS As Worksheet
Set wS = Worksheets("Sheet2")
Application.ScreenUpdating = False
wS.Range("A:A").Clear
With Worksheets("Sheet1")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A:A").AdvancedFilter Action:=xlFilterCopy, copytorange:=wS.Range("B1"), unique:=True
wS.Range("B:B").Sort key1:=wS.Range("B1"), order1:=xlAscending, Header:=xlYes
For i = 2 To wS.Cells(Rows.Count, "B").End(xlUp).Row
.Range("A1").AutoFilter field:=1, Criteria1:=wS.Cells(i, "B")
wS.Cells(Rows.Count, "A").End(xlUp).Offset(1) = "【" & wS.Cells(i, "B") & "】"
Range(.Cells(2, "B"), .Cells(lastRow, "B")).SpecialCells(xlCellTypeVisible).Copy _
wS.Cells(Rows.Count, "A").End(xlUp).Offset(1)
Next i
.AutoFilterMode = False
wS.Range("A1").Delete shift:=xlUp
wS.Range("B:B").Clear
wS.Activate
End With
Application.ScreenUpdating = True
End Sub 'この行まで

※　Sheet1のA列の並びはバラバラでも構いません。
※　関数でないのでデータ変更があるたびにマクロを実行する必要があります。m(_ _)m

お礼

すみません。
マクロが使いこなせないので
ごめんなさい。

回答No.1

keithin

簡単な方法：
Ｂ列に「番号」列を挿入
Ｂ２に
=IF(A2="","",A2&"-"&COUNTIF($A$2:A2,A2))
と記入、以下コピー

Ｆ１にクラスを記入する
Ｆ２に
=IF(ROW(F1)>COUNTIF(A:A,F$1),"",VLOOKUP(F$1&"-"&ROW(F1),B:C,2,FALSE))
と記入、以下コピーする。

＃適切に応用してご利用ください。

お礼

遅くなりましたがありがとうございます。

今回は列も増やしたくなかったため
今後の参考にさせていただきます！