Excel。AND関数はピンよりも組み合わせることで威力を発揮する関数です。【function:AND】

Excel。AND関数はピンよりも組み合わせることで威力を発揮する関数です。

＜AND関数＞

AND条件とかOR条件でもおなじみのAND関数。

AND関数単体では、なかなか使用することはないかもしれませんね。

Excelで使うとしたら、やっぱり、IF関数とのネストというのが多いですね。

IF関数との組み合わせもバッチリですが、特徴を知っておくことは悪くないので、ちょっと確認しておきましょう。

AND関数の読み方は「アンド」です。

カテゴリーとしては、「論理」に属しています。

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

AND(論理式1,[論理式2],…)

使うことはないかもしれませんが、論理式は255個設定することができます。

設定した引数のすべてが成立（TRUE）したら、「1」を返します。

逆に、設定した引数のうち、一つでも成立していなければ（FALSE）、「0」を返します。

このAND関数。知らなくても算出することはできるものも多いのですが知っていると便利でわかりやすくなる数式は多々あります。

次の場合の数式を確認してみると、一目瞭然です。

E列の判定は、英語・国語・数学のすべてが80以上なら○。そうでなければ、×とします。

E2の数式は、IF関数のみで構成した場合です。

E2の数式は、

=IF(B2>=80,IF(C2>=80,IF(D2>=80,"〇","×"),"×"),"×")

一つずつ、IF関数で質問していくことになりますから、数式は煩雑にあります。

「×」は質問数分必要になってしまいます。

E3の数式は、IF+AND関数のネストで作った場合です。

E3の数式は、

=IF(AND(B3>=80,C3>=80,D3>=80),"○","×")

質問を1回だけで済むことができるので、シンプルな数式となり、わかりやすくなりました。

AND関数は単独では、なかなか使わないかもしれませんが、パートナーとしてはとても重宝する関数なので、知っているといいかもしれませんね。

Excel。VBA。オートフィルターでセルの一部が合致するデータを抽出して別シートにコピーしたい。【Copy to sheet】

Excel。VBA。オートフィルターでセルの一部が合致するデータを抽出して別シートにコピーしたい。

＜Excel VBA＞

大量のデータを読み込んだりした後に、該当するデータを抽出して処理をするということは、よくあります。

作業自体の流れとしては、オートフィルターなどをつかって、データを抽出するわけですね。

ただ、いつも同じ条件で抽出して決まったシートにコピーするという作業は、単純作業になってしまうので、面倒くさい作業になってしまいます。

さらに、抽出条件が、「セルの一部が合致する」ものを抽出したいということで、オートフィルターの抽出設定も面倒となるわけです。

それがこのデータ。

やりたいことは、C列の住所にある「横浜市」のデータを抽出して別のシートにコピーしたいわけです。

オートフィルター機能を使った場合には、「テキストフィルター」にある「指定の値を含む」から、「横浜市」と設定することで抽出してくれます。

「テキストフィルター」の中にはいっての設定ですから、やっぱり面倒ですね。

ところで、「指定の値で始まる」というのは、どのように表現したらいいのでしょうか？

「テキストフィルター」にある、「指定の値で始まる」「指定の値で終わる」「指定の値を含む」は、ワイルドカードを使用することで表現することが出来ます。

横浜市から始まるデータを抽出したいので、ワイルドカードをつかった条件は「横浜市*」とすることで抽出できます。

横浜市以降の複数の文字が含まれていますので「*（アスタリスク）」を使います。

都道府県まで含まれていた場合は、「*横浜市*」とします。

ただ注意しないといけないのは、「中央区」とか「緑区」など、他の地域でも使われていることが想定される場合には、「東京都中央区」とか「横浜市緑区」など範囲を広げて、ワイルドカードを使ってあげる必要があります。

それでは、プログラム文を確認しておきましょう。

Sub ワイルドカードで抽出()

    Range("a1").AutoFilter field:=3, Criteria1:="横浜市*"

    Range("a1").CurrentRegion.Copy Destination:=Worksheets("COPY").Range("a1")

    Range("a1").AutoFilter

End Sub

それでは、実行してみましょう。

このように、「横浜市」から始まるデータを抽出することができました。

それでは、プログラム文を確認しておきましょう。

