（エクセル）INDEX、MATCHで全候補を反映

質問

初歩的な質問かもしれませんが、エクセル関数で分からないことがありましたので、教えてください。

sheet1のD8で以下の関数を入力しています。
=INDEX(Sheet2!$C$15:$C$500,MATCH(SUM(E8:P8),Sheet2!$AQ$15:$AQ$500,0))
（E8:P8にもデータ入力されています。）

sheet1のD9には
=INDEX(Sheet2!$C$15:$C$500,MATCH(SUM(E9:P9),Sheet2!$AQ$15:$AQ$500,0))
といった感じで、D8から下に同じ内容の関数を入力しています。

ここで、SUM(E8:P8)、SUM(E9:P9)、SUM(E10:P10)が同じ値だと、候補が複数あるにも関わらず、一つの候補がD8、D9、D10に入力されてしまいます。
例えば、人の名前であれば、D8：山田、D9：田中、D10：鈴木となってほしいところ、D8～D10で山田という結果になります。

山田、田中、鈴木ともに同じデータを持っているので、複数の条件で縛ろうとしても良い案が思いつきませんでした。
ROWで何とかできないかと試行錯誤しましたが、よく分かりませんでした。

出来ることならば、VLOOKUPなど他の関数ではなく、INDEX、MATCH関数で組み立てられたらうれしいです。
また、D8：鈴木、D9：山田、D10：田中といったように同じデータを持っているもの同士では順番はどのようになってもいいのですが、出来ることならsheet2で上の行から順番に入力されているどおりにD8～D10にも反映されていた方がいいです。

分かる方いましたら教えてください。
よろしくお願いします。

回答No.18

bunjii

>今回の例では、E6＝2016/9/7、F6＝2016/9/8と連日になっています。
>それで、sheet1のE8には
=IF(AND(SMALL(sheet2!$AQ$15:$AQ$500,$C8)<F$6,SMALL(sheet2!$AQ$15:$AQ$500,$C8)>=E$6),SMALL(sheet2!$AQ$15:$AQ$500,$C8),"")
>と入力しまいます（E～Pの8行目以降同じ）。
>そのため、sheet2のAQ列には、日にちだけなのか時間まであるかは関係ないとこちらで勝手に判断してしまったものです。
処理したいSheet1は既に表の形が整っていてD列へSheet2のAQ列から目的の値を抽出する数式を組み立てるだけですよね？
最初から模擬データと検索値の生成過程の説明があればもっと早く解決に導けたはずです。
Sheet1のE8からP8へはE6からP6の日付から自動的に生成されるものと解釈します。
回答No.16で提示した数式はE7:P7に数値が入力されていないことを条件にしています。
万一、E8:P8以下に生成した日付と同じシリアル値がE7:P7に存在すると抽出漏れが発生します。（文字列の場合は影響ありません）
添付画像のようになれば良いのですよね？

お礼

sheet1のE7には=COUNT(E8:E500)としています（P7まで同じ）。
そのため、日付と同じ値が入力されることはありません。

私のエクセルでも、添付していただいた画像と同じ結果になっており、完成イメージどおりです。
ありがとうございました。

回答No.17

kagakusuki

回答No.3、7、12、15です。

>こちらで勝手に、関数だけで求めるか？、マクロを利用して求めるか？ということかと思ってしまいました。

　まず初めにお断りしておきますが、マクロと作業列は全く異なるものです。
　回答No.7に添付されている画像をご覧頂ければ解ります様に、「作業列を使った方法」とは処理を行う過程で必要となる中間結果のデータを、「『出力先の列』(本件の場合はSheet1のD列)以外の列」(本件の場合はSheet3のA列とB列)のセルに一旦、値を入れておく様な関数を入力しておき、その中間結果のデータを利用して、最終的な結果を導き出すというものです。
　その際に使用する(Sheet3のA列とB列などの)中間結果のデータを出力させるために使われている列の事を「作業列」と呼んでいる訳です。
　作業列に入力されている関数は、マクロではなく通常のワークシート関数(セルに入力して使う関数)なのですから、「作業列を使った方法」はマクロなどではなく、通常の関数を使った方法の一種です。

