[[20160421164512]]　『配列について』（T1604）　ページの最後に飛ぶ

『配列について』（T1604）

お世話になります。
配列および配列数式？の概念がよくわからず、ご教授願います。
例）
A1:A5に数値が入力されておりその「逆数の和」を求めたく算出式を
考えていたところ、偶然にも解らしきものを得ました。
（A1:A5には空欄も含まれます）

 説明）
   A  B     C
 1 5  0.2   0.2
 2    0     0.2
 3 2  0.5   0.2
 4    0     0.2
 5 4  0.25  0.2
解　　0.95 式→{=SUM(IFERROR(1/INDEX($A$1:$A$5,),0))}
B1:B5→B1にIFERROR(1/INDEX($A$1:$A$5,),0)入力して下へコピー
C1:C5→C1に{=IFERROR(1/INDEX($A$1:$A$5,),0)}入力し下へコピー
※{ }は「Ctrl」＋「Shift」＋「Enter」で入力（CSE入力？）

ところが何故この解が成立するのか、いろいろ考えても全くわかりません。
D1:D5を選択したままC1式を「Ctrl」キー入力するとB1:B5と同じ結果を得ます。
この両者の違いがまずわかりません。
（F9キーで式を確認しても両者は同じ表示ですよね）

さらに、解を得た式は配列数式（CSE入力？）にしないと正解は得られません。
これもまたよくわかりません。

要するに“基本”がわかってないからですが、業務の合間にネットで漁っていても
モヤ～としたまま今に至っております。

どなたかこの○○にわかりやすく解説願えないでしょうか？
回答は急ぎませんがよろしくお願いします。
（所用でレスが遅くなりますこと、ご容赦願います）

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 まずは下記のリンク先をよく読んでみてくれ。
 そのうえで疑問点があればまた質問してみてくれ。

http://pc.nikkeibp.co.jp/pc21/special/hr/index.shtml

 あと、ちょっと配列数式とは関係しないことなので説明。

 >D1:D5を選択したままC1式を「Ctrl」キー入力するとB1:B5と同じ結果を得ます。 
 これは開始セルに式を入力後、フィルコピーすることと同じ（CTRLキーを押しながらEnterは
 セル範囲に同じ値を入力するキー操作、ただしセル参照はフィルコピーした時と同じ変化をする）
 ためで結局B列と同じ操作をしていることになるため結果もB列と同じになる。
