h00

難しいロジックは必要なく、誰でも今すぐできるExcel便利な機能を少し深掘り!!
今回は、Excelでのデータ入力の時間短縮に役立つ「置換」について解説します。

コピー&貼り付けではできない、データの一括入力や一括変更ができる置換。

空白のセルに同じデータを一括入力する。逆に特定の文字を一気に消すといった事もできますので、例えば、市区町村合併での住所変更(上水内郡豊野町を全て長野市豊野町にするような住所変更)のような変更も、置換ならミス無く一瞬で対応できます。

エクセルでデータ管理行っている(入力機会が多い)方は、ぜひ「置換」を使ってみて下さい。

エクセルセミナー

ショートカットキーは置換(チカン)する変態のHで覚えよう

置換もよく使う機能なので、ぜひショートカットキーで覚えて下さい。
置換する対象範囲を選択してから

CTRL + H

を押して下さい。
ちょっと下品ですみませんが、「チカンする変態のH」とすると覚えやすいかも知れません。

リボンで行う場合は、ホームの「検索と選択」から行います。
でも、絶対ショートカットキーの方が楽です。

h18

一括処理できる範囲は?

置換は一括してデータを書き換えてしまうので、どの範囲を処理するのかしっかり把握して実行しないと大惨事になりかねません。
元に戻せば失敗は無かった事にできますが、基本は「範囲指定をしてから置換する」と理解して下さい。

範囲指定すると、その範囲のみ置換の対象

下図のようにB54:B62までを範囲指定してから、CTRL+Hで置換を実行すると、B54:B62の範囲だけが置換による一括処理の対象になります。
h07

範囲指定をしない場合は、アクティブシート全体が対象

では、範囲を指定しない場合はどうなるのでしょうか?
初期値では、アクティブシート(今表示しているシート)全体が対象になります。
予期しないものまで置換する可能性があるので、ほとんど使いません。

ブック全体を対象にする事も可能

ブック全体を対象とする事もできますが、こちらも置換ではほとんど使いません。(検索で使う事があります。)

CTRL+Hを押し、検索と置換のダイアログを表示させ、オプションをクリックします。
h04

検索対象を「ブック」にする事で、ブック全体を対象に検索や置換ができます。
h06

検索場所を「ブック」に変更すると、セル範囲を指定した置換ができない!

検索場所を「ブック」にすると、いくらセル範囲を指定して置換を行っても、検索対象がブックとなってしまい意味がなくなってしまうので注意して下さい。
続けて置換する場合には、検索場所を「シート」に戻して下さい。
h05

※ブックを一度閉じて、再度開くと初期値(検索場所がシート)に戻ります。

置換による一括入力や一括変更の例

では、実際によく使うパターンで置換の使い方を説明します。

空白セルに指定した文字を一括入力できる

置換は、空白のセルに一括して指定した文字を入力する事ができます。
コピー&貼り付けではできないような、連続しない空白セルにも一括入力できるので、超便利ですよ。

実際に、B54:B62の範囲で空白のセルに一括して「長野市」と入力してみます。

手順1:まず、置換対象範囲を選択し、CTRL + Hを押し、検索と置換のダイアログを表示します。
h07

手順2:検索する文字列は空白にし、置換後文字列に 長野市 と入力し、すべて置換をクリックします。
h08

手順3:置換完了です。検索と置換のダイアログを閉じます。
h09

検索する文字列を空白にするのがコツです。空白を長野市に置換するという意味になります。
(既に何か入力されている場合は、DELETEで消して下さい。)

指定した文字を一気に変更できる

置換では、指定した文字を一括して変更する事もできます。
B41:B49の範囲で入力されている、「長野」を一括して「長野市」に変更してみます。

手順1:まず、置換対象範囲を選択し、CTRL + Hを押し、検索と置換のダイアログを表示します。
h01

手順2:検索する文字列に 長野 と入力し、置換後文字列に 長野市 と入力し、すべて置換をクリックします。
h02

手順3:置換完了です。検索と置換のダイアログを閉じます。
h03

検索する文字列に「旧名称」、置換後の文字列に「新名称」を入力すれば、市区町村合併による住居表示の変更や担当者の変更など色々な面で活用できます。

指定した文字を一気に消すこともできる

置換では、指定した文字を一括して消す事もできます。

