[toc]

1. 增减插入行

##1.1. 隔行插入空行（如A1~A10）

1 alt+f11，添加如下代码后f5执行

Sub main()
For I = 10 To 2 Step -1
Rows(I).Insert
Next
End Sub

Sub 插入空行()

For i = 1 To 10   #10即插入10行

Cells(2 * i, 1).Select   #(2 * i, 1)即在第2*i行，第1列处插入

Selection.EntireRow.Insert

Next i

End Sub

也可以添加数字辅助列，通过排序来实现空行插入。

##1.2. 隔行排序（制作双语字幕）
  1. 将原文和译文字幕放在同一列，中间不留空行
  2. 字幕左边插入一列，对原文以奇数填充（13579…），译文以偶数填充（246810…）
  3. 对数字列按升序排序，得到的结果就是“1句原文+1句译文”的格式了

2. 重复行+相同行

##2.1. 如何删除重复行
  1. 全选数据区域→数据菜单→删除重复项→全选（表示所有数据都相同的行才会被删除）
##2.2. 如何快速对比两列数据是否相同
  1. 选中数据区域
  2. ctrl+\
  3. 此时不相同的数据即被选中，填充颜色后即可进行排序
##2.3. 如何对比多列数据是否相同
  1. 在数据最后一列输入公式
=if(a2=b2,"同","不同") 
=if(exact(a2=b2)=true,"同","不同") （区分大小写）
  1. 然后可以对结果按条件格式显示或排序，方便查看
##2.4. 如何标出完全重复行（多条件求相同行）
  1. 开始→条件格式→新建规则→使用公式确定要设置格式的单元格
  2. 输入公式
=sumproduct(($a$1:$a$15=$a1)*($b$1:$b$15=$b1))＞1

示例公式表示有ab两列，各15个数据。先找出a列相同数据，再看这些相同数据对应的b列数据是否相同，如果相同则对应的行为完全相同行（有时候公式会出错，不知原因）
3. 设置单元格颜色为填充方式，这样方便按单元格颜色排序

3. 多条件处理

##3.1. 如何按单元格颜色+数值大小排序
  1. 开始→排序和筛选→自定义排序→排序依据选择单元格颜色
  2. 左上角添加条件→排序依据选择数值
##3.2. 如何多条件求和

=SUMIFS(B2:B1001,C2:C1001,K2,D2:D1001,L1)    （sumifs函数表示求和数据区+条件区1+条件1+条件区2+条件2+……）

以上公式表示先找出C列中的K2，然后找出这些K2在D列中对应的L1，最后求这些L1在B列中的和。

4. 单元格拆分

##4.1. 中英文分离

=left(a1,2)   （表示将a1单元格最左边的2个字符拆分出来）
=right(a1,len(a1)-2)   （len函数表示返回单元格字符数）
=left(a1,lenb(a1)-len(a1))   （lenb函数表示返回单元格字节数。这个公式可将单元格最左边不固定数量的中文类2字节字符拆分出来）

5. 单元格转换

##5.1. 全半角转换

=asc(a1)   （将a1单元格内的全角字符转换为半角）
=widechar(a1)   （将a1单元格内的半角字符转换为全角）

##5.2. 字符替换

=substitute(a1,"!","?")   （表示将a1单元格内的“!”全部替换为“?”）
=substitute(substitute(a1,"!","?"),"@","$")   （可在上一步的基础上多层嵌套）

6. 复制粘贴

##6.1. 快速复制
  • 拖动单元格右下角的填充柄进行复制 （不受方向限制，但数据量大则效率低下）
  • 双击填充柄进行复制 （效率高，但只可向下复制）
  • 先复制单元格，然后选择目标区域，按下enter/ctrl+v即可 （不受方向+连续性限制）
  • 先选中源单元格，然后在左上角地址栏中输入目标区域（如c1:c15），再点击右边的编辑栏，最后在ctrl+enter （不受方向限制，数据量大时效率高，但必须连续）
  • 先选中单元格，然后在地址栏中输入目标区域最后一个单元格地址（如c15）后按下shift+enter，再点击右边的编辑栏，最后在ctrl+enter （同上）
##6.2. 粘贴时跳过空单元格
  • 复制后右键→选择性粘贴→跳过空单元
##6.3. 快速不连续复制（多用于查找替换）
  • 查找对象所在的全部单元格
  • 在查找对话框中点击查找结果，然后ctrl+a
  • 关掉查找对话框，在任意查找对象所在的单元格上右键复制

此时粘贴后的结果是连续的，如果要保持和原对象所在的单元格（比如行）不变，参考6.4

##6.4. 快速不连续粘贴方法1（跳过隐藏行）

假设需要将A列中查找的所有不连续单元格，保持行不变复制到B列

  • 首先ctrl+9将正文内容全部隐藏
  • 选中A列，查找对象所在全部单元格
  • 在查找对话框中点击查找结果，然后ctrl+a
  • 关闭查找对话框，按下ctrl+shift+9将显示出查找对象在A列所有单元格
  • 使用excel插件“方方格子”→复制粘贴→复制并粘贴可见区域，保持“源区域+粘贴到”的单元格是一致后，点击确定即可
##6.5. 快速不连续粘贴方法2（跳过隐藏行）

假设需要将A列中查找的所有不连续单元格，保持行不变复制到B列

  • 添加C列辅助列“序号”，向下填充连续的序号
  • 筛选或按6.4的方法只显示出对象所在单元格
  • 添加D列辅助列“标记”，填充公式“=row()”
  • 取消筛选或隐藏，按D列排序，将对象所在单元格排在一起
  • 复制对象所在的全部单元格，粘贴到B列
  • 按C列还原之前的排序后，删除CD辅助列即可
    More