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

loop body

• ne or more statements

post-loop code

• 1. Evaluate condition
• 2. If that condition is

true

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): for item in a_list: if item == "": break print(item) print("done") def func(a_list): for item in a_list: if item == "": return print(item) 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