Excel

5月 162011
 
In our last entry, we described reading Excel files. In this entry, we do the opposite: write native Excel files.

R

In R, the WriteXLS package provides this functionality. It uses perl to do the heavy lifting, and the main complication is to install this with the needed Perl modules. There are detailed instructions here. On Nick's mac (which came with perl already installed), he needed to run the additional command:

cpan -i Text::CSV_XS

to add the needed functionality. Once this was done, he ran the commands:

library(WriteXLS)
testPerl()

which yielded the encouraging output:

Perl found.
All required Perl modules were found.

To generate the spreadsheet, the WriteXLS() function was called with a character vector or list containing the data frames to export, along with the name of the spreadsheet to create. Here we want to write out the HELP data frame.

HELP = read.csv("http://www.math.smith.edu/r/data/help.csv")
WriteXLS("HELP", "newhelp.xls")

It might be necessary to write multiple sheets to a single file. Here, as an example, we make a new table with just female subjects, then create an Excel file with the whole data and just the women.

helpfemale = subset(HELP, female==1)
WriteXLS(c("HELP", "helpfemale"), "newhelp.xls")


SAS

Several options exist in SAS for writing Excel files. The simplest may be through using the libname statement, but this appears to be platform dependent and we've had trouble using it. We've had more success with proc export, shown below.

data help;
set "c:\book\help.sas7bdat";
run;

proc export data = help outfile = "c:\book\newhelp.xls"
dbms=excel;
run;

proc export data = help (where=(female=1))
outfile = "c:\book\newhelp.xls" dbms = excel;
sheet="Females only";
run;

The second proc export statement adds a new sheet to the existing Excel file, with the designated name. This sheet contains only women due to the data set option where (section 1.5.1).
5月 122011
 
Microsoft Excel is an awkward tool for data analysis. However, it is a reasonable environment for recording and transfering data. In our consulting practice, people frequently send us data in .xls (from Excel 97-2003) or .xlsx (from Excel 2007 or 2010) formatted files.

In order to use the data in statistical software, you have to get it out of Excel. While Excel does provide some tools for exporting data, these are not easily replicable, since they rely on menu choices through the GUI. A better approach is to read the file directly from within the statistical software.

An additional complication is that a single file may contain several sheets, each of which may have unique columns and rows. While importing from Excel into SAS is shown in section 1.1.5, we don't discuss reading from specific sheets or show how to read an Excel file in R.

SAS

In SAS, it's possible to use the "Import Data" wizard to gain access via the GUI (File; Import Data; etc.) but this is no better than using the GUI in Excel. However, all the wizard does is compose a proc import to read from the file. This turns out to be important, because the documentation for using proc import for Excel files is hard to find.

The documentation is buried in the on-line help at SAS Products; SAS/ACCESS; SAS/ACCESS 9.2 for PC Files: Reference; File Format-Specific Reference; Microsoft Excel Workbook Files. This is not the same material found through SAS Products; SAS Procedures; Proc Import. The code below was derived by running the wizard and using its option to save the resulting commands. The help.xlsx file can be downloaded from the book website; SAS currently cannot read an Excel file in directly from a URL-- you must download the file manually and read it locally.

PROC IMPORT OUT= WORK.test
DATAFILE= "C:\temp\help.xlsx"
DBMS=EXCEL REPLACE;
RANGE="help.csv";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

The range option can be used to specify the desired sheet. This means that you must know the name of the sheet you want to import and type it in. The trailing "$" can be used to read in just a specific range of cells, but with no additional information it implies the full sheet. Since there is only one sheet in the helpdata.xlsx file, it's not required here.

R

The foreign package reads data from many file types. However, the .xls and .xlsx formats are not among them, as far as we know. Fortunately, the files can be read with the gdata package, using the read.xls() function. This will read files in either the .xls or the .xlsx format.

library(gdata)
ffdata = read.xls("http://www.math.smith.edu/r/data/help.xlsx",
sheet=1)

In this implementation, you specify the sheet by number, rather than name. This may be less precise than using the full name, but it does spare some tedious typing.
5月 122011
 
