配列数式の答を計算式入力セルに出す方法は？

質問

FREQUENCYなど配列数式の答は、式入力セルを選択した状態でF2を押した後F9を押すと数式バーに表示されますが、他の計算式のように式を入力したセルに直接答を出す方法はないのでしょうか。

　答もまた配列で返され、場合によっては無数に続くからでしょうか、それとも私のやり方が間違っているのでしょうか、式入力セルには必ず「0」が返されてしまいます。

　ご存知の方お教えください。

回答No.3

Wendy02 · ベストアンサー

こんにちは。

私用で、返事が遅くなりすみませんでした。

時々、同じような話にぶつかるのですが、方法論を問い始めると、求める解が出ないまま、いつまで経っても、同じような解の出てこない思考の無限ループに陥ってしまいます。そういう場合は、スッパリと、同じ思考法はやめたほうが、無駄が少ないように思います。

この関数は、特殊な関数のひとつで、そういう仕様に決めた人間の問題かもしれません。そういう関数は、いくつかあります。MS社とは書きましたが、実際は、MS社オリジナルとそうでない関数が入り混じっています。例えば、CELL関数やINFO関数、またDATEDIF関数は、MS社の関数のスタイルには馴染みません。SMALL/LARGE関数とRANK関数の仕様の整合性が悪いのも、何かの問題が潜んでいるようです。

私が、申している「MS社の仕様の否定」とは、こういう背景を意味しています。だから、究極的には、問題があれば、自分で作っていくしかない、そういう考えは、私自身のものです。そういう方法があるということで、反意語ではありません。

本題に入ります。
＞　次に試したのはB2～C6に下記のように入力し、D2の式を
＞｛＝FREQUENCY($A$2:$A$8,$B2:$C2)｝
＞と区分配列を変更し、行相対にして下方にコピーしてみましたがこれもやっぱり失敗でした。

なぜ、失敗したかというと、これは、FREQUENCY関数の解が、配列だからです。

たぶん、この問題は、配列のこと自体をご存知ないからなんだと思います。
配列数式で返るデータというのは、メモリ空間に、ワークシートと同じ構造を持ったデータのマトリックス（格子状）のデータ群なのです。

このFREQUENCY関数の戻り値は「配列を返す」、つまり、メモリ上に縦に、複数の戻り値を持ったデータの塊なのです。なぜ、範囲を選択して、Ctrl+Shift →Enter とするかというと、そのメモリ上の縦のデータの塊を、その場所に貼り付けるということであって、ひとつひとつの戻り値を出すわけではありません。

簡単な例を示します。

例えば、A1 ～A10 にデータを入れ、
D2に　（わざとずらしました）
=$A$1:$A$10 としても、

セルの行と同じひとつしかでません。フィルダウンで下にコピーしても、同じ行の値しか出てきません。

そこで、D2に、=A1:A10 の式を入れた後に、D2～D11 までの範囲を選択し、Ctrl+Shift → Enter としてみると、不思議と、A1:A10までが出てきます。配列で、A1:A10 を表示するためには、それと同じ構造（縦=高さ10セル分）の範囲が必要になります。これは、数式が配列式で、配列を返したことになります。

これが、一般式と配列式との違いです。

FREQUENCY関数の場合、
｛＝FREQUENCY($A$2:$A$8,$B$2:$B$6)｝
　　　　　　　　　　　　　　　↑注意
　　　　　　　　　　　　　　　これは範囲が増えて、$B$7 になります。

ですから、一般的な回答としては、
B9 に出すのでしたら、
　=FREQUENCY(A2:A8,B2:B7)

に式を入れ、B14まで範囲を選択して、Ctrl+ Shift → Enter とします。

＞この答え「={0;1;2;3;1;0}」を、F2→Ｆ9を押さなくても最初からＢ9に出す方法はないか、というのが質問の趣旨です。

ここが私には、良くわかっていないのですが、これは、ユーザーサービスで単に見えているだけであって、ある意味別世界（＝メモリ空間上）にあるデータ群です。データ構造が違います。そのままでは取り出せません。これを取り出すのは、ワークシート上では用意されませんが、VBAで作ることは出来ます。

もし、そのまま、配列確定をしないで出すのであったら、以下のような式が必要です。（ただし、これは裏技です）

B9:～　（エラーが出るまでフィルダウンでコピーします。）
=INDEX(FREQUENCY($A$2:$A$8,$B$2:$B$7),ROW(A1),1)

