在没有 VBA 的 Excel 中创建动态“if"语句 [英] Create a dynamic 'if' statement in Excel without VBA

问题描述

* 更新 *

我有一个相当大的 excel 数据集，我试图使用最多 3 个维度对其进行总结:地区、部门、行业.可以设置这些维度的任何组合或将其留空，我需要创建一个公式，以使用 VBA 来适应这种WITHOUT.

I have a rather large excel data set that I'm trying to summarise using up to 3 dimensions: region, sector, industry. Any combination of these dimensions can be set or left blank and I need to create a formula that accommodates this WITHOUT using VBA.

在数据中，我设置了命名范围来引用这些维度.

Within the data I've set up named ranges to refer to these dimensions.

我正在使用数组公式，但我想动态创建一个字符串，然后将其用作数组公式中的布尔参数.

I'm using an array formula but I'd like to dynamically create a string which is then used as the boolean argument in the array formula.

例如如果:

A1 = "Hong Kong" (region)
B1 = <blank> (sector)
C1 = <blank> (industry)

我在 D1 中创建了一个动态字符串，使得

I create a dynamic string in D1 such that

D1 = (region="Hong Kong")

然后我想使用 D1 中的字符串来创建数组公式

I then want to use the string in D1 to create an array formula

E1 = {counta(if(D1,employees))}

但是，如果用户包括这样的扇区:

However, if the user includes a sector such that:

 A2 = "Hong Kong" (region)
 B2 = "finance" (sector)
 C2 = <blank> (industry)

然后我希望 D2 中的字符串更新为:

Then I want the string in D2 to update to:

D2 = (region="Hong Kong")*(sector="finance")

然后自动更新 E2 中仍然具有相同公式的值.

Which then automatically updates the value in E2 which still has the same formula.

E2 = {counta(if(D2,employees))}

这可能吗?或者有没有其他方法可以实现相同的结果，请记住，我需要能够将 D1 和 E1 复制到不同的行中，以便可以同时查看不同的维度组合.

Is this possible? Alternatively is there any other way of achieving the same outcome, keeping in mind that I need to be able to copy D1 and E1 down into different rows so that different combinations of the dimensions can be viewed simultaneously.

谢谢.

* 更新 *

明确地说，我需要 D 列中的值是动态的原因是我可以在第 1 行、第 2 行、第 3 行等中创建不同的场景，并且我需要每行 E 列中的值匹配该行 A:C 列中设置的条件.

To be clear, the reason the I need the values in column D to be dynamic is so that I can create different scenarios in Row 1, Row 2, Row 3 etc. and I need the values in column E of each row to match the criteria set in columns A:C of that row.

推荐答案

必须有一个相当简单的方法！

There had to be a fairly simple way!

列 B:D 包含条件，A 是条件编号，E 是应用 DSUM 函数到该行中的条件.我使用了 DSUM，因为对员工人数求和似乎更自然(至少对我而言).但是，DCOUNT 同样可以很好地使用.为简洁起见，我没有展示我正在使用的数据，但它是一个非常简单的数据集，只有几行测试数据.

Columns B:D contain the criteria, A is a criterion number and E is the result of applying the DSUM function to the criterion in that row. I've used DSUM as it seems more natural (to me at least) to sum employee numbers. However, DCOUNT can equally well be used. For brevity I've not shown the data I'm using but it is a very trivial data set with just a few rows of test data.

第 2 行中的第一组标准是:Sector 取Man"(制造业)的值，而未指定 Region 和 Industry.第三组标准(第 4 行)是:地区是Fr"(法国)，行业是汽车".DSUM 列中的结果是通过应用相应行中的一组条件获得的.一行中的所有单元格、部分单元格甚至没有单元格可能包含条目.

The first set of criteria in row 2 is: Sector takes value of "Man" (manufacturing) whilst Region and Industry are unspecified. The 3rd set of criteria (in row 4) is: the Region is "Fr" (for France) AND the Industry is "Cars". The results in the DSUM column are obtained by applying the set of criteria in the corresponding row. All, some or even none of the cells in a row may contain entries.

所使用的方法基于列 G:J，其中单元格 G1 和 G2(包含数字 0 和1，分别)这些列中的所有内容都是由公式生成的.

The approach used is based on columns G:J, where with the exception cells G1 and G2 (which contain the numbers 0 and 1, respectively) everything in these columns has been generated by a formula.

