计算累积值并减少每天的总金额

肖恩·莱希
肖恩·莱希 ✭✭✭
在公式和函数

你好，我有一个安全违规表，它使用表格条目来记录违规行为，并指定一个以天为单位的测量试用期。如果一个人有多个条目，则试用期天数应累计，同时无事故每过一天，该值减少(1)。

我有(1)个表作为公司的Name数据库，该表上有一个列使用:=SUMIF({安全违规(响应)范围1}，[EMPLOYEE Name]@row，{安全违规(响应)范围2})来显示累积的试用天数。

在我的安全违规表上还有一个列，用于查询每次违规输入的天数:=VLOOKUP([违规严重程度]@row，{安全违规工作表范围2}，2,false)

另一列表示试用期每过一天减少(1):=SUM(TODAY() -)(电子邮件保护)) and =SUM([试用期结束(助手)]@row -[试用期天数(助手)]@row)

不幸的是，我使用公式的方式为每个条目减少(1)天，因此，如果John有(3)次违规，则每过一天删除(3)天，并且对于累积的内容应该只减少(1)天。

似乎我可能需要将我的减少(1)天公式移动到我的名称数据库表，旁边的SUMIFS公式，但我有麻烦，这看起来像什么。

我觉得应该有更简单的方法来做这件事……


任何帮助都会很感激

谢谢!

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

