1 / 28

IENG 423 Design of Decision Support Systems

IENG 423 Design of Decision Support Systems. Modeling with Excel Excel Basics Filtering, sorting, data validation, summarizing data. Note about files. Through out these slides references are made to files to use to try the concepts being discussed.

nysa
Download Presentation

IENG 423 Design of Decision Support Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. IENG 423Design of Decision Support Systems Modeling with Excel Excel Basics Filtering, sorting, data validation, summarizing data

  2. Note about files Through out these slides references are made to files to use to try the concepts being discussed. These files are from the CD included with the MS Excel: Step by Step book

  3. MS Excel Using Filters Sometimes you have more data in a worksheet than you want to see …Takes you attention away from what is relevant to the problem One way to deal with this issue is the use of Filters

  4. MS Excel Using Filters Note: If your primary DSS problem is one of locating, extracting or organizing records of data, rather than dynamic calculations, then the problem might be more suitably frames as a database problem

  5. MS Excel Using Filters But, sometimes Using MS Access (or another DBMS) complicates the solution MS Access has a nontrivial learning curve Your block of data is not that big You want to keep your DSS application in Excel Your client know Excel, does not know Access ! You need to do dynamic modeling…

  6. MS Excel Using Filters It may make more sense to stay with Excel

  7. MS Excel Using Filters Sometimes you have too much data – rows and rows You can limit the amount of data for your view… …or analysis You want the user to focus on a specific subset of data

  8. MS Excel Using Filters Use Filter.xls from UsingFilters subdirectory Filters eliminate rows (whole rows) that do not meet criteria Note: they are not permanently eliminated Turn off the filter and they are back

  9. MS Excel Using Filters Select a column or a header Select Data->Filter->AutoFilter This toggles on filtering for the selected column Select the pull-down arrow – see filter dialog box Select value from list Top 10 Custom Show All

  10. MS Excel Data Validation You can control using input By setting rules for what can and cannot be entered in a cell Improved user interface – pick from list Reduces errors Remember Murphy’s Law - if a user can do it wrong there is good chance it will be done wrong

  11. MS Excel Data Validation Select cell where you want data validation Select Data from the main toolbar… …then select Validation The Data Validation dialog box will pop-up Select the type of data validation that you want

  12. MS Excel Data Validation For the selected cell or cells you can limit input to – A specific range of whole numbers ( 1 to 10) A specific range of decimal numbers (0.5 to 6.5) A specific date or date range (1/1/2008 to 1/15/2008) A specific time or time range (08:00 AM to 11:30 am)

  13. MS Excel Data Validation Validating from a list You can an options list and only values that are in that list range can be entered in the cell or cells Suppose in G1:G5 you have WV, VA, PA, MD, and DE Then for Cell A1 using list validation with the rule =G1:G5 Would limit input into A1 to one of those five state values

  14. MS Excel Data Validation Validating from a list Note: the question came up in class – Can you validate from a list with the list coming from a different worksheet In other words for A1 on sheet1 the rule =Sheet2!G1:G5 Well, the answer is no, sort of Excel 2003 specifically forbids this However, read on….

  15. MS Excel Data Validation Validating from a list Suppose you have a lookup list on another worksheet (say Sheet2) Select you validation list on Sheet2 and make is named range (like states for the state code list) Then in the validation rule for your cell (i.e. A1) Enter an = and then the named range For example =states That works!

  16. MS Excel Data Validation Validating from a list You can also set an Input prompt for the cell or cells This prompt will show up in a balloon when you hover over the cell You can also set a specific error message to be displayed when the user enters something that does not conform to the rule

  17. MS Excel Data Validation Use Sorting.xls from ReorderingAndSummarizing folder Create a list of the days of the week abbreviations (Mon, Tue, Wed,…) Out in the worksheet like H1:H7 Select cell to set list validation Select Data->Validation, then List from the validation dialog box Click in Source field, then drag across your day-of-week list range (H1:H7) Then click OK

  18. MS Excel Sorting and Summarizing Data Use Sorting.xls in ReorderingAndSummarizing folder Select Range or Header for column Select range of a single column in a table with more columns… …and sort … Excel will want to fix your sort selection to include adjacent columns, its will say— .. Expand Selection

  19. MS Excel Sorting and Summarizing Data In Sorting.xls Select Sales -> Data -> Sort (don’t expand range– what happened) It scrambled the data, didn’t it! Undo Sort Redo Sort, but accept expanded range …what is different?

  20. MS Excel Sorting and Summarizing Data What is the sort order? If you sort days of the week what do you get What is the first day of the week – alphabetically? …the second?

  21. MS Excel Sorting and Summarizing Data What is the sort order? What if you want a non-normal order Like in day-of-week order Create a custom list Select Tools -> Options -> Custom Lists Enter or select list then OK Then to sort by day of week Select header or range Select Data->Sort, then on the Sort dialog box, select Options In the Options dialog box select the appropriate list under First Key Sort Order

  22. MS Excel Making a Data List Use Sorting.xls in ReorderingAndSummarizing folder Why, what is a data list Data in rows and columns belong together …like records in a set Excel 2007 calls this a table To create a data list (can’t have a filter enabled Select a range, select Data->List->Create List

  23. MS Excel Making a Data List After creating a data list Sort again Click anywhere inside the list, then select Data->Sort – Select Sales Sort treats list as a whole – that is, it sorted the list together FYI – autofiltering is automatically turned on when you create a list You can turn it off it you want

  24. MS Excel To get on-the-fly totals Select a range (column or row – sales for example) Look in the autosum box on the status bar Change Sum to other statistic – right click on Autosum box, select Mean, Min, Max, …

  25. MS Excel To add a Total row Click “Toggle Total Row” in toolbox To get a different statistic Change to Average if you want the mean – right click in Total row cell, select Average

  26. MS Excel Displaying Data in Collapsible Levels Sort by column that has categories– like Week Can’t be a list The click Data->Subtotals Complete dialog box Shows summary data for levels Controls on left side allow you to collapse or expand levels

  27. That is all

More Related