月計表と日別受注表を連動させてエクセルで作成したい

質問

エクセルの関数を使った表を作成したいのですが、知識がなくお尋ねします。
ちなみにエクセル2010です。2007でも作成可能です。

各営業所の受注数を月別で集計し、それとは別に受注のあった日のみ
受注数と累計を一覧にしたいと思います。
Sheet1に日別受注数、Sheet2に月計表を作成し、○年○月分受注として
ファイルを作成したいと思います。

例えば12月3日の時点で受注があった場合、月計表に営業所別の受注数を
入力するだけで日別受注数が自動的に入力されるようにしたいです。
受注がある日とない日があるので、受注があった日だけ入力して作成する
ことになります。

累計の行は月計表の合計をリンクさせればいいのだと思いますが、
日ごとの受注数をどのようにしたら自動入力にできるかが分かりません。
日別受注数に日付を入れたら月計表のその日の数字が自動的に
入力されるようなことができたらい一番便利そうです。

こだわりはないので、別の方法でもまったく構いません。

【Sheet1　日別受注数】

12月3日　営業所別受注数

第一営業所 第二営業所 第三営業所 合計
受注数 　0 　　　　　　1 　　　　　　0 　　　　　　1　　　←この行が自動入力
累計 　　　1 　　　　 　3 　　　　　　2 　　　　　　6　　　←この行が自動入力

【Sheet2　月計表】
第一営業所 第二営業所 第三営業所 合計
12月1日 　 0 　　　　　　0 　　　　　　0 　　　　　　0
12月2日 　 1 　　　　　　2 　　　　　　2 　　　　　　5
12月3日　　0 　　　　　　1 　　　　　　0 　　　　　　1
12月4日 　 0 　　　　　　0 　　　　　　0 　　　　　　0
～
12月31日 0 　　　　　　0 　　　　　　0 　　　　　　0
合計 　　　 1 　　　　　　3 　　　　　　2 　　　　　　6

説明不足で分かりにくかったら申し訳ありません。
エクセルにお詳しい方にお知恵を拝借したく、よろしくお願いいたします。

回答No.8

esupuresso · ベストアンサー

＞例えば12月3日の時点で受注があった場合、月計表に営業所別の受注数を入力するだけで日別受注数が自動的に入力されるようにしたいです。

添付画像を参照していただいて・・・
Sheet1のセルB3に　=INDEX(Sheet2!B$2:B$32,MATCH($A$1,Sheet2!$A$2:$A$32,0))　の式を入れ、セルD3までオートフィル機能で数式をコピーします

＞累計の行は月計表の合計をリンクさせればいいのだと思いますが・・・、

リンクさせてもいいですし、Sheet1のセルB4に　=Sheet2!B$33　と入れてセルD4まで式をコピーさせセル参照をする方法でも、どちらでもいいです

お礼

何名もの方から回答をいただき、有難うございます。
とっても勉強になるクリスマスプレゼントでした！

図入りで説明していただき、また関数も分かりやすく
大変助かりました。思い通りのファイルが出来上がり
感謝しております。

何通りものやり方があるものなのですね～
複雑な関数はちょっと理解するには大変ですが、
頑張ってトライしてみようと思います。

ベストアンサーの方はじめ、皆様有難うございました。

回答No.7

noname#204879

[No.6]の補遺、

式中の monthly は、Sheet2 の範囲 A1:E33 に付けた範囲名(Range Name)です。

回答No.6

noname#204879

Sheet1!B4: =VLOOKUP($A$1,monthly,COLUMN(B1),FALSE)
Sheet1!B5: =VLOOKUP("合計",monthly,COLUMN(B2),FALSE)

回答No.5

KURUMITO

回答No４です。シート１では入力の有った最新の日付のデータを表示するとのことでしたね。その際にはシート２にA１セルのデータは使わないことになりますね。
B２セルには次の式を入力してD２セルまで横にドラッグコピーします。

=IF(COUNTIF(Sheet2!$A:$A,INDEX(Sheet2!$A$2:$A$32,COUNT(Sheet2!$B$2:$B$32)))=0,"",INDEX(Sheet2!$B:$D,MATCH(INDEX(Sheet2!$A$2:$A$32,COUNT(Sheet2!$B$2:$B$32)),Sheet2!$A:$A,0),COLUMN(A1)))

B３セルには次の式を入力してD３セルまで横にドラッグコピーします。

=IF(COUNTIF(Sheet2!$A:$A,INDEX(Sheet2!$A$2:$A$32,COUNT(Sheet2!$B$2:$B$32)))=0,"",SUM(INDEX(Sheet2!$B:$D,2,COLUMN(A1)):INDEX(Sheet2!$B:$D,MATCH(INDEX(Sheet2!$A$2:$A$32,COUNT(Sheet2!$B$2:$B$32)),Sheet2!$A:$A,0),COLUMN(A1))))

ただし、シート２では入力の日付以降の日付の行にはお示しの場合のように０が入力されていないで空白のセルになっていることが必要です。入力の日付よりも前の日付のデータは空白とするのではなく受注が無い場合には０と入力します。

回答No.4

KURUMITO

