本文目录一览：

Excel图表：自动化图表之“切片器”！

文|仟樱雪

本文主要介绍涉及Excel可视化中，利用“透视表”+“切片器”的组合实现自动化图表！

日常处理数据中，不仅需将数据做成透视表，也需要做成各种图表，而且是可以筛选的，能进行动态变化的图，真正做到 一张动态图表胜过你的千言万语的描述！

传统的Excel图表，数据区域是不能隐藏的，隐藏后会导致图表也隐藏掉，因此很多时候会用其他的图形盖住；

但是用透视图就可以隐藏数据区域，图表保留展示哦！

案例： 某电商公司，需分析阿里，京东，天猫平台这3个销售渠道上各品类产品的销售业绩完成情况。

第一：阿里、京东、天猫3个渠道的所有商品的销售目标完成情况；

第二：阿里和京东，或者阿里和天猫平台，或者京东和天猫平台，这3个渠道中，2个渠道组合的商品的销售目标完成情况；

第三：阿里、京东、天猫平台，这个3个渠道单个渠道的销售目标完成情况；

一、数据准备：

step1：原始数据；销售平台导出的商品销售信息表，最原始的信息表；

step2：选中数据源区域，点击“插入”--左上角的“数据透视表”--确定，进行数据透视，选择需要展示的字段名称：将“产品名称”拖入行区域、将“收入”拖入“值”区域，此时是2个字段；

step3：透视表新建计算字段，选中“透视表”，点击“分析”--点击“字段、项目和集”，选择新建“计算字段”，命名为“未完成”，公式=需按中字段区域中的“目标收入”字段，减号，选中字段区域中的“收入”字段，点击“添加”，则将该字段自动 计入“值”区域显示出来；

step3：修改透视表的“行标签”为“产品名称”，将“求和项：收入”修改成“收入完成”，“求和项：未完成”修改成“ 未完成+1个空格键 ”，输入即可修正，然后修改透视表表头墨绿色，字体为微软雅黑，白色加粗字体，求和区域数据，设置成数值，小数位保留0位等；

注意：在直接输入一个“未完成”时，会弹出字段名称重复的警告，由于之前新建了一个计算字段叫“未完成”，此时透视求和字段，是新的字段，因此输入一个空格可以，类似的新生成了一个“未完成+1个空格”的形式

二、数据透视图绘制：

step：选中透视图区域，点击“插入”，选择“数据透视图”，从而弹出“插入图表”对话框，此处选择“百分比堆积条形图”；

step2：选中图表，将不需要的图表元素，删除网格线、图例，横坐标轴；

step3：选中图表，右键选择“设置图表区域格式”，填充选择 无填充 ，边框选项，选择无边框；

step4：选中填充图表区域，右键选择“设置绘图区格式”，填充选择 无填充 ，边框选项，选择无边框；

即可将图表的背景都绘制成 透明 、无边框线的图表；

step5：此时图表中还包含有透视表的一些“字段按钮”，选中字段按钮，右键选择“隐藏图表上所有字段按钮”即可隐藏

step6：选中图表的完成区域，设置颜色为墨绿色，将未完成区域设置成豆沙粉色；

再次选中“完成”区域，右键，选择添加“数据标签”，选中数据标签，设置字体格式为Agency FB， 棕褐色 加粗显示，调整字号大小；

step7：选中纵坐标轴的“产品名称”区域，设置字体格式为Agency FB， 棕褐色 加粗显示，调整字号大小；

再次选中纵坐标轴，点击格式，设置“形状轮廓”为无轮廓，即可将坐标轴的线去掉；

step8：切记一定选中透视图，点击“分析”或者“插入”，选择“切片器”，勾选“平台名称”字段，作为切片的筛选器；

step9：选中切片器：右键，选择“切片器设置”，去掉“页眉”下的“显示页眉”的勾选，保证切片器的顶部无说明，简洁大方，此时会发现切片器的选项很窄小，切片器很宽；

