[第47期]制作数据透视表并为页字段顺序编号（已进行总结!）

jssy

1.答题前请先阅读最新规则:正式竞赛区运行规则说明
2.请勿在跟贴中直接发答案(跟帖中如附答案一律不得分).
3.答案附件请以标准文件名格式发送至我的邮箱:Jssy_nj@sina.com



题目说明：                                                
1.根据给定的数据表,制作出页字段带有顺序号的数据透视表。                                                
2.完成后的数据透视表为动态表，数据源变动后或数据透视表刷新后，数据透视表按要求随之变动。                                                
                                                
答题要求：                                                
1.不能改动数据源表中的数据，不能使用辅助列、不能另外使用辅助表。                                                
2.如果地区个数发生变化,完成后的数据透视表页字段顺序号能随之变化，并保持升序排列（仅保持顺序号升序排列即可，但页字段数据项可能在10个以上）。                                                
2.只能在EXCEL中完成。                                                
3.使用数据透视表基本操作完成，也可使用其他基本操作方法。                                                
4.不可使用任何EXCEL函数与公式,不使用VBA。                                                
5.写出操作步骤或GIF动画(不要提供EXE文件)
6.将答案文件，尤其是EXCEL文件发给我，并在文件名中注明ID号，在答案邮件中注明ID号，否则无法评分！                                              
                                                
评分：                                               
1.制作出带页字段序号的数据透视表,得2分；数据透视表刷新后添加的页字段序号保持升序排序，再得1分，总分3分。                                                
2.精彩答案另加分.




至2009年5月20日已收到以下朋友正式提交的答案：shlzml、monvzhilei 、toby08、小蚁雄兵、Scarlett_88 、minjiwei 、fengbin7506  、linpansheng 、BIN_YANG168
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
答案,评分及总结:

1. select (select count(*) from (select distinct 地区 FROM [sheet1$a1:f25]) where 地区<=a.地区) as Ni,iif(Ni<10," "&Ni,Ni)&"-"&地区 as 地区1,* from [sheet1$a1:f25] a

复制代码

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
总结:

       现在终于到了可以进行小结的时候了，这期竞赛题创意来源于半年前所看的一本老外的EXCEL著作，但该书只有一个结果的图但并没有提供有价值的实现方法，而从实际工作角度，页字加上编号更便于查阅，哈哈现在终于有一个结果了。
       详细的总结内容请参见附件：
      后记：
       本期竞赛题吸引了许多爱透视表和SQL的朋友参与，给出了许多精彩的答案，在大家智慧碰撞下，产生许多出人意料的精彩结果，发现了SQL和透视表结合过程中存在的一些问题，在这里感谢所有的参与者，特别是：
        1、Scarlett_88 不但给出的正确答案，而且进行详细的SQL语句分析，同时对中文排序进行有益的探讨与尝试，参见：
         http://club.excelhome.net/thread-439987-1-1.html
        2、lbzcc 在本次竞赛的基础上进行应用扩展，制作了按销售额对地区进行排序并添加编号，参见：
        http://club.excelhome.net/thread-439472-1-1.html
        以上是个人对想法和理解，不当之处请大家批评指正，谢谢大家!

[ 本帖最后由 jssy 于 2009-5-29 19:51 编辑 ]

yvhgydn

请问:1\数据源在变动时,在地区字段会不会有新的地区产生
     2\(数据透视表刷新后保持升序排序)是否单指页字段


1.如果数据源中出现新的地区,地区字段应该同时产生新的字段项.
2.指的是批页字段

[ 本帖最后由 jssy 于 2009-4-30 15:31 编辑 ]

shlzml

答案已发送版主信箱，请查阅，谢谢

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
外部数据源创建数据透视表+新建数据库查询＋Microsoft Query+SQL语句

答题文件：

1. SELECT
   
2.     (SELECT   COUNT(*)   FROM   (SELECT `Sheet1$`.地区 FROM `D:\竞赛基础题\［47期基础题］为页字段自动编号－ID`.`Sheet1$`    group by  `Sheet1$`.地区)
   
3. where   `Sheet1$`.地区<=t.地区) &'-'& t.地区 as 地区 , t.月份, t.品名, t.数量, t.零售价, t.销售额
   
4. FROM `D:\竞赛基础题\［47期基础题］为页字段自动编号－ID`.`Sheet1$` t
   
5. WHERE t.地区 is not  null;

复制代码

实现添加序号要求，得2分，但页字段在10个以上时，排序不正确，不得分，共计得2分。

[ 本帖最后由 jssy 于 2009-5-26 14:44 编辑 ]

toby08

答案重新发送，请注意查收。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
导入外部数据+OLE DB查询＋SQL语句+创建数据透视表



答题文件：

1. SELECT seq&'-'&地区 ,* FROM (SELECT (SELECT COUNT(地区) FROM (SELECT DISTINCT 地区 FROM [A$] AS tbl1) WHERE tbl1.地区<=tbl2.地区) as seq,* FROM [A$] AS tbl2) ORDER BY seq

