データベースとデータの抽出について

質問

データベースとデータの抽出について

エクセル2003で以下のような、表を作っています。

データは現在、A列に10000万件程度入っています。
4列目の購入日付データの年、月、日がそれぞれのセル（D2、E2、F2）
に分割入力されています。

このデータベースから、氏名と購入日付を基準キー
にして、顧客NOと購入日付（1つのセルに結合）、
購入商品を抽出し、シート2に転記していきたいです。

抽出方法は、シート2に以下のような形で検索　
表示したいです。

（シート2）
氏名（B1に表示）→C1に入力
抽出開始年月日（D1に表示）→年はE1、月はG1、日はI1に入力　
抽出終了年月日（D2に表示）→年はE2、月はG2、日はI2に入力

検索後、表示される顧客NOと購入日付、購入商品は
同じシート2の以下のセルに表示したいです。

顧客NO（L1に表示）→L2から下に表示
購入日付（M1に表示）→M2から下に表示
購入商品（N1に表示）→N2から下に表示

したいです。
日付が分割入力されているため、抽出がうまくできません。
氏名、または名字だけでうまく検索できるコードはあるでしょうか。

どうぞよろしくお願いします。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.4

tom04 · ベストアンサー

またまたお邪魔します。
Sheet2に氏名の列を追加したいということですので・・・

前回のSheet2の表はそのまま利用し、M列すべてを選択し、（M列すべてを範囲指定、列番号の「M」上で右クリック）→　挿入　
これで以前のM列以降がそれぞれ右側に1列ずつずれますので、
M1セルに　氏名　と入力し、前回のL2に入れた数式をそのままもう一度列方向と行方向にオートフィルでコピーすると
↓のような感じになります。

前回の数式は列・行方向にオートフィルでコピーするだけで対応できるようにしていましたので
氏名の項目を増やすだけで大丈夫だと思います。

尚、すべての列に数式を入れていらっしゃるみたいですが、それは必要ありません。
オートフィルで列方向にコピーすると数式内の　
ROW（A1)部が　→　ROW（B1）　→　ROW（C1)・・・　のように変化しますが
これらはそれぞれの行番号を返していますので、すべて「1」ということになります。
これを↓（行方向）にコピーすると、ROW（A2）=2　ROW（A3)=3　・・・
という具合に変化するだけで行方向に関しては「1」ずつ増えたものを返すようになります。

結局L2に入れた数式
=IF(COUNT(Sheet1!$I$1:$I$10000)<ROW(A1),"",INDEX(Sheet1!$A$1:$H$10000,SMALL(Sheet1!$I$1:$I$10000,ROW(A1)),MATCH(L$1,Sheet1!$A$1:$H$1,0)))
の説明としては
IF(COUNT(Sheet1!$I$1:$I$10000)<ROW(A1),"",
はSheet1のI2～I10000セルの数値の数よりオートフィルした行数が大きい場合は空白に！
という単純にエラー処理です。Sheet1の数値個数以降の行は空白にします。

次に後半の
INDEX(Sheet1!$A$1:$H$10000,SMALL(Sheet1!$I$1:$I$10000,ROW(A1)),MATCH(L$1,Sheet1!$A$1:$H$1,0)))　
に関してはINDEX関数でSheet1のA1～H10000セルを範囲指定した表内で
行はSheet1のI1～I10000セルの数値で小さい順に行方向のデータを返す！

列はSheet2のL1（複合参照で列方向だけオートフィルで移動しますが、行方向は固定）を参照し、
Sheet1のA1～H1セルに入力してあるものと一致する列番号を返す！
という数式です。

以上、長々と書いてしまいましたが
参考になりましたかね？m(__)m

お礼

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

いつもすごいですね。

また、お願いします。勉強しますね。

回答No.3

tom04

No.1です！
補足を読ませていただきました。

Excel2003をお使いだということですので、
前回の数式は列すべてを範囲指定する数式でしたので、Excelの方で最終行の「65536」行目を自動的に指定したみたいですね！
補足の数式でも問題なく表示されるはずですが・・・

質問ではデータ量が10000程度ということなので
Sheet2のL2セルの数式を

=IF(COUNT(Sheet1!$I$1:$I$10000)<ROW(A1),"",INDEX(Sheet1!$A$1:$H$10000,SMALL(Sheet1!$I$1:$I$10000,ROW(A1)),MATCH(L$1,Sheet1!$A$1:$H$1,0)))
としてみてください。
（数式内の10000はデータ量によって15000や20000に変更しても大丈夫です）

たぶん大丈夫だと思いますよ。m(__)m

補足

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

あと、シート2の表示を以下のように変更したいのですが。

