エクセルで表の一番下から30個の平均を求めたい

質問

いつもお世話になっております。
先日ここで下方向に伸びていく表の最新データから上方向に30個の統計値を出す関数の組み合わせを教えてもらって各列に採用して一応目的は達成できたのですが、出来ると更に欲が出てきて非常に気になるので追加で教えていただきたく。

最新データを含む30個のデータの平均や±3σの値と比較して設定値を外れた場合はアラーム（赤太字）を出すようにしているのですが、この統計値に最新データは含まれないようにしたいのです。
最新データが異常値かどうかを判定するのに、現行だと異常値を含めた統計値と比較してアラームが出ることになるので出来れば最新入力値を含まない統計値を出したいのです。（特に今回データ数を30個にしたので統計値への影響が大きくなるので誤報の可能性が高くなってしまう事に気付きました。）

一番下まで数えて、そこから1つ上のセルから30個遡るのはこの式の変形では無理だと思うので、自分では出来ないので宜しくお願いします。

=IF(COUNT(C$6:C$1000)<30,AVERAGE(C$17:C$46),AVERAGE(OFFSET(INDIRECT("C"&COUNTA(C$17:C$1000)+16),,,-30,1)))

回答No.14

mt2015 · ベストアンサー

ANo.1です。

> しかし、実際のシートで色々試してみたのですが、データ数が30個以上の場合は正しく動いたのですが、データ数を30個以下にしてみたらデータ数が30個ではない値になってしまいます

元の式がデータが30個未満の時は存在する全データの平均を出すようになっていたので、それをベースに30個未満の時は一番下のデータを除いた平均を出しています。
＃データが25個あったら、最後の1個を除いた24個のデータの平均

実際に30個未満の時はどうしたいのでしょう？

> 再度30個以上にしても正しく計算されないのですが。
>
> 再度新しく式をコピペしたら正常に動きます。
> 最初から30個以下だと空白になります。

これはExcelの「ブックの計算」が手動になっている様に思えます。
Excelのオプションで自動にしてみてください。
自動への変更の仕方が解らなければ、ご使用のExcelのバージョンを提示してください。

お礼

本当にありがとうございました。
また、申し訳ありませんでした。

最初のご回答で解決できていました。

補足

＞実際に・・・どうしたいのでしょう？

本当～に申し訳ありません！
当方の検証ミスが重なり混乱して追加の質問（お願い）をしてしまいました。
複数のご回答で同じ結果だったので当方のミスは明らかなので色々試行錯誤して解決しましたのでこれ以上のお手数はかけないで下さい。

従来は何も考えずにただエクセルにデータを入力して少数点の打ち間違い（桁違い）等の防止が目的だったので統計値の意味も定義もなくただエクセルに入力していました。（その前はワードに直接入力で指摘されて訂正）
データが蓄積してきたので一歩改善で3σ外れ、過去最高値、最低値等の傾向異常も検出してみようと思った次第です。

実績が増えてくるとデータが数個しかない製品と数百個のデータの製品の傾向異常値では意味に大きな違いがあるのでデータ数を同じ（とりあえず30個）にしたい、と思った次第です。

やりたいことは、実績のない製品（データ数が足りない）場合は全部、30個を超えたら自身は除く、という「感じ」で質問させていただきました。
この辺の数字はご回答の数字を変えれば対応できるかと思ったので全体がいい加減な質問になってしまいました。

回答No.13

山田 太郎（@f_a_007）

【忘れていました】DLookupN()

Public Function DLookupN(ByVal strSQL As String, _
　　　　　　　　　　　　Optional intSearch As Integer = 1, _
　　　　　　　　　　　　Optional xlFileName As String = "", _
　　　　　　　　　　　　Optional isHeader As Boolean = True, _
　　　　　　　　　　　　Optional returnValue As String = "") As Variant
On Error GoTo Err_DLookupN
　　'
　　' 【要参照設定】
　　'
　　' Micrsoft ActiveX Data Objects 2.8 Library
　　'
　　Dim R　　　　　 As Integer ' 行インデックス
　　Dim N　　　　　 As Integer ' 行総数 - 1
　　Dim intCounter　As Integer '読み込みカウンター
　　Dim cnn　　　　 As ADODB.Connection
　　Dim rst　　　　 As ADODB.Recordset
　　Dim fld　　　　 As ADODB.Field
　　Dim dblValue　　As Double
　　Dim strData　　 As String

