分类目录归档:OFFICE相关

EXCEL条件判断函数IF IFERROR IFNA学习

EXCEL中经常用到条件判断的功能,例如不同的情况显示不同的值、计算出错如何显示、查询为空时如何显示等场景。EXCEL条件判断的函数有三个IF、IFERROR、IFNA,功能十分强大,下面系统地学习这些功能。

IF,if是如果的意思,能够根据不同的条件显示不同的数值,用法是IF(条件,条件为真是显示的值,条件为假时显示的值),举例如果A1的数值大于等于60就显示及格,否则显示不及格。
=IF(A1>=60,”及格”,”不及格”)

IFERROR,EXCEL在数值计算的时候,经常会遇到计算错误的情况,比如计算增速时去年的基数为0,这是结果会显示#DIV/0!,严重影响美观。IFERROR的作用是将这些错误替换成要显示的值,IFERROR用法是IFERROR(显示的值,如果结果为错时显示的值)。举例计算增速时,如果结果正常则显示增速,如果出错则显示0。
=IFERROR(B1/A1-1,0)

IFNA,使用VLOOKUP查询的时候,经常出现查不着的情况,这时系统就会显示#N/A,这样即影响美观,又无法完成汇总计算。类似IFERROR功能的函数是IFNA,能将#N/A显示成需要的值。用法与IFERROR一样,IFNA(显示的值,如果结果为空时显示的值)。举例当VLOOKUP查询没有结果时,显示空。
=IFNA(vlookup(,,,,),””)

一次较大规模数据处理的经历 EXCEL版

前一段时间写了一篇文章《一次较大规模数据处理的经历 PYTHON版》,尝试使用PYTHON和PANDAS解决较大规模数据的计算问题,近百万条数据如果使用EXCEL处理,速度比较慢,而且对于复杂的计算,还需要借助VBA程序,因此上次使用了PYTHON。但是EXCEL的优点不容忽视,比如简单直观,能够快速筛选、汇总等等,最终生成报表还得使用EXCEL,此次打算用EXCEL重新处理一遍数据,并寻找合适的方法避免EXCEL的缺点,使得EXCEL能够快速地处理较大规模的数据。

接之前的问题,数据规模在百万条,CSV格式,需要对数据进行计算,根据前三列数据的不同,公式分成四组,均类似于电费的阶梯电价计算方案,只不过具体标准不一样,【问题一】需要计算最终值;【问题二】后面还提出了一种探索的算法,即计算公式增加“次数”的条件,每一次的计算公式又不一样。

【问题一】针对该问题,最初使用if嵌套公式,发现嵌套起来复杂,而且复制公式时,机器非常卡,即使关闭了自动计算亦是如此。于是决定使用VBA编写程序解决,分为两部分:一是分段计算函数;二是调用的主函数。解决问题的思路是通过VBA计算数据,此时表中的数据结果是静态的,没有任何公式,因此避免了卡顿的问题。 继续阅读

Python操作Excel类库Openpyxl之初体验

以前尝试过通过win32com方式读写excel,Win32com调用Excel是最好的一个方式,因为直接调用的是windows平台的软件,可靠性要高一些,另外excel出错的可能性也小一些。由于win32com包怎么也找不到,于是这次尝试使用openpyxl类库读写excel,选择openpyxl的原因在于它能够读取和写入Excel文件,相对于xlrd、xlwt方便一些。

1 安装openpyxl

安装方法非常简单,直接使用pip即可:
pip install openpyxl
它能自动下载和安装openpyxl所依赖的包,然后再安装openpyxl。
但是本人使用的机器是离线的,因此只能采用离线安装的方式,经过尝试,需要下载以下安装包:
et_xmlfile
jdcal
openpyxl
上面三个安装依次下装并安装,注意版本,比如Openpyxl最新版本号是3.0.5,那么et_xmlfile和jdcal下载时应注意版本号,否则会出错。不过出错也没关系,因为出错信息中会提示所依赖包的版本号,重新下载一遍也没关系。

2 操作Excel
2.1 导入openpyxl
继续阅读

EXCEL数据透视表如何以表格形式显示?!

EXCEL中的数据透视表非常方便,可以快速生成报表,完成各种统计工作。最近遇到一个问题,相信大家都遇到过,就是如何以表格的形式显示数据透视表。
下面是平时常见的数据透视表样式,左边是列标题,是大纲形式的。

