TOPIC 7 MORE EXCEL Max Fowler (Computer Science) - PowerPoint PPT Presentation

CS 105: TOPIC 6 – LOOPS TOPIC 7 – MORE EXCEL Max Fowler (Computer Science) https://pages.github-dev.cs.illinois.edu/cs-105/web/ JULY 5, 2020

Week 4 Video Series Topics  for loops (definite loops) and range  while loops (indefinite loops)  break and continue in loops  Excel – SUM, COUNTIF/IFS,  Excel - INDEX/MATCH, *LOOKUP

For Loops

What's a loop?  Loops are the third basic coding structure 1. Sequences 2. Conditions/Branching 3. Looping/Iterations

General loop setup loop condition 1. Evaluate condition loop body 2. If that condition is one or more statements true post-loop code a) Execute body b) Loop back to step 1 3. Do stuff after the loop

For loops – definite loops  For loops do some action for each element of a collection for student in ['Holden', 'Melissa', 'Quinn'] print ('{0} is an awesome student!'.format(student))

for student in ['Holden', 'Melissa', 'Quinn'] print ('{0} is an awesome student!'.format(student)) Prints… Iteration 1: Holden is an awesome student! Iteration 2: Melissa is an awesome student! Iteration 3: Quinn is an awesome student!

Expanding for loop use - enumerate  For when you want indices as well as elements of a collection cities_by_pop = ['New York', 'Los Angeles', 'Chicago'] message = '{0} is number {1} by population' for index, name in enumerate (cities_by_pop): print(message.format(name, index + 1))

Range – the "other" kind of for loops  Most languages let a for loop run over a sequence of numbers – from i = 0 to 10, for example  Range let's use generate a finite sequence of numbers range(10) # [0, 1, 2, 3, 4, 5, 6, 7, 8, 9] range(3, 8) # [3, 4, 5, 6, 7] range(-3, 11, 3) # [-3, 0, 3, 6, 9]

Using range for fixed repetitions for i in range(5): # note that i not used print('CS 105 is pretty swell')

Video Question  How many lines will be printed? things = [22, [33, 44], 55, [66]] for thing in things: print(str(thing))

While Loops

While Loops  To a degree, easier  "Indefinite" loops – indefinite as in we don't know how many times the loop will run  Uses sentinel values – stopping symbols

Everyone's first while loop new_list = [] while True: val = input("Enter a value or 'q' to quit\n") if val == 'q': break new_list.append(value) print(str(new_list))

While loop to iterate on a value num = 15 while num >= 0 print(num) num -= 15

Video question  How many lines are printed? num = 14 while num >= 1: print(num) num = num // 2

Break and Continue

Comparing break and return def func(a_list): def func(a_list): for item in a_list: for item in a_list: if item == "": if item == "": break return print(item) print(item) print("done") print("done") How do these two differ if we pass ["a","b","","c"]?

Break vs continue  break ends a loop  continue skips an iteration, but continues the loop! a_list = [2,4,6,9,11,16] for num in a_list: if num % 2 == 0: continue print(num)

Video question  How many items are printed? mixed_list = [ 'hi', 3, math.pi, 'there', ['CS', 105]] for item in mixed_list: if type(item) != str: continue print(item)

Excel – SUM, COUNTIF/IFS

Excel - Functions  We've seen some functions already, either in the reading or the videos  Functions generate values for assignments  Example functions  SUM, COUNTIF, COUNTIFS

The SUM function  SUM(range)  Sum the values in a range – pretty self explanatory

COUNTIF(range, criteria)  COUNT values with a criteria  Criteria can be  A single value – 7 or "Illinois"  A comparison – ">7" or ">" & B2  A string with wildcards – "CS *"

COUNTIFS  COUNTIFS(range1, criteria1, range2, criteria2…)  Ranges much have equal row/column count  Counts each time the values of the ranges meet the criteria

No video question on this one  Don't want to bloat compass' gradebook

Excel – INDEX/MATCH, *LOOKUP

INDEX – Get value at a position  1-dimensional lookup:  =INDEX( cellrange , index )  cellrange is 1-dimensional range  index is 1-based, not 0-based like in Python  =INDEX(B3:B11, 4) ---> B6  2-dimensional lookup:  =INDEX(cellrange, index1, index2)  cellrange is 2-dimensional range  index1 specifies the row, index2 the column  =INDEX(B3:D11, 4, 2) ---> C6

MATCH – find a position in a list  =MATCH( value , cell-range , match-type )  value is the value we're looking for  cell-range is a row or a column (not 2D)  match-type is:  0 = Exact match  1 = Approximate (largest less than or equal, values must be sorted in increasing order)  -1 = Approximate (smallest value greater than or equal, values must be sorted in descending order)  returns 1-based index into the cell range for a cell containing the value  Return #N/A if no match

INDEX and MATCH – a duo  =INDEX(B1:B20, MATCH("Max", A1:A20, 0))  Search A1:A20 for a cell holding 'Max'  Read the corresponding element of the B column

VLOOKUP – INDEX + MATCH together  Handles a common case of INDEX + MATCH  Less flexible, but only 1 function  =VLOOKUP (value, table, col_index, [range_lookup])  value - value to look for in the first column of table.  table - table from which to retrieve a value.  col_index - The column in the table from which to retrieve a value.  range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match.  Value searched for must be to the left of value to return  HLOOKUP does for rows, what VLOOKUP does for cols

Video question  Which cell does =INDEX(C10:F20, 3, 2) read from? A) D12 B) E12 C) E13 D) F12