完美Excel

在Excel中，我们可以使用“数据有效性”功能来创建下拉列表，供用户从中选择。例如，如下图所示：

①选择主数据工作表中的B列。
②调出“数据有效性”对话框，选择“允许”下拉列表框中的“序列”。
③在“来源”中输入要提供用户选择的文本，例如：请假,出差,年休。
④单击“确定”后，在主数据工作表的B列中任一单元格单击，出现带有数据的可供选择的列表，如下图。

当然，也可以将需要提供的数据放置在工作表中的某单元格区域内，然后在“来源”中输入该单元格区域地址。
上述方法对于固定的或者说事先知道在列表中提供的数据时，非常合适。然而，如果下拉列表中的数据需要不断增加，或者需要调整，仍然按照上述方法的话，则需要重复上述步骤。
下面提供一种方法，能够动态改变列表中的数据，而无需每次调出“数据有效性”对话框。先看看下图：

①在基础数据工作表中，在列A单元格中依次输入想要显示在下拉列表中的数据，如“请假”、“出差”、“换休”。
注：将这些数据输入在单独的工作表中，能够避免在主工作表中操作时无意中修改这些数据。
②调出“新建名称”对话框，在“名称”中输入“List”。
③在“范围”列表中选择“工作簿”。
④在“备注”中输入一些说明文字。
⑤在“引用位置”输入公式“=OFFSET(基础数据!$A$1,0,0,COUNTA(基础数据!$A:$A),1)”。
现在，回到主数据工作表，如下图所示。

①选择列C。
②调出“数据有效性”对话框，选择“允许”下拉框中的“序列”。
③在“来源”中输入公式“=List”，即与前面定义的名称连接。
此时，单击列C中的任一单元格，将出现下拉列表框，包括在基础数据工作表中列A的数据，如下图所示。

现在，您在基础数据表中添加数据，如加班，此时在主数据工作表的列C中单元格的下拉列表中会自动出现添加的“加班”项，如下图所示。

