頻度の高い行を抽出

質問

エクセルの関数について質問させてください。

例えば以下の表があるとします。

名前　　　更新日
AA 2014/2/10
AA 2014/2/10
AA 2014/2/26
AA 2013/12/10
BB 2014/2/1
BB 2014/1/17
BB 2014/2/1

以下の抽出結果のように、各名前ごとに更新日の頻度が多いものだけを抽出するための
関数などありましたら教えてください。
よろしくお願いいたします。

<抽出結果>

名前 更新日
AA 2014/2/10
BB 2014/2/1

回答No.6

tom04 · ベストアンサー

こんばんは！
一例です。

↓の画像で左側がSheet1で右側のSheet2に表示するとします。
Sheet1に作業用の列を2列設けます。
尚、Excel2007以降のバージョンとします。
（COUNTIFS関数や、IFERROR関数を使用しているため）

作業列1のD2セルに
=IF(COUNTIF(A$2:A2,A2)=1,ROW(),"")

作業列2のE2セルに
=IF(COUNTBLANK(A2:B2),"",COUNTIFS(A:A,A2,B:B,B2)+1/ROW())

という数式を入れ、両列ともオートフィルでずぃ～～～！っと下へコピーしておきます。

そしてSheet2のA2セルに
=IFERROR(INDEX(Sheet1!A:A,SMALL(Sheet1!D:D,ROW(A1))),"")

B2セルに
=IFERROR(INDEX(Sheet1!B$1:B$1000,MATCH(MAX(IF(Sheet1!A$1:A$1000=A2,Sheet1!E$1:E$1000)),Sheet1!E$1:E$1000,0)),"")

このB2セルのみが配列数式になってしまいますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　B2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。

最後にA2・B2セルを範囲指定　→　B2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

※　作業列が目障りであれば遠く離れた列に設けるか、非表示にしてください。
※　同一名で更新日の頻度が最も高いものが複数ある場合
上位の行のデータが表示されます。m(_ _)m

お礼

ありがとうございました。
助かりました。わかりやすかったです。

回答No.9

MackyNo1

＞たとえで項目を2つだけ書きましたが、実をいうと10項目ぐらいあり、また名前も1000人分あります。
＞抽出結果を別シートにしたいと考えています。

この条件なら計算負荷が比較的少ない、以下のような数式で対応するのが簡便かもしれません。

元データがSheet1のA2セル以下にデータがあり（項目名が1行目）、別シートのA2セルに以下の式を入力し、下方向に10個オートフィルコピーします。

=INDEX(Sheet1!A:A,SMALL(INDEX((MATCH(Sheet1!A$2:A$1000&"",Sheet1!A$2:A$1000&"",)<>ROW(A$2:A$1000)-1)*100+ROW(A$2:A$1000),),ROW(A1)))&""

B2セルには以下の式を入力し、同じく10個程度オートフィルコピーします(セルの書式は日付）。

=MODE((Sheet1!$A$2:$A$1000<>A2)*ROW($A$2:$A$1000)/10000+Sheet1!$B$2:$B$1000)

お礼

ありがとうございました。
助かりました。

回答No.8

kagakusuki

回答No.7です。
　先程の回答文において、「配列変数やSUMPRODUCT関数」と記述している箇所がありますが、これは「配列数式やSUMPRODUCT関数」の間違いです。

お礼

ありがとうございました。
勉強になりました。

回答No.7

kagakusuki

頻度が最も多い日付が、同数1位で複数存在するという事もあり得ると思われますが、

>たとえで項目を2つだけ書きましたが、実をいうと10項目ぐらいあり、

とされておられるという事は、同じ名前の中で、頻度が同数1位の日付が複数ある場合であっても、表示する事が出来る行は、1つの名前につき1行だけという事になりますから、頻度が同数1位の日付の内、上から順番に検索して行った際に、最初に現れる行のデータのみを抽出する(該当する行が複数あっても、抽出するデータは1行分のみ)という考え方で宜しいでしょうか？
　もしその考え方で良いとした場合で、尚且つ、Excel2007以降のバージョンのExcelを使用する事が出来る場合には、作業列を1行使用するだけで、配列変数やSUMPRODUCT関数の様な処理が重くなりやすい方法などは使わずに、御質問の目的を達成する事が出来ます。

　今仮に、元データの表の中で「名前」と入力されているセルが、Sheet1のA1セルであり、抽出結果の表の中で「名前」と入力されているセルが、Sheet2のA1セルであるものとします。
　又、Sheet3のA列を作業列として使用するものとします。

　まず、Sheet3のA2セルに次の関数を入力して下さい。

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$B:$B,ROW())=""),"",IF(COUNTIFS(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROW()-1),INDEX(Sheet1!$A:$A,ROW()),Sheet1!$B$1:INDEX(Sheet1!$B:$B,ROW()-1),INDEX(Sheet1!$B:$B,ROW())),"",MATCH(INDEX(Sheet1!$A:$A,ROW()),Sheet1!$A:$A,0)*10000000+COUNTIFS(Sheet1!$A:$A,INDEX(Sheet1!$A:$A,ROW()),Sheet1!$B:$B,INDEX(Sheet1!$B:$B,ROW()))))

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。

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

