Excelで見積明細書を作成、小計の位置を自在に変更したい

質問

C列：「寸法」
D列：「数量」
F列：「単価」
G列：「金額（D列×F列）」
行2～行83まで
G84セルにG列の合計が入ります。

こういう雛形の見積明細書で例えばC6セルに「小計」と入力すると、G2セル～G5セル間での小計をG6セル表示させるようにし、G7セルからは通常通りD列×F列の結果が表示され、さらにC15セルに「小計」と入力すると、G7セル～G14セル間での小計をG15セル表示させるようにし、最終的には全ての小計の合計がG84セルに結果表示させるようなことは可能でしょうか？

マクロでないと出来ないなら、当方は知識を全く持ち合わせておりませんので、かなり困難かと思いますので、関数だけで対処できると助かります。
不足情報は補足致します。
よろしくお願い致します。

回答No.10

zap35 · ベストアンサー

＃０５です
COUNTIFではなくSUMIFでした

お礼

ありがとうございます！
これで全てうまくいきました。
本当に助かりました。

回答No.9

zap35

＃０５です。今思いつきました

G84の式は
=SUM(G2:G83)-COUNTIF(C2:C83,"小計",G2:G83)
でいけるでしょう

補足

度々ありがとうございます。

=SUM(G2:G83)-COUNTIF(C2:C83,"小計",G2:G83)
の数式ですと、「この関数に対して、多すぎる引数が入力されています・・・」と出てしまいます。

本当に申し訳ありませんが、ご教示よろしくお願い致します。

回答No.8

zap35

＃０５です。
＞D2及びF2を空欄にしますと「#Value!」が出てしまいます
そうですか？　私が試してもなりません。G列の値は0になるだけです。
実際の値を見ないと何とも言えませんが、エラー値からすると「空欄」ではなく文字の「空白」を入力したのではありませんか？

式最後のD2*F2を
IF(AND(ISNUMBER(D2),ISNUMBER(F2)),D2*F2,"ERROR")
とすれば計算できない文字が入力されたときチェックはできますが、むしろ#VALUE!のままのほうがエラーがすぐに分かって良いのではないでしょうか。

この式では小計がない場合は想定していません。
マクロで良ければ前回の質問にも書いたSUBTOATAL関数を小計欄に挿入できますが、「マクロはダメ」ならどうしようもないです

＞この数式、惜しいです。いい線いってます！
質問して回答してもらっている立場の方のセリフとは思えませんね
悪気はないのでしょうが、愉快ではありません

お礼

ありがとうございます。

>そうですか？　私が試してもなりません。G列の値は0になるだけ
>です。
>実際の値を見ないと何とも言えませんが、エラー値からすると
>「空欄」ではなく文字の「空白」を入力したのではありませんか？

仰せのとおりでした。
失礼致しました。

>マクロで良ければ前回の質問にも書いたSUBTOATAL関数を小計欄
>に挿入できますが、「マクロはダメ」ならどうしようもないです

マクロはゆくゆく勉強しなくてはいけない、とは思っておりますが、何分本職ではなく、片手間にやっているのでなかなか・・・。
そういう理由であれば仕方がありませんね。
この件はあきらめます。

>悪気はないのでしょうが、愉快ではありません

お気に障られたのであればお詫びいたします。
大変失礼致しました。

回答No.7

NNAQ

まず、
=IF(COUNT(F2)=0,"",IF(COUNT(D2)=1,SUM(D2*F2),F2))
↑の式は、
=IF(ISNUMBER(F2),PRODUCT(D2,F2),"")
で、良いかも。

で、本題はそれほどややこしくなく、いけそうです。
G2=IF(C2="小計",SUM($G$1:G1)-SUMIF($C$1:C1,"小計",$G$1:G1)*2,IF(ISNUMBER(F2),PRODUCT(D2,F2),""))
下へコピー。

G84=SUMIF(C2:C83,"<>小計",G2:G83)

お礼

ありがとうございます。

>=IF(COUNT(F2)=0,"",IF(COUNT(D2)=1,SUM(D2*F2),F2))
>↑の式は、
>=IF(ISNUMBER(F2),PRODUCT(D2,F2),"")
>で、良いかも。

なにしろIFかSUMしか知らないもので・・・。
勉強になりました。

回答No.6

abekkan

＃１です。
ご指摘のとおり、2行目に限っては違いましたね。すいません。
で、書き直します。

H列を作業用の列にします。（印刷の対象外または非表示）
1行目はタイトルで、2行目からデータが入っているとすると、

G2に、=IF(C2="小計",0,IF(COUNT(F2)=0,"",D2*F2))
H2に、=IF(C2="小計",0,IF(COUNT(F2)=0,0,G2))
G3に、=IF(C3="小計",H2,IF(F3="","",D3*F3)) と書いてG4～G83にコピー。
H3に、=IF(C3="小計",0,IF(COUNT(F3)=0,0,H2+G3))　と書いてH4～H83にコピー。

（最後が小計で終わることを前提とする場合）
G84に、=sum(G2:G83)/2 と書く。

（そうでない場合）
Ｉ列も作業用とし、I2に、=IF(C2<>"小計",G2,0)と書いてI3～I83にコピー。
G84に、=sum(I2:I83)　と書く。

