精华内容
下载资源
问答
  • 在我们日常的制表工作中,序号列是我们经常要用到的一列表格,...今天我们教大家一招,如何用ROW函数来自动更新表格序号。如图,我们在表格序号列第一行输入函数=row()-1,按回车,它会生成序号1,然后再下拉填充...

    在我们日常的制表工作中,序号列是我们经常要用到的一列表格,不论是做报表还是给数据排序,在表格的第一列添加序号,会让表格数据更加清晰。但是让新手小白感到麻烦的是,每当插入或者删除一行数据的时候,前面手动输入填充的序号会出现断层,每次我们都要重新更新一次。

    f347d1c7c732a601135db145355ddb8b.png

    今天我们教大家一招,如何用ROW函数来自动更新表格序号

    如图,我们在表格序号列第一行输入函数=row()-1,按回车,它会生成序号1,然后再下拉填充,补齐序号。

    函数解析:其实这里函数括弧里还有一个reference,用作更新序号可以省略;括弧后面的-1是因为前面有个序号标题行,你是在第二行开始排序的,减一个1就等于序号1了。

    1e964b716752e18e51e442b665691aea.png

    其实到这里操作就结束了,是不是有点简单,这就是Excel函数的奥义。但是照例,我们来给大家看看效果。

    第一种效果,我们把序号2、3、4的数据给删掉,可以看到,序号5、6的表格行自动向上补齐,并且序号也自动改为2、3。

    df1f6428777fa7ef24b1cf0be9792378.gif

    第二种效果,我们在序号1下面插入三行空行,可以看到,序号2、3自动跳为序号5、6,然后我们在把中间空行下拉填充,会得到一列完整的序号。

    639e8baabd624358366478f6ee34c8d0.gif

    操作简单,效果明显,用row函数自动更新表格序号,你学会了吗?

    展开全文
  • 点开「天才社」↑你的朋友都在关注 就等你啦告诉大家一个悲伤的消息,开心的周末余额又不足了……该收收心准备工作了,今天小社教给大家一个提高工作效率的技巧——让Excel表格序号完全自动化!相信无论你是不是...

    点开「天才社」↑ 你的朋友都在关注  就等你啦

    cbb65de19452b993155844290d51fc13.gif

    abede5026525b9677358cf598ae1d1aa.gif

    告诉大家一个悲伤的消息,开心的周末余额又不足了……

    该收收心准备工作了,今天小社教给大家一个提高工作效率的技巧——让Excel表格序号完全自动化

    相信无论你是不是“表哥表姐”(天天做表的人),这个方法都能为你节约很多时间和生命。

    58792d5a4ef1c9605a26826c31612148.png

    Excel作为Microsoft Office的经典基础组件之一,由于具备强大的电子表格数据功能,常被用在各类统计分析和事务管理方面,甚至丝毫不逊于一个小型的信息系统。

    bf289f3ca0a11f605bfd68b1b7458ea8.png

    日常使用中,大家一般都会给表格加一列人工序号,可以更加清晰规范。虽然程序左侧已经自带序号,但是严格来说那只是一个“坐标”,因为如果我们的表格数据不是从第1行开始的,比方加入了标题行之类的,这个数字和我们所要的“序号”还是有挺大差异的,何况在打印的时候,它也不会随着表格内容出现。

    7b200c69704dc82323baa9bcd26062cf.png

    比方说我们现在有一个项目情况表(工资表也好、成绩单也好,都一样啦),XX青年的做法可能是一个个手工去填这个序号。(我相信在读的各位都没有这样做的啦)

    a4815a0e5fe590875322cf4667e38530.png

    普通青年通常会用“自动填充”完成,像这样:

    ee0ddf1a0273a6b639ec08c7b8c28d92.gif

    如果填充的结果不是你想要的,注意要选择清楚是复制内容还是填充序列。或者,手工填2行,给系统“打个样”,就不存在这种歧义了。

    571481cdcb7d20238315550c37a71fd6.gif

    此外,如果行数较多不便一拖到底的话,在鼠标变成黑色“+”时直接双击,会自动填充到最后一行。

    那么问题来了:如果这个表格以后还需要经常增加和维护,难道需要每次都重复这个填充操作吗?如果删除或者重新调整了数据顺序,也只能从头到尾重新刷一遍?

    d224560471de3f49c7a2e036bcbed34e.png

    对于增加数据的问题,多数人可能会想到的是先提前刷出若干行,留着以后用。这样有多难看我就不细说了,都不忍心放图,自己脑补下吧,如果这个表是需要和领导同事共享的,这样显然万万不可。

    还有个办法,就是在序号单元格用函数判断本行后面是否有数据,没有则说明这一行还没有被启用,显示空白,否则显示序号。但这仍然离不开“提前刷好”,所以具体就不说怎么做了。

    此外,凡是提前刷数据的做法,除了不美观,还都会对表格的自动化功能有影响。

    难道就真的无法实现序号随着数据“自增”?

    方法倒是有,如果网上去搜,多数会建议去用宏命令(编写VBA程序代码),并不适合小白用户,有没有简单就好用的办法呢?

    教你们一个文艺青年的做法。

    1.先定义一个“表格”。

    2162c1ae3cabae235377260df417cf78.png

    演示界面以Excel 2013为例,其他版本可参考实现。在【插入】页签下选择【表格】。

    3a34e703ed35915fffb2e1ba389b57e9.png

    选择好表数据的来源范围。(如果在上一步点【表格】按钮前已经圈选了范围,会自动套过来)

    注意两件事!

    1.【表数据的来源】一定要完整无遗漏(最顶端的大标题【项目情况表】其实与表内容无关,不要选)

    2.【表包含标题】选项中指的是与数据内容有关的“标题”,示例表格中就是从【序号】到【备注】那一行,选中的区域包含这一行,则本项打勾,如果是从数据开始选的(从数据1 广告宣传 那行开始),则不打勾。

    解释一下为什么要定义表格。很多人可能有疑问,难道我平时在用的不就是表格吗?为什么还需要再专门定义?是这样,对于软件来说,我们平时所做的“表格”的只是工作表中的一块“区域”,并没有经过严谨定义,因此无法实现一些高级功能

    e916cc5a70bc1a10be97cc372192a430.png

    如果你照着示例定义后,可以看出来,系统自动给表格加上了深色的标题和隔行变色的数据背景,以及自动筛选功能,即使并不是为了实现自动填充序号,日常使用也会带来很多方便,而且系统提供了多种配色样式可选。

    2.填入公式。

    在序号下方第一个单元格内填入“=ROW()-2”。这里ROW()是获取当前的单元格的行号,“-2”运算是因为图中大小标题分别占用了两行,序号【1】所在的行已经是第【3】行,所以要减去这个差值,以此类推。

    3.将填好的公式填充到当前表格的最后一行。

    因为这次填充的本质上是公式,所以不存在复制还是序列的区别。

    4.测试是否生效。

    2-4步操作详见下图。

    cf8552311dc4b009711c04167c15ed5b.gif

    这样,无论今后这个表增加多少行新内容,序号都会跟着自动填上,而且在中间删除行、重新排序的时候也完全不会乱掉。

    就是想让序号绝对固定怎么办?(删除行则跳号、重排序号也跟着数据走)简单,选中序号列,【复制】-【粘贴为数值】覆盖掉就行了,这样就不是公式变成普通数字了。

    你学会了吗?

    026681be968d88c3114cbf0f7022d8d8.gif


    想获得更多有用技巧请「关注」天才社↑

    ↓请把本文「分享」给用得到的人

    展开全文
  • 全文共2391字,预计学习时长14分钟图源:morioh工作中,无穷无尽的表格有时会令人抓狂。Excel无处不在,即便有着像Python这样的强大工具任你使用,你也难以从中逃脱。...用Python将Excel报表自动化你还在天天Excel...

    全文共2391字,预计学习时长14分钟

    fead8678ad196be27772f19c0958b3c1.png

    图源:morioh

    工作中,无穷无尽的表格有时会令人抓狂。Excel无处不在,即便有着像Python这样的强大工具任你使用,你也难以从中逃脱。

    也许你的老板和同事仍然需要这种简单的方法来访问重要数据。但没关系!那并不意味着你不能通过使用Python来简化Excel工作。

    全程无需使用Excel,逃离报表魔爪!

    用Python将Excel报表自动化

    你还在天天做Excel报表吗?而且还是为不同的客户做着四五次同样的报表吗?不如让Python代劳吧!

    通过使用笔者在数据透视表教程中的数据(https://towardsdatascience.com/a-step-by-step-guide-to-pandas-pivot-tables-e0641d0c6c70),概览一下我们将要自动化到底是什么。

    数据格式将使你期望从公司数据库中获得的内容与客户销售数据相匹配,与你期望从公司数据库中获得的数据相匹配。它按区域细分这些数据,并且创建两个带有格式和图表的汇总表。无需使用Excel!

    9abea5f6dee1ee17ceab7a6599f93bf1.png

    最终的数据流—一个工作簿转化为四个格式化报告 | 图源:Nik Piepenbreier

    加载库

    使用Pandas和Openpyxl。笔者的“用Python自动化这三项(无聊!!!)Excel任务”( https://towardsdatascience.com/automate-these-3-boring-excel-tasks-with-python-666b4ded101b)一文中涵盖了许多关于Openpyxl的内容,对其如何运行提供了详细介绍。

    #Section 1 - Loading our Libraries              import pandas as pd                  fromopenpyxl import load_workbook              from openpyxl.styles importFont              from openpyxl.chart importBarChart, Reference

    你使用如下两个Librariy:

    1. Pandas负责转化数据,并创建初始Excel文件

    2. Openpyxl将工作薄格式化,并插入图表

    加载数据

    加载数据,并概览正在处理的内容。正如笔者所提到的,这些数据与读者从公司数据库系统获得的数据相似。

    #Section 2 - Loading our Data             df = pd.read_excel('https://github.com/datagy/pivot_table_pandas/raw/master/sample_pivot.xlsx', parse_dates=['Date'])             print(df.head())                  #        Date Region                 Type  Units Sales             #0 2020-07-11   East Children's Clothing   18.0    306             #1 2020-09-23  North Children's Clothing   14.0    448             #2 2020-04-02  South    Women's Clothing   17.0    425             #3 2020-02-28   East Children's Clothing   26.0    832             #4 2020-03-19   West    Women's Clothing    3.0     33

    在这里,使用Pandas读取Excel文件,并读取Date/日期列的日期。

    创建数据透视表

    接着我们要创建最终报告中所需要的汇总表。这里将创建一个数据透视表以作为例子,其中仅使用东部地区进行代码的实验。

    #Section 3 - Testing Pivot Tables            filtered= df[df['Region'] =='East']            quarterly_sales= pd.pivot_table(filtered, index =filtered['Date'].dt.quarter, columns ='Type', values ='Sales', aggfunc='sum')                  print("Quarterly Sales Pivot Table:")            print(quarterly_sales.head())                  #Quarterly Sales Pivot Table:            #Type Children's Clothing  Men'sClothing  Women's Clothing            #Date                                                                  #1                   12274           13293             16729            #2                    5496           17817             22384            #3                   14463            9622             15065            #4                  13616           10953             16051

    创建第一个Excel文件

    有了数据透视表后,将其导入到一个Excel文件中,我们将用pandas来导入:

    #Section 04 - Creating and Excel Workbook                 file_path=#Path to where you want your file saved                 quarterly_sales.to_excel(file_path, sheet_name ='Quarterly Sales', startrow=3)

    这一步是在做什么:

    · 创建一个文件路径变量,以确定要将文件存储在何处,

    · 使用ExcelWriter保存文件

    · 将两个透视表保存到单独的工作表中,从第3行开始(稍后从中保留以用于页眉)

    使报表更漂亮

    Pandas有助于将数据导入到Excel中。既然数据已经导入Excel,不妨将其美化一下,来添加一些可视化效果。

    #Section 05 - Loading the Workbook                 wb =load_workbook(file_path)                 sheet1= wb['Quarterly Sales']                  # Section 06 - Formatting the First Sheet                 sheet1['A1'] ='Quarterly Sales'                 sheet1['A2'] ='datagy.io'                 sheet1['A4'] ='Quarter'                  sheet1['A1'].style ='Title'                 sheet1['A2'].style ='Headline 2'                  for i inrange(5, 9):                     sheet1[f'B{i}'].style='Currency'                     sheet1[f'C{i}'].style='Currency'                     sheet1[f'D{i}'].style='Currency'                  # Section 07 - Adding a Bar Chart                 bar_chart=BarChart()                 data=Reference(sheet1, min_col=2, max_col=4, min_row=4, max_row=8)                 categories=Reference(sheet1, min_col=1, max_col=1, min_row=5, max_row=8)                 bar_chart.add_data(data, titles_from_data=True)                 bar_chart.set_categories(categories)                 sheet1.add_chart(bar_chart, "F4")                  bar_chart.title ='Sales by Type'                 bar_chart.style=3                 wb.save(filename = file_path)

    在Section 5中,将工作簿和工作表加载到Openpyxl可以处理的单独对象中。

    而Section 6中操作更多:

    · 在表一的A1和A2单元格中添加标题和副标题。

    · 更改“quarters”列的标题,使其更能反映数据。

    · 对标题和副标题应用样式。

    · 将金融领域的单元格转换为货币。这需要对每单个单元格进行单独处理。因此使用了for循环。

    在Section 7中,添加了条形图:

    · 创建一个条形图对象,并识别存储数据和类别的字段。

    · 随后将数据和类别应用于对象。

    · 最后,添加描述性标题和样式。使用许多不同的样式都试试!

    这就是工作簿现在的样子:

    403888fbd5915fb18744d92c8a09cb72.png

    所得工作簿之一 | 图源: Nik Piepenbreier

    对多个工作簿执行工作流自动化

    虽然已经很方便了,但是仅在一个区域执行这样的操作只能节约一点点的时间。我们可使用for循环,对所有的区域执行此操作。

    #Section 08 - Getting Region Names            regions =list(df['Region'].unique())                  # Section 09 - Looping Over All Regions            folder_path=#Insert the path to the folder you want tosave the reports in                  for region in regions:                filtered =df[df['Region'] ==f'{region}']                quarterly_sales = pd.pivot_table(filtered, index =filtered['Date'].dt.quarter, columns ='Type', values ='Sales', aggfunc='sum')                file_path =f"{path to your folder}{region}.xlsx"                quarterly_sales.to_excel(file_path,sheet_name ='QuarterlySales', startrow=3)                            wb =load_workbook(file_path)                sheet1 = wb['Quarterly Sales']                            sheet1['A1'] ='Quarterly Sales'                sheet1['A2'] ='datagy.io'                sheet1['A4'] ='Quarter'                      sheet1['A1'].style ='Title'                sheet1['A2'].style='Headline 2'                      for i inrange(5, 10):                    sheet1[f'B{i}'].style='Currency'                    sheet1[f'C{i}'].style='Currency'                    sheet1[f'D{i}'].style='Currency'                      bar_chart =BarChart()                data =Reference(sheet1, min_col=2, max_col=4, min_row=4, max_row=8)                categories =Reference(sheet1, min_col=1, max_col=1, min_row=5, max_row=8)                bar_chart.add_data(data,titles_from_data=True)                bar_chart.set_categories(categories)                sheet1.add_chart(bar_chart,"F4")                      bar_chart.                bar_chart.style =3

    在Section 8中,创建了一个列表,其中包含了想要覆盖的不同区域的所有唯一值。

    在Section 9中,在for循环中重复先前的代码:

    · 创建一个新变量,该变量用于保存文件所在文件夹的路径

    · 接下来,使用f-strings将区域名插入到脚本中,使得脚本对每个区域都是动态的。

    5edf0842e76c51d79094184584af1453.png

    图源:unsplash

    Python的好处在于,它可使重复的任务具有可伸缩性。

    想象一下,如果你每天都会收到这份文件,并且每天都要创建这些工作簿。这种方法能帮你节省多少时间!

    988f016b60a4d9e861cb01c87e480e75.png

    留言点赞关注

    我们一起分享AI学习与发展的干货

    如转载,请后台留言,遵守转载规范

    展开全文
  • 5) 自动显示标题行的升降排序标志符(▽降序△升序)并相应排序...............6 6) 点dbgrideh 标题排序..............................................................................................7 7) ...
  • 文章概要Word批量排版及自动生成目录多级标题自动编号(及编号变竖线修复)批量换行(或批量删除空行)图片显示不全Excel批量填充(给1000行数据添加序号如何点两下搞定,如何快捷提取1000人的出生...

    基层工作,常有“办文”“办表”两大内容,许多工作常是重复性的(尤其是操作表格),这就可以利用技巧来提高效率,总结了一些小技巧分享给大家以少加班,本文会不定时更新。

    本文极长极基础,适合一步一步对着做。

    文章概要

    Word

    1. 批量排版及自动生成目录
    2. 多级标题自动编号(及编号变竖线修复)
    3. 批量换行(或批量删除空行)
    4. 图片显示不全

    Excel

    1. 批量填充(给1000行数据添加序号如何点两下搞定,如何快捷提取1000人的出生日期Ctrl+E)
    2. 高级查找(想要查找字体为宋体的单元格,查找王X华,第二个字不确定怎么办)
    3. 定位条件(如何批量删除为空的单元格,数据筛选后不满足条件的也会被复制怎么办)
    4. F4(提高效率,一键重复上一步操作;格式下拉时有时想行不变有时想列不变,那就F4修改单元格引用方式)
    5. 排版打印(批量自适应行高列宽,轻松玩转Excel打印,避免重复录入标题,VBA在指定单元格添加分页符)
    6. 批量在指定位置插入分页符,批量分页
    7. 批量在指定位置插入复制的行
    8. 逻辑判断IF函数(B列输入内容则A列便自动添加递增序号;批量给每一户家庭成员添加序号,不同家庭序号重新从1开始;想对比一列数据的内容在另一列是否出现过;根据身份证号码批量识别性别)
    9. 求和函数(累计求和,单条件求和sumif,多条件求和(sumifs、sum结合数组),衍生:根据身份证计算年龄)
    10. vlookup函数(查找匹配信息,批量按照一人一页制表),一户一页批量制表(将家庭户成员信息批量填入统一样表,一户一页)
    11. 数据透视表
    12. Excel插件链接

    第一章

    办文 Word技巧

    • 大批量内容排版,自动生成目录

    解决办法:利用样式进行,将每一类的样式先设置好(字体、段落),之后选中段落设置样式

    《党政机关公文格式》(GB/T 9704—2012)模板格式见文末链接

    b96ead921db87b473609a3af878af24b.png

    还可利用定义新的多级列表来实现多级标题的自动编号

    7a82c852dadaf855eded9a9036ad5130.png

    选中段落,再点击上方样式来为段落设置样式,可见已自动生成编号(一)、(二)... ... 。

    db603cd25891c0dbd6d03cde2607b085.gif
    其中用到F4重复上一步,注意排版后左侧导航栏已生成级别

    补充:word标题多级自动编号前变成黑色竖线的修复办法

    8bcb2a2092feb3debadf7fcddc1bfbf0.png

    解决办法:

    a57b50a7004439e49ab5ef950ce51e3c.gif
    利用宏代码“(一)”这一级标题已修复

    1.点击菜单栏“视图”,

    2.找到“宏“选项卡,点击后弹出下拉选项中选择”查看宏“

    3.弹出的窗口中输入”宏名(例如repair)“,然后点击”创建“,将上面那段宏代码复制粘贴进去。

    4.在工具栏找到”运行宏“图标(向右的三角形),点击一下就可以运行了。

    宏代码如下:

    Sub repair()
     For Each templ In ActiveDocument.ListTemplates
     For Each lev In templ.ListLevels
     lev.Font.Reset
     Next lev
     Next templ
    end sub

    当段落设置为已自定义的样式后,在引用-目录中即可自动生成目录,目录的样式也可修改。

    80339c0185c28f2993c5bf34b90df7c1.png
    对目录的字体和段落进行设置
    • 批量删除空行/多段合并到一段

    解决办法:选定需要修改的段落,快捷键Ctrl+H,查找^p替换为空。当然也可以将字符替换为^p来实现批量自动换行。

    f8b8a5ebd352cdaa22d3245f37d39904.gif
    • 图片显示不全

    解决办法:选中图片,行距调整为单倍行距

    2b7579bfef0f2d7d4f6a712b52f816bf.gif

    第二章

    办表 Excel技巧 —— 基础的4+1技能

    基础数据整理时的一句忠告:基础数据很重要!!!千万不要合并单元格!!!(不便于数据处理),如果想对其分类就增加一列(填不同的类型)。
    一条记录就一行,一个字段就一列,比如一个人用一行来记录ta的信息,字段(一列)有身份证号、户号、与户主关系、村民小组、地址、手机号等等

    讲函数前先讲一些基础的操作。

    (一)几个快捷操作

    1.快速填充

    如下图,想要添加递增序号,一是可以选中123在右下角出现黑色+号后向下拖动。

    250aaeb5ea4e980d8c596cfa85158e50.png

    如果行数太多,拖动效率就太低了,可以试试:

    (1)双击黑色+号

    要求自动生成序号的相邻列必须有内容,且序列到相邻列内容截止时停止。

    afa9dd16dcf05c0ca115f204600ca188.gif

    (2)Ctrl+D(以复制的方式向下(Down)进行填充,Ctrl+R与之类似<向右Right>)

    还是上图,比如想得到序号+姓名,如“1彭万里”,利用简单的并联函数=C2&B2(这也是一个简单函数,函数都是以=开头,Excel就知道你要输入函数了)可以实现D2单元格“1彭万里”,之后向下拖动或者选中需要填充的区域后Ctrl+D。按住shift点击左键选中连续的单元格,按住Ctrl点击左键选中点击的单元格。

    6c98fb5b1c1bfc88e8a7b684036f4f6d.gif
    Ctrl+D 以复制的方式向下填充

    Ctrl+E(根据规律填充,Excel最强大快捷键)

    仅支持Office2013或以上版本,功能是根据你输入的内容自动联想与左边多列的关系并自动填充,比如上图的合并功能。

    785763f886135f4f318f908594b57b6a.gif

    根据身份证号批量提取出生日期

    13d0b4b32f834b5ec366d9f585ce24d3.gif
    更多Ctrl+E用法大家可以慢慢探索

    2.查找替换

    普通的Ctrl+F就不说了,这里讲一下如何实现一些特殊查找。

    (1)根据单元格格式来查找Ctrl+F后点击右下角选项,设置查找条件,查找黄底色+文本格式的单元格

    d31b4ec3e8552046f23646c252e6183f.gif

    (2)模糊查找,知道有一个人名,三个字,叫彭wan里,但是不确定中间是那个wan,可以试试?来代替wan?表示通配符,代表一个字符,英文状态输入

    f7354fadebfbbe2b1a80f2795ce7b7f5.gif

    3.Ctrl+G和F4

    (1)Ctrl+G定位条件,可以快速选中满足以下条件的单元格

    5fb2915741189e19eb1365fb04dc20e2.png

    以空值为例,快速选中下图出生日期为空的单元格,之后就可以对它们进行操作。

    98297acb03f45bb95a2607e9b24906a6.gif

    以可见单元格为例,使用数据筛选后,进行复制粘贴时常会把不满足条件的也选中,这时就需要使用Ctrl+G,选中可见单元格,再进行复制。

    (2)F4

    i 单元格引用锁定(公式拖动时,想要保持某个单元格不变时需要用到引用锁定)

    下图,在H2中输入=F2按F4,引用状态在四种中变化,F2、$F$2、F$2、$F2。$F2表示引用单元格时引用F列的,行数会变化。

    e172e3647f346cc1dc0bb494392b2f26.png

    ii重复上一步

    f246a564e3c27f663421c08ca5443024.gif
    插入一行,按F4继续上一步,继续插入行

    (二)排版/打印

    1、双击自适应行高/列宽

    6a4c8fb95961050a78c79b45beae013e.gif
    选中之后放在AC列之间,双击十字型按钮,A、B、C三列既自适应列宽,批量自适应行高同理

    2、视图-分页视图

    分页视图下所见的内容(四条蓝色边界线围合的范围)即打印的范围,在打开此视图之前可先在页面布局中设置纸张大小及方向,通过调整4条边界线即可实现将任意区域打印在一页内

    2952cbc0eb5508f670b128fab74842db.png
    鼠标置于蓝色边界线,变成双向箭头后,可拖动修改打印范围

    如果想某一单元格以下打印在下一页中,只需在本单元格插入分页符即可。

    1908fe2494072763022066d90325074a.gif
    页面布局-分页符

    以上设置的是打印区域,接下来调整打印区域在纸中的位置。

    通过调整打印页面的以下参数来控制整个打印区域在一页纸中的位置。

    77b431c8ad38ddd87a98f854683e7d19.png

    9badac12c6d7095fcca1abba358cac4f.gif
    还可设置页面参数、页眉、页脚等

    3、打印标题页

    假如我们需要将每一户人员信息打印在一张表,表头都是姓名、身份证、出生日期、姓氏,那么可以通过设置打印标题,避免每一页录入重复表头。

    362859924a68c8366bf1448f99d5fc38.gif
    首先按户分页(复习F4重复上一步),设置第一行为标题

    补充一个批量添加分页符的VBA代码,判断E列为“户主”即添加分页符。

    Sub VBA_AD()
    Dim Rng As Range
    For Each Rng In Range("E3:E1048576") '从E3到最后一行,可根据实际范围更改,也可使用FOR...NEXT SETP N方式
    If Rng.Value = "户主" Then ' 条件
    ActiveSheet.HPageBreaks.Add Rng '/添加分页符
    End If
    Next
    End Sub

    00368aaddbf88abd59caac91597602de.gif
    每户一页,每一页均带表头

    衍生!!!

    批量在指定单元格插入复制的行 VBA代码,可间接实现打印中的打印底端标题行操作。

    15434cfa7e3e0e5f88c084ea76979c37.png
    前三行需批量插入,插入的位置为A列单元格值为1的行
    Sub ADD_ROW()
    For i = 8 To 1048576 '起止行数
    If Range("A" & i).Value = "1" Then '判断区域为A列
    Rows("1" & ":" & "3").Select '选择第一至三行,即需要批量插入的行
    Selection.Copy
    Rows(i).Select
    Selection.Insert Shift:=xlDown ', CopyOrigin:=xlFormatFromLeftOrAbove
    i = i + 3 '批量插入n行,则改为i=i+n
    End If
    Next
    End Sub

    (三)4+1 Excel技能

    4个函数IF、SUM、SUMIF、VLOOKUP,1个数据透视表技能

    所谓函数,只要知道它会返回一个东西,至于是什么取决于选择什么函数,函数又决定了使用什么参数
    Excel中函数都是以“=”开始,这样Excel便知道你要使用函数而不是录入常规的数据,当录入函数后回车无反应时记得检查单元格格式是否为非文本
    注意:函数中的所有参数均需在英文状态输入!!!

    ——这段话仅针对纯新手,其他请忽略

    1、IF函数

    IF函数是一个最基础的逻辑判断,语法:“=IF(判断条件,结果1,结果2)”,即如果满足“判断条件”则返回“结果1”,如果不满足“判断条件”则返回“结果2”

    (1)下面利用IF函数找出在1996年以后出生的人员

    =IF(YEAR(H2)>=1996,"是","否")

    89df59f8c74cc00ec0a8d28cb06cbdde.gif
    其中YEAR(A2)返回A2日期的年份

    根据身份证号码批量识别性别 =IF(MOD(MID(C2,17,1),2)=0,"女","男")

    95f13346848b0277445fd6824d0b0f59.gif
    MID函数提取身份证号码的第17位,MOD(被除数,除数)计算余数(除数为2,余数为0或1)来判断第17位的奇偶性

    (2)利用IF函数来自动编号,实际工作中就碰见“一开始给数据拉好序号,但由于过程中删减了行,但忘记重新拉序号,导致最后总数和总序号对不上,许多工作又白干还浪费打印的纸”。对B列利用IF函数来判断C列是否为空,不为空就返回序号,如此便实现自动编号(C列输入内容B列就自动递增序号)。

    =IF(C2>0,ROW(B2)-1,"")

    2cec68d30c0ad6426591ff4431f1f746.gif
    ROW(B2)表示返回B2的行数,由于第一行为标题所以要减1,“”表示if判断不成立就为空。

    补充!!!

    按不同类别批量添加递增编号,同一类别下序号递增,不同类别序号重新从1开始递增

    应用实例:居民家庭户,批量给每户内添加递增序号

    =countif($B$2:B2,B2)

    $B$2:B2表示下拉公式时,选中区域始终为B2至当前单元格

    COUNTIF 是一个统计函数,用于统计满足某个条件的单元格的数量。COUNTIF 的最简形式为:=COUNTIF(要检查的区域, 要查找的内容)

    1730cb69f7a010d9c7954c307613e316.gif
    基本思路为:统计本行及以上行,本行B列 值的个数

    (3)利用IF的嵌套还可以实现多重条件判断,=IF(A1<60,"不及格",IF(A1<80,"良好","优秀")),即:

    A1<60,"不及格"

    60<A1<80,"良好"

    A1>80,"优秀"

    辅之以AND、OR函数可进行多条件并列判断,=IF(AND(A1>60,B1<100),"合格","不合格")

    注:and()表示括号内的多个条件要同时成立,or()表示括号内的多个条件任一个成立

    (4)衍生用法 查找对比一列的内容在另一列中是否出现过

    应用实例:有一个村民小组的人员信息以及非农业人口的信息,想查找对比这些村民哪些属于非农。

    =IF(COUNTIF(F:F,C2&"*"),"非农","") (函数内需要返回文本时需要用"")

    C2&"*"表示将C2强制识别为文本,对于身份证号码若不加入&"*"会出现误判,将340111195509176013和340111195510205026识别为相同,加入&"*"后需内容完全一致才返回一致。

    39ef62f5ae920c63e47f5e4f73ea73e9.gif
    COUNTIF 是一个统计函数,用于统计满足某个条件的单元格的数量。COUNTIF 的最简形式为:=COUNTIF(要检查哪些区域, 要查找哪些内容)

    IF函数是最基础的逻辑判断,通过与其他函数的配合或自身的嵌套可以实现复杂逻辑判断,即通常说的查找满足条件的区域/单元格。

    应当注意的是:IF函数判断后返回的不只是值,还可以根据条件返回区域引用。如:=VLOOKUP(A1,IF(B1=1,C:D,F:G),2,0)

    更多IF函数用法可以参考下面的链接:

    玩转初级会计:IF函数的所有公式(入门+进阶+高级)zhuanlan.zhihu.com
    f89809440d3f7d7dbe01afbb1e03df3b.png

    2、SUM函数

    SUM,求和函数,语法:“=SUM(number1,[number2],...)”,number表示要相加的数字,也可以选中一片区域。

    (1)常规的求和,输入函数名选取区域即可

    8c3d1d438a0eb1567787cdf25f75e8b7.gif

    (2)SUM函数结合数组来进行多条件求和

    =SUM((H7=C1:E1)*(B2:B13=G7)*C2:E:13),数组公式结束时切记以Ctrl+shift+enter结束

    3fe56586d5c8c1828f52941af0f0bc50.gif

    下面将数组的运算过程拆分一下,以便理解。H7为英文,则(H7=C1:E1)即为(0,0,1);G7为1班,则(B2:B13=G7)为(1,0,0,1,0,0,1,0,0)此处为列,数组运算结果如下:

    28c00823c207c21b457f6d790251905d.gif
    其中用到F4,调整引用状态为绝对引用

    更多SUM函数用法可以参考下面的链接:

    Excel函数公式:Excel函数公式:含金量超高的SUM函数7大实用技巧解读zhuanlan.zhihu.com
    804887658565108b88dbb109e3698171.png

    3、SUMIF

    (1)SUMIF,对 范围 中符合指定条件的值求和。 例如,如果某列中含有数字,你只需对大于 5 的数值求和。 可使用以下公式:=SUMIF(B2:B25,">5")

    =SUMIF(判断区域,判断条件,求和区域)

    年龄50岁以上人员 土地租金和为例, =SUMIF(D2:D14,">50",F2:F14)

    b669abc30c43c28da232d9a5367a0470.gif

    (2)SUMIFS 函数,SUMIF的加强版,用于计算满足多个条件的全部参数的总量。

    SUMIFS(需求和的单元格区域,使用判断条件1的区域, 判断条件1, [使用判断条件2的区域, 判断条件2], ...)

    注意与sumif的区别,sumifs把求和区域放到第一个参数!!!

    年龄89岁的非农人员 土地租金和

    根据身份证号码提取年龄

    =YEAR(NOW())-MID(C2,7,4)+1

    (YEAR(NOW())提取当前日期的年份,截取身份证号码中从第7位开始的4位数即出生年份)

    提取年龄后,计算89岁非农人员的土地租金和。

    =SUMIFS(G2:G14,E2:E14,89,F2:F14,"非农")

    1a7bc0d0835aeda9bbb2500d402dbc8a.gif

    4、VLOOKUP

    一个按列纵向查找函数,与LOOKUP、HLOOKUP为一类,应用极广泛。

    =VLOOKUP(要查找的值,要查找的区域,返回数据在查找区域的第几列数,模糊匹配/精确匹配)

    要求:要查找的值在查找区域的第一列!!!(下例中,查找区域中,身份证号码需在第一列

    根据身份证号查找户口性质(还在一个一个找吗?VLOOKUP可以一键搞定),绿色区域是身份证号和户口性质全部数据,类似于在一个完整的库里面找部分人员的信息。

    =VLOOKUP(E2,L:M,2,0)

    563d1f9d3a4b469f1f1c78770c489554.gif
    WPS中输入函数时,参数有汉字提示,降低使用难度

    衍生功能!!!

    (1)批量制表,一人一页

    适用范围:表头表尾固定不变、一张表只填入一个人信息(一行信息)(一表填入多人信息见下)

    baf036f2fe1afdfea6b983dec5436101.png
    左边绿色区域是数据源,右边是需要批量填写的表格样式

    88f1845379212634036e7d1594e743bb.gif
    思路:借助Q辅助列,在Q3输入1;在A-G列查找序号为Q列值来填入第一行数据,形成第一个表(第一行数据填入样表)之后选中整个表,之后下拉Q列生成递增序号1,2,3. 刚好对应查找序号1,2,3;之后调整一下打印边界即实现一人信息录入一表,通过批量插入分页符进一步实现一表一页

    (2)批量制表,一户一页

    shoesblockd:批量制表,一户家庭一表(一页)zhuanlan.zhihu.com
    fc28dd487056aff57a4624d14d9d66d2.png

    5、1个数据透视表技能

    功能:数据统计分析

    选中数据-点击插入 数据透视表-选中放置数据透视表的位置

    0c62de5a475e87729aa4fae3056e902b.png

    位置选择现有工作表:Q1,插入结果如下图所示

    f1e4ddff21baeff447ad94121d99db42.png

    第一行自动识别为数据透视表字段

    鼠标左键选中某一字段,按住不放拖动到行或是列,左边数据透视表便自动生成了统计结果

    658e3daa13e82ce4e41a88ee4403b536.png
    统计每个名字出现的次数

    将字段拖入列时,这样就可以统计满足两类条件的值

    473b12cb598400d75ed34429671610ae.png

    值可选择的计算类型有以下几种,单击计数项-值字段设置可改变统计类型

    4a7439b13c3e62a750ec4145f82f024b.png

    衍生功能:统计一列中数据类型种数(实例:根据户号的不同计算有多少户数)

    (1)数据透视表:选中户号列、插入数据透视表、把户口字段拖入行位置再拖入值位置、选中数据透视表行标签列,由此便有统计结果

    832664f9b4fcb388dc6db5532ba3adc7.png
    统计户号共1,2,3,4四种类型,即四户

    (2)函数

    =SUMPRODUCT(1/COUNTIF(被统计范围,被统计范围&""))

    此为数组,以Ctrl+Shift+Enter结束

    此函数对CPU要求高,计算量大,数据量大容易死机!!!

    &"":增加次为了将数据强制识别为文本,避免出错

    留意此统计结果,空单元格也算一种类型

    (3)数据展示

    1. 选中数据透视表结果,插入图表
    2. 选中数据透视表结果,插入切片器(选择切片器)

    296b86704c3c1d90beac6212ce493828.gif
    柱状图横坐标为村名小组,纵坐标为人数,切片中改变户口性质、性别、户主关系,村名小组-人数的柱状图便自动更新

    工欲善其事必先利其器

    公文模板及两个Excel插件(EasyCharts图表制作插件方方格子工具箱)链接

    链接:https://pan.baidu.com/s/1I_6JPm4_d6bHA8GRDzxLZQ

    提取码:hhtq

    展开全文
  • 2019-02-02 回答学生表 隐藏表格的一行 固定表格第一行 如果access数据库表中有一个字段是自动增加的序号如何插入一行 将a表的aa列求和. 为一行,追加到b表的bb列(bb列已存在).用access语句怎么写? 为什么报表中...
  • 根据规律的重复的姓名列产生自动序号 排名的函数 自动排名公式 百分比排名的公式写法为: 平均分及总分排名 求名次排名 排名次根据分数进行普通排名 对于普通排名分数相同时,按顺序进行不重复排名 依分数比高低名次...
  • EXCEL函数公式集

    热门讨论 2010-03-16 03:26:38
    根据规律的重复的姓名列产生自动序号 排名的函数 自动排名公式 百分比排名的公式写法为: 平均分及总分排名 求名次排名 排名次根据分数进行普通排名 对于普通排名分数相同时,按顺序进行不重复排名 依分数比高低名次...
  • 我在前一文中,已经介绍了如何用网页表大师批量注册志愿者了, 一个个志愿者... 第一步:首先一个如下格式的表格,存为CSV表或EXCEL表都可以: 序号,姓名,身份证号 1,孟庆民,372522194909096115 2,吴玉良,3...
  • word使用技巧大全

    热门讨论 2011-03-18 20:37:53
    四、如何自动生成目录? 6 五、奇偶页显示不同内容 7 六、在页眉中显示章编号及章标题内容 7 七、修改页眉中的划线格式 7 八、在页眉和页脚中插入章节号和标题 8 九、分隔符 8 十、目录生成教程 8 十一、怎样使首页...
  • C#编程经验技巧宝典

    热门讨论 2008-06-01 08:59:33
    2 <br>0003 设置程序代码行序号 3 <br>0004 开发环境全屏显示 3 <br>0005 设置窗口的自动隐藏功能 3 <br>0006 根据需要创建所需解决方案 4 <br>0007 如何使用“验证的目标架构”功能 4 ...
  • (1)题注中新建标签时,Word会自动在标签文字和序号之间加一个空格,看起来不那么舒服,可以在插入题注后将空格删除,然后再将文字成书签。 (2)书签名最好用图(表、公式)的说明文字,尽量做到见名知“图...
  • 双击摄像机节点自动播放视频,双击节点自动依次添加视频,会自动跳到下一个,双击父节点自动添加该节点下的所有视频。 摄像机节点拖曳到对应窗体播放视频,同时支持拖曳本地文件直接播放。 视频画面窗体支持拖曳交换...
  • MAPGIS地质制图工具

    2013-05-06 16:15:30
    3、如果要自定义表格转到MapGis范围大小,在点菜单Excel->MapGis后,框选输入数据的范围,数据输入MapGis后会自动调整数据(文本,表格)大小。 4、在较多文字的时候,如果一行写不下,将自动转为版面输出(和上一点...
  • JAVA面试题最全集

    2010-03-13 13:09:10
    在图形界面中,一个表格如何实现编辑单元格时弹出下拉框? 65.如何加载图片? 66.什么是模态对话框? 67.阐述MVC的概念 68.GUI布局管理器的使用,FlowLayout,BorderLayout,GridBagLayout 69.如何构造一棵树?...
  • SQL语法大全

    2014-03-30 11:00:11
    MAX(字段名) 取得一个表格栏最大的值 MIN(字段名) 取得一个表格栏最小的值 SUM(字段名) 把数据栏的值相加 引用以上函数的方法: sql="select sum(字段名) as 别名 from 数据表 where 条件表达式" set rs=conn....
  • Axx:ARJ压缩文件的分包序号文件,用于将一个大文件压至几个小的压缩包中(xx取01-99的数字) A3L:Authorware 3.x库文件 A4L:Authorware 4.x库文件 A5L:Authorware 5.x库文件 A3M,A4M:Authorware Macintosh...
  • •进程管理:又称处理机管理,实质上是对处理机执行“时间”的管理,即如何将CPU真正合理地分配给每个任务。 4、表征操作系统的属性 主要有:响应比,并发性,信息的共享、保密与保护,可扩充性、可移植...
  • 软件测试规范

    2018-04-23 09:16:12
    软件测试规范 目 录 一.概述 ...............................................................................................................................................................
  • Proteus仿真—40个单片机初学程序.

    热门讨论 2009-04-13 13:00:56
    作为单片机的指令的执行的时间是很短,数量大微秒级,因此,我们要求的闪烁时间间隔为0.2秒,相对于微秒来说,相差太大,所以我们在执行某一指令时,插入延时程序,来达到我们的要求,但这样的延时程序是如何设计呢...

空空如也

空空如也

1
收藏数 20
精华内容 8
关键字:

做表格如何自动序号