十字表公式极限错误

马特Metzger
马特Metzger ✭✭
在公式和函数

我目前得到一个与交叉表公式限制有关的错误。我不确定为什么会出现错误。到目前为止，我已经用SUMIFS公式引用了其他表格中的6列。我在下面添加2019公式时收到了错误。参考表上有17000条记录，这会导致问题吗?我需要完成2019年和2020年的参考资料，2020年我需要增加一列。我认为我最多只能从其他表格中引用10列，这应该会造成问题，对吧?谢谢你的帮助!

_amt =条件求和({2018},{2018 _acct},(电子邮件保护){2018 _subacct},(电子邮件保护)）

_amt =条件求和({2019},{2019 _acct},(电子邮件保护){2019 _subacct},(电子邮件保护)）

标签:
· 在Facebook上分享 在Twitter上分享

最佳答案

答案

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    你收到的确切错误是什么?

    thinkspi.com

    · 在Facebook上分享 在Twitter上分享
  • 马特Metzger
    马特Metzger ✭✭

    您只能从其他工作表中引用总计100000个单元格。

    · 在Facebook上分享 在Twitter上分享
  • 弗兰克·马林诺夫斯基
    弗兰克·马林诺夫斯基 ✭✭

    100,000个引用单元格是每个公式的限制，还是我所有智能表中所有公式的总数?

    · 在Facebook上分享 在Twitter上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    @Frank马林诺斯基它是所有的公式在一个单一的工作表。我还认为，这一限制已大幅提高。

    thinkspi.com

    · 在Facebook上分享 在Twitter上分享
  • soakley
    soakley ✭

    它还没有增加到足够.....

    将带有公式的列仅转换为值有帮助吗?

    image.png


    · 在Facebook上分享 在Twitter上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    @soakley你的特定错误是来自交叉表参考。如果您正在使用VLOOKUP，我建议替换为INDEX/MATCH。如果没有，则需要使用源工作表上的辅助列将所有行上的每个单元格连接在一起，使用公式将字符串移过来，然后在目标工作表上引用字符串。

    thinkspi.com

    · 在Facebook上分享 在Twitter上分享
  • 克里斯Kaliveas
    克里斯Kaliveas ✭✭✭✭

    @Paul新来的

    嗨,保罗,

    我想知道你是否碰巧知道我下面问题的答案。

    1. 如果我在一个工作表中有100行，并且在一个列公式(列1)中使用两个唯一的交叉表引用，然后在另一个列公式(列2)中使用与(列1)相同的两个唯一交叉表引用，那么针对100,000个单元格限制使用了多少个单元格?
    2. 如果在一个工作表中有100行，并且使用两个惟一的跨工作表引用(未在任何列或单元格公式中使用)使用摘要字段，然后在一个列公式中使用摘要字段，那么在100,000个限制下使用了多少个单元格?

    亲切的问候,

    屁股的

    · 在Facebook上分享 在Twitter上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    @Chris Kaliveas

    每个唯一的交叉表引用将算作对单元格数量的单个引用，因此实际上这取决于交叉表引用引用了多少单元格。重复这些引用(在目标工作表的多行上使用相同的公式)不会影响引用的单元格数量。

    因此，如果您的引用表包含1,000行，而您引用了两列，那么您已经使用了2,000个单元格，以达到100,000个限制。在另一个公式中再次使用这些交叉表引用不会增加引用的单元格计数。


    第二个问题的答案与第一个问题相同，因为交叉表参考是如何工作的。


    这就是INDEX/MATCH通常比VLOOKUP更好的原因之一。使用INDEX/MATCH，您只引用您实际想要使用的两个列，而使用VLOOKUP，您引用两者之间的每个列。假设您的参考表宽10列，长100行。要匹配的列是第一列，要提取的列是最后一列。这意味着对于VLOOKUP，您必须引用1,000个单元格，即使实际上您只需要其中的200个，而使用INDEX/MATCH仅引用您实际需要的列，您只引用您实际需要的200个单元格，而不是中间的800个单元格。


    ．


    然而……在同一工作表上有25,000,000单元格引用限制。假设我们只在表A中工作，并且有100行。如果我们对每一行使用这样的公式:

    (电子邮件保护)+(电子邮件保护)

    然后我们向25,000,000引用200个单元格(每行2个乘以100行)。

    但是，我们也使用设置来输出每一行的行号，这涉及引用整个列:

    =匹配((电子邮件保护)， Auto:自动，0)

    现在我们引用了100单元格(电子邮件保护)部分，但随后我们通过将100个单元格(Auto:Auto)放在每行上来引用100次。现在我们的计数是10100。

    thinkspi.com

    · 在Facebook上分享 在Twitter上分享
  • 克里斯Kaliveas
    克里斯Kaliveas ✭✭✭✭

    @Paul新来的


    嘿，Paul，谢谢你解释单元格和交叉表参考限制，因为有时很难在线理解智能表文档。再次感谢您的帮助，因为您的意见在设计解决方案的过程中发挥了重要作用。我们正在构建一个报告解决方案，从源系统导入数据，然后用汇总指标将这些数据显示给最终用户。简而言之，由于处理大量记录的跨表限制，我们将利用数据穿梭来更新基于4个其他汇总度量表的主度量表，每个汇总度量表的源数据来自其他3个表(4个汇总度量表共有12个表)。我们正在测试这个解决方案，所以让我们祈祷吧:-)

    亲切的问候,

    克里斯

    · 在Facebook上分享 在Twitter上分享
  • SS_New
    SS_New ✭

    @Paul新来的

    我正在努力解决我的表格上的10万份参考资料的问题。我在上面看到您建议在源端使用JOIN函数，并在目标端拆分它。我试着做这件事，但没能去上班。在这方面的任何帮助都将非常感谢。

    提前感谢。

    · 在Facebook上分享 在Twitter上分享

帮助文章参考资料欧宝体育app官方888

在智能表中创建和编辑公式

交叉表引用的公式组合

智能表功能列表

类别

想要直接在智能表中练习使用公式吗?

请查看公式手册模板!

进入赢取免费会议通行证到ENGAGE 2023

 了解更多
@Pamela Wagner<\/a> There's nothing in core Smartsheet. <\/p>

If you have a unique ID (like ticket number) that is in both sheets, you could use DataMesh if you have access to that or cross sheet formulas to pull in the data you want based on the matching unique ID.<\/p>

Otherwise, you'd have to use an Add-on. If you are interested in hearing about an add-on that can do this, send me a message at https:\/\/smartsheetguru.com\/contact<\/a><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106845,"type":"question","name":"Count function","excerpt":"Hi I need help with a formula. I have a column that I want to count but only based on a values in a different column. I have tried the following formula but it's not working and I can't figure out what I am doing wrong. =countif([Test Script #]:[Test Script #], [Pass ?]:[Pass ?], <> \"N\/A\") The Test Script # column is the…","categoryID":322,"dateInserted":"2023-06-23T17:50:06+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T19:30:43+00:00","insertUserID":127983,"insertUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":127983,"lastUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":44,"score":null,"hot":3375096049,"url":"https:\/\/community.smartsheet.com\/discussion\/106845\/count-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106845\/count-function","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106845,"commentID":382093,"name":"Re: Count function","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382093#Comment_382093","dateInserted":"2023-06-23T19:30:43+00:00","insertUserID":127983,"insertUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-23T19:30:46+00:00","dateAnswered":"2023-06-23T19:28:59+00:00","acceptedAnswers":[{"commentID":382092,"body":"

@Carol-Anne Cerbone<\/a> <\/p>

How about this?<\/p>

=COUNT([Test Script #]:[Test Script #]) - COUNTIF([Pass?]:[Pass?], =\"N\/A\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106844,"type":"question","name":"Avg number of selections per condition","excerpt":"Hi there, I need some help. I'm trying to calculate the avg number of issues per condition in our master tracker. The issues are in a multi-value cell {Rev Rec Master Tracker Range 1} while the condition is a single-value cell in {Rev Rec Master Tracker Range 2} with values either as \"Backlog\" or \"Net New\". I'd like to…","categoryID":322,"dateInserted":"2023-06-23T17:36:11+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T18:36:10+00:00","insertUserID":162710,"insertUser":{"userID":162710,"name":"Leah Schmid","url":"https:\/\/community.smartsheet.com\/profile\/Leah%20Schmid","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtenj_SBCEO-5DL1Uq6fCj1Eudu-GWv5LWQWq-DX-A=s96-c","dateLastActive":"2023-06-23T19:56:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162710,"lastUser":{"userID":162710,"name":"Leah Schmid","url":"https:\/\/community.smartsheet.com\/profile\/Leah%20Schmid","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtenj_SBCEO-5DL1Uq6fCj1Eudu-GWv5LWQWq-DX-A=s96-c","dateLastActive":"2023-06-23T19:56:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":36,"score":null,"hot":3375089541,"url":"https:\/\/community.smartsheet.com\/discussion\/106844\/avg-number-of-selections-per-condition","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106844\/avg-number-of-selections-per-condition","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106844,"commentID":382062,"name":"Re: Avg number of selections per condition","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382062#Comment_382062","dateInserted":"2023-06-23T18:36:10+00:00","insertUserID":162710,"insertUser":{"userID":162710,"name":"Leah Schmid","url":"https:\/\/community.smartsheet.com\/profile\/Leah%20Schmid","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtenj_SBCEO-5DL1Uq6fCj1Eudu-GWv5LWQWq-DX-A=s96-c","dateLastActive":"2023-06-23T19:56:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-23T18:35:49+00:00","dateAnswered":"2023-06-23T18:26:57+00:00","acceptedAnswers":[{"commentID":382060,"body":"

So for \"Backlog\" you would want it to count 3 Action Plan Notes and then divide by 2 \"Backlog\" entries? If so, try this:<\/p>

=COUNTM(COLLECT({Action Plan Notes}, {RR Type}, @cell = \"Backlog\")) \/ COUNTIFS({RR Type}, @cell = \"Backlog\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势

  • 只复制某些列到一个新的工作表?

    我正试图将两张纸结合在一起，一张是用于购票的新纸，另一张纸已被另一个团队使用，该团队有一个人的门票信息，但也有他们团队的额外信息。我不希望团队在填写表格的同时还必须在输入表格中输入数据。我…

  • 计数功能

    你好，我需要你帮我弄个公式。我有一列，我想计数，但只基于一个值在不同的列。我试过下面的公式，但它不起作用，我不知道我做错了什么。=countif([Test Script #]:[Test Script #]， [Pass ?]:[Pass ?]， <> "N/A") Test Script #列是…

  • 每个条件的平均选择数

    你好，我需要一些帮助。我正在计算主跟踪器中每个条件的平均问题数。问题在多值单元{Rev Rec Master Tracker Range 1}中，而条件是{Rev Rec Master Tracker Range 2}中的单值单元，值为“Backlog”或“Net New”。我愿意......