主にWindows・ソフトウェア・スマートフォン関連の操作・設定やCD/DVDレーベルを掲載しています、不定期の更新ですが是非、感想、コメント、評価をお願いします。 ブロとも・相互リンク募集中




UNIQUE関数、一意の値(ユニーク化)

UNIQUE関数は、範囲または配列から一意な値を返します。
範囲または配列から重複を削除して一意化した配列を返します。

UNIQUE関数スピルで登場した新しい関数です。

UNIQUE関数の書式

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

配列

必須です。
並べ替え処理する元データを指定します。
セル範囲または配列です。

列の比較

省略可能。
TRUE : 一意の列を返す

FALSE : 一意の行を返す

省略するとFALSE(一意の行)になります。
そもそも引数名がとても分かりづらいです。
これは、

横(列)方向のデータを一意にする。

縦(行)方向のデータを一意にする。

ということです。
つまり、通常の縦に連なるデータに対して使う時は、
FALSE : 一意の行を返す
これを使う事になります。
従って、ほとんどの場合この引数は省略して使えば良いです。

回数指定

省略可能。

TRUE : 1回だけ出現するアイテムを返す

FALSE : 個別のアイテムをすべて返す

省略するとFALSE(個別のアイテム)になります。

この引数も分かりづらいです。
FALSEが普通の一意化、重複を無くす処理になります。
従って、通常はこの引数は省略して使います。

TRUEの1回だけ出現が分かりづらいのですが、
元データに1回しか出現しないデータのみを
対象とするという事です。
つまり、
そもそも一意になっているデータのみ出力する、
重複しているデータは出力しないということです。
実際の使い道については、かなり限られると思います。


◆ワークシートでの一意化

ワークシート上で、データを一意化する方法として

・COUNTIF関数で重複を判定して削除

・ピボットテーブルで重複を削除

・重複の削除で重複を削除

・フィルタの詳細設定(フィルターオプションの設定)で重複を削除

どれも一長一短はあります。



UNIQUE関数の使用例

1列だけの単純な例

=UNIQUE(A1:A15)

image211.jpg
A列だけで一意化しています。
単純にA列のデータで重複を無くした状態で出力されます。


◆複数列の場合

=UNIQUE(A1:B15)

image221.jpg
A列とB列で一意化しています。
A列とB列を合わせたデータで重複を無くした状態で出力されます。
出力の仕方は違いますが、これは以下と同じことになります。

=UNIQUE(A1:A15&B1:B15)

image231.jpg


◆横(列)方向の一意化

=UNIQUE(A1:O2,TRUE)

image241.jpg
引数の「列の比較」という言葉がわかりづらいですが、
単純に、横(列)方向なら第2引数をTRUEにすれば
良いと覚えれば良いでしょう。


◆1回だけ出現するアイテム

=UNIQUE(A1:A15,FALSE,TRUE)

image251.jpg
A列で一回しか出てこないデータのみ出力されます。
複数列でも理屈は同じです。

=UNIQUE(A1:B15,FALSE,TRUE)

image261.jpg
例えば、A社A商品やA社C商品は2回出てきているので出力されていません。


UNIQUE関数の応用例

全自動の集計表を作成します。
データ行数、集計項目値の増減にも対応できます。

image55.jpg
元表(A:E)のデータ変更に全自動で対応しています。
データ行数はもちろん、取引先や商品の増減にも対応できます。

G2=SORT(SORT(UNIQUE(FILTER(FILTER(A:E,ISNUMBER(C:C)),{1,1,0,0,0})),2),1)

以下の順に処理するように数式を組み立てています。

・内側のFILTER関数でデータのある行だけに絞り、
 外側のFILTER関数で2列に減らしています。
FILTER関数は、定義した条件に基づいてデータ範囲を
フィルター処理した結果を返します。FILTER関数は
スピルで登場した新しい関数です。最後の方では、
表示する列を選択する方法も掲載しています。
FILTER関数の書式 =FILTER(配列,含む,[空の場合]) 配列 必須です。

