Excel笔记

发表于 · 归类于技术 · 阅读完需 18 分钟 · 报告错误

一、基础部分

（1）公式汇总

函数名称函数意义函数用法其他
FLOOR基数向下取整--
CEILING基数向上取整--
ROUNDUP位数向上取整--
ROUNDDOWN位数向下取整--
MOD获取余数--
DATEDIF日期间隔--
IF单重判断--
IFS多重判断--
MID截取中间字符串--
LOOKUP字典匹配--
VLOOKUP横向匹配--
XLOOKUP复杂匹配--

（2）快捷键汇总

# F2                 # 编辑当前单元格
# F12                # 另存为
# Ctrl+G             # 快速定位
# Ctrl+E             # 智能填充
# Ctrl+H             # 替换
# Ctrl+PgDn          # 右侧工作表
# Alt+=              # 快速求和

（3）求和

=SUM()

（4）平均数

=AVG()

（5）总数

=COUNT()

（6）条件总数

COUNTIF(A1:A10, ">20") #用来统计范围内的符合条件的数值量

（7）字符串截取

=LEFT(B34,1)

=CONCATENATE("张三","李四") #张三李四
=A1&B1 #张三李四

二、中级部分

（1）身份证数据计算

=MID(A6,7,8) #522228199907201735->19990720

=MID(U2,7,4)&"-"&MID(U2,11,2)&"-"&MID(U2,13,2) #522228199907201735->1999-07-20

=IF(MOD(MID(A6,17,1),2),"男","女") #522228199907201735->男

=DATEDIF(TEXT(MID(E2,7,8),"#-00-00"),TODAY(),"y") #522228199907201735->23

（2）时间数据转换

=(A1+8*3600)/86400+70*365+19

yyyy"年"mm"月"dd"日"hh"时"mm"分"ss"秒"

（3）数据校验

=IF(COUNTIF(A:A,A2&"*")>1,"重复","不重复")

=IF(COUNTIF(A:A,B2&"*")>0,"重复","不重复")

