[[20200410105507]]　『SUMPRODUCT関数で値を認識しない』（テレワーク）　ページの最後に飛ぶ

『SUMPRODUCT関数で値を認識しない』（テレワーク）

お世話になります。

E-mailで送られてきたExcelデータがあるのですが、これを集計するためにSUMPRODUCT関数を用いたところ値を認識せず
COUNTIF関数を用いたところ値を認識しています。
正しく集計するにはCOUNTIFやCOUNTIFSでは事足りず困っています。

また、データの入力されているセルを一つ一つ再編集すると
（ダブルクリックまたはF2キー）
再編集されたセルのデータだけはSUMPRODUCT関数でも認識するようになります。
一括でコピーまたは切り取りから貼り付けをしても認識してくれません。

また、同じテンプレートをもとにデータ入力されたものが送られてきますが
すべてのファイルでこの事象が起きているわけではなく、一部のファイルで発生しています。

どうにかそのままSUMPRODUCT関数でデータを認識させる方法はありませんか？
こちらのPCで再現性のあるサンプルデータを作成することができませんでしたので
不具合発生ファイルの当該データ部だけ抜粋したものをこちらにアップロードしました。
https://f.easyuploader.app/eu-prd/upload/20200410110947_3832436576475732554b.xlsx

なにとぞよろしくお願いします。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 たぶん数値が文字列の数字になっているのではないか？
 1列ごとの作業になるが列を選択してデータ-区切り位置を開きそのまま完了ではどうだろうか？
 上記でセルを再編集したのと同じ効果になる。
（ねむねむ） 2020/04/10(金) 11:23

 実際のデータと数式を見ないと確かなことは言えないですが、、

 各配列に1を掛けて数値化した数式に改変したらどうですか？

（半平太） 2020/04/10(金) 11:35

ねむねむ様回答ありがとうございます。

ご指摘の通り数値が文字列の数字になっています。
また、アドバイスの方法で行ごとの一括認識ができました。

ただ、データ形式は文字列の数字のまま維持したいです（先頭ゼロのデータがある、数字だがその値自体の計算はしない）。

やはりひと手間かけないと難しいでしょうか？
一部のデータとはいえ、このデータ不認識が起きるファイルとそうでないファイルの仕分けの手間が煩雑です。
原因がわかるとデータ送信者へ「このようにしてほしい」という提案ができるので、
もしこのようになる原因をご存じでしたらご教示いただけるとありがたいです。
（テレワーク） 2020/04/10(金) 11:38

半平太様回答ありがとうございます。
現在の数式（簡略化してあります）は「=SUMPRODUCT((A1:B23="20160216")*1)」で、認識していません。
「=COUNTIF("A1:B23","20190216")」では認識しています。

（実際は別のデータも参照しますが、データを認識していない現象を再現しているのでこれだけにしています。）
（テレワーク） 2020/04/10(金) 11:44

 ＞ =SUMPRODUCT((A1:B23="20160216")*1)

 =SUMPRODUCT((A1:B23="20160216"*1)*1)

or

 「=SUMPRODUCT((A1:B23=20160216)*1)

 ？？？？
（GobGob） 2020/04/10(金) 11:45

 なんか勘違いっぽいので。訂正。

 =SUMPRODUCT((A1:B23*1=20160216)*1)

 こっちかも。
（GobGob） 2020/04/10(金) 11:47

すみません上記の内容はどちらも20190216です。20160216はここに書いた時の転記ミスです。

GobGoB様具体的なアドバイスありがとうございます。
この方法で認識することができました。実際のデータに適用してまた報告したいと思います。

半平太様、アドバイスを誤認していました。すみません。
（テレワーク） 2020/04/10(金) 11:49

皆様申し訳ありません。
後出しになってしまいますが元データは文字列のみ入力されているセルも含まれています。
認識しないのが文字列の数値だけだったので端折ってしまいました。

文字列が入力されているセルは*1することでエラーになってしまいました。
（テレワーク） 2020/04/10(金) 12:00

 なら、

 =SUMPRODUCT((TEXT(A1:B23,"0;0;0;!0")*1=20160216)*1)

（半平太） 2020/04/10(金) 12:20

　↑

 それはマズかったです。m(__)m

 再考します。
（半平太） 2020/04/10(金) 12:23

 うーん、やっぱいけるかも知れないし、いけないかもしれない。

 どんな文字データがあるかにもよるので、実際のデータを見ないと何とも言えない。

（半平太） 2020/04/10(金) 12:26

 =SUMPRODUCT((A1:B23="20190216")*1)

 ↑ の式が認識しないというのはどうなるんですか？
 サンプルでは「5.0」になってますけど、数が合わないということ？

 だったら同じ「20190216」でも文字列と数値が混在しているのでは？
 それはかまわないんですか？

 簡略化したものではなく実際の数式を提示できないんですかね？

 以上
（笑） 2020/04/10(金) 12:32

 =SUM((IFERROR(A1:B23*1,A1:B23)=20160216)*1)
 と入力してShiftキーとCtrlキーを押しながらEnterキーで式を確定（確定後、式が{}で囲まれればOK）ではどうだろうか？
