Excel。複数列の表をまとめて、重複を除いて並べ替えた表を、つくるにはこの関数たちが便利です。

＜SORT＋UNIQUE＋VSTACK関数＞

氏名が入力されている3つの表があります。

A・C・E列に氏名のデータを、G列の1列にまとめるとしたら、どのようにしますか。

コピペするのも悪くはありません。

ただし、データ量が増えれば増えるほど、面倒です。

データを1列にまとめるならば、VSTACK関数がおすすめです。

G2に次の数式を設定します。

=VSTACK(A2:A5,C2:C5,E2:E5)

VSTACK関数は、列結合する関数なので、引数には、結合したい元の列を範囲選択するだけです。

このVSTACK関数は、スピル機能対応の関数なので、オートフィルで数式をコピーは不要です。

簡単に結合することができましたので、重複したデータを除きたいと思います。

重複データを除くには、データタブにある「重複の削除」をつかいたいところですが、スピル機能によって、ゴーストとして表示されています。

データそのものがあるわけではありません。

そのため、データタブの「重複の削除」はつかえません。

ここは、UNIQUE関数をつかうことで、重複データを除くことができます。

数式にUNIQUE関数を追加してみます。

=UNIQUE(VSTACK(A2:A5,C2:C5,E2:E5),FALSE,FALSE)

これで、重複データを除くことができました。

UNIQUE+VSTACK関数のネストです。

修正した数式を確認します。

最初の引数は、配列。範囲なので、VSTACK関数を設定します。

2つ目の引数は、列の比較。FALSEと設定します。

「一意の行」を返すことができます。

3つ目の引数は、回数指定。

FALSEと設定します。

個別のアイテムをすべて返すことができます。

最後に、カタカナなので、五十音順。

つまり昇順にしたいのですが、スピル機能のゴーストである以上、並べ替えをつかうことはできません。

並べ替えの関数であるSORT関数をつかうことで、対応することができます。

数式をさらに修正します。

G2の数式は、SORT+UNIQUE+VSTACK関数のネストになりました。

=SORT(UNIQUE(VSTACK(A2:A5,C2:C5,E2:E5),FALSE,FALSE),1,1,FALSE)

これで、並べ替えを行うことができました。

追加したSORT関数についても確認しておきましょう。

最初の引数は、配列。

範囲選択なので、UNIQUE関数の数式を設定します。

2つ目の引数は、並べ替えインデックス。

何番目の列を基準にするのかということです。

今回は、1列しかありませんので、1と設定します。

3つ目の引数は、並べ替え順序。

1なら昇順で2なら降順です。

五十音順なので1と設定します。

4つ目の引数は、並べ替え基準。

TRUEならば、列で並べ替えすることができますが、行での並べ替えなので、FALSEと設定します。

このように、最近追加された、関数を組み合わせてスピル機能と合わせてつかうことで、並べ替えや重複対応をした表をつくることができるようになりました。

Excel。複数の表を1つにまとめて、さらに並べ替えも手早く処理したい【Combine tables】

Excel。複数の表を1つにまとめて、さらに並べ替えも手早く処理したい

＜SORT+VSTACK関数＞

3つの地域の売上表があります。

 

A1:D4には、関東地区。

F1:I3には、関西地区。

そして、K1:N3には、九州地区の販売データがあります。

これを、A6から起点とする表に転記し、結合。

要するに、合体させます。

ただ、合体するのではなく、販売金額を降順で並べ替えした表にしたい。

コピペで貼り付けて、その後並べ替えするのでもいいのですが、少し面倒です。

また、Excel VBAでプログラム文をつくるというのも、面倒です。

そこで、今回は、SORT関数とVSTACK関数を組み合わせた数式で対応しようと思います。

A7をクリックします。

次の数式を設定します。

=SORT(VSTACK(A2:D4,F2:I3,K2:N3),4,-1,FALSE)

数式を確定して確認します。

表がまとまっただけでなく、販売金額が降順で並べ替えも終わっています。

では、数式を確認します。

SORT関数は、並べ替えをする関数です。

最初の引数は、「配列」。

範囲です。

この範囲は、合体したデータなので、それぞれの表を合体させるのがVSTACK関数です。

VSTACK関数の説明は後述します。

