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。文字列の最頻値は、どのようにして求めたらいいのでしょうか。【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。列方向の表から、重複したデータを除いたリストを手早くつくりたい【Column overlap】

Excel。列方向の表から、重複したデータを除いたリストを手早くつくりたい

＜UNIQUE関数＞

行方向のデータならば、データタブの「重複の削除」をつかうことで、重複を除いたデータだけをつくることは容易です。

ただ、列方向には対応しておりません。

そこで、UNIQUE関数をつかうことで、列方向であっても、行方向であっても、重複を削除したリストを手早くつくることができます。

2行目に担当者名があるデータを用意しました。

重複しているので、重複を除いたリストをつくります。

B4をクリックして、次の数式をつくります。

=UNIQUE(B2:K2,TRUE,FALSE)

スピル機能により、数式が自動拡張します。

これで、列方向の重複を除いたリストをつくることができました。

では、引数を確認しておきましょう。

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

範囲ですね。

なので、B2:K2と設定します。

2つ目の引数は、「列の比較」。

TRUEにすれば、「一意の列」で、FALSEならば「一意の行」。

今回は、列方向の表なので、TRUEを設定します。

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

TRUEは、「1回だけ出現するアイテムだけを返す」

FALSEは、「個別のアイテムをすべて返す」

重複しているデータも含めて表示したいので、FALSEを設定します。

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

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

＜Excel関数辞典：VOL.86＞

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

UNICHAR関数

読み方： ユニコードキャラクター　　

UNICHAR(数値)

数値で指定したUnicode番号の文字に変換します　

UNICODE関数

読み方： ユニコード　　

UNICODE(文字列)

文字列の最初の文字のUnicode番号を返す　

UNIQUE関数

読み方： ユニーク　

UNIQUE(配列,[列の比較],[回数指定])

範囲または配列から一意の値を返す　

UPPER関数

読み方： アッパー　　

UPPER(文字列)

英字を大文字に変換する　

Excel。行内で重複しているデータを手早く削除するにはどうしたらいいの【Remove duplicates】

Excel。行内で重複しているデータを手早く削除するにはどうしたらいいの

＜UNIQUE関数＞

列内の重複データを削除するには、データタブにある「重複の削除」を使えば、手早く重複したデータを除くことができます。

では、行内の重複データを削除するには、どうしたらいいのでしょうか。

データタブにある「重複の削除」は、列方向にしか使うことができません。

そこで、UNIQUE関数をつかうことで、手早く行内の重複データを除くことができます。

B3に設定した数式は、

=UNIQUE(B1:I1,TRUE,FALSE)

スピル機能によって、オートフィルで数式をコピーしなくても、数式を設定した範囲が拡張していきます。

UNIQUE関数の引数を確認しておきましょう。

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

範囲なので、B1:I1と設定します。

2つ目の引数は、「列の比較」です。

TRUEを選択すれば、一意の列を返します。

つまり、行内のデータが対象ということになります。

今回は行内のデータが重複しているのかを確認したいわけですから、「TRUE」で設定します。

最後の引数は、「回数の指定」です。

TRUEは、「1回だけ出現するアイテムを返す」、FALSEは「個別のアイテムをすべて返す」となっています。

TRUEの「1回だけ出現するアイテムを返す」という意味は、重複しているデータは、表示しない。

ということで、完全に除外することを意味しています。

今回は、重複している2つ目以降のデータを除きたいので、「FALSE」を選択します。

これで、重複を除いた行内のデータを抽出することができます。

このUNIQUE関数は設定次第で、データを抽出することができる関数なので、確認してみると、面白い使い方が見つかるかもしれませんね。

Excel。テーブルで、重複データを除いたリストをつくるには、UNIQUE関数が便利です。【overlapping】

Excel。テーブルで、重複データを除いたリストをつくるには、UNIQUE関数が便利です。

＜UNIQUE関数＞

A1:B9には、担当者リストというテーブル名を設定したテーブルがあります。

このB列の担当者から、重複データを除いたリストを、手早く作るとしたらどうしたらいいでしょうか。

データタブにある「重複の削除」ボタンをクリックして、重複を削除するのも悪くありませんが、「重複の削除」は、重複データを削除した状態にしてしまいます。

つまり、元の表を残して、重複データを除いたリストを作る場合には「重複の削除」では、少し面倒な処理を必要とします。

そこで、UNIQUE関数をつかうことで、簡単に重複データを除いたリストをつくることができます。

しかも、スピル機能によって、オートフィルで数式をコピーする必要もありません。

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

=UNIQUE(担当者リスト[担当者],FALSE,FALSE)

これだけで、重複データを除いたリストを作成することができます。

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

最初の引数は、配列です。テーブル名[フィールド名]で設定します。

2つめの引数は、列の比較。

TRUEだと、一意の列を返します。

FALSEだと、一意の行を返します。

今回は、行なので、FALSEと設定します。

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

TRUEだと、1回だけ出現するアイテムを返します。

つまり、重複していないデータのみを抽出します。

FALSEだと、個別のアイテムをすべて返す。

