Transcript Slide 1

LAYER
Application SW: Excel &
2
High-order P.L.: Visual Basic
1
Low-order P.L.: Assembly
3
System Software: O.S.
3
Machine Language
4
Data Representation
5
HW: Circuit Design
6
Access
Intro to Excel
Order
CSCI130-03A
Instructor: Dr. Imad Rahal
Introduction

Spreadsheets are applications used mostly to store, organize,
calculate, and display numeric data

Tabular format

Divided into rows and columns


Rows are numbered
Columns are labeled with letters

Cells hold data (numeric or text), formulas or functions

Power:

Automatically reapplies formulas as data changes


Average of student grades in a grade book
Automatically adapts formulas when copied to new locations
Introduction

Each Excel file or project is called a workbook

File  New  Workbook

Each workbook contains 3 worksheets by default

Each cell in a worksheet may contain




Text
Numeric
(built-in) Functions
Formulas
SHOW “GRADES.XLS” FILE
- Rows are numbered
- Columns are assigned letters
- L5 is highlighted and formula is shown above the sheet – address is
shown in upper left corner
- Data is text (left justified), numeric (right justified), formulas and/or
functions
SHOW “GRADES.XLS” FILE
Formulas

Formula to calculate average
Changes are automatically reflected
Drag to other cells
Copy/Paste
Begin with an = sign



-
Either type = in cell
Click on the = above worksheet
May contain



Constants
Single cell references: D5
Functions



AVERAGE, SUM, etc …
Cell ranges: D5:E7
Connections: +, -, *, /, ^, AND, Or, NOT
SHOW “GRADES.XLS” FILE
-
Formula to calculate average
Changes are automatically reflected
Drag to other cells
Copy/Paste
Formulas

Once we’ve written the formula for one cell we use it
for other rows or columns






No need to rewrite it
Place cursor on the small square in the lower right corner of
cell
Hold on the small black that appears by clicking on the right
mouse
Drag to desired cells
Notice how formula changes
If target cell is not close to cell holding formula


Highlight formula cell then Edit  Copy
Go to target cell then Edit  Paste
Formulas

Importance of using cell references



Formulas can be updated automatically when copied to
different cells
Formulas can be recalculated when referenced data changes
Relative addressing


A7 contains formula A5+A6
Copy to B7  ?


B7 is one column to the right of A7
Copy to C9  ?

C9 is two columns to the right of A7 and 2 rows below it
SHOW “DAY CARE CENTER.XLS” FILE
- What is the formula for the total charge?
- How can we apply for the rest of the parents?
- Any problems?
Formulas

Formula is changing as expected but that is NOT what
we want

Solution 1: write values as constants in formula



Changing the values implies changing the formulas
A VERY BAD SOLUTION
Solution 2: Absolute addressing




Place a $ sign before row number  row does not change
Place a $ sign before column letter  column does not change
Both  cell is fixed
New formula
Functions

As you enter the = sign

The name box changes to a listing of available functions

SUM


Adds all values in some range
Guesses the range




SUM(C1:C4) (along a column)
SUM(C1:F1) (along a row)
SUM(C1:F10) (along a block)
Other functions applicable to ranges: AVERAGE, MAX, MIN,
COUNT
Conditional Functions

IF and VLOOKUP function


IF (condition, yes action, no action)


SHOW DAY CARE CENTER.XLS
Enters one of two possible results in a cell based on condition



IF-ELSE and Select Case of VB
Employees.xls, IF(G5>50000,10%,12%)
Employees.xls, IF(F5=“F”,”Yes”,”No”)
Condition part be any equality or an inequality


Compound statements by using AND and OR
If worker hired before 01/01/1999 and is full time then they would
be eligible for a raise

=IF(AND(F4="F",H4<DATEVALUE("1/1/1999")),"Yes","No")
VLOOKUP

Better if we have more than two options to select from

Corresponding letter grade of numeric grade

Must have an accompanying lookup table

VLOOKUP(x,y,z)





x = cell containing value to lookup
y = table range (top left and bottom right corners)
z = column in table containing the result to write --- always 2
Table must be organized in ascending order from smallest to largest
One table entry must match the values being looked up


Direct match (equal one of the values)
Bounds match (table gives lower bounds of ranges)

Once the we get the value of the last cell before we cross the boundaries of range of
the given value
SHOW “GRADES.XLS” FILE
- TRY VLOOKUP WITH TABLE NAME
Review

Power of Spreadsheets derives from



Automatically reapplies formulas as data changes
Automatically adapts formulas when copied to new cells
Given that F11 = G23+E22*$F$2

What would L34 be if the same formula is copied there?


M46 + K45*$F$2
A20?

B32+#REF!*$F$2
Multiple Sheets

A workbook can have many worksheets

3 initially: Sheet1, Sheet2, and Sheet3

Click on the tab for a sheet brings it to the foreground

Add new sheets


