同じ行に混在している別々の項目の抜き出し

質問

Excelにて困りごとです。

同じ行に、例えばA列に「佐藤」「田中」「山田」の氏名がランダムに何回も（例えば100行ランダムに）書いてあり、B列に毎回のテストの点数、C列にテストを受けた日付が記載してあるとします。

これを各受講者ずつ抜き出して、佐藤さんの場合はD列に点数、E列に日付、田中さんはF列に点数、G列に日付、というように並べるには、どういう関数を使えばいいでしょうか？

回答No.6

mate0128

並べ替えとフィルタを使えば、楽になると思います。

まず、名前の入力してある列のどれか、１つのセルを選んでおいて、［データ］-［並べ替え］で、並べ替えます。そうすると、名前のアイウエオ順に並びます。

並べ変わった結果を［切り取り］ー［貼り付け］、または、［コピー］-［貼り付け］で、自分の貼り付け隊列に貼り付けます。

後で並べ替えを元に戻したかったら、先に１番左に列を挿入して１から始まる連番を振っておきます。後から、その番号を選択しておいて、並べ替えを実行してください。

回答No.5

imogasi

#4です。
質問者の要望と少し違いますが、操作だけでまとめの表を作れないかと考えて、下記は出来るので、書いてみます。
こういう表はどうですか。
ピボットテーブルを使う方法です。
例データ
Sheet1に　データに点数がありませんが、加えた表でも操作は同じです
氏名 科目 日付
a 英語 2011/1/1
a 数学 2022/2/2
b 数学 2011/2/1
b 英語 2011/2/4
c 数学 2011/2/4
b 国語 2011/3/1
c 英語 2011/3/2
a 数学 2011/3/2
b 数学 2011/3/3
ーー
データーピボットテーブルと・・
次へ
次へ
レイアウトで
「行」へ
右のボタンの中から
氏名
科目
日付
をそれぞれ、マウスでD&D（ドラッグアンドドロップ）
データは氏名をD&D（ドラッグアンドドロップ）して個数等を選ぶ
完了
ここでピボットテーブルが出来る。
ーー
英語/合計の１つを選択
右クリック
「表示しない」を選択
ーー
a合計の１つを選択し
右クリック
表示しない
--
結果
データの個数 / 氏名
氏名 科目 日付 合計
a 英語 2011/1/1 1
数学 2011/3/2 1
2011/2/2 1
b 英語 2011/2/4 1
国語 2011/3/1 1
数学 2011/2/1 1
2011/3/3 1
c 英語 2011/3/2 1
数学 2011/2/4 1
総計 9
--
ここで
合計列
総計行
を除いて範囲指定
コピー
他のセル範囲に貼り付け
ーー
結果　レイアウトが乱れますが実際にシートでやってみてください。－－は本当は空白セルです。
氏名 科目 日付
a 英語 2011/1/1
ーー 数学 2011/3/2
ーー ーー 2011/2/2
b 英語 2011/2/4
ーー 国語 2011/3/1
ーー 数学 2011/2/1
ーー ーー 2011/3/3
c 英語 2011/3/2
ーー 数学 2011/2/4
ーー 数学 2011/2/4

となる

回答No.4

imogasi

そもそも、エクセルで条件による抜き出しは、良い関数がない。こういう問題には適してない。エクセルの関数は計算が中心。
無理にしようとすると、既に出ている回答のような、長く、且つ初心者には理解できない式になる。
今まではそれでも初心者や、ここの質問者はコピペして、旨く出来ましたと、いっているのが多い。
それでもよければそうしたら良い。
ーーー
もうひとつ、A列に現われる名前の一覧を出すのも、質問者は気づいてないだろうが、結構難しいのだ。
そこで関数を使わず操作だけでやる方法を参考に挙げておく。
ーー
A列の名前の、もれがなく、ダブりがない、一そろいの名前が必要です。その出し方を書く。
A1:A13　３対づつになっているが、２回しかない人が居ても良い
氏名
佐藤
田中
上野
山田
佐藤
田中
上野
山田
佐藤
田中
上野
山田
メニュ０のデーターフィルターフィルタオプションの設定ー
抽出先
指定した範囲
リスト範囲　A1:A13
抽出する範囲　D1:D11
重複するレコードは無視する　
OK
ーーー
結果
D1:D5
氏名
佐藤
田中
上野
山田
ーーーー
D1:D5範囲指定
編修ーコピー
E２：IV２を名愛指定
編修ー形式を選択して張り付け
行列を入れ替える。
ーーー
E1を選択
E1に式　=IF(MOD(COLUMN(),3)=2,INDEX($E2:$IV2,1,INT((COLUMN()-3)/3)),"")
を入れて右方向に式を吹くさh
D1:D4をクリア
E2より右列をクリア。
E1:IV1をコピー
同じ範囲にkウィ式を選択して貼り付けー値
で式を消す。
＝＝＝
名前で並べ替えて、VBAで名前ごとに別のセル範囲に移すのが、考えやすいがVBAができないあｄろうし悩ましい。、
ーーー
でも手作業で並べ替えるのが、質問を出して回答を待って理解して、自分のために修正するより速いだろう。
５０人までなら確実に手作業がに速い。

