玩转行列数据的转换，就是这么任性！

▲

01

一行转一列

方法一、选择性黏贴

操作步骤：

选中要转换的数据区域A5:I5→复制→右键选择性黏贴→转置→确定

方法二、函数公式

公式：

=INDEX(A$5:I$5,ROW(A1))

或者公式：

=OFFSET(A$5,,ROW(A1)-1)

02

一列转一行

选中要转换的数据区域A1:A9→复制→右键选择性黏贴→转置→确定

=INDEX($A1:$A9,COLUMN(A1))

=OFFSET($A1,COLUMN(A1)-1,)

03

一行转多行多列

1、先行后列

=INDEX($A$1:$I$1,(ROW(A1)-1)*3+COLUMN(A1))

2、先列后行

=INDEX($A$8:$I$8,(COLUMN(A1)-1)*3+ROW(A1))

04

一列转多行多列

=INDEX($A$1:$A$9,ROW(A1)*3-3+COLUMN(A1))

=INDEX($A$11:$A$19,COLUMN(A1)*3-3+ROW(A1))

05

多行多列变一行

=INDEX($D1:$F3,INT(COLUMN(C1)/3),MOD(COLUMN(C1),3)+1)

=INDEX($D8:$F10,MOD(COLUMN(C1),3)+1,INT(COLUMN(C1)/3))

06

多行多列变一列

=INDEX(A$4:C$6,INT(ROW(A3)/3),MOD(ROW(A3),3)+1)

=INDEX(A$14:C$16,MOD(ROW(A3),3)+1,INT(ROW(A3)/3))

End

Excel函数公式的威力强大众所周知，但很多人还是用不好，原因之一是没有将千变万化的各种形式的公式实质性看透，只懂皮毛。

为了让大家更容易理解公式的框架结构和扩展方法，今天带来的这几个万能公式，会结合Excel实例展开，并且给出不同应用场景下的扩展方法。

1 多条件计数统计

2 多条件求和统计

3 多条件查找引用数据

万能公式的万能之处，最终不在于公式本身，而在于使用公式的人。

1 多条件计数统计

按照多个条件进行计数统计，是工作中很常见的需求。

咱们先看个按照两个条件统计符合条件的个数的数据的案例吧

案例场景截图如下：

F2=COUNTIFS(A:A,E2,C:C,">500")

多条件计数统计的万能公式

=COUNTIFS(条件区域1，条件1，条件区域2，条件2.....条件区域N，条件N)

这样，即使有再多的条件，以此类推，在公式中添加参数扩展即可。

2 多条件求和统计

按照多个条件进行求和汇总统计，也是办公一族常遇到的事了。

案例场景截图如下：

G2=SUMIFS($D:$D,$A:$A,$F2,$C:$C,G$1)

注意这个公式中的单元格混合引用的应用

多条件求和统计的万能公式

=SUMIFS(求和区域，条件区域1，条件1，条件区域2，条件2.....条件区域N，条件N)

利用SUMIFS，先声明求和区域，再依次罗列各种条件区域和条件，即使有再多的条件，以此类推，在公式中添加参数扩展即可。

3 多条件查找引用数据

查找引用数据的需求，几乎每天都有，如果你还只会Vlookup，那很多问题是无法解决的。

比如下面的这个多条件查找引用数据的问题。

案例场景截图如下：

=LOOKUP(1,0/(($B$2:$B$13=F2)*($C$2:$C$13=G2)),$D$2:$D$13)

多条件查找引用数据的万能公式

=LOOKUP(1,0/((查找条件1)*(查找条件2)*......*(查找条件N)),查找结果所在区域)

利用LOOKUP函数可以忽略错误值查找，以及支持区间判断查找的特性，再多的查找条件，按如上万能公式套用即可。

今天的分享就到这里，希望能够帮到大家轻松应对各种情况的工作日计算！

如果你觉得有用，就分享给朋友们看看吧~ 记得关注我哦 谢谢大家！！ 明天再见

Excel中有很多技巧，看似简单但是却能够有效提高工作效率的技巧；

下面小编给大家推荐几个比较实用的excel技巧。

一、快速提取字符串中的数字

同样是两端对齐的功能，不仅能实现数据的合并，还能定向提取。

案例：快速提取字符串中的数字

