=SUM(ROUND(A1,0),ROUND(A3,0),ROUND(A10,0))をもっと簡単に

質問

エクセル200です。
A1、A3、A10の各セルにある数値を四捨五入してから合計するのに
=SUM(ROUND(A1,0),ROUND(A3,0),ROUND(A10,0))
でできるのはもちろん存じておりますが、もっと簡単に書く方法はないでしょうか？

A2、A4からA9までのセルが空白であれば
=SUMPRODUCT(ROUND(A1:A10,0)*1)
A2、A4からA9までのセルが文字列であれば
｛=SUM(IF(ISNUMBER(A1:A10),ROUND(A1:A10,0),""))｝（配列数式）
でできるのですが、数値であるために使えません。

上記のA1、A3、A10はあくまで一例で、実際は同一列ではありますがもっとセル数が多いのです。そのため簡単に書ける方法を探しています。
よい方法があればご教示くださいませ。

回答No.5

myRange · ベストアンサー

雨の鹿児島から、おはようさんです。

これが簡単かどうかは別として、、、

=SUMPRODUCT((ROW(A1:A10)={1,3,10})*ROUND(A1:A10,0))

以上です。

お礼

おはようございます。
こちらも寒い朝です。

=SUM(ROUND(A1,0),ROUND(A3,0),ROUND(A10,0))
=SUMPRODUCT((ROW(A1:A10)={1,3,10})*ROUND(A1:A10,0))
と並べると、ご教示いただいた式の方が長く見えますが、対象のセルが4つ以上になるとすごい威力を発揮してくれますね！
セル指定が行番号だけで済み、セルがいくら増えても他の部分に影響を与えないなんてとてもすばらしい方法です。
ありがとうございました。
とても感謝いたしております。

補足

すばらしい数式をご教示いただきながら、今後のためにもうひとつおしえてください。
=SUMPRODUCT((ROW(A1:A10)={1,3,10})*ROUND(A1:A10,0))
とすると、その後範囲全体をドラック移動した場合や範囲内に行を挿入された場合、当然ながら{1,3,10}にだけは反映してくれません。
{ROW(A1),ROW(A3),ROW(A10)}としてみたらエラーになりました。
対応方法はありますか？
勝手を申しましてすみません。
.

回答No.12

ka_na_de

＃２です。

ユーザー定義関数による方法は、
SUM関数と同様の使い勝手を実現しています。

つまり、
=RSum(A1,A3,A10)
=RSum(A1:A10)
=RSum(A1:A10,A15:A25,B1:C20)
などのセル範囲の指定が可能です。

また、行の挿入、削除、移動にも対応しています。

また、セル範囲をクリック・ドラッグで指定可能です。

以上です。

お礼

なんどもありがとうございます。
やはりVBAの威力はすごいですね。
今回はワークシート関数でという制限があったもので使えないのが残念です。
でもありがとうございました。

回答No.11

myRange

CoalTarさん、早速の回答ありがとうございます。

SUMの引数を複数ですか、それは頭の隅にもありせんでした。
てっきり、引数１個でやるものと。。。。

SUMPRODUCTの件はそれ故のことです。
引数が複数なら仰るとおり意味がありませんね。

何れにしろ今回の件は、目から鱗。
貴重なお時間を割いていただき感謝します。
ありがとうございました。

merlionXXさん、質問者の質疑応答に当方が質問して申し訳ありませぬ。
ま、１回は回答したということでお許しあれ。。。
以上です。

お礼

お許しあれ。などとはとんでもない。
いつもお世話になり、本当にありがとうございます。

回答No.10

某HN クロメート(Chromate)（@CoalTar）

myRangeさん、お褒めいただきありがとうございます。
=SUM(ROUND(CHOOSE({1;2;3;4},A1,A5,A8,A10),0),
ROUND(CHOOSE({1;2;3},A11,A12,A15),0))
と考えれば29*30個は可能だと思います。
Chooseの29個とSUMの30個

SUMPRODUCTは使っても意味がないような？

参考まで

お礼

ありがとうございます。
ほんと勉強になります。

回答No.9

myRange

昨日とはうってかわった晴天の鹿児島から、こんにちは。

当方の方法で１０分程度検討してみました。
そのままのセル数での移動はINDEXを使用して出来ましたが、
（式は短いがちょと姑息な手段なので見せられませぬ）
１，３，１０の行数を指定するので挿入などがあるとダメ。

よって、CoalTarさんのが素晴らしい方法だと思われます。
但し、必ず、配列定数を使うこと（理由は式をみれば分かると思います）
そして式は、SUMの配列数式ではなく、SUMPRODUCTがいいでしょう。

