Excel関数のSUMIFとSUBSTITUTE

質問

以下の画像についての質問です。

B列に「11月 26日」を含む、A列価格の合計を出すには、

SUMIFで、B列の「11月 26日」を含むと指定し、
SUBSTITUTEで、A4からのA列の数字の「" 円"」を""に
置き換えし無ければいけないと思うのですが、

組み合わせ方が分かりません。

※WEBクエリで取り込んで自動更新されるデータなので文字はいじれません。

環境OS：Vista
Version：Excel2010

よろしくお願い致します。

回答No.5

kagakusuki · ベストアンサー

ANo.2,3です。

>数式のなかでA2セルも参照して算出していたのですね。

　はい、その通りです。
　只、毎回毎回、

日時が○月 ×日の合計

と入力するのは煩わしいかと思いましたので、

11/26

と入力しても、

日時が11月 26日の合計

と入力しても、どちらの入力方法で入力されても構わない方法を考えました。

　まず、次の操作を行って下さい。

Sheet1のA2セルを右クリック
　　↓
現れた選択肢の中にある[セルの書式設定]をクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの中にある[表示形式]タブをクリック
　　↓
「分類」欄の中にある[ユーザー定義]をクリック
　　↓
「種類」欄に

"日付が"m"月 "d"日の合計"

と入力
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック

　次に、合計を表示させるセルに次の関数を入力して下さい。

=SUMPRODUCT(SUBSTITUTE(SUBSTITUTE(0&INDEX($A:$A,ROW($A$3)+1):INDEX($A:$A,MATCH("*?",$A:$A,-1)),"円",),"0-","-")*(LEFT(INDEX($B:$B,ROW($A$3)+1):INDEX($B:$B,MATCH("*?",$A:$A,-1)),LEN(TEXT($A$2,"日時がm月 d日の合計"))-6)=MID(TEXT($A$2,"日時がm月 d日の合計"),4,LEN(TEXT($A$2,"日時がm月 d日の合計"))-6)))

　以上です。
　尚、WEBクエリ取り込みされて来た日時のデータが、1月2日の様に月や日にちが1桁の際に、もしも、01月02日の様に0を付けた2桁表示となっている場合には、上記の関数の中における

"日時がm月 d日の合計"

となっている箇所の全てを

"日時がmm月 dd日の合計"

に変更して

=SUMPRODUCT(SUBSTITUTE(SUBSTITUTE(0&INDEX($A:$A,ROW($A$3)+1):INDEX($A:$A,MATCH("*?",$A:$A,-1)),"円",),"0-","-")*(LEFT(INDEX($B:$B,ROW($A$3)+1):INDEX($B:$B,MATCH("*?",$A:$A,-1)),LEN(TEXT($A$2,"日時がmm月 dd日の合計"))-6)=MID(TEXT($A$2,"日時がmm月 dd日の合計"),4,LEN(TEXT($A$2,"日時がmm月 dd日の合計"))-6)))

として下さい。

　それから因みに、A2セルに行ったセルの書式設定と同様の操作を行って、「種類」欄に入力する内容を

##,#00 "円"

としますと、下の添付画像の様に合計金額も「『,』で3桁ごとに区切った数字+『 円』」の形式で表示・印刷されます。（表示が変わるだけで、データ自体は数値のままです）

お礼

言われた通りに行ったら簡単にできました。

ただ、関数が非常に長くて理解できず応用できそうにいので、
これから各関数の役割を勉強します。

是非聞きたいのですが、
このように複数の関数を組み合わせる時、
どのような数式にするか基準とかルールとかコツとかありますか？

たくさんご回答いただき感謝致します。

回答No.4

tsubuyuki

やはり作業列を使うのが楽だと思います。

一例として、MID関数・FIND関数をなんとなく使ったモノです。
便宜上、同じシートで用意しましたが、別のシートでもＯＫです。

Ｄ列は
　　=SUBSTITUTE(A4," 円",)*1
とし、" 円"（半角スペース付）を置き換え（実質削除）、１を乗することで数値の扱いにしています。

Ｅ列は
　　=MID(B4,1,(FIND("日",B4)))
とし、「日時」の列（Ｂ列）から日付部分だけを取り出しています。
ＭＩＤを使って、Ｂ列の１文字目から「ＦＩＮＤ関数で探った"日"の文字位置まで」を
抜き出して日付の作業列を作ります。

これらを作業列から、ＳＵＭＩＦを使って
　　=SUMIF(E4:E8,MID(A2,4,FIND("日の",A2)-3),D4:D8)
こんな感じで日付の合計を出しています。
解説がアレなのでハショりますが、もちろん
　　=SUMIF(E4:E8,"11月 26日",D4:D8)
でＯＫですし、Ｂ１辺りに"11月 26日"と文字列で打っておいて
　　=SUMIF(E4:E8,B1,D4:D8)
でもＯＫです。