先に、SORT関数の確認を続けます。

SORT関数の2つ目の引数は、「並べ替えインデックス」。

どの列を並べ替えの条件とするかということです。

左から4番目の販売金額で並べ替えをしますので、4と設定します。

3つ目の引数は、「順序」。

昇順で並べ替えならば、1。

降順で並べ替えならば、-1を設定します。

降順で並べ替えをしたいので-1と設定します。

4つ目の引数は、「方向」。

列方向で並べ替えをするならば、TRUE。

行方向で並べ替えをするならば、FALSEと設定します。

今回は、行方向で並べ替えをしますので、FALSE　と設定します。

これで、並べ替えを行うことができます。

最初の引数で使用するVSTACK関数ですが、これは、合体したいデータを設定するだけなので、見出し行をのぞいた、それぞれの地域

関東のA2:D4

関西のF2:I3

九州のK2:N3

を設定するだけです。

VSTACK関数は、指定した範囲を結合する関数です。

なお、このVSTACK関数は、便利な関数ですが、Microsoft365など、比較的新しいバージョンに登録されていますので、ご注意ください。

Excel。文字列の最頻値は、どのようにして求めたらいいのでしょうか。【Mode】

Excel。文字列の最頻値は、どのようにして求めたらいいのでしょうか。

＜UNIQUE関数,SORT関数,COUNTIF関数＞

分析の一つのデータに「最頻値」というのがあります。

最頻値は、データ内で一番多くあるデータです。

その最頻値を求めるには、MODE.SNGL関数というのが用意されています。

では、次のデータで、一番多い都道府県名を求めてみましょう。

 D2に設定した数式は、

=MODE.SNGL(B2:B16)

ところが、#N/Aというエラーが表示されてしまいました。

原因は、どこにあるのでしょうか。

MODE.SNGL関数の引数を確認してみると、原因がわかります。

数値1・数値2…となっています。

つまりMODE.SNGL関数は数値ならば、最頻値を求めることができるのですが、都道府県名という文字列では、最頻値を求めることができません。

ピボットテーブルをつかうのもいいのですが、今回は関数で対応していきます。

D1をクリックして、次の数式を設定します。

=UNIQUE(B2:B16)

UNIQUE関数は、データから重複を除いた、一意のデータを抽出することができる関数です。

まずは、重複を除いた一意のデータを抽出します。

UNIQUE関数は、スピル機能対応の関数なので、D3以降は、ゴーストが発生します。

よって、オートフィルで数式をコピーする必要はありません。

続いて、件数を求めます。

単一条件の件数を求めるので、COUNTIF関数をつかいます。

E2をクリックします。

E2に設定する数式は、

