お世話になっております。
取引先に送付する納品書について、購入金額に応じて商品発送の際に運賃が発生致します。
株式会社A社 購入金額10,000円以下で運賃500円
株式会社B社 購入金額15,000円以下で運賃500円
株式会社C社 購入金額10,000円以下で運賃500円
以下、D社…と続きます。
運賃は一律500円となります。
取引先は数十社あり、その内の上記5~7社程度が常に小額での取引の為、
購入金額が条件以下の場合、納品書の運賃の項目欄に500円、そうでない場合は0円、
その他の大口の取引先の運賃は常に0円という様式で自動的に運賃欄に値を取得したいと思います。
テーブルを作りVLOOKUP関数を使えば…と通常はなりますが、
システムで納品書を出力しており、出来るだけ出力後の作業量を軽減したいと思い
関数を予め組むことで、出力後の作業を行わず解決できればと考えております。
納品書の仕様としては…
B8…取引先企業名(株式会社A社等固定の値)
I34…小計
I36…運賃
B8が該当の取引先企業名の場合で、かつ小計が●円以下の場合、運賃は500円、異なる場合は0円、
また、それ以外の取引先は常に運賃0円という風にI36に関数を入れたいと思います。
IF関数で試行錯誤してみましたが、思うような結果が得られない為お力添え頂ければ幸いです。
尚、エクセルのver.は2007でネストの数は64まで問題ないとの事で、
10社に満たないものの、数式としては長たらしくなり少々非現実かもしれませんが、
この方法で解決できればと思います。また、IF関数でなくても結構です。
何卒よろしくお願い致します。
No.1
- 回答日時:
こんばんは!
>テーブルを作りVLOOKUP関数を使えば…と通常はなりますが・・・
確かにその通りだと思います。
別Sheetに対応表を作成しておけばごく簡単な数式だけで対応できるのですが、
どうしても対応表を作らず数式だけで処理したい!というのであれば、一例です。
セル配置は無視してやり方だけの説明となります。
例として、
「A社・D社」が10000以下は500・「B社・D社・E社」が15000以下は500
「F社・G社・H社」が16000以下は500、それ以外は0というようにしてみました。
↓の画像でD8セルに
=IF(COUNTBLANK(B8:C8),"",IF(OR(ISNUMBER(FIND({"A社","D社"},B8))),IF(C8<=10000,500,0),IF(OR(ISNUMBER(FIND({"B社","C社","E社"},B8))),IF(C8<=15000,500,0),IF(OR(ISNUMBER(FIND({"F社","G社","H社"},B8))),IF(C8<=16000,500,0),0))))
これは配列数式になりますので、Ctrl+Shift+Enterで確定!
この画面からコピー&ペーストする場合は
上記数式をドラッグ&コピー → D8セルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま)
Ctrl+Shiftキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。
これでB8・C8セルデータを色々入れてみてください。
※ 個人的見解ですが、
上記数式を見てもらっても判るように
IF関数のネストが多すぎると仮に数式のどこかに不具合があってもなかなか見つけにくいと思います。
また金額変更があったりした場合、メンテナンスの面でかなり面倒になります。
そういう点を考慮すると
別Sheetでも良いので対応表を作る方法をおススメします。m(_ _)m
ご回答ありがとうございます。
ご指摘の通りネストが多すぎるとメンテナンスが大変になる事もあり、
関数が簡易的なNo.2様の方を発展させて使用させて頂きたいと思います。
画像添付まで頂き分かりやすいご説明感謝致します。
No.2
- 回答日時:
I36に、
=IFERROR(FIND(B8,"A社・C社・E社")*(I34<10000)*500,0)
とかでどうでしょう?
FIND関数の中に指定取引先を羅列していく形です。
No.3
- 回答日時:
No.2さんの式だと
・B8セルが未入力の場合に500を返す
・B8セルが2番目以降の会社名の場合、運賃がおかしくなる
(例えばC社の場合2000、E社の場合は3500と計算される)
ので、これを回避するためには
=IFERROR((B8<>"")*ISNUMBER(FIND(B8,"A社・C社・E社"))*(I34<10000)*500,0)
とすればいいかと思います。
複数の条件を組み込む場合は、同じような式を組み立てて足してやればいいです。
A社・B社は10000円未満の場合に500円の運賃が発生する
C社・D社は15000円未満の場合に500円の運賃が発生する
それ以外の会社は運賃が発生しない、という場合
=IFERROR((B8<>"")*ISNUMBER(FIND(B8,"A社・B社"))*(I34<10000)*500,0)+IFERROR((B8<>"")*ISNUMBER(FIND(B8,"C社・D社"))*(I34<15000)*500,0)
このような式になります。
ご回答ありがとうございます。
お礼が遅くなり申し訳ございません。
皆様どのご回答も適切な物でしたのでベストアンサーに
選ばせて頂きたいのですが、最終的に一番数式が簡易的だった
他者様をベストアンサーに選ばせて頂きました。
今後ともよろしくお願い致します。
No.4ベストアンサー
- 回答日時:
No.1です。
区分けとしてはA・B社が10000以下、C・D社が15000以下の場合に500の送料が発生し
他の場合は「0」で良い!というコトですよね?
=IF(OR(B8="",I34=""),"",500*OR(ISNUMBER(FIND(B8,"A社・B社"))*(I34<=10000),ISNUMBER(FIND(B8,"C社・D社"))*(I34<=15000)))
という数式にしてみてください。m(_ _)m
ご回答ありがとうございます。
お礼が遅くなり申し訳ございません。
皆様どのご回答も適切な物でしたのでベストアンサーに
迷いましたが、最終的に数式が一番簡易的だった
NO.1様をベストアンサーに選ばせて頂きます。
今後ともよろしくお願い致します。
No.5
- 回答日時:
補足を頂いておりましたのに気が付きませんでした。
既に、他の回答者の方から、素晴らしい回答が寄せられておりますので、
参考にもならないとは思いますが、
当初の回答を改良するならば、
=IF(B8="","",IFERROR((FIND(B8,"A社・B社")>0)*(I34<10000)*500,0)+IFERROR((FIND(B8,"C社・D社")>0)*(I34<15000)*500,0))
となりますでしょうか。
ご回答ありがとうございます。
お礼が遅くなり申し訳ございません。
皆様どのご回答も適切な物でしたのでベストアンサーに
選ばせて頂きたいのですが、最終的に一番数式が簡易的だった
他者様をベストアンサーに選ばせて頂きました。
今後ともよろしくお願い致します。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 投資・株式の税金 一般口座で同一銘柄の総平均法のことで 1 2023/02/27 22:08
- 債券・証券 仕訳のこの問題が分かりません。教えていただけるとありがたいです 1 2022/06/15 20:34
- 飛行機・空港 教えてください 4月中旬、スプリングジャパンにて成田発、新千歳行きの飛行機を取りました その際、予約 3 2023/05/08 18:55
- 会計ソフト・業務用ソフト 固定資産管理で貸し出したマンションの登録操作について【弥生の青色申告】 2 2023/03/07 11:05
- 株式市場・株価 信用取引の「金利と管理費」について 1 2022/06/10 19:09
- 家賃・住宅ローン 単身女性です。 手取り20万で家賃9万、どう思われますか? 以下、現在の支出額です。 月20万程度。 4 2022/10/30 11:36
- 分譲マンション 皆さんの管理組合では)共用部分の修繕工事業者の選定は→どう選定されておられますか? ①管理会社へ丸投 3 2022/10/06 22:07
- 消費税 消費税の納税額の計算 1 2023/02/19 18:12
- Excel(エクセル) エクセルで割り振りをする方法 7 2022/08/02 14:02
- 固定資産税・不動産取得税 私は30代の独身です 決して自慢ではないです 【年収】 農業収入60万円 不動産収入3476万円 雑 9 2022/05/30 23:25
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
1974年(昭和49年)10...
-
高速バス(JRバスと関東鉄道...
-
駅での見送りについてです。 A...
-
バスの運賃の支払い方について...
-
田舎は全てにおいて、凄く不便...
-
往復切符って
-
路線バスの料金支払いについて
-
スカイマーク 障害者割引は子...
-
入間市駅からタクシーでアウト...
-
西武国分寺線からの運賃
-
大阪駅から京都駅まで何分?
-
運賃と料金について
-
JRの料金は消費税込み?
-
バスの整理券って何?。
-
佐川急便について教えてください
-
切符乗り換え方法 切符での乗り...
-
新幹線の乗車券で旅行会社にチ...
-
急いでます!!!特急あずさの...
-
駅の切符は出発駅買ったあと、...
-
みどりの窓口
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
バスの運賃の支払い方について...
-
JRの料金は消費税込み?
-
Excel VBAでインターネットを利...
-
JRの料金と地下鉄料金の違い
-
タクシーに乗るといつも直前で...
-
駅での見送りについてです。 A...
-
往復切符って
-
高速バス(JRバスと関東鉄道...
-
駅間の距離を表示するサイト
-
JRに乗るには、精神の障害者手...
-
京急、東京メトロ東西線、武蔵...
-
佐川急便について教えてください
-
レベリュートンってなんですか?
-
北千住-綾瀬の料金
-
ジェットスター乗った事ないん...
-
夏休みに新幹線で東京⇒博多へ帰...
-
スカイマーク 障害者割引は子...
-
運賃と料金について
-
再生砕石の単価
-
1974年(昭和49年)10...
おすすめ情報
ご回答ありがとうございます。
購入金額の送料条件は今のところ10,000万円以下と15,000円以下の2種類があります。
例えばA社・B社が10,000円以下で500円、C社・D社が15,000円以下で500円の場合、
ご回答頂きました数式に15,000円以下のC社・D社の場合の数式も組み込みたいのですが、
どのようにすればよろしいでしょうか?