Transcript Slide 1

INFORMATION SYSTEMS AND
DATA MANAGEMENT 27000
EXCEL
Christian Zieger
Excel
2

Files for our Excel exercises are available in:
\\ubz01fst\Courses\Course_Coletti\ISDM5
Material\Excel_Labs
 copy laboratory.xlsx

File Option  Advanced
 Editing,
calculation and other options
 check separators: if they are wrong, Excel interprets
numbers as text and aligns them to the left.
ISDM 27000 – Christian Zieger
21/07/2015
Excel Basics
3


Excel document: workbook  includes several
worksheets
Work-sheets: collection of cells


Sheet tabs
Cells: basic container of information



References: columns and rows
Name box
formula bar: it shows the real content of the cell
ISDM 27000 – Christian Zieger
21/07/2015
Excel Basics: types
4


Types: Home  Numbers 
Value (aligned to the right)
Number:
23 or 56,45 or -0,4 or ,23 or (24)
Currency: €34,2 or 9€ or 0,7 €
Date: 3 Mar or 7/10/05 or Jun 2005 or 3-5
Time: 2:34 am or 17:28
Percentage: 34,5%


Formula (begins with =)
Text (use ‘ to convince Excel): it is aligned to the left
and includes everything else (sequence of alphanumeric
characters and symbols)
ISDM 27000 – Christian Zieger
21/07/2015
Importing Tables
5

Consider the following files available in the network
folder:
 text_fixed.txt;
 text1.txt;
 text2.txt
Import “text_fixed.txt” using “fixed width”:
 Open
 select the file
or
 Data  Get External Data  from text
ISDM 27000 – Christian Zieger
21/07/2015
Importing Tables (2)
6


Import “text1.txt” specifying the correct delimiters;
Set the following cell formats:
 1st
column: text;
 2nd and 3rd columns: numbers (no decimal);
 4th column: currency (1.50 €);
 5th column: percentage (1 decimal digit);
 6th column: date (17-Apr-2007).
ISDM 27000 – Christian Zieger
21/07/2015
Importing Tables (3)
7



Import the table in text2.txt;
Use the correct delimiters;
Set the following column formats:
 1st:
date (17-Apr-07);
 2nd: time (1:30 PM);
 3rd: currency (€ 1.45);
 4th: text;
 5th: number, no decimal digits.
ISDM 27000 – Christian Zieger
21/07/2015
Series
8


Open an empty Excel workbook.
In column B create a list of dates (month-year) from
January 1993 up to December 2004;
 Generate

automatically the series of items;
Create in column C a list of potential interest rates
from 0% to 3% with a 0,2% step (column C);
 Set
the correct cell format;
 Drag the cells.
Or
 Editing  Fill  Series
ISDM 27000 – Christian Zieger
21/07/2015
References
9






Relative  A1
Dragging or copying the formula  Excel updates
the references automatically
Absolute $A$1
Partial absolute $A1 A$1
F4: switching between the reference types
Cell names
 write it in name box
 No spaces, start with letter, avoid ambiguity
ISDM 27000 – Christian Zieger
21/07/2015
Functions
10




Formulas
 function library
 insert function
 type the function name
Notation
= functionName(arg1;arg2;…)
Check the in-line help for the function prototype
Nested functions: a function is used in place of an
argument
=functionName1(functionName2(arg1;arg2;..);arg2;…)
ISDM 27000 – Christian Zieger
21/07/2015
Mathematical Operations
11

In the same worksheet, put in column D the squares of
the values in column C;
 Drag

the formula  Excel updates the cell addresses
Insert in column E a series of numbers from 1 to 10
with step 0,5. Then:
 Column
F: log2 of column E;
 Column G: values of column F rounded to the nearest
lower number with 1 decimal digit
Use ROUNDDOWN() (check the differences with respect
to ROUND and ROUNDUP )
ISDM 27000 – Christian Zieger
21/07/2015
Absolute and Relative References
12

We want to analyse the selling statistics of a company:





3 different products
Data grouped in trimesters
I trim
II trim
III trim
IV trim
prod 1
12
43
31
12
prod 2
45
21
66
54
prod 3
23
80
65
Compute:
TOT
Total sold product units for each type and each period
Build two new tables with the layout of the table above
computing in the yellow cells (using cell references to the
original table):


