Introduction to management information
Yüklə 24,56 Kb.
|
- Bu səhifədəki naviqasiya:
- We should all do this practice lab by ourselves and show it to the Professor, before we start on our Excel project.
- Formula Description (Result)
- Insert
- Order
|
MIS 3003 INTRODUCTION TO MANAGEMENT INFORMATION SYSTEMS Fall 2014 Professor: Akhilesh Bajaj Welcome to learning advanced Excel skills we will use in our Excel Project! This lab assumes we have a basic set of skills in Excel, including creating charts. In this lab, we will learn:
We should all do this practice lab by ourselves and show it to the Professor, before we start on our Excel project. Let’s get started! First, we should download the ExcelPractice.xls workbook from the course website: http://nfp.collins.utulsa.edu/bajaja/MIS3003/ We should save this sheet in a convenient folder either on a hard disk or a zip disk, that we can access later on as well. If we open the workbook, we’ll see sheets for practicing different features. E.g., one sheet is VlookupExample, that allows us to practice Vlookup. Now let us explore each new feature of Excel!!
Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column we specify in the table. The V in VLOOKUP stands for "Vertical." Syntax VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string. Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List. It is usually a range name.
Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value. Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. Remarks
Practice Example Look at the example sheet called VlookupExample. Try the following formulas in cell E4 of the sheet:
ISNA Function Syntax: ISNA(value) Value is the value (or reference to value) we want tested. Value can be a blank (empty cell), error, logical, text, number, or reference value, or a name referring to any of these, that you want to test. ISNA returns TRUE if the value is the #NA error string, otherwise it returns FALSE. Usually, when we do a Vlookup, we will use the ISNA function to check and make the lookup_value we are supplying actually exists on the leftmost column of the target range or sheet. Practice Example: First, let us create the range Testrange in sheet VlookupExample. To create a range we select the cells, and then click the name box and type in the name. Let us do this for the cells A2:C10 in sheet VlookupExample. Call these cells: TestRange. In cell E14, type in: =IF(ISNA(VLOOKUP(D14,TestRange,1)), "Bad", VLOOKUP(d14,TestRange,2)) What value do we see in E14? Now change this to: =IF(ISNA(VLOOKUP(D14,TestRange,1,FALSE)), "Bad", VLOOKUP(d14,TestRange,2)) What value do we see now in E14?
MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. Syntax: MID(text,start_num,num_chars) Text is the text string containing the characters you want to extract. Start_num is the position of the first character we want to extract in text. The first character in text has start_num 1, and so on. Num_chars specifies the number of characters we want MID to return from text. Remarks
Practice Example Open the MIDExample worksheet. In cell C3, type in:
Great!! Now let’s do Pivot tables. Pivot Tables A pivot table summarizes data along multiple dimensions. Open the Sheet PivotExample and study the raw data. Suppose we want to find out how much John sold in total, across sports & time. Suppose we wanted to know how much we sold in Quarter Q1. Suppose we wanted to know how much we sold for Golf? A pivot table will answer these kinds of questions by summarizing data across fields. Let us practice using the pivot table wizard in Excel. Do the following:
Usually you can lay out the report onscreen, and this method is recommended. Use the wizard to lay out the report only if you expect retrieval from a large external data source to be slow, or you need to set page fields to retrieve data one page at a time. If you aren't sure, try laying out the report onscreen. You can return to the wizard if necessary.
If you don't see the field list, click within the outlines of the PivotTable drop areas, and make sure Show Field List  is pressed in.
To see what levels of detail are available in fields that have levels, the click
If you add more than one data field, arrange these fields in the order you want: Right-click a data field, point to Order on the shortcut menu, and use the commands on the Order menu to move the field.
To hide the drop area outlines, click a cell outside the PivotTable report. Now practice creating a new sheet (call it PivotExample2) and summarizing the sales by salesperson, by quarter and by sport. Conditional Formatting Conditional formatting is very useful in making our sheets easy to read. To make conditional formats, we highlight the cells we want to format, then FileConditional Formatting, then put in the conditions, and the desired effect. Use the ADD button to add conditions. For our practice, we will create 2 conditions that are described in sheet ConditionalFormatExample. We should go ahead and try it out!!! Congratulations, you have finished the Excel lab!!! For a list of skills we acquired, please refer back to page 1. Yüklə 24,56 Kb. Dostları ilə paylaş:
|