Microsoft Excel is an awkward tool for data analysis. However, it is a reasonable environment for recording and transfering data. In our consulting practice, people frequently send us data in .xls (from Excel 97-2003) or .xlsx (from Excel 2007 or 2010) formatted files.

In order to use the data in statistical software, you have to get it out of Excel. While Excel does provide some tools for exporting data, these are not easily replicable, since they rely on menu choices through the GUI. A better approach is to read the file directly from within the statistical software.

An additional complication is that a single file may contain several sheets, each of which may have unique columns and rows. While importing from Excel into SAS is shown in section 1.1.5, we don't discuss reading from specific sheets or show how to read an Excel file in R.

SAS

In SAS, it's possible to use the "Import Data" wizard to gain access via the GUI (File; Import Data; etc.) but this is no better than using the GUI in Excel. However, all the wizard does is compose a proc import to read from the file. This turns out to be important, because the documentation for using proc import for Excel files is hard to find.

The documentation is buried in the on-line help at SAS Products; SAS/ACCESS; SAS/ACCESS 9.2 for PC Files: Reference; File Format-Specific Reference; Microsoft Excel Workbook Files. This is not the same material found through SAS Products; SAS Procedures; Proc Import. The code below was derived by running the wizard and using its option to save the resulting commands. The help.xlsx file can be downloaded from the book website; SAS currently cannot read an Excel file in directly from a URL-- you must download the file manually and read it locally.

PROC IMPORT OUT= WORK.test
DATAFILE= "C:\temp\help.xlsx"
DBMS=EXCEL REPLACE;
RANGE="help.csv";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

The range option can be used to specify the desired sheet. This means that you must know the name of the sheet you want to import and type it in. The trailing "$" can be used to read in just a specific range of cells, but with no additional information it implies the full sheet. Since there is only one sheet in the helpdata.xlsx file, it's not required here.

R

The foreign package reads data from many file types. However, the .xls and .xlsx formats are not among them, as far as we know. Fortunately, the files can be read with the gdata package, using the read.xls() function. This will read files in either the .xls or the .xlsx format.

library(gdata)
ffdata = read.xls("http://www.math.smith.edu/r/data/help.xlsx",
sheet=1)

In this implementation, you specify the sheet by number, rather than name. This may be less precise than using the full name, but it does spare some tedious typing.
1月 222011
 

Calculate the factorial of 171 (171!)? Just TRY! It is equal to 171*170*169*….2*1.

1. Google calculator

As Google fanatics, I first try to search the answer via Google:

Google171

Whoops, nothing interested returned! Type “170!” and get the output:

Google170 Why kinda things happened in this calculator? 171! is just equal to 171*170!.

2. Excel

Switch to Excel spreadsheet. Function fact(*) used:

Excel170 Excel171 Oo, interesting. The same.

3. SAS

Google and Excel may be the niche players in calculators’ family. Why not try to use some programming languages?

As a SAS programmer, my handy tool is SAS of course.

First, I use SAS data step with its build-in function fact(*):

data _null_;
    x=fact(170);
    y=fact(171);
    put x= y=;
run;

and I get

NOTE: Invalid argument to function FACT at line 49 column 7.
x=7.257416E306 y=.
x=7.257416E306 y=. _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to missing values.

Expected or unexpected? I don’t know how this fact(*) function is defined, and  try to define a function to calculate the factorials by myself. In SAS 9.2, you can use PROC FCMP(also available at 9.1.3 as a experimental procedure):

proc fcmp outlib = work.funcs.math ;
    function factorial(k) ;
        if k = 0 then return(1) ;
        z = k ; *preserve k ;
        x = factorial(k-1) ;
        k = z ; *recover k ;
        k = k * x ;
        return(k) ;
    endsub ;
quit ;

options cmplib=work.funcs ;

Use this self-defined function to get 170!

proc fcmp ;
    x = factorial (170) ;
    put x = ;
run ;

The FCMP procedure returns

x=7.257416E306

Try to calculate 171! ?

proc fcmp ;
    y = factorial (171) ;
    put y = ;
run ;

Just get the overflow error. The interaction stops at 170!:

