エクセルのデータを別シートでカレンダー形式に

質問

sheet1「売上データ」とsheet2「仕入れデータ」があります。
このデータをsheet4～sheet7「取扱店」ごとにカレンダー形式で表示したいのですが
エクセル初心者のためどんな関数を使えば思い通りにできるのか分かりません。
（VlookUpを勉強してみましたが、これでは無理な気がします。）

データは毎日増えます。（同じシートで行がどんどん増えます）
データの並びはランダムです。
No.は重複しません。
製品ごとに取り扱い店は決まっており変わることはありません。

よろしくお願いします。

回答No.1

don9don9 · ベストアンサー

Sheet3のD4に

=SUMPRODUCT((Sheet2!$A$2:$A$3=$A4)*(Sheet2!$D$2:$D$3=DATE($D$1,$D$2,D$3))*(Sheet2!$E$2:$E$3="東京")*(Sheet2!$C$2:$C$3))

D5に

=SUMPRODUCT((Sheet1!$A$2:$A$8=$A4)*(Sheet1!$D$2:$D$8=DATE($D$1,$D$2,D$3))*(Sheet1!$E$2:$E$8="東京")*(Sheet1!$C$2:$C$8))

と入力し、このD4:D5の二つのセルを縦横必要な範囲まで複写してください。
仕入および売上の実績が無い場合に「0」が表示されますが
条件付き書式で「値が0と等しい場合、フォントカラーを白」などに
設定しておけば見えなくなります。

D4の式の「$A$2:$A$3」「$D$2:$D$3」「$E$2:$E$3」「$C$2:$C$3」
D5の式の「$A$2:$A$8」「$D$2:$D$8」「$E$2:$E$8」「$C$2:$C$8」
の部分は、実際のデータの行数に合わせて変えてください。
データが増えていく場合は、最大でこのくらいの件数だろうという
大きめの数字をあらかじめ設定しておけばいいです。

ちなみに上記の式では、"東京"で固定しています。
Sheet4に式を入れる際には、"東京"を"福岡"に変えなければいけませんが
例えば「Sheet3～Sheet7はA1セルに取扱店を入れる」ようにすれば
「(Sheet2!$E$2:$E$3="東京")」の部分を「(Sheet2!$E$2:$E$3=$A$1)」
として、シートごと複写してA1セルのみ変更、で済むので少し楽だと思います。

参考URL：

http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/sumproduct.htm

お礼

ありがとうございます。
うまくいきました…が、データ量が多すぎるのか、私の考えていることに無理があるのか
かなり重くなってしまい、ファイルを開くだけで4、5分かかってしまいます。
１日のデータ量は仕入れ、売上合わせて２００～３００件程度。
テストの為、３日分入れてみました。

毎日データを追加せずに上書きするとしても、この作り方だと重いです。
何か解消方法はないでしょうか？

回答No.5

don9don9

No.1です。

お使いのExcelのバージョンは何でしょうか？

もしExcel2007以降のバージョンをお使いなら、SUMIFS関数が追加されていますので
こちらを試してみてください。

=SUMPRODUCT((Sheet2!$A$2:$A$3=$A4)*(Sheet2!$D$2:$D$3=DATE($D$1,$D$2,D$3))*(Sheet2!$E$2:$E$3="東京")*(Sheet2!$C$2:$C$3))

は、SUMIFS関数に置き換えると

=SUMIFS(Sheet2!$C$2:$C$3,Sheet2!$A$2:$A$3,$A4,Sheet2!$D$2:$D$3,DATE($D$1,$D$2,D$3),Sheet2!$E$2:$E$3,"東京")

となります。

Excel2003以前のバージョンをお使いの場合は…
すみません、ちょっといい方法が思いつきません。
Excelのオプションで計算方法を手動にすることくらいでしょうか。

ツール＞オプション＞計算方法のタブで、計算方法を手動にすると
データを1件入力する度に再計算が行われて、計算が終わるのを待たなければならない
ということはなくなるかと思います。
（都度、F9キーで再計算させないといけなくなりますが）

参考URL：

http://office.microsoft.com/ja-jp/excel-help/HA010047504.aspx

回答No.4

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

添付図の2行目と4行目のような重複はないものとして
F2セルに =D2&E2&A2
下へオートフィル
K2セルに
=IF(ISNA(MATCH(DATE($K$1,$K$2,K$3)&$I$1&$H4,$F:$F,0)),"",
INDEX($C:$C,MATCH(DATE($K$1,$K$2,K$3)&$I$1&$H4,$F:$F,0)))
右へオートフィル
選択したままコピーして
K4セルに貼り付け

売り上げも仕入も同様です

回答No.3

imogasi

