Excel。LEN関数は、文字列の文字数を返す関数です。【LEN】

Excel。LEN関数は、文字列の文字数を返す関数です。

＜関数辞典：LEN関数＞

LEN関数

読み方： レン　　

分類： 文字列操作 

LEN(文字列)

文字列の文字数を返す

Excel。セル内のカンマで区切られた文字列数を知るには、どうしたらいい【Comma separated】

Excel。セル内のカンマで区切られた文字列数を知るには、どうしたらいい

＜LEN関数＋SUBSTITUTE関数＞

表のB列には、会議参加者の氏名が入力されています。

そして、氏名は、「,（カンマ）」で区切られています。

その氏名の人数を、C列に求めたい場合、どのようにしたら、手早く求めることができるのでしょうか。

 「,」で区切られているわけですから、COUNTIF関数で「,」の数を求めればいいと考えがちですが、COUNTIF関数では対応することができません。

COUNTIF関数は、セル対象であり、セル内の文字数を求めることは出来ないからです。

そこで、全体の文字数から「,」を除いた文字数を減算すれば、「,」の数を求めることができます。

その値に「+1」すれば、「,」で区切られた文字列数を求めることができます。

C2には、次の数式を設定し、オートフィルで数式をコピーしました。

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

これで、「,」で区切られた文字列数を求めることができました。

では、この数式の仕組みを確認しましょう。

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

LEN関数は、セル内の文字数を求めることができる関数です。

最初に、セル内の文字列数を求めました。

SUBSTITUTE(B2,",","")

SUBSTITUTE関数は、置換してくれる関数です。

「,」を「””（空白）」に置換します。

そして、LEN関数をつかうことで、「,」を除いた文字数を求めることができます。

全体の文字数から「,」を除いた文字数を減算すれば、「,」の数を求めることができるというわけです。

そして、文字列と文字列の間に「,」がありますので、「+1」することで、「,」で区切られた文字列数を求めることができるというわけです。

Excel。年月日が分かれいるセルから日付をつくるには、どうしたらいいの【date】

Excel。年月日が分かれいるセルから日付をつくるには、どうしたらいいの

＜DATE+LEFT+LEN関数＞

年月日がそれぞれのセルに入力されているのですが、次の表のように、数値の後ろに「年」や「月」「日」まで入力されてしまっています。

D2に、DATE関数をつかって、日付をつくってみようとしますが、#VALUE!というエラーが表示されてしまいました。

D2には、

=DATE(A2,B2,C2)

という数式を設定しましたが、DATE関数の引数は、文字ではなく、数値でないといけないので、エラーが発生したというわけです。

では、どのようにしたら、よいのでしょうか。

ホームタブの置換をつかって、「年」「月」「日」を空白にしてもいいのですが、データが追加される場合には、そのたびに、置換作業が発生します。

そこで、DATE関数で日付をつくることができないのか、考えみることにします。

そもそも、「年」「月」「日」を除くことができたら、空白にすることができたら、いいわけです。

置換する関数である、SUBSTITUTE関数をつかってもいいのですが、あまり馴染みのない関数なので、今回は、もう少し身近なLEFT関数とLEN関数をつかって、日付を作っていきます。

D2の数式は、

=DATE(LEFT(A2,4),LEFT(B2,LEN(B2)-1),LEFT(C2,LEN(C2)-1))

これで、日付を求めることができました。

関数もわかりやすい、シンプルなLEFT関数とLEN関数だけで構成してあります。

LEFT関数は、左から指定した文字数を抽出する関数です。

LEN関数は、指定した文字が、何文字なのかを算出する関数です。

では、数式の説明をします。

DATE関数は、日付を作る関数です。

引数は、年と月と日　です。

最初の年ですが、2024年なので、左から4文字にすれば、年を除いた数値だけを抽出することができます。

2つ目の引数の月ですが、3月や10月というように、数値の部分が1桁、あるいは、2桁の場合があります。

そのため、単純に左から1文字と固定することができません。

そこで、LEN関数をつかって、セル内の文字数を算出します。

そして、その算出結果から「月」の1文字分を減らした数が、数値の部分になりますので、

LEFT(B2,LEN(B2)-1)

と設定することで、数値の部分を抽出することができます。

3つ目の引数の日ですが、これも、月と同じなので、

LEFT(C2,LEN(C2)-1)

とすることで、「日」を除いた数値をつくることができます。