复制代码

实现页字段添加序号功能，得2分，但10个以上项目时序号排序未能在所有情况始终保持正确，不得分，总分2分。

[ 本帖最后由 jssy 于 2009-5-26 15:35 编辑 ]

monvzhilei

好的题目。让我想了好几天了。

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2007透视表
答题文件：

1. SELECT (SELECT Count(*) FROM (SELECT distinct [地区] FROM [sheet2$]) AS B WHERE B.[地区]<=A.[地区]) & "-" & A.[地区] AS 地区,*
   
2. FROM [sheet2$] AS A order by 地区 desc

复制代码

实现页字段添加序号功能，得2分，但10个以上项目时序号排序不正确，不得分，总分2分。　

[ 本帖最后由 jssy 于 2009-5-26 15:18 编辑 ]

小蚁雄兵

答案已发至版主邮箱，请审阅，谢谢！----小蚁雄兵

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
导入外部数据+OLE DB查询＋SQL语句+创建数据透视表

答题文件：

1. SELECT S1.记录号&"-"&S1.地区 AS 地区, S2.月份,S2.品名,S2.数量,S2.零售价,S2.销售额 FROM  (SELECT T1.地区,COUNT(*) AS 记录号 FROM (SELECT DISTINCT  地区 FROM [Sheet1$]) T1,(SELECT DISTINCT  地区 FROM [Sheet1$]) T2 WHERE T1.地区>=T2.地区 GROUP BY T1.地区 )  S1,[SHEET1$] S2 WHERE S1.地区=S2.地区 ORDER BY S1.记录号

复制代码

实现页字段添加序号功能，得2分，但10个以上项目时序号排序未能在所有情况始终保持正确，不得分，总分2分。

[ 本帖最后由 jssy 于 2009-5-26 15:51 编辑 ]

BIN_YANG168

还是将答案发送邮箱较好，要不竞赛区挂名版主就不能答题了，呵呵。

答题文件：

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
导入外部数据+OLE DB查询＋SQL语句+创建数据透视表

1. SELECT [C.ID]&"-"&C.地区 AS 地区, D.* FROM  (SELECT a.地区,COUNT(a.地区) AS ID  FROM (SELECT `Sheet1$`.地区, Count(`Sheet1$`.地区) AS '计数  地区' FROM `Sheet1$` `Sheet1$` GROUP BY `Sheet1$`.地区 ORDER BY Count(`Sheet1$`.地区) ) as a,( SELECT `Sheet1$`.地区, Count(`Sheet1$`.地区) AS '计数  地区' FROM `Sheet1$` `Sheet1$` GROUP BY `Sheet1$`.地区 ORDER BY Count(`Sheet1$`.地区) ) as b WHERE a.地区>=b.地区 GROUP BY a.地区  )  C,[SHEET1$] D WHERE C.地区=D.地区 ORDER BY C.ID

复制代码

SQL语句可以简化，实现页字段添加序号功能，得2分，但10个以上项目时序号排序不正确，不得分，总分2分。

[ 本帖最后由 jssy 于 2009-5-26 16:00 编辑 ]

minjiwei

占位贴！谢谢！

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
外部数据源创建数据透视表+新建数据库查询＋Microsoft Query+SQL语句

答题文件：

1. select (select count( [地区]) from (SELECT DISTINCT 地区 FROM[AUTOID$]))-(select count([地区]) from (SELECT DISTINCT 地区 FROM[AUTOID$]) where 地区>a.地区) &' - '&地区 as AA, 月份,品名,数量,销售额  from [AUTOID$] a order by 地区

复制代码

实现页字段添加序号功能，得2分，但10个以上项目时序号排序不正确，不得分，总分2分。

[ 本帖最后由 jssy 于 2009-5-26 16:30 编辑 ]

Scarlett_88

我的电脑SQL窗口中不识别中文，我只好将中文全用英文或拼音代替了。
今天组合了4种SQL代码。已发邮件给你

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
导入外部数据+OLE DB查询＋SQL语句+创建数据透视表

答题文件：

1. select  b.地区2, 月份, 品名, 数量, 零售价, 销售额
   
2. FROM [Sheet1$] ,
   
3. (select  right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  地区 as 地区2,地区 from
   
4. (Select   (Select   SUM(1)   from  
   
5. (select DISTINCT 地区
   
6. FROM [Sheet1$]) where   地区   <=   a.地区) as ID , (Select   SUM(1)   from  
   
7. (select DISTINCT 地区
   
8. FROM [Sheet1$])) as NUM,地区
   
9. from  
   
10. (select DISTINCT 地区 FROM [Sheet1$])  a)) b
    
11. where `Sheet1$`.地区=b.地区

复制代码

实现页字段添加序号功能，得2分，实现10个以上项目序号正确排序，得1分，总分3分。

[ 本帖最后由 jssy 于 2009-5-26 16:38 编辑 ]

fengbin7506

这么复杂的语句，看不懂。先学一些简单的吧。