Excel笔记

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

一、基础部分

（1）公式汇总
# FLOOR # 基数向下取整 CEILING # 基数向上取整
# ROUNDUP # 位数向上取整 ROUNDDOWN # 位数向下取整 INT # 向下取整 
# MOD   # 取余     LEN     # 长度
# DATEDIF 日期间隔
（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("张三","李四") #张三李四

二、中级部分

（1）身份证数据计算
=MID(A6,7,8) #522228199907201735->19990720

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

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

=MID(U2,7,4)&"-"&MID(U2,11,2)&"-"&MID(U2,13,2)
（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匹配一年级、2匹配二年级、3匹配三年级…

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

要求：60分以下为不及格、60~80分为及格、80~90分为良好、90~100分为优秀

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

（7）区间统计

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

（2）由大到小排序

（3）分类汇总：计数

（8）联表查询
=VLOOKUP(E2,A:B,2,0)


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

（待补充）

三、高级部分

（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龙安周）明细表"
    sourceSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    ActiveSheet.Name = "（2龙立树）明细表"
    ActiveSheet.Range("B6").Value = "龙立树"
    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')
☰