Excel2007でCOUNTIFS前ゼロ否定条件

質問

Excel2007 のCOUNTIFS関数での否定条件の
指定方法について教えてください。

VBAのマクロの中で
　(略)
　For i = 1 to 10
　　 Cells(i, "B").Formula = "=COUNTIFS(" & "A1:A10, $C$" & i & ")"
　Next i　
　のような形で　COUNTIFSを利用しています。

　COUNTIFSの条件に <>を使って否定条件を指定したいのですが、以下の
　ように期待した動作になりません。　

　例
　 A　　　 　　B　 　　　　　C 　 　　 　　D　　　　　　　　　　　
1 '080　　一般品　 　 <>'080 　 　　　=COUNTIFS(A1:A5,C1)　
2 '020 　一般品　　　　<>一般品　　=COUNTIFS(B1:B5,C2)
3 '040 　一般品
4 '060　 　特別品　
5 '080 　 　一般品

　セルC1の記述を変えた結果は以下の通りです。

　　 C1　　　　　　D1 　 　　　備考
1) 　<>'080 　　5 　　　3　が入ってほしい。
2) 　<>080 　　5 　　　'　の有無で結果は変わらず。　　
　　'（シングルクォーテーション）は接頭辞で上記結果には無関係と理解。

3) 　"<>'080" 　　0 　　 "<>'080" 全体を条件値として認識してしまう。
4) 　'080 　 　　　2　 　当然
5)　 <='060 　　 3　 　当然

ちなみに C2に　<>一般品　と条件を設定すると D2には１と期待通りの値が入ります。　

1)2) の結果から　A列に記述されている'080と　セルC1の式に書か
　れた '080 が、なぜか別の値と評価され 結果 D1が 5 となっていると理解しています。

A列のシングルクォーテーションは削除、A列の書式を文字列とし
文字列"080"とIF関数で比較すると以下の結果でした。

　 A　　　 B　 　　　B列の式　　　　　　　　　　　
1 080　　 FALSE　 IF(A1<>"080",TRUE,FALSE) 　
2 020 　 TRUE IF(A2<>"080",TRUE,FALSE)
3 040 　　TRUE IF(A3<>"080",TRUE,FALSE)
4 060　 　 TRUE　 IF(A4<>"080",TRUE,FALSE)
5 080 　 　FALSE IF(A5<>"080",TRUE,FALSE)

Excelの「数式」メニュー　－「ワークシート分析」－「数式の検証」で
C1セルに　<>'080 、<>080、"<>'080" 、<='060 を指定した時、それぞれ

1) COUNTIFS(A1:A5,"<>'080") 　
2) COUNTIFS(A1:A5,"<>080")

3) COUNTIFS(A1:A5,"""<>'080""")
5) COUNTIFS(A1:A5,"<='060")

　と評価されています。

また　VBAのテクニックを解説するWebなどでは、"<>"と値'080を&
でつなげる方法も紹介されていましたが、上記の場合結局
COUNTIFS(A1:A5,"<>080")　→　5 と評価されて
　しまいます。　　

　文字列同士で比較すれば、期待する結果になりそうですが
条件の書き方には工夫がいりそうです。どのように記述すればよいでしょうか

お知恵をお借りしたくいと思います。どうかよろしくお願い致します。

以上

回答No.2

mt2008

検索条件で面倒なのは今回の様に数値に見える文字列の時ぐらいです。
数値に見えると、Excelが勝手に数値として扱ってしまい、"<>080"の検索条件では数値の80以外の物がカウントされてします。
そのため、*（任意の文字数）、？(任意の1文字）のワイルドカードを使って明示的に文字列と認識させます。
ちなみに今回の「<>080*」は、正確に言うと「080で始まらない…」と言う意味になりますので、A列に'0808なんてデータが有るとカウントから除外されてしまいます。
ご注意ください。

ただ、A列を数値にして、表示書式で080の様に表示させていれば悩まずに済んだと思いますよ。

同様の検索条件は、SUMIFSでも使えます。

回答No.1

mt2008

まず、コードを以下のように変えてください（空白セルもカウントから除外するようにしています）。

Cells(i, "B").Formula = "=COUNTIFS(" & "A1:A10, $C$" & i & ")"
↓
Cells(i, "B").Formula = "=COUNTIFS(" & "A1:A10,""<>"",A1:A10, $C$" & i & ")"

そのうえで、セルC1に入れる文字列を　'<>080　ではなく、'<>080*　にしてみてください（頭の’は、無くても良い）。
これでB列にはこんな感じの式↓が入ることになり、空白でもなく、080でもないセルがカウントされます。
=COUNTIFS(A1:A10,"<>",A1:A10, $C$1)

お礼

mt2008様

早速の回答を頂き有難うございました。

条件に<>080*　と＊(アスタリスク)を加える
ことで、080以外をカウントアップするという動作
になることを実機で確認しました

条件に<>　を指定することで、空白以外をカウント
アップするという動作になることも実機で確認しました

いずれも経験の無い人にはなかなか思いつかない
テクニックだと感じ入りました。質問の投稿から回答まで
わずか４０分で回答頂き、この数日ずっと悩んでいた問題
を解決することができました。

ワイルドカードの＊を加えることで文字列として比較させる
ことができることや、<>""ではなく<>　とだけ指定することで
空白以外の動作をさせられることなどは、他の文字列を操作し
たり比較したりする操作でも通用するテクニックなのでしょうか
折角なので補足いただければ非常にありがたいです。

とにかく今回の回答は非常に有用でした。有難うございました。