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

解決済みの質問

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

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

エクセル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から下に表示

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

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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2010-10-16 21:39:07

QNo.6254784

すぐに回答ほしいです

質問者が選んだベストアンサー

またまたお邪魔します。
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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2010-10-17 18:56:50

お礼

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

いつもすごいですね。

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

投稿日時 - 2010-10-18 19:08:34

ANo.4

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

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

回答(4)

ANo.3

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

投稿日時 - 2010-10-17 13:11:19

補足

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

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

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

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

M1以下の数式は以下のような感じでいいのでしょうか。
=IF(COUNT(Sheet1!$I:$I)<ROW(b1),"",INDEX(Sheet1!$A:$H,SMALL(Sheet1!$I:$I,ROW(b1)),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)
の()の中の意味が難しくわかりませんでした。

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

投稿日時 - 2010-10-17 14:13:51

ANo.2

数式と一般機能にて


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

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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2010-10-17 07:18:14

ANo.1

こんばんは!
参考になるかどうか判りませんが・・・
一例です。
↓の画像(小さくて見えにくいかもしれません)のように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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2010-10-16 23:35:08

補足

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

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

なぜでしょうか。

投稿日時 - 2010-10-17 07:32:48

あなたにオススメの質問