Google検索

Windows10 & Office2016 Excel2016

セル範囲の中から数値に見える文字列のセルを見つけるには

投稿日:2017年7月28日 更新日:

Excelのセルに数値が入力されていると思っても、実際は文字列だったということがあります。

見た目は数値に見えても表示形式は文字列になっていることがあります。

セル範囲の中に見た目は数値の文字列が混じっていると、トラブルの原因になったりします。

関数を使って計算しようとすると結果がおかしいとか、並べ替えもうまくいかなくなってしまいます。

セル範囲が小さい場合は、見つけるのも簡単かもしれませんが、セル範囲が広くなると見つけるのも困難です。

たとえば、下のような表があります。

1つのセルが文字列になっています。

見ただけでは分かりませんよね。

表

問題点:合計値が異なる

セルD7には、SUM関数が入力されてますが、合計値が間違っていますね。

参考職場で、計算機で計算した合計値とExcelの合計値が違うということがありました。原因は数字文字列(見た目は数値に見える文字列)でした。

合計値が異なる

問題点:最大値も異なる

5月の最大値も違っています。

最大値が異なる

文字列を探すには

数字の文字列を見つける方法はいくつかありますが、もろもろの設定をしてあっても確実に見つける方法を紹介します。

条件付き書式を使って

セル範囲を指定します。ここでは、セルB2からセルC10までです。

範囲選択

[ホーム]タブの[スタイル]グループにある[条件付き書式]から[新しいルール]をクリックします。

条件付き書式 新しいルール

[新しい書式ルール]ダイアログボックスが表示されます。

[数式を使用して、書式設定するセルを決定]をクリックします。

新しい書式ルール

下のようにダイアログボックスが変わります。[次の数式を満たす場合に値を書式設定]のテキストボックスに以下のように入力します。

=ISTEXT(B2)

参考ISTEXT関数は、セルの内容が文字列である場合にTrueを返してくれる関数です。

そして、[書式]をクリックします。

新しい書式ルール 数式

[セルの書式設定]ダイアログボックスが表示されます。

任意の背景色を選択して、[OK]ボタンをクリックします。

セルの書式設定

[新しい書式ルール]のダイアログボックスに戻ります。

[OK]ボタンをクリックします。

新しい書式ルールダイアログボックス

結果、下のように文字列のセルに背景色がつきます。

条件付き書式の結果

参考条件付き書式のルールの削除については、以下の記事で解説しています。

条件付き書式のルールの削除(ルールのクリア)

数字文字列を数値にするには

見つけた数字文字列を数値にするには、いくつかの方法がありますが、数値の1を乗算する方法が早いと思います。

表以外の空いているセルに[1]を入力します。

そして、そのセルをコピーします。

1と入力したセルをコピー

文字列のセルを選択して、右クリックして、[貼り付けのオプション]の[形式を選択して貼り付け]から[形式を選択して貼り付け]をクリックします。

形式を選択して貼り付け

[形式を選択して貼り付け]の中から[乗算]を選択して、[OK]ボタンをクリックします。

形式を選択して貼り付け 乗算

表は下のようになって、文字列だった数字も数値になります。

表示形式は[標準]になっています。

結果

ミスを防ぐには

文字列のセルに数値を入力するとセルの左上に緑色の三角マークが付き、下のようにポップヒントが表示されます。

このセルにある数値が、テキスト形式か、またはアポストロフィーで始まっています。

ポップヒント

また、エラーマークをクリックすると、下のようにメニューの中からエラーの処理を選択するように求められます。

エラーの処理

ですが、[Excelのオプション]で、下のように[数式]の[エラーチェック]で[バックグラウンドでエラーチェックを行う]のチェックボックスをオフにしていると、上記のようなポップヒントやメニューは一切表示されません。

できれば、この設定は既定のままオンにしておかれた方がいいと思います。

Excelのオプション

[Excelのオプション]ダイアログボックスは、[ファイル]タブをクリックして[オプション]をクリックします。

または、エラーマークの[エラーチェック オプション]をクリックしても表示できます。

エラーチェックオプション

参考データのクレンジングは大変な作業ですね。以下の記事もお役に立てるかもしれません。

フィルターと置換機能を使って不揃いのデータを整える

Excelのフィルター機能は、データのチェックをして整えるときにも便利です。 ピボットテーブルの元表には、統一されたデータがなければ、分析も ...

続きを見る

【重複の削除】は1番目の値が残る、データの空白スペースには注意

Excel2007以降には、[データ]タブに[重複の削除]というコマンドがあります。 ポップヒントには、以下のように記されています。 重複の ...

続きを見る

2つのセルの文字列を比較して同じ文字列かどうかを確認する方法

Excelで2つのセルに入力された文字列が合っているかどうかを確認する方法です。 下のようにB列とC列に文字列が入力されています。 B1=C ...

続きを見る

テーブルやピボットテーブルを作成する場合の注意点もまとめています。

テーブルやピボットテーブルでうまくいかない場合の見直し点

Excelのテーブルやピボットテーブルは、データベースとしての管理や分析が簡単にできるようになる、とても便利な機能です。 でも、既存の表をテ ...

続きを見る

関連以下は、条件付き書式に関するページです。

アクティブセルの行に色を付ける

Excelで横や縦に長い表の場合、アクティブセルのある行全体や列全体に塗りつぶしを設定すると、見やすくなることがあります。行番号、列番号を見 ...

続きを見る

条件付き書式のデータバーを活用して点数をグラフ化

Excel2010で試験の点数をデータバーを使ってグラフ化した方から、2つの質問を受けました。 作成された表は下図のようになっていました。C ...

続きを見る

条件付き書式のアイコンセットの区分け

Excel2010以降の条件付き書式の[アイコンセット]は便利ですが、アイコンセットのアイコンの種類を区分けする範囲は既定で決められています ...

続きを見る

条件付き書式のコピー

Excel2010に限ったことではありませんが、条件付き書式は、コピー、貼り付けして使用することができます。 条件付き書式の設定 たとえば、 ...

続きを見る

空白セル(0表示)をグラフに反映させたくない

Excelで下のような表を元に複合グラフを作成しました。 2011年度のデータは、7月までしかありません。 E列では、累積を求める式を入れて ...

続きを見る

2つの列の差分を求める(重複しない一意の値を求める)

Excelで2つの列にデータが入力されていて、そのデータの差分を求めたい場合がありますね。 たとえば、下のようにA列には分析用データとして別 ...

続きを見る

小数点の位置を揃えるには(小数点以下の異なる桁数が混在する場合)

一列の複数セルに小数点以下の桁数の異なる数値が並んでいる場合、小数点の位置は揃いません。 小数点以下の異なる桁数の数値が混在していても、小数 ...

続きを見る

[令和1年]を[令和元年]と表示する(ユーザー定義と条件付き書式)

Excelで[セルの書式設定]で和暦表示にすると、2019/05/01から2019/12/31までは[令和1年]と表示されます。この[令和1 ...

続きを見る

Profile

執筆者/はま

おすすめ記事と広告

-Windows10 & Office2016 Excel2016

Copyright © 2001-2019 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2019 AllRights Reserved Powered by AFFINGER4.