• ベストアンサー

エクセルVBAで表の数値の微調整

imogasiの回答

  • imogasi
  • ベストアンサー率27% (4737/17068)
回答No.4

私が仕事で、「合計の四捨五入値」と「各項の四捨五入値の合計」が合わない場合にぶつかっていたときのやり方を、VBAでやってみました。 ただし、お許しいただきたいのは、 (1)データが1の位の四捨五入になっていることを、回答を複雑にせずに、簡単にするため、数値直接で考えた。言い換えると1/10の値で考えた。普通はこのケースが多い。 (2)(横)行の合計調整だけで終わっている。縦列も同じ理屈でできるが、回答がながくなり、うんざりでしょうから省略。 コードを短くするため、エクセル関数をできるだけ利用した。 例データ A11:H11 432 1123 567 738 382 976 1314 5532(横合計) 第12行とI列は空白とする。CurrentRegionのため。 H13にターゲット値 5500 を入れている。 '--- コード(標準モジュール) Sub test01() r = Range("a1").CurrentRegion.Rows.Count '区画の行数 c = Range("a1").CurrentRegion.Columns.Count '区画の列数 MsgBox r & "行" & c & "列" '最下行、最右行は合計とする Cells(15, c) = 0 For i = 1 To c - 1 '各列について x = WorksheetFunction.Round(Cells(r, i) * Cells(13, c) / Cells(r, c), 3) '小数以下3位まで Cells(14, i) = x x = x - Int(x) '少数以下端数 Cells(16, i) = x Cells(15, i) = WorksheetFunction.Round(Cells(r, i) * Cells(13, c) / Cells(r, c), 0) '整数値 Cells(18, i) = Cells(15, i) Cells(15, c) = Cells(15, c) + Cells(15, i) '集計列に足しこみ Cells(16, i) = Abs(Cells(15, i) - Cells(14, i)) '差の絶対値 Next i '--- For i = 1 To c - 1 Cells(17, i) = WorksheetFunction.Rank(Cells(16, i), Range("A16:G16"), 0) 'ランク設定 Next i '----- d = Abs(Cells(13, c) - Cells(15, c)) '合計差額整数 If Cells(13, c) - Cells(15, c) > 0 Then s = -1 Else s = 1 '大小サイン For i = 1 To d '超過不足数だけ1を足し引きし調整繰り返し y = Range("A17:G17").Find(what:=i).Column 'ランク上位から順に探す Cells(18, y) = Cells(15, y) - s * 1 '調整 Next i End Sub 結果(元データも含む) 途中経過がわかるように各行にデータを出している。 A11:H18 432 1123 567 738 382 976 1314 5532 5500 429.501 1116.504 563.72 733.731 379.79 970.354 1306.399 430 1117 564 734 380 970 1306 5501 0.499 0.496 0.28 0.269 0.21 0.354 0.399 1 2 5 6 7 4 3 429 1117 564 734 380 970 1306 5500 多数例でチェック出来なかったので、バグがあるかもしれない。 H13を変えてやってみてください。 本番ではセルの変わりに配列に中間結果の値を入れて、最終結果の 第18行を出力し使用にすると良い。 またコマンドボタン等で起動するようにするとよいと思う。 既に長大なご回答が出たあとで、そちらの理解が精一杯で、こちらは考えて見る余力もないかもしれないが、一応上げておきます。

merlionXX
質問者

お礼

imogasiさん、いつもありがとうございます。 勉強します。