回答No.3

kagakusuki

回答番号:ANo.2です。

　先程の私の回答で述べた方法は、元データに氏名が現れた順番に、データを並べているため、元データが日付順に並んでいない場合には、抽出したデータも日付順には並ばない事があります。
　もし、元データが日付順に並んでいない場合であっても、抽出したデータを日付順に並べ換える必要がある場合には、作業列を3列使用した、以下の様な方法にすると良いと思います。
　今仮に、元データが存在しているSheetの名前がSheet1であるものとして、Sheet2のA列～C列を作業列として使用するものとします。
　まず、Sheet2のA1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT(Sheet1!$C:$C),"",SMALL(Sheet1!$C:$C,ROWS($1:1)))

　次に、Sheet2のB1セルに次の数式を入力して下さい。

=IF($A1="","",INDEX(Sheet1!$A:$A,SUMPRODUCT(ROW(OFFSET(Sheet1!$C$1,,,MATCH(9^9,Sheet1!$C:$C)))*(OFFSET(Sheet1!$C$1,,,MATCH(9^9,Sheet1!$C:$C))=$A1)*(COUNTIF(OFFSET(Sheet1!$C$1,,,ROW(OFFSET(Sheet1!$C$1,,,MATCH(9^9,Sheet1!$C:$C)))),$A1)=COUNTIF($A$1:$A1,$1)))))

　次に、Sheet2のC1セルに次の数式を入力して下さい。

=IF($A1="","",INDEX(Sheet1!$B:$B,SUMPRODUCT(ROW(OFFSET(Sheet1!$C$1,,,MATCH(9^9,Sheet1!$C:$C)))*(OFFSET(Sheet1!$C$1,,,MATCH(9^9,Sheet1!$C:$C))=$A1)*(COUNTIF(OFFSET(Sheet1!$C$1,,,ROW(OFFSET(Sheet1!$C$1,,,MATCH(9^9,Sheet1!$C:$C)))),$A1)=COUNTIF($A$1:$A1,$A1)))))

　次に、Sheet2のA1～C1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、Sheet1のD2セルに次の数式を入力して下さい。

=IF(OR(D$1="",ROWS($2:2)>COUNTIF($A:$A,D$1)),"",INDEX(Sheet2!$C:$C,SUMPRODUCT(ROW(OFFSET(Sheet2!$B$1,,,MATCH(9^9,Sheet2!$A:$A)))*(OFFSET(Sheet2!$B$1,,,MATCH(9^9,Sheet2!$A:$A))=D$1)*(COUNTIF(OFFSET(Sheet2!$B$1,,,ROW(OFFSET(Sheet2!$B$1,,,MATCH(9^9,Sheet2!$A:$A)))),D$1)=ROWS($2:2)))))

　次に、Sheet1のE2セルに次の数式を入力して下さい。

=IF(OR(D$1="",ROWS($2:2)>COUNTIF($A:$A,D$1)),"",INDEX(Sheet2!$A:$A,SUMPRODUCT(ROW(OFFSET(Sheet2!$B$1,,,MATCH(9^9,Sheet2!$A:$A)))*(OFFSET(Sheet2!$B$1,,,MATCH(9^9,Sheet2!$A:$A))=D$1)*(COUNTIF(OFFSET(Sheet2!$B$1,,,ROW(OFFSET(Sheet2!$B$1,,,MATCH(9^9,Sheet2!$A:$A)))),D$1)=ROWS($2:2)))))

