現在、画像のような表を作成しておりますが、
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.4
- 回答日時:
>1) VLOOKUP($E14,INDIRECT($J14),3,FALSE)のような式でセルの範囲を指定するべきところで
>INDIRECT($J14)が使われていますが範囲を指定したことになっていません。
名前の定義で 「旧」または「新」という名前で 4列以上の範囲が選択されているのでしょう
INDIRECT($J14)を多用すると計算速度が落ちるので代わりに
CHOOSE(1+(B14<="J121100144"),新,旧)
や
INDEX((新,旧),,,1+(B14<="J121100144"))
とすることも可能です。
>2) B14<="J121100144" についてはB14セルの値がJ121100144という文字列よりも以下の値である
>ということですが文字列以下というのは間違っています。数値であることが必要です。
>3) 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
- 回答日時:
最上段に5行挿入した場合にうまく作動しないとのことですが、挿入する前にはうまく作動したのでしょうか?
式そのものについていくつかの疑問が有りますね。
1) VLOOKUP($E14,INDIRECT($J14),3,FALSE)のような式でセルの範囲を指定するべきところでINDIRECT($J14)が使われていますが範囲を指定したことになっていません。3はその範囲の中の3列目を取り出すことになるのですが、そのような式になっていませんね。
2) B14<="J121100144" についてはB14セルの値がJ121100144という文字列よりも以下の値であるということですが文字列以下というのは間違っています。数値であることが必要です。
3) COUNTIF($K$14:$K$38,"<"&K14) の式ですがK14のデータを見ますとB14&"_"&H14のような文字列になっていますね。文字列未満の数値をカウントするということはできませんね。
上記の問題を解決した上でお示しの式でROW(A1)のように使われているのは5行下方にずらした場合にはROW(A6)のように変わっていますのでその部分はROW(A1)のように変更してやることが必要ですね。
いずれにしても使われている式そのものにいくつかの問題が有りますので十分に検討することが必要でしょう。
もしも、回答を望むのでしたらご質問を別の形で新たに投稿されることでしょう。
何回かテストをしてみましたが、
求めたい答えが得られていますので…。
今後の参考にさせていただきます。
また、
ROW(A1)のように変更してやることが必要ですね。
についてはやってみたところだめだったのでこちらで質問させていただきました。
ご教示ありがとうございました。
No.1ベストアンサー
- 回答日時:
基本的に行を挿入すれば、その挿入によって影響される範囲は自動的に数式が変更されますので(通常は問題が発生しないのですが)、A2~C2セルに入力されている数式中のROW関数の部分も「ROW(A6)」のように変化してしまいます。
この部分はセルを参照しているのではなく、単純に上から順番に1から始まる数字を取得しているだけですから、どのセルに移動してもこの部分は「ROW(A1)」のように1から始まる数式にしなければなりません(どちらかというと数式の1行目のROW関数は「ROW(1:1)」にしたほうがわかりよいかもしれません)。
ご教示ありがとうございました。
そういう意味だったのですね。
こちらでご教示いただいた式だったので、
理解できていませんでした。
勉強になりました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
- Visual Basic(VBA) 【変更】ファイルを閉じてダイアログで保存した時、更新したシートだけの処理の実行をする 5 2022/03/26 18:31
- Excel(エクセル) マクロを簡潔にしたい 6 2022/09/16 10:37
- Visual Basic(VBA) vbaのvlookup関数エラー原因を教えていただけないでしょうか。 3 2022/04/25 16:16
- C言語・C++・C# プログラミング c言語 4 2023/03/07 01:05
- Excel(エクセル) Formulaプロパティーを使ってセルに数式を組んだのですが簡潔にしたい。 3 2022/08/21 20:51
- Visual Basic(VBA) ExcelVBAでDo Until loopのネスト、IF文を使って一致する物と一致しない物としたい 11 2022/12/24 17:46
- その他(プログラミング・Web制作) pythonでクラスで複数のメソッドを利用する方法 2 2022/04/15 04:17
- Visual Basic(VBA) VBAコードが作動せず、どこに問題があるのか教えて下さい。 3 2023/06/13 13:20
- Excel(エクセル) ExcelのIF関数について 4 2023/05/24 12:54
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルでの作業計算方法について
-
はがきについて。
-
エクセル 文字を増やしたい。
-
セルの内容表示が邪魔になる
-
Microsoft365に変えたのですが...
-
エクセルの計算
-
Microsoft1Officeの互換ソフト...
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
エクセル初心者です 関数の入れ...
-
Excel ピボットテーブルで日付...
-
【関数】適切な文字数の数字を...
-
LOOKUP関数を使えばいいのでし...
-
Aというブックの1というシート...
-
エクセル関数を教えてください
-
Excelのチェックボックスの使い...
-
エクセル 白黒印刷で白線を印刷...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの条件付き書式につい...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報