エクセルのこんな場合の式はどうしたら？

質問

何時もお世話になっております。
エクセル初心者なので、非常にくだらない質問をしているかも知れませんが、その際はご容赦ください。

今、仕入れ台帳をエクセルで作成しているのですが、下記のような場合の計算式はどうしたら良いか困っております。（因みにこれが式として成り立つのかどうかもわかりません。）

（例）

　1月3日　　￥１５００
　1月15日 　￥２０００
　1月30日 　￥５０００

合計 ￥７５００（A)

　2月5日　　￥５００
　2月5日　　￥１０００
　2月15日 　￥２０００
　2月21日 　￥５０００

合計 ￥７５００（B)
1月＋2月合計￥１５０００（C)

と言う内容で、各月（C)をシート上で求めたいのですが、それぞれの月で仕入れ数は違うので、単純に（A)+（B)の数式コピーでは求められません。いったいどうしたら良いのでしょうか？図解本を見てもこのような例が載っておらず、色んなサイトを見てみたのですが、よく分かりません。

ご多忙のところ申し訳ありませんが、何卒ご教示、宜しくお願い申し上げます！

回答No.15

noname#9284 · ベストアンサー

6月月計のセル番地はB19
1000円のセル番地はC19
7月月計のセル番地はB41
2000円のセル番地はC41

わかりました。
１．
このレイアウトでは累計のセルが挿入されていませんが、
6月までの累計（というか、合計ですね）をD19に、
7月までの累計をD41に表示される、ということで宜しかったですか？

