タグ別アーカイブ: 連続データ

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へ


にほんブログ村

【M言語に慣れる】_13回_ダイナミックに連続した日付作成

【M言語は難しくない】今回は、M関数を使用して連続した日付を作成する方法を解説します。2つ連続した日付を作成する方法を解説した後に、ダイナミックに日付を作成する方法を解説します。下のGIF画像のように、ある表の最小・最大日と連動させて、連続した日付が入った表を作成する方法を解説します。

上のGIF画像では、画像左の最大値を「2021/04/04」から「2021/04/06」で入力し直しています

その後、「更新処理」を行います

すると、画像右にある表の最終行が「2021/04/04」から「2021/04/06」に拡大します

この回は、過去の記事で解説した波括弧:{}を使用した「連続データを作成する技術」をフルに活用します!

目次

今回のポイント

今回使用するデータ

連続した日付の作成

ダイナミックに連続データを作成

<まとめ>

今回のポイント

今回も前述のように、過去記事で紹介した括弧の使い方が最大のポイントになります

後、これまで紹介してこなかったM関数も使用します

括弧の使い方

過去記事にて、1から10まで連続した数字を空のクエリに作成する方法を解説しました

今回はこの波括弧 / {}(最小..最大)から作成される連続データを使用して、連続した日付を作成します

フィルタリング

日付が入ったテーブルは下の図のようにフィルタをかけて、最小日と最大日は特定できるようにしておきあす

上の図のようにフィルタリングを行うと、下の図のようなテーブルができます

1行目が最小日、2行目が最大日です

こちらは、連続した日付を作成するときに使用します

M関数

今回使用するM関数はこちらです

事前に概要を紹介しておきます

#date

「年、月、日」から日付を作成します

#duration

「日、時間、分、秒」から期間値を作成します

List.Dates

「開始日、リスト個数、増分」から日付のリストを作成します

#dateと#durationと組み合わせて作成します

Number.From

値を数値型式にして返します

今回使用するデータ

 今回は、記事の冒頭でも前述したように、まずは2つ連続した日付を作成する方法を解説します

その後、次の画像のデータを使用します

上の画像の「最小と最大の日付」を変えたら、連動して連続した日付を入れた表が作成されるようにします

連続した日付の作成

 これから2つ作成方法を解説しますが、どちらもまずは空のクエリから作成します

  エクセル画面の上にある「データタブ」➡「データの取得」の順でクリックします

その後、下の画像にて黄色に印をつけた箇所を上から順にクリックします

そして、下の画像にある「空のクエリ」をクリックします

するとエディタが開き、空のクエリが立ち上がります

M関数 / List.Datesで作成する方法

既に今回のポイントで解説済みの内容になります

前述の通り、#dateと#durationを組み合わせて作成します

では、以下の条件で連続データを作成してみます

・開始日 / 2021/05/01

・個数 / 5個

・増分 / 7日

関数は次のように記述します

=List.Dates(#date(2021,5,1),5,#duration(7,0,0,0))

上の関数を数式バーに入力すると、下のGIF画像のように2021年5月1日から始まる日付リストが「7日」おきに「5個」作成されます

波括弧 / {} を使用して作成する方法

前述の通り、波括弧 / {}(最小..最大)から作成された連番を利用して連続した日付を作成します

この方法の場合には、作成した連番を一旦テーブル形式に変換した後、日付形式に直すのがポイントです

ちなみに、エディタ画面からも「空クエリ」は作成できます

まず、下の画像のようにエディタ画面の左で右クリックします

その後に、上の画像の「新しいクエリ」右横にある▶マークをクリックします

すると「空のクエリ」を選択する画面がでます

さて、話を連続データの作成に戻します

まずは、次のように数式バーに入力して連続データを作成します

= {Number.From(#date(2021,4,1))..Number.From(#date(2021,4,30))}

数値型式を返す「Number.From関数」の中は、「#date関数」にて日付を作成しています

上の数式を入力した段階では、ただの「値」の羅列になっています

こちらはあくまで「リスト型式」なので、このままだと日付形式に変換できません

ですので、こちらをテーブルに変換します

すると次の画面のようになり、データ形式を指定できるようになります

こちらの画面で列名と合わせて、データ形式を直します

これで、連続した日付が作成できました

こちらのクエリは、後で表と連動させますので、クエリ名を「連続日付2」として保存します

この連続したデータを作成する方法の「2つ目」として解説した方法は、次の数式を空のクエリの数式バーに入力するところからはじめました

={最小日..最大日}

この数式は表と連動させてダイナミックに連続データを作成する際、応用して活用します

以降は有料ページ/課金サービスへ