 .      Excel
 


,       .    ,     , ,     Excel.  ,     . ,   ,     -,    .    ,               ,     ,  ,     .    , -   ,         .





 

 .     Excel



 . 

 . 

  . 

 . 

  . 

  . 



John Wiley & Sons, Inc., Indianapolis, Indiana, 2014

All Rights Reserved. This translation published under license with theoriginal publisher John Wiley & Sons, Inc.

  , , .   , 2016

 . Jason Travis / Courtesy ofJohn W. Foreman



 .     .                 ,      ,        .          5.  (. 49 ),        6 (. 146  ).


* * *







Editors choice   

 Big Data   ,    .      , Big Data ,     ,    .



   ,  ,  Big Data ,   ,  Excel.    ,           ,      .



 , ,   ,  .



 ,     


  .

,     !  ,    ()   













  ?


 - ,       -  ,  堖 ,   .     ,     ,    , ,        .

        ,       .   ,     .   -,      ------,   .

  .

  ! , ,  ,  ,     ,    .     .      , ,     .   , ,    .

  ,     .     ,      . Ƞ   ,  ,   ,         .

  蠖       . ,        컠 蠖    ,       .




   


       , -;  ; -;  ; ,    (     , ).  ,       ,     .

       .                  ,  HTTP     .        ,         .

, - ,         ,       (data mining),   ,    .    ,      -,       ,        .

           . ,    :



         ,  .


ߠ     .       ,  . ?         . ߠ  ,          ;      , ,   .

  ,       ,  ,  ,      ,  ,    .

      .     5.  ,      .  蠖   蠖     .

   ,      ,     .           ꠖ    .       , ,       .    .

   ,    -   .




,   ?


       , 젫 . Π  ?

  ,   .          ,    NoSQL-, ,   .

               ( ,  ),  ,   .

       ,  MongoDB Hbase.     ,  Mahout, Numpy,  R- ..    .

ߠ  .   ,  .  , ,  .  ,        ,   ,    .

.         ,          , ,      . ,         젖  .              .    , , ,   ,    ,      . , ,    .  ,       /.

    ,   .    . ,     , ,      ,  ,     ,    ..




?


  ,    .    ,   ,  ࠖ     .       . ߠ    , ,   ,  Coca-Cola,   Intercontinental Royal Carribbean.      :       .

ߠ ,   ,      . ߠ ,     [1 -     (),    ,   - .     . . .],        . ,     ,        PowerPoint,        -, -砖 S.

  蠖  ,       . ߠ ,  ,       . ߠ ,       ,  ,  ,     .

  : ?




?


,    ,   . ߠ , ,    ,   .

  (頖 , !),    ,     . , :

      -  ,    ,  ,     ;

,  ,  ,       ,    ;

  -,     ,       ;

-,        , ,    ;

-,   -      ,     .     ࠖ    .



 ,  ,         ,       .   蠖   (,   )       ,  ,       . , ,  .       .




 頖   



 . ߠ     (,  , 10)  . ?

         Git,     Emacs  Vi.

    Windows Microsoft Office,               MATLAB         ,  .

  ,  ,       ,  ? ! -          .

  ,  ,  ,    ?  !

        .

,,  -,   ,     .    蠖    R,   .   ,         .




   !


     .

    .       (,  ,  ).    .        ,  ,     .   .

, ,  : ߠ    . ߠ     ,    .

     ! ,      [2 -  . . .]  -  Excel,     , ,      ,      .




 Excel LibreOffice


