重量に対して送料表に当てはまる料金を自動反映させたい。

Question

前提・実現したいこと

シートで計算した月ごとの合計重量から送料表に当てはまる料金を自動で請求書に反映させたいです。
以下ロジックの④まで出来れば問題ないです。

###現状のロジック
①"数量計算表!"シートに重量を手入力
イメージはこちら

↓
②"送料表データベース!"シートに反映される

I6=SUM('数量計算表'!$B$3:$B$20)
K6=SUM('数量計算表'!$C$3:$C$20)
M6=SUM('数量計算表'!$D$3:$D$20)

例）10月15kg、11月5kg、12月10kgなど

↓
③ベースとなる送料表（"送料表データベース!"）から
同じシート（"送料表データベース!"）に「県✖︎重量=送料」のパターンを一つ一つつくる
（最低2kg以上なので2kgからとしているが1kgからでも支障なし）


↓
④"送料表データベース!"に送料を割り出す
F5==IFERROR(DGET($A$1:$C$847,"送料",$H$5:$I$6),0)

↓
⑤合計のところを請求書に反映させて完成

"送料表データベース!"に一つ一つパターンをつくる（最低2kg以上なので）方法が大変なので
それ以外に良い方法があれば、ご教示いただきたいです。

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

別途、受注書に月ごとの送料を記載する必要があります。
なので、月ごとの送料も算出したいです。
A30=TRANSPOSE('送料データベース'!E4:F17)

Answer 1

（Google Apps Scriptを使わない方法）

① ②までは変更ありません。
③ ④を少し変えます。

データの変更に柔軟に対応できるよう、「送料データベース」シートに、名前付き範囲を下記のように設定します。
セル範囲への名前の付け方は、セル範囲に名前をつけるを参照してください。

（下記指定範囲は、質問に記載された画像を参考にしています。実際の使用セル範囲が異なる場合は変えてください）
（画像は県名・料金等一部データを省略していますが実際には入力されているものとみなしてください）

シート：「送料データベース」

名前	範囲	備考
月	H4:AE4
月別重量	H6:AE6
都道府県	F22:O35	北海道の右列まで１列余分に範囲設定する
料金	F36:N44	１行多いので注意（下記 注意１）
料金区分	D35:D43	この範囲の内容は後述（下記 注意２）

＜注意１＞
「〜100kg」の行と同じ料金を１行下にコピーしておき、その行まで「料金」という名前をつけます。（図の黄色行まで）

＜注意２＞
料金の左の列に、「各基準重量に0.1kgを加えた数字」を入力します。
数式でやりたい場合は
=SUBSTITUTE(SUBSTITUTE(E36,"kg",""),"〜","")+0.1
のようになります。
また、5kgの一つ前の行に「0.1」も入力しておきます。
この範囲（H36:N44）に「料金区分」という名前をつけます。

---

上記まで準備ができたら、送料を計算する列の１０月のセル（質問記載の画像の場合、「送料データベース」シートのF5セル）に、下記の数式を入力します。

=IFNA(INDEX(料金,MATCH(INDEX(月別重量,1,MATCH(E5,月,0)+1),料金区分,1),
MOD(MATCH(INDEX(月別重量,MATCH(E5,月,0)),FLATTEN(都道府県),0),
COLUMNS(都道府県))),0)

１１月以降の列は、１０月のF5セルをコピーして、9月までペーストします。

Comments

[nomarks]

ご丁寧な説明まで有難うございます！！！
スムーズにやりたかったことが出来ました！
感謝

[nomarks]

後からすみません！
検証したところ、注文がない（数量0）月のところにも送料が発生してしまっている状態です。
注文のない月に対しては送料0となるように設定するには、どこを修正したら良いのでしょうか？
よろしくお願い致します。

[退会済みユーザー]

テスト不足でした。すみません。

D35セルを「0」から「0.1」に変えて下さい。
また、F5の数式を
「=IFNA(INDEX(料金,MATCH(INDEX(月別重量,1,MATCH(E5,月,0)+1),料金区分,1),MOD(MATCH(INDEX(月別重量,MATCH(E5,月,0)),FLATTEN(都道府県),0),COLUMNS(都道府県))),0)」
に修正してください
（今の数式を「IFNA(～今の数式～,0)」で囲むということです）

F6以降はF5セルの数式をコピーして貼り付けてください。

図はあとで差し替えます

[nomarks]

いえ、とんでもありません！
こちらの説明足らずでした。
五月雨式になり、申し訳ありません

無事にうまく反映されました。
有難うございます！