EXCELで残りの日数の平均の出し方が分かりません。

質問

EXCELで残りの日数の平均の出し方が分かりません。

具体的に説明させて頂きます。
月間の目標売上げを定め、月の残り日数で一日幾ら売り上げれば目標に達成するかを実数字で知りたいのです。

例：目標120万円　　30日間で平均4万円の売上げが必要
　　1日の売上げ10万円　残り110万円÷29日＝37,931円が必要
　　2日の売上げ2万円　残り108万円÷28日＝38,571円が必要
　　3日の売上げ9万円　残り99万円÷27日＝36,666円が必要
　　・
　　・
　　・
　　・

1日に必要な金額を一つのセルに出したいのですが、簡単に関数を組むだけで出来るのでしょうか？また、セルを組むときに、0を含めないような組み方をしたいのですが・・・

回答No.4

KURUMITO

回答Ｎｏ3です。
EOMONTH(A$1,0)の代わりに次のように置き換えても良いでしょう。

DATE(YEAR(A$1),MONTH(A$1)+1,0)

この式でその月の月末の年月日が求められます。

回答No.3

KURUMITO

月間の売り上げ目標を30日で平均4万円になるようにするとのことですかすから、31日までの月や28日までの月もあることですから初めに何年何月の計算をするかを決めることが必要ですね。
例えばＡ1セルに2010/9と入力します。数式バー上では2010/9/1と表示されますね。右クリックして「セルの書式設定」から「表示形式」でユーザー定義を選び種類の窓に　m"月" と入力してＯＫすれば9月と表示されます。
Ａ2セルには当月売上目標とでも入力します。
Ｂ2セルには次の式を入力して30日平均で当月の目標額を表示させます。

=DAY(EOMONTH(A1,0))*40000

EOMONTH(A1,0)はＡ1セルに表示した月の最終日の年月日を求めるものです。ＤＡＹ関数で最終日の日にちを求め、それに1日当たり40000円ですから40000を掛けています。

Ａ3セルには日付、Ｂ3セルにはその日の売上額、Ｃ3セルには翌日の売上必要額とでも入力します。
Ａ4セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(A$1="",MONTH(A$1+ROW(A1)-1)<>MONTH(A$1)),"",A$1+ROW(A1)-1)

2010/9/1から月末までの日付が表示されます。単に1日、2日のように表示させるのでしたらＡ4セルから下方を選択して表示形式のユーザー定義で d"日" と入力してＯＫします。

Ｂ4セルから下方にはその日の売上高を入力します。
Ｃ4セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(A4="",A4=EOMONTH(A$1,0)),"",INT((B$2-SUM(B$4:B4))/(EOMONTH(A$1,0)-A4)))

なお、Ｂ列の売上高が入力されていない場合にＣ列の値を空白にするのでしたらＣ4セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(A4="",B4="",A4=EOMONTH(A$1,0)),"",INT((B$2-SUM(B$4:B4))/(EOMONTH(A$1,0)-A4)))

回答No.2

kagakusuki

以下の様な方法は如何でしょうか。
　今仮に、A2セルに、その月の名称を

2010年9月

という書式で入力すると、A5セル～A35セルにかけて、その月の日付が表示され、続いてその月の目標売上をC2セルに入力した上で、B5セル～B35セルに、その日の売上を入力すると、自動的に、同じ行のC列のセルにその日の目標売上の残り額が、D列のセルに次の日以降の平均売上目標が、それぞれ表示されるものとします。

　まず、A2セルの書式設定の表示形式を、分類欄を日付、種類欄を

○○○○年○月

の形式に設定し、次にA5セルの書式設定の表示形式を、分類欄を日付、種類欄を

○月○日

の形式に設定して下さい。
　次に、A5セルに次の数式を入力して下さい。

=IF(ROWS($4:4)>DAY(DATE(YEAR($A$2),MONTH($A$2)+1,)),"",DATE(YEAR($A$2),MONTH($A$2),ROWS($4:4)))

　次に、C5セルに次の数式を入力して下さい。

=IF(ISNUMBER($B5),$C$2-SUM($B$5:$B5),"")

　次に、D5セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER($B5),ISNUMBER($B6)),$C5/(DATE(YEAR($A$2),MONTH($A$2)+1,)-$A5),"")

　そして、A5～D5の範囲をまとめてコピーして、A5～D35の範囲に貼り付けて下さい。

　以上でフォームは完成で、後は、A2セルにその月の名称を、C2セルにその月の目標売上を、それぞれ入力した上で、B5及びその以下のセルに、その日の売上を入力して行けば、同じ行のC列のセルにその日の目標売上の残り額が、D列のセルに次の日以降の平均売上目標が、それぞれ自動的に表示されます。

　尚、土日祝日や盆正月等を除外した日数で、平均売上目標を求める場合には、別シートの適当な列（例えば、Sheet13のA列）に前もって

2010/1/1
2010/1/2
2010/1/3
　　・
　　・
　　・
　　・
2010/12/23
2010/12/31

という具合に、土日以外のその年の休業日のリストを作成しておいてから、D5セルに入力する数式を、次の様な数式に変更して下さい。

=IF(AND(ISNUMBER($B5),ISNUMBER($B6)),$C5/SUMPRODUCT((WEEKDAY(OFFSET($A5,,,DATE(YEAR($A$2),MONTH($A$2)+1,)-$A5),2)<6)*(COUNTIF(Sheet13!$A:$A,OFFSET($A5,,,DATE(YEAR($A$2),MONTH($A$2)+1,)-$A5))=0)),"")

　尚、上記の数式はNETWORKDAYS関数を使えば、もっと単純になると思われるのですが、私が使用しているパソコンに入っているExcelでは、NETWORKDAYS関数が使えなかったため、他の方のパソコンでもNETWORKDAYS関数が使えるかどうかが判らないので、今回はNETWORKDAYS関数を使用しない数式としました。

回答No.1

-9L9-

売上累計欄を作れば単純な四則演算でできる計算でしょう。何がわからないのかがわからない。
「実数字」というのもわからないし（通常は実績について使う言葉であるがそれだと意味が通じない）、「セルを組む時に０を含めない」というのも意味不明。
もう少し整理してみたらどうですか。