Excel。個人情報保護で氏名の前後の文字を除いて伏字で表示したい【blank letters】

Excel。個人情報保護で氏名の前後の文字を除いて伏字で表示したい

＜LEFT・REPT+LEN・RIGHT関数＞

氏名など、個人情報保護上、表示することができない場合があります。

A列の氏名をB列のように、前後の1文字は表示して、中の文字は伏字。

今回は全角の「＊（アスタリスク）」に置き換えて表示したい場合の方法をご紹介します。

B2に、数式を設定します。

=LEFT(A2,1)&REPT("＊",LEN(A2)-2)&RIGHT(A2,1)

最初の文字は、LEFT関数をつかいます。

LEFT(A2,1)

これで、左から1文字を抽出することができます。

これを「&（アンパサンド）」で結合します。

伏字の部分の数式が

REPT("＊",LEN(A2)-2)

REPT関数の中にあるLEN関数から説明します。

LEN関数は文字数を算出する関数です。

その値から前後1文字分ずつ、つまり「-2」することで、伏字の数がわかります。

そして、REPT関数ですが、

REPT関数は、指定した文字列を指定した回数繰り返す関数です。

最初の引数の文字列に「”＊”」とします。これが、伏字の「＊」です。

2つ目の引数は、繰り返し回数です。

先程説明しましたLEN関数で繰り返し回数を算出しています。

最後に、RIGHT関数をつかって、右側の1文字を抽出し、結合することで、完成します。

Excel関数辞典 VOL.68。RIGHT関数～ROUNDUP関数【dictionary】

Excel関数辞典　VOL.68。RIGHT関数～ROUNDUP関数

＜Excel関数＞

今回は、RIGHT関数～ROUNDUP関数までをご紹介しております。

RIGHT関数

読み方： ライト　　

RIGHT(文字列,[文字数])

文字列の右端から文字を取り出す　

RIGHTB関数

読み方： ライトビー　　

RIGHTB(文字列,[バイト数])

文字列の右端から指定のバイト数を返す　

ROMAN関数

読み方： ローマン　　

ROMAN(数値,[書式])

アラビア数字をローマ数字に変換します　

ROUND関数

読み方： ラウンド　　

ROUND(数値,桁数)

指定桁数で四捨五入する　

ROUNDDOWN関数

読み方： ラウンドダウン　　

ROUNDDOWN(数値,桁数)

指定桁数で切り捨てる　

ROUNDUP関数

読み方： ラウンドアップ　　

ROUNDUP(数値,桁数)

指定桁数で切り上げる　

Excel。セル内でハイフンがないものだけを手早く追加入力したい【No hyphen】

Excel。セル内でハイフンがないものだけを手早く追加入力したい

＜LEFT+RIGHT関数＞

データを読み込んだあとに、データが統一されてないので、修正しないといけないケースは多々あります。

例えば、郵便番号の「-」ハイフン。

次の表を使って説明します。

B列の郵便番号のデータには、「-」ハイフンが入ってるものや入っていないものが混在しています。

ハイフンが無いデータは、数値型になっており、文字型と数値型が混在しているために、データベース機能を有効に使うことができません。

そこで、ハイフンが入っていないデータのみにハイフンを追加入力して、ハイフンを含んだデータに統一していきます。

置換する関数をつかえば、簡単に対応できると考えがちですが、そういうわけにはいきません。

例えば、REPLACE関数をつかって、C列に算出してみます。

ハイフンを追加することはできたのですが、数式が長いことで可動性が悪化しています。

=IFERROR(IF(FIND("-",B2),B2,""),REPLACE(B2,4,0,"-"))

どうしてもハイフンがあるのかないのかというアプローチになってしまうのですが、もっと簡単な関数で、シンプルに解決することができます。

一部にハイフンがあるかないのではなく、全部のデータの4文字目に「-」ハイフンを入れてしまえばいいわけです。

C1の数式は、

=LEFT(B2,3)&"-"&RIGHT(B2,4)

LEFT関数をつかって、左から3文字を抽出します。

