エクセルの関数の直し方

質問

現在、画像のような表を作成しておりますが、
1行目の前の行に5行挿入したいと考えております。

下の表からデータを製番・区分ごとに合計して上の表に表示されるようになっています。
5行挿入してタイトル等入れたいと思い、挿入してみると、
今まで下の表からの合計が上の表に表示されなくなってしまいます。

セル番号等確認はしてみたのですが、
どこがいけなくてうまく表示されないのかがわからなくて困っています。

どのように直したらいいかをご教示お願いいたします。

現在入っている関数は以下の通りです。

A2=IF(COUNT(Sheet1!$L$14:$L$38)<ROW(A1),"",INDEX(Sheet1!$B$14:$I$38,MATCH(SMALL(Sheet1!$L$14:$L$38,ROW(A1)),Sheet1!$L$14:$L$38,0),MATCH(A$1,Sheet1!$B$13:$I$13,0)))

B2=IF(COUNT(Sheet1!$L$14:$L$38)<ROW(B1),"",INDEX(Sheet1!$B$14:$I$38,MATCH(SMALL(Sheet1!$L$14:$L$38,ROW(B1)),Sheet1!$L$14:$L$38,0),MATCH(B$1,Sheet1!$B$13:$I$13,0)))

C2=IF(COUNT(Sheet1!$L$14:$L$38)<ROW(C1),"",INDEX(Sheet1!$B$14:$I$38,MATCH(SMALL(Sheet1!$L$14:$L$38,ROW(C1)),Sheet1!$L$14:$L$38,0),MATCH(C$1,Sheet1!$B$13:$I$13,0)))

D2=IF(A2="","",SUMIFS(Sheet1!$G$14:$G$38,Sheet1!$B$14:$B$38,A2,Sheet1!$H$14:$H$38,B2))

E2=IF(C2="","",IF(ISERROR(VLOOKUP(C2,list!$S$3:$T$6,2,0)),"",VLOOKUP(C2,list!$S$3:$T$6,2,0)))

H14=IF($E14="","",IF(ISERROR(VLOOKUP($E14,INDIRECT($J14),3,FALSE)),"",VLOOKUP($E14,INDIRECT($J14),3,FALSE)))

I14=IF($E14="","",IF(ISERROR(VLOOKUP($E14,INDIRECT($J14),4,FALSE)),"",VLOOKUP($E14,INDIRECT($J14),4,FALSE)))

J14=IF(ISBLANK(B14),"",IF(B14<="J121100144","旧","新"))

K14=IF(AND(B14<>"",H14<>"-"),B14&"_"&H14,"")

L14=IF(AND(K14<>"",COUNTIF(K$14:K14,K14)=1),COUNTIF($K$14:$K$38,"<"&K14)+1,"")

回答No.1

MackyNo1 · ベストアンサー

基本的に行を挿入すれば、その挿入によって影響される範囲は自動的に数式が変更されますので(通常は問題が発生しないのですが）、A2～C2セルに入力されている数式中のROW関数の部分も「ROW(A6)」のように変化してしまいます。

この部分はセルを参照しているのではなく、単純に上から順番に1から始まる数字を取得しているだけですから、どのセルに移動してもこの部分は「ROW(A1)」のように1から始まる数式にしなければなりません（どちらかというと数式の1行目のROW関数は「ROW(1:1)」にしたほうがわかりよいかもしれません）。

お礼

ご教示ありがとうございました。
そういう意味だったのですね。
こちらでご教示いただいた式だったので、
理解できていませんでした。
勉強になりました。

回答No.4

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

＞１）　VLOOKUP($E14,INDIRECT($J14),3,FALSE)のような式でセルの範囲を指定するべきところで
＞INDIRECT($J14)が使われていますが範囲を指定したことになっていません。
名前の定義で 「旧」または「新」という名前で 4列以上の範囲が選択されているのでしょう

INDIRECT($J14)を多用すると計算速度が落ちるので代わりに
CHOOSE(1+(B14<="J121100144"),新,旧)
や
INDEX((新,旧),,,1+(B14<="J121100144"))
とすることも可能です。

＞2) B14<="J121100144" についてはB14セルの値がJ121100144という文字列よりも以下の値である
＞ということですが文字列以下というのは間違っています。数値であることが必要です。
＞３）　COUNTIF($K$14:$K$38,"<"&K14)　の式ですがK14のデータを見ますとB14&"_"&H14のような
＞文字列になっていますね。文字列未満の数値をカウントするということはできませんね。
間違ってはいませんし、判断は可能です。
しかし、一般的ではないのも確かですし、何よりわかりづらいです。

＊＊＊＊＊
・理解せずに運用しようというのは無理がある
・SUMIFSが使えるのに、IFERRORを使わないというムラがある
・何度も同じ計算を繰り返す無駄がある
> A2=IF(COUNT(Sheet1!$L$14:$L$38)<ROW(A1),"",
> INDEX(Sheet1!$B$14:$I$38,MATCH(SMALL(Sheet1!$L$14:$L$38,ROW(A1)),
> Sheet1!$L$14:$L$38,0),MATCH(A$1,Sheet1!$B$13:$I$13,0)))
　COUNT(Sheet1!$L$14:$L$38)
　MATCH(A$1,Sheet1!$B$13:$I$13,0)
まだ出てきそうだが、どのようなデータかわからないし、質問と異なる回答なのでこの辺で。

お礼

何回かテストをしてみましたが、
求めたい答えが得られていますので…。
今後の参考にさせていただきます。
ご教示ありがとうございました。

回答No.3

KURUMITO

最上段に5行挿入した場合にうまく作動しないとのことですが、挿入する前にはうまく作動したのでしょうか？
式そのものについていくつかの疑問が有りますね。

１）　VLOOKUP($E14,INDIRECT($J14),3,FALSE)のような式でセルの範囲を指定するべきところでINDIRECT($J14)が使われていますが範囲を指定したことになっていません。3はその範囲の中の3列目を取り出すことになるのですが、そのような式になっていませんね。

2) B14<="J121100144" についてはB14セルの値がJ121100144という文字列よりも以下の値であるということですが文字列以下というのは間違っています。数値であることが必要です。

３）　COUNTIF($K$14:$K$38,"<"&K14)　の式ですがK14のデータを見ますとB14&"_"&H14のような文字列になっていますね。文字列未満の数値をカウントするということはできませんね。

上記の問題を解決した上でお示しの式でROW(A1)のように使われているのは5行下方にずらした場合にはROW(A6)のように変わっていますのでその部分はROW(A1)のように変更してやることが必要ですね。
いずれにしても使われている式そのものにいくつかの問題が有りますので十分に検討することが必要でしょう。
もしも、回答を望むのでしたらご質問を別の形で新たに投稿されることでしょう。

お礼

何回かテストをしてみましたが、
求めたい答えが得られていますので…。
今後の参考にさせていただきます。
また、
ROW(A1)のように変更してやることが必要ですね。
についてはやってみたところだめだったのでこちらで質問させていただきました。

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

回答No.2

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

きっとQ&A掲示板で回答をもらい理解せずコピペしてできたが、
編集できなくて困ったというところでしょう。
突っ込みどころ満載の数式が並んでいますから。

1. Sheet4を挿入して非表示にする
2. ROW(A1) を ROW(Sheet4!A1)
3. ほかも同様
としておけばSheet4を編集したり消さない限り変化しない。

お礼

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