【画像あり】複数条件をカウントできる関数について

質問

タイトルの件、質問いたします。

下記の図１の、Sheet１のB1セルに関数を埋め込みたいと考えています。

関数は、図２の表の中から

　　●Sheet2のC列【分類】が、　１　であり
　　　　　　　　　　かつ
　　●Sheet2のF列【生年月日】が、2011/4/30以前【4/30を含む】であり
　　　　　　　　　　かつ
　　Sheet2のD列【住所1】に、　世田谷　or　横浜　を含む
　　　　　　　　　　もしくは
　　Sheet2のD列【住所2】に、　世田谷　or　横浜　を含む

ものです。
絶対条件：分類が　１、　で　生年月日が　2011/4/30以前の人。
次の条件：絶対条件の中の人で、住所１か住所２に、世田谷、横浜を含む人です。
※住所１か、住所２のどちらかに、世田谷、横浜があればＯＫです。
住所１、住所２のどちらにも、世田谷か、横浜を含んでいてもＯＫです。

人数です。【答えは3人です】【図2、太い赤字】

Sheet1の、A3【世田谷】とA4セル【横浜】　を利用して頂ければ、助かります。

以上、よろしくお願いします。
エクセルバージョン；２００３、２００７です。

回答No.8

KURUMITO · ベストアンサー

回答No7です。

シート2のG2セルに入力する式は次のようにしてください。H2セルまでオートフィルドラッグしてから下方にもオートフィルドラッグします。

=IF(ISERROR(FIND("東京都",D2)),"",IF(ISNUMBER(FIND("区",D2)),LEFT(D2,FIND("区",D2)),IF(ISNUMBER(FIND("市",D2)),LEFT(D2,FIND("市",D2)),IF(ISNUMBER(FIND("郡",D2)),LEFT(D2,FING("郡",D2)),IF(ISNUMBER(FIND("島",D2)),LEFT(D2,FIND("島",D2)),"")))))&IF(ISERROR(FIND("神奈川県",D2)),"",IF(ISNUMBER(FIND("市",D2)),LEFT(D2,FIND("市",D2)),IF(ISNUMBER(FIND("郡",D2)),LEFT(D2,FIND("郡",D2)),"")))&IF(ISERROR(FIND("埼玉県",D2)),"",IF(ISNUMBER(FIND("市",D2)),LEFT(D2,FIND("市",D2)),IF(ISUNUMBER(FIND("郡",D2)),LEFT(D2,FIND("郡",D2)),"")))&IF(ISERROR(FIND("千葉県",D2)),"",IF(ISNUMBER(FIND("市川市",D2)),"千葉県市川市",IF(ISNUMBER(FIND("市原市",D2)),"千葉県市原市",IF(ISNUMBER(FIND("市",D2)),LEFT(D2,FIND("市",D2)),IF(ISNUMBER(FIND("郡",D2)),LEFT(D2,FIND("郡",D2)),"")))))

お礼

大変、高度な式を、ご教授いただき、ありがとうございます。
早速、試してみます。

回答No.7

KURUMITO

回答No5への補足を詳細にありがとうございます。
No3での方法ではそんなに検索項目があるのでしたら対応の仕方を変えることが必要でしょう。
式も複雑になりますのでシート2に作業列をいくつか設けて対応するのがよいでしょう。SUMPRODUCT関数などはデータが多くなれば計算速度も遅くなりますし、範囲を区切って指定することになりますので必ずしも良いものではありません。
初めにシート1ではつぎのようにします。
A1セルには分類の文字を入力し、B1セルにその値を例えば1と入力します。
A2セルには生年月日と入力し、B2セルには2011/4/30のように入力します。
A4セルには住所とでも入力し、A5セルから下方には東京都世田谷区、神奈川県横浜市などど入力します。
A5セルから下行にたくさんのデータが入力されることになりますね。
次にシート2に移って、管理番号から生年月日の項目名がA1セルからF1セルまでに入力されているとします。
作業列では初めに住所1及び住所2からシート1で検索項目となるデータの形でそれぞれの住所を表示させることにします。
G2セルには次の式を入力してH2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ISERROR(FIND("東京都",D2)),"",IF(ISERROR(FIND("区",D2)),LEFT(D2,FIND("市",D2)),LEFT(D2,FIND("区",D2))))&IF(ISERROR(FIND("神奈川県",D2)),"",IF(ISERROR(FIND("市",D2)),LEFT(D2,FIND("郡",D2)),LEFT(D2,FIND("市",D2))))&IF(ISERROR(FIND("埼玉県",D2)),"",IF(ISERROR(FIND("市",D2)),LEFT(D2,FIND("郡",D2)),LEFT(D2,FIND("市",D2))))&IF(ISERROR(FIND("千葉県",D2)),"",IF(OR(ISNUMBER(FIND("市川市",D2)),ISNUMBER(FIND("市原市",D2))),IF(ISNUMBER(FIND("市川市",D2)),"千葉県市川市","千葉県市原市"),IF(ISERROR(FIND("市",D2)),LEFT(D2,FIND("郡",D2)),LEFT(D2,FIND("市",D2)))))

