【EXCEL2003】DBで抽出したデータを別シートに貼り付けたい

質問

少しでも内容を理解していただきたいと思い、画像を添付します。
分かりやすいかと思って色をつけてみました。

データベースで日付（シリアル値）・店番・売上・客数を抽出したものを貼り付けます（Sheet1）。
すると、予め作成済みのSheet2、Sheet3、Sheet4・・・とそれぞれ店番ごとに分けたシートに売上や客数の数値が日毎に貼り付けられるようにしたいのです。

このデータを毎月作ると、30日の月があったり31日の月があったりします。
データベースは、小の月であっても31日を空白にできない（やり方を知らないだけですが）のが不都合です。
VLOOKUPで出来そうなのですが、知恵が沸きません。

ご指導お願いします。

ちなみに、店番は５０くらいありますのでSheet1は50店ｘ31日分の長い行数になります。

回答No.3

tom04 · ベストアンサー

こんばんは！
色々方法はあるかと思いますが・・・
一例です。
↓の画像で説明させていただきます。
店舗数が50くらいあるということはSheet2～Sheet52前後あるということですよね？
一応そういうことだとしての方法です。

まずSheet1に作業用の列を設けています。
Sheet1のE2セルに
=A2&B2
としてオートフィルで下へずぃ～～～！っとコピーします。
このデータを元に各Sheetに表示させるようにします。

まず、Sheet2～最後のSheetに同じ数式を入れますので
画面下のSheet2の見出しをクリックします。
そして、Shiftキーを押しながら最後のSheet見出しをクリックします。
これでSheet2～最後のSheetが作業グループ化されますので
Sheet2のみに数式を入力すれば全てのSheetに同じ数式が入ります。

今回はSheet2だけの方法になります。
Sheet2のA3セルに
=IF(MONTH(Sheet1!$A$2)=MONTH(DATE(YEAR(Sheet1!$A$2),MONTH(Sheet1!$A$2),ROW(A1))),DATE(YEAR(Sheet1!$A$2),MONTH(Sheet1!$A$2),ROW(A1)),"")
A3セルの表示形式はユーザー定義から　d　としておきます。

B3セルに
=IF(ISERROR(INDEX(Sheet1!C$2:C$10000,MATCH($A3&$A$1,Sheet1!$E$2:$E$10000,0))),"",INDEX(Sheet1!C$2:C$10000,MATCH($A3&$A$1,Sheet1!$E$2:$E$10000,0)))
としてB3セルの数式を隣のC3セルまでコピーします。
最後にA3～C3セルを範囲指定し、C3セルのフィルハンドルで
下へ31日目までオートフィルでコピーします。

最後のSheet見出し上で右クリック　→　「作業グループ解除」を選択して完了です。

これで各SheetのA1セルに店番を入力していけば
それぞれのSheetに振り分けられると思います。
そして、日付に関しても大の月・小の月に関係なく月末までの表が出来るはずです。

以上、長々と書いてしまいました。
参考になれば良いのですが
他に良い寳保があれば読み流してくださいね。m(__)m

お礼

大変お手数をおかけして申し訳ございませんでした。
今回は、この方法を使って対処することにしました。

日付に関しては、Ａ列を増やし以下の項目追加することで対処します。
=DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))

若干戸惑いながらも目的の物を作ることが出来ました。
ありがとうございました。

補足

ダメだ。Sheet2のA3セルに#NUM!が出る・・・

回答No.6

tom04

No.3・4です！
たびたびお邪魔しますｒ。

日付の列にエラーが出てしまいましたか！
それではワンクッション入れる方法になります。
各SheetのC1セルに
=YEAR(Sheet1!$A$2)
D1セルに
=MONTH(Sheet1!$A$2)
としてみてください。そしてこのデータで日付を表示させるようにします。
各SheetA3セルに
=IF(MONTH(DATE($C$1,$D$1,ROW(A1)))=$D$1,DATE($C$1,$D$1,ROW(A1)),"")
という数式を入れオートフィルで下へコピーしてみてください。
今度はたぶんエラーにならないと思います。