概述
当需要以一种格式采集数据，然后操纵该数据到另一种格式里，最后放置该结果到另一种过程例如数据库时，Excel是一种极好的工具。在本文中，Excel是一种中间工具，作为一种数据转换工具将数据从一种系统转移到另一种系统。
本示例考虑一个简单的数据集，介绍整理数据的步骤，并在数据集中执行不同的转换，成为更合适的格式。
示例数据
这里是本示例练习里讨论的数据，复制(导入)并粘贴到电子表格里。
SSN,Last Name,Middle Name,First Name,DOB,Date of Letter,E-mail Address,Home Phone,Address,City,State,Postal Code
123456789,freehafer    ,drew   ,nancy,19700101,11/1/2007,NancyF@northwindtraders.com,1235550102,123 1st   Avenue,Seattle  ,wa,09999
123456789,cencini        ,a   ,andrew,19700101,11/1/2007,AndrewC@northwindtraders.com,1235550102,123 2nd Avenue,Bellevue,wa,09999
123456789,kotas      ,bollen,jan,19700101,11/1/2007,JanK@northwindtraders.com,1235550102,123 3rd Avenue,Redmond  ,wa,09999
123456789,sergienko      ,,mariya,19700101,11/1/2007,MariyaS@northwindtraders.com,1235550102,123     4th Avenue,Kirkland,wa,09999
123456789,thorpe     ,j ,steven,19700101,11/1/2007,steven@northwindtraders.com,1235550102,123  5th Avenue,Seattle,wa,09999
123456789,neipper    ,john,michael,19700101,11/1/2007,MichaelN@northwindtraders.com,1235550102,123 6th Avenue,Redmond,wa,09999
123456789,zare       ,b ,robert,19700101,11/1/2007,RobertZ@northwindtraders.com,1235550102,123 7th Avenue,Seattle,wa,09999
123456789,giussani       ,,laura,19700101,11/1/2007,LauraG@northwindtraders.com,1235550102,123 8th Avenue,Redmond,wa,09999
123456789,hellung-larsen      ,marie,anne,19700101,11/1/2007,AnneHL@northwindtraders.com,1235550102,123 9th Avenue,Seattle   ,wa,09999
注意与该数据相关的一些事项：
• SSN没有被格式化，只是原始的数字
• Last Name全部小写并且包含额外的空格
• 一些人根本没有中间名，一些人是简写，一些人是完全拼写
• Date of Birth (DOB)是YYYYMMDD，而Date of Letter是MM/D/YYYY格式
• E-Mail Address包含大写和小写字符
• Home Phone没有格式化
• Address有额外的空格
• State全部是小写
• Postal Code有前导零并且不应该略去
这里是为处理准备这些数据所做的操作：
• 当将数据导入Excel中时不能失去前导零
• 格式SSN，在合适的地方放置破折号
• 从姓名字段和Address字段中移除额外的空格
• 在一个字段里获取所有的名称，以“Last Name, First Name Middle Initial.”的形式
• 转换DOB成MM/DD/YYYY格式
• 使E-Mail Address值全都成为小写
• 根据标准的US电话号码数字格式(NNN) NNN-NNNN格式Home Phone字段
• 大写State值
• 根据这些排序关键字排序数据: State / City / Last Name / First Name / Middle Name (not middle initial)
步骤1：导入数据并且不要意外移去前导零！
对Excel，Postal Code值看起来像数字，这是装载该文件时对待的缺省方式。(不巧的是，Excel不能读取标题“Postal Code”并切换它为文本字段)
有一种方式覆盖这种缺省方式，使用“文本导入向导”：
1、“文件”-“打开”该文本文件。
2、文本导入向导——步骤1(共3步)：选择“分隔符号”，然后单击“下一步”。
3、文本导入向导——步骤2(共3步)：选择“逗号”作为分隔符，然后单击“下一步”(注意，其它的分隔符可以选择，甚至在数据使用的字符不在UI列表中时可以指定自定义的分隔符)。
4、文本导入向导——步骤3(共3步)：滚动到Postal Code字段并选择，然后设置“列数据格式”为文本，然后单击“完成”。
在列标题之间双击，使得列内容适合于列，结果如下图所示。
 
现在，数据已导入到工作表中而没有失去关键的信息。下一步处理这些格式为报告和上载。
步骤2：格式SSN
SSN值需要被转换为NNN-NN-NNNN的形式，可使用下面的两种方式完成。
1、改变显示数据的方式，但仍保持其实质为数字，或者
2、在数据里插入连字号。
方式一：通过单元格格式修改显示
1、选择存储数据的单元格区域A2:A10；
2、调出“设置单元格格式”对话框(Ctrl+1)；
3、在数值选项卡中，单击“自定义”类；
4、在“类型”中，输入字符串：000-00-0000；
5、单击“确定”。
下图为最终的结果。
 
注意，实质上的值为“123456789”(可在公式编辑栏中看到)。格式化单元格来显示连字号。
方式二：插入连字号
需要公式来完成该操作。本示例将使用从原始数据中创建新的数据表来转换。
公式将使用MID()函数提取SSN中的前3个数字，然后使用“&”来连接连字号，接着使用MID()函数获取中间的2个数字，使用“&”插入另一个连字号，最后再使用MID()来取得SSN中最后的4个数字。
1、复制并粘贴A1：L1中的标题到N1：Y1中。
2、在N2中，输入下面的公式：
=MID(A2,1,3)&”-”&MID(A2,4,2)&”-”&MID(A2,6,4)
3、向下填充公式到所有数据行(本例为N2：N10)。
下图是应用公式后的结果：
 