UNIQUE関数で一意化

・内側のSORT関数で商品順、外側SORT関数で
 取引先順に並べ替えています。
SORT関数は、範囲または配列の内容を並べ替えます。
SORTBY関数は、範囲または配列を対応する範囲または
配列の値に基づいて並べ替えます。
SORT関数とSORTBY関数は範囲を並べ替える関数ですが、
同じこともできますが、
れぞれの関数でなければできないこともあります。

I2=SUMIFS(D:D,$A:$A,OFFSET($G1,1,0,COUNTA($G:$G)-1)
,$B:$B,OFFSET($H1,1,0,COUNTA($H:$H)-1))
J2=SUMIFS(E:E,$A:$A,OFFSET($G1,1,0,COUNTA($G:$G)-1),
$B:$B,OFFSET($H1,1,0,COUNTA($H:$H)-1))
行数自動対応でスピルさせている為、数式が複雑になっています。

SUMIFS関数を集計結果の行数変更に
自動対応させた上でスピルさせています。
SUMIFS関数は、SUMIF関数の条件が複数指定できるようになったものです。
COUNTIFS関数は、COUNTIF関数の条件が
複数指定できるようになったものです。
範囲の中で、指定した条件を満たすセルの個数を数えます。

難解に見えるのはOFFSET関数があるからです。
基準のセルまたはセル範囲から指定された行数と列数だけ
シフトした位置にある、指定の高さと幅のセル範囲の参照を返します。
つまり、基準セルから、指定数だけ移動したセルを起点として、
指定の大きさのセル範囲を取得出来ます。
OFFSET関数の書式 
OFFSET(基準,行数,列数,[高さ],[幅]) 基準

OFFSET関数を固定のセル範囲に置き換えてしまえば、
単なるスピルさせているSUMIFS関数だけになります。


これは、以下のようにテーブル+ピボットテーブルで
作成するものとほぼ同じことになります

※データ行数に自動対応させるためにテーブルにしています。

image110.jpg

どちらが良いという事ではなく、適宜使い分けすれば良いでしょう。





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



関連記事















(この一行は、各記事の最後に固定表示するサンプルです。テンプレートを編集して削除もしくは非表示にしてください。)

Excel2021の新関数 SORT関数、SORTBY関数

SORT関数は、範囲または配列の内容を並べ替えます。
SORTBY関数は、範囲または配列を対応する
範囲または配列の値に基づいて並べ替えます。

SORT関数SORTBY関数は範囲を並べ替える関数ですが、
同じこともできますが、れぞれの関数でなければ
できないこともあります。

SORT関数SORTBY関数スピルで登場した新しい関数です。


SORT関数の書式

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

配列

必須です。
並べ替え処理する元データを指定します。
セル範囲または配列です。

並べ替えインデックス

省略可能。
並べ替えのキーを指定します。
何列目(または何行目)を基準に並べ替えるかを
数値で指定します。
範囲の左端列が1(または上端行が1)です。
この引数を省略すると、範囲の1列目(1行目)を
基準として並べ替えます。
この引数は1つしか指定できません、つまり並べ替えに
指定できるキーは1つだけです。

上記説明における何列目または何行目の違い


並べ替え順序

省略可能。
並べ替えの順序、「昇順」か「降順」を指定します。

1 : 昇順

-1 : 降順

この引数を省略すると、1(昇順)で並べ替えられます。


並べ替え基準

省略可能。
並べ替えを行方向に行うか列方向に行うかを指定します。

TRUE : 列で並べ替え ・・・ 横に並べ替える

FALSE : 行で並べ替え ・・・ 縦に並べ替える

この引数を省略するとFALSE(行で並べ替え)で並べ替えられます。
TRUE,FALSEは、1,0で指定しても構いません。

行・列の表現が分かりづらいので、間違えないようにしてください。
一般的なデータ(横に項目、縦にデータ)では、
FALSE(行で並べ替え)になります。


