3人で入力した在庫表、原因探しに丸一日|Excelで同じ列に数値・文字列・日付が混ざったときの見分け方と直し方

アイキャッチ:同じ列に数値と文字が混ざり合計が合わない。NGは45000円が外れて110,000、OKは数値にそろえて155,000 初心者シリーズ

例えば、繁忙期に、パートさん3人に同じ在庫表への入力をお願いしたとします。月末にSUMIFで集計をかけたら、どうにも数が合いません。数式の範囲は合っている。条件も間違っていない。エラーは1つも出ていない。それなのに合わないのです。

原因にたどり着くまでに、丸一日かかってもおかしくありません。

分かってみれば単純な話で、「数量」の列に3種類のデータが入っていました。一人目は数字だけ。二人目は「30個」のように単位付き。三人目のぶんは全角の数字のまま残っていました。誰かが手を抜いたわけではありません。3人とも、あとで見る人が読みやすいようにと気をつかった結果です。

人の目には、どれも同じ「数量」に見えます。でもExcelは1つ目だけを数値として扱い、残りの2つを文字として扱います。そして文字のほうは、合計するときに何も言わずに飛ばします。

この記事では、同じ列の中に数値・文字列・日付が混ざってしまった表を、どう見つけてどう直すかをまとめます。冒頭の例のような場面で使える手順です。先に要点を1つだけ言うと、目で探さず、まず数えること。順番をこう変えるだけで、探す時間はまるで違ってきます。

範囲は、1つの列に数値・文字の数字・日付が混ざったときの見つけ方と直し方です。どの列が混ざっているのかまだわからないなら、フィルター一覧で入力のばらつきを見る点検から始めると早く見つかります。

丸一日かかったのは「目で探した」から

こういうとき、表を上から順に眺めて、おかしなセルを探したくなります。左に寄っているセルはないか、緑の三角が出ているセルはないか、と。

この探し方がまずいのは、見た目があてにならないからです。

セルの中身は、何も設定していなければ数値が右寄せ、文字列が左寄せになります。ところが、見栄えのために列全体を中央揃えにしてある表では、この手がかりが消えます。セルの左上に出る緑の三角(「数値が文字列として保存されています」という知らせ)も、誰かが「エラーを無視する」を押していれば出ません。そもそも「30個」のように単位が付いたセルには、三角は最初から出ないのです。

そこで、見た目で探すのをやめて、列の中身をExcel自身に数えさせます。データがA2からA101までの100行に入っているとして、表から離れた空いたセルに次の3つを入れてください。

=COUNTA(A2:A101)
=COUNT(A2:A101)
=SUMPRODUCT(--ISTEXT(A2:A101))
式数えているもの結果の例
COUNTA何か入力されているセル100
COUNT数値として扱われているセル(日付も含む)82
SUMPRODUCTとISTEXT文字列として扱われているセル18

数量の列なら、COUNTAとCOUNTが同じ数になるのが正常です。上の例のように18件の差が出たら、その18件は合計に入っていません。

どの行なのかを知りたいときは、隣の空いた列に =ISTEXT(A2) を入れて下までコピーし、フィルターでTRUEだけを表示します。100行を目で追う必要はなくなります。

売上の隣にISTEXTの列を足した表。単位付きの45000円と30,000円の行だけTRUEになっている
赤枠のTRUEが、文字列になっている行です

ここまでは、列の種類に関係なく使える調べ方です。ただ、このあとの直し方は「その列が何のための列か」で変わります。金額や数量の列、日付の列、社員番号や商品コードの列。この3つに分けて見ていきます。

金額・数量の列|SUMは黙って飛ばし、足し算はエラーを出す

いちばん被害に気づきにくいのがこの列です。

別の例として、経理の後輩から「この売上表のSUM、なぜか合計が合わないんです」と相談されたとします。範囲の指定もセル参照も合っているのに、電卓で足した数字と一致しない。原因はやはり、同じ列に数値と文字列が混ざっていることです。その状態を小さく再現すると、こうなります。

行売上の列に入っている内容Excelの扱い
250000数値
345000円文字列
430,000円文字列
560000数値

=SUM(A2:A5) の結果は110,000です。本当の合計は185,000なので、75,000円ぶんが消えています。それでもSUMはエラーを出しません。SUMは、範囲の中にある文字列を「数えないもの」として読み飛ばす作りになっているからです。SUMIFやSUMIFSの合計範囲でも同じことが起きます。

