分配给列的%分配公式
你能帮我想出一个IF公式，在我的表格的% Allocation栏中使用吗?
这就是我想要表达的:
如果Bob(来自“Assigned To”列)达到大于100%的分配(来自多行之和)，则将Bob的名字所在的“Assigned To”单元格填充为红色。下面的例子显示了101%的超额分配。如果你推荐一个不同的旗帜，然后用红色突出所有鲍勃的名字，请让我知道。还没有10000英尺/资源管理的预算。
另外，如果一个单元格里有很多人，你能提出另一个公式吗?那么%分配是如何工作的呢?
谢谢你的帮助!
Zain
最佳答案
-
Leibel博士年代 ✭✭✭✭✭✭您可以在技术上为每个可能的添加分配硬代码。下面的示例将检查每个单元格最多2个触点。
=IF(COUNTM([Assigned To]@row) = 1, IF(SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]， HAS(@cell， [Assigned To]@row)) > 1,1， "")， IF(COUNTM([Assigned To]@row)) = 2, IF(SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]， HAS(@cell, LEFT([Assigned To]@row, FIND("， "， [Assigned To]@row) - 1)) > 1, SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]， HAS(@cell, MID([Assigned To]@row, FIND("， "， [Assigned To]@row)):[Assigned To]， HAS(@cell, MID([Assigned To]@row, FIND("， "， [Assigned To]@row)):[Assigned To]， HAS(@cell, MID([Assigned To]@row) + 2，LEN([被]@row) -找到(“,”,[被]@row)))) > 1), 1 , ""), ""))
然而，更有意义的是，在另一个工作表中列出每个可能分配给的人员(每行单个人员)，并在其中一列中查找该特定员工的分配百分比，然后在此工作表中查找该人员，以查看他们是否被标记为超额分配，并相应地进行更改。
答案
-
保罗新来的 ✭✭✭✭✭✭第一个比特看起来像这样(把它放在一个单独的列中):
=IF(SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]， @cell = [Assigned To]@row) > 1,1)
然后，可以使用基于此新列的条件格式突出显示Assigned to列，其中新列中任何行都包含“1”。
至于第二点，我们只需要对上面的内容做一些小的调整，但是这些调整将取决于……
到底什么类型的列被分配到?允许多人联系吗?同时选中下拉?文本/号码吗?
thinkspi.com
-
保罗新来的 ✭✭✭✭✭✭
如果其中一个，另一个，或者两个都超过100%，它就会变成红色，但这就是公式的样子
=IF(OR(SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]， HAS(@cell， [Assigned To]@row) > 1, SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]， HAS([Assigned To]@row， @cell) > 1)， 1)
thinkspi.com
-
Zain ✭
谢谢您的回复!我插入了你刚刚给我的公式，我收到一个列错误:#不正确的参数集。
“Assigned To”列是“Contact List”列类型，在属性下选中“允许每个单元格有多个联系人”框。
“% Allocation”列是“文本/数字”列类型。
你能帮我一下吗?
另外，如果5个或更多的人被分配到一个单元格中，并且5人中有2或3人的分配超过100%，那么是否有一个公式或方法可以轻松识别哪些人被分配过度?所以，如果某个人有100个任务(5%，10%，6%等等)，而其他4个人有相同数量(但不同)的任务和百分比，除了通过5个人的名字过滤，看看谁的行变成了红色，有没有更快的方法来识别?
-
保罗新来的 ✭✭✭✭✭✭
很抱歉。我漏了一个右括号。
=IF(OR(SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]， HAS(@cell， [Assigned To]@row) > 1, SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]， HAS([Assigned To]@row， @cell)) > 1)， 1)
不过，我得回头再跟你说第二件事。它可能涉及到一个单独的表格。
thinkspi.com
-
Zain ✭
由于某种原因，我仍然得到#INCORRECT ARGUMENT SET错误。当我输入公式时，% Allocation和Assigned To列确实会突出显示，但在formula列中仍然会收到相同的错误。
谢谢你在这件事上的帮助。
-
保罗新来的 ✭✭✭✭✭✭
再次抱歉。出于某种原因，我真的很纠结于这个问题的括号:
=IF(OR(SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]， HAS(@cell， [Assigned To]@row)) > 1, SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]， HAS([Assigned To]@row， @cell)) > 1)， 1)
thinkspi.com
-
Zain ✭
谢谢你，错误消失了，但现在每当我在“分配给”单元格中分配任何两个利益相关者时，整个行都会变成红色，这是没有我调整%分配的情况。我检查了一下，看看两个人之间的分配百分比是否以某种方式加在一起，但即使加在一起，也不到100%。
-
Zain ✭
-
Zain ✭
-
吉纳维芙P。 员工管理目前还没有一种方法可以解析多选择单元格并在列中单独搜索每个单独的值。第二个SUMIFS语句将查看名称的特定组合是否出现不止一次(例如，“Bob”和“Riley”同时出现在一个单元格中)。
我建议创建两个任务，每个任务分配一个人，而不是一个任务分配两个人。
另一种选择是搜索单元格中每个可能的选择，然后检查每个选项的% Allocation。
如果这一行都选择了“Bob”和如果Bob被选中的SUMIFS大于100%，则返回1。
例:
=IF(AND(HAS([Assigned To]@row， "Bob"， SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]， HAS(@cell， "Bob")) > 1)， 1)
然后你可以将多个IF语句加在一起，每个IF语句对应一个联系人:
=IF(AND(HAS([Assigned To]@row， "Bob"， SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]， HAS(@cell， "Bob")) > 1)， 1)+如果((((分配)@row,“莱利”,SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]，(@cell。”莱利”))> 1)， 1)
一旦将所有可能要搜索的人加在一起，就可以设置条件格式规则来查看该公式列是否大于1。
让我知道，如果这是有意义的，将为您工作!
欢呼,
吉纳维芙
-
Leibel博士年代 ✭✭✭✭✭✭
您可以在技术上为每个可能的添加分配硬代码。下面的示例将检查每个单元格最多2个触点。
=IF(COUNTM([Assigned To]@row) = 1, IF(SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]， HAS(@cell， [Assigned To]@row)) > 1,1， "")， IF(COUNTM([Assigned To]@row)) = 2, IF(SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]， HAS(@cell, LEFT([Assigned To]@row, FIND("， "， [Assigned To]@row) - 1)) > 1, SUMIFS([% Allocation]:[% Allocation]， [Assigned To]:[Assigned To]， HAS(@cell, MID([Assigned To]@row, FIND("， "， [Assigned To]@row)):[Assigned To]， HAS(@cell, MID([Assigned To]@row, FIND("， "， [Assigned To]@row)):[Assigned To]， HAS(@cell, MID([Assigned To]@row) + 2，LEN([被]@row) -找到(“,”,[被]@row)))) > 1), 1 , ""), ""))
然而，更有意义的是，在另一个工作表中列出每个可能分配给的人员(每行单个人员)，并在其中一列中查找该特定员工的分配百分比，然后在此工作表中查找该人员，以查看他们是否被标记为超额分配，并相应地进行更改。
-
Zain ✭
@Genevieve P。和@Leibel Shuchat谢谢你的帮助!同样的任务或活动有另一张表是有意义的，这样我就可以适当地分配给每个人。
帮助文章参考资料欧宝体育app官方888
类别
Hi @Louis.Smith<\/a> <\/p> Maybe try this one:<\/p> =COUNTIF(Client:Client, \"Delta\")<\/p> I hope that helps.<\/p> Matt<\/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":106091,"type":"question","name":"How to reference column name pulled from formula","excerpt":"I'm trying to pull a date from columns based on the number in the helper column. I know I can do this with nested IFs, but is there a way to change which column I'm pulling the value from? My column Start Date should be whatever the column is based on the Index. So for the first column below, since Index is 3, it should…","categoryID":322,"dateInserted":"2023-06-06T19:01:04+00:00","dateUpdated":null,"dateLastComment":"2023-06-06T19:24:57+00:00","insertUserID":162114,"insertUser":{"userID":162114,"name":"mwat4482","url":"https:\/\/community.smartsheet.com\/profile\/mwat4482","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-06T19:24:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162114,"lastUser":{"userID":162114,"name":"mwat4482","url":"https:\/\/community.smartsheet.com\/profile\/mwat4482","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-06T19:24:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3372158761,"url":"https:\/\/community.smartsheet.com\/discussion\/106091\/how-to-reference-column-name-pulled-from-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106091\/how-to-reference-column-name-pulled-from-formula","format":"Rich","lastPost":{"discussionID":106091,"commentID":379133,"name":"Re: How to reference column name pulled from formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379133#Comment_379133","dateInserted":"2023-06-06T19:24:57+00:00","insertUserID":162114,"insertUser":{"userID":162114,"name":"mwat4482","url":"https:\/\/community.smartsheet.com\/profile\/mwat4482","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-06T19:24: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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/EITIHXA4Y0I7\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-06T19:24:45+00:00","dateAnswered":"2023-06-06T19:18:07+00:00","acceptedAnswers":[{"commentID":379128,"body":" Are you trying to use that as a dynamic cell reference? If so, that is not possible. What it looks to me like you need is something more along the lines of<\/p> =INDEX([1st Date Column]@row:[Last Date Column]@row, 1, Index@row)<\/p> You are going to need to update the column names in the formula to match what you are using in your sheet.<\/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":106087,"type":"question","name":"Conditional Formatting Based on 2 Date Columns, with or without Helper Column","excerpt":"Hi! I've scoured the Community posts, and can't find exactly what I need. I am managing a membership list and need to flag when memberships expire before or on a specific date (06\/29\/2023) so I can contact those members. I have one column with their membership expiration dates, a second column with the 06\/29\/2023 deadline,…","categoryID":322,"dateInserted":"2023-06-06T18:29:42+00:00","dateUpdated":null,"dateLastComment":"2023-06-06T19:02:02+00:00","insertUserID":162111,"insertUser":{"userID":162111,"name":"Mlichtenstein","title":"Director","url":"https:\/\/community.smartsheet.com\/profile\/Mlichtenstein","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BtDbwegK_WQ!wlnbvxwzuT4!fyPHolHHR0X","dateLastActive":"2023-06-06T18:55:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162111,"lastUser":{"userID":162111,"name":"Mlichtenstein","title":"Director","url":"https:\/\/community.smartsheet.com\/profile\/Mlichtenstein","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BtDbwegK_WQ!wlnbvxwzuT4!fyPHolHHR0X","dateLastActive":"2023-06-06T18:55:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":53,"score":null,"hot":3372157304,"url":"https:\/\/community.smartsheet.com\/discussion\/106087\/conditional-formatting-based-on-2-date-columns-with-or-without-helper-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106087\/conditional-formatting-based-on-2-date-columns-with-or-without-helper-column","format":"Rich","tagIDs":[437],"lastPost":{"discussionID":106087,"commentID":379120,"name":"Re: Conditional Formatting Based on 2 Date Columns, with or without Helper Column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379120#Comment_379120","dateInserted":"2023-06-06T19:02:02+00:00","insertUserID":162111,"insertUser":{"userID":162111,"name":"Mlichtenstein","title":"Director","url":"https:\/\/community.smartsheet.com\/profile\/Mlichtenstein","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BtDbwegK_WQ!wlnbvxwzuT4!fyPHolHHR0X","dateLastActive":"2023-06-06T18:55:36+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\/JWT4M72YQK8N\/screenshot-2023-06-06-142808.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-06-06 142808.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-06T19:00:34+00:00","dateAnswered":"2023-06-06T18:52:14+00:00","acceptedAnswers":[{"commentID":379113,"body":"
<\/p>
![\"Conditional](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/DD8Q3FFV2RUK\/conditional-formating.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
