セルの書式が標準なのに文字列にできるのはなぜ？

質問

会社のシステムで吐き出したデータですが
セルの書式設定が標準なのに文字列となっています。
マクロを利用して、あるセルの値を別セルへ転記すると、
数字は数値に変換され、先頭の０は消え、桁数が多いと指数表示になってしまいます。
会社のシステムが吐き出したエクセルはセルの書式が標準なのに
中の数字が文字列でいられるのが疑問です。

会社のシステムの吐き出したデータは
Microsoft Excel 2.1ワークシートで
全てのセルの書式設定は「標準」です。

で例えば

12345678901234
は
12345678901234
と表示されていて
「数値が文字列として保存されています」
となっています。（先頭に　'　は入っていません）

0101という値のセルでも
表示は0101ですが
「数値が文字列として保存されています」
となっています。（先頭に　'　は入っていません）

それらのセルの値をマクロとかで別セルに転記すると
転記先では1.23457E+13
と表示されてしまいます。
（0101は101になります。）

まっさらなエクセルに自分で手入力すると

・セルの書式設定が標準だと
12345678901234
↓
1.23457E+13になります。

・セルの書式設定が数値だと
12345678901234
↓
12345678901234

・セルの書式設定が文字列だと
12345678901234
↓
12345678901234
（ただし　数値が文字列と保存されています　と注釈有）

なのに会社のシステムの吐き出しデータは
セルの書式設定が標準なのにもかかわらず
12345678901234
↓
12345678901234
（ただし　数値が文字列と保存されています　と注釈有）
となっています。
手入力で書式設定が標準の状態でセルに12345678901234
と表示させるには先頭に　'　をつける方法しか知りませんが
'　が無いのに表示されています。
でもこの該当セルをマクロで別セル（書式設定：標準）に転記すると
1.23457E+13
となります。

またシステムが吐き出したエクセルの同じシート内に
以下のマクロで転記すると
↓
Sub 転記()
行 = 2
Do
If Cells(行, 6).Value = "" Then Exit Do
If Cells(行, 6).Value >= 10 Then
Cells(行, 7).Value = Range("A2")
Else
Cells(行, 7).Value = Range("A3")
End If
行 = 行 + 1
Loop
End Sub
↓
このマクロだと
A列～F列が書式設定が標準なのに文字列として表示されているのですが
転記先のG列が書式設定が標準であっても
12345678901234
（数値が文字列と保存されています　と注釈有）
と同じ状態のまま転記できます。

新しいBOOKのシート２に
会社の吐き出しデータをシートコピーした後、
（セルの書式設定：標準だが文字列で表示されている）
そのBOOKに登録されているマクロで
シート２のセルからシート１のセル（書式設定：標準）に転記すると
数字は数値に変換され、先頭の０は消え、桁数が多いと指数表示
となってしまいます。

ちょっと混乱しています。
理屈等教えていただければと思います。お願いします。

回答No.3

MackyNo1 · ベストアンサー

>Cells(行, 7).Value = Sheets("9219").Range("B44")

＞このマクロだとA列～F列が書式設定が標準なのに文字列として表示されているのですが転記先のG列が書式設定が標準であっても12345678901234（数値が文字列と保存されています　と注釈有）と同じ状態のまま転記できます。

モデルコードで検証してみました。
再入力型ではなくコピー型で変換されるのは、右辺のRangeオブジェクトのValueプロパティが省略されていることが影響しているようです。

通常はRangeオブジェクトのValueを省略しても、全く同じ結果が得られますので、私自身はあまり気にしたことはないのですが、検証してみたところ、Valueを省略すると元データと同じ書式でコピーしてくるようです（省略しないと通常のセルに入力するパターンになります）。

お礼

・会社のシステムはインポートするときに、
テキスト形式でエクセルにインポートする設定でエクセルファイルが
できあがる

・よって書式が標準なのにもかかわらず文字列で存在できる

・そのファイルはデータがある部分はそうであるが
データの無い部分は通常のエクセルの仕様で
書式が標準なら手入力するとその仕様の標準の結果になる

・マクロの書き方でセルの値をコピーする記述なら
元の値があるセルにある値をそのまま転記するので
転記先のセルの書式が標準であっても文字列のまま転記可能