=Sheet1!$A$1

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

=IF($A1="","",IF(COUNTIF(Sheet3!$A:$A,">"&(MATCH($A1,Sheet1!$A:$A,0)+1)*10000000)*COUNTIF(Sheet1!$A$1:$B$1,A$1),IF(INDEX(Sheet1!$A:$B,MATCH(SMALL(Sheet3!$A:$A,COUNTIF(Sheet3!$A:$A,"<"&CEILING(LARGE(Sheet3!$A:$A,COUNTIF(Sheet3!$A:$A,">"&(MATCH($A1,Sheet1!$A:$A,0)+1)*10000000)),10000000))),Sheet3!$A:$A,0),MATCH(A$1,Sheet1!$A$1:$B$1,0))="","",INDEX(Sheet1!$A:$B,MATCH(SMALL(Sheet3!$A:$A,COUNTIF(Sheet3!$A:$A,"<"&CEILING(LARGE(Sheet3!$A:$A,COUNTIF(Sheet3!$A:$A,">"&(MATCH($A1,Sheet1!$A:$A,0)+1)*10000000)),10000000))),Sheet3!$A:$A,0),MATCH(A$1,Sheet1!$A$1:$B$1,0))),""))

　次に、Sheet2のA2セルをコピーして、Sheet2のB2セルに貼り付けて下さい。
　次に、Sheet2のB2セルの書式設定を[日付]に設定して下さい。
　次に、Sheet2のA2～B2の範囲をコピーして、同じ列範囲の3行目以下に貼り付けて下さい。

　以上です。

回答No.5

esupuresso

頻度の多いものを導く関数に「MEDIAN」というものがあります。

"AA",”BB"をそれぞれ直接入力する場合は
　=MEDIAN(IF($A$2:$A$5="AA",$B$2:$B$5))
　=MEDIAN(IF($A$6:$A$8="BB",$B$6:$B$8))　という式になります。

セル参照をさせて式を作る場合は（添付画像参照）
セルF2　=MEDIAN(IF($A$2:$A$8=E2,$B$2:$B$8))の式を作り、最後に「Ctrl」+「Shift」+「Enter」で配列数式として完成させます。

尚、表示形式がシリアル値で出力しますので、お好みの日付を選択してください。

補足

早速回答いただきありがとうございます。
説明が不十分でした、すいません。
補足説明させていただきます。
たとえで項目を2つだけ書きましたが、実をいうと10項目ぐらいあり、
また名前も1000人分あります。
抽出結果を別シートにしたいと考えています。
いただいた回答を基にカスタマイズして自分なりにやってみたのですが、
うまくいきませんでした。助けていただけるとうれしいです。

回答No.4

MackyNo1

＞C2にAA, C3にBBが入力していない場合はどうすればいいですか？

重複のない名前を取得するなら、C2セルに以下の数式を入力して下方向にオートフィルコピーしてください。

=INDEX(A:A,SMALL(INDEX((MATCH(A$2:A$100&"",A$2:A$100&"",)<>ROW(A$2:A$100)-1)*100+ROW(A$2:A$100),),ROW(A1)))&""

補足

早速回答いただきありがとうございます。
説明が不十分でした、すいません。
補足説明させていただきます。
たとえで項目を2つだけ書きましたが、実をいうと10項目ぐらいあり、
また名前も1000人分あります。
抽出結果を別シートにしたいと考えています。
いただいた回答を基にカスタマイズして自分なりにやってみたのですが、
うまくいきませんでした。助けていただけるとうれしいです。

回答No.3

MackyNo1

以下のような配列数式で表示することができますが、表示データ数が多くなると（データ範囲が大きくなる場合も）、シートの動きが重くなるのであまりお勧めできません。

D2セル以下に重複のない名前が入力されているなら（この部分も自動的に表示させることはできますがお勧めできません）、E2セルに以下の数式を入力しCtrl+Shift+Enterで確定してください。

=INDEX(B:B,MAX((COUNTIFS($A$2:$A$100,D2,$B$2:$B$100,$B$2:$B$100)=MAX(COUNTIFS($A$2:$A$100,D2,$B$2:$B$100,$B$2:$B$100)))*ROW($B$2:$B$100)))

＃なお、上記の回答はExcel2007以降のバージョンを使用している場合の回答です。
Officeソフトはバージョンによって使用できる機能や操作方法が大きく異なるので、質問の際には必ずバージョンを明記するようにしましょう。

回答No.2

kybo

=MODE(IF($A$1:$A$100="AA",$B$1:$B$100,""))

=MODE(IF($A$1:$A$100="BB",$B$1:$B$100,""))

とすればいいです。
配列数式ですので、CtrlキーとShiftキーを押しながらEnterするのを忘れないようにしてください。

回答No.1

kybo

C2にAA、Ｃ3にBBと入力してあるとして、
D2のセルに以下のように入力、Ctrl+Shift+Enterで確定し、D3にコピー。

=MODE(IF($A$1:$A$100=C2,$B$1:$B$100,""))

補足

早速の回答ありがとうございます。
C2にAA, C3にBBが入力していない場合はどうすればいいですか？