シート２ではA２セルから下方に例えば１２月１日からの日付がA３２のセルまで入力されているとします。
B１セルからD１セルまでに第一営業所から第三営業所までの文字が入力され下方にそれぞれの日ののデータが入力されているとします。
お求めの表はシート１に有り、A１セルには１２月３日現在のデータを表示したければ１２月３日と入力します。
B１セルには営業所別受注数と文字を入力します。
B２ルからD２セルには第一営業所から第三営業所までを入力します。E２セルには合計
A３セルには受注数、A４セルには累計と入力します。
B３セルには次の式を入力してD３セルまでドラッグコピーします。

=IF(COUNTIF(Sheet2!$A:$A,$A$1)=0,"",INDEX(Sheet2!$B:$D,MATCH($A$1,Sheet2!$A:$A,0),COLUMN(A1)))

B４セルには次の式を入力してD４セルまでドラッグコピーします。

=IF(COUNTIF(Sheet2!$A:$A,$A$1)=0,"",SUM(INDEX(Sheet2!$B:$D,2,COLUMN(A1)):INDEX(Sheet2!$B:$D,MATCH($A$1,Sheet2!$A:$A,0),COLUMN(A1))))

最後にE３セルには次の式を入力してE４セルまでドラッグコピーします。

=SUM(B3:D3)

なお、累計についてはA１セルに入力した日にちまでの累計を表示するようになっています。

回答No.3

ryo_ky

No1です。

提案した方法ができたので、説明します。

先ず、作業行を用意します。
作業行はSheet2のG列～J列とし、Gは日付、H～Jは各営業所の受注数とします。
ここでSheet2の構成は下記と仮定します。

A　　　　　　B　　　　　C　　　　　D　　　　　E
　　　　第一営業所　第二営業所　第三営業所　　合計
12月1日　 0　　　　　　0　　　　　　0　　　　　　0
12月2日　 1　　　　　　2　　　　　　2　　　　　　5
12月3日　　0　　　　　　1　　　　　　0　　　　　　1
12月4日　 0　　　　　　0　　　　　　0　　　　　　0

作業行の日付の行（G2）に
=IF($E2<>0,A2,"")
を入力し、G2からJ32までコピーします。

これで作業行はOKです。

Sheet1で構成を
A　　　　　　B　　　　　C　　　　　D　　　　　E
日付　　第一営業所　第二営業所　第三営業所　　合計

とし、A2に
=IF(ROW(Sheet2!$F1)>SUMPRODUCT((LEN(Sheet2!G$2:G$32)>0)*1),"",INDEX(Sheet2!G$1:G$32,SMALL(INDEX((Sheet2!G$2:G$32="")*100+ROW(Sheet2!G$2:G$32),),ROW(Sheet2!$A1))))
と入力します。
これをA2～D32までコピーします。
A列をクリックして、セルの書式設定で表示形式を日付にします。
E2は合計なので、
=IF(SUM(B2:D2)<>0,SUM(B2:D2),"")と入力し、
これをE32までコピーします。

これでこちらの提案した表を作成する事ができます。

回答No.2

MarcoRossiItaly

「自動」というのとは少し違うかもしれませんが、ラクにそこそこ高度な集計ができるという意味では、まずは「ピボットテーブル」をお勧めしたいと思います。（数式のメンテもたいへんですよね？）参考 URL の他にも様々な情報がネット上にありますので、調べてみてください。

始めはピンと来ないかもしれませんが、まずは実行してみることです。慣れれば難しくも何ともありません。合計、個数、最大・最小などパッと求められます。数式みたいに数が増えると重くなるということは全然ありません。

ピボットテーブルでは、その操作よりも、元データの表をしっかり作ることがまず大事です。

お示しの「月計表」のように、毎日のデータを 1 つの表にまとめた元データ、つまり小規模なデータベースを作りましょう。例えば 1 か月 100 件の場合、1 年経ってもレコード（行）の数は 1,200 件に過ぎませんから、少なくとも数年くらいはファイルが重たいと感じることもなく、軽快に操作できるでしょう。できればいろいろな情報を載せるための列を他にも用意しておくとよいでしょう。いろいろな情報とは、数式による計算結果でも構いません。「=sum(b2:d2)」といった具合に。

そこから必要なデータのみ抽出して、「日別受注数」でもその他の表でも、パッと様々な表をピボットテーブルの機能によって作れると思います。

参考URL：

http://allabout.co.jp/gm/gc/297727/

回答No.1

ryo_ky

手入力するのはSheet2の月計表で、アウトプットがSheet1の日別受注数ってことですよね？

質問ですが、上の例ですと

12月3日　営業所別受注数

第一営業所第二営業所第三営業所合計
受注数　0　　　　　　1　　　　　　0　　　　　　1　　　←この行が自動入力
累計　　　1　　　　 　3　　　　　　2　　　　　　6　　　←この行が自動入力

となっていて、12月3日の他にも受注のある日はどのように表示するのかで難易度が変わります。
12月3日の部分も手入力で日付を入力した時点で受注と累計を変えるのであれば、VLOOKUP関数でできますが、この場合、1日分しかアウトプットできません。

もし、受注のあった日全てを表示させる表を作るのであれば、レイアウトを変えた方が良いかもしれません。

ここで提案ですが、月計表の受注のある日のみを抽出した表なら、関数をいくつか組み合わせて作る事ができそうです。

参考
http://okwave.jp/qa/q7858124.html

関数を組み立てるのに時間を下さい。