use of ribbon

Ribbon tabs - Insert

交叉分析筛选器Slicer

• Insert里有Slicer 选择field 可多项选择 最后会将选的field单独做成小表 格 每个表格里统计这个field下的值

枢纽分析表PivotTable

• Insert里Table->PivotTable 通常它会自动帮你找到表格的范围 可以选择创建新的sheet 或者已有的sheet添加分析表 这个表格类似交叉分析表 把所有的field都例出来 然后再设置row和column 然后可以filter选定的field value
• 在PivotTable Analyze里选择PivotChart初始化做column chart, 在>>里点design 选Change Chart Type更换chart的形状
• 数据在原本table里如有更改的 枢纽分析表不能自动更新 需要在PivotChart Analyze点击Refresh

Ribbon tabs - View

split window

• View里有split, 当有较多rows的时候 如相对比值得时候 可以选想要对比那层最后一个row或者第一个row 点split此时页面被分成两个页面，两个页面同时有所有值 只要在对应的页面上下滑动便可对比值

Ribbon tabs - Data

合并consolidate

• Data里Data Tools, consolidate 可以将多个sheet里的数据进行分析

Ribbon tabs - Home

数值格式(excel会自动预测数值类型 但是会误判 如日期和分数 0在数字前)

• 如果想要区分数 可再输入/前 先填写0 1/2 这样会直接预测这个数字
• 0在数字前 0232 excel会自动误判成232 此时续在这样填写即可’0232
• 自定义数值
  • 在Number里选择custom format
    ‘#’ - 设置整数 将小数变成整数
    ‘#.#’ - 保留小数 可以预留小数位 但只在小数已有多少位的时候 多余的0将不会显现
    ‘#.?’ - 预留位数 在多余的0时 用空格代替 这样可以是小数位对其
    0 - 强制显示每一个位数 不管0是否有实质性的作用
    -> 可以用显示手机号 000-0000 按照这个格式显示
    -> 可以用显示员工号 00000 -> 00001
    -> 也可以在数字加前缀或者后缀 “T”0000 或者 0”公分” 但不能在文字前后加前后缀
    @ - 用来代表文字预留的位置 可以加前后缀 如@”类” -> 苹果类
    ‘*’ - 重复输入符号 直到填满目前得储存格
  • 如格子里有苹果 这个数值并不能填满格子 可以用’.‘填满 那么@*. -> 苹果…… (填满格子)
  • 如格子里是$12129 数字与金钱符号紧贴 想要将金钱符号放在最左侧 那么$* 0 -> $ 12129
    , - 代表千 就是1000 ,,代表百万 可以用“K” “M”表示
    0.00 ; (0.00) ; 0.00 ; @
    正值 负值 零值 文字
    • 可以用_增加相应的宽度 使正负值小数点对上
    • 可以根据不同的区域设置不同的颜色 在该区域前加[颜色] 有八个现成颜色
      1. 红色 2.黑色 3.黄色 4.绿色 5.白色 6.蓝色 7.青色 8.洋红
         或者根据色彩盘填写[色彩##]
    • 可以不用填写全部区段
      1. 如果只填写一个 这一个变覆盖全部
      2. 如果只填写两个 第一个覆盖正负值 第二个覆盖零值
      3. 如果空下一个阶段 那段变会隐藏起来
      4. 如果连续输入;;; 全部隐藏起来
    • 可以加入条件是变换颜色 如[>90][绿色]0.00;[>80]青色;0.00 只能添加两个条件
• 时间格式
  • 时间填入时 不管是以24时制还是12时制 呈现出的都是以24时制体现 如想要体现成12时制 那么在自定义里 hh:mm 前或者后面填写AM/PM 上午/下午
  • 可以插入年月日等字 仅需在对应yyyy”年”mm”月”dd”日”
  • 转换数字到中文[DBNUM1]m”月”dd”日”
  • 将星期几加进去 在最后加aaa
  • control+; -> 变成当前时间 为静态的时间
  • 动态时间 TODAY()-今天日期 NOW()-现在时间 F9可以手动更新时间
  • 把data的format变成一般 那原本是日期的数据变成数字 是根据1900/1/1算起之后每一天加一
  • 算经过了多长时间 只根据’-‘ 是不能直接算出来 需要到format里加入
    1. [h]:mm 经过多长小时分钟
    2. [m] 经过多长分钟
    3. TODAY()-起始时间 再设置format为一般 算经过多少天
    4. DATEDIF(起始时间, TODAY(), “单位”) 按单位计算经过多久
       Y-年数 M-月数 D-天数 MD-天数 忽略年月 YM-月数 忽略年日 YD-天数 忽略年数
    5. NETWORKDAYS(起始日,结束日,除周末外的假日) 算出除周六日 假期日期
    6. NETWORKDAYS.INTL(起始日,结束日,自定周末,假日) 这样可以不被周六周日限制 可自行选择哪天是假日

Ribbon tabs - Review

保护表格

1. 如不想使用者看到表格内的公式时 可以先选中带有公式的表格点击format cell里的protection 点Hidden和locked 此时公式还是可以显现的 在Review点击Protect Sheet设置密码 设置你想要让使用者用什么功能 点击确定后 此时公式便被隐藏起来 但是此时 使用者还是可以创建新的sheet 对其他的sheet进行更改 如不想让使用者更改 那么点击Protect Workbook设置密码
2. 如不想使用者随意打开文档 便可以将文档锁起来 [mac]在File里Password设置密码 便可以设置权限

Ribbon tabs - Developer

在excel显示图标和进度
在Developer可以insert Check box 这个只是图像上的选格 为了能使这个checkbox能够灵活应用 在框子上单击右键点format control 这样可以link到想要显现TRUE/FALSE的位置
TRUE/FALSE可以用icon来代表 =IF(那个位置=TRUE,1,IF($C$3>=G6,0,UNICHAR(128336)))
用图标表示进度 插入图标 再插入文档 在文档上插入函数 连接想要代表的百分比

小技巧

1. 分割数据
   当复制很多数据时 被复制到一个格子里 此时可以通过Data里的Text to Columns选择固定式分割 如tab,semicolon,comma,space 或者通过选定式分割 滑动滑线选择想要的格式

2. 快速选取资料
   control+shift+键盘上下左右建 可以整行整列的滑动
   shift+键盘上下左右 可以单个格子的滑动
   左上角的三角 可选择全部

3. 插入多个空白列

• 先插入空白列，然后点F4是重复上次的插入
• 选择想要插入多少列 选取想要插入列数的范围 然后右键选插入

4. 拖移数据
   选取想要拖移的数据 移动鼠标到选取的数据 当出现一个小手时 便可以拖移任何一个地方 可以press control移动 这个会给多个option 如复制选中的值到这个地方 或者替换等等 可以press shift移动 这个会讲选定的值插入到表格 不会替换原本的值

5. 移除重复项
   当出现重复项的数值时，可以到Data里Data Tools去Remove Duplicates便可以移除重复的值

6. 表格转置
   当title是以行的形式呈现时 想要把title以列的形式呈现 可以把整个table复制 再在一个地方粘贴 选择paste special 点击Transpose便可以转置表格

7. 在一个范围内加简单的运算
   在一个格子里填入想要更变的数值 然后复制此数值 选择想要变更的范围 单击右键选择paste special可以选择加减乘除等运算

8. 显示所有公式
   control + ~ 变会显示这个excel里所有的公式
   再次按control + ~ 变会回到原样

9. 目标搜索
   在Data里What-if Analysis 可以选择Goal Seek 在Set cell选择的目标值坐标 在to value填入目标值 在By changing cell里选择更变值得范围 便可算出需要多少 才可以达到目标

10. 表格对角线
    方法一: 在borders里选择draw border就是画笔 在表格画对角线
    方法二: 单机右键选择format cells 在border里选择想要的对角线即可

11. 在单个表格内换行
    alt + enter 便可在一个格子里换行

重复内容

1. 标注
   方法一: 在conditional formatting里highlight cells rules选duplicate values 这个会把所有重复的值变色 但是不能整行整列一起变色
   方法二: 将整行整列的数值并到一个表格用cell-loc&cell-loc&cell-loc 全选范围 然后在conditional formatting里创建newrule 然后再style里选classic 再选use a formula to determine which cells to format填写COUNTIF(刚合并数值得例, 对应行合并数值的loc)>1 这样便可以通过reference别的行去判断那行是否有重复值 最后可以把reference的那个行隐藏起来
2. 移除
   在Data Tools里Remove duplicates即可
3. 防止输入重复
   在Data validation里设置custom 公式为COUNTIF(A:A,A1)=1 并且可以设置报错信息

显示图标的方法

windows上可以按Windows+; 便出现表情可供选择
其余可用UNICHAR(数字) UNICHAR可从 https://www.vertex42.com/ExcelTips/unicode-symbols.html#misc-symbols 查找