再次选中切片器，点击“选项”，将“高度”的数字增大一些，直到合适的高度即可

此时做切片器的筛选，可以是单选，也可以是按下“ctrl”的多选哦！图形发生变化，进行动态展示，且可以隐藏掉数据区域的展示。

（注：2018.12.12,Excel常见分析大小坑总结，有用就给个小心心哟，后续持续更新ing）

excel怎么实现报表自动化？

python操作Excel实现自动化报表

贺霆

贺霆

算法工程师

529 人赞同了该文章

Excel作为日常办公最广泛使用的工具之一，学好它可以帮助我们节约时间，提升工作效率，学会灵活高效的使用excel有着十分重要的意义。

本人觉得会使用大部分excel内置公式和以及VBA的就是高手了。

不过比这更高级的是用程序操作excel，针对相似性很高或者重复性、繁琐的excel任务用一个程序可以实现一劳永逸，夸张的说：可以节省你的生命 哈哈。

以下是python操作excel的介绍：

一、工具包

1.xlrd：从Excel电子表格中提取数据 doc地址：

2.xlwt：将数据写入Excel电子表格 doc地址：

3.xlutils：提供一组处理Excel文件的实用程序 doc地址：

二、安装

python -m pip install xlrd xlwt xlutils

三、基本用法

python操作excel的相关工具包可以具体到操作指定单元格的填充样式、数值类型、数值大小等等。然而python操作excel需要一定pandas数据处理功底，后续将补上章节：pandas数据处理技能

1.从指定文件路径读取excel表格，进行一定操作，然后保存到另一个excel文件：result.xlsx

import xlwt

import xlrd

from xlutils.copy import copy

import pandas as pd

from pandas import DataFrame,Series

import os

os.chdir('./')

# 从指定文件路径读取excel表格

df = pd.read_excel('D:/mypaper/data/data.xlsx')

# 查看df内容

# 根据age算出出生年份，增加一列

import datetime

import os

year = datetime.datetime.now().year#获取当前系统时间对应的年份

df['birth'] = year-df['age']

df.to_excel('result.xlsx')#保存到当前工作目录，可以用os.getcwd()查看

#查看下此时df的内容，可以看到已经生成了birth这一列

乍看好像只用到了pandas，还没有用到上面介绍的三个工具包，下面介绍利用python操作excel底层

2.单元格操作

# 定义方法：读取指定目录下Excel文件某个sheet单元格的值

def excel_read(file_path,table,x,y):

data = xlrd.open_workbook(file_path)

table = data.sheet_by_name(table)

return table.cell(y,x).value

# 定义方法：单元格值及样式

write_obj_list = []

def concat_obj(cols,rows,value):

write_obj_list.append({'cols':cols,'rows':rows,'value':value,/

'style':xlwt.easyxf('font: name 宋体,height 280;alignment: horiz centre')})

# 定义方法：合并单元格

def merge_unit(srows,erows,scols,ecols,value):

write_obj_list.append({'id':'merge','srows':srows,'erows':erows,'scols':scols,/

'ecols':ecols,'value':value,'style':xlwt.easyxf('font: name 宋体,height 280;alignment: horiz centre')})

# 定义方法：更新excel

excel_update(file_path,write_obj_list,new_path):

old_excel = xlrd.open_workbook(file_path, formatting_info=True)

#管道作用

new_excel = copy(old_excel)

'''

通过get_sheet()获取的sheet有write()方法

'''

sheet1 = new_excel.get_sheet(0)

'''

1代表是修改第几个工作表里，从0开始算是第一个。此处修改第一个工作表

'''

for item in write_obj_list:

if 'id' not in item.keys():

if 'style' in item.keys():

sheet1.write(item['rows'], item['cols'], item['value'],item['style'])

else:

sheet1.write(item['rows'], item['cols'], item['value'])

else:

if 'style' in item.keys():

sheet1.write_merge(item['srows'],item['erows'],item['scols'], item['ecols'], item['value'],item['style'])

else:

sheet1.write_merge(item['srows'],item['erows'],item['scols'], item['ecols'], item['value'])

'''

如果报错 dict_items has no attributes sort

把syle源码中--alist.sort() 修改为---- sorted(alist)

一共修改2次

'''

new_excel.save(file_path)

#参数详解

# srows:合并的起始行数

# erows:合并的结束行数

# scols:合并的起始列数

# ecols:合并的结束列数

# value:合并单元格后的填充值

# style:合并后填充风格：

# font: name 宋体

# height 280;

# alignment: horiz centre

# ... 与excel操作基本保持一致

注意:该方法仅仅是将需要直行的动作保存到一个list中，真正的动作还未执行,执行动作是发生在excel_update方法中

最终调用excel_update方法，传入每个单元格需要进行的操作和填充值的write_obj_list以及文件保存路径file_path

就可以在当前工作目录下生成想要的Excel结果文件。

注意：

1.write_obj_list支持用户自定义

2.write_obj_list也可以是根据excel_read方法读取现有待修改的excel文件（可以维持原有表格的格式）而生成

python操作excel还有很多其他的基本方法，因篇幅的限制不再陈述和演示，想要深入研究的可以点击上面的doc地址

Python操作Excel实现自动化报表

Python操作Excel实现自动化报表

安装

python -m pip install xlrd xlwt xlutils。

基本用法

1.从指定文件路径读取excel表格，进行一定操作，然后保存到另一个excel文件：result.xlsx

import xlwt

import xlrd

from xlutils.copy import copy

import pandas as pd

from pandas import DataFrame,Series

import os

os.chdir('./')

# 从指定文件路径读取excel表格

df = pd.read_excel('D:/mypaper/data/data.xlsx')

# 查看df内容

# 根据age算出出生年份，增加一列

import datetime

import os

year = datetime.datetime.now().year#获取当前系统时间对应的年份

df['birth'] = year-df['age']

df.to_excel('result.xlsx')#保存到当前工作目录，可以用os.getcwd()查看

#查看下此时df的内容，可以看到已经生成了birth这一列。

2.单元格操作

# 定义方法：读取指定目录下Excel文件某个sheet单元格的值

def excel_read(file_path,table,x,y):

     data = xlrd.open_workbook(file_path)

     table = data.sheet_by_name(table)

     return table.cell(y,x).value

# 定义方法：单元格值及样式

write_obj_list = []

def concat_obj(cols,rows,value):

    write_obj_list.append({'cols':cols,'rows':rows,'value':value,/

'style':xlwt.easyxf('font: name 宋体,height 280;alignment: horiz centre')})

# 定义方法：合并单元格

def merge_unit(srows,erows,scols,ecols,value):

    write_obj_list.append({'id':'merge','srows':srows,'erows':erows,'scols':scols,/

'ecols':ecols,'value':value,'style':xlwt.easyxf('font: name 宋体,height 280;alignment: horiz centre')})

# 定义方法：更新excel

excel_update(file_path,write_obj_list,new_path):

    old_excel = xlrd.open_workbook(file_path, formatting_info=True)

    #管道作用

    new_excel = copy(old_excel)

    '''

    通过get_sheet()获取的sheet有write()方法

    '''

    sheet1 = new_excel.get_sheet(0)

    '''

    1代表是修改第几个工作表里，从0开始算是第一个。此处修改第一个工作表

    '''

    for item in write_obj_list:

        if 'id' not in item.keys():

            if 'style' in item.keys():

                sheet1.write(item['rows'], item['cols'], item['value'],item['style'])

            else:

                sheet1.write(item['rows'], item['cols'], item['value'])

        else:

            if 'style' in item.keys():

                sheet1.write_merge(item['srows'],item['erows'],item['scols'], item['ecols'], item['value'],item['style'])

            else:

                sheet1.write_merge(item['srows'],item['erows'],item['scols'], item['ecols'], item['value'])

    '''

   如果报错 dict_items has no attributes sort

    把syle源码中--alist.sort() 修改为---- sorted(alist) 

    一共修改2次

    '''

    new_excel.save(file_path)

