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

解決済みの質問

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

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

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

よろしくお願いします。

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

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

投稿日時 - 2011-07-19 15:35:53

QNo.6885599

すぐに回答ほしいです

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

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

投稿日時 - 2011-07-19 17:03:29

お礼

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

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

投稿日時 - 2011-07-22 10:42:52

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

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

回答(5)

ANo.5

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

投稿日時 - 2011-07-23 18:53:50

ANo.4

添付図の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セルに貼り付け

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

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

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

投稿日時 - 2011-07-19 20:43:20

ANo.3

こういう表の組み換え課題を、関数でやろうとするのは無理があります。
例えば7月2日の鉛筆の売上があったとき、統合したシートのどの行に来るか、それ以前の日のデータの多少次第という
点が扱いにくいのです。
又この質問では、仕入れのデータ行も売上の中に割り込んできますのでますます複雑になります。
マジックや鉛筆の項目は、発生後、表示行が固定されるので、新規に販売された品物は最後の次に回るようだが、これも関数では難物。
毎日データが増えて、過去のものは位置を動かさないのも難物。
ーー
このタイプは、VBAを使う課題です。勉強してください。VBAを使えないものが、エクセルを仕事絡みで使おうとすると、直ぐ無理が来る・行き詰まる、と思います。
ただしこの程度になると、VBAを少し学習してもやや難しいと思うが。
ーー
質問の添付画像例が、ぼやけてよく見えないが、売上と仕入れとが同じシートにあるほうが、まだやりやすいかも。
ーー
筆のすさみに途中までだがやってみた。データ配置も質問とは変えている。
Sheet1  日付順とする
--売上
7月1日鉛筆30
7月1日消しゴム40
7月1日定規10
7月3日紙10
7月3日はさみ5
7月3日半紙5
7月3日ボールP25
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日ボールP5
結果
売上か仕入れした物商品の一覧表
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での商品の並べ順など注文がつけば、さらに複雑になる。
上記コードは、処理方式は人によってもっと優れたやり方があると思う。

投稿日時 - 2011-07-19 17:47:39

ANo.2

シート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セルに例えば福岡とかの名前を入力すればよいでしょう。

投稿日時 - 2011-07-19 17:43:11

あなたにオススメの質問