・マクロの書き方が新たに入力する書き方だと
手入力と同じなのでセルの書式どうりの結果になってしまう

マクロの書き方
RangeオブジェクトのValueプロパティの事

すっきりしました。大変助かりました。
どうもありがとうございました。

補足

シート9219のB列の書式設定が標準だが
90105050050080のように文字列として表示されている
状態において、以下の2つのマクロを作成しました。

(1)Valueプロパティ省略
Sub 転記()
Sheets("Sheet1").Select
行 = 2
Do
If Cells(行, 6).Value = "" Then Exit Do
If Cells(行, 6).Value >= 10 Then
Cells(行, 7).Value = Sheets("9219").Range("B44")
Else
Cells(行, 7).Value = Sheets("9219").Range("B45")
End If
行 = 行 + 1
Loop
End Sub

(2)Valueプロパティ省略しない
Sub 転記02()
Sheets("Sheet1").Select
行 = 2
Do
If Cells(行, 6).Value = "" Then Exit Do
If Cells(行, 6).Value >= 10 Then
Cells(行, 7).Value = Sheets("9219").Range("B44").Value
Else
Cells(行, 7).Value = Sheets("9219").Range("B45").Value
End If
行 = 行 + 1
Loop
End Sub

（ア）転記先のSheet1のG列の書式設定が標準のままの場合
(1)→90105050050080　セルの値と同じ書式でコピーしてくるから
(2)→9.01E+13　　　　セルに手入力したと同じ状態だから

（イ）転記先のSheet1のG列の書式設定を
標準→文字列に変更してからマクロ実行の場合
(1)→90105050050080　セルの値と同じ書式でコピーかつ書式が文字列なので（※１）
(2)→90105050050080　セルに手入力したと同じ状態だが書式が文字列なので

となりました。すっきりしました。
ただ　※１「（イ）の(1)」はマクロ終了後にセルの書式設定が
文字列から→標準に変更されるのかと思いましたが
（セルの値と同じ書式でコピ－されるから）文字列のままでした。（謎）

もう片方ですが

(3)５行目のValueを省いた
Sub 検索02()
Sheets("Sheet1").Select
Line = 2
Do Until Cells(Line, 1).Value = ""
On Error Resume Next
Cells(Line, 2).Value = Application.WorksheetFunction.VLookup(Cells(Line, 1), Worksheets("9219").Range("A1:B1000"), 2, 0)
On Error GoTo 0
If Cells(Line, 2).Value = "" Then
Cells(Line, 2).Value = Cells(Line, 1)
End If
Line = Line + 1
Loop
End Sub

(4)
Sub 検索()
Sheets("Sheet1").Select
Line = 2
Do Until Cells(Line, 1).Value = ""
On Error Resume Next
Cells(Line, 2).Value = Application.WorksheetFunction.VLookup(Cells(Line, 1).Value, Worksheets("9219").Range("A1:B1000"), 2, 0)
On Error GoTo 0
If Cells(Line, 2).Value = "" Then
Cells(Line, 2).Value = Cells(Line, 1)
End If
Line = Line + 1
Loop
End Sub

（ウ）転記先のSheet1のB列の書式設定が標準のままの場合
(3)→9.01E+13　　　　90105050050080の予定でしたが？
(4)→9.01E+13　　　　セルに手入力したと同じ状態だから

（エ）転記先のSheet1のB列の書式設定を
標準→文字列に変更してからマクロ実行の場合
(3)→90105050050080　セルの値と同じ書式でコピーかつ書式が文字列なので
(4)→90105050050080　セルに手入力したと同じ状態だが書式が文字列なので

こっちが分かりません。どこか記述がおかしいのでしょうか？

回答No.4

MackyNo1

>ただ　※１「（イ）の(1)」はマクロ終了後にセルの書式設定が
文字列から→標準に変更されるのかと思いましたが
（セルの値と同じ書式でコピ－されるから）文字列のままでした。（謎）

文字列セルを参照する数式では、参照先の数式が自動的に変更されることがありますが、マクロで値をセルに代入しても、代入先のセルの書式設定は変更されません。

＞（ウ）転記先のSheet1のB列の書式設定が標準のままの場合
(3)→9.01E+13　　　　90105050050080の予定でしたが？

