关注微信公众号：CodingTechWork，一起学习进步。
引言
  在线上运维的过程中，遇到一个头疼的事情，有一些合作公司的数据直接从平台上down下来是一个excel，然后发到研发手里去数据库中核对是否存在，怎么办呢？一般死脑筋方法就是使用sql语句select * from tb_name where colume_name="xxx";去核对是否存在该记录，完了，完全陷入到非sql不可的坑了。
  在思考如何更好的去比对两堆类型相同的数据时，除了程序员用后端的方法（不管是sql还是写脚本、程序工具类），其实还可以跳出这个思维圈，本文就总结一下如何通过我们日常用到的excel来比对两个数据表，当然我们也要解决表数据转为excel的问题。
sql转excel
方式
  我们先要解决一个mysql数据库中的数据如何转出到excel中的问题。
  进入到sql命令行选择数据库后，执行：select * from tbl_name into outfile '/home/outfile/test.xls';
问题
  我们可能会遇到一个ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement的报错，这个时候可以使用show variables like '%secure%';查看：
mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.01 sec)

  所以，我们可以在不修改数据库的权限情况下，选择这个默认路径进行输出：select * from tbl_name into outfile '/var/lib/mysql-files/test.xls';
  mysql数据转为excel这个问题解决后，我们就可以安心去解决excel中如何比对两个不同表格中的数据。
表达式
  我们先来了解一下今天登场的主角：IF(COUNTIF())表达式。
分解
=IF(COUNTIF(Sheet1!A:A,A1),"T","F")


IF()：IF()是条件判断函数；
COUNTIF()：是统计函数，其中，Sheet1代表的是另一个表格的数据，A:A表示统计区域，A1表示A1单元格的内容。
"T"：可自定义引号内的文字，是if如果成立，则会在单元格中输出该自定义内容。
"F"：可自定义引号内的文字，是if如果不成立，则会在单元格中输出该自定义内容。

操作详解
场景说明
  假设Sheet1为参考数据，Sheet2为需要比对的数据。比对Sheet2中是否在Sheet2中有相同的数据。




操作步骤

填写表达式：=IF(COUNTIF(Sheet1!A:A,A1),"T","F")并回车。

基准表格数据是在Sheet1中，比对的范围是A:A，单元格是在A1。T表示Sheet2中的这个数据在Sheet1中存在，F表示不存在。


下拉比对



结果显示：



注意：

比对结果的描述信息可以自定义


总结
  至此，我们就学会如何通过表达式来比对完两个数据表中的数据，是不是很赞？遇到问题的时候，我们不能死教条，一个一个的通过select * from ... ...去核对，这种效率低且单调。我们要学会跳出思维圈，利用一些辅助工具去帮助自己更好的工作，更好的去解决问题。

具体方法如下：


	
首先建立Sheet1和Sheet2两个表格，Sheet1和Sheet2两个中各有姓名列，现在我们就来查找Sheet1中的姓名是否与SHeet2中的重复
	
2.在Sheet1表格中输入公式"=iferror(vlookup(A2,Sheet2!A:A,1,0),"F”)

 

3.这个时候在Sheet表格中重复的就会显示为姓名，不重复的显示”F“

 

筛选同一表格下的多个sheet里的内容并保存在对应的sheet中
1 目标需求
2 处理步骤
3 步骤详解
3. 全部代码

1 目标需求
报表中含有多个sheet，而且几乎很多sheet表中都有相同要筛选的同种数据，因此就有需要将同一文件下多sheet表中内容进行筛选然后重新保存在文件中，数据样例如下，目标是提取每个sheet下Sample Type字段下的Unknown Sample对应的行数据，然后保存在该sheet下



目标结果：


2 处理步骤
① 读取Excel表格内容
② 提取Excel表格中所有的sheet名称，及其中的内容
③ 数据清洗
④ 数据分别保存在同一表格文件下对应的sheet中
3 步骤详解
① ② 读取Excel表格中的内容和提取Excel表格中所有的sheet名称，及其中的内容，这里选择pandas进行处理
sheet_name = None，要想获得所有的sheet_name，这里可以让其等于None，这样就可以得到对应的sheet名称和对应的内容
import pandas as pd
import os
os.chdir(r'C:\Users\86177\Desktop')

