☰

Excel の文字と数字の形式

tag-icon • tag-icon microsoft-excel worksheet-function
Excel の文字と数字の形式

特定の列に特定の形式を強制する方法がわかりません。

私が扱っているのは、在庫を追跡するために使用するスプレッドシートです。これは複数の人が使用しており、その結果、場所の形式が一貫していません。

(例: A1、A01、A-1、A-01)

これにより、A1、A2 ではなく、A1 が A10 より上になるため、並べ替えが少し面倒になります。何を入力しても、文字 + 2 桁の数字形式 (A01) に修正されるように修正したいと思います。

前もって感謝します

答え1

入力列内ではこれは不可能です。

  • 追加することもできます追加（ヘルパー）列文字列を分割し、それぞれを適切にフォーマットして、適切にまとめる数式が含まれています。
  • すべての文字列がAで始まる場合は、全員にAを落とす（ダッシュも）数字を入力するだけです（ただし、単に例を挙げただけかもしれません）
  • あなたは二列はアルファベット部分用と数字部分用
  • あなたは書くことができます大きいユーザーが入力したものを分析して並べ替えるマクロです。しかし、経験上、ユーザーが考えつく可能性のあるすべての不合理なバリエーションを処理できるマクロはこの世に存在しません。ユーザーは必ず、それを破るバリエーションを見つけます。

答え2

わかりやすいプロンプトとメッセージを備えたデータ検証を使用することもできます。データ検証式は、(文字を大文字にしたい場合) 次のようになります。

=AND(CODE(A1)>=65,CODE(A1)<=90,CODE(MID(A1,2,1))>=48,CODE(MID(A1,2,1))<=57,CODE(MID(A1,3,1))>=48,CODE(MID(A1,3,1))<=57)

答え3

次のイベント マクロをワークシート コード領域に配置します。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim B As Range, s As String
    Set B = Range("B:B")
    If Intersect(Target, B) Is Nothing Then Exit Sub
    s = Target.Value
    If Len(s) <> 3 Then GoTo fixit
    If Not Left(s, 1) Like "[A-Z]" Then GoTo fixit
    If Not IsNumeric(Mid(s, 2, 2)) Then GoTo fixit
    Exit Sub
fixit:
    Application.EnableEvents = False
        Target.Value = "A00"
    Application.EnableEvents = True
End Sub

このサンプルマクロは、列のエントリを監視して修正します。Bエントリが有効であればそのまま残されます。エントリが無効であれば、A00。

答え4

値をチェックするだけで、チェック ロジックがそれほど複雑でない場合は、標準の Excel データ検証で実行できます。ただし、値を正しく再フォーマットするには、VBA または Visual Studio Tools for Office を使用する必要があります。以下は、必要な操作を実行する簡単な VBA マクロです。

魔法が起こるのはワークシート_変更イベントが発生します。この例では、最初の列に単一の値を入力すると仮定します。正しい部品番号関数は大変な作業を行います。入力された値を分析し、正しくフォーマットされた値を返すか、検証エラーがあったことを示すために何も返しません (最大長のみをチェックします)。処理は例では機能しますが、現実の世界ではおそらく単純すぎます。正規表現または他の同様に強力な処理で簡単に拡張できます。

イベント ハンドラーは、正しくフォーマットされた部品番号でセルを更新するか、ユーザーが入力した値を太字と赤色にしてエラーを示すことで続行します。

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim NewValue As String

    If (Target.Column = 1 And Target.Count = 1) Then
        Application.EnableEvents = False
        NewValue = CorrectPartNo(Target.Value)
        If (Len(NewValue) = 0) Then
            Target.Font.Color = vbRed
            Target.Font.Bold = True
        Else
            Target.Value = NewValue
        End If
        Application.EnableEvents = True
    End If
End Sub

Function CorrectPartNo(PartNo As String)
    Dim StartPartNo As String
    Dim EndPartNo As String
    Dim EndPartNoPosition As Integer

    StartPartNo = Left(PartNo, 1)
    If (Mid(PartNo, 2, 1) = "-") Then
        If (Len(PartNo) > 4) Then
            CorrectPartNo = ""
            Exit Function
        Else
            EndPartNoPosition = 3
        End If
    Else
        If (Len(PartNo) > 3) Then
            CorrectPartNo = ""
            Exit Function
        Else
            EndPartNoPosition = 2
        End If
    End If
    EndPartNo = Right("0" + Mid(PartNo, EndPartNoPosition), 2)

    CorrectPartNo = StartPartNo + EndPartNo
End Function

関連情報