SORTBY関数の書式

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

配列

必須です。
並べ替え処理する元データを指定します。
セル範囲または配列です。

基準配列

必須です。
並べ替えのキーとして使うセル範囲または配列を指定します。
SORT関数では元データ範囲の列位置(行位置)を
数値で指定しましたが、SORTBY関数では
セル範囲または配列で指定します。

この基準配列の配列の向きによって、並べ替えの向きが決定されます。

行方向(縦方向)の配列を指定した場合は行方向(縦方向)に並べ替えます。

列方向(横方向)の配列を指定した場合は列方向(横方向)に並べ替えます。
この基準配列は、元データ範囲内にある必要はありません。
元データ範囲とは全く別のセル範囲または配列を指定できます。

行方向(縦方向)で並べ替える場合は、元データの行数と一致した縦の配列を指定します。

列方向(横方向)で並べ替える場合は、元データの列数と一致した横の配列を指定します


並べ替え順序

省略可能。
並べ替えの順序、「昇順」か「降順」を指定します。

1 : 昇順

-1 : 降順

この引数を省略すると、1(昇順)で並べ替えられます。


SORTBY関数のまとめ

1image35.jpg


SORT関数、SORTBY関数と、ワークシートの並べ替えの違い

ワークシートの並べ替えは、指定範囲のデータを並べ替えてしまいますが、
SORT関数SORTBY関数は、指定範囲のデータを並べ替えて
別のセル範囲に出力します。
ワークシートの並べ替えでは、元表のデータをそのままにしておきたい
場合は表範囲をコピーしてから行う必要があります。


しかし、これらの関数を使用すれば、このコピーが不必要になります。
ただし関数では、ワークシートの並べ替えにある以下の指定はできません。

・先頭行を見出しとして使用する。

・大文字と小文字を区別する

・ふりがなを使う

つまり、SORT関数、SORTBY関数では、
・全てデータ行として扱われる

・大文字と小文字を区別しない

・ふりがなを使わない

したがって、このような並べ替えが必要な場合は、
今まで通りワークシートで並べ替えを行う必要があります。


最も単純な並べ替え

並べ替え対象内の指定列で並べ替えるだけならSORT関数が簡単です。


SORT関数

=SORT(A2:E51)

2image32.jpg
※「適当に作った個人情報」です。

1列目(A列)をキーとして行方向に昇順で並べ替えしています。
[並べ替えインデックス],[並べ替え順序],[並べ替え基準]
これらを全て省略しています。
省略せずに指定するなら、
=SORT(A2:E51,1,1,FALSE)


SORTBY関数

=SORTBY(A2:E51,A2:A51)

1列目(A列)をキーとして行方向に昇順で並べ替えしています。
[並べ替え順序]
これを省略しています。
省略せずに指定するなら、

=SORTBY(A2:E51,A2:A51,1)

引数において範囲を2度指定しなければならず、このような単純な
並べ替えであればSORT関数を使ったほうが良いでしょう。


複数キーでの並べ替え

キーを連結した作業列を作成しキーとすれば様々な
並べ替えに対応できますが、以下では作業列を作成せずに
並べ替える場合の例になります。
※作業列を使って複数キーを結合して並べ替える事自体は
決して悪いものではありません。


SORT関数で複数キー並べ替え

SORT関数単独ではできませんが、
SORT関数をネストすれば可能です。
都道府県(E列) > 性別(D列) で並べ替えます。

=SORT(SORT(A2:E51,4),5)

3image33.jpg

注意点としては、関数ネストの内側から順に実行される点になります。
上記では、性別で並べ替えた後に都道府県で並べ替えられます。
つまり、優先度の高い並べ替えを外側の関数で指定します。

場合によっては、このような使い方をすることもあるかもしれませんが、
複数キーの場合はSORTBY関数が便利でしょう。


SORTBY関数で複数キー並べ替え

都道府県(E列) > 性別(D列) で並べ替えます。

