Excelのセルの取り扱い裏技集

Excelで、特定の区切り文字の前後の文字を削除する方法

Excelを使っていて、セルを操作するときに、こんなことが簡単にできたらいいなと思うような裏技をご紹介していきます。

まずは、まずは、この図の『A列』のようなデータがあるとします。
この例では、県名と都市名が「：」で区切られています。
これを、県名と都市名とに分けたいといった場合に、最初の「：」の前と後に分ける必要があります。

①の列には、「：」の前、すなわち県名だけを抽出します。
➁の列には、「：」の後、すなわち都市名だけを抽出します。
➂の列には、少し応用編ですが、二番目の「：」を境として、その前だけを抽出します。
④の列には、二番目の「：」を境としてその後だけを抽出します。

これらは、すべて、LEFT関数・RIGHT関数、LEN関数、FIND関数などを用いて関数式を入力し、後はコピペするだけで簡単に作ることができます。

一番上の行のセルに関数式を入力し、あとはその列に対してコピペするだけで完了です。
最後に、もう一度その列を指定し、コピー⇒値のみを貼り付けとすればOKです。

①「：」より前だけを表示する

B1=LEFT(A1,FIND("：",A1)-1)
ここでは、区切りの文字を「：」としていますが、「：」以外の文字を区切り文字にする場合は、「：」の部分に代入すればOKです。
例えば、スペースでは、「”：”」の代わりに「” ”」に置き換えれば問題ありません。

➁「：」より後だけを表示する

C1=RIGHT(A1,LEN(A1)-FIND("：",A1))
あるいは
C1=MID(A1,FIND("：",A1)+1,LEN(A1)-FIND("：",A1))
ここでは、区切りの文字を「：」としていますが、「：」以外の文字を区切り文字にする場合は、「：」の部分に代入すればOKです。
例えば、スペースでは、「”：”」の代わりに「” ”」に置き換えれば問題ありません。

➂２つ目の「：」より前だけを表示する

D1=LEFT(A1, FIND("：", A1, FIND("：", A1)+1)-1)

④２つ目の「：」より後だけを表示する

E１=RIGHT(A1, LEN(A1)-FIND("：", A1, FIND("：", A1)+1))

Excelで、ｎ番目の区切り文字の前後で文字を分ける方法

さて、区切り文字について、最初あるいは２番目の区切り文字「：」やスペースなどで分ける方法をご紹介しました。
それでは、３番目になるとどうなるのかということですが、関数を入れ子にして、あとはその応用になります。
図でいうと、⑤と⑥は、３番目の「：」によって分けられています。

A1のデーターをこのように分けるにはどうしたら良いかというと次のようになります。

⑤　３つ目の「：」より前だけを表示する

=LEFT(A1, FIND("：", A1, FIND("：", A1, FIND("：",A1)+1)+1)-1)

FIND("：",A1)+1の部分を
FIND("：", A1, FIND("：",A1)+1)+1)/span>で囲み、さらにそれを
FIND("：", A1, FIND("：", A1, FIND("：",A1)+1)+1)-1で囲んでいます。

⑥　３つ目の「：」より後だけを表示する

=RIGHT(A1, LEN(A1)-FIND("：", A1, FIND("：", A1, FIND("：",A1))+1))

⑦　コピーしたセルにフリガナを振る

コピーしたセルは、普通にExcelの『Phonetic関数』を使うことができません。
『Phonetic関数』が使えるのは、手打ちしたセルにのみで、コピペしたセルにはフリガナを振ることができません。
そこで、マクロを使うことになります。

標準モジュールに以下を追加します。

Sub ふりがな()
Selection.SetPhonetic
End Sub

そして、フリガナを表示したいセルに次のように記載します。
例えば、B1にA1にコピペした内容のフリガナを表示したい場合は、B1のセルに次のように入力します。

=PHONETIC(A1)

結合セルの隣に並んだ行の内容を一括して区切って並べたい

この例でいうと、どういうことをやりたいのかというと、A列とB列からなるデータがあった場合、D列のように表記を変えたいということです。