以上、参考までにどうぞ。

お礼

説明がとても分かりやすく簡単に出来ました。
各関数の解説凄く助かりました。

この方法なら数式が簡単でいいですね。
ありがとうございました。

回答No.3

kagakusuki

後、次の様な関数を使用しますと、作業列を用いずとも、合計を求める事が出来ます。

=SUMPRODUCT(SUBSTITUTE(SUBSTITUTE(0&INDEX($A:$A,ROW($A$3)+1):INDEX($A:$A,MATCH("*?",$A:$A,-1)),"円",),"0-","-")*(LEFT(INDEX($B:$B,ROW($A$3)+1):INDEX($B:$B,MATCH("*?",$A:$A,-1)),LEN($A$2)-6)=MID($A$2,4,LEN($A$2)-6)))

　但し、SUMPRODUCT関数は、計算処理に要するパソコンの負荷が大きくなりがちですので、表の行数が数千行以上にもなりますと、計算時間が異常に長くなってしまいますので注意して下さい。

お礼

こちらも、下記と同様の方法で解決しました。

負荷が大きくなるとのことですが、
使用を考えているのは数十行・列ですので問題ないかもしれないですね。

作業用シートを使わないのも魅力的です。

ありがとうございました。

回答No.2

kagakusuki

>SUBSTITUTEで、A4からのA列の数字の「" 円"」を""に置き換えし無ければいけないと思うのですが、

　その様な場合には作業列を使用します。

　今仮に、元データが自動更新されて来るシートがSheet1であり、Sheet2のA列を作業列として使用するものとします。

　まず、Sheet2のA4セルに次の関数を入力して下さい。

=IF(ISNUMBER(SUBSTITUTE(INDEX(Sheet1!$A:$A,ROW()),"円",)/(INDEX(Sheet1!$A:$A,ROW())<>"")),SUBSTITUTE(INDEX(Sheet1!$A:$A,ROW()),"円",)+0,"")

　次に、Sheet2のA4セルをコピーしてSheet2のA5以下に貼り付けて下さい。

　次に、合計を表示させるセルに次の関数を入力して下さい。

=SUMIF($B$3:INDEX($B:$B,MATCH("*?",$A:$A,-1)),MID($A$2,4,LEN($A$2)-6)&"*",Sheet2!$A$3:INDEX(Sheet2!$A:$A,MATCH("*?",$A:$A,-1)))

　以上です。

お礼

やっとできました。
Sheet1のA2の、「日時が11月 26日の合計」は説明用に記入していたので消してしまっていましたが、入力したら出来ました。
数式のなかでA2セルも参照して算出していたのですね。
そして別シートを作業用に使用することなど、
またまた勉強になりました。
ありがとうございます！

回答No.1

MarcoRossiItaly

●「4,544 円」など
(1)数値の「4544」にセルの書式で「 円」を付加している場合
　 数値であるため、何も加工しなくてもそのまま計算に使えます。
　 セルを右クリックなどして「セルの書式設定＞表示形式タブ＞ユーザー定義＞種類ボックス」に「#,##0" 円"」などの書式記号が記入されている場合がこれに当たります。

(2)文字列の一部として「 円」を付加している場合
　 おっしゃるとおり、次のように SUBSTITUTE 関数などを使うか、置換の機能により「 円」を削除。

　 =0+substitute(a4," 円",)
　 など

　 置換の機能（Ctrl+H）では、「 円」→「」（未入力）というふうに置換します。

●「11月 26日 18時 28分」など
　「2012年11月26日」のシリアル値は「41239」、「18時28分」のシリアル値は「0.76944」です。セルの書式を「標準」などに設定すると、見ることができます。

(1)日付・時刻のシリアル値の合計が 1 つのセル内に日時に関するセルの書式で表示されている場合
　 「41239.76944」という数値であるため、いきなり SUMIF 関数などを適用できます。

　 =sumif(b4:b9,">="&"2012/11/26",a4:a9)-sumif(b4:b9,">="&"2012/11/26"+1,a4:a9)

(2)文字列である場合
　 次式で一応、シリアル値になります。式中、半角スペースが入っている箇所とそうでない箇所があるので、区別してください。

　 =0+substitute(substitute(substitute(substitute("2012/"&b4,"月 ","/"),"日",),"時 ",":"),"分",)

　 この数式の入力されているセルの書式が「標準」などであると小数が表示されるので、必要に応じて、ユーザー定義で「m"月 "d"日 "h"時 "mm"分"」などに設定します。

　 上の金額の場合と同様に、置換の機能で「月 」→「/」という具合に 4 回置換していっても構いません。

お礼

色々なパターンが理解できました。
WEBクエリ取り込みなので置換はできませんでし、
自動更新されたら自動的もしくはF9で結果が表示されるようにしたいです。
勉強になりました。
ありがとうございました。