观赏鱼图片大全 图片:Excel数据透视表培训第2课！

来源：百度文库 编辑：九乡新闻网 时间：2024/04/26 03:03:24
Excel数据透视表培训第2课：玩儿转数据透视表2010年03月14日 星期日 22:19
  1. 数据透视表来源于英文Pivot Tables，港台直译为枢纽分析表，读港台Excel书籍可以看到。
  2. 数据透视表前世今生
    1. 最初有Lotus莲花公司提出，1993年Lotus开发出win版本的透视表。
    2. 95年微软在Excel95开发出数据透视表；
    3. Excel97提供了增强的数据透视表向导功能，对功能进行了重大改善，比如天价了计算字段的能力，开放了数据透视表缓存，从根本上改变了创建和管理数据透视表的能力；
    4. Excel2000：引入了数据透视图，为用户提供用图形的方式来表现数据透视表的方法
    5. 目前最新版的数据透视表应该是Excel2010.
  3. 数据透视表的特点——快 活
    1. 快
      1. 计算速度快：与函数计算相比，速度提升很大，可以认为是微软Excel计算速度最快的功能；
      2. 学习上手快：学习和使用是非常简单的，轻点鼠标就能创建数据透视表。
    1. 活
      1. 布局变换灵活：超越我们的想象，是本节课的重点；
      2. 学习后思维灵活：深入学习后，思维方面的灵活度能够提升，分析问题和展示的能力的锻炼比较高，能够锻炼发散思维。在以后的学习中慢慢体会；
    1. 学好数据透视表——快活
  4. 谋定而后动：深入认识数据源结构和数据透视表的关系
    1. 数据源表结构对透视表的影响：准备创建透视表的首要工作是对于数据源进行分析——结构；
      1. 数据清单式
        1. 从创建数据透视表，布局及已经开始了；
        2. 使用透视表向导，在第三步有【布局...】按钮对数据透视表进行布局
        3. 由某个字段的数据项构成列字段，列字段可以进行选择，能够自动出现列的合计
      1. 二维数据表
        1. 向导一直下一步完成；
        2. 拖动字段到相应区域；
        3. 列字段是由数据源的每个字段的求和项构成的，在进行列字段下拉框选择时候，第一次没有选中的列字段，在第二次会地市，如果需要恢复，重新拖列字段到数据透视表区域中。没有列的合计。
      1. 两种生成的表都可以具有二维数据表的结构，但是生成的方式是不同的。
    1. 用含有合并单元格的数据源制作数据透视表
      1. 需要对源数据进行调整，否则创建出的透视表含有空白数据项，不同的标准项下的结构也不是期望的结果。
      2. 选中合并单元格，格式刷到另外空白列，以备后用，然后取消合并单元格，可以看到很多的空白项，这是生成数据透视表不正确的主要原因。
      3. 选中包含合并单元格的那一列，按F5进行定位，定位条件选择空值；
      4. 在编辑栏数据等号等于他上面的单元格，然后按下ctrl+enter键，这样就填充满了所有空白单元格，然后将原来的格式刷回去。（这个技巧也可以保证合并单元格之后原单元格的数据也还保存着）
      5. 再次创建数据透视表。
    1. 创建之前一定要对数据源进行分析，这样才能根据数据源创建出我们需要的数据透视表。
  5. 神奇的变换：行字段区域和列字段区域的布局及透视表变换
  6. 玩转页字段：页字段使用技巧
    1. 选择页字段显示项：不能对于页字段进行复选
      1. 将页字段拖到行字段，然后复选，然后拖回页字段，发现只剩下了复选的项；
      2. 双击页字段名称，弹出数据透视表字段对话框，在隐藏数据项列表中选择要隐藏的，留下要查看的。
      3. 要恢复全部显示，反向操作以上就可以了。
    1. 重排页字段
      1. 将页字段从纵向改为横向：透视表中右键，选择表格选项，弹出窗口中改变页面布局：默认为垂直排列，可以选择水平排列。每行字段数默认为0，也可以进行更改显示每行的字段数。
      2. 也可以横向改纵向
    1. 分页显示方法
      1. 选择透视表一个单元格，打开数据透视表工具栏，在工具栏上单击数据透视表，在下拉菜单中选择分页显示；
      2. 弹出一个列表，选择相应的页字段，就能按照每个字段分布在各个sheet页展示出来了。实际表都是一样，只是每个sheet页的筛选条件都不一样。
  7. 数据的舍与得：学会在数据透视表中选择数据
    1. 在行字段或列字段中选择
      1. 行字段/列字段下拉列表中直接选择需要的显示的数据项。
    1. 设置自动筛选的两种方法
      1. 无法直接创建。
      2. 所有的数据透视表中的字段都设置自动筛选：
        1. 在行字段、列字段展示的最后单元格，选择菜单数据-筛选-自动门筛选，这样自动筛选就都显示出来了。之后进行条件的设置。
        2. 取消方法：反向操作即可。
        3. 这样筛选，行字段也是会变为自动筛选的。
      1. 只将列字段设置筛选，行字段不用自动筛选：选择最后单元格，往前选中列字段，选择自动筛选。
  8. 数据表的迁移：透视表、透视图的复制和移动
    1. 透视表的复制与移动
      1. 复制：选择数据透视表的一个单元格，光标移动到透视表左上角，光标变形，单击鼠标左键，选择了整个数据透视表，复制粘贴。两个表一模一样的。
      2. 移动
        1. Excel2003中没有移动的命令或功能，利用创建过程中的一个步骤实现移动的目的。
        2. 将原来的移动到新位置上，原来的位置上不存在了。
        3. 右键-数据透视表向导，弹出向导步骤之3，通过改变这个达到移动的目的。
    1. 影子数据透视表
      1. 使用Excel的照相机功能，生成动态照片。
        1. Excel没有直接列示出来，需要去自定义命令里面选择。
        2. 选中区域，单击照相机，再去空白区域单击一下就“冲洗”出来照片了。
        3. 去除“照片”的外框：右键，设置格式，颜色与线条，填充颜色选择物，边框的线条也设置为无。
        4. 对数据透视表做的照片只能最多显示原来显示的大小。
      1. 使用扩展命令
        1. 使用shift选中区域，按ctrl+c复制区域（右键菜单没有这个命令），到要粘贴的区域，按下shift键，去单击菜单编辑，里面出来了扩展命令，选择粘贴超链接。
        2. 这样粘贴出来的没有外框。
      1. 实际影子透视表就是一个图片，对于数据透视表进行引用。如果在上面放一个图形，影子区域也会显示出来。影子区域可以选择等图片的操作。
    1. 数据透视图的复制与移动
      1. 选中数据透视图，右键-位置-作为其中的对象插入，选择表名，确定后就移动过去了。
      2. 复制、粘贴操作正常使用。
  9. 追根溯源：获取数据透视表的数据源信息
    1. 获取数据源信息
      1. 先看有没有隐藏的sheet页；
      2. 数据表区域中，右键-数据透视表向导，步骤之三对话框中，单击上一步回到步骤之二，在这里能看到生成数据表的数据源。如果没删除数据源是可以看到的。
      3. 如果数据源删除，在页字段选择全部，所有字段都选择全部显示，双击总计栏，明细数据就生成了。
      4. 如果只想要一部分数据，在字段进行选择之后，就能生成相应的有过筛选按的数据了。
      5. 单击要查看的字段的数据单元格就能查看相应字段的明细数据。实际上就是能显示出该数据是如何汇总过来的。
      6. 得到明细数据的前提是，表格选项中已经勾选了显示明细数据，如果不勾选，明细数据无法显示。
    1. 显示明细数据
      1. 在页字段选择全部，所有字段都选择全部显示，双击总计栏，明细数据就生成了。
      2. 如果只想要一部分数据，在字段进行选择之后，就能生成相应的有过筛选按的数据了。
      3. 单击要查看的字段的数据单元格就能查看相应字段的明细数据。实际上就是能显示出该数据是如何汇总过来的。
      4. 得到明细数据的前提是，表格选项中已经勾选了显示明细数据，如果不勾选，明细数据无法显示。
      5. 禁止显示明细数据
      6. 得到明细数据的前提是，表格选项中已经勾选了显示明细数据，如果不勾选，明细数据无法显示。
      7. 上述方法并不可靠，因为还可以通过勾选找回来。
      8. 选中数据透视表，复制到其他工作簿上就不能查看明细数据了，前提是已经将显示明细数据取消勾选。
      9. 这样经过处理的数据透视表能够正常使用，但是无法通过双击单元格看到明细数据了，能够保密数据源。
类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(538) 《Excel实战技巧精粹》技巧237-241【2010-3-12】2010年03月12日 星期五 23:28
  1. 统计指定月份的销量汇总
    1. {=SUM(VLOOKUP($B$3,$D$3:$P$8,ROW(INDIRECT($B$4&":"&$B$5))+1,0))}
    2. 利用vlookup函数第三个参数支持数组的技巧进行汇总，利用row和indirect组合，根据指定起止月份生成自然序列{2;3;4;5;6;7;8;9}的常量数组
  2. 取得目标汉字的拼音首字母
    1. 查找前需要汉字字符集中每个字母的第1个汉字，以升序排列生存一个对应关系表或常量数组。
    2. 利用VLOOKUP函数进行查找
  3. 根据双列条件进行查找
    1. 一般就是将映射的数据合并到一个单元格，然后进行查找。
    2. 还可以利用INDEX和MATCH的构建数组公式，无需辅助列。
  4. 返回最后一条数据记录
    1. 先将数据记录按照查询的关键字段进行排序，利用lookup函数的特点。{=LOOKUP(G3,IF(A2:A10=G2,B2:D10))}
    2. 如果未经排序{=LOOKUP(9E+307,IF((A2:A10=G2)*(B2:B10=G3),D2:D10))}
  5. 按单条件筛选记录
    1. 先用find进行查含有张，返回数值，否则返回错误。
    2. 通过if做判断，将满足条件的行号取出来，最后将small函数逐个将行号提供给index返回最终结果。
类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(48) 《Excel实战技巧精粹》技巧232-236【2010-3-11】2010年03月12日 星期五 00:49
  1. 生成垂直、水平序列
    1. 垂直序列{=ROW(INDIRECT("1:26"))}；
    2. 水平序列={=COLUMN(INDIRECT("A1:Z1"))}。
  2. 理解OFFSET函数
    1. 以指定引用为参照系，通过给定偏移量得到新的引用，语法：OFFSET(regerence,rows,cols,height,width)；
    2. Height,width参数可设置为负数，表示向上，向左偏移。
  3. 批量生成工资条
    1. 如A-E列为一个工作表里面的工资数据，另外工作表中输入=IF(MOD(ROW(),3),OFFSET($A$1,(MOD(ROW()-1,3)>0)Z*ROUND(ROW()/3,),COLUMN(A1)-1),"")；
    2. 利用MOD和ROW函数形成自然数序列，每个员工之间间隔一空行。
  4. 建立超链接
    1. 利用HYPERLINK建立超链接
    2. HYPERLINK(link_location,friendly_name)
    3. Link_location除了使用直接的文本链接外，还支持使用Excel定义的名称，在相应的名称前必须加前缀，如#DATA,#LINKADDRESS。
  5. 自动跳转到未输入的单元格
    1. 使用hyperlink结合查找引用函数的方法，能够在Excel中实现动态链接的效果
    2. 先建立命名公式LINK：=OFFSET($B$1,COUNTA($B:$B),)
    3. 然后创建超链接：=HYPERLINK("#LINK","定位")。

