INDEX関数とＭＡＴＣＨ関数（長文です）

実は、以前にこの場で質問をしてＩＮＤＥＸ関数の利用を勧められた者です。実際、利用してみましたが、なぜか一定の範囲しか抽出しません。

具体的に現状を書きます。
管理表を作っています。

sheet１
　　　　Ａ　　　 　Ｂ
　　　　　 　次回予定日
１｜　　　｜　　　　　　｜←150日後日付をかえしたい
２｜　　　｜　　　　　　｜
３｜　　　｜　　　　　　｜
４｜　　　｜　　　　　　｜



sheet２
　　 　　　Ａ　　　　　Ｂ
　　　　　日付　　　追加補充　
１｜　05/2/1　｜　　有り　　｜
２｜　05/3/15 ｜　　無し　　｜
３｜　05/4/1　｜　　無し　　｜
４｜　05/4/15 ｜　　有り　　｜


私が作った関数は
sheet1のA1のセルにまず次のような式を入れました。
=INDEX(sheet2!A1:A4,MATCH("有り",sheet2!B1:B4))



次にsheet1のB1セルに次のような数式を入れました。
=DATE(YEAR(A1)+0,MONTH(A1)+0,DAY(A1)+150)

これで、sheet1のBの列の表示形式を日付にしたら一応、一部だけ抽出できました。


最終目的はsheet1のB1のセルに【sheet2のB1～B4の間で最新日付の「有り」があった場合、随時150日後の日付を更新、抽出していきたい】ということです。

今回の場合は”05/2/1”と”05/4/15”に「有り」がある訳ですから、最終的に”05/4/15”の150日後がsheet1のB1セルに抽出されればいいのですが…

なぜだか、#N/Aのような表示が出てきてしまいます。

現在わかっている事は、仮にsheet2のB2が「有り」にした場合、私の入力した式でsheet1のB1に150日後の日付が抽出されます。
ところが、sheet2のB3およびB4を「有り」とした場合、#N/A表示が出てきてしまい計算しないようなのです。
範囲はすべて指定しているつもりですが、B1とB2は正しく計算して、B3とB4の場合ではエラーになってしまうのはなぜでしょうか…

長々と恐縮ですが、ご教授願います。

No.6 ベストアンサー 回答者： kurikuri_maroon 回答日時： 2005/04/27 15:01

こちらこそ、たびたび申し訳ありません。

正直申し上げて、どうもいまひとつ噛み合っていないようですので、もう１度書かせていただきます。

DMAX関数の使用目的や仕様についても記しましたので、下記をごらんいただければ幸いです。
なお、セルの位置（シート毎）とその位置における計算式を十分に把握していただき、目的の日付が算出されることをご確認下さい。正しく動作するはずですよ。
（そもそも、当初のご質問の目的を達成した、と思ったのですが…。）

● Sheet1～Sheet3を使う

【Sheet3】（Criteria）
　　　列Ａ　　　　　Ｂ　　Ｃ
行１　日付　追加補充　有無
　２　　　　　　　１

ａ．セルA1，セルB1，セルC2は、それぞれ、そのまま「日付」「追加補充」「有無」という文字を入力します。
ｂ．セルA1に日付（例：2005/01/01）を入力する、ということではありません。

【Sheet2】（Database）
　　　列Ａ　　　　　　　　Ｂ　　Ｃ
行１　日付　　　　追加補充　有無
　２　2005/02/01　　　　１　有り
　３　2005/04/01　　　　０　無し
　４　2005/03/15　　　　０　無し
　５　2005/04/15　　　　１　有り

ｃ．セルC2　=IF(B2=1,"有り","無し")
（セルC3～セルC5へ式をコピー）
ｄ．日付の昇順に並び替える必要はありません。
ｅ．空行・空列・空白セルがないようにして下さい。
ｆ．検索対象のフィールドは列Ａ（セルA1）です。

【Sheet1】
　　　列Ａ　　　　　　Ｂ
行１　最終補充日　１５０日後
　２　****/**/**　****/**/**

セルA2　=DMAX(Sheet2!A1:C5,Sheet2!A1,Sheet3!A1:C2)
セルB2　=DATE(YEAR(A2)+0,MONTH(A2)+0,DAY(A2)+150)

● DMAX関数について

Sheet1のセルA2に入力したDMAX関数は、次の書式に基づきます。

DMAX(Database,フィールド,Criteria)

上記書式の各要素は、具体的には次のような内容です。

Database
　データベースを構成するセルの範囲
⇒ Sheet2!A1:C5

フィールド
　検索の対象となる、Database内の特定の列
⇒ Sheet2!A1
　（Sheet2の列Ａ、つまり「日付」の列を検索することになります。）

Criteria
　検索の条件を入力したセル範囲
⇒ Sheet3!A1:C2
　（この範囲に入力された「追加補充=1」(Sheet3のセルB2)が検索条件です。）

この結果、次のような順で検索・抽出されます。

⇒　１．日付を検索します。
⇒　２．「追加補充=1」（＝補充「有り」）の日付を抽出します。
⇒　３．２の日付で最大のものを返します。→　最終補充日