=SORTBY(A2:E51,E2:E51,1,D2:D51,1)

並べ替え順序の1は省略できるので、以下でも同じです。
=SORTBY(A2:E51,E2:E51,,D2:D51,)
ただし、最後の,カンマは省略できないので注意してください。


列方向(横方向)で並べ替え

SORT関数で列方向(横方向)並べ替え
3行目の単価で昇順に並べ替えています。

=SORT(B1:F3,3,1,TRUE)

4image40.jpg

SORTBY関数で列方向(横方向)並べ替え

=SORTBY(B1:F3,B3:F3)

5image41.jpg

範囲を2度指定しなければならないので、このような場合は
SORT関数を使ったほうが良いでしょう。
しかし、この下で説明しているように、並べ替え範囲外を
指定できるSORTBY関数ならではの使い方があります。


並べ替え範囲(配列)以外の基準で並べ替える

SORT関数は、並べ替え範囲しか並べ替えのキーが指定できませんが、
SORTBY関数は、並べ替え範囲以外をセル範囲または配列で指定できます。


列方向(横方向)を指定順序で並べ替え

=SORTBY(A2:E51,{2,5,1,4,3})

6image42.jpg

配列定数として、{2,5,1,4,3}これで指定していますが、
もちろんセル範囲でも指定できます。

=SORTBY(A3:E52,A1:E1)

7image44.jpg

さらに出力先の項目名を利用して、MATCH関数と
組み合わせるとより便利に使えます。

=SORTBY(A2:E51,MATCH(A1:E1,G1:K1,0))

8image45.jpg


ランダムに並べ替え

=SORTBY(A2:F51,RANDARRAY(ROWS(A2:A51)))

9image46.jpg

RANDARRAY関数で行数分の乱数を作り、その乱数を基に並べ替えています。


VLOOKUPの結果で並べ替え

都道府県コードを別表から取得し、取得したコードで並べ替えます。

=SORTBY(A2:F51,VLOOKUP(F2:F51,O:P,2,FALSE))

10image49.jpg

このように、他の関数(特にVLOOKUP系)で取得した順番で
並べ替える方法は、今後は頻繁に使われるようになるかもしれません。
ただし、並べ替え後の結果を見てもそれが
正しいかの確認がかなり困難になります。
可能な限り作業列を作成し、その列に他の関数の結果を出力して、
それを並べ替えのキーとして使うことをお勧めします。


列全体を範囲指定する場合

スピル関数で一番困るのが、列全体を指定しづらい事です。
単純に列全体を指定すると、SORT関数および
SORTBY関数はエラーとなってしまいます。

11image50.jpg

そこで、FILTER関数でデータが空白以外(<>"")の行だけに絞ってみると、

=SORT(FILTER(A:E,A:A<>""))

12image52.jpg

一見良さそうですが、見出し行まで並べ替えに入ってしまいます。
上記の場合の解決方法としては、データの入っている行の
判定方法を工夫することで対応できます。


13image54.jpg

データには、大抵はこのように数値しか入っていない
列が存在するはずなので、この手法は幅広く使えるはずです。
もちろん数値とは限らず、データと見出しを
区別することができる判定ならどのような条件でも構いません。
ただし、
AND関数で複数条件を指定するとエラーとなってしまうので、
単一条件だけで済むようにしてください。



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


関連記事















(この一行は、各記事の最後に固定表示するサンプルです。テンプレートを編集して削除もしくは非表示にしてください。)

Excel スピルとは

2019年にOffice365のExcelに実装された
革新的な機能としてスピルがあります。
数式を入力したセルから結果があふれて
隣接したセルにも出力されるのがスピルです。
今までは数式を入れたセルにしか
結果を出せませんでしたが、スピルでは
隣接するセルにまで結果が表示されます。
この革新的なスピルとは何か、
その概要について説明します。

以下で、「従来」または「旧」と呼んでいるのは
スピルしないエクセルを指しています。


スピルって?

