欢迎来到Smartsheet论坛档案


在这个论坛的帖子不再被监控的准确性和他们的内容可能不再是最新的。如果这里有你感兴趣的讨论，你想找到(或创建)一个更最新的版本，请访问当前论坛。

自动识别特定父级的子级

thadr
thadr ✭
在2017年发布的文章

社区团队好!

我正在为我的项目计划自动化一个% Effort栏。我需要在其相关的子节点的父节点级别上平均%的努力。我想要自动完成，而不需要识别父节点下的子节点。

我可以使用=avg(children())，但我需要在公式中指定子单元格。我希望公式能够自动识别父单元格下的子单元格并计算平均值。

有办法做到这一点吗?

任何帮助都将非常感激。

提前感谢。

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

评论

  • Shaine格林伍德
    Shaine格林伍德 员工

    你好,

    使用没有单元格引用的CHILDREN()函数将使其自动引用该单元格的所有直接子单元。您所需要做的就是确保子任务在正确的父任务下面缩进。

    关于CHILDREN功能的更多信息可以在帮助中心找到:https://help.smartsheet.com/function/children

    截屏时间2017-09-14上午9.30.50

    截屏时间2017-09-14上午9.30.58 .png

    · 在Facebook上分享 在Twitter上分享
  • thadr
    thadr ✭
    编辑09/14/17

    谢谢你提供的信息，肖恩。是的，我知道这一点。我用的是最高级别的公式。

    也许我的要求应该更明确一点。当你的计划中有多个父母/子女关系时，我的要求更多，这种情况几乎总是存在的。你的子任务是其他子任务的父任务，以此类推。是否存在一个公式，仅捕获与父级(级别1)直接关联的子级的信息，而无需指定单元格。我只想捕获直接在父层下面的子层，而不是下面的额外的子层。

    同样，我不想手动指定子单元格。我希望公式自动识别哪些是直接的孩子，只捕获那些第一级的孩子。

    谢谢

    · 在Facebook上分享 在Twitter上分享
  • 罗宾·舍伍德
    罗宾·舍伍德 员工

    “子女”功能只适用于直系子女，而不适用于孙辈。您需要将其添加到每个级别以获得汇总汇总。如果每一层都加了，那么它就会往上卷。

    Children函数可以将单元格ref作为参数，因此您甚至不需要将其放在同一行/列中。例如:=CHILDREN([%Effort]6)如果你这样做，而不是将其添加到实际列中，你可以在不将值放在列中并影响聚合的情况下计算级别。

    截屏时间2017-09-15上午11.07.46

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

    谢谢你的澄清，罗宾。我知道肖恩是对的。我试图设置一个模板，用户将不必在每次创建父/子关系时添加公式。我想让这张纸自动完成。

    我得到的最接近的方法是让用户将公式复制到他们创建的行。它可以简单到将公式拖到列中，而不是复制粘贴到单个单元格中。用户将在一列中输入% Effort，我的公式将在另一列中滚动它。如果存在子节点，它只是复制用户输入的值。我在父级使用=AVG(Children())公式。

    http://publish.smartsheet.com/cab882d657af4f59ae5d49a83129b9d1

    截图来自2017-09-15 14-04-14.png

    · 在Facebook上分享 在Twitter上分享
这次讨论已经结束了。

类别

Hi @ker9<\/a> <\/p>

That limit is specific to bulk-uploading the emails into the group, to ensure that the request is processed successfully. I don't believe there's currently a hard limit for the number of users that can be in a group. I have seen some groups with around 10,000 contacts, if that helps. <\/p>

