EXCELで顧客管理表を作っています。

顧客データ一覧の特定の項目の中から条件にあうセルがいくつあるかを数えて
同シート内に個数を表示させたいと思っているのですが、うまくいきません。
(今回の場合は項目が「支店」で、○支店を含むデータが何個、×支店を含む
データが何個…といった感じです)

また顧客データは毎日追加していき、オートフィルタを使った場合には表示
されているデータの中から検索、という形が希望です。

COUNTIF関数を使ってみたのですが、オートフィルタをかけた場合でも
全てのデータから検索されてしまうので、もし他の関数もしくは方法を
ご存知でしたら教えてください。

よろしくお願いします。

このQ&Aに関連する最新のQ&A

A 回答 (5件)

作業列(仮にA列とします)を追加して、A2に以下の式を入力します。



=SUBTOTAL(3,B2)

B2でなくても、データがある列ならよいです。
下にコピーします。

SUBTOTAL関数は非表示セルを無視しますので、A列は表示されていれば1、非表示なら0となっています。

あとはSUMPRODUCT関数の条件の一つとしてA列を加えれば、表示されているセルのみの計算結果が得られます。

=SUMPRODUCT((条件列範囲="○支店")*A列範囲)

なお、A列自体は非表示でも構いません。
    • good
    • 5
この回答へのお礼

回答ありがとうございます。
そういうやり方には気付きませんでした…!
おかげさまで望んでいた形のものができました。
ありがとうございました^^

お礼日時:2007/02/05 11:40

>オートフィルタをかけた場合希望の効果が得られませんでした…


オートフィルタを”すべて”にするしかないでしょう。
>条件が『「支店」の列(M列)に「○支店」を含むもの』という
ひとつしかない場合、数式はどうなるのでしょうか?
条件がひとつなら、COUNTIF です。やはりフィルタは外します。
    • good
    • 0
この回答へのお礼

今回は回答ありがとうございました。
No.5の方の方法で無事解決しました。
またつまづいた時はアドバイスをお願いします^^

お礼日時:2007/02/05 11:11

No1です。


あとはSUMPRODUCTが使い易いと思います。
参考までに
http://www.excel.studio-kazu.jp/lib/e3h/e3h.html
例中の*(C2:C8)をとると、個数になります。
    • good
    • 0
この回答へのお礼

サイトを参考にやってみたのですが、No.2の方にも書いた通り
オートフィルタをかけた場合希望の効果が得られませんでした…
もし式などでアドバイスがあれば、お願いしますm(_ _)m

お礼日時:2007/02/02 12:55

SUBTOTAL関数にはCOUNTIFの機能はないので駄目でしょうね。


オートフィルタの条件をどこかにセットできるなら方法は3つです。
視覚的判りやすい関数の解説のサイトを載せてますのでご覧ください。

DCOUNTA関数
=DCOUNTA("表の範囲","集計する列の1行目の見出し","条件の範囲")
http://office.microsoft.com/ja-jp/excel/HP100622 …

