Excel。結合列は不要！「苗字＋名前」の重複を条件付き書式で行全体塗りつぶす【Duplication】

Excel。結合列は不要！「苗字＋名前」の重複を条件付き書式で行全体塗りつぶす

＜条件付き書式+COUNTIFS関数＞

名簿を扱っていると、

「苗字と名前の組み合わせで重複していないか確認したい」

という場面がよくあります。

でも、わざわざ「苗字＋名前」を結合した列を作るのは面倒だし、初心者の方には説明もしづらいですよね。

そこで今回は、結合列を作らずに、苗字＋名前の重複を条件付き書式で行全体を塗りつぶすことで確認する方法を紹介します。

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

B列に名字、C列に名前が入力されています。

フルネームで重複しているかどうか条件付き書式をつかって判断します。

A2:D8を範囲選択してホームタブの条件付き書式にある「新しいルール」をクリックします。

新しいルールダイアログボックスが表示されます。

数式を使用して、書式設定するセルを決定　を選択します。

条件式のボックスに、

=COUNTIFS($B$2:$B$8, $B2, $C$2:$C$8, $C2)>1

と数式を設定します。

書式ボタンをクリックして、塗りつぶしたい色を設定します。

最後に、OKボタンをクリックします。

 

これで、重複している名字+名前のデータを対象にした、行全体を塗りつぶすことができました。

では、設定した数式を確認しておきましょう。

=COUNTIFS($B$2:$B$8, $B2, $C$2:$C$8, $C2)>1

重複というのは、その列内に複数。

つまり1件より大きい場合は重複というわけです。

件数ということから、COUNT系の関数を使うとよさそうです。

そして、名字と名前という複数列が対象ということで、条件が複数になります。

よってCOUNTIFS関数をつかいます。

検索条件範囲には、絶対参照を忘れに設定します。

検索値は、$B2のように、列固定の複合参照にすることで、行全体を対象にすることができます。

COUNTIFS関数の結果が「>1」。

つまり、重複というのは、2件以上あるわけです。

これで、条件付き書式で複数列に対応した重複チェックの方法をご紹介しました。

Excel。条件付きで空白セルを数えたい場合、どのようにしたらいいの。【blank cell】

Excel。条件付きで空白セルを数えたい場合、どのようにしたらいいの。

＜COUNTBLANK関数・COUNTIFS関数＞

計算結果が空白でも、元から空白でも、セルが空白にみえるなら、その空白セルの件数を算出するのが、COUNTBLANK関数です。

ところが、COUNTBLANK関数は条件がついてしまうと、対応することができません。

なぜかというと、COUNTBLANK関数の引数は、「範囲」だけしかないからです。

そして、この引数の「範囲」は、連続した範囲でないといけません。

つまり「,（カンマ）」で範囲を追加することもできません。

では、次のような表の場合、どのようにしたら、条件付きの空白セルの件数を算出することができるのでしょうか。

 

B列の上期下期が「上期」でC列の提出確認が「空白（空欄）」のセルの件数を算出するには、COUNTBLANK関数がつかえません。

そこで、COUNTIFS関数をつかえば、算出することができます。

E2に設定した数式は、

=COUNTIFS(B2:B9,"上期",C2:C9,"")

COUNTIFS関数は、複数条件で件数を算出することができる関数です。

なので、空白セルに条件を付けた場合は、COUNTIFS関数で対応することができます。

Excel。AND関数の欠点。ワイルドカードがつかえないので、どうしたらいいの。【trouble】

Excel。AND関数の欠点。ワイルドカードがつかえないので、どうしたらいいの。

＜AND関数・IF+COUNTIFS関数＞

AかつBという条件をつくることができる「AND関数」ですが、欠点があります。

それが、ワイルドカードをつかった条件式には対応してくれないということです。

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

判定しないのは、所属はAからはじまり、かつ、住所には横浜市が含まれるデータなら、「○」を表示して、そうでなければ、「空欄（空白）」としたい。

AND関数をつかってみたところ、思っているように算出されません。

C2に設定した数式は、

=IF(AND(A2="A*",B2="*横浜市*"),"○","")