>マクロを利用したり、作業列があると、その内容を理解できず、例えば修正したい場合に何もできなくなってしまうのではないかと懸念しています。

　通常のワークシート関数しかご存知ではない方々にとっては、マクロを利用する方法では理解し難くなると仰る事に関してはのはまだ当然であると言えます。
　一方、作業列があると理解し難くなると考えるのはむしろ逆です。
　当然の事ながら、1つのセルの中には1つの関数しか入力出来ません。
　そのたった1つの関数で一から十まで全ての処理を行うためには、複雑な関数にするしかありません。
　本件などは、検索値である日付がE列～P列のどこに入力されているのか未定な上、通常のVLOOKUP関数やMACH関数だけでは欲しい結果が得られない事をやろうとしているのですから、1つのセル内のみで全ての処理を行うためには非常に複雑な関数とならざるを得ません。
　その様な複雑な関数が理解しやすいものである筈が御座いません。
　例えば、もし御質問の件を作業列を用いずに処理しようとするのであれば、Sheet1のD8セルの関数は次の様なものとなります。

=IF(AND(SUM(INDEX($E:$E,ROW()):INDEX($P:$P,ROW()))>0,COUNTIF($E$7:INDEX($P:$P,ROW()-1),SUM(INDEX($E:$E,ROW()):INDEX($P:$P,ROW())))<COUNTIF(Sheet2!$AQ$14:$AQ$500,SUM(INDEX($E:$E,ROW()):INDEX($P:$P,ROW())))),INDEX(Sheet2!$C:$C,SUMPRODUCT(ISNUMBER(ROW(Sheet2!$AQ$14:$AQ$500))*(COUNTIF(OFFSET(Sheet2!$AQ$14,,,ROW(Sheet2!$AQ$14:$AQ$500)-ROW(Sheet2!$AQ$14)+1),SUM(INDEX($E:$E,ROW()):INDEX($P:$P,ROW())))<COUNTIF($E$7:INDEX($P:$P,ROW()),SUM(INDEX($E:$E,ROW()):INDEX($P:$P,ROW())))))+ROW(Sheet2!$AQ$14))&"","")

　この関数をSheet1のD8セルに入力してから、Sheet1のD8セルをコピーして、Sheet1のD9以下に貼り付ければ、お望みの結果が得られる事になりますが、果たして質問者様はこの関数を理解して修正したい時に修正する事が出来るのでしょうか？
　後、注意して頂きたいのですが、上記の関数はあくまで

>sheet1のE8:P8には、1箇所に日付データを入力しようと思います（E8:P8に複数個所入力することはありません）。

という事が必ず守られている事が前提条件ですので、もしも、同じ行のE列～P列のセル範囲内の複数のセルに日付が入力されていた場合には、Sheet1のD列に該当者の一部が表示されなくなる恐れがありますので、E列～P列に日付や数値を1行につき複数入力する事は必ず避けて下さい。
　本来であれば、1行につき2か所以上のセルに入力されている様な事があれば、警告を表示する様な関数としておきたいところなのですが、

>Sheet1のD8セルですが、旧バージョンのエクセルでも使用できるように

という縛りがあるため、更に複雑な関数とする事も躊躇われましたので、今回は警告機能を付け加える事は行いませんでした。

お礼

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

こちらの理解不足や言葉足らずで、手間が増えてしまい申し訳ありません。
作業列を使用することの一長一短も分かりました。

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

回答No.16

bunjii

>本当は、AQ列には、日時・時分が入力されています。
データの提示方法に問題は有ったようですね？

