人に聞けない痔の悩み、これでスッキリ >>

オートフィルタで絞込みをした後、データ数のカウントをする方法を教えてください。

例)
エステ店の来客状況をエクセルに入力しています。
A列 日付
B列 曜日
C列 氏名
D列 性別
E列 身長
F列 体重

こんな感じで日々の来客数とそのデータを入力しているとします。

ある程度データが蓄積したところでオートフィルタでデータ分析したいと思います。
たとえば曜日別の来客数を調べたい時、オートフィルタで曜日を選択して、絞り込んだ結果のデータ数を数えればいいと思います。
=SUBTOTAL(2,B10:E999)
とやれば「第10行から第999行の間で、絞り込んだ結果、現在表示されている行の数」を表示してくれます。

ここで一歩踏み込んで、
「曜日別に絞り込んだ後、トータル来客数ではなく、各日付ごとの来客数をカウントする方法」を知りたいです。

たとえば先月でしたら
2010/10の毎週月曜日の来客数は
10/4(月) ●人
10/11(月) ●人
10/18(月) ●人
10/25(月) ●人
という内訳がわかるようにしたいのです。

やり方を教えてください。
できれば、
1 出来合いの機能での方法
2 関数を組む方法
3 それでだめならマクロ
の優先順位でおねがいします。

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

A 回答 (3件)

オートフィルタからは離れますが、そのデータ形式ならピボットテーブルがすぐ使えます。


最初は少々取っつきにくいですが、便利ですので、まだ使った事がなければ、やってみて下さい。
そんな事は承知の上ですという事なら、スルーして下さい。
ピボットテーブルで検索すると、親切に解説してくれているサイトが沢山みつかります。
下記は、検索して上の方にあったもので、他にも多々あります。
http://hamachan.info/excel/piboto.html
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。
そーいえばありましたね。ピボット。
これで一発解決です。
ありがとうございました。

お礼日時:2010/11/13 17:40

1 出来合いの機能での方法


 ⇒フィルタのオプション設定やピボットテーブル(操作例は、URLを参考にして下さい)

2 関数を組む方法
 ⇒仮にデータシートがSheet1として、別シートに抽出する方法です。
  別シートのA列にサンプリングしたい日付(データシートのA列と同じ形式)は手動入力、B列に以下の数式で計数できます
  =IF(A1<>"",COUNTIF(Sheet1!A:A,A1),"")
  又、さらに複数条件が必要の場合、以下のような数式になります。
  仮に性別を追加します。
  =IF(A1<>"",SUMPRODUCT((Sheet1!$A$2:$A$999=A1)*($D$2:$D$999="男")),"")
  

参考URL:http://www.eurus.dti.ne.jp/~yoneyama/Excel/kih_m …
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。
こちらの方法も試して見ます。

お礼日時:2010/11/13 17:41

こんにちは!


オートフィルタではなく関数での方法です。

↓の画像のようにSheet1のデータをSheet2に表示するようにしてみました。
Sheet2に曜日の表を一つ作っています。

そして、Sheet2のB1・B2に集計したい日付の開始日と最終日を入力すれば、
B3セルにその期間の人数を!
そして、A5セルに検索したい曜日を入力すればB1~B2の期間内のその曜日のデータを
6行目以降に表示するようにしています。

Sheet1に作業用の列を設けています。
作業列G2セルに
=IF(AND(A2>=Sheet2!$B$1,A2<=Sheet2!$B$2),ROW(),"")
H2セルに
=IF(AND(G2<>"",WEEKDAY(A2)=VLOOKUP(Sheet2!$A$5,Sheet2!$D$1:$E$7,2,0),COUNTIF($A$2:A2,A2)=1),ROW(),"")
という数式を入れ、G2・H2セルを範囲指定し、H2セルのフィルハンドルで下へずぃ~~~!っとコピーします。
(データがなくても構いませんのでしっかり下までコピーしておきます)

そして、Sheet2のB3セルに
=COUNT(Sheet1!G:G)
という数式を!
A6セルに
=IF(COUNT(Sheet1!H:H)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!H:H,ROW(A1))))
B6セルに
=IF(A6="","",COUNTIF(Sheet1!A:A,A6))
という数式を入れ、A6・B6セルを範囲指定し、B6セルのフィルハンドルで下へコピー
これで画像のような感じになります。

尚、日付データはシリアル値が入っているものとします。

以上、参考になれば良いのですが
的外れならごめんなさいね。m(__)m
「オートフィルタ絞込みの後、データ数のカウ」の回答画像2
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。
こちらの方法も試して見ます。