AND関数とワイルドカードの組み合わせは、うまくいかないようです。

AND関数で、ワイルドカードをつかわない数式にすれば、算出することはできます。

ワイルドカードを使わない数式は、

=IF(AND(MID(A2,1,1)="A",MID(B2,5,3)="横浜市"),"○","")

ただ、MID関数をつかうので、可読性が下がってしまいます。

そこで、COUNTIFS関数をつかうことで、対応することができます。

C2の数式を次のように設定して、オートフィルで数式をコピーします。

=IF(COUNTIFS(A2,"A*",B2,"*横浜市*"),"○","")

COUNTIFS関数は、ワイルドカードをつかった条件をつかうことができます。

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

IF関数の引数であるCOUNTIFS関数から説明します。

COUNTIFS関数の最初の引数が、「検索条件範囲1」です。A2を設定します。

2番目の引数は、「検索条件1」。ここに「”A*”」とワイルドカードをつかっています。

「A*」とすれば、「Aで始まる」という意味です。

3番目以降は条件範囲と、条件の繰り返しです。

B2のあたいに、「*横浜市*」なのかを確認する条件式を設定します。

「*横浜市*」はB2に「横浜市という文字が含まれる」という意味です。

この2つの条件が合致したら「TRUE」、合致しない場合は「FALSE」と判定結果がでます。

IF関数をつかってTRUEならば「”○”」と表示します。

Excel。条件付き順位を算出したい時は、どのようにしたらいいのでしょうか？【RANK】

Excel。条件付き順位を算出したい時は、どのようにしたらいいのでしょうか？

＜COUNTIFS関数＞

順位を算出したい時には、「RANK.EQ関数」など順位を算出する関数を使えば、算出することができます。

ただし、条件付きで順位を算出したい場合には、「RANK.EQ関数」では対応することができません。

では、どのようにしたら条件付きの順位を算出することができるのでしょうか？

条件付きの順位を算出したいときには、COUNTIFS関数をつかい、数式を少し修正すれば算出することができます。

D2には、次の数式を設定して、オートフィルで数式をコピーしています。

=COUNTIFS($B$2:$B$8,B2,$C$2:$C$8,">"&C2)+1

なぜ、COUNTIFS関数なのかというと、順位というのは、自分自身の値よりも大きな値がない。

要するに0件だったならば、1番大きな数値ということがわかります。

10件中10位。

つまり最下位の場合は、自分自身の値よりも大きな数値が9件あるわけです。

順位は件数を算出することで順位を算出することができるというわけです。

なので、条件付きで件数を算出することができる「COUNTIFS関数」をつかうことで、条件付き順位を算出できるというわけです。

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

1番目の引数は、2番目の引数である、「検索条件1」が含まれている範囲です。

今回は検索条件1をクラスにしますので、

1番目の引数には、「$B$2:$B$8」。

オートフィルで数式をコピーするので、絶対参照を忘れずにセットしておきます。

2番目の引数は、「B2」。

3番目と4番目の引数は、1番目と2番目と同じ関係性です。

3番目の引数は、「$C$2:$C$8」。ポイントを範囲選択しています。

こちらもオートフィルで数式をコピーしますので、絶対参照を忘れないようにします。

4番目の引数は、「">"&C2」と設定することで、自分自身の値よりも大きな値という条件をつくることができます。

そして、注意しないといけないのは、算出した値に「+1」することです。

自分自身よりも大きな値が無い場合は、その値が1番なわけですが、値がないということは、算出結果は「0」となるわけです。

0位というのは変ですから、「+1」する必要があるというわけですね。

意外と件数算出系の関数は、アレンジ次第で重宝しますので、色々試してみるといいかもしれませんね。

Excel。COUNTIFS関数は、複数の条件を満たす件数を算出します【COUNTIFS】

Excel。COUNTIFS関数は、複数の条件を満たす件数を算出します

＜関数辞典：COUNTIFS関数＞

COUNTIFS関数

読み方：カウントイフズ

読み方：カウントイフエス

分類： 統計 

COUNTIFS(検索条件範囲1,検索条件1,…)

複数の条件を満たす件数を算出します

