Excel数式

　　A列　　　 E列
1 北海道　 北海道
2 青森県　 沖縄県
3 北海道
4 宮崎県
5 沖縄県
6 石川県
7 北海道

＜問題＞
A1:A7のデータの中で、E1、あるいはE2と同じセルの個数を求める。


上記の場合、COUNTIFの条件式にORが使えないので、
●=COUNTIF(A1:A7,E1)+COUNTIF(A1:A7,E2)
●=SUM(COUNTIF(A1:A7,E1),COUNTIF(A1:A7,E2))
●=SUM(COUNTIF(A1:A7,{"北海道","沖縄県"}))

…等の数式が使えると思います。

上記の数式のうち3つ目（一番下）の数式について質問があります。
COUNTIFの［検索条件］に「北海道」「沖縄県」とわざわざ文字列を入れていますが、これらをセル参照（E1とE2）としてできないのでしょうか？
［検索条件］に{E1,E2}としてみますと、"{E1,E2}"となってしまいうまくありません。
できるだけこの数式の関数のネストの形のままやりたいのですが、［検索条件］をどうすればできますか？
それともこの場合はセル参照ではできないのでしょうか？

=SUMPRODUCT(COUNTIF(A1:A7,E1:E2))
でもできるようですが、まだいまいちSUMPRODUCTを複雑に使いこなす自信がありません。

試しにFREQUENCY関数のときのように、確定時に［Shift］［Ctrl］［Enter］同時押しすると、
{=SUM(COUNTIF(A1:A7,E1:E2))}
…となり、求める値自体は合っているのですが、これが正解だったりしますか？

ご存知の方よろしくお願いします。
Vista Excel2007です。

No.5 ベストアンサー 回答者： MackyNo1 回答日時： 2013/01/22 00:38

>自分で文字列を入力する場合、セミコロンでもカンマでも別段構わないんでしょうかね？

今回のCOUNTIFS関数の場合は、行配列でも列配列でも加算すれば結果として同じデータとなりますので問題がありません。

しかし、以下のような配列数式で今回の結果を求めようとする場合は、行配列と列配列は重要な意味を持ちます。

=SUM((A1:A7=E1:E2)*1)

上記の式で検索値がE1:F1（横方向）に入力されているなら、そのままでも正しい値を返しますが（縦×横の二次元配列になる）E1:E2セルの場合は以下のようにTRANSPOSE関数で行列を入れ替えて二次元配列にする必要があります。

=SUM((A1:A7=TRANSPOSE(E1:E2))*1)

このような関数を利用した場合は、SUMPRODUCT関数を利用してもCtrl＋Shift+Enterの操作が必要となります。

この回答へのお礼

お礼が遅れましてすみません。
夜分遅くに何度もありがとうございます。
最初のお礼にチラッと入れた疑問にも回答いただき頭が下がります。

＞TRANSPOSE関数で行列を入れ替えて二次元配列にする必要があります。
→これはものすごく難しそうですね。
ちょっと敷居が高そうな気がします…。
現段階では、ひとまず頭の片隅に入れておこうと思います。
理解が追いつきましたらもう一度見直すつもりです。
とにかく、自分で作成するときには、［検索値］を同一行の列方向に入れておく防御姿勢をとることにします…。
{=SUM((A1:A7=E1:F1)*1)}なら確かにうまくいきますね。
配列を抜きにしましても、この数式は簡単そうで、わたし初めての形ですのでちょっとまだ理解できていません。
配列の基本概念をもう少し勉強してみたいと思います。

たくさん回答いただきありがとうございました。
大変勉強になりました。
お二人とも素晴らしい回答でした。
ベストアンサーがお一人だけというのはつらいです…。
どちらもベストですので、今回は最初に回答いただいた方にさせていただきます。
感謝です。
また、よろしくお願いします。

この場を借りて…
MarcoRossiItalyさん、欲しいベストな回答もいただきましたのに今回はすみません。
さすがは前回ベストアンサーにさせていただいた方の回答でした。
大変感謝しております。
これに懲りず、またよろしくお願いします。
本当にありがとうございました。

