公式:嵌套IF语句中的5个复选框

系统
系统 员工管理
在公式和函数
这个讨论是由以下几个人的评论引起的:选中Box & IF语句．
· 在Facebook上分享 在Twitter上分享

答案

  • Gunn_Jack
    Gunn_Jack ✭✭✭✭

    你好,

    我知道这是一个旧的帖子，但我有一个类似的问题，希望有人能帮助。

    我有5个复选框，并基于这些被勾选或未勾选，我想在另一个单元格中显示的东西。

    它本质上是一个简单的决策树，我想工作如下:

    如果组2未打勾(或没有打勾)=组1

    如果组2被选中，但3、4、5和Close不=组2

    如果第2组和第3组被选中，但第4、5和Close没有=第3组

    如果组2,3和4被选中，但5和close不=组4

    如果第2、3、4和5组被选中，但close不是=第5组

    如果组2,3,4,5和Close被选中(所有被选中)= Close

    有什么方法比通过嵌套的if在每个可能的场景中构建更简单吗?我也不能完全得到我的头在上面。它是一个线性结构，例如，3不会在2之前打勾，我知道这避免了我必须做每一个组合。

    非常感谢任何帮助。

    谢谢,

    杰克

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

    @Gunn_Jack我最近提供了一个非常类似的问题的解决方案。我看看能不能找到。

    thinkspi.com

    · 在Facebook上分享 在Twitter上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭
    编辑05/13/20

    @Gunn_Jack看起来是在你的另一个帖子上…


    Work backwards with your IF statements. Nested IFs stop on the first true value, so if it makes it to the next one, then the previous IF(s) MUST be false.<\/p>

    =IF(Close@row = 1, "Close", <\/strong><\/p>

    Starting with this means that everything that follows automatically assumes that Close@row is NOT checked.<\/p>

    =IF(Close@row = 1, "Close", IF([Group 5]@row = 1, "Group 5", <\/strong><\/p>

    Anything coming after this portion automatically assumes that Close@row and [Group 5]@row are both unchecked. So we continue with this logic that "if it has made it this far then everything before it must be false" and work (almost) the rest of your requirements in using the same logic.<\/p>

    =IF(Close@row = 1, "Close", IF([Group 5]@row = 1, "Group 5", IF([Group 4]@row = 1, "Group 4", IF([Group 3]@row = 1, "Group 3", IF([Group 2]@row = 1, "Group 2", <\/strong><\/p>

    Now that we only have one option left (because to get this far everything prior must be false which means all of those boxes are unchecked), we can just use the "if false" portion of the final IF statement to say that if everything prior is false, "Group 1". Then we close out all of the IF statements all at once and your formula is complete.<\/p>

    =IF(Close@row = 1, "Close", IF([Group 5]@row = 1, "Group 5", IF([Group 4]@row = 1, "Group 4", IF([Group 3]@row = 1, "Group 3", IF([Group 2]@row = 1, "Group 2", "Group 1")))))<\/strong><\/p>","bodyRaw":"[{\"insert\":\"Work backwards with your IF statements. Nested IFs stop on the first true value, so if it makes it to the next one, then the previous IF(s) MUST be false.\\n\\n=\"},{\"attributes\":{\"bold\":true},\"insert\":\"IF(Close@row = 1, \\\"Close\\\", \"},{\"insert\":\"\\nStarting with this means that everything that follows automatically assumes that Close@row is NOT checked.\\n\\n=IF(Close@row = 1, \\\"Close\\\", \"},{\"attributes\":{\"bold\":true},\"insert\":\"IF([Group 5]@row = 1, \\\"Group 5\\\", \"},{\"insert\":\"\\nAnything coming after this portion automatically assumes that Close@row and [Group 5]@row are both unchecked. So we continue with this logic that \\\"if it has made it this far then everything before it must be false\\\" and work (almost) the rest of your requirements in using the same logic.\\n\\n=IF(Close@row = 1, \\\"Close\\\", IF([Group 5]@row = 1, \\\"Group 5\\\", \"},{\"attributes\":{\"bold\":true},\"insert\":\"IF([Group 4]@row = 1, \\\"Group 4\\\", IF([Group 3]@row = 1, \\\"Group 3\\\", IF([Group 2]@row = 1, \\\"Group 2\\\", \"},{\"insert\":\"\\nNow that we only have one option left (because to get this far everything prior must be false which means all of those boxes are unchecked), we can just use the \\\"if false\\\" portion of the final IF statement to say that if everything prior is false, \\\"Group 1\\\". Then we close out all of the IF statements all at once and your formula is complete.\\n\\n=IF(Close@row = 1, \\\"Close\\\", IF([Group 5]@row = 1, \\\"Group 5\\\", IF([Group 4]@row = 1, \\\"Group 4\\\", IF([Group 3]@row = 1, \\\"Group 3\\\", IF([Group 2]@row = 1, \\\"Group 2\\\", \"},{\"attributes\":{\"bold\":true},\"insert\":\"\\\"Group 1\\\")))))\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2020-05-12T13:06:04+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-23T19:11:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/249788#Comment_249788","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/249788#Comment_249788


    那没用吗?你需要一些不同的东西吗?

    thinkspi.com

    · 在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”。我愿意......