|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 wuxiang_123 于 2012-11-17 11:48 编辑
今天在回答一个会员的问题,问题如下:
需要将下图A列数值区分正负值,并列出返回如C:D列效果。
使用VBA、和函数相信对于各位VBA和函数高手都很简单(偶这两方面都是菜鸟,不会),如果结果不需要返回重复值,那么,使用SQL也很简单,可以使用我的转置贴语句实现:
- TRANSFORM MIN(数值)
- SELECT NULL FROM
- (SELECT A.正负,A.数值,SUM(1) AS 排名 FROM
- (SELECT DISTINCT SWITCH(数值>0,"正数",数值<0,"负数") AS 正负,数值 FROM [SHEET1$] WHERE 数值)A,
- (SELECT DISTINCT SWITCH(数值>0,"正数",数值<0,"负数") AS 正负,数值 FROM [SHEET1$] WHERE 数值)B
- WHERE A.正负=B.正负 AND A.数值>=B.数值
- GROUP BY A.正负,A.数值)
- GROUP BY 排名
- PIVOT 正负
复制代码
但结果要求返回重复值,而SQL却没有行的概念,无法区分相同项,此时,我们需要使用辅助列来完成:
辅助的作用在于为我们区分相同的项,也可以为我们获取每个记录在数据源中的位置,此时,我们就可以利用辅助列使用以下语句:
- TRANSFORM MIN(数值)
- SELECT NULL FROM
- (SELECT A.正负,A.数值,A.辅助,SUM(1) AS 位置 FROM
- (SELECT 辅助,SWITCH(数值>0,"正数",数值<0,"负数") AS 正负,数值 FROM [SHEET1$] WHERE 数值)A,(SELECT 辅助,SWITCH(数值>0,"正数",数值<0,"负数") AS 正负,数值 FROM [SHEET1$] WHERE 数值)B
- WHERE A.正负=B.正负 AND A.辅助<=B.辅助
- GROUP BY A.正负,A.数值,A.辅助)
- GROUP BY 位置
- PIVOT 正负
复制代码
也许你会疑问:那新增数据不就是需要重新在辅助列输入公式?此时,我们完全可以借用列表功能完成公式的自动填充,然后我们隐藏数据源中的辅助列就可以了。
那有没有不在数据源插入辅助列的方法呢?这时候我们需要使用到【连接属性】-【属性】中的【包含行号】。
此时将出现一个“_RowNum” 新字段,作用等价于我们上文使用的辅助列,在返回的结果表中插入了一个行号,然后我们再以返回结果作为数据源进行交叉表查询。
- TRANSFORM MIN(数值)
- SELECT NULL FROM
- (SELECT A.正负,A.数值,A.[_RowNum],SUM(1) AS 位置 FROM
- (SELECT [_RowNum],SWITCH(数值>0,"正数",数值<0,"负数") AS 正负,数值 FROM [SHEET4$] WHERE 数值)A,(SELECT [_RowNum],SWITCH(数值>0,"正数",数值<0,"负数") AS 正负,数值 FROM [SHEET4$] WHERE 数值)B
- WHERE A.正负=B.正负 AND A.[_RowNum]<=B.[_RowNum]
- GROUP BY A.正负,A.数值,A.[_RowNum])
- GROUP BY 位置
- PIVOT 正负
复制代码 使用此方法,纯属取巧,不能适应数据源的动态更新,不可取。
该贴已经同步到 wuxiang_123的微博 |
评分
-
2
查看全部评分
-
|