#参数详解

# srows:合并的起始行数

# erows:合并的结束行数

# scols:合并的起始列数

# ecols:合并的结束列数 

# value:合并单元格后的填充值

# style:合并后填充风格：

#     font: name 宋体

#     height 280;

#     alignment: horiz centre

#     ... 与excel操作基本保持一致

(注意:该方法仅仅是将需要直行的动作保存到一个list中，真正的动作还未执行,执行动作是发生在excel_update方法中)

最终调用excel_update方法，传入每个单元格需要进行的操作和填充值的write_obj_list以及文件保存路径file_path,就可以在当前工作目录下生成想要的Excel结果文件。

注意：

1.write_obj_list支持用户自定义

2.write_obj_list也可以是根据excel_read方法读取现有待修改的excel文件（可以维持原有表格的格式）而生成

End

如何熟练使用办公自动化中的excel

如何熟练使用办公自动化中的excel

新手怎么样快速学习办公自动化中的excel并运用到工作当中提高效率，远离加班?以下为我的个人建议。

①先掌握常用快捷键，这是最基础也最容易学习的，而且节省时间的效果立竿见影。最具经典的比较的就是用鼠标拖动几千条记录的滚动条和使用键盘快速定位的区别。

②接下来根据你工作需求学习常用函数。一定是从解决问题出发来学习对应的函数，死记硬背只会事倍功半，实践大于学习，动手好过动眼。

③掌握一些极其实用的菜单的功能，每个菜单都探索一下，微软搞那么多菜单不是做摆设(除了编辑菜单)。

④完成以上三步，你应该可以熟练使用excel了。到这个阶段应付日常工作已经得心应手了。如果你有兴趣可以做一些进阶学习，比如追求表格的质量(格式美观、表格规范、数据准确以及布局排版)，化繁为简，少即是多，好的表格就是一份有质量的报告，数据自有其魅力，这个时候你可以尝试学习下数据分析、数据挖掘方面的知识，也可以学习下VBA为数据分析做准备。

下方是详细说明：

一、合理分配键盘和鼠标。看到很多人连个保存都要用鼠标去点菜单，真心替他捉急。有些人用一指禅(一般是领导)操作更是恨不得自插双目。excel的基本操作一定是左右互搏，左手键盘，右手鼠标，而且键盘的使用率要超过鼠标。一般来说鼠标用于：①选择某个单元格。②选择可视范围内的小块区域。③行列整选。④超长篇幅的滚动条拉动。⑤选择菜单。除此之外的常用操作，强迫自己用键盘吧，键盘+快捷键将成为你节省时间的功臣。

二、批量数据处理的神器——公式(函数)。公式不难，难在知道怎么用。学习公式的方法也很最简单，不用看书，不用看教程，在编辑栏点fx，进去一个个看几遍操作提示你就懂了。

——学习公式前先了解单元格定位，公式是通过复制和拖拉来提高效率的，在复制和拖拉的过程中会根据当前的行列改变引用的单元格。比如你引用A1，往下拖的时候会变成A2、A3...，往右拖会变成B1、C1...，有时候你不希望单元格随拖动而改变，这个时候就需要绝对定位，也就是固定行标或者是列标。通过在行标或者列标前加“$“实现(编辑状态下按F4快速添加，重复按则进行切换)，比如$A1，表示固定A列，无论你往右拖多少列，都是引用A1单元格，同理A$1表示固定第一行不便。很多新手在用公式的时候都会被定位搞得头疼无比，理解以后就开启了新天地。举个实例，用Vlookup按条件查找sheet2表中的B:L连续区域，你可以这样写=VLOOKUP($A1,Sheet2!$A:$L,2,0);依次向右拖拉，把2改为3,4,5...即可(下面讲如何优化)。

