初めての質問です。失礼な点があればお詫びいたしますので、ご教示下さい。
私の会社は、取引先に対してリベートを支払っています。支払いサイクルは顧客によってまちまちです。
例えば10月10日から11月10日分販売に対してリベートを10万円支払い
このデータはエクセルで手元にあります。
例
A列 B列 C列 D列
顧客名 対象期間(始) 対象期間(終) 金額
なにわ 10/15/2010 11/10/2010 100,000
この内容を弊社の月次期間毎に割振る必要があります。
月次1 10/10-11/7 月次2 11/8/-12/5...
先程の例を月次単位で分けると
月次1 24日分の 88,889円 月次2 3日分の 11,111円
と比率で計算すればなるのですが、これをエクセルで自動的に
計算したいと考えていますが、どうにも解決できていません。
何か良い方法があれば是非是非、教えていただけないでしょうか・
何方か、よろしくお願いいたします。
A 回答 (4件)
- 最新から表示
- 回答順に表示
No.1
- 回答日時:
》 月次1 10/10-11/7 月次2 11/8/-12/5...
「月次期間」はどういうルールで決まっているのですか?
上の例で言えば、
「月次1」は28日間で日曜日から日曜日まで
「月次2」は27日間で月曜日から日曜日まで
となっており、テンデンバラバラのように見えますが・・・
「アレは単に例を挙げただけで、実はコーなっている」などと、情報の出し惜しみは止めてくださいネ。
それから、提示されるデータ例は1行だけでなく、少なくとも3~4行分くらいは示してください。そうすることによって、回答者は数式を考えるためのルールを推測できますから。
この回答への補足
ご指摘、ありがとうございます。次回から質問する際は気をつけます。
実は、アメリカの会社で働いているため、時差の関係上お返事が遅くなってしまいました。
月次期間も間違えていました。うちの会社は4週28日が1月次期間となっています。
月次1は10月11日から11月7日
月次2は11月8はから12月5日
というふうに28日毎に区切られて、年13回の月次があります。
いくつか回答を頂けましたので、先ずそちらにチャレンジして更に疑問があれば
できるだけ詳細に質問いたします。
ありがとうございます。
No.2
- 回答日時:
例えば次のようにしてはどうでしょう。
2行目にお示しの項目名が入力されており3行目から下方にデータがあるとします。
作業列としていくつかを用意します。
E1セルには月次1、G1セルには月次2、I1セルには月次3のように入力します。
E2セルには10/10と入力し、F2セルには11/7、G2セルには11/8、H2セルには12/5と入力します。
E3セルには次の式を入力して、例えば右横方向にH2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(A3="","",IF(MOD(COLUMN(A1),2)=0,"",IF(AND($B3<=E$2,$C3>=E$2),MIN($C3,F$2)-E$2+1,IF(AND($B3>=E$2,$B3<=F$2),MIN($C3,F$2)-$B3+1,""))))
これでE列、G列には指定された範囲の期間における日数が表示されます。
そこでお金の配分ですが例えばM1セルに月次1、N1セルに月次2とE1セルやG1セルに入力したと同じ文字列を入力します。
M3セルには次の式を入力し横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(OR(M$1="",$A3=""),"",ROUND($D3*INDEX($A:$L,ROW(),MATCH(M$1,$1:$1,0))/SUM($E3:$L3),0))
これで、月次の期間で割り振られた金額が表示されます。
一度こちらの提案と同じにして試験してみてください。
ご回答、ありがとうございます。
試してみたら、きれいな表ができました。感激です。
自分でも、関数の中身を調べてみました。とても、勉強になります。
特に、Mon関すとCoulmn関数を使って、計算式の結果を空白にさせる
箇所は、びっくりしました。
今後、是非使ってみたいです。
また、金額だけが別に纏めて表示できるので、印刷するもの便利です。
結果を貼ったのですが、うまく貼れていません。
(エクセルをきれいに貼る方法があるのでしょうか?)
お忙しいところ、ありがとうございました!
顧客名始め終わり金額月次1月次2月次1月次2
11-Oct7-Nov8-Nov5-Dec
なにわ15-Oct10-Nov 100,000 24388,88911,111
江戸1-Nov2-Dec 50,000 72510,93839,063
No.3
- 回答日時:
対象期間の初日と最終日が明らかなので、対象期間の日数は計算で
きますね。「最終日-(初日-1)」です。そうすると1日あたりの金額
は割り算で出ますね。
次に対象期間のうち月次1に含まれる日数を考えましょう。数直線を
引いてみると判りますが、これは「対象期間初日と月次初日のどち
らか遅い方」から「対象期間最終日と月次最終日のどちらか早い
方」までの日数になります。さらに、対象期間が月次に全く含まれ
ない場合に日数がマイナスになってしまうのを防止するため、最小
値をゼロに揃えます。で、こういう時に活躍するのがmin関数とmax
関数なんですね。たとえば「対象期間初日と月次初日のどちらか遅
い方」はmax(対象期間初日,月次初日)となるわけ。全体ではちょっ
と長いですが、「max(0,min(対象期間最終日,月次最終日)-(max(対
象期間初日,月次初日)-1))」というような感じになりますね。
あとはかけ算でおしまい。
シンプルながら、的を得たご回答ありがとうございます。
MaxMin、両関数とも、初めて知りました。
日付はとても扱いにくく、いつも頭を悩ませていたのですが、この
考え方を教えていただき、多くの課題も解決できそうです。
また、日数がマイナスになるのも、簡単に解決されていて、びっくりです。
なんでも、発想が大事なんですね!
本当に役立ちました、ありがとうございました。
No.4
- 回答日時:
こんにちは!
すでに回答は出ていますが・・・
一例です。
↓の画像で説明します。
Sheet1のデータをSheet2に表示するようにしています。
Sheet1に作業用の列(月次毎の日数)を表示できればあとは簡単だと思います。
Sheet2のE~H列に月次の始と終を入力するようにしています。
かなり長い数式になってしまいますが・・・
Sheet1のE2セルに
=IF(AND(B2<Sheet2!$E$3,C2>Sheet2!$F$3),Sheet2!$F$3-Sheet2!$E$3+1,IF(AND(B2>=Sheet2!$E$3,C2<=Sheet2!$F$3),C2-B2+1,IF(AND(B2<Sheet2!$E$3,C2>=Sheet2!$E$3,C2<=Sheet2!$F$3),C2-Sheet2!$E$3+1,IF(AND(B2>=Sheet2!$E$3,B2<=Sheet2!$F$3,C2>Sheet2!$F$3),Sheet2!$F$3-B2+1,""))))
F2セルに
=IF(AND(B2<Sheet2!$G$3,C2>Sheet2!$H$3),Sheet2!$H$3-Sheet2!$G$3+1,IF(AND(B2>=Sheet2!$G$3,C2<=Sheet2!$H$3),C2-B2+1,IF(AND(B2<Sheet2!$G$3,C2>=Sheet2!$G$3,C2<=Sheet2!$H$3),C2-Sheet2!$G$3+1,IF(AND(B2>=Sheet2!$G$3,B2<=Sheet2!$H$3,C2>Sheet2!$H$3),Sheet2!$H$3-B2+1,""))))
という数式を入れ、E2・F2セルを範囲指定し、F2セルのフィルハンドルで下へコピーします。
最後にSheet2のB2セルに
=IF($A2="","",Sheet1!$D2*Sheet1!E2/SUM(Sheet1!$E2:$F2))
という数式を入れ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。
以上、長々と書きましたが、
参考になれば幸いです。m(__)m
画像まで付けていただき、ありがとうございます。
初心者の私でも、理解できる関数でここまでできるんですね!
自分も、もっと頭を使わなきゃと思いました。
作業シートと結果シートを分ける考え方も、非常に参考になります。
結果だけがきれいに印刷できるので、上司にも喜ばれそうです。
お忙しいところ、ご親切な回答ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 高校 日商簿記3級の勉強中なのですが 精算表が完成せず困っています。 こちらの問題の回答を教えていただきた 2 2023/03/02 09:07
- 財務・会計・経理 賞与引当金の計上について計上が必要かどうかまとめてみました(1月決算) 認識違うぞということがあれば 1 2023/07/24 17:17
- 簿記検定・漢字検定・秘書検定 簿記3級の勘定記入教えてください 2 2022/06/28 10:56
- 簿記検定・漢字検定・秘書検定 簿記3級 仕訳の問題です。 2 2022/09/23 22:03
- 財務・会計・経理 経理処理について質問です。 ①12月に1名入社がありました。 ※それまでは役員と不定期のアルバイトの 1 2023/02/23 11:14
- 簿記検定・漢字検定・秘書検定 満期保有目的債券の償却原価法の計算について(簿記2級) 1 2022/06/18 15:40
- 財務・会計・経理 パソコンの減価償却の質問になります (2022年1月31日決算) 2022年11月30日にパソコンを 2 2023/04/05 19:57
- 財務・会計・経理 もう一度減価償却について質問です。 (pc11月31日に、利用開始して1月末決算) 決算終了後の減価 2 2023/04/12 15:08
- アルバイト・パート 有給休暇の賃金 就業規則の記載 3 2023/02/16 21:29
- 死亡 相続放棄の手続きが必要ですか? 6 2022/04/07 11:41
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
Microsoft 365のディフェンダー...
-
英数字のみ全角から半角に変換
-
MicrosoftOfficeの1ユーザー2...
-
MicrosoftOffice2019なんですが、
-
会社PCのメールが更新されない
-
Excel 日付を比較したら、同じ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft Formsの「個人情報や...
-
outlookのメールが固まってしま...
-
Outlook で宛先が複数の場合の人数
-
Office 2021 Professional Plus...
-
エクセル関数について
-
マイクロソフト 一時使用コード...
-
エクセルの貼り付け「リンクさ...
-
Microsoft365で写真をアルバム...
-
会社のTeamsのことで相談です。...
-
officeビジネス型のワードやエ...
-
Microsoft Officeを2台目のPCに...
-
複数の写真を1枚に印刷
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報