仪表板-交叉表公式的数据矩阵

pkitzens
pkitzens ✭✭✭
编辑12/09/19 在公式和函数

我已经创建了广泛的数据矩阵来计算我们公司中个人的职位数量和美元金额，以跟踪产能。然而，当我们的表格变大时，公式就不能计算了。我收到一条错误信息:

"某些跨工作表公式无法更新，因为此工作表从其他工作表引用的单元格数量超过了允许的最大数量(25000)"

目前我有3个独立的数据矩阵来计算我们的仪表板上需要的信息。是否有办法解决这个问题，或者是否会增加对参考细胞的限制?我在所有这些单元格中都得到了“#计算”。

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

评论

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

    我能想到的唯一解决这个问题的方法是将矩阵中的数据分散到多个表格中，然后将这些数据编译到“主矩阵”中。

    thinkspi.com

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

    这就是我一直在做的，但在我知道它之前，我将有近5个矩阵。如果矩阵变得太大，主表是否会出现问题?

    · 在Facebook上分享 在Twitter上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭
    编辑04/10/19

    确实如此。如果有10个不同的区域引用母表和3个矩阵，则向母表添加的每一行都是30个额外的单元格链接。

    您可能需要在Master工作表上找到一些常见的标准并使用辅助列(我倾向于使用复选框)。然后，您可以使用IF语句根据某些条件选中复选框，并在交叉表公式中引用复选框列。当查看多组相似的条件时，JOIN语句在helper列中也很有用。

    您可以使用一个简单的JOIN语句将这三个列合并为一个列，而不是查看Project Lead的一个列、第一助理的另一个列和第二助理的第三个列，以查看是否在这三个列中列出了一个名字。

    现在你只有一个范围看你的交叉表参考，可以使用查找(“文本”，@cell)> 0作为从JOIN函数中搜索整个字符串以查找特定名称的条件。

    thinkspi.com

    · 在Facebook上分享 在Twitter上分享

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

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

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!
Hey @Marilen.Navarro103391<\/a> <\/p>

Please use the formula I provided. It was intentional for the character \"~\" to be included. <\/p>

=MID(Name@row, FIND(\"~\", SUBSTITUTE(Name@row, \"-\", \"~\", 5)) + 1, FIND(\"~\", SUBSTITUTE(Name@row, \"-\", \"~\", 6)) - 1 - FIND(\"~\", SUBSTITUTE(Name@row, \"-\", \"~\", 5)))<\/p>

Kelly<\/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":106552,"type":"question","name":"Problem w\/Index - Match formula","excerpt":"I using the following formula, =INDEX({Contract Focal Point}, MATCH([Contract No.]@row, {Contract No.}), 0) And it works for the first row when i input in the contract number into the column. On subsequent rows when i input the contract number it pulls back incorrect information and if I put in a fictious number it pulls…","categoryID":322,"dateInserted":"2023-06-16T15:13:32+00:00","dateUpdated":null,"dateLastComment":"2023-06-16T15:36:24+00:00","insertUserID":162467,"insertUser":{"userID":162467,"name":"Ed Smartsheet","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Ed%20Smartsheet","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJyYOjKt3k3tneIFbUPuoJzZbnd9quLS-Wdt5b7l=s96-c","dateLastActive":"2023-06-16T19:45:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-16T17:50:53+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":32,"score":null,"hot":3373859396,"url":"https:\/\/community.smartsheet.com\/discussion\/106552\/problem-w-index-match-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106552\/problem-w-index-match-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106552,"commentID":381031,"name":"Re: Problem w\/Index - Match formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381031#Comment_381031","dateInserted":"2023-06-16T15:36:24+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-16T17:50:53+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"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-16T19:45:15+00:00","dateAnswered":"2023-06-16T15:36:24+00:00","acceptedAnswers":[{"commentID":381031,"body":"

Hey @Ed Smartsheet<\/a> <\/p>

In addition to the IFERROR, it looks like you have the , 0 in the INDEX portion of the formula instead of the MATCH. Try moving it in one:<\/p>

=INDEX({Contract Focal Point}, MATCH([Contract No.]@row, {Contract No.}, 0))<\/strong><\/p>

Cheers,<\/p>

Genevieve<\/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":106550,"type":"question","name":"Invalid Operation","excerpt":"Good morning, I am trying to sum the entries that are greater than 30 but less than 60 days old. =SUMIFS({011-AP Archive -# of Days to Complete}, >30, {011-AP Archive -# of Days to Complete}, <60) I was able to do this same formula with a COUNTIFS but its' not working the same, what am I missing.","categoryID":322,"dateInserted":"2023-06-16T15:00:58+00:00","dateUpdated":null,"dateLastComment":"2023-06-16T15:22:46+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":156010,"lastUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":36,"score":null,"hot":3373857824,"url":"https:\/\/community.smartsheet.com\/discussion\/106550\/invalid-operation","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106550\/invalid-operation","format":"Rich","lastPost":{"discussionID":106550,"commentID":381027,"name":"Re: Invalid Operation","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381027#Comment_381027","dateInserted":"2023-06-16T15:22:46+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22: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-16T15:22:31+00:00","dateAnswered":"2023-06-16T15:19:44+00:00","acceptedAnswers":[{"commentID":381026,"body":"

Try this:<\/p>

SUMIFS({011-AP Archive -# of Days to Complete}, {011-AP Archive -# of Days to Complete}, > 30, {011-AP Archive -# of Days to Complete}, < 60)<\/p>

With SUMIFS, you have to include the range to SUM separately from the ranges to compare. In your scenario, they are the same, but can be different.<\/p>

SUMIFS( range, criterion_range1, criterion1, [ criterion_range2, ​criterion2​... ])<\/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":[]}],"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":[]}">

公式和函数趋势

  • 分割文本

    有人能帮帮我吗?我需要一个关于如何分割文本的公式。例LOW - VS-87 - Service-FS电池更换铅-MEGA MALL-1259410我需要一个只写MEGA MALL的专栏。

  • 问题w/索引匹配公式

    使用以下公式，=INDEX({合同焦点}，MATCH([合同编号])。@row，{合同号})，0)当我在列中输入合同号时，它对第一行起作用。在随后的行中，当我输入合同号时，它会拉回不正确的信息，如果我输入一个虚构的数字，它会拉……

  • 无效的操作

    早上好，我正在尝试求和大于30但小于60天的条目。=SUMIFS({011-AP Archive -# of Days to Complete}， >30， {011-AP Archive -# of Days to Complete}， <60)我能够用COUNTIFS做同样的公式，但它的工作方式不一样，我错过了什么。