（エクセル）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.8

kagakusuki

回答No.3、7です。

　説明し忘れておりましたが、回答No.7に添付した画像において、Sheet1のQ列にSheet1のE列～P列の合計値が表示されているのは、回答者様が添付画像を御覧になられた際に、「Sheet1のE列～P列の合計値」と「Sheet1のD列の値」(及びSheet3のA列の値)の関係を解りやすくするために付け加えているに過ぎないものであり、回答No.7の方法はSheet1のQ列の値が無くとも問題なく動作致します。

回答No.7

kagakusuki

回答No.3です。

>原則、Sheet2!AQ15:AQ500には全てのデータが入力されている前提なので、５名分の氏名がすでに入力されています（空白となることはありません）。
>例外として、空白の場合も想定されますので、その場合は「空白」として反映させれればと思います。

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

という事でしたら、以下の様な方法にすると解りやすくて宜しいのではないかと思います。

　まず、適当な使用していないシート(ここでは仮にSheet3)のA列とB列を作業列として使用するものとします。
　そのSheet3のA8セルに次の関数を入力して下さい。

=IF(COUNT(INDEX(Sheet1!$E:$P,ROW(),)),SUM(INDEX(Sheet1!$E:$P,ROW(),))&"◆"&COUNTIF(A$7:A7,SUM(INDEX(Sheet1!$E:$P,ROW(),))&"◆*"),"")

　次に、Sheet3のA8セルをコピーして、Sheet3のA9以下に貼り付けて下さい。
　次に、Sheet3のB15セルに次の関数を入力して下さい。

=IF(COUNT(INDEX(Sheet2!$AQ:$AQ,ROW())),INDEX(Sheet2!$AQ:$AQ,ROW())&"◆"&COUNTIF(B$14:B14,INDEX(Sheet2!$AQ:$AQ,ROW())&"◆*"),"")

　次に、Sheet3のB15セルをコピーして、Sheet3のB15～B500のセル範囲に貼り付けて下さい。
　次に、Sheet1のD8セルに次の関数を入力して下さい。

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

　次に、Sheet1のD8セルをコピーして、Sheet1のD9以下に貼り付けて下さい。

　以上です。

お礼

画像添付していただきありがとうございます。

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

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

回答No.6

nekotaro3

Sheet1!R:T
Sheet2!$AR$15:$AR$500

↑のセル範囲を、作業用セルとして使用します。
使用中の場合は別の場所を作業用セルにしてください。
別の場所にする場合は、以下の数式の参照を変更してください。
------------------------
Sheet2の$AR$15:$AR$500
=IF(COUNTIF(Sheet1!$T$8:$T$1000,AQ15)>=1,AQ15,"")