　　Set cnn = New ADODB.Connection
　　Set rst = New ADODB.Recordset
　　'
　　' ThisWorkbook.FullName の指定
　　'
　　If Not Len(xlFileName) Then
　　　　 xlFileName = ThisWorkbook.FullName
　　End If
　　'
　　' 接続設定
　　'
　　With cnn
　　　　.Provider = "Microsoft.ACE.OLEDB.12.0"
　　　　If isHeader Then
　　　　　　.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
　　　　Else
　　　　　　.Properties("Extended Properties") = "Excel 12.0;HDR=NO;IMEX=1"
　　　　End If
　　　　.Open xlFileName
　　　　'
　　　　' 列を読み込み
　　　　'
　　　　With rst
　　　　　　.Open strSQL, cnn, adOpenKeyset, adLockReadOnly
　　　　　　If Not .BOF Then
　　　　　　　　N = CInt(.RecordCount) - 1
　　　　　　　　intSearch = intSearch - 1
　　　　　　　　.MoveFirst
　　　　　　　　For R = 0 To N
　　　　　　　　　　If intSearch = R Then
　　　　　　　　　　　　strData = .Fields(0)
　　　　　　　　　　　　Exit For
　　　　　　　　　　End If
　　　　　　　　　　.MoveNext
　　　　　　　　Next R
　　　　　　End If
　　　　End With
　　End With
Exit_DLookupN:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DLookupN = strData & ""
　　Exit Function
Err_DLookupN:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DLookupN)" & Chr(13) & Chr(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr(13) & _
　　　　　　"・SQL Text=" & strSQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DLookupN
End Function

お礼

もうやめてくださいませ！

当方の対応遅れのためにワンちゃんの散歩にも影響しそうな状況。
これ以上のご回答は猫に小判、豚に真珠、馬の耳に・・です。
知っている関数の組み合わせが出来ないレベルでこのご回答が動くまでには1年はかかりそうです。

元々#No1さんのご回答で一発解決できたはずなのに当方の検証ミスで多くの無駄を発生させてしまったことを本当に申し訳なく思います。

回答No.12

山田 太郎（@f_a_007）

【DLookup():データ関数参照】

引数1=SQL文
引数2=Excel のWorkbook.FullName
　　　指定しない時は、自分
引数3=ヘッダー列有無
　　　指定しない時は、無し
引数4=該当データが無い時の戻り値
　　　指定しない時は、""

※DLookup()は、"Microsoft.ACE.OLEDB.12.0"バージョン

Public Function DLookup(ByVal strSQL As String, _
　　　　　　　　　　　　 Optional xlFileName As String = "", _
　　　　　　　　　　　　 Optional isHeader As Boolean = True, _
　　　　　　　　　　　　 Optional returnValue As String = "") As Variant
On Error GoTo Err_DLookup
　　'
　　' 【要参照設定】
　　'
　　' Micrsoft ActiveX Data Objects 2.8 Library
　　'
　　Dim DataValue
　　Dim cnn As ADODB.Connection
　　Dim rst As ADODB.Recordset

　　Set cnn = New ADODB.Connection
　　Set rst = New ADODB.Recordset
　　'
　　' ThisWorkbook.FullName の指定
　　'
　　If Not Len(xlFileName) Then
　　　　 xlFileName = ThisWorkbook.FullName
　　End If
　　'
　　' 接続
　　'
　　With cnn
　　　　.Provider = "Microsoft.ACE.OLEDB.12.0"
　　　　If isHeader Then
　　　　　　.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
　　　　Else
　　　　　　.Properties("Extended Properties") = "Excel 12.0;HDR=NO;IMEX=1"
　　　　End If
　　　　cnn.Open xlFileName
　　　　With rst
　　　　　　.Open strSQL, cnn, adOpenKeyset, adLockReadOnly
　　　　　　If Not .BOF Then
　　　　　　　　.MoveFirst
　　　　　　　　DataValue = .Fields(0) & ""
　　　　　　End If
　　　　End With
　　End With
Exit_DLookup:
On Error Resume Next
　　rst.Close
　　cnn.Close
　　Set rst = Nothing
　　Set cnn = Nothing
　　DLookup = IIf(Len(DataValue), DataValue, returnValue)
　　Exit Function
Err_DLookup:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DLookup)" & Chr$(13) & Chr$(13) & _
　　　　　 "・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　 "・SQL Text=" & strSQL, _
　　　　　 vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DLookup
End Function

