Excelでは、バージョンが上がるごとに新しい関数が導入されています。
今回エクセル2021、最新版関数を9個ご紹介します。
新関数
XLOOKUP
上記の表があります。ここでは、氏名から関数を使ってNoを抽出していきます。
XLOOKUP関数は縦方向にデータを検索するVLOOKUP関数と、横方向にデータを検索するHLOOKUP関数の後継に位置づけられる関数で、縦横のデータ検索が1つで行うことができる関数です。
XLOOKUPの引数はこの様になってます。
まず、【検索値】は名前を検索したいので、氏名をクリックします。
検索範囲は氏名の所から検索したいので上記の様に選択します。
次に戻り値です。今回はNoを検索したいので上記の様に選択します。
エンターキーを押すと
この様にナンバーを検索する事が出来ます。
上記の様な横の表も、
このように、XLOOKUP関数で、Noを抽出することが出来ます。
上記の様な表であっても
上記の様に関数を入力することで、
この様に部署と氏名が一発で抽出されます。
上記の表の場合、Noに20は入ってません。この様な場合
IFERROR関数で、エラー値かどうかを調べたい数式やセル参照を指定して、値］がエラー値の場合に返す値を指定していましたが、
XLOOKUP関数を使えば、
見つからない場合は、4番目の引数に【”該当ナシ”】と入力します。
すると
IFERROR関数 を使わなくても、上記のように該当ナシと表示されます。
SORT関数
SORT 関数は指定したセルの範囲や配列を行や列で並べ替えした配列を取得します。昇順や降順に並び替えしたいときに使用します。
sortというのは並び替えと言う意味になります。
インデックスで範囲の何行目または何列目で並べ替えるかを指定できます。順序で降順に並べ替えるように指定できます。方向で列単位に並べ替えるように指定できます。
デフォルトで良ければ引数は範囲だけでも大丈夫です。
SORT関数 を使って、範囲をドラックすると、氏名の順番で並び替えをする事が出来ます。
営業成績順で並び替えも可能です。
並び替えインデックスの引数を2とすると、売上成績は2列目なので、金額順の並び替えが可能になります。
並び替え順序を －1に設定することで、
この様に降順に並び替える事も出来ます。
UNIQUE関数とSUMIFS関数
指定した範囲の重複するデータを1つにまとめたり、1回だけ現れるデータを取り出したりできるUNIQUE（ユニーク）関数の使い方を解説します。
UNIQUE関数 は、［範囲］の中で、重複するデータを1つにまとめたり、1回だけ現れるデータを取り出したりします。
UNIQUE関数を使って上記の様に範囲を指定すると
この様に重複するデータがなくなります。
一緒にSUMIFS関数を使うと日付別の集計が出来ます。
Excel 2019とOffice 365のExcelでは、従来のExcelにない働きをする「スピル」という新機能が利用できます。スピルの記事は↓です。
スピル機能の概論を省いて説明します。
上記の様に関数を入力する事で、費目別集計が出来ます。
FILTER関数
FILTER関数に関しては、上記の記事をご確認ください。
SORTBY 関数
SORTBY関数は、指定したデータの範囲内で複数のデータを基準に並べ替えすることができる関数です。第1基準に名前を昇順で、第2基準に年齢を降順でといった柔軟な並べ替えを簡単に行うことができます。
上記の様な関数を挿入すると
E列はSORTBY関数で昇順に並べ替えています。
応用
上記のような表で、売り合上げ成績順・評価順に列を並び替えたい時
配列を指定して、売上成績順,昇順は1を入力します。
売上成績の低いものから並び替える事が出来ます。
配列を指定して、売上成績順,降順は-1を入力します。
売上成績の高い順から並び替える事が出来ます。
RANDARRAY関数
RANDARRAY関数の引数はこの様になってます。
具体的には、
※必須の引数はありません
ランダムな数値を簡単に作れるものです。
この様に、入力すると、ランダムな数値になります。
行数・列数を指定することで、行列の指定が出来ます。
最小値・最大値も指定できるので、例えば1～100までの値を出すときは、
上記のように設定します。
デフォルトでは、小数点が入っていますが、5つめの引数をTRUEにすると、整数になります。
XMATCH関数
XMATCH関数は、配列またはセル範囲内で指定された項目を検索し、最初に一致した項目の相対的な位置を返します。
検索値が見つからない場合は、#N/Aを返します。
MATCH関数を機能強化した新関数です。
=XMATCH(検索値,検索範囲,[一致モード],[検索モード])
検索値必須です。
検索する値を指定します。
MATCH関数は255文字の制限がありましたが、
XMATCH関数は文字数制限がなくなりました。
検索範囲必須です。
検索する範囲または配列を指定します。
指定範囲または配列は、1行または1列のみです。
複数行かつ複数列を指定した場合は、#VALUE!のエラーになります。
完全一致でのXMATCH関数を使った検索例を紹介します。下記例では支店名の中から、指定した数値が何番目に位置するかを返します。
上記の様に、数式バーに XMATCH関数 を使って 検索値と検索範囲を設定します。
すると、佐賀支店のが何番目に位置しているかが分かります。
宮崎支店を下の方から検索したい場合、上記の様な数式を入力すると
上記の様に第四引数を変更すると
この様にしたの方から数えることが出来ます。
コメント