まさか、該当セルを削除することもあるのでそれにも対応するには？
とか、該当セルが３０以上あるんですが、とかはないですよね？

実は該当セルが３０以上あったときの式を考えてみたのですが、
ご存知のとおり、エクセル一般機能には疎いもので１０分程度でギブアップ。

そこで、CoalTarさんにお願いあり。
これを見ている誰もが３０以上の式を知りたいでしょうから
それを是非アップいただければと。

以上です。

お礼

お手間かけました。
鹿児島にはまだ一度も行ったことがありませんがきっととても良いところなのでしょうね。
ありがとうございます。

回答No.8

某HN クロメート(Chromate)（@CoalTar）

＃７です
>対応方法はありますか？
絶対参照にするか、配列定数(3つなら{1;2;3})にすればよいだけです。
配列定数にするには ROW(A1:A3) を選択して[F9]キーです

お礼

配列定数ですか。

> 配列定数にするには ROW(A1:A3) を選択して[F9]キーです

勉強になりました。
ありがとうございます。

回答No.7

某HN クロメート(Chromate)（@CoalTar）

選択するセルが29個以内であれば(応用すればもっとできるけど)
3個の場合
=SUM(ROUND(CHOOSE(ROW(A1:A3),A1,A3,A10),0))
4個の場合
=SUM(ROUND(CHOOSE(ROW(A1:A4),A1,A5,A8,A10),0))
いずれも[Ctrl]+[Shft] ＋[Enter] で確定
セル範囲が使えないのでイマイチかな

お礼

ありがとうございます。
この方法でもできました。

欲を言えば、範囲全体を縦に方向にドラック移動した場合、当然ながらROW(A1:A3)にも反映してエラーになります。
対応方法はありますか？
勝手を申しましてすみません。

回答No.6

ASIMOV

おそらく、それらのセルを選択するには、それなりの理由があるわけですよね
その「理由」で、選択するような関数を使えば簡単になると思うのですが
例えば
　○○より大きい
　△列に○○という文字が入っている
など、
まったく、気まぐれでと言うことだと、式の中にその行に関するデーターを含めるか、作業列を使い、印を付けるしかないのでは？

お礼

はい、ありがとうございます。

回答No.4

KURUMITO

Ａ列に数値が入力されている中で指定したセルだけの数値にROUND関数を使ってその合計を求めることでしたら、=SUM(ROUND(A1,0),ROUND(A3,0),ROUND(A10,0))のような式で対応することになるでしょう。しかし、これがたくさんの数値となりますと面倒なことになりますね。
やや古典的ですが次のような作業列を使った方法がよいでしょう。
Ｂ列には指定するセルの行番号を入力します。例えば1，3，10とそれぞれ上からのセルに順に入力します。
Ｃ1セルには次の式を入力して下方にオートフィルドラッグします。
=IF(COUNTIF(B:B,ROW(A1))=0,"",ROUND(A1,0))
Ｄ1セルには=SUM(C:C)と入力すれば求める答えが得られませね。

お礼

ありがとうございます。
作業列を用いる方法はとてもわかりやすいのですが今回は使わない方法を探っておりました。

回答No.3

ka_na_de

＃２です。

余談ですがRound関数は、
ワークシート関数とVBA関数のどちらもありますが、
計算結果が異なるようです。

参考ページをごらんください。
http://support.microsoft.com/kb/225330/ja

こちらはOFFICE2003ですがOFFICE2000と同様に
異なる結果となりました。

ご参考までに。

参考URL：

http://support.microsoft.com/kb/225330/ja

お礼

VBAでのROUNDが銀行型になり、ワークシート関数での算術型とことなるということですね。
ありがとうございます。

回答No.2

ka_na_de

既存の組み込み関数を使った方法をお求めだと思いますが、
思いつかないので、ユーザー定義関数を作ってみました。

Function RSum(ParamArray argRng()) As Double
　　Dim buf As Double
　　Dim i As Variant, j As Variant

　　buf = 0
　　For Each i In argRng()
　　　　If IsArray(i) Then
　　　　　　For Each j In i
　　　　　　　　buf = buf + Application.WorksheetFunction.Round(j.Value, 0)
　　　　　　Next
　　　　Else
　　　　　　　　buf = buf + Application.WorksheetFunction.Round(i.Value, 0)
　　　　End If
　　Next
　　RSum = buf

End Function

'========使い方============
' =RSum(A1,A3,A10)

お礼

ありがとうございます。
やってみましたがうまくいきました。
今回はワークシート関数での方法を探っていましたが、勉強になります。