売上の列に50000・45000円・30,000円・60000が混ざった表。SUMの結果は110,000で本当の合計185,000より少なく、足し算でつなぐと#VALUE!になっている
赤枠のSUMは文字のセルを飛ばし、足し算はエラーになります

ところが、同じ4つのセルを =A2+A3+A4+A5 と足し算でつなぐと、今度は #VALUE! エラーになります。足し算は「円」の付いたセルも計算しようとして、そこでつまずくわけです。エラーを出してくれるぶん、こちらのほうがまだ親切だと私は思っています。合計が怪しいと感じたら、試しに数個のセルを+でつないでみる。これは手早い確かめ方になります。

単位が付いた数字と、何も付いていない「文字の数字」

直し方は、文字列になっている理由で分かれます。

「45000円」「30個」のように単位や記号が付いているものは、置換で単位を消すのが早道です。列を選んでCtrl+Hを押し、「検索する文字列」に「円」と入れ、「置換後の文字列」は空のままにして「すべて置換」。単位が消えた時点で、Excelが数値として読み直してくれます。セルの書式が「文字列」になっている列では消しただけでは数値に戻らないので、このあとに書く方法を続けて使ってください。

単位を消して数値にそろえた売上の列。SUMの結果も足し算の結果も185,000になり、本当の合計と一致している
緑枠の合計が、本当の合計と一致しました

単位を画面に出しておきたいなら、入力するのではなく表示形式に任せます。セルの書式設定の「ユーザー定義」で #,##0"円" と指定すると、中身は数値の50000のまま、見た目だけ「50,000円」になります。単位付きの入力については 数字と文字が混ざったセルの見分け方と直し方 で、件数が多いときの手順まで書きました。

一方、見た目は「45000」なのに文字列、というセルもあります。原因は、セルの書式が「文字列」になっている、先頭にアポストロフィが付いている、前後にスペースが入っている、のどれかであることがほとんどです。全角で打った数字は、ふつうはExcelが半角の数値に直してくれます。ただ、書式が「文字列」の列に打った場合や、別のファイルから貼り付けた場合は、全角のまま残ります。

こちらは、列を選んで「データ」タブの「区切り位置」を開き、何も変えずに「完了」を押す方法が手軽です(スペースが原因のときは、先にスペースを消す必要があります)。空いたセルに1と入れてコピーし、対象の範囲に「形式を選択して貼り付け」から「乗算」をする方法もあります。乗算は空白セルを0に変えてしまうので、空白を含めずに範囲を選ぶのがコツです。

原因ごとの細かい見分け方は、それぞれ別の記事にしてあります。スペースなら 見えないスペースの見つけ方と消し方、アポストロフィなら アポストロフィが付いた数字の見つけ方と直し方、形式の不ぞろい全般なら 数値の形式がバラバラなときの見分け方と直し方 が近い内容です。

日付の列|「日付に見えるもの」は3種類ある

金額の列は「数値か、文字列か」の二択でした。日付の列はもう一段ややこしく、三択になります。

Excelにとっての日付は、実は数値です。1900年1月1日を1として、1日ごとに1ずつ増える通し番号(シリアル値)で管理されていて、2026年4月1日なら46113。セルの表示形式を「標準」に変えると、この数字が見えます。「2026/4/1」という表示は、46113に日付の服を着せているだけです。

これを踏まえると、日付の列に入りうるものは次の3種類になります。

入力の例Excelの扱い寄せISNUMBERの結果
2026/4/1本物の日付(中身は46113)右TRUE
2026.4.1文字列左FALSE
20260401ただの大きな数値右TRUE

見落としやすいのは3つ目です。「20260401」は数値なので右に寄り、ISNUMBERもTRUEを返します。「数値か文字列か」を調べるだけでは、本物の日付と区別がつきません。システムから書き出したデータや、テンキーだけで入力を済ませたい人のデータによく入っています。

文字列になる日付も、ピリオド区切りだけではありません。

  • 「2026/4/1(水)」のように、曜日を手で書き足したもの
  • 「4/1頃」「4月上旬」のように、補足が付いたもの
  • 「2026/4/31」のように、存在しない日を打ってしまったもの
  • 書式が「文字列」のセルに打った「2026/4/1」

存在しない日付は盲点です。4月は30日までなので、「2026/4/31」はExcelが日付と認めず、文字列のまま残ります。裏を返せば、日付の列に文字列が紛れていたら打ち間違いの可能性もある、ということです。

フィルターの▼を開くと、3種類が分かれて見える

日付の列は、数式より先にフィルターの▼を開くのがいちばん早い診断になります。