=COUNTIF(B2:B16,D2#)

スピル機能対応の設定にしましたので、ゴーストが発生します。

引数の最初の範囲は、B2:B16

2つ目の引数は、検索条件。

D2#とします。

「#」は、範囲内の1件という意味です。

これで、最頻値を求めることができました。

ただ、できることならば、件数が多い順にしたいわけですが、一つの表ではないので、件数を降順にするわけにはいきません。

そこで、SORT関数をつかって、新たに表を作ります。

=SORT(D2:E7,2,-1,FALSE)

これで、件数が降順のリストをつくることができました。

最頻値は、千葉県であることが、よりわかりやすくなりました。

このSORT関数もスピル機能対応の関数なので、オートフィルで数式をコピーする必要はありません。

引数も確認しておきましょう。

最初の引数は、配列。D2:E7を設定します。

2つ目の引数は、「並べ替えインデックス」。

どの列で並べ替えるのかということなので、2列目ですから、2と設定します。

3つ目の引数は、「並べ替え順序」。

降順にしたいので、「-1」で設定します。

4つ目の引数は、「並べ替え基準」。

行で並べ替えをしますので、FALSEで設定します。

新しく追加された関数を組み合わせてつかうことで、文字列の最頻値を求めることができました。

今回は関数という条件をつけましたが、ピボットテーブルをつかえば、もっと手早く求めることができます。

Excel。組み合わせが重複しないデータを、取り出したいけど、どうしたらいいの。【overlapping】

Excel。組み合わせが重複しないデータを、取り出したいけど、どうしたらいいの。

＜SORT＋UNIQUE関数＞

重複データを取り出す方法は色々あります。

ただ、組み合わせが重複しないようにデータを取り出すには、どうしたらいいのでしょうか。

A1:C11に表があります。

商品名だけで重複をのぞくと、色鉛筆とポールペンの2つだけで抽出されます。

ただ、今回は、商品名と色を組み合わせで重複しないデータを抽出したいわけです。

A1:C11の元データはそのままにしておき、該E2当するデータを別の場所に抽出しますので、ここは、UNIQUE関数をつかうことにしてみます。

UNIQUE関数は、一意のデータを取り出すことができる関数です。

この関数は、組み合わせにも対応しています。

では、E2にUNIQUE関数をつかった数式を設定します。

E2に設定した数式は、

=UNIQUE(B2:C11)

これで、商品名と色の組み合わせが重複しないリストを作ることができました。

リストを抽出、作成することはできたのですが、並びがバラバラです。

そこで、SORT関数を加えることで、商品名ごとにまとめたリストにすることができます。

E2の数式を次のように修正します。

=SORT(UNIQUE(B2:C11),1,1,FALSE)

これで、商品名別で並べることができました。

SORT関数の引数を確認しておきます。

最初の関数は、配列。範囲です。

今回はUNIQUE関数の抽出結果です。

2つ目の並べ替えインデックスは、並べ替えの基準になる列です。

左から何列目なのかという設定をします。

今回は、商品名ごとなので、1と設定します。

3つ目の並べ替え順序は、昇順・降順設定です。

昇順でも降順でも構いませんが、昇順の1で設定しました。

4つ目の並べ替え基準は、列なのか行なのか、どちらが対象なのかという設定です。

行が対象になりますので、FALSEで設定します。

Excel。1行おきにデータを並べ替えもして手早く抽出するにはどうしたらいい。【extract】

Excel。1行おきにデータを並べ替えもして手早く抽出するにはどうしたらいい。

＜SORT＋FILTER関数＞

2行1組のデータから、1行おきにデータを抽出したいのですが、抽出後、並べ替えをするなら、抽出した時に、並べ替えが終わっていたら、作業効率がいいわけですね。

次の表をつかって、説明します。

それぞれの店舗のデータは、販売数のデータと、売上高のデータで構成されています。

売上高だけのデータを抽出して、さらに、6月の売上高を降順にしたいとします。

オートフィルター機能をつかったとしても、ちょっと面倒な作業なわけですね。

ところが、SORT関数とFILTER関数を組み合わせてつかうことで、手早く抽出して並べ替えもおこなうことができます。

必要な見出しをA11:E11に設定しておきます。

A12に次の数式を設定します。

=SORT(FILTER(A2:E9,B2:B9="売上高"),5,-1,FALSE)

これで、売上高のデータで6月のデータの降順で抽出することができました。

そして、この数式は、オートフィル機能をつかわなくても、スピル機能によって、自動的に数式が拡張されます。

では、数式を確認していきましょう。

SORT関数は、並べ替えを行う関数です。

最初の引数は、「配列」。

つまり範囲選択ですね。

ここにFILTER関数をネストしてます。

FILTER関数の説明はあとに回します。

2番目の引数は「並べ替えインデックス」。

左から何列目を並べ替えの対象としますかという設定です。

6月で並べ替えをしたいわけですから、左から5つ目なので、「5」

3番目の引数は「並べ替えの順序」。

「-1」を設定することで降順と指示できます。

ちなみに「1」だと昇順です。

最後の引数は「並べ替えの基準」。

「FALSE」の「行で並べ替え」を設定します。

「TRUE」にすると、「列で並べ替え」を設定できます。

「配列」で設定したFILTER関数も確認しておきます。

最初の引数は、「配列」。

範囲選択ですね。

「A2:E9」を設定します。

2番目の引数は、「含む」。

これが条件に該当します。

「B2:B9="売上高"」とすることで、B2:B9で売上高が対象にすることができるというわけですね。

FILTER関数と色々な関数を組み合わせて使ってみることで、意外な方法が見つかるかもしれませんね。

Excelの様々な関数の読み方や引数などを紹介。今回は、SLN関数～SORTBY関数です。【dictionary】

Excelの様々な関数の読み方や引数などを紹介。今回は、SLN関数～SORTBY関数です。

＜Excel関数辞典：VOL.73＞

今回は、SLN関数～SORTBY関数までをご紹介しております。

SLN関数

読み方： エスエルエヌ

読み方： ストレートライン

分類： 財務 

SLN(取得価額,残存価額,耐用年数)

減価償却費を定額表で算出します　

Straight LiNe depreciationの略

SLOPE関数

読み方： スロープ　　

分類： 統計 

SLOPE(既知のy,既知のx)

回帰直線の傾きを算出する　

SMALL関数

読み方： スモール　　

分類： 統計 

SMALL(配列,順位)

指定した○番目に小さい値を算出します　

SORT関数

読み方： ソート　　

分類： 検索/行列 

SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])