,    ,   Excel.

   (www.wiley.com/go/datasmart (http://www.wiley.com/go/datasmart))       ,      .     ,     ,  ,    .

  Excel  2007, 2010, 2011 MacOS 2013.         .

Ӡ    Excel          .  -     ,    ,     LibreOffice (www.libreoffice.org (http://www.libreoffice.org/)).




 Google Drive?

-   ,   ,   ,  Google Drive  ,   ,  . ,   ʠ,  .

Google Drive    , ,  ,  .      ,     ,     !

,   , 


LibreOffice   ,     Excel. ߠ ,     , Excel.        !




 


      ,      .






  ,      Google Drive,   ,  .





!

   ,   ,       .







   , ,    ,    .


        Excel  :

=CONCATENATE(THIS ISAFORMULA, INEXCEL!)/

=( ,  EXCEL!)

       .  , -    :

http://www.john-foreman.com.




, 


          Excel,       . ʠ        ,    :

    ;

   ,     ,     ;

  -       ;

     ,   ,     ;

       ,  - ,    ;

           ;

  R        .



  -    堖  !  堖  ! ߠ     .

,   !




1.,     ,  



     ,        .      ,   .     Excel     Excel      .

      Excel,          ,    . ,         .      .  젖  .      .




   Excel

   ,    Excel 2007, 2009, 2011 MacOS LibreOffice. ʠ,    Excel  Microsoft     .

,     2011      .    2010 2013 ,    2007 2011,     2007.

      Excel 2011.      ,   -,         . ߠ     .  -  ,   Excel Google   .

 , , ,   :      .

  LibreOffice.        ,  ,  ,    . Ƞ      LibreOffice,   . Excel    .





  





:

  Excel,    Concessions.xlsx,     www.wiley.com/go/datasmart


 ,    .   ,           . (,   !).

Ӡ     ,    .11.








.11   :  ,       ,    .




    


   蠖    ,  (   )  .    ,          ,   .     蠫     Windows (  MacOS2011,  .12).








     ,    ,       Ctrl+? (Command+? Mac).       .     ࠖ 200.  ,  Ctrl/Command         ,    .

        ,   ( )   :

=AVERAGE(C2:C200)/

=(2:200)

   $2,83,    , , .     :    , 200  Shift+Ctrl+?,     ,        (.13).  Windows     ,    , ,  ,   .  MacOS,    ,       ,  .









   


     , ,   -    ,    .   2        ,    :

=C2*D2

     $2.     . Excel           .   ѠD    ,   . ,   ,    ,       , Excel    ,   .14.     ,        .   ,     .

  ,  ,    ,     ? , .   - 젖     $.

ʠ,     2  :

=C$2?D$2








          .    2- .

   , Ѡ D, D  ..     ,  $     .   ,   .




 


Excel       .     ,     . ,    .         .

          .      .        .  2   ,  .15.       ,  ,  ,  ,  ..








 . ,     ,      ,      .

     ,     .

      .    ( MacOS)     (.16).    .      堖  .     ,        .








   ,       .




 


,   ,     ,   .14.      RAND()/(),   ,       ,    .            .

   ,     ,  ,        (     Windows   MacOS).         (.17). ,          .     .









 


,   ,      .   ,      ,   .       Excel.   ( MacOS)  ,     ,   ,    .






        ,    .    ! Ƞ  3D-,    Excel.  ,         !   ,   ,   3D-  ,      .


    Calories        .  .     ,    . ,       ,      .          ,   ,  .

       ,      .             (    ).    ,  .18.









   


         .  Windows  , ,  Ctrl+F    (Ctrl+H )   ,     .  MacOS        (    ,  Cmd+F    ).

   ,     Calories.      ,   (.19),        .









   


  ,       Excel (SUM, MAX, MIN, PERCENTILE / , , ,  ..),     .     .        ,     ,        .         , ,     .

ߠ    Calories.

      . ,  ?  MATCH/    .     18Match/.     18,  ,     Hamburger.    ,    ,   ,   0,      :

=MATCH("Hamburger", A2:A15,0) /

=("Hamburger", A2:A15,0)

 6, ꠫Hamburger    (.1-10).








 ࠖ INDEX / .   19Index/.

       . ,      1:15    3 , 2 ,     :

=INDEX(A1:B15,3,2) /

=(A1:B15,3,2)

  ,  0,  (.1-10).

 ,     ,  OFFSET/.   20 Offset/   20.

Ѡ     ,  ,  ,    ( ,  INDEX/   ,     0). ,    OFFSET/      1    3  ,   3  0 :

=OFFSET(A1,3,0) /

=(A1,3,0)

     ࠖ Chocolate Bar (.1-10).

 ,      , SMALL/ (  ꠖ LARGE/,    ).        , ,   ,     .   21 Small/, 21   ,     3:

=SMALL(B2:B15,3)/

=(B2:B15,3)

   150,      0 ( ) 120 (),  .1-10.

,,    ,   MATCH/,  .  VLOOKUP/ (   HLOOKUP/).    ,  .




 VLOOKUP/  


     .         ,         !. , Calories!2     ,  ,      .

,         .   -         ,  . , ,      VLOOKUP/.

  F   Calories / .  F2        蠖 .   ,      2   Calories!$A$1:$B$15  ,    .      :

=VLOOKUP(A2,Calories!$A$1:$B$15,2,FALSE) /

=(A2,Calories!$A$1:$B$15,2,)

FALSE/   ,     Beer.     Beer  ,  .

    , 200     Calories.  $    ,           . -! Ӡ     ,  .1-11.









 


     ,   , ,       ,   .      1:F200.   1  Shift+Ctrl+?,  ?.    堖    ,    ,    F,    6 .

,      ,     .   , .1-12.








  ,    ,    1,      (      ), .1-13.








            . ,    ,        ,         , .1-14.








    . ʠ,     ,      Profit/ (D1)    (), .1-15.








   ,   ,        ,      ,   . , ,       ,     ,    .

Excel       , ,   .  ,    (  A: F)       .    .  MacOS           .

  ,  .1-16,     ,     .

Ƞ      ,   . ,        .  ,   .          젖 , ,      .          ,  .









  


,            .

   aggregate 蠫group by,     SQL.  堖 .   . Ƞ     .

     ,   .   堖    1:F20.   (  MacOS)       .  ,   Excel      , , ,  ,      .

         (MacOS  ).          ,     .      ,   ,         .            .

 Windows      ,   MacOS                .    MacOS,          .

,        .    Item/     ,  Price/໠  . ,    ,   .

          . , .1-17  20  .








      .    Windows,            .  MacOS     i.          .

,       ?       .    ,  .1-18. ,        .








    -   ,       ,   ,    . , ,  .

       ,          .       (5:18 MacOS)            (.1-19).








    ,     . ʠ,          .




  


       . ,  '    ,          (,      ,    ).   ,       .

 ,       ?    ,            .

     ,   , ,  .     SUMPRODUCT/.    1         .  2  SUMPRODUCT/    :

=SUMPRODUCT(B2:B15,'Fee Shedule'!B2:O2) /

=(2:15,''!2:2)

,  !     #Value/#.  ?

 ,           SUMPRODUCT/,    ,     ,  .

ʠ, Excel      .  TRANSPOSE/.    :

=SUMPRODUCT(B2:B15,TRANSPOSE('FeeSchedule'!B2:O2)) /

=(B2:B15,(''!B2:O2))

!   .

,   , :   Excel      .   TRANSPOSE/     .       ,   TRANSPOSE/ ⠫ .       ,  .

  SUMPRODUCT/ - ,   . ,     Enter     Ctrl+Shift+Enter.  MacOS  堖 Command+Return.

!  .1-20,    57,60.    50     ?









    


 ,     ,     .  蠖  ,      (  ,     ..).         蠫.

     ,     ,            ,      . ,  ,     .      .     4,  ,        2 3.   ,        .

 Excel         .

 Windows    ,    (Excel 2007     Windows) ?  ? .     ,   .

 MacOS     ,    ,  Solver.xlam.

          Excel.

!   ,   . ,    2400 .        ,    ? ,   䠖  10 - 240 ,   ,   10 ?

   !

    Calories/,   蠖   ,   .     Excel,       ,    (),     蠫.    , .1-21.








     ,     ,    .      ,    .     Ѡ ? (  )       .

Excel       ,         .     .

  16     :

=SUM(C2:C15) /

=(C2:C15)

        ( ,  ,  2400),   SUMPRODUCT/:

=SUMPRODUCT(B2:B15,C2:C15) /

=(B2:B15,C2:C15)

   ,  .1-22.

    ,    ,      .











   Excel 2011,  .1-23,   , Excel 2010 2013.  Excel 2007   ,        .        .      .


 ,      ,  .1-23,  ,   ( ),   ,     , - .








      16. ,    ,   2:15.   ,  17   頖   2400.    ,     젖    ,            Excel 2007    . ,   1,7  . (     4).

     ,     17  2400,  .1-24.








      2:15,  .1-25.








 .

 Excel 2010, 2011 2013 ,    ࠫ    -.     ,  .   ,        2:15 (,      ..).

      (   ,  ),    -  ,  Excel.     4.

 Excel 2007    ,  ࠫ      .     ,  .1-26.








!     . Excel    .  .1-27,   5. Excel   -  5 ,     .




OpenSolver:   ,  


         Excel.           .

,        Excel 2007 Excel 2011 MacOS, Excel 2010 2013       ,       (  ,      ).








ʠ,      OpenSolver,   Excel Windows,    . Ѡ       ,  OpenSolver    -   ,    .








 OpenSolver   http://opensolver.org (http://opensolver.org/)  .     ,     ,         opensolver.xlam,        OpenSolver.    .  .1-28,  OpenSolver Excel 2013   ,  ,   5  .







   Excel   ,    ,   , VLOOKUP/    ,   ,    ,    ,     .

   (,    ) . ߠ    ,     ,    / :

1.    (, ,  ,  ,    ).

2.  Excel.

     (  ),     ,     .

:    ,    .  ,     . Ƞ     ,       .




2. , I:   k-    



ߠ      MailChimp.com.        .  ,  -     ,    .

?        ,    ,  . ,   (  -,  ,  Facebook  Pinterest)    ,     ,   , -,     .. 堖  .   .       . .

      (,  ..)   ,     ?      ,     ,     -  .        ,    .

              .     ࠖ      ,            ,  ..

 砖        .   蠖 ,   ,     ࠖ        .      ,    ,  .

       ,           ,   .       蠖      ,       .

    蠖  :    ,    . ʠ,     Flickr         -   . ,  ,    ,         .




   ?

   , ,   ,    .  .   ,     ,  ,        .           ,     堖  ,   .

   ,    ,  , ,      .   ,      ,    蠫 򻠖      ,      ,  .

    :       ,   . - - ,   .


          k-,     50-          ()    .

 k-     . ,  ,    ࠖ  . Ӡ    , ,      .    k-,   ,  , 蠖   (  ,       ).     .

,   ,  .




  ,   


   k-       k  ( k    ).       ,   : ,   ! ,     , !    (   )     k-.

   .         ,      .

  ࠖ    ,       ,   ,  .21. ߠ  Photoshop ,     .

  ,          (     , , Spotify):

Styx: Come Sail Away

Everything But theGirl: Missing

Ace ofBase: All that She Wants

Soft Cell: Tainted Love

Montell Jordan: This isHow We Do It

Eiffel 65: Blue








  k-   ,     .      (       k).          ,  .22,   3  ,     .




  .


   .

   ,     (https://www.litres.ru/dzhon-forman/mnogo-cifr-analiz-bolshih-dannyh-pri-pomoschi-excel/)  .

      Visa, MasterCard, Maestro,    ,   ,     ,  PayPal, WebMoney, ., QIWI ,       .



notes








1


    (),    ,   - .     . . .




2


 . . .