そして、「&（アンパサンド）」をつかって、「-」（ハイフン）を挟み、RIGHT関数をつかって、右側から4文字を抽出し結合させれば、「-」（ハイフン）があろうがなかろうが、関係なく、「-」（ハイフン）を入れた文字にすることができるというわけです。

Excel。ピリオドで区切られた日付では計算でつかえない！どうしたらいいの？【DATE】

Excel。ピリオドで区切られた日付では計算でつかえない！どうしたらいいの？

＜DATE・LEFT・MID・RIGHT関数＞

見た目、日付とわかるからという感じで、何も考えないで入力している場合、Excelの機能を使えないということが、ちょこちょこあります。

例えば、次のような日付計算。

B4には、B2-B3。

C4には、C3-C3という数式が設定してあります。

B4には、#VALUE!というエラーが表示されていますが、C4は、普通に減算された結果が算出しています。

何が違うのかというと、B列の日付は、「.（ピリオド）」で区切られた日付になっています。

「.（ピリオド）」で区切られいる日付は、見た目が日付なだけで、Excelとしては、日付と認識していません。

「文字」として認識されているので、#VALUE!というエラーが表示されてしまっているのです。

最初から「/（スラッシュ）」で区切っていれば、問題はなかったのです。

今回のように、2件程度のデータならば、入力し直すことも容易ですが、件数が多い場合は、絶望的な作業となってしまいます。

そこで、「.（ピリオド）」を「/（スラッシュ）」に変換する方法を紹介していきます。

とても単純ですが、現場レベルとしては重要な作業です。

C2には、次の数式を設定してあります。

=DATE(LEFT(B2,4),MID(B2,6,2),RIGHT(B2,2))

年月日を別々のセルに算出するならば、YEAR関数やMONTH関数などをつかって、抽出するわけです。

ところが、「.（ピリオド）」で区切ってしまっていると文字型になってしまっているので、YEAR関数などの日付系の関数を使うことができません。

文字型のデータならば、LEFT関数・MID関数・RIGHT関数をつかうことで、対応することができます。

そして、DATE関数をつかうことで、日付にすることができます。

DATE関数の最初の引数は、「年」の設定です。

左から4文字が年ですから、

LEFT(B2,4)

と設定することで、「年」を抽出することができます。

次の引数ですが、左から6文字目からの2文字を抽出する必要があります。

文字列の途中の文字を抽出したいので、MID関数を使います。

MID(B2,6,2)

と設定することで、「月」を抽出することができます。

最後の引数ですが、MID関数を使うよりも、右から数えた方が簡単なので、RIGHT関数を使います。

RIGHT(B2,2)

と設定することで、「日」を抽出することができます。

あとは、オートフィルで数式をコピーしています。

ただし、日付にはなるのですが、「0（ゼロ付）」の日付になっていません。

そのため、表示形式のユーザー定義をつかって、「0（ゼロ付）」の日付にしてあげるといいですね。

なお、TEXT関数をつかってしまうと、「0（ゼロ付）」の日付にすることはできるのですが、日付文字型になってしまうので、注意が必要です。

入力するという作業では、今回のように、もったいないというか、あとで、面倒な作業が発生することもありますので、ちょっと気を遣う必要がありますね。

Excel。氏名を苗字で分割したいが区切りがないので困っています。【String split】

Excel。氏名を苗字で分割したいが区切りがないので困っています。

＜LEFT&SUBSTITUTE関数 LEN&RIGHT関数＞

データを読み込んでみたら、次のようになっていました。

氏名フィールドのデータを姓フィールドと名フィールドに分割して管理したいのですが、氏名フィールドのデータには、半角や全角の空白などもありません。

区切る場所がわからないので、簡単にわけることができません。

データタブの「フラッシュフィル」をつかったところで、区切るポイントがExcel側としても判断できないので、処理はできません。

Excel VBAでプログラムならばと考えても、区切る位置がわからないので、自動的に区切ることができません。

要するに、このようなデータを分割するには、区切りになる場所は、自分自身で用意しなければいけないわけです。

