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ベストアンサー
- 回答日時:
こんにちは。
とりあえず、以下のようなものを考えてみました(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さん。
ご提示のあった表で、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以下にコピーする)数式が求めていたものです。
私の最終的な目標は、商品を複数回検査したことによる期間比較をしてその傾向を調べることでした。
有り難うございました。
cafe_au_laitさん。早速の答え大変ありがとうございました。
確かに、ご提示いただきました数式で、求めていた数値が出てきます!! 有り難うございます。
可能ならば、D,E,F…の列がD列1つにまとまればなお良いのですが。というのも、商品(a,b,c,..)の数を3000くらいと想定していますので。--このD列1つにまとまられるかどうかの点は、自分で試してみます。もしできるようなら、「補足」に記したいと思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで80万行、50列位のデ...
-
ゼロを表示
-
Excelの計算が合いません。 諸...
-
Excelの警告について
-
今まで文字化けなく開けていたc...
-
EXCELの散布図で日付が1900年に...
-
エクセルでファイルの最終更新...
-
エクセルの数式バーのフォント...
-
作成した数式を値として表示し...
-
ExcelでASCを使って全角を半角...
-
Excelセルを跨いで合計を出す方法
-
Excel 大小比較演算子による「...
-
Excel関数について教えてくださ...
-
マクロの処理が遅くなった
-
エクセルで入力すると隣のセル...
-
Excel関数について教えてくださ...
-
エクセルの文字が途中から消える
-
エクセルを使用して、円周率を...
-
エクセルのセル内に分数などの...
-
条件付き書式設定で罫線を引き...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報