検索・抽出できるのは、「補充した日」だけです。
その日付（＝補充した日）のうち、直近のもの（＝最終補充日）だけが返されます。

●「（No.4の）回答に対するお礼」への回答

＞関数は正常に動作したんですけど、もしsheet2のB5を「０」にして無しにするとします。
＞そうすると、B2は「１」になっているので、A1の日付を抽出するのかと思ったのですが、抽出しないんですが…

ん？何か勘違いされているのでしょうか？
意味が伝わってきませんが。
上で、Sheet2のセルB5を「Ｏ」にして「追加補充無し」にするとします。
すると、Sheet2のセルB2だけが「１」になるので、セルA2の日付（2005/02/01）が返り、その日付がSheet1に抽出されます。
また、セルB5が「１」のときには、こちらはちゃんと「2005/04/15」が返り、その日付がSheet1に抽出されるはずです。

● 注意すべきポイント
検索・抽出できるのは、「補充“した”日」だけです。
その日付のうち、直近の日付だけが返されます。

「補充“しなかった”日のうち直近の日付」を返すことはできません（そういう目的で使うのではない、ということです。）。
また、もしも『「補充しなかった日」も考慮して、その日付を「補充した日」とくらべ、「補充した日」のほうがあとに来ている場合に限って直近の補充日の150日後を計算する』、などということを考えているとしたら、その場合にもこの式ではできません。

ひょっとして、実はそのようなことを考えておられるのでしょうか？
直近最終補充日およびその150日後を知りたいだけであれば、上の式で十分なはずなのですが。

この回答への補足

度々ありがとうございます。
何度もチャレンジしました。ですが、教えて頂いた数式ではどうしても直近の日付を抽出しませんでした。

＞Sheet2のセルB5を「Ｏ」にして「追加補充無し」にするとします～その日付がSheet1に抽出されるはずです。

仰る通りで、「追加補充有りの場合」だけでの直近の日付だけでいいのです。
ですが、教えて頂いた式では（DMAXを用いるセルには）どうやっても、2005/4/15だけしか抽出できませんでした。

代わりにsheet3のB2セルの「１」をやめてB3に「有り」と変更してみました。
そうすると、なぜだか私がまさに求めたかった計算が出来るようになりました。
どうして、出来るようになったのかは、わかりませんでした…

補足日時：2005/04/27 16:54

この回答へのお礼

この度は何度も、キャッチボールをさせてしまい、誠に申し訳ありませんでした。
他にもご回答いただけた皆様にも大変感謝しております。

お礼日時：2005/04/27 17:09

No.5 回答者： BLUEPIXY 回答日時： 2005/04/27 02:16

＃１＞#REF!となってしまいます。

変ですね？
こちらでは動くのですけど、
コピーして貼り付けでされました？
#REFになる可能性としては、
INDEXで指定した行位置が指定した範囲を超えているということです。
sheet2のＡ１：Ａ４という範囲を指定しているので、
行位置として指定できる範囲は、１～４（０は別）です。
なので、SUMPRODUCT(式)の部分を他のセルで
=SUMPRODUCT(式)
としてみて、１～４の値になるか調べてみてください。（０の場合は、"有り"がなかったということで、行全体が返りますが、普通のセルに範囲を入れた場合、最初のセルになります、ので、この場合は、2/1が返ります）
セルが結合されていることは、関係ないと思います。

No.4 回答者： kurikuri_maroon 回答日時： 2005/04/26 21:24

＃３の者です。

すみません(^^;)。ケアレスミスをしてしまったようです。
ほんとうに申し訳ありません。

正しくは次のとおりです。
Sheet2とsheet3を先に作りましょう。

Sheet3のセルB2の「有り」は「1」に変更して下さい。
Sheet3のその他の部分はそのままです。
また、Sheet2の全体もそのままです。
（但し、Sheet2では「1」か「0」で補充の有無を入力します。）

Sheet1の式に大きなミスがありました。
表はそのままですが、セルA2（元のB2は誤り）、セルB2（元のC2は誤り）の式はそれぞれ次のとおりです。

A2　=DMAX(Sheet2!A1:C5,Sheet2!A1,Sheet3!A1:C2)
B2　=DATE(YEAR(A2)+0,MONTH(A2)+0,DAY(A2)+150)

つまり、Sheet2にデータベースを作ります。
日付の昇順に並び替える必要はありません。但し、空行・空列・空白セルを作らないようにします。
セルA2の「Sheet2!A1:C5」がデータベースの範囲をあらわします。

同じく「Sheet2!A1」で抽出の対象となるフィールドを指定します。
データベースの中から「日付」を抽出するのですよ、ということを表計算ソフトに指示するわけですね。
また、「Sheet3!A1:C2」で検索条件を指定するわけですが、『「日付」を抽出するときに「追加補充」が「1」であるものを検索・抽出して下さい』ということなので、sheet3のように記入します。

