値のrank付け後に関連するセルの数値を1セル内に

質問

エクセルに詳しくないので教えてください。

１から８の機械があります。

入力された値を元にテスト結果にrank関数でランク付けしました。

どの機械が優秀かひと目でわかるように機械順に番号を表示させたいと思います。

求めるのは、赤いセルの部分です。

入力してある数字は回答例で、この様に表示したいということです。

困っているのでよろしくお願いします。

回答No.9

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

画像は鮮明でないし、同じ質問を繰り返す。
MATCH関数も分からない。
計算部分と印刷部分は分ければよいだけの話なので、セルを結合させる意味が分からない。
過去の質問もリンクさせない。
回答するのをやめようと思いましたが、ほかの回答者ために回答させていただきます

rank 関数で表示された順位の関連セルを並べ
http://okwave.jp/qa/q7821656.html
rank関数で表示されたセルの親セルを順番に整列
http://okwave.jp/qa/q7821795.html

R4セル
=SUMPRODUCT(MOD(SMALL(10*B3:Q3+B2:Q2,{9,10,11,12,13,14,15,16}),10)*10^{7,6,5,4,3,2,1,0})
R11セル も同様
=SUMPRODUCT(MOD(SMALL(10*B10:Q10+B9:Q9,{9,10,11,12,13,14,15,16}),10)*10^{7,6,5,4,3,2,1,0})
結合なしのR15セル
=SUMPRODUCT(MOD(SMALL(10*B14:I14+B13:I13,{1,2,3,4,5,6,7,8}),10)*10^{7,6,5,4,3,2,1,0})
(10*順位+機械番号)を昇順で並べ替え、1の位(機械番号)を取り出し、
左からそれぞれに10の7乗、10の6乗、・・・10の0乗(=1) を掛けて合計する

お礼

ご回答ありがとうございます。

画像は鮮明でないし、同じ質問を繰り返し、MATCH関数もわかりません。

駄目駄目づくしでしたが、今回の回答で出来ました。

何度も教えてくださり、ありがとうございます。

回答No.10

okormazd

目的の結果を得るだけなら、列で並べ替えるだけです。

1つのセルに表示させたいなら、「&」か「Concatenate」でつなぐだけ。
RANKを第1優先、機械番号を第2優先にするだけだが、RANKをとるまでもなく、値で並べてもいい。

「困っているので」って、困るように考えてはいないか。

補足

毎日80個近いデータを処理し、並べ替えの手間がもったいないので関数で出来る質問をしました。

ひと目で比較するためです。

質問をご理解ください。

ありがとうございました。

回答No.8

kagakusuki

ANo.4、5です。
　何度も申し訳御座いません。
　御質問文中には記述がなかったため、「同順位が複数ある場合には、番号の若い順に表示させる」という条件が判ってはおりませんでした。
　従いまして、No.4、5の関数は、目的を果たす事が出来ない場合が御座います。
　そこで、提案ですが、

＞私の提示した画像ではBCセルが結合されているんですが

という事であれば、1つのセルの幅は小さくなる訳ですから、何も結合されて1個になっているセル内に、機械ナンバーを全て詰め込むような無理をしなくとも、横一列に並んでいる8個のセルの中に、機械ナンバーを1個ずつ入れて表示させる方が、関数が単純になって良いと思います。
　又、機械ナンバーの並べ方が、数字が大きい方から順番に並べられていますが、その様な並べ方では「番号が小さい方を優先して表示させる」事は難しくなり、「番号が大きい方を優先して表示させる」事になってしまいますから、是非とも、機械番号の並べ方は、数が小さいものが左、数が大きいものが右に来る様な並べ方に変更して下さい。

　その場合、順位に重複する値が複数存在する場合にも対応する様にするには、以下の様な方法となります。

　まず、テスト結果の順序を表示させるセルを、、テスト1がD6、テスト2がD13というような各テストごとに1セルずつとするのではなく、
　テスト1がD6セル～K6セルの計8個のセル、テスト2がD13セル～K13セルの計8個のセルに、1位～8位までの各々の順位の順番に、機械ナンバーを表示させる様にするものとします。

　まず、D6セルに次の関数を入力して下さい。

=IF(COLUMNS($D:D)>COUNT($B3:$Q3),"",INDEX(2:2,IF(ISNUMBER(1/(SMALL($B3:$Q3,COLUMNS($D:D))<COLUMNS($D:D))),MATCH(SMALL($B3:$Q3,COLUMNS($D:D)),INDEX($12:$12,MATCH(C6,$11:$11,0)+1):$Q3,0)+MATCH(C6,$11:$11,0),MATCH(COLUMNS($D:D),$B3:$Q3,0)+COLUMN($B3)-1)))

　次に、D6セルをコピーして、E6～K6の範囲と、D13～K13の範囲に貼り付けて下さい。

※こちらの関数は、同地・同順位のセルが幾つ存在している場合であっても、テスト結果の順序を正しい順番で表示させる事が出来ます。

お礼

ご回答ありがとうございます。

