タグ別アーカイブ: SORT関数
UNIQUE関数xSORT関数から集計表作成～#の魔法活用～
FILTER関数やUNIQUE関数などの新関数の醍醐味は「関数同士の組み合わせ」にあります！
今回は、UNIQUE関数とSUMIFS関数の組み合わせに加えて、「＃」を組み合わせて「集計表」を２つのセルへの関数入力だけで作成します
本来であれば、「ピボットテーブル作成」を経由しなければならない「集計表の作成」の作業が格段に楽になります
過去の記事でも＃を有効活用した「検索付きリスト」の事例は紹介させて頂いていますが、今回は改めて＃の使い方について解説させて頂きます
＃とは？
FILTER関数などの新関数は、１つのセルへの関数入力にて、複数のデータを抽出できるのが特徴です
「＃」を使用すると、新関数で抽出されたデータを再利用できます
では、１つの事例で解説したいと思います
下の画像のF2セルにはUNIQUE関数が入力されており、左にある表の「製品別」の列から重複しないデータを抽出しています
こちらのUNIQUE関数で抽出したデータを、＃を使って再利用してみます
上の図ではI2セルに「=F2」と入力することでF2セルの内容を、I2セルに抽出しています
では、I2セルに入力した「=F2」の後に＃を付けてみます
上の画像のように「参照セル名」の後に＃をつけると新関数のデータ抽出結果を再利用することができます
UNIQUE関数ｘSORT関数
＃について解説したところで、本題のUNIQUE関数を使用した集計表の作成に入りたいと思います
まず、前述のようにUNIQUE関数にて製品別の列から重複しないデータを抽出します
更に、UNIQUE関数の抽出結果をSORT関数で並び変えることもできます
上の画像のように、SORT関数の3つ目の引数を「-1」に変更して並び順を降順に変えることもできます
集計表の作成
それでは、前述のUNIQUE関数で抽出したデータ毎に対して、左の表から「売上金額の集計金額」を紐づけます
上の画像では、UNIQUE関数で抽出した「乗用車」に対して、SUMIFS関数を使用して左の表から「乗用車」の売上金額を集計しています
では、上の画像のSUMIFS関数の3つ目の引数「乗用車」のテキストが入ったF2セルの指定に対して前述の＃を組み合わせます
これで、１つのSUMIFS関数の入力で集計表の1列が完成しました
＜まとめ＞
今回は、UNIQUE関数ｘＳＯＲＴ関数の組み合わせに、更に＃を組み合わせて集計表を完成させました
＃を使えば、新関数の抽出結果を再度利用することができます
エクセル進化は本当に凄まじいですね
２つのセルへの関数入力だけで集計表が完成できるのは大きいです
この機会に新関数ｘ＃の組み合わせは有効活用できるようにしておきましょう！
SORT関数～関数１つでデータを並べ替え～
前回は、新ダイナミック関数の中でも一番代表的なFILTER関数を紹介しました
今回は、並べ替えを行う新ダイナミック関数である、「SORT関数」を紹介します
このSORT関数で行う並べ替えは、関数でなくても他の機能で行えます
それでも、このSORT関数を活用する大きなメリットが2点あります
SORT関数を使うメリット
組み合わせ活用
前述のように、並べ替えならば他の機能で行えます
SORＴ関数の醍醐味は他の関数との組み合わせです
・FILTER関数でデータ抽出 ➡ SORＴ関数で並べ替え
・SORT関数で並べ替え➡SUMIFS関数で並べ替えられたデータを集計
こちらの組み合わせ方法については、また別な記事で解説します
列の並べ替え
SORT関数は列方向でも並べ替えが行えます
今までのエクセルの並べ替え機能でも、列の並べ替えが行えます
今までの並べ替え機能との違いは、データ自体を並び変えるのではなく、他の箇所に出力されたデータを並び変える点です
SORT関数のメリットを２つ解説したところで、本格的な解説を始めます
通常の並び替え/基準1つ
今回使用するデータは次のデータです
まずは、上のデータの「年度」列を昇順で並び変えてみましょう
SORT関数の書き方
SORT関数の書き方は次の通りです
=SORT(①範囲 , ②基準, ③順序)
①範囲➡並び替え範囲（テーブルを範囲として使用可能）
②基準➡並び替えを行う列位置を1から始まる数字で指定
③順序➡1:昇順、-1:降順
並び替えの実際
並び替え範囲を「Data」というテーブル名にてテーブル化した上で、実際にSORT関数の並び替えを行います
左から1列目を基準に昇順で並び替え
この場合、SORT関数は次の通りに指定します
=SORT(Data,1,1)
すると、次のGIF画像の通りに並び替えが行われます
通常の並び替え/基準2つ
並び替えの基準が２つの場合には、前述のSORT関数の書き方が違ってきます
=SORT(①範囲 , ②基準, ③順序)
基準が２つの場合には、②基準及び③順序ともに書き方の工夫が必要です
波括弧を使用して、２つの基準と順を書けるようにします
・例1：②{1,1}、③{-1,1}
・例2：②{1,4}、③{1,-1}
ここで注意点ですが、２つの基準ともに昇順（降順）の場合でも、③は波括弧/{}で指定します
並び替えの実際
データは前のデータをそのまま使用します
今回は、②の基準を「年度」と「売上金額」の２つにします
そして、年度は昇順にて並び替え、売上金額は降順で並び替えます
この場合の②と③の書き方は次の通りです
・②基準➡{1,4}
・③順序➡{1,-1}
では、実際に並び替えを行います
列の並び替え
列の並び替えを行う場合には、４つめの引数を指定します
=SORT(C2:P5,4,-1,TRUE)
4つ目の引数に「TRUE」を指定すると列の並び替えとなります
今回使用するデータは次のデータです
こちらのデータにて、「売上金額」の行を降順で並び替えます
上のGIF画像における数式は次の通りです
=SORT(C2:P5,4,-1,TRUE)
まとめ
今回は、並び替えを行うSORT関数について解説しました
改めて書き方を解説すると、次の通りになります
=SORT(①範囲 , ②基準, ③順序)
特に問題となるのは、並び替え基準が２つになるケースです
この場合には、②と③の両方を波括弧/{を使用して指定します
記事の冒頭でも前述したように、SORT関数の醍醐味は他の関数と組み合わせ易い点です
この組み合わせについては、後日紹介したいと思います
#SPILLエラーについて~新関数に独自のエラー
こんにちは、Excellent仕事術のガッツ鶴岡です
これまで、１つのセルへの入力で複数セルに出力する新関数について、複数の記事を書いてきました
例えば、下のGIF画像はFILTER関数を使った検索付きのリストの例です
C列にFILTER関数を仕込んであり、E列で検索文字「鈴木」が設定されるとA列のリストデータから「鈴木」を含むリストデータを出力します
この新関数では独特のエラー「＃SPILL」が出力される時があります
このエラーの意味について、今日は少し解説したいと思います
見慣れないエラーですが、発生理由はシンプルです
この新関数は複数のセルに出力を行いますが、出力範囲に何らかの入力がある場合にこのエラーは発生します
上の画像で言えば、！の文字が新関数の出力をブロックしてるのです
このブロックしている文字を取り除けば、このエラーは解消されます
尚、ブロックしている文字のフォントが「白色」で人の目では見えない時にも同じエラーが発生するので注意が必要です
では、今回は以上です
最後までブログ記事を見て下さり、大変感謝しております！
新型関数のおすすめ記事～スピルを体験しよう～
新たなに加わった新型関数は使い勝手がとてもいいです
ピボットテーブルの集計データを無理に使い回さなくても、集計が簡単に行えます
１つのセルに関数を入れたら、値があふれでるスピル体験をしていない人はぜひ早めに体験してみましょう！
これまで五月雨式に記事を書きましたが、以下にまとめて紹介します
１．FILETER関数⇒特定の条件のデータを抽出する関数
１セルに１FILTER関数を入力すれば、それだけで複数のデータを抽出してくれます
FILTER関数を使用して、検索付き機能付きののリストを作成します
テーブル機能と連携して効率よくデータを抽出する方法を紹介しています
２．XLOOKUP関数⇒VLOOKUP関数の進化型関数
エクセル分析を行う人で、2軸でのランク付けを楽に行いたい人におすすめです
以下のGIFでは、ⅰ売上とⅱ前期比の２つの軸でランク付けを、自動で行っています
XLOOKUP関数をアニメ付きのエクセルを使用し、XLOOKUP関数が集計されるまでの複数の動きを体感できるようにします
３．SORT関数⇒データを並べ替えて出力する関数です
①SORT関数によるエクセルデータの並び替え~列の並び替えも可
エクセルではこれまで、列方向のフィルターや並び替えが弱い傾向はありました
列フィルターを「列の非表示」を通じて実現する方法はこれまでもありましたが、列方向の並び替え機能はあまり使い勝手はよくありませんでした
今回紹介するSORT関数は、そんなこれまでのエクセルの弱点を補ってくれる関数です
４．UNIQUE関数⇒重複を排除してリスト化する関数
UNIQUE関数を使用してリストを簡単に作成する裏技も紹介します
今回は以上です
ではスピル体験をぜひ楽しんでください！
SORT関数によるエクセルデータの並び替え~列の並び替えも可
前回は１つの関数に対して複数の出力がある関数（ダイナミック関数）として、FILTER関数を紹介しました
今回は、同じようにダイナミック関数のひとつである、SORT関数を紹介します
エクセルで大量のデータを扱う時に、注意しなくてはいけないことの一つが並べ替えです
間違って並び替えると、列間で食い違いが生じて大規模なミスにつながります。SORT関数では並べ替えのキーが複数あっても、正確に且つ、簡単に並べ替えの処理が行えます
加えて、SORT関数では列の並べ替えが行えるのが大きな利点です！
以前、列フィルター機能を紹介しましたが、従来のエクセルでは列の扱いが困難だったのです
では、今回は、SORT関数の概要だけ紹介します！
１．複数の条件による並び替え
＝SORT(並び替え範囲,{列位置,列位置・・・},{並び替え方法,並び替え方法・・・})
並び替え範囲/引数1を指定した後、並び替えの条件になる列の位置/引数2（例：{2,3})を指定し、更に並べ替え方法/引数3を指定します（例：{1,-1}）
並べ替え方法/引数3で指定する１、－１とは、＜１が昇順＞、＜－１が降順＞の意味にになります
ちなみみに、引数2と引数3は指定の仕方がセットになっています
前述の例で言えば、引数2を{2,3}と指定し、引数3を{1,-1}と指定したので
・引数2の1番目（2）は引数3の1番目(1)とセット
・引数3の2番目（3）は引数3の2番目（-1）とセット
となります
実際の例として、以下の表/テーブル2を並べ替え範囲として、地域（列：2、昇順：1）、売上金額（列：3、降順-1）を並び変えてみます
数式は以下の通りとなります
２．列の並び替え
エクセルは行フィルター機能があるのが便利なのですが、業務によっては在庫表のようなクロス表形式で作業することがあります
以前から、列の並び替え機能もあると便利だとは思っていました
列の並び替えを指定する場合には、 前述の「１．複数の条件による並び替え」 で指定した３つの引数に加えて、４つ目の引数【列方向で並び替えを行う】をTRUEで指定する必要があります
ちなみに、「１．複数の条件による並び替え」にて、列位置で指定した 引数2は行位置で指定します
実際の例として、以下の表の3行目を並べ替え条件に設定し、降順にて列の並べ替えを行います
以下が数式です。4番目の引数をTRUEで指定しています
但し、列の並び替えができるといっても元データ自体を並び替えできるわけではありません
利用機会を増やすには、別シートで並び変えた内容を編集して表示するなどの工夫が必要になります
ちなみに、FILTER関数と同じく、関数を入力したセル位置に「＃」をつけることにより、出力データの再利用は行えます
下のGIFでは、SORT関数が入力された位置（F12）に＃をつけることにより、表の右横にSORT関数の出力データを再出力しています
＜まとめ＞
今回はSORT関数により、データの並べ替えを行う方法を解説しました
SORT関数では、｛｝の文字を組み合わせることにより、複数の並べ替え条件に1（昇順）、-1（降順）をセットで指定することができます
後、SORT関数では列の並び替えを指定できるという特徴があります
こちらは使い方を研究して見て、ぜひ日常業務の中で活用してみてください
尚、実際のデータでSORT関数の動きを確認したい方は以下のファイルをダウンロードしてください！
