Excel の金額の列に、「2026/11/1 3,000」「11月1日 12,500円」のように日付がいっしょに入っていて、合計が出せない。手で打ち直してもらった表や、ほかのシステムから貼り付けた表で、よく見かける形です。
数十行なら手で直せますが、数百行になると、打ちまちがいが入りやすくなります。この記事では、元のデータを残したまま、式で金額だけを取り出して合計するまでの5つの手順を紹介します。例のデータはすべて架空です。
1. 元の列は消さずに、右に作業用の列を作る
最初に、元の列には手をつけないと決めます。直接書き換えると、あとで「この行は元は何だったか」を確かめられなくなるためです。
元のデータが A 列にあるとして、右に次の3列を作ります。
・B 列「整えた文字」
・C 列「取り出した部分」
・D 列「金額」
合計は D 列で出します。
2. 全角・半角と余分な空白をそろえる
「3,000」のような全角の数字や、全角の空白がまざっていると、あとの式がうまく働きません。B 列で、まず形をそろえます。
B2:=TRIM(ASC(A2))
ASC は全角の英数字・記号を半角に、TRIM は前後の空白と、続いた空白を1つにまとめます。これで「11/1 3000円」(全角の空白)も「11/1 3000円」になります。
3. 日付のあとの金額の部分を取り出す
日付と金額のあいだが空白で区切られている場合は、いちばん後ろの区切りから右を取り出します。「3,000(11/1)」のように金額のあとにかっこで日付がある場合は、かっこより左を取り出します。
C2:=IF(B2="","",IF(ISNUMBER(FIND("(",B2)),TRIM(LEFT(B2,FIND("(",B2)-1)),TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",99)),99))))
少し長いですが、やっていることは「かっこがあればその左、なければ最後の空白より右」の2通りだけです。
4. 数字にして、迷う行には「要確認」の印をつける
取り出した部分から「,」と「円」を取り除いて、計算できる数値にします。そのとき、式で決めてしまうと危ない行には、数値の代わりに「要確認」と出します。
D2:=IF(B2="","",IF(OR(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))>1,COUNT(FIND({"/","月","日"},C2))>0),"要確認",IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(C2,",",""),"円","")),"要確認")))
「要確認」になるのは、たとえば次のような行です。
・「1/15 3,000 5,000」:数字が2つあり、どちらが金額かわからない
・「1,234円 11/1」:日付が後ろにあり、取り出した部分が日付になっている
・「11月1日3000円」:日付と金額のあいだに区切りがない
・「11/21 1万円」:「万」が入っていて、そのままでは数値にならない
ここで大事なのは、迷う行を式でむりに数値にしないことです。とくに「1/15」のような文字は、Excel が日付の数値として読んでしまうことがあり、まちがった大きな金額がそのまま合計に入るおそれがあります。
B2〜D2 の式は、下の行までコピーします。
5. 合計と件数で確かめる
最後に、合計といっしょに件数も出して、数が合っているかを確かめます。データが2行目から601行目まであるとして、
・件数:=SUMPRODUCT((A2:A601<>"")*1)
・取り出せた件数:=COUNT(D2:D601)
・要確認の件数:=COUNTIF(D2:D601,"要確認")
・合計:=SUM(D2:D601)
「取り出せた件数+要確認の件数」が「件数」と同じなら、取りこぼしはありません。要確認の行は、フィルターで「要確認」だけを表示して、元のデータを見ながら D 列に金額を手で入れます。手で入れた行も数値になるので、合計にそのまま入ります。
13行の架空のデータ(全角の数字、かっこつきの日付、マイナスの金額、区切りのない行など)で、本物の Excel で確かめたところ、8行が数値になり、4行が「要確認」、空の1行は空のままで、件数と合計も手で計算した値と一致しました。
うまくいかないときに見るところ
・金額が全部「要確認」になる:日付と金額のあいだが空白ではなく、「_」や「/」で区切られていないか。区切りの文字に合わせて、C 列の式の " " を置き換えます
・合計が思ったより大きい:D 列を並べ替えて、桁が大きすぎる行がないかを見ます
・元のセルが日付だけ(金額がない):表示は「2026/11/1」でも、中身は数値なので、金額として取り出されることがあります。合計の前に、元の列の空欄や日付だけの行を確かめておくと安心です
まとめ
金額と日付が混ざった列は、元の列を残し、形をそろえ、金額の部分を取り出し、迷う行には印をつけ、件数と合計で確かめる、の5つの手順で整えられます。いちばん大事なのは、迷う行を式でむりに数値にせず、人の目で確かめる行として残しておくことです。
Excel の表の整理・集計をお手伝いできます
数百行の表の整形や、合計・集計の式づくりをお手伝いしています。元のデータは残したまま、式で整えて、判断がつかない行は一覧にしてお知らせします。データの混ざり方がばらばらでも、まず数行の例を見せていただければ、進め方をご提案します。下のサービスからご相談ください。