エクセル の学校
8.一覧 9.HOME
1.Top 2.Last

[[20260205171212]]

[ 初めての方へ | 一覧(最新更新順) |

|
| 全文検索 | 過去ログ | エクセルの学校HOME ]

　

『SUMに関する関数を教えてください』（Nancy）

はじめまして。 Excel初心者です。 次のようなSUMを出す関数があれば教えていただきたいです。

１）A1に「100(単位は円)」を入力 ２）B1にA1の値100のうち1〜30には5％を掛け、次の31〜60には4％を掛け、61〜90に3％を掛けて、最後91〜100には2％を掛けたものを合計した値が入力される関数。

すなわち、B1には30×5％＋30×4％＋30×3％＋10×2％＝「3.8」という値が入力されるような関数があれば教えていただきたいです。

A1に入力した値を等差で分けて、最初の組に任意のパーセンテージを掛けて、次の組には先のパーセンテージより1％下のパーセンテージを掛けて…を繰り返して合算したものが欲しいです。

宜しくお願いいたします。

＜ 使用 Excel：Microsoft365、使用 OS：Windows11 ＞

=SUM(30*5%,30*4%,30*3%,10*2%) （ワンセグ） 2026/02/05(木) 17:41:07 
 (私には具体的には組めませんけど)
 考え方としては


 MIN(MAX(A1-幅*回,),幅)*(率-回)%
 というのを 回=0から始めて INT(A1/幅) に達するまで繰り返し足す、って感じでしょうか...


 SUM(
      MIN(MAX(A1-幅*0,),幅)*(率-0)%
     ,MIN(MAX(A1-幅*1,),幅)*(率-1)%
     ,MIN(MAX(A1-幅*2,),幅)*(率-2)%
     ,MIN(MAX(A1-幅*3,),幅)*(率-3)%
                  ・
                  ・
 )

（白茶） 2026/02/05(木) 18:06:56

●ワンセグ様 早速ありがとうございます。 こちらの説明が足りていなかったため申し訳ございません。 A1は変数で考えており、B1も連動するようにしたいです。

