这是我的谷歌工作表中的一个真正有效的公式。我有5个单元格,用户可以从数据验证下拉列表中选择值。然后,此公式选择要过滤的工作表,然后根据这些值进行过滤。显然,我的问题是速度。所以我希望,如果我创建一个或多个自定义函数,我将提高我的计算性能。我的问题是,什么是最好的继续下去的方式。创建一个函数,或者我应该将其分解为多个函数,并从多个自定义函数中构建一个公式。我会看到改进吗?我还在考虑在用户单击图像按钮之前不要设置公式(以避免在用户进行选择时公式不断地重新计算。
=if(E4= "View All Contacts", filter('View All Contacts'!$A$1:$L$3999,if(I8<>"",'View All Contacts'!A:A =I8,'View All Contacts'!A:A <>""),if(E7<>"",'View All Contacts'!M:M =E7,'View All Contacts'!A:A <>""),if(E9<>"",'View All Contacts'!O:O =E9,if(E8 = "All Mile Stone Anniversaries",('View All Contacts'!N:N = (year(NOW()) - 1 )) + ('View All Contacts'!N:N = (year(Now()) - 5 ) )+ ('View All Contacts'!N:N = (year(Now()) - 10 ) )+ ('View All Contacts'!N:N = (year(Now()) - 15 ) )+ ('View All Contacts'!N:N = (year(Now()) - 20 )) + ('View All Contacts'!N:N = (year(Now()) - 25 ) )+ ('View All Contacts'!N:N = (year(Now()) - 30 ) )+ ('View All Contacts'!N:N = (year(Now()) - 40 ) )+ ('View All Contacts'!N:N = (year(Now()) - 50 )) + ('View All Contacts'!N:N = (year(Now()) - 60 ) )+ ('View All Contacts'!N:N = (year(Now()) - 70 )) + ('View All Contacts'!N:N = (year(Now()) - 75 ) )+ ('View All Contacts'!N:N = (year(Now()) - 80 ) )+ ('View All Contacts'!N:N = (year(Now()) - 90 )) + ('View All Contacts'!N:N = (year(Now()) - 100 )) + ('View All Contacts'!N:N = (year(Now()) - 125 ) )+ ('View All Contacts'!N:N = (year(Now()) - 150 ) )+ ('View All Contacts'!N:N = (year(Now()) - 175 )) + ('View All Contacts'!N:N = (year(Now()) - 200 )),If(E8 = ">100", ('View All Contacts'!N:N < (year(Now())) - 100) * ('View All Contacts'!N:N <>""), If(E8 >0, 'View All Contacts'!N:N = (year(NOW()) - E8 ),'View All Contacts'!A:A <>""))))),if(E4 = "View Active Contacts",filter('View Active Contacts'!$A$1:$L$3999,if(I8<>"",'View Active Contacts'!A:A =I8,'View Active Contacts'!A:A <>""),if(E7<>"",'View Active Contacts'!M:M =E7,'View Active Contacts'!A:A <>""),if(E9<>"",'View Active Contacts'!O:O =E9,if(E8 = "All Mile Stone Anniversaries",('View Active Contacts'!N:N = (year(NOW()) - 1 )) + ('View Active Contacts'!N:N = (year(Now()) - 5 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 10 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 15 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 20 )) + ('View Active Contacts'!N:N = (year(Now()) - 25 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 30 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 40 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 50 )) + ('View Active Contacts'!N:N = (year(Now()) - 60 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 70 )) + ('View Active Contacts'!N:N = (year(Now()) - 75 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 80 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 90 )) + ('View Active Contacts'!N:N = (year(Now()) - 100 )) + ('View Active Contacts'!N:N = (year(Now()) - 125 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 150 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 175 )) + ('View Active Contacts'!N:N = (year(Now()) - 200 )),If(E8 = ">100", ('View Active Contacts'!N:N < (year(Now())) - 100) * ('View Active Contacts'!N:N <>""), If(E8 >0, 'View Active Contacts'!N:N = (year(NOW()) - E8 ),'View Active Contacts'!A:A <>""))))),if(E4 = "View Contacts from Companies Pending Current Payments",filter('View Contacts from Companies Pending Current Payments'!$A$1:$L$3999,if(I8<>"",'View Contacts from Companies Pending Current Payments'!A:A =I8,'View Contacts from Companies Pending Current Payments'!A:A <>""),if(E7<>"",'View Contacts from Companies Pending Current Payments'!M:M =E7,'View Contacts from Companies Pending Current Payments'!A:A <>""),if(E9<>"",'View Contacts from Companies Pending Current Payments'!O:O =E9,if(E8 = "All Mile Stone Anniversaries",('View Contacts from Companies Pending Current Payments'!N:N = (year(NOW()) - 1 )) + ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 5 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 10 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 15 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 20 )) + ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 25 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 30 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 40 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 50 )) + ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 60 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 70 )) + ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 75 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 80 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 90 )) + ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 100 )) + ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 125 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 150 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 175 )) + ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 200 )),If(E8 = ">100", ('View Contacts from Companies Pending Current Payments'!N:N < (year(Now())) - 100) * ('View Contacts from Companies Pending Current Payments'!N:N <>""), If(E8 >0, 'View Contacts from Companies Pending Current Payments'!N:N = (year(NOW()) - E8 ),'View Contacts from Companies Pending Current Payments'!A:A <>""))))),if(E4 = "View Inactive Companies Contacts",filter('View Inactive Companies Contacts'!$A$1:$L$3999,if(I8<>"",'View Inactive Companies Contacts'!A:A =I8,'View Inactive Companies Contacts'!A:A <>""),if(E7<>"",'View Inactive Companies Contacts'!M:M =E7,'View Inactive Companies Contacts'!A:A <>""),if(E9<>"",'View Inactive Companies Contacts'!O:O =E9,if(E8 = "All Mile Stone Anniversaries",('View Inactive Companies Contacts'!N:N = (year(NOW()) - 1 )) + ('View Inactive Companies Contacts'!N:N = (year(Now()) - 5 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 10 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 15 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 20 )) + ('View Inactive Companies Contacts'!N:N = (year(Now()) - 25 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 30 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 40 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 50 )) + ('View Inactive Companies Contacts'!N:N = (year(Now()) - 60 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 70 )) + ('View Inactive Companies Contacts'!N:N = (year(Now()) - 75 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 80 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 90 )) + ('View Inactive Companies Contacts'!N:N = (year(Now()) - 100 )) + ('View Inactive Companies Contacts'!N:N = (year(Now()) - 125 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 150 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 175 )) + ('View Inactive Companies Contacts'!N:N = (year(Now()) - 200 )),If(E8 = ">100", ('View Inactive Companies Contacts'!N:N < (year(Now())) - 100) * ('View Inactive Companies Contacts'!N:N <>""), If(E8 >0, 'View Inactive Companies Contacts'!N:N = (year(NOW()) - E8 ),'View Inactive Companies Contacts'!A:A <>""))))),if(E4 = "View Contacts from Companies with Expired Memberships",filter('View Contacts from Companies with Expired Memberships'!$A$1:$L$3999,if(I8<>"",'View Contacts from Companies with Expired Memberships'!A:A =I8,'View Contacts from Companies with Expired Memberships'!A:A <>""),if(E7<>"",'View Contacts from Companies with Expired Memberships'!M:M =E7,'View Contacts from Companies with Expired Memberships'!A:A <>""),if(E9<>"",'View Contacts from Companies with Expired Memberships'!O:O =E9,if(E8 = "All Mile Stone Anniversaries",('View Contacts from Companies with Expired Memberships'!N:N = (year(NOW()) - 1 )) + ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 5 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 10 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 15 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 20 )) + ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 25 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 30 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 40 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 50 )) + ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 60 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 70 )) + ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 75 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 80 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 90 )) + ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 100 )) + ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 125 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 150 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 175 )) + ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 200 )),If(E8 = ">100", ('View Contacts from Companies with Expired Memberships'!N:N < (year(Now())) - 100) * ('View Contacts from Companies with Expired Memberships'!N:N <>""), If(E8 >0, 'View Contacts from Companies with Expired Memberships'!N:N = (year(NOW()) - E8 ),'View Contacts from Companies with Expired Memberships'!A:A <>""))))),if(E4 = "View Contacts from Potential Companies",filter('View Contacts from Potential Companies'!$A$1:$L$3999,if(I8<>"",'View Contacts from Potential Companies'!A:A =I8,'View Contacts from Potential Companies'!A:A <>""),if(E7<>"",'View Contacts from Potential Companies'!M:M =E7,'View Contacts from Potential Companies'!A:A <>""),if(E9<>"",'View Contacts from Potential Companies'!O:O =E9,if(E8 = "All Mile Stone Anniversaries",('View Contacts from Potential Companies'!N:N = (year(NOW()) - 1 )) + ('View Contacts from Potential Companies'!N:N = (year(Now()) - 5 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 10 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 15 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 20 )) + ('View Contacts from Potential Companies'!N:N = (year(Now()) - 25 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 30 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 40 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 50 )) + ('View Contacts from Potential Companies'!N:N = (year(Now()) - 60 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 70 )) + ('View Contacts from Potential Companies'!N:N = (year(Now()) - 75 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 80 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 90 )) + ('View Contacts from Potential Companies'!N:N = (year(Now()) - 100 )) + ('View Contacts from Potential Companies'!N:N = (year(Now()) - 125 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 150 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 175 )) + ('View Contacts from Potential Companies'!N:N = (year(Now()) - 200 )),If(E8 = ">100", ('View Contacts from Potential Companies'!N:N < (year(Now())) - 100) * ('View Contacts from Potential Companies'!N:N <>""), If(E8 >0, 'View Contacts from Potential Companies'!N:N = (year(NOW()) - E8 ),'View Contacts from Potential Companies'!A:A <>""))))),""))))))
发布于 2017-03-25 12:46:08
如果你将它分解成不同的函数,并从多个custom functions构建一个公式,那就更好了。正如你所说,如果你正在处理一张有很多过滤公式的工作表,那么performance will be slow。
以下是一些可能有帮助的参考资料:
发布于 2019-04-30 01:09:04
呀!我的眼睛只是呆滞地看着那个公式。我真的不明白你到底想做什么。一般而言,超过一行的电子表格公式是一种只写语言。
if(E4=“查看所有联系人”...
相反,请使用
if (E4=C2, filter(FilterRange,C2)
或者,如果您的标签与您正在过滤的内容不同...
if(E4= C2, filter(vlookup(FilterRange,C2,2)...
这允许您在公式之外设置匹配条件。
粉红色的盒子使用正则表达式匹配。这是非常强大的,但在大型数组上可能会很慢。绿色框使用简单的匹配,速度更快,但仅限于一项
橙色框显示结果:要么Critter以a开头,要么or类是fish。
通过在整个过程中使用命名范围,我拥有了一个更易于维护的电子表格。当您扩展命名范围时,它们在大多数情况下都会执行正确的操作。如果您在多个地方使用了命名范围,则只需编辑它一次。
https://stackoverflow.com/questions/43008749
复制相似问题