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。様々な数値の右側に揃えたい桁数の「0(ゼロ)」をつけるにはどうしたらいい。【digit】

Excel。様々な数値の右側に揃えたい桁数の「0(ゼロ)」をつけるにはどうしたらいい。

＜REPT＋LEN関数＞

「1」を「001」のように、左側に０（ゼロ）をつけて、桁を揃えるなら表示形式で対応することが多いと思います。

しかし、次の表のように、「1」を「100」のように、右側に０(ゼロ)をつけて、桁を揃えるとしたら、どのようにしたらいいのでしょうか。

表示形式では、「1」と「82」というように桁数が違う場合に揃えることができません。

今回は、6桁で添える場合で考えていきます。

ポイントになるのは、数値の桁数がバラバラなので、どのようにしたら、必要な桁数の0（ゼロ）を数値の右側というか、後ろ側に表示することができるのでしょうか。

そこで、REPT関数をつかえば、0（ゼロ）を繰り返して表示することができます。

そして、LEN関数で、現在の桁数を算出させて、その値は、6から減算させれば、0（ゼロ）を何桁分追加すればいいのかが、わかります。

では、B2につぎの数式を設定し、オートフィルで数式をコピーします。

=(A2&REPT(0,6-LEN(A2)))*1

これで完成します。

この数式を確認しておきます。

最初の「A2&」で、A2の後ろに文字結合させるわけですね。

REPT関数の最初の引数は、繰り返す文字を設定しますので「0（ゼロ）」と設定します。

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

ここで、LEN関数をつかうことで、A2の桁数を算出することができますので、6桁で揃えたいので、6からLEN関数の算出結果を減算します。

これで、ほぼほぼ完成なのですが、この時点だと、文字数値型になっているので、計算には使うことができません。

文字数値型だと、B8のようなSUM関数など数式で使いたくても、文字型になってしまっているので、算出することができません。

そこで、数式の最後の「*1」を追加するというわけです。

「*1」を追加することで、数値型に戻るので、算出したい時などには、便利になります。

このように、数値の右側（後ろ側）に「0（ゼロ）」をつけて桁を揃えたい場合には、このような方法もあります。

Excelの様々な関数を紹介 VOL.67。RECEIVED関数～REPT関数【dictionary】

Excelの様々な関数を紹介　VOL.67。RECEIVED関数～REPT関数

＜Excel関数辞典＞

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

RECEIVED関数

読み方： レシーブド　　

RECEIVED(受渡日,満期日,投資額,割引率,[基準])

割引債の償還価格を算出します　

REDUCE関数

読み方： リディース　　

REDUCE(initial_value,array,function)

LAMBDA関数を各値に適用し、アキュムレータの合計値を返すことによって、配列を累積値に減らします

REPLACE関数

読み方： リプレイス　　

REPLACE(文字列,開始位置,文字列,置換文字列)

指定した文字数の文字列を置換する　

REPLACEB関数

読み方： リプレイズビー　　

REPLACEB(文字列,開始位置,バイト数,置換文字列)

指定した位置からバイト数分の文字列を置換する　

REPT関数

読み方： リピート　　

REPT(文字列,繰り返し回数)

文字列を指定回数だけ繰り返して表示する　

Excel。Asterisk。文字を伏字にしたいのですが、いい方法はありませんか？

Excel。文字を伏字にしたいのですが、いい方法はありませんか？

＜LEFT関数+REPT関数+LEN関数＞

ちょっとしたご質問だったのですが、あまり使わない関数の組み合わせだったので、
それを今回はご紹介していきます。

ただ、タイトルだけでは、なんだかわかりにくいので、次の表をご覧ください。

A列に氏名が入力されていて、そのままではオープンな資料として使えないので、
氏名の一文字目はそのままで、
それ以降は、残りの文字数分を【伏字】にしたいということをやりたいわけなんですね。

今回は伏字で全角の”＊”を使っておりますが、
この伏字の数が決まっているならば、LEFT関数との文字結合で、完成しますが、
今回厄介なのは、文字数が違うということ。

ということは、
その最初の一文字目を除いて数えてあげる必要があります。

そして、その文字数分を伏字にしないといけないということ。
なので、REPLACE関数も使いにくいのです。

このような場合、Excelのベテランさんならともかく、Excelがもう少しと思っている方などは、
一つずつ区切って数式を作成していき、
最終的に合体させるほうがわかりやすいと思いますので、
E列以降を使って確認していきましょう。

E列には、最初の一文字目を抽出していきましょう。

左側から一文字ですから、LEFT関数を使います。

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

文字列は、A3
文字数は、1
としてOKボタンをクリックしましょう。オートフィルで数式をコピーしましょう。
E3の数式は、

=LEFT(A3,1)

最初の一文字目を抽出できましたね。

続いて、F列。
ここは、先に文字数を数える関数を設定していきます。
文字数を数える関数は、LEN関数ですね。

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

文字列には、A3
と入力して、OKボタンをクリックしましょう。

今、F3の数式は、
=LEN(A3)となっていますね。
今回は、この文字数から-1（マイナス1）した数をふせじにするので、
数式を=LEN(A3)-1と修正します。

それでは、オートフィルで数式をコピーします。

このF列で算出した数値の回数を繰り返して伏字である、”＊”を作りますので、
ここは、REPT関数が登場します。

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