如图所示，不使用函数或者快速填充功能，提取出A列单元格中的数字。

方法：

1、先将A列宽度设为小于一个字符，然后选定A列，点击【填充】→【两端对齐】，如图所示。

由于A列宽度小于一个字符，这样的填充会对单元格内容进行重拍，从而分离出A列的数字和汉字，如图所示。

2、用定位功能删除非数字字符的单元格，即可提取出各单格中的数字。

这是一个灵活使用填充功能的典型案例，很多人怎么也想不到填充功能尽然能够拆分单元格中的数据，通过此列才豁然开亮。

二、快速合并多行工具

通常情况下，在Excel中，不建议将多组数据写到一个单元格中，但是难免我们会碰到这样的需求：将多行数据快速汇总到同一个单元格中。

很多人想象不到的是，利用【填充】功能下的【两端对齐】竟然能实现数据合并的功能。

把列拉宽（宽到能够容下合并后的数据） - 选取数据 ， 一次点击【开始】 - 【填充】 - 【两端对齐】。

三、快速移动局部数据

这个技巧我在Excel拖拽技巧一文中提到过，方法就是按【Shift键】的同时鼠标拖动选中区域。

猛击这里可以回顾Excel拖拽技巧专题文章：

如何使用Excel“拖拽”技巧，10倍提升工作效率

四、excel转pdf

进入迅捷pdf在线转换器页面，选择文档转换中的“excel转pdf”选项，点击页面上的“添加文件”选项，将要转换的excel表格添加进来，文件被添加完成之后，点击“开始转换”按钮，最后将转换完成的文件下载保存即可。

五、快速输入26个字母

Excel中有一个 CHAR()函数，能将ASCII码转换成对应的字符。别的先不说，字母A就是65。

所以，我们在一个单元格中输入：=CHAR（65），就能得到大写字母A。

但为了得到26个字母序列，我们将上述公式改为=CHAR(64+ROW(A1))

ROW函数返回单元所在的“行号”。

这样向下填充时ROW（A1）会逐渐变成A2，A3，……，A25。

以上内容就是今天小编为大家分享的，喜欢的小伙伴可以及时收藏哦。

在高手眼中，"定位"（快捷键：Ctrl+G或者F5）是Excel最不必不可缺的功能之一。在日常工作中，Excel"定位"主要用于批量选择多个具有某种特征（例如空值、数据验证、公式等）的不连续单元格。

案例一、批量求和。

如下图所示，如何快速得到所有的求和结果呢？

技巧：1）选中D1:G17区域，按F5打开定位对话框，然后单击定位条件—空值，再单击确定，即可选中所有要求和的单元格。如下图所示：

2）按下Alt+=组合键即可得到所有的求和结果。如下图所示：

案例二、批量更改表中数字的格式。

如下图所示，如何将下表中所有的数字字体加粗，并改为微软雅黑字体？

技巧：1）通过定位选中所有值为数字的单元格；选中A1:D23区域，按F5（或者Ctrl+G快捷键）打开定位对话框，单击定位条件，选择常量，选择数字，单击确定。

这样所有的的数字单元格就全部被选中了。

2）设置单元格的格式；字体选择微软雅黑，加粗。

案例三、批量在部门末行添加空行。

如下图所示，如何批量快速地在每个部门的末行添加一个空白行呢？

技巧：1）A列后添加辅助列；并将A1:A31的内容复制到B2:B32，如下图所示：

2）选中A1:B32,按下Ctrl+G快捷键打开定位对话框，单击定位条件，选择行内容差异单元格，单击确定。

这时我们可以看到，所有行内容有差异的单元格的都被选中了。

3） 添加空白行；直接按下快捷键Ctrl+Shift+=,选择"整行"，单击确定。即可完成添加空白行的操作，然后删除表头两行及B列即可。

数据有效性（也叫数据验证）是我们使用频率最多的一个功能，比如下拉菜单的制作、限制性输入等，那你真的会用数据有效性吗？本期通过6个经典案例与大家分享一下数据有效性的用法。

1、下拉菜单的制作

下拉菜单是数据有效性最经典的用法，这里与大家分享一下相关的制作方法。

一级下拉菜单的制作：

选择区域，点击数据——数据验证——设置——验证条件——序列，之后选择来源区域即可。