类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(65) 《Excel实战技巧精粹》技巧227-231【2010-3-10】2010年03月11日 星期四 00:27
  1. 返回引用的单元格地址
    1. ADDRESS函数语法：ADDRESS(row_num,column_num,abs_num,al,sheet_text)
    2. a1省略，就会以A1引用方式返回结果
    3. 跟match函数配合使用挺爽，不过就是要注意行列如果不是从A1开始的，要加上相应的值
  2. 逆向查询数据
    1. {=VLOOKUP(B3,IF({1,0},E2:E7,D2:D7),2,0)}
    2. 使用if函数改变列顺序，构建一个新的二维数组
    3. =INDEX($D$2:$D$7,MATCH($B$3,$E$2:$E$7,0))
    4. index函数也挺好用啊，配合match也是比较爽
  3. 模糊查找数据
    1. 通配符查找：VLOOKUP,HLOOKUP,MATCH,LOOKUP
    2. VLOOKUP只能返回第一条记录，使用通配符也需要确定为精确查找；
    3. lookup需要对数据进行个升序排列。
  4. 返回字符串中连续数值
    1. =LOOKUP(9E+307,--RIGHT(A2,ROW(INDIRECT("1:"&LEN(A2)))))
    2. 利用right函数取出字符进行减负运算，将文本型数值运算得出具体数值，而真正文本运算后为#VALUE!错误，lookup函数支持忽略错误查找，直接返回整个数值中最后的数值。
    3. {=RIGHT(A2,MATCH(1,0*RIGHT(A2,ROW(INDIRECT("1:"&LEN(A2))))))}
    4. 与第一个公式的思路类似
    5. =LOOKUP(9E+307,--MID($A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2)))))
    6. 这个能查找出字符中间的连续数字了，利用find函数查找数字出现的第一个位置，再根据从第一个位置从后助威去除所有数值和文本字符串，进行减负运算之后，通过查找9E307得出结果。
    7. 9E307百度了一下，是Excel中最大能够支持的数字，是想要数字形式忽略错误值的一个好方法。
  5. 定位最后非空单元格
    1. =OFFSET($B$1,COUNTA($A:$A)-1,)
    2. 中间没有空单元格时候用上面公式，就是找出非空的数目，然后从第一个单元格向下偏移取值
    3. =MATCH(9E307,A:A)
    4. 数值型数据用上面这个可以返回
    5. =MATCH(CHAR(1),B:B,-1)
    6. 查找目标列中大于或等于ASCII码最小字符的值，从而返回最后一个非空单元格的位置。
    7. =LOOKUP(9E307,A:A)
    8. 如果最后一个非空单元格为数值型的函数，则能够返回
    9. =LOOKUP(CHAR(65536),B:B)
    10. 最后一个非空单元格为文本型数据，用上面的公式。
类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(88) 《Excel实战技巧精粹》技巧222-226【2010-3-9】2010年03月10日 星期三 00:08
  1. 求第n大的不重复值
    1. {LARGE(IF(FREQUENCY(A2:A11,A2:A11),A2:A11),4)}
    2. {=LARGE(IF(MATCH(A2:A11,A2:A11,0)=ROW(INDIRECT("1:10")),A2:A11),4)}

    第21章   查找与引用

  2. 根据首行（列）查找记录
    1. VLOOKUP,HLOOKUP
    2. 搜索首列（首行）中满足条件的数据，并按照指定的列号（行号）返回查找区域中的值；
    3. 查询条件与查询范围的首列/行需要保持数字格式一致。
  3. 多条件区间判断取值
    1. 使用IF函数
    2. VLOOKUP函数
    3. 数组vlookup
  4. 确定查找值是否存在
    1. MATCH函数
    2. COUNTIF函数
  5. 根据行列条件返回结果
    1. INDEX函数
    2. INDEX(array,row_num,column_num)返回数组指定的数值
类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(39) 《Excel实战技巧精粹》技巧216-221【2010-3-7】2010年03月07日 星期日 20:42
  1. 对称剔除极值求平均值
    1. Trimmean(B7:J7,2/COUNTA(B2:J7))
    2. 去除16中的前三后三：=TRIMMEAN(A1:A16,6/16)
  2. 认识FREQUENCY函数
    1. 对数据分段统计、去除重复数据；
    2. FREQUENCY(data_array,bins_array)分别代表数据源和分段点。
    3. {=FREQUENCY(A2:A11,B2:B5)}B2:B5为分段点；
  3. 统计不重复值的数量
    1. COUNTIF:{=SUM(1/COUNTIF(A2:A11,A2:A11))}
    2. =SUM(IF(FREQUENCY(A2:A11,A2:A11)>0,1))
    3. {=SUM(IF(MATCH(A2:A11,A2:A11,)=ROW(2:11)-1,1))}
  4. 单个区域排名
    1. 降序=RANK(A2,A$2:A$11),升序=RANK(A2,A$2:A$11,1)
    2. =COUNTIF(A$2:A$11,">"&A2)+1,=COUNTIF(A$2:A$11,"<"&A2)+1
    3. {=SUM(--(A$2:A$11>A2))+1},{=SUM(--(A$2:A$11
  5. 多个区域排名
    1. rank可以在数据源使用不连续区域，直接使用联合区域排名。
  6. 中国式排名
    1. 并列排名不占用名次
    2. {=SUM(IF(A$2:A$11>A2,1/COUNTIF(A$2:A$11,A$2:A$11)))+1}
    3. {=SUM(--(FREQUENCY(IF(A$2:A$11>=A2,A$2:A$11),A$2:A$11)>0))}
    4. {=SUM(--IF(A$2:A$11>=A2,MATCH(A$2:A$11,A$2:A$11,)=ROW($2:$11)-1))}
类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(44) 《Excel实战技巧精粹》技巧211-215【2010-3-6】2010年03月07日 星期日 04:13
  1. 分级累进求和
    1. 按判断级别，人后按照累进求和公式计算
  2. 自动筛选、隐藏、组合下的统计
    1. 可见单元格区域的求和、计数等运算需要使用SUBTOTAL函数；
    2. 自适应编号：=sumtotal(3,B$2:B2)
    3. 只对隐藏行有效，对隐藏列无效
  3. 取得各科成绩的最高分
    1. MAX函数；
    2. SUBTOTAL函数：{=SUBTOTAL(4,OFFSET($A$2:$A$11,,COLUMN($B$2:$F$12)))},利用OFFSET函数产生三维引用
  4. 统计不及格人数
    1. OFFSET函数产生各行相对独立的引用，通过countif函数分别对各行不及格升级统计，最后使用sum函数进行计数。
  5. 指定条件下求平均值
    1. 条件求和和条件计数的商：利用countif和sumif函数
    2. AVERAGE数组公式：{AVERAGE(IF(B2:B11<>"",B2:B11))}
类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(48) 《Excel实战技巧精粹》技巧206-210【2010-3-5】2010年03月05日 星期五 23:50
  1. 日记账中的余额累计
    1. E2=SUM(C$2:C2)-SUM(D$2:D2)，巧妙使用单元格的混合引用。
  2. 数据表动态小计
    1. 利用sumif的条件汇总功能，通过sum-sumif*2实现动态小计
    2. =SUM(C$2:C4)-SUMIF($A$2:$A4,$A5,C$2:C4)*2
  3. SUM数组公式计数与求和
    1. SUM和SUMPRODUCT函数进行数组运算也可以实现多条件计数和求和；
    2. 数组公式的单元格引用不能使用整列；
    3. 条件计数：{=SUM(IF(A2:A11>0,1))};{=SUM(--(A2:A11>0))}
    4. 多条件求和：sum数组公式在多条件求和上臂sumif更方便，{=SUM((条件1)*(条件2)*……*(求和区域))}
    5. 与COUNTIF,SUMIF的区别：利用SUM数组公式进行技术求和的原理是数组相乘，因此各个条件数组、求和区域必须具有相同尺寸
  4. SUM与SUMPRODUCT函数的区别
    1. SUMPRODUCT用户将给定的几组数组对应的元素相乘，并返回乘积之和。
    2. =SUMPRODUCT((条件1)*(条件2)*……*(条件n),求和区域)
  5. 部门评价登记转换
    1. 借助SUMIF函数将部门评分等级转为分数，在通过条件判断通过sumproduct汇总，注意数组尺寸相同即可。

     

     

类别：《excel实战技巧精粹》学习笔记| 评论(2) | 浏览(98) 《Excel实战技巧精粹》技巧201-205【2010-3-4】2010年03月05日 星期五 01:34
  1. 单字段多条件计数
    1. 使用countif函数，或者sum+countif函数
  2. 动态统计及格人数
    1. 动态引用主要依靠OFFSET函数结合match函数进行，在使用counif对分数进行计数。
  3. 认识SUMIF函数
    1. SUMIF(range,criteria,sum_range)
    2. 参数sum_range省略则指与range区域相同区域，简写为区域左上角单元格，则自动填充为与range相同
    3. 根据不重复关键词查询结果
  4. 单字段多条件求和
    1. sum和sumif条件相结合
  5. 使用通配符模糊求和
    1. 首要任务是查找

     

类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(42) 《Excel实战技巧精粹》技巧190-200【2010-3-3】2010年03月04日 星期四 00:07
  1. 数值的修约
    1. 按有效位数；
    2. 以0.5为单位修约与以0.2为单位修约；
  2. 产生规定范围内的随机数
    1. RAND(),RANDBETWEEN(bottom,top)（ROUNDBETWEEN需要分析数据库）;
    2. a-b之间的随机实数：RAND()*(b-a)+a;
    3. 1-10随机整数：RANDBETWEEN(a,b)，=INT(RAND()*9.0001+1)
    4. 不重复随机数：利用countif对于已产生的随机数进行过滤，再通过rand在产生随机数中取得数值，用数组公式
  3. 求解多元一次方程
    1. 分别输入方程式的系数矩阵和值矩阵，生成矩阵的逆{=MINVERSE(系数矩阵)}，得到解{=MMULT(C12:E14,G7:G9)}
  4. 求出成绩最高分
    1. =MAX(MMULT(三列区域，{1,1,1}))；

    第20章 统计求和

  5. 设置目标数据的上下限
    1. IF函数；
    2. MAX,MIN函数：=MIN(上限，MAX(下限，数值本身或计算表达式))。
  6. 统计选定区域数据个数
    1. 不重复数字个数：=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},$A2))
    2. 填写数据检查：=if(counta(A2:D2)=4,"填写完毕",""),=if(COUNTBLANK(A2:D2),"","填写完毕")
  7. 认识COUNIF函数
    1. =COUNTIF(range,criteria),易失性函数，大面积使用速度会变慢。

