Excelにて条件(ワイルドカード)付きの標準偏差を求めたいです

Excelにて、条件付き(ワイルドカード入り文字列)の標準偏差を求めたいです。

画像のようなデータを毎日１行ずつ追加更新しており、
毎月末にアルファベットごと(A0やB0ごと)の数値を別シートにまとめています。

平均・最大・最小・標準偏差(σ)が必要で、
AVERAGEIF、MAXIFS、MINIFSを使い管理していますが
σだけが思うようにいきません。

現在は =STDEVPA(Seet1!A15:B16,Seet1!A23:B30,…)
というようにマウスで範囲指定しておりますが、数が膨大で毎月大変な思いをしています。

以下試したものです

STDEVPA(IF(COUNTIF(Seet1!A1:B11,"*A0*")>0,〇〇〇))
→〇部分が分からない

=VLOOKUP("*"&"A0"&"*",Seet1!A1:J11,3)
→＃N/A

STDEVPA(IF(COUNTIF(Seet1!A1:B11,"*A0*")>0,OFFSET(MATCH("*A0*",Seet1!A1:B11,0),0,3,0,0),))
→数式として成り立たない


excelを全然理解出来ておらず、今使用している関数も検索して使ってみて改造して…というものがほとんどです。
関数そのものの意味を勉強中の為、意味不明な説明がありましたら申し訳ありません。
やろうとしていることが伝われば幸いです。

毎日のデータを入力すると同時の自動更新が理想です。
マクロやVBA等を使わずに関数や設定のみで上記のようにすることは可能でしょうか。
(実際のデータ欄も画像と同じようにセル統合されているため列数がずれます)

よろしくお願いいたします。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2020/09/16 10:59

こんにちは

エクセルの結合セルの値は、結合範囲の左上のセルにありますので、そちらの値を利用すれば十分で、計算の対象範囲をいたずらに広げる必要はありません。

＞現在は =STDEVPA(Seet1!A15:B16,Seet1!A23:B30,…)
＞というようにマウスで範囲指定しておりますが、
A（B）列は検索の対象であって、偏差を求める数値ではないと思いますが、肝心の「どの値の偏差を求めた」いのかがさっぱりわかりません。

＞以下試したものです
ダメな数式ということもありますが、残念ながら、こちらでも何をどうしたいのか全くわかりません。

・・・ということで、よくわからないけれど、検索して該当外を省けば良さそうなので、STDEVPはエラー値やFALSEを無視してくれるみたいなので、例えば、「A0」を含む行のＣ列の値の偏差を求めたいものとすれば、
　=STDEVP(INDEX(IF(FIND("A0",A1:A999),C1:C999),,))
で、求められると思います。

※ 「A0」の2文字目がゼロかオーか区別しにくいのですが、上式ではゼロで入力してあります。
※ 内容をきちんと把握できていないので、外していたらスルーしてください。

この回答へのお礼

こんにちは、さっそくのご回答をありがとうございます。
言葉足りずで申し訳ありませんでした、

＞現在は =STDEVPA(Seet1!A15:B16,Seet1!A23:B30,…)
＞というようにマウスで範囲指定しております

については数式の中でこれを検索値として使っているわけではなく、直接入力といいますか…通常の「STDEVPA(範囲)」としてセルに入力し、範囲をマウス(ドラッグ)で指定している、という意味でした。

教えていただいたもので試したところ、エラーもなくしっかりと偏差の数値を表示してくれました。本当にありがとうございます。

やりたいことが上手く伝えられなかったにもかかわらず、理想通りの表示で大変嬉しく思っております。

ただひとつだけ…「STDEVPA(範囲)」 と直接入力して計算された値と、「STDEVP(INDEX(IF(FIND("A0",A1:A999),C1:C999),,)) 」で計算された値が微妙に異なっているのが気になります。

業務に影響するような大きな差ではないのですが、こちらについてはもう少し調べてやってみようと思います。

早速こちらの数式を適用させていこうと思います。本当にありがとうございました。

お礼日時：2020/09/16 12:35

No.2 回答者： fujillin 回答日時： 2020/09/16 13:11

No1です

＞数式の中でこれを検索値として使っているわけではなく、直接入力といいますか…通常の「STDEVPA(範囲)」
＞としてセルに入力し、範囲をマウス(ドラッグ)で指定している、という意味でした。
範囲をＡ列にしている式なので、何を計算したいのかわからないという意味です。
（数値の値のある範囲でないと意味がない）

＞STDEVPA(範囲)」 と「STDEVP(INDEX(IF(FIND("A0",A1:A999),C1:C999),,)) 」で計算された値が
＞微妙に異なっているのが気になります。
使用している関数の違いと思います。
https://docs.microsoft.com/ja-jp/office/troubles …

この回答へのお礼

大変失礼致しました。また追加のご回答ありがとうございます。

A列を目視で確認し、同じ行にある数値をマウスで選択していた、ですね…

A列には文字列しかないからそこを選択しても数字は出ないよ、というですよね。
重ねての勘違い誠に申し訳ありません。

STDEVPとSTDEVPAでは母集団をどう見るかによって変わってくるのですね。これもしっかりと理解せずなんとなくで使用しておりましたので、適した方を使っていこうと思います。

本当にありがとうございました。

お礼日時：2020/09/16 14:50