タグ別アーカイブ: 新関数

関数一つで複数表を垂直統合~VSTACK~

【便利な関数一つを覚えるだけで、面倒な転記作業から解放されます】

複数表の垂直統合はPower Queryでもできますが、ただ統合するというだけならVSTACKが便利ですね

この記事では単にVSTACK関数を解説するだけでなく、応用的な使い方も解説します

VSTACK

VSTACK関数内では、単に複数の範囲を指定すれば表が垂直に結合されます

=VSTACK(B3:E9,G4:J10)

この場合、見出しが重複しないように指定範囲を調整します

見出し付きのVSTACK関数

前述の方法では、見出しを範囲の指定の仕方にて調整しました

では、範囲の指定をテーブルで行う場合には見出しの調整はどう行うか?

単にテーブルを指定した場合には、次の画像のように「見出し」が無しになります

この場合には、見出し自体を関数内で指定します

見出しは波括弧内に指定します

=VSTACK({“客先コード”,”部門コード”,”出荷日”,”金額”},Table1,Table2)

更に並び替え

では、

VSTACK関数で結合した表を、更にSORT関数で並び変えてみます

=SORT(VSTACK({“客先コード”,”部門コード”,”出荷日”,”金額”},Table1,Table2),①4,②-1)

SORT関数の第二引数①では並び替えの基準となる列を指定します

第三引数②では並べ替え方(昇順:1、降順:-1)を指定します

複数シートの結合

下の画像のように複数シートに分かれる表を結合する場合は、指定範囲が同じであれば「簡単な」書き方ができます

シート名を「:」でつなぐだけで複数シートを一気に指定できます

=VSTACK(‘2020年1月:2020年3月’!A2:C4)

<まとめ>

今回は、VSTACK関数の使い方を応用編も加えて解説しました

複数の表を関数1つでまとめられるのはとても便利です

内容もとても簡単です

範囲を関数内で複数指定するだけです

ただ見出しの扱いだけは、重複しないように注意しましょう!

参考までに今回の記事で使用したファイルを添付します

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

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

RANDARRAY関数でテストデータ作成

 今回は、1つのセルへの関数入力でテストデータが作成できる「RANDARRAY関数」を紹介します

 旧関数でもRANDBETWEEN関数という関数があり、この関数でもテストデータは作成できました

今回紹介する「RANDARRAY関数」との違いは主に次の2つです

・整数以外も指定できる

・複数列でも指定できる

上に紹介した2つの違いを意識しながら、解説を行って行きます

RANDARRAY関数の基本

RANDARRAY関数の書き方(引数)は以下の通りになっています

=RANDARRAY(行,列,開始,終了,整数)

上に挙げた引数のすべてが省略可能となっているのが特徴です

〇省略した場合の設定

・行➡1

・列➡1

・開始➡0

・終了➡1

・整数➡FALSE(0と1の間)

例として、第一引数の行だけを「5」と指定してみます

そうすると、上のGIF画像のように5つ行方向に「0と1」の間の「数字」がランダムに出力されます

では、こちらを5つ目/最後の引数に「TRUE」を指定してみます

すると、0か1が5つ出力されます

では、0と1だけではなく「1から5」まで出力されるようにしてみましょう

3つ目と4つ目の引数にそれぞれ「1」と「5」を指定します

=RANDARRAY(5,,1,5,TRUE)

これで1から5の間で出力値を動かせるようになりました

ちなみに、F9キーで値は変動させることができます

複数列に値を出力

では、前述のRANDARRAY関数の2番目の引数に3を指定してみます

すると上の画像のように3列で出力されます

実践編

では、仮に生徒をつのクラスにランダムに割り当てるケースを考えて見ます

この場合には、第一の引数/行数にCOUNTA関数を設定して、人数分のランダムな数字が出力されるようにします(第三の引数/開始には3を設定)

=RANDARRAY(COUNTA(B:B),1,1,3,TRUE)

そうすれば、生徒数に応じて割当が行われます

なお、このケースは生徒数がある程度の数がいるケースを想定しています

生徒数が少ないと割り当てられないクラスが出る可能性があります

まとめ