答案

  • Krissia B。
    Krissia B。 主持人

    你好@Shawn莱希

    希望一切安好!谢谢你的描述!请您提供截图(请屏蔽任何敏感数据)，以便我们可以尝试复制您的设置，以进一步测试这一点。谢谢你~


    欢呼,

    Krissia

    · 在Facebook上分享 在Twitter上分享
  • 肖恩·莱希
    肖恩·莱希 ✭✭✭

    嗨Krissia,

    当然做得很好，希望你也一样!谢谢你的回复，希望你能帮上忙。

    这里有一些截图，我裁剪，隐藏和编辑，但希望你能看到我在做什么。我在它们所在的列中覆盖了我正在使用的公式。如果你还需要看别的东西，请告诉我。

    产DATABASE.jpg
    违反安全规定(回应).jpg
    安全违章工作表。jpg


    · 在Facebook上分享 在Twitter上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    要为某人拉出最近的约会，你会想要一些类似……

    =DATEONLY(MAX(COLLECT({源数据时间戳列}，{源数据名称列}，@cell =[违规者名称]@row))))


    从“今天”中减去这个日期，你就会得到那个人从上次事件到现在已经过去了多少天。

    =TODAY() - DATEONLY(MAX(COLLECT({源数据时间戳列}，{源数据名称列}，@cell =[违规者名称]@row))))

    thinkspi.com

    · 在Facebook上分享 在Twitter上分享
  • 肖恩·莱希
    肖恩·莱希 ✭✭✭

    嗨,保罗,

    谢谢你的时间!

    如果我们只关注当前的违规行为，这些建议将会很好地发挥作用。我们的程序是累积到一个特定的数字导致纪律处分的点，它不会重置与最新的违规行为。

    我希望的是记录违规者累积了多少缓刑日，并减去没有发生事故的天数，所以:

    史蒂夫在6月1日犯了一个错误，被判了182天的缓刑，7月13日又犯了一个错误，被判了182天的缓刑。到8月4日他还剩多少天?

    6月1日至7月13日期间有43天，7月13日至8月4日期间有20天

    182 - 43 + 182 - 20 =截至8月4日，还剩301天试用期。

    我遇到的问题是，我没有办法从多次违规的人身上减去(1)缓刑日。似乎我可能需要优先考虑一个特定的人最近的违规行为，并每天减少(1)，但然后以某种方式冻结所有其他人，这样他们就不会倒数直到最近的零。只是不确定那会是什么样子。

    希望这是有意义的。

    感谢你提供的任何帮助。

    谢谢!

    · 在Facebook上分享 在Twitter上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    我的歉意。我以为你已经把其他的都弄清楚了只是不知道上次违规有多少天了。当我有更多的时间来解决这个问题时，我会再来找你的。

    thinkspi.com

    · 在Facebook上分享 在Twitter上分享
  • 肖恩·莱希
    肖恩·莱希 ✭✭✭

    别担心，谢谢你的时间!

    · 在Facebook上分享 在Twitter上分享
  • 詹姆斯•马
    詹姆斯•马 ✭✭✭✭✭

    我认为，在每一次事件中，你都需要数一数距离上次事件发生的日子。为了做到这一点，您需要在所有小于此事件的事件日期中找出最大事件日期。

    这里有一条线在这里用两张公开的表格来做这个。我觉得在这条路的某个地方可以找到解决办法。

    · 在Facebook上分享 在Twitter上分享
  • 肖恩·莱希
    肖恩·莱希 ✭✭✭

    你好,詹姆斯,

    感谢你对这件事的投入，这里的事情变得安静了，看起来这可能太多了。

    我看了你分享的例子，我没有看到我的问题的立竿见影的解决方案，但你至少给了我一些不同的方法来看待它。

    我再做些测试，看看能不能让它起作用。

    谢谢!

    · 在Facebook上分享 在Twitter上分享
  • 保罗H
    保罗H ✭✭✭✭✭✭
    编辑04/23/22

    @Shawn莱希@James马@Paul新来的

    看起来是个不错的挑战，你觉得怎么样?

    image.png

    以前的违反

    =MAX(COLLECT([违规日期]:[违规日期]，[违规日期]:[违规日期]，MAX(COLLECT([违规日期]:[违规日期]，Name:Name，(电子邮件保护)，[违规日期]:[违规日期]，<[违规日期]@row)，名称:名称，(电子邮件保护)）））

    上次违规后的十天

    =IFERROR(NETDAYS([先前违规]@row，[违规日期]@row) - 1,0)

    之前添加

    =MAX(COLLECT([缓刑添加]:[缓刑添加]，[违规日期]:[违规日期]，MAX(COLLECT([违规日期]:[违规日期]，Name:Name，(电子邮件保护)，[违规日期]:[违规日期]，<[违规日期]@row)，名称:名称，(电子邮件保护)）））

    运行总

    =IF(ISBLANK([上次违规]@row)，[缓刑添加]@row, IF([上次添加]@row -[上次违规后的十天]@row < -[上次添加]@row， -[上次添加]@row，[上次添加]@row))

    累积总

    =条件求和(名称:名称、(电子邮件保护)，[总跑数]:[总跑数])


    编辑

    添加了第二个名字来检查，似乎起作用了

    image.png


    · 在Facebook上分享 在Twitter上分享
  • 肖恩·莱希
    肖恩·莱希 ✭✭✭

    嗨，保罗·H，

    很抱歉回复晚了，我没有太多的时间来做这件事，我手上的创可贴还在……

    欢迎来到字符串，感谢您的贡献!

    这似乎是工作，它是比我有很多好，但我仍然有一点麻烦与累计总数。如果“缓刑添加”值保持一致，则模型看起来不错。我注意到，当我输入一个新条目时，在“缓刑添加”字段中输入一个值之前，“累计总数”填充，添加一个不同的值没有区别

    看起来像“运行总数”将“先前添加”减去“自上次违规以来的Netdays”，但如果“先前违规”是空白的，它将在行中填充“缓刑添加”值。这有点奏效，但它不考虑当前的违规行为，这可能会更高，并导致暂停(任何高于547缓刑天的值都会导致暂停，所以我需要查看当前的缓刑添加)。

    而且，时间上的相关性似乎只与之前的违规行为有关，因此，如果一名员工在几个月内有几次违规行为，它就会计算得很好，但如果该员工在几年后再次发生违规行为，它就不能准确地减少过去的天数。看起来，当输入新的违规时，如果“Netdays Since violation”小于“previous Added”中的值，则公式将计算先前“previous Added”的值，但它将减去，但不超过该值(“PA”=182，“NSPV”=172，“RT”=10)(“PA”=182，“NSPV”=760，“RT”=-182)，并且“Running Total”保存过去几年未滚动的所有先前数据。

    目的很简单:从(0)开始“试用日”，并在发布时添加违规行为，每过一天无事件发生，累计总数减少(1)。如果你达到(547)个试用日，你将被终止。不幸的是，事实证明建立一个正确计算的公式有点困难。

    我想詹姆斯是对的，答案就在这里的某个地方。


    感谢您的帮助!

    · 在Facebook上分享 在Twitter上分享

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

在智能表中创建和编辑公式

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!
This situation would be appropriate for a column formula. I would try pasting Ray's formula from above into the first cell of the column and then converting to a column formula.<\/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":106265,"type":"question","name":"If Date is blank + past formula","excerpt":"I can't figure out why this would return a \"yes\" when the date cell is blank. =IF([Date of Site Survey (Product)]@row = \" \", \" \", IF(TODAY() > [Date of Site Survey (Product)]@row, \"yes\", \" \")) Intended logic\/result - If date cell is blank, then \"blank\" (this worked when I tried without the 2nd argument added) If date is in…","categoryID":322,"dateInserted":"2023-06-09T14:00:25+00:00","dateUpdated":null,"dateLastComment":"2023-06-09T14:29:50+00:00","insertUserID":162220,"insertUser":{"userID":162220,"name":"dhawkins","title":"Product Manager","url":"https:\/\/community.smartsheet.com\/profile\/dhawkins","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T17:44:12+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162220,"lastUser":{"userID":162220,"name":"dhawkins","title":"Product Manager","url":"https:\/\/community.smartsheet.com\/profile\/dhawkins","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T17:44:12+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3372641415,"url":"https:\/\/community.smartsheet.com\/discussion\/106265\/if-date-is-blank-past-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106265\/if-date-is-blank-past-formula","format":"Rich","lastPost":{"discussionID":106265,"commentID":379837,"name":"Re: If Date is blank + past formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379837#Comment_379837","dateInserted":"2023-06-09T14:29:50+00:00","insertUserID":162220,"insertUser":{"userID":162220,"name":"dhawkins","title":"Product Manager","url":"https:\/\/community.smartsheet.com\/profile\/dhawkins","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T17:44:12+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-09T14:28:35+00:00","dateAnswered":"2023-06-09T14:12:11+00:00","acceptedAnswers":[{"commentID":379830,"body":"

Try this:<\/p>

=IF([Date of Site Survey (Product)]@row <> \"\", IF(TODAY() > [Date of Site Survey (Product)]@row, \"yes\", \"no\"))<\/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":106249,"type":"question","name":"Modifying a date based on 2 variables","excerpt":"Hello Everyone! I would like to create a formula that says If the value in the \"Variable\" column is \"HIGH\" AND the date in \"Column 1\" has a year less than today's year, return that date with TODAY\"S year. Otherwise return that date unchanged. In other words, dates in the previous years are brought to this year, dates in…","categoryID":322,"dateInserted":"2023-06-08T23:28:09+00:00","dateUpdated":null,"dateLastComment":"2023-06-09T15:45:32+00:00","insertUserID":127672,"insertUser":{"userID":127672,"name":"Carroll Wall","url":"https:\/\/community.smartsheet.com\/profile\/Carroll%20Wall","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T15:44:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-06-09T17:58:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":46,"score":null,"hot":3372597821,"url":"https:\/\/community.smartsheet.com\/discussion\/106249\/modifying-a-date-based-on-2-variables","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106249\/modifying-a-date-based-on-2-variables","format":"Rich","tagIDs":[216,234,249,254],"lastPost":{"discussionID":106249,"commentID":379864,"name":"Re: Modifying a date based on 2 variables","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379864#Comment_379864","dateInserted":"2023-06-09T15:45:32+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-06-09T17:58:43+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\/3EAGX5UGDNCQ\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-09T15:44:08+00:00","dateAnswered":"2023-06-09T15:30:15+00:00","acceptedAnswers":[{"commentID":379858,"body":"

Sure, here is how it breaks down:<\/p>

=IF([Variable]@row <> \"High\", \"\"\n<\/pre>
<> is a way of saying \"not equal to\". So, in this case, we are saying \"Do the following thing if [Variable] is not equal to \"High\". The double quotes, \"\", is another way of saying \"blank\". So if our formula sees that [Variable] contains anything other than \"High\", it is going to set [Goal Column] as blank.<\/p>
IF(YEAR(TODAY()) > YEAR([First Column]@row), DATE(YEAR(TODAY()), MONTH([First Column]@row), DAY([First Column]@row)), [First Column]@row))\n<\/pre>
This section of the formula applies to the \"else\" part of the original IF statement. The first part looked for [Variable] not equal to \"High\", so this part will apply to [Variable] that does equal \"High\". First, we are asking whether today's year, YEAR(TODAY(), is greater than the year of [First Column], YEAR([First Column]). If it is, we are going to build a date using the present year, combined with the month and day from [First Column], DATE(YEAR(TODAY()), MONTH([First Column]@row), DAY([First Column]@row)).<\/p>
[First Column]@row\n<\/pre>
Lastly, if the current year is not greater than [First Column], i.e., they are the same, then we are simply going to set [Goal Column] to the same value as [First Column].<\/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":216,"urlcode":"Non-profit","name":"Non-profit"},{"tagID":234,"urlcode":"Health-care","name":"Healthcare"},{"tagID":249,"urlcode":"Education","name":"Education"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
             

               

                
公式和函数趋势

               

               
    
                
  • If函数-嵌套-负百分比-符号
    我有一个让我抓狂的配方。我试图让符号根据计划列的百分比改变颜色(见截图)。如果%为正，我希望On Track列显示绿色。如果它是负的，但大于-25%，我想显示黄色，所有其他显示红色。问题是我…
    • 
                
  • 如果Date是空白+过去公式
    我不明白为什么这将返回一个“是”当日期单元格是空白。=IF([现场调查(产品)日期]@row = " "ob欧宝娱乐app手机下载， " "， IF(今天()>[现场调查(产品)日期]@row， "是"，" ")预期的逻辑/结果-如果日期单元格为空白，则"空白"(当我尝试不添加第二个参数时有效)如果日期在…
    • 
                
  • 基于2个变量修改日期
    大家好!我想创建一个公式，如果“变量”列中的值为“HIGH”，并且“第1列”中的日期比今天的年份短，则返回该日期为“今天”。否则，返回日期不变。换句话说，前几年的日期被带到今年，在…
    •