工作进度跟踪表excel_在Excel中跟踪垃圾

工作进度跟踪表excel

I’ve worked with “garbage” data many times in Excel. You know what I mean – data that is so messy it takes you hours (or days) to clean it up. This week though, I did a different type of garbage tracking. Maybe I went a little overboard, but you’ve probably done that too, right? Anyway, here’s how I spent way too much time on an Excel project, but had fun doing it. Don’t judge!

我已经在Excel中多次处理“垃圾”数据。 您知道我的意思–数据非常混乱，需要花费数小时(或数天)才能清理它。 但是，本周，我进行了另一种类型的垃圾跟踪。 也许我有点过头了，但您可能也这样做了，对吧？ 无论如何，这就是我在Excel项目上花费太多时间，但是这样做很有趣的方式。 不要判断！

花式的新车 (Fancy New Carts)

In January, our city switched from weekly garbage collection, to bi-weekly. On the alternate weeks, they pick up our recycling. And every week they pick up the food waste, that we store in these large green carts.  You need a personal assistant, just to keep track of the collection schedule!

一月，我们的城市从每周一次的垃圾收集改为每两周一次。 在接下来的几周中，他们回收了我们的废纸。 他们每周都会捡拾食物垃圾，我们将它们存储在这些大型绿色推车中。 您需要一个私人助理，只是为了跟踪收集时间表！

In the photo below, you can see the exciting day when our new carts were delivered. (When you work from home, any unusual activity is exciting.)

在下面的照片中，您可以看到新车交付的激动人心的一天。 (当您在家工作时，任何不寻常的活动都会令人兴奋。)

打印日历 (Printing the Calendar)

There is a PDF file on the city’s website, that has a calendar of the pickup dates. It’s not much use when you print it out though, because the black carts (Garbage) and blue carts (Recycling) print in the same shade of grey.

该城市的网站上有一个PDF文件，其中包含接送日期的日历。 但是，当您打印出来时，它用处不大，因为黑色手推车(垃圾)和蓝色手推车(回收)以相同的灰色阴影打印。

The green carts are Food waste, but they’re picked up every week, so we don’t have to keep track of that. The brown leaves are the Yard waste – it’s easy to identify them, even without the colour!

绿色的手推车是浪费食物，但每周都会被捡起来，因此我们不必跟踪。 棕色的叶子是堆场废物–即使没有颜色，也很容易识别它们！

Excel到救援 (Excel to the Rescue)

So, since Excel is the answer to every problem, I decided to create a schedule in Excel.

因此，由于Excel是每个问题的答案，因此我决定在Excel中创建一个计划。

First, based on the PDF file, I listed all the weeks in which our garbage is picked up on a different day. Usually the pickup is on Tuesday, but occasionally a holiday will delay the pickup for a day.

首先，基于PDF文件，我列出了在不同日期收集垃圾的所有星期。 通常情况下，取件是在星期二，但偶尔的假期会延迟一天的取件。

The list is formatted as an Excel table, and named Holidays. The first column is named HolList.

该列表的格式设置为Excel表 ，并命名为Holidays。 第一列名为HolList。

添加变量 (Add the Variables)

Next, I created named cells for the regular pickup day, and the type of waste that will be picked up on odd and even weeks.

接下来，我为常规的提货日创建了命名单元 ，并创建了奇数甚至偶数周的垃圾类型。

I started the schedule with this week’s date, so it’s week 1 on my list, and it’s Garbage collection week. Next week is recycling, and it’s week 2 on my list.

我从本周的日期开始了计划，因此清单上是第1周，这是垃圾收集周。 下周是回收利用，这是我名单上的第二周。

In the screen shot below, you can see the yellow cells where the variables are entered. I used the Create Name from Selection feature (on the Formulas tab), to name those cells.

在下面的屏幕快照中，您可以看到输入变量的黄色单元格。 我使用了“从选择中创建名称”功能(在“公式”选项卡上)来命名这些单元格。

创建时间表 (Create a Schedule)

On another sheet, I entered the dates for the first two Sundays in 2016. In the column to the left, I numbered the first two rows, as 1 and 2.

在另一张纸上，我输入了2016年的前两个星期日的日期。在左侧的列中，我将前两行编号为1和2。

Then, I selected those four cells, and dragged down, using the Fill Handle, to create a list of numbers and dates for the rest of the year.

然后，我选择了这四个单元格，并使用“填充手柄”将其向下拖动，以创建该年剩余时间的数字和日期列表。

制作格式化表格 (Make a Formatted Table)

Then, I entered a few more headings, and formatted the schedule as an Excel Table, named Sched. I created a custom Table Style too, with dark grey dashed lines separating the rows. Why? Because once you start on an Excel project, it’s hard to quit!

然后，我再输入几个标题，并将时间表格式化为一个名为Sched的Excel表。 我也创建了一个自定义的表格样式，用深灰色虚线分隔各行。 为什么？ 因为一旦开始Excel项目，就很难退出！

计算收集日 (Calculate the Collection Day)

Finally, after all that setting up, it was time to use some formulas. In cell D4, I entered an IF formula that checks the Holiday List, to see if the “Week Of” date is listed there.

最后，在完成所有设置之后，该使用一些公式了。 在单元格D4中，我输入了一个IF公式来检查“假日列表”，以查看是否在其中列出了“周”日期。

=IF(COUNTIF(HolList,[@[Week Of]]),         INDEX(Holidays[PU Day],MATCH(C4,HolList,0)),         RegDay)

