エクセルで文字の置換を行いたく、良い方法を教えてください。

A列に7種類の文字（単語）が複数あり、その文字を数字に置換したいのですが、
当該シートには7種類の文字を表示させたくありません。
本対応を行いたい場合の関数について教えてください。

自身が考えた方法としては、同一の２シートをコピーで作成。
３シート目に検索用の文字とその右列にそれぞれ置換るための数字を準備。
1シート目のA列の文字と3シート目の検索用の文字で検索し、一致した場合に置換用の数字を
２シート目にA列に表示。（事前に2シートのA列の文字は削除する）
そして、1シート目と3シート目を非表示とする。
で、どうにか関数にできないかと試案してますが、上手くいきません。
シンプルな検索と置換ができれば良いのですが、どなたか教えてください。
SUBSTITUTE、IFなどどの関数を利用したらよいか悩んでおります。

No.5 回答者： aouze_ya 回答日時： 2020/10/28 11:07

すごくバカげている方法ということになりますが、簡単な関数を繰り返し使うことでも、目的は達成出来るかもしれません。

オリジナルシートA列のセルに文字があるのを、別の文字や数値などに置き換えるのは、VLOOOKUP関数を使います。
7種の文字群を縦に並べ、隣に置換用文字数値を並べた表をつくり、その表に名前定義をします。
https://www.atmarkit.co.jp/ait/articles/1709/06/ …

オリジナルシートB列からE列(どこでも良いですけれど)に存在する7種の文字群をただ削除する場合には、SUBSTITUTE関数で、置換後を""に指定するだけですが、普通にやると、入れ子構造を使うことになります。それでもイイのですが、作業用に1つのシートをつくり、そこシートでは、第1の文字種の文字列だけを""に置換したものにします。　次に2つ目のシートをつくり、第2シートでは、第1シートから第2番目の文字種の文字列だけを""に置換したものにします。　次に3つ目のシートをつくり、第3シートでは、第2シートから第3番目の文字種の文字列だけを""に置換したものにします。　これを繰り返すと、7種でも、10種でも、指定文字列を""にすべて置換できます。

マクロを使った方が考えは楽ですが、とにかく関数だけでも、出来ます。

No.4 回答者： 銀鱗 回答日時： 2020/10/27 18:50

関数を使うのであれば、MATCH関数とINDEX関数を使い、置換する文字を決定してSUBSTITUTE関数で置換する。

という手順になります。
（MATCH関数とINDEX関数の組み合わせは、3シート目に対応表があるようなので、VLOOKUP関数やXLOOKUP関数で置き換えられます）

・・・
てか、「マクロの記録」は、手作業で行った置換の手順をマクロに置き換えるだけのものです。
質問者さんのようにマクロが分からない人向けの機能ですよ。
手作業で「置換」をする手順を一つずつ記録してマクロにしてくれるのです。
その結果が、前の回答で示したコードになります。

　「マクロの記録」を ”記録開始” して、
　「置換」機能を呼び出し、
　1つ目の置換を行う。
　2つ目の置換を行う。
　3つ目の置換を行う。
　…
　7つ目の置換を行う。
　「マクロの記録」を ”記録停止” する。

こんだけです。
記録したマクロを実行するだけで、何度でも同じ処理を行えます。

一度、ご自身で「マクロの記録」を行ってみると良いでしょう。

No.3 回答者： aouze_ya 回答日時： 2020/10/23 13:56

質問の状況がイメージにしにくいです。

❶　A列のセルには、｢7種類の文字（単語）｣の中のどれか1つが必ず入っているし、｢7種類の文字（単語）｣以外の文字などは入っていないのでしょうか。また1つのセルに、複数の｢7種類の文字（単語）｣が入っていることはないのでしょうか。

❷　｢当該シート｣というのは、Sheet１が元データのあるシートだとした場合、そのSheet1とは別のシートであって、｢当該シート｣と｢Sheet1｣の見た目の違いが、A列にだけあって、｢Sheet1｣のA列は｢7種類の文字（単語）｣のみ、｢当該シート｣のA列は｢7種類の文字（単語）を数字に置換したもの｣のみということでしょうか。

❸　｢当該シートには7種類の文字を表示させたくありません｣というのは、当該シートのＢ列、C列のどこかのセルの値か表示の中に、｢7種類の文字（単語）｣と見た目で合致する部分がある場合は、それを抹消するのでしょうか、あるいは、数字に置換するのでしょうか。

❹　｢当該シート｣は、見た目や印刷したときだけ､条件が整っていれば、実態は参照式で構成されているものでも構わないのでしょうか。

この回答へのお礼

ご質問ありがとうございます。
①どれか一つがかならず入っております。それ以外の文字は入っていません。また、複数の文字も入っていません。
②その通りです。
③抹消または数字置換どちらでも結構です。
当該シートに7種類の文字（単語）の情報を載せらないのです。
④構いません。

よろしくお願い致します。

お礼日時：2020/10/27 17:19

No.2 回答者： 銀鱗 回答日時： 2020/10/22 14:09

one　　→　1

two　　→　2
three　 →　3
four　　→　4
five　　 →　5
six　 　 →　6
seven　 →　7
とかかな？

7つだけなら、7回「置換」をすれば良いだけです。
たったの7回だよ。ホームリボンの「編集」の「検索と置換」から「置換」を選んで操作するだけです。
（Ctrl+Hで直接呼び出しても良い）
難しく考えて時間を浪費するよりも、遥かに短い時間で対処できます。

後から何度も繰り返し処理を行う必要があるなら、「マクロの記録」でマクロ化してしまえば、後は楽。
必要に応じてマクロを呼び出せば良い。

ちなみにマクロ化すると、
/***
Sub Macro1()
'
' Macro1 Macro
'

'
Cells.Replace What:="one", Replacement:="1", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False _
, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="two", Replacement:="2", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False _
, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="three", Replacement:="3", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="four", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="five", Replacement:="5", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="six", Replacement:="6", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False _
, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="seven", Replacement:="7", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub
***/
こんな感じになる。

コマンドの意味なんて考えなくても、
作業手順がそのままマクロになるって事が分かれば良い。

この回答へのお礼

ありがとうございます。出来れば、関数で処理したいです。
マクロの使い方が分からなくて．．．

お礼日時：2020/10/27 17:22

No.1 回答者： chonami 回答日時： 2020/10/22 11:32

新しいデータは入力しないという前提なんですか？

この回答へのお礼

はい。新たなデータ入力はありません。

お礼日時：2020/10/27 17:21