そこで、氏名フィールドのデータに区切り位置を表す、半角や全角のスペースを入力し修正するのは、面倒です。

そこで、別の列。

今回は、F列に、区切り位置の数値を用意します。

C列の姓フィールドを算出していきます。

C2には、

=LEFT(A2,F2)

と数式を設定したら、オートフィル機能をつかって、数式をコピーします。

これで、姓フィールドが算出することができました。

続いて、名フィールドの算出方法ですが、SUBSTITUTE関数をつかうと、別の列に色々算出しないで、スマートに名フィールドに、名前を算出することができます。

D2に次の数式を設定します。

=SUBSTITUTE(A2,C2,"")

数式をコピーします。

これで、名フィールドに名前を抽出することができました。

SUBSTITUTE関数をつかうと、なぜ、名前を抽出することができたのかを確認していきます。

SUBSTITUTE関数は、指定した文字列を置換することができる関数です。

SUBSTITUTE関数の引数は、

SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])

となっています。

文字列には、A2。「伊藤博文」という文字が対象です。

検索文字列には、C2。先程算出した、苗字を検索するわけです。

置換文字列には、「””」。「””」は空白という意味なので、苗字のところを空白に置換することができます。

苗字が空白になるので、残ったのが名前という仕組みです。

SUBSTITUTE関数は、色々使える重宝な関数です。

また、手順は増えてしまいますが、SUBSTITUTE関数を使う方法以外にも、RIGHT関数を使う方法もあります。

氏名フィールドの文字数をLEN関数で算出して、その数値を、F列の区切り位置の数値を減算すると、名前の文字数が算出できます。

あとは、算出結果をつかってRIGHT関数で名前を抽出する方法もあります。

Excelのデータは、分割よりも結合するほうが、簡単なので、できれば、最初から、姓と名を分けておくことをおススメします。

Excel。合計値の数値をセル一つずつに分割して表示するには【Split character】

Excel。合計値の数値をセル一つずつに分割して表示するには

＜CONCATNATE&LEFT&RIGHT&COLUMN&SUM関数＞

現場には様々な帳票がありまして、その中には、
なんでそんな風にしちゃったのかなぁ～という帳票もあるようでして、
例えば、次のような帳票がその一例ですね。

2行目の金額が、一つのセルにあるのではなくて、
セルごとに数値が入力されている状況なのです。

たぶん、かつて使っていた紙の帳票のレイアウトを基に同じように作ったので
このようなことになってしまったと推測はできますが、
変更しようとしても、帳票番号などを取っていると変更もままなりません。

そこで、今回は、3行目の消費税と4行目の合計を算出させて、
セルごとに表示する方法を考えていきます。

【セルごとに分かれていては何もできない】

2行目の金額ですが、このままでは、2・9・4・6・1という数値でしかないので、
一つの数値にしないと、消費税も算出することができません。

G列に一つの数値にするための数式を作っていきます。

G2をクリックして、CONCATNATE関数ダイアログボックスを表示しましょう。

文字1には、万の位のB2を入力します。

同じように、一の位まで入力してOKボタンをクリックします。

29461と算出されました。

数式は、
=CONCATENATE(B2,C2,D2,E2,F2)
”&”で結合させてもOKです。

ただ、今は、文字型の数字になっているので、
×1をして数値型に変更しますので、数式を修正します。

=CONCATENATE(B2,C2,D2,E2,F2)*1

次に、G3に消費税を算出します。

算出した結果をセルごとに分けて表示させていきます。

今回は、小数点は切り捨てにすることにしますので、
ROUNDDOWN関数を使っていきます。

数値には、G2*8%
桁数は、小数点は不要なので、0と入力します。
OKボタンをクリックします。

G3の数式は、
=ROUNDDOWN(G2*8%,0)

G4には、合計を算出させますので、SUM関数を使って算出させましょう。

G4の数式は、
=SUM(G2:G3)

今のところ、このようになっています。

あとは、算出した結果をセルごとに振り分けて
表示させてあげればいいわけですね。

