如何使用自动更新公式而无需拖动 [英] How to use automatically update formula without dragging

问题描述

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设置为拉"?在添加行中的列中连续的公式?

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

解决方案

使用

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!!)

解决方案

Use the spilled range operator # to reference the entire spilled range:

=COUNTIF(Sheet1!$A$2:$B$15,B1#)

这篇关于如何使用自动更新公式而无需拖动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！