步骤3：处理姓名
处理姓名的几个问题：
1、没有在正确的情形下
2、包含额外的空格
3、仅需要中间的大写
“&”操作符用于使用逗号和空格连接Last Name里的值，“&”操作符用于从First Name中添加值，并添加空格到结果中，LEFT()函数与“&”操作符一起用于从Middle Name中创建和添加中间的大写字母。最后两步将这些函数连在一起，并使用TRIM()函数移除额外的空格。同时，使用PROPER()函数来设置正确的字母拼写。
1、在这里，在O1：Q1中的标题“Last Name”、“Middle Name”和“First Name”不再需要。删除列P和列Q，并重命名O1中的“Last Name”为“Name”，且公式将在列O中输入。
2、在O2中输入下列函数：=PROPER(TRIM(TRIM(B2)&”, “&D2&” “&LEFT(C2,1)))
结果如下图所示：

注意：额外的空格不会是数据中唯一不需要的字符，CLEAN()函数可以用于移除非打印字符。
步骤4：将DOB转换为实际的日期
DOB值不会被Excel作为日期来识别，需要将其转换为MM/DD/YYYY格式。使用DATE()函数和MID()函数将获取所需要的结果。使用MID()函数从DOB字段中提取日期中的YEAR、MONTH和DAY部分，使用DATE()函数将结果转换为日期：
1、在P2中输入下列公式：
=DATE(MID(E2,1,4),MID(E2,5,2),MID(E2,7,2))
2、向下填充该公式到数据行(本例中为P2:P10)
结果如下图所示：
 
步骤5：填充Date of Letter
对Date of Letter字段不需要改变，因此复制并粘贴这些值，或者用公式指向它们以便自动完成表格创建。
步骤6：小写E-Mail地址
为使表达一致，应该小写电子邮件地址，因为电子邮件不会区分大小写。使用LOWER()函数来完成这项操作。
1、在单元格R2中，输入公式：=LOWER(G2)
2、下拉公式填充全部的数据行
结果如图所示：

步骤7：格式化Home Phone
Home Phone值难以阅读，且不是正确的格式，需要处理为(NNN) NNN-NNNN格式。
与SSN一样，可以通过单元格格式或在合适的位置插入字母来完成。
方式一：通过单元格格式显示改变
1、选择数据存储的单元格H2：H10
2、激活“设置单元格格式”对话框(Ctrl+1)
3、在“数值”选项卡中，单击“自定义”类别
4、在输入框中，输入字符串：(000) 000-0000
最终的结果如下图所示：

方法二：插入括号和连字号
需要使用公式来完成这些操作。该公式使用MID()和连接操作符创建正确的值。
1、在单元格S2中，输入公式：
=”(”&MID(H2,1,3)&”) “&MID(H2,4,3)&”-”&MID(H2,7,4)
2、下拉公式到所有数据行(本例中为S2：S10)
结果如下图所示：
 
步骤8：从Address中移除额外的空格
使用TRIM()函数移除额外的空格，与处理Name字段一样。
步骤9：添加City值到新表中
由于创建一个完整的新表取代原来的数据，因此需要从City中的值，即使它们中没什么。如果有大量的数据想要，可能不想手工查看它们。此时，可以使用TRIM()和CLEAN()。
步骤10：使所有的State值为大写
State字段包含小写的值，该字段所有的字符需要变为大写。PROPER()和LOWER()已经用于清除数据，因此使用UPPER()来转换小写字符成大写是容易的。
1、在V2中输入公式：=UPPER(K2)
2、下拉公式至所有数据行(本例中为V2：V10)
结果如图所示：

步骤11：填充Postal Code
从L2：L10中复制并粘贴到W2：W10中。
步骤12：确定最终的数值
现在，已使用所需要的格式完成了创建新表。应该移除公式，仅保留值，然后删除原始数据来完成最终的表。
通过，复制N1：W10区域，并选择性粘贴其值到A1：J10区域中，然后删除多余的列，完成最终的表如下图所示。
 