>最初の自分の結果では、sheet1のD8以下が
漸く、あなたの意図が分かりました。
面談のときは短時間で解決することですが文書の質疑応答では双方の思惑が通じ難いことを痛感しました。
比較的簡単に処理可能なことです。
Sheet1のD8セルに次の数式を設定して下へ必要数コピーしてください。
=IF(SUM(E8:P8)=0,"",INDEX(Sheet2!C:C,LARGE(INDEX((Sheet2!AQ$15:AQ$500=SUM(E8:P8))*ROW(Sheet2!AQ$15:AQ$500),0),COUNTIF(Sheet2!AQ$15:AQ$500,SUM(E8:P8))-COUNTIF(E$7:P7,SUM(E8:P8)))))
添付画像はSheet2のAQ15:AQ34をSheet1のE8:P27へ昇順で1行当たり1データを列をランダムに配置して検証しました。
作業用の中間データを格納するセルは必要ありません。
尚、複数の一致データについては塗りつぶしの色で区分けしてあります。

お礼

ありがとうございます。
思いが通じてよかったです。
関数も成功しています。

後だしする意図は無く、こちらで勝手に回答者が必要と思われる情報しか提供しなかったので、混乱させてしまったのかと思います。

例えば、
sheet1のC8から下には1、2、3と連番が振ってあり、
sheet1のE6から右に日付が入力されています。
今回の例では、E6＝2016/9/7、F6＝2016/9/8と連日になっています。
それで、sheet1のE8には
=IF(AND(SMALL(sheet2!$AQ$15:$AQ$500,$C8)<F$6,SMALL(sheet2!$AQ$15:$AQ$500,$C8)>=E$6),SMALL(sheet2!$AQ$15:$AQ$500,$C8),"")　と入力しまいます（E～Pの8行目以降同じ）。

そのため、sheet2のAQ列には、日にちだけなのか時間まであるかは関係ないとこちらで勝手に判断してしまったものです。

あまり議論が拡散しないように制約したつもりですが、すみませんでした。

回答No.15

kagakusuki

回答No.3、7、12です。

　回答No.3のお礼欄に

>「作業列を使用する事で軽快に処理出来る様にする方法」の方が良いかと思います。

とありましたので回答No.7、12では作業列を用いた方法をお伝えしたのですが、回答No.12のお礼欄において

>sheet3の作業をsheet1の数式に入力しまうなどして、sheet3のセルを省くことはできないでしょうか。

と書いておられるのはどうしてなのでしょうか？

>「関数だけで表示させる事が出来るが処理が重くなる方法」

の方を用いる事に方針を変えられたという事なのでしょうか？

お礼

ご連絡ありがとうございます。

すみません。
こちらで勝手に、関数だけで求めるか？、マクロを利用して求めるか？ということかと思ってしまいました。
マクロを利用したり、作業列があると、その内容を理解できず、例えば修正したい場合に何もできなくなってしまうのではないかと懸念しています。

もちろん、一つの数式で表現できたとしても、その数式が意味するところを理解しておかないと同じことなのだとは思いますが。

一つのセルで完結→作業列を利用して完結→マクロを利用
の順でできたらと思っています。

ただ、「処理が重くなる」というのがどの程度なのか分からないので、程度問題だとは思います。

回答No.14

bunjii

B回答No.13の追加です。
提示して頂いたSheet2の模擬データからSheet1!の必要事項を勝手解釈で作成し、要望に合うものと思われる処理をしてみました。
Excel 2013での処理ですが結果だけを添付画像で貼り付けました。
処理に使った数式はこの回答で提示しませんがSheet1のA列とB列は作業用として中間データを収納しています。
N8の値と一致するデータは2件です。
P9の値と一致するデータは2件です。
N10、M11、G12、K13の値と一致するデータは夫々1件です。
E14の値と一致するデータは7件です。
P15の値と一致するデータは3件です。
N16の値と一致するデータは2件です。
添付画像のような結果で良ければ必要な数式を提示します。

お礼

サンプルを作っていただきありがとうございます。

完成イメージと違う点があります。
それは、17行目～27行目のE~Pが入力されていない点です。

作成したいエクセルの操作は、まずは日付を入力します（E~P列）。すると、そのデータのラベルの意味でD列に数字（氏名でも可）が反映されてほしいです。