Total
9
(first table) for each product type the selling percentage of each
trimester compared to the total yearly sold units of each single
product type
(second table) for each trimester the selling percentage of each
single product type compared to the total product units sold in each
trimester
ISDM 27000 – Christian Zieger
21/07/2015
Multiple-sheet Formulas
13




Open a new workbook, and in Column A insert a
series of rates from 0% to 10% with step 1%.
Put a random number from 0 to 1 in cell A1 of
Sheet3;
Insert 10000 euro in cell C3 of Sheet2;
In column B of Sheet1 multiply cell C3 in Sheet2 by the
rates in column A of Sheet1and divide the results by
the random number in cell A1 Sheet3;
Pay attention to cell addresses (use $ to protect the address);
 Cross-sheet reference: sheetname!cellreference

ISDM 27000 – Christian Zieger
21/07/2015
Logical Functions
14



Open laboratory.xlsx, sheet First;
For german students, return the mark of the IIS exam,
otherwise return “-” in column I;
Use IF(cond; action if true; action if false)
For each non-german student, determine the highest
mark, otherwise return “-” in column J;
Use NOT(). Do you really need it?
ISDM 27000 – Christian Zieger
21/07/2015
Logical Functions (2)
15


In column L, compute the average marks of each
“Eng” and “Agr” student, while for “Polito” students
do not display anything.
In column M:



For Italian students of 1st year write “x”
For German students of an year larger or equal than 2
write “xx”
otherwise leave the cell empty
ISDM 27000 – Christian Zieger
21/07/2015
Sum, SumIF, CountIF, AverageIF
16






Consider the database in sheet “First” of
laboratory.xlsx
In B20 compute the sum of the IDs
In F20 compute the sum of the IIS marks larger than 25
In G20 compute the sum of the Stat. A marks of Polito
students
In C20 report the number of italian students
Average of the IIS marks of german students in F21
ISDM 27000 – Christian Zieger
21/07/2015
Working with text
17


Open laboratory.xlsx, sheet Text;
Compute:
 The
length of text in B2 and put the result in B3;
 Put in B4 the first 10 characters of the content of B2;
LEFT()
 Get the first 10 and the last 20 characters of the text in
B2 and print them together in B5.
LEFT(), RIGHT(), CONCATENATE()
ISDM 27000 – Christian Zieger
21/07/2015
Dates
18

In a new workbook in a sheet named Date put:
 A1:
today;
 A2: 7 days after today;
 A3: exactly 2 months after the date in A2 (use DATE);
 A4: exactly 1 year before the date in A2 (use DATE);
 A6: difference (in days) between A4 and A3;
 A7-A56: sequence of static dates: 1 Mar 2007, 6 Mar 2007,
11 Mar 2007,… (use date(), +5 or auto-fill);
 In
B7:B56  weekday number for dates in A7:A56
(Sunday=1);
ISDM 27000 – Christian Zieger
21/07/2015
DATEDIF
19




In cell B3 compute the number of days between
dates in A4 and A3
In cell B4 compute the number of months between
dates in A4 and A3
In cell B5 compute the number of years between
dates in A4 and A3
In cell B6 compute the rounded number of weeks
between dates in A4 and A3
ISDM 27000 – Christian Zieger
21/07/2015
Statistics
20


Open laboratory.xlsx, sheet statistics;
For each sequence compute:
 Average,



maximum, minimum, standard deviation
Open laboratory.xlsx, sheet probabilities
For each value x and for a normal distribution with
mean= –1.5 and standard_dev=4 compute the
area from –∞ to x (use NORM.DIST).
In cell C1 put the value of x for which the probability
is 95% (use NORM.INV)
ISDM 27000 – Christian Zieger
21/07/2015
Scenario Manger
21

Create a table to analyze the revenue of a company
for different possible scenarios.
I Trim.
 The
Amount
Price
Income
Fix Cost
Prod Costs
Total Costs
Net
CumulativeNet
Growth
100
€10,00
€1.000,00
€300,00
€3,00
€600,00
€400,00
II Trim.
III Trim. IV Trim
120
144
172,8
€10,00
€10,00
€10,00
€1.200,00 €1.440,00 €1.728,00
€300,00 €500,00 €500,00
€3,00
€3,00
€3,00
€660,00 €932,00 €1.018,40
€540,00 €508,00 €709,60
€2.157,60
company sells 100 products
in trimester I at 10 euro each
 Each trimester the production
20%
grows of 20%
 The fixed production costs are 300 euro per trimester if the
production is below 140 pieces, otherwise it is 500 euro
 The production of each product costs 3 euro (consumable).
 Build a table as in the picture to determine the cumulative net
income. Use formulas for cells with yellow/green background
ISDM 27000 – Christian Zieger
21/07/2015
Scenario Manger + Data Table
(sensitivity analysis)
22

Evaluate the cumulative income under the following
possible scenarios and create a report:
 the
growth is 30%, the price is 10 and the initial amount is 100
 the growth is 30%, the price is 9 and the initial amount is 120
 the growth is 30%, the price is 12 and the initial amount is 95
 the growth is 10%, the price is 10 and the initial amount is 100
 the growth is 10%, the price is 9 and the initial amount is 120
 the growth is 10%, the price is 12 and the initial amount is 95

Using Data Table evaluate the total net income for the
growth varying from 5% to 40% and the price from 8 to
15 euro
ISDM 27000 – Christian Zieger
21/07/2015
GOAL_SEEK()
23


Open worksheet Goal.
Compute:

In column B:

In column C:


In column D:
f1 x  cos(x) / x
f 2  x   3x 2  2 x  5
f3 x  log3 x
2

Using Goal Seek on cell A2, for functions 2 and 3 find:
 For
which value they are equal to 0;
 For which value they are equal to 2;
 There are 2 solutions….
ISDM 27000 – Christian Zieger
21/07/2015
Mathematical Graphs
24


Open laboratory.xlsx “Goal”
2


f
x

3
x
 2x  5
Create a mathematical graph of 2


(SCATTERPLOT using only lines).
Set the following options in the graph:
red and very thick line;
 Thick axes, correct format of the values;
 y-axis font: blue, Times New Roman, 11 pt.


Insert a text box:
Text: “Zero Crossing”;
 Box: 1 pts black;
 Background: white;
 Arrows from the box to the zero crossing points.

ISDM 27000 – Christian Zieger
21/07/2015
Graphs
25

Add the other two functions to the same graph;
Include a grid;
Use different colors and different line types;

Scatter plot VS lines:


 Plot
functions using “line” instead of “scatter plot”
and see what happens to the x axis.
 This is not a mathematical graph.
ISDM 27000 – Christian Zieger
21/07/2015
Bar Plots
26



Open the sheet called fruits in laboratory.xlsx;
Create a bar plot that represents for each fruit the
tons sold in 2006;
Features:
 Green
bars;
 Gray plot background;
 Horizontal step set to 20 tons;
 Values on the right of each bar;
 No legend
 Title and labels on each axis;
ISDM 27000 – Christian Zieger
21/07/2015
Bar Plot (2)
27
MODIFY the chart including also the data about the
productions in 2005 and 2004:
 2005
and 2004 bars adjacent to 2006 bars;
 Colors: red for 2005 and yellow for 2004;
 Insert a legend;
 Use overlapping bars (30%);
 Put the chart in a new sheet.
7.3
5.2
5.3
Strawberries
80.2
55.5
62.4
Bananas
Fruit
19
9.4
12.8
Pears
Tons 2004
Peaches
48.1
31.5
38.3
Apples
42.4
40.2
43.6
0
20
40
60
Tons 2005
Tons 2006
80
100
Tons

ISDM 27000 – Christian Zieger
21/07/2015
Pie Charts
28


Open file laboratory.xlsx and consider the labels in
“Pie Labels” and the data in “Pie Data”;
Create a pie chart:
Tons 2006
Manually insert the labels
using “select data”
 Legend at the bottom
 Data category labels outside
the pie

Strawberries
Apples
Bananas
Peaches
Pears
Apples
ISDM 27000 – Christian Zieger
Peaches
Pears
Bananas
Strawberries
21/07/2015
Solver
29