C19には　=SUBTOTAL(9,C4:C18)　と入力します。
「=SUBTOTAL(9,」と入力した後、マウスでC4:C18をドラッグします。最後に「）」を入力してEnterを押します。

C41には　=SUBTOTAL(9,C20:C40)　と入力します。

D19には　=SUBTOTAL(9,$C$4:C18)　と入力し、
このセルをD41に貼り付けます。
数式バーをドラッグして貼り付けではありません。
セルのコピー貼り付けです。

２．
> 一行目とはその表の一番上の行・・・と言うことでしょうか？
> でしたら年月日と仕入額・・・等の項目か、項目は外して・・・と言われるのなら6月1日仕入れの仕入れ金額がいきなり入っています。

いくつかの理由で項目は残された方がいいと思います。

３．
> =SUBTOTAL(9,$C$4:C18)を入れてみましたが、電卓と合わないので・・・

あらら、そうなんですか。
C4:C18には数字だけが入力されていますか？
そして書式設定はどうでしょう？
その範囲に「数値」や「文字列」の表示形式が設定してある場合、
SUBTOTALはそのセルを無視します。
一旦何も書式が設定されていないセルをコピーし、
その範囲をドラッグしてから書式のコピーを行ってください。

どうでしたでしょうか。

なお、私は sophia35 さんのご質問が解決するまで日にちはかかっても必ずお返事をさせていただきたいと思いますので、
諦めて閉じてしまわれないようにお願いいたします（笑）

お礼

有難うございます！！
出来ましたーーーー！！！（涙）

作ったBookがもうグチャグチャになってしまって、最初からやり直していたのでお返事が遅くなって申し訳ありません！

よくは分からないのですが、仰る通り何か書式が設定されていたようで、（セルをコピーしても、開けると全く別の数式が出ていたんです）新規に作り直してご説明の通りにやってみたら出来ました！！
一週間ずっと頭を悩ませ続けた問題が片付いたので、本当に安心しました。
ご丁寧な説明していただき、また、お気遣いをいただいて本当に感謝いたします。有難うございました！

回答No.14

noname#9284

> 何だか考えがループするばかりで・・・
> ここまで甘えてしまって大変恐縮なのですが、ご教示いただけたら幸いです！

気にされなくてもいいですよ。とことん聞いてやってください。

では質問させていただいて宜しいでしょうか。次の事柄を教えてください。

6月月計 のセル番地
1000円 のセル番地　←セルの書式で「円」と表示すればいいのでしょうか？
7月月計 のセル番地
2000円 のセル番地　←セルの書式で「円」と表示すればいいのでしょうか？
1行目には何が入力されていますか？

> 皆さんの教えていただいた数式を6月合計に当てはめてやっているのですがうまくいきません。

どのセルにどういう数式を入れられましたか？
お手数ですが数式バーからコピーして貼り付けていただけないでしょうか。

お礼

もう、何と言って良いやら・・・感謝感激です！

6月月計のセル番地はB19
1000円のセル番地はC19
7月月計のセル番地はB41
2000円のセル番地はC41
一行目とはその表の一番上の行・・・と言うことでしょうか？
でしたら年月日と仕入額・・・等の項目か、項目は外して・・・と言われるのなら6月1日仕入れの仕入れ金額がいきなり入っています。
因みにシートの最初の行は空白になっておりますので、実質数字の入力が始まるのはセル4からの入力となります。（円は表示しません）

一応入れた数式は
=SUBTOTAL(9,$C$4:C18)を入れてみましたが、電卓と合わないので・・・
合っておりますでしょうか？
宜しくお願い申し上げます！

回答No.13

noname#9284

> （何故か電卓で試算したのとちがっていたり・・・（涙））

ご苦労様です。
結果が違う原因の一つにSUBTOTALが指定している範囲が間違っているかずれている事が挙げられます。

sophia35 さんがどの方法を実行されるにしても、
ここに作られたレイアウトをセル番地付きで書いていただくと
あっと言う間に解決するように思います。
とりあえず回答どおりの手順を踏んでファイルを作ってしまってから、
「この数式がわからないのですが」「これをこう応用したいのですが」
と細かいところを相談されるといいと思います。

お礼

気に掛けていただいて、本当に感謝いたします。

昨日聞いておいて、御礼も何も無く放置しているのが心苦しくて、まだ途中だけど取り敢えずお礼だけでも先にしなきゃ・・・と焦っておりました。すみません。

実は当てはめ方が分からなくて・・・
今期の資料は新規に作るので、皆様の回答で理解が出来て解決したのですが、＃３さんのお礼にも書いたように、実は年度の途中からパソコンに切り替えたので、それまでの手書きの帳簿が残っております。で、手書きで6月までの累計があるので、それを使って皆さんの仰る式を当てはめているのですが、なかなか思うとおりにならなくて・・・（仕入れ数量が少ない仕入先は＃３さんの回答で何とかクリアしたのですが、数の多くて月々の仕入れ数にムラがあるところだとそうもいかなくて・・・）
取り敢えず今、

6月月計 1000円
累　　計5000円

　　　　・
　　　　・
7月月計 2000円
累　　計　X

このXを求めるべく、皆さんの教えていただいた数式を6月合計に当てはめてやっているのですがうまくいきません。
そも、今、私のやっているやり方では間違っているのでしょうか？

何だか考えがループするばかりで・・・
ここまで甘えてしまって大変恐縮なのですが、ご教示いただけたら幸いです！

回答No.12

mshr1962

SUBTOTAL関数に関しては出ているので別の集計方法です。
日付の欄の前に１列挿入して=MONTH(B2) として月を作成した上で
「データ」「集計」として月をグループにして金額を合計します。
月毎の計と、総計を表示してくれます。

参考URL：

http://office.microsoft.com/assistance/preview.aspx?AssetID=HP052009461041&CTT=3&Origin=HP052031161041

お礼

ご回答有難うございました！

とても心強いサイトがあるのですね！オフィスのHPにこのようなところがあることさえ気づきませんでした。
今、下から色々なやり方を試していっております。自分のやり方に一番合うものを見つけたいと試行錯誤中です。このようなサイトを教えていただいて、本島に有難うございました！

回答No.11

maruru01

こんにちは。maruru01です。

合計や総計は、C列に表示した方がいいかも知れません。
そこで、C列には予め数式を入力しておきます。
データは、A1～に日付、B1～に金額とします。
C列の2行目（C2）に、

=IF(A2="合計",SUM($B$1:B2)-SUMIF($A$1:A1,"合計",$C$1:C1),IF(A2="総計",SUM($B$1:B2),""))

と入力して、十分下の行へコピーします。
（C列の先頭のC1には数式は入力しません。）
これで準備完了です。
使用方法は、A列に日付の替わりに「合計」と入力すると、前の合計以降の合計（月ごとの合計）がC列に表示されます。
ただし、この時隣りのB列には何も入力しないで下さい。
次に、A列に日付の替わりに「総計」と入力すると、そこまでの総計がC列に表示されます。
この場合も隣りのB列には何も入力しないで下さい。
以下のようなデザインになります。

　　　　A　　　B　　C
1　　1月1日　300
2　　1月2日　400
3　　1月3日　500
4　　合計　　　　　1200
5　　2月1日　600
6　　2月2日　700
7　　2月3日　800
8　　合計　　　　　2100
9　　総計　　　　　3300
10　3月1日　900
11　3月2日　1000
12　3月3日　1100
13　合計　　　　　3000
14　総計　　　　　6300
15
16

お礼

有難うございました！
取り敢えず＃１５さんのお陰で今の問題は解決いたしましたが、まだまだ試行錯誤の途中で、一番使いやすい方法を模索している途中なので、ご回答の内容を良く理解して頑張ってみたいと思います！

補足

今、飲み込み中で・・・（どうも飲み込み悪くて・・・）理解できたら必ず御礼を書かせていただきます。放置しているのではありませんので、何卒今しばらくお待ちくださいませ。

回答No.10

noname#9284

SUBTOTALを言い出した本人です。
レイアウトの事でご苦労されているとのことですので例を具体的にしてもう一度書かせていただきますね。

B1:B31に1月のデータ
B32に　=SUBTOTAL(9,B1:B31)

B34:B62に2月のデータ(うるう年ですよね）
B63に　=SUBTOTAL(9,B34:B62)

B65:B95に3月のデータ
B96に　=SUBTOTAL(9,B65:B95)

さて
B33に　=SUBTOTAL(9,$B$1:B32)　←1月1日からの累計
このセルをコピーし、
B64とB97に貼り付けます。←1月1日からのそれぞれの累計

月ごとの合計を計算させるSUBTOTALのセルはコピーして使いまわせませんが（各月、日にちが違いますから）
累計のセルはコピーして使いまわせ、
しかも月ごとの合計のセルは無視してくれます。

お礼

おはようございます！！

何度も何度も足を運んでいただいて、本当に有難うございます！！
SUBTOTAL関数について提示して頂けただけでも、解決の糸口が見えて、本当に助かりました。
実はまだ出来上がってはいないんですが、取り敢えずいただいた回答をプリントアウトして、試行錯誤の真っ最中であります。（何故か電卓で試算したのとちがっていたり・・・（涙））
でも、解決の糸口が見えただけでも本当に感謝いたします！有難うございました！！

回答No.9

imogasi

実務から。
＝SUBTOTAL関数で集計した合計は、それより下の＝SUBTOTALに加えないという都合の良い点がある（下記注）が、私だったら、＝SUBTOTALで年初来合計を出しません。
１月合計（A)
Aが出ているセル＋２月合計（B)--->２月年初来合計
Bが出ているセル＋３月合計（C)--->３月年初来合計
Cが出ているセル＋４月合計（C)--->４月年初来合計
・・・・・
とします。
なぜといわれると困るが、皆さんどうなんでしょうか。
関数式のコピーにこだわらなくても、セル番地で簡単に
値がとって来れるし、やたらに複写で式の番地を変化
させるとチェックに神経を使います。
（注）A列
1
2
1
4　<---=SUBTOTAL(9,A1:A3)
3
4
5
16 <---=SUBTOTAL(9,A1:A7) <--A４セルの４は含まれてない。