これらの結果をDATE関数をつかうことで、日付をつくることができます。

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。入力された文字が、すべて全角なのか手早くチェックしたい【full-width】

Excel。入力された文字が、すべて全角なのか手早くチェックしたい

＜LEN関数とLENB関数＞

住所などを全角で入力しているのか、手早く確認したい場合には、LEN関数とLENB関数をつかうことで、確認することができます。

A列には住所が入力されています。

入力されている文字が全角なのかを確認するために、B2に次の数式を設定しました。

=LEN(A2)*2=LENB(A2)

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

TRUEと表示されていれば、全角の文字。

FALSEと表示されていれば、一部全角の文字ではないということが確認できます。

では、どのような仕組みなのか、数式を確認してみましょう。

 

LEN関数は、半角も全角も1文字は1と算出します。

LENB関数は、半角は1と算出し、全角は2と算出します。

そこで、LEN関数をつかって算出した結果を2倍した値と、LENB関数をつかって算出した結果が同じならば、すべての文字が全角であるということが確認できるというわけです。

Excel。数値を一桁ごとセルにわけて表示して、桁の不足分はゼロで補いたい【character per cell】

Excel。数値を一桁ごとセルにわけて表示して、桁の不足分はゼロで補いたい

＜MID＋TEXT＋SEQUENCE＋LEN関数＞

銀行の入出金用紙などで、口座番号を一桁ずつ右から埋めていくわけですね。

次の表のように、Excelで手早くやるにはどうしたらいいのでしょうか。

例えば、5桁として、それに満たない場合には、「0（ゼロ）」をいれるようにもしたいわけです。

これを、SEQUENCE関数を中心とした、一つの数式で、手早く処理することができます。

C2には、

=MID(TEXT(A2,"00000"),SEQUENCE(1,LEN(TEXT(A2,"00000"))),1)

という数式を設定します。

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

MID+SEQUENCE+LEN関数の組み合わせで、1つのセルに1文字ずつわりふることができます。

=MID(A2,SEQUENCE(1,LEN(A2)),1)

とすれば、文字列の場合ならば、対応できるのですが、数値だと、次のように、左側から割り振ってしまいます。

そのため、表示形式のTEXT関数をつかって対応する必要があるというわけです。

数式を確認しておきましょう。

最初のMID関数は、指定の位置から文字列を抽出することができる関数です。

最初の引数が「文字列」なので、A2を設定します。

ただ、桁が足らない場合、「0（ゼロ）」を表示したいので、TEXT関数をつかって、5桁にするようにします。

なので、最初の引数は、「TEXT(A2,"00000")」と設定します。

2つ目の引数が「開始位置」です。

ここに、SEQUENCE関数をつかいます。

SEQUENCE関数の説明は後にしまして、MID関数の3つ目の引数は「文字数」です。何文字抽出するかということなので1文字ずつなので「1」と設定します。

さて、SEQUENCE関数の説明ですが、

SEQUENCE関数の最初の引数は、「行」です。複数行にまたがることはありませんので「1」と設定します。

2つ目の引数は、「列」です。何列必要なのかという、列数を設定することで、スピル機能によって、自動的に2文字目3文字目と表示してくれます。

そこで、LEN関数をつかって、文字数=列数を設定することができますので、LEN(TEXT(A2,"00000"))とします。

あとの引数は省略できますので、省略。

これで、数値の場合対応することができるというわけです。

Excel。文字列をセルに1文字ずつに分けて表示するには、どうするの【one character per cell】

Excel。文字列をセルに1文字ずつに分けて表示するには、どうするの

＜MID+SEQUENCE+LEN関数＞

SEQUENCE関数の登場で、今までは複雑な数式、あるいは、中間結果を算出してから結果を求めるなどしていた数式を、シンプルな数式にすることができます。

今回は、SEQUENCE関数をつかって、手早く、文字列をセルに1文字ずつに分けて表示する方法をご紹介します。

A列には、サンプルの文字列を用意しました。

文字数はバラバラ。

A5は、「立花 宗茂」と入力してあって、苗字と名前の間に半角スペースが入力されています。

C2に、SEQUENCE関数をつかった数式を設定します。

=MID(A2,SEQUENCE(1,LEN(A2)),1)

あとは、C5まで、オートフィルで数式をコピーするだけで、文字列をセルに1文字ずつに分けて表示することができました。

数式を確認しておきましょう。

最初のMID関数は、指定の位置から文字列を抽出することができる関数です。

