[[20180428065306]]　『参照を反転する』（onocho）　ページの最後に飛ぶ

『参照を反転する』（onocho）

参照の向きを反転する事はできるでしょうか？
イメージとしては、参照元のトレースの、矢印を逆向きにするイメージです。

今扱っているexelブックには50枚のシートがあります
先頭シートは一覧表、2枚目以降のシートは入力用シートです
1枚目の一覧表は、それ以降の各シートに入力されたデータを参照するように作られています
2枚目以降のシートはすべて形式がバラバラで、その中の連続していないデータを参照しています(参照は1000箇所に及びます)

このブックで、今

  ２枚目以降のシートに入力したデータを1枚目の一覧に反映する
となっている参照を
  1枚目の一覧に入力さしたデータを２枚目以降のシートに反映する

というように作り替えたいのですが、一括で行う方法はありますか？
私のスキルでは一つ一つ参照を設定し直すほかに思い付きません。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 具体例を挙げて貰えませんか。

 数式で「参照」していると言うことですよね？

 その数式は単純な参照式なんですか？
 例えば、=あるシート!A1　とか

 それとも、四則演算、関数が含まれているんですか？

 いずれにしても、そんなことが第三者に分かるような例をあげてください。

 ＞ 一つ一つ参照を設定し直すほかに思い付きません。
 具体例の中に、設定し直した「希望結果」も書いてください。

（半平太） 2018/04/28(土) 08:42

わかりづらくてすみません

例えばsheet2のc2や、sheet3のf4にデータが入っており、
sheet1のa1に「=sheet2!c2」
sheet1のa2に「=sheet3!f4」
のように参照されてるとします

これを、
sheet2のc2に「=sheet1!a1」
sheet3のf4に「=sheet1!a2」
のように、逆に参照するように変換したいです
（onocho） 2018/04/28(土) 10:39

  単純な数式だと仮定します。
  それ以外の数式は無いものとします。（あったらトラブりますよ）

  さて、反転が成功すると循環参照になるので、一覧表の数式は全てクリアすることにしますよ。

  元に戻せなくなるので、テストは、コピーしたブックで行って下さい（必ずそうしてください）

 Sub reverseRef()
     Const Wsh As String = "一覧表"
     Dim rngDstn As Range
     Dim Cel As Range
     Dim Pos As Long
     Dim Temp
     Dim putBack()

     Set rngDstn = Sheets(Wsh).Cells.SpecialCells(xlCellTypeFormulas, 23)
     ReDim putBack(1 To rngDstn.Cells.Count) '数式の入れ物を確保

     For Each Cel In rngDstn
         Pos = Pos + 1
         putBack(Pos) = Wsh & "!" & Cel.Address(0, 0) & Cel.Formula
     Next

     rngDstn.ClearContents  '循環参照を回避するため、元の数式はクリアする

     For Pos = 1 To UBound(putBack)
         Temp = Split(putBack(Pos), "=")
         Application.Range(Temp(1)).Formula = "=" & Temp(0)
     Next Pos
 End Sub

（半平太） 2018/04/28(土) 11:03

 横から失礼します。

      For Each Cel In rngDstn
          Pos = Pos + 1
          putBack(Pos) = Wsh & "!" & Cel.Address(0, 0) & Cel.Formula
      Next
      rngDstn.ClearContents  '循環参照を回避するため、元の数式はクリアする
 のところは、
      For Each Cel In rngDstn
          Pos = Pos + 1
          putBack(Pos) = Wsh & "!" & Cel.Address(0, 0) & Cel.Formula
          Cel.Value= Cel.Value
      Next
 とでもしておかないと、ブック上から値が消えてしまう気がします。
 （バックアップは取って下さいと書かれているので、よもや心配はないと思いますが）

 ところで、集計表としての機能は捨てることになりますが、
 質問者さんは、それはそういうものだと了解されているのですね？

（γ） 2018/04/28(土) 12:43

別シートを参照の場合のみにしました

 Option Explicit

 Sub test()
    Dim rr As Range, r As Range
    Dim f As String

    Set rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)

    For Each r In rr
        f = r.Formula
        If InStr(f, "!") > 0 Then
            r.Value = r.Value
            Range(Mid(f, 2)).Formula = "=" & r.Address(, , , True)
        End If
    Next

 End Sub

（マナ） 2018/04/28(土) 13:04

できました！
ありがとうございます

読んでみればなるほどと思うのですが、これを短時間で思い付く発想がすごいです！
本当に勉強になりました
ありがとうございました
（onocho） 2018/04/28(土) 23:37

コメント返信：

[ 一覧(最新更新順) ]

[[20180428065306]] 『参照を反転する』（onocho） ページの最後に飛ぶ

[[20180428065306]]　『参照を反転する』（onocho）　ページの最後に飛ぶ