data  = pd.read_excel('AA_test.xlsx',header=None,sheet_name = None)
data

→ 输出的结果为：（根据报表中的内容来看，前四行和最后的几行的数据都不是要筛选的数据，这里先把取消默认的header）



根据输出的结果可以看出data是属于OrderedDict，那么就可以获取其中的keys和values
data.keys() #获取字典中的键，也就是sheet表的名称
data.values() #获取键对应的值，也就是sheet表中的内容

→ 输出的结果为：



尝试进行数据的索引，注意这里和普通的列表不是一样的，不能直接索引，需要先进行数据类型的转换，才可以进行索引，如下
list(data.keys())[0]  #提取第一个sheet的名称

print(type(list(data.values())[0]))  #查看一下转换后的数据类型
list(data.values())[0]  #获取第一个sheet表中的内容

→ 输出的结果为：



③ 数据清洗，需要剔除掉前四行和最后面几行的数据，并将第四行作为表格的列标题，代码如下
data_0 = list(data.values())[0]  # 以第一个sheet进行试错
data_0 = data_0.dropna(thresh = 6)  #如果该行的有效元素没有超过6个就会被剔除
data_0.columns = data_0.iloc[0]  #因为前几行最多的行元素有效值为6，所以就全部被剔除，自然第5行就变成第1行了，这里就直接把第一行的内容变成列标题
data_0 = data_0[data_0['Sample Type'] == 'Unknown Sample']  #筛选目标数据
data_0  #数据展示

→ 输出的结果为：（可以发现前面几行和最后几行的数据全部被剔除）



④ 数据分别保存在同一表格文件下对应的sheet中，这里直接将对应的数据变量写入到对应的sheet表中保存即可
writer = pd.ExcelWriter(f'{file_name}') 
data_i.to_excel(writer,sheet_name = list(data.keys())[i], index = False)  #这里的data_i就是分别提取每个sheet内容对应的数据变量
writer.save() 
writer.close()

3. 全部代码
为了方便调用，这里直接封装成函数，给一个命名文件，和要筛选的字段名及内容的接口，代码如下
import pandas as pd
import os
os.chdir(r'C:\Users\86177\Desktop') #这里填自己要处理文件的位置

data  = pd.read_excel('AA_test.xlsx',header=None,sheet_name = None)

def output_data(file_name,column,comment):
    writer = pd.ExcelWriter(f'{file_name}') 
    for i in range(len(data)):
        data_i = list(data.values())[i]
        data_i = data_i.dropna(thresh = 6)
        data_i.columns = data_i.iloc[0]
        data_i = data_i[data_i[column] == comment]
        data_i.to_excel(writer,sheet_name = list(data.keys())[i], index = False) 
    writer.save() 
    writer.close()

output_data('filter_data.xlsx','Sample Type','Unknown Sample')

→ 输出的结果为：（比如这里选择倒数第二sheet表中的内容查看）



最后的一个Component表中是没有符合要求的数据的，因此是属于空表单，但是还是要保存在sheet的，核实结果如下，只留下了列标题名称，完成任务



至此，筛选同一表格下的多个sheet里的内容并保存在对应的sheet中全部内容梳理完毕，难点在于如何获取全部的sheet名称及内容，以及存储到同一个文件中，数据清洗较为简单，完毕。

两个表格（不是在同一个表格中筛选）中的数据个数不用一样多，一个表格可能有很多数据，另一个可能很少进行数据筛选重复项。

 
想在表一中显示同时存在于表二中的数据
 
步骤：
一、先选中表二中的数据
二、Ctrl + F3 弹出名称管理器

新建

 
本例中我们设置名称为ABC

 
再选中表一中的数据
 

 
选择条件格式--->新建规则
 

选择最后一个规则
填写公式；
设置格式；
 
 
 
 
 
转载于:https://www.cnblogs.com/chillytao-suiyuan/p/10536123.html