A1

Document Sample
A1 Powered By Docstoc
					Spreadsheet-Based Decision Support Systems

                   Chapter 1: Introduction




Manuel Gonçalves
                                        mgoncalves@fe.unl.pt
FEUNL
                        Overview of Excel
   Excel Basic Functionality
     –   Chapter 2: Excel Basics and Formatting
     –   Chapter 3: Referencing and Names
     –   Chapter 4: Functions and Formulas
     –   Chapter 5: Charts
     –   Chapter 6: Pivot tables


   Excel Extended Functionality
     –   Chapter 7: Statistical Analysis
     –   Chapter 8: Solver and mathematical programming
     –   Chapter 9: Simulation
     –   Chapter 10: Working with Large Data




                                        2
Spreadsheet-Based Decision Support Systems
            Chapter 2: Excel Basics and Formatting




Manuel Gonçalves
                                        mgoncalves@fe.unl.pt
FEUNL
                           Overview
   2.1 Introduction
   2.2 Defining the Set of Excel Objects
   2.3 Entering Data into Cells
   2.4 Understanding Excel Menus and Toolbars
   2.5 Formatting
   2.6 Summary




                                   4
                             Introduction
   The Excel object hierarchy

   Basic data entry and data copying and pasting in cells

   Excel menus and toolbars

   Customizing and creating new menus and toolbars

   Formatting cells

   Conditional formatting




                                      5
         Defining the Set of Excel Objects
   Objects: The elements manipulated in spreadsheet applications and
    VBA programming.

   Object Hierarchy: An ordered listing of all objects.

   Object Model: The entire system of objects.




                                       6
                       Entering Data into Cells
   Simple values: Numbers and text that are entered into cells.

   Click-and-drag: Used to copy a highlighted cell or range of cells to an
    adjacent cell or range of cells.




         Copying a range of cells.
                                          Excel automatically numbers sequentially when
                                          copying from a pattern it recognizes.



                                      7
Understanding Excel Menus and Toolbars
   Menus and Shortcut Keys

   Toolbars

   Customizing Toolbars and Menus




                                     8
               Menus and Shortcut Keys
   The Menu contains the standard Windows options of File, Edit, View,
    Insert, Format, Tools, Window, and Help, as well as a Data option.



   Some options are specific to Excel; for example, in addition to Delete, we
    find Delete Sheet and Move or Copy Sheet.
   Another example of a menu option specific to Excel is the Paste Special
    option.
     –   Values
     –   Formulas
     –   Formats
     –   Transpose




                                      9
                              Toolbars
   Two main toolbars are Standard and Formatting.




   A dialog box is a separate window that provides several modifiable
    options on different tabs, or categories, within the box.

   To drag-and-drop, click, hold down on the mouse, and drag.




                                     10
        Customizing Toolbars and Menus
   Excel allows us to customize any toolbar.
     – Tools > View > Toolbars > Customize
     – Tools > Customize




   Select the toolbar you wish to customize or select New to create a new
    toolbar or menu.

   Choose from available commands to add to your toolbar. (We will also
    learn how to add customized commands when we study VBA macros.)


                                      11
                             Formatting
   Formatting Cells

   General Formatting

   Conditional Formatting




                                 12
                        Formatting Cells
   To format a cell, we either click on a cell and choose Format > Cells from
    the menu, or we right-click on the cell and choose Format Cells from the
    drop-down list.

   Formatting options include
     –   Number
     –   Alignment
     –   Font
     –   Border
     –   Patterns
     –   Protection




                                      13
                     General Formatting
   Auto Fit can alter the dimensions of rows and columns, making them as
    wide as necessary to show all values entered.

   Auto Format option offers several preset table designs that we can
    apply to a spreadsheet.




   Style is used to apply general formatting to the entire worksheet.
                                      14
                 Conditional Formatting
   Conditional Formatting only formats cells that meet a predetermined
    condition or a set of conditions.




                                    15
                                 Summary
   Excel objects are elements that you can manipulate in spreadsheet applications
    and VBA programming.

   Menus and toolbars create Excel’s user interface; they provide options for
    working in the spreadsheet environment.

   The two main toolbars on which you can find the most common operations are
    the Standard and Formatting toolbars.

   Customizing a toolbar allows you to edit the toolbars by modifying commands
    and display options.

   The format of a cell includes the following modifiable characteristics: Numbers,
    Alignment, Font, Border, Pattern, and Protection.

   Conditional Formatting formats cells only if they meet a specified condition.


                                          16
                         Additional Links
   (place links here)




                                17
Spreadsheet-Based Decision Support Systems
                  Chapter 3: Referencing and Names




