欢迎来到Smartsheet论坛档案


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

使用SMALL()对列表进行字母排序的改进方法

吉姆钩
吉姆钩 ✭✭✭✭✭✭
编辑12/09/19 在2017年发布的文章

我有一个项目仪表板，从其他几个表的数据链接，以显示项目财务和时间收费的概述。在一些情况下，我需要根据项目编号对结果进行排序，项目编号包括四个数字字符，偶尔后面跟着一个字母字符，我不能指望其中任何一个都是按字母顺序链接的。我找到了一种方法，将每个项目编号/字母转换成一个唯一的数字，准备按字母顺序排序，但这是非常困难的，直到最近当SMALL()和LARGE()函数被添加到Smartsheet时。最初使用最多100行× 100列的数组对包含100个条目的列表进行排序，每个单元格包含一个简单的IF()公式来对数据进行排序，现在减少到100个简单的SMALL()公式，每个公式查找下一个更大的值。旧的字母排序表是Smartsheet可以处理的极限，但现在相对较小。

感谢Smartsheet提供的这些新功能!

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

评论

这次讨论已经结束了。

类别

You da man, @Paul Newcome<\/a>! That works perfectly. Thank you!!!<\/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":106782,"type":"question","name":"IF cell contains specific letter then return value","excerpt":"I'd like to return a value of 120\/208 in Service Volts for Xfmr# ending in E value of 277\/480 for Xfmr# ending in X value of 120\/240 for Xfmr# ending with no letter Thanks","categoryID":322,"dateInserted":"2023-06-22T16:38:09+00:00","dateUpdated":"2023-06-22T16:45:09+00:00","dateLastComment":"2023-06-22T18:12:51+00:00","insertUserID":158055,"insertUser":{"userID":158055,"name":"mromaire","url":"https:\/\/community.smartsheet.com\/profile\/mromaire","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T18:12:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":158055,"lastUser":{"userID":158055,"name":"mromaire","url":"https:\/\/community.smartsheet.com\/profile\/mromaire","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T18:12:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":32,"score":null,"hot":3374911260,"url":"https:\/\/community.smartsheet.com\/discussion\/106782\/if-cell-contains-specific-letter-then-return-value","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106782\/if-cell-contains-specific-letter-then-return-value","format":"Rich","lastPost":{"discussionID":106782,"commentID":381817,"name":"Re: IF cell contains specific letter then return value","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381817#Comment_381817","dateInserted":"2023-06-22T18:12:51+00:00","insertUserID":158055,"insertUser":{"userID":158055,"name":"mromaire","url":"https:\/\/community.smartsheet.com\/profile\/mromaire","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T18:12:03+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\/RLY9EZQ8E5Y9\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-22T18:08:50+00:00","dateAnswered":"2023-06-22T17:35:06+00:00","acceptedAnswers":[{"commentID":381799,"body":"

Hi @mromaire<\/a>,<\/p>

Try this!<\/p>

=IF(RIGHT([xfmr#]@row, 1) = \"X\", \"277\/480\", IF(RIGHT([xfmr#]@row, 1) = \"E\", \"120\/208\", \"120\/240\"))<\/p>

Hope that helps!<\/p>

BRgds,<\/p>

-Ray<\/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":106771,"type":"question","name":"SUMIFS Formula for Column with Various Possible Returns","excerpt":"Hi there! I am a big Excel user switching to Smartsheet and need help with a SUMIFS formula. In Excel, the formula was: =SUM(SUMIFS('Outside Counsel Invoices'!$G:$G,'Outside Counsel Invoices'!$M:$M,\"FY23\",'Outside Counsel Invoices'!$D:$D,{\"=7607\",\"=7607*\"})) Since Wildcards (*) are not used in Smartsheet, I am struggling…","categoryID":322,"dateInserted":"2023-06-22T14:38:12+00:00","dateUpdated":null,"dateLastComment":"2023-06-22T18:57:32+00:00","insertUserID":162633,"insertUser":{"userID":162633,"name":"BuckeyeGirl72","url":"https:\/\/community.smartsheet.com\/profile\/BuckeyeGirl72","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T19:01:20+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"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-22T19:34:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":29,"score":null,"hot":3374907944,"url":"https:\/\/community.smartsheet.com\/discussion\/106771\/sumifs-formula-for-column-with-various-possible-returns","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106771\/sumifs-formula-for-column-with-various-possible-returns","format":"Rich","lastPost":{"discussionID":106771,"commentID":381826,"name":"Re: SUMIFS Formula for Column with Various Possible Returns","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381826#Comment_381826","dateInserted":"2023-06-22T18:57:32+00:00","insertUserID":45516,"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-22T19:34:53+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-22T18:38:09+00:00","dateAnswered":"2023-06-22T17:53:03+00:00","acceptedAnswers":[{"commentID":381809,"body":"

It looks like you have a mix of text strings and numerical values. Try the below. If that doesn't work then we do have one more option.<\/p>

SUMIFS({Range To Sum}, {Range To Evaluate}, OR(@cell = 7607, CONTAINS(\"7607\", @cell)))<\/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":[]}">

热门文章

  • IF/AND/BLANK公式逻辑

    我试图写一个公式来反映基于三个字段的进度%(使用条形符号)，其中一个字段，如果填写，计数为25%，第二个字段，如果填写，计数为25%，第三个字段，如果填写，计数为50%(等于100%)。我使用=IF(AND(NOT(ISBLANK)，但我收到错误。我…

  • IF单元格包含特定的字母，然后返回值

    我想返回以E结尾的Xfmr#的服务电压值为120/208，以X结尾的Xfmr#的值为277/480，以X结尾的Xfmr#的值为120/240，以无字母结尾

  • 具有各种可能收益的列的SUMIFS公式

    大家好!我是一个大的Excel用户切换到Smartsheet，需要帮助与SUMIFS公式。在Excel中，公式是:=SUM(SUMIFS('外部律师发票'!$G:$G，“外部律师发票”!$M:$M，“FY23”，“外部律师发票”!$D:$D，{“=7607”，“=7607*”}))由于通配符(*)未在Smartsheet中使用，我正在努力……