お礼日時：2013/01/22 03:01

No.4 回答者： MarcoRossiItaly 回答日時： 2013/01/22 00:18

No.3 さんの INDEX 関数を混ぜる方法は便利で、使えるようになると、数式で実現できることの可能性が広がると思います。

私も割と愛用しています（主にこの掲示板の回答にて（笑））。

まあ SUM の場合は SUMPRODUCT があるので、あえて SUM(INDEX) とする必要もないかもしれませんが。

INDEX は、戻り値がセル参照となる書式と、配列となる書式の 2 通りの用法があるのですが、No.3 さんの使い方は、後者の用法によるものです。

この回答へのお礼

夜遅くに何度もありがとうございます。
お二人に色々と教えてくださったことを確かめたりしていましたら、更にたくさんの回答をいただいておりました。
返事が遅れましてすみません。

＞No.3 さんの INDEX 関数を混ぜる方法は便利で、使えるようになると、数式で実現できることの可能性が広がると思います。
→そうですか。便利そうですね。
ただ、まだINDEXの基本的な意味が分かっていませんから、そこをしっかり頭に入れた上でもう一度読み返そうかと思っております。

＞SUM の場合は SUMPRODUCT があるので、あえて SUM(INDEX) とする必要もないかもしれませんが。
→なるほどです。
今回の＜問題＞パターンのような場合は、まずSUMPRODUCTがすんなり出てくるようにしてみます。

Excelは奥が深いですね。
ひとつできるようになってもまだまだ出てきます。
まだまだですね。

ありがとうございました。

お礼日時：2013/01/22 02:07

No.3 回答者： MackyNo1 回答日時： 2013/01/21 23:16

配列数式を作成するときの参考までに、