作っていただいたものから想像しますと、同じデータがある場合、E～Pのいずれかに一度入力したら、たちまちD列にその入力したデータと一致するもの全てが反映されてしまうのではないでしょうか。

E8～P8にデータ入力→D8に反映→E9~P9にデータ入力→D9に反映の繰り返しになるはずのため、E17～P17に入力していない段階ではD17に結果が反映されてしまうのは困ります。

（おそらく、E17～P17を入力する前に、既にD17にはデータが反映されてしまっているのではないでしょうか）

よろしくお願いします。

回答No.13

bunjii

>最初はE8~P8を見て、D8に結果を返すわけですが、次にE9~P9→E10~P10→E11～P11といったように、その都度SUMし、それに見合った結果を求めていくことから、SUMもずれていかなければいけないと思ってしまいました。
それはあなたの思い込みです。（判断の誤りです）
E8:P8に入力されたデータと一致する値がSheet2!AQ15:AQ500に複数有ったときにD8以下へ抽出するのですからE9:P9にE8:P8と異なる値になっているとD9へは条件の異なった値を返すことになりますので目的に合わないでしょう。

>sheet2はC列でしょうか？
提示して頂くのはC列とAQ列の15行目から34行目までと考えていました。
Sheet1のE8:P8の合計とSheet2のAQ15:AQ500を比較して一致する行のC列の値をD8へ求めると言う条件をあなたが提示していますのでSheet2の模擬データはC列とAQ列になります。

>sheet2
>Ｃ列　Ｄ列　ＡＱ列
>612016/9/8 6:00
>3212016/9/8 7:00
と言うことは下記のようになりますが、前回までの追加情報では「Sheet2のAQ列に日付」となっていたのは誤りですか？、それとも変更ですか？
C15=61、D15=2016/9/8、AQ15=6:00
C16=321、D16=2016/9/8、AQ16=7:00
AQ列の時刻は分単位、秒単位の端数がないものとして良いですか？
また、0:00もあり得るのですか？

今回提示して頂いたSheet2のデータを手作業で抽出した結果のSheet1の提示して頂けませんか？
あなたのやりたいことが現状では把握できていません。
条件が2転3転していますのでSheet1の結果の提示を頂いてから検証したいと思います。

お礼

>「Sheet2のAQ列に日付」となっていたのは誤りですか？、それとも変更ですか？
>C15=61、D15=2016/9/8、AQ15=6:00
>C16=321、D16=2016/9/8、AQ16=7:00
>AQ列の時刻は分単位、秒単位の端数がないものとして良いですか？
>また、0:00もあり得るのですか？

本当は、AQ列には、日時・時分が入力されています。
簡略するためと、あとは入力の仕方だけの問題なので、結果として変わらないかと思い、省力しました。すみません。

sheet2 C列
6
32
54
131
143
7
40
127
20
22
37
72
90
165
202
8
17
104
31
227

sheet2 D列（前回、知りたいという話でしたので）
1
1
1
1
1
1
1
1
1
1
1
2
1
1
1
1
1
1
1
1

sheet2 AQ列
2016/9/8 6:00
2016/9/8 7:00
2016/9/9 20:00
2016/9/9 21:00
2016/9/9 17:00
2016/9/10 4:00
2016/9/10 6:00
2016/9/10 7:00
2016/9/10 8:00
2016/9/10 8:00
2016/9/10 8:00
2016/9/10 8:00
2016/9/10 8:00
2016/9/10 8:00
2016/9/10 8:00
2016/9/10 9:00
2016/9/10 9:00
2016/9/10 9:00
2016/9/10 11:00
2016/9/10 11:00
です。

0:00もあり得えます。

最初の自分の結果では、sheet1のD8以下が
6
32
143
54
131
7
40
127
20
20
20
20
20
20
20
8
8
8
31
31
となりました。

よろしくお願いします。

回答No.12

kagakusuki

回答No.7です。

>Sheet1のD8セルですが、旧バージョンのエクセルでも使用できるように、IFERRORを使用せずに出来ないでしょうか。