もしこれでもダメならごめんなさいね。

どうも何度も失礼しました。m(__)m

補足

どうもありがとうございます。

Sheet1!$A$4が日付として認識されていないのが原因のようです。
2010/03/01と入力しなおし日付として扱うとちゃんできました。
いろいろと難しいですね・・・

回答No.5

keithin

＞暦に大の月小の月、うるう年がなければ「＝Sheet1!C4」で貼り付ければ良いのです。
＞しかし、それだと5/1に4/31部分が入ってしまうのです。

単に大の月小の月の月末日処理だけが問題なのでしたら
29日のセル：
=IF(MONTH(Sheet1!C4)=MONTH(Sheet1!C4+28),Sheet1!C32,"")
30,31日のセルにこのままコピー

29日のデータセル：
=IF(上述29日のセル="","",Sheet1!E32)
30,31日のセルにこのままコピー

のように。
何も特に難しくする必要はありません。

回答No.4

tom04

No.3です！
たびたびごめんなさい。

投稿した後で気になったので再び顔を出してしまいました。
前回の方法は日付と店番の重複はないものとしています。
（Sheet1のデータは、各店1日1行という前提です）

もし重複がありその集計を求めたいのであれば
他の方法を考えないといけなくなります。

どうも何度も失礼しました。m(__)m

回答No.2

keithin

あるいは実際のシート１が，もし本当にご相談で掲示された事例のように
１．店舗ごとに既に集めて固めてあって
２．日付ごとにも，すでに１行に合計済みで
３．店舗ごとに日付の昇順に並べ替え済みで
４．１日から月末まで１日の抜けも無く数字が埋まっている
のように良いことずくめで出来ているのなら，それを前提にして遙かに簡単な軽い数式で各シートのデータを持ってこさせることも出来ます。

無駄に条件を厳しく（汎用的に）して結局使えない式になるよりも，例えば上述のような運用条件を整理して合理的な計算式を工夫してみてください。そのためにも「本当のシート１の姿」について，もっと事実に基づいて情報提供して，またご相談を投稿なさってみてください。

補足

Sheet1に関してはその通りです。

SQLのselect文で引っ張ってきたデータです。
並び順は優先度を日付・店番の順にしています。
ですから、添付画像のまんまになります。

暦に大の月小の月、うるう年がなければ「＝Sheet1!C4」で貼り付ければ良いのです。
しかし、それだと5/1に4/31部分が入ってしまうのです。
SQLで4/31に空白が入るようにしても良いのですが、それが出来ないのでエクセルの方で対処しようと考えた次第です。

回答No.1

keithin

＞Sheet1は50店ｘ31日分の長い行数になります。

その程度の行数で済んでいるのでしたら

店舗１の当年当月１日の売上：
=SUMPRODUCT((Sheet1!$A$1:$A$2000=DATE(当年，当月，F4)*($B$1:$B$2000=店舗番号), Sheet1!$C$1:$C$2000)

のようで，何とか耐えられる範囲と思います。実際にパソコンに全部作成してみて，どのくらい「計算が重たくなるか」確認して使ってください。耐えられないほど重いか，そうでなくとも今の倍以上に行数が増えるようなら，この方法は使わないことをお奨めします。

各店舗シートの「店番号」「何年の集計をしたいのか」「何月なのか」は，それぞれのシートにあなたが作成したレイアウトのセルの番地を入れて作成してください。

＃別の手
シート１のE列に
=B4 & FORMAT(A4,"-yyyymmdd")
などのように「店舗番号－年月日」で結合したデータを計算させます。

各シートではごく簡単な
=SUMIF(店番号&FORMAT(DATE(当年，当月，F4),"yyyymmdd"), Sheet1!E:E,Sheet1!C:C)
のようにして，SUMPRODUCTなどより遙かに軽く計算できます。