EXCEL学习记录集合

52UEXCEL

众所周知在EXCEL函数中，“查找与引用”函数甚多，SUMPRODUCT（）并没在查找与引用的列中，但它不但可能进行查找与引用，而且可以多条件查找，这是其它函数不能与之比拟的。下面我谈一下关于该函数在作多条件引用时的使用过程中值得注意的关键两个问题：

1、公式变异；2、数组参数必须具有相同的维数。

一、公式变异：



EXCEL帮助中对SUMPRODUCT（）函数的解释是：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。



从以上解释中不难看出：该函数并未提及有查与引用功能，结果是积之和。它的参数的表达式是几组数组，然而我们在作多条件查找与引用中并非如此（这或许正是该函数的奇妙之处吧）。不用逗号“，”将各数组分开，反而用乘号“*”替之。

如：原表达式：SUMPRODUCT(array1,array2,array3, ...)

变异后表达式：SUMPRODUCT(array1*array2*array3* ...)

关于“*”的使用方法请参见坛中相关贴子，这里不再赘述。

二、数组参数必须具有相同的维数：如何理解具有相同的维数呢？我个人认为：若是一维的数组，就是数组间的单元格尺寸相同。如：A1：A10与C2：C11、D2：H2与F2：J2之间的维数是相同的。

若是二维的数组，就是维数间的矩形区域尺寸相同(即行与列所围成矩形区域尺寸分别相同,简单地说，行数相当于矩形的“宽或高”，列数相当于矩形的“长”分别相等；这里不能用面积相等，也就是长与宽互换后，虽面积不变，而各边并非分别一一对应相等，公式会出错)。如：都是6行3列（即长为6，即有6行，宽为3，即有3列）；但B2：D7与F4：K6之间就是6行3列与3列6行，看上去面积为18（请允许我姑且这样说），但长与宽就不是分别相等了，故会出错。

特别强调两点：

1、需要特别注意的是“必须相同”四字，也就是说既不能多也不能少，只能相同。

2、一组参数中，行与列的一维数组必须分别相同，即方向也尺寸均相同。但行的维数可以与列的维数不相同，不过在返回它们的二维数组区域时又必须相同，举例说明：

可以这样列表达式：

=SUMPRODUCT（（A1：A10)*（C2：C11））（均是10行1列）

=SUMPRODUCT（（D2：H2）*（F2：J2））（均是1行5列）

=SUMPRODUCT（（A1：A10)*（C2：C11）*（D2：H2）*（F2：J2））（只要行/列分别相同，行与列维数可不相同）

=SUMPRODUCT（（A2：A10)*（B1：E1）*（B2：E10））（区域必须是4列9行）

不可以这样列表达式：

=SUMPRODUCT（（A2：A10)*（B1：E1）*（B2:E9或E11））

三、实例说明：
实例1：统计：部门：甲板部，职务：大副，性别：男的基础薪金之和？

公式表达式：

B16=SUMPRODUCT((B2:B11="甲板部")*(C2:C11="大副")*(D2:D11="男")*(E1="基础薪金"))

    分析：

    这是个典型的多条件求值问题，题中共有4个条件，须同时满足。这里必须要求：B2:B11、 C2:C11、 D2:D11三个数组的维数相同，这里都是11行（个），否则出错。在录表达式时，不可以这样：

=SUMPRODUCT((B2:B11="甲板部")*(C2:C10="大副")*(D2:D9="男")*(E1="基础薪金"))

另外，根据乘法交换律的规则（请允许我这样说）：上述公式可以这样：

B16=SUMPRODUCT((E1="基础薪金") *(B2:B11="甲板部")*(D2:D11="男") *(C2:C11="大副"))

实例2：统计：部门：甲板部，职务：大副，性别：男等条件的基础薪金、岗位薪金、高温补贴、交通补贴的各项和？

E21=SUMPRODUCT(($B$2:$B$12=$B$21)*($C$2:$C$12=$C$21)*($D$2:$D$12=$D$21)*($E$1:$H$1=E$19)*($E$2:$H$12))

实例3：分别统计甲板部、轮机部及事务部各种条件之和。

E26=SUMPRODUCT(($D$2:$D$12=$C26)*($E$1:$H$1=$D26)*($C$2:$C$12=$B26)*($B$2:$B$12=E$25)*($E$2:$H$12))

不可以这样列表达式：