Excel。複数条件による件数を算出するならば、COUNTIFS関数をつかうと便利です。【COUNTIFS】

Excel。複数条件による件数を算出するならば、COUNTIFS関数をつかうと便利です。

＜COUNTIFS関数＞

数える関数は、Excelに色々登録されていますが、今回は、複数条件で件数を算出する時に使用すると便利な「COUNTIFS関数」です。

使いたかたを確認しておきましょう。

次の表を用意しました。

F2の条件は、地区が東日本で、売上高が1000以上の件数を算出させています。

F3の条件は、地区が西日本で、売上高が1000以上の件数を算出させています。

では、F2の数式を確認しておきましょう。

=COUNTIFS($B$2:$B$9,E2,$C$2:$C$9,">=1000")

最初の引数は、「検索条件範囲1」で、地区であるB2:B9までが対象になります。

オートフィルで数式をコピーしますので、絶対参照を設定してあります。

2番目の引数である、「検索条件1」は、E2に東日本という文字が用意されているので、E2を設定します。

複数条件なので、検索条件範囲と検索条件を繰り返し設定していきます。

3番目の引数の「検索条件範囲2」は、売上高なので、C2:C9です。

オートフィルで数式をコピーするので、絶対参照を設定してあります。

4番目の引数の「検索条件2」は、1000以上という条件です。

比較演算子を使って設定します。「”>=1000”」と設定しますが、比較演算子と数値全体を「”（ダブルコーテーション）」で囲ってあげます。

これで、算出することができました。

Excel。同じ列内での複数条件をつかって手早く件数を算出したい【Multiple conditions】

Excel。同じ列内での複数条件をつかって手早く件数を算出したい

＜DCOUNT関数＞

複数条件での件数を算出するには、COUNTIFS関数をつかうことで、算出することができますが、複数条件にもルールがあります。

E7には、次の数式を設定しています。

=COUNTIFS(A2:A9,"新宿",A2:A9,"渋谷")

何をやりたいのかというと、A列の店舗名に、新宿と渋谷が何件あるのかを算出したいわけです。

そこで、複数条件で件数を算出したいので、COUNTIFS関数をつかってみたところ、算出結果は「0」。

COUNTIFS関数は、複数条件で検索することはできるのですが、複数列での対応で、一つの列内で複数条件には、対応していないわけです。

要するに、店舗名が「新宿」でかつ、来店客数が「300以上」のような、AND条件には対応していますが、店舗名が「新宿」または「渋谷」といったOR条件には対応していません。

そこで、COUNTIF関数を複数つかい、それぞれの算出結果を加算させる

=COUNTIF(A2:A9,"新宿")+COUNTIF(A2:A9,"渋谷")

という方法で対応することもあります。

ただ、この方法だと、OR条件が多くなった場合は、数式自体の可読性も悪化し、数式のミスを誘発する可能性が高くなります。

何よりも、手早く算出することができません。

そこで、DCOUNT関数をつかうことで、素早く算出することができます。

ただし、DCOUNT関数は、条件のフィールドを用意する必要があります。

E1:E3が条件です。

E7に、DCOUNT関数をつかった数式を設定します。

=DCOUNTA(A1:C9,A1,E1:E3)

これで、算出することができました。

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

DCOUNTA(データベース,フィールド,条件)

データベースは、リストのことです。

「A1:C9」で、見出し行も含む必要があります。

フィールドは、次の条件が含まれている、見出し名です。

今回は、店舗名が対象なので「A1」。

条件は、先程設定した、条件の範囲である、E1:E3を設定します。

ここも見出しが必要になりますので、忘れずに範囲に含めます。

条件が増えたとしても、E3の下に追加するだけで、手早く算出することができます。

オートフィルターとかテーブルにすれば、算出する方法もありますが、ほぼ数式だけで算出することができます。

条件が複雑になった場合や、OR条件になった場合には、DCOUNTA関数など「D」がついたデータベース関数をつかってみると、いいかもしれませんね。

Excel。条件付き空白件数を算出するにはCOUNTBLANK関数が使えません【space】

Excel。条件付き空白件数を算出するにはCOUNTBLANK関数が使えません