【一文字ずつ振り分けるのがポイント】

3行目の消費税の万の位である、B3に数式を作っていきます。
最初に、LEFT関数ダイアログボックスを表示しましょう。

仮に、文字列に算出した数値のG3を入力して、
文字数はセルひとつに一つの数値ですから、1としてしまうと、
万の位にも関わらず、千の位の2が表示されてしまいますので、
アイディアが必要になります。

左から1文字を抽出したいけど、空白があるわけですね。

そこで、RIGHT関数を使って、右側から指定した文字数を抽出して、
その左側の数値を持ってくるようにしてあげればいいわけです。

指定した文字数が変われば、桁数をスライドさせることが出来ます。

わかりにくいので、一度、RIGHT関数を使って動きを確認しましょう。

文字列には、
" "&$G3
と入力します。

スペースを結合させているのは、位がないケースの対策です。

スペース一文字でいいの？と思われた方もいるかもしれませんが、
最終的に一番左の文字を抽出させたいわけですから、
先頭が空白ならば別に構いません。

G3を$G3と複合参照にしているのは、
オートフィルで数式をコピーするためですね。

文字数には、
7-COLUMN()
スライドさせたいので、右側から何文字抽出するのかを指示させるのですが、
このように数値が変動する場合には、数式を列方向にコピーをしますから、
COLUMN関数を使ってあげると便利です。

7は、逆算して求めた値です。

F列の一の位を抽出したいわけですから、
1を求めたいF列はCOLUMN()で6なので、7-6で1というわけですね。

では、OKボタンをクリックして、オートフィルで数式をコピーしましょう。

B3の数式は、
=RIGHT(" "&$G3,7-COLUMN())
です。

それでは、このRIGHT関数の考え方を踏まえて、
改めてLEFT関数から数式を作っていきます。

OKボタンをクリックして、オートフィルで数式をコピーしましょう。

B3の数式は、
=LEFT(RIGHT(" "&$G3,7-COLUMN()),1)
ですね。

算出はできたのですが、数値が文字型になってしまっています。

単純に右揃えにすればいいのですが、
数式でクリアしたい方は数式を修正する必要があります。

B3には、” “というスペース（空白）という文字が入っていますので、
数式に×1としてしまうと、エラーが発生するので、IF関数を使って修正します。

=IF(LEFT(RIGHT(" "&$G3,7-COLUMN()),1)=" ","",LEFT(RIGHT(" "&$G3,7-COLUMN()),1)*1)

これで完成しましたね。

このように、なかなか厄介なので、
本来ならこのような帳票は避けたいところですね。

Excel。数値を一つずつセルごとに表示するにはどうしたいいの？【Number】

Excel。数値を一つずつセルごとに表示するにはどうしたいいの？

＜LEFT&RIGHT&COLUMN関数＞

文字を一つずつセルごとに表示する方法をご紹介したところ、
その下に表示していた数値も同じようしても、
一つずつのセルに表示するのがうまくできないので、
教えてほしいということで、

今回は、文字ではなくて、
数値を一つずつセルごとに表示する方法をご紹介していきます。

前回は、【長宗我部元親】さんを使って、1行目を作ったわけですね。
D1の数式は、
=MID($A$1,COLUMN(A1),1)
でしたが、A1を123456789と変更してみましょう。

数値なのに、1行目は、文字と同じように、
左側から始まってしまっていますね。

3行目は、右側に詰まっていますよね。

前回のMID&COLUMN関数では、対応できないわけなんですね。

では、少しずつ、数式を作っていくことにしましょう。

今回は、右側に一の位が表示したいわけですね。
となると、文字列の一番右側の一文字をN列に表示したいわけです。

そこで、右側の文字を抽出できる、RIGHT関数を使うのと、
N列が一の位。M列が十の位。L列が百の位。とスライドさせる。

すなわち、オートフィルで数式をコピーすることも考慮すると、
COLUMN関数も使った方がいいでしょう。

