Microsoft Excel for Windows (Tutorial #2)
Shared by: smapdi62
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://188.8.131.52/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://184.108.40.206/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.