今回は、RANDARRAY関数でテストデータを作成する方法を解説しました

今回は、COUNTA関数と組み合わせてサンプル数に合わせて出力するケースについても解説しました

実は他にも有効な関数の組み合わせ方法があるかもしれませんね

ぜひ研究して見たいと思います!

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

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

UNIQUE関数xSORT関数から集計表作成~#の魔法活用~

FILTER関数UNIQUE関数などの新関数の醍醐味は「関数同士の組み合わせ」にあります!

 今回は、UNIQUE関数とSUMIFS関数の組み合わせに加えて、「」を組み合わせて「集計表」を2つのセルへの関数入力だけで作成します

  本来であれば、「ピボットテーブル作成」を経由しなければならない「集計表の作成」の作業が格段に楽になります

 過去の記事でも#を有効活用した「検索付きリスト」の事例は紹介させて頂いていますが、今回は改めての使い方について解説させて頂きます

#とは?

 FILTER関数などの新関数は、1つのセルへの関数入力にて、複数のデータを抽出できるのが特徴です

「#」を使用すると、新関数で抽出されたデータを再利用できます

では、1つの事例で解説したいと思います

 下の画像のF2セルにはUNIQUE関数が入力されており、左にある表の「製品別」の列から重複しないデータを抽出しています

こちらのUNIQUE関数で抽出したデータを、を使って再利用してみます

 上の図ではI2セルに「=F2」と入力することでF2セルの内容を、I2セルに抽出しています

では、I2セルに入力した「=F2」の後に#を付けてみます

 上の画像のように「参照セル名」の後に#をつけると新関数のデータ抽出結果を再利用することができます

UNIQUE関数xSORT関数

 #について解説したところで、本題のUNIQUE関数を使用した集計表の作成に入りたいと思います

 まず、前述のようにUNIQUE関数にて製品別の列から重複しないデータを抽出します

更に、UNIQUE関数の抽出結果をSORT関数で並び変えることもできます

 上の画像のように、SORT関数の3つ目の引数を「-1」に変更して並び順を降順に変えることもできます

集計表の作成

 それでは、前述のUNIQUE関数で抽出したデータ毎に対して、左の表から「売上金額の集計金額」を紐づけます

 上の画像では、UNIQUE関数で抽出した「乗用車」に対して、SUMIFS関数を使用して左の表から「乗用車」の売上金額を集計しています

 では、上の画像のSUMIFS関数の3つ目の引数「乗用車」のテキストが入ったF2セルの指定に対して前述のを組み合わせます

これで、1つのSUMIFS関数の入力で集計表の1列が完成しました

<まとめ>

 今回は、UNIQUE関数xSORT関数の組み合わせに、更に#を組み合わせて集計表を完成させました

#を使えば、新関数の抽出結果を再度利用することができます

エクセル進化は本当に凄まじいですね

2つのセルへの関数入力だけで集計表が完成できるのは大きいです

この機会に新関数x#の組み合わせは有効活用できるようにしておきましょう!

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

にほんブログ村

#SPILLエラーについて~新関数に独自のエラー

こんにちは、Excellent仕事術ガッツ鶴岡です

 これまで、1つのセルへの入力で複数セルに出力する新関数について、複数の記事を書いてきました

 例えば、下のGIF画像はFILTER関数を使った検索付きのリストの例です

 C列にFILTER関数を仕込んであり、E列で検索文字「鈴木」が設定されるとA列のリストデータから「鈴木」を含むリストデータを出力します

 この新関数では独特のエラー「#SPILL」が出力される時があります

このエラーの意味について、今日は少し解説したいと思います

SPLLLエラー

見慣れないエラーですが、発生理由はシンプルです

 この新関数は複数のセルに出力を行いますが、出力範囲に何らかの入力がある場合にこのエラーは発生します

 

上の画像で言えば、!の文字が新関数の出力をブロックしてるのです

このブロックしている文字を取り除けば、このエラーは解消されます

 尚、ブロックしている文字のフォントが「白色」で人の目では見えない時にも同じエラーが発生するので注意が必要です

では、今回は以上です

最後までブログ記事を見て下さり、大変感謝しております!

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

にほんブログ村