动态卓越报告-添加新行时，使Excel公式自动添加自身 [英] DYNAMIC EXCEL REPORT - Make Excel Formula automatically add itself when new rows added

问题描述

Sheet1中有一张表格，看起来像这样

  **体育**篮球篮球篮球排球排球足球足球足球足球足球足球曲棍球曲棍球曲棍球 

Sheet2中有一个表格，如下所示:

  SPORT篮球排球足球曲棍球分数3 2 6 3 

我在B1中应用了以下公式:= TRANSPOSE(UNIQUE(FILTER(Sheet1！$ A $ 2:$ A $ 15，Sheet1！$ A $ 2:$ A $ 15<");)))

B2中的

公式:= COUNTIF(Sheet1！$ A $ 2:$ B $ 15，Sheet2！B1)

但是，在Sheet1中的列被更新时.例如，将曲棍球字段之一更改为"Golf"，这将在HEADER中进行更新，但是下面的公式和格式不会随身携带，而无需实际拖动.

  SPORT篮球排球足球曲棍球高尔夫分数3 2 6 3 

您可以看到Gold的分数是空的....我需要自动填写该分数.有没有一种方法可以让我自动将Excel设置为拉"?在添加行中的列中连续的公式?

(简化的数据和公式，易于理解！)

解决方案

根据我对问题的理解，听起来可以结合使用 = ARRAY_CONSTRAIN 和 = ARRAYFORMULA 命令. = ARRAYFORMULA 允许沿着数组迭代公式或值-因此，一个公式使用与单个单元格相反的值填充范围. = ARRAY_CONSTRAIN 可用于限制此新数组的长度(例如，它不会添加一长串零).

下面是我想出的一个例子.在这里，我们根据您的

可以根据需要将此公式向下拖动到C列的长度.该单个公式填充了行的整个长度，并受行4的长度的限制.(无论您需要什么功能，都可以替换(B5 * $ C $ 4:$ N $ 4)code> ARRAYFORMULA .)

您可以看到，当我添加新数字时，数据会自动更新.

注意:此解决方案可在我误认为您正在使用的Google表格中使用.但是，经过仔细检查，您似乎正在使用Microsoft excel，这可能需要一些其他解决方案.我将尽力为这两个问题提供答案.

I have a table in Sheet1 that looks like this

**Sport**
Basketball
Basketball
Basketball
Volleyball
Volleyball
Football
Football
Football
Football
Football
Football
Hockey
Hockey
Hockey

I have a table in Sheet2 that looks like:

SPORT   Basketball  Volleyball  Football    Hockey
SCORE       3           2          6           3

I applied the following formula in B1: =TRANSPOSE(UNIQUE(FILTER(Sheet1!$A$2:$A$15,Sheet1!$A$2:$A$15<>"")))

formula in B2: =COUNTIF(Sheet1!$A$2:$B$15,Sheet2!B1)

However when the column in Sheet1 is updated. For example, changing one of the hockey fields to Golf, this is updated in the HEADER but the formula and formatting below is not carried across WITHOUT having to physically drag it across.

SPORT   Basketball  Volleyball  Football    Hockey    Golf
SCORE       3           2          6           3

As you can see the score for Gold is empty.... I need this to be filled automatically. Is there a way that I can have excel automatically "pull" the formula that is contiguous in the column into the added row?

(Simplified data and formula for ease of understanding!!)

解决方案

Based on my understanding of the question, it sounds like the effect you want can be replicated using a combination of the =ARRAY_CONSTRAIN and =ARRAYFORMULA commands. The =ARRAYFORMULA allows for formulas or values to be iterated along an array--so one formula fills in a range with values as opposed to a single cell. =ARRAY_CONSTRAIN can be used to restrict the length of this new array (so it doesn't add a long row of zeros, for example).

Below is an example I came up with. Here we have the columns being transposed as the header based on your earlier question so as I add values in column A they are being transposed as the header in row 4. Using a new formula down the length of column C, we can populate the entire row of new data:

=ARRAY_CONSTRAIN(ARRAYFORMULA(B5*$C$4:$N$4),1,COUNT(C4:N4))

This formula can be dragged down the length of column C however long you like. This single formula populates the entire length of the row and is limited by the length of row 4. (Whatever function you need can be replace the (B5*$C$4:$N$4) inside the ARRAYFORMULA.)

You can see that as I add new numbers the data updates automatically.

NOTE: This solution works in Google Sheets which I mistakenly believed you were using. However, it appears on closer inspection that you are using Microsoft excel which might require a bit of a different solution. I will try to provide answers to both soon.

这篇关于动态卓越报告-添加新行时，使Excel公式自动添加自身的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！