excel表格数据统计Word文档下载推荐.docx
《excel表格数据统计Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《excel表格数据统计Word文档下载推荐.docx(6页珍藏版)》请在冰豆网上搜索。
=aa
但本式不能识别如:
[、×
、÷
等符号进行计算,使用第一种较好。
三、如何在excel中编写自定义函数,象在表格中调用sum()一样?
第一种情况:
单元格a1=2;
单元格b1=2;
单元格c1=2
单元格d1=(a1+b1)×
c1显示结果为8;
那么如何才能在单元格e1中显示(2+2)×
2的计算表达式,并且建立关联,当单元格d1变成=(a1+ba)^c1计算式后,显示结果为16;
那么e1也就自动显示为(2+2)^2的计算表达式,也就是说随着单元格d1的计算公式变化,单元格e1显示的计算表达式也随之变化。
第二种情况:
是当计算的单元格任意变化时,怎么办?
比如说计算式e1=a1+b1+c1+d1,也有d2=a2+b2+c2,还有F3=a1+b1+c1+d1+e1时。
如何将计算式变为计算表达式。
即计算式可以在任意一个单元格,计算公式所引用数据的单元格可以任意变化,需要显示计算表达式的单元格也是任意的。
针对第一种问题,分两步做:
1,自定义一个函数
publicFunctionk(xasRange)
k=Right(x.Formula,len(x.Formula)-1)
endFunction
自定义函数操作过程如下:
工具-宏-Visualbasic编辑器-右击-插入模块-再将上面的函数粘贴进去即可。
2,在e1中写入公式
=substitute(substitute(substitute(k(d1),"
a"
”(西文单引号),然后输入“001”。
3日期的输入
如果要输入“4月5日”,直接输入“4/5”,再敲回车就行了。
如果要输入当前日期,按一下“ctrl+;
”键。
在excel中填写表格时,怎样在一格中换行填字
输入一行后按alt+enter就可以换行
5自动切换输入法
在一张工作表中,往往是既有数据,又有文字,这样在输入时就需要来回在中英文之间反复切换输入法,非常麻烦。
如果你要输入的东西很有规律性,比如这一列全是单词,下一列全是汉语解释,你可以用以下方法实现自动切换。
方法是:
(1)选中要输入英文的列,单击“数据”菜单,选择“有效性...”命令,在弹出的“数据有效性”对话框中,选中“输入法模式”选项卡,在“模式”框中选择“关闭(英文模式)”命令,单击“确定”按钮(如图12)。
(2)选中要输入汉字的列,在“有效数据”对话框中,单击“ime模式”选项卡,在“模式”框中选择“打开”命令,单击“确定”按钮。
这样,当光标在前一列时,可以输入英文,在下一列时,直接可以输入中文,从而实现了中英文输入方式之间的自动切换。
6、把数据彻底隐藏起来
工作表部分单元格中的内容不想让浏览者查阅,只好将它隐藏起来了。
1.选中需要隐藏内容的单元格(区域),执行“格式→单元格”命令,打开“单元格格式”对话框,在“数字”标签的“分类”下面选中“自定义”选项,然后在右边“类型”下面的方框中输入“;
;
”(三个英文状态下的分号)。
2.再切换到“保护”标签下,选中其中的“隐藏”选项,按“确定”按钮退出。
3.执行“工具→保护→保护工作表”命令,打开“保护工作表”对话框,设置好密码后,“确定”返回。
经过这样的设置以后,上述单元格中的内容不再显示出来,就是使用excel的透明功能也不能让其现形。
提示:
在“保护”标签下,请不要清除“锁定”前面复选框中的“∨”号,这样可以防止别人删除你隐藏起来的数据。
7、在excel中自定义函数
excel函数虽然丰富,但并不能满足我们的所有需要。
我们可以自定义一个函数,来完成一些特定的运算。
下面,我们就来自定义一个计算梯形面积的函数:
1.执行“工具→宏→Visualbasic编辑器”菜单命令(或按“alt+F11”快捷键),打开Visualbasic编辑窗口。
2.在窗口中,执行“插入→模块”菜单命令,插入一个新的模块——模块1。
3.在右边的“代码窗口”中输入以下代码:
FunctionV(a,b,h)V=h*(a+b)/2endFunction
4.关闭窗口,自定义函数完成。
以后可以像使用内置函数一样使用自定义函数。
用上面方法自定义的函数通常只能在相应的工作簿中使用。
8、单元格内容的合并
解决办法是:
在c行后插入一个空列(如果d列没有内容,就直接在d列操作),在D1中输入“=b1&
c1”,d1列的内容就是b、c两列的和了。
选中d1单元格,用鼠标指向单元格右下角的小方块“■”,当光标变成"
+"
后,按住鼠标拖动光标向下拖到要合并的结尾行处,就完成了b列和c列的合并。
这时先不要忙着把b列和c列删除,先要把d列的结果复制一下,再用“选择性粘贴”命令,将数据粘贴到一个空列上。
这时再删掉b、c、d列的数据。
下面是一个“&
”实际应用的例子。
用autocad绘图时,有人喜欢在excel中存储坐标点,在绘制曲线时调用这些参数。
存放数据格式为“x,y”的形式,首先在excel中输入坐标值,将x坐标值放入a列,y坐标值放入到b列,然后利用“&
”将a列和b列合并成c列,在c1中输入:
=a1&
,"
&
b1,此时c1中的数据形式就符合要求了,再用鼠标向下拖动c1单元格,完成对a列和b列的所有内容的合并(如图3-4)。
合并不同单元格的内容,还有一种方法是利用concatenate函数,此函数的作用是将若干文字串合并到一个字串中,具体操作为“=concatenate(b1,c1)”。
比如,假设在某一河流生态调查工作表中,b2包含“物种”、b3包含“河鳟鱼”,b7包含总数45,那么:
输入“=concatenate("
本次河流生态调查结果:
,b2,"
,b3,"
为"
,b7,"
条/公里。
)”计算结果为:
河鳟鱼物种为45条/公里。
9、条件显示
我们知道,利用if函数,可以实现按照条件显示。
一个常用的例子,就是教师在统计学生成绩时,希望输入60以下的分数时,能显示为“不及格”;
输入60以上的分数时,显示为“及格"
。
这样的效果,利用iF函数可以很方便地实现。
假设成绩在a2单元格中,判断结果在a3单元格中。
那么在a3单元格中输入公式:
=if(a2 例如,如果输入:
=if(a2 如果输入=if(a2 再比如,公式:
=if(sum(a1:
a5>
0,sum(a1:
a5),0)此式就利用了嵌套函数,意思是,当a1至a5的和大于0时,返回这个值,如果小于0,那么就返回0。
还有一点要提醒你注意:
以上的符号均为半角,而且iF与括号之间也不能有空格。
创建条件格式
可以使用六种逻辑符号来设计一个条件格式:
>
(大于)、>
=(大于等于)、(不等于),如果你觉得这些符号不好记,就干脆使用“>
”或“>
=”号来表示。
11、在Excel中小数点无法输入,按小数点,显示的却是逗号,无论怎样设置选项都无济于事,该怎么办?
这是一个比较特殊的问题,我曾为此花了十几个小时的时间,但说白了很简单。
在Windows的控制面板中,点击“区域设置”图标,在弹出的“区域设置属性”对话面板上在“区域设置”里选择“中文(中国)”,在“区域设置属性”对话面板上在“数字”属性里把小数点改为“.”(未改前是“,”),按“确定”按钮结束。
这样再打开excel就一切都正常了。
12、如何快速地将数字作为文本输入?
在输入数字前加一个单引号“”’,可以强制地将数字作为文本输入。
13、如何对工作簿进行安全保护?
如果你不想别人打开或修改你的工作簿,那么想法加个密码吧。
打开工作薄,选择“文件”菜单中的“另存为”命令,选取“选项”,根据用户的需要分别输入“打开文件口令”或“修改文件d令”,按“确定”退出。
工作簿(表)被保护之后,还可对工作表中某些单元格区域的重要数据进行保护,起到双重保护的功能,此时你可以这样做:
首先,选定需保护的单元格区域,选取“格式”菜单中的“单元格”命令,选取“保护”,从对话框中选取“锁定”,单由“确定”按钮退出。
然后选取“工具”菜单中的“保护”命令,选取“保护工作表”,根据提示两次输入口令后退出。
注意:
不要忘记你设置有“口令”。
14、工作表保护的口令忘记了怎么办?
如果你想使用一个保护了的工作表,但口令又忘记了,有办法吗?
有。
选定工作表,选择“编辑”\“复制”、“粘贴”,将其拷贝到一个新的工作薄中(注
篇二:
excel中人数或个数统计函数汇总
excel中人数或个数统计函数汇总
一、countiF(range,criteria)
说明
microsoftexcel还提供了其他一些函数,它们可根据条件来分析数据。
若要计算基于一个文本字符串或某范围内的一个数值的总和,可使用sumiF工作表函数。
若要使公式返回两个基于条件的值之一,例如某指定销售金额的销售红利,可使用iF工作表函数。
若要计算空或非空单元格的个数,可使用counta和countblank函数。
示例1:
通用countiF公式
如果您将示例复制到空白工作表中,可能会更易于理解该示例。
操作方法a
数据
apples
oranges
peaches
公式
=countiF(a2:
a5,"
apples"
)
a5,a4)
a5,a3)+countiF(a2:
a5,a2)
=countiF(b2:
b5,"
55"
b4)b数据32547586说明(结果)计算第一列中苹果所在单元格的个数
(2)计算第一列中桃所在单元格的个数
(2)计算第一列中柑桔和苹果所在单元格的个数(3)计算第二列中值大于55的单元格个数
(2)计算第二列中值不等于75的单元格个数
(2)
=32"
)-countiF(b2:
85"
)计算第二列中值大于或等于32且小于或等于85的单元格个数
(3)
二、dcount(database,field,criteria)
返回数据库或列表的列中满足指定条件并且包含数字的单元格个数。
参数field为可选项,如果省略,函数dcount返回数据库中满足条件criteria的所有记录数。
语法
dcount(database,field,criteria)
database构成列表或数据库的单元格区域。
数据库是包含一组相关数据的列表,其中包含相关信息的行为记录,而包含数据的列为字段。
列表的第一行包含着每一列的标志项。
Field指定函数所使用的数据列。
列表中的数据列必须在第一行具有标志项。
Field可以是文本,即两端带引号的标志项,如“使用年数”或“产量”;
此外,Field也可以是代表列表中数据列位置的数字:
1表示第一列,2表示第二列,等等。
criteria为一组包含给定条件的单元格区域。
可以为参数criteria指定任意区域,只要它至少包含一个列标志和列标志下方用于设定条件的单元格。
dcounta
请参阅
返回数据库或列表的列中满足指定条件的非空单元格个数。
参数Field为可选项。
如果省略,则函数dcounta将返回数据库中满足条件的所有记录数。
dcounta(database,field,criteria)
database构成列表或数据库的单元
格区域。
实例请
三、sumpRoduct(数组1,数组2,数组3,……)
20xx/09/2817:
00
sumpRoduct函数是一个数组类型的函数,能够计算多个区域的数值相乘后之和,也可以加入数组区域条件进行过滤后计算。
语法:
sumpRoduct(数组1,数组2,数组3,……)
结果:
返回数组(区域)中对应项相乘后的和。
例如下图所示的工作表数据:
要求将列a和列b中同一行的数据相乘后的结果相加,即:
a1×
b1+a2×
b2+a3×
b3+…+a10×
b10
可使用如单元格c3中的公式:
=sumpRoduct(a1:
a10,b1:
b10)
如果将单元格区域a1:
a10命名为"
dataa"
,将单元格区域b1:
b10命名为"
datab"
,那么可以使用公式:
=sumpRoduct(dataa,datab)
说明:
在sumpRoduct函数的参数中,数组的大小必须相等,否则将返回#Value!
错误。
sumpRoduct函数将数组中不是数字的数组元素作为0对待。
举例:
基于两个条件汇总数据
例如,下表所示的数据:
现在要求华东区域产品b的销售量,则可使用下面的公式:
=sumpRoduct((c2:
c10)*(a2:
a10="
华东"
)*(b2:
b10="
b"
))
结果为100。
其中:
c2:
c10为包含销售量的单元格区域通过a2:
产生一个数值为1,0的数组,即如果列a中的数据
为华东则值为1,否则为0
同理,通过b2:
产生一个数值为1,0的数组(c2:
)的结果为三者对应元素相乘后
的一个数组(第一个元素为a2*b2*c2)
sumpRoduct函数将这些数组分别相乘后相加即得到结果
探讨:
有时,如果不合适地应用sumpRoduct函数,将会得不到正确的结果。
例如,下表所示数据:
现在要求华中区域与目标日时间差为负数的项目的销售额。
其中,将单元格区域"
a2:
a10"
命名为"
区域"
,将单元格区域"
b2:
b10"
销售额"
c2:
c10"
时差"
如果我们输入下面的公式:
=sumpRoduct((时差 将得不到正确的结果。
由于某种原因,sumpRoduct函数不能正确处理布尔值,因此公式无效。
下面的公式在sumpRoduct函数中将布尔值乘以1,将得到正确的结果:
=sumpRoduct(1*(时差 也可以像上例一样,使用下面的公式:
=sumpRoduct(销售额*(区域="
华中"
)*(时差 篇三:
统计excel表格列中有多少个不同数据
在excel表格中统计列中有多少个不同数据
比如上面的这个表格,b是很长的一列,中间的数据不规律,有些编号出现了一次,有些出现了多次,在此表格中每个编号代表一个事项,想要统计表格中一共有多少个事项是人力所不能及的。
这就需要用到excel的数组公式。
结果公式如下:
=sum(1/countiF(b2:
b33,b2:
b33))
但要注意这类数组公式的输入方法和普通的公式不同,普通公式输入后“enter”回车即可,数组公式输入后需要按组合键“ctrl+shift+enter”,输入后公式多了两个大括号,如下图所示:
这个公式的实现原理是这样,先用countif计算所有出现的数据的出现次数,存为数组,比如一列的内容是a、a、b、b、b、c,用了这个countif就会出现6个数的数组:
2、2、3、3、3、1。
即a出现了2次,b出现了3次。
这一组数分别被1除以后,得到的自然就是类似1/2,1/3这类数字,就上面这个例子而言得到的数组就是1/2,1/2,1/3,1/3,1/3,1,把他们再相加就得到了总和3,也就是a、a、b、b、b、c这列内容不同数据的个数。
注意单元格如果为空,countif的结果是0,被1除就不合法,这个公式也就无效了。
原理搞通,此方法根据实际应用不同还可以举一反三,更加强大。