で、最後に、Sheet1のセルA2にDMAX関数を用いて、抽出された日付のうち最大値のものを返すようにします。
すると、セルA2には、最終補充日が入ります。
この150日後が最終的に求めたい日になるわけですから、セルB2のように式を入れると、今度はきちんと計算されると思います。

自分で確認してみましたが、一応、ちゃんと動きました。
今度はどうでしょうか？ちょっと心配ですが(^^;)。

この回答へのお礼

＞「日付」を抽出するときに「追加補充」が「1」であるものを検索・抽出して下さい』ということ

関数は正常に動作したんですけど、もしsheet2のB5を「０」にして無しにするとします。
そうすると、B2は「１」になっているので、A1の日付を抽出するのかと思ったのですが、抽出しないんですが…

何度も回答してもらったのにすいません。ありがとうございました

お礼日時：2005/04/27 11:55

No.3 回答者： kurikuri_maroon 回答日時： 2005/04/26 17:56

Sheet1～Sheet3を使う

＜Sheet1＞
　　　Ａ　　　　　Ｂ
１　最終補充日　150日後
２　2005/4/15　2005/9/12

B2　=DMAX(Sheet2!A1:C5,Sheet2!A1,Sheet3!A1:C2)
C2　=DATE(YEAR(B2)+0,MONTH(B2)+0,DAY(B2)+150)

＜Sheet2＞
　　Ａ　　　　　Ｂ　　　　Ｃ
１　日付追加補充有無
２　2005/2/1　　　1　　　有り
３　2005/4/1　　　0　　　無し
４　2005/3/15　　0　　　　無し
５　2005/4/15　　1　　　　有り

C2　=IF(B2=1,"有り","無し")
（C3～C5へコピー）

＜Sheet3＞
　　　Ａ　　　　　Ｂ　　　　　　　Ｃ
１　日付　　　　追加補充　　　　有無
２　　　　　　　　有り

Sheet3まで使い、DMAX関数を応用してはいかがでしょうか？
sheet2をデータベースとして使い、Sheet3に抽出条件を記しておくのです。
Sheet1のDMAX関数で「有り」のうち日付の最大値を抽出するようになっていますから、それに単純に150日を足せば、目的の日にちが出ると思うのですが…。

この回答へのお礼

ありがとうございます。

ご教授いただいた式を当てはめてみたところ、「最終補充日」のＡ2に抽出される数値が「０」になってしまって、日付に150日をプラスしても、1900年の5月29日になってしまいました…
やり方が悪いのでしょうか、教えて頂いたのに申し訳ありません。

お礼日時：2005/04/26 18:58

No.2 回答者： taocat 回答日時： 2005/04/26 15:24

こんにちは。

一般的には、BLUEPIXYさんの仰るとおりなんですが、今回の場合は最大の日付となってますので、Matchで可能かも知れません。

但し、質問者はMatchの基本的な部分で間違っています。
提示のMatch関数では「照合の型」が無いですよね。
ここでヘルプを見てみましょう。

-----------------------------------------------
●照合の型 を省略すると、1 であると見なされます

●照合の型 に 1 を指定すると、検査値以下の最大の値が検索されます。このとき 検査範囲 は、-2、-1、0、1、2、...、A、...、Z、...、ア、...、ン、...、FALSE、TRUE のように昇順で並べ替えられておく必要があります。
-----------------------------------------------

これでお分かりのように提示のMatchでは照合の型が省略されているにも拘らず、検査範囲の値は「昇順」になってないので、Matchで求められるものは不確かなものになるいうことです。

比較すると、「有り＞無し」になりますので、提示のSheet2のデータは

　　 　　　Ａ　　　　　Ｂ
　　　　　日付　　　追加補充　
１｜　05/3/15 ｜　　無し　　｜
２｜　05/4/1　｜　　無し　　｜
３｜　05/2/1　｜　　有り　　｜
４｜　05/4/15 ｜　　有り　　｜

となっていなければなりません。
ですから、追加補充を第１キー、日付を第２キーにして、昇順でソートしてやれば上手く動作するのではないでしょうか。

外しましたらご容赦願います。
以上です。

この回答へのお礼

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

ただ、データの昇順は行わないようにしたかったのです。
データを並び替えて、教えて頂いた式を当てはめたところ、私の求めたかった式になりました。

お礼日時：2005/04/26 18:52

No.1 回答者： BLUEPIXY 回答日時： 2005/04/26 14:32

このような、同じ値が複数でるような場合（有りが複数ある）質問文のような用途でMATCHは使えません。

替わりに、
=INDEX(Sheet2!A1:A4,SUMPRODUCT(MAX((Sheet2!B1:B4="有り")*ROW(Sheet2!B1:B4))))
のようにしてみて下さい。

この回答への補足

素早いご回答、誠にありがとうございます。

教えていただいた式をコピーして実際に当てはめてみたのですが、#REF!となってしまいます。

試行錯誤してみましたが、原因がどうしてもわかりませんでした。

(1)B1～B4セルにはデータ入力規則で有り・無しを選択できるように設定しています。
(2)B1～B4セルは結合セルとなっております。ただ、式にはそのままB1:B4と範囲を決定しました。

補足日時：2005/04/26 18:03