みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

(エクセル)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にも反映されていた方がいいです。

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

投稿日時 - 2016-09-24 12:57:23

QNo.9233551

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(18)

ANo.18

>今回の例では、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に存在すると抽出漏れが発生します。(文字列の場合は影響ありません)
添付画像のようになれば良いのですよね?

投稿日時 - 2016-09-27 19:29:32

お礼

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

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

投稿日時 - 2016-09-27 20:14:05

ANo.17

 回答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セルですが、旧バージョンのエクセルでも使用できるように

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

投稿日時 - 2016-09-27 12:57:04

お礼

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

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

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

投稿日時 - 2016-09-27 20:02:01

ANo.16

>本当は、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データを列をランダムに配置して検証しました。
作業用の中間データを格納するセルは必要ありません。
尚、複数の一致データについては塗りつぶしの色で区分けしてあります。

投稿日時 - 2016-09-27 09:21:38

お礼

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

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

例えば、
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列には、日にちだけなのか時間まであるかは関係ないとこちらで勝手に判断してしまったものです。

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

投稿日時 - 2016-09-27 10:50:50

ANo.15

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

 回答No.3のお礼欄に

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

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

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

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

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

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

投稿日時 - 2016-09-27 05:43:47

お礼

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

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

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

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

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

投稿日時 - 2016-09-27 10:31:11

ANo.14

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件です。
添付画像のような結果で良ければ必要な数式を提示します。

投稿日時 - 2016-09-26 22:59:29

お礼

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

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

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

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

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

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

よろしくお願いします。

投稿日時 - 2016-09-27 00:54:20

ANo.13

>最初は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
>C列 D列 AQ列
>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の結果の提示を頂いてから検証したいと思います。

投稿日時 - 2016-09-26 13:17:47

お礼

>「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
となりました。

よろしくお願いします。

投稿日時 - 2016-09-27 00:29:24

ANo.12

 回答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))&"",""))

投稿日時 - 2016-09-26 10:55:27

お礼

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

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

出来なければ諦めます。

投稿日時 - 2016-09-26 23:57:56

ANo.11

今度の画像は、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番目に大きい値....

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

投稿日時 - 2016-09-25 20:56:08

ANo.10

>一から、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のどこか1箇所)に2016/7/11
>9行目(E~Pのどこか1箇所)に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.K2016/9/15
C.M2016/8/25
G.N2016/7/11
M.V2016/7/29
I.D2016/8/24
Y.I2016/7/17
B.V2016/9/30
L.P2016/8/24
M.L2016/9/30
G.Y2016/9/30
K.V2016/5/13
D.A2016/5/13
W.V2016/4/9
N.O2016/5/13
Q.U2016/8/4
N.R2016/6/14
B.Y2016/7/17
Q.Z2016/7/8

投稿日時 - 2016-09-25 20:55:27

お礼

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

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


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

sheet2
C列 D列 AQ列
612016/9/8 6:00
3212016/9/8 7:00
5412016/9/9 20:00
13112016/9/9 21:00
14312016/9/9 17:00
712016/9/10 4:00
4012016/9/10 6:00
12712016/9/10 7:00
2012016/9/10 8:00
2212016/9/10 8:00
3712016/9/10 8:00
7222016/9/10 8:00
9012016/9/10 8:00
16512016/9/10 8:00
20212016/9/10 8:00
812016/9/10 9:00
1712016/9/10 9:00
10412016/9/10 9:00
3112016/9/10 11:00
22712016/9/10 11:00

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

投稿日時 - 2016-09-26 07:48:29

ANo.9

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列の値を返します。

投稿日時 - 2016-09-25 20:39:53

お礼

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

教えていただいたとおりに貼り付けたつもりですが、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となりました。

投稿日時 - 2016-09-26 07:28:59

ANo.8

 回答No.3、7です。

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

投稿日時 - 2016-09-25 19:51:17

ANo.7

 回答No.3です。

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

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

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

 まず、適当な使用していないシート(ここでは仮に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以下に貼り付けて下さい。

 以上です。

投稿日時 - 2016-09-25 19:15:44

お礼

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

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

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

投稿日時 - 2016-09-26 07:15:21

ANo.6

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))),"")

投稿日時 - 2016-09-25 16:50:04

お礼

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

古いエクセルでも使用できるように、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のいづれかが誤っているのかと思い、確認中です。

投稿日時 - 2016-09-25 19:28:38

ANo.5

>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)」のように修正し、上記のように手入力でデータを修正したものです。

投稿日時 - 2016-09-25 14:25:07

お礼

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

一から、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のどこか1箇所)に2016/7/11
9行目(E~Pのどこか1箇所)に2016/8/25
10行目(E~Pのどこか1箇所)に2016/9/1
11行目(E~Pのどこか1箇所)に2016/9/1
12行目(E~Pのどこか1箇所)に2016/9/15 と入力し

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

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

投稿日時 - 2016-09-25 18:31:15

ANo.4

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

投稿日時 - 2016-09-25 12:11:03

お礼

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

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空白でした。

よろしくお願いします。

投稿日時 - 2016-09-25 12:49:20

ANo.3

 不明な点があります。
 例えば、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通りに分けられるかと予想されますが、どちらのタイプの方法が宜しいのでしょうか?

投稿日時 - 2016-09-25 01:04:10

お礼

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

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

よろしくお願いします。

投稿日時 - 2016-09-25 10:19:44

ANo.2

>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関数を使っていますが該当するセルの行番号の若い順に抽出するよう配慮しています。
関数の使い方で解説を必要とする個所がありましたら補足してください。
検証結果の画像を添付しましたが解像度が悪く読めないかも知れません。

投稿日時 - 2016-09-24 23:58:39

お礼

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

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

投稿日時 - 2016-09-25 10:12:30

ANo.1

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

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

投稿日時 - 2016-09-24 13:36:40

お礼

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

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

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

投稿日時 - 2016-09-24 20:07:09

あなたにオススメの質問