本物の日付は、一覧の中で「2026年」「4月」と年・月ごとに折りたたまれて表示されます。文字列の日付は折りたたまれず、その下に1件ずつばらばらに並びます。8桁の数値も「20260401」のまま単独で並びます。年のまとまりの外にはみ出している項目が、直すべきデータです。

件数まで知りたいときは、隣の列にこの式を入れて下までコピーします。

=IF(A2="","空白",IF(ISTEXT(A2),"文字列",IF(A2>=10000000,"8桁の数値","日付")))

本物の日付のシリアル値は、いまの時代なら4万台です(40000が2009年7月、50000が2036年11月にあたります)。8桁の数値は1000万以上なので、大きさで見分けられます。

混ざると、動くものと動かないものが半端に分かれる

文字列の日付が面倒なのは、全部が動かないわけではないところです。

「2026/4/1」という文字列は、引き算に使うとExcelが気を利かせて日付として読んでくれるので、計算できてしまいます。ところが「2026.4.1」は #VALUE! になります。そしてどちらの文字列も、SUMIFSで「4月1日以降」のように期間を条件にした集計からは外れます。並べ替えれば本物の日付のうしろに回され、フィルターの「今月」「先月」といった絞り込みにも引っかかりません。

「4月ぶんの件数が、手で数えたときより少ない」というとき、消えているのはたいていこの文字列の日付です。8桁の数値のほうは、46113よりはるかに大きい数として扱われるので、「4月30日以前」という条件からいつも漏れます。

3種類を本物の日付にそろえる

まとめて直すなら、ここでも「区切り位置」が使えます。作業の前に、元の列を別の列にコピーして残しておいてください。

  1. 日付の列を選び、「データ」タブの「区切り位置」を開く
  2. 「次へ」を2回押して、3枚目の画面まで進む
  3. 「列のデータ形式」で「日付」を選び、右のリストを「YMD」にして「完了」

ピリオド区切りの文字列も8桁の数値も、これで本物の日付に変わります。曜日や「頃」が付いているものは変換されないので、先に置換で余計な文字を消しておきます。

数式で直したい場合は、8桁の数値にはこちらを使います。

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

ピリオド区切りの文字列にはこちらです。

=DATEVALUE(SUBSTITUTE(A2,".","/"))

結果が「46113」のような数字で出ても、失敗ではありません。表示形式を「短い日付形式」に変えれば日付の見た目になります。できあがった列をコピーして、元の列に「値」として貼り付ければ完了です。

直したあとは、最初の数え方に戻ります。COUNTAとCOUNTが同じ数になり、=COUNTIF(A2:A101,">=10000000") が0で、フィルターの▼の中が年のまとまりだけになっていれば合格です。

文字列になった日付を1件ずつ直す方法や、毎月くり返す場合の仕組みづくりは 文字列になった日付の見分け方と直し方 で詳しく扱っています。

コード・番号の列|数値にそろえるのが正解とは限らない

社員番号、商品コード、得意先コード。こうした列の混在は、VLOOKUPの #N/A という形で表に出てきます。

検索値が数値の101で、表の側の「101」が文字列だと、VLOOKUPは見つけてくれません。逆でも同じです。見た目が同じでも、数値の101と文字列の「101」は別のものとして比べられます。

この食い違いを見抜くのに、ちょうどいい現象があります。COUNTIFです。

=COUNTIF(D2:D101,101)

COUNTIFは、数値の101も文字列の「101」も区別せずに数えます。そのため「COUNTIFで数えると1件あるのに、VLOOKUPでは #N/A になる」という状態になったら、データがないのではなく、型の食い違いを最初に疑ってください。

ここで気をつけたいのは、金額の列と違って、コードの列は「数値に直せば解決」とは限らないことです。

商品コードが「001」「002」のようにゼロから始まる場合、数値にするとゼロが消えて「1」「2」になります。ハイフンやアルファベットが入るコード(A-101など)は、そもそも数値にできません。コードは足したり引いたりするものではないので、列全体を文字列にそろえるほうが安全な場面が多いのです。

数値になってしまったコードを文字列に戻すには、隣の列に次の式を入れます。

=TEXT(A2,"000")

“000” の部分は桁数に合わせます(3桁なら0を3つ)。ゼロで桁を埋める必要がなければ =A2&"" でも文字列になります。できた列を値として貼り戻し、あわせて列の書式を「文字列」にしておけば、次に入力するぶんも文字列で入ります。