まだ、Sheet1!$T$8:$T$1000に何も入れていないので空白が返ります。
Sheet1のR:Tに数式を入れた後は、
Sheet2のAQ列の値と一致するデータが、
Sheet1の$T$8:$T$1000に存在する場合は
AR列の値を返します。
存在しない場合は、空白を返します。
-------------------------
Sheet1のT8(入力したら、下方にコピペします）
=IF(SUM(E8:P8)>0,SUM(E8:P8),"")

E列～P列の合計が無い(データ無し)の場合は空白を返します。
-------------------------
Sheet1のR8(入力したら、下方にコピペします)
=IFERROR(LARGE(Sheet2!$AR$15:$AR$500,COUNTIF(Sheet2!$AR$15:$AR$500,">0")-ROW()+8),"")
------------------------
Sheet1のS8(入力したら、下方にコピペします）
=IF(R8<>"",ABS(COUNTIF($R$8:R8,R8)-COUNTIF($R$8:$R$1000,R8)-1),"")
------------------------
Sheet1のD8(入力したら、下方にコピペします)
=IF(R8<>"",INDEX(Sheet2!C:C,SUMPRODUCT(LARGE((Sheet2!$AR$15:$AR$500=R8)*ROW(Sheet2!$AR$15:$AR$500),S8))),"")

お礼

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

古いエクセルでも使用できるように、sheet1のR8を修正しました。
=IF(ISERROR(LARGE(sheet2!AR15:AR500,COUNTIF(sheet2!$AR$15:$AR$500,">0")-ROW()+8))=TRUE,"",LARGE(sheet2!AR15:AR500,COUNTIF(sheet2!$AR$15:$AR$500,">0")-ROW()+8))
これであっていますでしょうか。

sheet1のD8より下行ですが、うまく正解が反映されていません。
sheet1のD、R、Sのいづれかが誤っているのかと思い、確認中です。

回答No.5

bunjii

>sheet1のE8:P8には既にデータ入力されています（日付）。
>sheet1のE9:P9にもデータが既に入力されています。
>仮にE8とE9に入力されたデータが同一の場合、D8とD9に同じ値が入力されてしまうので、それを防ぎたいということです。
回答No.2で提示した数式では$E$8:$P$8の範囲に入力されている日付け（1つだけ）をSUM関数で取り出したものとSheet2の$AQ$15:$AQ$500に記録された日付けと比較して同じ日付の行の一覧をINDEX関数で配列値としてLARGE関数へ引き渡しています。
LARGE関数ではD8セルの数式では行番号が0以外の1番若いものを取り出すような計算をしています。
その行番号を使ってSheet2のC列に列記されたデータを抽出する数式になっています。
従って、D9セルではD8セルと同じ$E$8:$P$8に記載された日付けとSheet2の$AQ$15:$AQ$500の日付と比較して抽出した2番目に若い行番号を元にSheet2のC列からデータを抽出しています。
D10以降も同じ$E$8:$P$8に入力された日付けと一致する日付けの行番号から順次抽出しています。
Sheet2のAQ列と比較する日付けがSheet1のE:P列に列記されている場合はD列へ抜き出すデータとE:P列に入力された日付の関連性が崩れますので複数のデータを取り出すには別の考え方が必要になります。
例えばQ8:Z8セルへ横方法に該当データを列記するような抽出方法にすべきでしょう。

>例えば、
>sheet2に
>山田（C列）　2016/9/1（AQ列）
>田中（C列）　2016/9/1（AQ列）のデータが既に入力されていて、
>sheet1のE8に2016/9/1、E9に2016/9/1と入力したら、
>sheet1のD8に山田、D9に田中となってほしいイメージです。
Sheet1のE8とE9が同じ2016/9/1であればD8に山田、D9に田中になります。
添付画像はSUM関数の引数を「SUM($E$8:$P$8)→SUM($E8:$P8)」のように修正し、上記のように手入力でデータを修正したものです。

お礼

画像までつけていただきありがとうございます。

一から、SUM($E8:$P8)に修正してやり直してみました。
今回添付していただいた画像の例であれば、9/1の田中、山田はsheet1のD8とD9に反映されますが、sheet1のE10（E10:P10のどこでも良いですが）に2016/7/11と入力しても、「G.N」とは反映されないですよね。

やはりこのままでは難しいですか。
最初、自分で考えているときには、「重複データが現れた場合に、既に入力されたものは削除して考える」と命令できる都合の良い関数があればいいかと思ったのですが。
そうすれば、同じ2016/9/1でも一人目は検索から削除して、二人目をINDEXで反映してくれないかと。

sheet1のE～P列には、8行目から下に日付が新しくなるように入力しようかと思っています。

なので、画像の例では、sheet1の
8行目（E~Pのどこか１箇所）に2016/7/11
9行目（E~Pのどこか１箇所）に2016/8/25
10行目（E~Pのどこか１箇所）に2016/9/1
11行目（E~Pのどこか１箇所）に2016/9/1
12行目（E~Pのどこか１箇所）に2016/9/15　と入力し

D8：G.N
D9：C.M
D10：田中
D11：山田
D12：A.K　となる完成イメージです。

つまり、日付を並び替えて、それに伴う名前を反映させるといった感じでしょうか。
そのため、D9には「既存のD8とは重複しない（D8データを除いたものを反映させる）」といったように、既に出た、自分より上のデータを除ければと考えました。

回答No.4

bunjii

>しかし、D9以降にコピーしましたが、空白になってしまいます。
>SUM($E$8:$P$8)→SUM($E8:$P8)を２箇所修正しましたが、空白のままでした。
SUM関数の引数（$E$8:$P$8）を変更すると目的に合いません。
D8セルの数式を下へコピーしたときにSUM関数の引数の行番号を固定するために$マークを付けて絶対番地を指定していますので行番号の$マーク外すとD9セルのSUM関数の引数が変化して未入力のセルが比較対象になり該当なしとなったのでしょう。
別の目的が有るのでしたら条件を提示してください。（後出しジャンケンのような感じですね）

お礼

回答ありがとうございます。
説明不足の点があるかもしれませんので、確認します。

sheet1のE8:P8には既にデータ入力されています（日付）。
そして、そのデータに合うものをsheet1のD8にINDEX関数を使いsheet2から引用しようとしていました。

sheet1のE9:P9にもデータが既に入力されています。
仮にE8とE9に入力されたデータが同一の場合、D8とD9に同じ値が入力されてしまうので、それを防ぎたいということです。

例えば、
sheet2に
山田（C列）　2016/9/1（AQ列）
田中（C列）　2016/9/1（AQ列）のデータが既に入力されていて、

sheet1のE8に2016/9/1、E9に2016/9/1と入力したら、
sheet1のD8に山田、D9に田中となってほしいイメージです。

私のやり方では、D8に山田、D9に山田となってしまいます。
教えていただいた方法では、D8に山田、D9空白でした。

よろしくお願いします。

回答No.3

kagakusuki

不明な点があります。
　例えば、SUM(E8:P8)、SUM(E9:P9)、SUM(E10:P10)、SUM(E11:P11)、SUM(E12:P12)が同じ値だと、反映させなけれなければならない人の名前が5名分必要になりますが、それにもかかわらずSheet2!AQ15:AQ500の範囲の中でSUM(E8:P8)～SUM(E12:P12)と同じ値が入力されている候補が、Sheet2!C列に「山田」、「田中」、「鈴木」と入力されている3名分しか存在していなかった場合には、Sheet1のD11やD12にはどの様な値を表示させれば宜しいのでしょうか？

　後それから、御要望の事を実現するための方法にはおそらく複数の方法があり得るのではないかと予想されますが、大別して「関数だけで表示させる事が出来るが処理が重くなる方法」と「作業列を使用する事で軽快に処理出来る様にする方法」の2通りに分けられるかと予想されますが、どちらのタイプの方法が宜しいのでしょうか？

お礼

細かい点までありがとうございます。
原則、Sheet2!AQ15:AQ500には全てのデータが入力されている前提なので、５名分の氏名がすでに入力されています（空白となることはありません）。
例外として、空白の場合も想定されますので、その場合は「空白」として反映させれればと思います。

「作業列を使用する事で軽快に処理出来る様にする方法」の方が良いかと思います。
しかし、このエクセルは複数の者で共有することになるかと思いますので、それがマクロによるものであれば、マクロを理解していない人もいますので、マクロを使用せずに出来る方法がいいです。

よろしくお願いします。

回答No.2

bunjii

>Sheet2!$C$15:$C$500には、特に決まっていませんが数字や人の名前など入力しようかと思っています。
模擬データとしてアルファベット2文字の間にドット（.）を入れたものを使用させて頂きます。
文字列をランダムに生成し、重複を削除しました。

>sheet1のE8:P8には、1箇所に日付データを入力しようと思います
テスト用にE8:P8の1ヶ所に2016/4/1～2016/9/30から日付けを生成させました。

>sheet1のE8:P8には、1箇所に日付データを入力しようと思います（E8:P8に複数個所入力することはありません）。
2016/4/1～2016/9/30の日付をランダムに生成させます。

次の数式を使うと目的に合うはずです。
D8=IF(COUNTIF(Sheet2!$AQ$15:$AQ$500,SUM($E$8:$P$8))>=ROWS(D$8:D8),INDEX(Sheet2!$C:$C,LARGE(INDEX((Sheet2!$AQ$15:$AQ$500=SUM($E$8:$P$8))*ROW(D$15:D$500),0),COUNTIF(Sheet2!$AQ$15:$AQ$500,SUM(E$8:P$8))-ROWS(D$8:D8)+1)),"")
D8セルを下へ空欄が代入されるまでコピーします。
IF関数は日付けが一致する数を超えたとき空欄とするためです。
LARGE関数の引数としてINDEX関数を使っていますが該当する複数の行番号をLARGE関数へ返すためです。
LARGE関数を使っていますが該当するセルの行番号の若い順に抽出するよう配慮しています。
関数の使い方で解説を必要とする個所がありましたら補足してください。
検証結果の画像を添付しましたが解像度が悪く読めないかも知れません。

お礼

関数を作っていただき、ありがとうございます。

D8に入力した結果、正しく反映されました。
しかし、D9以降にコピーしましたが、空白になってしまいます。
SUM($E$8:$P$8)→SUM($E8:$P8)を２箇所修正しましたが、空白のままでした。

回答No.1

bunjii

>ここで、SUM(E8:P8)、SUM(E9:P9)、SUM(E10:P10)が同じ値だと、候補が複数あるにも関わらず、一つの候補がD8、D9、D10に入力されてしまいます。
提示の数式では同一候補が複数有っても最初に見つかった行を返します。
MATCH関数の仕様なので別の方法で抽出してください。

>出来ることならば、VLOOKUPなど他の関数ではなく、INDEX、MATCH関数で組み立てられたらうれしいです。
VLOOKUP関数でも同様になります。
INDEX関数とSMALL関数またはLARGE関数とROW関数を組み合わせれば列記するような抽出ができます。
具体的な数式は検証してからでないと提示できません。
出来れば模擬データを提示してください。

お礼

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

具体的にどのようなデータを扱っているか捕捉したらよいでしょうか。
Sheet2!$C$15:$C$500には、特に決まっていませんが数字や人の名前など入力しようかと思っています。
sheet1のE8:P8には、1箇所に日付データを入力しようと思います（E8:P8に複数個所入力することはありません）。
sheet2!$AQ$15:$AQ$500にも日付データが入力されています。

これで大丈夫でしょうか。