《Excel实战技巧精粹》技巧188-193【2010-3-2】2010年03月03日 星期三 00:20
  1. 工作日的相关计算
    1. 日期间：=NETWORKDAYS()
    2. 经若干工作日后的日期：workday；
    3. 需要加载分析数据库
  2. 解读时间格式代码
    1. Excel中时间是日期序列号的延伸，为日期整数后的小数；
    2. 冒号间隔默认为时间；
    3. 显示百分之一秒：单元格格式设置为"hh:mm:ss.00"，单元格输入=NOW()，按住F9；
    4. 军事时间："hhmm"。
  3. 将角度显示为度分秒以及相关转换计算
    1. 角度的输入与显示：[h]0m's'';
    2. 角度的转换计算：度数*24变为小数的度数，然后转为弧度等值。

     

    第19章  数学与三角计算

  4. 常用数值舍入计算
    1. 按指定小数位数：四舍五入ROUND(number,num_digits)，全入ROUNDUP,全舍ROUNDDOWN；
    2. 按指定基数倍数舍入：向上全入CEILING(number,signficance)，向下舍去FLOOR(number,significance)；
    3. 数值取整：INT按照小数延绝对值增大方向舍去，TRUNC直接根据制定小数位进行截位，ROUNDDOWN类似。
  5. 按人民币面额估算备钞数
    1. 利用int函数从百元开始计算，超过100的需要多少张，剩余部分先判断是否超过50，然后依次计算。
  6. 余数的妙用
    1. 使用MOD函数；
    2. 判断奇偶：
      1. ISODD,IDEVEN可以判断，需要加载工具分析库；
      2. mod对2求余数，对其返回的1或0进行判断。
    1. 判断星期几：=MOD(A1-1,7)
    2. 循环等差序列：=MOD(COLUMN(A1)-1,$A2)+1,向右向下填充，将按照A列数字产生循环等差序列；
    3. 隔行隔列取数：=SUM((MOD(ROW($A$1:$A$15),2)=1)*$A$1:$A$15)数字2为间隔，各列使用sumproduct和column。
类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(32) 《Excel实战技巧精粹》技巧183-187【2010-3-1】2010年03月01日 星期一 23:55
  1. 将中文日期文本转为日期值
    1. 用text函数将1900-2100的年份以及每年1-366日转为中文小写格式，再利用match函数定位原文本的年份和月、日对应的位置。数组公式。
    2. 定义名称，用substitute函数多次嵌套将可能出现的中文日期替换为规范的文本型日期，再转化数值。多层嵌套需要指定多个名称。
  2. 计算指定间隔的日期
    1. 计算指定间隔后的日期：data(year,month,day)返回特定的日期，month和day具有进位的特性；
    2. 闰年判断：=if(month(date(a1,2,29))=2,"闰年","平年")
  3. 月度、季度相关日期计算
    1. 返回月末、季度末日期：
      1. 本月最后一天：=DATE(YEAR(NOW()),MONTH(NOW())+1,0),=EOMONTH(NOW(),0)
      2. 当月天数：=DAY(DATE(YEAR(NOW()),MONT(NOW())+1,0)),=EOMONTH(NOW(0,0)
      3. EMONTH函数需要打开分析工具库之后才能使用。
      4. 上月月末日期:=TODAY()-DAY(NOW())
      5. 上月天数：=DAY(TODAY()-DAY(TODAY()))
      6. 本季度最后一天日期：=DATE(YEAR(NOW()),CEILING(MONTH(NOW()),3)+1,0))
    1. 每季度止息日期：=DATE(YEAR(A2),CEILING(MONTH(A2)+(DAY(A2)>20),3),20)
    2. 确定一个日期所属的季度、半年度：=TEXT(ROUNDUP(MONTH(A1)/3,0),"[dbnum1]第0季度")，=IF(MONTH(A1)>6,"下半年","上半年")；
  4. 隐秘函数DATEDIF
    1. 用于计算两个日期之间的天数、月数或年数；
    2. 公式格式：DATEDIF(start_date,end_date,unit)，unit代码含义：
      1. "y":时间段中的整年数
      2. "m":整月数
      3. "d":天数
      4. "md":忽略月和年的天数差
      5. "ym":月数差，忽略日和年
      6. "yd":天数差，忽略年
    1. 计算日期间相差的年月日：=TEXT(SUM(DATEDIF(A2,B2,{"y","ym","md"})*{10000,100,1}),"0年00月00日")利用dataif的三个参数计算相差的年月日，然后通过{10000,100,1}组合成数值，最后通过text进行文本组合；
  5. 星期的相关计算
    1. 计算星期几：weekday(A2,2)或者mod(A2-2,7)+1
    2. 最近的星期天：上一个=$A2-WEEKDAY($A2,2),=$A2-MOD($A2-1,7);下一个=$A2+8-WEEKDAY($A2),=$A2+6-MOD($A2-2,7)
    3. 两个日期相距星期几的个数：=((WEEKDAY(B1-n,2)+B2-B1)/7),n为0-6整数，0表示星期天；
    4. 计算某日期是年内第几周：=WEEKNUM(B1,2)，打开分析工具库才能使用WEEKNUM函数
类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(59) 《Excel实战技巧精粹》技巧178-182【2010-2-28】2010年03月01日 星期一 00:41
  1. 人民币金额大写公式
    1. 利用函数if来判断是否有角或分，小写变为大写的函数为text(A1,"[dbnum2]")

    =IF(A5<0,REPLACE(IF(TRUNC(A5)=A5,TEXT(A5,"[DBNum2]")&"元整",IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),"[DBNum2]")&"元"&TEXT(RIGHT(A5),"[DBNum2]")&"角整",TEXT(TRUNC(A5),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A5)),"零",TEXT(LEFT(RIGHT(A5,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A5),"[DBNum2]")&"分")),1,1,"负"),IF(TRUNC(A5)=A5,TEXT(A5,"[DBNum2]")&"元整",IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),"[DBNum2]")&"元"&TEXT(RIGHT(A5),"[DBNum2]")&"角整",TEXT(TRUNC(A5),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A5)),"零",TEXT(LEFT(RIGHT(A5,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A5),"[DBNum2]")&"分")))

    第18章 日期与时间计算

  2. 根据农历生日计算生肖与年份
    1. 生肖只与年份有关，取余后根据余数判断；
    2. =CHOOSE(MOD(YEAR(A3)-4,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")
    3. =MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(A3)-4,12)+1,1)
  3. 解读日期格式代码
    1. 以1900年1月1日为起始的序列值,Excel最大知道到9999-12-31;
    2. 日期输入以-或/为间隔数字.
    3. 三个隐秘的日期格式代码:e(代表year的缩写),b(佛历2为年份),bbbb(佛历4为年份),[dbnum1]yyyy年m月d日为中文小写日期,[dbnum2]yyyy年m月d日为中文大写日期.
    4. 公元前544年为佛历纪元元年.
  4. 日期与数字格式的转换
    1. 文本格式转为日期:
      1. 20080808:=TEXT(A2,"#-00-00");
      2. 2008.8.8:利用替换函数substitute
    1. 将日期转为文本:利用text函数指定要转化成的文本
  5. 英文日期的转换
    1. 标准日期转为英文序数日期:利用前面讲的先判断11,12,13,之后判断1,2,3结尾的,依次组合,月份用mmmm;
    2. 英文月份转为数字:=month(--(A1&1)).
类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(34) 《Excel实战技巧精粹》技巧173-178【2010-2-27】2010年02月28日 星期日 01:23
  1. 格式化数据
    1. 利用TEXT()函数，text的format_text参数可以使用单元格定义的自定义数字格式代码的大部分。
    2. 函数的结果是文本。
  2. 解读身份证编码信息
    1. 提取籍贯：编码与省份建立一个索引表，然后使用vlookup查找身份证的前六位；
    2. 身份证提取出生日期：截取第七位开始的信息，如果为15位身份证，出生日期前需要加上19；
    3. 提取性别信息：MOD对于身份证号码最后一份对2求余数，余数为1则为男性。
  3. 身份证位数的变换
    1. 18转为15：出生年度减为2位（replace），去除最后的校验码（用left截取前15位）；
    2. 升级：插入19到身份证的第七位前，校验码使用国家质量技术监督局的公民身份证号码中的校验码计算规则。
  4. 金额数字分列
    1. Round()数值取两位小数*100去除整个数值，使用rept根据所需位数与现有位数只差不起空格，得到所需位数的字符串，使用mid函数每次取一位填入单元格

    =MID(REPT(" ",11-LEN(ROUND($A2,2)*100)+($A2=""))&ROUND($A2,2)*100,COLUMN(A:A),1)

  5. 数字转换为英文序数
    1. 11，12，13添加th；除此之外1加st，2nd,3rd；其他都加th；
    2. 使用if挨个条件判断，right函数取末位判断。
类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(33) 《Excel实战技巧精粹》技巧168-172【2010-2-26】2010年02月27日 星期六 00:50
  1. 查找字符
    1. 主要使用的函数为FIND，SEARCH；
    2. find支持大小写区分但不支持通配符，seach不支持大小写区分但是支持通配符；
  2. 计算字符出现的次数
    1. 在某单元格内出现的次数：（单元格字符个数-使用substitute函数替换该字符后剩余的字符个数）/查找的字符个数；
    2. 包含某字符的单元格个数：countif()或数组公式{=COUNT(FIND("助",B3:B7))}
  3. 提取字符串
    1. LFET,RIGHT,MID；
    2. mid可以返回从某位置开始之后的所有字符。
    3. 从完整路径中提取文件名
      1. 利用mid，substitute找到最后一个\的位置替换为/，然后将/之前的内容清除；
      2. 也可使用replace函数。
    1. 文本和数字分离
      1. 用len和substitute计算数字个数来作为自取字符长度；
      2. 用find在单元格找到第一个数字并减1作为截取字符长度，然后进行截取；
      3. 以上公式只能在数字和文本没有混杂在一起的时候使用才有效。
    1. 提取连续的汉字
      1. 固定在左或右的连续汉字：利用双字节文本函数和单字节函数计算的差值来作为截取字符的长度，lenb函数返回汉字，使用公式=LEFT(A4,LENB(A4)-LEN(A4))；
      2. 去得任意位置的连续汉字：非全角字符ASCII编码在0-255之间的数值，通过match与code结合定位到第一个全角字符位置，取得汉字个数，然后提取汉字；
  4. 文本合并符号的妙用
    1. 使用&；
    2. 连接名字与姓氏；
    3. 连接注释文字和计算结果；
    4. 去除引用空单元格返回的0；
    5. 合并计算条件。
  5. 重复记录自动加编号
    1. 统计A列中每条记录的重复次数，通过重复记录统计的目标区域的不断缩小，计算出相应的编码进行添加；
    2. =A2&IF(COUNTIF($A$2:$A$10,A2)>!,COUNTIF(A$2:A2,A2),"")