最初の引数が「文字列」なので、A2を設定します。

2つ目の引数が「開始位置」です。

ここに、SEQUENCE関数をつかいます。

SEQUENCE関数の説明は後にしまして、MID関数の3つ目の引数は「文字数」です。

何文字抽出するかということなので1文字ずつなので「1」と設定します。

さて、SEQUENCE関数の説明ですが、

SEQUENCE関数の最初の引数は、「行」です。

複数行にまたがることはありませんので「1」と設定します。

2つ目の引数は、「列」です。

何列必要なのかという、列数を設定することで、スピル機能によって、自動的に2文字目3文字目と表示してくれます。

そこで、LEN関数をつかって、文字数=列数を設定することができますので、LEN(A2)とします。

あとの引数は省略できますので、省略。

これで、文字列をセルに1文字ずつに分けて表示することができるというわけです。

Excel。文字型になっている日付を日付型として使いたいけど、どうしたらいい【DATE】

Excel。文字型になっている日付を日付型として使いたいけど、どうしたらいい

＜LEFT＋LEN関数・表示形式＞

データを読み込んでみたら、日付がおかしい。

どうおかしいのかというと、文字（文字型）になっているわけです。

これでは、Excelの様々な機能を有効につかうことができません。

A列の日付は、日付はシリアル値なので数値なので、日付は右揃えになっているはずですが、左揃えになっています。

また、「=A2+1」と数式をつくってみたところ、「#VALUE!」というエラーが表示されました。

つまり、「日付」ではなくて「文字」として認識されているわけです。

このデータの原因は、「曜日」です。

曜日が付いてなければ、日付として認識されます。

まずは、対応方法をご紹介します。

色々な方法がありますが、比較的シンプルな方法をご紹介します。

C2に次の数式を設定し、オートフィルで数式をコピーします。

=LEFT(A2,LEN(A2)-3)*1

そのあと、C2:C3を範囲選択して、表示形式をホームタブの数値の書式にある「短い日付形式」に変更します。

これで、日付型にすることができました。

テキストファイルでもCSVでも曜日をつけたいなら、日付と分けてほしいところです。

C2に設定した数式の説明をしておきます。

=LEFT(A2,LEN(A2)-3)*1

なぜ、DATE関数ではなくて、LEFT関数をつかっているのか。

日付にするには、DATE関数をつかうというのがスタンダートなのですが、年と月と日をどうやったら抽出することができるのかを考えると、月と日をどのように抽出するのかというのが、問題になります。

例えば、2月の場合MID関数で、6文字目から1文字とすれば、抽出できますが、11月の場合だと、2文字抽出するように数式を設定しなければいけません。

同じように、日も、月によっては、何文字目から抽出したらいいのかも、異なるので、複雑な数式を用意する必要があります。

そもそも、曜日が邪魔なだけなので、曜日をカットした状態をつくればいいわけです。

そこで、LEFT関数をつかうわけです。

LEN関数をつかって、セル内の文字数を算出させて、曜日の「（曜日）」の3文字分の減算した値を左から抽出することで、文字数値型になります。

文字型を数値型にするVALUE関数をつかってもいいのですが、ネストだらけになってしまうので、VALUE関数をつかわずに、文字数値型に「×1」することで、数値型にすることができます。

また、算出結果に、TEXT関数で、年月日の短い日付形式をネストすると、また文字数値型に戻ってしまうので、表示形式をつかいました。

簡単に思われる処理も色々面倒だったりしますので、Excelにインポートする前に解決できるものは解決しておきたいところですね。

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。セルごとに分割された数値を合算し、その値もセルごとに分割したい【cell merging】

Excel。セルごとに分割された数値を合算し、その値もセルごとに分割したい

＜CONCAT・SUM・IFERROR・MID・LEN・COUNT関数＞

なんでそんなことしちゃったのという、Excelの帳票をみることがあります。

例えば、数値をセルごとに分割してある帳票。

そもそも、1500と分割しないでセル内にあれば、単純な和算で済むわけですが、セルごとに分割してあるわけですね。

このような場合、一の位で合算して、繰り上がったら…なんてやっていたら、桁数が増えたら大変というか、数式を考えるだけでも大変です。

最初にやることは、結合して1つのセルにまとめて数値にします。

A6に次の数式を作り、交通費を一つのセルにまとめます。

A6に設定した数式は、

=CONCAT(B2:G2)

同じように、A7に、宿泊費を一つのセルにまとめます。

