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

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

你好，我有一个安全违规表，使用表格进入记录违规，并分配一个衡量的试用期在天。如果一个人有多个条目，试用期天数应该累计，同时每平安无事地度过一天，试用期天数就减少(1)。

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

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

以及另一列，每过一天试用期就减少(1):=SUM(TODAY() -(电子邮件保护)and =SUM([试用期结束(佣工)]@row -[试用期(佣工)]@row)

不幸的是，我的公式为每个条目减少了(1)天，所以如果约翰有(3)个违规，每过去一天就删除(3)天，无论累积了多少，它应该只减少(1)天。

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

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


任何帮助将不胜感激

谢谢!

标签:

答案

  • Krissia B
    Krissia B 主持人

    你好@Shawn莱希

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


    欢呼,

    Krissia

  • 肖恩·莱希
    肖恩·莱希 ✭✭✭✭✭✭

    嗨Krissia,

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

    这里有一些截图，我裁剪、隐藏和编辑，但希望能让你看到我在做什么。我把我正在使用的公式叠加在它们所在的列上。如果你还需要看什么，告诉我。

    产DATABASE.jpg
    安全违反(反应). jpg
    安全违反WORKSHEET.jpg


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

    要给某人找最近的约会对象，你需要这样的东西……

    =DATEONLY(MAX(COLLECT({Source Data Timestamp Column}， {Source Data Name Column}， @cell =[违例者名称]@row)))


    从“今天”减去这个日期，你就能知道从这个人上次事件发生到现在已经过去了多少天。

    =TODAY() - DATEONLY(MAX({Source Data Timestamp Column}， {Source Data Name Column}， @cell =[违例者名称]@row)))

    thinkspi.com

  • 肖恩·莱希
    肖恩·莱希 ✭✭✭✭✭✭

    嗨,保罗,

    感谢您的时间!

    如果我们只着眼于当前的违例，这些建议便能够有效地发挥作用。我们的程序是累计到一个特定的数字导致纪律处分，它不重置与最新的违规。

    我所希望的是跟踪一个违规者已经累积了多少试用期，并减去没有发生事故的天数，所以:

    6月1日，史蒂夫因违规而损失了182个试用期，7月13日的另一件事使他损失了182个试用期。截止到8月4日他还剩多少天?

    从6月1日到7月13日已经过去(43)天，从7月13日到8月4日已经过去(20)天

    182 - 43 + 182 - 20 = 301天试用期截止到8月4日。

    我的问题是，我没有一个方法，只减去(1)试用期天数的人有多次违规。似乎我需要为一个特定的人优先考虑最近的违规，每天减少(1)，但然后以某种方式冻结所有其他的，这样他们不会倒数直到最近的零…只是不知道那会是什么样子。

    希望这是合理的。

    感激你能提供的任何帮助。

    谢谢!

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

    我的歉意。我以为你已经把其他部分都算出来了除了上次违规发生的时间。等我有更多的时间来处理这个问题的时候，我会给你回电话的。

    thinkspi.com

  • 肖恩·莱希
    肖恩·莱希 ✭✭✭✭✭✭

    不用担心，感谢您的时间!

  • 詹姆斯•马
    詹姆斯•马 ✭✭✭✭

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

    有一根线在这里用两个出版的表格就可以做到这一点。我觉得在这条路上总会找到解决办法的。

  • 肖恩·莱希
    肖恩·莱希 ✭✭✭✭✭✭

    你好,詹姆斯,

    感谢你对这个问题的意见，这里的事情变得安静，似乎这可能有太多的构想。

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

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

    谢谢!

  • 保罗H
    保罗H ✭✭✭✭✭
    编辑04/23/22

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

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

    image.png

    以前的违反

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

    自上次违规以来的网天数

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

    之前添加

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

    运行总

    =IF(ISBLANK([先前违规]@行)，[缓刑添加]@行，IF([先前添加]@行-[自先前违规以来的Netdays]@行< -[先前添加]@行，[先前添加]@行-[先前违规以来的Netdays]@行))

    累积总

    =条件求和(名称:名称、(电子邮件保护)，【运行总量】:【运行总量】)


    编辑

    添加了第二个名字来检查，似乎正在工作

    image.png


  • 肖恩·莱希
    肖恩·莱希 ✭✭✭✭✭✭

    嗨保罗H,

    很抱歉回复晚了，我没有太多的时间来处理这个，我的创可贴还在那里……

    欢迎加入我们的行列，感谢您的贡献!

    这似乎是工作，它比我有很多，但我仍然有一点麻烦累计总数。如果“试用附加价值”保持一致，你的模型看起来很好。我注意到，当我输入一个NEW条目时，“cumulative Total”在“Probation Added”字段中输入一个值之前被填充，并且添加一个不同的值没有什么不同

    看起来“累计”是“前一次添加”减去“自前一次违规以来的网天数”的总和，但如果“前一次违规”是空白的，它会在该行填充“试用期添加”值。这类工作，但它不是在看当前的违规，这可能是更高的，导致暂停(任何值高于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)试用期，你将被终止。不幸的是，要建立一个正确计算的公式有点困难。

    我觉得詹姆斯是对的，答案就在这里。


    感谢您的帮助!

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

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

