与多选择列的百分比匹配公式

eceisin
eceisin ✭✭✭✭✭✭
编辑12/09/19 在公式与函数

你好，我在为导师和徒弟配对。我们希望根据他们都在多选择列中选择的属性来匹配他们。也就是说，选择你最擅长的三项能力。现在我想要建立一个公式来确定被指导者的选择与被指导者的匹配百分比。即被指导者1选择了A, B和c，导师1选择了A, D, E;导师2选A、B、E;导师3选择了A, B, c，所以导师1应该是33%，导师2应该是66%，而导师3应该是100%。用新的多选择下拉列最好的方法是什么?

谢谢,

莉斯

标签:

评论

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

    说实话，如果你让导师或徒弟使用3个单选列而不是同时使用多选列，会容易得多。这是一种选择吗?

    thinkspi.com

  • eceisin
    eceisin ✭✭✭✭✭✭

    应用程序已经完成。我可以把它分成三列。有什么好办法把这三种选择分开吗?

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

    好的。是否总是有3个选择，或者有可能有人只会选择一个或两个?

    thinkspi.com

  • eceisin
    eceisin ✭✭✭✭✭✭

    有时用户会选择一个或两个。

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭
    编辑11/19/19

    好的。你会用哪种方式进行比较?导师与学员##%匹配，还是学员与导师##%匹配?

    编辑澄清:

    基本上我是在问…

    如果被指导者只选择了一个选项，而导师选择了3个…

    被指导者与导师的匹配度只有33%，但导师与被指导者的匹配度是100%。

    ．

    也可以反过来问……如果指导者只选了一个，而被指导者选了三个。

    你想把什么放在首位?

    thinkspi.com

  • eceisin
    eceisin ✭✭✭✭✭✭

    导师与被指导者匹配

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

    好的。在这里是到已发表的表格的链接。在这张表中，您将看到我解析出的学员选择。然后，我根据导师和被指导者之间的匹配数量生成了一个数字，并将其分为被指导者选择的数量。这让我知道导师与学员之间的契合度有多高，这是基于学员认为重要的东西。

    请让我知道你是否需要进一步的澄清或帮助修改后，以满足你的具体需求。

    thinkspi.com

  • eceisin
    eceisin ✭✭✭✭✭✭

    这太神奇了!!非常感谢!

  • Pam弗格森
    Pam弗格森 优等生

    @保罗新来的你有时间联系一下吗?我们有需求，但我想知道你们解决方案的规模，并想了解更多。

帮助文章资源欧宝体育app官方888

在Smartsheet中创建和编辑公式

交叉表引用的公式组合

Smartsheet功能列表

类别

