![相当复杂的 Excel 决策公式。数据处理](https://linux22.com/image/1594181/%E7%9B%B8%E5%BD%93%E5%A4%8D%E6%9D%82%E7%9A%84%20Excel%20%E5%86%B3%E7%AD%96%E5%85%AC%E5%BC%8F%E3%80%82%E6%95%B0%E6%8D%AE%E5%A4%84%E7%90%86.png)
我不确定我可以在哪里提问,但经过一番研究后,我决定在这里提问:
我对下表有疑问:
在输入 A — 单元格 B4我们把一个正数放入输入 B — 单元格 B7我们也放一个正数。关于预先给出的表格值A,值 B和价值C—数组 D3:I9对于所有如此描述的值(这些值可以是随机的,在我的情况下,我只使用了素数),我需要一种方法来生成输出输出C—K6。
产生输出值的关系如下:输入 A — 单元格 B4与...有关值 A — 数组 D3:D7以及输入 B — 单元格 B7与...有关值 B — 数组 E3:I7. 两者的结合值A和值 B与...相关值 C — 数组 E9:I9。
(1)输入 A生成值A通过挑选数组 D3:D7大于B4。
(2)输入 B只能是输入 A。 什么时候值A生成,值 B位于同一排作为值A, 同时值 B是数组 E[ROW_VALUE_A]:I[ROW_VALUE_A]。
(3)输出 C只有在我们生成值 B。 这价值C是唯一在同一柱子作为值 B。
(4)输出 C读价值C。
由于这解释起来有点复杂,我觉得举一个例子会使事情变得更容易:
(1)如果我们把数字2,6在B4, 这值A必须走单元格 D4 — 3, 因为3是数组 D3:D7。 阶段1
(2)因此,我们需要缩小选择范围值 B从数组 E4:I4.在此数组中,最小值大于输入 B — B7是79 — 单元格 H4。79 — 单元格 H4将成为我们的值 B。 第 2 阶段
(3)由于79位于单元格 H4,该列中唯一的数字,值 C — 数组 E9:I9是139 — H9。 第 3 阶段
(4)显然我们打印H9到K6。 第 4 阶段
如果有人问我如何实现这一点,我不会很受鼓舞,所以我将向你展示我迄今为止的进展:
(1)我设法从数组中选取最小的值值 A — D3:D7,大于输入 A — B4。公式如下:
=MIN(IF((D3:D7>=B4)*(D3:D7);(D3:D7)))
必须应用控制+转移+进入,因为它是一个数组公式。
关于(2)和(3),我不知道如何将上述公式强制应用于由行确定的数组值A。
如果我设法读取单元格的坐标值 A — 数组 D3:D7,这个问题大概已经解决了。
这里我应用了该文件,所以如果有人感兴趣的话,可以尝试一下: Exel 文件
我不喜欢用宏编码,而是用“单元格内”公式输入输出C—K6。
谢谢您的关注并祝您好运!:)
答案1
针对您的确切示例材料使用以下公式。当然,您可以对其进行修改以适应更大的表格,或者您可能对其中的部分内容使用命名范围。
它是一个数组公式,是 CSE 的变体,因此我将用括号来表示它,但自然只复制和粘贴中间的内容:
{=INDIRECT(ADDRESS(9,MATCH(MIN(IF(INDIRECT(CONCATENATE(ADDRESS(MATCH(MIN(IF(D3:D7>B4,D3:D7)),D3:D7,0)+2,5),":",ADDRESS(MATCH(MIN(IF(D3:D7>B4,D3:D7)),D3:D7,0)+2,9)))>B7,INDIRECT(CONCATENATE(ADDRESS(MATCH(MIN(IF(D3:D7>B4,D3:D7)),D3:D7,0)+2,5),":",ADDRESS(MATCH(MIN(IF(D3:D7>B4,D3:D7)),D3:D7,0)+2,9))))),INDIRECT(CONCATENATE(ADDRESS(MATCH(MIN(IF(D3:D7>B4,D3:D7)),D3:D7,0)+2,5),":",ADDRESS(MATCH(MIN(IF(D3:D7>B4,D3:D7)),D3:D7,0)+2,9))),0)+4))}
(我保留了相对的单元格引用,以使其更容易阅读。您可以用 $$ 表示它们,或者用每个单元格的命名范围替换它们。)
可以通过调整单元格和范围引用来扩展它。2,000 行?那么“:D2002”而不是“:D7”等等。如果对这些项目使用命名范围,则只需对每个范围引用(公式中的所有“D3:D7”位)进行一次更改,如果您移动输入单元格(B4 和 B7),也是如此。
基本上你:
1)用途:
=MIN(IF(D3:D7>B4,D3:D7))
根据输入 A 找到正确的结果。这很容易,但是它在范围内吗?
2) 使用 MATCH() 在范围 D3:D7 中定位其行。可能是该范围的最后一行,即第 5 行。由于它上面有 2 行,因此将其加 2,以获取它所在的电子表格行(而不是范围)。您已经知道列是 5 和 9(E 和 I),现在您知道行了。将所有这些放在一起,使用输入 B 获得要检查的范围。
3) 在上面的公式中,我使用 ADDRESS 函数构建了要根据值 B 进行检查的范围,以说明更大的情况:您可能需要动态创建所有这些。我犹豫着完全这样做,而是使用我所知道的方法来计算列,但如果表格永远不会增长,或者只会偶尔增长,那么您可以将 INDIRECT() 函数中的内容替换为 INDIRECT(),将 2) 中学到的行和两个列字母的简单连接起来,而不是所有 ADDRESS() 工作。
4) 无论哪种方式,找到输入 A 和 B 所导致的值后,使用 MATCH() 找到它的列并构建最后一个 INDIRECT(ADDRESS()) 部分以在所需的值 C 中创建地址。
5)这将返回您正在寻找的值,因此只要您记得对其进行 CSE,就可以开始工作了。
就我个人而言,我讨厌电子表格的复杂性,因此我使用前面提到的命名范围(清晰,并且在使用电子表格时更容易扩展),并且我还会将此公式的原始部分放入命名范围中,以便我可以随着时间的推移在使用电子表格时轻松升级它们。这样,我就不用在升级或扩展之前费尽心思地拆开并重新理解,而是可以在命名范围内一点一点地完成各个部分。如果您的表格可能会增长,尤其是如果它们可以一直且不可预测地增长,那么这种方法将真正帮助您管理流程。
来源:我从这个网站获取了上面 1)中的公式:
答案2
这是一个很好的答复,谢谢——它解决了我的问题。
因为它是用逗号而我的 Excel 由于某些 Windows 设置而更喜欢分号,我将使用你的解决方案并将其转换为分号,所以像我这样的用户可以直接复制解决方案:
=INDIRECT(ADDRESS(9;MATCH(MIN(IF(INDIRECT(CONCATENATE(ADDRESS(MATCH(MIN(IF(D3:D7>B4;D3:D7));D3:D7;0)+2;5);":";ADDRESS(MATCH(MIN(IF(D3:D7>B4;D3:D7));D3:D7;0)+2;9)))>B7;INDIRECT(CONCATENATE(ADDRESS(MATCH(MIN(IF(D3:D7>B4;D3:D7));D3:D7;0)+2;5);":";ADDRESS(MATCH(MIN(IF(D3:D7>B4;D3:D7));D3:D7;0)+2;9)))));INDIRECT(CONCATENATE(ADDRESS(MATCH(MIN(IF(D3:D7>B4;D3:D7));D3:D7;0)+2;5);":";ADDRESS(MATCH(MIN(IF(D3:D7>B4;D3:D7));D3:D7;0)+2;9)));0)+4))
不要忘记在单元格中应用公式控制+转移+进入!
我现在要开始解释这个公式,因为你强调了这一点以便我们能够理解它。
再次感谢您的帮助并继续努力!