类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(38) 《Excel实战技巧精粹》技巧156-167【2010-2-25】2010年02月26日 星期五 00:30
  1. 理解多重计算及数组公式
    1. 只要输入公式按下Ctrl+Shift+Enter结束，就是数组公式，只是一个名称代号。
    2. 按下组合键，相当于告诉Excel你要求执行多重计算。
  2. 多单元格数组公式
    1. 能产生多个计算结果并在多个单元格显示出来的单一数组公式；
    2. 多单元格数组公式需要用与结果行列数一致的单元格区域才能显示全部计算结果；
    3. 数组公式的优势：
      1. 保证公式同一性，创建公式后，区域所有单元格都不能单独编辑；
      2. 较大范围能生成规律数据。
  3. 数组的转置和变换
    1. 借助TRANSPOST函数完成。
    2. 变换：CHOOSE以及IF函数。如{=IF({1,2},B2:B5,A2:A5)};
  4. 为何不能用AND、OR替代*、+
    1. 数组公式中*和+可以替换and和or，但反之不可以，因为and和or返回的是true和false。

     

    第五篇  函数技巧

    第16章  信息处理

  5. 屏蔽公式返回的错误值
    1. ISERR(),ISERROR(),ISNA(),ISREF()；
    2. ISERR()除了不能判断#N/A型错误值之外，与ISERROR()相同。
    3. =IF(ISERROR(原公式),"",原公式)。
  6. 取得单元格信息
    1. CELL函数功能强大，能够返回区域左上角单元格的格式、位置或内容，以及文件所在路径等信息；
    2. =CELL("format",A1):返回A1单元格设置的数字格式；filename返回单元格所在工作表文件名、路径、文件类型等，需要保存后才能正确返回；contents返回单元格内容；
    3. =CELL("contents")返回最近一个更改的单元格信息，单元格发生改变要F9重新计算或激活公示所在单元格。
  7. 取得当前工作表表名
    1. 将CELL函数、NOW函数与定义名称方法结合起来使用，常常能完成一些较高难度的计算任务；
    2. 定义名称shtname：=CELL("filename"，!A1)&T(NOW()),全选工作表，在A1输入=shtname。
    3. !A1是名称定义中才能使用，加上&T(NOW())是利用易失性函数NOW()来实现自动更新。
  8. 转换数值
    1. 信息函数包含一个可将参数转化为数值的函数——N函数，用于：
      1. 过滤文本数据；
      2. 将逻辑值转换成数值；
      3. 在三维引用中生成内存数组；

     

    第17章  文本处理

  9. 字符转换技巧三则
    1. 英文字母大小写转换：UPPER()，LOWER()，PROPER()分别是转为大写，转为小写，首字母大写。
    2. 字符与ANSI字符代码转换：CODE(),可见将字符转为ANSI码，CHAR()将ANSI转为字符；
    3. 半角与全角的转换：ASC(), WIDECHAR()分别为全角返回半角，半角返回全角。
  10. 重复文本技巧二则
    1. 函数REPT(text,number_times)用来将特定字符按给定次数重复填充；
    2. 制作文本直方图：辅助列作出百分比数据，然后利用rept函数填充黑框。
  11. 字符串比较级清理垃圾字符
    1. Exat()区分大小写，TRIM()消除多余空格，CLEAN()可以清理垃圾字符。
  12. 替换字符
    1. 除使用Excel替换功能外，还可以使用替换函数SUBSTITUTE(),REPLACE()；
    2. replace可以在指定位置插入、清除字符，substitute可以用多重嵌套进行替换。

     

类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(36) 《Excel实战技巧精粹》技巧151-155【2010-2-24】2010年02月25日 星期四 01:03
  1. 运算符号
    1. 算术、比较、文本和引用运算符；
    2. 运算符优先顺序：引用运算符-算术(-)-算术(%)-算术(^)-算术(*和/)-算术(+和-)-文本运算符(&)-比较运算符；
    3. “- -”减负运算：逻辑值转化为数值，将文本型数字转化为数值型；
    4. 运算符巧替逻辑函数：0=FALSE,1=TRUE，所以可以在if中使用*和+来代替AND和OR函数；
    5. 通配符：*，？，~（用于解除*？~的通配性）。
  2. 函数参数的处理技巧
    1. 省略；
    2. 简写：逻辑值中0=False等；
  3. 函数公式的限制与突破
    1. 限制：
      1. 公式长度不超过1024个字符；
      2. 嵌套不超过7层
      3. 参数不超过30个。
    1. 突破嵌套限制：嵌套多于7层时，每7层建立一个名称，名称引用公式，之后的公式引用此名称；
    2. 突破参数限制：将参数多加上一层括号。
  4. 函数的易失性
    1. 易失性函数：now, today, rand, cell, offset, countif, sumif, indirect, info, randbetween；

    第15章 数组公式入门

  5. 理解数组
    1. 数组：由文本、数值、日期、逻辑、错误值等元素组成的集合，不允许存在长度不等的行或列；
    2. 常量数组：可包含不同的数据类型；
    3. 维数与尺寸：excel不支持显示三维数组，但完全支持三维以及多维数组的计算。一维数组可分为垂直数组（或列数组）和水平数组（或行数组），列数组用分号";"间隔，行数组用","间隔。常量数组只具有行列两个方向；
    4. 内存数组：数组通过数组公式运算后所生成的新数组通常成为“内存数组”。
    5. 数组公式比较难理解，现实生活中还没用到过，先过一遍。
类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(33) 《Excel实战技巧精粹》技巧144-150【2010-2-23】2010年02月24日 星期三 01:02
  1. 函数公式查错与监视
    1. 错误的类型：

    #####：列不够宽、负日期、负时间出现错误

    #VALUE!：参数或操作数类型错误；

    #DIV/0!：数字被0除，出现错误；

    #NAME?：未识别公式中的文本；、

    #N/A：数值对函数/公式不可用；

    #REF!：单元格引用无效；

    #NUM!：使用无效数字指；

    #NULL!：指定并不相交的俩区域的交点时出现错误。

    1. 工具-选项-错误检查-允许后台错误检查，当单元格内公式错误时，单元格左上角会出现绿色小三角，单击出现的智能按钮，可选择显示计算步骤。
    2. 公式审核工具条可逐个检查出错单元格，追踪错误。
    3. 监视窗口：创建了链接到其他工作簿数据的电子表格时候，可利用监视窗口查看单元格及其中公式
  2. 分步查看公式计算结果
    1. 公式求值：选中有公式的单元格，单击公式审核工具栏的公式求值-求值；
    2. 神奇的F9键：
      1. 选中单元格公式的需要显示结果的部分，按下F9即可显示计算结果；
      2. 可将单元格引用转换为常量数组；
  3. 保护和收藏工作表中的公式
    1. F5调出定位对话框，定位条件-公式。按Ctrl+1调出单元格格式-保护-勾选锁定和隐藏，单击工具-保护-保护工作表。

     

    第14章  函数基础

  4. 单元格的引用方法
    1. A1引用样式：行标为数字，列标为字母；
    2. R1C!引用样式：工具-选项-勾选RIC1引用样式，这时候的应用更能体现单元格坐标的概念；
    3. 三维引用：引用区域不在二维平面上，如=SUM(Sheet1:Sheet3!A1:C5)；
  5. 快速切换引用类型
    1. R1C1样式的相对符[]里面能够用数字标明相对于当前单元格的位置；
    2. 快速切换按F4键
  6. 数据类型与排序规则
    1. 数值小于文本，文本小于逻辑值，错误值不参与排序；
    2. 数值运算中，true=1，false=0。逻辑判断中，0=false，所有非0数值均=true
    3. 文本转为数字：=A1*1,=A1/1,=A1+0,=A1-0,=--A1,=VALUE(A1)；
    4. 通过left，mid，text等文本函数运算后的结果均为文本类，需要转换才能带入计算中；
    5. 真空与假空的区别，=isblank(A1)可鉴别真空。
  7. 逻辑关系判断
    1. AND：与；
    2. OR：或；
    3. NOT：非，逻辑值反转；
    4. 嵌套：直接带入参数；
    5. 运算：TRUE*1=1*1=1(TRUE+0=1), FALSE*1=0*1=0(FALSE+0=0), TRUE+TURE=1+1=2, TRUE+FALSE=1+0=1, TRUE*TRUE=1*1=1, TRUE*FALSE=1*0=0。
类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(35) 《Excel实战技巧精粹》技巧132-143【2010-2-22】2010年02月22日 星期一 22:41

    第12章  分类汇总与数据透视

  1. 分类汇总结果的复制
    1. 使用分类汇总前需要将进行分类的字段进行排序；
    2. 数据-分类汇总-选择分类字段-选择汇总方式-选定汇总项，勾选汇总结果显示在数据下方；
    3. 复制汇总结果：直接复制会将明细也一起复制，选择区域，按F5调出定位对话框，单击定位条件，选择可见单元格，然后进行复制、粘贴操作。也可以复制到到记事本，然后再从记事本复制回来。
  2. 多字段分类汇总
    1. 多次执行分类汇总功能即可。
    2. 数据量、汇总要求较少较简单时候较为有用，数据列表较为庞大的时候最好的工具是数据透视表。
  3. 多层次的数据浏览
    1. 选中区域，数据-组及分组显示-自动建立分级显示，不仅可以横向分级，也可以进行纵向分级。
    2. 也可以手动进行分级显示，选中要组合的区域，数据-组与分级显示-组合，选择行或者列。
  4. 在受保护的工作表中调整分级显示视图
    1. 如果希望保护工作表能够调整分级显示视图，需要借助宏代码
    2. Alt+F11打开VBA编辑，Ctrl+R打开工程资源管理窗口，双击ThisWorkbook，输入代码

    Private Sub Workbook_Open()

        Worksheets("sheet1").Protect Password:="pwd", userInterFaceonly:=True

        Worksheets("sheet1").EnableAutoFilter = True

    End Sub

    1. 再次打开表格，表格已经收到保护，同时分级显示调整功能不受影响。可以修改工作表名称和密码(pwd)。
  5. 【待完成】用二维表创建数据透视表
  6. 【待完成】组合数据透视表内日期项
  7. 【待完成】快速统计重复项目
  8. 【待完成】在数据透视表中添加计算项
  9. 【待完成】自动刷新数据透视表

    【数据透视表部分：对于其中的各种关系比较吃力，已经报了个数据透视表的培训班，上完之后继续练习这部分内容。】

     

    第13章  函数介绍

  10. 慧眼识函数
    1. Excel函数分为逻辑、信息、日期与时间、数学与三角计算、统计、查找与引用、数据库、文本、财务、工程等类别。此外还有宏表函数、扩展函数、外部函数等。
  11. 函数公式的输入和编辑
    1. 使用工具栏按钮输入函数：求和、平均值、技术、最大值、最小值、平均值；
    2. 使用插入函数向导：单击fX或者shift+f3，输入函数时候会有参数提示，但是个别函数的参数提示是错误的；
    3. 手工输入；
    4. 公式的编辑和复制：填充抦或复制粘贴。
  12. 函数工具提示
    1. 设置函数工具提示选项：工具-选项-常规-函数工具提示；
    2. 在单元格显示函数完整语法：输入=函数名后，按下Ctrl+Shift+A可得到完整语法提示；
    3. 阅读使用函数帮助文件。
