[[20100713211843]]　『INDEX関数での検索について』（ぶちとら）　ページの最後に飛ぶ

『INDEX関数での検索について』（ぶちとら）

 はじめまして、複数条件検索と抽出について質問させて下さい。
 次の様な「在庫.scv」から条件にあったデータを検索・抽出し
 「管理元帳.xls」Ｂ３、Ｂ４、Ｂ５とＤ３、Ｄ４、Ｄ５に
 出庫数を反映させたいと考えています。

 【出庫.csv】
 　　　 Ａ　　　　 Ｂ　　　　Ｃ　　　 Ｄ
 １　 出庫日　　部門ＣＤ　食材ＣＤ　出庫数
 ２　2010/7/1　　　 0　　　10015　　　10
 ３　2010/7/3　　　 0　　　10015　　　 8
 ４　2010/7/3　　　 0　　　10018　　　 6
 ５　2010/7/3　　　 0　　　10021　　　13
 ６　2010/7/5　　　 0　　　10007　　　21
 ７　2010/7/5　　　 0　　　10015　　　 9
 ８　2010/7/6　　　 0　　　10018　　　14

 【管理元帳.xls】
 　　　Ａ　　　　　Ｂ　　　　Ｃ　　　 Ｄ
 １　2010/7/3　 （空白）　2010/7/5 （空白）
 ２　食材ＣＤ　　出庫数　 食材ＣＤ　出庫数
 ３　 10018　　　　　　　　10007
 ４　 10021　　　　　　　　10018
 ５　 10156　　　　　　　　10029

 まずは管理元帳.xlsのB3に「Ａ１の日付とＡ３の食材ＣＤ」を元に
 出庫.csvにある「Ｄ４の出庫数」を検索し反映させたいのです。
 管理元帳.xlsのB5やD4,D5は出庫.csvにデータが無いので出来れば空白にしたいです。
 ちなみに出庫.CSVはシステムから取得するので加工出来ないルールです。
 管理元帳.xlsはシートの追加はOKですが上記のフォーマットは変更できません。

 私が記入した関数は次の通りです。
 管理元帳.xlsのＢ３に「=INDEX([出庫.csv]Sheet1!$D$2:$D$8,MATCH(A1&A3,
 INDEX([出庫.csv]Sheet1!$A$2:$A$8&[出庫.csv]Sheet1!$C$2:$C$8,),0))」

 INDEX関数自体にこだわりはありませんが、処理は関数で行いたいので
 上記のおかしい所のご指摘、またはその他のより良い方法などがあれば
 ぜひとも教えて頂けませんでしょうか。勉強不足でお恥ずかしい限りですが
 どうぞよろしくお願い致します。

 OS:Windows XP使用
 Excel2000使用

Sheet1が【出庫.csv】データ、Sheet2が【管理元帳.xls】として、

 Sheet2のB3セルに
 =SUMPRODUCT((Sheet1!$A$2:$A$8=Sheet2!A$1)*(Sheet1!$C$2:$C$8=Sheet2!A3)*(Sheet1!$D$2:$D$8))
 これを下へフィルコピー、
 D3にもコピーして下へフィルコピーで表示できました。

 SUMPRODUCT関数を確認してみるといいと思います。
 たとえば
　http://officetanaka.net/excel/function/function/sumproduct.htm

 ＜追加＞これもわかりやすいかも？
　http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/sumproduct.htm

 （こだぬき）

 書いている間にこだぬきさんからご回答がありましたが、せっかく書いたので挙げておきます。
 ----------
 こんにちは。
まず、ご提示の数式のおかしい点です。
「=INDEX([出庫.csv]Sheet1!$D$2:$D$8,MATCH(A1&A3,INDEX([出庫.csv]Sheet1!$A$2:$A$8&[出庫.csv]Sheet1!$C$2:$C$8,),0))」
                                          ~~
