现在位置:首页 > Excel相关 > Excel基础
  • 文本与数值的那些事儿(一)

    Excel基础2017-4-240评论379
    1、文本型数值转换成数值型数值,如下图。 方法1:在原单元格转换。首先选择A2:A11任意一个单元格,然后按CTRL+A全选单元格,点下图中的感叹号,然后选择“转换为数字”即可。 方法2:辅助列转换。在B2单元格输入公式=--A2或者=A2*1,公式下拉即可。 注:一般情况下从ERP或者其他系统导出来的很多数值都会是文本型数值,如果直接用SUM进行求和运算得到的结果将会是0,所以必选先转换成数值型数值,然后才能进行求和运算。 2、数值型数值转换成文本型数值,如下图。 方法1:在原单元格转换。首先我们先把A2:A11单元格格式设置为文本格式,然后随便打开一个记事本,将A2:A11复制粘贴到记事本中,然后再将记事本中的数据复制粘贴回A2:A11单元格区域中。 方法2:辅助列转换。在B2单元格输入=A2&"",公式下拉即可。 注:当我们需要将一些数据通过Excel导入ERP或者其他系统的时候,数值型数值往往会不被认可,某些系统的导入只识别文本,所以这个时候就需要我们将数值型数值先转换成文本型数值。可能有些人会说那我直接将单元格格式设置为文本不就行了吗,为什么还要这么复杂的操作呢?实际情况并不是这么简单,当一个单元格的内容格式已经确定,我们直接变更单元格格式,并不能实际改变数据类型,不相信的朋友可以试一下。 小结:有位关注公众号的朋友说希望出一期文本与数值间相互转化的文章,所以周末抽空写了一下文本与数值的那些事儿,先把第一部分放出来,下周一再放第二部分关于文本与数值的替换、提取,敬请期待。
  • 为什么这么帅这么标准的条件计数公式结果会不准确?

    Excel基础2017-4-120评论425
           看到上面的图片你发现什么问题了吗?要求根据入职时间计算人数,这明明是很简单的条件计数,函数公式也写得中规中矩,=COUNTIF($D$2:$D$21,F2),第1参数计数区域,第2参数计数条件,一切看起来很帅很标准嘛,可是我们再看一下结果,what?<3个月的人数居然有20人?那不是全部人数都是小于3个月?另外>3年的人数居然是0,可是D列的数据可不是这样的啊,这到底是怎么了?        问题就出在数据D列里面的“<3个月”和“>3年”里面以及条件里面的比较运算符“>”和“<”,公式=COUNTIF($D$2:$D$21,F2)相当于=COUNTIF($D$2:$D$21,“<3个月”),计算的时候Excel会认为我们是需要统计D列里面小于“3个月”的数据个数,就会将D2:D21里面的每一个单元格与“3个月”做比较,例如“="1年~3年"<"3个月"”,我们在单元格输入就会发现它返回的是TRUE,也就是将“1年~3年”与“3个月”做比较的时候,它认为“3个月”比较大一点,至于这是什么见鬼的逻辑我们就不深究了,反正文本之间比较大小都是很复杂的,所以最终计算就会得出这些乱七八糟稀奇古怪的结果,那么我们要怎么做才能得到正确的结果呢? 我们现在再来看上面这张图与最开始的时候有什么区别?我们会发现两个表里面原来第一个表的“>”和“<”都分别变成了“>”和“<”,这是插入里面的中文的符号,并不是比较运算符,虽然看起来很相似,这个时候我们在用刚才的公式进行计数就可以得到正确的结果了。 结束语  一路走来,太累了,停下来歇歇吧,多休息一会,是为了走更远的路。抬头看看天,看乌云的缝隙里钻出的斑驳阳光,重新拾起前行的勇气和信心;回首望望来路,想想丢下了什么,还有什么可以丢下的,只要心灵轻松些,任何放弃都是一种努力。坚定地走吧,毕竟梦想在远方,未来在远方,终点也在远方。
  • 为什么你的公式无法双击填充?

    Excel基础2017-4-110评论1583
    我们学公式的时候都知道,当公式完成后鼠标放到单元格右下角会有个黑色的十字,双击左键可以快速填充公式。但是当我们遇到下图这种情况的时候,直接双击公式却无法填充,这是为什么呢? PS:千万别想着手动下拉填充公式,这个表可是有25000行数据,手动下拉那可不是三两分钟能搞定的事 Excel双击填充公式是依据公式前面一列的数据来进行填充的,我们之所以无法双击填充公式,是因为公式前面的一列是空的没有任何数据,所以Excel就傻了不知道该怎么办了,那么这个时候我们该怎么做才能快速填充公式呢?下面我们来介绍三种常用的解决办法。 1、删掉空白列 最简单的办法当然是直接删掉公式前面的两列空白列,然后双击填充公式啦。当然可能有些实际情况不允许我们删列,那么我们接着看下一种办法。 2、剪切插入列 我们可以直接选中公式那一列,剪切,然后选中B列,插入剪切,双击就可以填充公式了,然后再把公式列剪切回原来的位置,如下图。当然这个办法比较麻烦,操作过程中还变动了字段的顺序,有没有更好一点的办法呢?我们接着看下一种办法。 3、快速选定单元格区域 我们可以直接在名称框里面输入“H2:H25000”,然后按回车键就可以快速的选中这一个区域,接着按CTRL+D就可以快速填充这个区域的公式。这是一个非常实用的操作,我们很多情况下需要快速选定一个范围很大的区域的时候可以直接这么操作,快速而精准。 PS:上面几种办法或许还不是最简单的,但是一定是最常用的。 结束语 这些天的公众号关注人数一直在缓缓增长,可是阅读量却一直停滞不前,少得可怜,看来我还有很多地方做得不够好,没关系,继续努力,加油吧!村长!你一定可以越做越好的!
  • Excel单元格地址四种引用样式(入门必看,高手请无视之)

    Excel基础2017-4-100评论338
    单元格地址的引用形式是所有想要学习Excel函数与公式的朋友都必须要知道和理解的,那么单元格的引用形式有哪几种呢? 1、相对引用 例如单元格A1,这就是相对引用,如果输入公式=A1,公式右拉会依次变成B1,C1,D1....,公式下拉会依次变成A2,A3,A4....,这是“墙头草引用样式”。 2、绝对引用列 绝对引用列是混合引用的一种,例如单元格$A1,在列标的前面加了个美元符号$,有了钱之后公式再怎么右拉依然会是$A1,因为这一列有钱它不舍得离开,但是行号前面没有钱$,所以它要造反啊,公式下拉的时候它就直接转行了,依次变成A2,A3,A4....,这是“列有钱行穷逼引用样式”。 3、绝对引用行 绝对引用行也是混合引用的一种,例如单元格A$1,在行号的前面加了个美元符号$,有了钱之后公式再怎么下拉依然会是A$1,因为这一行有钱打死它也不会离开的,这时候又成了列标前面没有钱$,所以它也要造反,公式右拉的时候它就换队列了,依次变成了B1,C1,D1....,这是“列穷逼行有钱引用样式”。 4、绝对引用行和列 绝对引用行和列也叫绝对引用,例如单元格$A$1,在行号和列标的前面都加了美元符号$,所以这个地方巨有钱,公式你随便拉单元格就是赖在$A$1,毕竟谁都想待在巨有钱的地方,这是“巨有钱引用样式”。 重要:F4是在四种引用间相互转换的快捷键,在编辑栏输入公式时按下F4功能键可进行快速切换,不需要手动输入美元符号。 结束语 万丈高楼平地起,不要觉得这个单元格的引用样式太过于简单就不重视它,只有深刻理解单元格的四种引用样式,我们才能灵活多变的写出我们想要的函数公式,这是所有学函数的朋友都必须要第一时间掌握的,它最简单,同时也最重要,如果理解不了我说的是什么,可以多看几遍动态图里面单元格的变化情况,结合实际情况自己动手练习一下。
  • 这样的数据有效性下拉框你见过吗?只要你有想法你也可以做到

    你见过这样动态的下拉框吗?可以根据你输入的关键字模糊匹配所有包含关键字的内容,想知道这个效果是怎么做出来的吗?那就请看村长来给你讲解吧!(如果可以,边看边动手自己做一遍吧,你会发现其实很简单) 第一步(准备数据表) 首先你得准备一个工作簿,工作簿里面准备两个表格,其中一个就是我们动画演示的那一个需要做下拉框的表格,另外一个表格就是如下图的一个“数据源”的表格,A列里面是所有学校的名称,然后我们还要做一列辅助列,也就是B列,我们给它取一个字段名叫“读取” 第二步(写辅助列公式) B2单元格输入公式=IFERROR(INDEX(A:A,SMALL(IF(ISERROR(FIND(有效性读取!$B$2,$A$2:$A$2880)),9^9,ROW($2:$2880)),ROW(A1))),""),注意这是数组公式要CTRL+SHIFT+回车键三键结束,然后公式下拉,这里下拉多少个单元格取决于你的需求,不能太多也不能太少,例如我模糊搜索的时候最多可能会同时有几十个满足条件的学校,那我们只要下拉100个左右的单元格就绰绰有余了,我这里公式下拉到了B2:B100,当然如果你不怕表格卡你也可以下拉几千个单元格。 第三步(定义辅助列数据的名称) 选择“公式”选项卡的“名称管理器”,然后点击“新建”,建立一个名称为“读取”,引用位置为“=数据源!$B$2:$B$100”的自定义名称,然后点保存。 第四步(设置数据有效性) 选中B2单元格,然后点击“数据”选项卡的“数据有效性”,点“设置—(允许)序列”,来源输入“=读取”,然后点击确定。到了这里操作已经全部完成,非常简单四步走,唯一麻烦一点的是辅助列的那个公式不大好理解,这里就不详细讲解那个公式了,想要学习更多函数知识就多多关注“我的Excel”微信公众号的最新动态吧。 你还在看吗? 看到这里,你是否觉得上面的操作步骤很简单?或者觉得似懂非懂?或者完全看不明白?可是,你有想过自己动手模仿着做一遍吗?不要告诉我你连模仿都不会,这是上天赋予我们人类最宝贵的财富,学习固然需要创新,但是模仿更加可贵,正如婴儿蹒跚学步的时候,你不可能要求他要学会创新不要学别人怎么走路,要走出自己的风格,那纯属扯淡,当我们学习一样新事物新知识的时候,就应该从模仿开始,然后才是融汇贯通,最后才是创新思维。所以,如果你想要学习,仅仅只是看那是远远不够的,你要学会动手,善于模仿,敢于创新。所以,赶紧动手吧,关注我们,转发我们,让更多人一起来学习。PS:想要本文表格模板的朋友可以留言留下您的邮箱!
  • 别让单元格显示的内容欺骗了你的眼睛

    Excel基础2017-1-260评论401
    都说耳听为虚眼见为实,然而对于Excel表格里面的单元格,眼见却不一定为实,请看下面几组图片: 眼睛看到A1:H1单元格的内容分别是1月、2月、3月、4月、5月、7月、8月,可是真实情况真是这样吗?我们接着来看下面这张图片: 我们可以看到原来A1单元格显示的是“1月”,然而我们选中A1单元格之后,在编辑栏里面看到的内容却是“1”,那么这个单元格它的真实内容到底是什么呢?其实在Excel中,编辑栏显示的内容才是单元格真正的内容,这里之所以单元格显示的和编辑栏显示的不一致,是因为我们用了自定义格式"0月",这里的0是占位符,表示正整数的意思; 我们再来接着看下面的图片,来看一下单元格内容有哪些类型,怎么区分? 从上面的图片我们看到A1单元格显示的是“正数”,B1单元格显示的是”负数“,C1单元格显示的是“零",D1单元格显示的是”文本“,当然,这些都仅仅只是我们从单元格表面看到的内容而已,那么我们再来看看这四个单元格实际的内容是什么? 为什么会这样子呢?A1编辑栏里面的内容明明是“1”,为什么显示的是“正数”呢?B1编辑栏里面的内容明明是-100,为什么显示的却是“负数”,而单元格C1编辑栏里面显示的内容是“=0/100“,结果明明是等于”0“,为什么单元格显示的却是大写的”零“呢?最后一个单元格编辑栏明明显示的是小写字母”a“,为什么单元格显示的却是”文本“呢? 其实答案很简单,我们再来看一下这几个单元格的单元格格式,然后再告诉大家这是怎么回事: 我们可以看到上面的单元格用的是自定义格式,而整个格式用了三个“;”分成了四部分,第一部分是“正数”,第二部分是“负数”,第三部分是“零”,第四部分是“文本”,这里有一点知识要跟大家普及一下,Excel单元格的常用格式有以下两类,数值和文本,其中数值又分为正数、负数和零,所以单元格格式正犹如上图划分的那样正数、负数、零、文本分别在分号的固定位置,格式为“正数;负数;零;文本”,其中“;”必须是英文输入状态下得“;”,所以我们只要在第一个“;”前面输入我们想要的内容,只要单元格输入正数,那么就会显示我们想要的内容,同理只要我们在最后一个“;”的后面输入我们想要的内容,只要单元格输入的是文本,那么单元格就会显示我们想要的内容。 利用单元格的这个特性,我们还可以将单元格的内容隐藏起来不被其他人看见,只要在单元格格式那里选择自定义格式,然后格式内容输入“;;;”即可 表示我们希望单元格的正数、负数、零和文本都显示为空,这样别人就看不到了,当然如果别人选定单元格看编辑栏显示的内容那就另当别论; 总之,如果我们要确定单元格的真正内容,一定不要仅仅看单元格显示的内容,一定要选中单元格后看编辑栏显示的内容是什么,因为只有编辑栏显示的内容才是单元格真正的内容,不管是自定义格式也好,亦或是文本后面有空格,在编辑栏里面都将无所遁形。
  • Excel工作簿是如何减肥的?

    Excel基础2017-1-260评论427
    我们在日常工作的时候,可能会发现相同的数据但是工作簿的大小却不一样,而且有的工作簿看起来内容不多,但是文件却非常的大,有些甚至几十MB,这是什么原因呢?这或许就是因为你的工作簿里面存在了许许多多的不可见的图形对象,导致了工作簿虚胖,就像我们下面演示的那样,将不可见的那些图形对象清理之后,工作簿立刻身材变得苗条了,具体请看下面动画(因涉及工作,工作簿数据已清除并改名): 动画详解: 1、有一个工作簿,大小是285KB,复制粘贴,生成一个副本,大小和原来一样都是285KB; 2、打开副本工作簿,会发现工作簿只有一个工作表,没有实际内容,但是表格的运行会有点卡; 3、选择开始选项卡——查找和选择——定位条件,然后定位对象,点确定 4、这时候我们观察工作表会发现显示出许许多多原本看不到的图形对象,这些就是造成我们工作表运行缓慢和虚胖的主要原因之一,主要是由于我们从其他地方复制粘贴数据到本工作表的时候没有选择性粘贴数值,而是直接复制粘贴,这就造成了复制的时候会把一些格式或其他东西带到本工作表; 5、定位出来这些图形对象之后,我们不要点工作表其他地方,还是在开始选项卡,选择清除——全部清除,这样就可以把我们定位出来的图形对象全部清除掉; 6、清除掉这些多余的不可见的图形对象之后,我们再保存一下当前工作簿,最后对比一下两个工作簿的大小,原来的285KB变成了24KB,将近缩小了12倍,反之我们可以这么理解,因为我们的一些不规范操作,导致了这个工作簿虚胖了12倍的大小,试问怎么能运行不缓慢呢? 7、以下两个工作簿也是一样,复件是原来的工作簿,大小是10M,清理了不可见的图形对象之后,大小变为285KB,这就意味着虚胖了接近40倍,工作簿因为不规范的操作造成了大量的不可见图形对象,如果我们还用上面那种办法来清理图形对象,电脑稍微差一点都可能会卡死,这时候我们就需要用另外一种方法来处理,那就是用宏代码直接执行清除对象的操作 8、按ALT+F11可快速进入到代码编辑器,编辑代码语句如下: Sub 删除所有对象() ActiveSheet.DrawingObjects.Delete End Sub 如图:
  • Excel常用技巧

    Excel基础2017-1-190评论565
    一、提取不重复值 上面是提取不重复值最常用的三种方法: 1、利用数据透视表透视需要提取不重复值的那一列即可; 2、利用高级筛选功能筛选不重复值到指定位置; 3、利用公式获取不重复值,公式为=IFERROR(INDEX(A:A,SMALL(IF(MATCH(A$2:A$30,A$2:A$30,)=ROW($2:$30)-1,ROW($2:$30),9^9),ROW(A1))),""),数组公式,需要三键结束; 二、分列的技巧 上面分列的技巧用了技巧法和函数法: 1、技巧分列在“数据”选项卡里面的分列功能,分隔符号选择其他,输入“-”; 2、函数分列,公式为=TRIM(MID(SUBSTITUTE($A2,"-",REPT(" ",99)),99*COLUMN(A1)-98,99)),公式右拉下拉即可 三、定位清除不可见图形对象 Excel中可不见的图形对象是导致工作簿运行缓慢,崩溃的主要原因之一,所以,当我们工作簿运行缓慢时就需要检查一下是否存在不可见图形对象并及时清除。 In me the tiger sniffs the rose. 心有猛虎,细嗅蔷薇。
  • Excel单元格那些不得不说的事

    Excel基础2017-1-190评论355
    1、什么是单元格? 答:单元格是表格中行与列的交叉部分,Excel表格可以看成行与列的集合,也可以看成是单元格的集合; 2、Excel有多少个单元格? 答:Excel2003有65536行,256列,所以总的单元格数应该是65536*256个; Excel2007级以上版本有1048576行,16384列,所以总的单元格数应该是1048576*16384个; 3、什么是单元格地址? 答:单元格是表格中行与列的交叉部分,而单元格地址表示的是单元格的具体位置,由列标与行号构成,例如C列的第3行交叉的单元格的地址就是C3; 4、单元格地址有多少种表示方法?多少种引用样式? 答:单元格有两种表示方法,分别是“A1”引用样式和“R1C1”引用样式;有四种引用样式,相对单元格引用(A1)、绝对单元格引用($A$1)、列绝对单元格引用($A1),行绝对单元格引用(A$1),按F4可快速轮流切换四种引用样式; 5、什么是单元格区域? 答:单元格区域是指多个连续单元格组成的区域,例如A1/A2/A3/B1/B2/B3单元格组成的区域就是A1:B3,选择区域左上角单元格地址和右下角单元格地址,中间用“:”来表示某单元格区域; 6、什么是单元格格式? 答:选中单元格或单元格区域之后,点击鼠标右键可以在弹出的下拉菜单处选择“设置单元格格式”,格式例如常规、数值、文本、自定义格式等,也可以设置边框有无,是否自动换行,文字方向等等; 7、如何快速选定指定单元格区域?例如选定单元格区域A1:H9999 答:在名称框输入A1:H9999,然后按回车键,即可快速选定指定单元格区域。 8、如何快速选定连续单元格区域? 答:CTRL+A可以快速选取所有连续单元格,CTRL+方向键(↑、↓、←、→)可快速按方向选取连续单元格。 9、如何快速定义单元格区域名称? 答:选定单元格区域之后,在名称框输入指定的名称可快速定义该单元格区域。 10、如何快速选择可见单元格? 答:选中单元格区域后,按F5弹出窗口,选择定位条件-勾选可见单元格即可定位可见单元格;也可以按CTRL+;快速选择可见单元格。
  • 这些数据有效性的用法你有用过吗?

    Excel基础2017-1-190评论464
    在Excel中,你知道什么是数据有效性吗?数据有效性是指为指定单元格或单元格区域设定一些规则,告诉Excel应该禁止哪些数据录入,只允许录入哪些数据,这是一个可以规范我们数据采集和防止错误的功能,非常实用。下面的几种数据有效性的用法你在工作中是否有使用过或者见到过? 1、创建一个只能输入“小学、初中、高中、大学”的下拉框 选择A2:A10单元格区域,点数据选项卡,然后点数据有效性,然后再选择序列,输入“小学,初中,高中,大学”,其中逗号输入要在英文输入法状态下。具体操作见动画 2、创建二级联动下拉选框 (1)选择G1:I1,点击“公式”选项卡,名称管理器,定义名称“省份" (2)选择G1:I7,点击“公式”选项卡,根据所选内容创建,选择“首行” (3)选择A2:A10单元格区域,点数据选项卡,然后点数据有效性,然后再选择序列,输入“=省份” (4)选择B2:B10单元格区域,点数据选项卡,然后点数据有效性,然后再选择序列,输入“=INDIRECT(A2)” 具体操作见动画 3、限制录入重复数据 选择A2:A10单元格区域,点数据选项卡,然后点数据有效性,然后再选择自定义,输入公式=COUNTIF($A$2:$A$10,A3)=1,具体操作见动画。 4、只允许录入开头是“13”、“15”或“18”的手机号码 选择A2:A10单元格区域,点数据选项卡,然后点数据有效性,然后再选择自定义,输入公式=COUNTIF=AND(OR(--LEFT(A2,2)=13,--LEFT(A2,2)=15,--LEFT(A2,2)=18),LEN(A2)=11,ISNUMBER(A2)),具体操作见动画。