id:tatyu
ポイントあり80pt回答受付終了
コンピュータ

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


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


５月までなら

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


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

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

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

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




　　　[A]　　　[B]　　　　[C]
[1]　４月　　　10　　　　　8
[2]　５月　　　20　　　　　21
[3]　６月　　　30
[4]　７月　　　40
[5]　８月　　　50
[6]　９月　　　60
--------------------------------------
　　　　　　　　　　累計昨年対比 96%

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

回答の条件
  • URL必須
  • 1人2回まで
  • 登録：
  • 終了：--
※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

回答（4件）

id:aki73ix No.1

回答回数5224ベストアンサー獲得回数27

ポイント20pt

http://nifberry.727.net/test/hatena23.xls

4月の累計昨年対比に

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

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

「$」を活用しましょう

id:tatyu

ごめんなさい！

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

右

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

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

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

回答回数1782ベストアンサー獲得回数96

ポイント20pt

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

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

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

[1]　４月　　　10　　　　　8

[2]　５月　　　20　　　　　21

[3]　６月　　　30

[4]　７月　　　40

[5]　８月　　　50

[6]　９月　　　60

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

　　　　　　　　　　累計昨年対比 96%

[E1]=B1

[E2]=E1+B2

・・・

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

[F1]=C1

[F2]=F1+C2

・・・

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

として

[D1]=F1/E1

[D2]=F2/E2

・・・

[Dn]=Fn/En

でどうでしょう？

id:tatyu

うう、すみません〜

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

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

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

回答回数6ベストアンサー獲得回数0

ポイント20pt

http://www.yahoo.co.jp/

Yahoo! JAPAN

　　[A]　　　[B]　　　　[C]　　　　［D］（分母）

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

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

[3]　６月　　　30

[4]　７月　　　40

[5]　８月　　　50

[6]　９月　　　60

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

id:tatyu

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

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

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

回答回数196ベストアンサー獲得回数0

ポイント20pt

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

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

まずD列の各行に

=IF(C1>0,B1,0)

=IF(C2>0,B2,0)

　　　．

　　　．

　　　．

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

次に、

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

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

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

id:tatyu

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

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

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

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

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

2004/06/23 17:10:57

コメント（10件)

  • id:cx20
    cx20 2004/06/23 17:12:05
    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
    ponta3rd 2004/06/23 17:18:03
    Re:1行で・・・。

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

    ピボットテーブルを利用するもの良いかもしれませんね。
    表が大きくなったり、小さくなったりするようなものに
    対処できますので。
  • id:cx20
    cx20 2004/06/24 00:28:38
    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

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

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

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

回答リクエストを送信したユーザーはいません
ユーザー登録をして使いはじめる!無料
すでに登録されている方はこちらからログイン
この質問に含まれるキーワード
知りたいことを検索してみよう
▲ページトップへ