そもそもワークシート関数は値だけしか取り扱えませんので、セルにその値を入力したパターンになります。

＞（エ）転記先のSheet1のB列の書式設定を
標準→文字列に変更してからマクロ実行の場合
(3)→90105050050080　セルの値と同じ書式でコピーかつ書式が文字列なので
(4)→90105050050080　セルに手入力したと同じ状態だが書式が文字列なので

こっちが分かりません。どこか記述がおかしいのでしょうか？

この部分も同じことで、文字列書式にしたセルに入力するパターンですので、文字列数字となります。

お礼

会社のシステムから吐き出したエクセルファイルに
マクロを実装する時に、
教えていただいた事を考えて
転記先の書式を変更したり、そういう記述（できないですが）
で対応します。
大変すっきりしました。
どうもありがとうございました。

回答No.2

MackyNo1

＞会社のシステムで吐き出したデータですがセルの書式設定が標準なのに文字列となっています。

おそらく、会社のシステムはインポートするときに、テキスト形式でインポートする設定になっているものと思われます。

例えばアクセスなどのデータからエクセルにエクスポートするときも、数字が文字列としてインポートされるケースが良く報告されています。

一方、エクセルで再入力したり（マクロ処理も同じ）、あるいはテキストファイルウィザードを起動せずに値貼り付けをすると（ｃｓｖファイルをダブルクリックで開く）と、値入力となり、通常ではエクセルが自動判定した書式での表示となります（すなわち標準書式のセルに「１－２」と入力すると日付表示になるのと同じです）。

ところで以下のマクロコードのRange("A2")が固定されている意味がわからないのですが、それはさておきCells(行, 6)の値（表示形式ではなく実際にセルに入力されている値）.が10以上ならA2セルの値を代入するというコードになっていますが、このセルの値が文字列数字の場合は、文字列はどのような数字よりも大きいと判定されてしまいますので、例えば文字列数字の「１」でも条件が成立してしまいます。

If Cells(行, 6).Value >= 10 Then
Cells(行, 7).Value = Range("A2")

いずれにしろ、上記の値を代入するマクロ処理では、A2セルの値を標準セルに再入力したパターンと同じになります（文字列で取り込みたいなら、セルの書式を文字列にしておきましょう）。

以上まとめると、エクセルのセルのコピーやエクスポートでは基本的に元データ（文字列数字ならそのまま文字列数字）がセルに取り込まれますが、ｃｓｖファイルや他のアプリケーションのテキストデータの取り込みでは、テキストファイルウィザードを起動する、あるいはセルの書式を指定して形式を選択してでテキスト貼り付けするなどの処理をする必要があります。

お礼

例えば、システムが吐き出したデータをクリックして
エクセルが起動し開かれます。シート名は9219です。
A～F列までデータがあります。800行程度です。
ワークシートを挿入します。
シート名はSheet1です。

Sheet1の
セルA2に43
セルA3に44
セルA4に45
セルA5に46
と入力します。
VBEを開いて標準モジュールに以下のマクロを入れます。

Sub 検索()
'2011年1月25日
'検索する対象値があるシート選択
Sheets("Sheet1").Select
'そのシートの検索開始の行数を選択2行目。
Line = 2
'そのシートの検索値の列指定1=A列。セルA2の値が検索したい値。
'その値がなくなったら検索を終了させる.Value = ""を追加。
Do Until Cells(Line, 1).Value = ""
'エラーとなっても次に進む
On Error Resume Next
'検索結果を記入する列を指定。Line2=B列（※１）
'検索する値があるシートとその列を指定
'VLookup(Cells(Line, 1)の部分。1=A列
'検索されるシートと検索範囲を指定
'Worksheets("従業員名簿").Range("A2:B1000")→セルA2からセルB1000まで
'検索されたらその行のどの列の値を結果とするのか指定　2=B列
'検索方法指定0=FALSE完全一致。
Cells(Line, 2).Value = Application.WorksheetFunction.VLookup(Cells(Line, 1).Value, Worksheets("9219").Range("A1:B1000"), 2, 0)
'VLOOKUP関数が終了又はエラーが発生したら止まる
On Error GoTo 0
'検索されなかったときの処理。上記（※１）の部分Line6=F列に値がない
If Cells(Line, 2).Value = "" Then
'Line6=F列にLine1=A列の値を代入
Cells(Line, 2).Value = Cells(Line, 1)
End If
'2行目から開始なので次の行の値を検索値とする
Line = Line + 1
'検索する値がなくなるまで繰返す
Loop
End Sub