お礼日時:2010/11/13 17:40

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

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

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

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

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

Qエクセルでフィルタ後の条件付き個数のカウント

いつもお世話になっております。

エクセル(2007)で作成した表をフィルタ後に条件に合致する個数をカウントする方法を教えてください。
(例)
A列:名前
B列:出身県のデータがあったとします。

名前でフィルタをかけた後、出身県ごとのカウントを行いたいのですが、条件が無い場合のデータ個数はSUBTOTAL関数を使えばOKなのはわかります。
COUNTIFS関数を使うと、フィルタで隠れた行のデータまでカウントしていまいます。

ピボットを使う(使い方がイマイチわかりませんが・・・)という方法もあると思いますが、今回は同一シート無いに関数でカウントしたいのです。

どなたかよろしくお願いいたします。

Aベストアンサー

=subtotal(3,a2:a3000)
あるいは
=subtotal(3,b2:b3000)
あるいは他の列

※空白セルは個数にカウントされないことに注意して、任意の列を選択

といった数式を、「フィルタで絞り込んだ際に非表示にならない位置」(表の最上行より上とか、最下行より下とか、別シートとか)に記入。その後、A 列で絞り込み、同時に B 列でも絞り込み。つまり、2 列で絞り込み。

以上により、A 列と B 列の AND 条件における個数が数式を記入したセルに表示されているはず。


>ピボットを使う(使い方がイマイチわかりませんが・・・)……

慣れ。やってみれば、意外とできます。たくさん使ってみてください。

Q=SUBTOTAL に =COUNTIF の機能を実装

お世話になります。

掲題の件で ご相談が御座います。


=SUBTOTAL(3,U5:U63) と
=COUNTIF(U5:U63,"完了") を 1つのセルで表現したいのですが
これは不可能でしょうか?

本当は「U列」だけではなく、20~30列全部に そのような機能を持った関数を入れたいのですが
両方を兼ね備えた関数を どなたかご存知ないでしょうか。

ようは、その表全体にフィルタを設定しており、いくつかの条件で抽出した時に「完了」の数が変わるので
現状 自分が思いつく所で考えると別シートに移して「=COUNTIF(●:●,"完了")」を都度入れていくか?
ぐらいしか思いつきません。

・フィルタで抽出しても元の範囲選択はそのまま。
・フィルタで抽出後に見えている選択範囲の「完了」だけを数えて欲しい
・上記2つの条件で1つのセルに数式を入力したい。

この3つの条件が満たされれば「=SUBTOTAL」や「=COUNTIF」にこだわる必要はありません。

どなたか ご存知であればご教示いただけますでしょうか。

説明が不足のようでしたら何度でも補足致しますので何卒宜しくお願い致します。

お世話になります。

掲題の件で ご相談が御座います。


=SUBTOTAL(3,U5:U63) と
=COUNTIF(U5:U63,"完了") を 1つのセルで表現したいのですが
これは不可能でしょうか?

本当は「U列」だけではなく、20~30列全部に そのような機能を持った関数を入れたいのですが
両方を兼ね備えた関数を どなたかご存知ないでしょうか。

