エクセルの配列数式、配列定数について教えてください

質問

エクセルに配列数式、配列定数というものがあることを最近知りました。
エクセルのA1セルに={1,3,3}と入力し、A2セルに=COUNTIF(A1,{1,2,3})と入力すると、
A2セルに1と表示されます。A2には1以外の数字が含まれることは何となく理解できるのですが、どのような計算結果が算出されるのかが理解できません。また、A2の1以外の計算結果を表示させる方法がわかりません。
同様に、A1セルに={1,2,2,3}と入力し、A2セルに=COUNTIF(A1,{1,2,3})と入力した場合（これもA2セルに1と表示されます）のA2の計算結果と表示方法がわかりません。
ご教示をよろしくお願いいたします。

回答No.4

MarcoRossiItaly

配列とは、数学で言うところのベクトルあるいは行列だと考えてください。また、曖昧な言い方ですが、複数セルの集まりのようなものだと思ってください。

説明を始めるといろいろな要素があって、なかなか一言では言えないので、他の回答者さんのご説明も参照してください。

とりあえず、配列がどのようなものであるかを感じていただくために、次の操作をしてみてください。何が起こるでしょうか？

1.A1セルに「={1,3,3}」を入力する。
2.A1:C1のセル範囲を選択する。
3.続けて、キーボードのF2キーを押す。
4.続けて、Ctrl+Shift+Enterを押す。
5.A1セルをダブルクリックし、Enterを押すと、「配列の一部を変更できません。」というメッセージが出るので、Escキーでその状態を抜け出す。
6.A1:C1のセル範囲をクリア（delete）する。
7.A1セルに「1」を入力する。
8.上の2.と3.の操作をもう一度、実行する。
9.続けて、Ctrl+Enterを押す。
10.A1セルをダブルクリックし、Enterを押す。

COUNTIF関数について。COUNTIFは、「基本的には」その引数に配列定数を取らない関数です。そういう仕様です。

配列定数を取らないけれどもムリヤリ書いてやると、最初の数字以外は読み飛ばします。つまり「=countif(a1:a3,{1,2,3})」と書いていても、「=countif(a1:a3,1)」と同じ意味になり、「2」や「3」は無視されます。

なので、それでも配列定数を混ぜるためには、No.1さんのおっしゃっているとおり、SUMPRODUCT関数など配列を扱える関数と組み合わせることが必要です。次の2式は、同じ計算結果になります。

=sumproduct(countif(a1:a3,{1,2,3}))
=sum(countif(a1:a3,1),countif(a1:a3,2),countif(a1:a3,3))

お礼

引数に配列定数を指定できる関数とそうでない関数があることがわかりました。わかりやすい例を教えていただき、ありがとうございました。

回答No.3

Nouble

まずは日経21のEXCEL虎の穴をご熟読ください
私はこちらで基礎を叩き込みました

あと、できれば私の過去答の全てを後参照ください
きっと直ぐに私程度には
すらすら書けるように
なれるものと思います。

私自身片手間にほぼ２ヶ月程度で理解しましたから

ただ、厄介でいまだに困らされる点が　幾つかあります。

それは、
→マイクロソフトが配列数式の仕様を
　無断で細々と変えてくること。
→配列数式を未経験なエクセルには
　ロックがかかってること。
→配列数式の計算結果を
　次段の関数に渡す関係で
　仕様と言うなのバグがあり、
　INDEX構文で一旦、
　なんちゃって定数配列に
　変えてあげる必要がある。
→数学の配列演算的な空想力が
　求められること。
→配列演算では、項目数をきちっと
　合わせる必要があること
などです。

この内、数学的云々は
まあ配列を知らなくても、直感的に感じとれば行けると思いますが

INDEXの方はかなり厄介です。
この構文を有機的にかける必要があり、
しかもその位置をマイクロソフトが
関数の使用変更に伴い
非公開でコロコロ変えてくるからです。

余談ですが
このことを知っている人は
この日本でも極少数だと思います。

まあ極意は伝えました
あとは簡単なものでロックをはずし
そのEXCELの表示されない
内部バージョンの仕様にしたがって
INDEXをかけるべきと頃を探せるようになれば
もうしめたものです。
きっと誰にもEXCEL作業ではヒケをとらなくなると思います。

まあ、さらにその先の関数結果の考察についての極意が
まだまだあるのですが
それはまた別の機会に

ところで、今回の引数
({1,2,3},1)も(1,{1,2,3})も実は正しいですよ。
ロックの外れた環境で試せば
楽しめると思います。

