○第171回目 規則的な座標を用いた計算式の作成
(最終的には加重平均作成となってしまった感がある)
前回の応用というのか、余興というのか。というようなものです。
一組の座標群を作り、それに飛びと項目群の数を与えて指定数の座標群を作ります。それらを+でつないで計算式モドキにし、セルに入れます。つなぐものとして、:も考えますので、その場合は計算式の要件を満たしていません。結果(計算式または、結合結果)をセルにいれる時には注意をしましょう。
具体的には、
【例1】
b3*c3をまず入れて、それから飛び数と項目群の数により複数の群を作り、結合します。この例では、計算式となります。
(b3*c3) + (f3*g3) + (k3*l3) + …
流れです。
0.計算式を入れるセルにカーソルを置いて実行
1.第一の項目群を作る。カッコの入力は不要です。
2.第二の項目群の方向と飛び数を入力する(片方の飛び数は0とする)
横方向が多いので、縦の場合は*をつけることにします。あわせて、パラメータの入力を、1-6とかして、3をかねてしまう。飛びはマイナスもあるので、-3-6という指定も可能とします。
3.全部の項目群の数を入力する
計算に使うセルはすべて同じシートにあるものとします。
ただし、計算式のあるセルと計算に使うセルは同じシートにある必要はありません。
最初の項目群の中でそれぞれの座標を区切るものとしては、四則演算子のほかに、:,も可能とします。
あわせて、同順位の項目どうしの結合(上の例では、b3,f3,k3 …の組と、c3,g3,l3…の組)も作成することも可能にします。これは割る数として使用すれば、加重平均が求められます。
Q 用途は？
A 加重平均を想定していますが、飛び飛びに合計を取るということはないでしょうか。これが10項目もあると大変ですよね。これまでは、補助数値を入れてsumifで合計をとっていたと思います。
Q 加重平均の場合、平均値のほかに、例えば、人数の合計値も必要なのではないでしょうか
A そのとおりですね。機能追加します。無条件に各階層の合計値の計算式((b3+f3+k3+…) 、(c3+g3+l3+…))を、下に向かって入れてしまいましょう。これを必要な場所に移植して、平均値の計算式を作るという流れです。こちらのほうが平均値の計算式が短くなります。下の部分は、これから計算式を作ろうとしていることが考えられるので、合計値を入れても支障がないと思われます。
【例2】
茶館複雑な例としては、《人数、項目1、項目2、項目3、項目4の組》が、下に向かって一行おきに8セットあり、そして、それぞれの項目で平均をとるというものがあります。
操作の流れです。
0.計算式(平均値)を入れるセルにカーソルを置いて実行。
1.アンカーとなるセルを指定。通常は第一群の人数のセルとなる。
2.人数1*項目1を指定
3.方向ととび数を与える。1行おきに縦方向で8グループなので、パラメータは*2-8となります。
Q 階層ごとの合計を求めるということですが、表示する場所はどこになりますか。
A まず、最初の計算式をセットするセルは、第8群の項目1の何行か下にします。人数の合計は、人数の下になり、平均を入れる計算式と同じ行です。各層の合計値は、平均値の下に展開されます。
イメージとしては次のようになります。
人数の合計、項目1の平均値、項目2の平均値、項目3の平均値、項目4の平均値
項目1の合計値、項目2の合計値、項目3の合計値、項目4の合計値
Q ん？何かおかしくありませんか。何かが不足していませんか。
A そのとおりのです。何かが不足しています。指定すべきものの記述がひとつ抜けています。これはやっている中で機能追加したものですが、頭がこんがらがるので、後で説明しようとしていました。
つまり、説明では、一組の計算式を求めるためのものでしたが、上の例では、4つの組の計算式を求めるものに変わっていたのでした。そのために、指定がひとつ必要となります。それは、2組目、3組目、4組目の元となる計算式です。これを指定する必要があります。もととする計算式の入力のところが、必要な分だけループするのです。
Q それなら納得です。では、少し形式を変化させたものとして、人数、金額が縦にあります。これが横に4つ展開しています。この加重平均はどうでしょうか。【例3】
A これは、先ほどと縦横逆になったものです。項目が横にならんで、縦に展開してある。ですから、これも同様にできます。
人数1 人数2 人数3 人数4 人数の合計
項目1 項目1 項目1 項目1 項目1の平均値 項目1の合計値
項目2 項目2 項目2 項目2 項目2の平均値 項目2の合計値
項目3 項目3 項目3 項目3 項目3の平均値 項目3の合計値
項目4 項目4 項目4 項目4 項目4の平均値 項目4の合計値
Q 縦に、人数、項目(1～4)、人数、項目…となっていたらどうですか。【例4】
A ほとんど同じ操作でできます。
まずもととなる計算式を与えなければならないのですが、
最初のものは、人数*項目と与えます。
次のものは、人数*項目と与えます。これを続ける。
パラメータは、1-4(横方向、飛び数は1行、1行に4個の項目がある)となります。
Q すごいですね。これの複数セットはどうですか。
更に同じものが下に2行はなれて2セットあるという状態です。
A これは、まず1セットでやってもらって、その結果をコピーするという方法です。
Q 基にする計算式は2項目のみでできているものに限られるのですか。
A そんなことはありません。どこまでかはマクロの中の問題ですが、3項目以上も可能です。また、項目をつなぐのは、四則演算のほかに、:も可能なようにしておきましょう。
データの形式別に整理してみました。(『値』は上の説明における『項目』と同じです)
一項目の加重平均
【縦1】
人数(ウェイト)、値
人数(ウェイト)、値
：
答え
【縦2】
人数(ウェイト)
値
人数(ウェイト)
値
：
答え
【横1】
人数(ウェイト)、値、人数(ウェイト)、値 … 答え
【横2】【例1】
人数(ウェイト)、人数(ウェイト) … 答え
値 、 値 …
数項目の加重平均
人数(ウェイト)が同じもの
【縦1】【例2】
人数(ウェイト)、値、値、値 …
人数(ウェイト)、値、値、値 …
：
答え、答え、答え …
【縦1-2】
人数(ウェイト)、人数(ウェイト)、人数(ウェイト) …
値、 値 、 値 …
値、 値 、 値 …
値、 値 、 値 …
値、 値 、 値 …
：
答え、答え、答え …
【縦2】【例4】
人数(ウェイト)
値 、 値 、 値 、 …
人数(ウェイト)
値 、 値 、 値 、 …
：
答え 、答え 、 答え …
【横1】【例3】
人数(ウェイト)、人数(ウェイト)、人数(ウェイト) …
値、 値 、 値 … 答え
値、 値 、 値 … 答え
値、 値 、 値 … 答え
値、 値 、 値 … 答え
【横1-2】＝⇒意味が薄い
人数(ウェイト)、値、値 … 答え
人数(ウェイト)、値、値 … 答え
人数(ウェイト)、値、値 … 答え
【横2】＝⇒意味が薄い
人数(ウェイト)
値 、 値 … 答え
人数(ウェイト)
値 、 値 … 答え
人数(ウェイト)が違うもの
【縦1】
人数(ウェイト)、値、人数(ウェイト)、値、人数(ウェイト)、値 …
人数(ウェイト)、値、人数(ウェイト)、値、人数(ウェイト)、値 …
：
答え 、答え 、 答え …
【縦2】
人数(ウェイト)、人数(ウェイト)、人数(ウェイト) …
値 、 値 、 値 、 …
人数(ウェイト)、人数(ウェイト)、人数(ウェイト) …
値 、 値 、 値 、 …
：
答え 、答え 、 答え …
【横1】
人数(ウェイト)、値、人数(ウェイト)、値 … 答え
人数(ウェイト)、値、人数(ウェイト)、値 … 答え
人数(ウェイト)、値、人数(ウェイト)、値 … 答え
【横2】
人数(ウェイト)、人数(ウェイト) …
値 、 値 … 答え
人数(ウェイト)、人数(ウェイト) …
値 、 値 … 答え