A8には、合算値を算出したいので、A8に設定する数式は、

=SUM(A6:A7)

ところが、「0」と算出されてしまいました。

原因は、よくみると、A6とA7は、左揃えになっています。

これは文字型になっていることを表しています。

CONCAT関数は、文字結合をする関数なので、算出されたものは、文字になっていて数値ではないわけです。

そこで、A6とA7の数式のうしろに「*1」を追加します。

=CONCAT(D2:G2)*1

こうすると、文字型は文字型でも、数値文字型に変わるので、合算値を算出することができます。

あとは、どうやって、一つずつのセルに分割して表示させる数式を考えていきます。

合計のB4の数式は、

=IFERROR(MID($A$8,LEN($A$8)-COUNT(C1:$G$1),1)*1,"")

あとは、この数式をG4まで、オートフィルで数式をコピーすれば完成です。

数式が長くなったので、説明していきます。

B1:G1までの見出し行は、「十万・一万…一」と表示してますが、元は、普通の数値が入力してあって、表示形式をつかって、漢数字にしています。

別に直接、漢数字で入力しても問題はありません。

最初の「IFERROR関数」は、数値がない場合、#VALUEというエラーが発生するので、エラーならば空白にするようにしています。

MIDはセル内の文字列を指定の場所にある、指定した文字数分だけ抽出する関数です。

先程、合算値を算出したA8を指定します。

オートフィルで数式をコピーしますので、絶対参照を忘れずに設定します。

何文字目の文字なのかを指定します。

「LEN($A$8)-COUNT(C1:$G$1)」

LEN関数は、文字数を算出する関数です。これで合算値が何桁なのかを算出します。

その値から、C1:G1の数値の個数を算出した値を減算します。

桁数をずらす必要があるので、終点のみを絶対参照に設定しておきます。

今回の例としては、

LEN($A$8)は、「5」

COUNT(C1:$G$1)は、「5」

5-5=0なので、0文字目を抽出ということになるのですが、0文字目を抽出というのは、おかしいので、エラーが表示されるわけですね。

このように、ちょっと複雑な数式になっていますね。

帳票上の必要があるかもしれませんが、基本的には、数値を一つずつ、一つのセルごとに分割する場合には、アレコレ、アイディアが必要になりますね。

Excel。セル内の文字に半角が含まれているか、手早く知るにはどうしたらいい【Half size】

Excel。セル内の文字に半角が含まれているか、手早く知るにはどうしたらいい

＜LEN+LENB関数＞

セル内の文字。半角の文字が含まれているのか、また何文字なのかを手早く知りたい時には、どのようにしたらいいのでしょうか？

次の表を用意しました。

A2とA3。違いは、「@（アットマーク）」。

A2は半角の「@」で、A3は全角の「＠」がセル内に含まれています。

よく見ないと、半角が全角か、わかりにくいわけですね。

ついでに、セル内に半角文字が何文字含まれているかも、わかるようにしたいわけです。

文字コードとも考えたいのですが、文字コードで判定するのは大変です。

そこで、着目するのは、全角と半角の違いです。

B2には、次の数式を設定しました。

=LEN(A2)*2-LENB(A2)

これで、セル内の半角文字数を算出することができます。

つまり、半角文字がセル内に含まれていることも判断することができるというわけです。

つかっている関数は、LEN関数とLENB関数の2種類。

全角・半角関係なく、1文字＝1と算出するLEN関数。

全角を2バイト、半角を1バイトとして算出するLENB関数

全角1文字=2。半角1文字=1と算出されるわけです。

このルールを踏まえて、数式を説明します。

C列は、LEN関数をつかった算出結果です。LEN関数は、セル内の文字数を算出します。

A2もA3も同じ6文字なので、「6」。

先に、E列を説明します。

LENB関数は、バイト数で算出しますので、全角文字は2ですから、6文字あるので、すべて全角ならば、12になるはずです。

ところがE2は11と算出された、すなわち、半角が含まれていることがわかります。

そこで、C列で算出した値を2倍した値からE列で算出した値を引けば、何文字の半角文字があるのかがわかります。

「0」ならば、半角文字は含まれていないことがわかります。

LEN関数とLENB関数を組み合わせることで、セル内に文字が含まれているかどうかを調べることができますよ。

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。スタッフ全員の日程確認から全員参加可能日を見つけるいい管理方法はないの【Full participation】

Excel。スタッフ全員の日程確認から全員参加可能日を見つけるいい管理方法はないの

