中企动力 > 商学院 > excel自动
  • ?

    「Excel实用技巧」Excel表格 自动 排序,牛了!

    Troon

    展开

    国庆假期结束,上班第一天,必须要分享一个亮眼的Excel技巧:

    让Excel表格自动排序

    怎么自动排?先看效果:

    从网上下载的2018年各大城市最新平均工资排行表

    当我改动其中一个城市的平均工资时,比如广州由7965改为10000,整个表格的顺序会自动调整:

    是不是很神奇的样子,怎么做到的?

    本技巧用到了两个VBA编程知识:录制宏和事件程序。别怕,你不用写代码,只需要简单2步,新手也可以轻松完成!跟一起做吧:

    1、点击左下角的“录制宏”图标(点击后图标会变成小四方块),弹出的窗口直接点确定(记住宏名称)。选取表格区域(从数字列向左选取)后点排序命令。最后点小四方块停止录制(这一步很重要,不要忘了点)

    2、在工作表标签上右键 - 查看代码 ,在弹出的窗口中左侧下拉框中选取worksheet,右侧下拉框中选取Change,然后在自动生成的代码中间插入一行,输入第一步中出现的宏名称(宏6),关闭弹出的代码窗口。

    设置完成!你修改数据后,表格就可以自动调整顺序了:

    注:

    表格中因为含有宏代码,所以在保存时会弹出一个提示窗口,点否并保存为Excel 启用宏的工作簿类型。删除自动排序功能方法:另存为xlsx格式即可。

    Excel解释说明:今天借自动排序学习了录制宏功能的使用。什么是录制宏,说白点就是把你操作的步骤录制下来,需要用的时候重复一次(本例是借用工作表事件自动触发)。

  • ?

    Excel自动批量生成目录, 这个方法太实用了!

    白宫苴

    展开

    平时都和大家分享了很多关于Excel方面的使用技巧,之前有个群友问到一个这样的问题!

    主要是想实现自动获取工作表名,而且要链接到对应的工作表,之前有和大家分享过超链接函数HYPERLINK。但是用函数的话是不能满足这个群友问的问题,只能一个个去链接操作,那么今天关于这个问题为大家分享一个使用技巧吧!

    第一步:我们先在Excel工作薄的前面新建一个工作表,命名为Excel技巧目录

    这个工作薄里面包含了很多张工作表,

    第二步:定义名称

    把光标放在对应B1单元格,然后选择公式下方的定义名称。在引用位置书写如下的函数,

    =INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW())然后点击确定即可。

    第三步:然后在B1单元格编辑函数

    在B1单元格中输入这样的函数:=IFERROR(HYPERLINK(索引目录&"!A1",MID(索引目录,FIND("]",索引目录)+1,99)),""),然后往下填充就可以获得相应的工作表标题了!

    而且点击后也能实现对应工作表的链接!

    最后可以按照直接的想法去美化设置一下,一个完美的Excel目录就完成了!

    希望这篇文章能帮助到大家!

    作者:小菜,一个热爱学习的人,对Excel情有独钟的人,一个善于终结分享的人……

    如果你是新朋友,扫码关注下方二维码,便每天可以和小菜一起学习,一起提升技能!当然大家也可以关注技巧分享,学习更多办公技巧哦!

    每天一起学习,一起进步。

  • ?

    Excel如何从身份证号码中提取年龄

    晶莹

    展开

    因为年龄每年都会改变,所以Excel表格中储存的人员年龄每年都会不同,要保证表格中人员年龄的准确性,需要每年对表格内容进行更新,但要是每年都手动逐个更新表格中的年龄,工作量会比较大。如果表格中存有身份证号码,我们可以根据身份证号码中的出生年月信息计算出这个人的实际年龄,而且这个年龄会根据电脑中的时间在第二年自动更新,从而可以避免手动更新的麻烦。这里介绍一下操作方法,以供参考。

    例如要根据下图表格中的身份证号码计算出实际年龄:

    先在“年龄”列的第一个单元格中点击鼠标左键,选中这个单元格 。

    选中单元格后,在Excel的编辑栏中输入 =YEAR(NOW())-MID((需要时可以把以上要输入的内容复制粘贴到Excel中,以避免输入字符的麻烦。)

    输入以上字符后,用鼠标点击同一行左侧包含身份证号码的单元格。

    点击单元格后,编辑栏中会自动输入该单元格的名称。

    然后在单元格名称后再输入 ,7,4)这样即输入完成了根据身份证号码自动计算年龄的公式,整个公式为“=YEAR(NOW())-MID(C3,7,4)”。其中的“YEAR(日期值)”可以得到某个日期中的年份;“NOW()”可以得到当前电脑系统中的日期;“YEAR(NOW())”即可得到当前电脑系统中的年份;“MID”可以从字符串中得到指定数量的字符,“MID(C3,7,4)”表示得到从C3单元格中第7位字符开始的共4位字符,即身份证中的出生年份信息。这样,用“电脑中的年份”减去“身份证中的出生年份”即得到该人员的实际年龄。当然,前提是电脑系统的时间要准确。

    输入公式后按键盘的回车键或者鼠标点击编辑栏左侧的对号按钮。

    这样,输入公式的单元格中就会自动显示出对应身份证的实际年龄。

    再将鼠标指针指向该单元格的右下角,当鼠标指针变成十字形状时双击鼠标左键或者向下拖动鼠标至列尾。

    这样,年龄列中就会自动计算并且显示出各身份证对应的年龄了。

    注:要保证计算年龄结果的正确性,必须让电脑系统的时间保持准确。

  • ?

    让Excel自动添加表示数据上升、下降的箭头

    Jin

    展开

    在用Excel对数据进行同比或者环比统计时,在统计结果中添加表示数据上升或者下降的箭头看起来会更加直观。Excel有多种添加上升、下降的箭头的方法,例如可以手动添加、可以用自定义单元格格式的方法添加、还可以用VB代码添加,但笔者感觉这些方法操作起来或者比较繁琐、或者专业性较强。这里以Excel2010为例介绍一种笔者感觉相对比较简单的用条件格式添加上升、下降的箭头的方法,以供参考。

    先选择要添加上升、下降箭头的单元格范围。

    点击Excel“开始”选项卡中“条件格式”下拉菜单中的“图标集”。

    在图标集的子菜单中点选一个上升、下降箭头样式。

    点选箭头样式后,之前所选单元格范围中的数据前就自动添加表示上升、下降的箭头了。如果统计结果中没有0值,则箭头就添加完毕了。但如果结果中有0值,有时会发现0值被标上了上升或者下降的箭头,而通常我们需要把0值标上水平的箭头。这时我们需要按下述方法再设置一下。

    再选择添加上升、下降箭头的单元格范围。

    点击“开始”选项卡中“条件格式”下拉菜单中的“管理规则”。

    弹出“条件格式规则管理器”对话框后,点击其中的“编辑规则”选项卡。

    点击“编辑规则”选项卡中下图所示的第一个选择大于小于号的下拉框。

    点选下拉菜单中的“>”号。

    点选“>”号后,在右侧的值输入框中输入“0”。

    在值输入框右侧的“类型”下拉菜单中点选“数字”。

    按以上方法,把第二行中的各项依次设置成“>=”、“0”、“数字”。

    此时第三行会自动被设置成“当<0”。

    设置好后,点击“确定”按钮。

    再点击一次“条件格式规则管理器”对话框中的“确定”按钮。

    这样,所选范围中的0值就会自动添加水平样式的箭头,其他数值会自动添加上升、下降箭头。

  • ?

    从零开始学Excel——自动填充功能的使用(九)

    平庸

    展开

    一、 少量填充。

    A、 在单元格内输入首位序数,左键点击单元格,同时按住“Ctrl”不要松,将鼠标从中心移至该单元格右下角边框,等到空心十字架变为实心十字架,往下拉动单元格进行填充即可。

    B、在单元格内输入首位序数,左键点击单元格,将鼠标从中心移至该单元格右下角边框,等到空心十字架变为实心十字架,往下拉动单元格,拉到适当列数,点击右下角的下拉菜单,选择填充序列。(也可根据自身实际情况,选择其他选项)

    注:直接下拉单元格,仅仅表示复制,若要复制可用此操作。

    C、在单元格内输入首两位序数,然后移动鼠标,等到空心十字架变为实心十字架,再往下拉即可。

    二、 多量填充

    1、 填充数字。

    在单元格内输入首位序数,选定该单元格,然后在开始界面,寻找编辑一栏,再选择填充功能(在自动求和与清除功能的中间位置),点击下拉菜单,选择系列,会弹出如下界面

    序列产生在:表示顺序是按行填充还是列填充(根据自身情况选择)

    类型:自动填充的规律是按照什么规律填充。

    步长值:这一个序列和下一个序列的间隔值(指公差和公比)

    终止值:该序列的最后一个序列数。

    日期单位:该序列是按日(工作日、月、年)进行递增(类型选择日期时可选择)

    然后根据自身实际情况进行选择即可

    2、 填充日期

    在单元格内输入首个日期,选定该单元格,然后在开始界面,寻找编辑一栏,再选择填充功能,点击下拉菜单,选择系列,然后会弹出序列界面(类型直接为日期),然后再根据自身需求进行选择即可。

    三、 两端对齐

    1、 将多行对齐为一行

    改变行宽,使得单元格一行能容纳下多行的内容,接着选定单元格,然后选择填充功能,点击下拉菜单,再点击两端对齐即可。

    2、 将一行变为多行。

    选定你需要两端对齐的单元格,调整单元格列宽,使其刚好能容纳一个字母,再选择填充,点击下拉菜单,再点击两端对齐,接着会跳出如下画面,点击确定即可。

    注:字母一行占几个可以根据自己需要调整列宽再对齐。

  • ?

    excel自动生成连续日期

    是非心

    展开

    在我们办公使用excel表格,一些数据需要用到日期的,特别是一行一个日期的,那么我们怎么设置excel自动生成连续日期呢?今天让我来教大家吧。

    excel自动生成连续日期

    输入第一个日期,然后让excel自动生成连续日期其实是excel自带的一种简单的功能,如果我们仅仅是想实现表格中的一列自动生成连续日期的话,简单,我们先在第一行输入一个日期。

    然后鼠标移动到这个日期的右下角,当鼠标箭头变成黑色的加号时,点击鼠标左键不要放开,然后往下拉,到自己想要连续日期停止的那一行时,再放开鼠标左键,这样excel连续日期就生成了。

    当然了如果我们想要日期一致的话,在第一个日期的右下角下拉的过程中,要按住键盘左下角的ctrl键,这样自动生成连续的日期就会一致。

    当然了这个日期是系统默认日期格式,如7月24,如果我们想要改变连续日期格式的话,就需要先对第一个单元格的日期做改变。首先鼠标选中第一个日期,然后鼠标右键选择设置单元格格式。

    然后在单元格格式数字菜单下的分类中选择日期,然后在右侧我们就可以看到很多日期的格式,选择一种我们需要的日期格式确定就可以了。

    确定后我们的单元格日期就改变了,然后我们再通过下拉的方法设置下方其它单元格的连续日期。excel自动生成连续日期的方法是不是很简单,你也试一试吧。

    霸气走火就是我,我就是百度经验作者:ixlxt7Xzsl7,我自己原创的。

  • ?

    Excel怎么自动显示出数据排名的名次(不改变单元格次序)

    舞雨火

    展开

    在用Excel对一些数据进行排名统计(例如对学生的考试成绩、产品的销量、利润等进行排名)时,许多人可能首先会想到使用Excel的排序功能,但使用排序功能后,原表格的单元格次序会发生变化。如果排名时不想改变单元格次序,只是想显示出对应的名次,可以考虑使用Rank函数,下面介绍如何操作,以供参考。

    例如要显示出下图表格中各成绩排名的名次:

    先在表格第一行要显示名次的单元格中点击鼠标,选中该单元格。

    然后在编辑栏中输入:=rank(

    输入后用鼠标点击左侧包含成绩的单元格。

    点击后,编辑栏中就会自动输入所点击单元格的名称,再在其后输入一个英文的逗号。

    输入逗号后,再选择成绩列中所有包含成绩的单元格,可以用鼠标左键点击成绩列上方第一个包含成绩的单元格,然后按住鼠标左键不放,向下拖动鼠标至该列的最后一个单元格,然后松开鼠标左键。如果成绩列的单元格较多,也可以用键盘的Shift键配合鼠标点击首尾单元格等方法进行选择。

    选择单元格范围后,代表单元格范围的字符就自动输入到编辑栏中,此时立即按下键盘的F4键(目的是把单元格的相对引用变成绝对引用)。

    按键盘的F4键后,代表单元格范围的字母和数字前就都加上了$符号,即变成了绝对引用。

    这时再输入后面的括号,即完成了函数公式的输入。

    完成公式输入后,按键盘的回车键或用鼠标点击编辑栏左侧的对号按钮。

    此时第一行的名次单元格中就会显示出其左侧成绩对应的名次。然后将鼠标指针指向该单元格的右下角,当鼠标指针变成十字形时双击鼠标左键或者按住鼠标左键向下拖动鼠标至列尾。

    这样,名次列各单元格中就会显示出其对应成绩的排名了。

  • ?

    Excel如何自动生成序号、编号

    祖若血

    展开

    用Excel编辑表格时,经常需要给表格添加序号,如果要添加的序号较多,则一个一个的手动输入序号会浪费一定的时间。我们可以利用Excel中的一些功能,让Excel自动给表格添加序号,这些功能有的适合表格行数列数较少的情况,有的适合表格行数列数比较多的情况。下面以Excel2007为例介绍具体的操作方法,供大家参考。

    方法一、输入序号的前两个数字后拖动填充柄自动添加序号。

    ●先在要添加序号的列或者行的前两个单元格中输入序号的前两个数字(一般是1和2)。然后用鼠标框选这两个单元格,再将鼠标指针指向框选区域的右下角,当鼠标指针变成十字形状时停止移动鼠标,此时鼠标指向的就是Excel的填充柄。

    ●此时按住鼠标左键不放,向下(序号在列)或者向右(序号在行)拖动鼠标至列尾或者行尾,则序号就会自动添加了。

    方法二、输入序号的第一个数字后用Ctrl键配合填充柄添加序号。

    ●先在要添加序号的第一个单元格输入序号的第一个数字(一般是1),再将鼠标指针指向该单元格的右下角,当鼠标指针变成十字形状时停止移动鼠标。然后按住键盘的Ctrl键不放,再按住鼠标左键拖动鼠标至要添加序号的最后一个单元格即可。

    方法三、输入序号的第一个数字后用用鼠标右键拖动填充柄添加序号。

    ●开始的步骤和方法二相同,先输入序号的第一个数字,再将鼠标指针指向该单元格的右下角,当鼠标指针变成十字形状时停止移动鼠标。之后不同的是要按住鼠标右键不放,拖动鼠标至要添加序号的最后一个单元格。

    ●此时松开鼠标右键,会弹出一个菜单,鼠标左键点击菜单中的“填充序列”选项,则序号就会自动添加了。

    方法四、用Excel的序列功能添加序号(适用于要添加的序号较多时)

    如果表格中要添加序号的行数或者列数较多,则用上述方法拖动鼠标添加序号可能也需要一定的时间,这时我们可以利用Excel的序列功能添加序号。用Excel的序列功能添加序号还可以分为两种情况:

    1.不知道序号的结尾数字时:

    如果不知道要添加多少个序号,也就是不知道序号的最后一个数是什么,可先选择要添加序号的单元格范围,如果单元格较多,不方便拖动鼠标框选,可使用结合键盘Shift键的方式进行选择,方法是:点击要添加序号的第一个单元格,然后滚动Excel表格,找到要添加序号的最后一个单元格,按住键盘“Shift”键,点击要添加序号的最后一个单元格,则所有要添加序号的单元格就会全被选中。

    ●再依次点击Excel“开始”选项卡右侧的“填充”→“系列”。

    再依次点击开始选项卡中的“填充”→“系列”。

    ●此时会打开“序列”设置对话框,可参考下述设置方法:

    “序列产生在”选项:Excel会根据之前的选择范围自动设置“序列产生在”选项,即如果之前选择的是一列,就会自动设置成“列”,如果之前选择一行,就会自动设置成“行”。

    “类型”选项:一般选择等差序列。

    “步长值”选项:即相邻序号之间的差值,通常设置成“1”。

    “终止值”选项:可以为空。

    设置好各选项后,点击“确定”按钮,Excel就会自动根据选项为表格添加序号。

    2.知道序号的结尾数字时:知道序号的结尾数字时:

    如果已经知道要添加多少个序号,即知道了序号的最后一个数是什么,可以不必选择要添加序号的单元格范围,让Excel自动根据设置添加对应数量的序号。

    ●先输入序号的起始数字(一般是1),然后让该单元格处于被选中的状态。

    ●再依次点击“开始”选项卡右侧的“填充”→“系列”打开“序列”设置对话框。其中与上述设置的不同之处在于:

    “序列产生在”选项:因为之前未选择行和列,“序列产生在”选项一般会被Excel设置为默认选项-“行”。如果要在列中添加序号,需要点击选择“列”选项。

    “终止值”选项:因为已知道序号的结尾数字,可以在“终止值”选项处输入序号的结尾数字。为减小图示图片的大小,本文设置成“8”,实际应用中可以设置成更大的数。

    ●设置好各选项后,点击“确定”按钮,Excel就会自动根据选项为表格添加序号。

  • ?

    让Excel自动调整序号,删除、添加行列后仍保持序号连续不中断

    Giles

    展开

    之前曾发文介绍过几种在Excel中自动添加序号的简便方法,但简便的方法在添加或者删除行、列后序号会中断,不连续了,这时又需要重新调整序号。如果要经常修改表格,增加或者删除行列,则反复调整序号也会影响工作效率。最近学习了一种利用函数在Excel中自动添加序号的方法,这种方法虽然在建立序号时有点复杂,但能够一劳永逸,可以实现让Excel在删除、添加行列后自动调整序号。下面以Excel2007为例介绍如何操作,以供参考。

    这里介绍两种方法,一种可以完全实现在删除、添加行列后自动调整序号,但需要先建立“Excel表”。另一种方法是不建立“Excel表”,但只能实现在删除行、列后自动调整序号。在添加行、列后还需要手动操作一下。

    方法一:在“Excel表”中实现完全自动调整序号。

    1、先建立“Excel表”。

    这里的“Excel表”(较早的Excel版本中称为“Excel列表”)不同于Excel的工作表,“Excel表”的功能是可以让在“Excel表”中的数据独立于Excel工作表中的其他行和列,从而让Excel单独管理其中的数据。建立“Excel表”的方法是:

    ●先选中要编辑的表格范围(选择之后还可以通过添加行、列的方式扩大“Excel表”范围)。

    ●然后点击插入选项卡中的“表”按钮。(其他的Excel版本中该按钮的名称和位置可能不同,如Excel2010中该按钮的名称为“表格”)

    ●点击后会弹出对话框,其中有一个“包含标题行”选项。

    ●如果勾选了“包含标题行”选项,Excel会自动将“Excel表”中的第一行设置成带下拉按钮的标题行。

    ●如果不勾选“包含标题行”,Excel会自动给“Excel表”添加一个标题行,则选择范围会自动多出一行。

    ●点击对话框中的确定按钮后,Excel会将选择的范围建立成“Excel表”并自动设置成默认的样式。(如果对表格的样式不在意,可跳过下面几个步骤,直接看小标题2。)

    如果不喜欢Excel的默认样式,可以用其他颜色取代表中的填充颜色和线条颜色(但笔者的Excel中不能选择“无填充颜色”设置“Excel表”,选择“无填充颜色”后笔者的“Excel表”的颜色不改变,只能选择白色来代替无填充颜色。)

    ●如果不想要最上方带下拉按钮的那一标题行,可以在建立“Excel表”时不勾选“包含标题行”,建立好“Excel表”后,在“Excel表”中点击鼠标,此时Excel会显示“设计”选项卡,再点击取消勾选“设计”选项卡中的“标题行”选项,则标题行就取消了。

    “Excel表”的神奇之处在于当在“Excel表”中添加行或者列后,Excel会根据“Excel表”单元格中的公式内容,自动在新增加的单元格中填充公式。

    2、表格的序号产生在列中时(即序号在表格中是纵向排列的)用“ROW”函数生成序号:

    如果序号产生在列中(在表中纵向排列)。则需要使用“ROW”函数,“ROW”函数可返回单元格所在的行号。

    例如在“C3”单元格中(其的列号为C,行号为3)输入“=ROW()”,然后按回车键,则C3单元格中会显示“3”即该单元格的行号。(注:ROW后的括号必须要有,这是Excel规定的语法。)

    ●那么要想在“C3”单元格中显示序号1,则应输入“=ROW()-2”,再按回车键。

    ●然后用拖动填充柄或者选择性粘贴公式的方法复制公式到其他序号所在的单元格中,但填充或者粘贴的范围不要超出之前建立的“Excel表”范围。这时,再删除或者添加序号所在的行时,Excel就会自动调整序号,保持序号的连续性。

    3、表格的序号产生在行中时(即序号在表格中是横向排列的)用“COLUMN”函数生成序号:

    如果序号产生在行中(在表中横向排列),则需要使用“COLUMN”函数,“COLUMN”函数可返回单元格所在的列号。其原理和“ROW”函数相同,只是在确定生成序号的公式时,要先将代表列号的字母转换成对应的数字。

    ●例如想在“C3”单元格中显示序号1(C列对应数字为第3列),则公式应为“COLUMN()-2”。

    注意事项:

    ●必须在“Excel表”范围内进行添加或者删除行、列的操作才能实现自动调整序号的功能。要想确认某个单元格是否在“Excel表”内,可以先点击选择该单元格,然后查看Excel是否显示“设计”选项卡,如果显示“设计”选项卡,说明该单元格是在“Excel表”范围内,如果不显示“设计”选项卡,说明该单元格不在“Excel表”范围内。

    ●如果同一行或者列中有两个“Excel表”,则在行号或者列标上点击鼠标右键后弹出的右键菜单中添加、删除行、列的选项会呈灰色、不能用。此时可以在“Excel表”中的单元格上点击鼠标右键,在右键菜单中会出现添加表行、表列和删除表行、表列的选项。

    方法二、不建立“Excel表”,直接输入公式生成序号。

    也可以不建立“Excel表”,直接输入方法一的公式生成序号,但这样只能实现在删除行、列后自动调整序号。在添加行、列后,序号之间会出现一个空单元格。

    此时只需拖动填充柄将空单元格上方的序号公式填充到这个空单元格中,则序号仍是连续的。如果添加行的操作较少,用这个方法也比较简便。

    除了“ROW”和“COLUMN”函数外,Excel中也可以利用其他的函数实现自动调整序号,但笔者感觉这两个函数用起来相对简单一些。如果读者有其他更好的方法,欢迎交流。

  • ?

    excel自动换行怎么实现

    纪无色

    展开

    我们在使用excel表格办公的时候经常会遇到这种问题,那就是在excel单元格里打字到后面是不会自动换行的,这样我们后面的文字可能被隐藏了,那么遇到这种我们需要excel自动换行的情况我们该怎么设置呢?今天就让我来教大家excel自动换行怎么设置吧。如图在我们没有设置单元格自动换行的时候,添加的文字是会自动覆盖住后面的单元格的,如果后面的单元格没有内容的话,而后面的单元格有内容的话,那么前面单元格内的大部分文字就会被隐藏。

    那么我们要取消这种情况的话,就需要设置这个单元格的格式了,首先我们鼠标选中这个单元格,然后鼠标右键选择设置单元格格式。

    在打开的设置单元格窗口中,我们点击选择对齐菜单,然后在对齐菜单下我们就可以看到文本控制选项下有个自动换行的选项了。

    勾选这个自动换行选择,确定后我们回到excel中就可以看到我们选择的那个单元格里的文字已经自动换行成功了。

    当然了如果碰到这种自动换行后造成这一个单元格的行高比较高的时候,我们就可以调整行高让excel页面美观一点,那么我们可以考虑设置固定的行高。首先鼠标移动到excel表格左上角这个位置,点击选中表格全部内容。

    然后鼠标移动到下方的第一行或者第二行的数字上,鼠标右键选择设置行高。

    在行高窗口中输入20吧,一般20的行高的excel比较美观,确定后我们的excel表格就变正常了,而单元格的内文字还是会自动换行的。霸气走火就是我,我就是百度经验作者:ixlxt7Xzsl7,百度经验和百家号首发原创内容分享。

所有视频需要登录后,才能观看

请先登录您的帐号,即可完整播放,如果您尚未注册帐号,请先点击注册。