具体的には、売上(A1）の売上手数料(B1)の計算になります。 売上(A1)の50万円ずつに7〜3％が掛かるようになっている仕組みです。

売上(A1)が120万円だった場合、A1に1200000を入力します。 すると手数料(B1)には「75000」が計算されるようにしたいのです。 ※(1〜500000)×7％＋(500001〜1000000)×6％＋(1000001〜1200000)×5％

もし売上(A1)が150万円あれば、手数料(B1)は9万円になるのか、 という具合にA1だけを触ってB1が変動する関数があれば、と思いご質問させていただきました。

●白茶様 早速ありがとうございます。 そのようなイメージです！ （Nancy） 2026/02/05(木) 18:29:52 

 最後の3つの引数で調節


 =LAMBDA(a,b,c,
 LET(
  div,ROUNDUP(a/b,0),
  vv,SCAN(0,SEQUENCE(div),LAMBDA(ac,v,MIN(b,a-b*(v-1)))),
  ratio,SCAN(c+1,SEQUENCE(div),LAMBDA(ac,v,ac-1)),
  SUM(vv*ratio/100)))(100,30,5)　　＝＞3.8


 =LAMBDA(a,b,c,
 LET(
  div,ROUNDUP(a/b,0),
  vv,SCAN(0,SEQUENCE(div),LAMBDA(ac,v,MIN(b,a-b*(v-1)))),
  ratio,SCAN(c+1,SEQUENCE(div),LAMBDA(ac,v,ac-1)),
  SUM(vv*ratio/100)))(1200000,500000,7)　　＝＞75000
（まる2021） 2026/02/05(木) 18:58:46

 補足的蛇足的な投稿で恐縮です。


 ロジックは、まる2021さんはじめ既に回答いただいているものと概ね同じですが、
 MAP関数を使った例を示してみます。


 ■
 以下の式は、LAMBDA関数を使って、自前の関数を定義したものです。
 これを「名前」として登録しておきます。
 ・ブックベースの名前にしてください。
 ・名前は任意です。（commissionでもcostでも手数料でもなんでもよいです）


 =LAMBDA(amount,unit,initialrate,
    LET(
        cnt,       CEILING(amount/unit, 1),         
        ary,       SEQUENCE(cnt),         
        amount2,   MAP(ary, LAMBDA(x, MIN(amount - unit*(x-1), unit))),
        rate,      MAP(ary, LAMBDA(x, initialrate-(x-1))),
        SUMPRODUCT(amount2, rate * 0.01)
    ) 
 ) 


 （なお、名前ボックスに一字一句手入力するのは非現実的です（入力域が狭すぎです）。
 　テキストエディタなりでコピーして、それを"参照範囲"のボックスに貼り付けるのがよいでしょう。
 　また、投稿の便宜上、式の始めにスペースを入れていますが、
 　名前登録にあたっては、スペースを入れずに、=で始めて下さい。）


 ■
 利用するときは、以下のようにします。
          A列        B       C     D列
 1        100        30      5     =cost(A1,B1,C1)       #     3.8が返ります
 2  1,200,000    500,000     7     =cost(A2,B2,C2)       #  75,000が返ります


 ■
 式の理解のためには、こんな式を入れてみると役立つかもしれません。
 (こちらを先に説明するべきだったかも）


       A列   B列   C列   D列   E列   F列
 1    100    30      5                 
 2                                   
 3      4     1     30    5           
 4            2     30    4           
 5            3     30    3           
 6            4     10    2          3.8
 7                                   
 A3:  =CEILING(A1/B1,1)
 B3:  =SEQUENCE(A3)
 C3:  =MAP(B3#,LAMBDA(x,MIN(B1,A1-B1*(x-1))))
 D3:  =MAP(B3#,LAMBDA(x,C1-(x-1)))
 F6:  =SUMPRODUCT(C3#,D3#/100)


 C3やD3やF6にある B3#　の#は"スピル範囲演算子"と呼ばれるもので、
 そのセル範囲にある数式で生成されたスピル範囲を指します。
 B3#は、この場合、B3:B6と同等です。（スピル範囲が変化すると動的に範囲が追随します。）


 MAP関数の働きは、ヘルプを読んで下さい。
 （既に回答がありましたSCAN関数とか、REDUCE関数やMAP関数は関数型言語でよく出てくる典型的なものです。）


 これをLET関数を使ってひとつにまとめたものが、最初の式です。
（xyz） 2026/02/06(金) 17:36:43

 一番最初の
 ＞B1には30×5％＋30×4％＋30×3％＋10×2％＝「3.8」という値が入力されるような関数　　であれば、
 =SUMPRODUCT(A1*{0.3;0.3;0.3;0.1}*{0.05;0.04;0.03;0.02})　とかも。
 まぁ小数点以下の端数処理はまったく考慮してないですけど。
　
（純丸） 2026/02/06(金) 20:18:45

 純丸さん、横からすみませんが、
 その案はA1が110であったり、80であったりしても有効なのでしょうか。
（xyz） 2026/02/06(金) 21:33:48

 xyzさん
 A1に2つの配列を掛け合わせてそれを合算する数式です。
 A1が変化しても有効です。


 実際には別セルにテーブル(例:E1〜F4)を用意して、


     A列   B列   C列   D列   E列   F列
 1   100   3.8               0.3    5%　 
 2                           0.3    4%   
 3                           0.3    3%
 4                           0.1    2%


 B1セル =SUMPRODUCT(A1*($E$1:$E$4)*($F$1:$F$4))  のようにした方がメンテナンスにしても実用的だと思います。
 =SUMPRODUCT(A1*$E$1:$E$4*$F$1:$F$4)のように中の括弧は無くてもいいのですが、
 括弧があった方が見やすいので私は付けるようにしています。
　
（純丸） 2026/02/06(金) 22:38:11


 純丸さん、回答拝見しました。
 しつこくて恐縮です。


 (100,30,5) と(80,30,5)が同じ
 =SUMPRODUCT(A1*{0.3;0.3;0.3;0.1}*{0.05;0.04;0.03;0.02})
 で計算できるというお話ですが、私には理解できていません。


 80のときは、
 30 * 0.05 + 30 * 0.04 + 20 * 0.03 = 3.3 になるはずですが、
 どうやったら貴兄の計算式で 3.3 が得られますか?

（xyz） 2026/02/07(土) 09:37:54 

 xyzさん、遅くなってすみません。
 手数料の計算ロジックについての解釈が、私とxyzさんとで違っているようです。


 私はA1の金額の3割部分に対し5％、次の3割部分に対し4％、次の3割部分に対し3％、
 残りの1割部分に対し2％をそれぞれ乗じて合計する 、と解釈したのですが、
 xyzさんは、30*5%、30*4%と計算していきながら元の数字を減算していくと考えられたのではないでしょうか？
　
 私の考え方が違っていたかもしれませんね。
 質問者さん、申し訳ありませんでした。私の回答はスルーして下さい。
　
（純丸） 2026/02/08(日) 17:55:45

 >  xyzさんは、30*5%、30*4%と計算していきながら元の数字を減算していくと考えられたのではないでしょうか？
 いえ私だけでなく、白茶さん、まる2021さん、それに質問者さんの2026/02/05(木) 18:29:52のコメントも
 皆さん同じ考え方だと思います。
 詳しく書く気になりませんが、他の回答者さんの回答も読まれたうえでコメントして頂きたいです。
 それと、既にたくさんの回答が出てから、おひとりだけ別の考え方を提示されるのですから、
 皆さんとの考え方の差についてご自身でコメントされてから、改善策を提示して欲しかったです。
 回答ありがとうございました。

（xyz） 2026/02/08(日) 20:23:57 

 =LET(
      _元の値,1200000,
      _等差,500000,
      _fee,7%,


      _n,INT(_元の値/_等差),
      _等差配列,VSTACK(SEQUENCE(_n,1,_等差,0),MOD(_元の値,_等差)),
      _fee配列,SEQUENCE(_n+1,1,_fee,-1%),
     SUM(_等差配列*_fee配列)
  ) 
 Lambdaヘルパー関数使わなくてもいいよねって思ったので供養しておきます
（ちくわ） 2026/02/09(月) 11:21:22

 率を1%づつ減じていって、0とかマイナスの値のときは、どう処理するのでしょう？
（´・ω・`） 2026/02/09(月) 11:47:30

 IF分岐を入れるしかないと思いますが、 
 計算の全体として 1%以下は1%にするなどがないので正しい挙動だと思っています。
 等差が不正などのエラーを出す方が良いですかね？
 エラーは吐けますけど エラーにする条件がよくわからないので入れてないですね


 エラーを入れるなら
 IF(_fee*100 < _n +1 , SUM(_等差配列*_fee配列),  "エラーメッセージ")
ですね
（ちくわ） 2026/02/09(月) 13:18:49
＞B1には30×5％＋30×4％＋30×3％＋10×2％＝「3.8」 ＞手数料(B1)には「75000」が計算されるようにしたいのです。 　※(1〜500000)×7％＋(500001〜1000000)×6％＋(1000001〜1200000)×5％ 金額によって率が変わるということですか。 ＞売上(A1)の50万円ずつに7〜3％が掛かるようになっている仕組みです。 その仕組みを説明したらどうですか。 ＞そのようなイメージです！ イメージではなくきちんとしたレイアウトを示すべきです。

（?） 2026/02/09(月) 15:57:16 

 名前定義の利用に起因する不具合に関する発言は、閲覧者の障害になりそうですのでカットしました。
 時期を見て【談話】としてでも再投稿するつもりです。
 要約すると「名前定義の利用と日本語変数・引数等はケミストリーが悪そうで、
 思わぬ障害の発生要因となる懸念があります」です。


 なお、私も率がマイナスになりうることは気になっていましたが、
 別用件にかまけてしまっていました。
 あとは、REDUCEを使った例もあるといいなとか、デバッグ手法はどんなことが
 一般的なのかを書くことも頭にはありました。


 色々と回答コメントがあるのですから、
 質問者さんから適当なところで反応をいただきたいところです。

（xyz） 2026/02/09(月) 16:41:03

皆様、ご回答いただき誠にありがとうございます。 またこちらの説明が不足しており、混乱も招いてしまい申し訳ございません。 これから、ご回答いただいた関数を一つ一つ拝見および試させていただき、またフィードバックしたいと思います。

普段関数をほとんど使用しないため、慣れずにお時間いただいております。 （Nancy） 2026/02/09(月) 18:30:27 

 その後の状況はいかがですか？　コメントをしていただきたいですね。
（xyz） 2026/02/18(水) 18:22:22


[ 一覧(最新更新順) |

]
キーボードヒント：[Home]または[Fn+Home]キーで一番上へ戻ります

YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.