从其他工作表中提取文本

菲尔-史密斯
编辑12/09/19 公式和函数

我有一个项目风险和问题日志,并希望通过前5名的风险和问题通过另一张表(基于风险评分)。

风险和问题日志有一个文本描述(有一个风险....)和一个风险评分(5)。

我理解细胞连接,但这不会起作用,因为前5个风险和问题会定期发生变化。

是否有一种方法可以执行查找(vlookup?)并自动将前5个风险和问题拉到另一个工作表?

我需要捕捉风险或问题的“文本描述”和分数,而不必将数据从一张表复制到另一张表。

很高兴分享样品表来进一步解释我正在努力实现的目标。

提前感谢。

菲尔。

评论

  • 吉姆钩
    吉姆钩 ✭✭✭✭✭✭

    菲尔,

    Smartsheet最近推出了两个新的高级功能,SMALL和LARGE,允许您扫描一系列单元格并挑选出第一大,第二大或最小的单元格等。您可能必须在LOOKUP或VLOOKUP函数中使用LARGE,以便可以在下一列(或前一列)中返回文本和类似的公式,以确定风险级别。作为SMALL或LARGE函数的一部分,您必须指定要返回的最小或最大的值,当我引入具有顺序数字的数据时,我通常使用索引列,在您的示例中从1到5。这样每行的方程都是一样的。只要记住使用$符号使函数范围是绝对的。

  • 谢谢这个,听起来很像我想要实现的。

    我想我理解了对数字的查找,但我不知道如何在相邻的列中拉出文本。

    C1 C2

    这是课文4

    这是文本25

    这是文本31

    这是文本43

    我希望能够在C2中找到最高的2个分数(作为一个例子),并将它们与C1中的相关文本一起拉到我的另一个表中。理想情况下,我希望在另一张纸上保持两列分开。

    任何例子(即使只是上面的例子)都会给我一些有用的东西。

    再次感谢

  • 吉姆钩
    吉姆钩 ✭✭✭✭✭✭
    编辑02/21/18

    Phil, LOOKUP和VLOOKUP函数允许您指定将引用数组的哪一列(您正在处理的行和列的范围)带回当前单元格。这一开始有点棘手,因为这两个函数都要求搜索值在数组的第一列中,一旦找到它,它就可以返回第二列中的值,例如,这将是文本。因此,在您的示例中,上面的表将无法工作,因为您正在搜索的项在第二列中。如果您将这两列颠倒,使数字位于文本的左侧(或将文本复制到风险数字右侧的列中),则在您重新排列风险数字和文本并将风险数字置于参考范围的第1列之后,下面的公式将返回文本。

    第一行C2 C1

    这是文本

    这是文本2

    这是文本3

    这是文本4

    =查找(5美元加元[2]2:美元(C1) 5美元,2,假)

    上面的公式将返回“这是文本2”,因为它在第一列中查找5,并返回引用的第二列中的值。这个公式可以放在目标工作表的任何地方,它仍然会返回相同的结果。我现在没有时间详细介绍如何使用LARGE函数来找到最大风险数。如果数据中存在两个或更多的5级风险,则可能存在问题,因为它可能两次都拾取第一个风险。我所知道的唯一方法是创建另一个列,在其中添加一个小值(比如0.001倍的行号),这样第一个5将比第二个5略小,如果您明白我的意思的话。这样第二个5就是最大的数第一个5就是第二大的数。如果这让你困惑了,很抱歉。如果你明天还有问题的话,我应该还有时间。

  • 我现在让每个部分单独工作。前xx个条目与额外的乘数(0.00x)配合得很好,我还可以为每个条目提取相关的文本。

    把这两个公式放在一起的最好方法是什么?我应该先使用LARGE函数,然后使用VLOOKUP还是反过来?

    同样,任何例子都是非常有用的。

    谢谢

    菲尔。

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

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

请查看公式手册模板!
@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":48,"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":37,"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":[]}">

公式和函数趋势