Excel2003で式の入ったセルのエラー表示

質問

｛=IF(COUNTIF($L2:$V2,"<=100")=0,"",AVERAGE(IF(($L2:$V2<=100)*($L2:$V2<>""),$L2:$V2,"")))｝
上記の入ったセルが、AD列に縦に並んでおり、条件に合ったセルには数値が出てますが、すべてのAD列のセルに、左上に緑のエラー表示、右上に赤マークが出ており、このエラーに関するヘルプを開いても白紙のダイアログが表示されるだけです。
数値の出てるものに0.15を掛けたものを結果として出したいのですが、上記の式のどの部分に0.15を掛けるのか分かりません。数値の出てるセルの隣のセルに＝0.15*セル番号としてもそのまま数式が表示されるだけです。元データのL列からV列までのセルエラーは数値データに変更して解決済みです。
どうぞ宜しくご教示下さい。

回答No.6

Wendy02 · ベストアンサー

こんばんは。

=IF(COUNTIF($L2:$V2,"<=100")=0,"",AVERAGE(IF(($L2:$V2<=100)*($L2:$V2<>""),$L2:$V2,"")))

[配列数式]自体には間違いないです。

#3 さんと考え方は同じですが、別の方法ではこうします。#5さんとは考え方は違います。

=IF(COUNTIF($L2:$V2,"<=100")=0,"",AVERAGE(IF(($L2:$V2<=100)*($L2:$V2<>""),0.15*($L2:$V2),"")))

>数値の出てるセルの隣のセルに＝0.15*セル番号としてもそのまま数式が表示されるだけです。

数式の入っているセルは#3さんのご指摘のように、書式が文字列になっているはずですが、他に、参照されているセルの中に、書式で文字列設定されたところがある可能性があります。

そうすると、数式を確定したら、対象セルの書式は、コピーされて数式のセルも文字列になります。これは、バグのようなものです。もし、数字を文字列にしたい場合などは、「'(アポストロフィ--文字列書式)」を代用したりします。

セルの左上の緑の三角

オプション--エラーチェック--

文字列として保存されている数値
または、
空白セルを参照する数式
のどれか。
エラーチェックのオプションは、嫌でしたら、全部、オフにすればよいです。

右上の赤の三角は、コメント

>AE列でエラー表示が出てたセルの計算では＃VALUE!と出てしまいます

これは、AEに数式があるでしょうから、その中が分からなければ、エラーは消えません。おそらく、AD列の数式の戻り値の""(長さ0の文字列)の参照をしてエラーが発生するか、と思います。=If(AD2="","",数式)のようなスタイルにします。

>コメントの削除で消えましたが、列全体を一括でコメント削除するにはどうしたら良いのでしょうか？

範囲を選択して、右クリック、コメントの削除

お礼

有難う御座いました。
教えていただいた式で、無事解決致しました。
他の方のアドバイスで、0.15を乗じますと空白セルのところでは、＃VALUE！エラーが出てしまいますが、これはデータのないものを演算要素にした為でしょうか？0表示させるように式を変更すれば解決出来るのでしょうか？
＞これは、AEに数式があるでしょうから、その中が分からなければ、エラーは消えません。おそらく、AD列の数式の戻り値の""(長さ0の文字列)の参照をしてエラーが発生するか、と思います。=If(AD2="","",数式)のようなスタイルにします。
この事が問題なのでしょうか？IF関数を検索した際、””が１つの場合、２つの場合がそれぞれ例示されてましたが、良く理解出来ませんでした。２つにする事により長さ0参照エラーが解消されるのでしょうか？

回答No.9

KURUMITO

答えに0.15をかけるのでしたら式を次のように入力します。
=IF(COUNTIF($L2:$V2,"<=100")=0,"",AVERAGE(IF(($L2:$V2<=100)*($L2:$V2<>""),$L2:$V2,"")))*0.15
Ctrl+Shiftキーを押しながらEnterキーを押して確定します。
ところで緑とか赤とかが出るとのことですがおそらくはデータに文字列などの数字が含まれているのではないでしょうか。一旦文字列として入力してしまったものは表示形式などで標準や数値に変えても変更できません。入力のやり直しになりますがそれも容易ではありません。それを解消するためにはどこか全く別のセルに1を入力してそれをコピーします。その後にＬ列からＶ列にある数値の入っているセル領域を選択し、右クリックをして「形式を選択して貼り付け」で[乗算]にチェックをしてＯＫします。このような操作ですべての数字を数値のとして扱うことができるようになります。
一度このような操作をぜひ行ってください。