．

お礼

懇切丁寧かつ含蓄に富んだ詳細なご回答誠にありがとうございました。

＞この問題は、配列のこと自体をご存知ないからなんだと思います。

全くそのとおりです。そもそも私のパソコンのレベルは基礎知識ゼロのまま、実務上の必要に迫られて、何とか日常の作業には事欠かない程度までスキルだけを積み上げてきただけのもので、配列式の構造など私の理解能力の限界を遙かに超えている事柄でした。

しかしご教示のおかげで、全体像や核心はともかく、漠然とその片鱗には触れることが出来たような気がしております。

最後にとっておきの裏技、なぜそうなるのか、式の構造や組立は今のところ分からないながらも、なーんだ、やっぱり方法があるじゃないかと正に驚嘆を通り越して一瞬虚脱感すら覚えるほどでした。

このような秘伝虎の巻をタダで伝授いただき、ほんとにいいんですか？といいたいような感謝の念で一杯です。

それにしてもこのたびのＱ＆Ａは正に序の口と横綱ほどのレベル差を痛感させられる数日でした。今後またご指導を仰ぐことがあるかも知れませんがよろしくお願いします。

最後にご指導を心から感謝申し上げます。

回答No.2

Wendy02

こんにちは。

＞お尋ねしたのは例えばSUM関数や単純な加減乗除の答のように、数式バーじゃなく計算式を入力したセルに直接答が表示出来ないかという意味だったんですが

おっしゃっていることが、良く理解できませんが、MS社が、ある関数に関して、ある設計の元に作られたものの仕様を否定することは出来ません。それが使いにくいのなら、一般関数で代用すればよいだけだと思います。

＞答の文字列が無数に並ぶ可能性がある配列数式
どんなにデータ個数は多くても、配列数式は、区間に対して、ひとつ足すだけですし、また、基本的には、数字のデータのみです。

＞式入力セルにはやはり「0」しか出ないのです
もし、0と出るなら、その区間に数字がないのでしょう。

具体例がないので、こちらか出しますが、例えば、以下のように試してみればよいでしょう。ただし、区間を最後に、ひとつ付け足す必要があります。

C1:～
=SUMPRODUCT(($A$1:$A$30>=B1)*($A$1:$A$30<B2))
ただし、区間が、1～10 までなら、最後に、11 を足します。

A　　　　 B　　　C
　15　　　1　　　式
　 5　　　2
　 1　　　3
　20　　　4
　14　　　5
　15　　　6
　 4　　　7
　20　　　8
　15　　　9
　11　　 10
　16　　 11　←付け足す。
　・
　・
　・

補足

再度のご回答、ありがとうございます。
　やはり私の説明というか表現が素人っぽかったんでしょうね。すれ違いの理由がおぼろげながら分かりました。

　例示いただいた、SUMPRODUKT関数の場合の答と、(例えばつきでしたけど)私が最初取り上げたFREQUENCY関数の場合の答とでは、前者は答の数値が1個だけなのに対し、後者の場合は｛｝の中に区間配列中の数値の個数分だけ並ぶので、式が入力されたセルの列幅の関係等でそもそも表示できないようになっているのか、あるいは何らかの方法を加えれば出来るのか、それだけを知りたかたのであって、別にMS社の仕様を否定しようなんて爪の垢ほども思っておりません。
もしできないのなら、一言そう仰って頂ければ氷解するんです。
　具体例をあげて説明させていただきます。

　　　A　　　　　B　　　　　C　　　　　D
1　　得点　成績区分(以下)該当数　　該当データ
2　　25 　　　20 　　　　　　0
2　　60 　　　40 　　　　　　2 　　25,33
3　　52 　　　60 　　　　　　3 　　52,58,60
4　　58 　　　80 　　　　　　1 　　69
5　　33 　　　100 　　　　　 1　 　94
6　　94
7　　69 　　　　　　　　
8　　　　　　　　
9　　　　　　　0　　　　　　　　　↑
　　　　　　　　　　　　ＣＤ列は手入力した答の人数と該当者の点数