　それでしたら、次の様な関数にされると良いと思います。

=IF(INDEX(Sheet3!$A:$A,ROW())="","",IF(COUNTIF(Sheet3!$B:$B,INDEX(Sheet3!$A:$A,ROW())),INDEX(Sheet2!$C:$C,MATCH(INDEX(Sheet3!$A:$A,ROW()),Sheet3!$B:$B,0))&"",""))

お礼

ありがとうございます。
無事、成功しました。

贅沢をいいますと、sheet3の作業をsheet1の数式に入力しまうなどして、sheet3のセルを省くことはできないでしょうか。
もちろん、sheet3ではなく、sheet1の空きスペースなどで行ってもいいかと思うのですが、できるだけ新しいシートや新しいセルは使用せずに出来ないかと思いまして。

出来なければ諦めます。

回答No.11

nekotaro3

今度の画像は、Sheet1のものです。

※F～P列の値は、すべて0としています。

T列には、E～Pの合計値が表示されます。

R列は、Sheet2のAR15:AR500の範囲で
値が返っている(空白以外)のデータを順に表示します。

従って、R列の値と、T列の値は一致しません。
理由は、Sheet2のAR15:AR500の重複値の件数と
Sheet1の(E～Pを合計した値)の重複値の件数とが、
必ずしも一致するとは限らないからです。

T15は9/8の日付ですが、R15は9/4の日付になっています。
9/8の日付は、Sheet2のAQ15:AQ500に存在しないので、
表示されません。(存在する場合は表示します)

S列に表示される値は、データ件数を大きい順に取り出します。

D8以降に入力する数式で、LARGE関数を使用しているからです。
=IF(R8<>"",INDEX(Sheet2!C:C,SUMPRODUCT(LARGE((Sheet2!$AR$15:$AR$500=R8)*ROW(Sheet2!$AR$15:$AR$500),S8))),"")

9/1のデータ件数は、5件なので
全5件の中から、行番号が5番目に大きい値
全5件の中から、行番号が4番目に大きい値....

というように、行番号が小さい順に取り出します。

回答No.10

bunjii

>一から、SUM($E8:$P8)に修正してやり直してみました。
その考え方が誤りです。
あなたが要求している条件はSheet1のE8:P8の何れかに2016/9/1と入力したとき抽出すべきデータが複数になったときすべてをSheet1のD8、D9、D10、D11、・・・へ列記することですよね？
それを満たすためには抽出すべきデータをすべて列記した後に次に抽出すべき日付けでSheet2のAQ15:AQ500から同一日付けの行番号を探さなければなりません。
従って、2016/9/1が5件有ったとすればD8、D9、D10、D11、D12の数式内のSUM関数はすべてSUM(E8:P8)でなければなりません。
D8セルの数式をD9以下のセルへコピーするとSUM(E8:P9)、SUM(E10:P10)、SUM(E11:P11)、SUM(E12:P12)のように行番号が遷移します。それを防ぐためにSUM(E$8:P$8)のように行番号を絶対指定としてD列のどの行へコピーしても変化しないようにします。
列記号の前に付けた$記号は別の列へコピーしたときに変化しないようにするための絶対番地指定に必要な方法です。
E9:P9へ2016/9/15と入力してD13セルから下へ抽出するべきデータが3件あったときはD13:D15セルのSUM関数はSUM($E$9:$P$9)にならなければなりません。
これを自動的に切り替えるには作業用の中間データを必要とし数式も複雑になります。
添付画像を用意してあなたの考えが誤りであることを諭すためにSUM($E8:$P8)でも下へコピーしたときにD8に田中、D9に山田が抽出されることを検証したものです。
それはE8と同じ日付けをE9にも入力してあるのでSUM(E8:P8)とSUM(E9:P9)が等価になるためです。
回答No.5で「例えばQ8:Z8セルへ横方法（横方向の誤り）に該当データを列記するような抽出方法にすべきでしょう。」と提案しましたが検討して頂けなかったのですか？