G:J 列中的行数是 B:D 中列出的条件集的两倍，并且这些行应成对出现.第一对(第 1 行和第 2 行)提供了一个用于 DSUM 的标准表，对应于第一组标准(该表是单元格 H1:J2)，第二个第 3 行和第 4 行中的对为第二组条件(单元格 H3:J4)等提供了一个条件表(忽略第 11 行 - 我在屏幕截图中向下复制了太多行！)

There are twice as many rows in columns G:J as there are sets of criteria listed in B:D and the rows should be taken in pairs. The first pair (rows 1 and 2) provide a criterion table for use in DSUM corresponding to the first set of criteria (the table is cells H1:J2), the second pair in rows 3 and 4 provides a criterion table for the second set of criteria (cells H3:J4), etc. (Ignore the 11th row - I copied too many rows downwards in the screenshot!)

列 G 有一个相当明显的模式，可以通过在单元格 G3 中应用一个简单的 =IF() 函数来生成，该函数引用了G1 和 G2 中的起始对与 G3 中的公式，然后向下复制.

Column G has a fairly obvious pattern and can be generated by applying a simple =IF() function in cell G3 which references the starting pair in G1 and G2 with the formula in G3 then copied downwards.

H:J 列中的单元格使用 INDEX 函数(并在此过程中使用 G 列中的值).创建一个可以从 H1 复制到范围 H1:J11 的单个公式并不太难如果或两个).请注意，对 B2:D6 中空单元格的引用将在 H:J 中的相应单元格中生成值 0，因此构造 IF(x=0,"",x) 必须使用 - 这使得 H:J 列中单元格中使用的公式有点笨拙但不过分.

The cells in columns H:J reference the appropriate cells of the set of all criteria (B1:D6 in the screenshot) using the INDEX function (and making use of the value in column Galong the way). It is not too difficult to create a single formula that can be copied from H1 to the range H1:J11 by judicious use of mixed relative and absolute addressing and an IF or two). Note that references to an empty cell in B2:D6 will generate a value of 0 in the corresponding cell in H:J so the construct IF(x=0,"",x) must be used - this makes the formula used in the cells in columns H:J a bit clunky but not excessively so.

生成了与B:D中的5组标准相对应的5个标准表，利用OFFSET函数来提供正确的标准表作为E 列中 DSUM 函数的第三个参数.

Having generated the 5 criteria tables corresponding to the 5 sets of criteria in B:D, use is made of the OFFSET function to deliver the correct criterion table as the third argument of the DSUM functions in column E.

我选择将 OFFSET 放在单元格 $H$1 上，因此第一组条件的条件表的左上角单元格与我的基本单元格由 0 行和 0 列组成.第二个标准表偏移 2 行 0 列，第三个偏移 4 行 0 列.应该清楚如何从 A 列中相应的标准编号计算要使用的偏移行和列的数量.还应该很明显，OFFSET 函数的最后两个参数将始终为 2 和 3.所以我的 <E 列中的 code>DSUM() 函数看起来像

I chose to base my OFFSETs on cell $H$1, so the top-left cell of the criterion table for the first set of criteria is offset from my base cell by 0 rows and 0 columns. The second criterion table is offset by 2 rows and 0 columns, the third by 4 rows and 0 columns. It should be clear how the number of offset rows and columns to use can be calculated from the corresponding criterion number in column A. It should also be obvious that the final two arguments of the OFFSET function will always be 2 and 3. So my DSUM() functions in column E look something like

=DSUM(myData,"Employees",OFFSET($H$1,row_offset,0,2,3))

其中myData 是包含测试数据集的命名范围，row_offset 是一个非常简单的公式，涉及A 列中的相应值.

where myData is the named range containing the test dataset and row_offset is a very simple formula involving the corresponding value in column A.

如果能够提供函数的第三个参数，而不必采用有效再现单元格 H1:J10 中 B1:D6 中的标准集的方法，那就太好了.虽然有一些方法可以在不将它们放在工作表上的情况下以公式方式生成所需的标准表数组，但我发现 DSUM 在应用此类数组作为其第三个参数时会生成错误.

It would have been nice to have been able to deliver the third argument of the function without having to adopt the approach of effectively reproducing the sets of criteria in B1:D6 in cells H1:J10. Whilst there are ways to generate the required criterion table arrays formulaically without putting them onto the worksheet, I found that DSUM generated an error when applying such an array as its third argument.

这篇关于在没有 VBA 的 Excel 中创建动态“if"语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！