では、N1に次のような数式を作ってみましょう。
=RIGHT($A$3,COLUMN())
結果は、当然ながらダメですよね。

この数式だと、COLUMN()はN列=14を使ってしまうので、
右側から14文字を抽出しちゃいます。

一文字だけほしいので、1にする必要があります。ということは、

15- COLUMN()と修正してみましょう。

一の位が表示できましたので、オートフィルで数式をコピーしてみましょう。

ありゃま。完成ではないんですね。

十の位を表示したいM列は、右側から2文字分を表示してしまっています。

89のうち、今度は、左側の1文字を抽出したいわけなので、
LEFT関数を使って、数式をアレンジしていきます。

=LEFT(RIGHT($A$3,15-COLUMN()),1)

ではオートフィルで数式をコピーしてみましょう。

アレレ。まだ完成じゃないんですね。

一桁ずつ表示できたと思ったら、
数値がない桁が空白になっていませんね。

これを解決しないといけませんね。

IF関数とか色々考えてしまいそうですが、
よく考えれば、左側から1文字抽出するようになっていますので、
最初に空白を追加した状態にしてあげるとよさそうですね。

数式を次のようにアレンジしていきます。
=LEFT(RIGHT(" "&$A$3,15-COLUMN()),1)

オートフィルで数式をコピーしてみましょう。

これで完成しましたね。
動きを確認してみましょう。
E列を使って動きを確認してみましょう。

RIGHT(" "&$A$3,15-COLUMN())の引数は、
15-5なので、右側から10文字を抽出した結果は、
” 123456789”と空白が先頭についています。

その左側の1文字なので、空白が抽出されます。

このように、数値の場合は、ちょっと複雑になりますが、
請求書などで使うこともあるかもしれませんね。

Excel。NAME。苗字と名前で分割したいけど、名前の抽出がわかりにくいので、どうにかならない？

Excel。苗字と名前で分割したいけど、名前の抽出がわかりにくいので、どうにかならない？

＜SUBSTITUTE関数・RIGHT+LEN+FIND関数＞

次のような表を作りたいということなのですが…

氏名があって、苗字と名前に分けたいという、
文字列操作系の関数でお馴染みのパターンですね。

LEFT関数とRIGHT関数をはじめ、LEN関数やスペースを見つけて、
そこまでの文字数を数えるFIND関数など、
文字列操作としては非常にいい練習になりますが、
実務において、「関数をどう組み合わせましたっけ？」ということをよく耳にします。

特に、苗字の方は、
比較的数式を作ることは出来るようなのですが、
名前の方が苦手な方が多いようです。

そこで、実は、今回のように、後半を抽出するにあたり、
関数を複数使わなくても、抽出することが出来るので、その方法をご紹介していきます。

なお、苗字のC列はどのような数式が設定されているかというと、

C3の数式は、

=LEFT(B3,FIND(" ",B3)-1)

半角スペースを見つけて、
その前までが苗字に該当するので、-1（マイナス1）する方法で抽出しております。

では、RIGHT関数を使った、オーソドックスな手法で、
D列の名前を算出した場合の数式は、次の通り。

=RIGHT(B3,LEN(B3)-FIND(" ",B3))

これは、全体の文字数をLEN関数で算出して、
半角スペースの場所を減算した数字が、名前の文字数と合致するので、
このような、RIGHT関数とLEN関数とFIND関数を使って算出する必要があるわけです。

ところが、実は、もっとシンプルで簡単に抽出することが出来るので、
その方法を今からご紹介していきます。

ただし、条件があって、今回のように苗字が先に抽出してあるようにして、
抽出したいものが「あまりもの」というようにしておく必要があります。

で、登場する関数は、【SUBSTITUTE関数】だけ。

この関数は、文字を置換してくれる関数ですね。

とりあえず、数式を作ってから、ご説明したいと思います。
直接、数式を作ってもいいのですが、ダイアログボックスで作っていきます。

D3をクリックして、SUBSTITUTE関数ダイアログボックスを表示しましょう。

文字列は、B3。