これは関数で一気にという訳にはいきません。
一つずつやっていくのであれば、『TEXTJOIN』関数を使い、区切り文字に「、」を設定していけばいいのですが、そうなると、いちいち「B1：B1」、「B3：B8」、「B9：B15」、「B16:B18]、・・・と設定していかなければならず、面倒くさくなってしまいます。

そこで、A列で結合されているセルの数、つまりそのブロックの行数をC列に表示します。
そのためには、マクロを作る必要があります。

この例でいうと、どういうことをやりたいのかというと、A列とB列からなるデータがあった場合、D列のように表記を変えたいということです。

そこで、A列で結合されているセルの数、つまりそのブロックの行数をC列に表示します。
そのためには、マクロを作る必要があります。

標準モジュールに以下を追加します。

Public Function CellRows(user_range As Range) As Integer
CellRows = user_range.MergeArea.Rows.Count
End Function

そして、A1の結合セルのセル数、すなわち行数を調べたいのであれば、次の式を入力します。

=CellRows(A1)

例えば、この図でいうと、C３のセルに、その行と同じA３の結合セルは、いくつの行が結合してできているのかの数字を表示したい場合、
C3=CellRows(A3)
とすれば、６と出てきます。

これを、ずっと下へコピペしていけば、①のように計算されてでてきます。
ちなみに、関東地方は７都県、甲信越は３県ですが、きちんと数字も変わってでてきています。

次に➁のセルには、
=TEXTJOIN("、",TRUE,OFFSET(B1,0,0,C1,1))
と入力します。
これを下にコピペしていきます。

『OFFSET関数』は、OFFSET(基準, 行数, 列数, [高さ], [幅])で表されます。
『TEXTJOIN関数』は、TEXTJOIN("区切り文字",TRUE（空白は無視）,文字結合する範囲)になります。
この２つの合わせ技をしているわけです。

コピペしたので、D3のセルには、次の式が入っていることになります。
D3=TEXTJOIN("、",TRUE,OFFSET(B3,0,0,C3,1))

この意味は、D3のセルには、B3のセルから数えて、下に０、横に０のセル（つまりB3）から、下にC3つまり６行、横に１列分の範囲になるので、その範囲はB3：B８になります。

そうすると➁のように結果がでてきます。

あとは、A列をコピーして、C列・D列に「値のみ貼り付け」をすれば、やりたかったような結果になります。

１つの行に区切り文字『、』で区切られた単語をそれぞれの行へ

今までは、この図では『A』を『B』の状態にする青の矢印の変換法でしたが、ここからは、『B』を『A』に変換する赤の矢印の変換法をご紹介します。

２つのシートを使って操作

このように、１つの行に区切り文字『、』で区切られた単語を、それぞれの行に分ける方法をにには、Excelで２枚のシート（Sheet1、Sheet2）を使って操作していきます。

まずは下準備

まずは、データが入っている１枚目のシートを『Sheet1』として、そこでの操作をします。
いろいろと操作をしたいので、まずは、図のように行や列を挿入して、『累計』、『項目』、『回数』という欄をつくり、下準備します。

１枚目のシートで関数で工夫する

次に、C2のセル、A2のセルにそれぞれ次の関数式を入れて、それぞれA列、C列の必要な分をコピーしていきます。
最初に、E列、この例の場合だと地方名をそのままB列の項目の欄にコピペします。

セルC２　：　=LEN(F2)-LEN(SUBSTITUTE(F2,"、",""))+1
セルA２　：　IF(C1="","",SUM(C$1:C1,1))

そして、セルC２とセルA２の式を列にコピペして、『回数』・『累計』の欄を埋めます。

セルC２に入れた式の意味
都道府県名の数だけ行を作りたいので、まずはセルの中に都道府県名がいくつあるかを関数で出してみます。
例えば、東北地方は６県あるわけですが、「６」という数字を割り出し、その数だけ「東北地方」と入力させないといけません。
したがって、『回数』の欄にそれぞれの地域の都道府県名の数を出す必要があるのです。

では、その都道府県名の数はどうやって出せばいいのでしょうか。

各セルの中にある都道府県名の数は、区切り文字である　『、』＋１になります。
そこで、各セルに入っている区切り文字『、』の数を求めたいということになります。
すると、区切り文字『、』の数は、次の式で表せます。
「区切り文字『、』の数」　＝　「各セルの文字数」　ー　「区切り文字『、』を除いた文字数」