重複データを除いたリストをつくりたいので、FALSEを設定します。

UNIQUE関数は、比較的新しい関数なので、色々試してみるといいかもしれませんね。

Excel。列方向に重複したデータを手早く除いたリストをつくりたい【overlapping】

Excel。列方向に重複したデータを手早く除いたリストをつくりたい

＜UNIQUE関数＞

データベースの機能はExcelにもたくさん用意されています。

データベースの機能の為、あくまでも行というレコードに対して処理できるけど、列というフィールドに対しては対応してくれません。

そのため、応用できないものもあります。

例えば、データタブにある「重複の削除」です。

重複のデータを簡単に除いたデータを抽出することができる機能ですが、列方向には対応しておりません。

次の表をつかって、実行してみます。

実行すると、「重複の削除に関する警告」ダイアログボックスが表示されます。

どちらを選択して、重複の削除ボタンをクリックしても、エラーメッセージが表示されます。

要するに、列方向ではこの「重複の削除」を実行することができないというわけです。

そこで、「UNIQUE関数」をつかうと、手早く、列方向の重複を除いたデータを抽出することができます。

B4にUNIQUE関数をつかった数式を設定します。

=UNIQUE(B2:G2,TRUE,FALSE)

設定後、スピル機能によって、数式がコピー（スピル）されます。

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

最初の引数は、配列。範囲選択のことだと思ってもらえればいいので、B2:G2と設定します。

スピル機能によって、絶対参照は不要です。

2つ目の引数は、列の比較です。

TRUEは一意の列を返す。FALSEは一意の行を返す。

列が対象なので、TRUEと設定しました。

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

TRUEは、「1回だけ出現するアイテムだけを返す」。

FALSEは、「個別のアイテムをすべて返す」。

を設定することができます。

重複していないデータのみを抽出したい場合は、「TRUE」を設定しますが、重複データも含めたデータを抽出したいので、今回は「FALSE」を設定しました。

たったこれだけですが、手早く抽出すうることができる効果抜群の関数ですね。

このUNIQUE関数は、「重複の削除」と同じように、行方向に対しても対応してくれます。

ちょっとした注意点として、比較的新しい関数なので、お使いのExcelには無いかもしれません。

Excel。UNIQUE関数が登場。重複してない一意のリストを作るのに大活躍【UNIQUE】

Excel。UNIQUE関数が登場。重複してない一意のリストを作るのに大活躍

＜UNIQUE関数＞

Office365のExcelで、Office insiderの登録をすると、新しい関数が登録されます。
その中に、【UNIQUE関数】があります。

このUNIQUE関数は、重複を除いたリスト（一意のリスト）を作ることが簡単にできる関数です。

今までのExcelでも、データタブの「重複の削除」をつかうことで、重複を除いて、一意にするボタンをありましたが、データをコピーして実行しないと、元のデータが変更されてしまうなど、処理をするのが面倒でした。

そこで、新たに登場した「UNIQUE関数」をつかうことで、簡単に重複を除いたリストを作ることができます。

次のデータを用意しました。

D2を基準として、氏名が入力されているB列から、重複を除いた一意のリストを作っていきます。

D2に次の数式をつくります。

=UNIQUE(B2:B15)

引数には、範囲選択するだけでOKです。

Office365のExcelで、Office insiderには、『スピル』という機能が追加されたことにより、オートフィルを使用しなくても、数式がコピー（スピル）されました。

このように、とても簡単に算出することができます。

ただ、残念なのは、今回の氏名のようにできれば、五十音順にしたいと考えた場合、スピルの影響で、昇順のボタンをはじめ並び替えを行うことができません。

また、Office365のExcelで、Office insiderで、新しく追加された「SORT関数」を使いたいところですが、フリガナ情報では並び替えを行ってくれません。

当然、数式の算出結果のため、D列の文字には、入力文字のデータは含まれていません。

そこで、どうしても、五十音順にしたい場合には、元のデータを五十音順に並び替えを行っておいてから、UNIQUE関数をつかうといいかと思います。

なんかイマイチのような関数に思えますが、重複の削除ボタンは列内の重複を見つけて処理をしていましたが、UNIQUE関数は、行にも対応しています。

次の表を用意しました。

このような、行内で重複していない一意のリストを作るのは、今までのExcelではなかなか大変でした。

それでは、G4に次の数式を作っていきます。

=UNIQUE(G2:T2,TRUE)

列になっていても、重複を除いた一意のリストを作ることができました。

あまり、氏名というケースは少ないかもしれませんが、アイディアによっては、使えそうですね。

なお、UNIQUE関数は、
UNIQUE(配列,列の比較,回数指定)となっています。

今回は、列の比較を「TRUE」にすることで、列同士を比較してくれます。

引数の回数指定をTRUEにすると、一度しか登場しないものだけをピックアップすることもできます。

B2を「桜木」に変更して、D10に次の数式を作ります。

=UNIQUE(B2:B15,,TRUE)

今回紹介した、UNIQUE関数ですが、いずれは、通常の関数として登場してくると思われますが、興味のある方は、確認してみると、色々使えるかもしれませんね。