课程: Excel Essential Training (Microsoft 365)

Sorting data

课程: Excel Essential Training (Microsoft 365)

开始一个月试用

Sorting data

“

- [Instructor] Among Excels many data management tools, most of which are found on the Data tab up on the ribbon, probably the most commonly used is sort. And nearly always in Excel, sort means we're moving rows up and down. We've got a list here in columns A through J. It's about 700 rows or so. And at different times we want to sort based on what we see in one of the columns one or more of the columns to be more accurate. Currently this list is sorted by hire date. Those people hired earliest appear at the top of the list here. Before sorting, always make sure that any other data that might be on this worksheet, like this data to the right, is separated from the list that we want to sort by at least one empty column as it is here. And when you're ready to sort, one other aspect particularly if it's new data or data sent to you that you're not too sure of, make sure there are no empty rows or columns within it. Now, if it's not too large, you could scroll up and down possibly, but there could be some hidden rows that are empty as well too. So a quick check here can be achieved by simply clicking a sale here. Press Control + A and either scroll up and down, or press Control + . a few times. This will simply move the active cell around the corners of this list. When you're at the bottom, if you're not sure, just scroll a little bit more. So Control + . a few times. If you work with this list frequently, you don't need to do that very often but occasionally it's helpful. No empty rows, no empty columns within the list. And when we're ready to sort we could begin on the Home tab, way up to the right, you'll see Sort & Filter. Those are the three choices here. I do recommend Custom Sort. It gives us more choices, the other ones are instantaneous and might surprise us at the results. We can also get here the more standard way by way of the Data tab, Sort right here. The larger of the three choices. When you come to this dialogue box give a quick look for this option to the right. My data has headers. Excel nearly always figures out that your top row is different. If it is, as it is in this case, if this should be checked in and isn't, of course give it a check. We can sort by a single field here, right now it's by hire date, but we can also sort on multiple fields. Imagine for example, that I do want to sort this list by department alphabetically but there's so many people in certain departments here that I want to secondary level here we can add a level. So within each department, for example, I might want them in order by their status, contract, full-time, et cetera. And here too, some of the departments are quite large. A lot of people will have the same status, same department. I could add another level and another perhaps, maybe I'll sort by years of service. That's a new numeric field. We might want that to be largest to smallest but either way, always give that some thought. With text fields, I think most of the time nearly always that A to Z alphabetical order is what we keep. We can even go to a fourth level here and sort by the employee name. So when you're sorting on multiple fields using the larger sort button makes sense, click OK. And sorting is amazingly fast, practically instantaneous. So here we have this list. It's sorted by department. Within each department we've got our contract people and our full-time people, for example. And here's a cluster of people and they're in order how? Descending by the years in column F here. And when they're the same, like these three here, it's alphabetical by name. So you quickly adjust to the idea that we sometimes sort on multiple fields but those other buttons are really handy too. So suppose for example, you want to sort the entire list based on the data in column A. That does not mean rearrange column A only. That would be a disaster. So don't click the column itself just click one of the entries in column A. And if you click the AZ button here, no questions asked. Amazingly fast again. The entire list has been reordered. All the rows got moved up or down so that it's alphabetical based on the data in column A. Now suppose at a later time, we sort by department, so I'll click in column C, click the AZ button. Now it's in order by department. But what did Excel remember? It remembered the previous order. So within each department here, you can see what's happening in account management, it's in order by employee name because that was the previous sort. And we come to the next department, even the shorter list here, that's alphabetical within that department. So we can see how fast these can be. And when you're only sorting on one or two fields, these buttons here come in handy. So sorting is an amazing tool. Nearly always, it means we move rows up and down. It's also extremely fast and easy to get to one of Excels best tools, Sorting.

内容