类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(40) 《Excel实战技巧精粹》技巧111-131【2010-2-21】2010年02月21日 星期日 21:10

    第10章 打印

  1. 控制需要打印的内容
    1. 打印整个工作簿：文件-打印-打印内容-整个工作簿；
    2. 打印部分工作表：按下Ctrl选择工作表标签，然后点击打印，如果要打印连续页码需要在选定工作表后在页眉或者页脚中打印页次，然后再打印；
    3. 打印部分工作表：
      1. 设置的打印区域：文件-打印区域-设置打印区域；
      2. 文件-打印-打印内容-选定区域；
    1. 打印特定页面：文件-打印-打印内容-打印范围-输入起始页码和结束页码。
  2. 重复打印顶端标题行和左侧标题行
    1. 文件-页面设置-工作表-顶端标题行，制定顶部的标题行。左端标题行-制定左侧要重复的标题行；
    2. 还可以使用定义名称的方法在工作表中直接快速指定。
  3. 在同一页上打印不连续区域
    1. 利用Excel的摄影功能即可。
  4. 控制打印输入的比例
    1. 文件-页面设置-页面-调整缩放比例或者直接单击调整为，输入指定的页宽和页高。
  5. 克隆工作表的页面设置
    1. 按下Ctrl选择工作表标签，文件-页面设置进行设置；
    2. 如果要进行克隆：先选定已经设置好的工作表，按Ctrl逐个单击要进行页面设置的工作表标签，文件-页面蛇者，不做改动，直接单击确定。
    3. 无论对多少工作表进行页面设置，页面设置里面的打印区域和打印标题都是不可设置。
  6. 在报表每一页打印公司LOGO
    1. 加图片到页眉或页脚。
  7. 在页眉或页脚增加分割线
    1. 使用画图花一条线，加入此图片到页眉或页脚中
    2. 注意加入图片后要有加入一个回车。
  8. Excel的双面打印
    1. excel本身不能奇偶页分开打印，使用打印机设置可以做到
    2. 文件-打印-打印内容-打印机属性，输出设置为双面打印。
    3. 如果打印机没有此项功能，那么就编辑宏吧，Alt+F11打开编辑器，插入-模块，输入

    Sub DoublePring()

       

        Dim TotalPage As Integer

        Dim PrintPage As Integer

        Dim bOK

       

        TotalPage = Application.ExecuteExcel4Macro("get.document(50)")

       

        For PrintPage = TotalPage - IIf(TotalPage Mod 2 = 0, 1, 0) To 1 Step -2

            ActiveSheet.PrintOut from:=PrintPage, To:=PrintPage

           

        Next

       

        If bOK = vbOK Then

            For PrintPage = 2 To TotalPage Step 2

                ActiveSheet.PrintOut from:=PrintPage, To:=PrintPage

            Next

        End If

           

       

    End Sub

    1. 按F5运行。
  9. 在Excel中定时打印
    1. 利用宏，Alt+F11打开编辑器，插入-模块，输入

    Sub OntimePrint()

        ActiveSheet.PrintOut Copies:=1, Collate:=True

    End Sub

    1. Ctrl+R调出工程资源窗口，双击ThisWorkbook，在代码窗口输入

    Private Sub Workbook_Open()

        Application.OnTime TimeValues("15:30:00"), "OntimePrint"

    End Sub

    1. 保存关闭工作簿，然后再次打开，每天15：30工作表会自动打印，但是需要保证计算机当前设置的默认打印机可以使用，此工作簿一直处于打开状态。
  10. 虚拟打印
    1. 使用微软的虚拟打印组件，Microsoft Office Document Image Writer，默认随office一起安装，如果没有，执行office的添加删除组件程序安装，位于office工具中；
    2. 打印时候，打印内容会存储为mdi格式

    第三篇 数据分析

    第11章 排序与筛选

  11. 对超过3列的数据排序
    1. office2003只支持3个关键词排序，2007已经解决此问题。
  12. 按照特定的顺序排序
    1. 如按照职称排序，要告知excel顺序，需要使用自定义序列；
    2. 设置序列后，数据-排序-主要关键字-升序，排序选项-选择设置的自定义序列。
  13. 按比划排序
    1. Excel默认是按照字母排序；
    2. 数据-排序-选项-笔画排序。
  14. 按行来排序
    1. 数据-排序-选项-按行排序。
    2. 按行排序不能选择标题列，否则会将标题列也进行排序
  15. 按字符数量排序
    1. 加入辅助列，计算单元格字数
    2. 计算字数使用LEN函数。
  16. 按颜色排序或筛选
    1. Excel2003需要借助辅助列和宏表函数，2007已经解决此问题。
    2. 辅助列单元格定义名称为colour1，然后引用位置使用公式=Get.cell(63,A2)+RAND()*0，该单元使用公式=colour1。
    3. 只能计算手工设置的单元格颜色，条件格式的无法计算。计算颜色的宏表函数无法自动重算，更改颜色需要重新执行公式。
    4. 07可以单元格底色，字体颜色进行排序。
  17. 随机排序
    1. 使用辅助列，辅助列使用公式=rand()。
  18. 排序字幕与数字的混合内容
    1. 如果希望先比较字母大小，然后比较数字大小，需要对数据进行修改.
    2. 设字母1位，数字为3位，辅助列B1输入公式=LEFT(A1,1) & RIGHT("000" & RIGHT(A1,LEN(A1)-1),3)。
  19. 返回排序前的表格
    1. 排序前插入辅助列，按照顺序填入数字。
  20. 解决常见的排序故障
    1. 没有正确选择数据区域，自动选择的区域包含空格：排序结果不正确；
    2. 内存不足的情况：增加内存、优化电脑、减少排序的数据区域；
    3. 数据区域中包含有格式化为文本的数字：将文本型数字转换为数值型数字；
    4. Excel提示“不同的单元格格式太多”：简化工作簿格式，使用统一字体、图案与数字格式；
    5. 排序区域包含合并单元格：取消合并单元格；
  21. 在受保护的工作表中使用自动筛选

 

第9章 数据有效性绝技

  1. 在单元格中创建下拉列表
    1. 数据有效性：数据-有效性；
  1. 另类的批注
    1. 数据-有效性-输入信息-勾选选定单元格时显示输入信息，输入标题和输入信息。
    2. 输入信息时候会有相应的提示。
  1. 快速切换有效性的来源
    1. 设三个区段依次排开，指定第一个数据来源的名称为List.1，数据-有效性-设置-序列-来源-输入=OFFSET(List.1,,A1-1)，在A1中输入1，2，3则会引用不同的数据来源。
  1. 动态源的数据有效性
    1. 诀窍是数据有效性的来源设置中引用动态名称；
  1. 只能录入某种特征的数据
    1. 数据有效性中也可以输入公式；
    2. 如要求输入4-7位的，首位是6，第3位是8的数值，可以在公式框中输入=AND(A1*1,LEN(A1)>3,LEN(A1)<8,SEARCH("6?8",A1)=1).
  1. 限制重复值的录入
    1. 针对单个关键词限制重复值录入：在区域中数值唯一存在，选中A2:B11区域，有效性-设置-自定义-公式-输入=COUNTIF($A$2:$B$11,A2)=1;
    2. 针对双关键字限制重复值：设数据输入区域为A2:C20，选中区域，有效性-自定义-输入=SUM(N($A2&"|"&$B2=$A$2:$A$20&"|"&$B$2:$B$20))=1。
    3. 上述公式实际是一个数组公式，数组公式不用在工作表中是不带花括号的。
  1. 杜绝负数库存
    1. 设销售表的B2:B10设置有效性，禁止输入大于库存表中库存的销售量；
    2. 库存表的A1:B11定义名称为KC，选定销售表的B2:B10，有效性-自定义-输入=B2<=VLOOKUP(A2,KC,2,0).
    3. 如需要增加不允许录入负数的显示，将公式写成=AND(B2<=VLOOKUP(A2,KC,2,0),V2>=0)
  1. 强制序时录入
    1. 新录入数据的日期不允许早于已有记录的最大日期，设日期区域为A2:A100
    2. 方法1：选定区域，Ctrl+1调出单元格格式对话框-数字-日期，数据-有效性-自定义-输入=N(A2)>=N(A1);
    3. 方法2：设为日期格式后，数据-有效性-日期-大于或等于，在开始日期中输入=MAX($A$2:$A2).
  1. 多级选择录入
    1. 设城市列表工作表中的A列到G列是中国行政区域资料；
    2. 城市列表工作表中添加名称City和Province，分别引用$A$2，$A$1:$G$1
    3. 在多级选择工作表中，选中A2到A11，数据-有效性-设置-序列-输入=Province，B2到B11的有效性序列输入=OFFSET(City,,MATCH($A2,Province,)-1,COUNTA(OFFSET(City,,MATCH($A2,Province,)-1,65535)))。

类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(37) 《Excel实战技巧精粹》技巧88-110（1）【2010-2-20】2010年02月20日 星期六 23:37

    第8章 活用条件格式

  1. 美丽的数据区域
    1. 国际象棋棋盘式底纹：选择第一列数据，格式-条件格式-条件1-公式，文本框输入=MOD(ROW()+COLUMN(),2),单击格式-图案后选择格式。该条件格式判断行号和列号值和除以2的余数是否为0，如果不为0，说明行与列的奇偶性不同，则进行填充。上面公式也可写作=MOD(ROW()+COLUMN(),2)<>0.
    2. 依据以上思路还可以写双色国际象棋棋盘式底纹：添加一个条件，选择公式项，输入=MOD(ROW(),COLUMN(),2)=0，单击格式-团选择底色，双色设置完成。
    3. 奇偶行不同底纹：格式-条件格式，公式项中输入=MOD(ROW(),2)，设置格式即可。
    4. 每三行：=MOD(ROW(),3)=1;
    5. 奇偶列：=MOD(COLUMN(),2)
  2. 永恒的间隔条纹
    1. 生成间隔条纹后，如果进行了筛选，条纹会被破坏；
    2. 选中区域，设置条件1，公式=MOD(SUBTOTAL(3,A$2:A2),2)=0,选择一种格式。条件2，公式=MOD(SUBTOTAL(3,A$2:A2),2)=1，设置另一种格式。
    3. 这种设置将不再受到自动筛选的影响
  3. 快速比较不同区域的数值（一）
    1. 校验数据：源数据A2:B21，校验数据D2:E21。选定D2:E21，格式-条件格式，条件1-单元格数值-不等于-输入=A2，设置格式。
    2. 不匹配数据将以特殊颜色标记出来。
  4. 快速比较不同区域的数值
    1. 位置不是一一对应的数据比对方法
    2. 选定源数据区域A2:A11，格式-单元格格式，条件1-公式-输入=OR(EXACT(A2,B$2:B$11))=FALSE
    3. EXACT函数用来比对两个字符创是否完全相同，若相同返回true
    4. 使用以下三个公式同样可以

    =ISNA(MATCH(A2,B$2:B$11))

    =ISNA(VLOOKUP(A2,B$2:Bz$11,))

    =NOT(OR(A2=B$2:B$11))

    1. CONTIF函数会在字符型数字长度超过15位时候，从16开始的数字按照0计算，可以用一下方法强制COUNTIF使用文本方式计数 =COUNTIF(B$3,B$12,A3&"*")。
  5. 自动醒目的小计
    1. 表格中经常有小计的列或者行
    2. 选中区域，格式-条件格式-条件1-公式-输入=(A1="小计")+(A$1="小计")>0，设置格式。
    3. 上面公式也可以写成=($A1="小计")+(A$1="小计")，=OR(($A1="小计"),(A$1="小计"))。
  6. 在工作日里中突显周休日
    1. 选中日期区域，格式-条件格式-条件1-公式-输入=WEEKDAY(A2,2)>5。
  7. 突显活动单元格的坐标
    1. 利用条件格式和宏，突显坐标，方便在数据量较大的时候查看；
    2. 假设A1:F10是需要输入数据的表哥区域，选中区域，格式-条件格式-条件1-公式-输入=(ROW()=CELL("ROW"))+(COLUMN()=CELL("COL"))。右键工作表标签，输入

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

        Calculate

    End Sub

    1. 以上步骤之后，激活某一单元格时候，该单元格所在行与列都会突出显示。
    2. CELL函数它返回某一引用区域左上角单元格的格式、位置和内容等信息，完整语法是CELL(info_type,reference).
  8. 生日提醒
    1. 假设有一份员工生日记录表格，A列为姓名，B列出生日期；
    2. 选中A2:B20区域，格式-条件格式-条件1-公式-输入=ABS(DATE(YEAR(TODAY()),MONTH($B2),DAY($b2))-TODAY())<=7，设置格式。
  9. 特殊数值提醒
    1. 一般用于成绩表等；
    2. 条件1-单元格数值-大于或等于-输入90，设置格式，条件2-单元格数值-小于或等于-输入60，设置格式。
  10. 标记重复值
    1. 如果有重复值则标记出来，如输入一些编号，唯一名称等
    2. 选定A列，格式-条件格式-条件1-公式-输入=COUNTIF(A1:A$2,A1)>1，设置格式。
  11. 标记含有公式的单元格
    1. 单击A1，插入-名称-定义-定义名称-名称-输入IsFormula，引用位置输入=GET.CELL(48,A1).
    2. 格式-条件格式-条件1-公式-输入=IsFormula，设置格式。
  12. 标记匹配目标数据的单元格
    1. 设第一行为目标值B1:H1，B3:H10为实验数据；
    2. 格式-条件格式-条件1-公式-输入=MATCH(B3,$B$1:$H$1,0)，设置格式。
    3. 上述公式也可以改为=COUNTIF($B$1:$H$2,B3)，或者=HLOOKUP(B3,$B$1:$H$1,1,0)
  13. 用8种颜色区分不同数值
    1. 数值保留两位小数，小于90/80/70/60/50各为一种颜色，文本则为黑色，错误值为淡蓝色；
    2. Ctrl+1调出单元格格式对话框，自定义数字格式代码：[颜色 7][<80]0.00;[颜色 43][<90]0.00;[颜色16]0.00;[黑色]@。
    3. 格式-条件格式-条件1-单元格数值-小于-输入50，设置红色。条件2-单元格数值-小于-输入60，设置蓝色。条件3-单元格数值-小于-输入70，设置紫罗兰。
  14. 无限条件的条件格式
    1. 03的Excel只支持三个条件的条件格式，07已经解决此问题，因为是VBA代码操作，暂时绕过