额外的讨论：使用多于3个关键值排序文本
可能一些用户需要使用多于3个关键字来对数据排序。Excel 2003仅支持3个层级的关键字，而Excel 2007支持更多。本示例无须使用Excel 2007，这里介绍如何使用Excel 2007来完成该操作。
假设原始数据设置需要通过State/City/Last Name/First Name/Middle Name来排序，考虑下列数据组：

可以创建一个新列(如列F)，使用文本联接将排序关键字连接在一起。在F2，输入下列公式：E2&D2&A2&C2&B2，将其标题命名为“SortKey”。
现在，排序该单个的列中与逐个排序关键字是相同的。当然，使用这种方式不能指定升序/降序。因此，Excel 2007更好。
附1：清除文本
Trim
移除单词之间除单个空格外的所有空格。在从另一应用程序中接收到的具有不规则空格的文本中使用TRIM。
TRIM函数被设计从文本中移除7位ASCII空格字符(值为32)。在Unicode字符集中，有一个额外的空格字符，称为非打断空格字符，其十进制值为160。该字符通常用于网页中作为HTML实体 。TRIM函数不能移除该字符。
Clean
从文本中移除所有非打印字符。使用CLEAN，清除从其它应用程序中导入的包含使用在操作系统中不可打印的文本。例如，可以使用CLEAN移除一些低级的计算机代码，这些代码经常在数据文件的开头和结尾，并且不可以被打印。
CLEAN函数被设计从文本中移除7位ASCII代码中开头的32个非打印字符(值从0到31)。在Unicode字符集中，有一些额外的非打印字符(值为127、129、141、143、144和157)。CLEAN函数不能移除这些非打印字符。
使用SUBSTITUTE和CHAR从Unicode中进行高级清除
有时，文本值包含前导的、一连串的、或多个嵌入的空格字符(Unicode字符集值为32和160)，或者非打印字符(Unicode字符集值为0至31、127、129、141、143、144和157)。在排序、筛选或搜索时，这些字符有时会导致不可预料的结果。例如，用户不会注意添加额外的空格字符或者从可能在嵌入了包含非打印字符的文本里导入文本，会产生印刷上的错误。因为不容易注意这些字符，不可预料的结果可能难于理解。要移除这些不需要的字符，可以使用TRIM、CLEAN和SUBSTITUTE函数的组合。
TRIM函数从文本中移除单词之间除单个空格外的空格，CLEAN函数从文本中移除所有不可打印的字符。这两个函数被设计来处理7位ASCII，这是ANSI字符集的一个子集(ANSE字符集：Microsoft Windows使用的一个8位字符集，允许使用键盘表示达到256个字符(0至255)，ASCII字符集是ANSE集的一个子集)。在7位ASCII里的前128个值(0至127)与Unicode字符集里前128个字符代表相同的字符，理解这一点是重要的。
TRIM函数被设计来从文本中清除7位ASCII空格字符(值为32)。在Unicode字符集中，有一个称作非打断空格字符的额外的空格，有一个十进制值160。该字符通常用于网页中作为HTML实体 。TRIM函数不能移除这个非打断空格字符。
CLEAN函数被设计来从文本中清除7位ASCII代码中开始的32个非打印字符(值从0到31)。在Unicode字符集中，有一些额外的非打印字符(值为127、129、141、143、144和157)。CLEAN函数不能移除这些额外的非打印字符。
要完成这项任务，使用SUBSTITUTE函数将Unicode字符中更高的值替换为TRIM和CLEAN函数被设计的7位ASCII字符。
注：本文译至MS Excel团队博客。本译文只是初稿，并未进一步整理和校对，仅供参考。

利用REPT()函数，可以根据单元格中的文本绘制图形。
如下图1所示，在C列中创建直方图以形象地对比数据。