＜COUNTIFS関数＞

空白データの件数を算出するには、「COUNTBLANK関数」をつかうと、簡単に算出することができますが、次のようなケースには、対応することができません。

前期と後期に分かれて管理されている表で、前期のデータで、提出列のデータが空白の件数を算出したいのですが、COUNTBLANK関数がつかえません。

よくみると、提出列の空白は、当然、前期の未提出も後期の未提出も混ざっています。

つまり、前期だけをターゲットにしなければなりません。

要するに、対象範囲は一行おきになるわけですね。

E2にCOUNTBLANK関数をつかって、算出してみると、どうなるかを確認してみましょう。

=COUNTBLANK(C2,C4,C6,C8,C10)

COUNTBLANK関数で数式をつくってみたところ、メッセージが表示されました。

メッセージには、「引数が多すぎる」とあります。

COUNTBLANK関数の引数は、「範囲」だけですが、この範囲は、連続した範囲でなければなりません。

つまり、今回のような一行おきの範囲選択には対応していないということです。

よって、COUNTBLANK関数では、条件を付けた算出をすることができないわけです。

なので、一行おきなど、連続した範囲でない場合には、COUNTBLANK関数ではなくて、COUNTIFS関数をつかって、数式を設定していきます。

E2に次のように数式を設定します。

=COUNTIFS(B2:B11,"前期",C2:C11,"")

検索条件範囲1には、B2:B11。

検索条件1には、「前期」

検索条件範囲2には、C2:C11。

検索条件2には、「””」

これで、一行おきなどに対応した空白の件数を算出することができます。

COUNTBLANK関数の引数の範囲は、連続した範囲でなければ、算出することができないので、範囲が複雑になる場合には、別な方法を考えないといけないようですね。

Excel。条件付き順位は発想を変えると簡単に算出することができます。【Conditional ranking】

Excel。条件付き順位は発想を変えると簡単に算出することができます。

＜COUNTIFS関数＞

店舗番号順の売上表があります。

算出したいのは、E列の「地区別順位」です。

順位ということで、使う関数は、「RANK.EQ関数かな？」と思い浮かぶかもしれませんが、残念ながら、RANK.EQ関数は、全体の中で何番目というように、「全体の中」なので、今回のように、地区別順位。

つまり、条件付き順位は、算出することができません。

RANK.EQ関数をつかうとしたら、範囲選択を地区ごとに分けて行って、その範囲内で順位を算出するようになります。

地区ごとにまとまっていれば、まだ範囲選択はしやすいのですが、出店した順に店番が振られている場合などだと、範囲選択は非常に面倒になります。

面倒ということで、わざわざ、Excel VBAでマクロをつくるというのも、なんか割に合いません。

そこで、発想の転換。

そもそも順位というは、自分の数値より大きい数値の件数が何件あるのかがわかれば、算出することができます。

何件あるのか？ということなので、COUNTIFS関数をつかうことで、条件付き順位を算出することができるのです。

では、E2をクリックして、COUNTIFS関数をつかった数式をつくっていきましょう。

E2の数式は、

=COUNTIFS($C$2:$C$10,C2,$D$2:$D$10,">"&D2)+1

数式の説明はあとでするとして、まずは数式を確定して、結果を確認してみましょう。

このように、地区ごとに順位を算出することができました。

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

=COUNTIFS($C$2:$C$10,C2,$D$2:$D$10,">"&D2)+1

検索条件範囲1は、地区の$C$2:$C$10。オートフィル機能で数式をコピーしますので、絶対参照で設定する必要があります。

検索条件1には、B2。

検索条件範囲2は、売上高の$D$2:$D$10。

こちらも絶対参照の設定を忘れずに行っていきましょう。

最後の、検索条件2ですが、「">"&D2」と設定します。

これは、D2:D10の範囲で、D2より大きい値がいくつあるのか確認しています。

さて、数式の最後尾に「+1」がされています。

これはなんなのかというと、「D2より大きい」という条件になっていますので、1番大きい値の場合、自分自身の値よりも大きい値はないわけですから、「0」と算出されてしまいます。

