Excel 操作指南

发表于 更新于 本文字数： 3.1k 阅读时长 ≈ 3 分钟

保留源列宽

目的：复制表格时，保持表格列宽格式相同

image.png

尽量不要合并单元格

  • 通过筛选之后，合并的单元格无法进行复制
  • 跨行合并的数据可能影响筛选的结果

统计数据出现的次数

image.png

1
=IF(A2<>A1, COUNTIF(A:A, A2), "")

为什么第 6 行和第 7 行单元格为空呢：通过 IF 函数进行判断，上下两行内容相同，所以显示为空

统计不同种类的数据

image.png

1
=IF(COUNTIF($A$2:A2,A2)=1,MAX($B$1:B1)+1,"")
1
2
3
4
5
6
7
% 判断某一行的数据在整列当中是否为第一次出现
COUNTIF($A$2:A2,A2)=1

% 如果第一次出现，则值为1；之后每出现一个第一次出现的新数据，则该数据每次+1
—> MAX($B$1:B1)+1

% 数据不是第一次出现，则为空值

如上图所示，第 6，7，9 行都为空值，能不能让这些行都带上编号呢：

image.png

1
=IF(COUNTIF($A$2:A2,A2)=1,MAX($B$1:B1)+1,VLOOKUP(A2,$A$1:B1,2,FALSE))

第 6，7，9 行之所以为空值，是因为第二行中第一次出现了该数据，因此只需查询到第一次出现数据的第二列编号即可，而 VLOOKUP 函数会自动从上往下进行查找，就能正确的找到第二列的编号。

复制工作表

按住 Ctrl 键拖动即可复制工作表

image.png

查找工作表当中的合并单元格

需求：从别人接手的工作表，很可能包含大量合并的单元格，需要将它们取消合并

1、查找，进入 格式 选择

image.png

2、勾选 合并单元格

image.png

宏绑定快捷键

如下图所示，不过不支持 Ctrl+ 数字键，那么就换成字母吧

image.png

单元格是否存在某些内容

例如，若 E 列中存在 PH值、PH、PH50% 这些字样，那么在 I 列中将其提取出来

image.png

1
=IF(ISNUMBER(SEARCH("pH", E4)), E4, "")

为什么要用 ISNUMBER 函数：因为 SEARCH 函数在未找到指定的字符串时会返回错误值，而不是返回 0。
比如没有找到相应的文字，那么 search 返回 false，isnumber 函数返回 false，最终就返回空值。如果找到了相应的文字，那么 search 返回对应的索引，isnumber 函数返回 true，最终返回

进一步地，将一个词的条件变为多个词，需求如下：

只要单元格存在 氢氰酸、总氰、游离氢氰酸、游离丙酮、硫酸丙酮、氰醇 这几个词中的某一个，那么就判断正确（即需要在其他列中将其提取出），否则为空

1
2
3
4
5
6
7
8
9
10
11
12
=IF(
    OR(
        ISNUMBER(SEARCH("氢氰酸", E4)),
        ISNUMBER(SEARCH("总氰", E4)),
        ISNUMBER(SEARCH("游离氢氰酸", E4)),
        ISNUMBER(SEARCH("游离丙酮", E4)),
        ISNUMBER(SEARCH("硫酸", E4)),
        ISNUMBER(SEARCH("丙酮氰醇含量", E4))
    ),
    E4,
    ""
)

最小公倍数

1
=LCM(B2:B5)

image.png

合并单元格 - 宏程序

假设三个单元格都存在数据，将这连续的三个单元格合并时，默认只会保留左上角单元格的数据，其他单元格数据丢失。而如果有如下需求：需要将多个单元格数据都保留下来

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Sub 合并选定单元格()
    Dim rng As Range
    Dim mergedCell As Range
    Dim cell As Range
    Dim text As String
    
    ' 检查是否有选定的单元格
    If Selection.Cells.Count < 2 Then
        MsgBox "请选择至少2个单元格进行合并。"
        Exit Sub
    End If
    
    Set rng = Selection ' 获取选定的单元格范围
    
    ' 创建一个合并单元格
    Set mergedCell = rng.Cells(1)
    
    ' 将其他单元格的值添加到合并单元格中
    For Each cell In rng
        If cell.Value <> "" Then
            text = text & cell.Value & Chr(10) ' Chr(10) 表示换行符
        End If
    Next cell
    
    ' 去除最后一个换行符
    text = Left(text, Len(text) - 1)
    
    ' 将合并后的文本赋值给合并单元格
    mergedCell.Value = text
    
    ' 合并单元格
    rng.Merge
End Sub

拆解合并的单元格（并填充内容）

image.png

例如第 145 和 146 行，按照常规拆解单元格流程，拆解后只有 145 行保留数据，146 行数据为空。
需求如下：如何保持每一格单元格的内容和拆解前的保持一致

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub 宏1()
'
' 宏1 宏
' 取消合并行。请注意，这段代码假设你只选择了一个连续的单元格范围。如果选择的范围包含多个不连续的区域，代码可能无法正常工作。确保选择的范围是连续的单元格范围。
'

'
    Dim selectedRange As Range
    Set selectedRange = Selection ' 将选择的单元格范围存储到变量中
    
    selectedRange.Select
    Selection.UnMerge
    selectedRange.Rows(1).AutoFill Destination:=selectedRange, Type:=xlFillCopy ' 使用第一行的内容自动填充整个范围
    selectedRange.Select
    selectedRange.Cells(selectedRange.Rows.Count, selectedRange.Columns.Count).Select ' 选中范围中的最后一个单元格
End Sub

计算单元格中字符出现的次数

image.png

1
=LEN(A2) - LEN(SUBSTITUTE(A2, "k", ""))

公式思路：先计算单元格中总的字符长度，然后将要找到字符替换为空字符，那么『总长度』减去『替换为空字符后的长度』，就是你要找到字符的个数
SUBSTITUTE：将 "k" 替换为空字符 ""

ROUNDUP 函数

参考资料：Fetching Title#mrdb

朝着远离 0（零）的方向将数字进行向上舍入：

  • ROUNDUP 的行为与 ROUND 相似，所不同的是它始终将数字进行向上舍入
  • 如果 num_digits 大于 0（零），则将数字向上舍入到指定的小数位数。
  • 如果 num_digits 为 0，则将数字向上舍入到最接近的整数。
  • 如果 num_digits 小于 0，则将数字向上舍入到小数点左边的相应位数。
1
2
3
4
5
6
7
8
=ROUNDUP(3.001,0) —> 4
=ROUNDUP(3.01,1) —> 3.1

注意：该函数并不考虑什么四舍五入，都是向上取整

=ROUNDUP(-3.14159, 1) —> 如果第一个参数是正数，那么返回值是3.2；是负数，返回-3.2

=ROUNDUP(31415.92654, -2) —> 将 31415.92654 向上舍入到小数点左边两位数，结果为31500

多列数据合并（自动添加分隔符）

未完待续…