2013年01月18日

経過月の計画の合計を算出したい(INDEX関数・COUNTA関数・SUM関数)[Excel]


index&counta&sum_例.gif
上のように、各月に「計画」と「実績」のセルがあり、「計画」は年度末まで入力されています。
「実績」が入力された月までの「計画」の合計を出すのに、毎月数式を修正しています。
なんとかなりませんか?

と、ご質問を頂きました!

なんとかなりますよexclamation


経過月の「計画」の合計とは、
12月の「実績」(セルG3)が入力されたら、10月〜12月の「計画」の合計『=B3+D3+F3』
1月の「実績」(セルI3)が入力されたら、10月〜1月の「計画」の合計『=B3+D3+F3+H3』
としたいということだそうです。
index&counta&sum_合計例.gif
これを言い換えると、、、

セルB3から、「実績」が入力されている内の一番右のセルまでの合計から、「実績」の合計(セルO3)を引いたもの ですよね!
index&counta&sum_合計例2.gif

ということは、「実績」が入力されている内の一番右のセル番地がわかればいい!

それには、INDEX関数&COUNTA関数を使います。



まず、COUNTA関数で「実績」が何か月分入力されているか算出してみましょう。

=COUNTA(C3,E3,G3,I3,K3,M3)』 で、答え『3』 となります。(10月、11月、12月の3ヶ月)
index&counta&sum_counta.gif


各月に「計画」と「実績」の2つのセルがあるので、
「実績」が入力されている内の一番右のセル番地とは、範囲:セルB3からセルM3の内の
「実績」が入力されているセルの個数の2倍の個数目ひらめき

=INDEX(B3:M3,COUNTA(C3,E3,G3,I3,K3,M3)*2)
index&counta&sum_index.gif


これで、「実績」が入力されている内の一番右のセル番地がわかったので、

セルB3からそのセル番地までの合計 − 「実績」の合計(セルO3 で、

=SUM(B3:INDEX(B3:M3,COUNT(C3,E3,G3,I3,K3,M3)*2))-O3』 で、、
index&counta&sum_式.gif

答え『15』となりましたぴかぴか(新しい)


ちなみに、1月の「実績」に数値を入力すると、、、答え『18
数式を変えずに10月〜1月の「計画」の合計となりましたぴかぴか(新しい)ぴかぴか(新しい)
index&counta&sum_結果2.gif


関数の中に関数、そして関数の組み合わせ・・・頭を柔らかくして頑張りましょうかわいい


posted by Ponta at 21:24| Comment(0) | TrackBack(0) | Excel実践 | このブログの読者になる | 更新情報をチェックする
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。