ERROR: An overflow occurred during execution in function ‘factorial’ in statement number 7 at   line 10 column 1.
       The statement was:
    1      (10:1)    k = (k=171) * (x=7.257416E306)

The above function definitions use recursion. Recursion may have some limitation on efficiency. We could try the loop without recursion. SAS/IML doesn’t support recursion. Let SAS/IML to the court:

proc iml;
    start factorial (n);
        fact=1;
        do i=1 to n;
        fact=fact*i;
        end;
        return (fact);
    finish factorial;

    x= factorial (170);
    print x;

    y= factorial (171);
    print y;
quit;

Again, I get 170!

    x
7.257E306

and a overflow error for 171!

            y= factorial (171);
ERROR: Overflow error in *.

Turing, Von Neumann and Tony, what happened?

4. R

When SAS failed, lots of voices pop up: use R! OK, Rction!

> x=factorial(170);x
[1] 7.257416e+306
> y=factorial(171);y
Warning message:
In factorial(171) : value out of range in ‘gammafn’
[1] Inf

5. C++

I don’t want to lose my patience. Think C++(use both recursive and non-recursive methods):

#include <iostream>
using namespace std;

double factRecursive(double num);
double factNonRecursive(double num);

int main()
{
    cout<<endl;   
    cout<<"Recursive: the factorial of 170 is "<<factRecursive(170)<<endl;
    cout<<"Recursive: the factorial of 171 is "<<factRecursive(171)<<endl;
    cout<<endl;  

    cout<<"NonRecursive: the factorial of 170 is "<<factNonRecursive(170)<<endl;
    cout<<"NonRecursive: the factorial of 171 is "<<factNonRecursive(171)<<endl;
    cout<<endl;   

return 0;
}

double factRecursive (double num)
{
    if (num==0)
        return 1;
    else
        return num*factRecursive(num-1);
}

double factNonRecursive (double num)
{
    double fact=1;
    for (double i=2;i<=num;i++) fact *=i;
    return fact;
}

Unfortunately, same story once more:

Cpp

Well. The story’s played out like this. It may be not the limitable of the language but the machine. I check which is the largest numbers my computer supports:

#include <iostream>
#include <cfloat>

using namespace std;

int main()
{
  cout<<"maxinum double value of machine: "<<DBL_MAX<<endl;
  return 0;
}

maxinum double value of machine: 1.79769e+308

Now everything’s in the open. The factorial of 170 is about 7.257416e+306. 171! is too big to be supported by my PC.