お礼

有難う御座いました。
式はwendy様に教えて頂いた事で解決致しました。
１、形式選択、乗算はハイパーリンク一括解除でも同様の方法が紹介されていました。成る程こんな方法もあるのかと感心した覚えがあります。
今回は元データのせいでは無かったようです。
お世話になりました。

回答No.8

imogasi

これは配列数式ですか。質問にはこの言葉が、何も出てこないところを見ると、ひょっとすると、他のEXCELに詳しい方が作って、質問者はそういうことを知らないのかな。
ーー
AVERAGEの中でもIFは使えます。
例データ
A2:B8
区分　　計数
1 1
2 2
3 3
2 4
1 5
3 6
2 6
例えば区分２の平均を出すには
=AVERAGE(IF(A2:A8=2,B2:B8,""))
と入れて、Shift、CTRL、Enterの３つのキーを同時押しする。
配列数式。
結果
４
Ifにするのは=AVERAGE((A2:A8=2)*(B2:B8))　　にすると平均が
=12/7になってしまうので、２以外の場合は空白にして、平均を計算させると、=12/3になってくれます。
ーーー
＞のどの部分に0.15を掛けるのか
また結果に０．１５したい場合は｝の直前に＊0.15をして置けばよいと思う。
上記の例で２倍する場合の例
=AVERAGE(IF(A2:A8=2,B2:B8,""))*2　と入れて、SHIFT+CTRL+ENTERキー同時押しで
８になります。
ーーー
さてこの質問の場合何をしているか？
COUNTIF($L2:$V2,"<=100")=0,"",AVERAGE・・
はL2:V2セルで１００以下の件数がないなら、空白、あれば平均を求めている。Average以下の式は、私の上例と同じく
L2:V2のセルで１００以下で、かつ（＊がそれを意味する）
L2:v2で空白でないセルは、そのL2:V2のセルを採り上げそれらの平均を求めている。１００以上（当然空白でない）で在れば、空白のセルと看做して平均を出す。上記で言ったように空白のセルは平均算出上
はデータが無いものとしてエクセルは扱う。
配列数式は条件に合うものを縦に合計とか平均を「出すとかするのですが、１行でもEXCELの考える条件に合わないものがあると、配列数式の結果そのものがエラーになります。
この配列数式の書き方にエラーは無いのではないかと思います。
あとは、データの実情との絡みです。L2:V2のデータ（絶対番地で固定しているので、＞AD列に縦に並んでおりは、各々式の番地が違うのでは？）はどうなってますか。
＝＝
上記を参考にして、正しい結果が出ているのか、ただ＞左上に緑のエラー表示、右上に赤マークが出ており、だけなのか、補足してください。
L2:V2に文字列やスペースのセルは無いでしょうね。

お礼

有難う御座いました。
配列数式は分かりましたが、式自体は別質問で教えてもらったものです。
今回は皆さんのお陰で無事解決致しました。
お世話になりました。

回答No.7

a987654

Ｎｏ４です。
=IF(COUNTIF(L2:V2,"<=100")=0,"",SUMIF(L2:V2,"<=100")/COUNTIF(L2:V2,"<=100"))
これで結果は出ます.
countif、sumifでは空白はカウントしませんので空白セルに対応
する必要はありません。
０．１５を掛けるのも何も問題ありません。

余計なお世話かもしれませんが、
AVERAGE(IF(($L2:$V2<=100)*($L2:$V2<>""),$L2:$V2,""))
の数式にこだわるのならそれも宜しいかと思いますがより分かりやすく
簡単な数式の方がベターと思います。

回答No.5

okormazd

