エクセル関数について

質問

こんにちは。エクセルについて質問します。

社員No.を9桁にて付与しています。
セルに社員No.「5000」と入力したら、自動的に「000005000」と
前に「0」が付き、9桁で表示するにはどうしたら良いでしょうか？？
また、セルの書式設定→表示形式→ユーザー定義で、種類に「000000000」(0が9個)表示は9桁でされますが、セルを選ぶと9桁には
なってなく、パソコンから出てくるデータは全て9桁になってますので、社員No.を関数で検索するさい、一致しないんです。
社員No.「5000」と入力したら、「000005000」と自動的になり、
表示だけではなく、セルの文字自体が9桁になるようにお願いします。

回答No.5

kickknock

5000の入力セルに以下の方法をお試しください。

右クリック→セルの書式設定→ユーザー設定
→000000##0　を入力してOK

そのセルはご期待通りの表示になりますのであとは、書式のコピーで
必要となるセルすべてにコピーすれば完了です。
もともと、0をコピーしておけば、000000000と表示されます。

一番簡単だと思いますが。

回答No.4

merlionXX

VBAで「5000」と入力したら、「000005000」と自動的になる方法です。
以下の手順をお試しください。

そうしたいセルがA1の場合、A1でなく、例えばB2なら
コードの　"$A$1"　の部分を　"$B$2" のように変更してください。

１．シートのタブを右クリックし、「コードの表示」を選択。
２．出てきたVisualBasicEditor（白い部分）に下記のコードをコピペ。

'*****ここから下をコピペ*****

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$A$1" Then Exit Sub
If IsNumeric(Target.Value) Then
Target.NumberFormatLocal = "@"
Target.Value = Format(Target.Value, "000000000")
Else
MsgBox "数字じゃないようです。", vbCritical, "Σ（￣ロ￣lll)"
End If

End Sub

'*****ここより上までをコピペ*****

３．AltキーとF11キーを同時に押してワークシートへもどります。

これでOKです。

回答No.3

noname#140971

エクセルは操作したことのない門外漢ですのでチト外しているかもですが・・・。

＞社員No.を関数で検索するさい、一致しないんです。

ここを解決することを考えられたが良いような気がします。

=MATCH(XferSuji("000040000"),A1:A10,1)

例えば、"000040000" を 4000　に変換する関数があれば・・・。

[イミディエイト]
? XferSuji("000050000")
50000

Public Function XferSuji(ByVal strSuji As String) As Long
　　Dim I As Integer
　　Dim L As Integer

　　L = Len(strSuji)
　　For I = 1 To L
　　　　If Mid$(strSuji, I, 1) <> "0" Then
　　　　　　XferSuji = Right$(strSuji, L - I + 1)
　　　　　　Exit For
　　　　End If
　　Next I
End Function

もちろん、

=TEXT("000050000", "0")

でもＯＫです。

回答No.2

mshr1962

>表示だけではなく、セルの文字自体が9桁になるようにお願いします。
通常の設定ではできません。行なうとしたらマクロになります。
ところで、検索にどのような関数を使ってるんでしょうか？
検索用のセルと、対象のデータが共に「5000」なら何の支障もないはずです。
検索用のセルが「5000」で、対象のデータが「"000005000"」の場合は
検索用のセルの内容をTEXT(検索用のセル,"000000000")で置き換えればOKです。
例
A2=5000
B2=VLOOKUP(TEXT(A2,"000000000"),AA2:AC1000,2,FALSE)
C2=COUNTIF(AA2:AA1000,TEXT(A2,"000000000"))
D2=SUMIF(AA2:AA1000,TEXT(A2,"000000000"),AC2:AC1000)

回答No.1

grumpy_the_dwarf

データを「000005000」の様にするならあらかじめ社員番号のセルに
「文字列」書式を指定しておけばいいですが、入力は「000005000」
になります。Excelが勝手に数字を数値に変換するのを防ぎます。

どうしても頭の0を入力したくないなら、入力用のセルと表示や検索
に使うデータを保存するセルを分離するしかありません。どこかで
=text(A1,"000000000")のように変換して、そちらを参照するように
して下さい。同じセルで入力したのと違うデータにするにはVBAが必
要で、実用的ではありません。