Excel-计算特定范围内符合特定条件的不同工作表中的项目数 [英] Excel - Counting no of items in a different sheet between a certain range matching specific criteria

问题描述

我一直在建立一个Excel工作表来询问数据集并产生一组度量.我的目标是使该过程尽可能扩展.

I have been building up an excel sheet to interrogate a data set and produce a set of metrics. I'm aiming to make the process as expandable as possible.

链接中有一个简单的模拟数据集的图片，用于说明我要实现的目标，其中包含一个指标表(我想在其中显示信息)和数据集表:

In the links are pictures of a simple mock up data set to illustrate what I'm trying to achieve, it contains a metrics sheet where I want to display the information and the data set sheet:

和

目的是使突出显示的单元格(D6)在"Sheet2"中显示符合项目状态打开"条件的项目数.

The aim is to have the highlighted cell (D6) display the number of items in "Sheet2" that meet the criteria of Item Status "Open".

我首先使用公式来解决此问题:

I have managed this at first with the formula:

COUNTIFS(Sheet2!C:C,"Open")

，然后使用以下方法使其更具扩展性:

and then to make it more expandable using:

COUNTIFS(INDIRECT(" ' "&D4&" ' !C:C "),"=" &C8)

这意味着我可以使用单元格(D4)引用我要查看的工作表，并使用单元格(C8)引用我要搜索的条件.在我需要在数据集中查找空白之前，这一直很好，这时它将对指定列中的所有空白进行计数(请参阅指标表中的单元格D8).

This means I can use a cell (D4) to reference the sheet I want to look in and a cell (C8) to reference the criteria I want to search for. This works well until I need to look for blanks in the data set, at which point it counts all the blanks in the column specified (see cell D8 in metrics sheet).

所以我想知道是否有一种整洁的方法来指定要查找的列范围，例如C1:C100，方法是使用单元格来引用该范围，类似于使用单元格来引用工作表和标准.我可以使用下面的公式，但是它仍然需要将表名称写在公式评分器中，而不是引用到单元格中.

So I was wondering if there would be a neat way to specify the column range to look in, eg C1:C100, by using cells to reference the range similar to using the cells to reference sheet and criteria. I can use the below formula but it still requires the sheet name to be written in the formula rater than referenced out to a cell.

COUNTIF((Sheet2!C1:INDIRECT(CONCATENATE("Sheet2!C", B2))),""&C8)

将其应用于要查看的数据集时，我将需要考虑多个工作表，其中数据集将包含相同的条件(位于所有工作表的同一列中)，但数据集的长度会有所不同.这就是为什么我要保留引用变量的单元格公式到指标表中的特定单元格，这样，如果我添加了新的数据集或要查看的条件，就不必重新键入大量的公式，但只需将其复制即可.

When I apply this to the data sets I'm looking at I will need to consider multiple sheets where the data sets will contain the same criteria (located in the same column across all sheets) but the data set will vary in length. This is why I'd like to keep the cell formula referencing the variables out to specific cells in the metrics sheet so that if I add in a new data set or criteria that I want to look at I don't have to re-type a load of formula but just copy it across.

UPDATE

UPDATE

按照JvdV的回答，我已经能够将公式中的所有变量都删除到指标表的单元格中(对我正在做的事情有用，并且可能对其他人很感兴趣).它实际上使用了'INDIRECT'和'CONCAT'函数来构建所需的字符串，用彩色编码的图片

Following the answer from JvdV I have been able to remove all the variables from the formula into cells in the metrics sheet (useful for what I'm doing and may be of interest to others). It essentially uses the 'INDIRECT' and 'CONCAT' functions to build the string needed, colour coded picture

推荐答案

您可以尝试以下方法来满足您的需求:

Here is something you can try to suit your needs:

我在F1中使用的公式将转换为:

The formula I used in F1 would translate to:

=COUNTBLANK(INDIRECT(G1&"C1:C"&COUNTA(INDIRECT(G1&"A:A"))))

单元格G1只是一个列表，如:

Cell G1 is just a list like:

如果您不想拥有所有已使用的行，而是使用单元格B2中指定的范围，那么我想它看起来像:

If you don't want to have all used rows but use the range specified in your cell B2 then I guess it would look like:

=COUNTBLANK(INDIRECT(G1&"C1:C"&B2))

当心；使用INDIRECT()会使您的公式变得不稳定！

Beware; using INDIRECT() causes your formula to be volatile!

这篇关于Excel-计算特定范围内符合特定条件的不同工作表中的项目数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！