エクセルの項目数抽出法？？

質問

エクセル2000使用です。
とある名簿のデータから、「何区の△▼という条件の人は何人か」を抽出したいのですが、＜セルA＞○●の字(区名）と＜セルB＞△の条件を満たすセルは×個あるか、別に作ってある表に×数が書き込まれるようにするにはどうしたらよいでしょう?

今は
名簿のシート：＜セルC＞オートフィルで日にちを限定”→その中で＜セルA＞○●区を含む”で更に抽出→＜セルB＞をみて条件△、■等を選択、そのセルの数×を数える
別表：○●区｛条件△…×個｝
　　　　　　{条件■…××｝と
×部分を手動で書き込んでいます。　

条件が何種類もあるので、結構大変です。
名簿に記入した時点で自動的に別表へ数値を入れていける方法を教えて下さい。

回答No.14

kazuhiko5681 · ベストアンサー

こんばんわ。私のところで再度コードをチェックしましたが問題なく動きました。まずこのマクロは、シート１のＢ１が変化し、そのセルを脱出した時に必ず走るという動作をします。

まず、もう一度シート１のＡ１に1/1・Ｂ１に1/31と入力してＢ１のセルを脱出してみて下さい。すると必ず２行目にオートフィルターのボタンが表示されますので、表示されているかどうか確認して下さい。

もし、表示されないのであれば、マクロが走っていないことになります。この場合は、ここでは解決できないと思います。私がマクロを組んでいて躓いた時に利用するＶＢＡ友の会というサークルがあります。このサークルは、無料でＶＢＡの組み方などを親切に教えて下さいます。どうしても解決したい時には、そこにご相談してみてはいかがでしょうか。担当者が親身になって懇切丁寧に教えて下さいます。事務局の電話番号をお知らせいたします。

ＴＥＬ　０４３(２７８)２５４８（午後１時～午後１０時まで）

私がこの場で解決できれば一番いいのですが、動かない原因として色々考えられますので、サークルの担当者にお聞きになるのが一番早い解決方法かと思います。

お礼

教えていただいた電話に問い合わせてみます。

本当にいろいろとありがとうございました。

回答No.13

kazuhiko5681

こんばんわ。コードの記述ミスです。申し訳ございません。修正マクロを作ってみました。前回と同様に操作してみて下さい。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Integer
Dim i As Integer
Dim j As Integer
Dim myRange As Range
Dim myAdr As String
Dim myCnt As Integer
Dim myClm As Integer

myRow = Target.Row
If Target.Address <> Cells(1, 2).Address Then Exit Sub
Rows("2:2").AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">=" & Range("A1").Value, Operator:=xlAnd, _
Criteria2:="<=" & Range("B1").Value

For i = 3 To Cells(Rows.Count, 2).End(xlUp).Row
Selection.AutoFilter Field:=2, Criteria1:=Cells(i, 4).Value, Operator:=xlAnd
For j = 3 To Cells(Rows.Count, 5).End(xlUp).Row
With Range("C3:" & Cells(Rows.Count, 3).End(xlUp).Address)
Set myRange = .Find(Cells(i, 5).Value, lookat:=xlWhole)
If Not myRange Is Nothing Then
myAdr = myRange.Address
Worksheets(2).Cells(Rows.Count, 1).Offset(1, 0).Value = Cells(j, 4).Value
Do
Worksheets(2).Cells(Rows.Count, 1).Offset(1, 0).Value = myRange.Value
myCnt = myCnt + 1
Set myRange = .FindNext(myRange)
Loop While Not myRange Is Nothing And myRange.Address <> myAdr
myClm = Worksheets(2).Range("C1:N1").Find(Month(Range("A2").Value)).Column
Worksheets(2).Cells(j, myClm).Value = myCnt: myCnt = 0
End If
End With
Next j
Next i

Rows("2:2").AutoFilter

End Sub

後、Ｂ１に1/31と入力確定後、タブキーではなく他の方法でＣ１にカーソルを移動してみて下さい。多分マクロが走ると思います。

補足

たくさん考えていただいたのにごめんなさい。

やはり何も起こらず・・・~(>_<。)

回答No.12

kazuhiko5681

こんばんわ。早速サンプルマクロを組んでみました。
１.新規ブックを立ち上げ、シートを下記の様に設定する。

シート１
・１行目は空白にしておく(Ａ1とＢ１に抽出条件を入力させるため）
・Ａ２に日付・Ｂ２に住所・Ｃ２に条件と項目名を入力
・Ｄ３・Ｄ４・・・というように市区名を入力
・Ｅ３・Ｅ４・・・というように各条件(I.N.など)を入力

シート２
・Ａ１に市区名・Ｂ２に条件名と項目名を入力
・Ｃ１～Ｎ１に1～12の数字を半角で入力

２.新規ブックを開き、ＡＬＴ＋Ｆ１１キーを押してＶＢＥ画面を表示させ、画面左上のVBAProjectと書かれている下のSheet1をダブルクリックして表示された画面の右側の白い部分に下記のコードをコピー＆ペーストする。

操作方法

１.シート１のＡ１に1/1・Ｂ１に1/31と入力し確定後タブキーを押す。

マクロが走り、シート２のＡ列に市区名・Ｂ列に条件名・Ｃ列に条件名別のカウント数が自動的に表示されます。（月名の下に）

ご不明な点・不都合な点がございましたら、ご遠慮なくお知らせ下さい。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Integer
Dim i As Integer
Dim j As Integer
Dim myRange As Range
Dim myAdr As String
Dim myCnt As Integer
Dim myClm As Integer

myRow = Target.Row
If Target.Address <> Cells(myRow, 2).Address Then Exit Sub
Rows("2:2").AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">=" & Range("A2").Value, Operator:=xlAnd, _
Criteria2:="<=" & Range("B2").Value

For i = 3 To Cells(Rows.Count, 2).End(xlUp).Row
Selection.AutoFilter Field:=2, Criteria1:=Cells(i, 4).Value, Operator:=xlAnd
For j = 3 To Cells(Rows.Count, 5).End(xlUp).Row
With Range("C3:" & Cells(Rows.Count, 3).End(xlUp).Address)
Set myRange = .Find(Cells(i, 5).Value, lookat:=xlWhole)
If Not myRange Is Nothing Then
myAdr = myRange.Address
Worksheets(2).Cells(Rows.Count, 1).Offset(1, 0).Value = Cells(j, 4).Value
Do
Worksheets(2).Cells(Rows.Count, 1).Offset(1, 0).Value = myRange.Value
myCnt = myCnt + 1
Set myRange = .FindNext(myRange)
Loop While Not myRange Is Nothing And myRange.Address <> myAdr
myClm = Worksheets(2).Range("C1:N1").Find(Month(Range("A2").Value)).Column
Worksheets(2).Cells(j, myClm).Value = myCnt: myCnt = 0
End If
End With
Next j
Next i

Rows("2:2").AutoFilter

End Sub

補足

お返事遅くなってすみません。
こりゃすごいことに・・・私、未知の世界にびっくりしています。

＞１.シート１のＡ１に1/1・Ｂ１に1/31と入力し確定後タブキーを押す。
これなんですが、セルの書式は関係ないのでしょうか？単に半角で1/1,1/31といれ、タブキー（私のはノートPCなんですが、Qの横にあるTabで間違いないですよね？）を押したものの、何も起こらず(・・？)

シートもよく参照して間違いなくできたと思ったのですが、どうでしょう。

回答No.11

oresama

確かに、別シート参照（○○!）で行うと、
#N/A
がでてしまいました。
むむむ・・

同一シートではほぼうまくいったとのことですので、
同一シート内で、集計し、
集計結果を別シートから参照するというのは、いかがでしょうか？
=○○!H1
みたいに。

参照先がずれる問題ですが、
私のイメージでは、
E1～H1に抽出パターン1（旭区・チラシ・10月）
E2～H2に抽出パターン2（旭区・タウンP・10月）
・
・
なので、
検索範囲の行を絶対参照にすればよいので、
A1：A15　B1：B15　C1：C15
の行の数字の前に、
$をいれる（A$1：A$15等）
で解決すると思います。

補足

おっしゃるとおり、＄で解決!($の意味も理解できました、うれし(^^)）

ですが更に問題が。
これをI.N.以外の別条件結果を記入するべきセルにコピペ、変更すべきセル地を書き換えても０のしか表示されず・・・
"引数を返します"との答えには何かしら数字が出ているのですが(これは求めたい答えとは違う数字ですが)、決定後のセルにはどこに再現しても0としか出て来ないんです。

セルの書式も確かめたのですが、両者は同じ書式となっています。

念のため、手動で数えてみましたが、1や3となるはずのところも全て結果は0表示でした。

どうしたものでしょう…

回答No.10

oresama

#1・6・9です。
daiju3000さんの配列数式を参考にさせていただきました。

A列　日付データ
B列　住所データ
C列　△とかのデータ

で、

E1に集計したい月　（10月とか）
F1に集計したい区　（千代田区とか）
G1に集計したい条件（△とか）
を入力後、
H1あたりに、以下の配列数式を仕込めばいかがでしょう。

=SUM((B1:B15>=F1)*(C1:C15=G1)*(TEXT(A1:A15,"m月")=E1))

お礼

すみません、それともうひとつ。
お礼欄だけど補足です。

いったんうまくいったと思ったので数式を下にコピーしていったら、B1:B15に当たる所がB2:B16・・・と、参照する（？）数字も一緒にずれて行ってしまいました。

という訳で改良できるでしょうか？

補足

う～～ん、たくさん考えてくださってありがとうございます。
がんばってみました！

同じシート内で、ばっちりできた!と思いきや、実際にはH1に当たる数値を書き込むセルが別シートだからでしょうか、左上にでる計算結果には数字が出るのにEnter後、お目当てのセルには0としか出てくれませんでした。

別シートを参照するには○○!A1…→{例として○○シートのA1｝という書き方で良いんですよね??

いい所まで来てるんですが、うまくできなくて申し訳なくなってきました・・・~(>_<。)

回答No.9

oresama

#1,#6です。
すいませんでした。
私の関数は、
北区滝野川～には対応しますが、
東京都北区滝野川～にはエラーを起こしてしまいました。

結局、住所の文字列から、
～区を抜き出すことにしました。
ロジックは、
～区の直前に文字があるとすれば、
市か都であるという前提です。

=IF(COUNTIF(B1,"*区*"),IF(ISERROR(SEARCH("都",B1,1)),IF(ISERROR(SEARCH("市",B1,1)),LEFT(B1,SEARCH("区",B1,1)),MID(B1,SEARCH("市",B1,1)+1,SEARCH("区",B1,1)-SEARCH("市",B1,1))),MID(B1,SEARCH("都",B1,1)+1,SEARCH("区",B1,1)-SEARCH("都",B1,1))),"")

いつのまにか、住所の列がB列になっているので、
B列に対応させてみました。

もし、△も”△を含む”であれば、
△の列がC列、F1に条件2を入力するとして

=IF(COUNTIF(C1,"*"&$F$1&"*")>0,F1,"")
で抽出できます。

あとは、#1、#6と重複する部分が多いので、
割愛させていただきます。

でも、これだけやるなら、
配列数式のほうが圧倒的に簡単でしたね。

回答No.8

kazuhiko5681

早速補足いただきまして有難うございます。次のように考えてみました。貴方様のご意見をお聞かせ下さい。

・シート１の１行目を抽出条件を入力するための場所に確保する。
・Ａ１(○○より)・Ｂ１(○○まで)に日付を入力する。
・Ｂ１を抜けた時点でマクロが走り、手動で書き込んでいる場所へ、手書きで書き込んでいるデータを入力する。

このマクロを実行させるためには、次の内容がわからないと組むことができませんので再度お知らせ下さい。

・○●区の全部の名前とカウントする条件のすべての名前

お手数をおかけいたしますが、よろしくお願いいたします。

お礼

御礼の欄になってますが、更に補足です。

○●区数が多くてびっくりでしょ？

この名簿と、今回数値を書き込みたいシートは別々なのですが(同ブック内）大丈夫でしょうか。
名簿はシートA,結果はシートBとします。

ちなみに市・区名はシートBのA22/31/40・・・と飛び飛びに、各条件(I.N.など）はシートBのB22～29、月名はシートBのC21～N21に先月までの分が記入済みです。
参考までに・・・。

補足

旭区・阿倍野区・生野区・北区・此花区・城東区・住之江区・住吉区・大正区・中央区・鶴見区・天王寺区・浪速区・西区・西成区・西淀川区・東住吉区・東成区・東淀川区・平野区・福島区・港区・都島区・淀川区・羽曳野市・吹田市・茨木市・高槻市・摂津市・東大阪市・八尾市・堺市・松原市・大東市・柏原市・和泉市・富田林市・豊中市・箕面市・寝屋川市・枚方市・尼崎市・守口市・門真市
※部屋番まで記入されたセルの中から「これらの語句を含む」が条件です

カウント条件
I.N.・チラシ・H.ペッパー・ぱど・タウンP.・関一・R・他

実は各月ごとに、区・市ごとに何を媒体としてアクセスした人が何人いるか、統計を取りたいというわけです。

これでお任せします。宜しくお願いしますm(_ _)mペコリ

回答No.7

daiju3000

#2です。

　DATEVALUE関数の件ですが、実際エクセルのセルに"2001/1/1"というように表示されていますが、それはあくまで視覚的にそう見えているだけです。実際はシリアル値というのが記載されています。DATEVALUE関数は指定した日付文字列をシリアル値に変換する関数です。
※簡単な例をあげると・・・・・・
　A1に日付があったとして、「A1が2001/1/1だったら・・・」というような条件式をたてるとき、『A1=2001/1/1』では通用しません。
　分かりやすく考えると「A1のシリアル値が2001/1/1のシリアル値と同じだったら・・・」という感じにとらえて『A1=DATEVALUE("2001/1/1")』と記入します。

さて、本題です。
あれっ、補足見たら質問のときと列の見出しの順が変わってる。
じゃぁ改めて定義します。
　A2～A2500＝日付
　B2～B2500＝●○区～番地・・・・(住所）
　C2～C2500＝条件△、■等
として、×数は
=SUM((A2:A2500=DATEVALUE("2001/1/1"))*(B2:B2500>="〇●区")*(C2:C2500="△"))
と入力し定番の【CTRL】+【SHIFT】+【ENTER】

しつこいようですが、"2001/1/1"と"〇●区"と"△"は実際使う条件に置き換えてください。

これでもエラーだったら、たぶん条件△のところが引っかかってると思います。
　　・△を含むなのか？
　　・△と同じなのか？
　　・△とあるが実際は条件式が書いてあるのか？

ただいま△ノイローゼです・・・

補足

いろいろ考えてくださって、ほんとにありがとうございます。

ところで実際、＞"2001/1/1"と"〇●区"と"△"は実際使う条件に置き換えてくださいのところなんですが
例えば10月の1ヶ月内でこれこれの条件の物がいくつなのか、統計を取る場合には何と置き換えたらよいのでしょうか・・・

これがナゾで。すみませんm(_ _)mペコリ

ちなみに△については”△と同じ”です。ご安心を(((((*^o^*)♪

回答No.6

oresama

#1です。
今回はあくまでCOUNTIFに拘るため、
やや強引にいきます（笑）
条件1：◆□区に居住（住所に◆□区を含む）
条件2：条件△に当てはまる（△に等しい）
条件3：月次ごとに集計する(たとえば2002/10/1以上2002/10/31以下）

住所がA列、△とか■がB列、日付がC列としたら、
（C列は文字列ではなく、シリアルが前提です）

D列に
=MID(A1,SEARCH("区",A1,1)-2,3)＆B1＆TEXT(C1,"m月")

E1に抽出条件1 （○●区）
F1に抽出条件2　（△）
G1に抽出条件3　（10月）

H1に、=COUNTIF(D:D,RIGHT(E1,3)&F1&G1) 入力

～区は、とりあえず、
都内では、◇◇区の◇◇部分は、23種類の完全ユニーク
ですので、（世田谷区や千代田区の3文字区名も含め）
区の前2文字までで、正誤を判定しました。

もし、重複があれば、ごめんなさい。
また考えます。

回答No.5

imogasi

VBAで考えて見ました。
２条件に簡略化し、簡単な少数例で説明しています。
興味がおありなら見てください。
例題としてA1:B10に下記データを用意します。
（A列）（B列）
a 1
b 1
c 1
a 1
c 1
b 2
a 2
a 1
c 2
b 2
－－－－－－
（１）Sheet1の画面でALT＋F11を押す。VBE画面になる。
（２）ALT+I（挿入）、ALT+M（標準モジュール）を押す。標準モジュールModule1が出来る。
（３）下記をModule1の画面に貼り付ける。
－－－－－－
A列がa,b、B列が1,2の４種類の件数を数えています。
類推して、変えれば、条件を増やしたり、条件値を実際の
ものに変える方法はわかると思います。
プログラムとして
Sub Test01()
Dim 条件1 As String
Dim 条件2 As Integer
Worksheets("sheet3").Activate
d = Worksheets("sheet3").Range _("a1").CurrentRegion.Rows.Count
n1 = 0: n2 = 0: n3 = 0: n4 = 0
' MsgBox d
For i = 1 To d
条件1 = Worksheets("sheet3").Cells(i, 1)
条件2 = Worksheets("sheet3").Cells(i, 2)
Select Case 条件1
Case Is = "a"
Select Case 条件2
Case Is = 1
n1 = n1 + 1
Case Is = 2
n2 = n2 + 1
Case Else
End Select
Case Is = "b"
Select Case 条件2
Case Is = 1
n3 = n3 + 1
Case Is = 2
n4 = n4 + 1
Case Else
End Select
Case Else
End Select
Next i
Cells(12, 1) = n1
Cells(12, 2) = n2
Cells(12, 3) = n3
Cells(12, 4) = n4
End Sub
実行するとA12,B12,C12,D12に、該当件数が出ます。
Cells(i,j)のi,jを適当に変えると、好きなセルへ
件数をセットできます。
3 1 1 2
がセットされます。
組み合わせのためのプログラムがIF文や本件CASE文の
ネスト（CASEの中にまたCASEがある）のため長くなります。そのため本質問に付いては、あまりすっきり行きません。
配列数式（＃２）や条件結合セルを作る（＃１）の回答の方を使われたほうが良いかも知れない。
参考までに上げます。

補足

加えて質問です。
ちょっと私には内容的に難しいので、プログラム中どこが何にあたるのかを教えて下さい。(^^)

・日にちを10/1~10/31に限定するには？
・結果の値が記入されるのは「グラフ」と名前のついたシートなのですが、これを反映させるには?
・”○●区”というのは部屋番まで記入されたセルの中から○●の文字が含まれるセルは、という意味なのですが、この点は大丈夫でしょうか。

以上、宜しくお願いしますm(_ _)mペコリ

回答No.4

kazuhiko5681

初めまして。貴方様のおやりになりたいことは、マクロを組めば簡単に実行することができると思います。コピー＆ペーストするだけで貴方様の思った通りの操作をするサンプルマクロをご希望でしたら組んでみたいと思いますので、下記のことを教えて下さい。

　・名簿データの列構成とセル番地
　・別表の列構成とセル番地
　・貴方様が現在おやりになっている操作方法

お手数をおかけいたしますが、よろしくお願いいたします。

補足

＞・名簿データの列構成とセル番地
A列＝日付(最初の説明書きではテレコになってしまいましたが）
B列＝●○区～番地・・・・(住所）
C列＝条件△、■等　

＞・別表の列構成とセル番地
A列＝●○区(条件欄の分結合）
B列＝条件△、■等の欄
C～N列＝1～12月

＞・貴方様が現在おやりになっている操作方法
名簿のシート：＜セルA＞オートフィルで日にちを限定”→その中で＜セルB＞○●区を含む”で更に抽出→＜セルC＞をみて条件△、■等を選択、そのセルの数×を数える
別表：○●区｛条件△…×個｝
　　　　　　{条件■…××｝と
×部分を手動で書き込んでいます。

いかがでしょうか？