如何使用excel公式引用从一个工作表到另一个工作表的单元格范围?

I have a single worksheet with sheets Sheet1 and Sheet2 and I am trying to reference a range of cells from Sheet2 to Sheet1

我有一个包含薄板1和薄板2的工作表，我正在尝试引用从薄板2到薄板1的一系列单元格

I know how to reference worksheet cells such as =Sheet2!A1 but how can I do the same for a cell range such as A1:F1 I tried =Sheet2!A1:F1 but it does not like the syntax.

我知道如何引用工作表单元格，比如=Sheet2!但是对于一个单元格范围，比如A1:F1，我尝试过=Sheet2，我怎么做呢?A1:F1，但是它不喜欢语法。

I need to use Excel Formulas for this if possible.

如果可能的话，我需要使用Excel公式。

8 个解决方案

#1

Simple ---

简单- - - - - -

I have created a Sheet 2 with 4 cells and Sheet 1 with a single Cell with a Formula:

我已经创建了一个有4个单元格的表2和一个有公式的单单元格的表1:

=SUM(Sheet2!B3:E3)

Note, trying as you stated, it does not make sense to assign a Single Cell a value from a range. Send it to a Formula that uses a range to do something with it.

注意，如您所述，从一个范围为单个单元分配值是没有意义的。将它发送到使用范围来处理它的公式。

#2

Ok Got it, I downloaded a custom concatenation function and then just referenced its cells

好了,我下载了一个自定义连接函数,然后引用它的细胞

Code

代码

    Function concat(useThis As Range, Optional delim As String) As String
 ' this function will concatenate a range of cells and return one string
 ' useful when you have a rather large range of cells that you need to add up
 Dim retVal, dlm As String
 retVal = ""
 If delim = Null Then
 dlm = ""
 Else
 dlm = delim
 End If
 For Each cell In useThis
 if cstr(cell.value)<>"" and cstr(cell.value)<>" " then
 retVal = retVal & cstr(cell.Value) & dlm
 end if
 Next
 If dlm <> "" Then
 retVal = Left(retVal, Len(retVal) - Len(dlm))
 End If
 concat = retVal
 End Function

#3

If you wish to concatenate multiple cells from different sheets, and you also want to add a delimiter between the content of each cell, the most straightforward way to do it is:

如果您希望连接来自不同表的多个单元格，并且还想在每个单元格的内容之间添加一个分隔符，最直接的方法是:

=CONCATENATE(Sheet1!A4, ", ", Sheet2!A5)

This works only for a limited number of referenced cells, but it is fast if you have only of few of these cells that you want to map.

这只能为有限数量的引用的细胞,但它很快如果你只有几个要映射的这些细胞。

#4

You can put an equal formula, then copy it so reference the whole range (one cell goes into one cell)

你可以用一个相等的公式，然后复制它来引用整个范围(一个细胞进入一个细胞)

=Sheet2!A1

If you need to concatenate the results, you'll need a longer formula, or a user-defined function (i.e. macro).

如果需要连接结果，则需要更长的公式或用户定义的函数(即宏)。

=Sheet2!A1&Sheet2!B1&Sheet2!C1&Sheet2!D1&Sheet2!E1&Sheet2!F1

#5

I rewrote the code provided by Ninja2k because I didn't like that it looped through cells. For future reference here's a version using arrays instead which works noticeably faster over lots of ranges but has the same result:

我改写Ninja2k所提供的代码,因为我不喜欢,它通过细胞循环。备查的版本使用数组而不是工作明显快了很多范围,但相同的结果:

Function concat2(useThis As Range, Optional delim As String) As String
    Dim tempValues
    Dim tempString
    Dim numValues As Long
    Dim i As Long, j As Long
    tempValues = useThis
    numValues = UBound(tempValues) * UBound(tempValues, 2)
    ReDim values(1 To numValues)
    For i = UBound(tempValues) To LBound(tempValues) Step -1
        For j = UBound(tempValues, 2) To LBound(tempValues, 2) Step -1
            values(numValues) = tempValues(i, j)
            numValues = numValues - 1
        Next j
    Next i
    concat2 = Join(values, delim)
