＃N／Aを含むセルデータを正しく計算できない

質問

sum関数により飛び飛びのセルの数字をカウントしています。

　　　行
　　　10　　　１
　　　11　　　３
　　　12　　　５
　　　13　　　２
　　　14　　　０
　　　15　　　#N/A
　　　16　　　#N/A

合計=+10+11+12+13+14+!5+16　　これだとErr
　合計=+10+11+12+13+14　　　　　正しく計算される

◎14行目までの合計であれば正しくでます。
　　　●１５行目を含むとエラーになります。

　　　　　いい方法はありませんか？

　　　　　#N/A　ではVlookupを使用し、データの範囲外の状態です。
　　　　　計算側で解決したのですが、わかりません

回答No.4

kagakusuki · ベストアンサー

>実際の関数は以下のとおりです。

> =IF((VLOOKUP($A370&"-"&$B370,データ!$D$9:$AG$1046,4,0)=""),"",(VLOOKUP($A370&"-"&$B370,データ!$D$9:$AG$1046,4,0)))

　それでしたら、その関数を次の様に変更されると宜しいと思います。

=IF(COUNTIF(データ!$D$9:$D$1046,$A370&"-"&$B370),VLOOKUP($A370&"-"&$B370,データ!$D$9:$AG$1046,4,FALSE),"")

或いは

=IF(ISERROR(VLOOKUP($A370&"-"&$B370,データ!$D$9:$AG$1046,4,FALSE)),"",VLOOKUP($A370&"-"&$B370,データ!$D$9:$AG$1046,4,FALSE))

或いは

=IF(ISNUMBER(MATCH($A370&"-"&$B370,データ!$D$9:$D$1046,0)),VLOOKUP($A370&"-"&$B370,データ!$D$9:$AG$1046,4,FALSE),"")

お礼

ご教示頂きました。Err回避策の[Countif iserror isnumber]
の使い方を学習し実施しました。ISERROR関数にて解決しました。
関数の知識が深まりました。エラー表示のない美しい表がでます。
ありがとうございました。

回答No.3

keithin

ご相談に書かれていなかったことを後出しされても、おつきあいする義理はありませんよ？

＞範囲の飛び飛び範囲が６０ぐらいあるのです。

少なくともSUMIF関数単独では「とびとびを計算する」方法はありませんので、

再掲：
＞気にせずヒトカタマリで計算して下さい。

=SUMIF(B1:B1000,">0")
で計算してください。と回答済みです。
６０カタマリもあるとは思いませんでしたけど、SUMIF(最初の塊,">0")+SUMIF(次の塊,">0")+…とやれば出来ますが最初から論外としています。

間の飛ばしたいセルに何を書いていてどんな結果が出てるのか、そちらも不明のままなので、もっとうまい手があるかどうかは何とも言えません。
（簡単な方法：飛ばしたいセルには「マイナスの値」で計算結果を出して置き、表示だけプラスに見せかけて表示するとか。文字列で計算結果を出しておくとか。）

まぁでも飛ばしたいセルに細工をするぐらいなら、別の方の回答の方法でエラーを出さないVLOOKUPの式をきちんと書けるようになって貰った方がクールです。回答されている方法で間違いなくできますので、寄せられた回答をもう一回よく見てやってみたらどうでしょう。

そのうえで、更に必要なら「とびとびのセル範囲を計算する方法」について、改めて具体的な詳しい目に見えるあなたのエクセルの姿の状況を添えて、別途ご相談を投稿してみて下さい。

＃ご利用のエクセルのバージョンも不明のご相談ですが、エクセル２００７以降なら
=IFERROR(今の数式,"")
としてしまうのが一番簡単です。

＃補足
今の数式も一応拝見しましたが、考え方から間違っているようですけど私に対しての情報提供じゃないんで回答は控えます。

お礼

大変お手数をお掛けしました。お陰様で、N/A　Err時の計算
について解決しました。
ありがとうございました。

　SUMIF関数に「とびとびを計算する」方法がないことや
　vlookupに関して知識が深まりました。

回答No.2

keithin

たとえば
=SUMIF(B10:B16,">0")
のようにして出来ます。

＞飛び飛びのセルの数字をカウントしています。

ご相談の情報提供では何も「飛び飛び」になっていませんが，まぁ気にせずヒトカタマリで計算して下さい。
カウントじゃなく合計しているのは，まぁただの誤記として。

補足

例が不適切で申し訳ありません。

ご指導頂いたSUMIF簡単ですのでこの方法にしたいのですが、
対象データの範囲が実は飛び飛びなのです。
「その際範囲の飛び飛び範囲が６０ぐらいあるのです。」
その方法を教えてください。

　SUMIF(B10:B16,">0")

　　B10：B16　B20:B26 B30:B36 ・・・・

以上のように範囲を特定したいのです。

回答No.1

tsubuyuki

> #N/A　ではVlookupを使用し、データの範囲外の状態です。

どのようなVLOOKUP式かわかりませんが、
IF式を追加して「エラーなら表示しない（空白を返す）」とし、
合計にSUM関数を使うとうまくいきそうな気がします。

例えば、
　　=IF(対象セル="","",VLOOKUP(対象セル,範囲,列))
とすると、対象セルが空白だったら空白を、空白以外だったらVLOOKUPが走ります。
もうひとつISERROR関数を使って、
　　=IF(ISERROR(VLOOKUP(対象セル,範囲,列)),"",VLOOKUP(対象セル,範囲,列))
とすると、VLOOKUPがエラー（データの範囲外）であれば空白を、
エラーじゃなかったら（データの範囲内）VLOOKUPの結果を返してきます。

SUMは範囲内に空白や文字列があっても計算してくれますから、
エラーだけ回避しておけば問題なく合計してくれると思いますよ。

補足

ERRの箇所には以下の関数が入っています。ここが#N/A
ご指導頂いたようにするのですがうまくゆきません。

実際の関数は以下のとおりです。
お時間があればで結構です。よろしくお願いします。

=IF((VLOOKUP($A370&"-"&$B370,データ!$D$9:$AG$1046,4,0)=""),"",(VLOOKUP($A370&"-"&$B370,データ!$D$9:$AG$1046,4,0)))