Excelの関数は日々増えているのですが、本当にやりたいことをとことんまでやりやすく極めていくと、今の関数ではまだまだ不足しています。
XLOOKUP関数ができてINDEX関数とMATCH関数を組み合わせることがなくなったとか、便利にはなったのですが、実際に他の表から値を参照したい時は1列だけではなくて複数列参照をしたいなと思うし、絞り込みが関数でできるようになったFILTER関数でも、必要な項目だけ抜き出して出力したいなどといったことがあります。
LET関数やLAMBDA関数によって、あらゆる計算が出来るようになるとはいえ、Excelの関数の使い方そのものだったり、やりたいことに対して関数を使って今あるデータにどうアプローチしていくか考える力必要だったりします。
本来、DXをとことんまで推進するということを考えれば、Excelの知識が一切なくても業務の知識だけでやりたいことが全てできる、というのが出来上がりそうだと私は考えています。
そこで、今まではExcelの使い方、パソコンの使い方が大事と言ってきたのですが、それはこれからも変わらずに知識を求めてより高い志で業務に向かいたい方に便利な使い方を提供していきますが、それと同時に今のExcelをスキルがなくても使えるようにしていくように私から提供できるものがないかなーと考えました。
作成した関数
そのような考え方のもと、昨晩からせめてSQLのような感覚で使えるExcelのユーザー定義関数をいくつか作りました。
ファイルはこちらから
yos_Name関数
ブックに設定されている名前の一覧を参照します。
書式：=yos_Name()
yos_union関数
セル範囲を縦に結合します。
書式：=yos_union(タイトル有り無し,範囲1,範囲2,…)
タイトル有り無しをFalseにすると単純に縦に積み重ねます。Trueにするとそれぞれの範囲の1行目をタイトルとし、タイトルに合わせてそれぞれの列に積み重ねて配置していきます。
使用例1：=yos_union(FALSE,A10:D16,A1:C3,E1:G5)
単純にA10:D16,A1:C3,E1:G5の3つの範囲を縦に積み重ねます。
使用例2：=yos_union(TRUE,A10:D16,A1:C3,E1:G5)
A10:D16,A1:C3,E1:G5それぞれの1行目をタイトル行として縦にそれぞれの項目ごとに積み重ねます。
yos_selectcolumn関数
一覧表から指定した項目だけを抜き出します。
書式：yos_selectcolumn(一覧表範囲,1行にまとまった出力する項目一覧)
タイトル行を含む一覧表範囲から出力する項目一覧のデータだけを抜き出した表を作成します。
使用例：=yos_selectcolumn(A1:D100,N1:P1)
A1:D100の範囲のN1:P1の項目のデータだけの表を出力します。
yos_join関数
タイトルを含む一覧表から指定した複数の項目のデータを抜き出します。
書式：=yos_join(キー範囲,一覧表範囲,出力する項目範囲1,出力する項目範囲2,…)
キー範囲と一覧表範囲はタイトル行を含み、出力する項目は1行に出力したい項目がまとまっているようにします。出力する項目範囲はいくつでも設定ができます。
使用例：=yos_join(K19:K23,D19:H25,L19:N19)
K19:K23にあるキー列一覧に該当するデータをD19:H25の中から選びL19:N19に記載されている項目のデータ一覧表を作成します。
yos_group関数
一覧表を集計します。
=yos_group(一覧表範囲,グループ化の基準の列,集計する列,”合計”/”個数”)
タイトルを含む一覧表範囲をグループ化の基準の列の項目を基準に集計する列の項目のデータを集計します。集計する方法は合計か個数を指定します。集計する列のデータに文字列が含まれる場合は個数のみ集計ができます。
=yos_group(J19:N23,K19,M19,”個数”)
J19:N23の範囲でK19の項目を基準にM19の項目の列のデータの個数を出力します。出力されるのはK19の項目とM19の項目の2列だけです。
配布方法
そして今になってこの関数をどのように広げていけばいいかということを考えなければならなくて、その方法を考えてみました。
ユーザー定義関数を作成したら、そのマクロ有効ブックを、名前を付けて保存をしてアドイン形式で保存します。
これだけで他の人がそのユーザー定義関数を使う準備ができます。
あとは、そのファイルを受け取った人がExcelのファイルタブのオプションでアドインの中の参照でその ファイルをアドインとして読み込むだけでそれらの関数が使えます。
調べてみてわかったのですがアドインはハードルが高いように感じていたのですがただアドイン形式で保存すればいいだけだったので誰でもExcelの機能追加ができるような気がします。
まとめ
Excelの機能の操作画面や関数の引数の指定が今一歩なのだけど、 Microsoft もいつ対応してくれるのか分からないので、こうなったら自分たちで作ってそれをみんなで作ってみんなで共有すれば良いのではないかと思っています。しかし、それには、皆が同じ形で同じように作成するルール作りだったり、誰かが作ったものを同じものをもう一度作ると言った手間が発生しないようにする重複しない仕組みづくりだったり、それぞれの関数や機能の使い方のマニュアルを配布する仕組みを作成したりといったことが課題になります。
でも、一旦そういったところを棚上げして仕組みを配布してExcelで苦労しないようにするということを優先して配布していきたいと思います。
またこのような考え方にご賛同頂けた皆さんにも簡単にご自身が考えた関数や機能を配布できるように作り方のテクニックなどもこれから発信していければと思います。
コメント