そのために「+1」することで、条件付き順位を算出できるというわけです。

順位をCOUNTIFS関数で算出できることを知っていれば、複数条件での順位を算出するなど、色々な角度から順位を算出することができるようになります。

例えば、全体では、順位が高い方だけど、地区内の順位だと、下の方ということも簡単に算出することができます。

日頃使っている資料などに、プラスしたりするといいかもしれませんね。 

Excel。カテゴリー別の順位を算出するには、RANK.EQ関数では対応できません。【Ranking by category】

Excel。カテゴリー別の順位を算出するには、RANK.EQ関数では対応できません。

＜RANK.EQ関数・COUNTIFS関数＞

次の表を用意しました。

氏名が五十音順になっているリストですが、全体の順位を算出するD列には、RANK.EQ関数を使うことで、順位を算出することが容易にできます。

それでは、全体順位を算出してみましょう。

D2をクリックして、RANK.EQ関数ダイアログボックスを表示します。

数値には、C2。
参照には、数値が含まれている全体なので、$C$2:$C$10
オートフィルで数式をコピーしますので、絶対参照を設定することを忘れないようにしましょう。

順序は、数値がいいものを1位という設定にしますので、0。

それでは、OKボタンをクリックして、オートフィルで数式をコピーして確認してみましょう。

このように、全体順位を算出するには、RANK.EQ関数を使うことで算出できることがわかりました。

なお、D2の数式は、
=RANK.EQ(C2,$C$2:$C$10,0)

では、B列のクラスというカテゴリー別での順位を算出するには、どうしたらいいのでしょうか？

先程確認したRANK.EQ関数には、条件を設定する引数はありませんでした。

また、最近のExcelには、IFS関数やMAXIFS関数など、条件付きで対応できる関数も登場してきましたが、残念ながら、順位を算出する、RANK.EQ関数には、条件付きに対応していません。

そこで、順位を算出場合どのようにしたらいいのか考えてみると、
カテゴリー別で、該当する数値より、大きい値があるのかを確認して、大きい値がないならば、1位ということですよね。

複数条件で、件数を算出するという、考え方なので、使用する関数は『COUNTIFS関数』を使ってみると算出できそうです。

E2をクリックして、COUNTIFS関数ダイアログボックスを表示しましょう。

検索条件範囲1には、$B$2:$B$10
今回、範囲1は、カテゴリー別の判定をさせます。

検索条件1には、B2

検索条件範囲2には、$C$2:$C$10
得点の列です。
範囲1と範囲2ともに、オートフィルで数式をコピーするので、絶対参照を設定忘れないようにしましょう。

最後に、検索条件2には、
">"&C2
比較演算子とセル番地を組み合わせて使用する時には、比較演算子を「”（ダブルコーテーション）」で囲む必要があります。

そして、文字結合の「&」をつかって、セル番地を結合させる必要があります。

“>C2”としてしまうと、>C2という文字を検索してしまうので、当然条件に合致するものはありませんので、注意が必要です。

OKボタンをクリックして、次の数式が出来ていますが、まだ数式としては完成していません。

E2の数式は、
=COUNTIFS($B$2:$B$10,B2,$C$2:$C$10,">"&C2)

このままだと、自分自身よりも大きいデータがあれば数える仕組みですが、自分自身よりも大きいデータがない場合は、「0」になってしまいます。

今回は順位を算出したいので、0位というのは、おかしいので、数式の最後に「+1」をする必要があります。

よって、E2の数式は、
=COUNTIFS($B$2:$B$10,B2,$C$2:$C$10,">"&C2)+1

では、オートフィルで数式をコピーして確認してみましょう。

このように、条件付きで順位算出することができましたね。

Excel。年代別で傾向や特徴をわかりやすくする表にまとめてみよう。度数分布表【Frequency distribution table】

Excel。年代別で傾向や特徴をわかりやすくする表にまとめてみよう。度数分布表

＜COUNTIFS関数・FREQUENCY関数＞

アンケート結果をただ、表にしただけでは、単なる文字データであって、もったいないので、カテゴリー別ごとに集計するとわかりやすくなります。