スピルを説明するには、まず配列についての
説明が必要になります。
まずは、従来のエクセルでの「配列数式」と
共通部分の参照」について説明します。


従来のエクセルの挙動


複数の値を返す数式(配列数式)は結果を表示する
全てのセルを選択して、

Ctrl + Shift + Enterで数式を一括入力することで、
{=数式}のように{}で囲まれた配列数式となります。

この配列数式CSEと略されてます。
例えば、
B1:B3を選択し、=A1:A3を
Ctrl + Shift + Enterで入力すると、
数式は{=A1:A3}となり、B1:B3はA1:A3を
参照するようになります。

1image5.jpg



暗黙的に共通部分を参照する機能があります。
これは、同一行または同一列の
値のみが返される機能です。


B1セルに=A1:A3と入力すると、A1セルが参照されます。
B2セルに=A1:A3と入力すると、A2セルが参照されます。
B3セルに=A1:A3と入力すると、A3セルが参照されます。

2image6.jpg

A2セルに=A1:C1と入力すると、A1セルが参照されます。
B2セルに=A1:C1と入力すると、B1セルが参照されます。
C2セルに=A1:C1と入力すると、C1セルが参照されます。

3image7.jpg


スピルとは

スピル(spill)とは、こぼれる、あふれる、
と言うような意味です。
数式を入力したセルから結果があふれて
隣接したセルにも出力されるのがスピルです。

スピルは動的配列数式とも呼ばれます。
スピルは数式の結果の複数の値が
隣接するセルに自動的に出力されます。

複数の値を返す配列数式を該当セル範囲の
先頭(左上セル)に入力すると、結果の
複数の値が入力したセルからこぼれ出して
隣接するセルに出力されます。

従来なら配列数式としてCSE(Ctrl+Shift+Enter)で入力
しなければならなかったものが、

数式をEnter入力することで、該当セル範囲
(スピルする範囲)に結果が出力されます。
スピルする範囲は、数式の結果データの
縦横(行列)の大きさによって決まります。
したがって、数式が変更されれはスピル範囲も
変わりますし、数式が参照している
セル値によっても大きさが動的に変化します。


スピルによって新しく追加された関数


関数名 説明
FILTER
フィルターは定義した条件に基づいたデータ範囲です。
SORT
範囲または配列の内容を並べ替えます。
SORTBY
範囲または配列の内容を、対応する範囲または
配列の値に基づいて並べ替えます。
UNIQUE
一覧表または範囲内から重複データを削除した一覧を返します。
RANDARRAY 0から1までのランダムな数値の配列を返します。
SEQUENCE 1、2、3、4など、配列内の連続した数値の一覧を生成します。
XLOOKUP 範囲または配列を検索し、見つかった最初の一致に
対応する項目を返します。
一致が存在しない場合、XLOOKUP は最も近い
(概算) 一致を返すことができます。
XMATCH 配列またはセル範囲内の項目の相対的な位置を返します。


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



関連記事















(この一行は、各記事の最後に固定表示するサンプルです。テンプレートを編集して削除もしくは非表示にしてください。)

Excel2021の新関数 FILTER関数 "範囲をフィルター処理"

FILTER関数は、定義した条件に基づいてデータ範囲を
フィルター処理した結果を返します。
FILTER関数スピルで登場した新しい関数です。

FILTER関数の書式

=FILTER(配列,含む,[空の場合])

配列

必須です。
フィルター処理する元データを指定します。
セル範囲または配列です。

含む

必須です。
フィルター条件を指定します。
元データの配列の縦または横の大きさと同じ
真偽値(TRUE,FALSE)の1次元配列を指定します。


空の場合

省略可能。
フィルター結果が空の場合に表示する値を指定します。
フィルター結果が空の場合にこの引数を
省略していると、#CALC!となります。


FILTER関数使用例のサンプルデータ

使い道の広い関数です。
配列を意識して使いこなすと、かなり便利なことができます。
以下のFILTER関数使用例で使う表は以下になります。