【DSelect():複数行、複数列を一括参照する関数参照】

引数1=SQL文
引数2=列データの区切り子
　　　指定しない時は、”；”
引数3=列データの区切り子
　　　指定しない時は、”；”
引数4=Excel のWorkbook.FullName
　　　指定しない時は、自分
引数5=ヘッダー列有無
　　　指定しない時は、無し

Public Function DSelect(ByVal strSQL As String, _
　　　　　　　　　　　　Optional colDelimita As String = ";", _
　　　　　　　　　　　　Optional rowDelimita As String = ";", _
　　　　　　　　　　　　Optional xlFileName As String = "", _
　　　　　　　　　　　　Optional isHeader As Boolean = True) As String
On Error GoTo Err_DSelect
　　'
　　' 【要参照設定】
　　'
　　' Micrsoft ActiveX Data Objects 2.8 Library
　　'
　　Dim R　　　　　 As Integer ' 行インデックス
　　Dim N　　　　　 As Integer ' 行総数 - 1
　　Dim cnn　　　　 As ADODB.Connection
　　Dim rst　　　　 As ADODB.Recordset
　　Dim fld　　　　 As ADODB.Field
　　Dim strList　　 As String　' 全てのデータを区切子で連結して格納

　　Set cnn = New ADODB.Connection
　　Set rst = New ADODB.Recordset
　　'
　　' ThisWorkbook.FullName の指定
　　'
　　If Not Len(xlFileName) Then
　　　　 xlFileName = ThisWorkbook.FullName
　　End If
　　'
　　' 接続設定
　　'
　　With cnn
　　　　.Provider = "Microsoft.ACE.OLEDB.12.0"
　　　　If isHeader Then
　　　　　　.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
　　　　Else
　　　　　　.Properties("Extended Properties") = "Excel 12.0;HDR=NO;IMEX=1"
　　　　End If
　　　　.Open xlFileName
　　　　'
　　　　' 列を読み込み
　　　　'
　　　　With rst
　　　　　　.Open strSQL, cnn, adOpenKeyset, adLockReadOnly
　　　　　　If Not .BOF Then
　　　　　　　　N = CInt(.RecordCount) - 1
　　　　　　　　.MoveFirst
　　　　　　　　For R = 0 To N
　　　　　　　　　　For Each fld In .Fields
　　　　　　　　　　　　With fld
　　　　　　　　　　　　　　strList = strList & .Value & colDelimita
　　　　　　　　　　　　End With
　　　　　　　　　　Next fld
　　　　　　　　　　strList = Mid(strList, 1, Len(strList) - 1) & rowDelimita
　　　　　　　　　　.MoveNext
　　　　　　　　Next R
　　　　　　Else
　　　　　　　　strList = ""
　　　　　　End If
　　　　End With
　　End With
Exit_DSelect:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DSelect = IIf(Len(strList) > 0, Replace(strList & "[END]", rowDelimita & "[END]", ""), "")
　　Exit Function
Err_DSelect:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DSelect)" & Chr(13) & Chr(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr(13) & _
　　　　　　"・SQL Text=" & strSQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DSelect
End Function

　DSelect()の働きについては、添付図を参照されて下さい。[イミディエイトウインドウ]でのテストでは、”ヘッダー有り”と引数で指示しています。また、行ごとに改行するようにChr(13)で指示しています。

　この関数を利用すれば、この条件に合致する行のデータのどれとどれを参照するということも可能かと思います。

　一応、この２つの関数で用は足りると思います。なお、VBAのエディターメニューの[ツール][参照設定]で《Micrsoft ActiveX Data Objects 2.8Librar

お礼

非常に丁寧なご回答ありがとうございます。

