エクセルの関数SUMPRODUCTとFINDを使ったデータの数え方
- エクセルの関数SUMPRODUCTとFINDを使って、特定の条件を満たすデータの数を求める方法について教えてください。
- 質問者は、あるシートにおいて特定の条件を満たすデータの数を数えたいと考えています。具体的な条件は、B列にあるデータの任意の文字であって、かつD列にあるデータの任意の文字も含むセルの数です。
- 関数SUMPRODUCTを使用することで、条件を満たすデータの数を求めることができます。関数FINDは、ある文字がセルに含まれているかどうかを判定するために使用されます。別のシートに結果を表す方法についてもアドバイスを求めています。
- ベストアンサー
関数の使い方SUMPRODUCT？DCOUNT？
エクセルの関数の使い方についてアドバイスをお願いいたします あるシート（仮にSheetAとする）において B列にあるデータの任意の文字（仮にCとする。文字位置は固定ではない）であって、且つ D列にあるデータの任意の文字（仮にEとする。文字位置は固定ではない）の双方を満たすセルの数を数えて、さらにその結果は同じファイルの中にある別のシート（仮にSheetFとする）に表したいと思います これについては関数SUMPRODUCTと関数FINDを使って導きたいと考えておりました。 =SUMPRODUCT(ISNUMBER(FIND("C",B1:B100))*ISNUMBER(FIND("E",D1:D100))) として同じシート上に結果を表すことはできたのですが、別シートに表すところで、 つまづいてしまいました（おそらく初歩的なミスと思うのですが・・・。） 別のシート名を表す部分をどこに入力したらいいのかアドバイスいただきたく お願いいたします それとも別の関数（DCOUNTなど）を使用したほうがよりベストなのか迷っています DCOUNTの使い方も精通しておらず苦慮しております 調べてみましたが前例を見つけだす事ができませんでした 簡単なことでしたらすみません エクセル2003です どうぞアドバイスのほど よろしくお願いいたします
- akwb
- お礼率75% (18/24)
- オフィス系ソフト
- 回答数5
- ありがとう数4
- みんなの回答 （5）
- 専門家の回答
質問者が選んだベストアンサー
シート名は、 =SUMPRODUCT(ISNUMBER(FIND("C",Sheet1!B1:B100))*ISNUMBER(FIND("E",Sheet1!D1:D100))) のように入力すればOKです。 --- データベース関数を使うのであれば、DCOUNTAを使います。 データベース関数を使う場合、 データが入っている列の1番上のセル（この例なら、B1とD1）に、 データの種類の名前（例えば「住所」「氏名」等）が入力されている必要があります。 次に、検索条件を書きます。 ここでは、E1～F2に 住所 氏名 C E と埋めます。 最後に結果を出したいセルに =DCOUNTA(B1:D100,"住所",E1:F2) と入れればOKです。 別シートの表から検索する場合は、SUMPRODUCTの例同様、シート名!を加えてください。 データベース関数については、例えばこのページが参考になります。 http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/count.htm#dcounta --- どちらの方法を使うかですが、 大量のデータを検索する場合、 配列形式の関数（SUMPRODUCT等）は動きが極めて遅くなりますので、 検索に特化したデータベース関数を使うのはとても有益です。 ただ、 ・条件を書くためのセルを作らなければいけない ・データの一番上にデータの名前を埋めなければいけない ・知らない人がメンテナンスしづらい などデメリットもあります。 そんなに大きくない表であれば、使い慣れたSUMPRODUCT関数を使うのも良いかと思います。
その他の回答 (4)
- imogasi
- ベストアンサー率27% (4737/17068)
2列のそれぞれの条件での探索は、エクセルでは（他のデータベース言語ではその備えがあるが）難しい。 だから２列を結合して、１列のデータに持っていって（そのため作業列は要るがやむをえない）、考える。 ーー Ｓｈｅｅｔ2で第2行目から(Sheet2は特別意味なし） Ｃ列 Ｄ列 Ｅ列（何処でも空き列）でよい） asdf xyzu asdf......xyzu...... dfg sxuf dfg.......sxuf...... s xyuf s.........xyuf...... dfg hude dfg.......hude...... ersdggg sxyzder ersdggg...sxyzder... t sdft t.........sdft...... s adre s.........adre...... dfg dfg dfg.......dfg....... xsdfgh wexyz xsdfgh....wexyz..... Ｅ２の式は =C2&REPT(".",10-LEN(C2))&D2&REPT(".",10-LEN(D2)) 後尾に埋める文字をコンマにしているのは、桁数が良くわかるようにしている。スペースだとわかりにくいが、スペースのほうが本来良い。 Ｃ，Ｄ列とも最大１０桁以内と仮定した場合の式。両列で最大桁が違っていても式の定数が変わるだけ。 仮に定桁式結合と呼ぼう。 これで Ｃ列はSdを含み、かつＤ列はxyzを含む行の数を求める 空きセルに =COUNTIF(E2:E10,"*sd*xyz*") で上記例では３ ワイルドカードが使える。 これで良いと思うが、色んなケースを十分考えていないので、質問者がチェックしてください。 ーー 別シートに式を入れるなら =COUNTIF(Sheet2!E2:E10,"*sd*xyz*") この部分は常識的なことです。
- MackyNo1
- ベストアンサー率53% (1521/2850)
参考までに 部分一致でDCOUNTA関数を利用する場合は、No1の回答に示されている条件や皆さんから紹介されているリンクの情報では、正しい個数を返さない可能性があります。 DCOUNTAなどのデータベース関数はバージョンによる違いがあり、Criteriaに入力する条件によって、前方一致で検索した入り完全一致で検索する可能性があります。 http://www.kenzo30.com/ex_kisotyu/ex_ks_tyukyuxb3_2_1.htm 例えば「C」を含むという条件なら、ワイルドカードを使って「'=*C*」(アポストロフィーの後に＝、アスタリスク、文字列、アスタリスク）と入力する必要があります。 ちなみに後方一致（最後がCで終わるセルの個数）なら「'=*C」と入力することになります。
- mshr1962
- ベストアンサー率39% (7418/18948)
=SUMPRODUCT(ISNUMBER(FIND("C",シート名!B1:B100))*ISNUMBER(FIND("E",シート名!D1:D100))) のようにセル範囲の前にシート名を設定するだけです。 DCOUNT関数の使い方は下記を参照 http://excel.onushi.com/function/dcount.htm
お礼
早々にありがとうございます ご丁寧にありがとうございます みなさんすぐにアドバイスいただき、嬉しいかぎりです 早速作成してみます
=SUMPRODUCT(ISNUMBER(FIND("C",SheetA!B1:B100)*FIND("C",SheetA!D1:D100))+0)
お礼
早々にありがとうございます 夜中にもかかわらずありがとうございます 実施してみます
関連するQ&A
- 文字列AかBを含むセル数をSUMPRODUCT関数で求める場合
文字列AかBを含むセル数をSUMPRODUCT関数で求める場合 エクセル2000です。 A1:A10のセル範囲だとして =SUMPRODUCT(ISNUMBER(FIND({"A","B"},A1:A10))*1) とすると、同一セルにA、Bの両方があった場合、重複してカウントされてしまいます。 重複を除外するために、 =SUMPRODUCT(ISNUMBER(FIND({"A","B"},A1:A10))*1)-SUMPRODUCT(ISNUMBER(FIND("A",A1:A10))*ISNUMBER(FIND("B",A1:A10))) と、重複してカウントされた数から両方が存在するセルの数を減じて求めましたが、以下のようなより簡素な式でも求められことがわかりました。 =SUMPRODUCT((ISNUMBER(FIND("A",A1:A10))+ISNUMBER(FIND("B",A1:A10))>=1)*1) この式でなぜAB両方があるセルが重複カウントされないのかどうもわかりません。 ご教示いただければ幸いです。 なお、SUMPRODUCTではなく =SUM(COUNTIF(A1:A10,{"*A*","*B*"}))-SUM(COUNTIF(A1:A10,{"*A*B*","*B*A*"})) でもできることは存じておりますが、今回はSUMPRODUCTの疑問として質問いたしました。
- ベストアンサー
- その他MS Office製品
- 【SUMPRODUCT】について
下記リストと条件で価格を算出したいのですが、A列の「7」以外の数字まで反映された値がかえってきてしまいました。適切な関数を使用しているかも不明です。 アドバイスのほどお願いいたしますm( _ _)m --------------------------------------------------------------- セル A列 Ｂ列 Ｃ列 Ｄ列 Ｅ列 1 5 A リンゴ店 TEL \100 2 5 B ミカン店 FAX \200 3 7 C リンゴ店 TEL \100 4 7 A ミカン店 FAX \200 5 7 B リンゴ店 TEL \100 6 7 C ミカン店 FAX \200 --------------------------------------------------------------- ＜条件＞ A列 「7」 B列 「Ｂ」と「Ｃ」 C列 「ミカン」を含む Ｄ列 「FAX」以外 ＜作成した数式＞（かえってきた値は上の条件とは相反したデータで、恐らくA列の「7」以外の数字も含んでいるようです・・） =SUMPRODUCT((A$2:A$10=7)*(B$2:B$10="B")*(B$2:B$10="C")*(E$1:E$10)) +SUMPRODUCT((A$2:A$10=7)*ISNUMBER(FIND("ミカン",C$1*C$10))*(E$1:E$10))+SUMPRODUCT((A$2:A$10=7)*ISERROR(FIND("FAX",D$1:D$10))*(E$1:E$10)) 以上、質問が長くなってしまい申し訳ありません。 ご教授のほどどうぞ宜しくお願いいたします。
- 締切済み
- オフィス系ソフト
- INDEXとSUMPRODUCT関数が分かりません
INDEXとSUMPRODUCT関数で以下の作業を行おうと思っています。 Sheet２に以下の関数を入れたのですが、どこかがおかしいです。 よろしければ、間違いを教えてください。 Sheet2のB2に入れている関数は =INDEX(Sheet1!$A$2:$A$4,SUMPRODUCT((Sheet1!$B$2:$D$4=A2 )*ROW(Sheet1!$B$2:$D$4))) です。
- ベストアンサー
- オフィス系ソフト
- SUMPRODUCT関数について
SUMPRODUCT関数について いつもお世話になっています。 以前こちらでSUMPRODUCT関数を教えていただき大変統計がしやすくなり、 仕事が効率化しました。ありがとうございます。 ところで、SUMPRODUCTだと思うのですが、計算式がうまく組み立てられないのでご質問させていただきます。 A列に部門名、B列に実施日が入力されているエクセルシートがあり、 部門ごとの実施件数を出したいのです。 SUMPRODUCT（（A2:A100="○部門"）＊（CAUNTA(B2:B100)）） としてみましたがうまくいきません。 たぶん ＊（CAUNTA(B2:B100)） の部分がいけないのだろうとは思うのですが、どう変化させればいいのでしょうか？ 関数の選択が間違っているのかもしれない、とは思うのですが、適切なものが分かりません。 ご教授お願いします
- ベストアンサー
- オフィス系ソフト
- ＳＵＭPRODUCTでエラーになってしまいます。
関数を少しづつ勉強しながら集計表作成の業務を同時進行しています。 初心者です。 今作ってるのは複数条件の合計金額を求めたくて、 SUMPRODUCT(('4月'!D4:D38="*"&$C135&"*")*('4月'!$H4:H38=C58)*('4月'!E4:E38)) を作ったのですが、どうやらSUMPRODUCTだと「～を含む」の機能が使えないみたいなので 色々調べて SUMPRODUCT((ISNUMBER(FIND($C$135,'4月!$D$4:$D$38)))*('4月!H4:H38=C61)*('4月!E4:E38)) が完成しました！ そして、更に右にコピーするとシートの月も５月、6月・・と変わるようにしたかったので 前回ここで教えてもらった関数を参考に SUMPRODUCT((ISNUMBER(FIND($C$135,INDIRECT(MOD(COLUMN(C1),12)+1&"月!$D$4:$D$38))))*(INDIRECT(MOD(COLUMN(C1),12)+1&"月!H4:H38=C61))*(INDIRECT(MOD(COLUMN(C1),12)+1&"月!E4:E38))) を作りました。 でも・・・エラーになってしまうのです。 セルにエラー表示がされるのではなく正しく直るまで「修正しなさい」って出るバージョンの エラーです。。。。 もう何がいけないのかさっぱりで・・・・・ あと一番上の原型の関数からなのですが、セル番地を列で指定したいのですが、 D:DとかH:Hにするとセルのエラーになってしまいます。 こちらも何でエラーになるのかわからなくて困っています。 どなたか詳しい方教えて下さい。
- ベストアンサー
- オフィス系ソフト
- SUMPRODUCT関数で困っています
エクセル2007にて、SUMPRODUCT関数を使ってデータ集計をしたいが 下記の現象で困っています。 SUMPRODUCTの特徴として参照先(sheet1)の指定範囲 「($B$2:$E$6)の範囲を($B$2:$G$20)」を変更すると SUMPRODUCT関数がn/aになり値が表示できない。 (やりたいこと) sheet1に元データが入力されています。 A B C D E 1 NO 社名 商品 購入日 金額 2 01 A商事 肉 1/3 50円 3 02 B電気 野菜 1/10 70円 4 02 B電気 肉 1/12 50円 5 03 C工業 魚 1/20 60円 6 03 C工業 肉 1/30 50円 sheet2はsheet1より必要なデータを、NOをキーにして、 社名・商品・金額を VLOOKUP関数にて取ってきて表示しています。 A B C D E 1 NO 社名 商品 金額 2 01 A商事 肉 50円 3 02 B電気 野菜 70円 4 02 B電気 肉 50円 5 03 C工業 魚 60円 6 03 C工業 肉 50円 6 03 C工業 鉄 90円 sheet3はsheet2より必要なデータを、NOをキーにしてSUMPRODUCT関数にて取ってきて表示しています。 次の式をB1に入力しています (=SUMPRODUCT((sheet2!$A$2:$A$6=A2)*(sheet2!$B$2:$B$6=B2<>"鉄")*(sheet2!$D$2:$D$6=D2)) A B C D E 1 NO 金額 2 01 50円 3 02 120円 4 03 110円 sheet3はsheet2より必要なデータを、NOをキーにしてSUMPRODUCT関数にて取ってきて表示しています。 次の式をB1に入力しています (=SUMPRODUCT((sheet2!$A$2:$A$6=A2)*(sheet2!$B$2:$B$6=B2"鉄")*(sheet2!$D$2:$D$6=D2)) A B C D E 1 NO 金額 2 03 90円
- 締切済み
- その他MS Office製品
- エクセルで文字列検索の関数
エクセルのワークシート関数で質問です。 「A1セルに、文字列、A、B、Cの何れかを含み、かつCDを含まない」ことを調べる関数です。 素直に、 =AND(ISNUMBER(FIND("A",A1)),ISNUMBER(FIND("B",A1)),ISNUMBER(FIND("C",A1)),NOT(ISNUMBER(FIND("CD",A1)))) と長ったらしく書けば取得できることはわかるのですが、もっと簡潔なやり方がありそうな気がします。 どうか教えてください。
- ベストアンサー
- その他MS Office製品
- SUMPRODUCT関数について
アンケートの集計をするにあたって、 SUMPRODUCT関数を使いました。 シートが2つあります。 【データ】シート・・・アンケートのデータ A:No. B：データ 1 0 2 1 3 2 4 3 5 空欄が入っている ・ ・ 【集計】シート・・・B列の空欄、1～3の個数を集計する B列に数式 =SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000=$A4)) を入れました。 A B 0 2 ・・・・※ 1 1 2 1 3 1 空欄 2・・・・※ ０（ゼロ）と空欄を区別しないで同じと計算してしまいます。 データに、ゼロと空欄が混在している場合、SUMPRODUCTは使えないのでしょうか？ この場合、どの数式を使うのがベストでしょうか？ 分かりにくくて申し訳ありませんが、 宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- SUMPRODUCT関数とROUND関数を一緒に
幾度どなくお世話になっております。 ４月からEXCEL中心の仕事になりましたが、まだまだ超初心者です。 今回は、ブック中の結合_OKシートのPセル列の文字がブック中の別シートのA列に記載している文字と一致したらその数分の結合_OKのAOセルに記載している金額を合計した値を別シートのB列に、整数値で表示させようとしています。 結合_OKのAOの金額のセルの数字は小数点3桁表示になっています。 ROUND関数を式に入れなければ、以下の式で、エラーは出力されず、別シートのB列に金額は出せましたが、この式にROUND関数を入れる為に色々とやってみましたが、エラーが表示されてしまっています。 =SUMPRODUCT((結合_OK!$P$3:$P$1000=$A2)*結合_OK!$AO$3:$AO$1000) 以下ではエラーが出ます =ROUNDDOWN(SUMPRODUCT((結合_OK!$P$3:$P$1000=$A2)*結合_OK!$AO$3:$AO$1000)) 因みに、結合_OKシートのPセル列には、空白のセルが混在している為、ブック中の別シートのA列に記載している文字は、手作業で作成しています。 別シートのA列、B列は以下のような記載です。 ID 合計金額 22NM5001 29470 22NM5002 11660 22NM5005 12045 22NM5007 2200 22NM5011 5500 22NM5012 16280 大変、お手数ですが、SUMPRODUCTとROUND関数を一緒に記載する方法を教えて頂けると大変助かります。 また、上記の式についても、SUMPRODUCT関数が最適なのか分からず使っています、こちらについてもご教示頂けると大変幸いです。 分かりずらい説明ではありますが、よろしくお願いいたします。
- ベストアンサー
- Excel（エクセル）
- Excel 特定の文字を含む時に隣セルを表示した
D列とE列は一覧データです。 A列とB列の文字列を含むものをE列で探して、見つかったらE列の隣のD列の文字列をC列に表示する関数を組みました。 ・関数 =SUMPRODUCT((ISNUMBER(FIND(A1,$E$1:$E$5)))*(ISNUMBER(FIND(B1,$E$1:$E$5)))*$D$1:$D$5) C列の数値が倍になっている箇所があるのですが理由がわかりません。 わかる方がいらっしゃいましたら教えていただきたいです。 例 A列 B列 C列 D列 E列 ○○ ×× 111 111 ○○★×× ○○ ×× 111 112 △△★◎◎ ○○ ×× 111 113 ▽▽★☆☆ △△ ◎◎ 112 114 ■■★◎◎ △△ ◎◎ 112 115 ●●★××
- ベストアンサー
- その他([技術者向] コンピューター)
お礼
早々にありがとうございます シート名はそこに入力すればよかったのですね やはりDCOUNTAだと、データーの種類の名前を入れないとだめなんですね その方法は、できればとりたくないと考えておりました 早速やってみます 大変ありがとうございました