EXCELで、以下のような表を作っています。


やりたいことは、「今年度実績数」を入れた月の数値から
昨年と比べての進捗の比率を出したいのです。


5月までなら

 = SUM(C1:C2)/SUM(B1:B2) =(8+21)/(10+20) =96%


6月の実績数に 40 と入れた場合

= SUM(C1:C3)/SUM(B1:B3) = (8+21+40) / (10+20+30)

みたいな感じで、115% という結果が出ればいいのですが。

データを入れていく都度、式を変えるのが面倒でして、
VBAではなく、なんとか「関数」だけで実現は可能でしょうか?




   [A]   [B]    [C]
[1] 4月   10     8
[2] 5月   20     21
[3] 6月   30
[4] 7月   40
[5] 8月   50
[6] 9月   60
--------------------------------------
          累計昨年対比 96%

よろしくお願い致します。

回答の条件
  • URL必須
  • 1人2回まで
  • 登録:2004/06/23 16:35:43
  • 終了:--

回答(4件)

id:aki73ix No.1

aki73ix回答回数5224ベストアンサー獲得回数272004/06/23 16:44:06

ポイント20pt

4月の累計昨年対比に

=SUM($C$1:$C1)/SUM($B$1:$B1)

をいれて、セルの右下をドラッグして引っ張って他の月に適用すればOKです

「$」を活用しましょう

id:tatyu

ごめんなさい!

セルの場所は1つなんです。月別での進捗なら分かるんですが・・・

下に書いたのがそういうつもりでした。

またご伝授よろしくお願い致します!!

2004/06/23 16:46:47
id:garyo No.2

garyo回答回数1782ベストアンサー獲得回数962004/06/23 16:45:36

ポイント20pt

http://www.vector.co.jp/

Vector:ソフトウェア・ライブラリ&PCショップ

   [A]   [B]    [C]    [D]    [E]    [F]

[1] 4月   10     8

[2] 5月   20     21

[3] 6月   30

[4] 7月   40

[5] 8月   50

[6] 9月   60

--------------------------------------

          累計昨年対比 96%

[E1]=B1

[E2]=E1+B2

・・・

