3

excel技巧

 2 years ago
source link: https://www.mihu.live/archives/256/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

excel技巧

yan
yan
2021-04-25 / 2 评论 / 259 阅读 / 已收录
04/25

excel拆分

场景：excel现在有100万条数据，需要拆分成多张表。
参考：https://www.cnblogs.com/jiujian/p/12642662.html
1.在sheet页右击选择查看代码，或者按ALT+F11进入VBA命令界面，粘贴如下代码：
拆分行数和路径自行设置，bt=1可以控制标题行数
2.返回sheet页按ALT+F8,点击执行

Sub cfb()
Dim r, c, i, WJhangshu, WJshu, bt As Long
r = Range("A" & Rows.Count).End(xlUp).Row
c = Cells(1, Columns.Count).End(xlToLeft).Column
bt = 1 'title
WJhangshu = 250 '行数
WJshu = IIf(r - bt Mod 20000, Int((r - bt) / WJhangshu), Int((r - bt) / WJhangshu) + 1)
For i = 0 To WJshu
    Workbooks.Add
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(i, String(Len(WJshu), 0)) & ".xlsx"  '路径
    Application.DisplayAlerts = True
    ThisWorkbook.ActiveSheet.Range("A1").Resize(bt, c).Copy ActiveSheet.Range("A1")
    ThisWorkbook.ActiveSheet.Range("A" & bt + i * WJhangshu + 1).Resize(WJhangshu, c).Copy _
     ActiveSheet.Range("A" & bt + 1)
    ActiveWorkbook.Close True
Next
End Sub

筛选重复或唯一数据

开始--条件格式--突出显示单元格规则--重复值（其中也可以设置唯一值的显示规则），之后可以在条件格式的管理规则里面删除或定义更多规则

筛选某列数据在另一列中不存在的

参考：https://zhidao.baidu.com/question/2137999315270054628.html
假设筛选A列有，B列没有的数据：
在C1单元格输入公式=IF(COUNTIF(B:B,A1)>0,"B列有","B列没有")，回车，然后在C1单元格右下角，出现“+”后，双击或下拉即可。


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK