Microsoft365のExcelで使える関数は知らないと損をする
Microsot Excelは、買い切り版であるExcel2019といったパッケージ製品の他に、サブスクリプションサービスであるMicrosoft365で利用可能なExcelの2種類があり、後者の場合は更にテスト利用の為のOffice Insider Programが存在します。
Excelに関して言えば、この両者はライセンスの違いだけでなく、機能にも差があり、特に利用できる関数については結構大きな差があります。その中にはGoogle Spreadsheetではもはや当たり前につかってる関数も存在し、日常でのExcel業務を推進する上で知らないと損をするものも含まれています。今回はこの当たりをまとめてみようと思います。
今回利用するアプリ
- Office 365 ProPlus
- 数式サンプルExcelファイル
他に、Office365 SoloやOffice Premiumで利用可能になっているようです。ProPlusも上位のサブスクリプションサービスであるので、企業で利用してる方は多いのではないかと思います。以前はOffice365版専用だった、IFS関数やMAXIFS関数、SWITCH関数などは現在、Excel2019以降であれば買い切り版でも利用できます。
※ちなみにMicrosoft365のExcel Online Businessでもこれらの専用関数は利用可能
※買い切り版のExcel2021ではFilter関数などが使えるようになりました。
図：Excel Online上で使ってる様子
動的配列数式関数
動的配列数式関数とは、以前のExcelに存在した使いにくい上に中途半端な機能であった「配列数式」をきちんと完成させたもので、元々Google Spreadsheetで先に実装されていた機能です。それまでのExcelは関数などで配列で返すという事ができず非常に不便な状態でした。現在この動的配列はスピルと呼ばれています。
この動的配列数式関数はまさにこのGoogle Spreadsheetの後追い機能で、ようやく装備されたものです。これにより、これまでの配列数式は廃止され、動的配列数式関数とスピルの２つの機能として再実装されたものになります。
※スピルおよび動的配列数式関数は、テーブルと併用する事ができないので、テーブル内では#SPILLというエラーが発生します。抽出結果をテーブルにしたい場合は、関数ではなくPower Queryを利用する必要があります。
FILTER関数
Googleスプレッドシートを利用していて最も便利な関数の１つがこのFilter関数。指定した範囲に対して条件式を指定する事で、適合するデータの塊を取得できる典型的な配列関数。この関数があるおかげで、VBA、GASなどでプログラムを書く場合には、データの塊に対してフィルタする処理を書かずに済むだけでなく、動的に条件を書き換えて取得も可能なので、コードの書き方そのものが変わって来ます。
コードを書かずともこれまで、1行ずつ関数を書いてつくっていた表であったり、テーブルを使ってどうこう、オートフィルタを使って絞り込みといったことが「関数１つ」である程度済んでしまうので、使わない手はありません。この関数については、以下のGoogleスプレッドシートでのFilter関数がほぼ同じ仕様であるので、参考になります。
サンプルのExcelシートにある数式は、以下のような式になります
|
1 2 |
//温泉一覧からポイントが80以上のデータを取り出す =FILTER(A2:E21,E2:E21>80) |
条件式はこれだけでなく、AND,ORなどの組み合わせ、別の関数との組み合わせ、重複した値の抽出などもできてしまうので、PowerQueryを使う前にまず使ってみるべき関数です。
図：これが一番の目玉機能と言えます
UNIQUE関数
こちらも、Google Spreadsheetで先に実装されていた非常に便利な関数で、指定範囲内の値のユニークな一覧を出力するという関数です。この抽出方法は単一の列に対してだけではなく、複数の列を指定した場合は、その２つの列でグループ化した形でのユニークな一覧を出力してくれる上に、スピル範囲演算子という仕組みを使うと、sumifsなどで条件式として使えるという結構深い関数です。
主に以下のような使い方をします。
|
1 2 3 4 5 |
//単純な使い方 =UNIQUE(D2:D21) //複数列を指定してユニーク値を取り出す =UNIQUE(C2:D21) |
スピル範囲演算子については、後述のスピルの項目で説明します。
図：単純ながら実は貢献度の高い関数
SORT・SORTBY関数
こちらも、Googleスプレッドシートにて装備されたものをExcel側が後から装備として追加した関数になります。いずれの関数も配列で結果を返すもので、関数でソートした値を配列で取得し表示してくれる便利な関数です。VBAなどで元データをソートするようなロジックを組む必要がなくなるため、コーディングに置いても間に作業シートでこの関数で整備したものを用意しておけば、楽になるだけでなく昇順・降順やソートする列などを変数で指定が出来るので、動的に変更も可能です。
sort関数は単一列のソート、sortby関数は複数列のソートにそれぞれ対応しています。記述方法は以下の通り。
|
1 2 3 4 5 |
//単一列のソート =SORT(ソートするデータ範囲,ソートする列番号,ソート基準) //複数列のソート =SORTBY(ソートするデータ範囲,第一ソート列の範囲,ソート基準１,第二ソート列の範囲,ソート基準２) |
２つの関数で、少し関数の引数の指定方法が異なるので注意。ソート基準はなぜか、1が昇順、-1が降順となっている。Sortby関数の場合はなぜか、列番号ではなくその列の範囲指定となってる点も要注意です。第一、第二と複数指定をした場合、その順番にソートが掛かるので、指定順にも注意を払う必要があります。
※Googleスプレッドシートの場合、Sortby関数は無く、もともとSort関数で複数列のソートに対応している。また複数列の場合であっても、列番号での指定なので、旧来の関数のスタイルに近い。
図：関数でソートした値を取得出来る
SEQUENCE関数
Googleスプレッドシートでも装備されてる1,2,3,4など連番（通し番号）を生成する為のちょっと変わった関数がSEQUENCE関数です。縦横で指定することで、複数行列でこれらの数値を生成してくれますが、現実の実務では、例えばID列の連番の自動生成などに使えます。これまでも、row関数+1の数式で実現していましたが、こちらは配列で返してくれるので１つの数式で十分です。
他にも例えば、タイトル列に１～１２月までを一発生成などで役に立ちます。
数式の指定方法は以下の通り
|
1 2 |
//基本は最初の２つの引数だけあれば良い =SEQUENCE(縦方向の数, 横方向の数, 開始数値, 増分) |
縦横の生成数の指定だけで、デフォルトでは１ずつ増えていく。そこに追加の引数で、開始数値をいれれば、その数値からの連番を作り、増分では2を指定すれば2ずつ増え、-1を指定すれば1ずつ減っていく連番を生成できる仕組みです。
※例えば、sequence(1,12) & "月"にて、○月という連番を作れるのですが、Googleスプレッドシートではこの挙動はサポートしていないので動きません
図：ちょっとしたデータの生成に役立つ
RANDARRAY関数
あまり実務では使う機会はないと思いますが、プログラミング用のダミーデータ生成などで活躍しそうなのが、このRANDARRAY関数。指定した行列の分だけ、乱数を生成し配列で返してくれる関数です。Googleスプレッドシートでは結構自分はお世話になっています。
数式の指定方法は以下の通
|
1 2 |
//通常は縦方向の数と横方向の数のみの指定で良い =rRANDARRAY(縦方向の数,横方向の数,最大値,最小値,整数or10進数) |
縦横の生成する数を指定し、乱数の最大値と最小値を指定します。整数or10進数はtrue/falseで指定します。
図：細かな指定も可能です
その他の新関数
XLOOKUP関数
通常の使い方
これまで、特定のIDなどに該当するデータの別の列の値をピックアップしてくる関数として長らくVLOOKUP関数が使われてきました。しかし、大きな弱点としてID列の値のようなものは必ず左端に配置しなければならない（つまり、対象の列の左側は検索が出来ない）ということで、そういったデータの場合は、Index関数 + Match関数の組み合わせで実現されてきました。
今回xlookup関数の登場によりIDなどの検索対象が左端でなくても検索が出来るようになったのと、iferrorを組み合わせなくても値が見つからない場合の返り値の指定なども出来るようになっているため、値のピックアップは今後xlookupが主流になると思われます。(尚、Googleスプレッドシートには2021年10月時点では実装されていません）
数式の指定はVLOOKUP関数と同じような仕様で、返り値はVLOOKUPだと列番号を指定していましたが、XLOOKUPでは範囲の指定になっていて、いちいち何列目なのか数える必要もないです。
|
1 2 |
//数式の指定 =XLOOKUP(検索値,検索範囲,答えを返す範囲,"データがなかった場合の値") |
という指定になっています。基本前方の３つの引数のみでOKで、後方には上記の「データがなかった場合の値」の他に、一致モード（完全一致か？近似値か？）、検索モード（データの上下どちらから検索するか？）が用意されていて、難解なIndex + Matchを使わずとも済むというのは大きな利点です。
また標準でスピル対応しているため、１つの値ではなく、そのレコードにある別の値も同時にピックアップ可能なので、以下のように返り値の範囲を複数列にすれば、複数列の値が返ってくるようになっています。１個ずつセルに記述するようなダサい真似をしなくて済みます。
|
1 2 |
//複数列の値を返す =XLOOKUP(I2,A2:A21,D2:E21,"no data") |
※vlookupと違い、空欄に対する挙動が以下のように異なるので注意
- VLOOKUP：空欄は無視して#N/A
- XLOOKUP：空欄は空欄に一致する
図：長年苦しめられた検索が楽になった
部分検索で検索する方法
摘要欄の項目などの一部から、それに該当する科目やコードを割り当てたい。結構現場だとデータの形式がしっかりしていないExcelファイルにぶつかるシーンがあり、これが大きな壁になってたりします。目視で割り当てるのはあまりにも苦痛。ということで使うのがxlookupとcountifs、ワイルドカードを利用した検索手法です（できるExcel2021のはけた氏が編み出しました）。
この検索方法は、「対象のワードで検索し、別の一覧表にあるワイルドカード込のデータ群からコードを引っ張る」という手法になります（よって、通常はデータ群はマスターデータである必要があります）
- 検索対象のワードをcountifsを使って、対象の文字が含まれているかどうかをチェック（含まれていれば1が返ってくる）⇒この時相手のテーブル側の「検索値列」を指定するので、スピルで返ってくる
- 戻り範囲はIDと分類コードの2列を指定してスピルで返すようにする
- 一致モードは、「ワイルドカード文字との一致」を選ぶ。これで検索値列のワイルドカード指定文字と検索対象のワードが適合するかを検索してくれる
すると、*項目名*とした場合は部分一致、*項目名ならば後方一致、項目名*ならば前方一致でテーブルの中から探してくれます。
|
1 2 |
//テーブルのデータをワイルドカードで引っ張る手法 =XLOOKUP(1,COUNTIFS(検索値,テーブルの検索値列),テーブルの戻り列,,2) |
図：xlookupでワイルドカード検索
部分検索で検索する方法２
上記の手法とは別にxlookup単体でワイルドカードを使っての検索手法もあります。ただしこの場合、検索対象の表から、数式内でワイルドカードを持って引っ張ってくる手法になるため、数式内が若干スマートじゃありません。
この検索方法は、「検索ワードを元にワイルドカードにて対象の表からlookupで検索する」ものになります。対象の表がマスターデータでない場合は1つ目だけがヒットします。
|
1 |
=XLOOKUP("*" & 検索値 & "*",検索範囲,戻り列,,2) |
検索値の前後を*で括ってワイルドカードにし、対象の表からそれにヒットするものを調べるものになります。前述の手法とは検索の方向性が異なるので、その点が注意です（例えば契約社員というワードが複数行にある場合、1つ目だけが返ってきます）
図：数式内でワイルドカードを使って検索する
xlookupの代わりとして
vlookupの弱点を補う為にかつてあったIndex + Matchの関数の組み合わせで縦横を特定しルックアップする手段がありました。しかし、これはxlookupの登場によってその座を完全に奪われています。また、xlookupはスピルとしての機能もあり、１行まるごともしくは１列まるごとルックアップも可能です。
ですが、逆に言うと１行分か1列分しかルックアップできない為、スピルとしての機能は弱く、まるごとルックアップみたいな真似が出来ません。
これを代替する手段としてChooserows + Matchの関数の組み合わせがあります。このChooserows関数は2022年8月に新規追加された関数で、この関数もまたスピルの機能を持ち合わせています。これとMatch関数を組み合わせることで、xlookupでやりたかった事が実現可能です。返す列のフィルタは出来ないので、他の関数と組み合わせが必要です。
|
1 2 |
//A列の値を元に元の表から一致する行をルックアップ =CHOOSEROWS(B2:D8,MATCH(A15:A17,A2:A8,0)) |
表1と表2の列を比較して同じものがあれば、元の表の残りの列の値を持ってくるというシンプルな作り。
図：縦横を行の値の一致他の行をまとめてルックアップ
XMATCH関数
指定した値が表の中での相対位置（何番目なのか？）を調べるための関数です。行番号ではなく表の中で何番目にあるのかを返すので注意が必要です。ただしこの関数はちょっと変則的なもので、マッチモードを指定すると、その値よりも大きな数値のものが何個あるか？といった表示なったりするので、オプションの指定に注意が必要です。
その性格上あまり単独での使用というより、返ってくる相対位置を利用して別の関数で利用するというのが正しい使い方になるかと思います。
|
1 2 |
//マッチモードは通常は指定する必要はない =XMATCH(検索する値,検索値の入ってる列の範囲,マッチモード） |
検索値の入ってる列の範囲はその値が入ってる列のみの範囲を指定します。
マッチモードは1を指定した場合、検索する値以上のレコードが範囲内に何個あるかを返すようになる（その場合、マッチモードは1を指定する）
図：相対位置を知り、他の関数で使うには便利かも
LET関数
2020年11月に正式に実装されたのがこのLET関数で、企業内ではまだ配信していない場所も多いであろう関数です。簡単に言えば「計算結果」に名前をつけて、他の計算時にそれを再利用するというもので、普通にセル番地で指定すれば良いのでは？という疑問が出る人も多いかもしれませんが、計算式の見通しをよくするのが主な目的です。
わかりやすい事例では、if文などとvlookupを組み合わせた場合、vlookupの値がtrueな時はvlookupの結果を利用する場合、もう一度同じvlookupの計算式を書くことになるため、数式の中が非常に冗長になり見通しが悪くなります。これをLET関数で名前付けをすると、数式内ではvlookupの数式は1回のみでOK。さらにその結果に対して演算も可能なので、関数のメンテナンス性がアップします。
|
1 2 |
//ifとvlookupの組み合わせをLETで使う事例 =LET(ルックマン,VLOOKUP(D2,A2:C10,2,FALSE),if(ルックマン="","",ルックマン)) |
ルックマンというのが適当に付けた名前。この名前にはvlookupの計算結果が入ります。これを第三引数のif文で利用する時は数式ではなく名前を使うので、結果的に数式の見通しが非常によくなっています。入れ子だけでもタダでさえ数式が読みにくいのに、条件分岐で同じ数式を何度も書くという無駄が省けるので、使えるようになってる環境では積極的にこの関数で書き直しをすることをおすすめします。
LAMBDA関数
2020年12月頃から、Insider Programのベータチャンネルにて装備された関数がこのLAMBDA関数で、非常に特殊な関数です。関数と言っても通常の数式の中で使うタイプの関数ではなく、「VBAで実現していたユーザ定義関数を関数のみで作成する機能」といったほうが正しいです。また、他の関数の引数としてこのLAMBDA関数を取り、結果を配列で返すような関数も７つほど追加されており、高度なExcelでの演算で利用することが可能になりました。
ベーシックな使う手順は以下の通り
- メニューより数式 -> 名前の管理を開く
- 適当に新規作成で名前の定義を作る（taroと命名してみた）
- 2.の参照範囲、ここにLAMBDA関数の定義に書き換える
1例：=LAMBDA(x,y, x+y) - 別のセルで=taro(5,2)と入力してEnterすると、5+2が計算されて結果7が表示される
- VBAではないので、ファイルはxlsmではなくxlsxのまま使えるのは利点とも言える。
ただ個人的にはユーザ定義関数は複雑な計算や条件判定で使う事例が多いので、果たしてLAMBDAで作るか？といったら疑問。メンテナンス性を考えても、スキルの足らない人からしたら意味不明な関数と結果が表示されてることになるので、個人での利用に限定されると思います。VBAを勉強しなくて済むという考えは安直で、むしろ複雑性が増すだけなので素直にVBAのほうがシンプルで全然マシなものが作れると思います。
図：参照範囲で使う特殊な関数
スピルとは何なのか？
基本的な使い方
元々はGoogleスプレッドシートで装備されているArrayformula関数で実現できていた、本来あるべき配列数式を実現するための機能で、Excelの場合は関数ではなく機能として装備されています。機能の概念的には、通常の数式で指定した結果が配列で返されるというもので、Arrayformula関数と同じ概念です。Excelではこれをスピルと呼び新機能としていますが、元々配列数式という機能があったものの、あまりにも利便性が悪く、実装が不完全で問題が多かったものを再実装したものになります。
文章だけ見るとややこしく難解のように思えますが、要するにこれまで数式は単一の答えしか返せなかったものが、範囲指定をした場合には複数（配列）で返してくれるようになったという事で、単純な以下のような式の場合、指定した範囲の値がゴッソリ持ってこられるようになるというわけです。
|
1 2 |
//指定範囲の値をゴッソリ参照する =A2:E21 |
図：単純に見えて奥の深い機能の１つ
よくある事例では、九九を数式一発で構築するのに使えるというので、以下のような形でスピルを応用して計算させると九九表が完成します。
|
1 2 |
//範囲同士を掛け合わせて九九を計算 =A2:A10*B1:J1 |
図：数式一発で九九表まで作れる。これが配列数式の力
スピル範囲演算子
Unique関数で説明が少しありましたが、スピル範囲演算子とは特定の範囲を丸ごと条件式として他の関数で扱えるようにするもので、例えば、unique関数で取り出した配列がF列にある場合、Sumif関数等で条件式として使えるようにするものです。但しちょっと使い方が特殊で変則的になるので、正直あまりオススメできません。便利であるとは言えますが、わざわざこれを使うくらいならPower Queryを使ったほうが楽で、いちいち頭を悩ましたり、他の人の理解を阻害せずに済む。
|
1 2 3 4 5 |
//通常のSumifsの関数 =SUMIFS(E2:E21,D2:D21,"Na硫化塩泉") //スピル範囲演算子でまとめてsumifs =SUMIF(D2:D21,G2#,E2:E21) |
sumifsは指定範囲内のデータの合計を、指定の条件を複数つけて「単一の合計値」を出すための関数です。=sumifs(合計したい範囲, 条件となる範囲, 条件１)といった形で指定します。当然答えは１個で返ってきます。これを１つの数式で全行まとめてsumifsを適用する（arrayformula関数で式を配列展開するが如し）のが、スピル範囲演算子です。
数式的には=sumifs(条件となる範囲,スピル範囲演算子で列指定, 合計したい範囲）となり、指定する値の順序が異なる。また、F列なので、F2#とすることでF2以下のF列の値全てという指定になり、これがスピル範囲演算子と呼びます。温泉一覧ではunique関数でユニーク値を取り、元データの温泉泉質の列を条件となる範囲として指定⇒スピル範囲演算子はunique関数でのユニーク値の列（F列）、そして、合計したいポイントの列を指定する事で、sumifs式の結果が単一の合計値ではなく「配列の計算結果」が返ってきます。
図：便利だけれど難解
スピルの裏技
例えばvlookup関数やif関数などはxlookupと異なり元々単体の参照した値を元に処理をする関数です。これらを数式一個書くだけで簡単にスピルで広げたい場合には、以下のように記述すると可能になります。
|
1 2 3 4 5 |
//IF関数の場合 =IF(E1:E12>30,TRUE,FALSE) //vlookup関数の場合 =VLOOKUP(E1:E12,H1:I12,2,0) |
テーブルなどを使わずとも数式一つで結果をバシっと出せます。
また、EOMONTH関数等は、単体の参照を得て答えを出すタイプの関数なのですが、これを前述のような範囲指定をしてもスピルしてくれません。このようなスピル未対応関数の場合、以下のようにするとスピル出来る場合があります。単体で範囲指定で実行するとエラーになります。EDATE, NETORKDAYSなどの日付系関数がこのタイプです。
|
1 2 3 4 5 |
//EOMONTHをスピルする =EOMONTH(A1:A18*1,0) //NETWORKDAYSをスピルする =NETWORKDAYS(A1:A11*1,B1:B11*1) |
範囲に対して「*1」をすると何故か配列になって返ってくるので、数式一つでスピルで答えを得ることが可能です。ただ殆どの関数がスピル対応してるので、このようなことをせずとも答えを返してくれるハズです。
また、逆に勝手にスピルされると困る場合があります。この場合結果の数式を変更できないので、以下のように@マークを付けるとスピル解除になります。
|
1 |
=@B2:B16*@C2:C16 |
数式の先頭に@マークをつけるだけなので簡単です。
Googleスプレッドシートとの互換性
アップロードしたxlsxファイルをGoogleスプレッドシートで開いたり、ダウンロードしてみました。今回はサンプルをアップロードして数式は正しく動くように手修正を加えています。こちらがサンプルシートになります。
xlsxをアップロード時
これまでのファイルの場合、今回の特に配列数式のような場合にはarrayformulaなどに変換されて表示がされていました。しかし、Filterなどのxlsxの配列数式の場合には、素直にFilter関数で変換をすれば良いものを
|
1 |
=ARRAY_CONSTRAIN(ARRAYFORMULA(_xlws.FILTER(A2:E21,E2:E21>80)), 6, 5) |
といったように、array_constrainとarrayformula関数で括られた上に、xlwsという文字とFilterが結合された入れ子で変な関数に変換されていました。結果、正しく動作しておらず、Filter関数の部分だけに数式を削ると正しく動作するという状態です。
Googleスプレッドシート側で完全に余計な事をやってるが故に互換性が失われてる状態です。
図：正しく表示するには数式の修正が必要
xlsxに変換してダウンロード時
逆に、Googleスプレッドシートで正しく動いてるスプレッドシートを、xlsx形式に変換してダウンロードした場合には、どのように表示されるのか？をテストしてみたいと思います。
前述のように互換性のない関数は除去し、また機能的にGoogleスプレッドシートでは出来ないものを取り除いてダウンロードしてみて、Microsoft365のExcelで読み込んでみました。まず起動してみるとさっそくエラー表示が出ます（Mac版Excelでも同様のエラー）。
図：ブック修復のエラーが出る・・・
図：修復結果の表示
修復するを実行しないと表示すらされないので、修復を実行してみます。あけてみたところ、こちら側もExcelに備わってる関数ではなく
|
1 |
=IFERROR(@__xludf.DUMMYFUNCTION("UNIQUE(D2:D21)"),"Ca硫酸塩泉") |
といったような、オカシナ変換が掛けられており、素直に元の関数で表示がなされない状態です。表示されてる結果自体は正しい値なのですが、これは非常に問題です。Arrayformulaの式はExcelの旧配列数式の形式に変換されて表示されており、Sort関数に至っては数式が削られて元の値のみが表示されてる状態。
seaquence関数やrandarray関数はエラーとなっており（エラー原因は数式がわざわざ配列数式に変えられてるのが原因）。よって、こちら側も手直しが必要になります。
Microsoft側が後から実装している関数が多い為、わざわざ寄せているというのが見て取れる反面、Google側は全く寄せる気が無いというのが伝わる結果です。この結果からすると、Microsoft365側にGoogle側が互換性で寄せないとGoogle Workspaceは利用者が離れていくのではないかと思います（パッケージ版Excel準拠で、Microsoft365は無視しているとも言えますが）
図：わざわざ数式を壊してる・・・