お礼

ご回答、ありがとうございます！

さすがプロだなぁ・・・と思ったのですが、実際やってみようとしたら、ご説明がうまく飲み込めなくて・・・
初歩の初歩かもしれませんが、何故１．２の次が１なんでしょうか？本を引っ張り出して照らし合わせてみたのですが、今いち分からなくて・・・お時間があればご教授いただけたら幸いです。

回答No.8

noname#9284

三度失礼します。

ANo.#7さん補足をありがとうございました。補足の補足です。
SUBTOTAL関数について。

3月までの累計欄のB30に
=SUBTOTAL(9,$B$1:B29)
となっていると思いますが、
この範囲の$B$1:B29には　1月の合計の=SUBTOTAL(9,$B$1:B9)の戻り値と
と　2月までの累計の=SUBTOTAL(9,$B$1:B19)の戻り値が入っていますので
SUMで合計を出せば3月の=SUBTOTAL(9,$B$1:B29)には
実際の合計の2倍の数値が戻っていることになります。

ところがSUBTOTALは、範囲の中にSUBTOTALで戻った値があるとそれを無視して計算してくれます。

これがSUBTOTAL関数を使ったメリットです。

補足

皆様、ご回答有難うございます！

今、頭を整理して（皆様の回答を飲み込むまでに時間がかかってしまってて・・・（汗））取り敢えず表組みから頑張っておりますので、お礼の方は今しばらくお待ち頂けますよう宜しくお願い申し上げます。
取り敢えず補足欄で申し訳ございません。

