Excel 重複データの番号を抽出

質問

Excel：Office2003
OS：WindowsXP

excelのデータで
A列 B列
1 A
2 B
3 A
4 C
5 D
6 A
7 A
8 B
9 B
10 C
以上のように並んでいるデータを以下の

E列 F列 G列 H列 I列
A 1 3 6 7
B 2 8 9
C 4 10
D 5

ようにしたい場合はどのようにしたらいいのか教えてください。
マクロではなく関数で処理をしたいと思っています。
よろしくお願いします。

回答No.3

kagakusuki

今仮に、御質問文中の例で1が入力されているセルをSheet1のA1セルだとします。
　又、E列の文字列は、元データとして最初から入力されているものとします。

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

=IF(OR($E1="",COLUMNS($F:F)>COUNTIF($B:$B,$E1)),"",INDEX($A:$A,SUMPRODUCT(ROW($A$1:$A$10)*($B$1:$B$10=$E1)*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A$10)),$E1)=COLUMNS($F:F)))))

　次に、F1セルをコピーして、結果を表示させるセル範囲（御質問文中の例ではF1:I4）に貼り付けて下さい。

　以上です。

　尚、行数が1～10行目という様には決まっておらず、行数が次第に増えて行く様な場合に関しては、次の様な数式とします。

【A列のデータが数値の場合】
=IF(OR($E1="",COLUMNS($F:F)>COUNTIF($B:$B,$E1)),"",INDEX($A:$A,SUMPRODUCT(ROW(OFFSET($A$1,,,MACH(99^9,$A:$A)))*(OFFSET($B$1,,,MACH(99^9,$A:$A))=$E1)*(COUNTIF(OFFSET($A$1,,,ROW(OFFSET($A$1,,,MACH(99^9,$A:$A)))),$E1)=COLUMNS($F:F)))))

【A列のデータが数値とは限らない場合で、且つB列のデータが文字列の場合】
=IF(OR($E1="",COLUMNS($F:F)>COUNTIF($B:$B,$E1)),"",INDEX($A:$A,SUMPRODUCT(ROW(OFFSET($A$1,,,MACH("゛",$B:$B,-1)))*(OFFSET($B$1,,,MACH("゛",$B:$B,-1))=$E1)*(COUNTIF(OFFSET($A$1,,,ROW(OFFSET($A$1,,,MACH("゛",$B:$B,-1)))),$E1)=COLUMNS($F:F)))))

　それから、E列のデータが手入力ではなく、B列のデータから関数を使用して、自動的に抽出させる場合には、適当な列（例えばSheet2のA列）を作業列として使用する必要があります。
　まず、Sheet2のA1セルに次の数式を入力して下さい。

=IF(AND(Sheet1!$B1<>"",COUNTIF(Sheet1!$B1:$B1,Sheet1!$B1)=1),ROW(Sheet1!$B1),"")

　次に、Sheet2のA1セルをコピーして、Sheet2のA2以下に貼り付けて下さい。
　次に、Sheet1のE1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT(Sheet2!$A:$A),"",INDEX($B:$B,SMALL(Sheet2!$A:$A,ROWS($1:1))))

　次に、Sheet1のE1セルをコピーして、Sheet1のE2以下に貼り付けて下さい。

　以上です。

回答No.2

aokii

とりあえず、ピボットテーブルを使ってA列とB列の個数を出してみてはいかがでしょう。

回答No.1

mu2011

一例です。
配列数式になっていますので、数式入力完了時にはshift＋ctrl＋enterキーを同時押下して下さい。
以下の数式をF1に設定、縦横にコピーでお試しください。
=IF(COUNTIF($B:$B,$E1)>=COLUMN(A1),INDEX($A:$A,SMALL(IF($B$1:$B$20=$E1,ROW($B$1:$B$20),999),COLUMN(A1))),"")