image10.jpg

例題にした個人情報です。50件用意しました。

以下では、上表を元にFILTER関数を使用した場合を例示しています。


FILTER関数の基本

数値でフィルター

30歳未満でフィルター

=FILTER(A2:E51,C2:C51<30)

image11.jpg


文字でフィルター

"東京都"でフィルター

=FILTER(A2:E51,E2:E51="東京都")

image12.jpg


フィルター結果が0件の場合

フィルター結果が0件の場合に、引数「空の場合」を
省略していると、#CALC!のエラーとなります。

image13.jpg

引数「空の場合」を適宜指定します。

image14.jpg


空白セルを0ではなく空白にする場合

エクセル関数全般での問題ですが、
空白セルを参照すると0になってしまいます。

=FILTER(A2:E51,B2:B51="女")

image15.jpg

空白セルを空白にしたい場合、いろいろな方法がありますが、
&""を付け加える方法が最も簡単だと思います。

=FILTER(A2:E51,B2:B51="女")&""

image16.jpg


複数条件のフィルター

AND条件

"女" AND "東京都"でフィルター

=FILTER(A2:E51,(B2:B51="女")*(E2:E51="東京都"))


AND条件は、*演算子を使います。

引数「含む」には、真偽値(TRUE,FALSE)の配列を指定するので、
個々の真偽値の掛け算がAND条件になります。
AND関数は使えません。


OR条件

"女" OR "東京都"でフィルター

=FILTER(A2:E51,(B2:B51="女")+(E2:E51="東京都"))

OR条件は、+演算子を使います。
引数「含む」には、真偽値(TRUE,FALSE)の配列を指定するので、
個々の真偽値の足し算がAND条件になります。
OR関数は使えません。


関数を使ってフィルター

四則演算

ここだけ少し違うデータ例になります。
C列とD列の足し算の結果でフィルターする場合。

=FILTER(A2:E51,C2:C51+D2:D51>100)

論理式として評価できる式であればどんな
四則演算でも構いません。


文字列関数

LEFT,MID,RIGHT等の文字列関数が使えます。
"京都府"と"大阪府"でフィルターしています。

=FILTER(A2:E51,RIGHT(E2:E51)="府")


日付・時刻関数

YEAR,MONTH,DAY等の日付・時刻関数が使えます。
10月生まれでフィルターしています。

=FILTER(A2:E51,MONTH(D2:D51)=10)

ただし、FILTER出力結果の日付の表示形式は
自動では設定されません。
適宜表示形式を設定してください。

image17.jpg


関数使用時の注意

引数「含む」の計算結果が1行でもエラーを
含んでいる場合、FILTER関数全体がエラーとなります。

=FILTER(A2:E51,FIND("京",E2:E51)>0)

image18.jpg

FIND関数は、検索値が無い場合はエラーとなるため、
上記ではFILTER関数全体がエラーとなっています。
このような場合は、IFERROR関数でくるみます。

=FILTER(A2:E51,IFERROR(FIND("京",E2:E51),0)>0)

image19.jpg

したがって、
見出し列を範囲に含めたり列全体で
指定したりする場合は、IFERROR関数が必要になる場合が多くなります。


横(列)でフィルター

ここまで、縦のデータによってフィルターしましたが、
横(列)のデータによってフィルターすることもできます。

=FILTER(A1:E51,A1:E1="年齢")

image20.jpg

ある特定の文字列を含む見出し列で
フィルターするといった使い方ができます。
しかし実務的には、この機能だけを単発で
使う事は少ないように思います。


表示する列を選択する

引数「含む」は真偽値(TRUE,FALSE)の配列を指定するものです。
1次元の配列であれば、縦・横どちらでも受け付けてくれます。

配列リテラルの書き方

縦の配列

{}の中に;セミコロンで区切って各要素を入れます。

{1;2;3}

1
2
3

横の配列

{}の中に,カンマで区切って各要素を入れます。


{1,2,3}


123