Delete sheets by clicking on their tab


Right on the sheet tab  Delete
Rename sheets


Right on the sheet tab  Insert
Right on the sheet tab  Rename (ONE WORD)
Rearrange sheets

Click on tab, hold down the button, and drag the tab to desired location
Multiple Sheets


When a cell referenced in a formula by row and
column (e.g. C5)  in current worksheet
Reference cells in different sheets, add the sheet
name followed by !



Make sure sheet name is a valid variable (1 string with no
spaces)
Sheet2!C5
Place table on different sheet in grades.xls
Cell Naming

A frequently used cell can be named so as not
to have to refer to it by its address all the time



Type in name in Name Box
Hit Enter
A range of cells can also be named similarly by
selecting it and proceeding as above

If you select a row or column with a title, the title is
suggested by default
Sorting


Much easier than VB
On a single column



Click on a cell in that column
Click on Ascending or Descending sort buttons under SORT AND FILTER
in the EDITING group
Information in ALL contiguous columns will be moved accordingly

To sort only specific columns, highlight desired area and press desired
button


DataSort
On more than one column

Sort by table by total score DESC and then by student names
ASC


Sort on names ASC
Sort on score DESC
Graphs

Click on the INSERT ribbon and use one of the chart
types in the chart collection


Select the type of chart



You can preview your data on every chart
Go to Layout Ribbon and add title, gridlines, and data labels
Decide where to place chart



Highlight the data we wish to have on the chart
On the Design Ribbon
Click on the location button
Notice how updating data changes the graph
accordingly
Forecasting and Simulation


Not only for data entry, nice formatting and calculating
formulas
Many functions for simulation and forecasting (whatif analysis)



One of which is the Pmt function
Returns the payment amount for a loan based on the interest rate and a
constant payment schedule
Some of the functions are:

PMT(RATE, NPER, PV)





RATE is the interest rate for the loan
NPER is the number of payment periods for the loan
PV is the present value or principal of the loan
PMT ALWAYS NEGATIVE UNLESS PV is negative
Show multisheetHousepmts.xls
Macros



Excel built-in functions are great but limited
Macros are a means for the user to define new
functions
A macro is a series of Excel actions


Bound together
Executed on a single command



Similar to “command button” subroutines
Good for non-professionals
There are two ways to create them


Recorded macros
Writing macros using VBA (VB for Applications)
Recorded Macros


While working with recorded macros and using Visual Basic
within Excel, it is helpful to have the Developer ribbon available
This ribbon might not be currently visible on the main menu



To add the Developer ribbon to the menu, click the Office button and
then click Excel Options.
In the Popular category, under “Top options for working with Excel”,
check the “Show Developer tab in the Ribbon” check box and click OK.
To record a macro



Click on developer ribbon
Within the Code group, choose Record Macro
You will get a pop-up window asking for a name, description, and control
key for the macro

Give your macro a meaningful name, a short description, and assign it to a key for
future execution
Recorded Macros

Start by positioning the cursor where you want it to be before the macro
begins.




Once you begin recording, everything you do will be recorded, so you don’t want to
position your cursor after you start recording, unless you want the macro to begin in
the same cell every time it is executed
After you click on ok, the window will disappear and everything you do
now will be recorded as part of the macro until you click on Stop
Recording in the Code group
Now enter the keystrokes you want as part of your macro, pressing the
stop recording button when finished
When you save an Excel sheet containing a macro, make sure
you save as it .xlsm – i.e. macro enabled workbook
Recorded Macros

There is an icon in the Code group that allows you to set the
addressing mode to relative addressing.

If you don’t change this, the default mode for recorded macros is
absolute referencing



Macros designed with absolute addresses always use the same cells on a worksheet, in
other words, if you wanted a macro that would print your name in cell A10 and your email address in B10, you would use absolute addressing
You could write the same macro with relative addressing to print your
name in whatever cell you position the cursor in, with your e-mail
address right below it
Relative addressing is much more flexible, and what you will want most
of the time, so make sure this button is pressed before you start
recording your macro when you wish to have your macro use relative
addressing
Recorded Macros

Past Due Accounting Example

Clear Sheet and save


Fill penalty column (if overdue=yes)



Record, Select cells, clear and save
10% if amount >$120
5% otherwise
Check recorded macros


DeveloperMacrosClick on desiredEdit
Complicated to read


What is FormulaR1C1?
Only good for small things


Adding prerecorded text
Executing simple formulas
Writing Macros using VBA

Much closer to what we did in VB


Begin as if you’re recording a macro and stop
recording



Simpler statements
After giving it a name and control character
Go and edit the macro
Past Due Accounting.xls

The ActiveCell and ActiveCell.offset(x,y)
VBA Examples

Macros for a single row


User places cursor in right spot. Use Activecell.Offset(x,y)
Macros that loop downwards



Cells(x,y).Select
Row=0
Do While ActiveCell.Offset(Row,0)<>””




