【Excel】循環参照の回避について

質問

こんにちは、いつもお世話になっております。
掲題の件、何かいい知恵はないかご教授いただけると幸いです。

ある一定の数字を部署で按分する作業がありまして、
その按分の端数は決まった部署で調整する、という作業があります。
例)数字100をA～Fの部署で按分(四捨五入)
各部17になり、合計すると102(C7にSUMを入れています) になるので、
過剰分2(E1で計算)をAからマイナスして、Aは15にしたい。
上記をエクセルで組むと、循環参照になってしまいます。

どのように組めば調整値も自動で反映されるように
なるでしょうか。

按分する数字が何百もあり、手入力はちょっと難しいです。

なるべく循環参照にならないように組みたいと思っています。
お知恵お貸しくださいますと幸いです。
宜しくお願い致します。

回答No.1

f272 · ベストアンサー

作業列としてD列を使う。
D1=ROUND(A$1/6,0)
合計もD7=SUM(D1:D6)で計算する。
過剰分E1=D7-A1が分かったら，あらためてC列を計算する。
C1=D1-E1
C2=D2

補足

ありがとうございます。大丈夫でした。
最初(私が作ったもの)と何が違うのかが
いまいち私が理解できず、どうしてこれなら大丈夫なのか
もう少し考えさせてください。
(私の理解の問題です。ごめんなさい)

回答No.10

tsubu-yuki

たびたびすいません。
Ｎｏ９、一箇所訂正です。

Ｄ２セル：=A2-SUM(C2:C6)　　　×
　　　　　↓
Ｄ２セル：=A2-SUM(C2:C7)　　　〇

でした。
失礼しました。

回答No.9

tsubu-yuki

あるいは、もっと単純でも良いかも。

なんとなく行見出しを追加して、データは２行目以降にしています。
　　※見出しは適当ですから、気になさらないでください。
そのうえで、Ｃ列には素直に「６部門で平均して四捨五入したもの」
　　Ｃ２～Ｃ７セル：=ROUND($A$2/6,0)
としてやります。
で、「調整値」として、元の数字と案分した合計との差を
Ｄ２セルに単純に求めます。
　　Ｄ２セル：=A2-SUM(C2:C6)
ここでは「－２」が返る（はず）ですね。
で、最終的にＣ列とＤ列を加えたものが求める値ですので、
　　Ｅ２（～Ｅ７）セル：=C2+D2　（以下、行方向にフィル）
という式で計算します。

というわけで、ＲＯＵＮＤとＳＵＭだけでできました。

おそらく、としか言えませんが、
「少ない列でなんとかしよう」という思いが強すぎるのかなぁ、
という気がします。
順を追って考えると難しい関数式はひとつも使わずに出来ることに
気づけると思いますよ。
まずは「単純に」考えることです。
「複雑な関数式がかっこいい」というのは幻想だ、と私は思います。

回答No.8

tsubu-yuki

えーと、（既出回答も含めて）現状がよくわからないのですが、
横から少し口を出させていただきますね。
申し訳ないです。

すごく単純に、質問文中の例示から添付図の通り、
部門Ｂ～Ｆ（５部門）は
「合計（１００）を、部門の全数（６）で割って四捨五入」
　＝ＲＯＵＮＤ（＄Ａ＄１／部門全数，０）
　※ここで「部門全数＝COUNTA(B:B)」にしています。
　　つまりＣ２セル：=ROUND($A$1/COUNTA(B:B),0)　で、行方向フィル
問題のＡ部門の分は
合計から「Ｂ～Ｆ部門の合計を引いたもの」ですから、
　＝全体－（ＲＯＵＮＤ（＄Ａ＄１／部門全数，０）＊（全部門数－Ａ部門））
ですから、
　Ｃ１セル：=$A$1-(ROUND($A$1/COUNTA(B:B),0)*(COUNTA(B:B)-1))
である、となりませんか？
部門数を固定出来るのなら、例えば６部門として
　Ｃ１セル：=$A$1-(ROUND($A$1/6,0)*5)
　Ｃ２～Ｃ６セル：=ROUND($A$1/6,0)
で良さそうですね。

Ｅ１セルに表示したい差額（？）についても深く考えずに
　Ｅ１セル：=A1-ROUND(A1/6,0)*6
　　※すいません、回答の都合で添付図ではＥ２セルに入れてますが、
　　　同じ式をＥ１セルにそのまま入れてもＯＫです。
つまり、元のＡ１セル（１００）から
「平均して四捨五入した数（１７）に部門全数（６）を掛けたもの」＝１０２
を引いてやれば良いとおもんですが、実際はどうなのでしょう？

