Excel courses

EXCEL tutorial working with advanced functions


Télécharger EXCEL tutorial working with advanced functions

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


          Advanced Excel    

Formulas :      Functions           

Being      able     to work    with     Excel    Formulas         can       take     your experience      with     the    program          to         a          new level.    Formulas         are       the      basic    foundation       of Excel.      For       the       beginning        of         the       class, we        are       going    to         review             the    basics of         formulas.         Formulas         are       the       ways    you can       calculate          cells,       numbers,         etc.      in your     workbooks.                  

Formula           Operations:                 

+          

Addition             

=1+1      

-­?         

Subtraction        

=1-­?1    

*          

Multiplication   

=3*3      

/           

Division             

=6/2       

^          

Exponent           

=3^4      

%         

Percent              

=20%      (Divides           by 100)    

=SUM   

Prefix     

Variety    of         prefixes to         perform               a          function

Order    of         Operations    

1.          Parentheses    (Everything     in parentheses)   

=1+1(2+2)

2.         Percent          

=1+1/20%          

3.          Exponents      

=1+1*2^3          

4.          Division/Multiplication        

=1+1+2*3          

5.          Addition/Subtraction           

=1+1      

Relative vs.      Absolute                                                                        

Relative =A1        

Information          can      change    

Absolute           =$A$1    

Information          cannot             change    

Mixed            =$A1      

Only column            A          does     not             change            

Use       absolute          when               format in          a          cell       has       to         stay     the       you need    to        keep    $1.50    use       the       $           in the       formula.                      

same       when               copied.            For example,         when              

Functions            are the       built-­?in          algorithms       that      are       incorporated into     formulas    (usually            in         a          form    of prefixes)          to         perform           a          variety of    calculations.                

Function          Ranges            

Comma            ,            

Separates           more than     one      cell.      For       example,         use       A1,         B5, C4       

Colon    :          

Creates               a range    of         cells     from    top-­?left          to bottom-­?right.               For       example,         B1:C3              

Space                

Find        cells     that are       common          to         two      or         more    difference   cell       ranges.            B1:B3              

C4:C6     

Foundation     Functions     & Formulas     

Basic       Function         Argument      

A             formula prefix               is          essentially       a          function argument.       Being    able        to         use       the       correct   prefix               is          the       biggest             challenge for    many    Excel    users.               For       this       exercise, let’s      use       the       PRODUCT        prefix.    This      argument is          simple              multiplication.                         

1.  Enter 9          in         cell       B3,       15       in         cell       B5, and      25        in         cell       B7.                       

2.  Click any       empty              cell.                  

3. Click     Formulas         -­?>       Insert               Function

4.  In the       Insert               Function           dialogue          box, select               Math    &          Trig.

5.  In the       Select               a          function           menu, select               Product.                      

6.  Click on        the       first      RefEdit             control             button. This      is          the       spreadsheet    looking             button at         the       end      of         the       box.                  

7.  Click on       cell       B3.       You      will       see       B3        enter into      the       Number           1          box.                

8.  Repeat this       for        Numbers          2          &          3.                     

9.  Once done,               click     Ok.                   You      should get       the       results              in           the       selected   cell.      *The    prefix               PRODUCT        is          a function           that     multiplies       

data        from    multiple cells     throughout      a          spreadsheet     or         even workbooks.      We    will       visit      the       Insert Function           box       later.                

Nesting              Functions                    

Nesting               functions allows              you       to         insert               at         least two      functions    within              one      formula.          A huge    majority           of         nesting             functions are    used     for        conditional      and      IF          formulas.             

1.  Place a          set        of         random            numbers          in cells     C1:C5               and      D1:D5.                        

2.  For this       exercise,          we        are       going    to         find the       average           of         the       numbers         in cell       C1:C5               and      D1:D5.            

3.  In a          blank    cell,      type:    =AVERAGE(MAX(C1:C5),MAX(D1:D5))

4.  Press the       Enter    key       to         get       the       answer.                        

5.  The MAX     function           is          nested              within the       AVERAGE         formula           in           this       example.             

IF             Formulas        

IF             formulas          are       set        up to         provide            a          true      or         false statement           after    a          calculation       is performed.      With     IF          statements,     you       can         add multiple          arguments       to         produce           different results.                                         Exercise           1:

1.  In any       blank    cell,      type:    =IF(D1<=100,               “True”, “False”)          

2.  Press the       Enter    key       to         run       the       formula.         

3.  What we        are      doing    here     is          creating           an argument        that      if          the           number            in cell       D1        is          less      than     or        

equal      to         100, then     display             true.     Display             false     if the       number            in    D1        is          greater           than 100.                 Exercise           2:

1.  In any       blank    cell,      type:   

=IF(D1>100,"A",IF(D2>200,"B",IF(D3>300,"C",IF(D4>400,"D","Incomplete"))))

2.  This IF          statement        is          saying              if          the number            in         D1        is          greater           than 100      put       “A”       in         the       blank    cell.      If none    of         the       argument       is          correct,            put “Incomplete”               in         the       blank    cell.     

3.  In the       cell       with     the       formula,          type:    140      in Cell      D1.                               4.           Change            D1 to         14        and      D2        to         411.                 

5.            You      may notice              that      the       argument        will       try to         find      the       true        statement        before defaulting        to         the       Incomplete.                 

IF/AND    Formulas

Now        let’s      say you       want    to         add      an        additional        argument to         an        IF    statement.      

Exercise              1:                                 

1.  In any       blank    cell,      type:    =IF(AND(D1<100,D2<=300), "Yes","No")     

2.  What we        are       saying              here     is          if          D1 is          greater            than     100           and      D2 is          greater            than     or         equal    to         300, then     yes.      If          not,     no.                   



Exercise              2:                     

1.  In the       same    formula,          change             AND     to         OR.             

2.  Press the       Enter    key.                  

3.  What we        are       saying              here     is          that      at least     one      argument        has           to         be        true to         result               with     the       Yes       answer.

IF            Conditions                  

You         can       add      conditions        to your     spreadsheets               that      act       like       IF statements.        In         this       exercise,          let’s      add a          condition         to         cells     C1:D5.                1. Highlight          cells    C1:D5.                          

2.            Click     the       Conditional Formatting     button              in         the       Home tab       on           the       Ribbon.            3.         Select New     Rule.                

4.   The               New     Formatting     Rule     dialogue box       will       appear.            Pay       close    attention      to the      

options                in this       box.      You      can       set        up        a          rule based               on        a    cell       value,               duplicate values,             and      even     use       a          formula           to determine          which               cells     to         format!                        

5.   For    this       exercise,          click     on        Format all        cells     based              on        their    values.                     

6.   Select           2-­?Color         Scale    in         the Format             Style.                

7.   Minimum:    Choose             Number.          Value    50. Color:              

Red.                    

8.   Maximum:    Choose             Number.          Value    50, Color:              

Green.                

9.   Click             Ok.                   What    we are       doing    here     is          telling             

Excel       if          the values              in         each     selected           cell       is fewer               than     50    then     fill        it          red. If          it          is          50        or         above, color    the       cell       green.                 

10.  Change a          cell       with     a          number            lower    than 50        to        

300.        It          should change             green.                          

SUMIF/SUMIFS

The         SUMIF function           tells      Excel    to         add      only      cells that      meet    criteria    you       choose.            For       example, let’s      open    your     budget             spreadsheet.                

1.  Click in         a          blank    cell.      Type:    =SUMIF(C7:C10, "Jewel",B7:B10)          

2.  This will       give      us         the       amount            we        spent at         Jewel    for        the       month           of         February. In         other    words,              we        are       using    the SUMIF              function          to         tell       Excel    to add      all        the       values              in         Column B           where              Column            C          contains “Jewel.”          

The         SUMIFS allows              you       to         use       two      or         more “if”       conditions        to    the       SUM     function.                      

1.            In         a blank    cell,     type:    =SUMIFS(B3:B18, A3:A18,"<4/1/2014",C3:C18,              "Jewel")

In            this exercise,          we        are       saying              to         give us         the       sum      of         how        much    we        spent at         Jewel    before              4/1/2014.        The      B3:B18 is          the    SUM     that      will       be        added. The      A3:A18             is          the       date     criteria we    are       setting             telling              Excel    we want    the       dates    before              4/1/2014.        The    C3:C18 has       the       categories       or         criteria            we want    results              from.       

