课程: Excel: Tracking Data Easily and Efficiently

Thinking about input, storage, and output - Microsoft Excel教程

课程: Excel: Tracking Data Easily and Efficiently

开始一个月试用 购买团队方案

Thinking about input, storage, and output

“

- [Instructor] Let's talk about input, storage, and output. This also gets into something that you'll hear me say a few times throughout the course, keep your data in as few places as possible. And I'm mentioning this because I've seen this over and over again over the years. Someone will develop a workbook and I can see their thought process. In this case, it makes sense to have this team, the Thunder right here, they're in the East League, we've got the Mystery separated and we're collecting data about the different participants. Let's go to the west. Okay, now we've got two other teams. Right now we have four places for inputs. Let's go back to the east. Ah, we found out that Mia has provided proof of insurance. Now we can put an X here. Oh, Bonita is a four year participant. Where is Bonita? Oh, here's Bonita right here. Okay, four year. And Paolo has paid the full fee of $150. Good, but see, that's a lot of moving around. But what if we wanted a solid list that said, how many people have not had a physical yet, or provided proof of having had a physical? How many people still owe money on their fees? How many first year participants do we have? Now, that gets messy because of the way that this data is set up with the multiple places to put inputs. And these are also your storage places, and your outputs are also just that. You could get some sums, but by team or by league. Let's go to a better example. Look at this. Everything is all stacked up. Now, no, it's not so easy to look at just one team right now, but that's not a problem, okay? First of all, let's put this data into a table. Cursor's inside the dataset. I'm going to go Home, Format as Table, and let's grab this blue. Table has headers, OK. Get rid of the filter buttons. All right, so if we wanted to see who has not had a physical yet, go into this column, data, and then I'm going to sort. And here are the three people who have not had a physical yet, and they're on two separate teams. Two on the Jesters and one on the Thunder. All right, let's scroll back up. We could sort ascending to see how many one year, how many three year, how many do we not have information about yet? But we do know Bonita is a four year. Enter and then sort it again. By having the data in one place, we can also do this. My cursor is in the data set, Insert, Slicer. Which slicers do I want? Let's look at league, insurance, physical. Just those. Okay. All right, let's get these positioned so that they look nice. Okay, and then I'm going to do one thing. Check this out. With that physical slicer highlighted, I'm going to Control and then select insurance and league. Drag them up together. And then I am going to align them, align them center. All right, beautiful. Now I can do this. I want to look at just east, look at just west. Now I want to clear this. And this should not be moving on me. Don't move or size with cells. OK. If I want to look at the people who don't have proof of insurance, click there, check that out. Now we know exactly who to contact and we don't have to go flipping back and forth between different worksheets. And this is a type of output. Now, another type of output could be a pivot table, and you'll see those in the course. Cursor is in a data set, Insert, Pivot Table. I want it on another sheet. Yes. OK. Let's say I want team right here, and then fee paid. Well, I noticed a misspelling. Let me fix that. Fee paid. All right, delete that. Now, go back and then right click and refresh. Okay, I have to go and put fee paid. Great. So now we can go back to our inputs, and then let's say Oscar has finally paid the full $150 and Norris has shown up with $75. It's just one place to put it. Okay, now we can go back, refresh, everything updates so much easier. When you think about your inputs, have as few inputs as possible. Keep your storage simple, and then you can get as many outputs as you want.

内容