如何从Excel中的查找范围进行计数 [英] How to count from an looked up range in excel

问题描述

我有一个数据透视表，其中有一组城市的行。我想计算某个州的城市数。不想在透视表中包含状态（尽管这会使事情变得更简单）。

我尝试使用以下公式：

  = {countif（index（city（ref_state [state]，match（G15：G30，city_ref [city_name]，0）），'= CA'）}} 
  

其中：

• city_ref 是一个至少包含列 [city_name] 和 [state]

  




• G15：G30 是数据透视表的行

  
  




数据看起来像这样（简化）：

city_ref ：

  city_name |州
 ==================== 
洛杉矶| CA 
西雅图|西澳州
波特兰|或
等。
  

G15：G30 ：

  city_name 
 =========== 
 G15：洛杉矶
 G16：西雅图
等（但不是city_ref的全部）
  

我确实意识到您可以将国家纳入枢轴表，但是a）不需要的（甚至将其隐藏）和b）还有其他与数据无关的数据，当包含状态时，数据透视表会增加其他行，从而弄乱了计数。

索引匹配查找似乎返回一个数组（如果单独使用）；如果我使用填充句柄，则会创建一个单元格数组，其查找值在 G15：G30 之间。但是，将其包含在 countif 中似乎没有任何作用。

在 countif的帮助页面中（不幸的是，我找不到在线参考链接），它说 range （第一个参数）可以是一个数组。 / p>


我希望能够获取任意值的数组，在另一个表中查找它们并计算与我的条件匹配的值。

解决方案

像这样吗？

{= SUM（（IFERROR（MATCH（$ D $ 2：$ D $ 7，INDEX（IF（$ B $ 2：$ B $ 13 = $ F $ 2，$ A $ 2：$ A $ 13），），0），0）> 0）* 1）}

I have a pivot table that has a row for a set of cities. I want to count the number of cities in a certain state. It's not wanted to include the state in the pivot table (although that would make things simpler).

I tried using the following formula:

={countif(index(city_ref[state], match(G15:G30,city_ref[city_name],0)),"=CA")}

Where:

• city_ref is a table that has at least the columns [city_name] and [state]

• G15:G30 are the rows of the pivot table

The data looks like this (simplified):

city_ref:

city_name   | state
====================
Los Angeles | CA
Seattle     | WA
Portland    | OR
etc.

G15:G30:

     city_name
     ===========
G15: Los Angeles
G16: Seattle
     etc. (but not all of city_ref)

I do realize that you could include the state in a pivot table but a) that's not wanted (even hiding it) and b) there is other, dis-related data that when the state is included the pivot table makes additional rows which messes up the count.

The index match lookup seems to return an array if used on its own; if I use the fill handle it does create an array of cells with the lookup values between G15:G30. However including this in countif does not seem to do anything.

In the help page for countif that I have in excel (unfortunately I cannot find an online reference to link to) it says that range (the first argument) can be an array.

I want to be able to take an arbitrary array of values, look them up in another table and count the ones that match my criteria.

解决方案

Something like this ?

{=SUM((IFERROR(MATCH($D$2:$D$7,INDEX(IF($B$2:$B$13=$F$2,$A$2:$A$13),),0),0)>0)*1)}

这篇关于如何从Excel中的查找范围进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！