1番多い数値の店名を表示する関数

質問

エクセルで、下のような数量の実績表を毎週を更新していくのですが、一番多い数量の店名と数量を自動的に表示する関数はないでしょうか？

店名　数量
A　　　5
B　　　3
C　　　1

　よろしくお願いします。

回答No.6

mu2011 · ベストアンサー

no1です。
範囲内での最大値を求めるのは、=MAX(A:F)で、
店名はIF文をネストすれば可能です。
=IF(COUNTIF(B:B,MAX(A:F)),INDEX(A:A,MATCH(MAX(A:F),B:B,0)),IF(COUNTIF(D:D,MAX(A:F)),INDEX(C:C,MATCH(MAX(A:F),D:D,0)),INDEX(E:E,MATCH(MAX(A:F),F:F,0))))

お礼

出来ました～！！
なるほど、COUNTIFを使うのですね。
他にも応用できそうです♪
いつも沢山の回答ありがとうございます。
とても助かりました（＾－＾）

回答No.7

Wendy02

こんばんは。Wendy02です。

>F9はこの画面で、数式をマウス選択して押すのですか？出来なかったのですが、エクセルのシートに数式をコピーして、読んでみました。

すみません。私は、どこで、マウス選択をするか書いていませんでした。

数式バーか、F2 を押して数式を編集モードにしてから、数式のまとまりの範囲を部分部分、範囲を選択してから、[F9] を押すのです。

http://support.microsoft.com/kb/214142/ja
製品[Excel　2000] 用語[配列]
で検索して選んでみました。

後は、いろいろググってみてください。
--------------------------------------

以下は、私が、配列数式を始めて知った場所です。
http://support.microsoft.com/kb/402181/ja
[XL2002] n 行おきの合計を算出する方法

(私は、知らなかった↓)
http://support.microsoft.com/kb/268001/ja
[XL2003] Excel でセル範囲内の一意の要素を数える方法

http://support.microsoft.com/kb/214142/ja
[XL] 条件を複数指定して INDEX および MATCH ワークシート関数を使用する方法

http://support.microsoft.com/kb/213916/en-us
[XL2000]同じ値がある場合の順番付け
Rank Duplicate Values Sequentially in Excel 2000

お礼

こんばんは。

色々なサイトを詳しく教えてくださって、ありがとうございました。

　＞数式バーか、F2 を押して数式を編集モードにしてから、数式のまとまりの範囲を部分部分、範囲を選択してから、[F9] を押すのです。

出来ました（＾－＾）こんな機能があったなんて知らなかったです。

とても勉強になるので、教えてもらったサイトへ行って、理解しようと思います。
沢山の事を教えてくださって、本当にありがとうございました。

回答No.5

Wendy02

こんにちは。#3 のWendy02です。

>どうしてMOD関数や、SUMPRODUCT、ROW関数を使っているのでしょうか？

それぞれの意味はありますが、だいたい、こういうのは、いくつかのパターンが決まっていて、それに当てはめているだけなのです。

問題は、ここに集中されるかと思います。

MOD(SUMPRODUCT(LARGE($B$2:$B$20+ROW($A$2:$A$20)/100,ROW(A1))),1)*100

簡単に中身を見る方法は、

　$B$2:$B$20+ROW($A$2:$A$20)/100

この範囲を、マウス選択して、みてください。そして、F9 を押すと、結果の中身が見えるはずです。数値と一緒に、小数点の値は、行を表しているはずです。
(ただし、データ数は、99 までですね。もし、データ数が越えれば、99
÷500　とか、÷　1000　などとしなければなりません。)

つまり、大きな数を出して、次に、もう一つ行の数を出す、というわけです。これを「重み付け」というそうです。

MOD(数値,1) で、小数点の数を出して、それを100 倍すれば、行番号が出てくるという仕組みです。

こうした配列数式は、そのままですと、二重の計算をしてくれませんので、一旦、SUMPRODUCT で計算し直させているわけです。

理屈は、そういうことですが、ともかく、[数式として認識できる範囲]をマウス・ドラッグで選択して、F9 を押すという作業をすれば、なんとなく見えてきます。こういう式は、できる出来ないは、パターンに当てはめて、後は、出たとこ勝負です。