Range("a1").AutoFilter field:=3, Criteria1:="横浜市*"

AutoFilterでオートフィルターを設定します。

field:=3は、左から3列目ということで、住所の列が対象です。

Criteria1:="横浜市*"　で、横浜市という文字で始まるデータが抽出条件です。

ここで、「*（ワイルドカード）」を使います。

Range("a1").CurrentRegion.Copy Destination:=Worksheets("COPY").Range("a1")

Range("a1").CurrentRegion.Copyで、A1から連なっている範囲（CurrentRegion）をコピーします。

Destination:=Worksheets("COPY").Range("a1")は、COPYシートのA1をコピー先とする意味ですね。

Range("a1").AutoFilter

コピーが終了したので、オートフィルターを残しておいてもしょうがないので、オートフィルターを解除するのがこの行です。

たった3行のプログラム文ですが、作業効率を改善できるかもしれませんので、通常作業も、Excel VBAをつかって、マクロを作ってみるといいかもしれませんね。

今週のFacebookページの投稿 2021/4/12-2021/4/18【one thing】

今週のFacebookページの投稿　2021/4/12-2021/4/18

＜Facebookページ＞

Facebookページで【書いてみた】ワンポイントです。

4月12日

Excel。エラーが表示されるとビックリしますよね。

けど、ちょっと知っていると、ビックリしなくなります。

4月13日

Excel。エラー。

#NULL!　ヌル　正式は、Null Intersection （ナル・インターセクション）です。

セル指定の「:（コロン）」や「,（カンマ）」がない時や、セル範囲に共通部分がないと登場します。

4月14日

Excel。エラー。

#DIV/0!　ディブゼロ　正式は、DIVided by 0（ディヴァイディッド・バイ・ゼロ）です。

0で割り算をしちゃうと、登場しますね。

4月15日

Excel。エラー。

#VALUE!　バリュー　正式は、Wrong Type Value （ローング・タイプ・バリュー）です。

不適切なデータが入っている時に登場しますね。

4月16日

Excel。エラー。

#REF!　レフ　正式は。A REFerence to a cell that does not exit （ア・リファレンス・トゥ・ア・コール・ザット・ダズ・ノット・イグジット）です。

セルが参照できない時に登場します。

4月17日

Excel。エラー。

#NAME?　ネーム　正式は、Unrecognized Name （アンリコグナイズド・ネイム）です。

関数名やセル範囲名などの名前が正しくない時に登場します。

4月18日

Excel。エラー。

#NUM!　ナム　正式は、An Invalid Number （アン・インヴァリッド・ナンバー）です。

数値が大きすぎる。または、小さすぎると登場します。

Excel。1回目と2回目のデータに差はあるのかないのか？そこでt検定のT.TEST関数の出番です。【t-test】

Excel。1回目と2回目のデータに差はあるのかないのか？そこでt検定のT.TEST関数の出番です。

＜T.TEST関数＞

期間をあけて、同じ試験を2回受けてみた結果の表があります。

1回目と2回目の数値ですが、見た感じでは、あまり差を感じません。

データ同士に差があるのかないのか？直感で判断するわけにはいきません。

それぞれの平均値を算出したとしても、数値としての差はありますが、なかなか判断できません。

1件ごとに、1回目よりも2回目の数値がいいというIF関数をつかってみて、2回目の成績がいい件数を数えたところで、2回目の数値が1でも大きければ、計上されてしまいます。

差があるのか、ないのかを調べたい時に登場するのが『t検定』です。

そして、使う関数は、T.TEST関数。

T.TEST関数を使えば、数値として、差があるのか、無いのかを算出することができます。

T.TEST関数は、「t検定」の時に使う関数で、2つの母集団の平均値に差があるかどうかを調べることできる関数です。

1回目と2回目は同じ内容の試験でしたが、2回目は1回目を経験しているので、2回目の方がたぶん、数値はいいのではないかなと、予想を立ててT.TEST関数で算出していきます。

それでは、F1にT.TEST関数の数式を作ってみましょう。

=T.TEST(B2:B11,C2:C11,1,1)

T.TEST関数の引数を確認していきましょう。

配列1は、1回目側なので、B2:B11　と範囲選択します。

配列2は、2回目側なので、C2:C11　と範囲選択します。

