Excel：非連続の複数セル値の上位5件

質問

Excel2003を使っています。

ひとつの行の中で、連続しない複数のセルに入力された値の多いもの上位5件について、セルの色を自動的に変えることは可能でしょうか？

連続するセルの場合は、
=RANK(自分のセル,対象範囲)<=5
といった感じの条件付き書式でうまくいったのですが、連続していないセルでは同様の方法ではできませんでした。

上位5件を抽出する元となるセルはほぼ1つおきに並んでいて、50コくらいあります。

ご存知の方いらっしゃいましたらお教え下さいm(__)m
今はセルを目視して値をメモしながら作っており、時間もかかりますし見落としてしまうのが不安です…

回答No.4

_Kyle · ベストアンサー

●「個数」と「金額」が必ず交互に並んでいる場合

　・偶数行が「個数」の場合
　　=IF(MOD(ROW(【自セル】),2)=0,LARGE(INDEX((MOD(ROW(【対象範囲】),2)=0)*【対象範囲】,),5)<=【自セル】)

　・基数行が「個数」の場合
　　=IF(MOD(ROW(【自セル】),2)=1,LARGE(INDEX((MOD(ROW(【対象範囲】),2)=1)*【対象範囲】,),5)<=【自セル】)

　※対象範囲が列(縦方向）ではなく行（横方向）の場合は、
　　ROW(～)となっている部分(2ヶ所)をCOLUMN(～)とします。

　Ex.参考画像左の場合
　　=IF(MOD(ROW(B2),2)=0,LARGE(INDEX((MOD(ROW($B$2:$B$21),2)=0)*$B$2:$B$21,),5)<=B2)
------------------------------------------------------------
●その行(列)が「個数」であることが判る項目列(行)がある場合

　　=IF(【自行項目列のセル】="個数",LARGE(INDEX((【項目列】="個数")*【対象範囲】,),5)<=【自セル】)

　Ex.参考画像右の場合
　　=IF(D2="個数",LARGE(INDEX(($D$2:$D$21="個数")*$E$2:$E$21,),5)<=E2)
------------------------------------------------------------
ご参考まで。

お礼

御礼が遅くなり申し訳ございません。画像までご丁寧に本当にありがとうございます!

「セルの数値が数量なのか金額なのかを判別できるか」の意味がおかげさまで理解でき、項目表示を追加して、ご教示の条件式を入れてみたら、見事に数量の上位5件のセル色を変えることに成功しました。

この機会にINDEX関数も学ぶことができ心から感謝しております!!!!!
お忙しいところありがとうございましたm(__)m

回答No.3

Higirini

こんな事でもよいでしょうか
最初にどこか別の列に 1から50?連番号をつけておいて
次に一つ置きなら、また別の列に0,1,0,1…と50?までふって
　該当セルがバラバラならば
そのセルをCtrlキーを押したまま選択して
　同じ数字または記号を入れておいて
別の列で=IF(B1=0,A1,””)などで0または1を抽出して
別の列で抽出した列をRANK付を出して
数値、関数など入っているセルをすべて選択して
RANKのところで昇順でならべかえて
後は条件付き書式で色付けをして
最後に連番を振ったところで昇順並べ替え
で終わりかな～
RANKの列など見られたくなければ　非表示にでも
難しい関数を使うより 間違えたとき分かりやすいかと
シンプル・オブ・ベスト

お礼

御礼が遅くなり申し訳ございません。
確かにSimple is Bestですが、知識不足なもので文字のご説明だけでは少々わかりづらく、せっかくのご意見を活用することができませんでした…申し訳ございませんm(__)m

回答No.2

noname#99913

＞「区別する方法」と仰るのがどういう意味なのかわからないのですが

セルにある数字が売上個数なのか金額なのかは、エクセルにはわかりません。それを判断する材料があるのかどうか、ということです。

たとえば、項目名など、ほかの情報を一切消して、数字だけにした状態で、記憶を抜きにして、それが売上個数なのか金額なのかがわかりますか？　わかるとしたら、それが判断材料です。それがあるのかどうか、ということです。

たとえば、必ず1行おきに売上個数が並んでいるのなら、それを別の列にセル参照で書き出し、その数値を元に条件付き書式で判断すればいいでしょう。まあ、50個ぐらいなら、1行おきに並んでいなくても、手作業でやっても大した手間ではありませんが。

お礼

わかりました。
そんなに作り込まれた表ではないので手作業で管理していくことにします。お忙しいところ有難うございました。

回答No.1

noname#99913

連続していないというのは、途中に空白セルがあるということですか？　それとも、別系列の数値があるのですか？

空白セルならRANK関数でうまくいくはずです。別系列の数値なら、それを区別する方法がない限り不可能です。

補足

対象としたくないセルにも数値が入っています。
具体的には、商品の売上個数とその金額が交互に並んでいる感じで、売上個数だけで5位までランク付けしたいのです。
「区別する方法」と仰るのがどういう意味なのかわからないのですが、やはり無理そうですね…