カテゴリごとに日付順位付け

質問

テーブルからデータを検索（抽出）する方法について、わからないことがあるので、皆さんのお知恵を拝借できないでしょうか？

○EXCELで以下のようなデータがあったとします。

A列のIDがtop100、mid200、low300で始まるカテゴリごとに、B列の一番後ろの日付を検索したいのですが、効率的な方法はないでしょうか。

例えば、top100のカテゴリでは「2010/1/15」、mid200のカテゴリでは「2009/11/10」、low300のカテゴリでは「2009/9/3」を選択したいのです。

今のところは、カテゴリごとにMAX関数を設定したり、条件付き書式を設定したりしているのですが、実際のデータは1000行ちかくあるもので非常に時間がかかります。なにか良い方法はないでしょうか。
（カテゴリごとに一番後ろの日付を含む行を別表に抽出出来たらなお助かります）

A列: ファイルID || B列: 日付
1| top100010 || 2009/7/3
2| top100050 || 200912/31
3| top100100 || 2010/1/15
4| mid200010 || 2009/8/15
5| mid200050 || 2009/11/10
6| mid200100 || 2009/9/20
7| low300010 || 2009/7/30
8| low300050 || 2009/8/25
9| low300100 || 2009/9/3

回答No.4

cistronezk

カテゴリの文字数が固定でしょうか？
もし、そうなら以下のようにしてカテゴリを拾い出せます。

たとえばカテゴリ文字列が頭６桁なら、
=IF(LEFT(A2,6)=LEFT(A3,6),"",LEFT(A2,6))
という式をC列のデータのある行まで入力します。

そして、C列をコピーしD列に値貼り付けします。
D列を選択してCtrl+Gでジャンプ画面を表示し、「セル選択」で「空白セル」を選択してOKします。
空白セルが選択されている状態で「編集」ー「削除」で「上方向にシフト」を選択してOKすれば、カテゴリがD列に並びます。

カテゴリ一覧をD列に拾い出したら
=INDEX($B$2:$B$1000,MATCH(D1,$C$2:$C$1000,0))
とします。

あるいは、
=INDEX($B$2:$B$1000,MAX((LEFT($A$2:$A$1000,LEN(D1))=D1)*ROW($A$2:$A$1000))-ROW($A$2)+1)
これをCtrl+Shift+Enterで入力します。

回答No.3

KURUMITO

ごめんなさい。解答Ｎｏ1です。
Ｃ2セルへの入力の式が抜けておりました。次の式です。
=IF(LEFT($A2,6)=C$1,MAX(C$1:C1)+1,"")
なお、答えの日付はＧ2セルからＩ2セルになります。
ところでカテゴリを自動判別したいとのことですが、カテゴリはＡ列に入力されているＩＤ文字の何文字目までがカテゴリに入るのでしょう。カテゴリを作られた時の規則がないとできませんね。

回答No.2

cistronezk

１行目が見出しでデータが２行目からあった場合の一例です。
=INDEX($B$2:$B$10,MAX((LEFT($A$2:$A$10,6)="top100")*ROW($A$2:$A$10))-ROW($A$2)+1)
これをCtrl+Shift+Enterで入力します。

作業列を使うなら、C列に
=IF(LEFT(A2,6)=LEFT(A3,6),"",LEFT(A2,6))
としておいて
=INDEX($B$2:$B$10,MATCH("top100",$C$2:$C$10,0))
とするやり方もあります。

補足

ご回答ありがとうございます。

質問を書いた時点では自分でもちゃんと把握してなかったのですが、今回最大の難関はカテゴリを自動判別できるかどうかにあります。

質問の記述ではわかりやすく単純化していますが、実際にはカテゴリが100以上あり、カテゴリ内のID・日付の数も1ケから20ケとさまざまです。

できれば、top100, mid200などカテゴリ名を手書きするのは避けたいです（実際のデータではカテゴリ名もこんなに分かり易くないです）。

＞作業列を使うなら、C列に
＞=IF(LEFT(A2,6)=LEFT(A3,6),"",LEFT(A2,6))

これを基にちょっと変えれば、カテゴリの自動判別の仕組みができそうですね。

回答No.1

KURUMITO

次のようにすることでわかりやすい方法を使いますので計算も早く処理できるのではないでしょうか。
例えばＡおよびＢ列のデータは2行目から下方にあるとします。
Ｃ1セルにはtop100、Ｄ1セルにはmid200、Ｅ1セルにはlow300と入力します。
Ｃ2セルには次の式を入力したのちにＥ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
次にＣ1セルからＥ1セルをコピーしてＧ1セルをアクティブセルにして貼り付けます。これでカテゴリ名がＧ1からＩ1に貼り付けられます。
Ｇ2セルには次の式を入力してＩ2セルまでオートフィルドラッグします。
=INDEX($A:$B,MATCH(MAX(C:C),C:C,0),2)
Ｇ２せるからＩ2セルを選んでセルの書式設定で日付を指定します。
Ｇ2セルからＩ2セルにお求めのデータが表示されることになります。

補足

ご回答ありがとうございます。

＞Ｃ2セルには次の式を入力したのちにＥ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

「次の式」が抜けていると思うんですが、日付を抽出する式がはいるんでしょうね。

今回の作業で最大の難点はカテゴリが100以上もあることなんです（カテゴリ内のID・日付の数も1から20とばらばらです）。カテゴリ名を手入力するなら、カテゴリ毎に条件付き書式を設定したほうが早く綺麗な結果が得られるかもしれません。

カテゴリを自動判別する仕組みはないもんでしょうか？