![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?5a7ff87)
エクセルで表を作っています。
1週間程悩み、同じようなような質問を沢山なさってる方もいましたので参考にし
OFFCET/INDIRECT/マクロなど参考にしましたがやはりできませんでした。
どなたかお助けください。
状態は下記のとおりです
Sheet1,Sheet2に同じ項目でデータを作成します。
Sheet1にA2にID番号を入力するとSheet2のA2にデータが自動入力されるようになっています。
Sheet2のA2→=IF(Sheet1!A2="","",VLOOKUP(Sheet1!A2,Sheet1!$A$2:$B$500,1,0))
Sheet2のB2→=IF(Sheet1!A2="","",VLOOKUP(Sheet1!A2,Sheet1!$A$2:$B$500,2,0))
と入力しています。
設定したいことは以下のとおりです。
①Sheet1に行の追加すると、Sheet2の同じ位置に行を追加・削除
②Sheet1にA1にID番号を入力するとSheet2のA2に反映され、Sheet2のB2にも自動で反映されるようにしたい。(但し、Sheet2のA2/B2も関数を自動に反映させたい。)
①に関してCtrlを押しSheet1/Sheet2をクリックすれば行の追加はできるのですが・・・
どうしても組み立てられません。画像も添付致しますのでよろしくお願いします。
できれば、関数でお願いしたいのです。
![「エクセル 行の挿入すると別シートに行が挿」の質問画像](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/5/542366897_5a2f8b4d7a4eb/M.jpg)
No.4ベストアンサー
- 回答日時:
#3です。
#3で提示したマクロのシート名の修正を忘れていました。
(テストは違うシートで行っていましたので)
念のため訂正したものを以下に載せておきます。
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Worksheets("Sheet1").Range("A:B").Copy _
Destination:=Worksheets("Sheet2").Range("A:B")
Application.EnableEvents = True
End Sub
fujilinさんへ
お世話になっております。
わざわざ訂正までありがとうございます。
本当にすごいですね。さらさらと関数もマクロも。そういうことができれば効率あがるな~と思いました。関数がんばってはいるんですが、どうしても2つ以上3つの関数となると
組み立て方がわからなくなります。
弟子入りさせてください・・・と思いました。
また是非お力添えください。よろしくお願いします。
No.3
- 回答日時:
#2です。
ようやくおっしゃっている意味がわかりました。
なぜわからなかったかというと、普通はそういう発想はしないからでしょうね。
SHeet2は常にSheet1と同じにしておくようにセットされているのに、敢えてそれを変更する(壊す)操作を許容することになるからです。
これは、本来の目的とは矛盾する操作といえますよね?
端的に言い換えると、A1セルに他のシートを参照する参照式が入っている時に
「DeleteキーでA1セルの内容を消したり、他の値に書き換えても変わらないようにしたい」
ということとほぼ同様です。
普通に考えれば、これを防止したい時には、このような操作を禁止(できなくする)すると思います。
誤操作や(または悪意で)このようなことが起きることはありますので、そのような事故を防止したい場合、通常は、シートの保護機能を利用すると思います。(そのための保護機能とも言えるでしょう)
あるいは、ユーザの変更を許可するのであればそのままとします。(この場合は、変更されます)
ご質問のような内容であるなら、#2で提示したような関数式では無理だと思いますが、無理やり実現するなら「A、B列に変更があったら、Sheet1のA,B列をSheet2にコピペする(あるいはunDo処理を行う)」みたいなマクロを作成しておくことで実現は可能でしょう。
しかしながら、普通はこのようなことは行わないと思います。
なぜなら、仕組みを知らないユーザからみると、上記のように「A1セルでdeleteキーを押して内容を消しても、復活してしまう」という、自分の操作に対してわけがわからない(不快な)結果を生じてしまうことになるからだと思います。
シートの保護機能を利用なさることをお勧めしますが、どうしてもと言うのであれば、こんな感じでしょうか?
(シート2のマクロシートに設定)
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Worksheets("Sheet23").Range("A:B").Copy _
Destination:=Worksheets("Sheet24").Range("A:B")
Application.EnableEvents = True
End Sub
※ 本来の目的(?)のシート1の変更も常時反映するようにするためには、別途同様のマクロが必要になります。(シート1の変更で同様の処理を行う)
なるほど~よくわかりました。
基本的にそういうことをしないということですね。
要望を汲み、励んでおりましたが根本的に違うと=だから検索してもでてこない
お恥ずかしい限りです。
やっとこれを諦めます。
マクロもありがとうございました。
助かりました。
色々ありがとうございました。また是非ご指導お願いします。
No.2
- 回答日時:
#1です。
>Sheet2のA列B列には既に関数が入っているため
>挿入された場合も関数が反映されるという意味です。
意味がわかりません。
同じ表示にしながら、それとは別の関数が設定されているということでしょうか?
関数式が別に設定されているなら、そのセルの値はその計算結果に依存するので、同じ表示にすることは無理だと思います。
設定されている関数というのが、「同じ表示にするための関数」が入っているという意味なら、#1で回答したように、「常に同じセル位置を参照する関数」を各セルに設定しておけば良いだけだと思いますが・・・
私の解釈がどこかおかしいのでしょうか?
例えば、Sheet2のA列、B列の各セルに
=INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()))
の式を入れておくだけではダメということでしょうか?
(上の式はコピーフィル可能です)
ただし、上の式の場合、Sheet1のセルに空白セルがあると、計算結果として0が表示されます。
(これはエクセルの関数式でセルを参照した時の仕様のようです)
これを表示したくない場合は、エクセルの設定で、「0を表示しない」設定にしておくか、式が複雑になりますが、以下のようにすることでも可能です。
=IF(INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()))="","",INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN())))
※ どうやら、ご質問内容を理解できていないようなので、ご参考になるかならないかもわかりませんが・・・
何度も書き込みありがとうございます。
>例えば、Sheet2のA列、B列の各セルに
=INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()))
の式を入れておくだけではダメということでしょうか?
(上の式はコピーフィル可能です)
上記をSheet2にA2貼り付けました。
そこに行を挿入した場合、上記の関数は自動的にはいりませんよね?
再度フィルドラッグしなおさないと関数式ははいりませんよね?
それを自動化できますか?という意味です。
何度もすみません。
No.1
- 回答日時:
こんにちは
なんとなくですが、Sheet1を編集(行の挿入・削除を含む)しても、常にSheet1のA、B列とSheet2のA、B列を同じ表示にしたいということのように思えますが、違うのでしょうか?
仮にそうであるとして、
>(但し、Sheet2のA2/B2も関数を自動に反映させたい。)
の意味がよく分かりませんが、単純に、同じ行、列を参照するようにINDIRECT関数で設定しておけばよいように思います。
(INDIRECTでなく直接の参照設定だと、行の追加・削除を行った際に、エクセルがセル位置を調整しようとするのでうまくいかないかと…)
>常にSheet1/2を同じ表示にしたい・・・違うでしょうか?
そのとおりです。
>但し、Sheet2のA2/B2も関数を自動に反映させたい。
とういうのは、Sheet2のA列B列には既に関数が入っているため
挿入された場合も関数が反映されるという意味です。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBA 別sheetからの転記なのですが 2 2023/05/22 15:55
- Excel(エクセル) 【Excel質問】別シートにある複数の同型の表から、同じ行項目にある数字を集計する 4 2023/02/16 00:14
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Visual Basic(VBA) VBAでvlookup関数から、別シート参照するやり方・・・ 2 2022/11/14 18:49
- Visual Basic(VBA) 【変更】ファイルを閉じてダイアログで保存した時、更新したシートだけの処理の実行をする 5 2022/03/26 18:31
- Excel(エクセル) エクセルカレンダーに予定表を反映したいです。 6 2022/09/30 14:39
- Visual Basic(VBA) vbaのvlookup関数エラー原因を教えていただけないでしょうか。 3 2022/04/25 16:16
- Visual Basic(VBA) このプログラムなんですがsheetにデータを置いて表示できるようにしてありますがsheetに101を 2 2023/02/23 20:13
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Visual Basic(VBA) Sheet「状況」から、分類の年齢別カウント数をSheet「D表」へ転記する下記マクロを作っています 7 2022/12/14 17:57
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教える店舗&オフィスのセキュリティ対策術
中・小規模の店舗やオフィスのセキュリティセキュリティ対策について、プロにどう対策すべきか 何を注意すべきかを教えていただきました!
-
エクセルの参照行の挿入による追加と行の削除に対応できる関数を教えてくだ
Excel(エクセル)
-
[エクセル]行の挿入、シート間で連携できますか?
Excel(エクセル)
-
エクセルのシートを同時に行削除、行挿入させて自動連動させたい
Excel(エクセル)
-
-
4
Excelで行が挿入したときに、自動的に別シートにも挿入したい
Excel(エクセル)
-
5
エクセル。行を挿入すると、関数が反映されない
Windows 10
-
6
行、列の挿入がリンク先に反映できないでしょうか
Excel(エクセル)
-
7
エクセルでシートにリンクを貼り付けているのですが元シートに行を挿入した
Excel(エクセル)
-
8
シートを同期させるには?(行の削除を同期させるのは可能?)
Excel(エクセル)
-
9
excelで、セル内に文字が入力される毎に行が自動挿入される仕組みを作りたいのですが…
Excel(エクセル)
-
10
エクセルの参照元ブックで行の挿入を
Excel(エクセル)
-
11
エクセルで行挿入しても計算式がずれない方法
Excel(エクセル)
-
12
EXCEL 元データを更新したら別シートも更新
その他(Microsoft Office)
-
13
エクセルでの複数シートへの同時行挿入
その他(Microsoft Office)
-
14
行を削除しても関数の参照範囲がずれないようにしたい
Excel(エクセル)
-
15
条件付き書式で、結果が1行ずれる
Excel(エクセル)
-
16
Excelで計算式がずれるパターンとずれないパターンを教えて下さい(絶対参照の話ではない)
Excel(エクセル)
-
17
【EXCEL】数式を相対参照でコピーできない
Excel(エクセル)
-
18
SUMIF関数で、「ブランク以外を合計」を指定したい
その他(Microsoft Office)
-
19
【エクセル】行挿入で数式もいっしょにコピーしたい
Excel(エクセル)
-
20
Excel 条件によって入力禁止にする
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの関数について教えて...
-
Excelカスタム関数(アドイン登...
-
Excelデータをコピペして、ペー...
-
Excel関数-文字列で自動作成さ...
-
スプレッドシート、Excelでの数...
-
Excelで50個のセルに同じ文字を...
-
Microsoft Officeの中古は信用...
-
スプレッドシートで使う数式を...
-
エクセルVBA、別ブックへ転記す...
-
エクセルで会社の従業員のデー...
-
エクセルで不等号記号(≠)が上に...
-
エクセルの表で1年間の曜日を...
-
A列とB列を参照してC列に連番を...
-
エクセルの空欄をつめて、次の...
-
エクセルでの特別な文字を上に...
-
エクセルでセルに標準で入力さ...
-
エクセル日付 文字列の関数がエ...
-
エクセル2013で月間勤務表から...
-
エクセルの日付を編集する
-
EXCELの質問です 119から足した...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報