＜IF＋LEN＋SUBSTITUTE＋CONCAT関数＞

Microsoft TeamsやOutlookなどでスタッフ全員の会議参加日時をみつけることが、比較的容易に出来るようになりましたが、これをExcelでやりたい場合はどのようにしたらいいのでしょうか？

スタッフさん個人個人のシートが用意されています。

やりたいことは、全員が「○」の日時を探すということです。

今回は、3人分のシートですが、人数が増えるとか、項目がもっと細かく区分けされていたら、目視で確認するのは、とても大変な作業だといえます。

これからご紹介する数式をつかうと、このような結果が表示されます。

「OK」と表示されているところは、3人全員が「○」と報告してきたところです。短時間で、日程調整が出来るというわけです。

では、どのような数式をつくったのか、B4の数式をみてみましょう。

=IF(LEN(SUBSTITUTE(CONCAT(内藤:北沢!B4),"×",""))=3,"OK","")

ちょっと長い数式ですが、関数ごとに確認していきましょう。

数式の内側から確認していきます。

最初の登場する関数は、「CONCAT関数」。

文字結合する関数です。

CONCATENATE関数の進化系ですね。

CONCAT関数のところだけを算出してみます。

B4に、

=CONCAT(内藤:北沢!B4)

という数式を設定しました。

CONCAT関数が便利なところは、シート間で対応できるのと、範囲選択するだけで、文字結合できる点です。

スタッフごとのシートのB4の文字を結合させた結果が表示されています。

「○」が3個あれば、全員参加というのがわかるのですが、人数が多いと「○×」だらけで見つけるのも大変です。

そこで、「SUBSTITUTE関数」をつかって、「×」を消す作業をします。

そうすれば、「○」だけ残るので、見やすくなります。

B4の数式を

=SUBSTITUTE(CONCAT(内藤:北沢!B4),"×","")

と変更します。

SUBSTITUTE関数は、置換する関数です。

「×」を空白に置換するように数式を変更してみましょう。

「×」が消えて「○」だけが残りました。

「○」だけ残ったのですが、「○」が3個あれば、「OK」と表示するように修正すれば、もっとわかりやすくなります。

そこで、次のように修正します。

=IF(LEN(SUBSTITUTE(CONCAT(内藤:北沢!B4),"×",""))=3,"OK","")

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

「○」の数を算出します。

最後に、

IF関数をつかって、

LEN関数の結果が3（文字）だったら、「OK」と表示する

というように設定すれば、完成というわけですね。

新しく加わった関数をつかうことで、今までは算出するのに苦労していたことが改善できるかもしれませんので、数式を見直してみるのもいいのかもしれませんね。

Excel。セル内に同じ文字がいくつあるのか求めたいけど、どうやったらいいの？【Characters】：再録

Excel。セル内に同じ文字がいくつあるのか求めたいけど、どうやったらいいの？

＜LEN関数・SUBSTITUTE関数＞

ある商品の評価結果の資料があります。

一人ずつの評価がセルごとに入力されていればいいのですが、ひとつのセルにまとめて入力されてしまっています。

これでは、Aが何件、Bが何件と数えたい場合、目視で確認するしか方法はないのでしょうか？

左から1文字目は、A。

2文字目はBとセルに文字をいれる数式をつくってから、数えるという考え方もありますし、あるいは、Excel VBAでマクロをつくるという方法もあるとは思いますが、LEN関数・SUBSTITUTE関数をつかうだけで、簡単に、セル内に同じ文字がいくつあるのか算出することができるのです。

LEN関数・SUBSTITUTE関数をつかった数式をつかうことで、次のように集計することができます。

どのような数式をつくるといいのかを確認していきましょう。

C2につくった数式は、

=LEN($B2)-LEN(SUBSTITUTE($B2,C$1,""))

この数式を、オートフィルで数式をコピーすれば、セル内の同じ文字がいくつあるのかを簡単に算出することができます。

この数式を説明しておきましょう。

LEN($B2)は、B2のセル内の文字数を算出しています。

LEN関数はセル内の文字を数える関数ですね。

セル内の文字数から、LEN(SUBSTITUTE($B2,C$1,""))を減算しています。

LEN(SUBSTITUTE($B2,C$1,""))を見ていきましょう。

最初のLEN関数は、文字を数える関数でした。

何を数えているのかというと、SUBSTITUTE($B2,C$1,"")

SUBSTITUTE関数は、指定した文字を置換する関数です。

