[[20191125111541]]　『INDEX関数　重複の場合の対処法』（てん）　ページの最後に飛ぶ

『INDEX関数　重複の場合の対処法』（てん）

 INDEX関数を使って、リストからの抜き取りをしています。

 =INDEX($A$4:$A$300,MATCH(D7,$E$4:$E$300,0)

 D列には、LARGE関数で求めた数字が入っているのですが
 同じ値が何件か存在します。

 D列に同じ値が入っていても、リストを検索して表示することは
 可能でしょうか？

 宜しくお願いします。

＜使用 Excel：Excel2010、使用 OS：Windows10 ＞

 作業列を使う方法。
 たとえばF列を作業列として使うとして。
 F4セルに
 =E4-COUNTIF(E$4:E4,E4)/100
 と入力して下へフィルコピー。
 そしてMATCH関数のE列の個所をF列とする。
（ねむねむ） 2019/11/25(月) 11:48

 私の式はE列が整数なのを前提にしている。
 E列が小数の場合には使えない。
（ねむねむ） 2019/11/25(月) 11:56

 ＞D列に同じ値が入っていても、リストを検索して表示することは
 ＞可能でしょうか？

 どのような表示を期待されているのでしょうか？

 例えば一つ見つかって、件数を表示させたいとかも考えられますよね

 でしたら　隣のセルにCOUNTIF関数で　OKだと思います。
　
（渡辺ひかる） 2019/11/25(月) 12:03

 渡辺様
 ありがとうございます。

 D列に上位10までの数値(整数)が抽出されています。
 数値の根拠は件数です。

 例えば　2件としてヒットしたものが
 3個あったとして、
 それぞれの項目がなんであったかを表示させたいのです。

（てん） 2019/11/25(月) 12:08

 こんな感じかなと思って　サンプルを作成しました

 レイアウトは以下です

     |[A]   |[B] |[C]|[D]  |[E]   
 [1] |項目  |件数|   |TOP10|項目  
 [2] |AAAA1 |  76|   |   89|AAAA8 
 [3] |AAAA2 |  26|   |   87|AAAA11
 [4] |AAAA3 |  55|   |   87|AAAA16
 [5] |AAAA4 |  66|   |   77|AAAA14
 [6] |AAAA5 |  10|   |   76|AAAA1 
 [7] |AAAA6 |  26|   |   74|AAAA10
 [8] |AAAA7 |  17|   |   69|AAAA17
 [9] |AAAA8 |  89|   |   69|AAAA19
 [10]|AAAA9 |  47|   |   67|AAAA15
 [11]|AAAA10|  74|   |   66|AAAA4 
 [12]|AAAA11|  87|   |     |      
 [13]|AAAA12|   6|   |     |      
 [14]|AAAA13|  46|   |     |      
 [15]|AAAA14|  77|   |     |      
 [16]|AAAA15|  67|   |     |      
 [17]|AAAA16|  87|   |     |      
 [18]|AAAA17|  69|   |     |      
 [19]|AAAA18|  34|   |     |      
 [20]|AAAA19|  69|   |     |

 D2セルに　=LARGE($B$2:$B$20,ROW(A1))　としてD11までコピー
 E2セルに　=INDEX($A$2:$A$20,AGGREGATE(15,6,ROW($B$2:$B$20)/($B$2:$B$20=D2),COUNTIF($D$2:D2,D2))-1)としてE11　までコピー

（渡辺ひかる） 2019/11/25(月) 16:34

渡辺様

ありがとうございます。

E列に項目が表示されるようになりました。

ただ‥
思い通りのセルを返していませんので
どこか、設定を誤っているのだと思います。

関数の意味も考えながら、検証してみます。

（てん） 2019/11/25(月) 17:52

２行下の項目を返しているようです。

例
本来の項目　　　A3
返している項目　A5

のような感じです。

宜しくお願いします

（てん） 2019/11/25(月) 18:00

渡辺様

大変失礼しました。

ROW関数を使っているにも関わらず、
4行目から開始していました。

２行目からに設定すると、正しい項目を得ることができました。

ありがとうございました。
（てん） 2019/11/25(月) 18:08

何度も申し訳ありません。

やはり
$A$2:$A$20を
$A$4:$A$20にするには
どこを変化させてよいのか、いま一つ理解が出来ていませんでした。

A3セルに集計の数値が入っているため、
そのセルも参照されてしまいます。

基礎的なことで申し訳ありません。
再度、ご教示いただけますと、助かります。
（てん） 2019/11/26(火) 07:39

 横から失礼します。

 	A	B	C	D	E
 1					
 2					
 3	項目	件数			
 4	AAAA1	76			
 5	AAAA2	26			
 6	AAAA3	55		TOP10	項目
 7	AAAA4	66			
 8	AAAA5	10			
 9	AAAA6	26			
 10	AAAA7	17			
 11	AAAA8	89			
 12	AAAA9	47			
 13	AAAA10	74			
 14	AAAA11	87			
 15	AAAA12	6			
 16	AAAA13	46			
 17	AAAA14	77			
 18	AAAA15	67			
 19	AAAA16	87			
 20	AAAA17	69			
 21	AAAA18	34			
 22	AAAA19	69

 D7セル =LARGE(B$4:B$22,ROW(A1))
 E7セル =INDEX(A$4:A$22,AGGREGATE(15,6,ROW(B$2:B$20)/(B$4:B$22=D7),COUNTIF(D$7:D7,D7))-1)

 それぞれを16行目までコピー
 ということでしょうか？

 あとねむねむさんの回答をスルーしているのはなぜですか？
（bi） 2019/11/26(火) 09:33

 =INDEX($A$2:$A$20,AGGREGATE(15,6,ROW($B$2:$B$20)/($B$2:$B$20=D2),COUNTIF($D$2:D2,D2))-1)

 ですが

 INDEX(配列、行番号、[列番号])

 ですから

 配列は　$A$2:$A$20　です、

 行番号は AGGREGATE(15,6,ROW($B$2:$B$20)/($B$2:$B$20=D2),COUNTIF($D$2:D2,D2))-1) で

 ここでのAGGREGATE関数は

 AGGREGATE(SMALL,エラーを無視する、配列、順位)　です

 配列は　ROW($B$2:$B$20)/($B$2:$B$20=D2)　となっていて

 該当の行番号をB列とD2が一致しているかどうかの結果（True:1,False:0)で割っています
 つまり一致すれば、その行番号を返し、一致しなければ0除算でエラーになります

 例えばサンプルで言うとD3の87の場合は

 エラー	エラー	エラー	エラー	エラー	エラー	エラー	エラー	エラー	エラー	12	エラー	エラー	エラー	エラー	17	エラー	エラー	エラー

 という配列が返ります。

 この配列から、SMALLで　順位が1番目を取得すると　12となり、順位が2番目　では　17　が返ります。

 最後に　順位の指定ですが

 COUNTIF($D$2:D2,D2))-1　で　D列のD2から自分の行までの間で、自分と同じ値がいくつあるか数えています

 例えば D3では　D2からD3の間で　87　は1つ
 D4では　D2からD4の間で　87　は2つ　ありますので、それが順位となります

 ただしこのAGGREGATEでは、純粋なセル番地の行を返すので、見出しの分ずれることになりますので最後に1 を引いています。

 見出し行が2行目でデータが3行目から始まっている場合には、2を引けばいいことになります