>なので、画像の例では、sheet1の
>8行目（E~Pのどこか１箇所）に2016/7/11
>9行目（E~Pのどこか１箇所）に2016/8/25
　　　　　　・
　　　　　　・
>D8：G.N
>D9：C.M
>D10：田中
そのような考え方の数式ではありませんので回答No.2の数式をそのまま使ってください。
目的に合わないときは元データと抽出結果（手作業で処理したもの）を提示してください。
提示して頂く模擬データはSheet2のD列とAQ列が20行程度とそれを手作業で処理したSheet1のデータをCSV形式で保存したテキストデータをメモ帳で開いて全選択したものを補足へコピペしてください。
Sheet2の例
C列 AQ列
田中 2016/9/1
山田 2016/9/1
A.K 2016/9/15
C.M 2016/8/25
G.N 2016/7/11
M.V 2016/7/29
I.D 2016/8/24
Y.I 2016/7/17
B.V 2016/9/30
L.P 2016/8/24
M.L 2016/9/30
G.Y 2016/9/30
K.V 2016/5/13
D.A 2016/5/13
W.V 2016/4/9
N.O 2016/5/13
Q.U 2016/8/4
N.R 2016/6/14
B.Y 2016/7/17
Q.Z 2016/7/8

お礼

最初はE8~P8を見て、D8に結果を返すわけですが、次にE9~P9→E10~P10→E11～P11といったように、その都度SUMし、それに見合った結果を求めていくことから、SUMもずれていかなければいけないと思ってしまいました。

「例えばQ8:Z8セルへ横方法（横方向の誤り）に該当データを列記するような抽出方法にすべきでしょう。」については、極力、空きセルを使用したくなかったのと（他の入力がすでにされていたりします）、具体的にどうするべきかイメージが着かず、手詰まりしていました。すみません。

「Sheet2のD列とAQ列が20行程度とそれを手作業で処理したSheet1のデータをCSV形式で保存したテキストデータをメモ帳で開いて全選択したものを補足へコピペしてください。」とのことですが、sheet2はC列でしょうか？

sheet2
Ｃ列　Ｄ列　ＡＱ列
6 1 2016/9/8 6:00
32 1 2016/9/8 7:00
54 1 2016/9/9 20:00
131 1 2016/9/9 21:00
143 1 2016/9/9 17:00
7 1 2016/9/10 4:00
40 1 2016/9/10 6:00
127 1 2016/9/10 7:00
20 1 2016/9/10 8:00
22 1 2016/9/10 8:00
37 1 2016/9/10 8:00
72 2 2016/9/10 8:00
90 1 2016/9/10 8:00
165 1 2016/9/10 8:00
202 1 2016/9/10 8:00
8 1 2016/9/10 9:00
17 1 2016/9/10 9:00
104 1 2016/9/10 9:00
31 1 2016/9/10 11:00
227 1 2016/9/10 11:00

Ｃ列は、何でもいいので（数字、人の名前など）、今回は数字を入力しています。

回答No.9

nekotaro3

IFERROR関数が使えない場合は
Sheet1のR8セルに入れる数式を、以下のものに変更してください。

=IF(T8<>"",LARGE(Sheet2!$AR$15:$AR$500,COUNTIF(Sheet2!$AR$15:$AR$500,">0")-ROW()+8),"")

画像は、Sheet2です。
Sheet1のE列からP列を合計した(検索値)と一致するデータは、
AR列に、AQ列の値を返します。

お礼

新たに教えていただきありがとうございます。

教えていただいたとおりに貼り付けたつもりですが、sheet1のD8以降には正しい解答が反映されていません・・・

単純にsheet2のC15＝1、C16=2、・・・・C24=10と入力し、AQ15～AQ24のデータをそのままsheet1のE8～E18にそのままコピペしました。
sheet1のD8～D18には、D8=1、D9＝2、・・・D18=10となるはずですが、D8から順に、4、6、3、5、10、8、9、1、7、2となりました。