B2の中にある、C1＝「A」を、「””」＝空白に置換させています。

H2に、

=SUBSTITUTE(B2,"A","")

の結果を見ると、何をやっているのかがわかります。

要するに、「A」を除いた文字列をつくっていたわけです。

この文字数を数えて、Aも含めた最初のLEN関数で算出したあたいから、減算すれば、「A」がいくつあったのかがわかるという仕組みです。

発想としては、どうしても「A」を残すように考えてしまいますが、Aだけを残すというのが、難しいので、逆転の発想で、算出させてみました。

今回のように、全体の数から減算するというのは、よくある方法ですので、覚えておくと、いざという時にいいのかもしれませんね。

なお、同じようなパターンとしては、「○」「×」で、「○」の数はいくつなのかということもできます。

2種類の場合は、一つ算出できれば、おのずと、もう一つも算出されるので、今回のケースよりも、もっと簡単に算出することもできます。

Excel。セル内に同じ文字がいくつあるのか求めたいけど、どうやったらいいの？【Same character】

Excel。セル内に同じ文字がいくつあるのか求めたいけど、どうやったらいいの？

＜LEN関数・SUBSTITUTE関数＞

ある商品の評価結果の資料があります。

一人ずつの評価がセルごとに入力されていればいいのですが、ひとつのセルにまとめて入力されてしまっています。

これでは、Aが何件、Bが何件と数えたい場合、目視で確認するしか方法はないのでしょうか？

左から1文字目は、A。

2文字目はBとセルに文字をいれる数式をつくってから、数えるという考え方もありますし、あるいは、Excel VBAでマクロをつくるという方法もあるとは思いますが、LEN関数・SUBSTITUTE関数をつかうだけで、簡単に、セル内に同じ文字がいくつあるのか算出することができるのです。

LEN関数・SUBSTITUTE関数をつかった数式をつかうことで、次のように集計することができます。

どのような数式をつくるといいのかを確認していきましょう。

C2につくった数式は、

=LEN($B2)-LEN(SUBSTITUTE($B2,C$1,""))

この数式を、オートフィルで数式をコピーすれば、セル内の同じ文字がいくつあるのかを簡単に算出することができます。

この数式を説明しておきましょう。

LEN($B2)は、B2のセル内の文字数を算出しています。

LEN関数はセル内の文字を数える関数ですね。

セル内の文字数から、LEN(SUBSTITUTE($B2,C$1,""))を減算しています。

LEN(SUBSTITUTE($B2,C$1,""))を見ていきましょう。

最初のLEN関数は、文字を数える関数でした。

何を数えているのかというと、

SUBSTITUTE($B2,C$1,"")

SUBSTITUTE関数は、指定した文字を置換する関数です。

B2の中にある、C1＝「A」を、「””」＝空白に置換させています。

H2に、

=SUBSTITUTE(B2,"A","")

の結果を見ると、何をやっているのかがわかります。

要するに、「A」を除いた文字列をつくっていたわけです。

この文字数を数えて、Aも含めた最初のLEN関数で算出したあたいから、減算すれば、「A」がいくつあったのかがわかるという仕組みです。

発想としては、どうしても「A」を残すように考えてしまいますが、Aだけを残すというのが、難しいので、逆転の発想で、算出させてみました。

今回のように、全体の数から減算するというのは、よくある方法ですので、覚えておくと、いざという時にいいのかもしれませんね。

なお、同じようなパターンとしては、「○」「×」で、「○」の数はいくつなのかということもできます。

2種類の場合は、一つ算出できれば、おのずと、もう一つも算出されるので、今回のケースよりも、もっと簡単に算出することもできます。

Excel。セル内にある○×それぞれの文字を数えるにはどうしたらいいの？【Count letters】

Excel。セル内にある○×それぞれの文字を数えるにはどうしたらいいの？

＜LEN+SUBSTITUTE関数＞

やりたいことは簡単でも、それをどうやって「数式化」したらいいのか、悩むケースがあります。

例えば、テキストファイルなどデータが送られてきて開いてみたら、セル内に○×の結果が詰まっていたというのが次の表。

B列のデータは、アンケート結果の○×が詰まったデータというわけです。

そのデータに○がいくつ含まれているのか？

×はいくつ含まれているのか？ということを知りたい場合、目視で数えるというのは、大変以外の何物でもありません。