(Note: I put these codes in http://codepad.org, a online complier. if you don’t have any C++ complier in your machine, you can see the codes and outputs in:http://codepad.org/xnneavsw  and http://codepad.org/3FeEC9t2)

6. WolframAlpha

Struggled for hours, I turn to WolframAlpha computing platform. It returns the factorial of 171 AT LAST:

WA171 WA171_s  AT LAST we know the factorial of 171 has 310 digits.

7. Windows Calculator

I try to use Windows build-in calculator. Amazing, it is powerful:

winCalc

8. Python

Return to programming language.  First, I defined a function(recursive version) in Python and then use its MATH library:

>>> def factorial(n):

    if n==0:

        return 1

    else:

        return n*factorial(n-1)

>>> factorial(170)

7257415615307998967396728211129263114716991681296451376

5435777989005618434017061578523507492426174595114909912

3783852077666602256544275302532890077320751090240043028

0058295603966612599658257104398558294257568966313439612

2625710949468067112055688804571933402126614528000000000

00000000000000000000000000000000L

>>> factorial(171)

1241018070217667823424840524103103992616605577501693185

3889518036119960752216917529927519781204875855764649595

0167038705280988985869071076733124203221848436431047357

7889968548278290754541561964852153468318044293239598173

6968996572359039476161522785581800611763651084288000000

00000000000000000000000000000000000L

>>> import math

>>> math.factorial(171)

1241018070217667823424840524103103992616605577501693185

3889518036119960752216917529927519781204875855764649595

0167038705280988985869071076733124203221848436431047357

7889968548278290754541561964852153468318044293239598173

6968996572359039476161522785581800611763651084288000000

00000000000000000000000000000000000L

Amazing, Python beats up C++!

(to be continued :

Too Big to Be Accurate(2): Approximation

)

1月 202011
 
Tomorrow I'll be taking a few hours away from work to build something important: the self-esteems of a handful of middle-school-aged children.

I'm volunteering as a judge in a middle-school science fair. And even though I'm not a scientist ("computer science" isn't a category), I understand enough about physical science and the scientific method to contribute to the event. If you ever get a similar opportunity, take it! It's fun for you and rewarding for students. Here is a useful guide for how to be an effective judge.

I've participated in this event in previous years, but this year is different for me: one of the middle-school students is my 6th-grade daughter. Of course, I won't serve as a judge for her age group so there will be no chance for impropriety. However, I have enjoyed watching my daughter work on her project, from the time when it was just an idea (well, several ideas) to seeing the completed work. Like any parent would, I helped (when asked) with a few logistics, but the content is one-hundred-percent hers.

I'll admit that it was a little painful for me to watch as she recorded her results in Excel, summed and averaged the numbers, created tables and built graphs. It's so easy to veer off into the weeds of formatting and colors and lose the real content. Readability is important, and so is an attractive presentation. But nothing is more important than an accurate portrayal of your results. (And no, I didn't try to convince her to use SAS; that would be too overbearing.)

I know you're wondering: what was my daughter's project? Here's what it's not:

  • Can you teach a hamster to dance to music when food is on the line? (We don't have a hamster, let alone another hamster that would be needed as a control. I think this was an end-run effort to get a hamster in the house.)
  • Does the time of day that you were born have an effect on how early a riser you tend to be? (If that's the case, I think that most teenagers were born after noon.)

The real project: which brand of soda reacts more when you add Mentos to it: Diet Coke or Diet Pepsi? It won't win a Nobel prize, but it is testable and measurable. What's your hypothesis? Add it to the comments, with your reasoning. Then I'll follow up with my daughter's observations.

11月 302010
 
Japan has a superfast train called the Shinkansen. The first I heard of it was when my sister visited Japan on a scholarship. Engine-awestruck, we wanted a ride, but weren’t able to get to Japan. Nor, did we know the language. (With over 5000 kanjis, it took my sister 3 years to master the alphabet alone!)

What does the Shinkansen have to do with reading foreign data?

You don’t have to go far nor learn a new language to read your foreign data. In SAS one statement lets those smooth-and- oh-so-powerful engines hum away in the back lifting your data from foreign lands and bringing it to your doorstep. Like the Shinkansen, these library engines do the heavy lifting, freeing up your time to work on other tasks. In this post I will show you how and hope you like the music link at the end.

Recently I taught SAS Programming 1 in Ottawa. On this trip, a SAS user asked:

How can I read a range of data from excel into SAS? (Note: No imports please)

SAS can read virtually any type of data. Here’s how to read Excel data.
Let’s start with a checklist:

Continue reading "How SAS engines can read a range of data in Excel"
10月 012010
 
Since its 4.2 release, SAS Enterprise Guide has been able to import Microsoft Excel 2007 and 2010 spreadsheet files (usually encountered as .XLSX files). But while SAS Enterprise Guide can export XLS files (which are compatible with all versions of Microsoft Excel), it does not have the ability to export to a native .XLSX file. Customers see this as a limitation. The Microsoft Excel 2007 format allows for more rows and columns than traditional XLS files can support, and SAS Enterprise Guide does not offer a point-and-click method to create output results in this new format. In SAS 9.2, you can use PROC EXPORT or the EXCEL LIBNAME engine to write data in this new format, but using PROC EXPORT requires the customer to write a custom SAS program, and both PROC EXPORT and the EXCEL LIBNAME engine require you to license and install SAS/ACCESS to PC Files. New Custom Task: Export Data to Microsoft Excel 2007/2010 To help customers to work around this limitation, SAS R&D has created a custom task that focuses on one thing: export your SAS data to a Microsoft Excel 2007 (.XLSX) file. Here is an example screen shot of the task:
The task supports the following features/options:
  • Works only with SAS Enterprise Guide 4.3
  • Export to XLSX or XLS file
  • Specify the name of the worksheet in the exported file
  • Specify which columns to include in the exported file
  • Specify the output format (as a SAS format) for each column. This is especially useful for applying special treatment to date and date-time values to preserve the correct format in the spreadsheet.
  • Optionally place field headings on the first row of the exported sheet. You can also use labels instead of variable names.
  • Optionally create multiple worksheets if the row count exceeds the maximum supported by Microsoft Excel
  • Optionally create multiple worksheets if the column count exceeds the maximum supported by Microsoft Excel
Note: This task uses "native" technology to do its work: SAS/ACCESS to PC Files is not required. However, this also means that the task does not generate a SAS program that can be run independently outside of SAS Enterprise Guide or within a stored process. Download the custom task from this location (ZIP file, 473KB). Read the README PDF file in the ZIP archive to learn how to install and use it with SAS Enterprise Guide 4.3.
5月 222010
 

SAS-Excel

晚提到Excelpro的新书,《Excel图表之道——如何制作专业有效的商务图表》(刘万祥,北京:电子工业出版社,2010)。说些Excel,从一个SAS程序员的角度。

相对SAS,或其他的软件包(SPSS、R/S-Plus、Matlab等),Excel都是一个轻量级的分析工具。本来,Excel无意在统计分析方面跟那些统计工具包一较长短,各有适用的地方而已。但在商业世界,用Excel做分析的用户太多了,说最流行也不为过,一些统计学者,就提醒广大用户,用Excel做统计分析时一定要谨慎些,它有时候不够严谨有时候不够靠谱。详细的,可以参考谢益辉的三篇博文(以及他文中所附的参考材料):

1.Excel中的缺失值和0

2.为什么避免用Excel作统计计算

3.再谈为什么不用Excel做统计分析

用Excel本身的计算引擎有时是不够严谨,这是故事的一个方面(当然,它应付日常工作还是可以胜任的,对Excel,我们这点信心还是有)。故事的另一个方面,是鉴于Excel的广泛流行,各大BI(商务智能)套件,包括SAS系统,都要提供与Office/Excel整合的功能。上面就是 SAS Add-In for Microsoft Office在Excel里面的一个截图。利用这个Excel插件,商业用户可以继续使用他/她熟悉的Excel,访问SAS数据,并利用SAS的计算引擎来分析和作图,最后结果返回到Excel(或者Word、PPT)。一家公司不提供这玩意,它的BI技术就要受到质疑,至少也是没有做到user-friendly。夸张点说,小小的Excel就这样爬上了BI平台的最顶端。

我们注意到,与Excel的整合,重点是把它作为用户接口和展示平台。对一个传统的SAS程序员,与Excel打交道也是他职业生涯的一部分:

1. 有时候,数据会存储在Excel里面。你可能没法理解,为什么要把数据存在Excel里面,这样轻量级的数据,存在文本里面不是更好吗?在真实世界里,大部分人的行为模式一般是这样,他们打开Word来记事,打开Excel来记录数据。数据存在Excel或许也能接受,如果它们规规矩矩,行列有序。有一种情形就是,在一个Excel Book里,数据散布于各个Sheets,在同一个Sheet,数据可能不是按行列来排,而是长得就像一个report。平心而论,这样的数据真的是user-friendly,只是不够machine-friendly。在Windows平台下,SAS Base有三种最常用的方式读取Excel数据:

1-1 proc import or Import Wizard

1-2 SAS Excel Libname Engine

1-3 SAS DDE (Dynamic Data Exchange,这属于Windows的技术)

Excel不是一个跨平台的数据格式。在Unix平台下,如何读取Excel数据呢?SAS也有解决方案,那需要用到一个SAS/ACCESS Interface to PC Files for UNIX和SAS PC File Server for Windows,这也是一个Libname Engine的方式。

2. 读入Excel数据,一个相反的操作就是把SAS数据(或output)转化成Excel。程序员或许不太愿意,但这在商业世界有需求。这里SAS更是提供了五花八门的解决方案:

2-1 proc Export or Export Wizard

2-2 SAS Excel Libname Engine

2-3 SAS DDE (Dynamic Data Exchange)

2-4 proc printto

2-5 SAS ODS

2-6 data steps

2-7  SAS Add-In for Excel

……

除了DDE,你还可以利用其他非SAS技术,比如ODBC、OLE DB等。有一个小花招。利用ODS,可以把数据转化成用户可以用Excel打开的CSV、XML或者HTML格式,而它们本身是比.xls更优越的存储格式。特别是CSV(comma-separated values file),本身是文本文件,在Windows世界里,很多用户都把它当成Excel的一种格式,默认的打开方式也是Excel(而不是文本编辑器)。

罗列了这么多SAS与Excel的交互,回到Excel本身。它是一个功能强大的电子表格,在数据分析与展示方面也有不俗的表现(Excel作图,可以参考上面提到的Excelpro所达到的境界)。它赢得如此多的用户,说明它能满足大部分人大部分的需求。一个SAS用户,或者SPSS、R/S-Plus、Matlab等用户不必在统计分析方面展示对Excel的优越,这样显得就不够大气了——本来Excel就无意在这方面跟它们竞争。况且,即使工具之间有优劣,工具的本身的先后也不代表他们用户的先后,急于争辩工具厉害的人,往往忽略了人本身,自以为手持屠龙刀,就无敌于天下。笑话。

————————————————————————–

再讲个笑话,跟谢益辉有关(最近举例似乎老拿谢同学说事)。谢于数据可视化颇有研究,最近秀出了不少的工作,当然都是用R来展示。楼下便有人惊呼:R好强大啊。不知道谢听了做如何想。

5月 212010
 

Excel

 

Excelpro的《Excel图表之道——如何制作专业有效的商务图表》(刘万祥,北京:电子工业出版社,2010)4月份由武汉博文视点推出。我拿到书时,都已经是5月份第二次印刷的版本。“左手Excel,右手PPT”,现在这本书与一本叫《PPT演示之道》的书在当当卓越大卖,被认为是“2010年最值得阅读的职场充电图书”。好家伙,早知道这本书会火,没想到的是会这么快。扯扯我与这本书及其作者的花絮。

Excelpro一直在经营一个“ExcelPro的图表博客”,专攻Excel与商务作图,把Excel玩出花来的那种。读研时开始订阅他的博客,那时就想结识一番,这样的高手,要趁早认识。查看gmail记录,2008年12月19日,我给Excelpro写mail(当时只知道他的网名),说“ExcelPro,  你好。一有更新就看你的博客,还不知道你的真名真是有些说不过去。我叫***,blablabla”。很快收到回复。

知道了Excelpro的大名,按着习惯,在接下来的沟通中,我就直呼“万祥”了。在公司,包括以前的实习单位,无论同事还是老板,或者老板的老板,都是直接叫名字,慢慢也养成这习惯了——关于称谓,有一个趣事在下面,先打住。

武汉博文的周筠老师,不知什么时候也在看“ExcelPro的图表博客”。看这个博客,我的反应是高手要趁早认识,周老师的反应大概是,这样好的内容,要趁早出书。于是去年年底,武汉博文的编辑梁晶,问我能不能帮着看看一本Excel方面的新书。一看,ExcelPro,这么巧啊。赶紧答应下来,刚好可以趁着看文稿的机会好好学习一下Excel作图。

这本书讲Excel作图,可不是“excel->插入->图表->图表向导”这么简单。这种默认的作图方式,大伙一眼就能看出来,就是传统的、灰头土脸的Excel图表(熟悉吧?):

excel0

而这本书,是教你用Excel做出这样的图来:

excel1

是不是也很熟悉?就是国外顶尖商业杂志如《商业周刊》、《经济学人》等杂志风格的商务图表。如何做到,如何用Excel做到?审稿的时候,我就是这样怀着激动的心情,看Excelpro如何突破Excel的默认颜色、默认布局和默认的作图元素,从而一步步做出类似上图的专业商务图表,真是大呼过瘾。书还附有数据和样例,自己可以随手在机器上跟着实现。我平时鼓捣SAS,Excel用得不多,看了这书(你可以在这里下载高清样章),想什么时候该自己用Excel了,照着这书玩下去,铁定又是一条好汉。

出版社的老师喜欢称呼作者为老师。跟周老师和梁晶编辑邮件来往,她们都会提到“刘万祥老师”,一看,心想:惨了,我跟Excelpro邮件来往,叫名字都叫顺口了。我称呼周筠老师,周筠老师称呼刘万祥老师,而我就直呼“万祥”!这可窜辈了,邮件fwd来fwd去的。问Excelpro,最后我们约定,私下里我们称名字,有周老师在场就叫老师。——后来,刘万祥老师终于跟我开了个玩笑,在书的前言里,他写道:“感谢本书的评审专家孔文达老师、杜茂康老师和胡江堂老师”——“胡江堂老师?”我旁边朋友一看,立马收不住笑,Friday Humor?

5月 212010
 

Excel

 

Excelpro的《Excel图表之道——如何制作专业有效的商务图表》(刘万祥,北京:电子工业出版社,2010)4月份由武汉博文视点推出。我拿到书时,都已经是5月份第二次印刷的版本。“左手Excel,右手PPT”,现在这本书与一本叫《PPT演示之道》的书在当当卓越大卖,被认为是“2010年最值得阅读的职场充电图书”。好家伙,早知道这本书会火,没想到的是会这么快。扯扯我与这本书及其作者的花絮。

Excelpro一直在经营一个“ExcelPro的图表博客”,专攻Excel与商务作图,把Excel玩出花来的那种。读研时开始订阅他的博客,那时就想结识一番,这样的高手,要趁早认识。查看gmail记录,2008年12月19日,我给Excelpro写mail(当时只知道他的网名),说“ExcelPro,  你好。一有更新就看你的博客,还不知道你的真名真是有些说不过去。我叫***,blablabla”。很快收到回复。

知道了Excelpro的大名,按着习惯,在接下来的沟通中,我就直呼“万祥”了。在公司,包括以前的实习单位,无论同事还是老板,或者老板的老板,都是直接叫名字,慢慢也养成这习惯了——关于称谓,有一个趣事在下面,先打住。

武汉博文的周筠老师,不知什么时候也在看“ExcelPro的图表博客”。看这个博客,我的反应是高手要趁早认识,周老师的反应大概是,这样好的内容,要趁早出书。于是去年年底,武汉博文的编辑梁晶,问我能不能帮着看看一本Excel方面的新书。一看,ExcelPro,这么巧啊。赶紧答应下来,刚好可以趁着看文稿的机会好好学习一下Excel作图。

这本书讲Excel作图,可不是“excel->插入->图表->图表向导”这么简单。这种默认的作图方式,大伙一眼就能看出来,就是传统的、灰头土脸的Excel图表(熟悉吧?):

excel0

而这本书,是教你用Excel做出这样的图来:

excel1

是不是也很熟悉?就是国外顶尖商业杂志如《商业周刊》、《经济学人》等杂志风格的商务图表。如何做到,如何用Excel做到?审稿的时候,我就是这样怀着激动的心情,看Excelpro如何突破Excel的默认颜色、默认布局和默认的作图元素,从而一步步做出类似上图的专业商务图表,真是大呼过瘾。书还附有数据和样例,自己可以随手在机器上跟着实现。我平时鼓捣SAS,Excel用得不多,看了这书(你可以在这里下载高清样章),想什么时候该自己用Excel了,照着这书玩下去,铁定又是一条好汉。

出版社的老师喜欢称呼作者为老师。跟周老师和梁晶编辑邮件来往,她们都会提到“刘万祥老师”,一看,心想:惨了,我跟Excelpro邮件来往,叫名字都叫顺口了。我称呼周筠老师,周筠老师称呼刘万祥老师,而我就直呼“万祥”!这可窜辈了,邮件fwd来fwd去的。问Excelpro,最后我们约定,私下里我们称名字,有周老师在场就叫老师。——后来,刘万祥老师终于跟我开了个玩笑,在书的前言里,他写道:“感谢本书的评审专家孔文达老师、杜茂康老师和胡江堂老师”——“胡江堂老师?”我旁边朋友一看,立马收不住笑,Friday Humor?