——公式的运用在于你灵活的安排，很多人认为凡是计算必上公式，而且嵌套越多越复杂越是高大上，唬下菜鸟可以，其实是闲的蛋疼。比如筛选一列数据的.重复值删除，你可以用一个高大上的公式：match(A1,A:A,0)=ROW(A1),然后把结果为false的筛选出来删掉，其实一个数据透视就搞定，但如果这个表有100列，你要用数据透视过滤我只能泪牛满面。再比如身份证提取生日，一般用mid(a1,7,if(len(a1)=16,6,8))，其实mid(a1,7,(len(a1)=18)*2+6)，省去if嵌套，这个例子似乎不太恰当，只是说公式是死的，要提高效率关键还是看怎么运用他们。还有什么时候用Vlookup，什么时候用Lookup，以及index和match的区别等等。

上面vlookup的例子：=VLOOKUP($A1,Sheet2!$A:$L,COLUMN(),0)，把要手动输入的列数用当前列号函数进行嵌套，根据你要选择数据的起始列进行+-数字调整即可。这样你只要在B1单元格输入一个公式，就可以愉快的向右向下随便拖拉了。

——进阶。知道了怎么组合运用公式后，考虑的是怎么简化公式，可以学习下命名。还是VLOOKUP那个例子，你可以把Sheet2!$A:$L取个名字叫area，那么公式变成=VLOOKUP($A1,area,COLUMN(),0)。命名引用区域很多的时候很有用。

三、熟练掌握菜单栏的一些常用操作。可以参考这样的学习顺序：①排序(注意扩展相关列)、冻结窗口、②筛选(自动筛选(*号、?号匹配等技巧)，高级筛选可以了解下)、③分列(固定分列和符号分列，注意分列后的数据格式)、④数据透视。其他的如条件格式、数据有效性、自定义单元格格式、自动更正等技巧也要知道。工具里面的选项菜单是必须要全部掌握的，比如遇到大数据量公式计算的时候先改为手动重算，输入公式填充后再改为自动重算或者直接保存，否则会卡很长的时间(可能电脑配置好不会有这个情况)。

四、数组公式。这里单独说一下数组公式。在大范围数据计算的时候很有用。这里说一个常用的多条件求和。sum((条件一)*(条件2)*(条件n))，输入完公式后在编辑状态同时按下ctrl+shift+enter，条件一般是区域比较，比如(A2：A1000=B2：B1000)，数组公式的好处是选中结果单元格，一次输入，按组合键自动填充无需拖动复制(我不会告诉你数组公式计算后排序可能会很诡异的)。数组公式结合命名用使用更简单，比如={sum((性别="男")*(学号50)*(...))}

五、如果说excel什么键用得最多，记得是ctrl+s。一分钟保存10来次毫无违和感，从来没有丢失数据的烦恼，别指望后台几分钟保存一次，对于excel熟练者来说，一分钟已经可以完成几张表了。这个强迫症我沿用了好多年，很有用。 ;

excel数据汇总自动化方法 excel 如何实现一键全自动化统计

1、【数据】-【获取数据】-【来自数据库】，也可以来自本地文件或其他方式，这里以mysql数据源为例讲解。

2、连接数据库，填写SQL语句，点击“确定”。数据查询完后弹出查询结果，点击下方【加载】-【加载到】选择数据放置位置。

3、数据的放置位置可以选现有工作表（选择一个单元格）或新工作表，点击确定即可加载到表中。

4、数据源更新完成后，下面制作报表，主要是利用函数公式来完成（此数据源为分部门、分日的业绩数据，一般报表中需统计当前业绩、同环比等数据，可根据具体需要设置表头）。

5、报表表头设计，并使用日期公式添加辅助统计条件。

6、统计周期起止日期确定后，下一步计算对应周期内的业绩，常用的为sumifs函数，注意使用固定符号$，方便下拉、右拉直接计算出对应结果。

7、汇总公式填写完后，自动报表即制作完成。之后每天刷新数据即可自动获取数据库最新数据，报表也会根据最新的统计时间周期计算相应的完成。