そこで、今回は、次のような文字データをまとめる作業をしていきます。

50件のデータです。性別・年齢・購入数だけのデータの集まりだけです。

そこで今回は、年代別で何名いるのかを把握していきます。
集計表を用意します。

F列は、年代の区切りの始めの数値を入力しておきます。

G列は、年代の区切りの終わりの数値を入力してありますが、書式形式を使って、数値の前に「～」を表示させています。

"～"G/標準　という表示形式のユーザー定義を設定しています。

H列は、最初はCOUNTIFS関数を使ってケースを紹介します。
I列は、FREQUENCY関数を使ったらどうなるのか、確認するための列です。

H2に10代の人数をCOUNTIFS関数で算出していきますが、引数で何度も、C2:C51を範囲選択する必要があり、煩雑になりますし、面倒なので、C2:C51に名前の定義で名前を設定すると便利です。

今回は、『年齢値』という名前を設定しておきます。

それを踏まえたうえで、数式を作っていきます。

H2をクリックして、COUNTIFS関数ダイアログボックスを表示します。

検索条件範囲1には、「年齢値」
検索条件1には、”>=”&F2
条件で比較演算子とセル番地を組み合わせて使うときには、比較演算子をダブルコーテーションで囲む必要があります。

そして、セル番地を「&」を使って結合します。

検索条件範囲2には、「年齢値」
検索条件2には、”<=”&G2
OKボタンをクリックして、オートフィルを使って数式をコピーしましょう。

H2の数式は、
=COUNTIFS(年齢値,">="&F2,年齢値,"<="&G2)
こんな簡単な数式を使うだけでも、10代が少ないとか、色々見えてきますね。

【COUNTIFS関数よりもFREQUENCY関数が便利】

算出するのに、COUNTIFS関数で十分なのですが、今回のような「度数」を算出するのに、便利な関数があります。

それが、FREQUENCY関数です。

ただ、配列関数で作る必要がありますので、ダイアログボックスで作成すると、面倒なので、今回は、直接数式を入力して作る方が楽かと思います。

最初に、算出したい箇所を範囲選択します。

=FREQUENCY(年齢値,G2:G8)
と入力したら、通常Enterキーで数式を確定しますが、配列関数で算出させるので、Ctrl+Shift+Enterで数式を確定します。

=FREQUENCY(年齢値,G2:G8)　という数式の前後に、配列関数を意味する、「{ }」が付きました。

算出結果をみれば、COUNTIFS関数と同じ結果になっているのが確認できます。

FREQUENCY関数は、度数分布を算出する関数です。

FREQUENCY(データ配列,区分配列)　という2つの引数を設定するだけなので、覚えておくと便利な関数ですね。

ただ、配列関数で算出する点を忘れないようにしましょう。

Excel関数辞典 VOL.14。COUNT関数～COUNTIFS関数

Excel関数辞典　VOL.14。COUNT関数～COUNTIFS関数

＜Excel関数＞

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

今回は、お馴染みの、数える関数ばかりですね。


COUNT関数
カウント
数値の個数を算出
COUNT(値1,値2…)

COUNTA関数
カウントエー
空白セル以外の件数を算出
COUNTA(値1,値2…)

COUNTBLANK関数
カウントブランク
空白セルの件数を算出
COUNTBLANK(範囲)

COUNTIF関数
カウントイフ
1つの条件を満たす件数を算出
COUNTIF(範囲,検索条件)

COUNTIFS関数
カウントイフズ
複数の条件を満たす件数を算出
COUNTIFS(検索範囲1,検索条件1,検索範囲2,検索条件2…)

YandSシステムズのExcel関数一覧表
https://sites.google.com/view/yandsssystems/function?authuser=0

Excel。セルの部分一致が複数条件の時はAND関数が使えない【Partial Match】

Excel。セルの部分一致が複数条件の時はAND関数が使えない

＜IF+COUNTIFS関数＞

新宿店の女性の顧客というような複数条件のデータをわかるようにする場合は、IF+AND関数を使うことで、簡単に算出することができるわけですが、AND関数が意外とオールマイティーではなくて、現場で困ることがあります。