が、申し訳ありませんが当方には手が出ませんのでご了承願います。

回答No.11

山田 太郎（@f_a_007）

【補足】主キーがあれば、検索と集計は簡単になります。

・A列が主キー列と仮定します。データ列がB列。
・SQLは、冒頭の列名か、それがない場合はF1、F2・・・Fnを用います。
・読み込むテーブルの範囲は、[Sheet3$A1:B1000]で指示します。
　この場合は、A1～A1000、B1～B1000の大きさのテーブルと認識します。

　添付図の集計値は、すべてSQL文だけで求めています。

１、B列の行数

=DLookup("SELECT SUM(F2) FROM [Sheet3$A1:B1000]",, FALSE)

２、B列の合計

=DLookup("SELECT SUM(F2) FROM [Sheet3$A1:B1000]",, FALSE)

３、総平均

=DLookup("SELECT AVG(F2) FROM [Sheet3$A1:B1000]",,FALSE)

４、A列<=3の平均のB列の平均

=DLookup("SELECT AVG(F2) FROM [Sheet3$A1:B1000] WHERE F1<=3",,FALSE)

５、A列>8の平均のB列の平均

=DLookup("SELECT AVG(F2) FROM [Sheet3$A1:B1000] WHERE F1>=8",,FALSE)

６、A列>=8でB列の値が>0のデータ数

=DLookup("SELECT COUNT(F2) FROM [Sheet3$A1:B1000] WHERE F1>=8 AND F2>0",,FALSE)

７、A列>=8でB列の合計

=DLookup("SELECT SUM(F2) FROM [Sheet3$A1:B1000] WHERE F1>=8 AND F2>0",,FALSE)

８、ID>=8 * >0 の平均

=DLookup("SELECT AVG(F2) FROM [Sheet3$A1:B1000] WHERE F1>=8 AND F2>0",,FALSE)

９、ID>=8 * <>0 の平均

=DLookup("SELECT AVG(F2) FROM [Sheet3$A1:B1000] WHERE F1>=8",,FALSE)

10、有効数４個目の主キー(F1)を求める

=DLookupN("SELECT F1 FROM [Sheet3$A1:B1000] WHERE F2>0",4,,FALSE)

※SQL文の次に4番目と指示する。

11、有効数４個目の合計

=DLookup("SELECT SUM(F2) FROM [Sheet3$A1:B1000] WHERE F1<=5",,FALSE)

※F1<=5 の5は10で求める

12、有効数４個目の平均

=DLookup("SELECT AVG(F2) FROM [Sheet3$A1:B1000] WHERE F1<=5 AND F2>0",,FALSE)

13、最後から３個目の主キーを求める

=DLookupN("SELECT F1 FROM [Sheet3$A1:B1000] WHERE F2>0 ORDER BY F1 DESC",3,,FALSE)

※昇順に読む　ORDER BY F1
※降順に読む　ORDER BY F1 DESC

14、最後から3個の合計

=DLookup("SELECT SUM(F2) FROM [Sheet3$A1:B1000] WHERE F1>=6",,FALSE)

15、最後から3個の平均

=DLookup("SELECT AVG(F2) FROM [Sheet3$A1:B1000] WHERE F1>=6 AND F2>0",,FALSE)

16、B列の有効行数

=DLookup("SELECT Count(*) FROM [Sheet3$A1:B1000] WHERE F2>0",,FALSE)*1

以上、添付図の集計は、ごくごく簡単なSQL文を書くことで求めています。

http://www.accessclub.jp/sql/22.html

SQLの集計機能は多彩で、グループ毎の集計も可能です。

※※※※※※※※※※※※※※※※※※※※※※※※※※※※
　補足：SQL文を実行する関数は４つです。
※※※※※※※※※※※※※※※※※※※※※※※※※※※※

１、DBLookup()・・・・・AccessのLookup()と同じ。
２、DBLookupN()・・・・何番目かを指定できるDBLookup()関数
３、SQLExecute()・・・SQLのUpdate文、Insert文、Delete文を実行する関数
４、GetFieldName・・・Excelのシートの列名を取得する関数

　以上４つの関数を標準モジュールに登録しておけば、式に上記のように書くだけでVBAコードは一切書く必要はありません。

