Go to Qiita Advent Calendar 2024 Top
LoginSignup
Cygnus_North
@Cygnus_North (Cygnus North)

Are you sure you want to delete the question?

Leaving a resolved question undeleted may help others!

SUMIF/SUMIFS関数が完全一致の合計にならないことへの対処方法

Q&A

Closed

ExcelExcel関数

Office365を使用しています

SUMIF関数が完全一致ではない模様

Excelの集計でSUMIF/SUMIFSを使用するケースは多いと思います
検索条件は「完全一致」がデフォルトであると思っていたのですが、必ずしもそうでなはないケースがありました。
これらの対処についてご相談させてください

発生している問題

A列にCode、B列に数値が入っています
このときCODEは文字列として入力されています

image.png

このときD列、E列で集計を行います
image.png
E列に「=SUMIF(A:A,D2,B:B)」の式を採用すると上記のようになり、2103と02103が合算された形になります

またワイルドカードを使用して以下の式を採用すると2103が正しく集計されません
=SUMIF(A:A,D2&"*",B:B)
image.png

自分で試したこと

・EXACT関数でそれぞれのコードを比較しましたが、やはり別ものとして認識しています
・SUMIF・SUMIFS関数の中だけで完全一致にならない…という問題が発生しているようです

何十年とExcelを使ってきましたが、このようなケースは初めてで、対処に困っております
なにかヒントでもいただけると助かります

0

1Answer

albireo
@albireo

SUMIF関数が完全一致ではない模様

完全一致です。ただし「文字列としての完全一致とは限らない」です。
「数式などがセルの内容を参照するとき、セルの書式指定が何であろうが数値に変換可能な文字列なら数値扱いする」という迷惑な仕様です。(日付などでも起こります)
どこか適当なセルに=TEXT(A5,"@")と入力すると、A5の内容「02103」を文字列化しているだけなのに「2103」になってしまうことが確認できます。

"_"&A2のように書けば0を消さずに文字列扱いしてくれるのですが、SUMIF関数では参照するセルの側を加工できないのでE列の式だけで解決はできそうにないです。

というわけでC列に「A列の内容を無理矢理文字列化したもの」を用意することで強引に実現しました。

A B C D E
1
2 2103 100 =TEXT("_"&A2,"@") _2103 =SUMIF(C:C,D2,B:B)
3 02103 1000 =TEXT("_"&A3,"@") _21032 =SUMIF(C:C,D3,B:B)
4 2103 100 =TEXT("_"&A4,"@") _02103 =SUMIF(C:C,D4,B:B)
5 02103 3000 =TEXT("_"&A5,"@")
6 21032 500 =TEXT("_"&A6,"@")

ワイルドカードを使用して以下の式を採用すると2103が正しく集計されません

D2&"*"は「先頭がD2セルの内容と一致するものすべて」なので、「21032」を含むのは正しい動作です。

3Like

Comments

  1. @Cygnus_North

    Questioner

    早速のご返答ありがとうございます！

    という迷惑な仕様です

    ほんとにもうっ！これofこれです
    集計用の列の追加不可避ですね
    さっそく帳票に手を加えるようにVBA組みなおします
    重ねてありがとうございます<(__)>

  2. @jinbei230525

    納得したのあれば、本問をクローズしましょう。

  3. @Cygnus_North

    Questioner

    ご指摘ありがとうございます:pray:

Your answer might help someone💌

LoginSign Up