E26=SUMPRODUCT(($D$2:$D$12=$C26)*($E$1:$H$1=$D26)*($C$2:$C$12=$B26)*($B$2:$B$12=E$25)*($E$2:$H$11))

顺便提一下，这里谈到的该函数仅是函数公式中多条件统计公式之一，在工作中根据实际情况，因事而用各种公式达到理想的效果。

[ 本帖最后由 52UEXCEL 于 2009-12-18 14:13 编辑 ]

han3zhong

xhw8181

处女贴，发到这里啦

MicrosoftEH

厉害！叹为观止！！

52UEXCEL

《Excel数据透视表使用方法精要12点》
1、Excel数据透视表能根据时间列和用户自定时间间隔对数据进行分组统计，如按年、季度、月、日、一周等，即你的数据源表中只需有一个日期字段就足够按照（任意）时间周期进行分组了；。 If you look at the Source Data worksheet, you'll notice that there is no Quarters column. The Quarters field was created from the Order Date field by grouping the dates in the field, using the Group and Show Detail commands on the shortcut menu. Excel allows you to group dates in several ways including by year, month, or day. You can also use seven-day groups to group by week.
2、通常，透视表项目的排列顺序是按升序排列或取决于数据在源数据表中的存放顺序； Initially, the items in a field are either sorted in ascending order or displayed in the order received from the source database, depending on the type of source data you use. For example, in the reports on the Order Amounts and Orders by Country worksheets, the names of the salespersons are alphabetized.
3、对数据透视表项目进行排序后，即使你对其进行了布局调整或是刷新，排序顺序依然有效； Once you use this command to sort a field, the field retains the sort order even if you rearrange or update the report.
4、可以对一个字段先进行过滤而后再排序； you can display a field both filtered and sorted if you want.
5、内部行字段中的项目是可以重复出现的，而外部行字段项目则相反； The report repeats items in an inner row field as necessary for each outer row field item.
6、通过双击透视表中汇总数据单元格，可以在一个新表中得到该汇总数据的明细数据，对其可以进行格式化、排序或过滤等等常规编辑处理；决不会影响透视表 和源数据表本身； You can easily list the records from the source data that are summarized in a particular data cell, just by double-clicking the cell. Excel creates a new worksheet like this one with a copy of the data. You can format, sort, and filter this detail data without affecting the PivotTable report or the original source data.
7、以上第6点对源数据是外部数据库的情况尤其有用，因为这时不存在单独的直观的源数据表供你浏览查阅； This feature is available for most types of source data, and is particularly useful with source data taken from external databases, where you don't have a separate Excel source data worksheet to view.
8、透视表提供了多种自定义（计算）显示方式可以使用； Excel provides several types of custom calculations for data fields. To see what's available, double-click the Percent of Order Total field and look at the Show data as dropdown list.
9、如果源数据表中的数据字段存在空白或是其他非数值数据，透视表初始便以“计数”函数对其进行汇总（计算“计数项”）； When the data in a field includes blanks and other values that aren't numbers, Excel initially uses Count to summarize the data. You can easily change the fields to total the amounts instead of counting: double-click the field and click Sum under Summarize by.
10、透视表在进行TOP 10排序时会忽略被过滤掉的项目，因此在使用此功能时要特别注意； If you had previously used the dropdown list for the Customer field to hide some customers, when you set the Top 10 AutoShow options, Excel omits the hidden items from the calculations.
11、在一个透视表中一个（行）字段可以使用多个“分类汇总”函数；
12、在一个透视表数据区域中一个字段可以根据不同的“分类汇总”方式被多次拖动使用。

