Excel: Sorting and Filtering

Download Exercise File Here: ExcelTable

Sorting and Filtering are two basic functions you will perform quite often in Excel. When dealing with large data sets, it can be difficult to see what you are looking for, especially if the records appear in random order. Using sort and filter, you can adjust the spreadsheet to show what you want to see.

Sort

Sorting is simple in Excel. Just click on the header cell for the column you want to sort (in my example I chose “Name”). From the Ribbon Bar select Data. Now click on the AZ and ZA arrows. The column you selected will sort.

excelsort

Now, try it out on the other columns as well.

Finally, select the box directly above the word sort in the picture above. This will give you an advanced sort menu.

From this menu,  can select the column you wish to sort, the values you want to sort on, and the sort order.

excelsort1.jpg

Filter

To filter, select the Filter icon from the Data tab in the Ribbon Bar

excelsort2

Notice arrows now appear in the column header cells

excelsort3

Click on the arrow for Quarter. Now, uncheck the Select All box and check Quarter 2 > Okay

excelsort4.jpg

Now your sheet only has rows containing Quarter 2. Take note of the row numbers though. Notice the row numbers skip around. This is because the other rows are still there, they are just hidden by the filter.

excelsort5.jpg

Advertisements

Python: lambda, map(), reduce(), filter()

Lambda()

One of the guiding principles of Python is simplicity. In fact, you will often see well coded, simplistic Python programs called Pythonic as tribute to the programmer’s talent at crafting clean code.

When I think of Pythonic code, the first thing that comes to my mind is lambda

The best way I can describe lambda is as a short cut function. Look at the function in ln[12]. This function accepts 1 argument “y”.  It then multiplies it by 2 and returns the result.

Now look at ln[13]:

syntax:

x               = lambda            y        :    y*2

ASSIGNED VARIABLE = lambda ARGUMENT: ACTION

 

pythonLambda

map()

Let’s build on lambda and introduce a new function: map()

Start with ln[15] :

  • lst = [1,2,3,4] – we assign lst a list of numbers
  • n = 0 – setting a counter variable
  • for i in lst: – start a for loop, iterating through the numbers in lst
  • lst[n] = i * 2 – replace each value in lst with the original value * 2
  • n += 1 – iterates n by 1
  • lst – outputs new values of lst

pythonLambda1.jpg

Now let’s examine ln[16]:

  • lst = [1,2,3,4] – we assign lst a list of numbers
  • x = map(lambda y:y*2, lst) – one at a time, pass each value in lst to the lambda function – multiply these values by 2 and assign the results to x
  • x – outputs results

reduce()

Reduce takes all the elements in a list an combines them.

In the example below, x is fed into the lambda function a * b as such:

1*2=2*3=6*4=24

pythonLambda2

filter()

Filter allows you to run a set of values through a function and filter out the false results.

Below we filter out all values from x that are less than 0

pythonLambda3.jpg

 


If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT

Follow this link for more Python content: Python