再次说，百度非常贱，发个文章非得拆成N段。。。。

类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(80) 2010-2-19：《Excel实战技巧精粹》技巧61-87（2）2010年02月20日 星期六 02:03
  1. 工作表背景图片的妙用
    1. 格式-工作表-背景，可以插入背景；
    2. 为了工作表内容的和谐，可关闭网格线，工具-选项-视图-清楚网格线复选框；
    3. 默认情况下，背景图片平铺工作表，无法打印出来；
    4. 只在特定单元格区域中显示背景：插入背景后，ctrl+a叙安全，按ctrl+1调出单元格格式对话框，在图案选项卡中选择单元格底色为白色，选定需要背景的单元格区域，设置单元格底纹为无。
    5. 打印背景：使用Excel的摄影功能把需要打印的区域复制为链接图片贴到空白工作表中，右键连接图片，选择设置图片格式，在颜色与线条-填充-颜色-填充效果-选择图片，选取要打印出来的图片背景
  1. 定义名称的3种方法
    1. 插入名称：选择好区域，插入-名称-定义（或者ctrl+3组合键）输入名称；
    2. 如果要修改名称，需要添加新名称，删除就名称（Excel2007可以直接进行修改）；
    3. 使用名称框：选择区域，直接在名称框中输入要定义的名称；
    4. 制定名称：选择区域，单击插入-名称-制定（ctrl+shift+f3组合键），在指定名称对话框中勾选首行复选框（Excel2007没有此功能）。
  1. 名称命名的为与不为
    1. 名称不能以数字开头；
    2. 名称不能与单元格地址相同；
    3. 不能以字幕R,C,r,c作为名称，因为他们在R1C1引用样式中表示行，列；
    4. 不能包含空格；
    5. 不能使用出下划线，点，反斜线以外的其他符号，允许问号，但不能作为开头；
    6. 不能超过255个字符；
    7. 不区分大小写；
    8. 特殊含义的名称
      1. Print_Titles：当前工作表打印的顶端标题行和左端标题行
      2. Print_Area:工作表的打印区域。
  1. 在名称中使用常量与函数
    1. 名称并不仅仅是单元格区域的一个容易记忆的名字，名称完全可以理解为一个有名字的公式，创建名称实质上就是常见命名公式；
    2. 名称中不但能够使用单元格引用还能使用常量与函数；
    3. 使用常量：
      1. 税率等常量：插入-名称-定义（Ctrl+F3），在引用位置输入=常量。以后使用时可以直接调用该常量，常量变动时候直接修改名称即可；
      2. 经常使用的汉字，如公司名称等，可以创建BUPT，引用位置“=北京邮电大学”；
    1. 使用函数：将名称定义为一个公式，在使用时直接调用，如求总成绩，定义名称为总成绩，引用位置“=sum(求和位置)”；
  1. 名称的作用范围
    1. 默认情况，所有名称能够在工作簿的任何一张工作表使用；
    2. 每个工作表使用的名称成为工作表级名称或局部性名称；
    3. 创建工作表级名称：命名格式为工作表名称+感叹号+名称；
    4. 当前工作表使用该名称可以不加表名称，其他工作表引用时候需要使用完整名称。
  1. 编辑名称引用
    1. 引用位置文本框默认处于指向模式，此时箭头的作用实在工作表选择区域而不是移动光标；
    2. 想要使用箭头的作用，需要把光标定位到引用位置文本框，按F2切换到编辑模式即可
  1. 创建动态名称
    1. 利用OFFSET函数与CONTA函数组合，可以创建动态名称，能够实现对一个未知大小区域的引用，如表哥行数不断随着记录追加而增多；
    2. 插入-名称-定义，引用位置输入格式如下 =OFFSET(Sheet1!绝对应用第一条记录的单元格,,,COUNTA(Sheet1!绝对引用名称所在列)-1)，减去1是要去除标题行；
    3. 只能计算不间断的连续数据。
  1. 图片自动更新
    1. 使用动态名称与ActiveX空间，能轻松实现图片自动更新，以职员资料表为例；
    2. 含有两张工作表，资料表(B姓名，D照片)，图片表(A姓名，B照片)存储所有支援相片；
    3. 为图片表定义名称 ，pic引用位置=OFFSET(图片!$B$1,MATCH(资料表!$B$1,图片!$A$1:图片!$A$10,0)-1,0)；
    4. 在资料表中选择控件工具箱，选择命令按钮（07成为ActiveX控件），画出符合大小的命令按钮；
    5. 在B1中输入姓名，单击命令按钮，编辑框中输入=pic，照片显示成功；
    6. 使相片更好的被 单元格容纳，绘图工具栏-自动靠齐-对齐网格，双击相片-设置图片格式-属性-大小位置随单元格而变。
  1. 快速选择定义名称的区域
    1. 使用名称框，选择下拉菜单中的名称即可；
    2. 使用定位：按F5，定位对话框中显示所有名称（不包括常量名称和函数名称），单击选择即可。
  1. 创建名称表格
    1. 定义了很多名称的时候，可以创建一份表格查看所有名称和引用内容
    2. 插入-名称-粘贴，可以看到所有名称，单击粘贴列表。
  1. 以图形方式查看名称
    1. Excel可以以图形化方式查看定义了名称的区域；
    2. 将视图-显示比例设置小于40%，如39%，Excel会以图形化方式显示名称。
  1. 快速删除所有名称
    1. Excel定义名称对话框只能逐个删除；
    2. Alt+F11打开VBA编辑窗口，插入-模块，输入

Sub DelNames()

    Dim nm As Name

    For Each nm In ThisWorkbook.Names

        nm.Delete

    Next nm

End Sub

  1. 按下F5执行代码，一次性删除所有名称

类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(97) 2010-2-19：《Excel实战技巧精粹》技巧61-87（1）2010年02月20日 星期六 02:01

 

  1. 自动更正的妙用
    1. 除了帮助输入常用词汇之外，还能更正错别字和错别用法；
    2. 工具-自动更正选项；
    3. 自动更正的自定义项目保存在一个acl文件中，位于用户的配置文件中。如C:\Documents and Settings\Administrator\Application Data\Microsoft\Office。复制acl文件给其他用户即可共享自定义更行项目。
    4. 自动更正在所有office组件中都是通用的。
  2. 为文本添加拼音
    1. 选择要添加拼音的单元格，格式-拼音指南-显示或隐藏，开启对拼音的显示。格式-拼音指南-编辑，输入与单元格文本对应的拼音信息，按enter结束；
    2. Excel不能自动生成拼音，也不能给拼音加上声调，甚至无法让拼音与文字自动对齐。
  3. 替换掉所有单元格中的换行符
    1. excel没有特殊字符供用户选择；
    2. Ctrl+H打开替换，alt+小键盘10，虽然看不见输入的东西，但是实际上换行符已经输入进去了。
  4. 模糊查找数据
    1. 通配符和*。
    2. 查找内容本身包含和*，则需要在通配符前面加上~，表示内容为或者*。
  5. 查找替换单元格格式
    1. 查找替换窗口后有格式按钮，“从单元格选择格式”可以从单元格吸取格式
  6. 神奇的选择性粘贴
    1. 粘贴
      1. 全部：绝大多数情况下等效于常规粘贴；
      2. 公式：只复制原始区域的公式；
      3. 数值：只复制数值、公式计算结果；
      4. 格式、批注、有效性验证、列宽、公示和数字格式、值和数字格式；
      5. 边框除外：复制除了边框以外的所有内容
    1. 运算：允许用户执行一次简单数值运算；
    2. 跳过空单元格：有效防治Excel用原始区域中的空单元格覆盖目标区域中的单元格内容。
    3. 转置：转置后公式仍能继续正常计算；
    4. 粘贴链接：将建立一个由公式组成的连接原始区域的动态链接。
  7. Excel的摄影功能
    1. 将某区域的数据同步显示在另外一个地方，包括格式等；
    2. 复制区域，单击目标区域左上单元格，按住shift，单击菜单栏的编辑，多出许多命令，单击粘贴图片链接命令；
    3. Excel中的相应命令按钮名为照相机，可以自定义到工具栏显示。
  8. 很奇妙的自定义数字格式
    1. 单元格格式对话框，选择分类为自定义，在类型文本框中输入自定义数字各市代码或修改原来格式代码；
    2. 自定义格式代码可以为正、负、零、文本制定不同格式；
    3. 用分号分割不同区段，完整格式代码组成为；大于条件值格式；小于条件值格式；等于条件值格式；文本格式。
    4. 没有特别制定条件值的时候，默认条件值为0，组成结构也可看作：正数格式；负数格式；零值格式；文本格式。
    5. 无必要写全所有格式。区段数为1，作用于所有类型；区段为2，第一区段作用于正数和零，第二区段作用于负数；区段数为3时，正数、负数、零值。
    6. 常用自定义数字格式代码：

    G/通用格式,不设置任何格式；

    #，数字占位符，只显示有效数字；

    0，数字占位符，当数字比代码的数量少时，显示无意义的0；

    ，数字占位符，需要的时候在小数点两侧增加空格来使得小数点对齐；

    .，小数点；

    %，百分数；

    ,，千位分隔符；

    E,科学计数符号；

    /，显示格式里的下一个字符；

    *，重复下一自负来填充列宽；

    -，留出与下一个字符等宽的空格；

    "文本"，显示双引号里面的文本；

    @，文本占位符，单个@是引用原始文本，使用多个@则可以重复文本；

    [颜色]，颜色代码，颜色可以是黑色、白色、红色、青色、蓝色、黄色、紫红色、绿色；

    [颜色 n]，显示Excel调色板上的颜色，n是0-56之间的一个数值；

    [条件值]，设置格式条件。

  9. 随心所欲设置日期格式
    1. 系统日期格式修改：控制面板-区域和语言选项-日期-自定义，分别设置日期格式和时间格式；
    2. m:使用没有前导0的数字显示月份；

    Mm:使用有前导0的数字显示月份；

    mmm，使用英文缩写显示月份；

    mmmm，使用英文全称来显示月份；

    Mmmmm:显示月份的英文首字母；

    d、dd：显示日期有无前导的0；

    ddd、dddd：英文缩写、英文全称表示星期几；

    aaaa、aaa：使用中文显示星期几、中文显示星期几，无星期二字；

    yy：两位数表示年份；

    yyyy：四位数表示年份；

    h、hh：无/有前导0来显示小时；

    m、mm：无/有前导0来显示分钟；

    s、ss：无/有前导0来显示秒钟；

    [ ]：显示超出进制的时间；

    AM/PM：使用12小时制显示小时；

    上午/下午：使用12小时制显示小时。

  10. 自定义数字格式的经典应用
    1. 零值不显示：
      1. 工具-选项-视图，取消勾选零值复选框；
      2. 自定义数字格式能使工作表一部分单元格不显示零值，一部分仍然显示零值。

    G/通用格式;G/通用格式

    1. 快速放缩数值：如0.00,,为按百万缩放数值；
    2. 智能显示百分比：[<1]0.00%;#.00_%则大于等于1的数字使用标准格式显示，同时还让所有数字排列整齐；
    3. 显示分数：如#"又"?/?将显示几又几分之几；
    4. 隐藏某些类型的内容：大于100才显示“[>100]0.00”，只显示文本“;;”.
  11. 把自定义数字格式的显示值保存下来
    1. 单元格应用数字格式，只改变显示内容，不改变存储内容；
    2. 选定应用了数字格式的单元格区域，快速连续两次按Ctrl+C，出现Office剪贴板，选定目标单元格或区域，单击Office剪贴板中复制项目旁的下拉箭头，在弹出菜单中选择粘贴，单击编辑-选择性粘贴，选择文本。
    3. 经过两次粘贴，最终可以得到与原始区域显示值完全相同的内容；
    4. 复制区域内容到记事本，再从记事本粘贴回来即可。
  12. 合并单元格的同时保留所有数值
    1. 通常，几个含有数据单元格合并时候，excel会提示并只会保留左上角的数据并删除其他数据；
    2. 在辅助列中按照要合并单元格的样式进行合并，选择格式刷，然后去刷原数据区域，这个时候没有弹出提示了，虽然单元格只显示了左上区域，但是实际数据并未丢失。
  13. 为同一个单元格里的文本设置不同格式
    1. 设置单元格格式的对象并不必是整个单元格，如果有文本型内容，可以将一个单元格中的内容设置成不同的格式；
    2. Ctrl+1调出单元格格式对话框；
  14. 制作斜线表头
    1. 一条斜线的表头，利用Excel里面的边框设置画出斜线，
      1. 利用文本框填入表头上下的内容；
      2. 上下标方式：右上的表头使用上标输入，左下的表头使用下标输入；
      3. 使用空格进行中分；
    1. 多斜线表头：借助自选图形进行绘图，然后利用文本框输入各个表头项目；
  15. 单元格里的文字换行
    1. 文本格式单元格字符数超过255个的时候，工作表中只能显示#，常规单元格没有这个限制；
    2. Ctrl+1调出单元格格式对话框，对齐选项卡中勾选自动换行；
    3. 如果要自定义换行，手动加入软回车alt+enter键。
    4. excel没有提供行间距的功能，间接方式为选中长文本单元格，调出单元格格式对话框，在对齐选项卡中，选择垂直对齐方式为两端对其，调整单元格高度，得到不同行间距。