上の~~の部分は、「2010/7/3」という日付を参照するのですが、この書き方ですと、数式を下にコピーしたときに、
A2&A4,A3&A5,,,のように、ずれていってしまいます。
なので、この部分はA$1として、行番号がずれないようにする必要があります。

 以下は、上の修正も含めて、段階的に修正したものです。
（便宜上、同シートのSheet2にあるものとしています）

 1. 上記の修正後です。
  B3 =INDEX(Sheet1!$D$2:$D$8,MATCH(A$1&A3,INDEX(Sheet1!$A$2:$A$8&Sheet1!$C$2:$C$8,),0)) →B3:B5フィルコピー
　※D列以降は、B列の数式をそのままコピーすればOKです。

 2. A$1&A3として、そのまま連結すると、「4036210018」（40362が日付）のように全て数字となって分かりづらいので、
　　間に"-"を挿入します。
  B3 =INDEX(Sheet1!$D$2:$D$8,MATCH(A$1&"-"&A3,INDEX(Sheet1!$A$2:$A$8&"-"&Sheet1!$C$2:$C$8,),0)) →B3:B5フィルコピー

 3. 該当データがないと、#N/Aエラーになるので、IFとISERROR関数で分岐します。
  B3 =IF(ISERROR(MATCH(A$1&"-"&A3,INDEX(Sheet1!$A$2:$A$8&"-"&Sheet1!$C$2:$C$8,),0)),"",
         INDEX(Sheet1!$D$2:$D$8,MATCH(A$1&"-"&A3,INDEX(Sheet1!$A$2:$A$8&"-"&Sheet1!$C$2:$C$8,),0))) →B3:B5フィルコピー

 4. 上まででご希望の結果になると思いますが、もう少し簡潔な数式を作ってみました。
　　複数条件の検索で、出力データが数値なので、以下のようなSUMPRODUCT関数で対応できます。
　　この数式だと、該当データがない場合には、エラーではなく0が返りますので、3.のような
　　エラー対策をしなくても、表示形式「G/標準;;」やツールオプションの0非表示などでいいかと
　　思います。
  B3 =SUMPRODUCT(Sheet1!$D$2:$D$8*(Sheet1!$A$2:$A$8=A$1)*(Sheet1!$C$2:$C$8=A3)) →B3:B5フィルコピー

 ○【Sheet2】シート
  ※「_」セルは未入力または""
  [R/C]       [A]       [B]       [C]       [D]
   [1]   2010/7/3  （空白）  2010/7/5  （空白）
   [2]   食材ＣＤ    出庫数  食材ＣＤ    出庫数
   [3]      10018         6     10007        21
   [4]      10021        13     10018         0
   [5]      10156         0     10029         0

 （コタ）

 お返事が遅くなり申し訳ありませんでした。

 こだぬきさんへ
 　回答だけではなく分かりやすい説明があるサイトまで親切に教えていただき有難うございます。
 　SUMPRODUCTを紹介頂いたサイトで確認しました。とても使い勝手のよさそうな関数で感激です。
 　同時に勉強不足も痛感しました…。もっと精進しますが行き詰った時はまたお願いします。

 コタさんへ
 　私のINDEX関数に対する修正回答とその後の段階的で分かりやすい説明、有難うございます。
 　さらにはSUMPRODUCT関数による簡素な数式まで教えて頂き、同じ関数でもこだぬきさんや
 　コタさんの様に色々なアプローチがあるのが分かりました。細やかな説明が目から鱗でした。

 お二人に教えていただいた関数で私の望んでいる結果をエクセルで実現され
 管理元帳のメンテナンスが今までに無いほどに早く楽になりそうです。
 言葉では表せないほどに感謝しております、本当に有難うございました。

 （ぶちとら）

コメント返信：

[ 一覧(最新更新順) ]

[[20100713211843]] 『INDEX関数での検索について』（ぶちとら） ページの最後に飛ぶ

[[20100713211843]]　『INDEX関数での検索について』（ぶちとら）　ページの最後に飛ぶ