いろいろ試してみたのですが、今回はシートの変更をせずに関数だけで済んだNo9のcoaltarさんのやり方が良いようでした。

とは言え、考えていただきありがとうございました！

回答No.7

KURUMITO

回答No６です。
B列から右の列は２列毎に結合されてQ列までの表となっているとのことですね。
でしたらB３セルには次の式を入力してQ３セルまでドラッグコピーします。

=RANK(B4,$B4:$P4,1)+B2/100

D６セルには次の式を入力します。

=INDEX(B2:P2,MATCH(SMALL(B3:P3,1),B3:P3,0))&","&INDEX(B2:P2,MATCH(SMALL(B3:P3,2),B3:P3,0))&","&INDEX(B2:P2,MATCH(SMALL(B3:P3,3),B3:P3,0))&","&INDEX(B2:P2,MATCH(SMALL(B3:P3,4),B3:P3,0))&","&INDEX(B2:P2,MATCH(SMALL(B3:P3,5),B3:P3,0))&","&INDEX(B2:P2,MATCH(SMALL(B3:P3,6),B3:P3,0))&","&INDEX(B2:P2,MATCH(SMALL(B3:P3,7),B3:P3,0))&","&INDEX(B2:P2,MATCH(SMALL(B3:P3,8),B3:P3,0))

その他の操作は回答No６ のとおりです。

お礼

ご回答ありがとうございます。

せっかく考えていただいたのですが、今回はひとつの関数を入れれば済んだNo9の方のやり方を採用しました。

一緒に考えていただきありがとうございます。

回答No.6

KURUMITO

ランク付けを行っていますが重複したデータが有る場合には機械Noの小さい数値の機械によるテスト結果が優先されるとのことですね。そんな場合も考慮してランク付けの操作においては重み付けをするなどの操作が必要でしょう。
テスト結果としてランクがB3セルからI3セルに表示されていますが、その際の式を次のように変更します。
B３セルには次の式を入力してI３セルまで横方向にドラッグコピーします。

=RANK(B4,$B4:$I4,1)+B2/100

B３セルからI３セルの数値は小数点付の数値になりますが、それが気になるのでしたらその範囲を選択して右クリックし「セルの書式設定」の「表示形式」で「ユーザー定義」を選択し種類の窓には　０　を入力してOKします。それによって整数の表示になりますね。
D６セルには次の式を入力します。

=INDEX(B2:I2,MATCH(SMALL(B3:I3,1),B3:I3,0))&","&INDEX(B2:I2,MATCH(SMALL(B3:I3,2),B3:I3,0))&","&INDEX(B2:I2,MATCH(SMALL(B3:I3,3),B3:I3,0))&","&INDEX(B2:I2,MATCH(SMALL(B3:I3,4),B3:I3,0))&","&INDEX(B2:I2,MATCH(SMALL(B3:I3,5),B3:I3,0))&","&INDEX(B2:I2,MATCH(SMALL(B3:I3,6),B3:I3,0))&","&INDEX(B2:I2,MATCH(SMALL(B3:I3,7),B3:I3,0))&","&INDEX(B2:I2,MATCH(SMALL(B3:I3,8),B3:I3,0))

回答No.5

kagakusuki

>私の提示した画像ではBCセルが結合されているんですが

　申し訳御座いません、気が付きませんでした。
　その場合、D6セルに次の関数を入力してから、D6セルをコピーしてD13セルに貼り付けて下さい。

=REPLACE(IF(COUNTIF($B3:$Q3,1),","&INDEX($B2:$Q2,MATCH(1,$B3:$Q3,0)),"")&IF(COUNTIF($B3:$Q3,2),","&INDEX($B2:$Q2,MATCH(2,$B3:$Q3,0)),"")&IF(COUNTIF($B3:$Q3,3),","&INDEX($B2:$Q2,MATCH(3,$B3:$Q3,0)),"")&IF(COUNTIF($B3:$Q3,4),","&INDEX($B2:$Q2,MATCH(4,$B3:$Q3,0)),"")&IF(COUNTIF($B3:$Q3,5),","&INDEX($B2:$Q2,MATCH(5,$B3:$Q3,0)),"")&IF(COUNTIF($B3:$Q3,6),","&INDEX($B2:$Q2,MATCH(6,$B3:$Q3,0)),"")&IF(COUNTIF($B3:$Q3,7),","&INDEX($B2:$Q2,MATCH(7,$B3:$Q3,0)),"")&IF(COUNTIF($B3:$Q3,8),","&INDEX($B2:$Q2,MATCH(8,$B3:$Q3,0)),""),1,1,)

>おそらく　$B3:$I3 の部分を書き換えれば良い様に見えますが、この場合どういう風に書き換えたらよいでしょうか？

　B3:$I3 の部分を$B3:$Q3に、B2:$I2 の部分を$B2:$Q2に、それぞれ書き換える必要がありますから、Excelの置換機能を使用して、$I を $Q に置換されると良いと思います。

回答No.4

kagakusuki

その表示のやり方では、機械の台数が10台以上になった場合には、順序が判らなくなりますので、間に「,」を入れて区切っては如何でしょうか？