回答No.7

hinebot

ふたたび＃２（＃６）です。

＃５の方のSUBTOTALを使うほうが操作が簡単ですね。

>=SUBTOTAL(9,$B$1:B9)

（　）の中の最初の"9"は、集計に合計を使うという意味ですので、他の数字ではなく"9"でないといけません。
後ろの "$B$1:B9"は集計する範囲です。$がついているのは、コピー＆貼り付けしたときに、値が変わらないようにするためです。
仮に"B1:B9"として、B20に貼り付けると"B11:B19"になりますが、"$B$1:B9"としておけば"$B$1:B19"で、範囲の先頭は変わりません。

お礼

ご回答、有難うございました！！

最初のやり方も大変参考になりました！それに、補足の「９」の説明も大変良く分かりました！
実は＃５さんの回答を初めて見たときに、この数式を自分のシートのセルの数値に変えるにはどうしたら良いの・・？と、暫しPCを前にフリーズしてしまっていたんで、＃７さんのご回答を見たときにはひざポン☆状態でした（苦笑）

まだ実際に表は出来ていないのですが・・・・皆さんの回答をプリントアウトして試行錯誤の真っ最中です。
本当に助かりました！有難うございました！！

因みにもう一つ甘えても宜しいでしょうか・・・
＃３さんのお礼にも書いたのですが、実は昨年の年度途中でいきなり渡されたので、昨年の途中からエクセルでの経理に切り替わりました。（それまでは手書きでした。）昨年の分の資料は6月からで、期間の途中なので既に前月までの累計が出ているのですが、その際にもこの式を当てはめて良いのでしょうか？
お手すきの時で結構ですので、ご教示いただけたら幸いです。

回答No.6

hinebot

＃２です。

総計のところに
=SUM(１月の合計のセル、２月の合計のセル、３月の合計のセル、…）
とすればOKです。

"Σ"という文字が書かれたボタンがないですか？
総計のところでこのボタンを押すと
=SUM()
と出て)の１つ前にカーソルがある状態になります。
※もちろん、手入力でもOKです。
このあと、Ctrlキーを押しながら、各月の合計が入っているセルをクリックすれば、勝手に式を作ってくれます。

回答No.5

noname#9284

わかりました、累計を出したいとのことですね。

金額がB列に入っているとして、
1月の合計欄がB10
2月までの累計欄がB20
3月までの累計欄がB30だとします。

1月の合計を出すセルB10に
=SUBTOTAL(9,$B$1:B9)
と入力してこのセルをB20とB30に貼り付けてください。
どうでしょうか。