これでほとんどのケースで正しい住所が表示されることでしょうが東京都の場合には西多摩郡、大島などの場合は考慮していません。必要なら式を多少修正することが必要でしょう。最後にG列やH列の表示が正しく行われていることを確認し、誤りがあれば訂正の文字を入力します。

その上で、I2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(C2="",F2=""),"",IF(AND(C2=Sheet1!B$1,F2<=Sheet1!B$2,COUNTIF(Sheet1!$A:$A,G2)+COUNTIF(Sheet1!$A:$A,H2)>0),1,""))

検索の結果をシート1の例えばC2セルに表示させるとしてC2セルには次の式を入力します。

=SUM(Sheet2!I:I)

お示しのデータでしたらC2セルには3が表示されますね。

回答No.6

MackyNo1

>関数を入力しましたが、回答が、　５　人となります。回答は　３人　となるようにしたいです。

数式がOR条件の形になっていません（D列とE列の重複もカウントしています）。

すなわち、以下の数式のように、A3セルとA4セルの４つの検索結果が1つでも成り立つ（すなわちFIND関数の足し算の答えが０より大きい）場合という条件にする必要があります。

=SUMPRODUCT((Sheet2!C2:C100=1)*(Sheet2!F2:F100<="2011/4/30"*1)*((ISNUMBER(FIND(A3,Sheet2!D2:D100))+ISNUMBER(FIND(A4,Sheet2!D2:D100))+ISNUMBER(FIND(A3,Sheet2!E2:E100))+ISNUMBER(FIND(A4,Sheet2!E2:E100)))>0))

回答No.5

KURUMITO

回答1，2，3です。東京都や神奈川県に限られた検索でしょうか？
そのようなことでしたら別の方法も考えられますね。どのような住所が具体的に入力されているのか示してくれませんか？

補足

東京都、神奈川県に限られていません。

下記の４つの都道府県のさらに、次の地方まです。下記の事例のとおりです。
４つの都、県の条件で、約５０～７０ほどあります。
Sheet2の、住所１、住所２には、必ず、下記の住所の、文字列は含んでいます。その後は詳細住所まで
入力されています。

東京都世田谷区　東京都港区　　　　etc
神奈川県横浜市　神奈川県横須賀市　etc
埼玉県所沢市　　埼玉県さいたま市　etc
千葉県市川市　　千葉県松戸市　　　etc

できれば、上記、条件【住所】が、Sheet1のセルに入力されているものを使えれば助かります
→本質問でいう、Sheet1の、A3【世田谷】、A4【横浜】の下に続く形です。
→本質問の、A3、A4セルは次のとおり修正します
A3【世田谷】→修正→A3【東京都世田谷区】
A4【横浜　】→修正→A4【神奈川県横浜市】

大変、お手数ですが、何から良い方法がありましたら、教えて下さい。

回答No.4

imogasi