End Function

I can't help but think there's definitely a better way...

我忍不住认为肯定有更好的办法……

Here are steps to do it manually without VBA which only works with 1d arrays and makes static values instead of retaining the references:

以下是在没有VBA的情况下手动操作的步骤，VBA只对1d数组有效，并且会生成静态值，而不会保留引用:

Update cell formula to something like =Sheet2!A1:A15
将单元格公式更新为=Sheet2!A1:A15
Hit F9
打击F9
Remove the curly braces { and }
删除花括号{和}
Place CONCATENATE( at the front of the formula after the = sign and ) at the end of the formula.
将CONCATENATE(位于公式的前面，在=符号之后)置于公式的末尾。
Hit enter.
回车。

#6

If these worksheets reside in the same workbook, a simple solution would be to name the range, and have the formula refer to the named range. To name a range, select it, right click, and provide it with a meaningful name with Workbook scope.

如果这些工作表位于同一工作簿,一个简单的解决方案是名字的范围,和公式引用命名范围。范围名称,选择它,右击,并提供一个有意义的名字与工作簿的范围。

For example =Sheet1!$A$1:$F$1 could be named: theNamedRange. Then your formula on Sheet2! could refer to it in your formula like this: =SUM(theNamedRange).

例如= Sheet1 !1美元:F $ 1美元可以命名:theNamedRange。那么你的公式Sheet2 !可以把它在你的公式:=(theNamedRange)。

Incidentally, it is not clear from your question how you meant to use the range. If you put what you had in a formula (e.g., =SUM(Sheet1!A1:F1)) it will work, you simply need to insert that range argument in a formula. Excel does not resolve the range reference without a related formula because it does not know what you want to do with it.

顺便说一句，你的问题并不清楚你打算如何使用范围。如果你在一个公式(例如，=SUM(Sheet1!A1:F1)中输入你的值，它就会工作，你只需要在一个公式中插入这个范围参数。Excel没有相关公式就不能解析范围引用，因为它不知道您想用它做什么。

Of the two methods, I find the named range convention is easier to work with.

的两种方法,我发现命名约定范围更容易处理。

#7

Its quite simple but not easy to discover --- Go here to read more. its from the official microsoft website

这很简单，但不容易发现---去这里读更多。它来自微软官方网站

Step 1 - Click the cell or range of the source sheet (that contains the data you want to link to)

步骤1 -单击源表的单元格或范围(包含要链接到的数据)

Step 2 Press Ctrl+C, or go to the Home tab, and in the Clipboard group, click Copy Button image .

第二步按Ctrl + C,或者去标签,和剪贴板组,点击复制按钮图像。

Step 3 Clipboard group on the Home tab

步骤3剪贴板组在主标签

Step 4 Press Ctrl+V, or go to the Home tab, in the Clipboard group, click Paste Link Button. By default, the Paste Options Button image button appears when you paste copied data.

第四步按Ctrl + V,或者去标签,剪贴板组,单击粘贴链接按钮。默认情况下,当你粘贴复制粘贴选项按钮图像按钮出现数据。

Step 5 Click the Paste Options button, and then click Paste Link .

步骤5点击粘贴选项按钮，然后点击粘贴链接。

#8

The formula that you have is fine. But, after entering it, you need to hit Control + Shift + Enter in order to apply it to the range of values. Specifically:

的公式很好。但是,进入之后,您需要按下ctrl + Shift + Enter以应用于值的范围。具体地说:

Select the range of values in the destination sheet.

选择目标表中的值范围。
Enter into the formula panel your desired formula, e.g. =Sheet2!A1:F1

在公式面板中输入你想要的公式，例如=Sheet2!A1:F1
Hit Control + Shift + Enter to apply the formula to the range.

点击控制+ Shift + Enter将公式应用到范围。

#1