excel - 我需要在 Excel 中创建具有多个条件的条件公式，包括相对和非相对条件

Question

我在 AD 到 AI 列中有关键等级列——每列都有 1、2、3、4、5、N/A 或空白。 AJ 列只是一个占位符列，而 AK 列是我通过将前六列中的每个评级乘以该评级区域的权重来计算重新平衡的重要性评级的地方——在我的例子中，这些权重是6 列中分别为 30%、20%、20%、10%、15%、5%，这些权重列在第 729 行。我在处理 727 行数据时有两个目标:

如果列中有“N/A”或空白，请从这些列中获取百分比并重新分配它们 同样到其他列。因此，在图像的第一行，评分为 5、空白、2、1、1、5，权重为 30%、20%、20%、10%、15%、5%。如果没有重新分配，则公式为 (5 * 30%) + (BLANK * 20%) +(2 * 20%) + (1 * 10%) + (1 * 15%) + (5 * 5% ) = 1.5 + 0 + .4 + .1 + .15 + .25 = 2.4，即占位符 AJ 列。我的公式确定 N/A 和空白加起来代表 20% 的列，并且有 5 列中有值，所以我在这 5 列中的每一列中添加 4%，我的新数学变为 (5 * 34% ) + (空白 * 24%) +(2 * 24%) + (1 * 14%) + (1 * 19%) + (5 * 9%) = 1.7 + 0 + .48 + .14 + .19 + .45 = 2.96，这是重新平衡的 AK 列。一切正常，我在 Excel 中使用的公式是:

=LET(总计,SUMIF(AD2:AI2,"N/A",$AD$729:$AI$729)+SUMIF(AD2:AI2,"",$AD$729:$AI$729),count,COUNTIFS(AD2: AI2,">=1",AD2:AI2,"<=5"),SUM(IFERROR((AD$729:AI$729+total/count)*AD2:AI2,0)))
我的问题是关于下一个目标:

如果列中有“N/A”或空白，请从这些列中获取百分比并重新分配它们 基于它们的原始重量 到其他列。因此，在图像的第一行，评分为 5、空白、2、1、1、5，权重为 30%、20%、20%、10%、15%、5%。如何编写公式，以便在这种情况下，根据 AD 和 AF 中通过 AI 的列的百分比，将 AF 列中代表 BLANK 的 20% 通过 AI 添加到 AD 和 AF？我仍然需要知道“总数”，即重新分配的百分比，在本例中为 20%。但是我如何查看剩余的列，在这种情况下是 30%、20%、10%、15%、5%，然后重新分配 20%，使得 新的百分比相对于彼此是否相同？ AD 柱从 30% 开始，AG 柱从 10% 开始，AH 柱从 15% 开始，所以 AD 需要以 AG 的 3 倍和 AH 的两倍结束，我需要考虑到任何数量的列(最多 6 个)都可以有空白或 N/A 的事实。在这种情况下，新的加权百分比将为 37.5%、0%、25%、12.5%、18.75%、6.25%。

提前致谢!

Answer 1

下面这个疯狂的公式呢？

=IF(OR(AD2="N/A",AD2=0),0,AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AE2="N/A",AE2=0),0,AE2*AE$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AF2="N/A",AF2=0),0,AF2*AF$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AG2="N/A",AG2=0),0,AG2*AG$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AH2="N/A",AH2=0),0,AH2*AH$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AI2="N/A",AI2=0),0,AI2*AI$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))

这一项针对第 2 行进行了调整。假设权重以百分比形式存储(因此，如果单元格格式设置为一般，30% 将显示为 0,3)。
build 中
首先，如果合适，它会返回给定单元格的基本权重:
重量 * AND( 值 <>0, 值 <>"N/A")

AD$729*AND(AD2<>0,AD2<>"N/A")

对行的每个单元格重复:
单元格 1 的上一个公式 + 单元格 2 的上一个公式 + 单元格 3 的上一个公式 + 单元格 4 的上一个公式 + 单元格 5 的上一个公式 + 单元格 6 的上一个公式

(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A"))

用于划分值(value)和重量的乘积:
值(value) * 重量 / 上一个公式

AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A"))

仅在必要时才实际计算:
IF(OR( 值 = "N/A", 值 = 0), 0, 上一个公式 )

IF(OR(AD2="N/A",AD2=0),0,AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))

对行的每个单元格重复:
单元格 1 的上一个公式 + 单元格 2 的上一个公式 + 单元格 3 的上一个公式 + 单元格 4 的上一个公式 + 单元格 5 的上一个公式 + 单元格 6 的上一个公式

=IF(OR(AD2="N/A",AD2=0),0,AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AE2="N/A",AE2=0),0,AE2*AE$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AF2="N/A",AF2=0),0,AF2*AF$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AG2="N/A",AG2=0),0,AG2*AG$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AH2="N/A",AH2=0),0,AH2*AH$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AI2="N/A",AI2=0),0,AI2*AI$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))