（ねむねむ） 2020/04/10(金) 12:42

 数値と文字列が混在していてもかまわないのなら
 ↓ だとどうなりますか？

 =SUMPRODUCT((A1:B23&""="20190216")*1)

 あくまでサンプルデータなら、ということですけど。

 以上
（笑） 2020/04/10(金) 12:52　変更 13:00

半平太様検討いただきありがとうございます。
文字列の数値以外は自由入力です。いまのところあるデータではひらがな、カタカナ、漢字で
記号や数値と文字が混ざった入力は見当たりませんが、制限はしていません。

笑様回答ありがとうございます。
サンプルのものは、数式を入力したときの値は0でしたが5つだけこちらでF2またはダブルクリックをした状態です。
サンプルデータの希望はCOUNTIFの結果と同じ値になって欲しいです。

実際使われている数式の例は
=SUMPRODUCT((($F$6:$F$34=1)*(($H$6:$H$34=$S6)+($I$6:$I$34=$S6)))
です。
シートは複数あり、シートごとに34の値は可変
$S6の6は数式入力行で複数行あります。
F列は空白または1～9の数で、ここに文字は入力されません。
9列の集計列があり、ここの値が1～9のものをそれぞれ集計しています（空白は集計されません）
他にも集計列がありますが、SUMPRODUCTを用いているのはこの9列だけで
この9列以外には集計エラーは発生していません。
（テレワーク） 2020/04/10(金) 13:07

ねむねむ様検討いただきありがとうございます。
サンプルデータに適用してみましたが空白になってしまいました。

笑様検討いただきありがとうございます。
サンプルデータに適用してみたところ希望の結果が得られました。
実際のデータに適用してまた報告したいと思います。
（テレワーク） 2020/04/10(金) 13:19

ねむねむ様
こちらが誤記していたのを訂正し適用したところ希望の結果が得られました。
こちらも実際のデータに適用してまた報告したいと思います。
（テレワーク） 2020/04/10(金) 13:38

 なんか納得いかないんですけど･･･

 ＞5つだけこちらでF2またはダブルクリックをした状態です。

 ということは、その 5個は数値ですよね？

 1）=SUMPRODUCT((A1:B23=20190216)*1)　　← が「5」ならわかります
 2）=SUMPRODUCT((A1:B23="20190216")*1)　← は「35」になるのでは？

 提示してもらった実際の数式ですけど、サンプルに対応するのはどの部分なんですか？

 以上
（笑） 2020/04/10(金) 14:03

笑さん
すみません、こちらの理解が追い付いていないかもしれません。

ただ、14:03の笑さんの例示のようになって欲しいのになっていないものがあるので困っている状況です。
さらに、同じテンプレートを用いてちゃんとできているものとできていないものがあるのです。

実際の数式に対応する部分ですが、「($H$6:$H$34=$S6)」「($I$6:$I$34=$S6)」です。S6セルには「20190216」の値があり、S列の値はすべて書式設定で文字列となっています。
また、H列とI列も書式設定で「文字列」にしてあります。
（テレワーク） 2020/04/10(金) 14:31

笑さん、大ヒントをありがとうございます！
数式の検証で見直したところ、認識されないH列とI列のデータが「""」のついていない数値データになっていました。
F2またはダブルクリックをすることで文字列化されて認識される、ということのようです。

「『文字列』と書式設定されているセルに数値を打ち込む」ってどうやってできるのでしょう・・・？
（テレワーク） 2020/04/10(金) 14:43

 よくわかりませんけど ↓ についてだけ言えば
 ＞F2またはダブルクリックをすることで文字列化されて認識される

 表示形式が「標準」のセルに「2019」と入力
 この 2019 は当然ながら数値です
　　　　↓
 そのセルの表示形式を「文字列」に変更
 この時点でも 2019 は数値です
　　　　↓
 F2キーまたはダブルクリックで 2019 は文字列になります。

 こういうこと？

 数値と文字列が混在している（混在する可能性がある）のなら
 ↓ でできませんか？

 =SUMPRODUCT(($F$6:$F$34=1)*(($H$6:$H$34&""=$S6)+($I$6:$I$34&""=$S6)))
                                        ~~~~                ~~~~
 ところで、この式ですけど
 6行目だけにデータがあり
 F6が「1」、H6とI6 が両方とも「20190216」だったらどうなればいいんですか？

 その式だと「2」になりますけど、それでいいんですか？

 以上
（笑） 2020/04/10(金) 15:16

 「2」でいいんだったら ↓ でもいいのでは？

 =SUMPRODUCT(($F$6:$F$34=1)*($H$6:$I$34&""=$S6))

 参考まで
（笑） 2020/04/10(金) 15:23

笑さん再びご検討いただきありがとうございます。
まさに求めていた方法をアドバイスくださりありがとうございます。
実際のデータに適用し、希望の値を得ることができました。

また、ご指摘とおり、その場合に得られる値は「2」でなければいけません。

他の皆様も、貴重なお時間を割いてくださりありがとうございました。
とても勉強になりました。
（テレワーク） 2020/04/10(金) 15:29

コメント返信：

[ 一覧(最新更新順) ]

[[20200410105507]] 『SUMPRODUCT関数で値を認識しない』（テレワーク） ページの最後に飛ぶ

[[20200410105507]]　『SUMPRODUCT関数で値を認識しない』（テレワーク）　ページの最後に飛ぶ