百度日志允许的字符数太小了，分为两篇

类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(76) 2010-2-18：《Excel实战技巧精粹》技巧41-602010年02月19日 星期五 01:43

 

  1. 快速删除所有空行
    1. 列不多的情况下，自动筛选，在每个筛选条件下选择空白，删除行；
    2. 使用辅助列，在数据区域后插入新列，使用counta()函数计算该列前的数值个数，然后使用自动筛选，筛选出为0的行，删除行；
  2. 快速改变行列的次序
    1. 选定某列，按住shift拖动。
  3. 快速设置最适合的列宽
    1. 选中行/列，待鼠标变形为十字架形状时候，双击。
  4. 先顶工作表的可用范围
    1. 设置ScrollArea属性，右击工作表标签，打开查看代码，在属性窗口找到ScrollArea项目，输入可用的单元格范围，按Enter键，excel自动转换为绝对地址。按alt+f11返回工作簿；
    2. 上面方法的局限性是一个工作表只能设置一个连续区域，保存关闭后就会失效了，除非用宏来让他每次启动就运行。
    3. 使用工作表保护，选定可用范围，格式-单元格，保护-锁定取消勾选。工具-保护-保护工作表-取消勾选选定锁定单元格复选框。
  5. 彻底隐藏工作表
    1. 隐藏后保护，工具-保护-保护工作簿，勾选结构。
    2. 右键工作表标签-查看代码，修改属性中的visible，-1表示显示，0表示隐藏，2表示超级隐藏，按alt+f11返回工作簿，此sheet已经消失了，要让它重新显示需要进入vba编辑器更改visible更改属性。
  6. 自定义默认工作表
    1. 新建工作簿，只保留一张工作表，进行各方面设置，保存为.xlt，文件名为sheet.xlt，保存到xlstart文件夹中
  7. 输入数据后向不同方向移动单元格指针
    1. 工具-选项，编辑-按enter键后移动的方向，选择上下左右。
  8. 在单元格区域中移动
    1. 选定区域，按enter，单元格指针会按照一定方向移动。tab键为从左往右移动。
  9. 方便查看标题的“冻结窗格”功能
    1. 要冻结区域外的左上单元格为基点，设置冻结
  10. 多窗口协同作业
    1. 工作表不同部分，窗口-拆分窗口；
    2. 工作簿不同部分，窗口-新建窗口；
    3. 并排比较，窗口-与......并排比较，此时在一个窗口中移动活动单元格，另一个窗口也同时移动。
  11. 定义自己的序列
    1. 工具-选项-自定义序列，输入值或者从单元格中找到序列。
    2. 自定义英文字母序列，A1中输入=CHAR(ROW()+64)，一直拖动，然后导入自定义序列中。
  12. 自动填充的威力
    1. 启用，工具-选项，编辑-单元格拖放功能；
    2. 按住ctrl进行拖拽，数值类的按照步进值为1条虫，文本+数值则为复制填充。
    3. 等差填充；
    4. 日期逐日，逐月，逐年填充；
    5. 文字自动填充为按照选定区域顺序依次复制；
    6. 特定内容的填充，即序列方式填充
    7. 填充公式
  13. 巧用右键和双击填充
    1. 拖拽后会有小方块弹出，单击选择不同填充方式；
    2. 双击单元格右下角，会自动填充。参考列为左侧一列；
  14. 输入分数的方法
    1. 顺序：整数-空格-分子-反斜杠-分母。
    2. 纯分数需要整数部分输入0；
    3. 选中输入了分数的单元格，按ctrl+1在单元格格式中设置数字格式。
  15. 使用语音校验输入准确性
    1. 工具-语音-显示文本到语音工具栏，可以设置按enter键开始朗读，输入一个数据，校验一个数据；
    2. excel2007中，添加快速启动栏命令-朗读单元格。
  16. 控制自动超链接
    1. 取消单个内容转换为超链接，输入邮箱等内容前加单引号，或者输入后按enter，然后按ctrl+z;
    2. 关闭自动超链接，工具-自动更正徐昂想，自动更正-键入时自动套用格式0Internet及网络路径替换为超链接
  17. 取消现有的超链接
    1. 单个，右键-取消超链接
    2. 多个，选择任意空白单元格，复制，然后按下ctrl选中所有需要取消的单元格，选择性粘贴-加；
    3. 利用VBA，按alt+F11打开编辑器，按ctrl+g打开理解窗口，输入Cells.Hyperlinks.Delete；
  18. 提取超链接信息
    1. 按alt+F11打开编辑器，插入-模块，输入以下代码(hyperlinks后面括号中为数字)

    Function GetName(HyCell)

        Application.Volatile Ture

        GetName = HyCell.Hyperlinks(1).Name

    End Function

    Function GetAddress(HyCell)

        Application.Volatile True

        With HyCell.Hyperlinks(1)

            GetAddress = IIf(.Address = "", .SubAddress, .Address)

        End With

    End Function

    1. 使用getname和getadress函数即可生成名称和链接的列表
  19. 输入特殊字符
    1. 特殊字符，插入-特殊符号，或者插入-符号
    2. 快速输入对号与错号，对号为alt+41420，错号是alt+41409，数字需要使用小键盘输入，笔记本用户需要开启数字键盘模式;
    3. 快速输入平方与立方，先输入底数，然后alt+178为平方，alt+179为立方。
  20. 输入身份证号码
    1. excel能够处理的数字精度最大为15位，多余15为的数字当作0来保存，大于11位的以科学技术法来表示；
    2. 输入身份证之前，先输入单引号；
    3. 预先将单元格格式设置为文本。

类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(77) 2010-2-17：《Excel实战技巧精粹》技巧26-402010年02月18日 星期四 04:54
  1. Excel文件转化为PDF文档
  2. 选取excel区域的高招
    1. 使用shift键选择大区域；
    2. 选择当前数据区域，使用ctrl+*快捷键；
    3. 选定非连续区域，ctrl加鼠标，纯键盘是选中某区域，按shift+F8组合键 后去选择另外区域；
    4. 选定整行，单行的键盘操作是shift+space（没成功，不知道为啥），多行使用shift或者ctrl加上鼠标。
    5. 选定整列，单列的键盘是ctrl+space（没成功）；
    6. 反向选择剩余的行/列，ctrl+shift+下箭头/右箭头。
    7. 选取多个工作表的想同区域，其中一张工作表选取后，按住ctrl选择其他工作表标签。
  3. 轻松选择特殊区域
    1. 编辑-定位（F5），输入定位条件即可定位。
    2. 常量，指数字，文本，日期或逻辑值等手工输入的静态数据
    3. 当前区域，定界条件是空行或空列；
    4. 行/列内容差异单元格，目标区域内每行/列与其他单元格不同的单元格
  4. 快速填充所有空白单元格
    1. 不连续的，使用定位功能找到空值，然后输入内容，按下ctrl+enter
    2. 连续的，选中区域，输入内容，按下ctrl+enter
  5. 改变撤销的步数
    1. Ctrl+Z是撤销操作，excel默认16步的撤销。
    2. 更改可撤销的数量通过注册表修改进行，我的电脑\HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options ,编辑-新建-DWORD值，将其命名为UndoHistory，双击，编辑dword值，选择基数为十进制，数值数据中输入0-100的数值，这个数值就是Excel允许撤销的步数。
    3. 每多一步，系统就多分配一些内存给Excel，所以不要设置太大。
  6. 省心省力的重复操作
    1. Ctrl+y是恢复撤销操作，如果没有操作可恢复时候，组合键就是重复命令。
  7. 快速切换工作表
    1. Ctrl+PgUp, ctrl+PgDn组合键来切换上一张和下一张工作表
  8. 重置工作表中的已使用范围
    1. Ctrl+End定位到工作表数据区域的右下角单元格
    2. 保存工作簿；
    3. Alt+F11打开VBA编辑器，按Ctrl+G组合键显示立即对话框，输入 MsgBox ActiveSheet.UsedRange.Address，然后按Enter键。
  9. 该表工作表标签颜色与自豪
    1. 从excel2000开始可以为工作表标签设置颜色；
    2. 标签字号deep大小只能借助windows的外观设置来做。桌面右键-属性，显示属性-外观-高级，项目-滚动条，调整大小。
    3. 字号设置对所有应用程序有影响。
  10. 更改Excel默认的行列标签颜色
    1. 借助windows的外观设置
    2. 项目-已选定的项目，调整他的颜色，默认是蓝色。
  11. 隐藏私密数据
    1. 隐藏行/列后保护工作表，被隐藏的行/列就无法查看到；
    2. Ctrl+9隐藏选中单元格或区域所在的行，ctrl+0隐藏列，Ctrl+shift+9取消隐藏行， ctrl+shift+0取消隐藏列。
    3. 高级方法1，选定要保密的单元格，自定义单元格格式，输入“;;;”三个半角的分号，内容就看不见了。
    4. 高级方法2，需要保密的单元格字体颜色设置的与底色相同；
    5. 高级方法3，利用绘图画一个矩形盖住要保密的单元格。
    6. 三种高级方法只是不显示隐藏内容，还需要格式-单元格，单元格格式-保护，勾选隐藏复选框，这样在工作表被保护之后，高级方法中设置的内容在编辑框中也不会显示了。
  12. 快速插入多个单元格
    1. 标准做法，右键，插入单元格
    2. 快捷方法，选定区域，按shift键，光标移动到选取右下角，当光标变为分隔箭头的时候，向右或者向下拖动鼠标，拖动举例就是要插入的单元格数量。
  13. 快速插入多个行或列
    1. 右键；
    2. Ctrl+shift+=为插入行的快捷键、
    3. 插入多行，插入一行后按ctrl+Y。
  14. 隔行插入的绝招
    1. 一般为利用排序的方法，先插入1，2，。。。，然后在最底部空白的地方开始1.5,2.5…...之后排序此列即可、
  15. 在非空行之间自动插入空行
    1. 插入两列，A列顺序填入1，2。。。，B列填入公式，if(AND(OR(C1<>"",D1<>""),OR(C2)),A1+0.5,10000)
    2. 按照B列的值进行排序