= IF(COUNTIF(HolList，[@ [Week Of]]))，INDEX(假日[PU Day]，MATCH(C4，HolList，0))，RegDay)

The IF formula checks for the date in the Holiday List, using COUNTIF

IF公式使用COUNTIF检查假日列表中的日期

• If the test result is True (the date was found), the INDEX/MATCH functions return the pickup day for that date.

  如果测试结果为True(找到日期)，则INDEX / MATCH函数返回该日期的取件日。

• If the test result is False (the date was NOT found), the regular pickup date is returned.
  如果测试结果为False(找不到日期)，则返回常规取件日期。

Since this is a formatted table, the formula fills down automatically.

由于这是格式化表格，因此公式会自动填写。

捡到了什么？ (What’s Being Picked Up?)

In column E, there’s another formula, and it calculates which type of pickup there is each week – Garbage or Recycling. This is a simple formula, that checks if the ID number is odd or even.

在E列中，还有另一个公式，它可以计算出每周有哪种类型的皮卡车–垃圾或回收。 这是一个简单的公式，用于检查ID号是奇数还是偶数。

=IF(ISODD($B4),OddWk,EvenWk)

= IF(ISODD($ B4)，OddWk，EvenWk)

• If the ID number is odd, the formula returns the value in the OddWk named range.
  如果ID号为奇数，则该公式将返回OddWk命名范围内的值。
• If the ID number is even, it returns the EvenWk value.
  如果ID号为偶数，则返回EvenWk值。

手动输入 (Manual Entries)

In column F, I manually entered a “Y” for the weeks when Yard waste is picked up.

在F列中，我在收集废料的几周内手动输入了“ Y”。

In column G, I manually entered any special types of collection, such as Batteries, and Christmas Trees.

在G列中，我手动输入了任何特殊类型的集合，例如电池和圣诞树。

Doing that was much easier than trying to cook up a formula!

这样做比尝试制定公式要容易得多！

隐藏过去的几周 (Hide the Past Weeks)

My goal was to create a printable schedule, so I wanted an easy way to hide all the past weeks.

我的目标是创建一个可打印的日程表，因此我想找到一种隐藏过去几周的简单方法。

In cell H4, I entered a formula that compares the date in the next row to today’s date. I also check for an empty cell, so the last row won’t show TRUE.

在单元格H4中，我输入了一个公式，将下一行中的日期与今天的日期进行比较。 我还要检查一个空单元格，因此最后一行不会显示TRUE。

=AND(C5<>"",C5<TODAY())

= AND(C5 <>“”，C5 <TODAY())

Then I can filter that column, to hide all the FALSE rows.

然后，我可以过滤该列，以隐藏所有FALSE行。

But why stop there? I added a Slicer to the table too, so I can just click a button to hide the FALSE rows. Yes, it’s just a garbage collection schedule, but it deserves nice things too!

但是为什么要停在那里？ 我也向表中添加了切片器，因此我只需单击一个按钮即可隐藏FALSE行。 是的，这只是垃圾收集时间表，但是它也值得拥有！

设置打印区域 (Set the Print Area)

There’s no need to print column H – it’s just used for filtering the list. So, I selected columns A:G, and set that as the Print Area.

无需打印H列-它仅用于过滤列表。 因此，我选择了A：G列，并将其设置为“打印区域”。

最后的接触 (The Final Touch)

The waste collection schedule looked good, but since I’d already gone way overboard in setting it up, why not add one final touch?

废物收集时间表看起来不错，但是由于我已经设置了废物，所以为什么不增加最后的修饰呢？

To make the dates easier to read, I added conditional formatting to separate the months. I selected all the data in the schedule (not the headings), and clicked Conditional Formatting, New Rule

为了使日期更易于阅读，我添加了条件格式来分隔月份。 我选择了计划中的所有数据(而不是标题)，然后单击“条件格式，新规则”

For the Rule Type, I selected “Use a Formula to Determine Which Cells to Format”

对于规则类型，我选择了“使用公式来确定要格式化的单元格”

In the formula box, I entered this formula, to compare the month in each row with the month in the previous row:

在公式框中，我输入了此公式，以将每一行中的月份与上一行中的月份进行比较：

=Month($C4)<>Month($C3)

=月($ C4)<>月($ C3)

Then I clicked the Format button, and on the Border tab, added a solid black top border.

然后，我单击“格式”按钮，然后在“边框”选项卡上，添加了黑色实心顶部边框。

完工时间表 (The Finished Schedule)

And here is the finished schedule, with past weeks hidden, and lines separating the months.

这是完成的时间表，其中隐藏了过去的几周，行分隔了几个月。

It prints nicely, and it’s easy to read, so I’ve put a copy on the back door. Now we’ll always know that to put out on waste collection day.

它打印效果很好，而且很容易阅读，所以我将副本放在后门上。 现在我们永远知道要在垃圾收集日将其淘汰。

下载样本文件 (Download the Sample File)

If you’d like to see a copy of this masterpiece, and adjust it for your own waste collection schedule, go to the Excel Sample Files page on my website. In the Functions section, look for FN0048 – Waste Collection Schedule.

如果您想查看该杰作的副本，并根据您自己的废物收集时间表进行调整，请转到我网站上的“ Excel示例文件”页面 。 在功能部分中，查找FN0048 –废物收集时间表 。

The zipped file is in xlsx format, and does not contain macros. Maybe the next version will have some!

压缩文件为xlsx格式，不包含宏。 也许下一个版本会有一些！

翻译自: https://contexturesblog.com/archives/2016/06/23/keeping-track-of-garbage-in-excel/

工作进度跟踪表excel