Excel。SUBSTITUTE関数は文字列中の特定の文字を別の文字に置換する
＜関数辞典：SUBSTITUTE関数＞
SUBSTITUTE関数
読み方： サブスティチュート
分類： 文字列操作
SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])
文字列中の特定の文字を別の文字に置換する
12/30/2024
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」することで、「,」で区切られた文字列数を求めることができるというわけです。
12/19/2023
Excel。セル内の余計な空白を削除して、セル内の空白は半角で整えたい【remove white space】
Excel。セル内の余計な空白を削除して、セル内の空白は半角で整えたい
＜TRIM+SUBSTITUTE関数＞
セル内の余計な空白を削除するには、TRIM関数という便利な関数が用意されていますが、それだけでは、対応することができません。
A2:A6のデータは、A2のように、苗字と名前の間には、半角空白が1文字分、空くようにしたいわけです。
TRIM関数は、前後の余計な空白と、文字内の連続している空白を1つだけにします。
C2には、
=TRIM(A2)
と数式を設定して、オートフィルで数式をコピーします。
どのデータも前後の空白は削除されていますし、文字内の空白にも対応しています。
ですが、よくみると、C3やC5は、半角空白にならず、全角空白になっています。
TRIM関数では、半角文字を全角文字にするまでは、やってくれません。
そこで、文字を置換することができるSUBSTITUTE関数を合わせてつかうことで、対応することができます。
A2の数式を次のように修正します。
=TRIM(SUBSTITUTE(A2," "," "))
あとは、オートフィルで数式をコピーすれば完成です。
SUBSTITUTE(A2," "," ") の説明をします。
最初の引数は、文字列。A2を選択します。
2番目の引数は、検索文字列。” “と全角空白を設定します。
3番目の引数は、置換文字列。” “と半角空白を設定します。
資料を作ろうとしても、データがガタガタだと作成することができませんので、綺麗に整えるスキルを知っておくのもいいかもしれませんね。
2/25/2023
Excelの様々な関数の読み方や引数などを紹介。今回は、STEYX関数～SUBTOTAL関数です。【dictionary】
Excelの様々な関数の読み方や引数などを紹介。今回は、STEYX関数～SUBTOTAL関数です。
＜Excel関数辞典：VOL.76＞
今回は、STEYX関数～SUBTOTAL関数までをご紹介しております。
STEYX関数
読み方： スタンダードエラーワイエックス
STEYX(既知のy,既知のx)
回帰直線の標準誤差を算出する
STOCKHISTORY関数
読み方： ストックヒストリー
STOCKHISTORY(stock,start_date,[end_date],[interval],[headers],[properties1],…)
指定された銘柄と日付範囲の過去の相場データの配列を返す
SUBSTITUTE関数
読み方： サブスティチュート
SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])
文字列中の特定の文字を別の文字に置換する
SUBTOTAL関数
読み方： サブトータル
SUBTOTAL(集計方法,参照1,…)
11種類の集計方法で小計を算出します
2/07/2023
Excel。セルごとに年・月・日が含まれているデータから日付をつくりたい【Date】
Excel。セルごとに年・月・日が含まれているデータから日付をつくりたい
＜DATE＋SUBSTITUTE関数＞
データを読み込んでみたら、そのあとの作業がスムーズにできないで困るケースがあります。
例えば、年月日がセルごとにわかれているのですが、それぞれに、年・月・日という文字も含まれているために、DATE関数をつかって、手早く日付にすることができません。
DATE関数は、年月日に設定できるのが数値型でなければなりません。
「2023年」のように「年」が含まれていると、文字型になってしまいます。
左揃えになっていることからも、文字型になっていることがわかります。
当然、DATE(a2,b2,c2)としても、エラーが表示されてしまうだけで、日付にすることができません。
置換処理で、「年」とかの文字を空白にしてもいいですが、少し面倒です。
そこで、置換することができる関数であるSUBSTITUTE関数をDATE関数と組み合わせてつかうことで、解決することができます。
D2に次の数式を設定します。
=DATE(SUBSTITUTE(A2,"年",""),SUBSTITUTE(B2,"月",""),SUBSTITUTE(C2,"日",""))
オートフィルで数式をコピーしています。
「SUBSTITUTE(A2,"年","")」を説明します。
SUBSTITUTE関数の最初の引数は、文字列なので、A2を設定します。
2つ目の引数は、検索文字列。年という文字を削除したいので、「”年”」と設定します。
3つ目の引数は、置換文字列。年を削除したいので「””」とすることで、年を消すことができます。
これを、「月」と「日」にも同じように設定してきます。
セルに入力されているデータによっては、簡単に日付にすることができないこともありますので、すんなり日付にできない時には、文字型になっていないかを確認するところから始めてみるといいかもしれませんね。
7/29/2022
Excel。セル内の余計な空白を消去して、文字の間は半角空白で揃えたい【TRIM】
Excel。セル内の余計な空白を消去して、文字の間は半角空白で揃えたい
＜TRIM＋SUBSTITUTE関数＞
テキストファイルなどをインポートして、データを読み込んでみたら、セル内に余計な空白がアチラコチラ入っている。
その空白も、全角や半角が混じっているし、文字と文字の間は半角空白を一つだけで揃えたいといった場合、どのようにしたら、手早く、データを綺麗にすることができるのでしょうか。
サンプルの表をつくってみました。
B2には、
=TRIM(SUBSTITUTE(B1," "," "))
という数式を設定しました。
その数式をオートフィルで数式をコピーしてD2まで設定しています。
TRIM関数は、セルの前後にある空白を削除してくれます。
これで、B1のようなパターンでは、前後の空白を削除してくれます。
そして、このTRIM関数は、文字と文字の間の空白が複数ある場合は1つだけ残して削除してくれる便利機能も付いているので、B1のように、全角空白2文字の場合は、全角空白1文字にしてくれます。
なお、半角空白と全角空白の組み合わせでも、空白1文字残してくれます。
空白を削除するだけならば、TRIM関数だけでいいのですが、半角空白で揃えたいので、SUBSTITUTE関数をつかって、半角空白に揃えていきます。
SUBSTITUTE関数は、指定した文字に置換してくれる関数です。
文字と文字の間の空白は、半角空白にしたいので、全角空白を半角空白に置換するように引数を設定してあげるだけで、文字の前後の空白と、文字と文字の間の空白を半角空白にすることができました。
9/02/2021
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のデータは、分割よりも結合するほうが、簡単なので、できれば、最初から、姓と名を分けておくことをおススメします。
7/31/2021
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」と表示する
というように設定すれば、完成というわけですね。
新しく加わった関数をつかうことで、今までは算出するのに苦労していたことが改善できるかもしれませんので、数式を見直してみるのもいいのかもしれませんね。
7/19/2021
Excel。月別シートの合計値を集めた表を効率よく作りたい時はINDIRECT関数が便利です。【INDIRECT】
Excel。月別シートの合計値を集めた表を効率よく作りたい時はINDIRECT関数が便利です。
＜INDIRECT関数/INDIRECT＋SUBSTITUTE＋(LEFT＋CELL関数＞
月ごとに集計されているシートがあります。
それぞれのシートの合計値をまとめた、「年間」シートに数値を設定して年間合計を算出した集計シートをつくりたいわけです。
今回のケースは、サンプルなので、2枚のシートですんでいますが、年間となれば12枚のシートですし、もっと多くのシートからデータをコピーする必要があるとすれば、面倒な作業となってきます。
Excel VBAでプログラムをつくってもいいのですが、数式レベルで、効率よくコピーする方法はないのでしょうか？
このような場合、どのようなパターンがあるのか、ないのかを見つけるところから考えていくといいですね。
集計先のシートのB2に、セル参照の数式を設定してみましょう。
='1月'!B5
となっています。
横方向に、オートフィルで数式をコピーすれば、
='1月'!C5
='1月'!D5
と、横方向は、うまく参照結果が表示されていますが、縦方向にオートフィルで数式をコピーしても、当たり前ですが、うまくいきません。
理由は、シート名が違うからです。
シート名を変更する”だけ”かもしれませんが、とても面倒な作業です。
よくみると、シート名と、A列のデータが同じになっています。
シート名をA列に入力されている値そのものを使うことができれば、上手くいきそうです。
そこで、登場するのが、「INDIRECT関数」です。
B2にINDIRECT関数をつかって、次のような数式に変更してみましょう。
B2の数式は、
=INDIRECT($A2&"!b5")
この関数は、
='1月'!B5
という数式をどうやったらつくれるのかをイメージして作る関数です。
A2に1月という文字があるので、それをつかいたいわけです。
オートフィルで数式をコピーすることを前提としていますので、列固定の複合参照にしています。
そして、「&（アンパサンド）」で「"!b5"」という文字を結合させています。
縦方向に、オートフィルで数式をコピーすると、きちんと参照してくれたのですが、横方向にオートフィルで数式をコピーしたら、うまくいきません。
原因は、「"!b5"」。
文字になってしまっているので、オートフィルで数式をコピーしても「b」が「C」に自動的に変わってくれることはありません。
本当は、変わってくれれば、いいのですが…
そこで、どうやったら、「b」を「c」に出来るのかを考えて、B2の数式を次のように修正しました。
=INDIRECT($A2&"!"&SUBSTITUTE(LEFT(CELL("address",B1),2),"$","")&5)
なんか、長くなっちゃいましたが、この数式を、縦方向。
横方向にオートフィルで数式をコピーすると、綺麗に、参照することができました。
では、この長くなった数式の説明をしてきます。「b」を「c」にするための数式が、
SUBSTITUTE(LEFT(CELL("address",B1),2),"$","")
です。
内側から説明しないと、わからないので、最初は、CELL("address",B1)
CELL関数は、セルの情報を算出してくれる関数で、引数にaddressをつかうと、絶対参照がついたセル番地を文字として、算出してくれます。
B1のセル情報なので、「$B$1」という文字が算出されます。
LEFT(CELL("address",B1),2)
は、LEFT(“$B$1”,2)ということなので、LEFT関数をつかって、左から2文字分を抽出します。
これで、「$B」という文字が算出されています。
「$AA$1」だったら3文字分を抽出する必要があります。
SUBSTITUTE($B,"$","") という状態になっていることがわかります。
SUBSTITUTE関数は、置換することが出来る関数なので、「$」を空白に置換させます。
これで、「B」だけを抽出することができるます。
ということで、シート名を参照できるセルをつくることで、INDIRECT関数をつかうことで、別シートから必要なデータを参照することができます。
7/10/2021
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種類の場合は、一つ算出できれば、おのずと、もう一つも算出されるので、今回のケースよりも、もっと簡単に算出することもできます。
6/19/2021
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種類の場合は、一つ算出できれば、おのずと、もう一つも算出されるので、今回のケースよりも、もっと簡単に算出することもできます。
5/14/2021
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
となるわけですね。
文字関係は、色々なアイディアで、算出したり、変えたりすることができますので、文字列関係の関数を知っておくといいのかもしれませんね。
11/03/2019
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関数をつかうことで、セル内の文字数や、セル内で該当する文字数を算出することが、比較的、簡単に算出することができますので、色々と使ってみるといいかもしれませんね。
7/09/2019
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列に値のコピーをすれば、完成です。
今回のようにコードを作る場合、将来を見越して、カテゴリーなど統一させるなど、最初に考えて作ると後が楽になりますが、修正する必要がある場合には、このようなフラッシュフィルを使ってみるという方法もあります。
12/04/2018
Excel。アンケート結果の集計で使える、セル内のこの文字だけを数える。セルにまとめる【Count only letters】
Excel。アンケート結果の集計で使える、セル内のこの文字だけを数える。セルにまとめる
＜PHONETIC関数・CONCAT関数・MID+COLUMN関数・LEN+SUBSTITUTE関数＞
簡単そうに思う処理であっても、意外とできない人が多いものって結構あるのですが、今回は、アンケート結果の集計とかで知っていると便利なものをご紹介していきましょう。【回答を一つのセルにまとめる】
次の表があります。
アンケート結果が、D列からH列にそれぞれの回答が入力されています。
これをC列の回答欄である、一つのセルにまとめたい。
すなわち、回答者1は、○×○×○と回答欄のC2に入力したい場合どのようにしたらいいのでしょうか？
CONCATENATE関数やあるいは、”&”による文字結合ということを考えるかもしれませんが、このケースでは、『PHONETIC関数』を使えば簡単に算出することができます。
C2をクリックして、PHONETIC関数ダイアログボックスを表示しましょう。
参照に、D2:H2
と入力してOKボタンをクリックして、オートフィルで数式をコピーしましょう。
C2の数式は、
=PHONETIC(D2:H2)
となっています。
このように、一つのセルにまとめることができました。
フリガナでお馴染みのPHONETIC関数を使うというのは面白いアイディアですよね。
また、office365のExcel2016で新しく加わった、CONCAT関数を使うことでも、同じように算出することができます。
数式は、
=CONCAT(D2:H2)
としても算出することができます。
このCONCAT関数は、CONCATENATE関数の進化版の関数で、文字結合をすることができる関数なのですが、CONCATENATE関数とは異なり、範囲選択して範囲内のセルを結合することができます。
【一つのセルの文字をセルごとに入力する】
次の表のように、今度は、逆に、一つのセルにまとめられたデータを一文字ずつ、セルに入力するにはどうしたらいいのでしょうか？
このようなケースでは、MID関数を使うことで簡単に算出することができます。
D9をクリックしてMID関数のダイアログボックスを表示しましょう。
文字列には、$C9
複合参照にしているのは、このあと、オートフィルで数式をコピーからですね。
開始位置ですが、左側から1文字ずつズレた文字を検索させますので、列番号を算出する、COLUMN関数を使って、そこから-3することで、1をつくれますので、
開始位置には、
COLUMN()-3
文字数は、1文字ずつ検索させますので、
1
OKボタンをクリックして、オートフィルで数式をコピーして完成ですね。
なお、D9の数式には、
=MID($C9,COLUMN()-3,1)
という数式が設定されています。
これで完成しました。
【セルの中の特定の文字だけを数える】
アンケート結果を一つのセルにまとめた状態の表があります。
回答を一つずつのセルにいれて、特定の文字を数えるならば、全く問題はありませんが、このように、例えば回答のセルの中に○がいくつあるのか、数えたい場合にはどのようにしたらいいのでしょうか？
セルの中にある特定の文字を数える関数はありません。
なので、次のように数式をD16に作ってみましょう。
=LEN($C16)-LEN(SUBSTITUTE($C16,D$15,""))
LEN関数はセルの中の文字の数を数える関数です。
まずは、セル全体の文字数を数えます。
そして、特定の文字を空白に置換して、その残った文字数を数えたものを、先程LEN関数を使って算出した結果から減算してあげれば、セルの中の特定の文字数を数えることができます。
そこで登場するのが、SUBSTITUTE関数。
今回は、D15に○がはいっているので、この文字を、空白に置換させます。
そしてLEN関数と合わせて使うことで、求めることできます。
このように、アンケート結果などの集計で使えそうなものはたくさんありますので、少し知っているだけでも作業効率が改善できるかと思いますので、使ってみてください。
登録:
投稿 (Atom)