関連するQ&A

  • EXCELでの足し算

    A列に数字が入っていてB列に=A1/2という式を入れています。 それが20行まであり、B21のセルに=SUM(B1:B20)という式を入れています。 A列の数字が例えば105と言う数字がどこかにはいっているとするとB列は52.5ですが四捨五入されているのか53となっています。 B1からB20は四捨五入されていいのですが B21に足された数字もB1からB20の表示どおり四捨五入されたものを足してほしいのですが 52.5を足したようになってしまいます。 どうしたらよいでしょうか? どなたか教えてください。 Excelは2000です。

  • ExcelのSUMを使用して足し算をすると違った答えになります

    合計が違ってきます。 1)Excelの掛け算をした結果(四捨五入の値で表記)を表記されている数値で足し算 2)掛け算されているセルをSUMを使って足し算をする(四捨五入) 合計が違う結果となる場合があるので困っています。

  • Excelで作った表で・・・

     よくある「単価×数量」の表を作りました。 答えに小数点以下になるものが含まれるため、その列に セル書式で小数点以下を四捨五入して整数になるように 設定しました。それぞれの答えはそれで良かったのですが、今度はさらにそれらの合計を求めようと「SUM」を 入力したら表示された整数の合計ではなく、 それぞれのセルある小数点以下の数次を合計して四捨五入してしまいます。  (例)単価×数量=1.2 表示は1    単価×数量=1.3 表示は1        計 3 になってしまう←「2」にしたい       どうしたらいいのでしょうか?教えてください。

  • Excelで

    お願いします Excelで 1.セルD12に=SUM(D1:D11)と入ています値が25555時   25600と成るよう10の位を四捨五入したい。 2.残高がマイナスの時赤字又はセル色を赤にしたい。 3.シート1のセルA25に値が入るとシート2のセルD10にA25の値  が入る様にしたい。

  • Excel VBAでの値の比較

    お世話になります。 Excel VBAでの値の比較方法についてご教授頂きたく存じます。 下記のような値がセルに入っていると仮定しまして、 セルA1とセルG1を比較する セルA2とセルG2を比較する セルB1とセルH1を比較する セルB2とセルH2を比較する 値が違う場合のみ、A列、又は、B列のセルの色を変更したいのですが・・・。 下記例の場合であれば、B1とA2がセルの色が変われば良いです。    A列 B列    G列 H列 1行  1  1     1   2 2行  2  2     3   2 VBAで実現したいと思います。 何卒、宜しくお願い申し上げます。

  • どなたかエクセルにお詳しい方がいらっしゃいましたら、

    どなたかエクセルにお詳しい方がいらっしゃいましたら、 教えて頂けるとうれしいです。 下記数値をそれぞれ小数点以下を四捨五入しています。(それぞれ別の数値をSUMで合計しています。) 四捨五入したものをSUMすると下記の数値になってしまい、1円ずれてしまいます。 どうしたら、四捨五入したそれぞれの数値をちゃんと計算できるように なりますでしょうか? 宜しくおねがいしますm(__)m 23,650.00 38,280.00 9,890.00 61,060.00 72,670.00 30,040.00 20,640.00 10,200.00 28,050.00 17,850.00 40,162.50 46,537.50 35,062.50 32,650.00 下記は、上記数値をSUMで合計を出した数値 466,743円 本来は、 466,743円にしたいですm(__)m

  • エクセルで最大値から調整したいが最大が複数あった場合?

    すみません、助けてください。 エクセルの表計算で困っています。 A1~M1にそれぞれ数値(小数あり)がはいっており、N1に合計が入ります。 A2~M2には上の行の値を10円単位に四捨五入した値が入ります。これを合計してN2に持ってくると、四捨五入の関係でN1を10円単位に四捨五入した値にならないときがあります。 そこでN2はN1を四捨五入した値(これが最優先の値)にし、2行目(A2~M2)の合計と差が出た場合はA2~M2中の最大値から引いたり足したりして調整しています。そのため P2に=MAX(A2:M2)として最大値を求め、3行目A3~M3に最大値と同じならTrueがでるようにして、Trueのある列の2行目から差額を差し引きした額をA4~M4の4行目に表示しています。 ところが、これだと万一、最大値が2つあった場合、二重に差し引きされ、またおかしくなってしまいます。 なにかいい方法はないでしょうか?

  • エクセルの足し算

    エクセルで四捨五入の計算式を入れたセルどうしを足し算すると1合わなくなってしまいます。 セルAに620が入っていて、セルBにAの値620×167.66四捨五入をした数字を入れ、その結果のセルBの値を3行足し算すると1合わなくなります。 セルBは103,949になり3行足すと311,848になってしまうのです。 どうしたら良いか教えてください。

  • 範囲指定した箇所の数値を一括で四捨五入したい

    EXCELのマクロで 範囲指定をしたセルの数値(数式ではなく、数字です)を一括で四捨五入(小数点第3位を四捨五入(小数点第2まで表示))した数値を置き換えるマクロを知りたいのですが、調べられずに困っています。 (roundで四捨五入して、それを値だけ貼り付ければ・・と言うことではなく、あくまでもマクロでの方法を教えてください) よろしくお願いいたします。 バージョンEXCEL2000

  • エクセルで数値を四捨五入したいのですが・・・

    初歩的ですいません。今E14のセルに666.66666・・・と計算の結果、数値がはいっております。この数値を自動で四捨五入させ、670と表示させたいのです。どのようにしたらいいでしょうか?教えて下さい。