タグ別アーカイブ: スピる

SEQUENCE関数で連続データ作成~組み合わせ活用~

 今回は、SEQUENCE関数という少しユニークな使い方をする新型関数について解説します

 このSEQUENCE関数は他の関数と組み合わせて使用すると、その便利さは無限に広がります

上のGIF画像では、1つのセル1つの関数を入力するだけでB2セルに入力されている「エクセル」の文字列を各セル別に分解しています!

では、基本的な使い方から解説をはじめます

基本的な使い方

SEQUENCE関数の書き方は次の通りです

 ちなみに[]の中は省略可という意味になっており、指定しなければ自動的に1が設定されます

=SEQUENCE(行数,[列数],[開始番号],[増分])

行数のみ指定

例えば、下のGIF画像のように行数を5とだけ指定すると、1~5の連続する数字が自動作成されます

行数に加えて列数等も指定

では次の様に、各引数を指定してみます

=SEQUENCE(2,3,4,5)

①行数:2

②列数:3

③開始:4

④増分:5 

 ①~④をSEQUENCE関数の引数に反映すると、次のような連続データができます

開始が4からはじまり、増分が5(例:4→9)になっています

そして行数は2、列数は3となっています

日付データ作成

では、今日(2021年5月27日)の日付を含めた10個の日付データを作成してみます

開始日には、=TODAY()を指定します

=SEQUENCE(10,1,TODAY())

上のGIF画像では、ただの連続データが作成されていますが、書式を日付に変更すれば下の画像のようになります

曜日データ作成

では、上の画像の日付データの横に曜日データを作成します

こちらは、WEEKDAY関数を各セルに入力するのではなく、下のGIF画像のように「#」を使って入力します

上のGIF画像では、=WEEKDAY(B2#)としてB2セルの次に#を付け足すことで、新型関数の抽出結果を再利用しています

上記の画像では、曜日が1~7の数字で表現されていますが、書式を「ユーザー定義」で下の画像のように「dddd」と設定すれば文字列の曜日で表示されます

上の画像のように連続した日付データを作成しておけば、下の画像のように「FILTER関数」の抽出条件式をOR条件で使用して、複数の好きな曜日の日付を抽出できます

上の画像では、抽出したい曜日をE2セルとF2セルに指定しています

ちなみに3は火曜日(日曜日が1)で5が木曜日です

*WEEKDAY関数の2番目の引数/省略可でどの曜日を1にするかは指定可

FILTER関数の抽出条件の設定(2番目の引数)についてはOR条件なので、2つの()の中に条件式を入れた上で「」でOR条件を指定します

=FILTER(B2#,(C2#=E2)+(C2#=F2))

文字列データの分解

記事の冒頭で紹介した、SEQUENCE関数を使用した「文字列の分解」について解説します

こちらはSEQUENCE関数をMID関数及びLEN関数と組み合わせて使用します

まず、話しを分かり易くするために「エクセル」の文字列が入力されたB2セルを操作対象として、MID関数を次の様に使用してみます

=MID(B2,1,1)

抽出結果は次の画像の通りです

上の画像では、MID関数の抽出開始位置を1としていますので「エ」が抽出されています

この2番目の引数の1を「エクセル」の文字列の長さまで変動させたらどうでしょう?

SEQUENCE関数を「=SEQUENCE(1,LEN(B2))」のように指定すると、次の画像のように1~4の文字列が行方向で作成されます

では、MID関数の2番目の引数に上のSEQUENCE関数を、次のように入れます

=MID(B2,SEQUENCE(1,LEN(B2)),1)

そうすると、次のGIF画像のように「エクセル」の文字列/4文字を5文字の「EXCEL」にかえても正確にセル毎に分割されます

<まとめ>

今回は、SEQUENCE関数という少しユニークな新型関数を紹介しました

連続データを作成するだけであれば、ホームタブに既に機能はあります

 このSEQUENCE関数は他の関数と組み合わせてみてはじめて、有効性を発揮します

 今回は、日付データと文字列データの分解の2つしか事例を紹介できませんでしたが、読者の皆さんの職場に色々とアイデアが落ちていそうですね

ぜひ、使用事例を連絡頂けると嬉しいです

長文を最後まで読んでくださり誠にありがとうございます

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

Filter関数~複数行を抽出~

 Office365のバージョンから新たなコンセプトの関数が使えるようになっているのをご存知ですか?

 新たなコンセプトの関数では、1つのセルへの関数の入力で、複数の値が抽出できます

 このブログではこの新たなコンセプトの関数を新ダイナミック関数と呼ぶことにします

 この新ダイナミック関数を使いこなせば、データの集計にかけていた時間を大幅に削減することができます

 この回では、新ダイナミック関数の中で一番代表的な「Filter関数」について紹介します

この回で解説するは「Filter関数」の基本的な使い方のみです

次の回では、テーブル機能と連携して引数を設定する方法、及び、抽出条件をAND条件やOR条件で抽出するを紹介します

ぜひ、そちらも楽しみにしていてください

Filter関数とは

Filter関数は次の様な文法で書きます

=Filter(①抽出範囲 , ②抽出条件 , ③該当するものが無い場合[省略可])

①の抽出範囲から、②の抽出条件に該当するものを集計します

これまでの関数とは全く違うイメージの関数なので、早速、実際の使用例を見てFilter関数の便利さを体感してみましょう!

Filter関数の使い方・実際

使用データ

Filter関数を使用するデータは次のデータになります

上のデータから「製品別」が「トラック」のデータだけを抽出します

集計

範囲指定

まず、抽出範囲を指定します

上の画像の段階では、数式は次のようになっています

=FILTER(A2:D31

3つ目の引数の「該当するものが無い場合」については、ここでは省略し、後ほど別途解説します

抽出条件

次に、抽出条件を指定します

この抽出条件は、次の画像の黄色の箇所のようにを丸ごと指定するのがポイントです

では、ここで”)”で上の画像の数式を閉じて、Filter関数を確定させます

Filter関数はF5セルに入力しただけですが、抽出範囲から「製品別」の列の内容が「トラック」に該当する「全ての行」が抽出されます

該当するものが無い場合

Filter関数で3番目の引数「該当するものが無い場合」を指定して見ましょう

ちなみに、既に作成したFilter関数をそのまましておき、抽出条件を該当するものが無い「EV」にすると次の画像のようになります

では、3番目の引数を次のように「該当なし」と設定してみます

=FILTER(A2:D31,B2:B31=F2,”該当なし”)

すると、次のように出力されます

使用上の注意点

新ダイナミック関数の全般に言えることですが、関数の出力範囲のセルに「なにがしかの入力」があると次の画像のようなエラーがでます

入力されたフォントの色が白になっていて、入力されているように見えなくてもこのエラーは出ますので注意が必要です

<まとめ>

Filter関数は前述の通り、これまでと全く違ったコンセプトの関数です

 発想を変えれば、今までならVBAなどを使って処理するものでも関数だけで処理が行えます

 このブログでは、Filter関数を他の関数と組み合わせて使用する事例も紹介しています

 例えば、シート名を抽出できるCELL関数と組み合わせて、1つのデータをシート別に振り分けたりすることもできます

 上のGIF画像では、Filter関数が既に入力されているシートをコピーし、シート名を「CCC」というシート名に変更しています

 そうすると、自動的に「カテゴリー」が「CCC」のデータが元のデータから自動抽出されています

 次回は、Filter関数の応用として「テーブル連動」や「AND条件やOR条件でのデータ抽出」を行います

記事を最後まで読んで下さり誠にありがとうございました

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村