提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在Excel中使用/引用以前工作表中的值?

在Excel中創建副本時如何引用以前工作表中的特定單元格值? 例如,在創建Sheet2副本時,您可能需要自動將Sheet1的單元格A2引用到新復制的工作表中(稱為Sheet3)。 如何實現它? 本文將幫助你。

使用用戶自定義函數從以前的工作表中使用/引用值

使用Kutools for Excel從以前的工作表中使用/參考值


使用用戶自定義函數從以前的工作表中使用/引用值


說實話,創建工作表副本時,任何方法都不能自動引用特定的單元格值。 實際上,您可以創建一個新工作表,然後使用以下用戶定義函數引用先前工作表中的單元格值。

1。 創建新的空白工作表後(請參閱Sheet3),請按 其他 + F11 鍵同時打開 Microsoft Visual Basic for Applications 窗口。

2。 在裡面 Microsoft Visual Basic for Applications 窗口中,單擊 插入 > 模塊。 然後將以下VBA代碼複製並粘貼到代碼窗口中。

VBA:使用Excel中以前的工作表中的參考值

Function PrevSheet(RCell As Range)
    Dim xIndex As Long
    Application.Volatile
    xIndex = RCell.Worksheet.Index
    If xIndex > 1 Then _
        PrevSheet = Worksheets(xIndex - 1).Range(RCell.Address)
End Function

3。 按 其他 + Q 鍵同時關閉 Microsoft Visual Basic for Applications 窗口。

4。 選擇Sheet3的空白單元(稱為A1),輸入公式 = PrevSheet(A1)配方欄 然後按下 輸入 鍵。

現在您將在當前工作表中獲取前一張工作表(Sheet1)的A2單元格值。

注意:代碼將自動識別屬於當前工作表的前一個工作表的工作表。


使用Kutools for Excel從以前的工作表中使用/參考值

隨著 動態參考工作表 實用程序 Kutools for Excel,您可以輕鬆使用或引用Excel中以前的工作表中的值。

Kutools for Excel :與超過300方便的Excel加載項, 免費試用60天無限制.

1。 如果要將前一個工作表中的單元格A1的值引用到當前工作表,請在當前工作表中選擇單元格A1,然後單擊 Kutools > 更多 > 動態參考工作表。 看截圖:

2。 在裡面 填寫工作表參考 對話框中,只檢查以前的工作表名稱 工作表列表 框,然後單擊 填充範圍 按鈕。

然後,您可以看到當前工作表中引用了之前工作表中單元格A1的值。

注意:使用此實用程序,可以將不同工作表中的同一單元格值同時引用到當前工作表中。


使用Kutools for Excel從以前的工作表中使用/參考值

Kutools for Excel 包含了比300更方便的Excel工具。 免費試用60天無限制。 立即下載免費試用!



推薦的Excel生產力工具

Kutools for Excel幫助您提前完成工作,並從人群中脫穎而出

  • 超過300強大的高級功能,專為1500工作場景設計,通過70%提高生產力,讓您有更多時間照顧家庭和享受生活。
  • 不再需要記憶公式和VBA代碼,從現在起讓你的大腦休息一下。
  • 成為3分鐘的Excel專家,複雜和重複的操作可以在幾秒鐘內完成,
  • 每天減少成千上萬的鍵盤和鼠標操作,現在告別職業病。
  • 110,000高效人才和300 +世界知名公司的選擇。
  • 60-day full功能免費試用。 60天退款保證。 2多年的免費升級和支持。

將選項卡式瀏覽和編輯帶到Microsoft Office,遠比瀏覽器的選項卡強大

  • Office選項卡專為Word,Excel,PowerPoint和其他Office應用程序設計:Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Anon · 1 months ago
    I followed steps 1-4, but I keep getting a #NAME error. Excel 2016. Please advise?
    • To post as a guest, your comment is unpublished.
      crystal · 26 days ago
      Hi Anon,
      The code works well in my Excel 2016. Did you find specific error reminded in your code window when getting the #NAME error result?
  • To post as a guest, your comment is unpublished.
    Andrew · 1 months ago
    I followed steps 1-4 but I keep returning a #NAME? error - Please advise?
    • To post as a guest, your comment is unpublished.
      crystal · 1 months ago
      Hi Andrew,
      Which Office version do you use?
  • To post as a guest, your comment is unpublished.
    Alex · 6 months ago
    What about use value from Next Sheet?
    • To post as a guest, your comment is unpublished.
      crystal · 5 months ago
      Hi Alex,
      To get the value from next Sheet, please replace the minus sign with plus sign in the sixh line of the code:
      PrevSheet = Worksheets(xIndex + 1).Range(RCell.Address)
      • To post as a guest, your comment is unpublished.
        Mav · 1 months ago
        Hi Crystal, I tried replacing the minus sign with a plus sign but it didn't work - the relevant cell just returned a zero. I'm just wanting the change(s) required to do exactly as above BUT with the workbook numbering going from right to left, ie. Sheet3, Sheet 2, Sheet1 (the previous sheet will be the one to the right). Thanks
        • To post as a guest, your comment is unpublished.
          crystal · 1 months ago
          Hi Mav,
          The method works based on the worksheets' order in your workbook. ie. The worksheets order from left to right are Sheet3, Sheet2 and Sheet1. For Sheet2, it's previous worksheet is Sheet3, and it's next worksheet is Sheet1.
          • To post as a guest, your comment is unpublished.
            Mav · 1 months ago
            Hi Crystal,
            Yes I know. My query (like Alex's) is what change is required to make it work the other way, ie, for the next sheet, where the next sheet is to the left of the preceding sheet? You wrote to Alex,
            "please replace the minus sign with plus sign in the sixh line of the code:
            PrevSheet = Worksheets(xIndex + 1).Range(RCell.Address)".
            My comment was just to say that that particular change did not work and to ask if you have a way that will actually work.
  • To post as a guest, your comment is unpublished.
    Ryan · 1 years ago
    I've been looking for something to do this forever, thank you so much you have no idea. I have a simple spreadsheet for data entry weekly and a new tab for each week. the days, date, week #, etc goes +1 and then it grabs from previous sheet and moves to new one, this created a fast solution to data transfering to different sheets--- Again thank you
  • To post as a guest, your comment is unpublished.
    Steve Bannister · 2 years ago
    Excellent work and explanation. Saved me so much time, thank you VERY much.