ペースト入力で時計算式の入っているセルを保護したい

質問

計測値を表にするために Excelを使用しています。
例えば、

10　タブ　20　タブ　タブ　30

上記のようなテキストをペーストして Excelの表を埋めています。
タブをペーストすると次のセルに進んでくれます。
上記のタブ２つはセルを１つスキップさせたいからなのですが、
Excelに計算式が書かれていてもタブをペーストすると計算式も消えてしまいます。

入力規制とかセルの保護とかも考えたのですが、それだとエラーメッセージが出てペースト（入力）自体が無効になってしまいました。

やりたいことはテキストのデータを Excelの表に埋めたいのですが計算式のあるセルは計算式を残したまま飛ばしたいことです。

何か良い方法をご存じの方がいらっしゃったらお教え下さい。
メンテを考えて極力シンプルに制限できたらなと考えています。

よろしくお願いいたします。

回答No.3

MackyNo1 · ベストアンサー

ご希望のコピー貼り付けをするなら、以下のような手順が実戦的に最も簡便な操作になります。

タブを含むテキスト文書をいったんエクセルのシート（データのない編集用のセル）に貼り付け、そのまま右クリック「コピー」、貼り付け先のセルを選択して、右クリック「形式を選択して貼り付け」で「空白セルを無視する」にチェックを入れてOKしてください。

お礼

一番シンプルでメンテナンスのかからない手法としてはこれがいいのかも知れないなと思いました。
「形式を選択して貼り付け」は私も多用しているのですがショートカットできると一番楽ですね。
「形式を選択して貼り付け」をツールバーに出しておいて１クッションおくのがいいかなと思いました。

シンプルな提案ありがとうございました。ペコ

回答No.4

nayuta_lot

こんにちは

ちょっと変わったところで次のようなやり方はどうでしょう。

１．数式の入ったシート（本表）を作成する。
２．本表と同一形式の入力用のべた貼りシートを作成する。
３．統合を利用して本表に転記する。

統合を使用すると計算式はそのまま残ります。　保護はいりません。
本来統合は、複数の範囲を合計したり、平均をとったりするものですが、
統合元を一つにして、合計をとれば、転記として活用できます。

また、マクロの記録で作業を記録して、ボタンに貼り付けておけば、
ボタンを押すだけで済みます。

例

下記のようなデータの構成になっていたとします。

＜テキストデータ＞
10　タブ　20　タブ　タブ 30
10　タブ　30　タブ　タブ 40
10　タブ　40　タブ　タブ 50
10　タブ　50　タブ　タブ 60
10　タブ　60　タブ　タブ 70

全て選択してデータシートに貼り付けます。

　　　↓↓↓

＜データシート＞

　 　A　 B　 C　 D
1　10　20　空　30
2　10　30　空　40
3　10　40　空　50
4　10　50　空　60
5　10　60　空　70

本表のシートに統合します。

1.本表のA1からD5を範囲選択します。

2.メニュー　→　データ　→　統合を選択します。

3.統合ウィンドウが開きますので、集計の方法が合計になっている
　ことを確認します。　なっていなければ合計にします。

4.統合元範囲にデータシートのA1からD5を選択します。　→　データ!$A$1:$D$5
　この時、データの選択範囲は統合元と同じ範囲を選択します。

5.統合の基準（上端、左端）　統合元データとリンクする　のチェックをはずします。
　統合の基準は、統合元フォーマットと統合先フォーマットが同一なら上端、左端の
　基準がなくても（チェックをはずしても）大丈夫です。

6.OKを押します。

数式以外の個所に統合元範囲のデータが合計されます。
統合元範囲はひとつですので事実上、転記と変わりません。

　　　↓↓↓

＜本表シート＞

　 　A　 B　 C　 D
1　10　20　式　30
2　10　30　式　40
3　10　40　式　50
4　10　50　式　60
5　10　60　式　70

上記の作業を、本表シートを選択するところからマクロで記録すると

Sub Macro1()

Sheets("本表").Select
Range("A1:D5").Select
Selection.Consolidate Sources:="'E:\データ\[sample.xlsm]データ'!R1C1:R5C4", _
Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False
End Sub

のようになります。　このマクロをデータシートにボタンを作ってマクロを登録すれば

テキストデータをデータシートにコピー
ボタンをクリック

の手順で本表に転記できます。

VBAをどの程度ご存じかわかりませんが、一応解説しておきます。

Sheets("本表").Select　・・・　本表のシートを選択
Range("A1:D5").Select　・・・　A1からD5を選択（統合先の範囲）

