配列数式？ 中央値 平均値

質問

いつもお世話になっております。
早速ですが以前質問させていただいた内容からも発展というか
修正というかです。

http://oshiete1.goo.ne.jp/qa4561210.html

当初、配列数式にて
=IF(COUNTIF($A$2:$A$33930,A2)=1,b2,AVERAGE(IF($A$2:$A$33930=a2,$B$2:$B$33930)))｝
を使用しておりましたが、
= SUMIF($A$2:$A$33930,A2,$B$2:$B$33930)/COUNTIF($A$2:$A$33930,A2)
でも、同一の回答が得られまた処理も大変早く行われるため
非常に助かっておりました。

しかし、最近になって元データ側に不具合あり　平均値ではターゲットとする値から離れていることが発覚しました。

そのため、平均値から中央値　最頻値　のどちらかと思っているのですが
同一データが２つ無い場合もあるため最頻値ではエラーになると思い
中央値で求めたいと思っています。

データ量が6000行を超える場合もあり　配列数式を使って計算するべきなのか、他に良い方法があるのか教えて頂きたく　よろしくお願いします。
エクセル2003です

回答No.3

Wendy02 · ベストアンサー

こんにちは。

本来、私は、マクロが中心ですから、基本的な方法から実務に関する方法まで、いくつか思いつくわけですが、今回、言葉だけですから、実際のマクロ自体の組み立ては出来ませんが、私自身、こういうことには経験がありますが、それをやっていた頃とは、今の私の技術は格段に上です。

>1枚の途中で処理を終わらせて半分ぐらいの時間で計上されている

（いうまでもなく)期待値の範囲外のデータは排除できます。

>　生産予定枚数に対して1台づつカウントされている欄があるので
>その部分の数値が突然３つほど飛んでいたりしたので判明しました。

データがなければ、それも排除できます。

ただ、あまり人為的に手を加えすぎるのは、ちょっと怖いものがあるような気がします。

こちらはあくまでも想像なのですが、6000個のデータ程度でしたら、かなり精密な振り分けは可能だと思います。

通常の 関数では、

品番　---　期待値

この二つの対比は不可能だと思います。品番はひとつであること、期待値の範囲にあること。この二つを簡単に扱えるのは、Dictonary オブジェクトではないかと思います。私個人は、もっと数の多いこともあるので、あまりこのオブジェクトを使いませんが、6000個程度なら十分に使いこなせるられるはずです。

お礼

こんにちは、いつもありがとうございます。

期待値との事なのですが、実際の生産モデルに対する生産時間実績を取り込みたいのです。　基準となる数値が無いために範囲が決められない状況なのです。

また、データが無ければではなく　データは存在しているのですが
３つ飛んだデータが出た場合　3台分の生産時間が1台の生産時間として表示されていたりするのです。他の2台は　ありえない1秒とかそんな数字が出力されていたりするのです。

Dictonary オブジェクトの場合ですと
基準になる元データが必要に思うのですが・・・・

相変わらす説明がへたくそで申し訳ありません。

H53AR-S 75 59 000001:18:28 000000:55:57 0:00:56
H53AR-S 75 60 000001:19:45 000000:56:52 0:00:55
H53AR-S 75 61 000001:20:57 000000:57:48 0:00:56
H53AR-S 75 62 000001:22:45 000000:59:00 0:01:12
H53AR-S 75 63 000001:23:27 000000:59:39 0:00:39
H53AR-S 75 64 000001:24:40 000001:00:35 0:00:56
この様な感じで　75台生産を行った時のデータで61台目までは大きな差は無いのですが62～63にかけて　異常な時間となって出力されるのです

回答No.4

Wendy02

こんばんは。

返事をつけるのが遅くなりました。

結局のところ、今の段階では、あれこれ考えたあげくは、

= SUMIF($A$2:$A$33930,A2,$B$2:$B$33930)/COUNTIF($A$2:$A$33930,A2)
これで成り立ったのでしたら、以下のように上限・下限を切り捨てるということぐらいしか思いつきませんでした。マクロにしても、しょせん、同じだと思います。

実際には、">=下限",">上限" の文字の部分は数字を入れますが、
= (SUMIF($A$2:$A$33930,">=下限",$B$2:$B$33930)-SUMIF($A$2:$A$33930,">上限",$B$2:$B$33930))/COUNTIF($A$2:$A$33930,">=下限")-COUNTIF($A$2:$A$33930,">上限")

この「=」をどう入れるかが、ややこしいような気がしますが。

　　　　捨てる　　　　　　　　　　　　　　　　　　捨てる
---------------+==================+--------------　測定値
　　　　　　　　　　　　実数値

回答No.2

Wendy02

こんばんは。

二種類の方向で考えてみたらどうでしょうか？

ひとつは、データ量が、6000行を越えてしまうと、まず、配列数式がスワップ側に流れていきますから、配列を抜きで進めていくことです。
しかし、私は、SUMIF　で問題ないとは思ったものの、「元データ側に不具合」という「元データ」を振り分けるための条件として、SUMIF を二つ使って、上限から下限を引くというような方法で、COUNTIF も同じように上限と下限を引いて数を出す方法。

もうひとつは、もとのVBAのユーザー定義関数で、その内容の吟味をさせる方法です。
そのためには、元のデータ側の不具合を出すデータが何かを限定しなければなりません。

それと、余談になるかもしれませんが、何か、ヒストグラムを取る過程に似ているような気がします。

もう少し、情報をください。「不具合」とは具体的にはなんのでしょうか？

お礼

お世話になります
いつもありがとうございます。
ヒストグラムを取る過程と考えて頂いて間違いではありません。

元は生産設備の生産時間を求める為に考えた物なのです
設備より1台生産完了毎　稼動分析ソフトへデーターが送られて蓄積されているのです（1台ごとCSVで1ファイル）
そのため、順当に処理をソフトで行っているのであれば問題なく平均でよかったのですが
生産時間の短いものや、他の処理を同時に進行していた時などと思われる時に　1枚の実績ではなく2枚の実績が1枚の生産時間としてOUTされていたり
1枚の途中で処理を終わらせて半分ぐらいの時間で計上されていたりとする
データが幾つか点在していることが判明したのです。
　どのタイミングでその処理を行っているのかがもわからない状態で
また、稼動分析ソフトを作った所には、その旨の連絡をしているのですが原因の特定が出来かねない状態のようです。
　生産予定枚数に対して1台づつカウントされている欄があるので
その部分の数値が突然３つほど飛んでいたりしたので判明しました。

　仕事の都合によりレスポンスが悪く申し訳ありません。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

作業列を使えば 明らかに早くなりますが、だめでしょうか？
とりあえず、だめだと考えて C2セル
=IF(MATCH(A2,A:A,0)<>ROW(),INDEX(C:C,MATCH(A2,A:A,0)),MEDIAN(IF($A$2:$A$5=A2,$B$2:$B$5)))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式です({}で囲まれる)
フィルハンドル ダブルクリック

配列数式１つだったら計算速度は気になるほど遅くないはずです
作業列案推奨なので参考まで

お礼

ありがとうございます
確認してみます