☰

平均に最も近い数値を見つける方法

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
平均に最も近い数値を見つける方法

平均に最も近い値を見つけたいサンプルがあります

都市と重量は2つの別々の列です

city  weight
A      23
A      22
A      45
A      97
B      34
B      22
B      23
C      76
C      23
C      23

ピボットを作成し、A-の重量の平均を計算しました。46.75です。

Aに最も近い数字を見つける必要があります。この場合、45になります。

インデックスと一致を使用する必要があると思いますが、重複した都市名と異なる重み値を持つ 17,000 行がある場合はどうすればよいでしょうか?

助けていただければ幸いです

私が探している答えは

Row Labels  Average of WEIGHT     nearest number
A                 46.75            45
B                 38.75            34
C                  23              23

同様の回答のほとんどはこのセットを使用していません。私が試したこの数式の設定を手伝ってください:

INDEX(rawdata,MATCH(MIN(ABS(weight-$B2)),ABS(weight-$B2),0),2)

しかし、ACからの重みの配列全体を見ています。Aの平均を比較するときにAの値だけを見たいのです。

そしてBの平均と比較した場合のBの重みは、

等々....

私の式のどこが間違っているのか教えてください。

前もって感謝します

答え1

編集：

Weight申し訳ありませんが、あなたの質問をよく読んでおらず、平均値に最も近い値を見つけたいとおっしゃっていることに今気づきました。都市の価値の中で平均が計算されたものです。そのため、以下の回答を更新しました。

見つけたようですXOR LXの回答同様の質問に対して、あなたはほぼ正解に近づいています。

XOR LX は、順序付けられていないデータを検索する際の制限を回避する、非常に優れた小さな式を使用しましたMATCH()。その仕組みを以下で説明します。

以下に示すデータ テーブルでは、次の値を使用して平均を計算しました。

=AVERAGEIF(A$2:A$11,A14,B$2:B$11)(上記で示した回答とは異なる回答が得られます)。

そしてWeight平均値に最も近いのは次のようになります。

=INDEX((A$2:A$11=A14)*(B$2:B$11),MATCH(TRUE,(A$2:A$11=A14)*ABS(B$2:B$11-B14)=MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14))),0))

これは配列数式なので、CTRLShiftEnterだけではなく と一緒に入力する必要があることに注意してくださいEnter。

ここに画像の説明を入力してください _________________________________________________________________________________

使い方：

ABS(B$2:B$11-B14)は、平均とリスト内のすべての数値との差の配列ですWeight。 は、が等しい箇所では となる値(A$2:A$11=A14)の配列です。これら 2 つを掛け合わせると、 に対応する位置では となり、それ以外の箇所ではとなる差の配列が得られます。True/FalseTrueCityA14City = A140

次に、それらの差の最小値を見つけたいのですが、配列に差がある 場合はMIN()が返されるため、少し異なる配列を作成する必要があります。00's

IF(A$2:A$11=A14,ABS(B$2:B$11-B14))がどこにあるかをチェックしCity = A14、Weightそれらの位置での と平均の差を返しますFalse。それ以外の場合は となります。

その配列の最小値を取ると、MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14)))差は最小になります該当するポジションのみ City = A14。

ここで、等式は、現在の の最小差の位置にある値(A$2:A$11=A14)*ABS(B$2:B$11-B14)=MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14)))の配列を返します。 は の位置(最も近い数値の位置) を見つけ、それが に渡されて実際の値を返します。True/FalseTrueCityMATCH()TrueINDEX()

これが役に立つことを祈ります。幸運を祈ります。

関連情報