　その場合のE6セルの関数は以下の様なものとなります。

=REPLACE(IF(COUNTIF($B3:$I3,1),","&INDEX($B2:$I2,MATCH(1,$B3:$I3,0)),"")&IF(COUNTIF($B3:$I3,2),","&INDEX($B2:$I2,MATCH(2,$B3:$I3,0)),"")&IF(COUNTIF($B3:$I3,3),","&INDEX($B2:$I2,MATCH(3,$B3:$I3,0)),"")&IF(COUNTIF($B3:$I3,4),","&INDEX($B2:$I2,MATCH(4,$B3:$I3,0)),"")&IF(COUNTIF($B3:$I3,5),","&INDEX($B2:$I2,MATCH(5,$B3:$I3,0)),"")&IF(COUNTIF($B3:$I3,6),","&INDEX($B2:$I2,MATCH(6,$B3:$I3,0)),"")&IF(COUNTIF($B3:$I3,7),","&INDEX($B2:$I2,MATCH(7,$B3:$I3,0)),"")&IF(COUNTIF($B3:$I3,8),","&INDEX($B2:$I2,MATCH(8,$B3:$I3,0)),""),1,1,)

　E13セルに関数を入力する際には、E6セルをコピーして、E13セルに貼り付けて下さい。

補足

ご回答ありがとうございます。

どうやらこのやり方が一番手早く、応用が利きそうな気がします。

一点だけ、私の提示した画像ではBCセルが結合されているんですがその場合

REPLACE(IF(COUNTIF($B3:$I3,1),","&INDEX($B2:$I2,MATCH(1,$B3:$I3,0)),"")　の部分、
おそらく　$B3:$I3 の部分を書き換えれば良い様に見えますが、この場合どういう風に書き換えたらよいでしょうか？

お手数ですが、教えていただけないでしょうか？

よろしくお願いいたします。

回答No.3

mt2008

回答ではありませんが、ご自分で投稿された質問と回答に対するご自分の補足やお礼をもう一度ご覧になってみては？

・詳しい説明もなく、画像を張り付けて回答者に問題点を察しろと投げている。
・その画像も文字が小さくて見づらい。
・もらった回答で得た結果が望むものでなかった時、どう駄目だったのかを伝えていない。

これでは問題はなかなか解決しないと思います。

補足

ご指摘頂き、ありがとうございます。

誤解しないで下さい。

問題点を察しろとは思っていません、そう思われたのならお詫びいたします。

問題を察しろと言うより、こちらの求める物とは答えが違っていたので、こちらとしても困っている状態です。

説明が悪かったのかなぁと・・・。

画像も小さかったのですね、みずらいかなぁと思ったのですが、そのサイズにしか出来なくてでも困っていたので貼り付けました。

ご回答頂いた事に関してはありがたく思っていますが、問題の解決にはなっていません。

ですから、新しく、しかも教えていただける方にわかりやすく伝わるように質問と画像を新しくしたつもりです。

大変失礼しました。

それと
・もらった回答で得た結果が望むものでなかった時、どう駄目だったのかを伝えていない。
の部分ですが、

以前に投稿したセルの数字や列と使っているものの列や数字が違って、応用が効きそうになかったので実際に近い形で新たに投稿するのが、私も、そして教えて下さっている方にも結果的に近道だと思ったからです。

お手数をおかけしてすいません、そしてありがとうございます。

回答No.1

tom04

こんばんは！

色々お悩みのようですが・・・

一案です。
元データの並びを少し変えてはダメですか？

同順位がある場合が問題ですので、
↓の画像のような感じで列の左側から昇順にします。

そしてB3セルに
=COUNTIF($B4:$I4,"<"&B4)+COUNTIF($B4:B4,B4)
という数式を入れI3セルまでオートフィルでコピー！

B3セル上で右クリック　→　コピー　→　B9セルに貼り付け　→　そのままI9セルまでオートフィルでコピー！
これで同順位があった場合、右側列が上位になります。

この後の操作ですが、VBAになってしまいます。
画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub 表示() 'この行から
Dim i As Long
Dim j As Long
Dim k As Long
Dim tmp As Variant

For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row Step 6
Cells(i + 3, 3) = ""
For j = 1 To 8
tmp = WorksheetFunction.Small(Range(Cells(i, 2), Cells(i, 9)), j)
k = WorksheetFunction.Match(tmp, Rows(i), False)
Cells(i + 3, 3) = Cells(i + 3, 3) & Cells(i - 1, k)
Next j
Next i
End Sub 'この行まで

※　関数でないのでデータ変更があるたびにマクロを実行する必要があります。
※　↓の画像通りの配置でないと滅茶苦茶な表示になってしまいます。
※　画像通りの配置であれば行方向にデータが増えても対応できます。

以上、ご希望の方法でなかったらごめんなさいね。m(_ _)m

お礼

ご回答ありがとうございます。

前回もマクロを教えていただきありがとうございます。

シート自体を作り直す必要がありそうですが、ちょっと試してみます。

ありがとうございます！