おっといい忘れてました
競るに表示される結果は常に一つ
対して、配列数式の結果はしばしば複数になりますよね
どうやって結果を確認するかですが
数式バーで確認したい範囲を謎って
PF9をおせば
結果が数式バーに表示できる範囲の長さならばですが
確認できます。
違う方法としては
INDEXとROWを用いて
複数のセルに結果の一つ一つを表示する方法とかもありますがね。

まあ頑張ってください
配列数式の有用性が広く世に広まれば
EXCELシートプログラマに開発の仕事以来が来るでしょうから
ね、

p.s.
EXCELで表に現れる結果を鵜呑みにしたらダメですよ

例えば、今回挙げられた例では
A2は1と現れていると思いますが
このA2の表示形式を
ユーザー指定形式で
¨¨,¨¨,¨¨,¨¨としてみてください
きっと目を疑うことになると思いますよ
もしかしたら今までのEXCEL作業の危うさにうちひしがれる、
いや、人生観すら揺らぐかも？

見えているものが真実と
信頼できなくなるのですからね

お礼

ページを紹介いただきありがとうございました。初心者なので教えていただいたことの全てを理解することができませんが、エクセルは奥が深そうですね。勉強のし甲斐がありそうです。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

配列数式のとっかかりは「◆“達人”芳坂和行氏に学ぶ、エクセル(Excel)「配列数式」講座」のページで覚えました
http://pc.nikkeibp.co.jp/pc21/special/hr/
しかし、基本的に作業列を使った方が計算が早く、そのExcelファイルを引き継ぐ人にやさしいです。

お礼

ページを紹介いただきありがとうございました。少しずつ学んでいきたいと思います。

回答No.1

keithin

＞A1セルに={1,3,3}と入力し、A2セルに=COUNTIF(A1,{1,2,3})と入力すると、

その数式では配列計算が出来ていないので、イメージしていた結果も得られません。
といっても、アナタがその式で一体「何を計算したかった」のかイマイチ不明なので、「これが正解です」を掲示する事もできないんですが。

とりあえず。
間違いのその１）
＞A1セルに={1,3,3}と入力し、

Ａ２の式中でそのＡ１セルを参照していますが、そのやり方では配列定数の「先頭の１」が単独で参照されるだけです。配列定数（１と３と３）として呼び込めていません。

任意のセルに
=A1
と数式を記入し、Ａ１セルに記入した配列定数の中身を変えてみて、理解してください。

間違いのその２）
＞A2セルに=COUNTIF(A1,{1,2,3})

Ａ２のセルの式の中でも配列定数｛１，２，３｝を使おうとしていますが、そのやり方では配列定数の「先頭の１」だけしか計算できていません。

間違いのその３）
＞A1セルに={1,2,2,3}と入力し、A2セルに=COUNTIF(A1,{1,2,3})と入力した場合のA2の計算結果と表示方法がわかりません

そもそもCOUNTIF関数の１つ目の引数（範囲）には、配列を指定することはできません。セル範囲を指定できるだけです。そのセルに配列定数が記入されていた場合は、先に説明したように先頭の１つだけが参照されます。

間違いのその４）
Ａ１セルに１
Ａ２セルに２
Ａ３セルに２
Ａ４セルに３
をそれぞれ記入します

Ｂ１：Ｄ１セルを選択
=COUNTIF(A1:A4,{1,2,4})
と記入し、コントロールキーとシフトキーを押しながらEnterして配列入力すると、結果は１，２，０と現れます

縦１×横３のセル範囲に配列入力された３つの数式は、式中の横方向の配列｛１，２，４｝に対応する位置にある要素を使って計算します。
丁寧に書けば
Ｂ１は　=COUNTIF(A1:A4,1)
Ｃ１は　=COUNTIF(A1:A4,2)
Ｄ１は　=COUNTIF(A1:A4,4)
をそれぞれ計算します。これが配列計算です。

＃参考
１つのセルの中で
=SUMPRODUCT(COUNTIF(A1:A4,{1,2,4}))
と計算すると、上述のＢ１とＣ１とＤ１の１，２，０のそれぞれがSUMPRODUCTの中に計算され、合計の３が求まります。

このあたりの計算の流れは、「数式の検証」の機能を使って追跡し勉強できますので参考にしてみて下さい。

お礼

質問がわかりにくくて、申し訳ありませんでした。配列を引数に指定できる関数とそうでないものがあることがわかりました。「数式の検証」の機能は計算の過程がわかって便利ですね。ありがとうございました。