作者kisha024 (4545454554)
看板Office
标题[算表] excel中类似SQL distinct的函数
时间Tue Mar 27 11:12:36 2018
软体:excel
版本:2010
各位好
譬如这个档案
https://www.sendspace.com/file/3nsqel
栏位A有15个资料 但其实只有6种资料(重复的要扣掉) 也就是A,B,C,D,E,F(栏位D)
请问excel中 有类似SQL distinct的函数 可以得到这样的结果吗?
谢谢
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 60.251.130.136
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Office/M.1522120360.A.A94.html
2F:→ soyoso: 功能方面有,移除重覆;有表头的话,进阶筛选,不选重覆的 03/27 11:20
3F:→ soyoso: 记录 03/27 11:20
4F:→ soyoso: 或枢杻分析表,拖曳到列取得 03/27 11:21
谢谢 F1的部分没问题 但是G2好像没办法得到A,B,C,D,E,F
G2=IFERROR(INDEX(A:A,SMALL(IF(COUNTIF(G$1:G1,A$1:A$15)=0,ROW($1:$15)),1)),"")
谢谢 我步骤错了
我没有先把G2变成阵列公式 而是先选取G2:G16 再按Ctrl+Shift+Enter
这样在F栏没问题 但是在G栏有问题
因为G$1:G1就固定住了 不会变依序变成G$1:G2,G$1:G3,...
-----------------------------------------------------------------
不好意思 我另有个问题
F1,G2先Ctrl+Shift+Enter 变成阵列公式 再往下拉 产生A,B,C,D,E,F
此时点选F2 点编辑列 按enter F2会不见
此时点选G3 点编辑列 按enter G3:G8的值会改变
如果是先选取F1:F15 再按Ctrl+Shift+Enter 变成阵列公式後
此时单独去改F2 就会跳出警告:您不能只改变一个阵列中的一部分
所以後者是不是有避免误改的好处?
谢谢
6F:→ soyoso: 如原po回文所述 04/01 15:32
谢谢 我仍有其他疑问
我原本只有15个资料 但资料会增删
如果现在多了1个资料 且不是A,B,C,D,E,F 假设是X 如下
https://www.sendspace.com/file/x2vk4i
那原本的F1 只要是A$15 都要改成A$16 如下
F1=IFERROR(INDEX(A:A,SMALL(IF(MATCH(A$1:A$16,A$1:A$16,0)=ROW($1:$16),ROW($1:$16)),ROW())),"")
虽然可以work 但有更好的写法可以在增加资料时 不用修改也能work吗?
7F:→ soyoso: 动态范围可indirect或offset配合counta来调整 04/01 16:32
8F:→ soyoso: 也可写於自订名称内来引用 04/01 16:33
不好意思 拖了这麽久
我最後只用了indirect 如下 但counta要怎麽加到这例子中 我想不出来
https://www.sendspace.com/file/wp662p
但我这样改 只是把每次范围变动 需要改公式时 变成去改H2和H3 并不是不用改
想请问 有办法完全都不用改就可以work吗? 如果可以 可以给我一个范例吗?
谢谢
谢谢 我对 OFFSET(A$1,,,COUNTA(A:A)) 有疑问
https://bit.ly/2Jua9Ba
OFFSET(reference, rows, cols, [height], [width])
官网提到 Rows和Cols 都是必要,而上方的OFFSET里面Rows和Cols都是空的
我第一眼看到时 直觉是错的 但跑起来却没有错 效果跟0一样
请问 是所有函数的参数标示成「必要」 都可以空着吗?
还是OFFSET比较特别 所以可以这样用?
10F:→ soyoso: 是必要的,但省略时它有预设;就要看省略时预设是否是要的 04/08 00:25
11F:→ soyoso: 引数值,或是回传值是否不为错误 04/08 00:25
谢谢 那如果资料间不连续 有空白 如下 原本的公式就无法work了
https://www.sendspace.com/file/b3ycmz
请问 这样公式要怎麽改 才有办法找出所有distinct的值?
12F:→ soyoso: 要distinct显示空白,或是distinct和where不为空白 04/08 10:13
13F:→ soyoso: 要找最後有值的列号可以lookup(1,0/(a栏范围不为空白),row 04/08 10:22
14F:→ soyoso: (范围列号)) 04/08 10:23
15F:→ soyoso: match查找空白储存格会回传错误值,要将范围後以""连接& 04/08 10:24
16F:→ soyoso: 以上可找distinct(含空字串储存格[原空白储存格]) 04/08 10:26
17F:→ soyoso: 对应上空白储存格会回传0值,可於index(...)外连接&"" 04/08 10:28
18F:→ soyoso: 如where不为空白的话,则在if内再加上范围内不为非空字串 04/08 10:30
19F:→ soyoso: 的判断 04/08 10:30
20F:→ soyoso: 如公式太长,可於其他储存格取得最後有值的列号後,offset 04/08 10:31
21F:→ soyoso: 再指定height引数为该储存格;或将重覆引用的范围,以自订 04/08 10:32
22F:→ soyoso: 名称的方式引用 04/08 10:32
不好意思 你可以给我一个例子吗? 光有描述 我还是试不出来
23F:→ soyoso: match(offset()&"",offset()&"",0)的方式 04/08 14:30
24F:→ soyoso: 上述为显示空白的部分 04/08 14:32
25F:→ soyoso: offset()<>"" and match()就为where不为空白 04/08 14:32
谢谢 你可以给我最後有值的列号的例子吗? 底下这个我看不太懂
lookup(1,0/(a栏范围不为空白),row(范围列号))
26F:→ soyoso: a栏范围a1:a1000不为空白<>"",范围列号1:1000 04/08 16:06
谢谢 完成了 为避免忘记 我把结果记录在底下的工作表1
https://www.sendspace.com/file/2o3elc
我还有另一个问题 在工作表2
随便指定一块连续的空间(譬如B2:E5) 列出里面distinct的值(不含空白)
请问公式该怎麽写?
27F:→ soyoso: match的话,将范围转为1栏多列(offset、index或indirect) 04/08 17:44
28F:→ soyoso: 再抓取。 04/08 17:45
29F:→ soyoso: 或以countif配合indirect(r1c1)来抓取回文范围 04/08 17:46
谢谢 但光是将范围转为1栏多列 我就想不出要怎麽做了 可以给我个例子吗?
谢谢 我完成了 我在offset的第三个参数也加上mod
并加了一个ROW()<=3*3的条件 以避免E栏中值重复出现
范围内如果有储存格为空白 offset後会变成0 所以我加了一个 <>"" 的条件 如下
https://www.sendspace.com/file/tuvwtu
但我还有一个问题 在上一个档案 如下
https://www.sendspace.com/file/2o3elc
我把栏位A的资料删到只剩一个(剩A1) 我原本预期F1会是A 结果是空白 如下
https://i.imgur.com/iSwxLPw.png
但如果栏位A的资料只剩A2 F1就会如预期是B 如下
https://i.imgur.com/hRsXDgH.png
我发现是 MATCH(OFFSET(A$1,,,H10)&"",OFFSET(A$1,,,H10)&"",0) 的关系
但不懂为什麽会这样?
31F:→ soyoso: 剩a1的方面储存格h10加1 04/12 13:25
32F:→ soyoso: 公式,判断是否不为空字串上,是不用连接&符号的,写法回 04/12 13:27
33F:→ soyoso: 文上原po就有提到加了一个条件的写法 04/12 13:28
34F:→ soyoso: 调整後剩A2方面应可显示於储存格F1内 04/12 13:29
谢谢 test6.xlsx G栏的&""拿掉後 就正常了
但我还是不懂 MATCH(OFFSET(A$1,,,H10)&"",OFFSET(A$1,,,H10)&"",0)
https://www.sendspace.com/file/jdg2k2
当A1是A,H10是1时
OFFSET(A$1,,,H10) 和 OFFSET(A$1,,,H10)&"" 都是 A
MATCH( OFFSET(A1,,,H10),OFFSET(A1,,,H10),0) 是 1
而 MATCH( OFFSET(A1,,,H10)&"",OFFSET(A1,,,H10)&"",0) 却是 #VALUE!
这是为什麽呢?
35F:→ soyoso: 单一储存格&""会为文字类型,复数储存格&""会为array 04/13 09:47
这太细了 你不说我根本不会注意到
至此 我没问题了 感谢
※ 编辑: kisha024 (36.239.84.59), 04/14/2018 00:24:27