エクセルの学校：エクセル質問ボード[[20260227134241]]

エクセル	の学校
8.一覧	9.HOME
1.Top	2.Last

[[20260227134241]]

[ 初めての方へ | 一覧(最新更新順) |

| | 全文検索 | 過去ログ | エクセルの学校HOME ]

『リストの選択名に沿った上位・下位3つを表示させる方法』（ミルミル）

よろしくお願いいたします。複数の店舗において、それぞれの店舗の商品分類別に前年比が入ったシートが下記のようにございます。

　　 A 　　 B　　 C 　　 D 　 E 　　 F 　 G　　 H　　I　　 J 　　 K 　

1　　　　分類1 分類2 分類3 分類4 分類5 分類6 分類7 分類8 分類9 分類10 2　 A店　102　 99.5 　85.5　120.1 65.3 　78.5　95.4 93.8 　89.8 　87.5 3　 B店 4　 C店 5　 D店 6　 E店 7　 F店 8　 G店 9 　H店 10　I店

上記のように列に店舗名があり、行に各分類売上前年比があります。この中から前年比の悪いほうから3分類、良いほうから3分類取り出して表示させたいと考えております。店舗名のリストを作成し、そのリストから店舗名を一つ選べばその店舗名の下位3分類、上位3分類が表示されるようにしたいと考えております。一つの列からリストの店舗名にあった数字を出すのであれば INDEX,MATCH（使用しているバージョンが古いので）を使用して店舗名が替わるごとにその店舗の数字にあったものが表示できるようになるのはわかるのですが、上記のように複数列の数字を比較して下位、上位で表示させる方法はないかと思い、いろいろ考えましたがうまくいかず、こちらにてご教授頂きたく投稿させていただきました。希望としては、分類名が入るセル、前年比が入るセルを横並びで作成したく、例としては、リストからA店を選択すると　　下位1位　　　　　下位2位　　　　　下位3位　　分類5 65.3 　　　分類6 78.5 　　　分類3 85.5 のように表示したいと希望しております。分類名の取得、前年比の取得それぞれに関数が必要になるかと思いますがそのような方法がありましたらご教授頂きたく、よろしくお願いいたします。

＜使用 Excel：Excel2016、使用 OS：Windows11 ＞

 一つの店で複数の分類が同じ前年比になることはあるのだろうか？
 また、前年比の小数点以下は最大何桁になるだろうか？
（ねむねむ） 2026/02/27(金) 15:08:32

 12、13行目に次のように表示するものとして、

  	下位1位		下位2位		下位3位		上位1位		上位2位		上位3位	
 A	分類5	65.3	分類6	78.5	分類3	85.5	分類4	120.1	分類1	102	分類2	99.5

 B12：表示形式を"下位"#"位"として、数値の１を入力、同様にD12、F12、H12、J12、L12も表示形式を変更

 C13：=SMALL(OFFSET($B$2,MATCH($A$13,$A$2:$A$10,0)-1,0,1,10),B12)
 B2を基準にMATCH($A$13,$A$2:$A$10,0)-1で求められる行数分（Aなら0行）シフトした範囲のうち、
 最も小さい値（B12）を表示

 B13：=MATCH(C13,OFFSET($B$2,MATCH($A$13,$A$2:$A$10,0)-1,0,1,10),0)
 C13がどの列にあるかを検索　→　表示形式を"分類"#とする

 一つの店で複数の分類が同じ値になることはない前提です。

（jjj） 2026/02/27(金) 16:45:11

ねむねむ様

ご質問ありがとうございます。前年比が同一になる可能性は低いもののあり得ます。ただし、毎日変動する数値ですので同一の値が出てエラーなど生じた場合も翌日には解消される可能性が高いので許容範囲かと思っております。

小数点以下は第1位まででございます。（ミルミル） 2026/02/27(金) 21:27:02

jjj様

ご回答ありがとうございました。私の思った結果が出ました。

大変恐縮ですが追加でご教授いただけましたら幸いです。あまりにも前年比が低い(想定として前年比50%以下)、あまりにも前年比が高い(想定として前年比250%以上)数値は明らかに異常値で去年何かがあったか、今年何かがあったため売り上げに大きく影響している可能性が大きいため、前年比50%以下及び250%以上は除外したいと考えておりました。そのため元のデータにそのような数値が出た場合は0として IF(OR(40以下,250以上),0,範囲内ならそのままの数値)というような感じでデータの数値を改変しておりました。ただし、当然ながらこのような方法ですと 0が最小値となりますので0を除いて jjj様のC13：=SMALL(OFFSET($B$2,MATCH($A$13,$A$2:$A$10,0)-1,0,1,10),B12)を用いたいのですがどのようにすればよろしいでしょうか？最後にB12の値を利用しているためCOUNTIFで0を除外する方法は取れなさそうに思われ、色々やってみましたが上手くいきませんでした。

元データをそのままに戻して、40から250の間でSMALL関数を使用する方法今のように元データで40から250の間以外は0として0を除外してSMALL関数を使用する方法などあるかと思いますが、ご教授いただけますと幸いです。何卒よろしくお願いいたいます。

（ミルミル） 2026/02/27(金) 21:45:55

AGGREGATE関数を使った式です。式を入力セルは適当に決めてください。

小さい順：=AGGREGATE(15,6,$B$2:$K$2/(($B$2:$K$2>=40)*($B$2:$K$2<=250)),COLUMN(A1))

大きい順：=AGGREGATE(14,6,$B$2:$K$2/(($B$2:$K$2>=40)*($B$2:$K$2<=250)),COLUMN(A1))

両式とも右方向にコピーします。（メジロ） 2026/02/28(土) 08:18:30

 別案です

 13行目の数式はそのまま使い、何番目に小さいかを判定する値を固定値ではなく、
 関数で変化させる案です

 対象となる範囲に、40以下が何個あるかカウントして、その個数分だけプラスする

 B12：=COUNTIF(OFFSET($B$2,MATCH($A$13,$A$2:$A$10,0)-1,0,1,10),"<=40")+1

（jjj） 2026/02/28(土) 09:02:05

他の店も処理するならセルの指定方法を次のように変更してください。

小さい順：=AGGREGATE(15,6,$B2:$K2/(($B2:$K2>=40)*($B2:$K2<=250)),COLUMN(A1))

大きい順：=AGGREGATE(14,6,$B2:$K2/(($B2:$K2>=40)*($B2:$K2<=250)),COLUMN(A1)) （メジロ） 2026/02/28(土) 09:11:53

jjj様

誠に有り難うございました。思い通りのエクセルができました。まだまだOFFSET関数など学ばなけれなならない事が多々あると痛感しました。

本当にありがとうございました。（ミルミル） 2026/02/28(土) 12:38:20

メジロ様

ご回答ありがとうございました。 AGGREGATEは初めて拝見しました。こちらも試させていただき勉強させていただきます。

誠にありがとうございました。（ミルミル） 2026/02/28(土) 12:40:55

[ 一覧(最新更新順) |

]

キーボードヒント：[Home]または[Fn+Home]キーで一番上へ戻ります