=IF(LEN(A2)=18,IF(RIGHT(A2,1)="X",IF(CHOOSE(MOD(SUM(LEFT(RIGHT(A2,18))*7+LEFT(RIGHT(A2,17))*9+LEFT(RIGHT(A2,16))*10+LEFT(RIGHT(A2,15))*5+LEFT(RIGHT(A2,14))*8+LEFT(RIGHT(A2,13))*4+LEFT(RIGHT(A2,12))*2+LEFT(RIGHT(A2,11))*1+LEFT(RIGHT(A2,10))*6+LEFT(RIGHT(A2,9))*3+LEFT(RIGHT(A2,8))*7+LEFT(RIGHT(A2,7))*9+LEFT(RIGHT(A2,6))*10+LEFT(RIGHT(A2,5))*5+LEFT(RIGHT(A2,4))*8+LEFT(RIGHT(A2,3))*4+LEFT(RIGHT(A2,2))*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=LEFT(RIGHT(A2,1)),"正确！","出错啦！"),IF(CHOOSE(MOD(SUM(LEFT(RIGHT(A2,18))*7+LEFT(RIGHT(A2,17))*9+LEFT(RIGHT(A2,16))*10+LEFT(RIGHT(A2,15))*5+LEFT(RIGHT(A2,14))*8+LEFT(RIGHT(A2,13))*4+LEFT(RIGHT(A2,12))*2+LEFT(RIGHT(A2,11))*1+LEFT(RIGHT(A2,10))*6+LEFT(RIGHT(A2,9))*3+LEFT(RIGHT(A2,8))*7+LEFT(RIGHT(A2,7))*9+LEFT(RIGHT(A2,6))*10+LEFT(RIGHT(A2,5))*5+LEFT(RIGHT(A2,4))*8+LEFT(RIGHT(A2,3))*4+LEFT(RIGHT(A2,2))*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=LEFT(RIGHT(A2,1))*1,"正确！","出错啦！")),IF(LEN(A2)=15,"老号，请注意！",IF(LEN(A2)=0,"缺号码","位数不对！")))

（4）行数判断

前后两人为一组

=IF(MOD(ROW()+1,2)=1,CONCATENATE(ROW()/2,"组：",B2,A2,";",B3,A3),"")

思路：判断行数是奇数还是偶数，是偶数则获取下一行的信息，是奇数则为空

（5）获取户主身份证号码

思路：当本人为户主姓名，则取户主身份证号码；当本人不是户主姓名，则取上一个人的户主的身份证号码

（6）区间统计

（1）基于10向下取整 =FLOOR(A2,10)

（2）由大到小排序

（3）分类汇总：计数

（7）字典匹配

LOOKUP
=LOOKUP(D2,{1,2,3,4,5},{"一年级","二年级","三年级","四年级","五年级"})

=LOOKUP(A2,{0,60,80,90,101},{"不及格","及格","良好","优秀"})

（8）分数段等级匹配

IFS
=IFS(A2>=90, "优秀", A2>=80, "良好", A2>=60, "中", A2<60, "不及格")

=IFS(
    D5="男", IFS(
        I5>=2.39, 15,
        I5>=2.35, 14.5,
        I5>=2.31, 14,
        I5>=2.27, 13.5,
        I5>=2.23, 13,
        I5>=2.19, 12.5,
        I5>=2.15, 12,
        I5>=2.11, 11.5,
        I5>=2.07, 11,
        I5>=2.03, 10.5,
        I5>=1.99, 10,
        I5>=1.95, 9.5,
        I5>=1.91, 9,
        I5>=1.87, 8,
        I5>=1.83, 7,
        I5>=1.79, 6,
        I5>=1.75, 5,
        I5>=1.71, 4,
        I5>=1.67, 3,
        I5>=1.63, 2,
        I5>=1.59, 1,
        TRUE, 0
    ),
    D5="女", IFS(
        I5>=1.86, 15,
        I5>=1.83, 14.5,
        I5>=1.80, 14,
        I5>=1.77, 13.5,
        I5>=1.74, 13,
        I5>=1.71, 12.5,
        I5>=1.68, 12,
        I5>=1.65, 11.5,
        I5>=1.62, 11,
        I5>=1.59, 10.5,
        I5>=1.56, 10,
        I5>=1.53, 9.5,
        I5>=1.50, 9,
        I5>=1.48, 8,
        I5>=1.46, 7,
        I5>=1.44, 6,
        I5>=1.42, 5,
        I5>=1.40, 4,
        I5>=1.38, 3,
        I5>=1.36, 2,
        I5>=1.34, 1,
        TRUE, 0
    ),
    TRUE, 0
)

类似于分支语句（if-else）

（8）联表查询

VLOOKUP
=VLOOKUP(A1,{"敦寨小学",2152016547;"新化小学",2152016572;"亮司小学",2152016548;"铜鼓小学",2152016540;"龙池小学",2152016549;"花桥小学",2152016545},2,0)

=VLOOKUP(E2,A:B,2,0)


=VLOOKUP(G2,IF({0,1},A:A,D:D),2,FALSE)

XLOOKUP

XLOOKUP是Excel中用于查找和返回数据的函数，比VLOOKUP更灵活强大

=XLOOKUP(F2, A:A, B:B, "查无此人", 0)

=XLOOKUP(A2&B2, D:D&E:E, F:F, "查无此人", 0)

三、高级部分

（1）VBA批量复制sheet表，并修改内容

打开VBA的快捷键是Alt+F11

实现生成N个表

Sub CreateSheets()
    For i = 1 To 100
        Worksheets.Add After:=Worksheets(Worksheets.Count)
        With Worksheets(Worksheets.Count)
            .Name = "Sheet" & (Worksheets.Count - 1) ' 假设第一个Sheet的名字为Sheet1
        End With
    Next i
End Sub

把表A复制成表B，并修改相应内容

Sub CopyWorksheet()
    ' 定义要复制的工作表名称
    Dim SourceSheetName As String
    SourceSheetName = "（1张XX）明细表"
    
    ' 检查源工作表是否存在
    On Error Resume Next
    Set sourceSheet = ThisWorkbook.Sheets(SourceSheetName)
    On Error GoTo 0
    
    If sourceSheet Is Nothing Then
        MsgBox "工作表 " & SourceSheetName & " 不存在!"
        Exit Sub
    End If
    
    ' 复制工作表并指定新的名称
    sourceSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    ActiveSheet.Name = "（1龙A周）明细表"
    sourceSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    ActiveSheet.Name = "（2龙L树）明细表"
    ActiveSheet.Range("B6").Value = "龙L树"
    MsgBox "已将 " & SourceSheetName & " 复制为 Sheet2。"
End Sub

（2）Python复制sheet表，并修改内容

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import openpyxl
# 打开工作簿
wb = openpyxl.load_workbook('（敦寨中学）2024年综合报表培训学时统计表.xlsx')
# 获取表 1
sheet1 = wb['模板']
# 创建表 2，如果表 2 已存在会覆盖现有内容
wb.copy_worksheet(sheet1).title = '教师姓名'
sheet2 = wb['教师姓名']
# 修改表 2 的 B6 单元格内容为“贵州”
sheet2['B6'] = '贵州'
# 保存工作簿
wb.save('（敦寨中学）2024年综合报表培训学时统计表-处理后.xlsx')

（3）拆分Excel文件

from openpyxl import load_workbook
# 加载工作簿
wb = load_workbook('2025年秋季报名册-20250919.xlsx')
# 选择要操作的工作表，这里假设选择第一个工作表，你可以根据实际情况修改
ws = wb.active
# 从最后一行开始向前遍历，这样删除行时不会影响后续行的索引，数据是从5行到1661行
for row in range(1661, 5, -1):
    # 班级是在21列
    cell_value = ws.cell(row=row, column=21).value
    if cell_value != 6:
        ws.delete_rows(row)
# 保存修改后的工作簿
wb.save('2025年秋季报名册-20250919-1.xlsx')
若以上内容满足不了你的需求，建议学习Python或JavaScript数据处理
☰