この質問の特徴は
・条件が複数
・年月日の範囲が条件にある
・世田谷などのあり場所が１列ではなく２列をチェックする
・含む、という条件
・エクセル・バージョンが２００３を使うこともある(2007の場合のほうがCOUNTIFS関数利用でやさしくなろう）
・日付リテラル（2011/4/30）を関数の中で使うときの表し方
　まず、これらは単独の条件を　　関数で判定できる力がないと出来ない。
ーー
質問の書き方は丁寧だが、すべて回答者に丸投げで、質問者は何処までやってみたのか。
２００７でCOUNTIFS関数が使えると思う。回答略。
そんなことも１言もふれてない。日頃の関数の勉強（どんな関数があって、主にどんなことに使うのか）をしてないとこういう場合に役立たない。
それ以前だとSUMPRODUCTを使う。複数条件の課題の常識。
Googleででも「エクセル　複数条件　カウント」（標題どおりだよ）で照会をしてみたのか。
万を越える沢山の記事がある。
別の方法の話だが、明細もフィルタオプションの設定で出せると思う。
ーー
SUMPRODUCTの引数部分は
（１）分類が１はC1:C20＝１
（２）生年月日が2011/4/30以前の人　F1:F20=<"2011/4/30"*1
（３）住所１で横浜か世田谷を含む　COUNTIF(D1：D20,"横浜*")+COUNTIF(D1：D20,"世田谷*")>=1
（４）住所２で横浜か世田谷を含む　COUNTIF(E1：E20,"横浜*")+COUNTIF(E1：E20,"世田谷*")>=1
例データ
001 A 1 世田谷 八王子市 2011/4/30
002 B 1 世田谷 世田谷 2011/5/2
003 C 2 世田谷 世田谷 2011/5/25
004 D 1 横浜市 八王子市 2011/4/30
005 E 2 横浜市 横浜市 2011/5/26
006 F 1 港区 港区 2011/6/1
007 G 2 港区 港区 2011/7/25
008 H 1 江戸川区 世田谷 2011/4/30
009 I 2 墨田区 墨田区 2011/8/1
010 J 1 葛飾区 葛飾区 2011/8/2
011 K 1 横浜市 2011/3/25
012 L 1 相模原市 2011/3/26
013 M 2 千代田区 横浜市 2011/3/27
014 N 1 世田谷区 2011/3/28
ーーー
住所の条件が複雑で式が長くなるので、J列に作業コードを一旦出す。
J2セルに　=(COUNTIF(D2,"横浜*")>=1)+(COUNTIF(D2,"世田谷*")>=1)+(COUNTIF(E2,"横浜*")>=1)+(COUNTIF(E2,"世田谷*")>=1)>=1
OR条件なので４つを結ぶところで＋が入る。
ーー
=SUMPRODUCT((C2:C21=1)*(F2:F21<="2011/4/30"*1)*(J1:J20>=1)*1)
最後の*1は件数を出すときの常道。
ーー
結果
５
ーーーー
質問者の現状では、この課題は、複雑すぎて自力では無理だろう。
操作で
フィルタオプションの設定
を勉強した方が直感的でよいと思う．明細もでる。

回答No.3

KURUMITO

シート1のA3セルに世田谷、A4セルに横浜の文字がありましたね。その場合にはB1セルには次の式で良いですね。

=SUMPRODUCT((Sheet2!C2:C100=1)*(Sheet2!G2:G100<="2011/4/30"*1)*(ISNUMBER(FIND(A3,Sheet2!D2:D100))+ISNUMBER(FIND(A4,Sheet2!D2:D100))+ISNUMBER(FIND(A3,Sheet2!E2:E100))+ISNUMBER(FIND(A4,Sheet2!E2:E100))))

補足

いつも、ご回答ありがとうございます。

関数を入力しましたが、回答が、　５　人となります。回答は　３人　となるようにしたいです。

まず、分類が　１で　生年月日が、　4/30以前の人が　３人

上記３人の中で、　住所１　もしくは　住所　２に

世田谷　もしくは、　横浜　を含む　人は　３人となります。
図２の　赤字の人が　回答【３人】　となります。

以上、宜しくお願いします。
エクセルバージョンは　２００７で　試しました。

回答No.2

KURUMITO

ごめんなさい。分類の条件を忘れていました。

=SUMPRODUCT((Sheet2!C2:C100=1)*(Sheet2!G2:G100<="2011/4/30"*1)*(ISNUMBER(FIND("世田谷",Sheet2!D2:D100))+ISNUMBER(FIND("横浜",Sheet2!D2:D100))+ISNUMBER(FIND("世田谷",Sheet2!E2:E100))+ISNUMBER(FIND("横浜",Sheet2!E2:E100))))

補足

上記、補足の訂正をいたします。自分の確認ミスです。

(Sheet2!f2:f100<="2011/4/30"*1) を

(Sheet2!G2:G100<="2011/4/30"*1)　と書き換えOKでした。

本当に、いつもありがとうございます。

回答No.1

KURUMITO

例えばB1セルには次の式を入力します。

=SUMPRODUCT((Sheet2!G2:G100<="2011/4/30"*1)*(ISNUMBER(FIND("世田谷",Sheet2!D2:D100))+ISNUMBER(FIND("横浜",Sheet2!D2:D100))+ISNUMBER(FIND("世田谷",Sheet2!E2:E100))+ISNUMBER(FIND("横浜",Sheet2!E2:E100))))

補足

何度も何度も問合せ、恐縮です。

エクセルのバージョンが２００２なのですが
事例の、A3（世田谷）、A4（横浜）が、実際は　１００個ほどの、地域があります。
よって、エクセルの式が長くなり、バージョン２では、エクセルに入力する事ができません。

何か良い方法はないでしょうか？？

kurumito様の、一番、最新の回答を利用させていただいております。