今回のようにCOUNTIFS関数で得られた配列データを、Ctrl+Shift+Enterの配列処理をしなくてもそのままSUM関数で加算したいなら、その配列データ部分をINDEX関数(第2引数省略）で範囲と同等に変換しすることにより計算することができます（ただし四則計算で得られた配列のみ範囲に変換が可能で一部認識できない関数もあります）。

=SUM(INDEX(COUNTIF(A1:A7,E1:E2),))

この回答へのお礼

何度もありがとうございます。

＞INDEX関数
→これも気になっていました。
PCスクールで資格取得後に時間がかなり余ったのでやってみた資格範囲外の問題を、HLOOKUPを無理やり駆使して（？）解いたんですが、その時インストラクターの方がINDEX関数と言っていたような気がします（範囲外でしたのでそれ以上は教えてもらえませんでしたが…）。
ちょっと難しそうでまだ手を付けておりませんでした。
Excelのヘルプだったかで一度やりかけたことがあったような…。
この機会に頑張ってみようかなと思います。
教えていただいた数式をみてみました。
ちゃんと合っていました。素晴らしいです。
ただ、［行番号］省略なら［列番号］は必須、逆もしかりという説明が出ていましたが、この数式ではその説明外のやり方のようで、ちょっと混乱しています。
もう少しINDEX関数の基本の理解を深めてから、見直してみようと思います。

お礼日時：2013/01/22 01:49

No.2 回答者： MarcoRossiItaly 回答日時： 2013/01/21 22:55

配列定数の中身に{E1,E2}みたいに書くことはできないということを理解されているので、ご質問になっているのだと思います。

配列定数{ }とか、Ctrl+Shift+Enter でも{ }が付加されるわけですが、「{ }を書かないで計算できる」というのが、SUMPRODUCT 関数の存在意義なのです。つまり SUMPRODUCT は、ご質問のように、E1、E2 を使いたいが{ }は書けないといったケースのためにあるような関数なわけです。

{=SUM}というのは、正しい値を算出するのでこれも誤りとまでは言えませんが、後でこのセルが数式が、変更されないまでも編集状態になることがあると、その度に Ctrl+Shift+Enter の操作が必要で、行わないと計算を誤ってしまいます。SUMPRODUCT があるのだから、そういう煩わしい操作はあえてしないということですね。

Ctrl+Shift+Enter をせず、{ }も書かずにセル番地は書くというのは、無理なので、配列を扱うなら SUMPRODUCT ということになります。

なおセル番地ではなく配列定数で計算する場合は、お示しの「=SUM(COUNTIF(A1:A7,{"北海道","沖縄県"}))」のように SUM でオッケーなので、逆に、SUMPRODUCT までは不要と言えますね。

この回答へのお礼

お世話になっています。
回答ありがとうございます。

＞{ }を書かないで計算できる」というのが、SUMPRODUCT 関数の存在意義なのです。
＞つまり SUMPRODUCT は、E1、E2 を使いたいが{ }は書けないといったケースのためにあるような関数なわけです。
→なるほどです。お聞きしたかったことズバリです。
　やはりSUMPRODUCT覚えたほうがいいですね…。

今回のパターンの＜問題＞（他の方々の質問をみて自分なりにやってみています）の場合、SUMとCOUNTIFの［検索条件］の文字列を{}で囲む数式はわりとすんなり出てくるようになりました（まだまだおぼつかないですが…）。
ただ、SUMPRODUCTがちょっと理解不足でして、簡単な使い方しかわかっていませんので、これを使わずSUMとCOUNTIFのネスト（セル参照）でできればいいなぁ…とご質問させていただきました。
必要だとわかりました。

わかりやすいご説明に感謝です。
ありがとうございました。

お礼日時：2013/01/21 23:54

No.1 回答者： MackyNo1 回答日時： 2013/01/21 21:55

＞［検索条件］に{E1,E2}としてみますと、"{E1,E2}"となってしまいうまくありません。

できるだけこの数式の関数のネストの形のままやりたいのですが、［検索条件］をどうすればできますか？
それともこの場合はセル参照ではできないのでしょうか？

検索条件を配列（範囲）指定する場合は、後述されている「=COUNTIF(A1:A7,E1:E2)」のように範囲指定した数式にする必要があります（{E1,E2}のような配列定数の（文字列指定する）式にはできません）。
なお、数式バーで数式のE1:E2の部分を選択してF9キーで計算すると「{"北海道";"沖縄県"}」のように配列定数にすることができます。
ちなみに行方向のデータはセミコロンで区切られ、列方向のデータはカンマで区切られます。

しかしこれでは、検索値がE1セルの個数とE2セルの個数の配列（行方向の）を取得しているだけです（COUNTIF(A1:A7,E1:E2)の部分を選択してF9キーで確認してみてください）。

合計値を求めるにはこの配列で返したデータを加算する必要があるので、「=SUMPRODUCT(COUNTIF(A1:A7,E1:E2))」「=SUM(COUNTIF(A1:A7,E1:E2))」のような数式にする必要があります。

ちなみに後者の数式は、Ctrl＋Shift+Enterで処理して配列数式にしないと加算できませんが、SUMPRODUCT関数ではそのまま配列を加算できる数式なのでそのまま加算できるわけです。

この回答へのお礼

回答いただきありがとうございます。

＞数式バーで数式のE1:E2の部分を選択してF9キーで計算すると「{"北海道";"沖縄県"}」のように配列定数にすることができます。
→やってみました。なるほどです。
これは便利ですね。
今回は［検索条件］がE1:E2だけでしたが、これがE1:E100だった場合、かなり効率的ですね。

＞行方向のデータはセミコロンで区切られ、列方向のデータはカンマで区切られます。
→今回のケースでは、列方向ですので確かにセミコロンで区切られました。
　一応、試しにそれをわざとカンマに変えてみましたがそれでも合っていました。
　自分で文字列を入力する場合、セミコロンでもカンマでも別段構わないんでしょうかね？

2007は一応勉強して資格を取りましたが、その範囲外の関数や数式、技術をここでいろいろ勉強させていただいております。
数式などの質問をみて自分で挑戦していますが、簡単そうでできないこともしばしば…。
回答で、よく配列数式やSUMPRODUCTがでてきますので、少しずつ理解を深めております。

わかりやすい説明でまた一つ勉強になりました。
わざわざありがとうございました。

お礼日時：2013/01/21 23:30