[[20220715203857]]　『数値の均等化とその可視化』（まゆはむ）　ページの最後に飛ぶ

『数値の均等化とその可視化』（まゆはむ）

ＡからDの異なる数値の平均値に数字を均等化し、
なおかつどこの数値を動かしたのかがわかるように
したいのですが、可能な関数はありますでしょうか？？

例:
a100 b80 c100 d120
→上記の場合平均値は100なので
　dの20をbに足せば平均値になる

＜使用 Excel：Office365、使用 OS：WindowsVista ＞

 AVERAGE関数との差でいいんでない?

（白茶） 2022/07/15(金) 21:06

単に平均値を計算することには意味がありますが、
「どこの数値を動かしたか」を問う意味を明確にしてください。

A 150 B 80 C 110 D 60 などという時、
いろいろな「動かし方」があるでしょう？
ユニークには決まりませんよ。
（感想） 2022/07/15(金) 21:12

AからDの異なる人物のそれぞれの持ち数があり、
均等にするには誰から誰にどのくらい
数値を動かしたかを明確にしたいと思ってます。
(実際には30人ほどいます)

仰られているように、私が例でだしたように
綺麗な数値ではなく、いろいろな数値の動かし方があるので
より明確に「誰から、誰に、いくら」
がわかればと思い質問させていただきました。

