Excelの新しい関数
2022年にExcelに新しい関数が追加されていた。
新しい文字列操作関数と配列関数
スプレッドシートを強化する 14 個の新しい文字列操作関数と配列関数との事。
TEXTBEFORE、TEXTAFTER、TEXTSPLIT、EXPAND、TAKE、DROP、VSTACK、HSTACK、CHOOSEROWS、CHOOSECOLS、TOROW、TOCOL、WRAPROWS、WRAPCOLS
ものによっては今までにない特殊な動きをする関数がある様に思うので、見てみたい。
14個と多いが、挙動が対になっている関数が多い為、実質下記8種類と言えるだろう。
TEXTBEFORE / TEXTAFTER
TEXTSPLIT
EXPAND
TAKE / DROP
VSTACK / HSTACK
CHOOSEROWS / CHOOSECOLS
TOROW / TOCOL
WRAPROWS / WRAPCOLS
今回見るのはTEXTBEFORE/TEXTAFTERの挙動について。
TEXTBEFORE / TEXTAFTERの構文
Excelで”=TEXTBEFORE”、”=TEXTAFTER”と打ち込むと出てくる構文が下記
=TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
それぞれの要素は下記の通り。
text
この場所に対象とするテキスト文を代入する。
テキスト文を直接入力しても良いし、テキスト文が入っているセルを示しても良い。
前者の場合には[“]ダブルクオーテーションでテキストの前後を囲う。
delimiter
区切り記号として使用するテキストをここで設定する。
TEXTBEFOREはこの区切り記号から前のテキストを返し、TEXTAFTERはこの区切り記号から後のテキストを返す。
この区切り記号として使うテキストが数字であれ文字であれ、全て区切り記号のテキストをダブルクォーテーションで囲う。
例えば、”M” や ”。” や ”1” の様に。
instance_num
求める区切りが複数ある場合、左から何番目か、を設定する。
実際含まれる個数より多い数字で設定した場合、#N/Aが返される。
match_mode
区切りとして使用するテキストが英文字の場合、大文字と小文字を区別するかどうかを設定する。
0 大文字と小文字を区別する。
1 大文字と小文字を区別しない。
設定しなければ0 区別する設定。
ちなみに、現時点では1を設定した時に区別せず認識してくれるのは、全角英字のテキスト同士、半角英字のテキスト同士での大文字小文字に限られる。
全角英字と半角英字の間では、例えそれが大文字同士
”M”（半角英大文字）と”Ｍ”（全角英大文字）
であっても別のテキストと認識されるので注意。
それぞれのascii（アスキーコード）が異なるから仕方ない訳だけれど。
ついでにそれぞれの文字のアスキーコードをPythonで調べたのが下記。
>>> ord("M") <=半角大文字 "M" の文字コードをpythonで求める
77
>>> ord("Ｍ") <=全角大文字 "Ｍ" の文字コードをpythonで求める
65325
アスキーコードが作られた最初から設定されていたと言える、最初からある半角大文字”M”の77と、後から設定されたと言える全角文字の大文字”Ｍ”の65325が大きく離れている事が分かる。
match_end
テキストの最後の文字のその後を区切り記号（delimiter）として扱うかどうかの設定。
この書き方で分かるだろうか？
最初良く分からなかった設定match_end
実はこれは最初良く分からなかった設定。
Microsoft の説明
テキストの末尾を区切り記号として扱います。 既定では、テキストは完全一致です。
0 区切り記号をテキストの末尾に一致させないでください。
1 区切り記号をテキストの末尾に一致させてください。
を読んでも何を言っているのか良く分からない。
本家英語版の説明
Treats the end of text as a delimiter. By default, the text is an exact match. Optional.
0 Don’t match the delimiter against the end of the text.
1 Match the delimiter against the end of the text.
と見比べて、使って、やっと分かった。
match_endとは
textで指定しているテキストの最後の文字のその後を区切り記号（delimiter）として扱うかどうかの設定。
例えばtextで指定するテキストが下記だったとする。
この間Mountainに芝刈りに行った。おばあさんは川へ洗濯に行った。おしまい。
このテキストの区切り記号を”。”とした場合、前から3つあるが、この最後の”。”の後にもう一つ、”テキストの末尾”と言う4つめのdelimiterを設定できるのがこのmatch_endの設定。
0に設定すれば、テキストの末尾を区切り記号として使わない。
これは要するに目で見えている区切りだけ、前の例では”。”の3つ目まで区切り記号とする。
1に設定すれば、テキストの末尾、最後の文字”。”の後を区切り記号として扱う。
これは目で見えている区切り文字にプラスして1つ最後の区切りを使用できると言う事。
match_endを1に設定しておけば、区切り記号で設定したテキストが一つも無くても、テキストの末尾の区切り記号だけは認識される。
match_endの設定を使うと
TEXTBEFOREの場合、match_endを1に設定し、テキスト末尾を指定すれば、全文が返ってくる。
TEXTAFTERの場合、match_endを1に設定し、テキスト末尾を指定した場合、テキスト末尾の後のテキストは空のテキストなので、空データが帰ってくる。
TEXTBEFOREはともかく、TEXTAFTERについては使い所が良く分からない設定ではある。
if_not_found
delimiterで設定した区切り文字が無かった場合、何を返すかの設定。
数字を返す場合はそのまま、文字列を返す場合、その文字列をダブルクォーテーションで囲う。
何も設定しないと#N/Aが返される。
使用例
エクセルでの使用例を下記に。
textに代入するテキストはセルB1のテキスト。
B列左側はどの様に表示されるか、C列右側にはB列のセルの中の式を示した。
雑感
今までのExcelに無かったタイプの関数と感じた。
これからも触って慣れていきたい。
最近買って良かった本
この「即興型ディベートの教科書」はディベートのスキルを磨きたい方にも、短時間で自分の考えをまとめる練習をしたい方にも参考になる知識が盛りだくさんの一冊。
2020年の本ですが、この手の本の内容は簡単に陳腐化しないんじゃ無いだろうか。
繰り返し読みそうな予感。
そんな「即興型ディベートの教科書」が3/2までAmazonのKindle本50％ポイント還元対象。
リフロー型だから使い勝手も良いし、中古よりもポイント分100円（+有れば送料）お得。
興味があるなら買い時と考えます。
コメント