『使用していない値と重複計上の抽出』（ジュン）
ーーーーーーーＡーーーーーーーー+ーーＢーー
1 ヤフー アウトドアの●●ヤフー店 1,500
2 ヤフー 家電●● オンライン店 2,700
3 楽天 ●●ネットショップ店 1,900
4 Amazonカード利用料 5,000
5 ソフトバンクネット利用料 3,100
6 ヤフークレジット利用料 7,200
7 電気使用料 9,000
上記のような表を作成し、SUMIF関数でネットショッピング(1，2、3)とクレジット(4，6)、その他(5、7)に振分しようとしたとき、
ネットショッピングの合計(6,100)の検索条件に「ヤフー」を設定すると14,800となり、1を二重計上、6を誤算入してしまします。
このようなとき、手動で「利用料」等を差引くよう設定して、誤算入は防いでいますが、二重計上を自動で算出する方法はないでしょうか？
また、条件漏れから、7の電気使用量が計算から抜けてしまうことがあります。この際は1つずつ手作業で探していますが、振分漏れを自動で発見する方法はないでしょうか？
是非お教えください。
＜ 使用 Excel：Excel2019、使用 OS：Windows11 ＞
１．すべての集計項目を列挙してください。
２．サンプルの正解値を提示してください。
（半平太） 2025/12/18(木) 08:46:09
重複、欠落なく処理するには、全行に集計区分を割り当てるのが王道です。 ただ、全体の項目に集計区分を割り当てる作業を簡易化したいのが目的でしょうか。
下記は Ｈ列、Ｉ列にカテゴリを「集計区分のパターン」と「集計区分」を記述し、処理するコードをAIで作成した例です。
H列は正規表現をサポートしています。
正規表現例
| ：OR
.* ：任意の文字列
今回のデータに対応する定義は下記のようなイメージです。 事前にH、I列に記載してから実行してください。
H列（条件） I列（区分） (Amazon|ソフトバンク|ヤフー|楽天).*利用料 ネット利用料 (Amazon|ソフトバンク|ヤフー|楽天).*店 ネットショッピング支払い (電気|電話|水道|ガス).*使用料 光熱費
設定後下記のマクロの「集計全体処理」を実行すると、C列に集計区分が設定され、それに応じて E:F列に集計結果が表示されます。
C列が空欄の場合は、集計対象から漏れているので、H:I列に定義を追加してください。
結果例 ヤフー アウトドアの●●ヤフー店 1,500 ネットショッピング支払い ネットショッピング支払い 6100 ヤフー 家電●● オンライン店 2,700 ネットショッピング支払い ネット利用料 15300 楽天 ●●ネットショップ店 1,900 ネットショッピング支払い 光熱費 9000 Amazonカード利用料 5,000 ネット利用料 ソフトバンクネット利用料 3,100 ネット利用料 ヤフークレジット利用料 7,200 ネット利用料 電気使用料 9,000 光熱費
マクロ --- Option Explicit
'======================== ' 列定数定義（A〜I） '======================== Const COL_DETAIL As String = "A" ' 細目 Const COL_AMOUNT As String = "B" ' 金額 Const COL_CATEGORY As String = "C" '集計 区分（結果）
Const COL_SUM_CATEGORY As String = "E" ' 集計区分 Const COL_SUM_AMOUNT As String = "F" ' 集計金額
Const COL_REGEX As String = "H" ' 正規表現 Const COL_REGEX_CAT As String = "I" ' 正規表現に対応する集計区分
Sub 集計全体処理()
集計区分判定
集計
End Sub
Sub 集計区分判定()
Dim lastRowA As Long, lastRowDef As Long
Dim i As Long, j As Long
Dim detail As String
Dim pattern As String, category As String
Dim reg As Object
Set reg = CreateObject("VBScript.RegExp")
reg.IgnoreCase = True
reg.Global = False ' 最初の一致のみ
lastRowA = Cells(Rows.Count, COL_DETAIL).End(xlUp).Row
lastRowDef = Cells(Rows.Count, COL_REGEX).End(xlUp).Row
For i = 1 To lastRowA
detail = Cells(i, COL_DETAIL).Value
Cells(i, COL_CATEGORY).ClearContents
For j = 1 To lastRowDef
pattern = Cells(j, COL_REGEX).Value
category = Cells(j, COL_REGEX_CAT).Value
If pattern <> "" Then
reg.pattern = pattern
If reg.Test(detail) Then
Cells(i, COL_CATEGORY).Value = category
Exit For ' 最初にマッチした集計区分を採用
End If
End If
Next j
Next i
End Sub
Sub 集計()
Dim lastRow As Long
Dim dict As Object
Dim i As Long
Dim category As Variant
Dim amount As Double
Dim outputRow As Long
Set dict = CreateObject("Scripting.Dictionary")
lastRow = Cells(Rows.Count, COL_CATEGORY).End(xlUp).Row
' 集計
For i = 1 To lastRow
category = Cells(i, COL_CATEGORY).Value
If category <> "" Then
amount = Cells(i, COL_AMOUNT).Value
If dict.Exists(category) Then
dict(category) = dict(category) + amount
Else
dict.Add category, amount
End If
End If
Next i
' 出力エリアクリア
Range(COL_SUM_CATEGORY & ":" & COL_SUM_AMOUNT).ClearContents
' 出力
outputRow = 1
For Each category In dict.keys
Cells(outputRow, COL_SUM_CATEGORY).Value = category
Cells(outputRow, COL_SUM_AMOUNT).Value = dict(category)
outputRow = outputRow + 1
Next category
End Sub
正規表現の利用がネックかな・・・
（英愛） 2025/12/21(日) 18:13:13
要するに分類の方法を考えてください、という趣旨の質問と見受けましたが、 それぞれの判定条件をご自分の日本語で説明する必要があります。
本来は ・ご自分でこうした整理検討をしたうえで、（これはデータを持つ質問者さんにしかできません） ・仕様をきちんと説明し（できれば箇条書きで）、 さて、Excelでどう実現したらよいですか、といった質問にしてもらいたいところです。
ありがちな展開は、「ああこうした例もありました」と仕様を小出しにされるというものです。 これを避けるために、既に、「すべてのケースを列挙して、対応する結果を示して」 という極めてまっとうなコメントがありました。 私も全く同感で、ありえるケースをすべて列挙するのが解決への第一歩かと思います。
（xyz） 2025/12/23(火) 14:12:55
質問者さんへの私のコメントは前発言で尽きています。
質問者さんへのコメントではありませんが、 英愛さんから正規表現を使用したコード例が提示されましたので、 正規表現を使った別のやり方をメモしてみました。（当該分野に興味があるかたの何かの参考になれば幸いです）
概ね英愛さんの投稿に沿った前提にしていますが、ひとつだけ修正しています。 H列の正規表現パターンを以下のように、マッチ記憶をしない(?: )を使用するという前提を置いています。 (?:Amazon|ソフトバンク|ヤフー|楽天).*利用料 (?:Amazon|ソフトバンク|ヤフー|楽天).*店 (?:電気|電話|水道|ガス).*使用料 # もちろんコードを変更（SubMatchesの中身をstep 2 で見ていく）すれば、前提の変更はしなくても済みます。 # ただし、もし各パターンに ()を使用しないケースもありうるなら、修正方法のようにしておくのが安全です。
Sub 集計区分別集計処理()
Dim lastRowA&, lastRowH&
Dim i&, j&
Dim detail As String
Dim s As String
Dim category As String
Dim reg As Object
Dim matches As Object
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary") '集計区分別合計金額
'正規表現の設定
Set reg = CreateObject("VBScript.RegExp")
reg.IgnoreCase = True
lastRowH = Cells(Rows.Count, "H").End(xlUp).Row
For j = 1 To lastRowH
s = s & "(" & Cells(j, "H").Value & ")" & "|"
Next
reg.pattern = Left(s, Len(s) - 1)
'集計区分の判別と金額合計の算出
lastRowA = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastRowA
detail = Cells(i, "A").Value
Cells(i, "C").ClearContents
Set matches = reg.Execute(detail)
If matches.Count > 0 Then
For j = 1 To matches(0).SubMatches.Count
If Not IsEmpty(matches(0).SubMatches(j - 1)) Then
category = Cells(j, "I").Value '判定した集計区分
Cells(i, "C").Value = category
dic(category) = dic(category) + Cells(i, "B").Value '金額合計
Exit For
End If
Next
End If
Next
'集計結果のシート書き出し
[E1].Resize(dic.Count, 1) = Application.Transpose(dic.Keys)
[F1].Resize(dic.Count, 1) = Application.Transpose(dic.Items)
End Sub
（xyz） 2025/12/23(火) 14:16:28
1) Alt + F11 でVBE(Visual Basic Editor)を表示 2) [挿入] - [モジュール] 右空白部分に下記コードを貼り付ける 3) 再度 Alt + F11 でエクセル画面に戻る 4) Alt + F8 でtestを選択して[実行]
データはA/B列、列項目無し 結果はF/G列に出力 ということで...
Sub test()
Dim a, e, i&, ii&, dic As Object, x
Set dic = CreateObject("Scripting.Dictionary")
x = Array("項目", "ネットショッピング", "クレジット", "その他")
For i = 0 To UBound(x)
dic(x(i)) = IIf(i = 0, "合計", 0)
Next
a = [a1].CurrentRegion.Value2
With CreateObject("VBScript.RegExp")
.Pattern = "^.+((店)|((カード|クレジット)利用料))$"
For i = 1 To UBound(a, 1)
If .test(a(i, 1)) Then
For ii = 1 To 2
If .Execute(a(i, 1))(0).SubMatches(ii) <> "" Then dic(x(ii)) = dic(x(ii)) + a(i, 2)
Next
Else
dic(x(UBound(x))) = dic(x(UBound(x))) + a(i, 2)
End If
Next
End With
[f1].Resize(dic.Count, 2) = Application.Transpose(Array(dic.keys, dic.items))
End Sub
（jindon） 2025/12/23(火) 18:05:54
前提を明記していませんでしたが、私のコードは、英愛さんの仕様の"イメージ"を踏襲したものです。
jindonさんが提示された仕様の解釈でOKであれば、数式でも対応できそうですね。
費目CODE 費目分類 1 ネットショッピング 2 クレジット 3 その他 といった費目体系にしておいて、
=IF(IFERROR(SEARCH("店",A2),0)>0,1,
IF(IFERROR(SEARCH("カード利用料",A2),0) + IFERROR(SEARCH("クレジット利用料",A2),0) > 0,2,3))
などとすればA列の支出項目名から、費目CODEを得ることができそうです。
あとは、SUMIF関数で集計すればよいでしょう。
質問者さんからの応答もないことですし、私はこれで失礼します。
（xyz） 2025/12/24(水) 15:06:13
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.