请求对此Google工作表公式提供替代方法建议
请求对此Google工作表公式提供替代方法建议
提问于 2020-02-28 19:49:47
我在Google中写了这个非常长的公式(60,000+字符，如下所示)，它工作得非常完美。然而，现在我想简化它，因为我确信它是低效的，并且至少可以写得更紧。
本质上，它是查看酒店预订的列表，并显示每个预订的货架价格。它对此使用了几个标准：
- 季节(有四个季节:假日、高、中、低)。
- Room类型(目前有12个不同的房间类)。
- 年份(每个业务年度有不同的日期范围来定义季节)。
- Day of Week (平日和周末房间的收费不同)。
我并不是在寻找任何人自愿地为我重写公式(或做任何其他事情)，但我的猜测是，如果我使用IFS和公式以外的其他方法，我可能会获得同样的结果(甚至更好)。
我希望有人能指点我做这件事的最好方法(枢轴表？查一下？还有别的事吗？)这样我就可以去阅读它的文档，然后自己构建它。一个正确的方向可以帮助我避免一些不必要的死胡同。
(编辑)-链接到演示表：https://docs.google.com/spreadsheets/d/1weJgCmV_2Hzifb8QoR7I7gMhrAovLs5tXnKqW1ZWuU4/edit?usp=sharing
谢谢!
H.
=IFS(
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$4,S4=2020,U4=True), 'Rack Rates'!$C$8,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$5,S4=2020,U4=True), 'Rack Rates'!$C$9,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$6,S4=2020,U4=True), 'Rack Rates'!$C$10,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$7,S4=2020,U4=True), 'Rack Rates'!$C$11,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$8,S4=2020,U4=True), 'Rack Rates'!$C$12,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$9,S4=2020,U4=True), 'Rack Rates'!$C$13,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$10,S4=2020,U4=True), 'Rack Rates'!$C$14,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$11,S4=2020,U4=True), 'Rack Rates'!$C$15,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$13,S4=2020,U4=True), 'Rack Rates'!$C$17,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$14,S4=2020,U4=True), 'Rack Rates'!$C$18,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$15,S4=2020,U4=True), 'Rack Rates'!$C$19,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$16,S4=2020,U4=True), 'Rack Rates'!$C$20,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$4,S4=2020,U4=True), 'Rack Rates'!$F$8,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$5,S4=2020,U4=True), 'Rack Rates'!$F$9,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$6,S4=2020,U4=True), 'Rack Rates'!$F$10,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$7,S4=2020,U4=True), 'Rack Rates'!$F$11,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$8,S4=2020,U4=True), 'Rack Rates'!$F$12,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$9,S4=2020,U4=True), 'Rack Rates'!$F$13,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$10,S4=2020,U4=True), 'Rack Rates'!$F$14,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$11,S4=2020,U4=True), 'Rack Rates'!$F$15,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$13,S4=2020,U4=True), 'Rack Rates'!$F$17,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$14,S4=2020,U4=True), 'Rack Rates'!$F$18,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$15,S4=2020,U4=True), 'Rack Rates'!$F$19,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$16,S4=2020,U4=True), 'Rack Rates'!$F$20,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$4,S4=2020,U4=True), 'Rack Rates'!$I$8,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$5,S4=2020,U4=True), 'Rack Rates'!$I$9,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$6,S4=2020,U4=True), 'Rack Rates'!$I$10,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$7,S4=2020,U4=True), 'Rack Rates'!$I$11,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$8,S4=2020,U4=True), 'Rack Rates'!$I$12,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$9,S4=2020,U4=True), 'Rack Rates'!$I$13,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$10,S4=2020,U4=True), 'Rack Rates'!$I$14,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$11,S4=2020,U4=True), 'Rack Rates'!$I$15,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$13,S4=2020,U4=True), 'Rack Rates'!$I$17,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$14,S4=2020,U4=True), 'Rack Rates'!$I$18,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$15,S4=2020,U4=True), 'Rack Rates'!$I$19,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$16,S4=2020,U4=True), 'Rack Rates'!$I$20,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$4,S4=2020,U4=True), 'Rack Rates'!$L$8,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$5,S4=2020,U4=True), 'Rack Rates'!$L$9,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$6,S4=2020,U4=True), 'Rack Rates'!$L$10,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$7,S4=2020,U4=True), 'Rack Rates'!$L$11,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$8,S4=2020,U4=True), 'Rack Rates'!$L$12,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$9,S4=2020,U4=True), 'Rack Rates'!$L$13,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$10,S4=2020,U4=True), 'Rack Rates'!$L$14,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$11,S4=2020,U4=True), 'Rack Rates'!$L$15,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$13,S4=2020,U4=True), 'Rack Rates'!$L$17,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$14,S4=2020,U4=True), 'Rack Rates'!$L$18,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$15,S4=2020,U4=True), 'Rack Rates'!$L$19,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$16,S4=2020,U4=True), 'Rack Rates'!$L$20,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$4,S4=2020,U4=False), 'Rack Rates'!$B$8,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$5,S4=2020,U4=False), 'Rack Rates'!$B$9,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$6,S4=2020,U4=False), 'Rack Rates'!$B$10,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$7,S4=2020,U4=False), 'Rack Rates'!$B$11,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$8,S4=2020,U4=False), 'Rack Rates'!$B$12,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$9,S4=2020,U4=False), 'Rack Rates'!$B$13,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$10,S4=2020,U4=False), 'Rack Rates'!$B$14,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$11,S4=2020,U4=False), 'Rack Rates'!$B$15,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$13,S4=2020,U4=False), 'Rack Rates'!$B$17,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$14,S4=2020,U4=False), 'Rack Rates'!$B$18,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$15,S4=2020,U4=False), 'Rack Rates'!$B$19,
AND(R4='Rack Rates'!$B$2,Q4=Data!$A$16,S4=2020,U4=False), 'Rack Rates'!$B$20,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$4,S4=2020,U4=False), 'Rack Rates'!$E$8,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$5,S4=2020,U4=False), 'Rack Rates'!$E$9,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$6,S4=2020,U4=False), 'Rack Rates'!$E$10,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$7,S4=2020,U4=False), 'Rack Rates'!$E$11,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$8,S4=2020,U4=False), 'Rack Rates'!$E$12,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$9,S4=2020,U4=False), 'Rack Rates'!$E$13,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$10,S4=2020,U4=False), 'Rack Rates'!$E$14,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$11,S4=2020,U4=False), 'Rack Rates'!$E$15,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$13,S4=2020,U4=False), 'Rack Rates'!$E$17,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$14,S4=2020,U4=False), 'Rack Rates'!$E$18,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$15,S4=2020,U4=False), 'Rack Rates'!$E$19,
AND(R4='Rack Rates'!$E$2,Q4=Data!$A$16,S4=2020,U4=False), 'Rack Rates'!$E$20,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$4,S4=2020,U4=False), 'Rack Rates'!$H$8,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$5,S4=2020,U4=False), 'Rack Rates'!$H$9,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$6,S4=2020,U4=False), 'Rack Rates'!$H$10,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$7,S4=2020,U4=False), 'Rack Rates'!$H$11,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$8,S4=2020,U4=False), 'Rack Rates'!$H$12,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$9,S4=2020,U4=False), 'Rack Rates'!$H$13,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$10,S4=2020,U4=False), 'Rack Rates'!$H$14,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$11,S4=2020,U4=False), 'Rack Rates'!$H$15,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$13,S4=2020,U4=False), 'Rack Rates'!$H$17,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$14,S4=2020,U4=False), 'Rack Rates'!$H$18,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$15,S4=2020,U4=False), 'Rack Rates'!$H$19,
AND(R4='Rack Rates'!$H$2,Q4=Data!$A$16,S4=2020,U4=False), 'Rack Rates'!$H$20,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$4,S4=2020,U4=False), 'Rack Rates'!$K$8,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$5,S4=2020,U4=False), 'Rack Rates'!$K$9,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$6,S4=2020,U4=False), 'Rack Rates'!$K$10,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$7,S4=2020,U4=False), 'Rack Rates'!$K$11,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$8,S4=2020,U4=False), 'Rack Rates'!$K$12,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$9,S4=2020,U4=False), 'Rack Rates'!$K$13,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$10,S4=2020,U4=False), 'Rack Rates'!$K$14,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$11,S4=2020,U4=False), 'Rack Rates'!$K$15,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$13,S4=2020,U4=False), 'Rack Rates'!$K$17,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$14,S4=2020,U4=False), 'Rack Rates'!$K$18,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$15,S4=2020,U4=False), 'Rack Rates'!$K$19,
AND(R4='Rack Rates'!$K$2,Q4=Data!$A$16,S4=2020,U4=False), 'Rack Rates'!$K$20,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$4,S4=2021,U4=True), 'Rack Rates'!$P$8,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$5,S4=2021,U4=True), 'Rack Rates'!$P$9,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$6,S4=2021,U4=True), 'Rack Rates'!$P$10,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$7,S4=2021,U4=True), 'Rack Rates'!$P$11,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$8,S4=2021,U4=True), 'Rack Rates'!$P$12,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$9,S4=2021,U4=True), 'Rack Rates'!$P$13,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$10,S4=2021,U4=True), 'Rack Rates'!$P$14,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$11,S4=2021,U4=True), 'Rack Rates'!$P$15,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$13,S4=2021,U4=True), 'Rack Rates'!$P$17,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$14,S4=2021,U4=True), 'Rack Rates'!$P$18,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$15,S4=2021,U4=True), 'Rack Rates'!$P$19,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$16,S4=2021,U4=True), 'Rack Rates'!$P$20,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$4,S4=2021,U4=True), 'Rack Rates'!$S$8,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$5,S4=2021,U4=True), 'Rack Rates'!$S$9,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$6,S4=2021,U4=True), 'Rack Rates'!$S$10,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$7,S4=2021,U4=True), 'Rack Rates'!$S$11,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$8,S4=2021,U4=True), 'Rack Rates'!$S$12,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$9,S4=2021,U4=True), 'Rack Rates'!$S$13,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$10,S4=2021,U4=True), 'Rack Rates'!$S$14,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$11,S4=2021,U4=True), 'Rack Rates'!$S$15,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$13,S4=2021,U4=True), 'Rack Rates'!$S$17,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$14,S4=2021,U4=True), 'Rack Rates'!$S$18,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$15,S4=2021,U4=True), 'Rack Rates'!$S$19,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$16,S4=2021,U4=True), 'Rack Rates'!$S$20,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$4,S4=2021,U4=True), 'Rack Rates'!$V$8,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$5,S4=2021,U4=True), 'Rack Rates'!$V$9,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$6,S4=2021,U4=True), 'Rack Rates'!$V$10,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$7,S4=2021,U4=True), 'Rack Rates'!$V$11,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$8,S4=2021,U4=True), 'Rack Rates'!$V$12,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$9,S4=2021,U4=True), 'Rack Rates'!$V$13,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$10,S4=2021,U4=True), 'Rack Rates'!$V$14,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$11,S4=2021,U4=True), 'Rack Rates'!$V$15,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$13,S4=2021,U4=True), 'Rack Rates'!$V$17,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$14,S4=2021,U4=True), 'Rack Rates'!$V$18,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$15,S4=2021,U4=True), 'Rack Rates'!$V$19,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$16,S4=2021,U4=True), 'Rack Rates'!$V$20,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$4,S4=2021,U4=True), 'Rack Rates'!$Y$8,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$5,S4=2021,U4=True), 'Rack Rates'!$Y$9,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$6,S4=2021,U4=True), 'Rack Rates'!$Y$10,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$7,S4=2021,U4=True), 'Rack Rates'!$Y$11,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$8,S4=2021,U4=True), 'Rack Rates'!$Y$12,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$9,S4=2021,U4=True), 'Rack Rates'!$Y$13,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$10,S4=2021,U4=True), 'Rack Rates'!$Y$14,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$11,S4=2021,U4=True), 'Rack Rates'!$Y$15,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$13,S4=2021,U4=True), 'Rack Rates'!$Y$17,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$14,S4=2021,U4=True), 'Rack Rates'!$Y$18,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$15,S4=2021,U4=True), 'Rack Rates'!$Y$19,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$16,S4=2021,U4=True), 'Rack Rates'!$Y$20,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$4,S4=2021,U4=False), 'Rack Rates'!$O$8,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$5,S4=2021,U4=False), 'Rack Rates'!$O$9,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$6,S4=2021,U4=False), 'Rack Rates'!$O$10,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$7,S4=2021,U4=False), 'Rack Rates'!$O$11,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$8,S4=2021,U4=False), 'Rack Rates'!$O$12,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$9,S4=2021,U4=False), 'Rack Rates'!$O$13,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$10,S4=2021,U4=False), 'Rack Rates'!$O$14,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$11,S4=2021,U4=False), 'Rack Rates'!$O$15,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$13,S4=2021,U4=False), 'Rack Rates'!$O$17,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$14,S4=2021,U4=False), 'Rack Rates'!$O$18,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$15,S4=2021,U4=False), 'Rack Rates'!$O$19,
AND(R4='Rack Rates'!$O$2,Q4=Data!$A$16,S4=2021,U4=False), 'Rack Rates'!$O$20,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$4,S4=2021,U4=False), 'Rack Rates'!$R$8,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$5,S4=2021,U4=False), 'Rack Rates'!$R$9,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$6,S4=2021,U4=False), 'Rack Rates'!$R$10,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$7,S4=2021,U4=False), 'Rack Rates'!$R$11,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$8,S4=2021,U4=False), 'Rack Rates'!$R$12,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$9,S4=2021,U4=False), 'Rack Rates'!$R$13,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$10,S4=2021,U4=False), 'Rack Rates'!$R$14,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$11,S4=2021,U4=False), 'Rack Rates'!$R$15,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$13,S4=2021,U4=False), 'Rack Rates'!$R$17,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$14,S4=2021,U4=False), 'Rack Rates'!$R$18,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$15,S4=2021,U4=False), 'Rack Rates'!$R$19,
AND(R4='Rack Rates'!$R$2,Q4=Data!$A$16,S4=2021,U4=False), 'Rack Rates'!$R$20,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$4,S4=2021,U4=False), 'Rack Rates'!$U$8,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$5,S4=2021,U4=False), 'Rack Rates'!$U$9,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$6,S4=2021,U4=False), 'Rack Rates'!$U$10,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$7,S4=2021,U4=False), 'Rack Rates'!$U$11,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$8,S4=2021,U4=False), 'Rack Rates'!$U$12,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$9,S4=2021,U4=False), 'Rack Rates'!$U$13,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$10,S4=2021,U4=False), 'Rack Rates'!$U$14,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$11,S4=2021,U4=False), 'Rack Rates'!$U$15,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$13,S4=2021,U4=False), 'Rack Rates'!$U$17,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$14,S4=2021,U4=False), 'Rack Rates'!$U$18,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$15,S4=2021,U4=False), 'Rack Rates'!$U$19,
AND(R4='Rack Rates'!$U$2,Q4=Data!$A$16,S4=2021,U4=False), 'Rack Rates'!$U$20,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$4,S4=2021,U4=False), 'Rack Rates'!$X$8,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$5,S4=2021,U4=False), 'Rack Rates'!$X$9,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$6,S4=2021,U4=False), 'Rack Rates'!$X$10,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$7,S4=2021,U4=False), 'Rack Rates'!$X$11,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$8,S4=2021,U4=False), 'Rack Rates'!$X$12,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$9,S4=2021,U4=False), 'Rack Rates'!$X$13,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$10,S4=2021,U4=False), 'Rack Rates'!$X$14,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$11,S4=2021,U4=False), 'Rack Rates'!$X$15,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$13,S4=2021,U4=False), 'Rack Rates'!$X$17,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$14,S4=2021,U4=False), 'Rack Rates'!$X$18,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$15,S4=2021,U4=False), 'Rack Rates'!$X$19,
AND(R4='Rack Rates'!$X$2,Q4=Data!$A$16,S4=2021,U4=False), 'Rack Rates'!$X$20,
AND(R4<>99999), "...")回答 1
Web Applications用户
回答已采纳
发布于 2020-03-03 15:10:29
您需要更新您的Rack Rate数据一点。
添加到您的"Rack Rate“技术行中，其中将包含进一步映射的键--只是将变量(即Low2020FALSE)相加在一起。
然后使用下面的公式来获得所需的费率
=INDEX('Rack Rates'!$A:$Y,
MATCH(P4,'Rack Rates'!$A:$A,0),
MATCH(Q4&R4&T4,'Rack Rates'!$A$1:$Y$1,0))页面原文内容由Web Applications提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://webapps.stackexchange.com/questions/139154
复制相关文章
相似问题
腾讯云开发者