範囲または配列を並べ替えます　

SORTBY関数

読み方： ソートバイ　　

分類： 検索/行列 

SORTBY(配列,基準配列1,[並べ替え順序1],…)

範囲または配列を、対応する範囲または配列の値に基づいて並べ替えます　

Excel。並べ替えしたデータを抽出するにはSORT関数を使うのが便利です。【SORT】

Excel。並べ替えしたデータを抽出するにはSORT関数を使うのが便利です。

＜SORT関数＞

テーブル機能を追加した表を「並べ替え」するだけなら、いつものように並べ替えボタンを使うとか、昇順や降順といったボタンを使えばいいわけです。

ただ、並べ替えを実施した後に、そのデータを抽出というかコピーするとしたら、一度で出来る方が、作業効率的に、わざわざコピー&ペーストしなくても、いいわけですね。

それができるのが、SORT関数です。

次の表を使って説明します。

A1:D11までの範囲はテーブルになっていて、テーブル名を「売上表2月」と設定しています。

このテーブルのD列の売上高フィールドを降順としたデータを抽出したいわけです。

そこで、F2に次の数式を設定します。

=SORT(売上表2月,4,-1)

たったこれだけで、OKです。スピル機能があるので、オートフィルで数式をコピーする必要もありません。

とても便利なSORT関数なのですが、日付型データが含まれている場合、表示形式が解除されてシリアル値に戻ってしまうため、あとで、表示形式を再度設定する必要があります。

あと、SORT関数の引数も確認しておきましょう。

SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])

なお、「並べ替え順序」ですが、昇順は「1」で降順は「0」ではなく「-1」です。

Excel。元データはそのままで、手早く別シートにコピーして並べ替えもしたい【SORT】

Excel。元データはそのままで、手早く別シートにコピーして並べ替えもしたい

＜SORT関数＞

Excelで並べ替えをする場合、昇順降順のボタンや、並べ替えボタン。

あるいは、オートフィルターをつかっての並べ替えなど、様々な方法があります。

並べ替えをしたデータを別シートに転記したい場合、どの方法でも、元データを並べ替えしてからでないと、転記することはできません。

どのようにしたら、手早くおこなうことができるのでしょうか？

例えば、次のデータ。

F列の合計を降順にした状態で別シートに転記（コピー）したいわけですね。

今回のデータは、連番のNOフィールドがあるので、並べ替えをして、処理をした後でも、元に戻せますが、NOフィールドのようなデータが無い場合は、並べ替えてしまうと、元に戻すことが難しいわけです。

なので、今回紹介する方法を知っていると手早く処理を行うことができます。

その方法とは、SORT関数をつかいます。

準備として、転機先のシートに見出し行をコピーしておきます。

A2をアクティブにしておいて、SORT関数をつくっていきます。

A2に設定する数式は、

=SORT(成績シート!A2:F6,6,-1,FALSE)

数式を確定すると、スピル機能によって、自動的に、数式がコピーされるので、合計フィールドが降順のデータで表示されていることが確認できます。

数式を確認しておきましょう。

最初の引数は、配列。

まぁ、対象範囲ですね。

今回は、データがあるシートの、A2:F6が対象です。

2番目の引数は、

並べ替えインデックス。

並べ替えを行いたい列番号です。

今回は、合計フィールドなので、配列（範囲）の左側から、「6」番目にあるので「6」。

3番目の引数は、並べ替え順序。

昇順なのか降順なのかを設定することが出来ます。

