excel 使用技巧两则

1、人名去重

有很多人名，由于指导教师有很多位，因此如何将其转变为以下这种格式是个比较麻烦的问题：

应该应用文本去重功能。先将“、”号转变成为\r\n，再删掉空格等字符。

然后通过文本去重工具，将其去重。http://quchuchongfu.renrensousuo.com/

2、F9的妙用，将数组变为带括号的字符。

需要将区域①的姓名列表转换成区域②中的这种集合

{“曹迪”;”丁育萍”;”谈李清”;”徐晓”;”郭忠南”;”傅天泓”;”顾雯雯”;”黄燕飞”;”张灵芝”;”庞一凡”;”高煜宇”;”王凯”;”印蔚蔚”;”王亮”;”孟静”;”成洁”;”严浦钰”;”王卫仪”;”李红博”;”陈志英”;”孙杨丹”}
{“赵震奇”;”陆晓东”;”赵晓虹”;”尤晨”;”王建儒”;”于阳”;”黄睿”;”王丹丹”;”李纯”;”胡艳秋”;”钱玺娇”;”张迪”;”成晓琰”}
{“贾厚林”;”仲文祥”;”吴展遥”;”唐卫群”;”徐良芝”;”方菁”;”王琴”;”缪彩琴”;”钮克芳”;”袁红梅”;”马丽萍”;”钱颖雪”;”沈小倩”;”邵臻”;”刘畅”;”庄超超”}
{“刘立钧”;”尤俊伟”;”朱雪芳”;”高旅明”;”徐军”;”陆国民”;”徐自远”;”冯蕾琳”;”王晓斐”;”蔡妍娜”;”糜凌飞”;”辛峰杰”;”盛华”}
{“吴建”;”王芸”;”耿建军”;”丁祥青”;”何岚”;”吴雨星”;”马旻明”;”王月”}

可以选中之后按下F9按键，然后把等号去掉即可。

在一个单元格输入数组公式=A1:D3,在编辑栏按F9″抹黑”,把=去掉

为什么要这个呢，因为要做sumifs：

=SUM(SUMIFS(B$3:B$68,$A$3:$A$68,{“曹迪”;”丁育萍”;”谈李清”;”徐晓”;”郭忠南”;”傅天泓”;”顾雯雯”;”黄燕飞”;”张灵芝”;”庞一凡”;”高煜宇”;”王凯”;”印蔚蔚”;”王亮”;”孟静”;”成洁”;”严浦钰”;”王卫仪”;”李红博”;”陈志英”;”孙杨丹”}))

今天突然对sumifs的应用有了点兴趣。个人觉得可以取代部分sumproduct的多条件求和功能。

1、客户A的销售额
=SUMIFS(C2:C22,A2:A22,”A”)

可替换公式：
=SUMPRODUCT(C2:C22*(A2:A22=”A”))
=SUMIF(A2:A22,”A”,C2:C22)

2、客户A的1月份销售额
=SUMIFS(C2:C22,A2:A22,”A”,B2:B22,1)

可替换公式：
=SUMPRODUCT(C2:C22*(A2:A22=”A”)*(B2:B22=1))

3、客户A的1月份和3月份销售额
=SUM(SUMIFS(C2:C22,A2:A22,”A”,B2:B22,{1,3}))

可替换公式：
=SUMPRODUCT(C2:C22*(A2:A22=”A”)*(B2:B22={1,3}))

4、客户A和C的销售额
=SUM(SUMIFS(C2:C22,A2:A22,{“A”,”C”}))

可替换公式：
=SUMPRODUCT(C2:C22*(A2:A22={“A”,”C”}))
=SUM(SUMIF(A2:A22,{“A”,”C”},C2:C22))

5、客户A和C的1月份销售额合计
=SUM(SUMIFS(C2:C22,A2:A22,{“A”,”C”},B2:B22,1))

可替换公式：
=SUMPRODUCT(C2:C22*(A2:A22={“A”,”C”})*(B2:B22=1))

6、客户A的1月份和客户C的3月份销售额合计
=SUM(SUMIFS(C2:C22,A2:A22,{“A”,”C”},B2:B22,{1,3}))

可替换公式：
=SUMPRODUCT(C2:C22*(A2:A22={“A”,”C”})*(B2:B22={1,3}))

7、客户A和客户C的1月份和3月份销售额合计
=SUM(SUMIFS(C2:C22,A2:A22,{“A”,”C”},B2:B22,{1;3}))

*注意此公式7和公式6的差异仅为{1,3}和{1;3}中间的符号。

可替换公式：
=SUMPRODUCT(C2:C22*(A2:A22=”A”)*(B2:B22={1,3}))+SUMPRODUCT(C2:C22*(A2:A22=”C”)*(B2:B22={1,3}))

8、客户A和客户C的1月份\3月份\4月份销售额合计
=SUM(SUMIFS(C2:C22,A2:A22,{“A”,”C”},B2:B22,{1;3;4}))

可替换公式：
=SUMPRODUCT(C2:C22*(A2:A22=”A”)*(B2:B22={1,3,4}))+SUMPRODUCT(C2:C22*(A2:A22=”C”)*(B2:B22={1,3,4}))

9、客户A\B\C的1月份\3月份\4月份销售额合计
=SUM(SUMIFS(C2:C22,A2:A22,{“A”,”B”,”C”},B2:B22,{1;3;4}))

替代公式：
=SUMPRODUCT(C2:C22*(A2:A22=”A”)*(B2:B22={1,3,4}))+SUMPRODUCT(C2:C22*(A2:A22=”B”)*(B2:B22={1,3,4}))+SUMPRODUCT(C2:C22*(A2:A22=”C”)*(B2:B22={1,3,4}))

如果再次增多就可以看到SUMIFS的优势了。

大家可以看到，SUMIFS在7和8的情况下，字符明显减少。（当然上面的情况好些还能用MMULT完成，但感觉SUMIFS更加简洁易懂）

大家一起来探讨一下，这个新函数的还有什么新特性。。。。。

10、客户A的数量
=COUNTIFS(A2:A22,”A”)

替代公式：
=SUMPRODUCT(–(A2:A22=”A”))
=COUNTIF(A2:A22,”A”)

11、客户A和B的数量
=SUM(COUNTIFS(A2:A22,{“A”,”B”}))

替代公式：
=SUMPRODUCT(–(A2:A22={“A”,”B”}))
=SUM(COUNTIF(A2:A22,{“A”,”B”}))

12、客户A和B的1月份数量
=SUM(COUNTIFS(A2:A22,{“A”,”B”},B2:B22,1))

替代公式：
=SUMPRODUCT((A2:A22={“A”,”B”})*(B2:B22=1))

13、客户A和B的1\3月份数量
=SUM(COUNTIFS(A2:A22,{“A”,”B”},B2:B22,{1;3}))

替代公式：
=SUMPRODUCT((A2:A22={“A”,”B”})*(B2:B22=1))+SUMPRODUCT((A2:A22={“A”,”B”})*(B2:B22=3))
*如果条件更多，COUNTIFS的优势就显现出来了。

14、客户A的1月份和客户B的3月份数量
=SUM(COUNTIFS(A2:A22,{“A”,”B”},B2:B22,{1,3}))