（渡辺ひかる） 2019/11/26(火) 09:44

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

思い通りの結果が出ました。
ありがとうございました。
また、ねむねむ様への返信が抜け落ちているご指摘もありがとうございました。
記述した返事が反映されていないことに気付いていませんでした。

ねむねむ様
早々にご回答いただきながら、返信が漏れており
大変失礼しました。
すぐに操作してみたのですが、F列にマイナスの小数点以下の数値が返されてしまい
うまく結果を出すことができませんでした。
(おそらく私の理解不足かと思います）

ねむねむ様
渡辺様
bi様

貴重なお時間をありがとうございました。
（てん） 2019/11/26(火) 09:59

渡辺様

返信がバッティングしてしまっていました。
失礼しました。

新しい関数に触れて、解読しなければ、
と思っていたところ、こんなにも丁寧に分かりやすくご説明いただいて
感謝しかありません。

何度も本当にありがとうございました。

（てん） 2019/11/26(火) 10:03

 ＞$A$4:$A$20にするにはどこを変化させてよいのか

 LARGE の式が D4から入ってるとして
 ↓ でいいのでは？

 E4 =INDEX(A:A,AGGREGATE(15,6,ROW($B$4:$B$20)/($B$4:$B$20=D4),COUNTIF($D$4:D4,D4)))
           ~~~                    ~~~~~~~~~~   ~~~~~~~~~~
           (1)                      (2)          (3)

 波線部(1)の範囲を「A列全体」にする
 波線部(2)(3)は実際の行範囲に合わせる
 以上です
　
「1」を引いたり「3」を引いたり、といった帳尻合わせを考える必要はありません。

 ■ところで･･･
 ＞D列に上位10までの数値(整数)が抽出されています。

 LARGE 関数の式を何行目までコピーしてるんですか？
 10行分？

 たとえば10位の値が重複していたら10行では足りませんよね？
 その場合どうするんですか？

 ピボットテーブルの「トップテンフィルター」でやった方がいいと思いますよ。

 参考まで
（笑） 2019/11/26(火) 11:14

笑様

目にとめていただき、ありがとうございます。

ピボットにトップテンフィルターというのがあるのですね。

両方検証させていただきます。
勉強になります。
感謝します
（てん） 2019/11/26(火) 11:52

コメント返信：

[ 一覧(最新更新順) ]

[[20191125111541]] 『INDEX関数 重複の場合の対処法』（てん） ページの最後に飛ぶ

[[20191125111541]]　『INDEX関数　重複の場合の対処法』（てん）　ページの最後に飛ぶ