まず、この数式は配列数式なので、
=IF(COUNTIF($L2:$V2,"<=100")=0,"",AVERAGE(IF(($L2:$V2<=100)*($L2:$V2<>""),$L2:$V2,"")))*0.15
を入力したときか、入力済みの場合は数式バーをクリックしてから、Shft+Ctrl+Enterで確定することはご存知ですよね。
数式自体にエラーはないと思います。データに少なくとも１つ以上の数値データがあれば計算されるでしょう。
「左上に緑のエラー表示」
緑三角の隣の情報ボタンをクリックしてエラーチェックオプションを見て判断し、特に問題なければ、エラーを無視するをチェックするか、エラーになる項目のチェックをはずして、緑三角を表示しないようにします。
「右上に赤マーク」
赤三角なら、コメントなのて、コメントを削除すればいいのですが、「赤マーク」というのはどんなマークなのでしょう。

補足

有難う御座いました。
緑エラーはエラー無視で消えますが、次のAE列でエラー表示が出てたセルの計算では＃VALUE!と出てしまいます。
赤三角です。コメントの削除で消えましたが、列全体を一括でコメント削除するにはどうしたら良いのでしょうか？
エラーチェックオプションはもう少し調べてみます。
唯、別表でエラー表示なしで結果が出てるので、基データも含めて検討します。

回答No.4

a987654

なにをやろうとしている数式なのかわかりませんが
AVERAGE(IF(　の後の条件式　($L2:$V2<=100)*($L2:$V2<>"")
これが、#VALUE!になっています。
この部分の数式自体を再確認してください。
（済みません私にはどう直すべきかは判りません）

作業列を使わず数式を駆使して１つの数式で結果を出すのも
ときによりけりです。
このように何が原因かわからなくなるような計算式の場合は
作業列を使い数式を簡単にしてデバッグを楽にすることも考慮
すべきかとおもいます。

補足

入ってる数値の100以下のものだけの平均を求めたく、他の質問で教えてもらった結果です。又最終的に作業列を使わずに数式を作成すべく、色々教えてもらってるところです。

回答No.3

kozimaya

=IF(COUNTIF($L2:$V2,"<=100")=0,"",0.15*AVERAGE(IF(($L2:$V2<=100)*($L2:$V2<>""),$L2:$V2,"")))

>＝0.15*セル番号としてもそのまま数式が表示されるだけ
セルの書式設定が「文字列」になっていませんか。
標準に直して、一度、セルをアクティブにし、式を確定し直してみてください。

>右上に赤マーク
コメントが設定されていないでしょうか。

補足

有難う御座いました。
文字列になってました。一度確かめたと思ってたのですが見落としてました。数値に変更して、AD列に数値が表示されてるところは、正しく計算されてますが、空白セルの隣では＃VALUE!と表示されてます。
AD列のエラーのプルダウン表示では　1.数式を更新してセルを含める　2.このエラーに関するヘルプ　3.エラーを無視する　4.数式バーで編集
5.エラーチェクオプション　6.[ワークシート分析］ツールバーの表示
となってます。
又、赤印の部分をポイントすると、私の名前が左上に表示されてるウィンドウが開きます。
AE列の＃VALUE!ではプルダウンでAD列のそれと、1が（計算の過程を表示）となっていて他は同じです。こちらはヘルプウインドウが開き「引数やオペランドが正しく無い時に表示されます」とでます。AD列のエラーが引き続いてると思われますので、AD列のエラーが解決されれば良いのですが、どのように対処すれば良いのか分かりません。この表はZ列からAC列まで他の計算式が入ってまして、最終的にZからACまでの和とADに0.15を掛けたAEをたした数値結果を出したいのです。
サンプル的に作成した別表でL列からV列まで数値の入ってる表のX列に同じ式を入れましたが、こちらはエラー表示が出ていませんし、0.15を乗じられました。

回答No.2

tsubu_m

ざっと拝見したところ、エラーが出る要素が２つ。

まず、２つ目のIF関数の中の、論理式が完結されていませんね。
この式の結果がどうだったらと言う条件分岐が成されていません。
よって、ここで構文エラー。

続いて、AVERAGE関数に文字列は使用できません。
つまり、""(IF関数の「偽の場合」の部分)は文字列として認識されます。
よって、属性エラーが返されます。

まぁ、参考までに。

回答No.1

中京区 桑原町（@l4330）

AVERAGE(IF(

averageの中にifはできません。

補足

Excel2007からAVERAGEIFが追加されたと聞きましたが、AVERAGE(IFは2003で普通に使われてますが、実際に当方で結果が出ています。他の表では同じ式でエラー表示無く普通に表示されています。