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

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

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

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

交叉表引用的公式组合

内容功能列表

类别
I could solve the issue. It was a simple spelling mistake or difference between the two sheets.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"reasonUpdated":"","dateUpdated":"2022-08-06 20:28:39","updateUser":{"userID":151036,"name":"Jörg Schmidt1","url":"https:\/\/community.smartsheet.com\/profile\/J%C3%B6rg%20Schmidt1","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hJjVoY907bY!!P7w61eHMiE7","dateLastActive":"2022-08-06T20:26:27+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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":93987,"type":"question","name":"how can i copy a value from one cell to another in the same sheet","excerpt":"I have the above project plan setup The FOCUS AREA is a dropdown value. When i add children to a parent, i want the children to inherit the parents FOCUS AREA value. i created a helper column to identify children and that childs parent value for focus area. How can i automate the copy over AND still allow the user to pick…","categoryID":322,"dateInserted":"2022-08-05T17:50:56+00:00","dateUpdated":null,"dateLastComment":"2022-08-05T20:20:49+00:00","insertUserID":120623,"insertUser":{"userID":120623,"name":"Umesh Shah","url":"https:\/\/community.smartsheet.com\/profile\/Umesh%20Shah","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!B_DAgW7inuQ!GutBH5L31vo!a4sw_iKY2Cf","dateLastActive":"2022-08-05T20:19:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":120623,"lastUser":{"userID":120623,"name":"Umesh Shah","url":"https:\/\/community.smartsheet.com\/profile\/Umesh%20Shah","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!B_DAgW7inuQ!GutBH5L31vo!a4sw_iKY2Cf","dateLastActive":"2022-08-05T20:19:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":14,"score":null,"hot":3319453905,"url":"https:\/\/community.smartsheet.com\/discussion\/93987\/how-can-i-copy-a-value-from-one-cell-to-another-in-the-same-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/93987\/how-can-i-copy-a-value-from-one-cell-to-another-in-the-same-sheet","format":"Rich","lastPost":{"discussionID":93987,"commentID":338859,"name":"Re: how can i copy a value from one cell to another in the same sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/338859#Comment_338859","dateInserted":"2022-08-05T20:20:49+00:00","insertUserID":120623,"insertUser":{"userID":120623,"name":"Umesh Shah","url":"https:\/\/community.smartsheet.com\/profile\/Umesh%20Shah","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!B_DAgW7inuQ!GutBH5L31vo!a4sw_iKY2Cf","dateLastActive":"2022-08-05T20:19:13+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\/353Y6W0RK5SL\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-08-05T20:21:04+00:00","dateAnswered":"2022-08-05T19:49:10+00:00","acceptedAnswers":[{"commentID":338851,"body":"

You would use<\/p>

=PARENT([Focus Area]@row)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"reasonUpdated":"","dateUpdated":"2022-08-05 20:21:04","updateUser":{"userID":120623,"name":"Umesh Shah","url":"https:\/\/community.smartsheet.com\/profile\/Umesh%20Shah","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!B_DAgW7inuQ!GutBH5L31vo!a4sw_iKY2Cf","dateLastActive":"2022-08-05T20:19:13+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":93984,"type":"question","name":"Multiselect Dropdown Parsing","excerpt":"Hello all, I have 3 columns in a sheet. 1 is a multi-select dropdown (\"MSD\"), 1 is a single contact (\"SC\"), and 1 is a multi-select contact (\"MSC\"). In the SC, I want to take the first dropdown value from the MSD and use it to index match a value from another sheet. In the MSC, then I want to take the remaining values from…","categoryID":322,"dateInserted":"2022-08-05T17:28:01+00:00","dateUpdated":null,"dateLastComment":"2022-08-05T19:48:02+00:00","insertUserID":151077,"insertUser":{"userID":151077,"name":"Allen M Vance","url":"https:\/\/community.smartsheet.com\/profile\/Allen%20M%20Vance","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wvjQf0zrtpU!HVPSXzyfi_0!-X5Gyhg_Kh5","dateLastActive":"2022-08-05T22:14:05+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":"2022-08-05T20:10:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":10,"score":null,"hot":3319449963,"url":"https:\/\/community.smartsheet.com\/discussion\/93984\/multiselect-dropdown-parsing","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/93984\/multiselect-dropdown-parsing","format":"Rich","lastPost":{"discussionID":93984,"commentID":338850,"name":"Re: Multiselect Dropdown Parsing","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/338850#Comment_338850","dateInserted":"2022-08-05T19:48:02+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":"2022-08-05T20:10:09+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-08-05T22:14:17+00:00","dateAnswered":"2022-08-05T19:48:02+00:00","acceptedAnswers":[{"commentID":338850,"body":"

Technically yes. You can pull these values in based on the multi-select dropdown, but...<\/p>


<\/p>

We cannot currently use formulas to populate multiple USABLE contacts in a single cell even if the column is set to allow multiple. It will simply generate a text string and won't be usable in automations for notifications, update requests, etc..<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"reasonUpdated":"","dateUpdated":"2022-08-05 22:14:17","updateUser":{"userID":151077,"name":"Allen M Vance","url":"https:\/\/community.smartsheet.com\/profile\/Allen%20M%20Vance","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wvjQf0zrtpU!HVPSXzyfi_0!-X5Gyhg_Kh5","dateLastActive":"2022-08-05T22:14:05+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":[]}">