検索文字列には、C3&” “。
これは、苗字と半角スペースという意味ですね。

置換文字列には、””。
空白ということですね。

置換対象は、そのままでOKです。

あとは、OKボタンをクリックしましょう。
そして、数式をオートフィルで数式をコピーしましょう。

完成したD3の数式は、

=SUBSTITUTE(B3,C3&" ","")

ですね。

けど、結果は、

きちんと、名前が抽出されていますよね。

では、数式の説明をしますが、
先程も、「あまりもの」と書きましたが、考え方はいたってシンプルで、
苗字はすでに抽出済みなので、
その苗字に半角スペースまで含めた文字列を空白に置換してしまえば、「あまりもの」。

残ったのは、名前。ということになりますよね。

ということで、右側の文字を抽出するには、
RIGHT関数というアイディアもいいのですが、

SUBSTITUTE関数という方法も知っていると、いいかもしれませんね。

Excel。DATE。数値を元号表示の日付に変えたいけど、うまくいかないので、どうしたらいい？

Excel。数値を元号表示の日付に変えたいけど、うまくいかないので、どうしたらいい？

＜TEXT関数・LEFT関数・MID関数・RIGHT関数&DATE関数＞

最初から日付として入力されているデータならば、
単純に表示形式を変更することで、和暦にしたり、西暦にしたり、
年月日にしたりなどできるのですが、

下記のようなデータの場合、なかなか、大変な作業が必要になるのです。

では、このようなことをしたいわけです。

A列に申込日というのがあって、単純に日付を数値として入力してあるそうです。

さらに、年は元号というものなのですが、
これをB列のように西暦表示にしたいというのが今回のやりたいことなのです。

別に難しくはないでしょう？と思った方も多いかと思いますが、
今回の曲者は年が元号ということなのです。

西暦の場合だと、どうなるのかを確認してみましょう。

次の表を見てみましょう。

こちらの場合は、A列は、西暦で数値を入力しております。

B列のように、こちらは、/（スラッシュ）で区切られている西暦表示にしてあります。

B8の数式は、

=DATE(C8,D8,E8)

関数に慣れている人でしたら、
LEFT関数などをネストしながら算出することもできると思いますが、

今回は、C列に年。D列に月。E列に日。
を求めて、DATE関数を使って日付を算出させています。

C8には、=LEFT(A8,4)
左から4文字分ということですね。

D8には、=MID(A8,5,2)
5文字目から2文字分ということですね。

E8には、=RIGHT(A8,2)
右から2文字分ということですね。

という数式がそれぞれ設定されております。

ということで、西暦の場合は、それほど困らずに算出することができます。

なお、表示形式で、/（スラッシュ）が入る表示形式に変えたらいいのでは？
と思われるかもしれませんが、それはできません。

日付。Excelではシリアル値というもので管理しております。
1を1900年1月1日として、一日を1としてカウントするようになっております。
ですから、1900年1月1日から20160614日後を指すことになってしまうので、
表示形式では対応できないわけです。

そこで、本題に戻りますが、このシリアル値のこともあり、表示形式は使えません。

C列の年。D列の月。E列の日。は先ほどと同じように算出しております。

先ほどの西暦は、DATE関数を使って算出できましたが、今回はどうでしょうか？

B3をクリックして、DATE関数ダイアログボックスを表示しましょう。

年には、C3
月には、D3
日には、E3
これで、OKボタンをクリックしましょう。

残念ながら、DATE関数ではダメでした。要するに年が元号という認識がないわけです。

だから、今回のように簡単に思われるかもしれませんが、一筋縄では、いかないわけです。

今回は、TEXT関数と表示形式を使って算出していきます。

C3の数式は、

=TEXT("H"&C3&-D3&-E3,"yyyy/mm/dd")*1

というように設定していきます。

それでは、C3をクリックして、TEXT関数ダイアログボックスを表示しましょう。

値の"H"&C3&-D3&-E3　ですが、
"H"は元号の頭文字です。平成なのでHです。