...
Row=Row+1
Loop
Add the numbers in consecutive cells (in col 1) assuming the first cell is at A1






Dim Number As Single, Sum As Single
Dim Row As Integer
Cells(1,1).Select
Sum = 0
Row = 0
Do While ActiveCell.Offset(Row,0)<>””




Number = ActiveCell.Offset(Row,0)
Sum = Sum + Number
Row = Row + 1
Loop
Other Features

Assigning Macros to Buttons


Rather than using Ctrl + Character all the time
More handy to have a button




Insert  Shapes
Draw button
Right Click on the button  Assign macro
Any of the VB commands can be used

InputBox


ActiveCell = InputBox(“Enter you name”,”Namebox”)
Msgbox
Other Features

Reading data from data files


Open file, assign the data to the active cell and them move
cell down to across the sheet
File in same folder as project directory

Open ActiveWorkbook.Path & "\data.txt" For Input As #1
File Input in Excel

Design macros for the following, each activated by a separate
button


(1) Ask the user for her name via an inputbox and store in active cell
(2) Read data from a text file






Salesperson, Sales
Write salespersons in column A and their sales in column B starting row 4
(3) Find the salesperson with the highest sales and display his/her name
(4) Find sales of an input salesperson
(3) Find salespersons with sales >= some input
The number of salespersons is not known but

The row after the last salesperson is empty
File Input in Excel













Sub ReadSalesPersons()
Dim Sales As Single, Row As Integer
Dim Salesperson As String
Open ActiveWorkbook.Path & "\sales.txt" For Input As
#1
Range(“A4”).Select
Row=0
Do While Not EOF(1)
Input#1, Salesperson, Sales
ActiveCell.Offset(Row,0) = Salesperson
ActiveCell.Offset(Row,1) = Sales
Row = Row + 1
Loop
End Sub
File Input in Excel
















Sub FindMax ()
Dim MaxSales As Single, MaxiName As String
Dim Row As Integer
Range(“A4”).Select
‘place on first name
Row = 0
MaxiName = ActiveCell.Offset(0,0)
MaxiSales =ActiveCell.Offset(0,1)
Do While ActiveCell.Offset(Row,0)<>””
If(ActiveCell.Offset(Row,1) > Maxi) Then
Maxi = ActiveCell.Offset(Row, 1)
MaxiName = ActiveCell.Offset(Row,0)
End If
Row = Row + 1
Loop
Msgbox “The salesperson with the highest sales is:” &
MaxiName , , FormatCUrrent(MaxiSales)
End Sub
Excel Problems

Summaries:

(1) Do something for the Selected-Row using VBA


ActiveCell.Offset(x,y)
(2) Loop throughout a sheet using VBA



Range(“XY”).Select
Row=0
Do While ActiveCell.Offset(Row,0)<>””




…
Row = Row + 1
Loop
(3) Reading from a file to a sheet

Do While Not EOF(1)
File Input in Excel

Design macros for the following, each activated by a separate
button


(1) Ask the user for her name via an inputbox and store in active cell
(2) Read data from a text file






Salesperson, Sales
Write salespersons in column A and their sales in column B starting row 4
(3) Find the salesperson with the highest sales and display his/her name
(4) Find sales of an input salesperson
(3) Find salespersons with sales >= some input
The number of salespersons is not known but

The row after the last salesperson is empty
File Input in Excel













Sub ReadSalesPersons()
Dim Sales As Single, Row As Integer
Dim Salesperson As String
Open ActiveWorkbook.Path & "\sales.txt" For Input As
#1
Range(“A4”).Select
Row=0
Do While Not EOF(1)
Input#1, Salesperson, Sales
ActiveCell.Offset(Row,0) = Salesperson
ActiveCell.Offset(Row,1) = Sales
Row = Row + 1
Loop
End Sub
File Input in Excel
















Sub FindMax ()
Dim MaxSales As Single, MaxiName As String
Dim Row As Integer
Range(“A4”).Select
‘place on first name
Row = 0
MaxiName = ActiveCell.Offset(0,0)
MaxiSales =ActiveCell.Offset(0,1)
Do While ActiveCell.Offset(Row,0)<>””
If(ActiveCell.Offset(Row,1) > Maxi) Then
Maxi = ActiveCell.Offset(Row, 1)
MaxiName = ActiveCell.Offset(Row,0)
End If
Row = Row + 1
Loop
Msgbox “The salesperson with the highest sales is:” &
MaxiName , , FormatCUrrent(MaxiSales)
End Sub
Excel Problems

Summaries:

(1) Do something for the Selected-Row using VBA


ActiveCell.Offset(x,y)
(2) Loop throughout a sheet using VBA



Range(“XY”).Select
Row=0
Do While ActiveCell.Offset(Row,0)<>””




…
Row = Row + 1
Loop
(3) Reading from a file to a sheet

Do While Not EOF(1)