图1
在C列中的公式为：
=REPT(“|”,B2)
一直拖拉至单元格C6。
使用文本并设置不同的字体，还可以将直方图变成一个实心的直方图。如下图2所示，利用直方图表示各工程的进展情况。

图2
在C列中的公式为：
=REPT(”g”,100*B4/5)
一直拖拉至单元格C8，然后设置单元格区域C4：C8的字体为Webdings。为了获取函数参数值，将其乘以100，同时为了使图形的长度合适，将所得数值除以5。

Rand()函数简介
Excel中的Rand()函数返回大于或等于0且小于1的均匀分布的随机数。在每次计算工作表或重新打开工作表时，该函数都将返回一个新的数值。
语法：RAND( )
基本用法
1、若要生成大于或等于数值a且小于数值b的随机实数，可使用公式：
RAND()*(b-a)+a
例如，下面的公式生成介于0到10之间的随机数：
=RAND()*10
下面的公式生成大于等于50且小于100的随机数：
=RAND()*(100-50)+50
2、如果要使用函数RAND()生成一个不随单元格计算而改变的随机数，则可以在编辑栏中输入“=RAND()”并保持编辑状态，然后按F9键，从而将公式永久性地改为随机数。
随机生成指定位数的整数值
有时，需要随机生成具有指定位数的整数值。例如，随机生成一个6位的帐号，则可使用下面的公式：
=INT(RAND()*(1000000-100000)+100000)
按F9键，该公式会随机生成大于等于100000而小于等于999999的整数值。
随机生成字母
如果要随机生成A-Z这26个字母中的一个字母，可以联合使用Rand()函数和Choose函数来完成。
公式：=INT(RAND()*26+1)
随机生成一个1至26之间的整数。
公式：
=CHOOSE(INT(RAND()*26+1),”A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”J”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”)
随机生成A-Z中的一个字母。

参考资源：http://www.exceluser.com
Excel的SUMPRODUCT函数提供Excel数组公式的大多数功能，并且在使用上不复杂。
SUMPRODUCT函数可以方便地计算工作表内多列中对应值相乘后的和，其语法为：
SUMPRODUCT(array1,array2,array3, …)
其中，Array1, array2, array3, … 为 2 到 30 个数组，其相应元素需要进行相乘并求和。数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。 该函数将非数值型的数组元素作为 0 处理。
下面通过示例介绍SUMPRODUCT函数的基本使用方法。如下图1所示的工作表：
图1
其中所定义的名称为：

(1)要计算工作表中姓名是张三且公司为A的用工数统计，则可以使用下面的公式：
=SUMPRODUCT(0+(姓名=”张三”),0+(公司=”A”),用工数)
返回结果24。
(2)要获取姓名张三出现的次数，则可以使用下面的公式：
=SUMPRODUCT((姓名=”张三”)*1)
或=SUMPRODUCT(0+(姓名=”张三”))
结果为5。
(3)要获取姓名为张三且公司为A的总数，则可以使用下面的公式：
=SUMPRODUCT((姓名=”张三”)*(公司=”A”)*1)
或=SUMPRODUCT((姓名=”张三”)*(公司=”A”))
结果为4。

图2
(4)探讨
在计算工作表中姓名是张三且公司为A的用工数统计时，使用的是公式=SUMPRODUCT(0+(姓名=”张三”),0+(公司=”A”),用工数)。按照常规做法，可以使用公式：
=SUMPRODUCT(姓名=”张三”,公司=”A”,用工数)
但其结果为0，即并不是所想要的正确结果24。

• Excel在公式中能将以文本表示的数字转换为数字，例如公式：
  =”3”*5
  虽然”3”是文本，但该公式能返回结果15。

• Excel也能将数字转换为文本，例如：
  =”No” & 1
  返回的结果为No1。

• Excel将逻辑值转换为数值，例如：
  =0+TRUE返回的结果为1；
  =1*TRUE返回的结果为1。

因此，在公式中添加0强制将逻辑值进行转换？！