顧客NO（L1に表示）→L2から下に表示
氏名（M1に表示）→L2から下に表示
購入日付（N1に表示）→M2から下に表示
購入商品（O1に表示）→N2から下に表示

氏名を追加したいのです。

M1以下の数式は以下のような感じでいいのでしょうか。
=IF(COUNT(Sheet1!$I:$I)<ROW(ｂ1),"",INDEX(Sheet1!$A:$H,SMALL(Sheet1!$I:$I,ROW(ｂ1)),MATCH(M$1,Sheet1!$A$1:$H$1,0)))

N1
=IF(COUNT(Sheet1!$I:$I)<ROW(C1),"",INDEX(Sheet1!$A:$H,SMALL(Sheet1!$I:$I,ROW(C1)),MATCH(N$1,Sheet1!$A$1:$H$1,0)))

O1
=IF(COUNT(Sheet1!$I:$I)<ROW(D1),"",INDEX(Sheet1!$A:$H,SMALL(Sheet1!$I:$I,ROW(D1)),MATCH(O$1,Sheet1!$A$1:$H$1,0)))

tom04さんの数式のROW（A1）、ROW（B1）、ROW（C1）
の（）の中の意味が難しくわかりませんでした。

いつもありがとうございます。
よろしくお願いします。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

数式と一般機能にて

Sheet1!H2セルに
=DATEVALUE("H"&D2&"."&E2&"."&F2)
フィルハンドルダブルクリック
(平成としています、2月30日などはエラーが出ます)

Sheet2!B4セル 氏名
Sheet2!B5セル ="*"&C1&"*"

Sheet2!C4セル 日付
Sheet2!C5セル =IF(I1="",">0",">=H"&E1&"."&G1&"."&I1)

Sheet2!D4セル 日付
Sheet2!D5セル =IF(I2="",">0","<=H"&E2&"."&G2&"."&I2)

Sheet2!L1セル No.
Sheet2!M1セル 日付
Sheet2!N1セル 商品

Sheet2において、データ - フィルタ - フィルタオプションの設定
●指定した範囲
リスト範囲 Sheet1!$A$1:$H$10000
検索条件範囲 $B$4:$D$5
抽出範囲 $L$1:$N$1

以上です。
(適当に簡略化していますので画像を見て変更してください)

回答No.1

tom04

こんばんは！
参考になるかどうか判りませんが・・・
一例です。
↓の画像（小さくて見えにくいかもしれません）のようにSheet1に作業用の列を2列設けています。
日付が分割されていますが、シリアル値にした方が扱いやすいと思いますので、
すべてシリアル値にして抽出するようにしてみました。

Sheet1の作業列H列の項目を「購入日付」としています。
（これはSheet2に抽出する場合に検索項目とするためです）

H2セルに
=IF(A2="","",DATE(D2+1988,E2,F2))

作業列I2セルに
=IF(OR(B2="",COUNTBLANK(Sheet2!$C$1:$I$1)),"",IF(AND(ISNUMBER(FIND(Sheet2!$C$1,B2)),H2>=DATE(Sheet2!$E$1+1988,Sheet2!$G$1,Sheet2!$I$1),H2<=DATE(Sheet2!$E$2+1988,Sheet2!$G$2,Sheet2!$I$2)),ROW(),""))

という数式をいれ、H2・I2セルを範囲指定し、I2セルのフィルハンドルでダブルクリック、またはオートフィルで下へずぃ～～～！っとコピーします。

そして余計なお世話かも知れませんが、Sheet2のC1セルに一文字でも入力するとその文字が含まれる氏名の人を抽出するようにしています。

Sheet2のL2セルに
=IF(COUNT(Sheet1!$I:$I)<ROW(A1),"",INDEX(Sheet1!$A:$H,SMALL(Sheet1!$I:$I,ROW(A1)),MATCH(L$1,Sheet1!$A$1:$H$1,0)))

という数式をいれ、オートフィルで列方向と行方向にコピーすると
画像のような感じになります。
尚、Sheet1・Sheet2共に、日付の列はシリアル値が表示されると思いますので、
セルの表示形式から好みの日付を選択してください。

以上、参考になれば良いのですが・・・m(__)m

補足

tom04さん、完ぺきでした。

一つ、L2に数式をコピーした際、
=IF(COUNT(Sheet1!$I$1:$I$65536)<ROW(A1),"",INDEX(Sheet1!$A$1:$H$65536,SMALL(Sheet1!$I$1:$I$65536,ROW(A1)),MATCH(L$1,Sheet1!$A$1:$H$1,0)))

という数式になってしまい、うまく値が表示できませんでした。

なぜでしょうか。