配列数式で行を増やすためには

質問

シート1の一覧表からシート2の配列数式の入った表へある条件のものを抽出する作業を行っています。
シート2で行を増やそうと思い、配列数式の入ったセルを選択→解除→行を増やす→再度配列数式へ、という作業を行いました。
しかし、#N/Aというエラー表示がでます。シート1の表と指定範囲は変わっていないのにエラー表示はなぜ出るのでしょうか。

回答No.3

mu2011 · ベストアンサー

「行を増やす」方法ですが、現状の４０行をコピー後、挿入されているのでしたら数式のROW($1:$500)が変位し、表範囲外となりますので＃Ｎ／Ａエラーとなったと思われます。
単純にシート２の最終行セル範囲を選択して下方向に４０行分コピーすればよいのではないでしょうか。

お礼

ありがとうございました。
解決しました。
先に行を増やしてから数式を入れるのではなく、数式の入った行をそのまま下にコピーしながら増やす違いだったのですね。同じ方法のようで違いに気づきませんでした。

回答No.2

imogasi

本コーナーの回答でも、配列数式を用いる回答はは、相当式が複雑になります。特に抽出の関数式の中では。
具体的に式でも書かないと、＃N/Aでだけで原因が突き止めてくれる（可能と）と思うのは、修行が足りない。私も配列数式の回答を沢山しますが、長時間試行錯誤の上にやっとできるというようなのが多い。誤りの原因指摘は、現場にいない回答者にとって、至難の業です。
＞配列数式の入ったセルを選択→解除→行を増やす→再度配列数式へ、という作業を行いました
まず配列数式は
（１）配列を返す関数、FREQUENCYなど
（２）（あいまいな言いかtらですが）抽出などにつかうもの
があり、（２）タイプらしい。
（A)それと集計などして１つの値を返すタイプと
（B)複数セル・配列に値を返すのとある。
抜き出しは、（B)はSMALL,LARGEなどと配列数式部分の組み合わせが多い。
ーー
A1:A10とか繰り返すセル範囲が、必ず配列数式にはあり、そこを
（通常何箇所かある場合があり）、全てA1:A20とかに変えて、S+C+E
押せばよいはず。
質問の解除するのはどういうことかわからない。
生かしたほうが賢明。
「配列の一部は変更できません」とか出る場合が多い。
例データ
aの「行を抜き出しする。
A列　　　B列
a 1
b 2
c 3
d 4
a 5
f 6
g 1
a 2
ーー
結果（＃NUM!は手当てしてない）
C列　　D列
a 1
a 5
a 2
#NUM! #NUM!
#NUM! #NUM!
#NUM! #NUM!
#NUM! #NUM!
#NUM! #NUM!
上記のうちのどの部分で、「＞#N/Aというエラー表示がでます」
のでしょうか。
ーー
A1:A10をA1:A20に増やすと
=INDEX($A$1:$B$20,SMALL(IF(A1:A20="a",ROW(A1:A20),""),ROW()),1)
のようにINDEXに絡んだ、A$1:$B$20の部分も直す必要あるが、大丈夫ですか。

補足

回答ありがとうございます。
式は以下のとおりです。
{=IF(COUNTIF(Sheet1!$L$7:$L$506,$A$1)<ROW(A1),"",INDEX(Sheet1!$B$7:$N$506,SMALL(IF(Sheet1!$L$7:$L$506=$A$1,ROW($1:$500)),ROW(A1)),COLUMN(B1)))}
シート1はB7からN506まで入力されています。シート2で上の式の入った欄が足りなくなったので増やす作業をしていました。
今までは60行で設定したのを100行までに増やしたかったのですが、増やすとエラーが出てしまい、また60行に直すともとのとおり表示されるのです。
単純なことの見落としかと思い、式も書かずに投稿してしまいました。
大変失礼しました。

回答No.1

Nayuta_X

< 配列数式の入ったセルを選択→解除→行を増やす→再度配列数式 >
の中に有る　解除とは、数式と値のクリアですか??。
　　　解除　は、ないのでね。

数式には、絶対参照と相対参照の方法があります。

例: 相対参照　C1 ⇒ 絶対参照　$C$1

参考URL
参照形式(絶対参照 相対参照 )を切り替える方法
http://excel.onushi.com/purpose/refer-form.htm

配列数式の入ったセルを選択→数式と値のクリア→行を増やす→再度配列数式　で、
数式と値のクリアをしたこと。
そして、再度配列数式(参照先に値がはっいないセルを設定した)為　発生したのです。

#N/Aのエラー　 Not Available value 　値がない ()

参考URL http://www.relief.jp/itnote/archives/000012.php
行を削除したり、追加しても数式を有効にしたい場合は、
数式を相対参照にします。数式が、自動変更されます。
追加した、行に数式を入れる場合は、行を選択して　ドロップします。

また、行を削除したり、追加しても数式を変更したくない場合は、絶対参照にします。

補足

回答ありがとうございました。
初めは下記のアドレスを参考にして試行錯誤していました。
http://okwave.jp/qa803961.html
解除はないのですね。
エラーの「値が無い」というのが私も理解できなくて・・・。
元のデータは変更ないはずなのに・・・と考えこんでしまいました。