昇順なら「1」。降順なら「-1」。

最後の引数は、

並べ替え基準。

列で並べ替えをするのか、行で並べ替えをするのかを設定することができます。

列ならば「TRUE」、行ならば「FALSE」と設定します。

引数の設定もわかりやすい関数ですので、色々試してみるといいかもしれませんね。

Excel。SORT関数は、列の並び替えもできるのです！複数条件ならSORTBY関数の登場です。【Sort】

Excel。SORT関数は、列の並び替えもできるのです！複数条件ならSORTBY関数の登場です。

＜SORT関数・SORTBY関数＞

Office365 InsiderのExcelに新しく加わった【SORT関数】というのがあります。

Office365 Insiderなので、Excel2016などには、ありませんが、ちょっとした時に、便利な関数なので、ご紹介していきます。

SORT関数ということからわかるように、並び替えを行うことができる関数です。

まずは、動きを確認しておきましょう。
次の表があります。

注意点というか、通常の並び替えと異なり、関数、つまり数式なので、現在のデータをそのまま並び替えるわけではありません。

あと、見出し行は除いて範囲選択する必要があります。

見出し行を含めると当然データ行とミックスされてしまいます。

SORT関数は、
SORT(配列,並び替えインデックス,並び替え順序,並び替え基準)
という引数を持っています。

なので、SORT関数は、ダイアログボックスを表示するよりも、手入力するほうがわかりやすいので、手入力で設定するといいように思います。

今回は、売上高を降順で並び替えを行うことにします。

E2にSORT関数を設定していきます。

=SORT(A2:C6,3,-1,FALSE)
と設定したら、Office365 InsiderのExcelの新機能である「スピル」によって、オートフィルを使うことなく、必要な範囲に数式が反映されます。

では、数式を確認していきましょう。

=SORT(A2:C6,3,-1,FALSE)

配列は、A2:C6。見出し行を除いて、データ行を選択します。仮に最終行が合計行だった場合は、合計行は除きます。

並び替えインデックスは、3。
配列で選択した範囲の左から何列目を基準として並び替えを行うのかを設定します。

並び替えの順序には、「-1」。「1」が昇順で「-1」が降順です。
今回は、降順で並び替えを行うので、「-1」。

並び替え基準には、「FALSE」を設定します。
「FALSE」は行を対象にした並び替えを行います。
「TRUE」は列を対象にした並び替えを行います。

このように、並び替えを行うことができる関数が登場しました。

そして、このSORT関数は、行だけでなく列の並び替えも簡単に行うことができます。

次の表を用意しました。

【列の並び替えも簡単にできるSORT関数】
見出し列は範囲選択から除外してデータのみを範囲選択することになります。

B12をクリックして、次のように数式を設定します。

=SORT(B9:F10,2,-1,TRUE)
列の場合は、並び替え基準を「TRUE」に設定すればいいだけです。

あとは、スピルによって、自動的に範囲が設定されます。

今まで列の並び替えを行うことは、大変な作業でしたが、SORT関数を使うことで、簡単に列で並び替えを行うことができました。

最後に、複数条件で並び替えを行う場合はどうしたらいいのでしょうか？

【複数条件ならばSORTBY関数】
複数条件で並び替えをするには、SORTBY関数を使います。

基本的に、SORT関数と使い方は似ています。

次の表があります。

F16をクリックして、SORTBY関数ダイアログボックスを表示して次のように設定していきます。

配列には、A16:D20。見出し行を除いたデータをすべて範囲選択
基準配列1は、第一条件で並び替えをしたい範囲を選択しますので、D16:D20
並び替え順序1は、「1」ならば昇順、「-1」ならば降順で並び替えをおこなうことができます。

今回は、昇順にしたいので、「1」で設定します。

基準配列2は、第二条件の並び替えを範囲選択しますので、C16:C20。
並び替え順序2は、金額の高い順にしたいので、「-1」の降順で設定します。

F16の数式は、
=SORTBY(A16:D20,D16:D20,1,C16:C20,-1)

実行して確認してみましょう。

複数条件で並び替えを行うことができました。

Office365 Insiderには、色々新しく追加された関数がありますので、知識をプラスしていくと、さらに色々できるかもしれませんね。