在没有VBA的Excel中创建一个动态的“if”语句 [英] Create a dynamic 'if' statement in Excel without VBA
问题描述
*更新*
我有一个相当大的excel数据集，我试图总结使用最多3维：区域，部门，行业。
这些维度的任意组合都可以设置或留空，我需要创建一个可以使用VBA适应此 WITHOUT 的公式。
在数据中我设置了命名范围以引用这些维度。
我使用数组公式，但我想动态创建一个字符串然后在数组公式中用作布尔参数。
例如，如果：
A1 =香港区域）
B1 =< blank> （扇区）
C1 =< blank> （行业）
我在D1中创建一个动态字符串，使得
D1 =（region =Hong Kong）
$ b $然后我想使用D1中的字符串来创建数组公式
E1 = {counta（if（D1 ，员工））}
但是，如果用户包含一个扇区：
A2 =香港（地区）
B2 =金融（部门）
C2 =< blank> （行业）
然后我想要D2中的字符串更新到：
D2 =（region =Hong Kong）*（sector =finance）
然后，它自动更新E2中仍然具有相同公式的值。
E2 = {counta（if（D2，employees））}
这可能吗？另外还有其他方法可以达到相同的结果，但请记住，我需要将D1和E1复制到不同的行中，以便不同的维度组合可以同时查看。 / p>
谢谢。
*更新*
要清楚，我需要列D中的值为动态的原因是为了在第1行，第2行，第3行等中创建不同的方案，我需要列E列中的值每行匹配在该行的列A：C中设置的条件。
必须有一个相当简单的方法！
列 B：D 包含条件 A 是一个标准号而 E 是将 DSUM 函数应用于该行中的条件的结果。我已经使用 DSUM ，因为它似乎更自然（至少对我来说）来计算员工人数。但是，同样可以使用DCOUNT。为了简洁起见，我没有显示我使用的数据，但它是一个非常简单的数据集，只有几行测试数据。
第一组标准在第2行是：部门在区域和行业未指定时，以人（制造）的价值。第3组标准（第4行）是：区域为Fr（法国），行业为汽车。 DSUM 列中的结果是通过在相应行中应用一组条件获得的。全部，一些或甚至没有一行的单元格可能包含条目。
使用的方法基于列 G：J ，其中除了单元格 G1 和 G2 （分别包含数字0和1）这些列中的所有内容都是由公式生成的。
列 G：J 中的行数是两倍，因为 B：D ，这些行应该成对进行。第一对（行1和2）提供了一个标准表，用于与第一组标准相对应的 DSUM （表格为 H1： J2 ），第3行和第4行中的第二对为第二组标准（单元格 H3：J4 ）等提供了一个标准表。 （忽略第11行 - 我在截图中向下复制了太多的行）！
列 G 明显的模式，可以通过在单元格 G3 中应用一个简单的 = IF（）函数来生成，引用起始对 G1 和 G2 ，其中公式为 G3 然后向下复制
列 H：J 中的单元格引用所有条件集合中的相应单元格（ B1：D6 在截图中）使用 INDEX 函数（并使用列中的值 G 一路上）。创建一个可以从 H1 复制到 H1：J11 范围的单一公式并不难使用混合的相对和绝对寻址以及 IF 或两个）。请注意，引用 B2：D6 中的空单元格将在 H中的相应单元格中生成一个值0：J 因此，必须使用 IF（x = 0，，x）的构造 - 这使得列 H中的单元格中使用公式： J 有点笨重，但不是太过分。
生成了与 B：D 中的5组标准相对应的5个条件表，使用 OFFSET 函数，将 DSUM 函数的第三个参数传递给正确的标准表，列电子。
我选择将我的 OFFSET 在单元格 $ H $ 1 ，所以第一组条件的标准表的左上角的单元格与我的基本单元格偏移了0行和0列。第二个标准表被2行和0列偏移，第3个由4行和0列偏移。应该清楚如何使用的偏移行和列的数量可以从列A中的相应标准数来计算。还应该明显的是，OFFSET函数的最后两个参数将始终为2和3.所以我的 DSUM（）列中的函数E 看起来像
= DSUM（myData，Employees，OFFSET（$ H $ 1，row_offset，0,2,3））
其中 myData 是包含测试数据集的命名范围， row_offset 是一个非常简单的公式，涉及相应的值列 A 。
已经能够提供函数的第三个参数是没有问题的采用在H1：J10细胞中有效再现B1：D6标准的方法。虽然有方法可以公式地生成所需的标准表数组，而不将它们放在工作表上，但我发现当应用这样的数组作为第三个参数时， DSUM 会生成错误。
* Updated *
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.
For instance if:
A1 = "Hong Kong" (region)
B1 = <blank> (sector)
C1 = <blank> (industry)
I create a dynamic string in D1 such that
D1 = (region="Hong Kong")
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)
Then I want the string in D2 to update to:
D2 = (region="Hong Kong")*(sector="finance")
Which then automatically updates the value in E2 which still has the same formula.
E2 = {counta(if(D2,employees))}
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.
Thanks.
* Updated *
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!
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.
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.
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.
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!)
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.
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.
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.
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))
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.
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屋！