Excel VBAでマクロを作成するとか、あるいは、一文字ごと、セルひとつずつに、○や×を入力し直すというのも大変ですし、MID関数とか使うのも面倒です。

今回のような場合、LEN関数とSUBSTITUTE関数のコンビネーションで、比較的簡単に問題を解決することができるのです。

とりあえず、D2にLEN+SUBSTITUTE関数のネストの数式を作ってみましょう。

D2に作る数式は、

=LEN(SUBSTITUTE(B2,"×",""))

E2の数式は、D2でつくった数式と異なります。

E2の数式は、

=LEN(B2)-D2

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

とても簡単に算出することができました。

仮に、○×▲のような３つであっても、作り方としては、同じです。

それでは、数式を確認していくことにしましょう。

D2に設定した数式。

=LEN(SUBSTITUTE(B2,"×",""))

LEN関数は、セル内の文字数を算出する関数です。

単純に=LEN(B2)とすれば、10と返してくれるわけです。

では、引数でつかっている、

SUBSTITUTE(B2,"×","")

を確認していきます。

SUBSTITUTE関数は、検索した文字を、希望する文字に置換してくれる関数です。

今回は、「×」を「””」つまり空白に置換させているわけです。

考え方のポイントとして、「○」だけ数えるには、「×」がなければいいわけです。

文字数を数えてくれる関数は、LEN関数というのがあるわけですから、「○」だけにしたい。

だったら、「×」を消しちゃえばいいのでは、という発想したわけです。

だから、「×」を「空白」にするために、SUBSTITUTE関数をつかったというわけです。

B2のセルは、×が3個あるので、それが3個の空白に置換されるわけですから、残った文字数を数えれば算出されるというわけです。

「○」の数がわかれば、あとは、全体から「○」の数を減算すれば、「×」の数を算出することができるわけですから、E2の数式は、

=LEN(B2)-D2

となるわけですね。

文字関係は、色々なアイディアで、算出したり、変えたりすることができますので、文字列関係の関数を知っておくといいのかもしれませんね。

Excel関数辞典 VOL.45。LARGE関数～LENB関数【dictionary】

Excel関数辞典　VOL.45。LARGE関数～LENB関数

＜Excel関数＞

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

LARGE関数

ラージ

指定した○番目に大きい値を算出します。

LARGE(配列,順位)

LCM関数

エルシーエム

整数の最小公倍数を算出します。

LCM(数値1,[数値2])

LEFT関数

レフト

文字列の左端から文字を取り出せます。

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

LEFTB関数

レフトビー

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

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

LEN関数

レン

文字列の文字数を返す

LEN(文字列)

LENB関数

レンビー

文字列のバイト数を返す

LENB(文字列)

Excel。アンケート結果が一つのセル内に！セル内の文字を数えるにはどうするの？【Number of characters in the cell】

Excel。アンケート結果が一つのセル内に！セル内の文字を数えるにはどうするの？

＜LEN+SUBSTITUTE関数＞

Excelの基本ルールを知っていれば、次の表のようにYES/NOを選ぶ2択10問の結果を一つのセルにまとめてしまうことはあまりないかと思いますが、現場レベルではよくあるケースでもあります。

B列のアンケート結果からセル内のYとNの数を数えたいわけですね。

YとNがそれぞれ、一つのセルごとに入力されているならば、COUNTIF関数を使えば簡単に算出できるのですが、セル内ではCOUNTIF関数など使うことができません。

【セル内のNを消す（空白と置換する）】

そこで考え方を変えてみましょう。

セル内に2種類のYとNという文字があるわけですから、とりあえず、Nという文字を消すことができれば、残ったのは、Yだけになりますよね。

セル内の文字数を数えることは簡単にできるので、Yがいくつセル内にあるのか数えることができます。

セル内の文字を置換する時に使う関数が、SUBSTITUTE関数です。

今回は、Nを空白（置換）にするようにするのが目的です。

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

文字列には、B2。
検索文字列には、Nという文字を消したいので、”N”。
置換文字列には、”N”を消す、つまり空白に置き換えればいいわけですから、「””(ダブルコーテーション×2)」と入力します。

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

Yだけを残すことができました。

現時点でのD2の数式は、
=SUBSTITUTE(B2,"N","")
あとは、このYを数えればいいわけですね。

セル内の文字数を算出するのは、LEN関数です。

=SUBSTITUTE(B2,"N","")という数式をLEN関数で包む。
LEN+SUBSTITUTEというネストにすればいいわけですね。