类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(78) 2010-2-16：《Excel实战技巧精粹》技巧11-252010年02月17日 星期三 01:30
  1. 让Excel始终显示完整的菜单
    1. 视图-工具栏-自定义，选项-始终显示整个菜单
  2. 自定义菜单和工具栏
    1. 视图-工具栏-自定义，工具栏-新建按钮；
    2. 新建工具栏，自定义-命令-类别，找到新菜单，拖到新工具栏上；
    3. 继续拖动放入其他选项。
  3. 共享自定义工具栏
    1. 文件-新建，任务窗格中的空白工作簿；、
    2. 视图-工具栏-自定义，工具栏-附件，调出工具栏选择要复制的工具栏；
    3. 保存该工作簿；
    4. 其他用户打开该工作簿即可。
  4. 备份工具栏和菜单
    1. 用户界面修改保存在.xlb文件中，搜索该文件，保存到其他位置即可。
    2. 取消各种个性化设置可以将该文件删除，再次打开excel会自动创建一个.xlb的文件，所有工具栏和菜单恢复初始状态
  5. 显示工作簿的完整路径
    1. 菜单区域右击，选择 web项。、
  6. 自定义默认工作簿
    1. 创建一个名为book.xlt的模板文件并将他放到Excel的启动文件夹中；
    2. 新建一个文件，设置好工作表数量，名称，打印设置等等，保存为book.xlt，保存到XLSTART文件夹中；
  7. 预览工作簿
    1. 文件-打开，查找范围定义到目标文件夹，工具按钮左侧是视图按钮，选择预览；
    2. 要是工作簿能够预览，文件-属性，摘要-保存预览图片勾选，保存文件；
    3. 被设置了打开密码的excel文件，无法预览。
  8. 多用途的文档属性
    1. 文件-属性其实很重要，可以在资源管理器中显示出来很多文件属性信息；
    2. 很多信息还可以进行自定义，
    3. 属性中的信息可以用来进行高级文件搜索，打开-文件搜索-高级文件搜索；
    4. 可以设置第一次保存新工作簿时输入摘要信息，工具-选项，常规-勾选提示输入工作簿摘要信息；
  9. 查看最后打开文件的人
    1. 用记事本打开excel文件，可以看到最后一次打开该文件的电脑的用户名；
    2. excel的文件头自动保存打开文件的用户名，这个用户名是工具-选项，常规选项卡里面的用户名，如果用户未设置，则无法追踪；
    3. 以只读方式打开的不会保留此信息。
  10. 使用工作区文件
    1. 多个excel按照用户定义好的顺序排列，每次打开工作区即可打开一组excel文件；
    2. 排列好excel各个文件后，文件-保存工作区，文件类型.xlw
    3. 工作区文件仅记录工作簿的路径和文件名以及排列方式，不保存工作簿文件，如果文件位置变化，工作区文件就失效咯
  11. 一次关闭多个文件的技巧
    1. 右上角两个关闭的意义不用，外部的关闭所有，内部的是关闭当前工作簿；
    2. 如果多个文件都编辑过，会弹出是否保存的提示，如果多个文件都不需要保存，每次点击麻烦
    3. win系统的程序操作有一个特性，按住shift再执行某个命令，能够执行该命令的扩展或隐藏功能。excel的全否功能就是按住shift再单击否，excel放弃所有文件的保存，直接关闭。
    4. 新建的从未保存的文件，全否操作无效。
  12. 简繁转换不求人
    1. word中提供了中文简繁转换，office其他组件没有该功能；
    2. 微软提供加载项形式的简繁转换，在微软去下载该加载项；
    3. 选择区域，工具-中文简繁转换。
    4. 工作表中的名称，批注，工作簿中的宏代码不在转换之列。
  13. 保护excel文件
    1. 保护工作表，可以锁定编辑，但是还是可以查看数据。工具-保护-保护工作表
    2. 保护工作簿
      1. 工具-保护-保护工作簿，可以设置密码锁定工作簿的结构和窗口。结构就是工作表不能移动、删除、隐藏、重命名等，窗口则是指每次打开工作簿都保持窗口的固定位置和大小。
      2. 打开权限密码，工具-选项，安全性选项卡。excel的最高级保护，无密码不能打开文件。
      3. 修改权限密码，无密码只能以只读方式查看
  14. 为工作簿减肥
    1. 大量细小图形对象，按F5调出定位对话框，定位条件选择对象，查看这些对象是否需要删除。产生原因是网页上复制后没有选择性粘贴、无意中使用了绘图工具、插入了图片但是设置的非常小，对象设置了不可见属性等；
    2. 工作表在较大区域内设置了单元格格式或者条件格式。观察滚动条，如果滑标很小那么就是有一块被设置了格式。单击真正需要的行的下一行，按ctrl+shift+下箭头，选择所有多余行，编辑-清除-格式，同理清除多余的列；
    3. 为行列设置格式最好是通过整行或者整列进行，A1:A65536设置单元格格式与A:A设置单元格格式相差100倍；
    4. 大量区域包含数据有效性，数据-有效性，全部清除。
    5. 包含大量复杂公式，设法优化公式，使用名称代替单元格引用。
    6. excel的bug，新建空白工作簿，ctrl+a全选单元格，单击格式-行-隐藏，名称框中输入32768行以后的人以的单元格，如A32800,按enter，单击格式-行-行高，设置一个固定行高，ctrl+a全选单元格，单击格式-行-最适合的行高，保存文件后会发现有1.42M。
    7. 使用了大图片作为背景。
    8. 图片格式影响文件大小，如bmp,tiff格式的图片
    9. 共享工作簿引起体积虚增，取消共享工作簿即可。
    10. 其他未知原因，文件-另存为网页，选择保存整个工作簿，不要勾选添加交互，文件名中输入任意，确定保存，不安息excel。启动excel，打开刚刚的htm文件，检查数据和格式是否正确，再保存会xls格式，文件会小很多。
  15. 修复受损的excel文件
    1. 使用excel自动修复，打开受损文件或者文件-打开，定位文件后单击打开后面的下拉按钮，选择打开并修复；
    2. 专业修复软件Recovery for Excel和EasyRecovery FileRepair。
    3. 养成经常备份的好习惯，工具-选项-保存-自动保存

类别：《excel实战技巧精粹》学习笔记| 评论(0) | 浏览(58) 02-15：《Excel实战技巧精粹》技巧5-102010年02月16日 星期二 02:05
  1. 设置不同的excel启动效果
    1. 在excel程序快捷方式的<右键><属性>选项卡的<目标>内容末尾加上<空格/e>，excel启动屏幕将不再显示，不自动新建工作簿，可加快启动速度；
    2. <目标>末尾加上<空格/s>，强制excel以安全模式运行，忽略excel存在的某种未解决问题。这种方式只提供excel的基本功能；
    3. 其他参数可以查看excel帮助。
  2. Excel启动时自动打开指定的工作簿
    1. 工具-选项-常规，勾选启动时打开此目录中所有文件的复选框，填入文件夹地址；
    2. 搜索到本机上名为的文件夹，将要打开的文件放进去；
    3. 利用启动参数：快捷方式后面加上参数<空格/r空格 工作簿路径\文件名>，这种方式只能以只读方式打开一个只读文件。
  3. 实用的Excel选项设置
    1. 启动不加载任务窗格：选项-视图-启动任务窗格 复选框；
    2. 最近使用的文件列表：选项-常规-最近使用的文件列表 复选框以及调整其数量；
    3. 清除最近使用的文件列表：关闭excel，运行-输入 regedit，找到HKEY_CURRENT_USER\Software\Microsoft\office\11.0\Excel\Recent Files 删除其中的一个或多个子键；
    4. 新建工作簿时候工作表数量：选项-常规-新工作簿内的工作表数，调整数值即可；
    5. 默认文件位置：选项-常规-默认文件位置；
    6. 用户名：选项-常规-用户名
  4. 更新excel到最新状态
    1. Office update
    2. 下载其他网站的补丁
  5. Excel2000中的赛车游戏
    1. excel2000以后的版本中没有这个很cool的赛车游戏；
    2. 启动excel2000，文件-另存为-另存为web页；
    3. 单击发布，勾上 添加交互对象，保存成一个叫做2000.html的文件。
    4. 用ie打开文件，在工作表里面使用pagedown移动工作表矩形光标到2000行，鼠标单击行标，选中该行，用tab键移动光标到wc列；
    5. 按ctrl+shift+alt组合键，然后单击excel左上角的office标签。
  6. 解决双击XLS文件无法打开的问题
    1. 任务管理器关闭excel.exe进程；
    2. 重启
    3. 删除excel启动文件夹中的所有文件；
Excel数据透视表培训第2课！ Excel数据透视表 excel数据透视表 Excel 数据透视表查看数据 Excel数据透视表制作库存表 Excel数据透视表制作库存表一例 Excel 用数据透视表进行分数段统计 EXCEL 数据透视表—比较销售人员 Excel 透视表汇总多工作簿数据 在Excel中用数据透视表进行分数段统计 数据透视表 数据透视表 使用数据透视表分析数据1 数据透视表和数据透视图概述 数据透视表系列讲座 关于数据透视表和数据透视图的源数据 用VBA创建数据透视表 - 转 Excel2007数据透视表应用实例 Excel2007数据透视表和透视图 标准数据库数据组织格式_数据透视表 对Excel数据区域或表中的数据进行排序 远洋培训-第三天-2 Excel2007数据透视表应用实例（分类汇总） 在Office PowerPoint 2007中插入数据透视表