绘制随时间变化的成本
评论
-
保罗新来的 ✭✭✭✭✭✭我能算出的最好的方法是使用一些额外的列。2个日期栏和一个比例栏(见下图)。在日期列中，您将输入每月的1日和最后一天。在比例栏中输入:
= IFERROR(如果按比例分配((电子邮件保护)，[开始日期]@row，[结束日期]@row、[计费周期1开始]@row、[计费周期1结束]@row)= 0， "-"，比例((电子邮件保护)，[开始日期]@row，[结束日期]@row、[计费周期1开始]@row、[计费周期1结束]@row))”、“)
它的作用是计算当月的成本。如果成本为$0.00，则用-代替。(我觉得看起来干净了一点)。
如果在相应的行(标题行)中没有所有4个日期，IFERROR部分将使单元格留空。这允许您向下拖动填充列，并避免在单元格中显示“无效数据类型”错误。
然后，您只需更改列标题以反映列名。
如果希望工作表看起来更整洁，可以隐藏结算周期开始日期和结束日期列，并将列名更改为相应的月份。
thinkspi.com
-
老库尔特·米勒 ✭
嗨,保罗,
我将以下内容复制/粘贴到我的成本加载建设计划中，以计算每月的成本…然后我将创建一个每月收入支出的钟形曲线……但是没有成功?我只是得到错误“#不正确的参数集”。我只改变了列标签名称，以匹配我的，但我得到这个错误?你能帮忙吗?
=IFERROR(IF(prate (Budget5， [Start Date]5， [End Date]5，[第一个月开始]5，[第一个月结束]5，[第一个月结束]5 = 0，"-"，prate (Budget5， [Start Date]5， [End Date]5，[第一个月开始]5，[第一个月结束]5，""))))
-
她名叫Stara ✭✭✭✭✭✭嗨,库尔特,
乍一看，你有一些括号错误。
我已经将公式更新为并将行号更改为@row，因此您不需要考虑行号。
试试这样做。
= IFERROR(如果按比例分配((电子邮件保护)，[开始日期]@row，[结束日期]@row，[第1个月开始]@row，[第1个月结束]@row) = 0， "-"，(电子邮件保护)，[开始日期]@row，[结束日期]@row，[第一个月开始]@row，[第一个月结束]@row)， "")
这有用吗?
我希望这对你有帮助!
注意安全，祝你度过美好的一周!
最好的
她名叫Stara
工作流程顾问/ CEO @工作大胆
✅我的帖子是否帮助或回答了您的问题或解决了您的问题?请帮助社区将其标记为可接受的答案/有用的。这将使其他人更容易找到解决方案或帮助回答!
智能表专家顾问兼合伙人
W:www.workbold.com| E:(电子邮件保护)| p: +46 (0) - 72 - 510 99 35
请随时与我联系，了解有关Smartsheet，集成，一般工作流建议或其他方面的帮助。
-
老库尔特·米勒 ✭
这招奏效了!
谢谢你！
库尔特
-
保罗新来的 ✭✭✭✭✭✭
-
她名叫Stara ✭✭✭✭✭✭
智能表专家顾问兼合伙人
W:www.workbold.com| E:(电子邮件保护)| p: +46 (0) - 72 - 510 99 35
请随时与我联系，了解有关Smartsheet，集成，一般工作流建议或其他方面的帮助。
-
她名叫Stara ✭✭✭✭✭✭
@Paul新来的确定的东西!
智能表专家顾问兼合伙人
W:www.workbold.com| E:(电子邮件保护)| p: +46 (0) - 72 - 510 99 35
请随时与我联系，了解有关Smartsheet，集成，一般工作流建议或其他方面的帮助。
帮助文章参考资料欧宝体育app官方888
=COUNTIFS({Range}, @cell =<\/strong> [Job Number]@row)<\/p> Do you have any job numbers anywhere that do not have a leading zero?<\/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":106824,"type":"question","name":"pulling distinct data to another sheet","excerpt":"I have a worksheet which I am working metrics on a separate worksheet. I have a list of courses on the main sheet and they can be duplicates. What I am trying to do is pulling those distinct courses onto another worksheet and I can use count how many times they are used for, but I need to pull that distinct courses from a…","categoryID":322,"dateInserted":"2023-06-23T13:21:13+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T13:54:22+00:00","insertUserID":160445,"insertUser":{"userID":160445,"name":"topazfae","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/topazfae","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxbJ3U64IGvX8bmfaqFsly2Ax_danmhhYKsfmMgQsQ=s96-c","dateLastActive":"2023-06-23T16:06:54+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":"2023-06-23T16:43:05+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":19,"score":null,"hot":3375055535,"url":"https:\/\/community.smartsheet.com\/discussion\/106824\/pulling-distinct-data-to-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106824\/pulling-distinct-data-to-another-sheet","format":"Rich","lastPost":{"discussionID":106824,"commentID":381960,"name":"Re: pulling distinct data to another sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381960#Comment_381960","dateInserted":"2023-06-23T13:54:22+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":"2023-06-23T16:43:05+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-23T14:47:10+00:00","dateAnswered":"2023-06-23T13:54:22+00:00","acceptedAnswers":[{"commentID":381960,"body":" You would need a helper column on the metrics sheet (called \"Number\" in this example). You would manually enter the numbers 1 - whatever to cover the maximum number of possible unique classes. I also suggest a bit of a buffer just in case.<\/p> Then the formula to pull in the distinct list would be<\/p> =IFERROR(INDEX(DISTINCT({Course List}), Number@row), \"\")<\/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":106788,"type":"question","name":"Using COUNTIFS Formula","excerpt":"Is there a way to use the COUNTIFS function (or another function) like the SUMIFS? I have this one working perfectly: =SUMIFS({OM Book Date}, {OM Total Sell}, AND(@cell >= DATE(2023, 6, 1), @cell <= DATE(2023, 6, 30))) Now I need it to count OM Total Sell (some are blank, I need it to count only the ones that have numbers…","categoryID":322,"dateInserted":"2023-06-22T16:52:09+00:00","dateUpdated":"2023-06-22T16:58:39+00:00","dateLastComment":"2023-06-23T13:58:05+00:00","insertUserID":162642,"insertUser":{"userID":162642,"name":"jtomeaCH","title":"ISG Support Manager","url":"https:\/\/community.smartsheet.com\/profile\/jtomeaCH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T13:53:42+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"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":"2023-06-23T16:43:05+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":48,"score":null,"hot":3374985614,"url":"https:\/\/community.smartsheet.com\/discussion\/106788\/using-countifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106788\/using-countifs-formula","format":"Rich","lastPost":{"discussionID":106788,"commentID":381963,"name":"Re: Using COUNTIFS Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381963#Comment_381963","dateInserted":"2023-06-23T13:58:05+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":"2023-06-23T16:43:05+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-23T13:53:39+00:00","dateAnswered":"2023-06-23T12:53:15+00:00","acceptedAnswers":[{"commentID":381926,"body":" It looks like you may need a COUNT\/DISTINCT\/COLLECT combo then.<\/p> =COUNT(DISTINCT(COLLECT({Work Order}, {Book Date}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2023)))<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
<\/p>
<\/p>
<\/p>
