Excelで、異シート間のセルのリンクを簡易に貼る方法をご相談します。


今、こちらにExcelのファイルがあります。
https://drive.google.com/open?id=0BzkvwuFCJtLEclRtbm9FUlh2ckE

シート「帳票元」のオレンジのシートに、シート「帳票0」のセルへのリンクを貼ります。
3行程度リンクを貼ってみましたが、312行程度あり、なかなか1個1個やっては大変です。

・一度にうまくコピーする方法がある
・VBAを駆使する
等、アドバイスいただければ幸いです。

どうぞよろしくお願い致します。

回答の条件
  • 1人1回まで
  • 13歳以上
  • 登録:2017/06/29 20:19:55
  • 終了:2017/07/06 20:20:05

回答(3件)

id:a-kuma3 No.1

a-kuma3回答回数4558ベストアンサー獲得回数19032017/06/29 20:46:03

ポイント100pt

HYPERLINK 関数というのがあります。

Google Drive の Excel のファイルは見ていないのですが、「帳票元」の A2 セルに、以下のように数式を書くと、「帳票0」シートの A2 セルへジャンプするハイパーリンクになります。

=HYPERLINK("#帳票0!A2")

ただ、これを複写しても数字が変わるわけではないので、まず使わない列に #帳票0!A2 の文字を書いて、オートフィルで下方向にコピーします。
そして、A2 のセルに、その文字列をパラメータに取る HYPERLINK 関数を書いて、下方向にコピーです。

|   |       A       |   B    |    C    |  D   |
| 1 |               |    |         |   |
| 2 |=HYPERLINK(C2) |    |#帳票0!a1| … |
| 3 |  ↓にコピー   |    |#帳票0!a2|   |
| 4 |               |    |#帳票0!a3|   |
| 5 |               |    |#帳票0!a4|   |
| 6 |               |    |#帳票0!a5|   |
| 6 |               |    |#帳票0!a5|   |

不必要な C列は、非表示にしておくと良いと思います。

id:nepia11 No.2

nepia11回答回数290ベストアンサー獲得回数512017/06/30 12:51:43

ポイント100pt

index関数がいいと思います。
index(対象範囲,行番号,列番号)で戻ってきます。

1行目に行を挿入して、
ルート概要の上に「3」、スパンの上「7」、設備種別「8」・・・・・と入力。
これで行番号を指定します。

列番号の指定は、A-C列を新たに挿入して、
A5:ルート概要はAG列なので「33」
B5:番号はAH列なので「34」
C5:その他はAE列なので「31」
すべて4列ずつずれるので、A6に「=A5+4」と入力して、
A6:C314でコピペ。
これで列番号の指定ができます。

あとは、
E5(2ルート概要)に
=INDEX(帳票0!$1:$1048576,帳票元!E$1,帳票元!$A5)
H5(2ルート番号)に
=INDEX(帳票0!$1:$1048576,帳票元!H$1,帳票元!$B5)
F5(2スパン)に
=INDEX(帳票0!$1:$1048576,帳票元!F$1,帳票元!$C5)
を入力。
F5をコピーして、
G5とI5:N5に貼付け。

その後にE5:N5を314行目まで貼り付ければ完了です。

最後に、Excelのプライバシー情報は消したほうがいいですよ。

id:kimuram No.3

kimuram回答回数12ベストアンサー獲得回数42017/07/05 09:38:42

ポイント100pt

やはりここはVBAかなと、どんなものか作ってみました。
以下に、コード内容の掲示と、それからExcel結果ファイルの添付をグーグルドライブでしておきます。

補足です。コードを見ていただければわかりますが。
・データの3行目をベースにして、このリンク内容を元に以降の行に変更して設定している。
・データの312行目まで一律処理をしてから、312行目のB列とE列はクリアを実施。(影響ないところかと。ロジック簡便化も)
※起動ボタンを付けようかと思ったけど、繰り返し実行するようなものでもないだろうし、手動でのマクロ起動で行えばよいかと。シート「帳票元」を表示した状態で、マクロ「linkSet」を起動してください。
※データ3行目の内容を元にし、行進む毎に参照先の列を4進める機能だけで、参照先のシート名などはロジックでは意識していない。3行目の元の設定内容次第でロジック変更無しで運用できる。しかし、モデル行としての3行目は手作業で作成する必要があります。また後から思ったのですが、このモデル行を2行目にすることで手作業を軽減することも考えられる。そのときはロジックの定数を一部変更してください。  

サンプルコード


Sub linkSet()
'Dim shRef As Worksheet
Dim shSet As Worksheet
Dim rowSet
Dim colSet
Dim wkFormula
Dim wkNextAddr
Dim wkNextCol
Dim wkNewFormula
Dim bgnTime, endTime

'Set shRef = Sheets("帳票0")
Set shSet = ActiveSheet 'Sheets("帳票元")
bgnTime = Time()
For rowSet = 1 + 4 To 1 + 312
For colSet = 2 To 11
wkFormula = shSet.Cells(rowSet - 1, colSet).Formula 'ex[=帳票0!$AK$3]
p1 = InStr(1, wkFormula, "$")
p2 = InStr(p1 + 1, wkFormula, "$")
wkcolstr = Mid(wkFormula, p1 + 1, p2 - p1 - 1) 'ex[AK]
'
'参照先カラムを4進める
wkNextAddr = Range(wkcolstr & 1).Offset(0, 4).Address 'ex[$AO$1]
p3 = InStr(2, wkNextAddr, "$")
wkNextCol = Mid(wkNextAddr, 2, p3 - 2)
wkNewFormula = Left(wkFormula, p1) & wkNextCol & Mid(wkFormula, p2) 'ex[=帳票0!$AO$3]
'Stop
'新しい式をセットする
Cells(rowSet, colSet).Formula = wkNewFormula
'Stop
Next colSet
Next rowSet
endTime = Time()
'余分なリンクを削除する
'Stop
Range("B" & 1 + 312).ClearContents
Range("E" & 1 + 312).ClearContents


MsgBox "処理終了!" & vbCrLf & Format(endTime - bgnTime, "h:m:s")
End Sub



サンプル結果ファイル:
https://drive.google.com/open?id=0B-CF2dLT_eveRlprTVF5bllfdnc(※これをきっかけに忘れていたグーグルドライブを使ってみました。うまく伝わるかな)

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません