查看: 2136|回复: 7
多列数据中连续三列或以上有重复项用不同颜色标注出来，求助！！！！
阅读权限20
在线时间 小时
excel 将12列人名中，连续3列出现的人名标注出来 我有一个表格& &里面有12列内容
每一列都有200左右个人的名字
这12列人名相同的人名
我想问一下，通过什么，能将连续出现在三列及以上的人名标注出来？
(69.08 KB, 下载次数: 3)
18:27 上传
阅读权限70
在线时间 小时
[code=sql]SELECT name,COUNT(1) AS counts
&&SELECT [201001] AS name FROM [SHEET1$] UNION ALL
&&SELECT [201002] AS name FROM [SHEET1$] UNION ALL
&&SELECT [201003] AS name FROM [SHEET1$] UNION ALL
&&SELECT [201012] AS name FROM [SHEET1$]
GROUP BY name
HAVING COUNT(1) &= 3&&[/code]
阅读权限70
在线时间 小时
& & & & & & & &
看错了，要连续的，上面的是出现 &=3 次的，非连续的
阅读权限20
在线时间 小时
amazeyeli 发表于
看错了，要连续的，上面的是出现 &=3 次的，非连续的
嗯 一定要连续的 三列
阅读权限70
在线时间 小时
由于Excl只能执行简单的SQL语句，下面的应该可以，循环的怕执行不了，还是用VBA好一点，不过不会VBA
[code=sql]SELECT *
& & SELECT [201001] AS name FROM [SHEET1$] UNION
& & SELECT [201002] AS name FROM [SHEET1$] UNION
& & SELECT [201012] AS name FROM [SHEET1$] UNION
& & LEFT JOIN
& && &SELECT [201001] AS name FROM [SHEET1$]
& & ) AS A1
& & ON A1.name = A1.name
& & LEFT JOIN
& && &SELECT [201002] AS name FROM [SHEET1$]
& & ) AS A2
& & ON A1.name = A2.name
& & LEFT JOIN
& & ON ...
& & LEFT JOIN
& && &SELECT [201012] AS name FROM [SHEET1$]
& & ) AS A12
& & ON A1.name = A12.name
WHERE A1.name && NULL AND A2.name && NULL AND A3.name && NULL
& &OR A2.name && NULL AND A3.name && NULL AND A4.name && NULL
& &OR A10.name && NULL AND A11.name && NULL AND A12.name && NULL
& &[/code]
阅读权限70
在线时间 小时
[code=sql]USE tempdb
IF OBJECT_ID('TEST') IS NOT NULL
&&DROP TABLE TEST;& && &--&如果表TEST不为空,删除表
CREATE TABLE TEST& && & --&创建测试表,字段及类型如下
&&name1&&char(1),
&&name2&&char(1),
&&name3&&char(1),
&&name4&&char(1),
&&name5&&char(1),
INSERT INTO TEST VALUES --&向表中插入数据
('a','b','c','i','e'),
('b','d','f','e','b'),
('c','f','e','f','a'),
('d','g','d','k','c'),
('e','a','h','l','d');
SELECT * FROM TEST& && &--&表的结构如下
name1 name2 name3 name4 name5
----- ----- ----- ----- -----
a& &&&b& &&&c& &&&i& &&&e
b& &&&d& &&&f& &&&e& &&&b
c& &&&f& &&&e& &&&f& &&&a
d& &&&g& &&&d& &&&k& &&&c
e& &&&a& &&&h& &&&l& &&&d
(5 行受影响)
-------------------开始查询------------------
SELECT name
&&SELECT name1 AS name FROM TEST UNION
&&SELECT name2 AS name FROM TEST UNION
&&SELECT name3 AS name FROM TEST UNION
&&SELECT name4 AS name FROM TEST UNION
&&SELECT name5 AS name FROM TEST
&&SELECT name1 FROM TEST
ON A.name = A1.name1& &
&&SELECT name2 FROM TEST
ON A.name = A2.name2& &
&&SELECT name3 FROM TEST
ON A.name = A3.name3& &
&&SELECT name4 FROM TEST
ON A.name = A4.name4& &
&&SELECT name5 FROM TEST
ON A.name = A5.name5
WHERE name1 IS NOT NULL AND name2 IS NOT NULL AND name3 IS NOT NULL
& &OR name2 IS NOT NULL AND name3 IS NOT NULL AND name4 IS NOT NULL
& &OR name3 IS NOT NULL AND name4 IS NOT NULL AND name5 IS NOT NULL& &
-------------------运行结果------------------
(3 行受影响)
阅读权限70
在线时间 小时
以下是思路：
[code=sql]&&SELECT name1 AS name FROM TEST UNION
&&SELECT name2 AS name FROM TEST UNION
&&SELECT name3 AS name FROM TEST UNION
&&SELECT name4 AS name FROM TEST UNION
&&SELECT name5 AS name FROM TEST
-------------------运行结果------------------
(11 行受影响)
阅读权限70
在线时间 小时
[code=sql]SELECT *
&&SELECT name1 AS name FROM TEST UNION
&&SELECT name2 AS name FROM TEST UNION
&&SELECT name3 AS name FROM TEST UNION
&&SELECT name4 AS name FROM TEST UNION
&&SELECT name5 AS name FROM TEST
&&SELECT name1 FROM TEST
ON A.name = A1.name1& &
&&SELECT name2 FROM TEST
ON A.name = A2.name2& &
&&SELECT name3 FROM TEST
ON A.name = A3.name3& &
&&SELECT name4 FROM TEST
ON A.name = A4.name4& &
&&SELECT name5 FROM TEST
ON A.name = A5.name5
-------------------运行结果------------------
name name1 name2 name3 name4 name5
---- ----- ----- ----- ----- -----
a& & a& &&&a& &&&NULL&&NULL&&a
b& & b& &&&b& &&&NULL&&NULL&&b
c& & c& &&&NULL&&c& &&&NULL&&c
d& & d& &&&d& &&&d& &&&NULL&&d
e& & e& &&&NULL&&e& &&&e& &&&e
f& & NULL&&f& &&&f& &&&f& &&&NULL
g& & NULL&&g& &&&NULL&&NULL&&NULL
h& & NULL&&NULL&&h& &&&NULL&&NULL
i& & NULL&&NULL&&NULL&&i& &&&NULL
k& & NULL&&NULL&&NULL&&k& &&&NULL
l& & NULL&&NULL&&NULL&&l& &&&NULL
(11 行受影响)
最新热点 /1
ExcelHome每周都有线上直播公开课，
国内一流讲师真身分享，高手贴身答疑，
赶不上直播还能看录像，
关键居然是免费的！
厚木哥们都已经这么努力了，
你还好意思说学不好Office。
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师}