[関数]同じ品番で個数がそれぞれ違う時に使ってみたINDEX関数

こんにちは
同じ品番で個数がそれぞれ違う時に下記書式を使用してみて実現できましたが、よく中身がわからないので、分析していきたいと思いますが、理解できない部分がありました。
=INDEX(B:B,SMALL(IF($I$2:$I$1999=M2,ROW($2:$1999)),COUNTIF(M$2:M2,M2)))

真ん中からバラしてみると、
IF($I$2:$I$1999=M2,ROW($2:$1999))
もしI列の中にM2と同じデータが存在していたら、その行のナンバーを返すいうことでしょうか？
結果は2になります。

COUNTIF(M$2:M2,M2))
多分COUNTIFで同じデータの何個目を指定する。

=SMALL(IF($I$2:$I$1999=M4,ROW($2:$1999)),COUNTIF(M$2:M4,M4))
結果は4ですが、
ここは理解できない部分です。データの中から指定した順位に小さい値を返すと思いますが、
なぜ４でしょうか？

INDEX(B:B,SMALL(IF($I$2:$I$1999=M4,ROW($2:$1999)),COUNTIF(M$2:M4,M4)))
リンゴは二つがあって、
SMALL(IF($I$2:$I$1999=M4,ROW($2:$1999)),COUNTIF(M$2:M4,M4))=4,
=IF($I$2:$I$1999=M4,ROW($2:$1999))=2,
まさかリンゴの2個目のとなりに、個数の列の４番目の144を返すということでしょうか？

ご教示お願い致します。

質問者からの補足コメント

• 鮮明な画像をアップロードします。

  
    補足日時：2020/02/05 15:38

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

  
    補足日時：2020/02/05 15:38

No.3 ベストアンサー 回答者： yoreyore 回答日時： 2020/02/06 22:27

No.2 回答者： fujillin さんの回答の通りで良いと思います。

=INDEX(B:B,SMALL(IF($I$2:$I$1999=M2,ROW($2:$1999)),COUNTIF(M$2:M2,M2)))　を入力するときに、Ctrl＋shift+Enterで入力したと思います。
配列数式の入力です。
右のWEBを参考にして下さい。　http://office-qa.com/Excel/ex256.htm

COUNTIF(M$2:M2,M2))
＞　多分COUNTIFで同じデータの何個目を指定する。
⇒　それで正しいです。　この命令がある行までのM列でカウントします。

=SMALL(IF($I$2:$I$1999=M4,ROW($2:$1999)),COUNTIF(M$2:M4,M4))
＞　結果は4ですが、
⇒　違います。　No.2 回答者 fujillin さんが書いている通り、これを配列数式として入力した場合、画面には出てこないですが、[IF($I$2:$I$1999=M4,ROW($2:$1999))]の部分で、$I$2:$I$1999全行について｛[＝M列の該当行位置の値]であればROW($2:$1999)すなわち行番号、[＝M列の該当行の値]でなければFalse｝の配列が作られて、その配列の中で、[COUNTIF(M$2:M4,M4)](この4は、配列数式の場合[＝M列の該当行位置])のカウントで配列の中の位置を指定しています。

この回答へのお礼

ご回答ありがとうございます。
なるほどです。
SMALLというのは行の小さい順ですね。
添付して頂いたリンクの内容を読み、参考になりました。
ご指導ありがとうございます。

お礼日時：2020/02/10 10:06

No.5 回答者： mike_g 回答日時： 2020/02/07 11:03

[No.4]訂正、

文中の「叩き付け」は「ダブルクリックし」に訂正しておきます。(*^_^*)

No.4 回答者： mike_g 回答日時： 2020/02/07 10:59

[No1お礼]へのコメント、

遅れ馳せ乍ら、呈示された式
=INDEX(B:B,SMALL(IF($I$2:$I$1999=M2,ROW($2:$1999)),COUNTIF(M$2:M2,M2)))
をセル O2 に入力直後に Ctrl+Shift+Enterをパシーツと叩き付けた後で、同セルのフイルハンドルを「エイヤツ!」と叩き付けたら、難なく鮮明な画像どほりになりました。
他の方の回答で、既に目出度く解決されましたでせうか？

No.2 回答者： fujillin 回答日時： 2020/02/05 10:24

こんにちは

図も説明もよくわからないので無いものとして、式だけをヒントにして解釈してみました。

＞結果は2になります。
配列計算をさせているので、結果は配列となります。
I2:I999でM2と同じ値の行番号の配列を返します。異なる行に関しては（式中に記載がないので）FALSEが返されます。
この部分の結果は、{2,FALSE,4,FALSE,FALSE,7,FALSE,FALSE,・・・}のような感じになるはずです。
（上の例では　2、4、7…行が該当行ということになる）

この配列に対して、SMALL関数で
＞COUNTIF(M$2:M2,M2)
番目の（FALSEではない）番号を求めていますが、ご提示の式の場合I列とも異なるM列から求めているので、どのような意味の順番をピックアップしているのかは式からだけではわかりません。
COUNTIF(～～ではM列で自分の行までの同じ値の数が出されるので、内容的に異なる値が並んでいる場合に結果の数はいろいろに飛ぶので、「それぞれのグループ内の出現順番」とでも言うべき内容の値になります。

全体的には、このようにして求めた行番号を利用して、Ｂ列のその行番号の値を求めています。
この方法は、「全体から条件に該当するものをピックアップして、詰めて表示する（抽出）」の際に良く用いられる方法に似ていますが、抽出の場合には順序の部分を1から連番になるようにするのが常道です。
ご提示の式はそうなってはおらず、さらにI列とM列の関連が不明なので実際のところの意味がイマイチわかりません。
（もともと、どのような意味の計算をしようとしているのかも全く不明ですので…）


＞よく中身がわからないので、分析していきたいと思いますが～～
配列計算であるということに思いが至っていらっしゃらないのではないかと想像します。
言葉での説明でもよくわからないと思いますが、現状の範囲（2～999行目）を小さくして（数行程度に）、「数式」-「数式の検証」で辿ってみると、エクセルがどのような手順で計算しているかを見ることができます。
（行数が多いままやると、とても見られたものではなくなりますのでご注意）
「数式の検証」の内容が理解できるようになれば、式の中身も理解できるようになると思います。


※ 多少なりともご参考にでもなれば・・・

この回答へのお礼

ご回答ありがとうございます。
参考になりました。
分かり易い画像をアップロードしました。
ご迷惑をおかけし申し訳ありません。

お礼日時：2020/02/05 15:39

No.1 回答者： mike_g 回答日時： 2020/02/04 21:19

添付図、不鮮明！、判読不可！

この回答へのお礼

ご回答ありがとうございます。
分かり易い画像をアップロードしました。
ご迷惑をおかけし申し訳ありません。

お礼日時：2020/02/05 15:39