こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

投稿日時 - 2010-07-29 23:43:50

QNo.6073940

困ってます

質問者が選んだベストアンサー

#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セルも同様です

投稿日時 - 2010-07-31 06:46:53

ANo.5

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.4

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

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


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

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

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

投稿日時 - 2010-07-30 23:08:38

ANo.3

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

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

投稿日時 - 2010-07-30 17:29:03

補足

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

投稿日時 - 2010-07-30 21:36:37

ANo.2

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

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

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

投稿日時 - 2010-07-30 09:41:52

お礼

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

投稿日時 - 2010-07-30 21:41:21

ANo.1

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

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

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

=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)))

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

=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)))

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

=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)))

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

投稿日時 - 2010-07-30 08:50:40

お礼

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

投稿日時 - 2010-07-30 21:35:32

あなたにオススメの質問