我的电子表格的公式
我的电子表格的公式
提问于 2015-09-13 00:05:37
我将在Excel工作簿中为我的服务和包定价，并且已经到了我的Excel知识之外的地方。在一个名为"Packages“的工作表中，我为我提供的每个不同的包都有列。在专栏中，我有一个下拉列表，可以选择我的任何一个产品(我有13个不同的产品)。
我想要做的是有一个公式，在单元格A25中查看单元格A9:A21并确定是否有任何文本，然后从工作表“产品定价”中获取相应于所述单元格中所列产品的成本，并将所有值相加在一起。所以，如果A9有产品1，成本是50美元，而A10有Product2，成本是75美元，而A11是空白的，我想要公式使A25值125美元(50+75)。
我觉得这应该是一堆嵌套的IF和ISBLANK公式，但我只是其中的一部分，我被告知“公式太长了，公式不能超过8192个字符”。我还有别的办法可以做到吗？
以下是我迄今所写的公式：
/*
* 提示：该行代码过长，系统自动注释不进行高亮。一键复制会移除系统注释
* =IF(ISTEXT(C9), IF(C9='Product Pricing'!$A$8,'Product Pricing'!$L$8+ IF(ISTEXT(C10), IF(C10='Product Pricing'!$A$8,'Product Pricing'!$L$8, IF(C10='Product Pricing'!$A$9,'Product Pricing'!$L$9, IF(C10='Product Pricing'!$A$10,'Product Pricing'!$L$10, IF(C10='Product Pricing'!$A$11,'Product Pricing'!$L$11, IF(C10='Product Pricing'!$A$12,'Product Pricing'!$L$12, IF(C10='Product Pricing'!$A$13,'Product Pricing'!$L$13, IF(C10='Product Pricing'!$A$14,'Product Pricing'!$L$14, IF(C10='Product Pricing'!$A$15,'Product Pricing'!$L$15, IF(C10='Product Pricing'!$A$16,'Product Pricing'!$L$16, IF(C10='Product Pricing'!$A$17,'Product Pricing'!$L$17, IF(C10='Product Pricing'!$A$18,'Product Pricing'!$L$18, IF(C10='Product Pricing'!$A$19,'Product Pricing'!$L$19, IF(C10='Product Pricing'!$A$20,'Product Pricing'!$L$20, 0))))))))))))),0), IF(C9='Product Pricing'!$A$9,'Product Pricing'!$L$9+ IF(ISTEXT(C10), IF(C10='Product Pricing'!$A$8,'Product Pricing'!$L$8, IF(C10='Product Pricing'!$A$9,'Product Pricing'!$L$9, IF(C10='Product Pricing'!$A$10,'Product Pricing'!$L$10, IF(C10='Product Pricing'!$A$11,'Product Pricing'!$L$11, IF(C10='Product Pricing'!$A$12,'Product Pricing'!$L$12, IF(C10='Product Pricing'!$A$13,'Product Pricing'!$L$13, IF(C10='Product Pricing'!$A$14,'Product Pricing'!$L$14, IF(C10='Product Pricing'!$A$15,'Product Pricing'!$L$15, IF(C10='Product Pricing'!$A$16,'Product Pricing'!$L$16, IF(C10='Product Pricing'!$A$17,'Product Pricing'!$L$17, IF(C10='Product Pricing'!$A$18,'Product Pricing'!$L$18, IF(C10='Product Pricing'!$A$19,'Product Pricing'!$L$19, IF(C10='Product Pricing'!$A$20,'Product Pricing'!$L$20, 0))))))))))))),0), IF(C9='Product Pricing'!$A$10,'Product Pricing'!$L$10+ IF(ISTEXT(C10), IF(C10='Product Pricing'!$A$8,'Product Pricing'!$L$8, IF(C10='Product Pricing'!$A$9,'Product Pricing'!$L$9, IF(C10='Product Pricing'!$A$10,'Product Pricing'!$L$10, IF(C10='Product Pricing'!$A$11,'Product Pricing'!$L$11, IF(C10='Product Pricing'!$A$12,'Product Pricing'!$L$12, IF(C10='Product Pricing'!$A$13,'Product Pricing'!$L$13, IF(C10='Product Pricing'!$A$14,'Product Pricing'!$L$14, IF(C10='Product Pricing'!$A$15,'Product Pricing'!$L$15, IF(C10='Product Pricing'!$A$16,'Product Pricing'!$L$16, IF(C10='Product Pricing'!$A$17,'Product Pricing'!$L$17, IF(C10='Product Pricing'!$A$18,'Product Pricing'!$L$18, IF(C10='Product Pricing'!$A$19,'Product Pricing'!$L$19, IF(C10='Product Pricing'!$A$20,'Product Pricing'!$L$20, 0))))))))))))),0), IF(C9='Product Pricing'!$A$11,'Product Pricing'!$L$11+ IF(ISTEXT(C10), IF(C10='Product Pricing'!$A$8,'Product Pricing'!$L$8, IF(C10='Product Pricing'!$A$9,'Product Pricing'!$L$9, IF(C10='Product Pricing'!$A$10,'Product Pricing'!$L$10, IF(C10='Product Pricing'!$A$11,'Product Pricing'!$L$11, IF(C10='Product Pricing'!$A$12,'Product Pricing'!$L$12, IF(C10='Product Pricing'!$A$13,'Product Pricing'!$L$13, IF(C10='Product Pricing'!$A$14,'Product Pricing'!$L$14, IF(C10='Product Pricing'!$A$15,'Product Pricing'!$L$15, IF(C10='Product Pricing'!$A$16,'Product Pricing'!$L$16, IF(C10='Product Pricing'!$A$17,'Product Pricing'!$L$17, IF(C10='Product Pricing'!$A$18,'Product Pricing'!$L$18, IF(C10='Product Pricing'!$A$19,'Product Pricing'!$L$19, IF(C10='Product Pricing'!$A$20,'Product Pricing'!$L$20, 0))))))))))))),0), IF(C9='Product Pricing'!$A$12,'Product Pricing'!$L$12+ IF(ISTEXT(C10), IF(C10='Product Pricing'!$A$8,'Product Pricing'!$L$8, IF(C10='Product Pricing'!$A$9,'Product Pricing'!$L$9, IF(C10='Product Pricing'!$A$10,'Product Pricing'!$L$10, IF(C10='Product Pricing'!$A$11,'Product Pricing'!$L$11, IF(C10='Product Pricing'!$A$12,'Product Pricing'!$L$12, IF(C10='Product Pricing'!$A$13,'Product Pricing'!$L$13, IF(C10='Product Pricing'!$A$14,'Product Pricing'!$L$14, IF(C10='Product Pricing'!$A$15,'Product Pricing'!$L$15, IF(C10='Product Pricing'!$A$16,'Product Pricing'!$L$16, IF(C10='Product Pricing'!$A$17,'Product Pricing'!$L$17, IF(C10='Product Pricing'!$A$18,'Product Pricing'!$L$18, IF(C10='Product Pricing'!$A$19,'Product Pricing'!$L$19, IF(C10='Product Pricing'!$A$20,'Product Pricing'!$L$20, 0))))))))))))),0), IF(C9='Product Pricing'!$A$13,'Product Pricing'!$L$13+ IF(ISTEXT(C10), IF(C10='Product Pricing'!$A$8,'Product Pricing'!$L$8, IF(C10='Product Pricing'!$A$9,'Product Pricing'!$L$9, IF(C10='Product Pricing'!$A$10,'Product Pricing'!$L$10, IF(C10='Product Pricing'!$A$11,'Product Pricing'!$L$11, IF(C10='Product Pricing'!$A$12,'Product Pricing'!$L$12, IF(C10='Product Pricing'!$A$13,'Product Pricing'!$L$13, IF(C10='Product Pricing'!$A$14,'Product Pricing'!$L$14, IF(C10='Product Pricing'!$A$15,'Product Pricing'!$L$15, IF(C10='Product Pricing'!$A$16,'Product Pricing'!$L$16, IF(C10='Product Pricing'!$A$17,'Product Pricing'!$L$17, IF(C10='Product Pricing'!$A$18,'Product Pricing'!$L$18, IF(C10='Product Pricing'!$A$19,'Product Pricing'!$L$19, IF(C10='Product Pricing'!$A$20,'Product Pricing'!$L$20, 0))))))))))))),0), IF(C9='Product Pricing'!$A$14,'Product Pricing'!$L$14+ IF(ISTEXT(C10), IF(C10='Product Pricing'!$A$8,'Product Pricing'!$L$8, IF(C10='Product Pricing'!$A$9,'Product Pricing'!$L$9, IF(C10='Product Pricing'!$A$10,'Product Pricing'!$L$10, IF(C10='Product Pricing'!$A$11,'Product Pricing'!$L$11, IF(C10='Product Pricing'!$A$12,'Product Pricing'!$L$12, IF(C10='Product Pricing'!$A$13,'Product Pricing'!$L$13, IF(C10='Product Pricing'!$A$14,'Product Pricing'!$L$14, IF(C10='Product Pricing'!$A$15,'Product Pricing'!$L$15, IF(C10='Product Pricing'!$A$16,'Product Pricing'!$L$16, IF(C10='Product Pricing'!$A$17,'Product Pricing'!$L$17, IF(C10='Product Pricing'!$A$18,'Product Pricing'!$L$18, IF(C10='Product Pricing'!$A$19,'Product Pricing'!$L$19, IF(C10='Product Pricing'!$A$20,'Product Pricing'!$L$20, 0))))))))))))),0), IF(C9='Product Pricing'!$A$15,'Product Pricing'!$L$15+ IF(ISTEXT(C10), IF(C10='Product Pricing'!$A$8,'Product Pricing'!$L$8, IF(C10='Product Pricing'!$A$9,'Product Pricing'!$L$9, IF(C10='Product Pricing'!$A$10,'Product Pricing'!$L$10, IF(C10='Product Pricing'!$A$11,'Product Pricing'!$L$11, IF(C10='Product Pricing'!$A$12,'Product Pricing'!$L$12, IF(C10='Product Pricing'!$A$13,'Product Pricing'!$L$13, IF(C10='Product Pricing'!$A$14,'Product Pricing'!$L$14, IF(C10='Product Pricing'!$A$15,'Product Pricing'!$L$15, IF(C10='Product Pricing'!$A$16,'Product Pricing'!$L$16, IF(C10='Product Pricing'!$A$17,'Product Pricing'!$L$17, IF(C10='Product Pricing'!$A$18,'Product Pricing'!$L$18, IF(C10='Product Pricing'!$A$19,'Product Pricing'!$L$19, IF(C10='Product Pricing'!$A$20,'Product Pricing'!$L$20, 0))))))))))))),0), IF(C9='Product Pricing'!$A$16,'Product Pricing'!$L$16+ IF(ISTEXT(C10), IF(C10='Product Pricing'!$A$8,'Product Pricing'!$L$8, IF(C10='Product Pricing'!$A$9,'Product Pricing'!$L$9, IF(C10='Product Pricing'!$A$10,'Product Pricing'!$L$10, IF(C10='Product Pricing'!$A$11,'Product Pricing'!$L$11, IF(C10='Product Pricing'!$A$12,'Product Pricing'!$L$12, IF(C10='Product Pricing'!$A$13,'Product Pricing'!$L$13, IF(C10='Product Pricing'!$A$14,'Product Pricing'!$L$14, IF(C10='Product Pricing'!$A$15,'Product Pricing'!$L$15, IF(C10='Product Pricing'!$A$16,'Product Pricing'!$L$16, IF(C10='Product Pricing'!$A$17,'Product Pricing'!$L$17, IF(C10='Product Pricing'!$A$18,'Product Pricing'!$L$18, IF(C10='Product Pricing'!$A$19,'Product Pricing'!$L$19, IF(C10='Product Pricing'!$A$20,'Product Pricing'!$L$20, 0))))))))))))),0), IF(C9='Product Pricing'!$A$17,'Product Pricing'!$L$17+ IF(ISTEXT(C10), IF(C10='Product Pricing'!$A$8,'Product Pricing'!$L$8, IF(C10='Product Pricing'!$A$9,'Product Pricing'!$L$9, IF(C10='Product Pricing'!$A$10,'Product Pricing'!$L$10, IF(C10='Product Pricing'!$A$11,'Product Pricing'!$L$11, IF(C10='Product Pricing'!$A$12,'Product Pricing'!$L$12, IF(C10='Product Pricing'!$A$13,'Product Pricing'!$L$13, IF(C10='Product Pricing'!$A$14,'Product Pricing'!$L$14, IF(C10='Product Pricing'!$A$15,'Product Pricing'!$L$15, IF(C10='Product Pricing'!$A$16,'Product Pricing'!$L$16, IF(C10='Product Pricing'!$A$17,'Product Pricing'!$L$17, IF(C10='Product Pricing'!$A$18,'Product Pricing'!$L$18, IF(C10='Product Pricing'!$A$19,'Product Pricing'!$L$19, IF(C10='Product Pricing'!$A$20,'Product Pricing'!$L$20, 0))))))))))))),0), IF(C9='Product Pricing'!$A$18,'Product Pricing'!$L$18+ IF(ISTEXT(C10), IF(C10='Product Pricing'!$A$8,'Product Pricing'!$L$8, IF(C10='Product Pricing'!$A$9,'Product Pricing'!$L$9, IF(C10='Product Pricing'!$A$10,'Product Pricing'!$L$10, IF(C10='Product Pricing'!$A$11,'Product Pricing'!$L$11, IF(C10='Product Pricing'!$A$12,'Product Pricing'!$L$12, IF(C10='Product Pricing'!$A$13,'Product Pricing'!$L$13, IF(C10='Product Pricing'!$A$14,'Product Pricing'!$L$14, IF(C10='Product Pricing'!$A$15,'Product Pricing'!$L$15, IF(C10='Product Pricing'!$A$16,'Product Pricing'!$L$16, IF(C10='Product Pricing'!$A$17,'Product Pricing'!$L$17, IF(C10='Product Pricing'!$A$18,'Product Pricing'!$L$18, IF(C10='Product Pricing'!$A$19,'Product Pricing'!$L$19, IF(C10='Product Pricing'!$A$20,'Product Pricing'!$L$20, 0))))))))))))),0), IF(C9='Product Pricing'!$A$19,'Product Pricing'!$L$19 IF(ISTEXT(C10), IF(C10='Product Pricing'!$A$8,'Product Pricing'!$L$8, IF(C10='Product Pricing'!$A$9,'Product Pricing'!$L$9, IF(C10='Product Pricing'!$A$10,'Product Pricing'!$L$10, IF(C10='Product Pricing'!$A$11,'Product Pricing'!$L$11, IF(C10='Product Pricing'!$A$12,'Product Pricing'!$L$12, IF(C10='Product Pricing'!$A$13,'Product Pricing'!$L$13, IF(C10='Product Pricing'!$A$14,'Product Pricing'!$L$14, IF(C10='Product Pricing'!$A$15,'Product Pricing'!$L$15, IF(C10='Product Pricing'!$A$16,'Product Pricing'!$L$16, IF(C10='Product Pricing'!$A$17,'Product Pricing'!$L$17, IF(C10='Product Pricing'!$A$18,'Product Pricing'!$L$18, IF(C10='Product Pricing'!$A$19,'Product Pricing'!$L$19, IF(C10='Product Pricing'!$A$20,'Product Pricing'!$L$20, 0))))))))))))),0), IF(C9='Product Pricing'!$A$20,'Product Pricing'!$L$20, 0))))))))))))),0)
*/编辑:下面是工作簿的截图
因此，在这些图片中，单元格C24应该等于$1550.15，这将是单元格‘Package’！C8、'Product‘！L8、'Product’！L11和L13的总和。
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-09-14 14:07:35
试试这个公式数组
=SUM(IF( $A$9:$A$21 <=0, 0, SUM(INDEX( 'Product Pricing'!$L$8:$L$20,
MATCH( $A$9:$A$21, 'Product Pricing'!$A$8:$A$20, 0), 0))))$A$9:$A$21是列出产品的范围(选择)
'Product Pricing'!$L$8:$L$20是包含工作表“产品定价”中价格的范围。
'Product Pricing'!$A$8:$A$20是在工作表“产品定价”中包含产品代码的范围。
同意@Andrew，您应该显示每个产品之外的产品价格，然后SUM所有的价格乘以数量，以显示价格除了每个产品之外使用这个公式(记住包括数量)：
=IFERROR(VLOOKUP( A9, 'Product Pricing'!$A$8:$L$20, 12,0),"")'Product Pricing'!$A$8:$L$20是包含产品和价格的范围，12是在上述范围内提取价格的列。
根据需要替换所有范围
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/32545018
复制相关文章
相似问题
腾讯云开发者