Cheers,<\/p>

Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":442,"urlcode":"groups-and-group-management","name":"Groups and Group Management"}]},{"discussionID":106588,"type":"question","name":"If formula with several criteria","excerpt":"Hi, I was working on a formula last week, but I am still struggling with a second criteria on this. the way its set up, updating the field depending on dates being input into others. this works well as follows; =IFERROR(IF([Client review Required]@row = \"Yes\", IF([Actual Submit Date]@row = \"//www.santa-greenland.com/community/discussion/13531/\", \"Pending\", (IF([Actual…","categoryID":322,"dateInserted":"2023-06-19T07:17:16+00:00","dateUpdated":"2023-06-19T07:50:51+00:00","dateLastComment":"2023-06-20T07:35:16+00:00","insertUserID":161866,"insertUser":{"userID":161866,"name":"Kirsteen Leckie","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Kirsteen%20Leckie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-20T07:32:56+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":161866,"lastUser":{"userID":161866,"name":"Kirsteen Leckie","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Kirsteen%20Leckie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-20T07:32:56+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":62,"score":null,"hot":3374408552,"url":"https:\/\/community.smartsheet.com\/discussion\/106588\/if-formula-with-several-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106588\/if-formula-with-several-criteria","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106588,"commentID":381239,"name":"Re: If formula with several criteria","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381239#Comment_381239","dateInserted":"2023-06-20T07:35:16+00:00","insertUserID":161866,"insertUser":{"userID":161866,"name":"Kirsteen Leckie","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Kirsteen%20Leckie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-20T07:32:56+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-20T07:33:41+00:00","dateAnswered":"2023-06-20T04:25:04+00:00","acceptedAnswers":[{"commentID":381236,"body":"

@Kirsteen Leckie<\/a> <\/p>

I made some tweaks to the formula, please try below<\/p>

=IF([Client review Required]@row = \"Yes\", IF([Actual Submit Date]@row = \"//www.santa-greenland.com/community/discussion/13531/\", \"Pending\", (IF([Actual Compete date]@row = \"//www.santa-greenland.com/community/discussion/13531/\", \"Internal Review\", (IF([Actual Date to Client]@row = \"//www.santa-greenland.com/community/discussion/13531/\", \"Send To Client\", (IF([Actual Client comments]@row = \"//www.santa-greenland.com/community/discussion/13531/\", \"Client Review\", (IF([Actual Piramal Response]@row = \"//www.santa-greenland.com/community/discussion/13531/\", \"Addressing Comments\", (IF([Actual Final Client Approval]@row = \"//www.santa-greenland.com/community/discussion/13531/\", \"Client Approval\", (IF([Actual submission to QA]@row = \"//www.santa-greenland.com/community/discussion/13531/\", \"QA Review\", (IF([Actual Final or QA Approval]@row = \"//www.santa-greenland.com/community/discussion/13531/\", \"QA Review\", \"Approved\"))))))))))))))), IF([Client review Required]@row = \"No\", IF([Actual Final or QA Approval]@row = \"//www.santa-greenland.com/community/discussion/13531/\", \"QA Review\", \"Approved\")))\n<\/pre>"}]}},"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":106601,"type":"question","name":"Please can someone help with this formula","excerpt":"I am trying to calculate how many \"qualification\" stages there are in a given fiscal period for example Q4-2023, I know I am missing something but every variation of Count if formulas I have tried do not seem to work, it will be something really simple, that I am trying to make to complicated, but any help will be…","categoryID":322,"dateInserted":"2023-06-19T15:46:15+00:00","dateUpdated":"2023-06-19T15:48:22+00:00","dateLastComment":"2023-06-19T15:54:15+00:00","insertUserID":150472,"insertUser":{"userID":150472,"name":"Mel C","url":"https:\/\/community.smartsheet.com\/profile\/Mel%20C","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T18:04:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":150472,"lastUserID":144360,"lastUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-20T12:11:18+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":28,"score":null,"hot":3374380230,"url":"https:\/\/community.smartsheet.com\/discussion\/106601\/please-can-someone-help-with-this-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106601\/please-can-someone-help-with-this-formula","format":"Rich","lastPost":{"discussionID":106601,"commentID":381177,"name":"Re: Please can someone help with this formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381177#Comment_381177","dateInserted":"2023-06-19T15:54:15+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-20T12:11:18+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/MRW4VGYRA7HG\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-19T17:03:50+00:00","dateAnswered":"2023-06-19T15:54:15+00:00","acceptedAnswers":[{"commentID":381177,"body":"
It needs to be a countifs instead of countif<\/p>
=COUNTIFS([column4]2:[column4]5,\"Qualification\",[column5]2:[column5]5,\"Q4-2023\")<\/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&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&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 Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
             

               

                
热门文章

               

               
    
                
  • 组——一个组中有多少用户?
    帮助文件显示，您可以批量上传1000封邮件到一个群组。这是组中的最大用户数吗?我找不到说明一个组中可以有多少用户的文档。蒂雅
    • 
                
  • 有几个条件的公式
    嗨，我上周在做一个公式，但我仍然在努力解决这个问题的第二个标准。它的设置方式，根据输入的日期更新字段。它的工作原理如下:=IFERROR(IF([Client review Required]@row = "Yes"， IF([实际提交日期]@row = "" "Pending"， (IF([实际…]
    • 
                
  • 请问有人能帮我弄一下这个公式吗
    我试图计算有多少“资格”阶段有一个给定的财政期间，例如第四季度-2023年，我知道我错过了一些东西，但计数的每一个变化，如果我尝试过的公式似乎不起作用，这将是一些非常简单的东西，我试图使复杂，但任何帮助将是……
    •