　これらの関数を駆使するには、Excelのシートを２種に分けると良いと思います。

１、テーブルシート‥‥AccessのTableに相当
２、作表シート‥‥‥‥Excelの一般的なシート

　これで、テーブルシートがデータベース化できます。作表シートは、AccessのReportに相当します。ただ、SQLのUpdate文、Insert文、Delete文を実行する関数を利用するには、ユーザーフォームを作成しプログラミングする必要があります。ユーザーシートは、AccessのFormに相当します。いわば、４つの関数は、ExcelをAccess風に利用する関数ということです。

　今から、犬の散歩です。４つの関数は、帰ってから補足します。

お礼

申し訳ありません。
ワンちゃんにお詫びしておいてください。

回答No.10

bunjii

>データ数を30個以下にしてみたらデータ数が30個ではない値になってしまいます。
それで良いのでは？
最新データを除いたデータ数が1個以上無いとエラーになります。
最新データが1個のみの場合は平均値を計算対象が無いので計算できませんがデータ数が最新データを含めて2個以上あれば計算できます。
但し、別解の数式ではC31以下が空欄のときエラーになります。

>再度30個以上にしても正しく計算されないのですが。
セルの削除をすると数式に影響しますのでセルから値を消すときは「数式と値のクリア」で空欄にしてください。

>最初は1個のデータから始まるので、もし必要なら式の最初にIF（データ数が30個以下の場合は17行目から30個を計算する）でもよいのですが。
データ数が30個以下のときは最新データも含めて30個まで（空欄も含む）の平均値を計算するのですか？（質問の一部を変更する？）

>表により色んなケースが想定されるので今少しご指導をお願いします。
具体的な模擬データ（実際の値の数で値はアレンジする）を数例提示してください。
文字だけで分かり難い説明をされてもあなたが求めている内容が読み取れません。

補足

度々申し訳ありません。

昨夜アレコレやってみて、やはり（当然ですが）ミスであることが分かりました。
原因を記載するのも恥ずかしいようなウッカリがダブったりしていたために訳が分からなくなってしまいました。
よって
＞しかし再度新しく式をコピペしたら正常に動きます。
＞当方の無知ゆえの不具合だと思いますが・・・（これは当たっていました！？)

おまけに、データ数が少ない場合（５個以下とか）は傾向管理ではなく全部の実績を含めたい（ご指摘の通り質問と違う内容）というような無意識の思いもあり「結果が合わない」などとお騒がせしてしまいました。

本当にお騒がせしてすみませんでした。

回答No.9

chayamati

こんにちは
あなたの式を元に定数を替え、条件式もとってみました
=AVERAGE(OFFSET(INDIRECT("C"&COUNTA(C$17:C$1000)+15),,,-30,1))

お礼

ありがとうございます。

参考になりました。

回答No.8

山田 太郎（@f_a_007）

【訂正の訂正】面目ない！

補足

何度もありがとうございます。

当方には主キーという発想が無く、また使い方も全く知らないのですが、これは上から順にナンバリングした列があれば、例えば今回の場合も簡単に出来る、ということでしょうか？

もしそうならその具体的な方法、式を教えていただければ幸いです。
表によっては当方が作成する表もあり、A列に数字を埋めることは簡単に出来ますので。

他の人が作った表だとリンクや参照が有るので出来ない場合も多いのですが。

回答No.7

山田 太郎（@f_a_007）

【訂正】添付図の集計項目の見出しに誤りがありました。

訂正後を示しておきます。

お礼

ご丁寧な対応に感謝いたします。
当方の対応が遅れたために多くのお手数をおかけすることになり申し訳ありませんでした。

当方にはなじみのない発想故検証はPendingとさせていただきます。

回答No.6

山田 太郎（@f_a_007）

【横から一言だけ】《主キー》という考えはないのかな？

　当方、Excelは一度も操作したこともないし、同VBAも未経験者。で、無謀にも出された課題に挑戦した訳だが・・・。で、質疑応答も読むにつれて、「ウーン。Excel のテーブルでは《主キー》という考えはないのかな？？？」て疑問を抱いた。主キー列さえあれば、大抵の集計はできるのではないかと思った次第です。まあ、「フーン。そういう考えもあるのか・・・」程度で添付図を一瞥してもらえれば幸いである。

