PTT數位生活區 / Office

[算表] excel中類似SQL distinct的函數

看板Office作者 (4545454554)時間8年前 (2018/03/27 11:12), 8年前編輯推噓0(0推 0噓 35→)
留言35則, 1人參與, 8年前最新討論串1/1
軟體: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://www.ptt.cc/bbs/Office/M.1522120360.A.A94.html
→
03/27 11:18, 8年前 , 1F
類似這樣
03/27 11:18, 1F
→
03/27 11:20, 8年前 , 2F
功能方面有，移除重覆；有表頭的話，進階篩選，不選重覆的
03/27 11:20, 2F
→
03/27 11:20, 8年前 , 3F
記錄
03/27 11:20, 3F
→
03/27 11:21, 8年前 , 4F
或樞杻分析表，拖曳到列取得
03/27 11:21, 4F
謝謝 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)),"")
→
03/31 21:17, 8年前 , 5F
測試，是可以取得 https://youtu.be/0Y0drRqy04Y
03/31 21:17, 5F
謝謝 我步驟錯了 我沒有先把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 就會跳出警告:您不能只改變一個陣列中的一部分 所以後者是不是有避免誤改的好處? 謝謝
→
04/01 15:32, 8年前 , 6F
如原po回文所述
04/01 15:32, 6F
謝謝 我仍有其他疑問 我原本只有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嗎?
→
04/01 16:32, 8年前 , 7F
動態範圍可indirect或offset配合counta來調整
04/01 16:32, 7F
→
04/01 16:33, 8年前 , 8F
也可寫於自訂名稱內來引用
04/01 16:33, 8F
不好意思 拖了這麼久 我最後只用了indirect 如下 但counta要怎麼加到這例子中 我想不出來 https://www.sendspace.com/file/wp662p 但我這樣改 只是把每次範圍變動 需要改公式時 變成去改H2和H3 並不是不用改 想請問 有辦法完全都不用改就可以work嗎? 如果可以 可以給我一個範例嗎? 謝謝
→
04/07 20:32, 8年前 , 9F
類似這樣
04/07 20:32, 9F
謝謝 我對 OFFSET(A$1,,,COUNTA(A:A)) 有疑問 https://bit.ly/2Jua9Ba OFFSET(reference, rows, cols, [height], [width]) 官網提到 Rows和Cols 都是必要,而上方的OFFSET裡面Rows和Cols都是空的 我第一眼看到時 直覺是錯的 但跑起來卻沒有錯 效果跟0一樣 請問 是所有函數的參數標示成「必要」 都可以空著嗎? 還是OFFSET比較特別 所以可以這樣用?
→
04/08 00:25, 8年前 , 10F
是必要的，但省略時它有預設；就要看省略時預設是否是要的
04/08 00:25, 10F
→
04/08 00:25, 8年前 , 11F
引數值，或是回傳值是否不為錯誤
04/08 00:25, 11F
謝謝 那如果資料間不連續 有空白 如下 原本的公式就無法work了 https://www.sendspace.com/file/b3ycmz 請問 這樣公式要怎麼改 才有辦法找出所有distinct的值?
→
04/08 10:13, 8年前 , 12F
要distinct顯示空白，或是distinct和where不為空白
04/08 10:13, 12F
→
04/08 10:22, 8年前 , 13F
要找最後有值的列號可以lookup(1,0/(a欄範圍不為空白),row
04/08 10:22, 13F
→
04/08 10:23, 8年前 , 14F
(範圍列號))
04/08 10:23, 14F
→
04/08 10:24, 8年前 , 15F
match查找空白儲存格會回傳錯誤值，要將範圍後以""連接&
04/08 10:24, 15F
→
04/08 10:26, 8年前 , 16F
以上可找distinct(含空字串儲存格[原空白儲存格])
04/08 10:26, 16F
→
04/08 10:28, 8年前 , 17F
對應上空白儲存格會回傳0值，可於index(...)外連接&""
04/08 10:28, 17F
→
04/08 10:30, 8年前 , 18F
如where不為空白的話，則在if內再加上範圍內不為非空字串
04/08 10:30, 18F
→
04/08 10:30, 8年前 , 19F
的判斷
04/08 10:30, 19F
→
04/08 10:31, 8年前 , 20F
如公式太長，可於其他儲存格取得最後有值的列號後，offset
04/08 10:31, 20F
→
04/08 10:32, 8年前 , 21F
再指定height引數為該儲存格；或將重覆引用的範圍，以自訂
04/08 10:32, 21F
→
04/08 10:32, 8年前 , 22F
名稱的方式引用
04/08 10:32, 22F
不好意思 你可以給我一個例子嗎? 光有描述 我還是試不出來
→
04/08 14:30, 8年前 , 23F
match(offset()&"",offset()&"",0)的方式
04/08 14:30, 23F
→
04/08 14:32, 8年前 , 24F
上述為顯示空白的部分
04/08 14:32, 24F
→
04/08 14:32, 8年前 , 25F
offset()<>"" and match()就為where不為空白
04/08 14:32, 25F
謝謝 你可以給我最後有值的列號的例子嗎? 底下這個我看不太懂 lookup(1,0/(a欄範圍不為空白),row(範圍列號))
→
04/08 16:06, 8年前 , 26F
a欄範圍a1:a1000不為空白<>""，範圍列號1:1000
04/08 16:06, 26F
謝謝　完成了　為避免忘記 我把結果記錄在底下的工作表1 https://www.sendspace.com/file/2o3elc 我還有另一個問題 在工作表2 隨便指定一塊連續的空間(譬如B2:E5) 列出裡面distinct的值(不含空白) 請問公式該怎麼寫?
→
04/08 17:44, 8年前 , 27F
match的話，將範圍轉為1欄多列(offset、index或indirect)
04/08 17:44, 27F
→
04/08 17:45, 8年前 , 28F
再抓取。
04/08 17:45, 28F
→
04/08 17:46, 8年前 , 29F
或以countif配合indirect(r1c1)來抓取回文範圍
04/08 17:46, 29F
謝謝 但光是將範圍轉為1欄多列 我就想不出要怎麼做了 可以給我個例子嗎?
→
04/09 00:16, 8年前 , 30F
offset類似這樣
04/09 00:16, 30F
謝謝 我完成了　我在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) 的關係 但不懂為什麼會這樣?
→
04/12 13:25, 8年前 , 31F
剩a1的方面儲存格h10加1
04/12 13:25, 31F
→
04/12 13:27, 8年前 , 32F
公式，判斷是否不為空字串上，是不用連接&符號的，寫法回
04/12 13:27, 32F
→
04/12 13:28, 8年前 , 33F
文上原po就有提到加了一個條件的寫法
04/12 13:28, 33F
→
04/12 13:29, 8年前 , 34F
調整後剩A2方面應可顯示於儲存格F1內
04/12 13:29, 34F
謝謝 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! 這是為什麼呢?
→
04/13 09:47, 8年前 , 35F
單一儲存格&""會為文字類型，複數儲存格&""會為array
04/13 09:47, 35F
這太細了 你不說我根本不會注意到 至此 我沒問題了 感謝 ※ 編輯: kisha024 (36.239.84.59), 04/14/2018 00:24:27
‣ 更多 kisha024 的文章
文章代碼(AID): #1QkRQegK (Office)
Office 近期熱門文章
更多 近期熱門文章 >>
PTT數位生活區 即時熱門文章
更多 即時熱門文章 >>
‣ 更多 kisha024 的文章
文章代碼(AID): #1QkRQegK (Office)