Prof. Name                                           name@email.com
Position                                               (123) 456-7890
University Name
                           Overview
   3.1 Introduction
   3.2 Referencing Cells
   3.3 Names for Cells, Ranges, and Worksheets
   3.4 Summary




                                  19
                            Introduction
   Referencing a cell or range of cells

   Different types of referencing affect how cell addresses are copied

   Use R1C1 notation

   Avoid circular referencing

   Various ways to name cells

   Creating basic formulas and constants using cell names

   Use natural range names



                                      20
                     Referencing Cells
   Relative Referencing and Absolute Referencing

   R1C1 Notation

   Referencing Other Worksheets and Workbooks

   Circular Referencing




                                   21
Relative Referencing and Absolute Referencing
   There are four basic types of referencing
     –   relative
     –   absolute
     –   row absolute
     –   column absolute referencing

   Relative Referencing: Row and column value will change (B2).

   Absolute Referencing: Neither row nor column value will change
    ($B$2).

   Row Absolute: Row value does not change, but column value will
    change (B$2).

   Column Absolute: Column value does not change, but row value will
    change ($B2).


                                       22
                              Figure 3.1
   The SUM function is
    entered in column B16
    using relative
    referencing:
    =SUM(B4:B13)

   When the function is
    copied to cell C16, the
    function values shift
    relative to the new
    position




                                  23
                           Figure 3.2(a)
   In cell E11, the
    sum from cell B16
    is multiplied by the
    value in cell E4:
    =E4*B16

   Copying this
    formula to cell
    F11, does NOT
    yield E4*C16, but
    rather F4*C16




                                 24
                        Figure 3.2(b)
   Absolute
    referencing will
    keep E4 constant
    in both formulas:
    $E$4*B16 and
    $E$4*C16




                              25
                     Referencing (cont’d)
   Row absolute referencing places the $ in front of the row number
     – column letter $ row number
     – A$1


   Column absolute referencing places the $ in front of the column letter
     – $ column letter row number
     – $A2




                                     26
                       Figure 3.3(a)
   Numbers in row headings are same as column headings and we
    want one row number multiplied by one column number




                                 27
                          Figure 3.3(b)
   When the formula is copied, only the column of the row absolute
    value will change and only the row of the column absolute value will
    change




                                     28
                           R1C1 Notation
   R1C1 notation: Refers to a cell’s position relative to the origin cell
    where the formula is entered.

   The cell in which the formula is entered is considered to have position
    R[0]C[0].
     – A formula entered in A1 to refer to A2 would be:
          R[1]C[0] or R[1]C
     – A formula entered in B1 which refers to A3 would be:
          R[2]C[-1]


   To switch row and column titles to R1C1 notation:
     – choose Tools > Options from the menu
     – Click on the General tab
     – Check R1C1 reference style from the list of options




                                        29
                             Figure 3.4
   The first value of the table is
    referenced in cell R1C1 (or A1)




   The sum of the first two table
    values is calculated in cell
    R2C1 (or A2)




                                      30
                              Figure 3.5
   The formula from cell R2C1 is copied to the three cells below.




   Notice the actual formula is identical in each cell.




                                       31
                             Figure 3.6
   Excel offers a Formula View that shows cells by their formulas,
    instead of their calculated values.
     – Choose Tools > Options from the menu
     – Click on the View tab
     – Select Formulas from the Windows Options




                                     32
Referencing Other Worksheets and Workbooks
   Cells can also contain formulas which reference cells outside of the
    current worksheet or workbook.

   Worksheet in the same workbook: Sheet1!A1

   Workbook: [Data.xls]Sheet1!A1

   Workbook with spaces in the title: ‘[Collected Data.xls]Sheet1’!A1

   Workbook not currently open in Excel:
    ‘C:\My Documents\Project\[Collected Data.xls]Sheet1’!A1




                                     33
                     Figures 3.7 and 3.8
   Data is in one worksheet and calculations are made in another worksheet




                                       34
                     Circular Referencing
   A referencing loop in a spreadsheet creates a circular reference.
     – Example: cell A1 has the value “=B1,” cell B1 has the value “=C1,” and cell
       C1 has the value “=A1.”
     – This referencing loop causes an error in Excel.


   The first possible solution to this problem requires us to rearrange our
    references or to modify our formula.

   However, if neither can be done, Excel offers another tool to aid in
    sequential calculations.
     – Tools > Options > Calculations and select Iteration
     – Excel performs a specified number of iterations, or repetitions, of the
       calculations, to try to find a solution applicable to all equations.




                                         35

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:22
posted:9/4/2011
language:English
pages:35