作者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/m.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