もし昭和の場合は、Sで作成する必要があります。&で年月日を結合させるのですが、
-（ハイフン）を年月日の間にいれることで、文字タイプの日付型に変えることができます。

/（スラッシュ）を入れたいところですが、/（スラッシュ）では、結合することができません。

この時点で、値の結果は、H27-5-9　となるのですが、
西暦表示にしたいので、TEXT関数を使って、表示形式を"yyyy/mm/dd"とします。

そしてOKボタンをクリックしましょう。オートフィルを使って数式をコピーしましょう。

とりあえず、完成しました。

ただ、完成ではあるのですが、先ほど作りました、
B8は、右揃えになっていますが、B3は左揃えになっていますよね。

本来日付は、右揃えになるはずですよね。

実は、TEXT関数を使って求めたB3:B5は、文字タイプの日付になっているのです。

そこで、×1をすることで、シリアル値にすることができるようになりますので、

B3の数式の後ろに×1を追加します。
よって、
B3の数式は、

=TEXT("H"&C3&-D3&-E3,"yyyy/mm/dd")*1

となっているわけです。

このように、日付を用途に合わせて、表示をかけたい場合、
簡単にできないケースもありますので、最初から、
日付形式で入力するように心がけるといいかもしれませんね。

Excel。hyphen。ハイフンがあったりなかったりで、ないものだけにハイフンをいれたい。

Excel。ハイフンがあったりなかったりで、ないものだけにハイフンをいれたい。

＜LEFT+&+RIGHT関数＞

少し前に、ハイフンが入ってないデータにハイフンを入れる。
または、その逆で、
ハイフンが入っているデータからハイフンを削除するということをご紹介したのですが、
先日、データによって、文字が入っていたり、いなかったりする表があって、どうやったら、
データを綺麗にすることは出来ますかね？というご相談を受けたのですが、

そもそも、データがバラバラということ自体が、NGなわけですが、やはり、現場。

いろんな方がExcelをいろんな風に使っているわけで、
想定していないことも発生しちゃうわけですね。

そこで、
今回はハイフンが入ってないデータにハイフンを入れるように修正する方法をご紹介します。

さて、以前ご紹介したことがある、ハイフンを入れるために使ったREPLACE関数を使えば、
別に問題がないように思うかもしれませんが、そうはいかないのです。

次の表をご覧ください。

A列の元データにすでに、ハイフン(-)が入っているものをREPLACE関数を使ってしまうと、
ハイフンが二重になってしまうのです。

よって、REPLACE関数を使っての変更をすることが出来ないのです。

そこで、このような場合、
データの構成がどのようになっているのか？というパターンを見つけていきましょう。

ハイフン(-)は、何文字目にあるのかを考えてみましょう。

左から4文字目。右から5文字目に必ずハイフンがはいってほしいわけです。

なので、ハイフンが入っているデータでも、入ってないデータでも、かまわずに、
左から3文字にハイフン(-)を加えて、右から4文字を加えたものを作るようにしてあげれば、
いいことがわかります。

それでは、B列のデータを削除して、B2をクリックしましょう。

左から3文字ということなので、LEFT関数ダイアログボックスを表示しましょう。

文字列には、A2。
文字数には、3。
OKボタンをクリックしましょう。

=LEFT(A2,3)

という数式が出来ました。このあとに &”-“&と入力しましょう。

そうしたら、残り右側の文字列を合体させますので、
RIGHT関数ダイアログボックスを表示させましょう。

文字列には、A2。
文字数には、4。
入力したら、OKボタンをクリックしましょう。

これで、完成しましたので、オートフィルで数式をコピーしてみましょう。

なお、数式は、

=LEFT(A2,3)&"-"&RIGHT(A2,4)

本当ならば、データが統一されていてほしいのですが、
そういうことばかりとは限りませんので、
様々なパターンパターンで対応できるようにExcel力を日々アップできるといいですよね。

Excel。phonetic。氏名のフリガナを苗字と名前に分けて表示をしたい

Excel。氏名のフリガナを苗字と名前に分けて表示をしたい