これで一応できると思います。あそこも空欄になった場合、とかさらなる条件がある場合はやはりＶＢＡでしょうか。

マクロやＶＢＡを毛嫌いされているようですが、このレベルの関数を考えるより、マクロやＶＢＡをちょっとかじるほうが簡単です。また、複雑な関数はあとで他の人（や自分さえも）理解できなくなってメンテナンスができない、となる危険があります。

お礼

ありがとうございます。
マクロやVBAを毛嫌いしているわけではありません。
こういう作業を本職にしていないので、なかなかスキルアップに費やす時間がないので、可能な手段を出来るだけ駆使してできれば、と思っているだけなのです。
もちろん時間さえあれば、もっともっとスキルアップに努めるんですが・・・。

回答No.5

zap35

少し長い式ですがG2セルに以下の式を貼り付けて下方向にコピーしてみてください

=IF(C2="小計",IF(COUNTIF($C$1:C1,"小計")=0,SUM(INDIRECT("G$2:G"&ROW(A2)-1)),SUM(INDIRECT("G$2:G"&ROW(A2)-1))-SUM(INDIRECT("G$2:G"&MAX(INDEX(($C$1:$C1="小計")*ROW($C$1:$C1),))))),D2*F2)

G84の式は
=SUMIF(C2:C83,"小計",G2:G83)
になります

補足

ありがとうございます。
この数式、惜しいです。いい線いってます！
但し、問題点があります。

1)
3行目以降はこの数式でバッチリなのですが、2行目（データ入力行の1行目）にこの数式を使った場合、D2及びF2を空欄にしますと「#Value!」が出てしまいます。
何か妙案はありますか？

2)
「小計」を全く使用しなかった場合、合計が「0」になってしまいます。
「小計」を全く使用しないケースは少ないとは思うのですが、可能性は０ではないので、何か妙案はありますか？

以上、お手数とは存じますが、よろしく御教示お願い致します。

回答No.4

mu2011

前回のご質問の折り、回答したものですが、小計位置の自在はＶＢＡでないと出来ないと思います。
為参考ですが、小計／合計位置を固定にして小計欄には=SUBTOTAL(9,小計範囲）、合計欄には=SUBTOTAL(9,合計範囲)としておくと、
合計欄には小計値が計数されなくなるのですが如何でしょうか。

お礼

お世話になります。
合計位置は固定でも問題ないのですが、やはり小計の固定は困難ですので、VBA・・・ちょっと抵抗が・・・そうも言ってられないですかね？

回答No.3

myandriver

Ｎｏ２で説明不足がありました
集計したいＨ列は同じ名前にして下さい。
そうしないと集計がばらばらになします。

回答No.2

myandriver

Ｈ列に仮に注文者名のようなものを作り（印刷時表示させない）
Ｈ列で集計させます。
ただし合計欄は削除しておきます。
集計の仕方は、データ→集計をクリック
グループの基準をＨ列もしくは仮タイトル名注文者にし
集計の方法を合計にし
集計する項目を金額にチェックを入れます
現在の集計表と置き換える・集計行をデータの下に挿入する、にチェックを入れてＯＫをクリック
これでＨ列の注文者ごとに小計が着いた表になるはずです。
この作業をマクロにしておけば簡単だと思います

お礼

ありがとうございます。
マクロもゆくゆくは勉強して使えるようにならないとは思うのですが、現状、何も出来ません。
せっかくのご回答でしたが、関数のみで対処できる方法で考えていきたいと思います。
マクロが出来れば関数を延々並べるよりも簡単なのでしょうが・・・。

回答No.1

abekkan

H列を作業用の列にします。（印刷の対象外または非表示にしてください）
1行目はタイトルで、2行目からデータが入っているとすると、
G2に、=IF(C2="小計",H1,D2*F2)　と書いてG3～G83にコピー。
H2に、=IF(C2="小計",0,H1+G2)　と書いてH3～H83にコピー。
G84に、=sum(G2:G83)/2 と書く。

（説明）
Hの作業列は累積金額を表示、ただしCが「小計」だった場合は0とする。
Gの列は数量×単価を表示、ただしCが「小計」だった場合はH列の１つ上の行を示すことによりそこまでの小計を表示する。
G84は、小計分が2重にカウントされるので2で割る。
というようになっています。

関数だけで何とかしたい場合は、作業用の列を使う、というのが私のやりかたですが、これでどうでしょうか。

補足

ありがとうございます。

>G2に、=IF(C2="小計",H1,D2*F2)　
これは、
G2に、=IF(C2="小計",H2,D2*F2)
の誤りですよね？

>H2に、=IF(C2="小計",0,H1+G2)　
これは、
H2に、=IF(C2="小計",0,G2)
ではないでしょうか？
　　　※2行目は加算する必要がないと思いますので・・・。
で、3行目が、
=IF(C3="小計",0,H2+G3)
ですよね？

実は現状のG2セルには、空白時に「value」が出ないように
=IF(COUNT(F2)=0,"",IF(COUNT(D2)=1,SUM(D2*F2),F2))
という数式が入っています。

このあたりを踏まえて、G2、G3、H2、H3各セルに入力する数式をご教示頂けないでしょうか？
お手数ですが、よろしくお願いいたします。