つづきを展開
☆きれいな列を作るためにすること☆
1. まず結論
よくあるズレはこの3つである。
- 空白が混じる
- 全角と半角が混じる
- 数値と文字列が混じる
これを直すには、検索前に
- 空白を削る
- 全角スペースを半角化または削除する
- 文字列にそろえる or 数値にそろえる
ことをする。
2. 社員番号用の定番式
社員番号は、ふつう 計算しない識別子 なので、
文字列としてそろえるのが基本である。
社員番号を検索用に整形する式
これは、
TO_TEXT(A2)で文字列化CLEAN(...)で見えない文字を除去TRIM(...)で前後の余計な空白を整理
という意味である。
全角スペースも混じる場合
これは全角スペースを半角スペースに直してから整える式である。
社員番号の中の空白を全部消したい場合
社員番号は通常、途中の空白も不要なので、むしろ全部消す方が強い。
これで、
- 前後空白
- 全角スペース
- 半角スペース
- 見えない文字
をかなり整理できる。
これが社員番号用の本命
社員番号、会員番号、商品コードにはかなり使いやすい。
3. 氏名用の定番式
氏名は少し注意が必要である。
名前では、途中の空白に意味がある場合があるからである。
たとえば
山田太郎山田 太郎
は運用によっては同一視したいし、別扱いしたいこともある。
氏名の前後だけ整える式
これは、
- 全角スペースを半角化
- 見えない文字を除去
- 前後や余計な連続空白を整理
である。
氏名ならまずこれが無難である。
氏名の空白も全部消して検索したい場合
表記ゆれを強引にならしたいならこうである。
これで
山田 太郎山田 太郎山田太郎
などをかなり同一視しやすくなる。
ただし、名前の空白を情報として残したい場合は使いすぎ注意である。
4. 時給や金額用の定番式
時給や金額は 値として使いたい。
だからこちらは数値にそろえる。
数字っぽい文字列を数値化する式
カンマや円が混じる場合
たとえば
1,2001,200円1200
のようなデータなら、
でかなり整う。
5. VLOOKUP / XLOOKUP に直接入れる定番
本当は補助列を作るのが安全だが、直接式に入れるならこうである。
社員番号で XLOOKUP
A2 の社員番号を検索し、D列の社員番号から E列の時給を返す：
SUBSTITUTE(TRIM(CLEAN(SUBSTITUTE(TO_TEXT(A2)," "," ")))," ",""),
D:D,
E:E,
"該当なし"
)
ただしこれだと、D列側が汚れていたら弱い。
だから本当は D列側にも整形列を作るべきである。
社員番号で VLOOKUP
たとえば F列に整形済み社員番号を作っておいて、G列に時給があるなら：
SUBSTITUTE(TRIM(CLEAN(SUBSTITUTE(TO_TEXT(A2)," "," ")))," ",""),
F:G,
2,
FALSE
)
6. いちばん実務向きのやり方
これが定番である。
元データ
- D列：社員番号
- E列：時給
補助列を作る
F列に「整形済み社員番号」を作る：
これを下までコピーする。
入力側も整形する
検索する側 A2 に対して：
その整形済みどうしで検索する
ここで B2 が整形済み検索値である。
これが一番壊れにくい。
7. 用途別に一発でまとめる
コード類（社員番号・商品コード）
空白も消して文字列化したい：
氏名
前後と全角半角だけ整えたい：
氏名を強引に同一視したい
空白を全部消す：
金額・時給
数値化したい：
8. 迷ったときの原則
- 社員番号 → 文字列でそろえる
- 氏名 → まずは空白整理だけ
- 時給・金額 → 数値でそろえる
この3つを分けると混乱しにくい。
9. 最低限これだけ覚えればよい式
まず最重要なのはこの2本である。
社員番号用
金額・時給用
この2つでかなり戦える。
10. ひとことで言うと
検索前に「整形列」を作り、コードは文字列、金額は数値にそろえる。
これが実務の定番である。