（ねむねむ） 2016/04/21(木) 17:13

 さらに B:C列をさらに下に引っ張ったらどーなるでしょう？
 (D列はねむねむさんの通り、フィルコピーのショートカット(？）っぽい処理）

 さらに A列 20行目～25行目にしたらどーなるでしょう？

 CTRL+SHIFT+ENTERなしは 配列確定してないので、1データが返る。
 そのデータが、数式の範囲と連動して番手のデータを返す。
 (コレは 範囲指定してC+S+Eも同じ）

 1セル内でC+S+Eすると、1セルに配列処理、返るのは先頭データ
（GobGob） 2016/04/21(木) 17:22

 あと、ちょっと細かいところだが。

 =SUM(IFERROR(1/INDEX($A$1:$A$5,),0))
 は
 =SUM(IFERROR(1/$A$1:$A$5,0))
 で構わない。

 配列数式関連でINDEX関数を使う場合は、だいたいShift+Ctrl+Enterをせずに通常のEnterで済むようにする場合に使うが
 IFERROR関数でINDEX関数をくるむとINDEX関数を使っていてもShift+Ctrl+Enterが必要になり結果、INDEX関数が無駄になる。
（ねむねむ） 2016/04/21(木) 17:29

 IFERRORなしで検証したほうがわかりやすいんでない？
　
（GobGob） 2016/04/21(木) 17:43

ねむねむさん、GobGobさん、　ありがとうございます。

 >CTRL+SHIFT+ENTERなしは 配列確定してないので、1データが返る。
 >そのデータが、数式の範囲と連動して番手のデータを返す。
 >(コレは 範囲指定してC+S+Eも同じ）

↑でしたら「範囲指定してC+S+E」のメリットって何でしょうか？
キチンと範囲指定してからでないと変更できないので、むやみに
編集できないようにすること．．でしょうか？
私には可読性のデメリットの方が大きいように思うのですが。

 >1セル内でC+S+Eすると、1セルに配列処理、返るのは先頭データ

↑こう説明されるとわかるのですが、何でなん？となってしまうんです．．
Excelとはこういうもんだと頭に叩き込むしかないですね、これも。

紹介されたサイトは知ってましたが、会話形式の説明に不慣れで
モヤ～としてました（苦笑）。
INDEXの件、勉強になりました。　感謝します。

これからもよろしくお願いします。
（T1604） 2016/04/22(金) 16:03

 修正。。。

 ＞(コレは 範囲指定してC+S+Eも同じ）

 C+S+Eは「数式範囲に連鎖」でなく「配列数に連鎖」なので 同じではないっすね。スンマセン。

 配列確定したら1セル全て「配列」処理するけど、結果は1セルで求めてるので
 先頭セルが返る。 （1セル内で配列処理してるから）

 ってこと。

 チョッと変えてみて。

	A	B	C	D
1	5	-	-	-
2		#DIV/0!	0.2	0.2
3	2	0.5	#DIV/0!	0.2
4		#DIV/0!	0.5	0.2
5	4	0.25	#DIV/0!	0.2
6		#VALUE!	0.25	0.2

 1行目入力なし。

 B列 ・・・ B2セルに =1/A$1:A$5  と入力しENTER。それを下へフィルコピー。
 C列 ・・・ C2:C6を選択し、 C2セルに =1/A$1:A$5 と入力し、 C+S+E。
 D列 ・・・ D2セルに =1/A$1:A$5  と入力しC+S+E。それを下へフィルコピー。

 こんな結果。

 1セルでENTERのみ ・・・ 数式範囲と同行（同列）で配列結果を1セルごとに返す。
 複数セルでC+S+E  ・・・ 数式の配列範囲に連鎖して選択した複数セルに返す。
 1セルでC+S+E     ・・・ 1セルで配列処理、結果を返すのは単独セルなので先頭セルが返る。
（GobGob） 2016/04/22(金) 16:28

 ＞さらに、解を得た式は配列数式（CSE入力？）にしないと正解は得られません。 
 ＞これもまたよくわかりません。

 まずは「そもそもなぜ1セルで配列処理するのか？」ってことっすね。
 んでなぜそれを下へフィルコピーする意味があるのか？ってことっすね。
　
（GobGob） 2016/04/22(金) 16:37

 もう少し整理。。。けど
 Microsoftの人ではないんで憶測あります。

 /////////////////////////////////////////////////////////////////////////

 （１）1セルで配列処理する（C+S+E） ・・・ 配列処理を1セルで完結させる。
 （２）その他 ・・・ 配列処理し、結果を各セルへ返す。

 この違いですわ。

 T1604さんの例で言うと（２）は理にかなってるけど （１）は理にかなってまへん。

 「1セルで完結させる」処理なんで下へコピーする意味はないってことっす。

 んで、（１）を配列処理して、今回みたいに「完結させてない」処理（配列計算してほっぽらかし）
 すると「とりあえずデータの先頭かえしますわー」って処理をする。ってことちゃいますか？

 ※ {=IFERROR(1/INDEX($A$1:$A$5,),0)}  → 配列処理を1セルで。けど結果に対し完結させていない。 → 先頭データを羅列してるだけ。下フィルコピーの意味なし。

 ※ {=SUM(IFERROR(1/INDEX($A$1:$A$5,),0))} → 結果を「SUM」で完結させている。
　
（GobGob） 2016/04/22(金) 16:57 → 17:05 修正

 式を簡単にしてみる。
 B列には1行目に
 =A$1:A$5
 と入力して下へフィルコピー

 C列には1行目に
 {=A$1:A$5}
 と入力して下へフィルコピー

 D列にはD1セルからD5セルを選択して
 {=A$1:A$5}
 とまとめて入力

 これで

    A   B   C   D
 1  5   5   5   5
 2      0   5   0
 3  2   2   5   2
 4      0   5   0
 5  4   4   5   4
 という結果になる。
 まず、B列。
 これは

http://pc.nikkeibp.co.jp/pc21/special/hr/hr6.shtml

 にある「共通部分参照」機能のため

 1  5  =A1
 2     =A2
 3  2  =A3
 4     =A4
 5  4  =A5
 と入力しているのと同じことになる。
 配列ではなくセル番地と式とで共通するセルだけを計算の対象としている。

 対してC列のほうは「共通部分参照」を無効にしているため配列が返ってくる。

 1  5   ={5,0,2,4}
 2      ={5,0,2,4}
 3  2   ={5,0,2,4}
 4      ={5,0,2,4}
 5  4   ={5,0,2,4}

 ただし、Excelでは一つのセルで配列を表示する機能はないため先頭の項目だけ表示する。
 つまりすべてのセルで先頭の「5」が表示される。

 最後にD列だが範囲を選択してShift+Ctrl+Enterは選択した範囲に配列の項目を表示させる機能のため１行目に
 配列の1項目目、2行目に2項目目、3行目に3項目…と表示する。

 一見、B列と同じように思えるがB列はセル番地と行・列が共通していない場合にはエラーとなる。
 たとえばB10セルに
 =A$1:A$5
 と入力すると共通する行がないため#VALUE!エラーとなる。

 対して
 {=A$1:A$5}
 はB10セルからB14セルを選択して入力した場合でも正しく表示される。
 (ただし配列の個数より多いセル範囲を選択した場合は多過ぎる部分が#N/Aエラーになるが)

 追記
 C列はD列でセル範囲を一つだけにしている状態ともいえるため1項目目だけが表示される。
（ねむねむ） 2016/04/22(金) 17:08

 >↑でしたら「範囲指定してC+S+E」のメリットって何でしょうか？ 
 >キチンと範囲指定してからでないと変更できないので、むやみに 
 >編集できないようにすること．．でしょうか？ 
 >私には可読性のデメリットの方が大きいように思うのですが。

 FREQUENCY関数など結果を配列で返す関数の表示用…ですかね。

 もっとも私はINDEX関数と組み合わせて表示させることが多いが。
 (範囲が変更になった際の修正のしやすさを取って)
（ねむねむ） 2016/04/22(金) 17:26

GobGobさん、ねむねむさん
レス遅くなり申訳ありません。

お二人の具体例をじっくり考えて、ようやく“胸のつかえ”が
とれたような気分です。

「共通部分参照」．．紹介サイトを見てた筈なんですが、頭に
残ってなかったです（恥）、　復習します。

私のような〇〇に懇切丁寧に解説いただき本当にありがとうございます。
にしても、ここのサイトの教授陣はスゴイですね。
管理人さんにも感謝感謝です。

これからもよろしくお願いします。
（T1604） 2016/04/23(土) 18:07

コメント返信：

[ 一覧(最新更新順) ]

[[20160421164512]] 『配列について』（T1604） ページの最後に飛ぶ

[[20160421164512]]　『配列について』（T1604）　ページの最後に飛ぶ