検定の指定（尾部）ですが、1の「片側分布」を選択します。

「片側分布」は、いずれかが大きいかどうかを調べる時に使います。

「両側分布」は、差があるかどうかを調べる時に使います。

1回目の経験があっての2回目なので、たぶん、2回目の方が点数は高くなっているのではという想定を立てましたので、1の「片側分布」をつかって算出しようと思います。

検定の種類を1の「対」とします。

1の「対」をつかうのは、同じ人が、1回目と2回目を経験していますので、このような同じ人の評価の場合は、「対」を使います。

なお、2の「等分散の2標本」は、母集団の分散が等しいと仮定される場合で使用します。

3の「非等分散の2標本」は、母集団の分散が等しいと仮定されない場合で使用します。

F1の値は、0.0487。パーセント表示に変更すると、4.88%と算出できました。

「0.05以下ならば、平均値に差があるといえる。」といわれています。

今回は0.05以下なので、見た目似た感じのデータ同士の比較でしたが、差があることが数値的に算出することができました。

Excelには、様々な関数が用意されていますので、実務で使えそうなものを増やしていくといいかもしれませんね。

Excel。19種類の集計ができる。AGGREGATE関数をもっと使ってみよう！【function:AGGREGATE】

Excel。19種類の集計ができる。AGGREGATE関数をもっと使ってみよう！

＜AGGREGATE関数＞

SUBTOTAL関数をグレードアップというか、機能拡張した関数があります。

それが、AGGREGATE関数。

SUBTOTALは「小計」という意味ですね。

それに対して、AGGREGATEは「集計」という意味ですね。

なんで、SUBTOTAL関数があるのに、AGGREGATE関数を使うのと思うかもしれませんが、「小計」と「集計」ということで、似て非なる関数なわけです。

結果的に、19種類の集計を行うことができるというのが、AGGREGATE関数の強みとなったわけです。

AGGREGATE関数の読み方は「アグリゲイト」です。

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

AGGREGATE（集計方法,オプション,参照1,…）

AGGREGATE（集計方法,オプション,配列,[順位])

19種類の集計を行えるということで、2種類の引数を持っているのが特徴です。

集計の種類で使い分けます。

そして、このAGGREGATE関数は、『手入力』で数式を作ることをおススメします。

その理由は、集計方法やオプションに集計の種類ごとなどに番号が割り振られています。

関数挿入ダイアログボックスだと、その番号がわかりません。

また、関数の分類は、「数学・三角」に所属しています。

次の表を使って確認してみましょう。

今回は、H1に、データが非表示になっても連動して、売上高の第3位を算出してみます。

関数を作る前に、G1の「3位」は、ユーザー定義をつかって、表示形式で「位」を表示させています。G1の内容は、3です。

ポイントなのは、上位何番目のデータを算出できる。

LARGE関数は行の非表示には対応していませんし、SUBTOTAL関数では、LARGE関数のような「上位何番目」を算出する方法はもっていません。

なぜならば、SUBTOTAL関数は「小計」を算出する関数ですから。

それでは、H1に入力していきましょう。

最初に設定する引数が、「集計方法」。今回は、LARGEの「14」をつかいます。

次が、「オプション」。

「非表示の行を無視します」をクリックすることで、行が非表示になった時でもリアクションしてくれるようになります。

配列には、売上高のD2:D11を設定し、順位はG1を設定します。

完成した数式は、

=AGGREGATE(14,5,D2:D11,G1)

結果を確認してみましょう。

上位3番目の数値が算出することができました。

では、データを非表示にしてみましょう。

このように、数値が連動して変わったことが確認できました。

用途によって、SUBTOTAL関数とAGGREGATE関数が使い分けられるようになるといいのかもしれませんが、算出結果があっていれば、どっちでもいいというのが、現場レベルというところなんでしょうね。

最後に、集計方法とオプションを紹介しておきます。

『集計方法』

1　AVERAGE　平均

2　COUNT　数値の件数

3　COUNTA　データ件数

4　MAX　最大値

5　MIN　最小値

6　PRODUCT　積

7　STDEV.S　標本標準偏差

8　STDEV.P　母集団標準偏差

9　SUM　合計

10　VAR.S　不偏分散

11　VAR.P　母集団の分散

12　MEDIAN　中央値

