Excelセル内の条件付最大値抽出方法で困っています

質問

Excel2003を使用しています。
800件ほどのデータで、縦一列に、カテゴリをあらわす2桁もしくは3桁のアルファベットと数字の組み合わせのデータが入っています。
例）
kim2
kim3
kim4
kim343
hon209
hon210
coi4
coi5
sys92
coi9
coi59
ur2004
ur2005
これについて、カテゴリ（kim、honなど）ごとの最大値を求めようと思っています。
カテゴリごとにセルを一列足して、左の文字を抜いた値をセルに入れ、その列ごとの最大値を求めるという方法はわかるのですが、今後カテゴリが増えていくときのために列を増やさなくても計算できる方法を探しています。
表と離れたところに、カテゴリの値を入れたセルを置き、横のセルに最大値が入るイメージで作成しています。
ご教示いただけると助かります。

回答No.1

mt2008 · ベストアンサー

カテゴリ毎に列を増やす必要は無いと思います。
1列だけ追加して、カテゴリと数値にわけてはいかがですか

kim　2
kim　3
hon　209
hon　210

A列がカテゴリ、B列が数値の場合、以下の様な式でカテゴリ毎の最大値が求められます。
=SUMPRODUCT(MAX((A1:A4="kim")*B1:B4))

お礼

mt2008さん、ありがとうございました。
テストしてみたところ問題なさそうですので、
カテゴリと数値とに分ける方向で表を修正することにしました。
詰まったときは既存のフォーマットを疑ってみることも必要ですね！
ありがとうございました。

回答No.2

mu2011

一例です。
仮にA1:A13をデータ範囲、カテゴリ名をB1、C1に次の数式を設定、下方向にコピーで如何でしょうか。
=MAX(IF(ISNUMBER(FIND(B1,$A$1:$A$13)),SUBSTITUTE($A$1:$A$13,B1,"")*1))
尚、入力完了時にshift＋ctrl＋enterキーを同時押下して下さい。

お礼

mu2011さん、ありがとうございました。
参考にさせていただきます。