例えば、下図はホームページのページタイトルをExcelに出力したものですが、サイト名である SILAND.JP が全ての行にあり、セル幅も広くなってしまい邪魔です。
このような場合にも置換が便利で、指定した文字を一括消去する事もできます。
h13

手順1:まず、置換対象範囲を選択し、CTRL + H を押し、検索と置換のダイアログを表示します。
h14

手順2:検索する文字列に | SILAND.JP と入力し、置換後文字列を空白にし、すべて置換をクリックします。
h15

手順3:置換完了です。検索と置換のダイアログを閉じます。
h16

置換は、セルの値の一部分だけを、消したり変更できたりするので便利です。

※オプションで完全一致のセルのみ置換対象にする事もできます。

数式も変更できる

置換では数式も変更できます。

例えば、MENU!C5のようにMENUシートのC5を参照している数式で
h10

MENUシートではなくM5シートを参照するように変更する場合は、検索する文字列を MENU! とし、置換後の文字列を M5! として置換を実行します。
h11

また、数式内の絶対参照の指定を消したいので$を消す・・・と言った指定もできます。
h12

セル内改行を消去するには?

Excelで入力したデータを基幹システムやデータベースソフトに取り込む際、ALT+ENTERのセル内改行が邪魔する事があります。
セル内改行を消すときも置換が使えます。(CLEAN関数を使って消すこともできます。)

検索する文字列で、CTRL + J を押します。
パッと見ただけでは分かりづらいですが、よくみると入力欄の左端でカーソルが点滅していますので確認して下さい。(慌てて連打しないで下さい。)
h19

後は、置換後の文字列は空白にして実行するだけです。
稀にこの処理を行うと「対象が見つからない」とメッセージが出て置換できない事があります。その際は、一度ブックを閉じ、もう一度ブックを開いて実行してみて下さい。置換できるはずです。

ワイルドカードを使った一括変更は便利そうだけど・・・

ワイルドカードは、トランプのジョーカーのように、万能な使われ方をする特殊文字になります。
置換は、検索する文字列にワイルドカードを使う事で、一気に複数の文字を対象にできます。

例えば、下図のリストで全ての県名を、置換を使って消してみたいと思います。
h20

長野県だけ県名を消すなら、検索する文字列を 長野県 とすれば良いのですが、全ての県(43県)を1つずつ消していくのは手間です。このような場合にワイルドカードを使います。

全ての県名を一気に消したい場合
検索する文字列を *県 とします。置換後の文字列を空白にして、すべて置換を実行します。
h21

検索する文字列を *県 とする事で、県の前の文字は何でも対象という意味になります。

一見、超便利!と思うかも知れませんが、実はこれは置換に失敗しています。
A2セルの「長野県長野市県町」が「町」だけになってしまいました。(本当は長野市県町としたかった)
h22

県町に「県」が入っているので、「長野県長野市県」を空白に置換してしまったのです。
本当は長野県だけ消したいのに、市区町村名に県が入っていたため、想定外の文字まで空白に置換されてしまいました。

件数が少なければ目視で確認できますが、1000件2000件とデータ数が増えると、そうも行きません。
このように、ワイルドカードを使うと対象が広くなるぶん、想定外のものまで一気置換されてしまう可能性があるので注意しましょう。

出典: ワイルドカード (情報処理) – Wikipedia

検索など(グロブ(w:glob (programming)))の際に指定するパターンに使用する特殊文字の種類で、どんな対象文字、ないし文字列にもマッチするもののことである。カードゲームのワイルドカードに由来する呼称。

失敗したら元に戻す

置換は一括してセルの値を書き換えてしまいます。
範囲指定を間違えた、置換後の文字列を間違えた。そんな失敗をしても慌てず CTRL+Z で元に戻して下さい。

ASC関数とJIS関数も併用すれば更に便利!

置換だけでなく関数を使った文字列の一括変更も知っておくと更に便利です。

ASC関数は、全角の英数カナを半角に変換します。(漢字や平仮名は半角になりません)
JIS関数は、半角英数カナを全角に変換します。

h17

自由に文字入力できると、半角全角が入り混じってしまう事が多いです。
このような関数を使って、半角全角を統一する事によって、フィルタによる絞り込みや集計がしやすくなります。

※UPPER関数、LOWER関数、SUBSTITUTE関数なども知っておくと便利ですが、また別の機会に説明します。

エクセルセミナー