お礼

ご丁寧な対応に感謝いたします。
当方の対応が遅れたために多くのお手数をおかけすることになり申し訳ありませんでした。

回答No.5

HohoPapa

・集計対象が30以下でも耐える
・途中に空白セルが含まれても耐える
・平均値に加え、中間値も求めたい
・集計対象数を減らした集計もしてみたい
といったことまで考慮するのであれば

最下行の１つ上から上方向に全数抽出し
抽出結果を任意に集計するアイディアはいかがでしょうか。
ちょっと毛色が皆さんと異なるので違和感があるかもしれません。
それでもよかったら試してみてください。

Ｂ列：作業列です。
B3=0
B4=IF(C4<>"",INT(B3)+1,INT(B3)+0.0001)
以下、必要数下方向に複写

Ｃ列：データ列です。
4行目以下、任意行からデータが埋まることを想定しています。

Ｇ列：作業列です。
G2=INT(MAX(B:B))-1
G3=G2-1
以下、必要数下方向に複写

Ｈ列：抽出結果を書き出す列です。
H2セルから下方向に抽出結果が並びます。
H2=IF(G2>0,VLOOKUP(G2,B:C,2,FALSE),"")
以下、必要数下方向に複写

J2セル：平均値を計算したセルです。
J2=SUM(H2:H6)/COUNT(H2:H6)
or
J2=AVERAGE(H2:H6)
5件の平均を求めています。

お礼

ご丁寧な回答ありがとうございます。

ご回答の汎用性は非常にありがたいのですが残念ながら当方のレベルでは単機能でないと結局宝の持ち腐れになりかねないのと、今回に限っては作業列の確保が難しいので参考にさせていただく程度にさせていただきます。

作業列に関しては質問に記載しておらず申し訳ありません。
空白セルや文字のセルの問題は別途考慮することになるかもしれません。

今後も欲が出て一旦は「出来た！」と思ったことも、今回のように条件を追加したくなることもありますのでその節には（も）よろしくお願い致します。

回答No.4

bunjii

>一番下まで数えて、そこから1つ上のセルから30個遡るのはこの式の変形では無理だと思うので、自分では出来ないので宜しくお願いします。
「C17以下にブランク無しで数値のみ順次入力する」と言う条件ですよね？
提示のIF関数は不要になり、OFFSET関数の高さに-30とCOUNT関数を使いMAX関数でデータ数の調整をします。
次の数式を試してください。
=AVERAGE(OFFSET(INDIRECT("C"&COUNT(C$17:C$1000)+15),,,MAX(-30,1-COUNT(C$17:C$1000)),1))
別解（但し、C1:C16には数値が入力されていないこと）
=AVERAGE(OFFSET(C$1,MAX(INDEX(ISNUMBER(C$1:C$1000)*ROW(C$1:C$1000),0),1-COUNT(C$17:C$1000))-2,0,-30,1))

補足

いつもお世話になっております。
上のご回答の式をコピペしてみたら、一発で正常に動いたのですが＃No1さんの現象（下記）が発生します。
同じ操作をすると、No1さんのご回答と同じ結果になるようなので当方の無知ゆえの不具合ですが、何故でしょう？
随時この式に変更したいのでお手数ですが今少しご指導お願いします。

早速コピペで試してみたところ1発で動きました。
データはご推測の通り17行目からです。
しかし、実際のシートで色々試してみたのですが、データ数が30個以上の場合は正しく動いたのですが、データ数を30個以下にしてみたらデータ数が30個ではない値になってしまいます。

再度30個以上にしても正しく計算されないのですが。

しかし再度新しく式をコピペしたら正常に動きます。
最初から30個以下だと空白になります。（⇒No1さんのご回答の場合）

この式を適用したい表、列は一杯あり、列の上の方（例えば16行まで）に色んな情報があり、最初は1個のデータから始まるので、もし必要なら式の最初にIF（データ数が30個以下の場合は17行目から30個を計算する）でもよいのですが。

当方の無知ゆえの不具合だと思いますが、表により色んなケースが想定されるので今少しご指導をお願いします。