[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ある特定の文字以外をカウント』（やっちゃば）
例としてシート全体でが対象で、"あいう"と言う文字以外の文字が入っているセルの個数をカウントするにはどうしたらよろしいでしょうか？ちなみに空白セルはカウントしないやり方を希望です。
宜しく願いします。
COUNTA(範囲)-COUNTIF(範囲,"*あいう*") ←"あいう"を含むもの以外 とか COUNTA(範囲)-COUNTIF(範囲,"*あ*")-COUNTIF(範囲,"*い*")-COUNTIF(範囲,"*う*") ←"あ"or"い"or"う"を含むもの以外 文字通り"数字"は含めず"文字"だけなら、追加でCOUNT(範囲)も引いておけば良いです。
ただし、COUNTA関数は全くの空白以外は、数式の計算結果で""が入っているセルもカウントします。 例) B1に=if(A1=1,1,"") というような数式が入っており、A1が1ではない場合 B1は見た目には空白ですが、COUNTA関数ではカウントします。 これの回避方法は私では解らないので、このようなセルがあるなら他の方をお待ち下さい。
ちなみにシート全体を範囲にすると重くなるのでおススメしません。 実際に使用している範囲だけにした方が良いです。 （夜勤中）
>数式の計算結果で""が入っているセルもカウントします ◆""をカウントしない方法です =SUMPRODUCT((LEN(A1:A10)>0)*1)-COUNTIF(A1:A10,"*あいう*")
◆"あ"or"い"or"う"を含むもの以外 =COUNTIF(A1:A10,">*")-SUMPRODUCT((LEN(A1:A10)>LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A10,"あ",),"い",),"う",)))*1)
◆""のみをカウントする式です =COUNTIF(A1:A10,"<*") (Maron)
Maronさんへ 勉強させて頂きました。 ありがとうございます！
=COUNTIF(A1:A10,"<*") *を使ってこんな書き方が出来るんですね。
あと、質問主さんではないですが質問させて下さい。 COUNTIF(A1:A10,">*") と SUMPRODUCT((LEN(A1:A10)>0)*1) は同じ意味ですよね？？？ 意味的には同じように思いますし、実際試してみても大丈夫そうなんで、 2通りの書き方を示してくれたと解釈して良いでしょうか？ （夜勤中）
◆夜勤中さん、「2通りの書き方を示した」ことは確かですが、式の意味は少し違います
★SUMPRODUCT((LEN(A1:A10)>0)*1) は、文字列と数値をカウントします
★COUNTIF(A1:A10,">*") は、文字列のみカウントする簡便式です
(Maron)
いろんな解釈が出来そうですね
>"あいう"と言う文字 1 文字通り"あいう"という文字・・・"○○あいう××"は該当外 2 "あいう"を含むもの 3 "あ"or"い"or"う"を含むもの 4 "あ○○い××う"・・・三つの文字を含む
>以外の文字が入っているセル 1 数値も含む 2 文字通り文字のみ
条件により回答も違ってきます。 条件を明確にして下さい
私はどちらも1と解釈しましたが、他の回答者は"あいう"を含むものという 解釈ばかりですね。 私の読み取りがおかしいのかな
回答は、質問者の返事待ちとします
By しげちゃん 再編集8:15
すいません、ちょっと忙しい時間になりまして、返事遅れました。
>★COUNTIF(A1:A10,">*") は、文字列のみカウントします
動作確認できました。 *は何でもOKというイメージがあったんで、つい同じかと思ってしまいました。 ありがとうございました。
完全に質問者になってしまってますが、やっちゃばさんへ SUMPRODUCT は非常に計算に時間がかかる関数なので 広い範囲を対象にする場合は ツール → オプション → 計算方法 を手動にしたほうが良いかもしれません。 F9キーを押せば計算します。
（夜勤中）
>SUMPRODUCT は非常に計算に時間がかかる関数なので 何を根拠にこのような記載になるのでしょう
By しげちゃん
以前にも指摘させていただいたように思いますが、 Excel97 SR2 では、以下のようになります。 (6UP)
A B 1 ( 0 ←=COUNTIF(A1,">*") 2 ) 0 ←=COUNTIF(A2,">*") 3 * 0 ←=COUNTIF(A3,">*") 4 + 1 ←=COUNTIF(A4,">*") 5 , 1 ←=COUNTIF(A5,">*")
しげちゃんさん あれ、違うんでしょうか 勉強した本は会社にあって今手元にはないんですが、同じようなことを書いてあるＨＰがあったのでリンクを貼り付けてみます。 http://pc.nikkeibp.co.jp/pc21/special/hr/hr6.shtml ここで、 SUMPRODUCTは「配列数式とは言えないかもしれませんが、計算内容はまったく同じ」 「配列数式は、セルの値を直接使うのではなく、一度、配列にコピーしてから使うので、 そのための時間が余分にかかってしまう」 という記述があります。 私が勉強した本にも同じようなことが書いてありました。 これからSUMPRODUCTは計算に時間がかかると解釈したんですが、解釈の仕方を間違っているでしょうか？ 練習で使ってみたときに、実感できたように思ったので書いたんですが、先入観からくる勘違いかもしれませんね。
6UPさん 2003でもご提示のようになりました。 法則が解らないんですが、何かあるんでしょうか？ 該当する質問をキーワード[6UP COUNTIF >*]で検索してみたんですが、見つけられませんでした。 何かしらの結論が出ていたのであれば、是非教えて頂きたいです。
自分が質問主でもないのに、あまり質問しすぎるのも良くないのかもしれませんね。 別に質問を立てた方がいいでしょうか？ （夜勤中）
=COUNTIF(A1,">*")は、掲載のように文字列「*」より大きいという意味のようです。
42 ←=CODE("*")
43 ←=CODE("+") (6UP)
6UPさん、何度もすみません。 コード番号を比較しているんですね。 勉強したことがない箇所で、コード番号についての知識はほとんどありませんが コード番号を比較しているという法則はわかりました。 ありがとうございました。
誤字があったので直そうとしたら、誤って6UPさんの書き込みを一時消してしまっていました。 訂正しました。すみません。 （夜勤中）
削除したのですが、復活されてしまいましたので、補足させていただきます。 文字列の「-」は、エクセルでは、並び替えや比較演算子の処理に特別なものがあります。 =COUNTIF(A1,">=-")などということも思いつきますが、使用する気はありません。(6UP)
>「配列の大きさ、数式の量、計算の内容、それからパソコンの性能にもよります。 >セルの値を直接使うのではなく、一度、配列にコピーしてから使うので、 >そのための時間が余分にかかってしまうんです。最近のパソコンは性能も高いですし、 >利用できるメモリーも大きいので、気にならないことが多いでしょうけど」
>この方法では条件の数が増えると計算時間も増えるんです。データベース関数の >DSUM 関数を使うと、同じ計算がずっと速くできることがあります
個人的にSUMPRODUCT関数単体で、違いが実感できる様な式を見たことも、 使用したこともありません。
>配列数式では作業セルを使わずに計算できることがメリットなんですが、 >作業セルを使わないと、データの一部を書き換えたときにすべての計算を >やり直すことになります。作業セルを使って段階的に計算するようにしておけば、 >一部の計算をやり直すだけで済みます。データ入力時の再計算時間が >気になるようなら、作業セルを使って段階的に計算することも考えてください」
同じようなSUMPRODUCT関数を多用し、条件・範囲が大きい場合には実感しますね
By しげちゃん
6UPさんご自身で削除されたんですね。 余計なお世話でした^^; >文字列の「-」は、エクセルでは、並び替えや比較演算子の処理に特別なものあります。
[A] [1] '+ 「+」のコード番号は43 [2] '- 「-」のコード番号は45
を昇順で並べ替えると、コード番号が大きいはずの「-」が上にくる。 これも特別な処理の一例でしょうか。 せっかく補足して頂いたんですが、私にはこれくらいが精一杯です。 言わんとされていることにカスってでもいるのか不安です^^;
しげちゃんさん 条件の数が増えるというところがポイントですかね。 今回Maronさんが提示されているくらいでは関係ないんですね。 一応試しては見たんですが、遅かった理由はSUMPRODUMTではなく、単に範囲を広くし過ぎたせいのようです。 1000行まるごとを範囲にしてましたから^^; 100行なら実感できない程度だったので、現実的には単体では問題ないと思っていて良いんですね。 失礼しました。 （夜勤中）
誤解があってもいけないので、 全てのデータを変更して再計算する場合と言うことです。
データおよび式が入力されている場合で、データの一部を変更した場合の 計算にかかる時間ではありません
データの一部を変更した場合の計算は、変更したデータを範囲に持つ式 および揮発性の関数を使用した式が計算の対象になります 。
用事ができましたので、続きは後で・・・
By しげちゃん
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.