　次に、Sheet2のD2～E2の範囲をコピーして、下方や右方の、抽出結果を表示させるセル範囲に、貼り付けて下さい。

　以上です。

回答No.2

kagakusuki

まず、
D1セルに　佐藤
F1セルに　田中
と言う具合に、1列おきの1行目のセルに、各氏名を入力して下さい。
　次に、D2セルに次の数式を入力して下さい。

=IF(OR(D$1="",ROWS($2:2)>COUNTIF($A:$A,D$1)),"",INDEX($B:$B,SUMPRODUCT(ROW($A$1:$A$100)*($A$1:$A$100=D$1)*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A$100)),D$1)=ROWS($2:2)))))

　次に、E2セルに次の数式を入力して下さい。

=IF(OR(D$1="",ROWS($2:2)>COUNTIF($A:$A,D$1)),"",INDEX($C:$C,SUMPRODUCT(ROW($A$1:$A$100)*($A$1:$A$100=D$1)*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A$100)),D$1)=ROWS($2:2)))))

　次に、D2～E2の範囲をコピーして、下方や右方の、抽出結果を表示させるセル範囲に、貼り付けて下さい。

　以上です。

　尚、元のデータの行数が、次々に増えて行き、100行以内には収まらなくなる場合には、以下の様な数式とすれば、元データの行数に合わせて、一々数式を修正する必要が無くなります。（もし、満点が999点かそれ以上になる場合は、数式中の999となっている部分を、満点よりも高い数値に変更して下さい）

D2セルの数式

=IF(OR(D$1="",ROWS($2:2)>COUNTIF($A:$A,D$1)),"",INDEX($B:$B,SUMPRODUCT(ROW(OFFSET($A$1,,,MATCH(999,$B:$B)))*(OFFSET($A$1,,,MATCH(999,$B:$B))=D$1)*(COUNTIF(OFFSET($A$1,,,ROW(OFFSET($A$1,,,MATCH(999,$B:$B)))),D$1)=ROWS($2:2)))))

E2セルの数式

=IF(OR(D$1="",ROWS($2:2)>COUNTIF($A:$A,D$1)),"",INDEX($C:$C,SUMPRODUCT(ROW(OFFSET($A$1,,,MATCH(999,$B:$B)))*(OFFSET($A$1,,,MATCH(999,$B:$B))=D$1)*(COUNTIF(OFFSET($A$1,,,ROW(OFFSET($A$1,,,MATCH(999,$B:$B)))),D$1)=ROWS($2:2)))))

回答No.1

tom04

こんにちは！
一例です。

↓の画像のようにA列を挿入し、作業用の列としています。
（氏名を重複なしに抽出するためです）

作業列A2セルに
=IF(COUNTIF($B$2:B2,B2)=1,ROW(),"")
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そしてF1セルに
=IF(COUNT($A:$A)*2<COLUMN(A1),"",INDEX($B$1:$B$1000,SMALL($A$1:$A$1000,COLUMN(B1)/2)))
という数式を入れ隣のG1セルを結合しています。
この結合セルのフィルハンドルで列（右）方向に2列ずつオートフィルでしっかりコピーしておきます。

F2セルに
=IF(OR(F$1="",COUNTIF($B$1:$B$1000,F$1)<ROW(A1)),"",INDEX($C$1:$C$1000,SMALL(IF($B$1:$B$1000=F$1,ROW($A$1:$A$1000)),ROW(A1))))
これは配列数式になってしまいますので、F2セルに貼り付け後
Shift＋Ctrlキーを押しながらEnterキーで確定！

G2セル（これも配列数式です）に
=IF(OR(F2="",COUNTIF($B$1:$B$1000,F$1)<ROW(A1)),"",INDEX($D$1:$D$1000,SMALL(IF($B$1:$B$1000=F$1,ROW($A$1:$A$1000)),ROW(A1))))
という数式を入れ、Shift＋Ctrlキーを押しながらEnterキーで確定！
G2セルの表示形式は「日付」にしておきます。

そしてF2・G2セルを範囲指定し列方向に2列ずつオートフィルでコピー！
最後にそのまま下へコピーすると画像のような感じになります。

以上、他によい方法があればごめんなさいね。m(__)m