注意：来源若是手动输入，要注意的是文本之间的分隔符是英文状态下的逗号。

二级下拉菜单的制作：

先选中区域，点击公式——定义名称——根据所选内容创建——勾选首行，之后建立一级菜单和二级菜单，做二级菜单时，来源写公式=INDIRECT(单元格地址)。

2、在指定的区域输入数字

选中区域，打开验证对话框，在允许条件中选择整数，之后根据需要输入相应的区间。

3、只允许输入数字

这时选择的条件是自定义，之后输入公式=ISNUMBER(B1)=TRUE。

4、逐行输入内容

一样是选择自定义，在公式中输入=COUNTA(A$1:A13)=ROW(A1)

5、输入唯一值

同理，选择自定义输入公式=COUNTIF(A:A,A1)=1

6、输入当前时间

选中区域，之后按Ctrl+1打开设置单元格格式，然后选择自定义，输入数字格式H:MM:SS，点击确定。

这里需要在其他任意单元格输入公式=NOW()

完成以上操作之后，直接选中区域，打开数据验证，选择序列，来源设置=刚刚输入的公式的单元格。

这几个用法，你会了么？若有什么问题欢迎在下方留言。

效果

抽奖公式=INDEX(A:A,RANDBETWEEN(3,COUNTA(A:A)-2))该公式，随机获取A列中的人员，达到抽奖效果。Index函数返回表格或区域中的值或值的引用。用法：INDEX(要查找的区域, 第几行, 第几列)如示例中，从A列中，随机获取人员姓名。Randbetween函数返回位于指定的两个数之间的一个随机整数。每次计算工作表时都将返回一个新的随机整数。用法：randbetween(开始数字,结束数字)类似函数还有rand生成0~1随机数如示例中，第1，2行是标题行，所以，随机生成从3~38的随机数Counta统计函数计算区域 中不为空的单元格的个数。用法：Counta(区域)Count家族是统计中不可获取的一组函数，相关的函数有Count、Counta、Countif、Countifs、Countblank函数。如示例中，通过Counta函数获取人员数量。

F9 重新计算Excel中的计算可以有手动计算，默认是自动计算

当工作簿中含有大量数据和公式时，每次修改公式、打开关闭文档都很卡时，那建议将自动计算修改为手动计算。示例中，每次按下F9，则Randbtween函数随机取数一次，一直按着F9，则Randbetween会一直随机更换数字。

推荐阅读

制作便携、免安装软件程序

安卓手机秒变WIN10电脑桌面

推荐春节大家一起用小程序放电子鞭炮

效率软件WGestures：自定义全局鼠标手势

语音变声软件morphvox pro破解版下载及使用教程

【END】

—— 黑火软件大师 ——

专注各类软件的下载、安装、教学

“Excel数据透视表有一个灵活的特点，根据你的需求实时变动汇总方式”

01

—

实例分析

如下所示：是一份销售流水数据，有时间，月份，区域，商品，数量，金额几个字段，如下所示：

现在针对不同的数据汇总需求，可以制作不同的数据透视表进行汇总分析数据，关键是各个字段需要放置的位置不同，从而得到我们想要的结果

02

—

呈现数据方式案例

问题1、需要知道各个区域销售的数量和金额情况？

思考几秒钟，这种需要将“区域”字段拖动至行，将“数量”和“金额”字段拖动至值即可，操作窗口如下所示：

问题2：需要知道每个区域，各种商品销售的金额，有两种展示方式

第1种是将区域和商品字段都拖入行，将金额插入值，效果如下：

第2种是将区域拖入至行，将商品拖入至列，将金额拖入至值，得到的结果如下：

问题3：需要知道各个区域，各种商品销售的数量和金额情况，也有两种展示方式

第1种是将区域和商品两个字段拖入行，将数量和金额拖入值，展示效果如下所示：

第2种是将区域插入至行，将商品拖入至列，将数量和金额拖入至值，展示效果如下所示：

从中我们总结一个数据透视表的规律，当我们的值标签里面有两个字段时，最好列标签里面不要放内容，要不然表格会很庞大。

问题4：需要知道每个区域每个商品，每个月销售金额是多少

当然根据自己的需要，月份，区域，商品，三个字段的位置可以调整，并且行项目里面可以上下级的更换位置，如下是商品和区域调换位置的结果：

