EXCEL图文教程

用微信扫码二维码

分享至好友和朋友圈

编按：小伙伴们，你们好呀！开工后，大家一定都很忙吧？有部分小伙伴可能忙着做年初计划，开始一年中使用表格频次最高的时刻。提到计划一定少不了数据分析，表格中数据分析用到最多的就是数据透视表。今天跟大家聊一下使用数据透视表过程中遇到的经典案例。

首先我们来复习一下：如何在表格中插入数据透视表？

鼠标放在数据区域，在插入选项卡中找到数据透视表点击插入，随后会出现新建工作表存储（也可以自定义在当前工作表空白单元格存储）在新建的数据透视表中拖动透视字段到行和值中，会自动进行计算。

常规的数据字段项拖动到值字段时，会自动显示成求和，但是今天我们遇到的问题是所有的数据都显示计数项，这是为什么呢？

我们来手动选中值计数字段，将计算类型更改为求和看一下，发现更改求和项后结果显示为0，很明显数据存在异常的现象。

回到数据源检查数据发现原来数字是文本格式，我们都知道文本数字求和的结果会是0，对应数据透视表也会默认文本数据是计数，所以不会是求和。

我们全选数字所在单元格区域，将单元格格式设置为常规格式，然后复制数据，打开剪贴板，利用剪贴板所见即所得的原理，将数字由文本格式转换为常规可以求和的格式。

小互动：除了使用剪贴板将文本数据转换为数值，还有什么方法？(提示：数据*1)

紧接着我们回到刚刚的数据透视表里，打开数据透视表分析选项卡，找到刷新功能，选择全部刷新一下数据，会发现前面更改求和的字段数值不再是0了，证明前面的数据格式确实对数据透视表求和存在影响。

再次将其他值字段由计数项更改为求和即可，但是这里我们会发现一个问题就是遇到的字段项如果比较多的时候，如何批量将数据透视表的计数字段更改为求和字段呢？

可能此时有小伙伴们会将字段值全部取消勾选，然后再次选择到值字段中就会全部变成求和项了，操作如下图：

这样如果值字段只有少量时一定是最优选择，遇到非常多的字段需要全部点击选择一遍很明显就不简便了。

以下是小编手动调整将计数字段为求和字段后的效果图示：

既然聊到这里，小编也不藏着掖着了，直接上代码，使用VBA编程可以快速将数据透视表值字段中的计数字段更改为求和字段。代码如下：

Sub数据透视表字段转换()

Dim i As PivotField

With Selection.PivotTable

.ManualUpdate = True

For Each i In .DataFields

With i

.Function = xlSum'计数改求和调整这里；xlSum是求和、xlCount是计数

.NumberFormat = "#,##0"

End With

Next i

.ManualUpdate = False

End With

End Sub

代码操作注意事项：

第一步先复制代码，第二步从开发工具中打开VBE，插入一个新的模块，双击模块进入后粘贴代码，然后点击运行，运行前注意上面代码的计数和求和状态。

总结一下：我们介绍了如何插入数据透视表，常见的数据为文本型在数据透视表中不求和的解决办法是将文本数据转换为数值型，给大家推荐了剪贴板的所见即所得的原理，调整后的数据记得刷新数据透视表哦!使用VBA批量设置数据透视表字段时注意备份数据，因为VBA代码操作后不可以撤销的哦！

以上就是今天跟大家分享的内容，感谢大家耐心看完，希望大家能够喜欢!

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

将excel中的数据按照指定数量进行重复，非得用VBA才能实现吗？

如何在特定位置批量插入空行等12种实用办公技巧

4种删除excel重复值的小妙招，速收藏

Power Query的数据替换技巧比Excel函数更万能！

版权申明：

本文作者花花；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。

特别声明：以上内容(如有图片或视频亦包括在内)为自媒体平台“网易号”用户上传并发布，本平台仅提供信息存储服务。

Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services.