• ベストアンサー

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

少しでも内容を理解していただきたいと思い、画像を添付します。 分かりやすいかと思って色をつけてみました。 データベースで日付(シリアル値)・店番・売上・客数を抽出したものを貼り付けます(Sheet1)。 すると、予め作成済みのSheet2、Sheet3、Sheet4・・・とそれぞれ店番ごとに分けたシートに売上や客数の数値が日毎に貼り付けられるようにしたいのです。 このデータを毎月作ると、30日の月があったり31日の月があったりします。 データベースは、小の月であっても31日を空白にできない(やり方を知らないだけですが)のが不都合です。 VLOOKUPで出来そうなのですが、知恵が沸きません。 ご指導お願いします。 ちなみに、店番は50くらいありますのでSheet1は50店x31日分の長い行数になります。

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

  • ベストアンサー
  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.3

こんばんは! 色々方法はあるかと思いますが・・・ 一例です。 ↓の画像で説明させていただきます。 店舗数が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

jiyoun
質問者

お礼

大変お手数をおかけして申し訳ございませんでした。 今回は、この方法を使って対処することにしました。 日付に関しては、A列を増やし以下の項目追加することで対処します。 =DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2)) 若干戸惑いながらも目的の物を作ることが出来ました。 ありがとうございました。

jiyoun
質問者

補足

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

その他の回答 (5)

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.6

No.3・4です! たびたびお邪魔しますr。 日付の列にエラーが出てしまいましたか! それではワンクッション入れる方法になります。 各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

jiyoun
質問者

補足

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

  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.5

>暦に大の月小の月、うるう年がなければ「=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日のセルにこのままコピー のように。 何も特に難しくする必要はありません。

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.4

No.3です! たびたびごめんなさい。 投稿した後で気になったので再び顔を出してしまいました。 前回の方法は日付と店番の重複はないものとしています。 (Sheet1のデータは、各店1日1行という前提です) もし重複がありその集計を求めたいのであれば 他の方法を考えないといけなくなります。 どうも何度も失礼しました。m(__)m

  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.2

あるいは実際のシート1が,もし本当にご相談で掲示された事例のように 1.店舗ごとに既に集めて固めてあって 2.日付ごとにも,すでに1行に合計済みで 3.店舗ごとに日付の昇順に並べ替え済みで 4.1日から月末まで1日の抜けも無く数字が埋まっている のように良いことずくめで出来ているのなら,それを前提にして遙かに簡単な軽い数式で各シートのデータを持ってこさせることも出来ます。 無駄に条件を厳しく(汎用的に)して結局使えない式になるよりも,例えば上述のような運用条件を整理して合理的な計算式を工夫してみてください。そのためにも「本当のシート1の姿」について,もっと事実に基づいて情報提供して,またご相談を投稿なさってみてください。

jiyoun
質問者

補足

Sheet1に関してはその通りです。 SQLのselect文で引っ張ってきたデータです。 並び順は優先度を日付・店番の順にしています。 ですから、添付画像のまんまになります。 暦に大の月小の月、うるう年がなければ「=Sheet1!C4」で貼り付ければ良いのです。 しかし、それだと5/1に4/31部分が入ってしまうのです。 SQLで4/31に空白が入るようにしても良いのですが、それが出来ないのでエクセルの方で対処しようと考えた次第です。

  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.1