主要看我们分析的侧重点在哪，然后决定字段的位置摆放

本节完，有什么不懂的可以留言讨论，期待您的转发分享

欢迎关注，更多精彩内容持续更新中...

--------------

小伙伴们大家好，我是小菜，今天是和大家分享关于Excel办公会用到的50个函数案例，希望能帮助到大家！

1、转换函数20例

text非常重要，后面会和大家分享一篇这个函数的文章。

2、基本统计函数17例

3、文本处理函数14例

这篇文章是小菜在自己头条号之前编辑的文章，现在分享在微信来希望能帮助到大家，当然大家可以关注技巧分享，对文章有任何疑问，欢迎评论处留言，小菜会和大家免费解答的，谢谢大家，记得分享转发给其他小伙伴哦！

为了凑字数真的不容易，原谅我的口水话！这下应该满了300字了吧！

作者：小菜，一个热爱学习的人，对Excel情有独钟的人，一个善于终结分享的人……

如果你是新朋友，扫码关注下方二维码，便每天可以和小菜一起学习，一起提升技能！

每天一起学习，一起进步。

可能很多朋友不知道Excel中VBA的用途。那什么是VBA呢？

一句话概括：在Excel中想实现什么功能，就可以用VBA语言编写一段程序去完成。

今天通过例子带大家了解Excel中的VBA！

软件说明：Excel 2010版本

场景再现：表格中点击按钮，向单元格E1中输入数字 “233”，并且能够实现清除！

第一步、打开编写VBA代码的窗口

在Excel表格中编写VBA的窗口叫VBE编辑器，有两种打开的方法。

在工作表标签上右键 - 查看代码按Alt+F11

第二步、创建写VBA代码的地方

有朋友肯定会问：“我没学会编程怎么办？”、“我都没见过代码能行吗？”；别急！我的回复是肯定的！

在哪编写代码呢？有好几种方式，今天先学最常用的：“插入” - “模块”。新建一个模块用来存放编写的代码；记住：修改模板的名称：我的VBA模板【如下图虚线标注】

第三步、开始写代码

新建模板后，在右侧的空白位置就是编写代码的地方。可以执行的VBA代码，结构是这样的：

Sub 程序名(参数)可以执行任务的代码End Sub

因为例子中我要进行输入与清除的操作，输入Sub 程序名()然后回车，End Sub就会自动输入；如下图：

在开始和结束语句之间输入代码，执行在单元格A1中输入数字"233"

Range("E1") = 123

备注：在VBA中，Range("单元格地址")来表示单元格。在单元格中输入值，直接用=值 即可(字符串两边要加双引用)，如果清空则 =""。

再编写一段清空代码：

第四步、测试运行代码

在编写代码时经常要测试是否正确。测试方式是把光标放在代码行的任意位置【下图中“测试按钮”的位置】，点击运行小按钮进行测试；同样清空代码也一样。

第五步、点击按钮执行VBA代码

在Excel中插入的图形、图片、按钮控件都可以执行VBA代码。让它们执行很简单，点击“插入” - “矩形” - “指定宏”

只需要右键菜单中点击指定宏 - 选取编写的宏名称，选择“输入”，添加输入按钮。

同样添加清除按钮：

当然自己可以调整字体的颜色、字体，让其变得更加美观！

此时，通过VBA设置的输入“233”，并可以点击清除！

第六步、保存VBA代码

此时的VBA代码应该是既有输入代码又有清空代码；最后需要保存为“启用宏的工作簿"类型【注意下图中虚线框标注】，VBA代码才能保存下来。

好了！今天的分享就到这里，是不是感觉VBA也没有想象的那么难？

赶快转发、关注吧，更多技巧尽在头条号中！

做财务真的不只是为了拿月薪3万多一点，而是与时俱进，活到老，学到老。

在卢子认识的人中，有不少是做财务的，有些财务经理年薪20-30万，Excel玩得炉火纯青。一句话概括：专业素养加Excel技能，能创造更高价值。

1.再忙也要把这两招对账技能学会！

每年对账，有不少财务都是用最原始最手工的方法，逐笔勾挑的，非常浪费时间。今天，卢子教你两招很实用的对账方法，可以为你省下不少时间。

