[[20121114113711]]　『SUMIF関数とSUBTOTAL関数を組み合わせたい』（とも）　ページの最後に飛ぶ

『SUMIF関数とSUBTOTAL関数を組み合わせたい』（とも）

 過去履歴等、調べてみたのですが、なんだかちょっとづつ違うようで、
 理解ができず、質問させていただきました。

 A____	B____	C____	D____	E____	F____
	税区分	A	B	C	金額
○○会社		15,000			15,000
××会社	非課税			200,000	200,000
△△会社			5,000		5,000
○○会社		10,000			10,000
○○会社			750		750

				小計	230,750
				非課税	200,000
				課税	30,750
				消費税	1,537
				総計	232,287

 としていて、A列でﾌｨﾙﾀをかけ、各会社ごとに資料を印刷しています。

 E8  =SUBTOTAL(9,F2:F6)
 E9  =SUMIF(B2:B6,"非課税",F2:F6)
 E10 =F8-F9
 E11 =ROUNDDOWN(F10*5%,0)
 E12 =SUM(F9:F11)

 としているのですが、問題なのはｾﾙE9の関数なのですが、
 例えば、A列の○○会社をﾌｨﾙﾀかけた場合、

 A____	B____	C____	D____	E____	F____
○○会社		15,000			15,000
○○会社		10,000			10,000
○○会社			750		750

				小計	25,750
				非課税	200,000
				課税	-174,250
				消費税	-8,712
				総計	17,038

 となり、××会社の非課税の金額はそのまま表示されてしまいます。
 私の希望としては下記のように、

 A____	B____	C____	D____	E____	F____
○○会社		15,000			15,000
○○会社		10,000			10,000
○○会社			750		750

				小計	25,750
				非課税	0
				課税	25,750
				消費税	1,287
				総計	27,037
 こういう結果を得たいのです。
 E9セルの関数を教えてください。
 よろしくお願いいたします。

 (とも)

 ■作業列を使う

 G2: =IF(SUBTOTAL(3,A2),F2,"")　　G6までオートフィル

 ＞E9セルの関数を教えてください。

 E9かF9か、どっちかわかりませんけど

 =SUMIF(B2:B6,"非課税",G2:G6)
　
　
 ■作業列を使わない

 E9かF9か、どっちかわかりませんけど（しつこい？）

 =SUMPRODUCT((B2:B6="非課税")*SUBTOTAL(9,INDIRECT("F"&ROW(F2:F6))))

 （よみびとしらず）

 (よみびとしらず)さん

 できました！！
 作業列をを使わない方法で早速関数を使ってみました。
 完璧でした。
 本当に助かりました。ありがとうございます。

 ところで、この関数について質問です。

 >=SUMPRODUCT((B2:B6="非課税")*SUBTOTAL(9,INDIRECT("F"&ROW(F2:F6))))

 ですがSUMPRODUCT関数は理解しています･･･次のSUBTOTAL～でINDIRECT("F"&ROW(F2:F6)が
 どうも理解不足で、意味を教えてください。
 もちろん、SUBTOTAL(9,F2:F6)では正しい結果が求められないのは分かっているのですが、
 INDIRECT("F"&ROW(F2:F6)はどう解読するのか分かりません。
 "F"列？　F2～F6の範囲？

 今後の知識に役立てたいのでぜひとも教えてください。

 (とも)

 SUBTOTAL(9,範囲) → フィルタ結果範囲の合計を返す

 SUBTOTAL(9,INDIRECT("F"&ROW(範囲)) → フィルタ結果範囲毎の数値を配列で返す。

 (GobGob)

 SUMPRODUCTが理解できてるというのは、配列数式もわかってるってことかな？

 配列数式の基本的なことはわかってるという前提で説明します。
 えっえっ何それ？ってことやったら、ネットで調べてみてください。
　
　
 作業列で考えるとわかりやすいんですけど

 G2: =(B2="非課税")*SUBTOTAL(9,F2)　　G6までオートフィル

 非課税の合計は　=SUM(G2:G6)　でもええはずです。
　
　
 これを配列数式にしようと思って ↓ にしたところで

 =SUMPRODUCT((B2:B6="非課税")*SUBTOTAL(9,F2:F6))

 B2:B6="非課税"　のところは配列になっても
 SUBTOTAL(9,F2:F6)　はあくまで F2:F6 の合計であって配列ではありません。

 これを配列にするために　SUBTOTAL(9,INDIRECT("F"&ROW(F2:F6)))　としているということ。

 サンプルデータで言えば、INDIRECT("F"&ROW(F2:F6))　は
 {15000;200000;5000;10000;750}　という配列

 ○○会社でフィルタをかけると、SUBTOTAL(9,INDIRECT("F"&ROW(F2:F6)))　は
 {15000;0;0;10000;750}　という配列になります。

 今後に役立てるといっても、こういう使い方はめったにしませんけどね。
 それとINDIRECTは揮発性関数なんで、あまり重たい数式と併用すると計算速度が遅くなりますので、ご注意を。

 （よみびとしらず）

 ありがとうございます。
 参考にさせていただきます。

 (とも)

コメント返信：

[ 一覧(最新更新順) ]

[[20121114113711]] 『SUMIF関数とSUBTOTAL関数を組み合わせたい』（とも） ページの最後に飛ぶ

[[20121114113711]]　『SUMIF関数とSUBTOTAL関数を組み合わせたい』（とも）　ページの最後に飛ぶ