PHONETIC関数

ご質問がありまして、以前紹介した、氏名を苗字と名前に分割する方法で、
氏名を分割する方法はわかったんだけど、
氏名のデータを元にフリガナを苗字と名前に分けて表示することって出来ますか？というもの。

なるほど、確かに現場レベルでないと出てこないご質問ですね。

ただ、先に話しておきますが、Excelというか、テーブルのフィールド管理のポイントは、細かく。
が大切で、分割よりも結合の方が簡単に出来ますので、その点も含めて、
最初の段階でしっかり、フィールドを考えるほうがいいと思います。

では、下記の表があります。

このように、氏名から直接、苗字と名前のフリガナを抽出したいという訳ですね。

フリガナを算出する関数である、PHONETIC関数がいっぱい出てきますので、
煩雑でわかりにくいという方は、B列に一度、PHONETIC関数を使って、
フルネームのフリガナを算出して、それを利用したほうがわかりやすいかと思います。

では、早速作っていくことにします。

B3をクリックしましょう。

まずは、苗字ですが、左側から何文字というように算出させますので、
LEFT関数を使いますので、LEFT関数ダイアログボックスを表示しましょう。

文字列には、A3のフリガナ情報を抽出させる必要がありますので、
PHONETIC関数ダイアログボックスを表示しましょう。

OKボタンをクリックしてはいけません。
まだLEFT関数を作り始めたばかりです。

では、LEFT関数ダイアログボックスに戻り、
今度は、LEFT関数ダイアログボックスの文字数を入力していきます。

この文字数は、空白までの文字数になります。

今回A列の苗字と氏名は【全角空白】で分けておりますので、その全角空白を見つけるために、
まずは、FIND関数ダイアログボックスを表示しましょう。

検索文字列は、”　“　全角空白ですね。

対象は、A3のフリガナですから、PHONETIC(A3)。入力の仕方は先程と同じですね。

これで、全角空白のある場所がわかりましたが、その前までですから、
マイナス1する必要がありますので、LEFT関数ダイアログボックスに戻りましょう。

文字数の数式。FIND("　",PHONETIC(A3))の最後に-1を入力して、
FIND("　",PHONETIC(A3))-1
とします。

そしてOKボタンをクリックして、オートフィルで苗字のフリガナを算出しましょう。

まずは、苗字が完成しましたので、続いて名前を抽出していきましょう。

名前は、全角空白のあとの文字数ということになりますので、
C3をクリックして、MID関数ダイアログボックスを表示しましょう。

文字列には、PHONETIC(A3)　を入力します。今回はこればっかりですね。

開始位置は、全角空白の次の文字ですから、全角空白を見つけないといけませんので、
FIND関数ダイアログボックスを表示しましょう。

検索文字列には、”　“ 全角空白　を入力します。
対称は、おなじみ、PHONETIC(A3)
そうしたら、MID関数ダイアログボックスに戻りましょう。

開始位置の数式が、FIND("　",PHONETIC(A3))だと、3になっていて、
この次の文字からになりますので、プラス1をする必要がありますので、

開始位置は、FIND("　",PHONETIC(A3))+1

文字数は、開始位置以降の文字数が欲しいわけですが、A3の文字数でも問題はありません。

ここには、文字数を数えるLEN関数が登場しますので、
LEN関数ダイアログボックスを表示しましょう。

文字列には、PHONETIC(A3)　を入力して、OKボタンをクリックしましょう。
そして、オートフィルハンドルを使って数式をコピーしましょう。

ちなみに、C3は、
=MID(PHONETIC(A3),FIND("　",PHONETIC(A3))+1,LEN(PHONETIC(A3)))
という数式です。

このようにすると、
氏名から直接、フリガナを苗字と名前で分割して抽出することができますので、
色々挑戦してみましょう。

なお、名前をRIGHT関数で挑戦してみると、

=RIGHT(PHONETIC(A3),LEN(PHONETIC(A3))-FIND("　",PHONETIC(A3)))

という数式になりますが、長さがあまり変わりませんね。