[[20231002163931]]　『参照している別シートの行を削除した場合』（ツバサ）　ページの最後に飛ぶ

『参照している別シートの行を削除した場合』（ツバサ）

ご教示をお願いします。
下記、シート１の表D列にある文字をシート２の表D列にSubstitute関数を使って指定した文字を変更しています。シート１にある行を（例えば２行目を削除）削除した場合にシート２のSubstitute関数の参照先に指定しているセルの値が「＃REF！」と表示されてしまいます。この場合どのような関数を使えば防げるでしょうか。

シート１

   A   B   C   D
1 001 US  OPS Associate
2 002 JP  OPS Specialist
3 003 GB  OPS Supervisor

シート２

   A   B   C   D
1 001 US  OPS Non-Mgt
2 002 JP  OPS Mgt-Spec
3 003 GB  OPS Sup

＜使用 Excel：Microsoft365、使用 OS：unknown ＞

IFERRORで対応すれば？

（SWA） 2023/10/02(月) 17:05:05

SWA様、早速ありがとうございます。すみません、説明不足でした。「＃REF!」と表示させたくないのではなく、シート１の行を削除した場合、シート２の行も連動して関数が正しく処理されるようにしたかったのですが、この場合どのようにすればうまくいきますでしょうか。。
（ツバサ） 2023/10/02(月) 17:16:36

 INDIRECT関数を使ってはどうだろうか？
 もっともフィルコピーに対応させるには工夫が必要だが。
（ねむねむ） 2023/10/02(月) 17:18:45

 Sheet2のD列に入れている SUBSTITUTE の式を提示してください。

 ひとまず以上です
（笑） 2023/10/02(月) 17:21:41

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

笑さん、SUBSTITUTEの式は、=SUBSTITUTE(SUBSTITUTE(SUBSITUTE('Sheet1!'D1,"Associate","Non-Mgt"),"Specialist","Mgt-Spec"),"Supervisor","Sup") となります。

むねむねさん、INDIRECT関数とSUBSTITUTE関数の組み合わせで可能なのでしょうか？関数の式が見当つかず。。
（ツバサ） 2023/10/02(月) 17:30:11

 Sheet1の2行目を削除したら
 Sheet2の ↓ はどうなればいいんですか？

	A	B	C	D
1	001	US	OPS	Non-Mgt
2	002	JP	OPS	Mgt-Spec
3	003	GB	OPS	Sup

 以上、確認だけ
（笑） 2023/10/02(月) 17:43:29

笑さん、Sheet1の２行目を削除したらSheet1の３行目にあったAからD列が２行目に来るので、Sheet2もSheet1の行のと同じように対応させたいです。ちなみにSheet1は行数が６００くらいあり、いくつかの情報をソートして、いらない行を削除することを想定しております。よってSheet２も同じ情報（表）になるようにしたいです。よろしくお願いいたします。
（ツバサ） 2023/10/02(月) 17:54:14

 Sheet1の2行目を削除したら
 Sheet2は ↓ のようにしたいんですか？

	A	B	C	D
1	001	US	OPS	Non-Mgt
2	003	GB	OPS	Sup

 本当に 1行目から始まっているのだとして

 A1 =FILTER(Sheet1!A:C,Sheet1!A:A<>"")

 D1 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FILTER(Sheet1!D:D,Sheet1!D:D<>""),"Associate","Non-Mgt"),"Specialist","Mgt-Spec"),"Supervisor","Sup")

 コピーしなくても勝手に展開すると思います。
 重くなるかどうかは知りません。
（笑） 2023/10/02(月) 18:24:06

笑さん、無事に解決しました。色々とありがとうございました。大変助かりました。
（ツバサ） 2023/10/03(火) 07:55:45

コメント返信：

[ 一覧(最新更新順) ]

[[20231002163931]] 『参照している別シートの行を削除した場合』（ツバサ） ページの最後に飛ぶ

[[20231002163931]]　『参照している別シートの行を削除した場合』（ツバサ）　ページの最後に飛ぶ