但是有时候,我们需要的是表格形式的数据,方便进一步筛选或者处理。这样显示的方法是,点击报表布局,选择“以表格形式显示”,然后点击字段设置,布局和打印,勾住重复项目标签。

EXCEL中录入身份证号码的方法

EXCEL中经常录入像身份证这样一大串数字的情况,通常情况下,EXCEL会把它当成数字,四舍五入,然后用科学计数法来表示,就变成类似于1.12E+17这样的形式了,点来一看,发现这个数字后面的几位都变成零了,显然出了问题。
先来说解决方法,再说原因。第一种适合录入身份证号码的时候使用,就是在身份证号码前输入一个’符号(分号右边的那个键,注意是英文状态下),然后再输入号码,这样就没有问题了,左上角的绿色三角表示是文本型数据,没有任何影响;第二种情况是大批量复制粘贴时出现的问题,比如身份证号码是在邮件、记事本中存储的,要复制到EXCEL中,这时一整列身份证号码也会变成科学计数法表示的数字,正确的方法是,先将EXCEL中要存放身份证号码的那一列选中,鼠标右键、设置单元格格式、把分类从常规改成文本,然后复制过来就一切正常了。
为什么会出现这样的问题?!因为EXCEL存储的数据,对应着不同的格式,默认是常规格式,常规格式下输入的数据,EXCEL会智能处理为相应的类型:文本、数值、日期、逻辑、公式等,方便运算。数值型数据如果超过11位,系统就会用科学计数法表示,如果超过15位,15位后面的数字将被舍去,变成0。18位的身份证号会被认为是一个很大很大的数字,所以没有指定是文本的情况下,它会被四舍五入,用科学计数法表示。

Excel对字符串的截取、拼接和对比

Excel数据处理,有一部分是对字符串的操作,也就是对字符串进行截取、拼接和对比,因此,本文将对Excel字符串操作的一系列函数进行讲解,旨在提高工作的效率。

1 字符串的截取
字符串的截取就是从一个给定字符串中截取一部分内容,常见的比如截取身份证号中的年月日信息,可采取的函数有MID、LEFTRIGHT

MID是middle的缩写,意思取一个字符串中间的一部分,使用方法是=MID(字符串,起始位置,截取长度),起始位置从1开始。

LEFT是从左边截取给定的一个定符串,RIGHT与LEFT相反,是从右边截取给定的一个字符串,需要指定截取的长度,使用方法是=LEFT(字符串,截取长度)=RIGHT(字符串,截取长度)
excel012 字符串的拼接
拼接就是将两个或多个单元格或字符串连接一起,拼接的方法是使用concatenate函数或直接使用&符号进行连接,如下图所示,其中”-“是一个单独的字符串“减号”:
excel063 字符串的比较
经常遇到两个单元格的内容进行比较的情况,如果使用眼睛判断比较麻烦,也可能犯错误,正确的方法是使用EXACT函数或等号来判断。

excel07

EXACT进行对比时,区分大小写,公式2中使用等号对比时,不区分大小写

4 其它一些字符串函数

4.1 求字符串长度
使用len函数求一个字符串的长度,在中文语言状态下,汉字、数字、符号都占一位,注意前后空格也占位置,使用方法是=LEN(A1)

4.2 去前后面的空格
在对手工填写的内容进行对比时,经常发现明明看着一样,但是对比起来不一样或者用VLOOKUP查不着,那可能就是前面后面有空格,使用TRIM函数可以去掉前后的空格,使用方法是=TRIM(A1)

Excel常用的几个数据处理的方法

工作中经常遇到处理数据的问题,这些数据量通常不大,用Excel处理起来比较方便。如果再掌握一些技巧的话,工作效率就能事半功倍,懂下面几招,一般的工作就游刃有余了:

1. 数据准备

这一步就是数据的预处理,我经常遇到的数据是CSV格式 ,即分隔符为逗号、管道符等字符的数据,例如”ABC|234|23“。因此在计算之前,就先将数据整理。第一步将数据粘贴到Excel中,点击”数据“-》”分列“,选中数据的分隔符”|“,此处要注意的是最好对每一列单独设置数据类型,比如上面的数据,第一列为文本、第二列为常规、第三列为常规,区分数据类型,是因为有”0“开头的数据、长数据如身份证号,如果不设置成文本,那么”0“开头的数据”0“会被删除;身份证号会被当成数字,将后面几位舍成”0“。第二步就是利用筛选功能,检查是否有错列的现象,如果有的话得先删除。
继续阅读