Excel 関数やVBAなどを使ってランク仕分けを連動するには？

質問

Excel　関数やVBAなどを使ってランク仕分けを連動するには？
図のようにランクA・B・Cがあったとします。このセルは触りません。
下の元データがあり、ランクが常に変動するものとします。（これは任意で入力：黄色部）
たとえばおにぎり￥１２０がAランクの場合、上のランク表のＡランク部におにぎり・￥120が表示され、おにぎりがBランクに変わった場合、Ｂランク部のリストに入ってくる方法ってできますか？　色々試してみましたがお手上げです。Vlookupなどの関数にて挑戦しましたが全くできませんでした。すいませんが分かる方、ご回答のほどよろしくお願いいたします。

回答No.5

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

#3です
＞表内に収まりきらない場合は、行を挿入するしかない・・・という事でしょうか。
例では各ランク8行分しかありません。
数式を使った場合、上から見つけて9つ以上は表示できないってことです。
金額が上位8個とか、「売上」といった項目が別にあり、その売上順にするのかと思ってのことです。

配列数式が24個程度なら問題ないでしょうが、千個単位なら作業列やマクロを推奨します。

作業列案です。
(計算スピードをもっとずっと上げる方法もあるのですが、
ややわかりにくいので初心者向けです)
A3セル 1 [Ctrl]+下へオートフィル(連番)
E13セル A、 F13セル B、G13セル C
E14セル =IF(E$13=$D14,SUM(E13,1),SUM(E13))
右へ下へオートフィル
(※SUM(E13)は見た目だけなのでE13でもよい)

C14セル
=IF($E$27<$A3,"",INDEX(B$14:B$27,MATCH($A3,$E$14:$E$27,0)))
右へ下へオートフィル(検索のMATCHも作業列化したほうが効率が良い)

G14セル も同様
=IF($F$27<$A3,"",INDEX(B$14:B$27,MATCH($A3,$F$14:$F$27,0)))
K14セルも同様です

回答No.4

MackyNo1

>関数の”ランク”部は、どこを参照するという事なのでしょうか？　Aを参照してAランクとマッチするという意味でしょうか？

普通なら、結合セルのB3セルには「A」のように「ランク」を付けないで入力すると思いますが、例示のレイアウトでは「ランク」付きで入力されていたので、そのまま使える式を提示するためにB列の文字に「ランク」を付けた数式で比較しています。

「ランク」を付けない場合は(この方が一般的ですね）、当然「&"ランク"」の部分は必要ないので以下のような数式になります。

=INDEX($B:$B,SMALL(INDEX(($D$14:$D$30<>B$3)*1000+ROW($D$14:$D$30),),ROW(A1)))&""

このようなご質問では、条件によって最も合理的な数式が変わりますので、「たとえば」ではなく、できる限り実際のデータで例示するようにしてください。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

オートフィルタで抽出したり、フィルタオプションで抽出するのを
マクロの自動記録で記録してみてはいかがでしょうか？

また、表内に収まりきらない場合はどのように処理するのでしょうか？

補足

表内に収まりきらない場合は、行を挿入するしかない・・・という事でしょうか。

回答No.2

MackyNo1

例示の表のレイアウトそのままの場合（Ｂ３セルが結合されていて「Ａランク」と「ランク」付きで入力されている）、以下のような数式を入力し、この2つの数式を右の２組の表にコピーし、下方向にオートフィルします。

Ｃ３セル
=INDEX($B:$B,SMALL(INDEX(($D$14:$D$30&"ランク"<>B$3)*1000+ROW($D$14:$D$30),),ROW(A1)))&""

Ｄ３セル
=IF(C3="","",VLOOKUP(C3,$B:$C,2,0))

お礼

素晴らしい！このような関数方法があるのですね。　１点気になりましたが、関数の”ランク”部は、どこを参照するという事なのでしょうか？　Aを参照してAランクとマッチするという意味でしょうか？　初心な質問ですいません。

回答No.1

KURUMITO

作業列を設けて対応することが最もわかりやすい方法です。
Ｅ14セルには次の式を入力して下方にオートフィルドラッグします。

=IF(D14="","",D14&COUNTIF(D$14:D14,D14))

その後にＣ3セルには次の式を入力してＤ3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTIF($E$14:$E$100,"A"&ROW(A1))=0,"",INDEX($B$14:$C$100,MATCH("A"&ROW(A1),$E$14:$E$100,0),COLUMN(A1)))

Ｇ3セルには次の式を入力してＨ3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTIF($E$14:$E$100,"B"&ROW(A1))=0,"",INDEX($B$14:$C$100,MATCH("B"&ROW(A1),$E$14:$E$100,0),COLUMN(A1)))

Ｋ3セルには次の式を入力してＬ3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTIF($E$14:$E$100,"C"&ROW(A1))=0,"",INDEX($B$14:$C$100,MATCH("C"&ROW(A1),$E$14:$E$100,0),COLUMN(A1)))

これでランクの仕分けが変わっても即座に連動して上の表も変化します。

お礼

なるほど、ありがとうございます。こんな方法があるのですね。E14から下のセルを参照しないとリストに反映してこないということですか。参考になりました！