13　MODE.SNGL　最頻値

14　LARGE　何番目に大きな値

15　SMALL　何番目の小さな値

16　PERCENTILE.INC　百分位数 0%以上～100%以下

17　QUARTILE.INC　四分位数 0%以上～100%以下

18　PERCENTILE.EXC　百分位数 0%超～100%未満

19　QUARTILE.EXC　四分位数 0%超～100%未満

こんな番号。覚えられないよね。

『オプション』

0　集計値の無視

1　集計値の無視と非表示行の無視

2　集計値の無視とエラーの無視

3　集計値の無視と非表示の無視とエラーの無視

4　何も対象外にしない

5　非表示行の無視

6　エラーの無視

7　非表示行の無視とエラーの無視

Excel。複数列に個別の条件で合致するデータを抽出するにはどうしたらいいの？【Matching data】

Excel。複数列に個別の条件で合致するデータを抽出するにはどうしたらいいの？

＜オートフィルター：AND関数＞

オートフィルター機能をつかうことで、条件に合致したデータを抽出することができますが、ちょっと条件が複雑になると、オートフィルター機能単独だと、太刀打ちできないことがあります。

例えば、次のような表で見てみましょう。

B1:D1の数値は、各月の平均値です。

B1の数式は、

=AVERAGE(B4:B10)

今回やりたいことは、4月～6月までの各月の平均以上のもの、すなわち、全部の月で兵員以上だった店舗はどこの店舗なのか、わかるように抽出したいわけです。

抽出とすれば、オートフィルター機能をつかうわけですが、今回のような複数列を対象にした抽出方法はありません。

また、一つだけのフィールドだけであったとしても、平均以上を抽出することは容易ではありません。

オートフィルター機能の数値フィルターで項目にある「指定の値以上」をつかってみても、オートフィルターオプションダイアログボックスが表示されるので、平均値が算出されているセルをクリックすることはできませんし、B1と入力しても、対応してくれません。

つまり、オートフィルターオプションをつかってみても、抽出することができないわけです。

それが、今回は、複数の列が対象となっているわけですね。

では、このようなことは出来ないのでしょうか？

このような場合には、判定用の列をつくってあげる方法が一番わかりやすいと思います。

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

=AND(B4>=$B$1,C4>=$C$1,D4>=$D$1)

AND関数をつかうことで、引数内の条件が合致していればTRUEを算出することができます。

IF＋AND関数という方法でもいいのですが、条件に合致するかどうかだけわかればいいわけなので、わざわざ、IF+AND関数というネストにしなくてもいいと思いますが、わかりにくい場合には、合致する場合には、「○」とかしておくといいかもしれませんね。

算出できたらば、オートフィル機能をつかって、数式をコピーします。

結果をみてみましょう。

条件が成立していると、「TRUE」を算出して、成立していない場合には「FALSE」を算出しています。

算出することさえできれば、あとは、オートフィルター機能をつかって、「TRUE」のみを抽出してあげれば、いいわけですね。

このように、オートフィルター機能のメニューにない場合は、アイディアによって抽出することができるようになりますので、色々考えてみると、今まで以上に作業効率が改善できるかもしれませんね。

今週のFacebookページの投稿 2021/4/5-2021/4/11【one thing】

今週のFacebookページの投稿　2021/4/5-2021/4/11

＜Facebookページ＞

Facebookページで【書いてみた】ワンポイントです。

4月5日

Excel。シートの全部のセルを選択するのは、全選択ボタンをクリックします。

A列の左側と1行目の上の四角のボタンです。

4月6日

Excel。足し算を使う記号は+。

引き算は-。掛け算は×でなく*。割り算は÷でなく/ですね。

4月7日

Excel。2乗3乗のべき乗の記号は^（キャレット）ですね。

4月8日

Excel。数学・算数のルールと同じで、（）カッコの中が先に計算されます。

｛ ｝とか[ ]は使いません。

4月9日

Excel。文字と文字も足す（合体）することもできます。

&を使うと、簡単にできますね。

4月10日

Excel。Σ。オートSUMボタンをクリックして、範囲を決定するだけで合計を求めることができます。

4月11日

Excel。オートSUMボタンで、合計するときに離れている所を計算したい時は、Ctrlキーでクリックすれば、算出できますね。