课程: Excel: Tracking Data Easily and Efficiently

Using dropdown lists for accuracy and consistency - Microsoft Excel教程

课程: Excel: Tracking Data Easily and Efficiently

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

Using dropdown lists for accuracy and consistency

“

- [Instructor] Let's talk about dropdown lists to control the inputs in your data. Now let's look at this dataset. In column D, in the In Stock, Yes or No column, we want yes or no. We don't want not sure or 11, need some more, you know, maybe somebody think that they're funny. And then they add in a birthday cake. No, we can't have this, 'cause what if we want to sort to have the yes's together and the no's together? We want to do a summary or a count of the number that we have versus the numbers that we don't have. No, we've got to control this. Let's get rid of all of this. Here we go. I am going to highlight this column because I want the dropdown list to be in the entire column. Go to Data, Data Tools, Data Validation, and I want list in sale, yes. And the options. I want a Y comma, N, that's it. Error alert. I can set this up to where it will warn a person, but I want 'em to stop. And if they insert something unacceptable, please input Y or N only, okay. And what do we want 'em to input? Okay, the Y or N and, okay. Okay, so we do have espresso powder. I don't know if we have espresso gel. I don't know. And so, oh, input a Y or N only cancel. Alright, so I will leave that alone for now until I know, okay, I'm going to put it in here because we don't have any, I can even put a lowercase N and it flips it to an uppercase N, lowercase Y. Great. So that is one simple way of controlling our inputs. And now if we did want to sort, okay? The ends are up top, why I can fill this in. Okay? And then sort. And then if I wanted to get a count, I could easily count Y's and N's. Now let's look at a dynamic dropdown list. Go over here, in the dataset we have sessions, cities, and registrations. We see here that the June session in Sao Paulo had 25 registrations, but here is an issue. Did you see it? We have Fort Lauderdale with Fort spelled out. Here we have FT period. Okay, that can make analysis really difficult. So here's what I'm going to do with the cursor in the data set. I'm going to go to the insert tab and then pivot table. I want to put it on an existing worksheet and I want to put the pivot table say right here, okay. And I'm going to get to pivot tables a little more in depth later in the course but it doesn't hurt to see it right now. Alright, I want to put city in rows and registration in the values. Oh boy, look at this. Two Albuquerque's. Let's close that. Fort Lauderdale, Ft. Lauderdale, New York, NYC. So our analysis is not accurate because we should have one Albuquerque for 78 not these two. Now over here in this range we have the city names as we would like to have them. And nothing is right. We could have New York or NYC or New York City. They just have to be consistent. So I'm going to put these into a table. All right, cursors in that data set. I'm at home, format as table. Let's go with this purple again the table does not have headers. Okay, so I'm going to call this city list. And get rid of the filter button. I'm going to highlight that column and then go over here. And instead of table three I'm going to call this cities and enter. Now watch, I'm going to highlight this column. Data, data tools, data validation, list equals cities. I've got that equal sign and I'm telling it to pull the options from that city range that I named and okay. Oh, look at that. Look at that. We now have the dropdown list. And look at this. We've got these triangles these green triangles telling us that we've got some errors because NYC is not acceptable. And go here though, and click New York. Go here, click Fort Lauderdale go down for the correct spelling of Albuquerque. And what do we do now? We are in the data tab already so we can refresh and watch the pivot table. Really? 'Cause here we go. One, two, three, look at that. Now we have one Albuquerque. That's 78. We've got one New York. Alright, so now let's wrap up by scrolling down. I want to show you one other thing by making the options dynamic. I'm going to drag this in and look at that. The table brought down our dropdown list. So November, we've got a Fort Lauderdale that had 25. Our San Paolo had 39. November, Singapore, 44. Oh, Mexico City is not an option, okay? But let's move past that for right now. New York, December 31. Now go over here. Mexico City, enter. Look at that. Now watch this. There is Mexico City as an option in our dropdown list. Beautiful. Now this is an option here. Just go ahead and pick it. The green error went away. Scroll back up, go over to the pivot table and then refresh. Look at that. Everything updated. Everything is consistent. All right, there you have it. Here you saw a basic dropdown list where you enter exactly the options that people should have is easy when you have why or in or yes or no. But then if you have something like cities where you might add some more it's better to make it dynamic by using tables and naming the range so that it is variable and you control your inputs and your analysis is solid.

内容