この枠内は詳しい解説

●LEN関数●
そこでまずは、『LEN関数』を使います。
=LEN（文字数を数えたいセル）
例えば、「=LEN(F2)」で、セルF2に入っている文字数が出てきます。
これで、「各セルの文字数」は、簡単に出すことができました。

次に、「区切り文字『、』を除いた文字数」ですが、これは、区切り文字『、』を削除した状態で文字数を数えればいいのです。
そこで白羽の矢が立つのが、『SUBSTITUTE関数』です。

●SUBSTITUTE関数●
『SUBSTITUTE関数』は、セル内の文字の置換を行う関数です。
つまり、「区切り文字『、』を除いた文字数」は、区切り文字『、』を『』（空欄）に置換してしまってから文字数を数えればいいのです。
=SUBSTITUTE(文字列,検索文字列,置換文字列,置換対象)

F2のセルの中にある『、』を「空欄」にします。
最後の置換対象は、後でコピペで全て対象にするので、何も記載しません。
文字列：F2
検索文字列："、"
置換文字列：""
置換対象：

例えば、
＝LEN(SUBSTITUTE(F3,"、",""))
とすれば、『、』が削除された
「青森県岩手県秋田県宮城県山形県福島県」の文字が結果表示されます。

この文字を数えるには、これを『LEN関数』でくくればよく、
＝LEN(SUBSTITUTE(F3,"、",""))
とすることで、「青森県岩手県秋田県宮城県山形県福島県」の文字数である 18 が結果表示されます。

これを、本来の『、』が入った「青森県、岩手県、秋田県、宮城県、山形県、福島県」文字数は、
＝LEN(F3)
であり、23 と結果表示されるはずです。

ところが、都道府県名の数となると、区切り文字『、』に１を加えなければなりません。

最終的に、『セル内の「、」で区切られた単語の数（ここでは都道府県名の数）』は、例えばセルF2であれば、
＝LEN(F2)-LEN(SUBSTITUTE(F2,"、",""))
のようになります。

セルA２に入れた式の意味
＝IF(C1="","",SUM(C$1:C1,1))

おなじみの『IF関数』と『SUM関数』ですね。
C列には、「都道府県名の数」が出ているので、
=SUM(C$1:C1,1)
で、C1から、該当セルまでのC列の合計を出しています。
そして、
A2=IF(C1="","",(当該セルまでのC列の合計))
つまり
A2＝IF(C1="","",SUM(C$1:C1,1))
は、一つ上の行までのC列の合計に１加えた数になっています。
セルC1が空白でないなら、当該セルまでのC列の合計に１を加えた数が表示されるようになっています。
これは、例えば、関東地方は、最初の行から８行目から１５行目までが該当するということになります。

区切り文字『、』で区切られた分の行を展開

今まで操作していた”Sheet1” に加えて、今度は２枚目のシート "Sheet2" を作ります。
そして新たに作ったシート ”Sheet2” のセルA１に次の式を導入し、A列全体にコピーします。

=LOOKUP(ROW(),Sheet1!A:B)&""

すると、A列に、『、』で区切られた単語の数分の行が作られます。

この例の場合だと、北海道、東北地方、関東地方、甲信越のそれぞれの都道府県の数の分だけ行が作られ、そこの地方名が入力された状態になっています。
最後に、仕上げに隣の列も入力します。

セル内の区切り文字『、』で区切られた単語をセル内改行に置換

”Sheet1” に戻って都道府県名が入っている列を選択して、区切り文字『、』を「セル内改行」に置換します。

置換前の文字列：『、』
置換後の文字列：『"Ctrl"キー＋J』

『"Ctrl"キー＋J』としても、画面上には何も文字が現れませんが、そのまま気にせずに置換すると、ちゃんとF列、つまり都道府県名がセル内改行されています。

これをメモ帳に貼り付けると、地方別の区切りのところで『”』（ダブルクォーテーション）が入っているので、これをメモ帳の置換機能を使って削除します。

あとは、これを ”Sheet2” のB列に貼り付ければ、出来上がりです。
加工しやすいように、全体をコピーし、値のみ貼り付けをしておくと良いでしょう。