スプレッドシートで按分比率の自動調整

Question

前提

画像の参照イメージを元に説明致します。

スプレッドシートにて施策ごとにメールを送りますが、同じ対象には月1回となるように按分して送付をします。

さらに施策ごとに優先度を付けて送りたいため、按分する比率を設けています。
F列が按分比率になります。

G1〜AH1のところでは、「個人、1-10名、、、」という従業員数ごと×「課金or未課金」にセグメントを分けています。

チェックが付いているのは施策ごとにメールを送付する対象です。
※オレンジのハイライトは按分をしない施策も混ざるという意味で無視します。

按分の計算について

例えば、個人課金セグメントの場合、"I" "J" 列で見ると3件分あります。

この中で一番優先度が高い施策は8行目となり、按分比率は1.02です。
他の2件の按分比率は0.32となります。

ですので、
1.02/(1.02+0.32+0.32)
=0.614457
※四捨五入する形で構わない

この計算によって、
8行目の施策は60%の按分比率（J8）
9、16行目の施策は20%ずつの按分比率（J9、J16）
となります。（合計100%）
※按分比率はチェック欄の右に出る

▼参照イメージ

実現したいこと

• チェックが入った施策の中で自動的に按分比率を出す
• 計算された按分比率はチェックしたセルの右のセルに自動で入る

按分比率が自動で出てほしい列はH、J、L、N、、、AHまでとなります。

GASでもそれ以外でも解決方法は問いません。

発生している問題・課題

できれば下の行に翌月、翌々月分と繰り返していきたいが、その月にどれだけの施策があるかは毎回変わってしまう。。
（複雑になり、悩ましい点です。。）

翌月、翌々月分はシートを変えて対応することも可能です。

試したこと

手作業で按分比率を記載している状態

補足情報（FW/ツールのバージョンなど）

解決方法のアドバイス等頂けましたら、幸いです。
不明点あれば早めにお答えしていきたいと思います。
よろしくお願いいたします。

Comments

[退会済みユーザー]

5%刻み（場合によっては2%刻み）で表示させたいという要件と、どんなチェック入力パターンの場合でも合計をきっちり100%にする、という要件は両立させることができませんがそれでよろしいですか？

[nomarks]

そちらで構いません。
ありがとうございます！

Answer 1

注意）下記の数式は、質問文中の図に記載のチェック入力パターンのみ、対応するものです。図に記載のチェック入力パターン以外については合計が100%にならない場合があります。

質問文中の図のように、5%刻み（場合によっては2%刻み）で表示させたいという要件と、どんなチェック入力パターンの場合でも合計をきっちり100%にする、という要件は両立させることができません。

---

※上段（5行目～16行目）のみ示します。下段（20行目～31行目）は記載した数式を参考に参照範囲を変えてください。

① 書式設定：H列、J列、L列...(以降略）は、メニューの表示形式から、%表示となるようにあらかじめ設定しておきます。

② 次に、H5 セルに、以下の数式を入力します。

=if(round(sumif(G$5:G$16,true,$F$5:$F$16),2)=3.29,if(G5=true,round($F5/sumif(G$5:G$16,true,$F$5:$F$16)/2,2)*2,""),if(G5=true,round($F5/sumif(G$5:G$16,true,$F$5:$F$16)/5,2)*5,""))

③ H5セルをコピーして、H6セルからH16セルまで貼り付けます。

④ 以降同様に、H5セルをコピーして、J5:J16セル、L5:L16セル...など、必要な列に貼り付けます。

Comments

[nomarks]

ご回答ありがとうございます！！
問題なく表示されました！

もっとコピペ作業や列を自動で出来る方法はありますでしょうか？