Excelではバージョンが上がるごとに新しい関数が追加されていて、それらを「新関数」と呼びます。新関数をいくつか紹介します
Unique 関数
範囲内の重複していない値を返します
=UNIQUE(配列）
Unique関数は「スピル」が機能する関数です。「スピル」とは、１つの数式をセルに入力するだけで、隣接するセル範囲にも結果を表示する機能です。スピルは英語でSpill。「こぼれる、あふれる」という意味があります。つまり、数式を入れたセルから隣接したセルに結果があふれて表示される、と言った意味合いになります。Unique関数だけでなく、多くの新関数は「スピル」が機能します
＜例＞ A2:A11の範囲で、重複していない支店リストが欲しい場合。C2セルにUnique関数を入れるだけで、重複していないリストが出来上がります
Randbetween 関数
指定された範囲内の整数の乱数を返します。ワークシートが再計算されるたびに、新しい整数の乱数が返されます。Excelファイルを開いた時や、シートの再計算がされるたびに、異なるランダムな値が表示されます。テストデータ、シミュレーション、ランダムなサンプルを作るときなどに便利です
=RANDBETWEEN(最小値、最大値）
＜例＞ A1からA5の各セルに、ランダムな1～100の間の整数が欲しい場合
Sequence 関数
指定した範囲内に規則性を持った連続した整数が欲しい場合に使用します。下記数式にあるように、欲しい「行数、列数、開始番号、目盛り」と入力していきます
=SEQUENCE(行、列、開始、目盛り）
＜例1＞ 行数は10、列数は1、1からスタートし、1ずつ増やす、という指示で、結果は下記の通り
＜例2＞ 行数は10、列数は1、0からスタートし、5ずつ増やす、という指示で、結果は下記の通り
＜例3＞ 行数は5、列数は3、1からスタートし、1ずつ増やす、という指示で、結果は下記の通り
Textsplit 関数
1セルにある文字などを分割する
=TEXTSPLIT(セル、区切り記号）
＜例1＞ 列Aの氏名を姓と名前に分割する。区切っているのは全角スペースなので、「“ ”
」が区切り記号
＜例2＞ A1セルに複数の情報が規則性のある区切りで入力されている場合にも使用できます。「,
」と「行替え」が区切り記号です。「行替え」は「CHAR(10)」と書きます。これは、このまま覚えてください
Textjoin 関数
文字などを結合して1つのセルに入れる
=TEXTJOIN(区切り記号、空のセルは無視、テキスト1、…）
＜例＞ 列Aと列Bを半角スペースを間にいれて結合したい場合
Textbefore 関数
区切り記号の前だけを取り出す
=TEXTBEFORE(セル、区切り記号）
＜例＞区切り記号「＠」の前だけを返す
Textafter 関数
区切り記号の後ろだけを取り出す
=TEXTAFTER(セル、区切り記号）
＜例＞区切り記号「＠」の後ろだけを返す
Chooserows 関数
選択した行だけを取り出す
=CHOOSEROWS(配列、取り出したい行番号、取り出したい行番号…）
＜例＞A2:C6の配列にある、2行目と4行目を取り出したい場合。最初に項目名をE1:G1にA1:C1をコピーし貼り付けておきます
Choosecols 関数
選択した列だけを取り出す
=CHOOSECOLS(配列、取り出したい列番号、取り出したい列番号…）
＜例＞A2:C6の配列にある、1列目と3列目を取り出したい場合
Vstack 関数
同じ項目名を持った、複数の配列があり、縦に結合したい場合に使います
=VSTACK(配列1、配列2、配列3…）
＜例＞3つの配列を縦に結合したい場合。配列1はA2:D4、配列2はA7:D9、配列3はA12:D14なので、下記の数式になります
Hstack 関数
同じ行項目を持った、複数の配列があり、横に結合したい場合に使います
=HSTACK(配列1、配列2、配列3…）
＜例＞3つの配列を横に結合したい場合。配列1はA2:B4、配列2はB7:B9、配列3はB12:B14なので、下記の数式になります
Sortby 関数
配列または範囲の内容を並べ替えます。「データ」タブにある並べ替えの機能とは異なり、元の表はそのままで、別の場所に並べ替えた結果を表示します
=SORTBY(配列、並び替えをする列範囲、昇順なら「1」、 降順なら「-1」）
＜例1＞配列A2:C6をC2:C6の点数の降順にて並べ替えたい場合。降順にしたいので「-1」
＜例2＞2列で並び替えたい場合。まずは「班」で昇順の並び替え、次に「点数」の降順で並び替えたい場合
Sort 関数
Sortby関数と同じように並び替えたい場合に使用します。Sortの場合は、並び替える範囲ではなく、並び替える列の番号で指定します
=SORT(配列、並び替える列、昇順なら「1」、 降順なら「-1」）
＜例1＞配列A2:C6を3列目の点数で並べ替えたい場合。降順にしたいので「-1」
＜例2＞2列で並び替えたい場合
少し複雑になりますが、”{“記号を使用します。配列の後に、2列目の班、3列目の点数で並び替えたい場合は、{2, 3}と書き、次に昇順か降順かを、その列の順番で入れます。この場合、2列目は昇順、3列目は降順にしたいので、{1, -1}と入れます
Torow & Wraprows 関数
Torows関数とWraprows関数を紹介します。一緒に使用することが多いので、一緒に説明します
下記例をステップを確認ください
=SORT(配列、並び替える列、昇順なら「1」、 降順なら「-1」）
＜例＞結合セルがあったり、エクセルの基本的なデータベースの配列になっていない表を、基本的な1セルに1データが入っているデータベースに修正します
Step1: Torow関数を使って、まずは、1行のデータにします。A1:D10の配列を指定し、「1」を入力。「1」とは、「空白を無視する」という指示になります。例えば、A1とA2は結合されていますが、「長野店」はA1に入力されているとみなされ、A2は空白になります。「1」を入れることで、空白が削除された形で1行のデータに変換されます
Step2: Torow関数をWraprowsで挟んで、関数を完成させます。
F1の数式は、=WRAPROWS(TOROW(A1:D10,1),6)
なぜ、「6」かということ、6つのデータで繰り返されているデータだからです。スピル機能によって配列が完成しているので、項目の順番を修正したい場合は、出来上がった配列を値貼付けして、それを加工してください
練習ファイル
良かったら、ご利用ください
コメント