如何引用另一个工作表中的数据表列？

发布于 2024-12-12 01:05:45 字数 819 浏览 0 评论 0原文

Microsoft Excel 2010 -
我有两个工作表，Sheet1 和 Sheet2。
两个工作表上都有数据表，涵盖不同类型的数据 - 如下所示。

<Worksheet "Sheet1">
    <DataTable "Table1">
        <Columns>
            <Column "T1Col1" />
            <Column "T1Col2" />
        </Columns
    </DataTable>
</Worksheet>

<Worksheet "Sheet2">
    <DataTable "Table2">
        <Columns>
            <Column "T2Col1" />
            <Column "T2Col2" />
        </Columns
    </DataTable>
</Worksheet>

目前，T2Col2 中的每个单元格都包含这样的函数：

=COUNTIF('Sheet1'!B:B, [T2Col1])

我想将 "B:B" 替换为对 [T1Col2]< 的括号列引用/strong>，但我不确定如何在单独的表和工作表之间完成此操作。

原文

Microsoft Excel 2010 -
I have two worksheets, Sheet1 and Sheet2.
Both worksheets have data-tables on them, covering differents types of data - illustrated below.

<Worksheet "Sheet1">
    <DataTable "Table1">
        <Columns>
            <Column "T1Col1" />
            <Column "T1Col2" />
        </Columns
    </DataTable>
</Worksheet>

<Worksheet "Sheet2">
    <DataTable "Table2">
        <Columns>
            <Column "T2Col1" />
            <Column "T2Col2" />
        </Columns
    </DataTable>
</Worksheet>

Currently, every cell in T2Col2 contains a function like this:

=COUNTIF('Sheet1'!B:B, [T2Col1])

I'd like to replace "B:B" with a bracketed column reference to [T1Col2], but I am uncertain how to accomplish this between seperate tables and worksheets.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

别闹i 2024-12-19 01:05:45

有几种方法。最简单的方法是选择表格的相关部分，例如 B2:B100，假设您的表格有标题并且数据位于第 2 行到第 100 行中。Excel 将自动插入类似以下内容：

TableOnSheet1[DataToCount]

以便整个公式如下所示

=COUNTIF(TableOnSheet1[DataToCount], [T2Col1])

：表名称，后跟括号中的列标题。

您可以而且应该为您的表命名。为此，请单击“设计”选项卡并选择表格，然后在最左侧的“表格名称”框中对其进行编辑。

您还可以使用自动填充表名称来编写公式，例如，如果您键入 = "=COUNTIF(Table" Excel Intellisense 将提供“Table1”、“Table2”以及工作簿中的任何其他表。

There are a couple of ways. The easiest is to just select the relevant part of the table e.g., B2:B100, assuming your table has headings and the data is in rows 2 through 100. Excel will automatically insert somthing like:

TableOnSheet1[DataToCount]

so that your whole formula looks like:

=COUNTIF(TableOnSheet1[DataToCount], [T2Col1])

Which is the table name followed by the column header in brackets.

You can, and should, name your tables. Do so by clicking on the Design tab with the table selected and then editing it in the Table Name box on the far left.

You can also write your formulas using autofill for the table name, e.g., if you type = "=COUNTIF(Table" Excel Intellisense will offfer "Table1," "Table2," and any other tables in the workbook.

回复收藏 0 原文

~没有更多了~