第一招

现在有两个表，将银行账和手工账进行核对。在实际对账的时候，只有银行的借方金额和手工帐的贷方金额可以核对，其他的信息都是不同的。

银行下载的明细表

手工账的明细表

当金额都是唯一值的时候可以用VLOOKUP函数进行查找核对，但大多数情况下，金额是有可能出现多次的。有重复值的情况下用VLOOKUP函数查找就会出错。

对账要满足两个条件：

01 金额一样02 金额出现的次数也一样

举个例子，10元在银行这个表出现2次，在手工账这个表也出现2次，证明这个金额是正确的，也就是TRUE，否则就是FALSE。

统计金额的次数，可以用COUNTIF函数，函数语法：

=COUNTIF(条件区域,条件)

在手工账这个表，现在要统计每个贷方金额出现的次数。

统计金额在银行表出现的次数：

=COUNTIF(银行!B:B,G2)

两个公式综合起来：

=COUNTIF(G:G,G2)=COUNTIF(银行!B:B,G2)

将有问题的金额（FALSE）筛选出来，只对这些有问题的进行核对，会减轻很多工作量。

选择任意一个FALSE的单元格，右击，选择“筛选”，单击“按所选单元格的值筛选”。

筛选后的效果。

手工账核对完，银行账也可以用同样方法进行核对。

第二招

系统与手工两个表，必须满足客户名称、金额、日期、出账状态完全相同才是正确的。

系统下载的明细表

手工录入的明细表

思路：将四个条件合并起来，在另外一个表进行计数，次数等于1就是正确。

条件计数的万能公式：

=COUNTIFS(条件区域1,条件1,条件区域2,条件2,条件区域3,条件3,条件区域n,条件n)

在手工表的E2输入公式，并向下填充公式，显示0的就是错误的。

=COUNTIFS(系统!$A$2:$A$20,A2,系统!$B$2:$B$20,B2,系统!$C$2:$C$20,C2,系统!$D$2:$D$20,D2)

用同样的方法，在系统表的E2输入公式，并向下填充公式，显示0的就是错误的。

=COUNTIFS(手工!$A$2:$A$20,A2,手工!$B$2:$B$20,B2,手工!$C$2:$C$20,C2,手工!$D$2:$D$20,D2)

这样就能找出两个表不同的内容，再筛选出0即可。

说明：如果手工表存在空格或者格式跟系统表不一致，必须先进行处理，将手工表和系统表都整理成规范表格再对账。

2.用最快的方法将金额合计为540.00的银行明细找出来

手工记录的时候都是记录每一笔账的总金额，而实际银行明细有的时候是多条记录的，现在要将贷方金额为540.00的银行明细找出来。

如果不懂方法，这种是要花费大量的时间和精力。其实借助规划求解的功能也可以快速帮你实现。

默认情况下，Excel是没有规划求解的功能，需要重新加载才可以。

单击“文件”→“选项”→“加载项”→“转到”。

勾选“分析工具库”和“规划求解加载项”，单击“确定”按钮。

这样就可以在“数据”选项卡的右边看到“规划求解”这个功能。

添加完后，就可以开始操作。

Step 01在D2输入公式。

=SUMPRODUCT(A2:A67,B2:B67)

Step 02在“数据”选项卡的最右边单击“规划求解”，在弹出的“规划求解参数”对话框，设置目标为$D$2，目标值为540.00，通过更改可变单元格为$B$2:$B$67，单击“添加”按钮。

Step 03单元格引用为$B$2:$B$67，选择bin，约束为二进制，单击“确定”按钮。

Step 04设置完条件，单击“求解”按钮。

Step 05经过大概1分钟就将目标值计算出来，单击“确定”按钮。

Step 06这样B列为1的就是满足条件的值，选择任意一个1，右击，选择“筛选”→“按所选单元格的值筛选”。

这样就将所有符合条件的金额筛选出来。

当然规划求解也不是万能的，当数据比较多的时候也是求解不出来，这时需要借助超级复杂的VBA代码。

将明细的金额复制到A列，在B2输入目标值540.00，单击“开始凑金额”按钮。

瞬间就将满足条件的组合值列在F列。

操作动画

用鼠标单击它，在你不经意间，这个动作背后隐藏的付出，只有原创作者本人才知道。