多数の商品の複数に亘る検査結果の平均を求めるには？

質問

MS-EXCELを使い、個別かつ多数の〔3000位の〕商品につき、日付の異なる複数回にわたり、商品の品質を検査してランク付けをし、2回目以降の商品検査において、次の通り、品質ランクの平均値（a2, 3,..,b2,b3,..,c2,c3,..,aa2,aa3,..,ij2,ij3,..）を計算したいと思います。

例えば、Aという商品の2回目、3回目、...の各平均値 a2, a3,..
Bという商品の2回目、3回目、...の各平均値 b2, b3,..
・・・・・・・
AAという商品の2回目、3回目、..の各平均値 aa2, aa3,..
・・・・・・・
IJという商品の2回目、3回目、..の各平均値 ij2, ij3,..
・・・・・・・
を自動的に計算したいのです。

この場合、フィールド（名）を商品の検査日、商品、ランクの3つに絞ることにします。
MS-EXCELのDSUM関数およびDAVERAGE関数では、その書式でCriteriaとして複数の条件設定も可能なようにフィールドとレコードを範囲指定するようになっています。その場合、フィールド名として検査日、商品が、
その直ぐ下の行にあたる各レコードには、具体的な検査日（例えば「＜検査日」）と商品名（上記の例ではA, B,..AA, IJ...のいずれか）を設定することが求められます。
しかし、商品が多数にわたる場合、このCriteriaを商品毎に一々設けたり、あるいは自動的に設定することは困難です。
この（検査日と商品という）複数の条件をつけた上、各レコードの右に設けた一つのセルの中で特定の日付以前の各商品の2回目、3回目、..という複数にわたる検査のランク平均値を計算したいのですが、どのような関数と計算式を利用すればよいのでしょうか？

回答No.1

cafe_au_lait · ベストアンサー

こんにちは。

とりあえず、以下のようなものを考えてみました（A～C列および1行目がデータ入力部分、D2以降が計算結果）。

　　　　A 　　　　B　　　C　　　D E F ・・・

1　　検査日　商品　ランク　| a b c
2　　　4/1　　　a　　　　5　　| - - -
3　　　4/2　　　b　　　　2　　| - - -
4　　　4/3　　　c　　　　3　　| - - -
5　　　4/3　　　a　　　　5　　| 5 - -
6　　　4/4　　　c　　　　1　　| 5 - 1
7　　　4/6　　　a　　　　3　　| 4 - 1
8　　　4/9　　　a　　　　1　　| 3 - 1
：
：
※検査日順に並べ替えておく必要があります。

D2のセルに以下の数式を入力します。

=(SUMIF($B$2:$B2,D$1,$C$2:$C2)
-VLOOKUP(D$1,$B$2:$C2,2,FALSE))
/(COUNTIF($B$2:$B2,D$1)-1)

これを縦横にコピーします。

まずSUMIF関数で、そのセル（の検査日）までの、1行目に入力した商品名のランクの合計を求めます。次にVLOOKUP関数で、その商品名の1回目のランクを求めます。最後にCOUNTIF関数で、そのセルまでの検査回数を求めます。あとは、合計から1回目を引いたものを検査回数-1で割れば、そのセルまでの2回目以降のランクの平均値が求められます。

ただしこの数式は、検査回数0回のときエラー値#N/Aを、1回のときエラー値#DIV/0!を返します。

一応目的は達成できたとは思いますが・・・。勘違いしている、うまくいかない、もっとこうしてほしい等ありましたら補足してください。

お礼

cafe_au_laitさん。早速の答え大変ありがとうございました。
確かに、ご提示いただきました数式で、求めていた数値が出てきます!!　有り難うございます。

可能ならば、D,E,F…の列がD列1つにまとまればなお良いのですが。というのも、商品（a,b,c,..）の数を3000くらいと想定していますので。－－このD列1つにまとまられるかどうかの点は、自分で試してみます。もしできるようなら、「補足」に記したいと思います。

補足

cafe_au_laitさん。

ご提示のあった表で、D2に

=IF(COUNTIF($B$2:$B2,B2)=1,"",(SUMIF($B$2:$B2,B2,$C$2:$C2)-VLOOKUP(B2,$B$2:$C2,2,FALSE))/(COUNTIF($B$2:$B2,B2)-1))

とする（そしてこれをD3以下にコピーする）数式が求めていたものです。

私の最終的な目標は、商品を複数回検査したことによる期間比較をしてその傾向を調べることでした。

有り難うございました。