Common          Functions     & Formulas     

Arrays!   

Arrays     are       methods on        completing      a          function           on        a whole               set    of         values.             In         other words,              arrays              allow    you       to         run a          function              on        multiple           cells     at the       same    time.    Most    arrays              will       have the       {           and      }           in         the       formula.             

Simple    Array   

1.  Click in         cell       G10      in         the       array    worksheet.      

2.  Type =SUM(             *Note               you       do        not       type     {             

3.  Click on        cell       D3        and      drag     your     mouse down    to         D7.       Your     formula           should look      like       =SUM(D3:D7   

4.  Type *         

5.  Click on        cell       E3        and      drag     your     mouse down    to         E7.       Your     formula           should look      like      

=DUM(D3:D7*E3:E7      

6.  Close the       formula           with     the       )          

7.  Press Cntl+Shift+Enter          to         run       the       array. *Note,              you       do        not           press    the       Enter Key       to         run       the       array.               Your     formula should           now      look      like:      {=SUM(D3:D7*E3:E7)}

Array      with     Functions       

You         can       use arrays              to         work    with     functions.        Perhaps the       most    popular               function           with     an array    is          the       TRANSPOSE     function.          In         this exercise,          let’s      transpose         the       Function Array    data     in         the       Array    spreadsheet.                  

1.  Highlight cells     B24:G26.          When               working           with TRANSPOSE,    make    sure     you           highlight          enough cells     to         fit         the       data     you       want    to flip.                 

2.  In cell       B24,     type:    =TRANSPOSE(             

3.  Click on        cell       B16      or         the       left       corner of         the       table.                

4.  Drag your     mouse              down    to         D21      to         highlight the       entire               table.               

5.  Type the       )           to         close    the       formula.         

6.  Press   Cntl+Shift+Enter.                     

The         formula should              look      like:{    =TRANSPOSE(B16:D21)}       

Array      with     Functions        Part     2

Now        what    if you       want    to         change             data     in         your array?              For       example,            try       changing the       15        to         25.       You      will       be prompted:       You         cannot            change            part     of an        array.

1.  Press the       ESC      key       to         release            the cell       for        editing.                        

2.  Highlight the       entire               table    that      was      created by         the       array.                          

3.  Press the       ESC      key.      This      will       release            the entire               table    so         you           can       edit      or even     delete              the       table.                

VLOOKUP          

VLOOKUP           is          vertical lookup.             VLOOKUP        is          a          useful function    when    you       need    to         perform           calculations that      reference        a          table    with     a    range    of values.             VLOOKUP        searches          down    the       first column            of         a    table    to         find      a value    you       want.    Then    it          moves              across a          specified             number            of         columns and      returns             the       value    in         the       target cell.      This      feature            is          frequently        used when    cross    referencing     incomes    with     income            tax ranges             or         cross    referencing     sales    revenues with     commission        ranges.                        *Note: when    entering           a          VLOOKUP        formula    remember you       will       need:   

1.  A specified          value    in         the       leftmost           column

2.  A range    or         area     that      is          named            

3.  Column offset               from    the       leftmost           column           

Select      the       cell of         the       first     commission     rate,     select the       Formulas         ribbon     and      then     Insert Function.                                

In            the       Table_array field,    highlight          A3        through            C7        in the      table.    You    can       also      just       type     A3:C7 in         the      

Important:                      The “Table_array”             range    must   then     be        converted to         absolute             values              by         entering a          $          before              each     column            letter and      each     row      number.           However,         lookup functions          must    use       Absolute            functions. In         the       Table_array     field,    change             the data     to:        $A$3:$C$7         In         the       Col_index_num field,    enter    the       relative            column            number of         the       Commission     data.                This      table    has three    columns           and    the       Commission     data     is in         the       third     column,           so         enter    3.             

Excel       then     cross references       the       salesperson’s               revenue           with the       revenue/commission     table    and     

This         will       calculate the       Vlookup           function           for        Salesperson:   Cynthia Roberts.              You      may     also      use       the      



VLOOKUP           with     OFFSET (part    2)        

Now        that      we walked             through            the       Function           Argument from    the       dialogue             box,      let’s      now      type out       a          VLOOKUP.       In         page    two      of         the Revenue          spreadsheet,    let        find      the       price    for   the       Apple               iPad.                Click       in cell       H13.     The      formula           we        will       type here     is:                     

=VLOOKUP("Apple iPad",OFFSET(Vendor,0,1),3,FALSE)            

1.  The =VLOOKUP(    starts    off        the       formula.                      

2.  The “Apple             iPad”                is          what    we are       looking             for        within              the table.                

3.  OFFSET(Vendor,0,1) is          a          bit        difficult            to         explain. The      OFFSET           is          telling              Excel    that we        are       searching         for        the      Apple iPad    that      is          not       in         column            A or         to         the       first      far        left       column. I’ve       already            named             the       table    “Vendor”. (To       name    a          table:              just       go        to the       Design             Tab      -­?>       Under              Table Name,           type     Vendor             and      press    the       Enter key.)     The      zero     is          saying              to           not start     the       search              from    the       first     

column    but       the       1 is          saying              to         start     on        the       second column.    Here    is          helpful             website            to explain            

OFFSET:              ­?offset-­?function-­?explained

4.  The 3          will       pull      the       information     to place    in         the      selected           cell       from    the third     column            relative            to         the       ITEM column            where              VLOOKUP       is          performing its         search.            

5.  The last       part      of         the       formula           is          either True     or         False.    True     will           find      an approximate    match              in         the       lookup. False    will       find      an           exact    match              in the       VLOOKUP.       (Check             your     spelling!)                                

HLOOKUP          

HLOOKUP           is horizontal       lookup.             This      function           will work    exactly             the    same    as         VLOOKUP        except it          will       search              across              the       first row         in         the       cell       range    we        select. You      can       also      use       offset               to    calculate data     you       need.                For       this       exercise, let’s      stay      in         the       Revenue             spreadsheet, page    2          that      has       the      

Vendor    table.                

What       we        want    to accomplish      is          getting             the       total     amount in         cell    I15.      The      formula           we        will use       is:                     

=HLOOKUP(G15,E1:H9,3)*H15             

1.  =HLOOKUP( starts    off        the       formula.                      

2.  G15 is          the       criteria            which               we        want to         search              by.        In         other              words, the       HLOOKUP        will       attempt           to         find      a cell       that     matches           what    is          G15.                 

3.  E1:H9 just       tells      HLOOKUP        where              to         look.     In this       case,    we        are           going    to         search the       entire               table.                4.         The      3 is          what               offsets              HLOOKUP        from    the top       row.     In         other    words,              Excel will       count               three    rows     down    and      use that      information.    In         our           table,    the       three is          pointing           to         cell       H3.                   

5.            *H15    is what    we        are       going    to         multiple           our number            to         get    the       end      result.                         

Money             Functions     &         Formulas     

When      working           with financial          functions,         you       will       encounter        a few      functions            that      are       common          with these    types    of         formulas.                     

PV-­?Present       Value-­? the       value    of         an        object              or investment      at         time        or         purchase.                     

FV-­?Future         Value-­? the       value    or         projected         value    of         an object              or    investment      in         the       future.             

Rate-­?The         rate      is the       interest            rate      for        a          loan     or investment.        

PMT-­?Payment-­?The    amount of         money             going    towards           the       object or    investment.                 NPER-­?Number           of Payments-­?The           total     number            of         payments quarterly,        annually,          etc.      for        the       loan, object,             and      investment.                    

Calculate            the       Interest You      Pay      on        a          Loan   

You         can       figure out       what    you       are       paying              towards interest            on    a          loan.    For       this       exercise, let’s      open    the       Loan     spreadsheet     in         the Budget    workbook.        In         this       example,         we        are going    to         figure               out       what       our interest            is          on        a          mortgage        loan for        the       10th      month    of         the       loan.             The      function           we        are       going    to use       is          IPMT.                

=IPMT(B4/12,10,B5,B3)            

1.  The B4/12               tells      Excel    that      the       APR      is         4.30% with     monthly           payments.                  

2.  The 10        can       be        changed.          This      is          telling Excel    which               payment           number/month            you are       seeking            the       interest            amount for.       With    most    loans,               the       interest rate      is          higher              in         the       beginning and      lower    towards           the       end      of         the loan.    Excel    is          taking              this           into account.                       

3.  The B5        and      B3        take     the       principal          and number            of         payments         into     account            with calculating       how      much    you       paid     in         interest.             

Calculate            Payment         Towards Principal        

Now        let’s      calculate the       payment          towards           the       principal          for the       10th         month              of         the mortgage.       The      function           we        will       use       is: =PPMT   

The         formula           we will       use       is:                     

=PPMT(B4/12,10,B5,B3)          

1.  The B4/12               tells      Excel    that      the       APR      is 4.30%              with     monthly           payments.                  

2.  The 10        can       be        changed.          This      is          telling Excel    which               payment           number/month            you are       seeking            the       principal          payment amount.           With     most    loans,               the       interest rate      is          higher              in         the       beginning and      lower    towards           the       end      of         the loan.    Excel    is          taking           this       into      account.             

3.  The B5        and      B3        take     the       total     principal and      number            of         payments           into      account with     calculating       how      much    you       paid     in principal.                    

Double    Check:             Calculate the       Total    Monthly          Payment                     

Let’s        double check               our       work.    If          we        add      up the       totals    for        the    Interest            Payment          and Principal          Payment,         it          should              total the       amount               for        the       Monthly           Payment. That     is          the       number            we        will    be paying              out       every    month.             The      function we        will       use       for        this    formula           is: PMT.                

=PMT(4.3%/12,360,250000)    

Net         Present           Value vs.        Present           Value                           

The         Net       Present Value    (NPV)               is          the       difference       between the       present               value    of         cash     inflows and      outflows           for        an        investment      for    a specific            amount            of         time.    In         other words,              what    would               the    value    of         a mutual             fund,    or         bond    be        worth after    maturity?         The    Present            Value    (PV)      formula will       return              the       current            value    of a    particular        investment.     So         the       difference between          these    two      functions          is    an        investment’s timeframe.                  

Net          Present Value   

In            the       Loan spreadsheet,    the       NPV      table    is          set        up with     an        ETF       with        a          3%        rate      with a          $30,000           initial    investment.     Change            your data        type     to         “Accounting”               in         the Home               Tab      in         the       Ribbon.    In         cell:



Year        1:         -­?5000

Year        2:         7000   

Year        3:         2000   

Cell         A18:     the formula           is          as         follows:            =NPV(3%, B19,     C19,     D19)+E19

Present               Value   

The         Present Value    (PV)      will       return              the       present value    on        an        investment         or         loan.    In cell       B12,     the       formula           for        the       value of         the    mortgage        is:                     

=PV(B4/12,B5,B8)                      Future Value              

In            most    cases, when    calculating       the       present            value,               you may     want       the       future              value.               The Future              Value    (FV)      returns             the      future value    of         an        investment.     This      is          highly used     for        IRAs     values    at         time     of retirement.                  

Age:        20       

Retirement         Age:     65

Annual    Payments         to IRA:      $2000              with     rate      of         average rate      of         return     3%       

Formula: =FV(3%,45,-­?2000)    

What       if          you roll       over     the       IRA       due      to         a          new job?      That     means    the       new      IRA       will       have     a cash     value    when    you       begin    the       new      IRA       with your     new      job.      Let’s     assume            you       roll over     $10500.           The      formula    will       change:                        

=FV(3%,45,-­?2000,-­?10500,1)             

The         1          at the       end      of         the       formula           is          the “Type”             field.    You    can       only      put       0          or 1          into      this       field.    0          means              that your     first         annual             deposit             was      at the       end      of         the       period.             The      1 indicates          that      you       made    the       deposit             at the       beginning        of         the       period.    If          you leave    this       field     blank,               Excel    will       assume the       value    is    0.                     

Depreciation    

There      are       four     fields to         know    how      to         operate            when    looking a          deprecation        in         Excel.                           

Life:        the       number of         periods            of         time     (years,             months, etc.)     which      the       object              loses    its         value.

Salvage:              the value    of         the       object              after    it          stops depreciating    in         value.     In         other    words, the       lowest              it          can       fall       in value.                  

Cost:       how      much    did the       object              cost      when    it          was      first purchased       

Per/Period:         The period              of         time     or         timeframe       in which               the       object     depreciates.    For       example, a          car       will       depreciate       10        years    then stop.       The      Per/Period       here     is          10        years.             

There      are       four functions          in         Excel    that      are       used     to calculate          depreciation.                  

Straight               Line Depreciation    (SLN)    –          estimate          the       salvage of         the       object     at        the       end      of         the depreciation    term    to         determine       use       to         produce revenues.                     

Sum        of         Years    by Digits    Depreciation    (SYD)    –          an        accelerated depreciation    which      depreciation    is          a         fractional part      of         a          sum      of         all        years    in the       Per/Period.                  

Declining            Balance Depreciation    (DB)     –          the       original            asset    or object    goes     through            an        accelerated     depreciation during              the       first      few      years    of    the Per/Period.                  

Double    Declining Balance           (DDB)               –          depreciation    counts twice    the       object     or         asset’s              book     value each     year     compared        to         straight            line      depreciation.             

Straight               Line (SLN):                           

Click        in cell       D9:       =D8-­?SLN($D$3,$D$5,$D$4)              Click     in         cell       E10:     =E8-­?SYD($D$3,$D$5,$D$4,$C$9) Click     in         cell       F10:      =F8-­?DB($D$3,$D$5,$D$4,$C$9)                 Click     on        cell G10:     =G8-­?DDB($D$3,$D$5,$D$4,$C$9)

Sumof     Years    (SYD):

Declining            Balance Depreciation    (DB):   

Double    Declining Balance           (DDB):                          

Date      Formulas      &         Functions                    

Planning            Ahead            

The         most    commonly used     date     function           is          the       =TODAY() function.          In    a          blank    cell       in         the Date     spreadsheet,    type:                

=TODAY()

You         should get       today’s             date     after    pressing           the Enter    key.                  

However,            what    if you       wanted            to         enter    the       date     seven days     from       today?              The      formula           you will       use       is:                     

=TODAY()+7      

Note*     The      TODAY function           is          not       static.               Excel    will update             the    function           with     the       current date     that      you       open    the       spreadsheet.    A static      function           is          the       =NOW()           function.             Go        ahead              and      try    =NOW()          

The         DATE    Function The      =DATE              function           will       calculate the       date     either     by         serial    or         by         a formula.          *Serial:            Excel    starts    its         dates from       January            1,         1900.                            

In            cell       E2, type:                

=DATE(C2,A2,B2)          

To           sort      and filter    dates,               you       have     to         use       the serial    numbers          to    calculate          the       filters. In         cell       E2,       press    F2.        This      will reformat          the    cell       into      edit      mode.              Then press,               F9         to         get       the       value. Press       the       Enter    key       in         the       formula bar       to         activate           the       serial    date.                   

Date       Serials            

Date        serials are       the      number            of         days     since    1/1/1900. This      is          needed               to         sort      and      use other    date     functions.                     

Click        into      cell A17      and      select               the       General           option from    the       “Date”    drop     down    in         the       Home Tab.     You      will       see       the       date     serial    for    this particular        cell.      Click     back     on        the       General drop     down    and      select    Short    Date.    This      will return              the       date     back     to         a          readable format,    but       it          will       keep     the       serial underneath      the       cell.                  

In            cell       B17, let’s      put       in         the       day       of         the       month. Type     the       formula:                         

=DAY(A17)        

To           get       the text      date,    in         cell       C17,     type:                

=TEXT(A17,”dddd”) [The     four      d’s        represent         Excel    format. dd=10              ddd=Tue    dddd=Tuesday]           

To           see       months as         a          number            between          1          and 12,       in         cell    D17,     type:                

=MONTH(A17)               =TEXT(A17,”mmmm”)

To           get       months spelled             out,      in         cell       E17,     type:   

For          year,    type     in F17:      =YEAR(A17)    

First        day       of the       month,             type     in         G17:     =EOMONTH(A17,0) [The     month    is          represented     by         the       0, try        changing          it          to         1]         (End     of month     formula:          =DATE(YEAR(A17),MONTH(A17)+1,0)          

To           determine       a vest      date,    let’s      assume            A17      is          the start     date     and    it          takes    5          years    to vest.     The      formula           is:         =EDATE(A17,5*12) This      will    get       the       serial    number.           Home Tab,     change             to         Short    Date.                    



50