他の方たちはどうかしりませんが、実は、作る時も、同じことをしています。
後は、他人のマネですね。他人は他人でも、その元を発表したのは、Microsoft サポートで、その後、Microsoft News Group で発展してきたものが、今日の配列数式があるようです。細かな違いはあるけれども、大まかなところは、ある程度覚えると、新しいものがめったに出てこなくなります。それでも、少しずつは、進展しているようです。

掲示板に新しいパターンが書かれると、いつのまにか、別の掲示板でも広まっているようです。私は、後発ですが、それでも、少しはその発展に貢献したつもりです。^^;

ちなみに、私は、配列数式はVBAよりも、難しく感じます。

お礼

こんにちは。詳しく説明してくださってありがとうございました。
配列数式は難しいですね。
自分のレベルが低いので、関数なども色々調べたりしてみました。
理解するにはまだ時間がかかりそうですが、使いこなせるようになれたらと思います。

F9はこの画面で、数式をマウス選択して押すのですか？出来なかったのですが、エクセルのシートに数式をコピーして、読んでみました。

忙しい中に回答してくださって、ありがとうございました。
とても勉強になりました。

回答No.4

maron--5

◆「一番多い数量」が複数店ある場合で、店名表示は上から表示順です
=IF(ROW(A1)>COUNTIF($B$1:$B$10,MAX($B$1:$B$10)),"",INDEX(A:A,SMALL(INDEX(SUBSTITUTE(N($B$1:$B$10=MAX($B$1:$B$10)),0,10^5)*ROW($B$1:$B$10),),ROW(A1))))
★下にコピー

補足

回答ありがとうございます。教えて頂いた数式はとても役立つと思うのですが、私の知識不足で、SUBSTITUTE関数から先が作り方もよく分りませんでした。どのような意味があるのでしょうか？

申し訳ございません。

回答No.3

Wendy02

こんばんは。

同数がある場合。
ただし、店名は、順序が逆になります。

範囲は適当に調整してください。ただし、B:B と列全体を指定するのは出来ません。

=IF(ROW(A1)>COUNTIF(B:B,MAX(B:B)),"",INDEX(A:A,MOD(SUMPRODUCT(LARGE($B$2:$B$20+ROW($A$2:$A$20)/100,ROW(A1))),1)*100,))

=INDEX(範囲,行番号,列番号)　'領域番号は無視してよいです。

補足

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

　教えていただいた関数は同数があった場合、役立ちそうです。しかし、私の知識不足で、MOD関数以下が理解できませんでした。

どうしてMOD関数や、SUMPRODUCT、ROW関数を使っているのでしょうか？

申し訳ございません。

回答No.2

mu2011

No1です。
INDEX関数は、配列，行番号，列番号を選択して下さい。
MATCH関数は、検索値と一致する検索範囲の相対値が得られます。
数式は、次の通りです。
(1)MAX(B:B)で数量列の最大値を取得。
(2)MATCH(MAX(B:B),B:B,0)で最大値で数量列（Ｂ列）上の一致する相対値を取得。
(3)INDEX(A:A,MATCH(MAX(B:B),B:B,0))で店名列の（(2)の行番号,列番号０)のセルを参照。

お礼

回答ありがとうございました。出来ました。
関数の勉強になりました。

　ところで、また質問で申し訳ないのですが、
表が下記の様だったら、上記の関数は使えるでしょうか？

行列　 　 A　　　 B　　　　　C　 　 D　　　　 E　 　F
(1)　　　店名　　数量　　　　店名　　数量　　　　店名　　数量
(2)　　　 ア　　　　5　　　　 エ　　　7　 　　　　キ　　　8
(3)　　　 イ　　　　3　　　 　オ　　　6　　 　　　ク　　　7
(4)　　　 ウ　　　　1　　 　　カ　　　3　　　 　　ケ　　　5

お時間があれば、よろしくお願いします。

回答No.1

mu2011

店名をＡ列、数量をＢ列として、次の方法は如何でしょうか。
店名は=INDEX(A:A,MATCH(MAX(B:B),B:B,0))
数量は=MAX(B:B)

補足

早速、回答ありがとうございました。教えてもらった関数で出来ると思います。
　ところで、INDEX関数を使ったことがないので、教えていただけないでしょうか。
　関数の挿入ボタンでINDEX関数を選ぶと、引数を配列か範囲を選ぶのですが、どちらがいいでしょうか？
　また、範囲、行番号、列番号、領域番号などは何を入れたらいいでしょうか？
　MATCH関数とは、どういうものなのでしょうか？

　すみません、沢山質問してしまって。よろしくお願いします。