差額（？）がプラスに転じたものについても同様に
Ａ部門に加算するのであれば、
おそらく、コレでいけると思いますよ。

回答No.7

asciiz

按分してから戻って修正しようとしてるので、循環してしまいます。
なのであらかじめ、生じる端数を計算しておけば、戻らなくて済みます。

A1 = 按分する数字
A2 = いくつに按分するか(例の場合、6)
A3 = round(A1/A2, 0)*A2 - A1

これでA3に、四捨五入したものを足した場合に、最初の数字からズレる分が表示されます。(今回の場合、「2」)

なので、一行目だけその数字で調整して、あとはそのまま四捨五入。

C1 = round($A$1/$A$2 , 0) - $A$3
C2 = round($A$1/$A$2 , 0)
C3 = round($A$1/$A$2 , 0)
:
以下同様
(C3以降はC2からコピーでOK、行・列がずれないように絶対参照にしています)

どうでしょう。

なおA3を表示したくなければ、直接C1セルに

C1=round($A$1/$A$2 , 0) - (round(A1/A2, 0)*A2 - A1)

と書いてもいいです。A3セルの結果をそこでしか使ってませんので。

回答No.6

Prome_Lin

では、セル「C1」に

=A1 - SUM(C2:C6)

そして、セル「C2」～「C6」に

=ROUND($A$1/6,0)

でも、ダメでしょうか？

回答No.5

Chiquilin

何だかよく分かりませんが 差分を C1で調整したいということで
しょうか。なら C1の数式だけ

=A1-ROUND(A1/6,0)*5

とすればいいのでは？

回答No.4

bunjii

>過剰分2(E1で計算)をAからマイナスして、Aは15にしたい。
E1の数式は？
C1の数式は？
C7はSUM関数でC1:C6の合計ですよね？
あなたの脳の中で思考が循環しているためかも知れません。
C1セルのみで過剰分も減算できる方法を考えてください。
例
=ROUND(A$1/6,0)-IF(B1="A",ROUND(A$1/6,0)*6-A$1,0)
除数の6は別の関数で算出する場合もあるかと思います。
また、過剰分を上から順に1を減算する考えもあるかも知れません。
超過分を1ヶ所から減算するのは不合理のように思います。
もう1度最初から考え直す方が良いのではないでしょうか？

補足

ありがとうございます。
私の思考が循環しているかもしれませんが…。

とりあえず、Aの部署で過不足を吸収する、という方針は
私の力ではいかんともしがたいため、この発想になってしまいました。

明日もう少し考えてみます。

回答No.3

msMike

C1: =A1-SUM(C2:C6)
C2: =ROUND($A$1/6,0)
C2 を下方にズズーッと(C6 まで)オートフィル
C7: =SUM(C1:C6)
としたら如何？(E2 は不要なので使わない)

補足

総合計から差額を最初から入れる、という発想ですね！

もともといくらで、いくら差額を調整したのかを
見せたいのですが、差額(E2部分)は作成しても循環しないでしょうか？
(明日作成して確認します)
ありがとうございます。もうすこしおじかんくださいませ。

回答No.2

Prome_Lin

これでは、いけないのでしょうか？

Sub Sample()
Dim x As Integer, y As Integer
Dim i As Long
x = Range("A1").Value
y = x / 6
For i = 2 To 6
Cells(i, 3).Value = y
Next i
Range("C1").Value = x - y * 5
End Sub

簡単な説明です。

x = Range("A1").Value

まず、セル「A1」（「100」）の値を「x」に入れています。

y = x / 6

次に、「x÷6」の値を、「整数型」の「y」に入れます。

これで、自動的に、小数点以下が四捨五入して、「y」に入ります。

For i = 2 To 6

２行目から６行目まで処理。

Cells(i, 3).Value = y

「y」の値を、列「C」に列挙（それぞれに「17」が入りました）。

Range("C1").Value = x - y * 5

あとは、セル「C1」に「x - y * 5」すなわち、もともとのセル「A1」の値から、「y * 5」を引いてやれば、「15」が入ります。

お礼

ありがとうございます。色々な人が触るファイルなので、
マクロは登録したくないのです…。
単発の作業ではないため、関数で、数字が変わるたびに自動で
反映できる仕組みにしたいと思っています。