そこで、ここまでの例で示した「含む」のほとんどは

縦の配列であることを理解してください。


E2:E51="東京都"


これは、


{FALSE;FALSE;…;TRUE,FALSE;…}

行数分の縦の配列になります、

条件に合致した行はTRUE、それ以外はFALSEです。

そして、TRUEは1、FALSEは0として代用できます。

そこで、「含む」に直接この配列を指定してみましょう。

「含む」の行数は元配列と同じ行数に

しなければならないので、ここでは5行だけにしました。


=FILTER(A2:E6,{1;0;1;0;1})


image21.jpg


「含む」に指定した配列の1(TRUE)の行だけが出力されました。

とはいえ、このような使い方をすることはまずないでしょう。

では、横の配列を指定してみましょう


=FILTER(A2:E6,{1,0,1,0,1})


image22.jpg


見事に元範囲の1,3,5列だけが出力されました。

これは使えそうです。


FILTER関数の結果をFILTERすれば、

条件で絞り込んだ後に必要な列だけに絞り込めます。


FILTER関数をネストして表示する列を選択


=FILTER(FILTER(A2:E51,C2:C51<30),{1,0,1,0,0})


image23.jpg


外側のFILTER関数に指定した配列通りの列のみ出力されました。

これを使えば、元表から欲しい列だけにすることができます。

注意点としては、「含む」に指定する

配列の大きさは、元表の列数に必ず合わせることです。


表示する列の選択を自動化する


配列{1,0,1,0,0}を自動的に作成すれば良いだけです。

FILTER関数を入れる上の行に事前に見出し文字列を入れておくことで、

配列{1,0,1,0,0}を見出し文字列から自動生成します。

=COUNTIF(G1:H1,A1:E1)

この数式はスピルして、


1 0 1 0 0

このようになりますので、これをそのま引数に指定できます。


=FILTER(FILTER(A2:E51,C2:C51<30),COUNTIF(G1:H1,A1:E1))


image24.jpg



FILTER関数の結果を他の関数で使う


FILTER関数が返すものは配列です。

したがって、配列を受け入れる関数の引数として使う事が出来ます。


"東京都"の"男"の平均年齢を出して見ましょう。

もちろん、AVERAGEIFSで簡単に求められます。

=AVERAGEIFS(C2:C51,B2:B51,"男",E2:E51,"東京都")

あくまで、ここまでの総復習としてやってみましょう。


=AVERAGE(FILTER(FILTER(A2:E51,(B2:B51="男")*

(E2:E51="東京都")),COUNTIF(C1,A1:E1)))


この数式は解読してみてください。

ここまでに説明してきたことを組み合わせて

使っているだけです。

この数式が読めれば、FILTER関数はほぼ確実に

理解できたと言えるでしょう。


スピル導入によって、エクセルの使い方が

大きく変わろうとしています。

FILTER関数は、その中核となる関数と言えると思います。


にほんブログ村 PC家電ブログ Windowsへ
 
関連記事















(この一行は、各記事の最後に固定表示するサンプルです。テンプレートを編集して削除もしくは非表示にしてください。)

Excel2021の新関数 LET関数 “変数”を使って可読性と計算速度アップ

image1.png
オートコンプリートにも対応

米Microsoftは11月16日(現地時間)、「Excel」の新しい関数
「LET」を一般公開したと発表した。
今年3月から“Office Insider”でテストされていた機能です。

「LET」関数は計算結果に名前を割り当て、
それを再利用できるようにするもの。
同じような処理を何度も記述して数式が
長く読みにくくなるのを避けられるほか、
計算量の多い処理を何度も繰り返す必要がなくなるため
パフォーマンスが向上する。
プログラミング言語の“変数”によく似た概念ですが、
それが利用できる範囲
(スコープ)のはその数式の中だけです。

「LET」関数の基本的な構文は、以下の通りです。


= LET (name1, name_value1, name2, name_value2, ……calculation)