文字列には、”＊”
繰り返し回数には、F3
として、OKボタンをクリックしましょう。オートフィルで数式をコピーします。

G3の数式は、

=REPT("＊",F3)

あとは、E3とG3を結合させてあげればいいので、C3には、

=E3&G3

とすれば、完成ですね。

なお、E列～G列までの作業列を使わないで、一発で数式を作るのでしたら、
C3に、

=LEFT(A3,1)&REPT("＊",LEN(A3)-1)

という数式を設定してあげれば、作成することが出来ますよ。

Excel。Split。セル内の文字列分割をどうしても関数を使ってやってみたい。

Excel。セル内の文字列分割をどうしても関数を使ってやってみたい。

TRIM&MID&SUBSTITUTE&REPT&COLUMN関数

以前紹介しました、セル内の文字列を分割したいというテクニック。

個人的には、区切り位置で十分対応が出来るので区切り位置でいいとは思うのですが、
【関数を使ってできませんか？】とご質問がありまして…。

結構考え方も作り方も大変なのですが、そんな声に応えてご紹介をしていきます。

なお、今回登場する関数は…

TRIM関数
MID関数
SUBSTITUTE関数
REPT関数
COLUMN関数

と、文字操作関係の関数総動員？です。

さて、下記の表があります。

B列にある文字列を分割していくわけです。
使用例としては、
ミドルネームがあるばあいの氏名とか、
組織や配属部署で分けたい場合などが使用するケースだと思います。

そして、ポイントなのですが、区切りたいところには、半角空白が一つ入力されています。

真田 源次郎 信繁

真田と源次郎の間に、半角空白が入っています。これが重要になります。

通常ですと、LEFT関数やRIGHT関数。

そして、MID関数を使って分割していくのが多いのですが、
今回のように、半角空白から次の半角空白までの文字数が一定でないのと、
いくつ区切られるものがあるのかが、一定でなく、
バラバラな場合を考慮して関数を作っていくことになります。

C2の数式は、
=TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",50)),COLUMN(A2)*50-49,50))

というのを作っていきます。
C2をクリックして、TRIM関数ダイアログボックスを表示します。

このTRIM関数は、余計な空白を削除します。
ワザと空白を付けてあるのでそれを削除する為です。

文字列には、MID関数をネストしていきます。

文字列には、今度は、SUBSTITUTE関数をネストしていきます。

SUBSTITUTE関数は、置き換えをする関数です。

文字列には、B2を入力しますが、オートフィルハンドルを使って数式をコピーしますので、
複合参照で設定しますので、$B2

検索文字列は、REPT関数をネストしていきます。

文字列は” “
繰り返し回数は　50で今回は設定します。
このREPT関数は文字を繰り返します。半角空白を50回繰り返すという処理になります。

では、SUBSTITUTE関数に戻り数式作成を続けています。

置換対象は省略しますので、さらに上のMID関数に戻ります。

開始位置は、文字列を区切りで使っている半角空白の位置のことです。

ここもオートフィルハンドルで数式をコピーすることを考慮して、
COLUMN関数を使っていきますので、COLUMN関数ダイアログボックスを表示します。

参照にはA2を入力します。MID関数ダイアログボックスに戻ります。

そして、開始位置をアレンジします。COLUMN(A2)*50-49　と*50-49というのを追加します。

最後に文字数に50と入力します。

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

コレで完成なのですが、
MID関数のCOLUMN(A2)*50-49　のところが【？】だと思われますので、
何をどうしていたのかを説明します。

C2とD2とE2には、TRIM関数を除いた数式を入れてあります。

C3とD3とE3には、LEN関数でC2 D2 E2の文字数を算出させています。

簡単説明しますと、いくつ半角空白で分岐されていて、
半角空白が何文字目に登場して、
そこから次の半角空白が何文字先なのかを、
確認することを関数を使うと煩雑になりすぎてしまいます。

そこで、半角空白を50個増やした状態にします。

その文字列から、文字＋半角空白=50文字分ずつをとりあえず、ザックリ、抽出させます。
それがC2やD2。E2は残りの文字です。

そうすると、その50文字の中に、分割したい文字が含まれているハズなので、
あとは、TRIM関数を使って、空白を取り除くという処理をしている訳です。

ですので、分割したい文字列が長い場合は、
50のところを100にして、49のところを99にするといいでしょう。

この方法を使えば、文字列を分割することが出来ますが、
やっぱり区切り位置の方が楽ではないかと思いますが、
スキルアップの為に挑戦してみてはいかがでしょうか？

Excel。条件付き書式のデータバーを使わずに大小を把握する方法。REPT関数

Excel。
条件付き書式のデータバーを使わずに大小を把握する方法。

REPT関数

Excel。条件付き書式のデータバーは便利なんですが、なんとなく、分かりにくいので、
ちょっとした関数を知っていると、同じように表現できるので、ご紹介。

下記のような表があります。

この点数を10ポイントを★一つとして、データバーに表示してみたいと思います。
20ポイントなら★★という感じですね。
こういう時は、

REPT関数

の登場です。

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

文字列に表示したい文字。今回は★と入力します。
繰り返し回数には、B2/10と入力します。

これで完成。

余談ですが、65とかにしたら、★が半分表示されることはありませんので、ご注意のほど。