このマクロを走らせるとSheet1の
セルB2にシート9219のA列のどこかの行の値が
セルB3にシート9219のA列のどこかの行の値が
セルB4にシート9219のA列のどこかの行の値が
セルB5にシート9219のA列のどこかの行の値が
転記されます。

でこの場合は、シート9219のA列は書式が標準で
数字14桁で入力されているので転記された値は
9.01+E
9.1E+13
という感じ指数になります。
ご指摘いただいたように
転記先のSheet1のB列を文字列にしてから
マクロを走らせると
90105050050080
という風にシート9219のA列の表示と同じになります。

ですが以下のマクロ
Sub 転記()
Sheets("Sheet1").Select
行 = 2
Do
If Cells(行, 6).Value = "" Then Exit Do
If Cells(行, 6).Value >= 10 Then
Cells(行, 7).Value = Sheets("9219").Range("B44")
Else
Cells(行, 7).Value = Sheets("9219").Range("B45")
End If
行 = 行 + 1
Loop
End Sub

だと転記先となるSheet1のG列は
書式設定が標準であるのに
90105050050080とシート9219と同じ状態で転記され
9.01+Eなどは表示されません。

別にマクロの文内で転記先を文字列にしたり、
又は転記先のセルの書式を文字列にしておけば
解決するのですが、なぜこうなるのかがわからず
すっきりしないのです。

どうもありがとうございました。

補足

>ところで以下のマクロコードのRange("A2")が
>固定されている意味がわからないのですが、
>それはさておきCells(行, 6)の値
>（表示形式ではなく実際にセルに入力されている値）.が
>10以上ならA2セルの値を代入するというコードになっていますが、
>このセルの値が文字列数字の場合は、文字列はどのような数字よりも
>大きいと判定されてしまいますので、
>例えば文字列数字の「１」でも条件が成立してしまいます。

申し訳ありません。私はマクロの理解度は凄く低いです。
このマクロは
・マクロによって転記してもセルの書式が標準なのに文字列となったまま転記されるか？
の実験用です。

＞いずれにしろ、上記の値を代入するマクロ処理では、
＞A2セルの値を標準セルに再入力したパターンと同じになります
＞（文字列で取り込みたいなら、セルの書式を文字列にしておきましょう）。

A列～E列までデーターがあり、
それは全部セルの書式設定が標準です。
なのにもかかわらず
A列は
12345678901234は
1.23457E+13では無く
12345678901234
と表記されています。先頭に　’もないです。

掲載したマクロでこのA列が全て数字で14桁であるセルA2とセルA3を
G列に転記させようとして
F列に上から 1 とか 12 とか 3 とか 15 とか適当に入力して
この仮マクロを走らせました。
そうしたら、転記先のＧ列は書式設定が標準のままなのに
キチンと12345678901234で転記されるのです。
掲載したマクロを見ていただけばお分かりになると思いますが、
マクロで転記先を文字列にはしていません。
私は転記先は1.23457E+13と表示されると思っていたのです。
ちなみにF列はF5行目まで数値を入力しました。
G2～G5まで転記がされました。
12345678901234とか
77777777778888
となっています。

で仮マクロが走ったあとなのでG6以降は空白です。
試しにセルG6に手入力で
12345678901234としたら
1.23457E+13になってしまいました。
書式は標準のままです。

マクロもこのA列をいったん配列に取り込んでから
別セルに書き出しとかにすると
上記の例の場合
12345678901234は転記先が標準ならば
1.23457E+13となります。

これが疑問です。

回答No.1

FEX2053

ちょっとうろ覚えなんですが・・・。

AccessやOracleなどで「文字列」と定義されたテーブルを直接Excelで読み込むと、そういう現象が起きたような・・・。

お礼

このデータはシステムが
ネットワークの指定フォルダに
夜間バッチでエクセルファイルで
自動作成されます。
エクセルファイルなのでそのままクリックしてエクセルが
起動して開いています。
ありがとうございました。