D2の数式は、
=LEN(SUBSTITUTE(B2,"N",""))
完成したら、オートフィルを使って数式をコピーします。

これで、YESの数を算出することができました。SUBSTITUTE関数という、あまり馴染みのない関数ですが、簡単にYの数を算出することができました。

あと、Nですが、YESと同じ方法で、セル内のNの文字数を算出してもいいのですが、LEN関数は、セル内の文字数を算出する関数ですから、単純にセル内の文字数から、先程算出した、Yの数を減算してあげればNの数を算出することができます。

すなわち、E2の数式は、
=LEN(B2)-D2
オートフィルを使って数式をコピー完成ですね。

このように、LEN関数をつかうことで、セル内の文字数や、セル内で該当する文字数を算出することが、比較的、簡単に算出することができますので、色々と使ってみるといいかもしれませんね。

Excel。ゼロ付番号で桁数を整えたいけど、文字数がバラバラで困っている。【Flash fill】

Excel。ゼロ付番号で桁数を整えたいけど、文字数がバラバラで困っている。

＜フラッシュフィル・IF+LEN+TEXT+SUBSTITUTE関数＞

次の表をご覧ください。

A列が現在使用しているコードです。

桁数がバラバラで並び替えをすると綺麗に並ばないなど、業務に支障をきたしているので、B列の新コードのように整えたい場合、どのようにしたら楽に作れるのかということを今回紹介していきます。

A列の問題は、数値よりも、カテゴリー分けされている、アルファベットが問題です。

例えば、頭2桁がカテゴリーとなっていれば、LEFT関数を使って、それ以降は数値なので、それを整えるようにすればいいわけですが、今回は、アルファベットの桁数が”ab”と”bnk”と桁数がバラバラ。

こうなると、左から何文字目までが、カテゴリーなのかわからないので、LEFT関数では太刀打ちできません。

そこで、『フラッシュフィル』というExcel2010とかの旧バージョンには無い機能を使うことで、カテゴリーのアルファベットを抽出することが簡単にできますので、まず、フラッシュフィルを使ってみることにします。

次のように入力をします。

C2にabと入力しておきます。

フラッシュフィルは便利なのですが、自動的にこちらのやりたいことを完璧に汲み取れませんので、抽出したい形状のサンプルを入力しておくといいです。

C2をクリックして、データタブのデータツールにある「フラッシュフィル」をクリックします。

アルファベットだけが綺麗に抽出されました。ただし、フラッシュフィルは完璧なツールではありませんので、上手くいかないケースもあります。

【TEXT+SUBSTITUTE関数でゼロ付に変換する】

カテゴリーのアルファベットは抽出できました。次の問題は、数値の桁数がバラバラということですね。ab1は、ab00001とゼロを4個入れる必要がありますが、bnk652は、bnk0654となるので、ゼロは1個で済みます。

数値の桁数を数えるのは大変です。

そこで使用する関数が、TEXT+SUBSTITUTE関数です。

D2に次のような数式を作成します。
=C2&TEXT(SUBSTITUTE(A2,C2,""),"00000")
オートフィルを使って数式をコピーしておきます。

数式について説明します。
=C2&　までは、問題ないですね。&は文字結合させることができますので、C2のあとに文字を結合させます。

TEXT(SUBSTITUTE(A2,C2,""),"00000")
TEXT関数は、表示形式の関数です。

"00000"とすれば、ゼロ付数値にすることができます。

そして、SUBSTITUTE関数は、どのような処理をしているのかというと、

文字列を置換してくれる関数です。何をしているかというと、アルファベットを削除して、数値だけを残したいわけです。

この数式は、残った数値をゼロ付にする処理をしていたわけです。

ただし、これで完成では、ありません。

abは2文字でbnkは3文字なので、綺麗に揃っていません。ただ単に、ゼロが挿入されただけです。

そこで、IF関数を使って次のように数式を修正します。
=IF(LEN(C2)=2,C2&TEXT(SUBSTITUTE(A2,C2,""),"00000"),C2&TEXT(SUBSTITUTE(A2,C2,""),"0000"))

LEN関数を使って、文字数を数えます。

それに合わせて、ゼロの数を調整させています。

あとは、B列に値のコピーをすれば、完成です。

今回のようにコードを作る場合、将来を見越して、カテゴリーなど統一させるなど、最初に考えて作ると後が楽になりますが、修正する必要がある場合には、このようなフラッシュフィルを使ってみるという方法もあります。