Hi @chelsi.johnson<\/a> <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=COUNTIFS(Resolution:Resolution, CONTAINS(\"Duplicate\", @cell))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-22 17:44:54","updateUser":{"userID":152723,"name":"chelsi.johnson","url":"https:\/\/community.smartsheet.com\/profile\/chelsi.johnson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wx9nv0OzvvM!c5lk5xPUwzs!ncykvroFsEa","dateLastActive":"2022-09-22T19:03:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":95792,"type":"question","name":"Two Dates, one independent and one dependent in one cell?","excerpt":"Does anyone know how or if I could create a dependent date based on an independent date in the same cell? I don't think this would work and may have to create a different column dependent on the independent date. The scenario is say meeting A is the independent date, Meeting B is a prep for Meeting A, which will take place…","categoryID":322,"dateInserted":"2022-09-22T15:33:54+00:00","dateUpdated":null,"dateLastComment":"2022-09-22T17:11:06+00:00","insertUserID":152722,"insertUser":{"userID":152722,"name":"kgibson","url":"https:\/\/community.smartsheet.com\/profile\/kgibson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-22T18:33:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":136737,"lastUser":{"userID":136737,"name":"Toufong Vang","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Toufong%20Vang","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/n65S9PRP4PA0M.png","dateLastActive":"2022-09-22T19:20:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":21,"score":null,"hot":3327728100,"url":"https:\/\/community.smartsheet.com\/discussion\/95792\/two-dates-one-independent-and-one-dependent-in-one-cell","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/95792\/two-dates-one-independent-and-one-dependent-in-one-cell","format":"Rich","lastPost":{"discussionID":95792,"commentID":344454,"name":"Re: Two Dates, one independent and one dependent in one cell?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/344454#Comment_344454","dateInserted":"2022-09-22T17:11:06+00:00","insertUserID":136737,"insertUser":{"userID":136737,"name":"Toufong Vang","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Toufong%20Vang","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/n65S9PRP4PA0M.png","dateLastActive":"2022-09-22T19:20:48+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":"2022-09-22T18:33:28+00:00","dateAnswered":"2022-09-22T17:11:06+00:00","acceptedAnswers":[{"commentID":344454,"body":"

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

If I understood you correctly, then the simplest approach would be to subtract 7 days from the meeting date. For example, the date of the prep meeting, Meeting B<\/strong>, would be = [Meeting A]@row - 7<\/code> . The assumption being that Meeting A<\/strong> is exclusively a date column type.<\/p>

Hope this helped.<\/p>

Cheers!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-22 18:33:28","updateUser":{"userID":152722,"name":"kgibson","url":"https:\/\/community.smartsheet.com\/profile\/kgibson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-22T18:33:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":95783,"type":"question","name":"CountIfs Invalid Ref","excerpt":"I cannot for the life of me get this CountIfs function to work. Referencing another sheet. Plz help! =COUNTIFS({FY23 PWP Meets Objective:Meets Objective}, \"No\", {FY23 PWP PHC:PHC}, \"FFH\") Screenshot of my reference sheet columns and values.","categoryID":322,"dateInserted":"2022-09-22T14:29:13+00:00","dateUpdated":null,"dateLastComment":"2022-09-22T16:44:10+00:00","insertUserID":152716,"insertUser":{"userID":152716,"name":"Casey A","url":"https:\/\/community.smartsheet.com\/profile\/Casey%20A","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3MVb_0AahWQ!3LPaf7goDDw!HtxADOtzLHP","dateLastActive":"2022-09-22T17:12:38+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":152716,"lastUser":{"userID":152716,"name":"Casey A","url":"https:\/\/community.smartsheet.com\/profile\/Casey%20A","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3MVb_0AahWQ!3LPaf7goDDw!HtxADOtzLHP","dateLastActive":"2022-09-22T17:12:38+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3327723203,"url":"https:\/\/community.smartsheet.com\/discussion\/95783\/countifs-invalid-ref","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/95783\/countifs-invalid-ref","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":95783,"commentID":344450,"name":"Re: CountIfs Invalid Ref","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/344450#Comment_344450","dateInserted":"2022-09-22T16:44:10+00:00","insertUserID":152716,"insertUser":{"userID":152716,"name":"Casey A","url":"https:\/\/community.smartsheet.com\/profile\/Casey%20A","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3MVb_0AahWQ!3LPaf7goDDw!HtxADOtzLHP","dateLastActive":"2022-09-22T17:12:38+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\/TUEY7CQIT200\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-09-22T16:42:52+00:00","dateAnswered":"2022-09-22T15:46:26+00:00","acceptedAnswers":[{"commentID":344434,"body":"

@Casey A<\/a> I have never seen crosslink references that look like that. I don't think you have the info in the swirly brackets right - usually it would just be {sheetname rangenumber} It looks like you are using syntax similar to what you would use if you were referencing a range in the same sheet. <\/p>

I recommend rebuilding those sheet range references. Start from scratch and let the tool create the syntax for you.<\/p>

dm<\/p>"},{"commentID":344450,"body":"

Thanks @Dale Murphy<\/a>, the reference select tool worked this time (was an issue with my view) and this worked: =COUNTIFS({FY23 HCCN Master Work Plan Range 1}, \"No\", {FY23 HCCN Master Work Plan Range 2}, \"FFH\"). Appreciated.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-22 16:42:52","updateUser":{"userID":152716,"name":"Casey A","url":"https:\/\/community.smartsheet.com\/profile\/Casey%20A","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3MVb_0AahWQ!3LPaf7goDDw!HtxADOtzLHP","dateLastActive":"2022-09-22T17:12:38+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">