人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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

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

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

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

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

●質問者: yoshifuku
●カテゴリ:コンピュータ
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● a-kuma3
●100ポイント

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列は、非表示にしておくと良いと思います。


2 ● nepia11
●100ポイント

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のプライバシー情報は消したほうがいいですよ。


3 ● kimuram
●100ポイント

やはりここは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(※これをきっかけに忘れていたグーグルドライブを使ってみました。うまく伝わるかな)

関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