マスタの側をすぐには直せないときは、VLOOKUPの検索値のほうを合わせる手もあります。表の側が文字列なら =VLOOKUP(A2&"",D:E,2,FALSE)、表の側が数値なら =VLOOKUP(A2*1,D:E,2,FALSE) です。あくまで応急処置なので、落ち着いたら元のデータをそろえてください。

#N/A の原因は、型の食い違いだけではありません。スペースの混入や範囲のずれなど、ほかの原因の切り分けは VLOOKUPで#N/Aが消えないときの原因の探し方 にまとめています。全角と半角の違いで一致しない場合は 半角・全角(文字コード違い)の見抜き方と直し方 のほうが近い内容です。

直したあと、来月また混ざらないようにする

在庫表の件で最後に効いたのは、数式ではありませんでした。入力をお願いする前に「数量は、数字だけを半角で入れてください」と一行伝えた。それだけで、翌月からは集計がぴったり合うようになります。丸一日かけた原因探しに比べると、拍子抜けするほど小さな手間です。

ルールは短いほど守られます。列ごとに一行で言える形にしておくのがおすすめです。

列一行ルールの例
金額・数量数字だけを半角で。単位は入れない(表示形式で出す)
日付2026/4/1 の形で。曜日や「頃」は別の列に書く
コード・番号桁数をそろえて文字列で。先頭のゼロは省かない

口頭や貼り紙だけでは心もとない場合は、「データの入力規則」で列に制限をかけます。列を選んで「データ」タブの「データの入力規則」を開き、「入力値の種類」で数量の列なら「整数」、日付の列なら「日付」を選びます。これで「30個」や「2026.4.1」を打つと、その場で止められます。決まっていない納期に「未定」と書きたくなる日付の列は、日付と文字を別の列に分ける方法もあわせて確認しておくと安心です。

ただし、入力規則には抜け道が1つあります。コピーして貼り付けたデータは、規則をすり抜けて入ってしまうのです。別のファイルやシステムからの貼り付けが多い表では、入力規則を信用しすぎず、月に一度は冒頭の3つの式で数えるほうが確実です。数える式を消さずに置いたままにしておけば、ファイルを開くたびに目に入ります。

型はそろっているのに集計がずれる、という場合は、列の途中から入力する内容そのものが変わっていないかも見てみてください。「備考」列が途中から別物になっていた表の記事 で扱ったのが、その例です。

よく聞かれること

緑の三角を選んで「数値に変換する」を押すのではだめですか?

だめではありません。件数が少なければ、それがいちばん簡単です。ただ、緑の三角が出ていない文字列(単位付きの数字など)には使えません。行数が多いと変換に時間がかかることもあるので、この記事では区切り位置と置換を中心にしました。

セルの書式を「数値」に変えたのに、合計が変わりません。

書式を変えても、すでに入っている中身は文字列のままです。書式は「これから入力するもの」と「見た目」に効くもので、入力済みのデータを作り変えてはくれません。区切り位置や乗算の貼り付けのように、中身を入れ直す操作が必要です。

日付を打ったのに「46113」のような数字になってしまいました。

壊れたのではなく、表示形式が「標準」や「数値」になっているだけです。中身は正しい日付なので、表示形式を日付に変えれば元どおりに見えます。数字が出るのは、むしろ本物の日付である証拠です。

CSVを取り込むたびに、数字が文字列になります。

毎回あとから直すより、取り込みの手順を決めてしまうほうが楽です。考え方と手順は CSV取り込みで文字列になるときの見分け方と直し方 に書いています。

列を見る前に、列の「役割」を見る

最後に、3種類の列を並べておきます。

列の役割混ざると出る症状そろえる先最初に使う手
金額・数量SUMの合計が少ない(エラーなし)数値単位は置換、残りは区切り位置
日付期間の集計・絞り込みから漏れる本物の日付区切り位置で「日付・YMD」
コード・番号VLOOKUPが #N/A になる文字列(が安全なことが多い)TEXT関数で文字列に戻す

冒頭の在庫表の例で時間がかかるのは、「数字に見えるものは数字のはず」と思い込んでしまうからです。Excelが見ているのは見た目ではなく、中身の種類です。そして、列の中身が1種類にそろっているかどうかは、3つの式を入れれば数秒で分かります。

集計が合わない表を渡されたら、数式を疑う前に、まず列を数えてみてください。

型の混在は、表づくりの落とし穴のうちの1つにすぎません。表全体をまとめて点検したい方に向けて、12項目で確認できるチェックリストをnoteに置いています。 表の健康診断12項目(note)

コメント

タイトルとURLをコピーしました