excel常用函数
FREQUENCY函数分段计数
EXCEL做数据分析的时候,经常会遇到分段统计数量的问题。今天以学生成绩分析为例,来述如何使用FREQUENCY函数简单方便的统计各分数段的人数。示例数据如下图:关键操作函数实现选中E2:E6单元格区域,输入公式:“=FREQUENCY(B2:B16,{60,70,80,90}-0.1)”,组合键结束。如下图:
如何查找包含通配符的文本?
Q:我想查找并使用其它的字符替换工作表单元格文本中的*号,但实际上会替换掉单元格中所有的文本字符,如下图1所示,如何只替换掉*号呢?图1A:由于Excel会把*号当成任意数量的字符,因此在查找时不仅仅是单元格中的*号,还会是单元格中的所有字符。这样,替换掉的不仅仅是单元格中的*号,会替换掉单元格中的所有字符,就像图1中的那样。如果我们在查找时,在*号前加上一个“~”号,那么就会只是找到单元格中的*号,并且在替换时会会正确地替换掉*号,如下图2所示。图2同理,Excel会认为?号代表字符中?号所在位置是任意一个字符。因此,如果只是要查找单元格里文本中的?号,那么在查找时应该在?号前加上“~”号。例如下图3的示例工作表,想将“Gree?Red”中的“?”替换成“n”。
如何统计带分隔符的字符串中不重复的子字符串数?
Q:某些情况下,我们可能要统计带有分隔符的字符串中不重复的子字符串数。如下所示,我想知道单元格A1中不重复的数字有几个,应该怎么编写公式?A:下面的数组公式可以完成单元格A1的字符串不重复值的统计:=SUM(N(MATCH(TRIM(MID(SUBSTITUTE(A1,”,”,REPT(“”,999)),ROW(INDIRECT(“1:” &LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1))*999-998,999)),TRIM(MID(SUBSTITUTE(A1,”,”,REPT(“”,999)),ROW(INDIRECT(“1:” &LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1))*999-998,999)), )=ROW(INDIRECT(“1:”& LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1))))结果为6。注意,公式输入完成后要按下Ctrl+Shift+Enter组合键。公式解析这么长的公式,一看到可能被吓着了,让我们来看看这个复杂的公式是怎么得来的。上面的公式可以简化为:=SUM(N(MATCH(单元格中的子字符串组成的数组, 单元格中的子字符串组成的数组, )=连续数字组成的数组))其中,生成单元格中的子字符串组成的数组的公式:
excel表格名称的秘密
名称是Excel中的一项强大功能,无论是在公式中还是在VBA中运用名称,都能带来极大的便利。本文主要谈谈在Excel中不被注意到的一些名称的使用。Excel内置的名称Excel有一些内置的名称,它们会在特定的环境下自动使用。例如,设置打印区域后,Excel会自动命名打印区域为Print_Area,如下图1所示。图1如果设置打印标题,则Excel会创建名称Print_Titles。又如,在使用高级筛选获取不重复值列表时,Excel会自动创建名称Extract,如图2所示。
excel怎么添加、查找和编辑数据记录
在Excel中,我已经创建了一个输入数据的用户窗体,用于在工作记录工作表中添加新数据记录。最近,老板提出了新的需求,要通过该用户窗体能够编辑数据记录,增强其功能。这是我们在使用Excel编程时经常会遇到的问题。虽说直接在工作表中添加数据没有什么不好的,但就是有很多人喜欢使用自已设计的界面输入数据,包括我自已。在设计好输入数据界面后,更进一步增强界面的功能,可以查找数据,对找到的数据进行编辑并将修改更新到工作表中。如下所示。用户窗体界面设计存储数据的工作表如下所示。根据工作表数据结构,设计用户窗体如下所示。其中,用于导航的4个标签按钮放置在一个名为fraNavigate的框架控件中。编写代码
IFERROR函数,从结果中剔除不需要的值
在使用公式时,我们经常遇到将某个值从结果数组中剔除,然后将该数组传递给另一个函数的情形。例如,要获取单元格区域中除0以外的最小值,可以使用数组公式:=MIN(IF(A1:A10<>0,A1:A10))或者对于Excel 2010及以后的版本,使用AGGREGATE函数:=AGGREGATE(15,6,A1:A10/(A1:A10<>0),1)(注意,这里必须指定第1个参数的值为15(SMALL),因为如果指定其值为5(MIN)的话,AGGREGATE函数不接受除实际的工作表单元格区域外的任何值。然而,如果指定该参数的值为14-19,那么可以先操作任何单元格区域,也可以使用来源于AGGREGATE函数里的其他函数生成的数组、或者常量数组,这些都不是指定其值为1-13所能够处理的。)然而,有时包含0的数组不是一个简单的工作表单元格区域而是由函数通过计算生成的数组。在这种情形下,特别是公式相当长时,重复的子句将使公式更长,这使得公式看起来很“笨重”,并且还会使Excel进行一些不必要的计算,例如:=MIN(IF([a_very_long_formula]<>0,[a_very_long_formula],””)下面用一个例子来说明,如下图1所示:
SUM+COUNTIF统计不重复值的个数
下表是某电子产品商店五天内的销售情况,需要统计,在这五天内,一共销售出了几种商品。公式实现在E2单元格输入公式:“=SUM(1/COUNTIF(B2:B16,B2:B16))”,组合键结束。结果如下:公式解析“=SUM(1/COUNTIF(B2:B16,B2:B16))”其中:
如何在不同的页中打印每个学生的成绩?
Q:如下图1所示的工作表,我已经按照学生姓名排序,现在想要在不同的页中打印每个学生的成绩数据?图1A:我们介绍使用VBA来隐藏并逐条显示记录,从而实现批量打印。当然,后面的文章中也提到过可以使用筛选达到同样的效果。下面介绍的实现方法,不使用VBA,只使用Excel自身的功能。方法1:使用“分类汇总”将当前单元格置于数据区域中,选取“数据”选项卡中的“分类汇总”命令。在“分类汇总”对话框中,选取“每组数据分页”复选框,如图2所示。图2
excel如何识别带有图片的单元格?
Q:前不久有网友问我,在工作表的一列中,有些单元格放置有图片,有些单元格为空,如何识别带有图片的单元格并输入相应的文字?如所示,对工作表Sheet1的列B中含有图片的单元格,输入“有图片”,而没有图片的单元格则输入“无图片”,效果如列G所示。A:下面的代码可以完成我们的需求:Sub DecidePic()Dim cell As RangeDim lngCells As LongApplication.ScreenUpdating = False‘设置查找列的单元格数lngCells = 3For Each cell In Range(“B2:B”& lngCells)
小心!Excel的保护工作表/工作簿密码很容易破解
有时候,我们会使用Excel的保护工作表或者保护工作簿功能(图1)来对Excel实施保护,以免他人修改工作表或工作簿。图1然而,这种方法很不安全,很容易被。究其原因,主要是由Excel对保护工作表或工作簿的定位决定的。在保护工作表或者工作簿时,就是告诉Excel允许用户能够做什么,不能够做什么,只是对Excel授予了某些权限,并没有对Excel本身进行加密。Excel密码保护原理在保护工作表或者工作簿时,如果你设置了密码(图2),那么无论该密码的长度是多少,Excel都会将其转换成包含12个字符的特殊序列,并将这个经过转换的密码保存在Excel中。因此,虽然很难猜测出原始密码,但我们可以分析这12个字符序列。图2假如Excel将所设置的密码转换后与包含12个字符的序列匹配,那么就相当于获取了原来由密码所限制的权限,实际上就是了设置的密码。