>Sheet1は50店x31日分の長い行数になります。 その程度の行数で済んでいるのでしたら 店舗1の当年当月1日の売上: =SUMPRODUCT((Sheet1!$A$1:$A$2000=DATE(当年,当月,F4)*($B$1:$B$2000=店舗番号), Sheet1!$C$1:$C$2000) のようで,何とか耐えられる範囲と思います。実際にパソコンに全部作成してみて,どのくらい「計算が重たくなるか」確認して使ってください。耐えられないほど重いか,そうでなくとも今の倍以上に行数が増えるようなら,この方法は使わないことをお奨めします。 各店舗シートの「店番号」「何年の集計をしたいのか」「何月なのか」は,それぞれのシートにあなたが作成したレイアウトのセルの番地を入れて作成してください。 #別の手 シート1のE列に =B4 & FORMAT(A4,"-yyyymmdd") などのように「店舗番号-年月日」で結合したデータを計算させます。 各シートではごく簡単な =SUMIF(店番号&FORMAT(DATE(当年,当月,F4),"yyyymmdd"), Sheet1!E:E,Sheet1!C:C) のようにして,SUMPRODUCTなどより遙かに軽く計算できます。

関連するQ&A

  • 指定日までの売上合計を別シートに抽出したい。

    指定日までの売上合計を別シートに抽出したい。 添付図左側(Sheet1)に指定した日までの売上合計を出したいのです。 Sheet2でA店の売上と客数を入力していきます。 Sheet3はB店、Sheet4はC店、・・・・と続き、それぞれに売上と客数を入力します。 Sheet1のB3に「15」(2010/5/15の意)と入力します。 すると、C5に1日~15日までの合計売上金額が抽出されるようにしたいのです。 B3の表示形式は、ユーザー定義で「d」にしています。 どうかよろしくお願いいたします。

  • エクセル 関数 別シートへの抽出

    エクセル関数について質問します。 「数値の入ったセルだけを、その行ごと丸々別シートに抽出する方法」 について教えてください。 例、C列に空白セルと1、2や3など、数値の入ったセルがあります。空白セル以外の数値の入ったセルを、その行ごと別シートに抽出したい。 C3とC5に数値が入ってる→3行目と5行目を別シートに抽出する。 このようなことは関数でできるのでしょうか? マクロでないと無理でしょうか? お力をお貸しください。 よろしくお願いします。

  • Excelでデータを抽出

    エクセルでデータベースを作成しました。 このデータベースから目的のものだけを別のシートに抽出したいです。 データベース A ID1 B ID2 C 内容 この内容が100行書かれているとします。 ある20個のIDの内容だけを別のシートに抽出したいのです。 ID1を昇順にならべるとID2は昇順にならないようなIDになっています。 なので、VLOOKUPがつかえません。 ID1から抽出したいもの20個を選んで、シート2へ選んだ内容のID1と内容を抽出 ID2から抽出したいものを20個選んで、シート3へ選んだ内容のID2と内容を抽出 どの様にしたら出来ますか? うまく説明できなくてすみません。 宜しくお願いします。

  • 複数のシートからデータを抽出する。集金管理表作成。

    エクセルの質問です。2003を使っております。初心者なので行き詰ってしまいました。教えてください!下記の事はできるのでしょうか? 毎月ごとにシートが分かれております。             7月(シートA)             8月(シート2)               (新規シート) NO>客名>売上>入金  NO>客名>売上>入金       7月分                                           8月分  NO、客名は、変わる事はありません。それぞれのシートから新規のシートに一人のお客のデータだけ抽出できるのでしょうか?  売上の管理表みたいなものですのでシートは毎月分増えていきます。 新規シートに抽出したいのは、毎月の一人のお客データのみだけです。 毎月の客数は約300件程度です。 売上+入金等のチェックで使うことが目的です。 <1枚のシートに7月+8月+9月~と縦に並べてオートフィルタを掛ければ出来るのですが、複数のシートから同じような事は出来るのでしょうか?> 説明不足だとは思いますが、どなたか回答を宜しくお願い致します。

  • データの抽出方法

    エクセルでデータの抽出を行いたいです。 シートが、「9月売上商品個数」(シート1)、「10月売上商品個数」(シート2)、「商品データベース ※20品目のみ ※JAN入力済」(シート3)の3枚あります。シート1と2の中から、シート3に入力されている20品目だけの商品をJAN(これは必ずJANとさせてください)で抽出し、シート3内の商品のみが9月と10月でどれほど売れたかのデータを作りたいです。 欲を言うと、シート3の商品の並び順と、シート1と2の商品の並び順は異なります。シート3のJANの横に「9月」と「10月」の列を作るので、そこに一括で個数が入るととても便利なのですが.... わかりづらく申し訳ありません。アドバイスいただければ幸いです。

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

    データベースとデータの抽出について エクセル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から下に表示 したいです。 日付が分割入力されているため、抽出がうまくできません。 氏名、または名字だけでうまく検索できるコードはあるでしょうか。 どうぞよろしくお願いします。

  • データ抽出について

    エクセル初心者です。 エクセル2003を使っています。 sheet1に元データとなる表があります。 ・データセル内は数値データです。 ・行方向へは200程度 ・列方向へは1500程あります。  A   B    C    D   E     F  1日付 項目1 項目2 項目3 項目4 項目5 ・・・ 2 3 4 5 6 この元データからsheet2へ日付をキーにして行データを抽出したいのです。 抽出先のsheet2のA列に予め目的の日付データのみ1200程入っています。 最初Vlookupでやり始めたんですが無理と分かりました。 フィルタオプションで試みたのですが、「検索条件範囲」の式の書き方が 分からず困っております。 OKWaveの関連したQAを参考に試行錯誤を続けていましたが、進展がありません。 お詳しい方よりのご回答をお待ちしております。

  • 複数のシートからデータを抽出し別シートに纏める

    業務で毎日エクセルを使っています。 予め用意された表にデータを入力し、決まった時間にメールに添付して報告しています。 月に一度、毎日入力したデータを別のシートに纏めるのですが、1日分ずつコピペしており、マクロを使って時間短縮できないかと考えるようになりました。 なにぶん最近マクロという言葉を覚えたのでマクロを組むにまでは至っておりません。 どなたか良いアドバイスをいただければと思い、質問させていただきました。 【前提】 業務で使うPCはWindowsXPでExcelは2003です。 入力する表は決まった形式であり、セルの位置も同じです。 1日最大で5シート程使いますが、日によっては1シートの時も2シートの時もあります。 毎報告時に表が全て埋まることはなく、さっき報告した時は5行だったけど次の報告の時は1行だったりします(逆の場合もあります) 月に最大で110シートほどになりますが、先月は70シート程でした。 【やりたいこと/欲しい結果】 ・表のセルからデータがある行を抽出し、別シートに纏める 具体的なセルは、(1)D5~M14の範囲と、(2)D17~M26の範囲です。 ・シート1からシート100の(1)を日付順にまとめて別シート(例:まとめ1)に ・シート1からシート100の(2)を日付順にまとめて別シート(例:まとめ2)に 不慣れな上、質問を頂いてもすぐに返答できないかもしれませんが、よろしくお願い致します。

  • 【Excel】データの検索、抽出について

    教えてください! 以下のような表があります。 日付   Data 4月1日    2 4月2日 4月3日   34 4月4日 4月5日   4 4月6日 4月7日   34 4月8日 4月9日   4 4月10日 4月11日  4 4月12日 4月13日  4 4月14日 4月15日 4月16日 4月17日 4月18日 ・ ・ ・ 日付が一番新しくて、尚かつその隣にデータが存在する日付と データを抽出したいと考えています。 (別途抽出用のセルがあります) 関数でおこなってみたのですが、なかなか出来ず マクロでもかまいませんので何か良い方法を教えてください◎

  • 日付から別のシートにデーターを抽出する。

    大まかに2つの質問があります。 まず、下記の勤務表のE6のセルに月数(この場合6)を入れると「6月度勤務表」と表示しそしてA1セルに平成24年度と同時に表示し、更に、A4以下にその月の1ヶ月分の日付が出るようにしたいのです。 問題は、締めの関係で始まりは毎月21日から翌月の20日迄になるようにしなければなりません。 もう、一つはその日付を検索して、あらかじめ用意している31日分のシート(21日シート、22日シート・・・)に勤務表の出欠勤情報を呼び出し、出勤する人の作業を指示する予定表を一ヶ月分まとめて月初めに作りたいのです。 問題は勤務表の日付が、1日~月末という順序であれば問題はないのですが、締めの関係で21日~月末~翌月の20日という順序で並んでいる日付では、例えば28日で終わるような2月と31日まである3月では、順番にデーターを流し込むと日付がずれてしまいます。 以上の2点について何か良い手法はないものでしょうか? 1点づつの回答でも構いませんので、どうかお教えください。

専門家に質問してみよう