ようは、その表全体にフィルタを設定しており、いくつかの条件で抽出した時に「完了」の数が変わるので
現状 自分が思いつく所で考えると別シートに移して「=COUNTIF(●:●,"...続きを読む

Aベストアンサー

またまたまた登場、myrangeです。

質問者がマクロをどの程度使えるのか分からないのでなんですが、
(質問から推測するにあまり使ったことがない?)
先に提示したマクロは関数ではありませんから
データの内容が変るたびに(結果を求めたい時点で)実行しなければいけません。
もし、SUBTOTALのような関数(セルに式としてセットする)にしたければ
以下のようなユーザー定義関数を作ることになります。

'---------------------------------------------- 
Function Ookami(myRange As Range, myStr As Variant) As Long
 Dim Rng As Range
 Dim Cnt As Long
 For Each Rng In myRange
  If Rng.EntireRow.Hidden = False And Rng.Value = myStr Then
    Cnt = Cnt + 1
  End If
 Next Rng
 Ookami = Cnt
End Function
'---------------------------------------------

使い方は、以下のようにします。
関数名は質問者のハンドルネームです。。(^^;;;

■Ookami(セル範囲, 検索文字)■

=Ookami(U5:U63,"完了")

F5~F666の範囲で、表示されてる"連絡済"をカウントしたければ

=Ookami(F5:F666,"連絡済")

要するにふつうの関数と同じような使い方になります。
以上です。
 

またまたまた登場、myrangeです。

質問者がマクロをどの程度使えるのか分からないのでなんですが、
(質問から推測するにあまり使ったことがない?)
先に提示したマクロは関数ではありませんから
データの内容が変るたびに(結果を求めたい時点で)実行しなければいけません。
もし、SUBTOTALのような関数(セルに式としてセットする)にしたければ
以下のようなユーザー定義関数を作ることになります。

'---------------------------------------------- 
Function Ookami(myRange As Range, myStr...続きを読む

Qオートフィルタの選択肢数を数えたいのですが・・・

エクセルについて質問です。

オートフィルタを使用しますと、プルダウン式に
選択肢が一覧で出ますけれど、その数を数えるには
どうしたらよいのでしょうか?

例えば出荷一覧を見る場合など、
出荷日でソートしてみるのですが、
その日に動いたのはいったい何アイテムなのか、
ということを知りたいのです。

プルダウンで出る候補をいちいち数えるしか
方法はないのでしょうか?

どなたか、何とぞご教授ご指導のほど、よろしくお願いいたします。

Aベストアンサー

No.2です。
すみません、質問の意図を誤って解釈していました。
私の知識では、オートフィルタを使用して重複を除いた個数を数える方法は思いつきませんでした。

ちょっとややこしいですがフィルタオプションを使用した以下の方法はいかがでしょうか。

A列に出荷日、C列にアイテムが入力されているとします。
A列のタイトルが「出荷日」となっていると場合、抽出条件としてD1に「出荷日」、D2にフィルタリングしたい出荷日を記入しておきます。

この状態で、A列全体とC列全体を選択して、「データ」>「フィルタ」>「フィルタオプションの設定」の画面で、「検索条件範囲」に$D$1:$D$2を指定し、「重複するレコードは無視する」にチェックを入れ、OKを押します。
こうすると、D2のセルで指定した条件の日付のアイテムが重複なしに表示されるので、下のステータスバーを見れば抽出件数がわかります。

ただ、条件を変える(抽出する日付を変える)には、いちいち「データ」>「フィルタ」>「すべて表示」で元の表示に戻して、検索値(今の例ではD2)の内容を変えて同じことを繰り返さないといけないので、ちょっと面倒です。一連の流れをマクロにすれば簡単かもしれません。

一応、今の例をマクロにしてみたら、以下のようになりました。ボタンを配置してマクロを実行できるようにしてみたら、比較的簡単に抽出ができると思います。

Sub 抽出()
Range("A:A,C:C").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("D1:D2"), Unique:=True
End Sub

Sub 全て表示()
ActiveSheet.ShowAllData
End Sub

No.2です。
すみません、質問の意図を誤って解釈していました。
私の知識では、オートフィルタを使用して重複を除いた個数を数える方法は思いつきませんでした。

ちょっとややこしいですがフィルタオプションを使用した以下の方法はいかがでしょうか。

A列に出荷日、C列にアイテムが入力されているとします。
A列のタイトルが「出荷日」となっていると場合、抽出条件としてD1に「出荷日」、D2にフィルタリングしたい出荷日を記入しておきます。

この状態で、A列全体とC列全体を選択して、「...続きを読む

Qエクセル関数 重複をのぞいて個数を数える方法

いつもお世話になっています
セルに
a  b  c  a  b b
と入力したとき、個数を数える方法は知っていますが、種類が3個だと集計するにはどんな関数を使えばいいのでしょうか。

Aベストアンサー

下記URLの方法は如何でしょう。

「重複しないデータを数える」
http://integer.exblog.jp/2132536/

「リストから重複するデータをはぶいた件数(個数)をカウントする」
http://ameblo.jp/xls/entry-10073848203.html

「重複しないセル数」ユーザー定義関数の説明
http://www.katch.ne.jp/~kiyopon/soft/juhukunai.html

QEXCEL 重複するデータを1としてカウントする方法

以下のような、ある施設の利用者一覧表です。

A列には利用した日にち、B列には利用者のIDがあります。

A   | B
日にち | ID
01/10 | 0001
01/10 | 0002
01/13 | 0001
01/15 | 0003
01/17 | 0001

これをEXCELで延べ人数ではなく、同じIDが複数回出てきても1とカウントしたいのですが方法が分かりません。
上記の表では0001、0002、0003しかありませんので3という数字を求めたいのです。

出来れば作業列を使いたくないのですが、やむをえない場合はしょうがないかなと思います。

宜しくお願いします。

Aベストアンサー

#7です
◆すでに同じ回答をzap35さんがされていました
◆大変失礼しました

◆文字列または数値で途中空白があってもよい方法で別の式(少し長いですが)
=INT(SUMPRODUCT(1/SUBSTITUTE(COUNTIF(A1:A100,A1:A100),0,100)))

◆もし、ID番号が数値であればこんな方法も
=COUNT(1/FREQUENCY(A1:A100,A1:A100))

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[EXCEL]列の項目を何種類かカウントする方法

いつもお世話になっています。Excelで3000件ぐらいのデータを整理したいのですが、普通に合計などはできますが、1列に何種類かのデータが色々な順で混在している場合、それが全部で何個かではなく、何種類かをすぐに計算する方法はありますか?

たとえば、"りんご"・"みかん"・"いちご"・"りんご"・"ばなな"
とそれぞれ入力しているとして、個数は5個だけれども種類は4種類ですよね。この種類の数を出せるとうれしいのですが。どなたか教えてください。
今、使っているのはOffice2003ですが、方法が違うようならOffice2000での方法もわかる方いらっしゃれば教えてください、お願いします。

Aベストアンサー

自信はありませんが、
以下の数式は、1件目のデータを1と返し、2件目移行に出現する重複データを0と返す数式です。

=IF(A1=0,0,IF(COUNTIF($A$1:A1,A1)>1,0,1))

はじめに、上記式のセル番地A1とはデータの始まりがセルA1の場合ですので、ご自身のデータで始まりのセル番地と置き換えて下さい。あとはオートフィルでコピーです。
最後に、ランダムに算出された1と0に対し、SUM関数で合計を求めれば種類(重複しない個数)を出すことができるかと思います。

Q関数で可視セルのデータ個数カウント

Excel2002を使用しています。
オートフィルタで抽出したデータの個数を関数で
カウントしたいのですが、可視セルのみをカウント
出来る関数はあるのでしょうか?

例えば

  A
1 件数
2  2
3  1
4  3
5  1
6 関数


A6セルに何らかの関数を入れておき、
A列のオートフィルタを使って、1を抽出した時は
A6に”2”
3を抽出した時はA6に”1”を表示させたいの
です。
このように、可視セルのデータ個数のみを
カウントする関数はありますでしょうか?

フィルタを使わずにCOUNTIF関数でカウントする
方法、また、可視セルをコピー貼り付け後
カウントする方法は避け、あくまでフィルタで
抽出したデータの個数を瞬時にカウントしたいの
です。
ご教授宜しくお願いします。

Aベストアンサー

=subtotal(2,範囲)

A6だとオートフィルタで消されちゃうので1行目に行挿入してA1にする方が良いです。

QエクセルVBAでフィルタ抽出部分のみのコピー

エクセルVBAで売上帳を作成していますが、オートフィルタでデータ抽出した後、表示されている行のみをコピーして別シートに貼りつけるにはどうすればよいのでしょう?

別シートは指定したセルに値のみの貼り付けをしたいと思っています。

宜しくお願いします。

Aベストアンサー

こんにちは
マクロの記録で作成した一例です。コメントを読んで、適当にアレンジして下さい。

Option Explicit
Sub SampleMacro1()
'
' SampleMacro1 Macro
' マクロ記録日 : 2009/3/13
'
 'フィルター部分
 Selection.AutoFilter Field:=1, Criteria1:="=ほげほげ", Operator:=xlAnd
 '可視セルの選択
 Selection.SpecialCells(xlCellTypeVisible).Select
 '選択範囲のコピー
 Selection.Copy
 'コピー先のシート&セル選択
 Sheets("Sheet2").Select
 Range("A1").Select
 'ペースト
 ActiveSheet.Paste
 'コピー元シートに戻りコピー状態解除
 Sheets("Sheet1").Select
 Application.CutCopyMode = False
 Range("A1").Select
End Sub

外してたら、ごめんなさい

こんにちは
マクロの記録で作成した一例です。コメントを読んで、適当にアレンジして下さい。

Option Explicit
Sub SampleMacro1()
'
' SampleMacro1 Macro
' マクロ記録日 : 2009/3/13
'
 'フィルター部分
 Selection.AutoFilter Field:=1, Criteria1:="=ほげほげ", Operator:=xlAnd
 '可視セルの選択
 Selection.SpecialCells(xlCellTypeVisible).Select
 '選択範囲のコピー
 Selection.Copy
 'コピー先のシート&セル選択
 Sheets("Sheet2").Select
 Range("A1").Select
 'ペース...続きを読む

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい


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

人気Q&Aランキング