こういう表の組み換え課題を、関数でやろうとするのは無理があります。
例えば７月２日の鉛筆の売上があったとき、統合したシートのどの行に来るか、それ以前の日のデータの多少次第という
点が扱いにくいのです。
又この質問では、仕入れのデータ行も売上の中に割り込んできますのでますます複雑になります。
マジックや鉛筆の項目は、発生後、表示行が固定されるので、新規に販売された品物は最後の次に回るようだが、これも関数では難物。
毎日データが増えて、過去のものは位置を動かさないのも難物。
ーー
このタイプは、VBAを使う課題です。勉強してください。VBAを使えないものが、エクセルを仕事絡みで使おうとすると、直ぐ無理が来る・行き詰まる、と思います。
ただしこの程度になると、VBAを少し学習してもやや難しいと思うが。
ーー
質問の添付画像例が、ぼやけてよく見えないが、売上と仕入れとが同じシートにあるほうが、まだやりやすいかも。
ーー
筆のすさみに途中までだがやってみた。データ配置も質問とは変えている。
Sheet1　　日付順とする
-- 売上
7月1日 鉛筆 30
7月1日 消しゴム 40
7月1日 定規 10
7月3日 紙 10
7月3日 はさみ 5
7月3日 半紙 5
7月3日 ボールP 25
Sheet2　日付順とする
-- 仕入
7月1日 鉛筆 20
7月1日 消しゴム 20
7月1日 定規 5
7月2日 帽子 20
7月3日 紙 10
7月3日 はさみ 30
7月3日 チョーク 5
7月3日 半紙 10
7月4日 ボールP 5
結果
売上か仕入れした物商品の一覧表
Sheet3
鉛筆 売上
鉛筆 仕入
消しゴム 売上
消しゴム 仕入
定規 売上
定規 仕入
紙 売上
紙 仕入
はさみ 売上
はさみ 仕入
半紙 売上
半紙 仕入
ボールP 売上
ボールP 仕入
帽子 売上
帽子 仕入
チョーク 売上
チョーク 仕入
このC列以右列に日付列に応じてデータをセットしていく（略）
上記で
Sub test01()
Dim sh1, sh2, sh3
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Set sh3 = Worksheets("Sheet3")
'表に項目を用意
d = InputBox("処理開始日")
dt = DateValue(d)
g1 = sh1.Range("A:A").Find(dt).Row
MsgBox g1
lst1 = sh1.Range("A65536").End(xlUp).Row
lst2 = sh2.Range("A65536").End(xlUp).Row
lst3 = sh3.Range("A65536").End(xlUp).Row
k = lst3 + 1
MsgBox lst1
For i = g1 To lst1
MsgBox sh1.Cells(i, "B")
Set fnd = sh3.Range("A:A").Find(sh1.Cells(i, "B"))
If Not fnd Is Nothing Then
Else
sh3.Cells(k, "A") = sh1.Cells(i, "B")
sh3.Cells(k, "B") = "売上"
k = k + 1
sh3.Cells(k, "A") = sh1.Cells(i, "B")
sh3.Cells(k, "B") = "仕入"
k = k + 1
End If
Next i
'---
g2 = sh2.Range("A:A").Find(dt).Row
For i = g2 To lst2
MsgBox sh2.Cells(i, "B")
Set fnd = sh3.Range("A:A").Find(sh2.Cells(i, "B"))
If Not fnd Is Nothing Then
Else
sh3.Cells(k, "A") = sh2.Cells(i, "B")
sh3.Cells(k, "B") = "売上"
k = k + 1
sh3.Cells(k, "A") = sh2.Cells(i, "B")
sh3.Cells(k, "B") = "仕入"
k = k + 1
End If
Next i
End Sub
d = InputBox("処理開始日")のところで、間違いないように未反映の最初日を指定する。
これでもSheet3での商品の並べ順など注文がつけば、さらに複雑になる。
上記コードは、処理方式は人によってもっと優れたやり方があると思う。

回答No.2

KURUMITO

シート1のF列は作業列としてF2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",E2&D2*1000+A2)

シート2も同様にF2セルには上と同じ式を入力して下方にオートフィルドラッグします。

東京のシートではA1セルに東京と入力します。

そのほかはお示しの表と同じとしてA,B,C列については予め入力されているものとします。
その上でD4セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にオートフィルドラッグします。

=IF($C4="","",IF(MOD(ROW(A1),2)=1,IF(COUNTIF(Sheet3!$F:$F,$A$1&DATE($D$1,$D$2,D$3)*1000+$A4)=0,"",INDEX(Sheet3!$C:$C,MATCH($A$1&DATE($D$1,$D$2,D$3)*1000+$A4,Sheet3!$F:$F,0))),IF(MOD(ROW(A1),2)=0,IF(COUNTIF(Sheet2!$F:$F,$A$1&DATE($D$1,$D$2,D$3)*1000+$A3)=0,"",INDEX(Sheet2!$C:$C,MATCH($A$1&DATE($D$1,$D$2,D$3)*1000+$A3,Sheet2!$F:$F,0))))))

このシート全体をコピーして福岡のシートやその他のシートに貼り付けます。
それらのシートではA1セルに例えば福岡とかの名前を入力すればよいでしょう。