可以在不同的表格中模拟excel行吗？

问题描述

所以在Sheet 1中，我有2000行信息​​，我每天添加一个新列，我想在Sheet 2中只有一个Sheet 1行的一部分，它自己更新为将新列放入Sheet 1。是否可以使用内置的excel函数？

So in 'Sheet 1' I have 2000 rows of information which I add a new column to every day, I want to have only a subset of the rows of 'Sheet 1' in 'Sheet 2' which update themselves as I put new columns into 'Sheet 1'. Is this possible to do using inbuilt excel functions?

推荐答案

如果要选择一组特定行，并且您知道其行数字，将它们的行号（1,5,8,11,12 ...）放在单独的1列范围内并命名此范围，例如RowNumbers

If you want to select a set of particular rows and you know their row numbers, put their row numbers (1,5,8,11,12 ...) in a separate 1 column range and name this range e.g. "RowNumbers"

然后使用以下正常的非数组公式，您可以向下和向右拖动。

Then use following normal non-array formula which you can drag downwards and rightwards.

= IF(INDEX(RowNumbers,ROW(A1)+1)>0, INDEX(NamedRange,INDEX(RowNumbers,ROW(A1)+1),COLUMN(B1)+1),"")

可能需要根据您的值从第1行或第2行开始调整 +1 部分。

You will probably need to adjust the +1 parts, depending on whether your values start at row 1 or 2 etc.

基本上，Excel INDEX 公式会根据您需要的 - 根据给定的行和列号将值从另一个工作表或范围复制。

Basically the Excel INDEX formula does what you need - copies the value from another sheet or range by given row and column numbers.

否则，您可以使用以下数组公式（Ctrl + Shift + Enter）根据多个条件从列中选择过滤的值：

Otherwise you can use following array formula (Ctrl+Shift+Enter) to select filtered values from columns based on multiple criteria:

 = IFERROR(INDEX(NamedRange,
   SMALL(IF((INDEX(NamedRange,,1,1)=1)*(INDEX(NamedRange,,2,1)="A"),
   ROW(NamedRange)-MIN(ROW(NamedRange))+1,""),
   ROWS(C1:$C$1)),3,1),"")

这是一个示例数据表（Sheet1）

Here is an example data sheet (Sheet1)

这是过滤的数据输出表。您应该在第一行中输入数组公式并向下拖动以填充预期的输出范围。在本例中，我只选择具有值 1 和 A ind filter1 和 filter2 列。

Here is the filtered data output sheet. You should enter the array formula in the first row and drag downwards to fill expected output range. In this example I select only rowns that have values 1 and A ind filter1 and filter2 columns.

这篇关于可以在不同的表格中模拟excel行吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！