今回は、その例をご紹介していきます。
次の表があります。

例えば、顧客コードの第2レベルが18で、住所は、川崎市のデータには”○”を表示させるようにしたいわけです。

簡単にいかない理由は、セルの一部のデータが含まれているかどうか、ということなわけです。しかも、複数列。

先程の、新宿店の女性のデータということならば、
=IF(AND(A3=”新宿店”,B3=”女性”),”○”,””)
というような数式を作れば、いいわけです。

ところが、セルの一部のデータが複数列で含まれているという場合は、AND関数が使えません。

では、C3に数式をIF+AND関数で作って確認してみましょう。
C3に作成する数式は、
=IF(AND(MID(A3,3,2)=18,MID(B3,5,3)="川崎市"),"○","")

MID関数は、セル内の文字データの何文字目からいくつ目までの文字というように文字データの途中を算出する関数ですね。

では、オートフィルを使って数式をコピーしてみましょう。

何も表示されません。
確認のために、C3の数式をAND関数ではなくて、OR関数に変更してみましょう。

=IF(OR(MID(A3,3,2)=18,MID(B3,5,3)="川崎市"),"○","")
としてみると、算出されていますので、AND関数内の引数にミスはないわけです。

AND関数は、完全一致を求めるので、セル内の一部では使うことができないようです。

【COUNTIFS関数を使う】
そこで、今回のようなケースの場合は、COUNTIFS関数を使うことで解決できます。

D3をクリックして、IF関数ダイアログボックスを表示させます。

論理式に、COUNTIFS関数をネストします。

COUNTIFS関数ダイアログボックスが表示されますので、設定していきます。

検索条件範囲1には、A3。通常COUNTIFS関数は、A3:A10のような範囲選択をしますが、このセルの値がということなので、一つだけのセルを選択します。

検索条件1には、"*18*"
*（アスタリスク）はワイルドカードですね。
前後で挟めば、挟んだ文字列を含むという意味ですね。

検索条件範囲2には、B3。
検索条件2には、"*川崎市*"

IF関数ダイアログボックスに戻ります。

値が真の場合には、”○”
値が偽の場合には、””　空白を設定して、OKボタンをクリックします。

オートフィルを使って数式をコピーしましょう。

D3の数式は、
=IF(COUNTIFS(A3,"*18*",B3,"*川崎市*"),"○","")
となっています。

今回は、条件に合致するものを算出することができました。

今回のように、セル内の一部のデータが複数条件で合致するものを見つけたい場合には、IF+AND関数ではなくて、IF+COUNTIFS関数を使うと解決できます。

Excel。今再びの関数基本。数える関数全員集合！COUNT関数～AGGREGATE関数【COUNT】

Excel。今再びの関数基本。数える関数全員集合！COUNT関数～AGGREGATE関数

＜COUNT関数～AGGREGATE関数＞

現場でお馴染みの数える関数ですが、色々ありまして、まとめて一度確認しておきましょう。
次のような表があります。

【数値を数えるならCOUNT関数】

参加人数などを数えたい場合、もしNoなどの数値の列があれば、とても簡単に算出することができるのが、COUNT関数です。

C9に算出してみましょう。オートSUMボタンの▼をクリックして、数値の個数を選び範囲選択して確定させます。

ココでポイントになるのは、A列のように数値でなければなりません。

今回は、A2:A7を範囲選択します。

C9の数式は、
=COUNT(A2:A7)
とても簡単に算出することができますが、問題なのは、「数値の個数」であること。
つまり、B列のように『文字』は数えてくれないわけです。

【空白以外を数えるのがCOUNTA関数】

B列のような文字も数えたい場合は、COUNTA関数を使います。COUNTA関数は、文字も数値も対象になります。

テキストなどには、空白以外が対象になるように書かれているものが多いですが、計算式の算出結果が空白の場合は、数える対象になってしまいます。

C10に算出していきます。
COUNT関数と同じように、途中まで作っていきます。

残念ながら、0件と算出されました。