SUMPRODUCT関数
=SUMPRODUCT((条件列範囲1=条件1)*(条件列範囲2=条件)*ISNUMBER(FIND("○支店",支店名範囲,1))
http://excel.100page.net/100mag/112step3/no111.h …

配列数式({=SUM(IF(条件,集計範囲))})
=SUM(IF((条件列範囲1=条件1)*(条件列範囲2=条件)*ISNUMBER(FIND("○支店",支店名範囲,1),1)
としてCtrl+Shift+Enterで配列数式にする。
http://hp.vector.co.jp/authors/VA014071/tips/hai …

この回答への補足

お礼の文章の続きです。
配列数式を試そうとしたのですが…すみません、よく分かりませんでした(--;
条件が『「支店」の列(M列)に「○支店」を含むもの』という
ひとつしかない場合、数式はどうなるのでしょうか?

補足日時:2007/02/02 13:07
    • good
    • 0
この回答へのお礼

回答ありがとうございます。
SUMPRODUCTでうまくいきそうだったんですが、オートフィルタをかけた場合
非表示セルのデータも計算されてしまいました。数式は

=SUMPRODUCT((条件列範囲="○支店")*1

としてみたのですが…式がダメなんでしょうか…?

今から配列数式のやり方も試してみようと思います。

お礼日時:2007/02/02 12:51

こんにちわ


DCOUNT を使うのはだめですか?
    • good
    • 0
この回答へのお礼

早速のご回答ありがとうございます。
あまり関数を知らなかったのでDCOUNTに気づきませんでした;
この関数でもできそうなのですが、できれば関数の中で検索文字を
指定したいのですが…そのような関数はありますか??

お礼日時:2007/02/01 14:28

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QEXILEのカウント関数について教えて下さい。 40行あるA列に同じ文字をセルに入力。B列には異なる

EXILEのカウント関数について教えて下さい。

40行あるA列に同じ文字をセルに入力。B列には異なる文字をセルに入力。C列にA行とB行を結合した文字を表示しています。
D列にC列の行ごとで同じセルの数をカウントしたいと思っていますが
C列は関数がある為、関数をカウントしてしまいます。D列は、40行全て40と表示してしまいます。表示されてない文字はカウントしない様に出来ないでしょうか。教えて下さい。

Aベストアンサー

No.1です。

数式は2行目に入れているのですね?
>何も表示されてない時に1048575と途方にもない・・・

Excel2007以降では最終行が1048576行目になります。
データがない場合は空白セル数(数式は入っているが空白に見えている)セル数が表示されているものと考えられます。

このような場合はエラー処理が必要になります。
一般的にはIF関数で対処できます。
(エラー処理は色々な場面で出てきます)

D2セルの数式を
=IF(C2="","",COUNTIF(C:C,C2))
としてフィルハンドルで下へコピーしてみてください。
数式の意味
C2セルが空白に見えている場合は何も表示しない、
そうでない場合はC列にあるC2セルの個数を表示
といった感じになります。

※ 厳密にいえば数式が入っているので「空白」ではないのですが
現段階ではこの程度でやめておきます。m(_ _)m

Qオートフィルタ使用時にCOUNTIFをSUBTOTALのように使いたい。

エクセルのシートでデータ管理をしようと思います。
その時便利なのが、オートフィルタですが、

   SUBTOTAL関数「例:=SUBTOTAL(9,A1:A100)」

を設定した場合、フィルタを操作すると、必要な数値が返ってきます。ところがというか当然というか、

   COUNTIF関数「例=COUNTIF(B1:B100,"○")

ではだめですよね。どうにかして関連づける方法はないでしょうか。
または、その他に同様な効果が得られる方法はありませんか。

Aベストアンサー

回答がありませんね。
質問の意味は、
  例えば、101行目にSubtotalを置いていればフィルタをかけたときに選択されたものだけが集計される。
  しかしCountifのように条件付きの集計はできないので代替手段は?
ということでしょうか。

多分、Subtotalの集計方法はフィルタで絞られたものを対象にするので、さらにそれを絞り込むことは難しいと思います。

>その他に同様な効果が得られる方法はありませんか
ということなので、機能が無ければ作ればいいという事で下記のユーザー定義関数を書いてみました。
フィルタで絞る時は、該当しない行は自動的に非表示になるのでそれを利用しています。

使い方は、COUNTIF(B1:B100,"○") と同じようにsubtotal_countif(B1:B100,"○") とします。

標準モジュールに貼り付けます。
Public Function subtotal_countif(rgSelect As Range, moji As String)
  Dim rg As Range 'セル
  Dim cot As Long 'カウンタ

  For Each rg In rgSelect
    If Rows(rg.Row).Hidden = False Then '表示されている行だけ対象にする
      If rg = moji Then
        cot = cot + 1
      End If
    End If
  Next
  subtotal_countif = cot
End Function

回答がありませんね。
質問の意味は、
  例えば、101行目にSubtotalを置いていればフィルタをかけたときに選択されたものだけが集計される。
  しかしCountifのように条件付きの集計はできないので代替手段は?
ということでしょうか。

多分、Subtotalの集計方法はフィルタで絞られたものを対象にするので、さらにそれを絞り込むことは難しいと思います。

>その他に同様な効果が得られる方法はありませんか
ということなので、機能が無ければ作ればいいという事で下記のユーザー定義関数を書いて...続きを読む

Qエクセルである文字の並びの入力があるセルのカウント

エクセルである文字の並びの指定された列にある何かが入力されたセル数のカウントについて
=SUMIF(B4:I300,L51,I4:I300)
現在上の数式を使って指定セルの数値の合計は出せるのですが
この数値の合計ではなく、数値が入力されているセルの数をカウントしたいのですが
ググってもヒントが出てきません。
上記の数式のまま簡単んな追加で出来るのか?
全く別の数式になってしまうのでしょうか?

OS7 エクセル2007です。

よろしくお願いします。

Aベストアンサー

間違い:
>文字の並びの指定された列にある何かが入力されたセル数のカウント

やりたいこと:
B列に所定の内容が記入されている行で、I列に何でもいいけど何かが「記入されている」セルの数

=COUNTIFS(B4:B300,L51,I4:I300,"<>")
ただしI列には、生数字など生データが記入してあるとして。



もしもI列に最初から全部「数式が記入されていて」数字とか””が計算されているみたいなのだと、それはまた別のお話なので注意してください。
=COUNTIFS(B4:B300,L51,I4:I300,"<="&MAX(I4:I300))

QExcel2003でシート保護(オートフィルタ使用にはチェック)するとマクロのオートフィルタでエラーになる

QNo.3901563で回答いただき、Excelの試用で保護後のオートフィルタは使えないのはわかりました。
そこで予めオートフィルタを設定し、シートに保護をかけました。
Excelの画面上ではオートフィルタは使用できます。
マクロよりオートフィルタの抽出条件を変更するコマンドを実行すると「保護されたシートに対して、このコマンドは実行できません」と表示されます。コマンドは新しいマクロの記録で操作を記録したものです。記録したときは特にエラーはでませんでした。

Selection.AutoFilter Field:=2, Criteria1:="AAA"

マクロのコマンドでは保護のかかったシートのオートフィルタの抽出条件を変更することは出来ないのでしょうか。
マクロの前後にシート保護を解除するコマンドを入れようかとも思ったのですが、保護にパスワードがかかっており、本末転倒となるもので。

Aベストアンサー

こんにちは。

'標準モジュールで、自動設定

Sub Auto_Open()
Const PWS As String = "PS" 'パスワード
 With Worksheets("Sheet2")
  .Unprotect PWS
  .Protect PWS, UserInterFaceOnly:=True
  '以下をつければ、手動でもオートフィルタは使えます。
  .EnableAutoFilter = True
 End With
End Sub

'-----------------------------------------------
UserInterFaceOnly:=True のオプションをつければ、マクロで処理できますし、また、EnableAutoFilter にすれば、保護された状態で、オートフィルタは使えます。

なお、オートフィルタは、こんな感じです。

Sub Test1()
With Range("A1").CurrentRegion
 .AutoFilter Field:=2, Criteria1:="aaa"
End With
End Sub

こんにちは。

'標準モジュールで、自動設定

Sub Auto_Open()
Const PWS As String = "PS" 'パスワード
 With Worksheets("Sheet2")
  .Unprotect PWS
  .Protect PWS, UserInterFaceOnly:=True
  '以下をつければ、手動でもオートフィルタは使えます。
  .EnableAutoFilter = True
 End With
End Sub

'-----------------------------------------------
UserInterFaceOnly:=True のオプションをつければ、マクロで処理できますし、また、EnableAutoFilter にすれば、保護された状態...続きを読む

Q(Excel 2003) 日付が入力されているセルの個数をカウントしたい

Excel 2003 です。

2つ質問がありますが、2点とも稀なケースの問題ですので急いではおりません。
お時間のある時にでもアドバイスをいただければ幸いです。
よろしくお願いします。


【質問1】

日付が入力されているセルの個数をカウントしたいのですが

=COUNTIF(B2:B6,">=2006/04/01")

のように数式を記述すると期待通りの結果を求められます。
しかし、セル範囲の中に 38885 のような値を入力すると、そのセルも日付として
カウントしてしまいますよね。
そうなる理由は分かっているのですが、どういう数式を記述すればよいのかが
分かりません。


【質問2】

上記課題を解決できないのでユーザー定義関数を自作しました。

Public Function CountDate(範囲 As Range) As Long
  Dim MyCount As Long
  Dim MyRange As Range
  MyCount = 0
  For Each MyRange In 範囲
    If IsDate(MyRange.Value) Then
      MyCount = MyCount + 1
    End If
  Next
  CountDate = MyCount
End Function

最初は完璧と思えたのですが、1点問題がありました。
セルに 38885 と入力した後に表示形式を日付に変更するとカウントしてくれません。
根本的に記述が間違っているのでしょうか?

Excel 2003 です。

2つ質問がありますが、2点とも稀なケースの問題ですので急いではおりません。
お時間のある時にでもアドバイスをいただければ幸いです。
よろしくお願いします。


【質問1】

日付が入力されているセルの個数をカウントしたいのですが

=COUNTIF(B2:B6,">=2006/04/01")

のように数式を記述すると期待通りの結果を求められます。
しかし、セル範囲の中に 38885 のような値を入力すると、そのセルも日付として
カウントしてしまいますよね。
そうなる理由は分かっているの...続きを読む

Aベストアンサー

2についてですが書式変更時点では確かに計算しませんがそれはユーザー定義関数の仕様で範囲内に入力された時点で正しく計数されますからそんなに問題ではないのでは?例えば何かダミー入力するセルを範囲内に設けては?

Qオートフィルタで複数の項目を同時に設定する方法

EXCEL2002をデータベースに使用しています。
オートフィルタで一定条件の項目を抽出する際に、同時に同じ列の中の複数の項目を設定して、抽出表示することはできるのでしょうか?
できるならその方法を教えて下さい。お願いします。

Aベストアンサー

「2つまで」又は「範囲」なら▼で「オプション」にすれば設定できます。

「3つ以上」の場合は「データ」「フィルタ」「フィルタオプションの設定」で行ってください。
別のシートか空いている列に
項目名
条件1
条件2
条件3
の様にして
元の表をリスト範囲に、上記の条件を抽出条件範囲に設定してOKです。

Q特定のセル範囲で4文字以上入力されているセルの数をカウントしたい

特定のセル範囲で4文字以上入力されているセルの数をカウントしたいと思っています。
VBAで記述すれば簡単なことだと思いますが、これくらいなら関数でもできそうだと思い
チャレンジしてみましたが挫折しました…。

関数の引数に「A1:C5」などという感じで範囲を指定する記述で
できれば短くシンプルな記述を希望しています。

よろしくお願いします。

Aベストアンサー

=SUMPRODUCT((LEN(A1:C5)>3)*1)

=SUM(INDEX((LEN(A1:C5)>3)*1,))
でも可能です

QOpenOfficeオートフィルタで含むができない

OpenOffice2.4の オートフィルタで例として県名をタイトルにして山を含む県を(山形や富山など)出したいのですが標準フィルタで値に*山*と入力して正規表現にチェックをつけたのですが上手くいきません。
EXCELでは簡単に出てきたのですがどのようにすればいいのかわかりません。
よろしくお願いします。

Aベストアンサー

> *山*と入力して正規表現

正規表現としてなら、こうかも:

.*山.*

Qエクセル 入力済セル数のカウント

とある範囲のセル内になにかしらの入力がある(blankでない)場合。
“入力があるセル”数を数値として別セルに引用する方法はありますでしょうか。

例:
A1 → [太郎]
A2 → [次郎]
A3 → [  ](空欄の意)

B1 → 2
 ※指定範囲内に、入力のあるセルが2つなので「2」

例:
A1 → [太郎]
A2 → [次郎]
A3 → [三郎]

B1 → 3
 ※指定範囲内に、入力のあるセルが3つなので「3」

Aベストアンサー

=COUNTA(A1:A3)
と計算します。

ただし「空欄」が「全く何も記入されていない」のが条件です。
たとえば数式で「""」が計算されていて、「空白に見える」ではダメです。

Qエクセルのオートフィルタで各項目別に自動保存するには?

エクセルでオートフィルタで項目別にフィルタをかけた抽出結果を自動保存していく事は出来ませんでしょうか?

最終的には4、5列にわたる複数条件のオートフィルタによる抽出結果を保存したいのですが
第一列目だけでも項目別に自動的に保存が出来るような方法はありませんでしょうか?
関数では、出来ないようなのでVBAによるものだと思うのですがやり方が分からないです。

具体的な例を簡素化した感じにすると、以下のようなものとなります。
(列の順番は固定的ではありません)

A 発送受付支店(40ポイント)
B 発送日付
C 発送先別(30エリア)
D 受付担当者(15人) 
E 受付番号
F 入金確認

手作業で一つ、一つ保存していく事は可能なのですが
頻繁なデータ更新により変化する度に、多数の項目を保存していくのに効率の良い方法をお教え下さい。
分かりづらいかもしれませんが、宜しくお願い致します。

Aベストアンサー

「フィルタオプションの設定」を使う方法もあるのでは?

別シートに
同じ項目で抽出する内容を設定しておきます。
「データ」「フィルタ」「フィルタオプションの設定」で
「リスト範囲」に現在の表の範囲
「検索条件範囲」に設定した条件の範囲
「抽出範囲」に保存する先の範囲(左上のセルでOK)
でOKして実行する。

数が多い場合はマクロの記録で作業をショートカット化すれば楽です。

抽出する内容の設定の詳細はヘルプをご覧ください。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング