こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセルで表の一番下から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)))

投稿日時 - 2019-02-15 11:56:49

QNo.9588044

困ってます

質問者が選んだベストアンサー

ANo.1です。

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

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

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


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

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

投稿日時 - 2019-02-20 01:15:33

補足

>実際に・・・どうしたいのでしょう?

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


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

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

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

投稿日時 - 2019-02-20 11:55:03

お礼

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

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

投稿日時 - 2019-02-20 12:40:29

ANo.14

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(14)

ANo.13

【忘れていました】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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2019-02-19 20:20:16

お礼

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

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


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

投稿日時 - 2019-02-20 12:38:44

ANo.12

【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)で指示しています。

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

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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2019-02-19 19:26:01

お礼

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

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

投稿日時 - 2019-02-20 12:42:02

ANo.11

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

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

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

1、B列の行数

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

2、B列の合計

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

3、総平均

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

11、有効数4個目の合計

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

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

12、有効数4個目の平均

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

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

=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文を実行する関数は4つです。
※※※※※※※※※※※※※※※※※※※※※※※※※※※※

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

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

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

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

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

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

投稿日時 - 2019-02-19 17:37:45

お礼

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

投稿日時 - 2019-02-20 12:43:08

ANo.10

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

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

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

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

投稿日時 - 2019-02-18 15:18:22

補足

度々申し訳ありません。

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

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

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

投稿日時 - 2019-02-19 10:59:08

ANo.9

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

投稿日時 - 2019-02-17 13:04:41

お礼

ありがとうございます。

参考になりました。

投稿日時 - 2019-02-18 12:02:03

ANo.8

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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2019-02-16 19:36:54

補足

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

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

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

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

投稿日時 - 2019-02-19 11:35:13

ANo.7

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

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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2019-02-16 19:24:25

お礼

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

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

投稿日時 - 2019-02-18 12:00:52

ANo.6

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

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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2019-02-16 18:11:21

お礼

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

投稿日時 - 2019-02-18 11:59:20

ANo.5

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

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

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

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

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

H列:抽出結果を書き出す列です。
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件の平均を求めています。

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2019-02-16 10:35:53

お礼

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

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

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

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

投稿日時 - 2019-02-19 11:51:17

ANo.4

>一番下まで数えて、そこから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))

投稿日時 - 2019-02-15 21:31:01

補足

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


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

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

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

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

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

投稿日時 - 2019-02-18 11:55:11

ANo.3

【補足」少し、計算に誤りがありました。

10-5=5・・・・5,6,7,8,9,10
10-4=6・・・・6,7,8,9,10

このミスは、まあ、見逃してください。

投稿日時 - 2019-02-15 14:15:41

ANo.2

Q、A列の総平均値、トップ5、ラスト5の平均値を取得するのは?
A、以下のSQL文でも求めることが出来ます。

C1の式:=DLookup("SELECT Count(*) FROM [Sheet3$A1:A1000]",,FALSE)
C3の式:=DLookup("SELECT AVG(F1) FROM [Sheet3$A1:A1000]",,FALSE)
C4の式:=DLookup("SELECT AVG(F1) FROM [Sheet3$A" & D1-5 & ":A1000]",,FALSE)
C5の式:=DLookup("SELECT AVG(F1) FROM [Sheet3$A1:A" & D1-5 & "]",,FALSE)

>エクセルで表の一番下から30個の平均値
 ↓
>エクセルで表の一番下から05個の平均値←C4の式

と、SQL文ですと割と簡単に求められるかと思います。こういうやり方でよければ、DLooolup()をご紹介します。

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2019-02-15 14:08:37

お礼

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

当方には全くなじみのない発想で式が全く理解できないので、申し訳ありませんが検証はPending とさせていただきます。

投稿日時 - 2019-02-18 11:57:30

ANo.1

データはC6から下、C17から下 どちらですか?
恐らくC17から下だと思いますが。

元の式を活かしつつ修正するとしたらこんな感じでしょうか
=IFERROR(AVERAGE(OFFSET(INDIRECT("C"&COUNTA(C$17:C$1000)+15),,,-1*MIN(COUNTA(C$17:C$1000)-1,30),1)),"")

もし、C6以下の数値をカウントする事に意味が有るようでしたらこの回答は無視してください。

投稿日時 - 2019-02-15 13:55:13

補足

早々のご回答ありがとうございます。

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

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

再度新しく式をコピペしたら正常に動きます。
最初から30個以下だと空白になります。

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

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

投稿日時 - 2019-02-18 11:50:04

あなたにオススメの質問