数式は、
=COUNT(B2:B7)
と出来上がっていますので、「T」と「(」の間に「A」と入力すればCOUNTA関数に変わり算出することができます。

C10の数式は、
=COUNTA(B2:B7)
これで、算出することができました。

【条件が一つの場合はCOUNTIF関数】

得点が80点以上の件数を知りたい場合は、COUNT関数やCOUNTA関数では対応することができません。

そこで条件付きで対応している関数が、COUNTIF関数とCOUNTIFS関数です。

今回は、80点以上と条件が一つだけ（単数条件）なので、COUNTIF関数を使います。

慣れれば、手入力で数式を作るのが楽なのですが、まだExcelの手入力になれていない人は、ダイアログボックスで作っていくといいでしょう。

C11をクリックして、COUNTIF関数ダイアログボックスを表示しましょう。

範囲には、次の検索条件が含まれている範囲なので、C列の得点である、C2:C7を設定します。

検索条件ですが、80点以上なので、「”>=80”」と入力します。比較演算子と数値を合わせて使うときには、「””(ダブルコーテーション)」が必要になります。

なお、比較演算子とセル番地を使うときには、「”>=”&セル番地」（例：”>=”&C3）という表示の仕方になります。

あとは、OKボタンをクリックして数式は完成です。

直接でなくても、80点以上の件数を数える方法はあります。

たとえば、E列の80点以上という列のように該当するデータをわかるようにしておいて、その結果の数を数えることでも求めることができます。

今回、E2には、
=IF(C2>=80,"○","")
というIF関数を使って、80点以上なら○。そうでなければ、空白。という数式を作っています。

なので、○の個数で何名該当しているかがわかるわけです。

そこで、空白以外ということで、COUNTA関数を使ってみると、どういう結果が算出されるのでしょうか？

結果は、6。

なんと空白も数えてしまっています。確認できたように、COUNTA関数は、数式を使って算出された結果の『空白』も数えてします。

このような場合にも、COUNTIF関数をつかって、「○」を数えるようにすればいいわけです。

【複数条件は、COUNTIFS関数】

条件が複数になった場合は、複数形ではありませんが、IFにSをつけた、COUNTIFS関数を使うことで算出することができます。

今回は、80点以上で、セミナー参加希望の件数を求めていきます。

C13に結果を出しますので、COUNTIFS関数ダイアログボックスを表示しましょう。

検索条件範囲1には、得点のC2:C7
検索条件1には、80点以上ということで、”>=80”
検索条件範囲2には、セミナー希望のD列で、D2:D7
検索条件2には、”○”と入力します。

数式はというと、
=COUNTIFS(C2:C7,">=80",D2:D7,"○")

【空白を数えるのは、COUNTBLANK関数】

COUNTIF関数で検索条件を「””（空白）」で設定しても空白セルを数えることはできますが、空白セルを数える専用の関数があります。

それが、COUNTBLANK関数。

このCOUNTBLANK関数は、引数に範囲を設定するだけで、空白を数えてくれますので、COUNTIF関数よりも簡単に空白を数えることができますし、計算結果が空白だったものも、空白として数えてくれます。

C14の数式は、
=COUNTBLANK(D2:D7)

【非表示に対応するにはSUBTOTAL関数・AGGREGATE関数】

行が非表示になると、COUNT系の関数では、対応されません。

非表示になった行を除いて算出させるには、SUBTOTAL関数・AGGREGATE関数を使う必要があります。

SUBTOTAL関数・AGGREGATE関数ともに、手入力することをお勧めします。

SUBTOTAL関数。

手入力していくと、入力補助が出てきますので、今回は、2がCOUNTとなっていますので、選択したくなりますが、非表示に対応しておりませんので、102のCOUNTを選択するようにします。100番台は、非表示に対応してくれます。

C15の数式は、
=SUBTOTAL(102,A2:A7)
AGGREGATE関数。

最初の入力補助は、集計方法。今回は2番のCOUNTを選択します。
オプションですが、今回は、単純に非表示の行に対応する、5番を採用します。

C16の数式は、
=AGGREGATE(2,5,A2:A7)

では、非表示にしてみましょう。

非表示に合わせて、対応してくれましたね。

このように、数えるだけでも様々な関数がありますので、現場に沿った関数を見つけられるといいですね。