- 締切済み
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 と評価されて しまいます。 文字列同士で比較すれば、期待する結果になりそうですが 条件の書き方には工夫がいりそうです。どのように記述すればよいでしょうか お知恵をお借りしたくいと思います。どうかよろしくお願い致します。 以上
- kazusmo
- お礼率7% (1/14)
- Excel（エクセル）
- 回答数2
- ありがとう数1
- みんなの回答 （2）
- 専門家の回答
みんなの回答
- mt2008
- ベストアンサー率52% (885/1701)
検索条件で面倒なのは今回の様に数値に見える文字列の時ぐらいです。 数値に見えると、Excelが勝手に数値として扱ってしまい、"<>080"の検索条件では数値の80以外の物がカウントされてします。 そのため、*（任意の文字数）、？(任意の1文字）のワイルドカードを使って明示的に文字列と認識させます。 ちなみに今回の「<>080*」は、正確に言うと「080で始まらない…」と言う意味になりますので、A列に'0808なんてデータが有るとカウントから除外されてしまいます。 ご注意ください。 ただ、A列を数値にして、表示書式で080の様に表示させていれば悩まずに済んだと思いますよ。 同様の検索条件は、SUMIFSでも使えます。
- mt2008
- ベストアンサー率52% (885/1701)
まず、コードを以下のように変えてください（空白セルもカウントから除外するようにしています）。 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)
関連するQ&A
- エクセル、COUNTIFS関数の範囲指定について
エクセル2010です。 「A1の文字列が、B列とC列の中から合致し、さらにD列の"○○"という条件を満たすものはいくつあるか」 という数式を作りたいです。 写真でいうと、B・C列の中から中央区があるかどうか、さらにその中に山田がいるか、という検索式です。 そこで、COUNTIFS関数を使い、fxボタンで半自動で作ったところ、 =COUNTIFS(B:C,A1,D:D,"山田") となったのですが、なぜか結果はエラーになってしまっています。 どうやら検索条件の範囲が「B:C」になっているのが問題なのか、B:BやC:Cだけにすると、ひとつの列なら正常に計算するようです。 そこで式を二つに分け、プラスでつなげてみました。 =COUNTIFS(B:B,A1,D:D,"山田")+COUNTIFS(C:C,A1,D:D,"山田") こうしてみると一応成功しました。 しかし動作が重くなり、処理に数秒かかるようになってしまいました。 （B:Bなどひとつの列なら、数式を記入した瞬間に結果がでます） できれば数式は短くシンプルにしたいので、前述の短い形式のようにならないものか、と思います。 COUTIFS関数で、検索範囲を「○列～△列」のようにできないものでしょうか？ アドバイスをよろしくお願いします。
- ベストアンサー
- その他MS Office製品
- COUNTIFSの条件に日付を指定する方法
Excel 2007 の COUNTIFS 関数 の条件として ２つの月日で範囲 として データを絞り込むマクロを記述しています 外部からExcel2007形式(.xlsm) 形式のsheet1を読みこみ sheet2 に記述した条件値とCOUNTIFSの条件値と比較しよう としています。 尚、比較対象は sheet1 のＡ列「出荷時期」のデータで 書式設定は 標準となっていて 文字列データです。 【sheet1】 [A] [B] [C] ヘッダ 出荷時期 商品分類 サイズ [1] '2014/04 一般 Ｌ [2] '2014/05 一般 Ｍ [3] '2014/06 特別 Ｍ 【Sheet2】 ・B列に ＝COUNTIFS( Sheet1!$A$1:$A$3,$A2)のように 条件値の＄A2の"2"の所を行によって変えて入力してます。 [Ａ] [Ｂ] [１] 出荷時期 件数 数式の検証＝Excelの評価結果 [２] ">=2014/04" 0 """>=2014/04""" [３] >="2014/04" 3 ">=""2014/04""" [４] >="2014/05" 3 ">=""2014/05""" ＝＝＞＞ ""をとる、演算子と 文字列の間に半角スペースを入れる と [５] >=2014/04 0 ">=2014/04" [６] >= 2014/04 3 ">= 2014/04" [７] >= 2014/05 3 ">= 2014/05" ＝＝＞＞ アスタリスクをつける 等。 [８] >=2014/04* 2 ">=2014/04*" [９] >=2014/05* 1 ">=2014/05*" [10] >=2014/06* 0 ">=2014/06*" ＝＝＞＞ 演算子と文字列の間に'(シングルクォーテーション)をつける [11] >='2014/04 2 ">='2014/04" [12] >='2014/05 1 ">='2014/05" [13] >='2014/06 3 ">='2014/06" ＝＝＞＞ 文字列操作関数を入れる [14] >= & DATE(2014,4,1) 3 ">= & DATE(2014,4.1)" (注) 文字列関数を使う方法は http://www.relief.jp/itnote/archives/excel-countif-date-less-greater-than.php の例を参照しました。この方法は sheet1 の出荷時期データが日付データである必要 があるようです。 上記のようにいろいろと条件値のシンタックスを変えて試してみましたが期待する結果 になりません。 sheet1から入力する 出荷時期の元のデータが文字列の部分に修正を加え、日付 データに修正すると sheet2 の件数の値が変わるので元データを修正する方法 もあるかもしれません。 ただし 基本的にには 元データには 手を加えない 方法で対処したいと思います。 条件値の指定を どう記述すれば、利用者が自然に期待した通りの結果が得られるのか ご存知の方があれば教えてください 以上
- ベストアンサー
- Excel（エクセル）
- 複数条件でのカウント
データの個数のカウントで行き詰りましたので質問させていただきました。 複数条件で検索するのですが、その条件の中に AND と OR があります。 現状ではCOUNTIFSを足していって算出していますが、場合により条件が多くて とんでもなく長い式になってしまっています。 なんとか簡潔に短い式にまとめられないかと思いまして試行錯誤したり調べたり してみたのですが答えが出ませんでしたので質問に至りました。 現状では以下の様になっております。 Ａ列・・・２ケタの数字 Ｂ列・・・数字・文字列の混合 データは約１０００行あります。 このデータから、Ａ列の １０ or １５ のデータで、 かつＢ列の ４ or １_１００ のデータの個数を求める式です。 =COUNTIFS(A1:A1000,C1,B1:B1000,D1)+COUNTIFS(A1:A1000,C1,B1:B1000,D2) +COUNTIFS(A1:A1000,C2,B1:B1000,D1+COUNTIFS(A1:A1000,C2,B1:B1000,D2) 条件を変えていろいろカウントしたいので、 C1～C2にＡ列の検索条件を入力するセルとし、 D1～D2にＢ列の検索条件を入力するセルとしております。 上記の式は条件がそれぞれ２個ずつとなっておりますが、 片方だけで６個までいく事もあり、その場合式がかなり長くなります。 これをどうにかして簡潔に短くまとめたいと思っております。 ご存知の方がおられましたらご教授下さい。 よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- COUNTIFSに関してお聞きしたいです(>_<)
似たような質問はたくさんあるのですが、 私が求めているものと完全に一致するものがないため、困っています。 （例） A B C D I 1 楽しい まあまあ楽しい 普通 ・・ 遊園地 2 ○ 3 ○ 4 ○ ○ 5 ○ 6 ○ 7 ○ 8 ○ 9 ○ ○ 10 ○ ○で集計をしているのですが、 求めたいものは、「楽しいORまあまあ楽しいに当てはまる人の中で、さらに遊園地を選んだ人」です。 ここでは2という回答が欲しいのですが。 ただ１列のセル数を数えるときはCOUNTAを使うので、きっとCOUNTIFSで 出来るのだろうとは思ってるのですが、 エラーが出たりしてうまくいきません。 よく、「この中から年齢が何歳以上を選ぶ」とかいうのは複数条件でよく話題になっていますが、○だとどうなるのでしょうか？ 聞くまでもない質問かもしれませんが、 宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- エクセル2010、複数条件のセルを数える
エクセル2010です。 A列のリストの中から、「B1またはC1のセル」と同一の名前がいくつあるか、という計算式を作りたいです。 複数条件ですから、COUNTIFS関数で、 =COUNTIFS(A:A,B1,C1) としてみたのですが、これではエラーが出ます。 よく考えてみたら、「または」という、どちらかの条件が含まれるということですから、これでは駄目なようです。 そこで調べたところ（http://support.microsoft.com/kb/275166/ja）、こういう場合SUM(IF())を使うというのがわかり、 =SUM(IF((A:A=B1)+(A:A=C1),1,0)) としてみましたが、これでも結果がすべて0になってしまいます。 私は何か捉え違いをしているのでしょうか？ こういう条件の場合、どういう式にすればよいのでしょうか。 どうぞよろしくお願いします。
- ベストアンサー
- その他MS Office製品
- COUNTIFS関数について
お世話になります。 3つ以上の条件を含む式の間違いが分かりません。 =COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2",sheet1!G$1:Q$200,B3) のようなブック内の別シートを3つの条件に合うデータ件数を集計したいのですが、#value!が出ます。 （ )内の3つの条件をばらして、 =COUNTIFS(sheet1!A$1:A$200,E$1) (1) =COUNTIFS(sheet1!C$1:C$200,"2") (2) =COUNTIFS(sheet1!G$1:Q$200,B3) (3) および =COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2") は、正しく計算できるのですが、なぜか(1)～(3)を一つの式にすると、エラーになります。 何が悪いのでしょうか。
- ベストアンサー
- Excel（エクセル）
- ３っの条件式の書き方をお聞きします。
複数条件の式の書き方をお聞きします。 A.B.C の３っの条件式です。 Aが空白のときtrue、falseを○、 Bが空白のときtrue、falseを△、 Cが空白のときtrue、falseをとします。 A B C D E F G 1○△=false:false:false=◇ 2○△=false:false:false=◇ 3○△=false:false:false=◇、 trueを1、falseを0とし、 これを DEF G 111:空白"" 110: 101:△ 011:○ 100:И 010:Е 001:Ш 000:◇ と表示したときの、 この場合のG 1.2.3の式を教えていただきたい。 式が長すぎてどうすれば良いのか分かりません。 A.B.C３っの条件式は、どう書くのでしょうか。 エ2010。
- ベストアンサー
- オフィス系ソフト
- countifs関数
countifs関数で複数条件に合うものをカウントしたいのですが たとえばA列にあるリンゴとミカンとバナナの中で B列にあるA商店 C列にある掛け売り といったようなカウントをする場合 どのように設定すればいいのでしょうか？
- ベストアンサー
- Excel（エクセル）
- COUNTIFS関数 条件が日付の場合の入力方法
いつも回答ありがとうございます。 別シートに一覧があります。この別シートから個数を算出し、集計用シートに個数を表示させようと思っています。COUNTIFSの記述方法通り【集計用のC4セルに=COUNTIFS(一覧!$D$8:$D$76,C3,一覧!B8:B51,B4)】に入力しましたが、結果がVALUEになります。日付の条件記述方法が間違っていると思われますが、何処が間違っているのか分かりません。御指導の程よろしく願い致します。 一覧 B8：B100 に日付 D8：D100 に商品名 集計用シート B4：B100 に日付が連番で入力されている C3：Q3 に商品名が入力されている 集計用シートのセル番地を使用して条件入力。
- ベストアンサー
- オフィス系ソフト
- excel2003でcountifs関数と同等の
excel2003でcountifs関数と同等の効果を出すにはどうすればいいのでしょうか？ 具体的には、A1からB100に1から5の数字がランダムに入っている場合、 ＡとＢの列がどちらも３である行の個数をカウントしたいです。 よろしくお願いいたします。。
- ベストアンサー
- オフィス系ソフト
お礼
mt2008様 早速の回答を頂き有難うございました。 条件に<>080* と＊(アスタリスク)を加える ことで、080以外をカウントアップするという動作 になることを実機で確認しました 条件に<> を指定することで、空白以外をカウント アップするという動作になることも実機で確認しました いずれも経験の無い人にはなかなか思いつかない テクニックだと感じ入りました。質問の投稿から回答まで わずか４０分で回答頂き、この数日ずっと悩んでいた問題 を解決することができました。 ワイルドカードの＊を加えることで文字列として比較させる ことができることや、<>""ではなく<> とだけ指定することで 空白以外の動作をさせられることなどは、他の文字列を操作し たり比較したりする操作でも通用するテクニックなのでしょうか 折角なので補足いただければ非常にありがたいです。 とにかく今回の回答は非常に有用でした。有難うございました。