SUMIF関数で検索列のトリミングについて

よろしくお願いします。

例えば次のような式（=SUMIF(B:B, F2, C:C)）で、
B列には「0003」とか「0024-0014」とかの値が入っているとします。

それをB列の下4桁を検索対象に、たとえば「0024-0014」なら「0014」が検索対象で、
「0003」ならそのまま「0003」が検索対象にする方法はあるでしょうか？

以上、よろしくお願い申し上げます。

質問者からの補足コメント

• すみません！
  商品番号列はB列でした

  No.2の回答に寄せられた補足コメントです。 補足日時：2023/04/13 17:14

No.4 ベストアンサー 回答者： fujillin 回答日時： 2023/04/14 11:01

No3です。

＞なるほど、そういう調べ方があるのですね。
＞結果、これは「1」になりました。
想像通りです。

＞であればエラーはでませんが、正確な結果が取得できず全て「0」になります。
使い方が正しくないと推測されます。
No3の文章をきちんとお読みになれば、解決すると思います。

この回答へのお礼

配列の仕組みが良くわかっていないので難しいです。。。

なので再度違う方法でチャレンジしているのですが、
=SUMIF(B:B, "*" & TEXT(F2, "0000"), C:C)であれば0000-0000の商品番号の値が取得できるのですが一方で「SUMIF(B:B, F2, C:C)」では取得できる0001などの形式の商品番号の販売個数が取得できなくなります。

で、
=IF(ISNUMBER(B:B), SUMIF(B:B, F2, C:C), SUMIF(B:B, "*" & TEXT(F2, "0000"), C:C))とかもがいてみてもうまくいかず。。

理解が追いつかないのでNo1さんの言う通りになりそうです。。。

お礼日時：2023/04/14 15:59

No.3 回答者： fujillin 回答日時： 2023/04/13 22:04

No2です。

＞その式をコピーしたところエラーとなりました。
＞環境はエクセル2019です。
当方、エクセル2019で検証しています。
多分、C列に見えない文字列（＝スペースなど）が存在するのではないかと。
試しに、どこかの空きセル（＝C列以外）に
　=COUNTA(C:C)-COUNT(C:C)
と入れてみて、0にならなければ、数値以外のものがどこかに存在することになります。

あるいは、計算範囲を狭めて
　=SUMPRODUCT(C1:C20*(RIGHT(B1:B20,4)=F2))
とすると1～20行迄が対象となりますが、それでもエラーになるのでしょうか？


最悪、数値以外を除外するのなら、式を
　=SUMPRODUCT(IF(ISNUMBER(C:C),C:C,0)*(RIGHT(B:B,4)=F2))
として、Ctrl+Shift+Enterで確定（＝配列数式）すれば、計算できませんか。

この回答へのお礼

ご回答ありがとうございます！

> =COUNTA(C:C)-COUNT(C:C)

なるほど、そういう調べ方があるのですね。
結果、これは「1」になりました。

原因は1行目には項目が入っているからだと思われます。先にお伝え損ねていたことお詫び申し上げます。

「=SUMIF(B:B, F2, C:C)」の式では、「0000-0000」形式以外、つまり0000形式（形式は0000ですが中身は単なる9999までの数値です）の結果は正常に取得できていたので、項目列は関係く処理できると思い込んでいました。。。


>　=SUMPRODUCT(C1:C20*(RIGHT(B1:B20,4)=F2))
>=SUMPRODUCT(IF(ISNUMBER(C:C),C:C,0)*(RIGHT(B:B,4)=F2))

であればエラーはでませんが、正確な結果が取得できず全て「0」になります。

商品番号は「9999」（4桁）まであり、その中には「0000-0000」という商品番号があります（親番号-子番号）。それが例えば「0025-0458」であるとすると、書式設定を無視した検索値としては「25-458」になってしまい、正常に値が抜き出せていないように思えます。

今はD列にB列の下4桁を抜き出した値を抽出し、それを検索対象として処理しています。それであれば問題はないのですが、、、1列増やすというところがなにか口惜しいです；ｗ

お礼日時：2023/04/14 10:26

No.2 回答者： fujillin 回答日時： 2023/04/13 17:00

こんにちは

＞それをB列の下4桁を検索対象に～～
「必ず最後から4文字だけを検索対象にする」というので良ければ、
　=SUMPRODUCT(C:C*(RIGHT(B:B,4)=F2))
ではいかがでしょうか？

※ C列には、数値または空白セル以外は無いものと仮定しています。
　（文字等が存在する場合は、エラー値になります）
※ 計算式を多用する場合は計算負荷がかかりますので、セル範囲を必要な範囲に狭めておく方が宜しいでしょう。

この回答へのお礼

ご教示ありがとうございます！
その式をコピーしたところエラーとなりました。
環境はエクセル2019です。

おそらく、B列の書式設定を「0000」として4桁表示させているからかもしれません。

> ※ C列には、数値または空白セル以外は無いものと仮定しています。

はい、A列は商品番号、C列は販売数の列ですのでC列は数値か空白セルのみです。

お礼日時：2023/04/13 17:12

No.1 回答者： angkor_h 回答日時： 2023/04/13 16:59

そのままではできません。

なので、別列に、B列下4桁を用意して、
そこを検索列にしてください。

この回答へのお礼

やはりそうですか、、、それならばマクロにしようかと考えたのですが単純箇所なのでエクセル関数でここは処理したかったのですが。。。；

AIさんに聞いてもめちゃくちゃな式ばかりでした。

お礼日時：2023/04/13 17:12