（まゆはむ） 2022/07/15(金) 21:23

 ＞ 使用 Excel：Office365、使用 OS：WindowsVista
 Vistaで365が動いているんですね？

 ＞より明確に「誰から、誰に、いくら」
 何も以て明確と言うかは、多分感覚的なものもあり、
 ケースバイケースで(人により、状況により）判断が分かれると思います。

 余裕のあり過ぎる方から、余裕のなさ過ぎる方へ優先的に持ち点を移動する方針の場合

 ーー以下、削除ーー

 （半平太） 2022/07/16(土) 17:55　バージョン相違により、無用化回答を削除　2022/07/17(日) 17:30

スマホ画面にて押し間違いをしていました(^_^;)
すみません。windows10です。

拙い言葉から意図を汲み取っていただき感謝いたします。
こちらの式で欲しい数値がでてきました。ありがとうございます。
自身のPCではできるのですが、職場のExcelが2016の場合
この式はエラーになりますか？？

（まゆはむ） 2022/07/16(土) 21:21

 ＞職場のExcelが2016の場合
 ＞この式はエラーになりますか？？

 サブスクで365のサービスを契約していればOKですが、 
 なければ、エラーになります。
 その場合は、他の回答者のレスをお待ちください。

（半平太） 2022/07/16(土) 23:05

ご回答ありがとうございます。
自宅PCと職場PCのバージョンの違いをうっかりしてました、、
回答を待ちたいと思います。
（まゆはむ） 2022/07/17(日) 16:52

 こんばんは、「ダメでもともと」ですが				
 何もしないよりはやってみたほうがよいので^^;

     |[A] |[B]     |[C]     |[D]     |[E]|[F] |[G] |[H] |[I]     |[J]|[K] |[L] |[M] |[N]    |[O]|[P]     |[Q]     |[R]   |[S]     			
 [1] |RANK|名前    |持数    |偏差    |   |昇順|名前|持数|偏差    |   |降順|名前|持数|偏差   |   |名前    |持数結果|可視化|付与持数			
 [2] |   1|A       |     150| 48.125 |   |   1|A   | 150| 48.125 |   |   8|G   |  60|-41.875|   |G       | 108.125|A ⇒ G| 48.125 			
 [3] |   5|B       |      95| -6.875 |   |   2|E   | 130| 28.125 |   |   7|F   |  75|-26.875|   |F       | 103.125|E ⇒ F| 28.125 			
 [4] |   6|C       |      80|-21.875 |   |   3|D   | 115| 13.125 |   |   6|C   |  80|-21.875|   |C       |  93.125|D ⇒ C| 13.125 			
 [5] |   3|D       |     115| 13.125 |   |   4|H   | 110|  8.125 |   |   5|B   |  95| -6.875|   |B       | 103.125|H ⇒ B|  8.125 			
 [6] |   2|E       |     130| 28.125 |   |   5|B   |  95| -6.875 |   |   4|H   | 110|  8.125|   |H       | 101.875|-     |-       			
 [7] |   7|F       |      75|-26.875 |   |   6|C   |  80|-21.875 |   |   3|D   | 115| 13.125|   |D       | 101.875|-     |-       			
 [8] |   8|G       |      60|-41.875 |   |   7|F   |  75|-26.875 |   |   2|E   | 130| 28.125|   |E       | 101.875|-     |-       			
 [9] |   4|H       |     110|  8.125 |   |   8|G   |  60|-41.875 |   |   1|A   | 150| 48.125|   |A       | 101.875|-     |-       			
 [10]|      			
 [11]|    |平均値  |101.875 |      			
 [12]|    |標準偏差|28.16664|        |   |    |    |    |        |   |    |    |    |       |   |標準偏差|3.852759|      |

 （ まゆはむ ）さんが求めているのは、				
 上記のような数値の振り方でも可なのでしょうか？				
 可視化と一言で言われても…どうすれば理想なのでしょうか？

 特に関数の説明は無くても、確認はできるかと思います。				
 上記の例は8人の例です。

 求められている意味合いは、こんな感じでも…良いのでしょうか？

（あみな） 2022/07/17(日) 21:37

(あみな)さんご返信ありがとうございます！
本当に説明が言葉足らずで申し訳ないです(-_-;)

仕事で使う資料で、現在は30人ほどの情報を毎日手計算しています。
30人の持ち数から平均をだし、それぞれが均等になるように一つ一つ足し引きして計算していると、時間と量力が多くかかり負担に感じているため、エクセル等で瞬時に計算できればいいなと思った次第でした。

〈やりたいこと〉
?@1人1人異なる持ち数があるが、その持ち数を均等配分して全員平均値になるように数字を再編成したい
➁均等配分の際に受け渡した持ち数が、誰から誰にいくら移ったのかがわかるようにしたい
(移した持ち数と、渡す人、もらう人を記録して提出する必要があるため)

提示していただいた表で、ほしい情報は大丈夫かとおもいます。

（まゆはむ） 2022/07/18(月) 09:30

 こんにちは

 仕事中で、返信が遅くなりました。（ ; ; ）
 確認からです。

 全員平均値になるようにって...限りなく近いで良いのかしら？
 C列の標準偏差は、28程度だと思うのですが、持ち数振り分け後は
 上記の表だと3～4程度です。( こんな程度しかなりませんが良いのかな？）

 渡す人って、複数人に持ち点を渡す事は良いの？
 それと、上記の表の方法で良いならですが...もう少し表を
 使いやすくしないとって感じます。

 因みに、毎日されてる作業時間は...確認とかされるだろうから
 1時間とか位～かかっちゃてるのかな？

（あみな） 2022/07/18(月) 12:22

移動後の持ち点は少数でも可なのでしょうか。
それとも整数でないとだめですか。

それとVBAでの処理は可ですか。
（hatena） 2022/07/18(月) 14:06

(あみな)さん
お仕事お疲れ様です。
お忙しい中ありがとうございますm(_ _)m

●平均値は限りなく近いで大丈夫です！
●持ち数を渡す人は複数人になってもかまいません。
●作業はそのくらい時間がかかっていて、その作業を行わないと業務に移れず、毎日朝がバタバタしています(-_-;)

(hatena)さん
少数でも大丈夫です。
vbaでの処理は出来ないかとおもいます。すみません
（まゆはむ） 2022/07/18(月) 15:23

 持ち数の付与が、複数人でも可なのですね。ちょっとややこしくなった^^;
（あみな） 2022/07/18(月) 15:45

 これね、本当の意味で言ったら...VBAの出番なんだと思う。

 A➡G への付与数が、平均値より 6.25 高いじゃないですか～、つまり
 オーバー分を Cさんに付与するような事は、マクロの出番なんだと思う。

 VBAの初期設定だけなら、1～2時間勉強すれば...できちゃうですよ。
 私なら、Hatena さんによろしくお願いしますってしてしまいます。（笑）

 仕事中なんで、またコメントします。
（あみな） 2022/07/18(月) 16:41

そうなんですね！
Excel初心者で知識がなくよくわからなかったので、、、
作業が楽になればなー、という安易な気持ちで質問してしまいました(^_^;)
勉強してみますね。

（まゆはむ） 2022/07/18(月) 17:12

 こんばんは、VBAでやる気になったでありますか (*´꒳`*ﾉﾉﾞﾊﾟﾁﾊﾟﾁ

 「当該」作業を行わないと業務に移れず、毎朝がバタバタでは									
 いやですよね。大丈夫！！...まもなく解放されるかと思います。( きっと )

 VBAを事務員さんなら少しは触れた方が、仕事の能率が上がること									
 間違いないのでチャレンジされると良いと思います。

 でも、その前にですが...（hatena）さんがもう一度このスレを見てくださるかは									
 わかりませんよ。他にしてくださる方がいるかも...わかりません。

 因みにですが、私は仕事なのですぐに時間を作れませんし、出来るかもわかりません。(笑)									
 と上記のレイアウトですが、VBAでする場合と関数でされる場合では、必要な列などが変わります。									
 作りこみは、その方にお任せするってかんじでよいのでしょうか？

 先回のサンプル表では、比率の列がありましたが…これはいらないですよね？									
 なので消しますね。

 と...先回までのサンプル表と違い、ちょこっと見出しなどを変更してみました。

 （あみな） 2022/07/17(日) 21:37 レス表を確認ください。

  (ﾉω｀*)んふふ…♪

 目安として...標準偏差があれば良いかなと思いますです。									
 あと、小数点以下「端数処理」、第二位、第三位などの四捨五入か切捨てかなどどうされてますか？

 とVBAの初期設定の説明動画を、ピックアップして見ました。( 参考にどうぞ )

 ◆なんだ！カンタン！Excel塾

 エクセルマクロはじめの一歩、マクロ講座超入門編1回									
https://www.youtube.com/watch?v=AC-KU-jE1xw&list=PLCLY7GstKj_BRN_5p_m9FbC13Z7izsCjt

 標準モジュールの作成と削除、モジュールのエクスポート・インポート、マクロ講座超入門編2									
https://www.youtube.com/watch?v=Gs-Q8xqCHRw&list=PLCLY7GstKj_BRN_5p_m9FbC13Z7izsCjt&index=2

 VBA 開発タブを表示しマクロの記録をやってみる,Excel塾の日本一やさしいマクロ講座 入門編 1回(再）									
https://www.youtube.com/watch?v=ORVIfnChyRM&t=352s

 VBA セキュリティの警告とセキュリティ設定,マクロのコード、Excel塾の日本一やさしいマクロ講座 入門編 2回(再）									
https://www.youtube.com/watch?v=NBpoMN6OilE

 ◆PC活用ちゃんねる

 VBEの設定！エクセルVBAの記述が快適になるおすすめ7つ									
https://www.youtube.com/watch?v=9lQslW8UHpM&t=24s

 ◆Office TANAKA

 VBEの使い方 - コードウィンドウの分割 からの 複数モジュールを同時に表示する方法									
https://www.youtube.com/watch?v=PMtICUxB-OI

（あみな） 2022/07/18(月) 21:49

 VBAならなんとかできるだろうと思っていましたが、
やってみると意外と難しかった。
仕事の合間にやっていたので、いままでかかってしまいました。

 まずは、下記のようなデータがあるとします。(あみなさんのデータをそのまま流用させていただいてます)

     |[A]     |[B]     |
 [1] |名前    |持数    |
 [2] |A       |     150|
 [3] |B       |      95|
 [4] |C       |      80|
 [5] |D       |     115|
 [6] |E       |     130|
 [7] |F       |      75|
 [8] |G       |      60|
 [9] |H       |     110|

 これを、E列F列にコピーして、持数の降順で並べ替えます。
F列の持数をG列にコピーしてこれを持数結果とします。

 Do Loop で先頭からと最後から平均との差を求めて、
それを比較しながら数値を移動させます。

 Public Sub test()
    Dim lastRow As Long
    lastRow = Range("A1").End(xlDown).Row        '最終行

    'A列B列をE列F列にコピー
    Range("E1:F" & lastRow).Value = Range("A1:B" & lastRow).Value

    'F列で降順に並べ替え
    Range("E1:F" & lastRow).Sort Key1:=Range("F1"), Order1:=xlDescending, Header:=xlYes

    'F列をG列にコピー
    Range("G1").Value = "持数結果"
    Range("G2:G" & lastRow).Value = Range("F2:F" & lastRow).Value

    Dim avg As Double
    avg = WorksheetFunction.Average(Range("F2:F" & lastRow))

    Dim i As Long, diff1 As Double, j As Long, diff2 As Double
    Dim c As Long, c2 As Long
    c = 8                                        'H列から移動処理を表示
    i = 2
    j = lastRow

    Do Until i >= j
        Stop '動作確認用の一時停止 確認後は削除
        diff1 = Range("G" & i).Value - avg
        diff2 = avg - Range("G" & j).Value
        If diff1 = diff2 Then
            Cells(j, c).Value = Range("E" & i).Value
            Cells(j, c + 1).Value = diff1
            Cells(i, c).Value = Range("E" & j).Value
            Cells(i, c + 1).Value = -diff2
            Range("G" & i).Value = avg
            Range("G" & j).Value = avg
            i = i + 1
            j = j - 1
            c = 7
        ElseIf diff1 > diff2 Then
            Cells(j, c).Value = Range("E" & i).Value
            Cells(j, c + 1).Value = diff2
            Cells(i, c).Value = Range("E" & j).Value
            Cells(i, c + 1).Value = -diff2
            Range("G" & i).Value = Range("G" & i).Value - diff2
            Range("G" & j).Value = avg
            j = j - 1
            c = c + 2
        Else
            Cells(j, c).Value = Range("E" & i).Value
            Cells(j, c + 1).Value = diff1
            Range("G" & j).Value = Range("G" & j).Value + diff1
            Cells(i, c).Value = Range("E" & j).Value
            Cells(i, c + 1).Value = -diff1
            Range("G" & i).Value = avg
            i = i + 1
            c = c + 2
        End If
    Loop

    MsgBox "完了しました。"
End Sub

 上記の実行結果

     |[A]     |[B]     |[C]|[D]|[E] |[F] |[G]     |[H]|[I]    |[J]|[K]  |[L]|[M]    |[N]|[O] |[P]|[Q]    |[R]|[S]  |[T]|[T]   |
 [1] |名前    |持数    |   |   |名前|持数|持数結果|   |       |   |     |   |       |   |    |   |       |   |     |   |      |
 [2] |A       |     150|   |   |A   | 150| 101.875| G |-41.875|F  |-6.25|   |       |   |    |   |       |   |     |   |      |
 [3] |B       |      95|   |   |E   | 130| 101.875|   |       |   |     | F |-20.625| C |-7.5|   |       |   |     |   |      |
 [4] |C       |      80|   |   |D   | 115| 101.875|   |       |   |     |   |       |   |    | C |-13.125|   |     |   |      |
 [5] |D       |     115|   |   |H   | 110| 101.875|   |       |   |     |   |       |   |    |   |       | C |-1.25| B |-6.875|
 [6] |E       |     130|   |   |B   |  95| 101.875|   |       |   |     |   |       |   |    |   |       |   |     | H | 6.875|
 [7] |F       |      75|   |   |C   |  80| 101.875|   |       |   |     |   |       | E | 7.5| D | 13.125| H | 1.25|   |      |
 [8] |G       |      60|   |   |F   |  75| 101.875|   |       |A  | 6.25| E | 20.625|   |    |   |       |   |     |   |      |
 [9] |H       |     110|   |   |G   |  60| 101.875| A | 41.875|   |     |   |       |   |    |   |       |   |     |   |      |

 このデータ以外にもあと２つぐらいサンプルデータを作成して動作確認してますが、
うまく行かない場合があるかもしれません。
実際に運用する場合は、数多くのデータで動作確認をしてください。

 また、数値移動の表示は空欄が多くて見づらいという場合は、空欄を詰めるの処理をいれるなどしてください。

（hatena） 2022/07/19(火) 20:18

ドタバタしておりまして、コメントが遅くなりすみませんm(__)m

◎あみなさん
ご丁寧に参考動画まで教えていただきありがとうございます！！
何から何まで感謝いたします。
事務仕事ではないのですが、、
知って勉強しておけば必ず役に立つと思うので、この機会にExcelを勉強してみます。
表などはお任せさせて貰えればありがたいです。表の修正もありがとうございました！
小数点以下は切り捨てで考えています。

◎hatenaさん
お仕事の合間の貴重なお時間をさいていただきすみません...！！
本当にありがとうございます！！
動作確認も承知しました。
自身で挑戦してみたいと思います。

（まゆはむ） 2022/07/19(火) 21:25

まゆはむさん

VBA初心者には回答のコードを理解するのはかなり難しいと思います。

あみなさんのリンク等を一通り参照してみて、VBAの概要をつかんだら、
回答のコードをコピーして、標準モジュールに貼り付けてください。

処理したいデータを新規シートにコピーしてから、新規シートがアクティブな状態で、
コードを実行してください。コード内にカーソルを置いてF5キーで実行できます。（メニューの▶をクリックしても可）

Stop のところで中断しますので、シートの結果を確認してからまた、F5キーを押して続きを実行します。
"完了しました。"とメッセージが出たら処理完了です。
結果を確認してください。

希望の結果なら、これをもとのシートにコピーします。
これでだいぶ楽ができると思います。

コードの内容の理解は少しずつ進めていけばいいでしょう。
（hatena） 2022/07/19(火) 22:04

hatenaさん

ご丁寧にご説明ありがとうございます。
頑張ってやってみます！！
理解ができるように勉強もすすめていこうと思います。
本当にありがとうございます。

（まゆはむ） 2022/07/19(火) 22:47

  ﾟ（hatena）さん+｡:.ﾟヽ(*´∀`)ﾉﾟ.:｡+ｲｲﾈ!! ｲｲﾈ!ﾟ

 動いた動いたよー、うん…ドンピシャ「理想型」出力だと思う								
 私が、喜んでドウスルンダッテ… ^^;

 （まゆはむ）さん…後は、マクロ走らせて…運用試験するだけよ。

 これで、朝ゆっくりお茶飲めるよ。								
 また、動いたらコメント入れてね。

 ではでは♪ (*´︶`*)ﾉ　bye bye♪♪♪

（あみな） 2022/07/19(火) 22:58

(hatena)さん
先程行ってみました！無事にエクセルに表示することができました。
希望の数字が見えるようになって感動してます・・！！
明日からの業務負担が軽くなると思います。本当にありがとうございました！

（あみな）さん
皆様の支援のお陰で無事に動きました(;_;)
何から何まで親身にありがとうございました。
動画も少しずつ見ています！
本当にありがとうございました！！

（まゆはむ） 2022/07/20(水) 21:07

 o(*'▽'*)/ ﾔｯﾀｧﾈ+.☆ﾟ!!「ようこそ...VBAの世界へ！」

（あみな） 2022/07/20(水) 22:48

コメント返信：

[ 一覧(最新更新順) ]

[[20220715203857]] 『数値の均等化とその可視化』（まゆはむ） ページの最後に飛ぶ

[[20220715203857]]　『数値の均等化とその可視化』（まゆはむ）　ページの最後に飛ぶ