質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

91.33%

  • Excel

    970questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

  • Windows 7

    288questions

    Microsoft Windows 7は過去にリリースされたMicrosoft WindowsのOSであり、Windows8の1代前です。2009年の7月にリリースされ販売されました。Windows7の前はWindowsVistaで、その更に3年前にリリースされました。

AVERAGEIFS関数で抽出がうまくできない

解決済

回答 1

投稿 2017/12/08 12:30

  • 評価
  • クリップ 1
  • VIEW 39

vbabeginner

score 7

前提・実現したいこと

発注したステッカーの価格検討に際し、
検索条件1「ステッカーを使うエリア別(銀座・原宿・その他の3地域)」に分けた上で
検索条件2「ステッカーサイズ別(XS-XLの5種)」に分けて
検索条件3「ステッカー加工のあり・なし」の場合で、
ステッカー単価平均をエクセルで算出して比較分析したいです。

発生している問題

算出されたステッカー単価の表と、
元となる台帳(データベース)にフィルタをかけてみたものとが一致していないことに気づきました。
関数計算式が間違っているようです。
例えば、算出された表には、”加工あり・XSのステッカー単価”は「#VALUE!」と表示されていますが、データベースをフィルタかけると1件加工ありのものが出てきます。

試したこと

台帳は下記のような一覧です。

B C D E F G H I J K L M N
エリア ステッカー枚数 ステッカー単価 合計金額   ㎡数/枚あたり ㎡単価(G列÷J列) J列*E列 サイズ種類(XS-XL) 加工(あり・なし)
銀座 1 125,000 125,000 18 ¥6,996 18 L 加工なし|    

台帳の上の行に入れたステッカー単価算出表は、次のような表示です。

B C D E F G H
サイズ別㎡単価平均(加工含) XS S M L XL
原宿 #DIV/0! ¥12,979 #DIV/0! #DIV/0! #DIV/0!
銀座 #DIV/0! ¥12,015 #DIV/0! #DIV/0! #DIV/0!
その他     #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

例えば、算出表「その他・XS」にいれた関数式は下記です。
=AVERAGEIFS($K$15:$K781,$B$15:$B781, "<>*原宿*AND*銀座*",$M$15:$M781,"*XS*",$N$15:$N780, "*加工あり*")
「#VALUE!」と表には算出されたので、該当するものがない(加工ありのものがない)と思ったのですが、
確認のため台帳のN列のフィルタをかけると、1件該当するものがあります。

補足情報(言語/FW/ツール等のバージョンなど)

Excel2016を使っています。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

checkベストアンサー

+1

AVERAGEIFS 関数 - Office サポート
すべての検索条件を満たすセルがない場合は、エラー値 #DIV0! が返されます。

関数のヘルプを見てもらえばわかりますが、#VALUE!は「該当するものがない(加工ありのものがない)」を表すエラーではありません。

Excelでエラー表示を見てもらうと数式で使用されるデータの形式が正しくありません。となっており、関数の引数が間違っていることになります。

質問にある関数式を見ると、ほとんどの条件範囲は781行目まで見ていますが、最後の条件範囲が$N$15:$N780、780行目までとなっています。
AVERAGEIFSはすべての条件範囲の大きさが一致する必要があるため、これがエラーの原因となります。

AVERAGEIF 関数の範囲引数および検索条件引数とは異なり、AVERAGEIFS では、各検索条件範囲に含まれるセルの個数と形状は、合計対象範囲と必ず一致している必要があります。

 蛇足

恐らくこうなった原因として関数式の参照が、絶対参照・相対参照入り交じっているせいがあるかと思います。

可能であれば台帳をテーブルに変換することで構造化参照が使えるようになるため、より堅牢に参照範囲を設定することが出来るようになります。

投稿 2017/12/08 18:20

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/12/11 11:07

    早速のご回答ありがとうございます!
    理解が遅く、お礼が遅くなり大変恐縮です。

    エラー表示の意味、そして表とテーブルの違いを知ることができました!

    とてもクリアに理解できました。
    わかりやすいご解説誠にありがとうございます!!

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

ただいまの回答率

91.33%

関連した質問

同じタグがついた質問を見る

  • Excel

    970questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

  • Windows 7

    288questions

    Microsoft Windows 7は過去にリリースされたMicrosoft WindowsのOSであり、Windows8の1代前です。2009年の7月にリリースされ販売されました。Windows7の前はWindowsVistaで、その更に3年前にリリースされました。