[[20050110121805]]　『自動的に小計、合計を行いたい』（KI）　ページの最後に飛ぶ

『自動的に小計、合計を行いたい』（KI）

 Ａ列に商品名、Ｂ列に売上金額があります。
Ａ列には商品名のほか、いくつかの小計と合計欄及び空白があります。
Ｂ列の数値は別シートを参照してｓｕｍｐｒｏｄｕｃｔ関数で集計した値をいれるようにしています。
但し、小計、合計の行番号は毎回かわります。
下表ですと、Ｂ7とＢ14が小計でＢ20が合計ですが、Ａ列の小計とか合計を判断して勝手に
集計してくれるような都合の良い、わがままな方法はないでしょうか。
小計、合計行の位置を決めてしまえば苦労することもないんですが・・・。それが出来ないんです。
 	Ａ	Ｂ	Ｃ
1	商品名	1月売上	
2	aaa1	1000	
3	aaa2	1200	
4	aaa3	500	
5			
6			
7	小計	2700	
8	bbb1	1200
9	bbb2	900
10	bbb3	800
11	bbb4	1000
12	bbb5	1100
13		
14	小計	5000
15	ccc1	800
16	ccc2	600
17	ccc3	400
18	ccc4	800
19	小計	2600
20	合計	10300

 合計の式は、=SUMIF($A$1:A19,"小計",$B$1:B19)　などですが、
 位置が決められないの意味が良く判りません。
 （川野鮎太郎）

 川野様ありがとうございます。
説明不足で申し訳ありません。月によりＡ列の商品明細が変化しますので
小計欄の行位置もそれにより変わってしまうという事です。
小計という文字列を見つけた場合に、自動的に小計をやって欲しいという
ことなんです。よろしくお願いします。
(KI)

 IFの分岐だけですが・・・。
そのSUMPRODUCTの式が分かりませんので、そこは省略します。B2に
=IF(A2="","",IF(A2="小計",SUM($B$1:B1)-SUMIF($A$1:A1,"小計",$B$1:B1)*2,SUMPRODUCT()))
としで下方へコピーで、どうでしょう？
（ケン）

 ケンさん、こんにちは。
 以前どこかで見たことあるけどなと思った式がそれですね。
 確かみやほりんさんが、SUFIFの数値*2を引くことに感心しておられたような記憶があります。＾＾
 私が上の式を見る前に、配列式で
B14=SUM(OFFSET(B14,-1,,MAX(IF($A$1:A13="小計",ROW($A$1:A13)))+1-ROW()))
 これで　5000　が返ってくるので、使えるかなと思ったら・・・
=IF(A14="小計",SUM(OFFSET(B14,-1,,MAX(IF($A$1:A13="小計",ROW($A$1:A13)))+1-ROW())),"")　とすると
 1200が返ってきてしまうんですよね・・・＿/￣|○ il||li
 なぜなのかわかんないぃぃ・・・。

 判りました。ROW()　がガンでした。ROWS関数にしたら上手くいきました。
 =IF(A2="小計",SUM(OFFSET(B2,-1,,MAX(IF($A$1:A1="小計",ROW($A$1:A1)))+1-ROWS($A$1:A2))),"")

 KIさん、お邪魔しました。　私の書いている式は配列数式なので実用性はあまり無いかもしれません。
 お使いになるのはケンさんの式でお願いします。
 （川野鮎太郎）

 原因が分かってなによりです。
（ケン）

 ケン様、川野様有難うございます。少しの間出かけてたのでお返事が遅れました。
ケン様ので小計ができました。完璧です。
お願いばかりで申し訳ありませんが、合計の計算はできますでしょうか？
実は、下表のように小計がいくつかあって合計、そしてまたその下側に小計が
いくつかあって合計があるといった具合なんですが。
 	A	B	C
1	aaa1	100	
2	aaa2	200	
3	aaa3	300	
4	小計	600	
5	bbb1	200	
6	bbb2	100	
7	bbb3	150	
8	bbb4	200	
9	小計	650	
10	合計	1250	
11	ccc1	100	
12	ccc2	300	
13	ccc3	400	
14	小計	800	
15	ddd1	100	
16	ddd2	200
17	小計	300
18	合計	1100
19		
20	
　　　(KI)

 試してないですけど、ケンさんの式の小計の部分を合計に変えれば出来るんじゃないですか。
 *2の部分を*3か*4ぐらいにしないといけないかも。
 ※すいません・・・、ウソ言ってました。

 =IF(A2="","",IF(A2="小計",SUM($B$1:B1)-SUMIF($A$1:A1,"小計",$B$1:B1)*2-SUMIF($A$1:A1,"合計",$B$1:B1),IF(A2="合計",(SUM($B$1:B1)-SUMIF($A$1:A1,"合計",$B$1:B1)*3)/2,SUMPRDUCTの式)))
 でした。
 （川野鮎太郎）

 ケン様、川野様有難うございます。合計も計算できました。
今、ずーと式を検証してたんですが、難しい式ですね。
何となく雰囲気的には理解できるところまでは来たんですが
もう少し時間がかかりそうです。でもこれで明日、会社の仕事に
役立ちそうです。本当に有難うございました。またよろしくお願い致します。
                          (KI)

コメント返信：

[ 一覧(最新更新順) ]

[[20050110121805]] 『自動的に小計、合計を行いたい』（KI） ページの最後に飛ぶ

[[20050110121805]]　『自動的に小計、合計を行いたい』（KI）　ページの最後に飛ぶ