交叉表引用的公式组合

内容功能列表

类别

You will need to substitute each space with %20 so when you are adding the Primary Column value use,<\/p>
=SUBSTITUTE([Primary Column]@row, \" \", \"%20\")\n<\/pre>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-03 11:16:07","updateUser":{"userID":152031,"name":"austinov","url":"https:\/\/community.smartsheet.com\/profile\/austinov","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-03T11:16:11+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":95022,"type":"question","name":"Determine if form submissions are missing","excerpt":"Hi there. We have 16 geographically dispersed service locations who perform time of service collections and bank deposits. Our finance team needs to capture and reconcile these collections & deposits on a daily basis. I have created a form to collect time of service collection information from each location, and I'm trying…","categoryID":322,"dateInserted":"2022-09-02T17:59:05+00:00","dateUpdated":null,"dateLastComment":"2022-09-02T20:41:55+00:00","insertUserID":99446,"insertUser":{"userID":99446,"name":"JS_NCHC","url":"https:\/\/community.smartsheet.com\/profile\/JS_NCHC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T22:15:46+00:00","banned":0,"punished":0,"private":false,"label":""},"updateUserID":null,"lastUserID":99446,"lastUser":{"userID":99446,"name":"JS_NCHC","url":"https:\/\/community.smartsheet.com\/profile\/JS_NCHC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T22:15:46+00:00","banned":0,"punished":0,"private":false,"label":""},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":21,"score":null,"hot":3324293460,"url":"https:\/\/community.smartsheet.com\/discussion\/95022\/determine-if-form-submissions-are-missing","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/95022\/determine-if-form-submissions-are-missing","format":"Rich","lastPost":{"discussionID":95022,"commentID":342319,"name":"Re: Determine if form submissions are missing","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/342319#Comment_342319","dateInserted":"2022-09-02T20:41:55+00:00","insertUserID":99446,"insertUser":{"userID":99446,"name":"JS_NCHC","url":"https:\/\/community.smartsheet.com\/profile\/JS_NCHC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T22:15:46+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-02T20:46:38+00:00","dateAnswered":"2022-09-02T20:41:55+00:00","acceptedAnswers":[{"commentID":342319,"body":"
Hi again.  I figured out the core formula I needed, so I thought I'd update my post in case others have similar questions in the future.<\/p>
Form for submission is part of \"Daily Collections\" sheet, and SITE NAME is the site submitting the form.  I created an Audit sheet with a list of SITE NAME's, and look to the Daily Collections sheet to determine if at least one submission matches with SITE NAME, resulting in \"Y\" or \"N\".<\/p>
=IF(COUNTIF({Daily Collections SITE NAME}, =SITE NAME@row) > 0, \"Y\", \"N\")<\/p>
Thanks! 😁<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-02 20:46:38","updateUser":{"userID":99446,"name":"JS_NCHC","url":"https:\/\/community.smartsheet.com\/profile\/JS_NCHC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T22:15:46+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":95016,"type":"question","name":"Countifs with multiple sheet references and CONTAINS","excerpt":"Hi! I am struggling with how to build the correct formula to provide the result of the following scenario: I am using the Smartsheet \"Fruit Sheet\" to create a formula in the Smartsheet \"Orchard\" Search \"Fruit Sheet\" for any \"Apples\" that may be \"Red\" in the \"Summer\" The sheet has several fruits and several apples. The…","categoryID":322,"dateInserted":"2022-09-02T14:58:09+00:00","dateUpdated":"2022-09-02T15:08:46+00:00","dateLastComment":"2022-09-02T15:55:50+00:00","insertUserID":152024,"insertUser":{"userID":152024,"name":"MoyaC","url":"https:\/\/community.smartsheet.com\/profile\/MoyaC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T15:56:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":152024,"lastUser":{"userID":152024,"name":"MoyaC","url":"https:\/\/community.smartsheet.com\/profile\/MoyaC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T15:56:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3324266039,"url":"https:\/\/community.smartsheet.com\/discussion\/95016\/countifs-with-multiple-sheet-references-and-contains","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/95016\/countifs-with-multiple-sheet-references-and-contains","format":"Rich","lastPost":{"discussionID":95016,"commentID":342299,"name":"Re: Countifs with multiple sheet references and CONTAINS","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/342299#Comment_342299","dateInserted":"2022-09-02T15:55:50+00:00","insertUserID":152024,"insertUser":{"userID":152024,"name":"MoyaC","url":"https:\/\/community.smartsheet.com\/profile\/MoyaC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T15:56:00+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-02T15:55:57+00:00","dateAnswered":"2022-09-02T15:46:55+00:00","acceptedAnswers":[{"commentID":342297,"body":"
@MoyaC<\/a> <\/p>
The below should work for you:<\/p>
=COUNTIFS({FRUIT_SHEET_FruitType}, \"Apple\", {FRUIT_SHEET_Color}, has(@cell,\"Red\")<\/strong>, {FRUIT_SHEET_Season}, \"Summer\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-02 15:55:57","updateUser":{"userID":152024,"name":"MoyaC","url":"https:\/\/community.smartsheet.com\/profile\/MoyaC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T15:56:00+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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">