[En]=En-1+Bn (Bの同じ行と1つ上のセルの和

[F1]=C1

[F2]=F1+C2

・・・

[Fn]=Fn-1+Cn (Cの同じ行と1つ上のセルの和

として

[D1]=F1/E1

[D2]=F2/E2

・・・

[Dn]=Fn/En

でどうでしょう?

id:tatyu

うう、すみません〜

「累計昨年対比」を表示したいセルが”1カ所”なんです。

関数で何か実現できる方法を是非ご伝授をお願いします。

2004/06/23 16:53:27
id:atsuatsu No.3

atsuatsu回答回数6ベストアンサー獲得回数02004/06/23 16:55:18

ポイント20pt

  [A]   [B]    [C]    [D](分母)

[1] 4月   10     8    =if(C1=””,0,B1)

[2] 5月   20     21   =if(C1=””,0,B1)

[3] 6月   30

[4] 7月   40

[5] 8月   50

[6] 9月   60

D列にこのように式を入れて、累計昨年対比の分母にすればいけると思いますが。

id:tatyu

あ!あ!分かったような気がします。

(仰せの内容を検討しておりましたら、一人で大混乱してまして、回答遅れてすみませんでした)

2004/06/23 17:04:35
id:ponta_3rd No.4

ponta3rd回答回数196ベストアンサー獲得回数02004/06/23 16:55:30

ポイント20pt

http://www.hatena.ne.jp/1087976143###

EXCELで、以下のような表を作っています。 やりたいことは、「今年度実績数」を入れた月の数値から 昨年と比べての進捗の比率を出したいのです。 5月までなら  = SUM(C1:.. - 人力検索はてな

まずD列の各行に

=IF(C1>0,B1,0)

=IF(C2>0,B2,0)

   .

   .

   .

の様に計算式を入れて、D行を丸ごと非表示にします。

次に、

=SUM(C1:C6)/SUM(D1:D6)

で、非表示の場所と割り算すれば、

C列に値が入っているところまでで計算できると思いますがこの様な方法ではいかがでしょうか。

id:tatyu

あっ!そうですね。どうもありがとうございます。

SUM関数から頭が抜けず、どうしても B1:C1 みたいな範囲が頭をぐるぐるとしてました。

別の列という発想がございませんでした。

一人大混乱ですみませんでした。

皆様、どうもありがとうございました。

2004/06/23 17:10:57
  • id:cx20
    1行で・・・。

    かなり強引なやり方ですけど、1行でやる方法です(汗)(^^A

    =SUM(C1:C6)/SUM(B1:OFFSET(B1,COUNT(C1:C6)-1,0))

    <説明>
    COUNT(C1:C6) … 実績の月数を取得(例:2ヶ月)
    OFFSET(B1,COUNT(C1:C6)-1,0) … B1からの月数ぶん進んだ位置を取得(例:B1+2-1 = B2)
    SUM(B1:OFFSET(B1,COUNT(C1:C6)-1,0)) … 実績と同じ集計期間で集計(例:B1〜B2)
  • id:ponta_3rd
    Re:1行で・・・。

    これ良いですね。
    今まで、セル非表示ばかりでしたから。
    とても、勉強になりました。
  • id:aki73ix
    Re(2):1行で・・・。

    終わっちゃってますが、こんな感じですね

    http://nifberry.727.net/test/hatena23a.xls
  • id:garyo
    もっと強引ですが1行で・・・。

    もっと強引ですが、一行でできます。実用性はあまりありませんが(^^;)

    =SUM(C1:C6)/(B1*SIGN(C1)+B2*SIGN(C2)+B3*SIGN(C3)+B4*SIGN(C4)+B5*SIGN(C5)+B6*SIGN(C6))
  • id:tatyu
    ありがとうございました

    cx20さん
      OFFSET関数での方法、ご丁寧に大変勉強になりました。
      実際は、表が縦ではなく横なのでOFFSET関数でハマって
      しまいましたが、完成しました!
    ponta_3rdさん
      情報のご提供どうもありがとうございました。
      別セルで非表示が思いつかず、SUM関数から頭が
      離れませんでした。
    aki73ixさん
      私の当初の質問内容が悪くてスミマセンでした。
      またサンプルの方も大変ありがとうございました。
      大変助かります!勉強させて頂きます。
    garyoさん
      数値がゼロかどうかをSIGN関数で掛ける方法ですね。
      これもこういう手段があるのかと大変勉強になります。

    皆さん、本当にありがとうございました!!!

    ポイントが缶ジュース1本程度にまでなってしまいまして、
    かなり締め切りが早くて申し訳ございません。

    またポイント補充しましたら、EXCELの新たな質問(社内要望・・・
    ポイントは私のなんですけど)が出てきてしまいました。

    近日中に質問させて頂くと思いますが、どうぞよろしくお願い致します。
  • id:ponta_3rd
    Re:もっと強引ですが1行で・・・。

    これもすごい。
    offsetはXXの位置のセルをって云うのを計算で求められて良いと思いましたが、SIGNは、確実にC列に値が入っている箇所だけで計算をするので
    これも良いですね。
    計算範囲が広くなってしまうと、非表示列でやってしまいますが、
    それ以外のテクニックとして勉強になりました。
  • id:higesennin
    Re:ありがとうございました

    このような場合は「SUMIF」関数を使ってください。

     =SUM(C1:C6)/SUMIF(C1:C6,”>0”,B1:B6)


    http://t-amma.com/
  • id:tatyu
    Re(2):ありがとうございました

    >このような場合は「SUMIF」関数を使ってください。
    >
    > =SUM(C1:C6)/SUMIF(C1:C6,”>0”,B1:B6)
    >
    >
    higesenninさん、ありがとうございます!
    こんな方法もあるんですね。
    さらに勉強になります。ありがとうございます。
  • id:SmcrHash
    回答にはなってませんが

    ピボットテーブルを利用するもの良いかもしれませんね。
    表が大きくなったり、小さくなったりするようなものに
    対処できますので。
  • id:cx20
    Re(2):ありがとうございました

    > =SUM(C1:C6)/SUMIF(C1:C6,”>0”,B1:B6)

    SUMIF() 関数の存在を知りませんでした(汗)
    簡潔(シンプル)でいいですね。

    > =SUM(C1:C6)/SUM(B1:OFFSET(B1,COUNT(C1:C6)-1,0))

    なんて、セルに埋め込まれてたら、メンテする人が大変そうです。
    (たぶん、自分でも2度目に見たら、何やってるんだ?と考えてしまいそう(^^A)

    http://www.officetanaka.net/excel/function/function/offset.htm

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません