Get documents about "Microsoft Excel for Windows (Tutorial #2)
Document Sample
Microsoft Excel for Windows (Tutorial #2)
Objectives of this tutorial
This tutorial is designed to further strengthen your skills in using Excel. This will assist you to import
data from other systems or platforms in order to create a business report. In this tutorial, you will learn
to:
• import data from text file to Microsoft-Excel
• use a lookup function to look up an answer from a table of possible answers
• use IF function.
Importing Data to Excel
Sometimes it is very useful to import data from other systems or platform to Excel. Excel provides a
worksheet view that enable users to work on data easily. In this exercise, you will need to copy two files
from https://203.131.208.148/registrar/login.asp The files are default.txt and customer.txt.
The default.txt contains customer default data: customers’ ID, monthly installment, and number of
months that customers fail to pay.
In this section you will learn how to import data from a text file, using an Excel Wizard to guide you in
converting the data into Excel format.
Before you begin importing data to Excel, it is important to understand text files and separation choices.
• Text data consists only characters (letters, digits, and special characters) that you can type on a
keyboard—devoid of any special formatting. Text data is the most common data format because
just every program exists today can store your data in text format regardless of the product’s native
format.
• As text data have no internal markers to indicate where data begin and end, text files and programs
that read text files rely on delimiters to separate data filed from one another. A delimiter is a
character or group of characters that separates two pieces of data. Common delimiters you will
encounter include blanks, commas, semicolons, quotation marks, brackets, and braces. An
alternative to delimited data is to arrange data into specific locations or starting positions. Such as
arrangement is known as fixed-width data. Values separated by commas are called comma-
separated values (CSV)
Consider the example below (taken from default.txt and customer.txt). Notice that data in the file
customer.text consists of three fields which are separated by commas. Data in the file default.txt consists
of three fields without any separators. However, each fields have fixed length.
Opening the Text Import Wizard
2009
Page 2
XNZ is a local bank in Christchurch. The slow-down economy makes some customers not be able to pay
back the mortgage. Your task is to create a spreadsheet showing customers’ loan defaults for further
business analysis. Once you start Excel, you are ready to import/convert the text files.
Step 1: Click the Open button on the
standard toolbar, click the Files of
type list arrow, click the Text Files
(*.prn;*txt;*.csv) entry, click
default.txt, and click the Open
button. The Text Import Wizard—
Step 1 of 3 dialog box opens.
Step 2: Click the fixed width option
button, if necessary, to indicate that
the text file contains fixed-width
data. (As you can see earlier that
the file default.txt is arrange in
fixed width format.) Then, click the
Start import at row spin box, if
necessary. In this case, you will start
importing at row 1. Click the Next
button to go to the second Text
import Wizard step.
Step 3: Move the suggested column
break indicator by clicking and
dragging the line to the 7th position
in order to separate Cust_ID from
Monthly installment field. (The
Cust_ID field has 7 characters).
Suggested column
break indicator
Step 4: You can create a break line by
clicking at the 26th position in order to
separate Monthly Installment column
from Months Default column. Then,
click the Next button.
Tip: You can delete a breakline by double clicking on it.
Page 3
Step 5: You can specify how to import
values in each columns separately
by clicking on any columns in the
Text Import Wizard. You can
choose to import columns as text or
date at a specific format. In this
case, you choose General which
converts numeric values to
numbers, date values to dates, and
all remaining values to text. Then,
click the Finish button.
Excel imports data from the text file. There are three columns:
Cust_ID, Monthly Installment, and Months Default.
Save the file as Customer_Default.xls.
Notice that, there is no column indicate customers’ names
which you may wish to add later in this exercise.
Another task is to import another file which contains customers’ names. It can be done by repeating
Steps above. However, this time you will import the file customer.text which its data is arranged by a
delimiter.
Step 1: Click the Open button on the
standard toolbar, click the Files of
type list arrow, click the Text Files
(*.prn;*txt;*.csv) entry, click
default.txt, and click the Open
button. The Text Import Wizard—
Step 1 of 3 dialog box opens.
Step 2: Click the Delimited option button,
if necessary. Then, click the Next
button.
Step 3: In the second Text import Wizard
step, untick Tab and tick Comma.
Excel puts column breaks where
commas are presented in the original data. Then, click the Next button or the Finish button. The
Next button will lead you to the third step where you can choose to delete some columns or to
specify the type of data to be imported as.
Excel imports data from the text file. There are three columns: Cust_ID, First
name and Surname.
You may wish to copy data from the file to work as another work sheet on
Customer_Default.xls.
Page 4
Copy entire data
Step 1: Click to activate the Customers_Default.xls file. On the standard toolbar, click Insert and click
Worksheet. Excel gives a new worksheet name Sheet 1
Step 2: Rename the worksheet by double-clicking at the tab Sheet 1 and type in customer. Now you
have two worksheets named customer and default.
Step 3: Click to activate the customer.txt. Click the top left corner of the worksheet (left of column A
and above the 1st row) that contains customers data in order to select the entire worksheet. Click
Edit on the standard toolbar and click Copy.
Step 4: Navigate to cell A1 on the customer worksheet in Customers_Default.xls file, Click Edit on
the standard toolbar and click Paste.
You may wish to save the file for further use.
Lookup function
In some situation, you need to look up an answer from another database. For example, you need to look
for customers’ names corresponding to the customer IDs. Situations like the preceding ones call for a
special class of functions called lookup functions, also known as table lookup functions. The table that a
lookup function searches is called the lookup table, and the value being used to search the lookup table is
called the lookup value.
Excel provides two lookup functions: VLOOKUP and HLOOKUP. You use the VLOOKUP function,
which stands for vertical lookup, for a vertical lookup table—one in which the search values are in the
first column of the table. You use the HLOOKUP function, which stands for horizontal lookup, for
lookup tables in which the lookup column is in the first row of a multi-row table. In this exercise, you
will learn only VLOOKUP. You can apply the same approach to use HLOOKUP.
Before you learn about VLOOKUP, you need to rearrange the default worksheet in order that more data
can be added. You task is to prepare a table that show the analysis of customer loan defaults. The
following fields are suggested: Cust ID, First name, Surname, Monthly installment, Months default,
Total default balance (Monthly installment x Months default), Estimated debt loss percentage, Estimated
debt loss (Total default balance x Estimated debt loss percentage).
Add/insert columns
Step 1: You want First name and Surname of the customers to appear next to Cust_ID. Click on
Column B in order to select the entire column and on the standard toolbar, click Insert; then,
Columns. Click on Column B again to insert another column by repeating the Insert command.
Type in the columns’ headers as First name (cell B1) and Surname (cell C1).
Step 2: Type in the followings:
- “Total default balance” in cell F1
- “Estimated debt loss (%)” in cell G1
- “Estimated debt loss ($)” in cell H1
Using VLOOKUP
Page 5
For the VLOOKUP function to work properly, the table must be sorted from low to high on the values in
the first column. Otherwise, the function returns spurious results. If necessary, sort the list on the
customer worksheet with the Descending option.
The first argument of the function is the lookup value. The VLOOKUP function uses that value to search
the lookup table. The second argument is the lookup table, which is the cell range or name specifying the
lookup table’s location. The third argument is the column containing the data you want VLOOKUP to
retrieve. Finally, the fourth argument can be FALSE, TRUE, or omitted. If it is FALSE, VLOOKUP will
look for an exact match in the lookup table. If it does not find a value in the first column exactly
matching the lookup value, VLOOKUP returns #N/A error value. If the argument is TRUE or omitted
(most people omit the argument), then VLOOKUP searches the first column looking for the largest value
in the first column that is less than or equal to the search value.
In order to prepare the table, you may use VLOOKUP to find the exact values (FALSE) because the
customer IDs are unique and are the primary key in the list. You will look up for the Customers First
name and Surname corresponding to the Customer IDs.
Step 1: Click cell B2 on the customer worksheet. On the
standard toolbar, click Insert, and Function… The Insert
Function dialogue box opens.
Step 2: On Or select a category drop-down box, select
Lookup & Reference. Scroll down in Select a function
list to find VLOOKUP, which is at the end of the list.
Click the OK button.
Step 3: Type cell “A2” as
Lookup_value. Excel looks
for data that is corresponding
to this cell (Cust_ID). Click
Tab or click the blank next to
Table_array. Navigate to
customer worksheet by
clicking a customer tab at
the bottom of the worksheet;
then, select cell A2 to C58 in
the customer worksheet by
clicking cell A2, dragging to
cover the range, and releasing
the mouse at cell C58.
Alternatively, you may type
in “customer!A2:C58”
which specify the Table_array to get data from customer worksheet ranging from A2 to C58.
Step 4: Type “2” in the Col_index_num box. Excel looks and returns value from the second column in
the array corresponding to the lookup value being specified.
Step 5: Type “FALSE” in the Range_lookup box. Notice that the Formula result shows Adam. Then,
click the OK button.
Tip: Instead of filling functions cell-by-cell in column B, you can simply copy the function
from B2 to the rest of column B. However, the function is filled with a relative cell
reference, you may encounter a problem with the copied function in cell B3 as the
function will have the value to look in the Table_array as “customer!A3:C59”
although the data range is still A2:C58. To correct this problem, before you copy the
function to the rest of column B, you need to edit the formula using an absolute cell
Page 6
reference. The absolute cell reference fixes the cell references no matter where the
formula containing it is copies. While you want the lookup_value to change to A3, A4,
etc., you want to fix the table_array range to A2:58 in the customer worksheet.
Click cell B2 and click the formula bar in order to edit the function.
With the cursor between “customer!A|2” press F4. (or type “$” in front of “A”
and “$” in front of “2”.) Then, do the same for C|58. Press enter.
Step 6: Click cell C2 on the customer worksheet. Repeat Step 2-5 in order to look up for customer’s
Surname corresponding to Cust_ID. Do not forget to change the col_index_num and edit the absolute
cell reference. Then, copy the function to the rest of column C.
Step 7: Complete the rest of the table by using what you have learned from the previous tutorials. Format
cells as you want.
Note: Total default balance = Monthly Installment x Months default
IF function
Page 7
From the bank’s experience, customers who fail to pay back the loan for more than 6 months would be
bad debts. While the customers who default for less than 6 months would be bad debts by 20 percent.
You task is to estimate the total bad debts loss.
You can manually input percentage one-by-one to the default worksheet. However, it would take hours
when the data is large. Writing a formula to capture the preceding business rule requires a new
statement—one in which there is one outcome from two possible choice. Excel anticipates this and
provides the function IF, belonging to the logical function category. The IF function has the following
form:
IF (conditional test, expression if true, expression if false)
A conditional test is an equation that compare
two values, functions, formula labels, or
Start
logical values. Every conditional test
equation must include a relational operator,
True False which compares two parts of a formula. The
result of the comparison is either true or
Months > 6
false. For example, in the conditional test E2
> 6, the greater than symbol (>) relational
operator compares the values in cells E2 and
6. IF E2 is greater than 6, then the result of
Bad debts = 100% Bad debts = 20% the conditional test is true. Otherwise, it is
false. In the default worksheet, the
conditional expression you will write
examines the number of months customers
have defaulted.
The second and third arguments of the IF function can be constants or arbitrarily complex expressions.
The IF function displays the computed value of only one of the two argument expressions, depending on
the evaluation of the conditional test. If the conditional test is true, Excel calculates and displays the
value of the second argument. Otherwise (if the test is false), Excel calculates and displays the value of
the third argument. Thus, you can create a formula whose output depends on a condition—a value in
another cell.
Writing an IF function
Step 1: On default worksheet, click cell G2, the cell that will display bad debts percentage.
Step 2: Type =IF(E2>6,100%,20%) and press Enter. You may copy the formula to other cells in
column G
G2, G3, G4, G5 show 20% since the conditional test (E column is not greater than 6) and G6 shows
100% since the conditional test (E column is greater than 6).
Using Insert Function to write an IF function
Page 8
Step 1: Click cell G2 on the default worksheet. On the
standard toolbar, click Insert, and Function… The
Insert Function dialogue box opens.
Step 2: On Or select a category list box, select Logical.
Click IF in the Select a function list box, and then
click OK. The function Arguments dialog box
opens.
Step 3: In the following boxes, type in:
- Logical_test – E2>6
- Value_if_true – 100%
- Value_if_false – 20%
Then click the OK button.
A more complex argument can be written when there are more than two possible outcomes from which
to choose. For example, customers who fail to pay back the loan for more than 6 months would be bad
debts. While the customers who default for more than 3 months but less than 6 months would be bad
debts by 20 percent. And the customers who default for 3 months or less would be bad debts by 10%. We
can draw the conditions as:
Start
The conditions can be shown:
=IF (Months>6, 100%,
True False IF (Months>3, 20%,
Months > 6
10%)
There is another IF function
Bad debts = 100% True False when the first condition is not
Months > 3 met in order to check for further
condition.
Bad debts = 20% Bad debts = 10%
If the bank change the conditions, you can write IF function as:
=IF(E2>6,100%,IF(E2>3,20%,10%))
The rest of your task is to calculate the Estimated debt loss ($) which is equal to Estimate debt loss
(%) multiplied by Total default balance. Format the table as you wish. The figure in the next page
shows the completed table with the estimated debts loss of 100% for defaults over 6 month, 20% for
defaults over 3 months, and 10% for 3-month defaults or less.
Tip: IF function can return phase or words. For example, the if function that return Yes if E2>12 and
return No for everything else is written— =IF(E2>12,”Yes”,”No”). The return value that are words or
phases need to be put under the double-quote. If you want the value to return as a blank, you can use “”.
Page 9
In your own time
In this exercise, you will need to copy two files from https://203.131.208.148/registrar/login.asp name
qc1.txt and product.txt. Paul Sweet, Inc. is a chocolate manufacturing company in South Island. The
company use two bag-filling machines. The quality control (QC) staff samples batches of chocolate bags
and counts. The QC staff enters the data into the main computer system of the company. You extract 50
batches of data from the main computer system. Your task is to analyze the batches of chocolate whether
they are filled with designated pieces. The marketing department also give you an extraction of product
data as product.txt. The company policy is to “Return to the line” any batches that are inaccurately
filled by 1 piece and to “Sell without Package” any batches that are inaccurately filled more than 1
piece. Prepare a worksheet to determine which batches must be returned to the line and which packages
must be sold without package.
The file product.txt has 3 fields which are ID (product ID), Product (product description), and
Benchmark (the standard quantities for bags to be filled). The file qc1.txt has 3 filed which are Batch
(batch number), product (product ID), and Piece (average numbers of pieces in the batch).
Hints: =abs(number or cell) returns the cell with the absolute value of the number or the cell.
Page 10
The solution worksheet from the case.
