Excel 办公技巧

Excel办公使用技巧汇总，基本操作，数据分析，函数运用

Excel办公使用技巧汇总

• 基本操作
  • 重要快捷键
  • 按下Enter，向下移动一个活动单元格；按下Tab，向右移动一个活动单元格
  • 选择连续输入数据的单元格范围，同时按下Shift+Ctrl+方向键
  • 确认输入的公式引用了哪几个单元格内容，可选择公式所在单元格并按下F2
  • 绝对引用：利用F4（可多次按）输入$
    • $A$1——固定列和行
    • A$1——固定行
    • $A1——固定列
    • A1——不固定
• 函数运用
  • 乘法运算
    • PRODUCT（A1:A3）
  • 连接单元格（字符串）内容
    • CONCATENATE（A1:A3）
  • 计算单元格个数
    • COUNTA（）：更为常用；指定参数范围内，计算除空白格之外的单元格个数（统计包含数据，无论是数字还是文本，的单元格数量）
    • COUNT（）：指定参数范围内，计算含有数值的单元格数量（只计算数字单元格个数）
  • 快速计算总和
    • 选中单元格，按下Alt + =
  • 分类计算总和
    • SUMIF（参数一，参数二，参数三）：在指定的区域（参数一）内，只计算与指定的值（参数二）相同行的、指定列（参数三）里的数值总和
      • 参数一：用于条件判断的区域
      • 参数二：在参数一指定的范围内，需要计算总和的行的判定条件
      • 参数三：实际求和的区域
  • 判断单元格中是否包含特定的字符串
    • COUNTIF（参数一，参数二）：指定范围（参数一）中，计算出参数二所指定的值或条件一致的单元格数
      • 参数一：计算其中非空单元格数目的区域
      • 参数二：在参数一范围内计算数目的条件
    • 统计销量大于500的产品
    • 消除重复数据
      • 如何判断数据是否有重复
        • COUNTIF（参数一，参数二），计算在 A 列中与单元格 A2有相同数值的单元格的数目
        • 另一种办法
      • 选中并删除重复的单元格
        • COUNTIF（$A$2 : A2，A2）
  • 数据索引（垂直）
    • VLOOKUP（参数一，参数二，参数三，参数四）：
      • 参数一：检索值（为获取需要的数值，含有能够作为参考值的单元格）
      • 参数二：检索范围（在选定的最左列查找检索值的范围）
      • 参数三：指定对应参数二范围左数第几列的数值
      • 参数四：0，或FALSE
  • 操作技巧：SUMIF、COUNTIF、VLOOKUP函数，都以整列作为选取范围
  • 制作有起始值的连续数据（例如对数据进行编号1~1000）
    • 起始单元格输入1，并选中此格
    • 【开始】-【填充】-【系列】
    • 【序列产生在】选择【列】，【终止值】输入1000
  • 输入连续数字
    • ROW（）：显示该单元格位于工作表中的第几行的数值
    • COLUMN（）：显示该单元格位于工作表中的第几列的数值
  • 输入日期
    • 输入可计算的数据（如2014年1月1日），应在半角模式下，将年、月、日用 / 隔开
    • 2014/1/1，此数据格式可用于日期加减运算
    • Ctrl+；——快速输入今天日期
    • Ctrl+：——快速输入现在时间
    • TODAY（）：此单元格将自动更新数据为当前日期
  • 根据出生年月计算年龄
    • DATEDIF（起始日期，结束日期，单位）
      • 单位包括：Y-年，M-月，D-日，计算年龄则选择Y
      • 已知出生日期单元格为B2，则在年龄单元格中输入=DATEDIF（B2，TODAY（），“Y”）
      • 若要计算出“×个月”，将参数三设置为“YM”
      • 若要计算出“×日”，将参数三设置为“MD”
  • 整理数据
    • 如何判断字符串是否相同
      • 在B2单元格中输入=EXACT（A1，A2）：判断A1与A2单元格的字符串是否相同，输出结果到B2中（TRUE/FALSE）
    • 将全角字符转换为半角字符
      • ASC（）
    • 删除指定文字
      • SUBSTITUTE（参数一，参数二，参数三）：在参数一制定的字符串单元格/范围内，将参数二指定的文字替换为参数三指定的文字；如果参数三输入空白，相当于删除指定文字
  • 定位最后一个单元格
    • 快捷键Ctrl+End
    • 定位功能：Ctrl+G打开【定位】，点击【定位条件】，选择【最后一个单元格】
  • 利用【条件格式】定制单元格格式
    • 【新建规则】-【使用公式确定要设置格式的单元格】
    • 去掉输入公式中的绝对引用$
  • 每隔一行标注不同颜色
    • 【新建规则】-【使用公式确定要设置格式的单元格】
    • =MOD（ROW（），2）=1/0，参数二指定的数值除以参数一指定的数值后得到的余数为1（或0），即可以选定奇数/偶数行
  • 冻结窗口
    • 设置冻结窗口时，要保持A1单元格始终出现在窗口上
    • 如果想固定显示前三行的内容，就要选到整个第四行操作【冻结】
    • 如果想固定显示前三列的内容，就要选到整个第四列操作【冻结】
    • 按上述要求指定单元格【冻结】行+列
• 数据分析
  • 商业数据分析的三大指标
    • 同比率（与去年/上季度等相比是增加还是减少）
    • 预算与实际相比（是否达到目标）
    • 构成的比率（每项各占多少比例）
  • 实际数据，以百分比（比例，比率）呈现才是有说服力的数据