
winXP,office2002
エクセルにて、作業一覧シートから「担当者」と「〆切日」別で進捗一覧を作りたいとおもい、
SUMPRODUCT関数を使用したところ、全て0を返してしまいます。
A B
1 担当者 山田
2 〆切日 作業数
3 1/7(月) =SUMPRODUCT(((別シート!$F$4:$F$109)="山田*")*((別シート!$G$4:$G$109)=$A3))
4 1/8(火) =SUMPRODUCT(((別シート!$F$4:$F$109)="山田*")*((別シート!$G$4:$G$109)=$A4))
5
日付の書式はm/d(aaa)です。
別シートF行は担当者名が入力されております。空欄もあります。
別ソートG行は日付m/d(aaa)が入力されております。空欄もあります。
担当者は連名の場合もあるため、"山田*"にしております。
なお試しに、countifでそれぞれ担当者と〆切日の単条件をカウントすると、正常に結果を返します。
いろいろと検索し、試してみましたが、どうしても0になってしまいます。
どこが間違っているのでしょうか?
よろしくお願いします。
No.2ベストアンサー
- 回答日時:
>比較演算子ではワイルドカードは使用できないからです。
というの点に関しては、回答No.1様の仰る通りなのですが、
FIND("山田",別シート!$F$4:$F$109)
としたのでは、担当者名が例えば「小山田」となっているデータに関しても、「山田」のデータとして一緒にカウントされてしまいます。
ですから、
>担当者は連名の場合もあるため、
という場合には、連盟となっている複数の担当者名の間の区切り方が、どの様になっているのか、という事が重要になります。
例えば、「山田さん」と「佐藤さん」の連名ですと、「山田,佐藤」の様に「,」で区切られているのか、或いは、「山田 佐藤」の様に半角のスペースで区切られているのか、等々によって、若干異なる関数を使用せねばなりません。
例えば、複数の担当者名の間が必ず「,」で区切られている場合には、B3セルに入力する関数を次の様なものにされると良いと思います。
=IF(OR(ISERROR(1/DAY($A3)),$B$1=""),"",SUMPRODUCT(ISNUMBER(FIND(","&$B$1&",",","&別シート!$F$4:$F$109&","))*(別シート!$G$4:$G$109=$A3)) )
又、複数の担当者名の間が必ず半角のスペースで区切られている場合には、B3セルに入力する関数を次の様なものにされると良いと思います。
=IF(OR(ISERROR(1/DAY($A3)),$B$1=""),"",SUMPRODUCT(ISNUMBER(FIND(" "&$B$1&" "," "&別シート!$F$4:$F$109&" "))*(別シート!$G$4:$G$109=$A3)))
又、複数の担当者名の間が、半角のスペースで区切られている場合と、全角のスペースで区切られている場合と、「,」(半角)で区切られている場合と、「,」(全角)で区切られている場合と、「/」(半角)で区切られている場合と、「/」(全角)で区切られている場合の6パターンがある場合には、B3セルに入力する関数を次の様なものにされると良いと思います。
=IF(OR(ISERROR(1/DAY($A3)),$B$1=""),"",SUMPRODUCT((ISNUMBER(FIND(" "&ASC($B$1)&" "," "&ASC(別シート!$F$4:$F$109)&" "))+ISNUMBER(FIND(","&ASC($B$1)&",",","&ASC(別シート!$F$4:$F$109)&","))+ISNUMBER(FIND("/"&ASC($B$1)&"/","/"&ASC(別シート!$F$4:$F$109)&"/"))+>0)*(別シート!$G$4:$G$109=$A3)))
No.5
- 回答日時:
回答No.3様は
>(FIND = 1) という論理式による論理値を使えば、後ろのほうの区切り方については何も考える必要がないということが重要になります。
と仰っておられますが、やはり区切り方は考えに入れなければなりません。
何故なら、
=SUMPRODUCT((FIND("山田",別シート!$F$4:$F$109&"山田")=1)*(別シート!$G$4:$G$109=$A3))
や
=sumproduct((countif(indirect("別シート!$F"&row($4:$109)),"山田*")=1)*(別シート!$G$4:$G$109=$A3))
や
=SUMPRODUCT((FIND("山田",別シート!$F$4:$F$109&"★山田")=1)*(別シート!$G$4:$G$109=$A3))
では、連名が「佐藤、山田」となっている場合には、「山田」さんのデータとして扱われませんし、仮に、「山田屋」という担当者名があったりしますと、「山田屋」さんのデータも「山田」さんのデータとして扱われてしまいます。
まあ、「山田」さんであれば、「山田■」という形式の名前は少ないと思われますが、「林」さん、「小林」さん、、「林田」さん、とか、「藤」さん、「内藤」さん、「藤本」さん、という組み合わせも考えられます。
>なお、この数式において、「山田*」→「*山田*」と書き換えれば、先頭が山田でないケース(「小山田」とか「佐藤さん、山田さん」とか)も条件に該当するものとして数えます。
その場合、「小山田」さんや「山田屋」さんが担当者に含まれてさえいますと、「山田」さんが含まれていないデータであっても、「山田」のデータとして一緒にカウントされてしまいます。
No.4
- 回答日時:
No.3 です。
ごめんなさい、ミスをしたので、No.3 の 1 本目の数式を次のとおり訂正させてください。=SUMPRODUCT((FIND("山田",別シート!$F$4:$F$109&"★山田")=1)*(別シート!$G$4:$G$109=$A3))
★でなくても、好きな文字でいいです。
これがないと、「別シート」の F 列が未入力かつ「別シート」の G 列が A3 セルの値に等しい場合に、「1」と数えてしまうので、少し問題です。失礼しました。
No.3
- 回答日時:
>>比較演算子ではワイルドカードは使用できないからです。
というの点に関しては、回答No.1様の仰る通りなのですが、
FIND("山田",別シート!$F$4:$F$109)
としたのでは、担当者名が例えば「小山田」となっているデータに関しても、
「山田」のデータとして一緒にカウントされてしまいます。
という点に関しては、回答 No.2 さんのおっしゃるとおりなのですが、
>連盟となっている複数の担当者名の間の区切り方が、どの様になっているのか、
という事が重要になります。
ということが言えるのは、ISNUMBER 関数が返す論理値を使って立式した場合に限られるのであって、(FIND = 1) という論理式による論理値を使えば、後ろのほうの区切り方については何も考える必要がないということが重要になります。
=SUMPRODUCT((FIND("山田",別シート!$F$4:$F$109&"山田")=1)*(別シート!$G$4:$G$109=$A3))
上式では、「山田」が 2 回出てくることに注意してください。2 回目の「山田」を付けていない数式の場合は、「別シート」の F 列に「山田」という文字列が含まれないセルが 1 つでも存在すると、エラーになってしまいます。
ワイルドカードが使えるのは、一部の関数だけです。多くの関数または数式においては、使えません。
COUNTIF で複数条件とすることは、一応、次のようにして可能です。つまりワイルドカードが使えます。ただ、COUNTIF と配列という組み合わせの相性の問題で、ROW と INDIRECT を入れるという工夫をしているので、割と難しい数式です。
=sumproduct((countif(indirect("別シート!$F"&row($4:$109)),"山田*")=1)*(別シート!$G$4:$G$109=$A3))
なお、この数式において、「山田*」→「*山田*」と書き換えれば、先頭が山田でないケース(「小山田」とか「佐藤さん、山田さん」とか)も条件に該当するものとして数えます。
ワイルドカードでできる方法も教えていただきありがとうございます。
便利ですが、よく考えて使わないと、混乱しますね(笑)
いろいろと勉強になりました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Visual Basic(VBA) エクセルについて教えてください。 3 2023/06/28 09:11
- Excel(エクセル) Excelについて 1 2023/03/06 10:26
- Excel(エクセル) 添付写真上のExcelシートのように時間と曜日ごとに担当者が振り分けられているシートがあります。 例 1 2023/03/08 13:02
- Excel(エクセル) エクセルVBA VLOOKUPを使ってのカウント作業 2 2023/02/19 09:03
- その他(パソコン・スマホ・電化製品) EXCELのSUMPRODUCT関数について 2 2022/09/21 17:30
- Excel(エクセル) Excel(エクセル)でフィルター抽出後、非表示の行を計算しないで、合計を算出する方法 【内容】 添 4 2023/01/30 17:17
- Excel(エクセル) EXCEL 関数を教えてください。(A列の同じ値が複数ある場合vlookupで出来ますか) 4 2022/12/07 20:54
- Access(アクセス) アクセス where句を使用して複数条件抽出をするには 2 2022/08/29 13:24
- Excel(エクセル) Excel セルに入っている日付を参照して、別シートのリストを表示させたい 1 2022/04/12 17:02
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
快活CLUBについて 私用で使う書...
-
Microsoft Officeを2台目のPCに...
-
別シートの年間行事表をカレン...
-
英数字のみ全角から半角に変換
-
Office2021を別のPCにインスト...
-
エクセルで特定のセルの値を別...
-
MSオフィス2013にMS365が上書き...
-
大学のレポート A4で1枚レポー...
-
エクセルVBAで1004エラーになり...
-
Office 2021 Professional Plus...
-
excel2010の更新プログラムにつ...
-
office365って抵抗感ないですか?
-
Microsoft Office Homeインスト...
-
エクセル:一定間隔で平均値を...
-
Officeを開くたびの「再起動メ...
-
マクロ自動コピペ 貼り付ける場...
-
outlookのメールが固まってしま...
-
別ブックへのエクセルマクロの...
-
Microsoft365搭載Windows11PCへ...
-
Excel テーブル内の空白行の削除
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Microsoft Officeを2台目のPCに...
-
Office2021を別のPCにインスト...
-
大学のレポート A4で1枚レポー...
-
Microsoft365について
-
outlookのメールが固まってしま...
-
英数字のみ全角から半角に変換
-
Office 2021 Professional Plus...
-
エクセルVBAで1004エラーになり...
-
エクセルで特定のセルの値を別...
-
MSオフィス2013にMS365が上書き...
-
Microsoft Formsの「個人情報や...
-
【Excel VBA】PDFを作成して,...
-
別シートの年間行事表をカレン...
-
office2019 のoutlookは2025年1...
-
マクロ自動コピペ 貼り付ける場...
-
表の作成について
-
office365 回復できない。
-
マイクロソフト 一時使用コード...
-
Teams内でショートカットって貼...
-
Microsoft Office Homeインスト...
おすすめ情報