Activate the Solver Add-in in options  add-ins
Optimize objective functions given a set of constraints.
Simple example:
consider the problem we solved in dataTable.xlsx
(available at Course_Coletti\ISDM3\Excel_Labs
Maximize the Total Net Profit given that:
 the
total number of production hours: 16hours x 5days
 For each product the minimum production is 40 pieces.
ISDM 27000 – Christian Zieger
21/07/2015
Conditional Formatting
30


Open file laboratory.xlsx sheet cond_format
Highlight all the cells containing the word “Dog”
Select the Favorite Pet column
 Click the Conditional Formatting button





Choose Highlight Cells Rules  Equal To
Type the word “Dog”, select the color and press OK
Apply a color scale from light green to dark green to
the column “Weekly Allowance”.
Put a red circle when the value is ≥ 20, yellow when ≥
10 and green otherwise.
ISDM 27000 – Christian Zieger
21/07/2015
Save as PDF and protect the file
31

Save the sheet economics of laboratory.xlsx as PDF
file with name: ecosheet.pdf
 Office

Button  Print  select “ADOBE PDF”
The PDF file must have the following features:
 Show
the gridlines; Landscape;
 Fit the sheet in 1 page;
 Header: your name in the middle;
 Footer: time on the left, “ISDM” in the middle and date
on the right

Protect the file: remember to save to apply
protection
ISDM 27000 – Christian Zieger
21/07/2015
Protecting Worksheets
32


Open the file laboratory.xlsx and consider the sheet
pivot
Protect whit a password the whole sheet but the
“party” column
 Changes
 Allow Users to Edit Ranges  New to free the party
column
 Protect Sheet and type your password twice
 Review
ISDM 27000 – Christian Zieger
21/07/2015
Sorting
33


Open laboratory.xlsx sheet Sorting;
Order the small database by:
 Sales,
descending;
 Surname, ascending;
 Surname and then name (since there are duplicate
surnames)
 Now
include Headers (unselect “My data has headers”):
the sorting messes up your database!
ISDM 27000 – Christian Zieger
21/07/2015
Statistics with Excel
34

Reminder: TOOLPACKS
 File
-> Excel Options -> Add-Ins -> Analysis ToolPak ->
Go -> Analysis ToolPak



Open the file laboratory.xlsx, sheet probabilities
Display descriptive statistics for x
Display an histogram for variable x, paying attention
to:
Histogram intervals (you must specify them directly)
 Distance among bars (reduce it to 0)

ISDM 27000 – Christian Zieger
21/07/2015
Worksheets
35


Open laboratory.xlsx
Create a copy of the sheet economics:
 Name:
duplicate;
 Tab color: green;
 Move it at the end of the sheet list.

Lock the first two rows of the sheet:
 Use
split and freeze
or
 Go to a cell in the third row and click split

Unhide column C and hide column H;
ISDM 27000 – Christian Zieger
21/07/2015
Pivot Tables and Charts
36


Open the file laboratory.xlsx, sheet pivot
Exercise 1
 Build
in a new sheet a pivot table with age groups as
rows, parties as columns, and vote count as cells
 Build a stacked column diagram

Exercise 2
 Build
in a new sheet a pivot table with parties as rows,
ballot status as columns and vote count as cells
 Remove “ABT” and “(empty)” columns
 Build a pie chart for party including all ballot statuses
ISDM 27000 – Christian Zieger
21/07/2015
Exercise
37




Create an Excel document that computes the amount of
taxes you have to pay given your annual income. Consider
the following taxation scheme:
Retirement contribution: 9% of the income
On the remaining gross income the following tax rates apply:
up to 15.000 euro  23%
up to 28.000 euro  27%
up to 55.000 euro  38%
up to 75.000 euro  41%
above 75.000 euro  43%
rates are applied to the amount exceeding the previous threshold
(i.e: 23000  15000*23%+8000*27%)
ISDM 27000 – Christian Zieger
21/07/2015
Exercise (cont.)
38



Compute the amount to pay, the net income (after taxes)
and the average tax rate for a given income. Consider
different scenarios.
Create a mathematical graph reporting the average tax
rate as a function of the annual total income.
Modify the worksheet considering deducible costs (e.g.,
medical expenses). 19% of this costs is subtracted from
the income after retirement contributions.
Compute the new net income (including the costs) and
analyze its trend through a 2-D sensitivity analysis (data
table)
ISDM 27000 – Christian Zieger
21/07/2015