ブログトップ

カクレ理系のやぶにらみ

tamm.exblog.jp

時間のある方はお読みください。軽い気持ちで読み始めると頭が痛くなります。

数値と文字列の混在を正す

 Excelでデータベースを作成していると、数字を扱うフィールド(縦の列のこと。ちなみに横の行のことはレコードと呼びます)で数値データと文字列データが混在してしまうことがあります。こうなってしまうと、数式での処理がうまくいかなくなり、困ったことになります。今回は、そのような場合の復旧のやり方と、そもそもそういうことが起こらないようにするにはどうしたらいいのかという予防法についてまとめることにします。

(数値と文字列の混在を発見するには)
 数字を扱うフィールドに対し数式を設定した結果、部分的にエラーが発生するときは、十中八九数値データと文字列データの混在が原因です。そのことを確認するには2つの方法があります。

(1)「%」ボタンを使う
 まず、当該列の列番号をクリックして、列全体を選択状態にします。次に、「%」ボタンをクリックすると、数値データが入力されているセルは%表示に切替わりますが、文字列データが入力されているセルでは何も起こりません。

(2)文字の配置を「標準」にする」
 列全体を選択状態にしたら、右クリックで「セルの書式設定」をクリックします。次に「配置」タブで、「文字の配置」-「横位置」を標準にします。こうすると数値は右詰め、文字列は左詰め表示されるようになります。


(数値と文字列の混在を正すには)
 この作業には関数を使います。最初に関数を入力してあとはコピーすればよいのですが、作業効率を高めるために、最後の行に関数式を入力して先頭行に向かってコピーするという方法をとることにします。(最初の行に関数式を入力して最後の行に向かってコピーするのとでは作業効率が数倍から数十倍も異なるからです。)

(1)当該フィールドのセルをどれかひとつクリックして、Ctrlキーを押しながら「↓」キーを押して、当該フィールドの最後の行へ移動します。

(2)当該フィールドのひとつ右横の列番号右クリックして「挿入」を選択し、空白の列を挿入します。

(3)当該フィールドの最後の行のひとつ右隣のセル(たった今挿入した列なので空白状態となっています)をクリックして、次の数式を入力します。

=value(ひとつ左隣のセルの行列番号)

 value関数は文字列として入力されている数字を数値データに変換する関数で、変換対象が数値の場合はそのままとなります。つまり、value関数を使うと、すべての数字を数値データに揃えることができるわけです。なお、value関数の代わりにABS関数を使っても同じ結果になります。


(参考)数値データを文字列に変換する関数
 text関数を使うと、数値データを文字列に変換することができます。その際に、たとえば次のようにすると先頭に0を表示させることもできるようになります。

=text(変換するセルの行列番号,"0000")

 こうすると、元の数値データが4桁の場合は見た目そのままとなりますが、3桁の数値データでは頭に0が付いた4桁の文字列になります。

(4)入力した関数式を、先頭行に向かってコピー&ペーストするには、貼付ける範囲を選択する際に、CtrlキーとShiftキーを押しながら「↑」キーを押すと、先頭行まで一気に選択することができるので、Shiftキーを押したまま「↓」キーを1回押してタイトル行を範囲から除外します。

(Ctrlキーと「↓」キーの組み合わせについて)
・連続してセルにデータが入力されているとき
 データが入力されている最後の行に移動します。

・途中に空白行がある場合
 次にデータが入力されているセルまで移動します。

・途中にデータが入力されているセルがない場合
 ワークシートの最後の行まで移動します。

 このように、Ctrlキーと「↓」キーの同時押しは、アクティブセルを移動させる場合に用いますが、Shiftキーを一緒に押していると、その間のセルがすべて選択状態になります。

(5)関数による計算結果を値に戻すため、コピー&ペーストしたすべての関数式が入っているセルを選択してCtrlキーを押しながら「V」キーを押してコピーします。次に、「形式を選択して貼り付け」から、「値」の貼り付けを選択します。これで、文字列と数値データの混在を整理して、どちらかに統一することができたことになります。


(セルに入力するデータの種類を指定するには)
 列全体を選択状態にして右クリック。「セルの書式設定」を選択します。「表示形式」タブで「文字列」をクリックすると、その列に入力する数字はすべて文字列となります。また、同じようにして「数値」を指定するとすべて数値データとして入力されるようになります。実際には「ユーザー定義」の方を選択して「#,##0」(3桁毎のカンマを表示させる)や「#,##0,」(千円単位で表示させる)、「#,##0.00」(3桁毎のカンマを表示させ、さらに小数点以下第二位まで表示させる)などを選ぶ方が実用的であると思います。


(強制的に文字列として入力する方法)
 セルの書式設定で数値データを入力するように設定している場合でも先頭に「’」(Shiftキーを押しながら「7」のキーを押します)をつけた状態で数字を入力すると、それは文字列としてみなされます。例外処理として覚えておくとよいでしょう。
by t_am | 2012-11-26 22:54 | Excel のあの手この手