Selection.Consolidate　・・・　統合の意味
Sources:="'E:\データ\[sample.xlsm]データ'!R1C1:R5C4", 　・・・　統合元のセル範囲を
ファイルのパスから指定、R1C1:R5C4はA1:D5と同じ意味

　　以下略・・

実際に作成した表を元に統合を記録すれば、細かい記述はしなくてよいので、
なんとなく、この辺で指定しているのがわかればよいと思います。

統合の本来の使用方法については下記あたりを参照してください。
　　　　　　　↓↓↓
http://www.eurus.dti.ne.jp/~yoneyama/Excel/tougou.html

なにかあれば補足してください。
それでは

お礼

統合のことは知識がなかったです。
良い提案を誠にありがとうございます。
補足なども促していただき本当にありがとうございます。ペコ

レイアウトの変更が頻繁な場合、表自体の信頼性を保つためのテストなども考えるとかなり辛いのはＶＢＡと替わりがない気がしますが・・・
ＶＢＡよりはシンプルだなとは思いました。
本来セルをスキップさせる機能があれば良いだけなのにここまで複雑化してしまうのは悲しいことですね。

また回答者様の提案を読んで統合を学びたいなと思いました。

お時間のかかる詳細な回答を誠にありがとうございました。ペコ

回答No.2

imogasi

VBAぐらいしか思いつかない。VBAは質問者にとって想定外かもしれないが
（式があれば貼り付けを飛ばす、エクセルの用意された仕組みは無いのでは）。
例データ
テキストデータ　メモ帳で作成・保存。
1 23 - 32
2 21 - 45
3 45 - 12
4 31 - 45
1+tab+23+tab+tab+32+ENTERのようなデータが各行に入っている。
Sheet1のA1:D4
1 2 3 11
2 3 5 23
3 5 8 41
4 5 9 25
C列には=A1+B1のような式が入っていて、C列各行は現状データで計算されている。
A,B,D列は空白でもかまわない。
ーー
標準モジュールに
Sub test01()
i = 1 'シートとの1行目をポイント
Open "tabtest.txt" For Input As #1 'タブ入りテキストファイルを開く
While Not EOF(1)
Line Input #1, a '1行読み込み
MsgBox a
x = Split(a, Chr(9)) 'タブで分割
For j = 0 To UBound(x)
If x(j) = "" Then '空白ならシートの隣列へ
If Cells(i, j + 1).HasFormula Then
'MsgBox j
Else
MsgBox "式が無いがデータは飛んでいる 注意"
End If
Else
'MsgBox x(j)
Cells(i, j + 1) = x(j) 'i行J列セルへデータをセット
End If
Next j
i = i + 1
Wend
Close #1
End Sub
これを実行すると
Sheet1
A1:D4
1 23 24 32
2 21 23 45
3 45 48 12
4 31 35 45
シートのC列の式は壊れず、もと入っていた式として働いている。
ーー
本来はシートにデータをセットしてから、別途式を設定すれば、仕舞い（簡単）かと思うが。

お礼

短時間に詳細な回答を誠にありがとうございます。ペコ
ＶＢＡやマクロを組めばほとんどのことは可能かと思います、１つの解ではありますね。

＞本来はシートにデータをセットしてから、別途式を設定すれば、仕舞い（簡単）かと思うが。

確かに今まではこのやり方です。
ペーストすると式が消えるので他の計算式の消えていないセルからコピーしなおす。
ただこれだと事故の元なので。
計算式がＣ，Ｅ，Ｇ列に設定されているとメンテを考えてどうしてもＥＦＧ列というレイアウトにして計測結果がわかりづらくなるという循環になりますし・・・。

ただ提出まで考えると大がかりにＶＢＡで組むより現実的なのかも知れません。

＞（式があれば貼り付けを飛ばす、エクセルの用意された仕組みは無いのでは）。

・タブの替わりに別の入力値でセルそのものを上書きではなくスキップさせる
・Excelのオプションでタブで上書きしないように設定する
・入力規制やセルの保護でエラーのでなかったセルは有効にする設定が出来る

などがあれば良かったのですが。

短時間にＶＢＡまのソースまで提供していただいてありがとうございました。

回答No.1

noyuo

安直な方法ですが、同じレイアウトのシートを作って、
・入力（ペースト）用と、
・計算用（値は、もちろん入力用シートの同じアドレスのセルを参照するようにする）

にするのが、後々ややこしくないかもしれませんね。

お礼

早速の回答ありがとうございました。
レイアウトは結構変わるため、メンテを考えると多重化はあまりやりたくないところです・・・。
同じレイアウトをコピーするのは３０秒ですが、参照部分を張り替えて入力結果が正しく最後まで反映されていくかをトレースするのはかなり手間がかかりますので・・・。