汇总Excel日常操作遇到的一些问题

写在前面

一些日常工作中Excel操作，包括一些快捷键、公式命令、单元格操作等，这些在网上基本都能直接搜到，这里仅作为日常使用记录下来。主要有：

 - 常用快捷键;
 - 数值文本转换数值格式；
 - 获取所有sheet名称，以sheet名称作为变量，批量处理sheet；
 - 公式计算错误类型判断；
 - 空格/空行删除；
 - 批量合并单元格；
 - 查找特殊字符；
 - 单元格切换被锁定后解锁。

快捷键

快捷键	含义	备注
Ctrl+PgDn/PgUp或Ctrl+Fn+⬇/⬆	同一Excel表不同sheet之间切换
Ctrl+Shift + l(字母"L"的小写)	筛选	一般筛选第一行
Ctrl+⬇/⬆	定位该列第一行/最后一行	如果有空单元格，则定位到空单元格位置
Ctrl+⬅/➡	定位该行第一列/最后一列	同上
Shift+⬅/➡/⬇/⬆	多个单元格选择
Ctrl+z	撤销上一步操作
Ctrl+y	恢复撤销上一步操作
Alt+Enter	单元格编辑时，强制换行
F2	编辑单元格
Shift+F2	添加批注
F4	重复上一步操作/固定区域	编辑公式时，例: 输入"C1"，按F4后变为"$C$1", 多次操作可切换为部分固定：C$1，$C1
F9	返回局部公式表达式的值
TAB	单元格向后切换；输入公式时快速补全
Shift+TAB	单元格向前切换

函数公式

• 问题：数值为文本格式，不能用于计算，如何转化为数值？
  解决：两种方法。1）选中要转化单元格，会出现一个黄色”叹号“图标提示是否转换（如下图），点击"转换为数字"即可；2）若1）不起作用，考虑在新的单元格，直接使用公式=VALUE(text), 可将数值的格式由原来文本(text)转换为数值。
  

• 问题：对各sheet中的数据进行汇总，如何获取sheet名称并将其作为变量？（各sheet的内容信息相似，比如：各个样本名称为sheet名）
  1）获取sheet名称：

  1. 先定义名称：“公式” – “定义名称” – 编辑名称 – “名称：‘get_sheet_name’(自定义)”、“引用位置：=GET.WORKBOOK(1)” – 确定；
     

  2. 然后使用=INDEX(自定义名称, 行号)（如下图），获取的是包含该Excel文件名及sheet名。下图中两中方式均可获得：=INDEX(get_sheet_name, ROW(A1))或者=INDEX(get_sheet_name, C1)（C1是先定义的1-n整数），这里"get_sheet_name"为第一步中自定义名称。

     若只需sheet名称，可结合使用FIND和RIGHT公式，例如：=RIGHT(A1, LEN(A1)-FIND("]",A1,1))得到"A1"位置的sheet名称"sheet1"。（注：由于Excel版本问题定义名称可能不会被保存，注意应将最后获取的名称复制进行"值粘贴"）
     

  2）sheet名称作为变量。 如果对各sheet作批量处理，在获取各sheet名称后，需要对各sheet的的数据操作汇总，比如，统计每个sheet第一列(A:A)的和。

  1. 首先使用=INDIRECT('Excel名称'!目标区域)获取指定索引（如下图），下图中红色框，左侧数字由右侧公式得到(注意使用单引号将对应sheet名称连接(&)起来)。如果在同一个表格中操作，直接使用sheet名称即可(红色框)，但对于不同表格操作时需要写完整的Excel文件名称(绿色框)，且操作时文件应处于打开状态。
  2. 然后再进行求和=SUM()即可。
     

• 计算公式有错，如何判断错误类型？
  解决： 使用=ERROR.TYPE(error_val)，常用于其他公式计算时，用于取代错误值，例如，分母为0在计算时，会返回"#DIV/0!"错误，如果使用"ERROR.TYPE()“对该错误进行判断，则返回2。
  
  其他错误类型：
  
  对应”#N/A"的错误类型，还可使用ISNA(value)(返回True或False)、=IFNA(value, value_if_na)返回"value_if_na"或者0。

其他操作

• 删除单元格中的空格

  • 通常可直接选中要删除的区域，使用快捷键"Ctrl+H" – 替换 – 查找内容为一个空格(" “)，替换为空(”")即可。
  • 但有时按该操作替换后仍有空格（或者是其他空字符），这时可将目标替换区域复制，使用值粘贴，再进行替换操作，可删除空格。

• 删除空行
  通过定位到空行，再使用右键删除行。具体步骤：“Ctrl+G” – “定位” – “定位条件” – “空值” – 确定，此时目标区域所有空值被选中，然后 右键 – 删除 – 整行 即可。
  

• 批量处理所有sheet
  当各sheet格式相同，对它们进行同样的操作时，选中所有sheet后(按Ctrl/Shift后点选目标sheet)，即可在一个sheet下操作，实现所选sheet同步操作。【注：按Ctrl后点击是每次点选一个sheet，按Shift后点击是点击首尾sheet，选择之间所有sheet】

  与上面使用公式获取sheet名称后不同的是，上面是对各sheet进行数据汇总，这个是直接对各sheet进行编辑。例如在各sheet第一列前添加一列并将表头命名为"ABC"：“Shift+点选目标sheet” 后，在一个sheet中操作，同步到其他sheet。

• 批量合并单元格
  这里合并是将一列相邻的相同单元格进行合并，例如第一列的多个"A", “B”, "C"合并成一个（如下图）。具体步骤：

  第一步：“插入” – “数据透视表” – “选择一个表或区域” – “选择放置数据透视表的位置” – 确定。
  
  第二步: 在"数字透视表字段"中勾选要合并的列(表头)，若勾选区域有重复，则会去除所选区域重复行。
  第三步：“设计” – “报表布局” – “以表格形式显示”
  

• 查找特殊字符"?","*","~"
  一般特殊字符的查找要使用转义字符，Excel中转义字符波浪号~（类似python中的转义字符反斜杠("\")），例如：查找"a~"，使用a~~。除了"~“用于转义，”?"、"*"本身匹配的字符如下：

  • ? 匹配1个任意字符，例如：使用"a?2"，可查找包含"a12"、“aa2"等字符；注:当查找使用”?“结尾时，匹配0个或1个任意字符，例如：“aa?”，不仅可匹配"aa1”、“aa2"等，还可匹配"aa”。
  • * 匹配0个或多个任意字符，例如，使用"a*23"，可查找"a123"、“aa23”、"aaa23"等。

• 上下左右键变成页面滚动，而不能在单元格之间切换

  • 如果老式键盘，可关闭滚动锁定键(“SCR LK”或者“scroll lock”），就可回复上下左右键切换单元格；
  • 笔记本电脑一般没有这个键，需使用"Win+R"，输入osk调出屏幕键盘（如下图），点击"ScrLk"键即可；
  • 网上也有说使用"Fn+c"的，有的电脑可能起作用，目前使用的Dell笔记本不行，还是需要使用屏幕键盘解决；
  • 当然还可以选择不使用上下左右键盘进行单元格之间的切换，而是选择组合快捷键进行切换："Tab – 切换到右单元格", "Shift+Tab – 切换到左单元格", "Enter – 切换到下方单元格"，"Shift + Enter – 切换到上方单元格"。
    PS：笔记本上没锁定键，是使用的哪些组合键开启了锁定键？之前出现类似情况，记得是用快捷键解决的，忘了记录。
    
    <完>