・name:変数の名前。文字で始まる必要があり、

 数式の出力や範囲構文と競合してはならない

・name_value:“name”に割り当てる値

・calculation:変数を利用した計算。

 最後の引数はこれでなければならない


変数は最大126個が利用でき、その数だけ

名前と値のペアを引数に指定する。

最後の引数は変数を利用した

計算式(calculation)である必要があるため、

引数の数はかならず奇数個になる(最短の引数は3)。


たとえば以下の売り上げデータから社員名が

“Fred”のものだけを抽出したい場合は以下のようになります。


image2.jpg


LET を利用しない場合:

=IF(ISBLANK(FILTER(A2:D8,A2:A8="Fred")),"-",
FILTER(A2:D8,A2:A8="Fred"))

LETを利用した場合:

=LET(filterCriteria,"Fred”,filteredRange,FILTER
(A2:D8,A2:A8=filterCriteria),
IF(ISBLANK(filteredRange),"-",filteredRange))

“Fred”や“FILTER(A2:D8,A2:A8=……)”を繰り返し記述する

代わりにわかりやすい変数名を付けられるので、

あとでフィルタリングの条件を変更する

必要が生じても容易に式を再利用できる。

計算速度も「LET」関数

利用した方が2倍速いという。


正式版の「LET」関数では、“name1”“name2”などで宣言した

変数を“calculation”引数でオートコンプリートできるようになった。

データ参照の競合を防ぐため

変数の名前に“.”(ドット)を利用できなくしたり、

関数名のローカライゼーションが廃止されたのも、

Office Insider”のフィードバックを取り入れた成果です。

どの言語の「Excel」でも

「LET」という名前で関数を利用できます。


image3.gif


「LET」関数は「Office 365」または「Microsoft 365」の「Excel

Excel 2021」で利用可能。Webを含めたすべての

プラットフォームの“Current”チャネルで利用できます。


にほんブログ村 PC家電ブログ Windowsへ 

関連記事















(この一行は、各記事の最後に固定表示するサンプルです。テンプレートを編集して削除もしくは非表示にしてください。)
(この一行は、各ページ下部に固定表示するサンプルです。テンプレートを編集して削除もしくは非表示にしてください。)
検索フォーム
カウンター



閲覧者数
現在の閲覧者数:
ジャンルランキング
[ジャンルランキング]
コンピュータ
47位
ジャンルランキングを見る>>

[サブジャンルランキング]
ウィンドウズ
17位
サブジャンルランキングを見る>>
RSSリンクの表示



カテゴリ
タグ一覧

 Windows   Windows10   Windows11 

 パソコン   インターネット   HTML 

 自作パソコン   コマンドプロント 

 Microsoft   アプリケーション   iPhone 

 Android   Office2021   Excel 

 Excel2021 

 AfterEffects 

FC2リンク
  • 赤髪船長のCUSTOMラベル
  • ベジベジの自作BD・DVDラベル 超(スーパー)

  • ブロとも一覧

    You talkin' to me ? 2nd_新たなる驚異を求めて

    LEVEL1 FX-BLOG

    梅月夜の夢物語り

    株式情報市場
    QRコード
    QR
    ブログランキング




    にほんブログ村 スマホ・携帯ブログへ にほんブログ村 スマホ・携帯ブログ iPhoneへ にほんブログ村 IT技術ブログへ にほんブログ村 IT技術ブログ IT技術情報へ にほんブログ村 地域生活(街) 沖縄ブログへ にほんブログ村 地域生活(街) 沖縄ブログ 沖縄市・コザ情報へ

    ブログ王ランキング

    相互リンクとランキングプラス

    PC・ソフトランキング













    タイムライン
    セキュリティーソフト
    ウイルスバスター公式トレンドマイクロ・オンラインショップ

    ノートンストア

    マカフィー・ストア

    ZERO ウイルスセキュリティ

    ソフトウェア
    Acronis True Image

    サイバーリンク Power2Go 12

    アフェリエイト











    Powered By FC2ブログ