　これはあるグループの得点配分（20点以下は何人かなど）を調べるための表です。
　セルB9に次の式を入れます。（なぜC2でなくB9かは後で説明します。）
｛＝FREQUENCY($A$2:$A$8,$B$2:$B$6)｝
　するとＢ9にはＡ列の点数中、Ｂ2（20）を下回る人数「0」が返されます。
　しかし求めたい答は、F2→F9と押して数式バーに現れる「={0;2;2;2;1;0}」です。この答は同時にB9に（=を除いて）反転表示で現れます。
　この答え「={0;1;2;3;1;0}」を、F2→Ｆ9を押さなくても最初からＢ9に出す方法はないか、というのが質問の趣旨です。

　最初の希望は、C2に前記の計算式を入力するとB2に対応する答「0」が返ってくるので、C2の計算式をC3以下にコピーすることにより、C3～C6に「2，3，1，1」と答が現れる方法（Wendy02さんが例題のSUMPRODUCT関数で示されたような方法）がないかいろいろチエを絞ったのですが、どうしてもその方法が見付からず、仕方がないからどこでもよい適当なセルに直接「={0;2;2;2;1;0}」が現れればよいと考えた訳です。

　絞ったチエというのは、計算式
｛＝FREQUENCY($A$2:$A$8,$B$2:$B$6)｝
の「$B$2」を行相対の「$B2」にし、下方にコピーしたらと考えやってみましたがもちろん失敗でした。（C2以下、「0，2，5，6，7」となる）

　次に試したのはB2～C6に下記のように入力し、D2の式を
｛＝FREQUENCY($A$2:$A$8,$B2:$C2)｝
と区分配列を変更し、行相対にして下方にコピーしてみましたがこれもやっぱり失敗でした。

　　A 　　B 　　　　C 　　　　D 　　　　　　　E
1　得点　成績区分（以下）　セルに出た数　F2F9押した答　　　　　　　　　　　　　　　　　　　　
2　　25 　0.00 　　 20 　　　0 　　　　　　 0,0,7
3　　60 　20.01 　　40 　　　0 　　　 　　　0,2,5
4　　52 　40.01 　　60 　　　4 　　　 　　　2,3,2
5　　58 　60.01 　　80 　　 10 　　　 　　　5,1,1
6　　33 　80.01 　　100 　　13 　　　 　　　6,1,0
7　　94
8　　69

　と、こんな調子で何度も試行錯誤を繰り返し、結局お手上げとなって最初のような質問になったというのが事の真相です。

　最初からこのように詳細に経緯を説明すれば私の言いたい内容をご理解いただけたのではないかと思いますが、要はC2～C6にB列の区分配列に応じた答（0,2,3,1,1）が返ってくる方法はないかということなんです。

　多分私の考えには何か幼稚というか根本的な発想のミスがあるのでしょう。
　であるとすればこういう場合、B列の区分配列をどのように入力するのか、そして計算式はどこにどのように設定するのか、そこのところを教えていただければ幸いです。
　よろしくお願いします。

回答No.1

Wendy02

こんにちは。

FREQUENCY(データ配列,区間配列)

データがあって、区間配列を別に入力したら、

式の引数に、データ配列と区間配列の範囲を入れ、一旦、その先頭に式を入れ、その区間配列より1つ分範囲を多くして、セル範囲を決めます。（縦のみです）

そして、Ctrl+Shift + Enter で、配列確定すれば、式がコピーされて、その全てが出てきます。外すときは、配列数式の範囲を選択して、Ctrl+ Enterで外れます。

お礼

ご回答ありがとうございました。
　私の質問内容が不十分だったのでしょうか。お尋ねしたのは例えばSUM関数や単純な加減乗除の答のように、数式バーじゃなく計算式を入力したセルに直接答が表示出来ないかという意味だったんですが………
　仰るように試させていただきましたところ、確かに区間配列が縦の場合は（F2やF9を押さなくても）直接数式バーに答が現れるので大変便利ではありますが、式入力セルにはやはり「0」しか出ないのです。ご回答内容を何度も読み直していろいろと試してみましたが結果は同じで、やり方のどこが間違っているのかどうしても分かりません。

　単純乗算、例えば1000×1000の場合でも、式入力セルの幅が狭いと「1E+06」のような答が出るので、答の文字列が無数に並ぶ可能性がある配列数式ではセル表示が無理なので数式バーにしか出ないようにしてあるのでしょうか。
　ともあれ仮にそうだとしてもF2やF9を押さなくて答が見れるという点では希望の半分以上は達成されましたので、ご教示厚くお礼申し上げます。