==========================================
动态数据透视表转化为固定数据表的超级方法
如果报表中的汇总数据可见，则可以使用函数 GETPIVOTDATA 从数据透视表报表中检索汇总数据
GETPIVOTDATA(pivot_table,name)
Pivot_table 对数据透视表报表中包含待检索数据的单元格的引用。Pivot_table 可以为报表中的一个单元格或单元格区域，也可以是包含数据透视表报表的区域的名称或数据透视表报表上方单元格中所存储的标志
Name 为括在引号中的文本串，用以描述包含待检索数值的数据透视表报表中的特定单元格。例如，如果数据透视表报表包含一行标志为“销售人员”的字段，且需要检索名称为“Suyama”的销售商的总计值，则名称应为“Suyama”；如果数据透视表报表中的列字段包含产品，且需要得到名为“Suyama”的销售商的软饮料销售的总计值，则名称应为“Suyama 饮料
在函数 GETPIVOTDATA 的计算中可以包含计算字段、计算项及自定义计算方法
如果 pivot_table 为包含两个或更多个数据透视表报表的区域，则将从区域中最新创建的报表中检索数据
如果名称描述的是单个单元格，则返回此单元格的数值，无论是文本串、数字、错误值或其他的值
如果 pivot_table 并不代表找到了数据透视表报表的区域，则函数 GETPIVOTDATA 会返回错误值 #VALUE！。
如果名称的语法没有描述一个可见的字段，或是省略了名称，或是名称包含尚未显示的页字段，函数 GETPIVOTDATA 均返回错误值 #REF！。
假设下面的数据透视表报表存储在一个工作表中，且名称 PT1 为对 A2:E12 的引用，则包含报表的区域为
GETPIVOTDATA(PT1,"销售总额") 返回“销售总额”字段的总计值 $49,325
GETPIVOTDATA(PT1,"三月") 返回“三月”的总计值 $30,337
下面的示例引用的仍是同一个数据透视表报表，但使用数据透视表报表中的一个单元格作为参数 pivot_table：
GETPIVOTDATA(A4,"三月 Buchanan 农产品") 返回 $10,201
GETPIVOTDATA(A4,"March South") 返回错误值 #REF！，因为南部地区的数据是不可见的
GETPIVOTDATA(A4,"Davolio Beverages") 返回错误值 #REF！，因为没有 Davolio Beverages 的软饮料销售的总计值。


1sPbjGtb.rar (224.37 KB, 下载次数: 111)

2009-12-18 15:08 上传

点击文件名下载附件

===================================

[ 本帖最后由 52UEXCEL 于 2009-12-18 15:11 编辑 ]

52UEXCEL

无论是15位还是18位
1、C2=TEXT(REPLACE(MID(A2,7,6+(LEN(A2)=18)*2),1,,IF(LEN(MID(A2,7,6+(LEN(A2)=18)*2))=6,19,)),"0000-00-00")
或
2、C2=TEXT(MID(REPLACE(A2,7,,IF(LEN(A2)=15,19,)),7,8),"0000-00-00")

3、IF(LEN(A2)=15,TEXT(MID(A2,7,6),"0000-00-00"),TEXT(MID(A2,7,8),"0000-00-00"))
4、IF(LEN(A2)=15,"19"&MID(A2,7,6),MID(A2,7,8))
5、TEXT(TEXT(MID(A2,7,6+(LEN(A2)=18)*2),"0-00-00"),"emmdd")

52UEXCEL

1、返加包含值12的单元格数量

=COUNTIF（DATA,12）

2、返回包含负值的单元格数量

=COUNTIF（DATA，"<0"）

3、返回不等于0的单元格数量

=COUNTIF（DATA，"<>0"）

4、返回大于5的单元格数量

=COUNTIF（DATA，">5"）

5、返回等于单元格A1中内容的单元格数量

=COUNTIF（DATA，A1）

6、返回大于单元格A1中内容的单元格数量

=COUNTIF（DATA，“>”&A1）

7、返回包含文本内容的单元格数量

=COUNTIF（DATA，“*”）

8、返回包含三个字符内容的单元格数量

=COUNITF（DATA，“???”）

9、返回包含单词"GOOD"(不分大小写)内容的单元格数量

=COUNTIF（DATA，“GOOD”）

10、返回在文本中任何位置包含单词"GOOD"字符内容的单元格数量

=COUNTIF（DATA，“*GOOD*”）

11、返回包含以单词"AB"(不分大小写)开头内容的单元格数量

=COUNTIF（DATA，“AB*”）

12、返回包含当前日期的单元格数量

=COUNTIF（DATA，TODAY()）

13、返回大于平均值的单元格数量

=COUNTIF（DATA，">"&AVERAGE(DATA))

14、返回平均值上面超过三个标准误差的值的单元格数量

=COUNTIF（DATA，“>"&AVERAGE(DATA)+STDEV(DATA)*3)

15、返回包含值为或-3的单元格数量

=COUNTIF（DATA，3）+COUNIF（DATA，-3）

16、返回包含值;逻辑值为TRUE的单元格数量

=COUNTIF（DATA，TRUE）

52UEXCEL

函数嵌套实例30P

[ 本帖最后由 52UEXCEL 于 2009-12-27 23:33 编辑 ]

yxnbx

建议给楼主加分，进级

pangcheng

不错 ，楼主是个有心人