您好: 首先，搜尋板上文章"集中" ● 1 11/07 JieJuen □ [算表] EXCEL:依條件集中資料 2 111/24 JieJuen □ [算表] EXCEL:1.產生ABCD... 2.資料集中公式 3 212/09 JieJuen □ [算表] EXCEL:篩選-使用資料集中公式 4 2/15 JieJuen □ [算表] Excel:依條件集中資料-直轉橫 或是參考 │ 文章代碼(AID): #17JjZjF4 (Office) [ptt.cc] Re: [算表] Excel較少被提及的? │ ● 3973 11/29 JieJuen R: [算表] Excel較少被提及的函數與小技巧 集中資料，常使用SMALL(IF())式 不過每次雖然核心相同 總有小部分不同 您的想法可以套用此式 ※ 引述《matryoshka (俄羅斯娃娃)》之銘言： : 您所使用的軟體為:Excel : 版本:2003 : 問題: : 請教板上各位高手 : 假設我現在有一個總表如下 : Name DataA DataB DataC : Mary A DE_277412 自由歌 : John B DE_277492 以為你都知道 : Peter C DE_277503 我的未來不是夢 : Kitty A DE_277683 一天到晚游泳的魚 : Kitty B DE_277811 烈火青春 : Peter C DE_277812 帶我去月球 : John A DE_278633 永遠不回頭 : Mary B DE_278637 愛從不輕易的來 : Peter C DE_278639 天天想你 : Kitty A DE_523963 和天一樣高 : John B DE_631451 大海 : Peter C DE_631518 如果你冷 : John A DE_621852 沒有煙抽的日子 : Mary B DE_624398 湖心草深長 : Mary C DE_626238 我是一棵秋天的樹 : Peter A DE_627932 我呼吸我感覺我存在 : 總表裡其中AB欄可能會有重覆,唯一確定百分百不會重覆的是C欄 : 然後我要另外弄一個分表叫Mary(或Peter,Kitty等) : 把這個總表裡篩選出來的Mary資料丟到[Mary]裡去 : 分表裡的格式跟欄位跟總表一樣 : 我本來嘗試在分表裡以姓名欄做索引，用vlookup去抓 : 可是發現這樣做的話，不管怎麼拉都只會顯示第一筆而已 : 後來在網上問到說可以利用樞紐分析表欄位編排產生分類好的資料表 : 但是用一用又發現問題....這樣產生出來的儲存格只能丟255個字元 : 而我原始資料裡有一拖拉庫是超過255的.... : (別人系統產生出來的報表就是超過255的，我們要拿這些東西再加上我們自己的欄位) : 原本我們是用在總表篩選條件，把相同人名列出來後再copy到各對應分表去 : 只是說後來看看這樣覺得蠻有點沒效率的 : 因為我們總共有近十個分表、總表光欄位就2x個、列數也是有數百，這樣copy貼上很難拉 : 所以想請教有沒有其它比較好的方式.... : 我自己是想到，分表那邊如果以C欄做索引值帶資料是很好帶 依據您的想法 需要得到合條件的C欄 {=OFFSET($C$1,SMALL(IF($A$2:$A$20="Mary",ROW($1:$19)),ROW(1:1)),)} 如此可依序得到"Mary"的各個索引值 : 可是問題在於我要如何先把C欄做分類讓它可以知道這是屬於誰的資料..? : 本來有嘗試過開一個新的工作表專門丟分類或公式函數索引一類的(變成工作表互帶公式) : 試一試發現問題還是會卡在以人名做分類這裡... : 想請各位幫忙解答一下，看有沒有除了巨集以外更好的方式呢 : (因為我不會寫巨集...orz) : 感恩!!~ 這樣算解決了，沒問題的話以下可省略不看^^" 如果工作表名稱已經打好 "Mary"處引用工作表名稱 MID(CELL("filename",C2),FIND("]",CELL("filename"))+1,31) 如此可選取多個工作表一起編輯 或用 編輯/填滿/跨表填滿(填滿工作表) 完成 另可定義動態範圍 自動調整資料表長度 寫公式時也比較清楚 例如 Name =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A$2:$A$65536)) 若資料有空列 有別的寫法 │ 文章代碼(AID): #17IL5XKA (Office) [ptt.cc] [算表] EXCEL:求一欄最後一個位 │ ● 3926 11/25 JieJuen □ [算表] EXCEL:求一欄最後一個位置 現在最重要的問題是 超過255字元的是哪一欄? 如果是code欄 就不能把code放到 match vlookup裡面 如果是Name欄 就不能把長長的名字放到 match vlookup中 也就是說寫到上面的式子後(上面的式子應該沒問題) 其他資料要藉由該code欄來vlookup 或match等 code太長時會使vlookup錯誤 就無法如您所說"以C欄做索引值帶資料" 不過沒關係，新增一欄 {=SMALL(IF($A$2:$A$20="Mary",ROW($1:$19)),ROW(1:1))} 再以此數字當索引就不會有問題 而且此法其他欄引用之公式很短 節省空間與計算 比如索引值在分表的a2 =OFFSET(總表!A$1,$A2,) 拉滿整個分表即可 檔案 http://kuso.cc/3j@M ------------------------------- 看到您的推文 原來新增一欄是個不便之處 SMALL(IF())式只是一數字 並非一定要新增一欄(只是幫助了解) 稍加更改 請參考 http://kuso.cc/3k2v 總共就一個式子 在各分表的A2 =OFFSET(總表!A$1,no,) 定義 Name =OFFSET(總表!$A$2,,,COUNTA(總表!$A$2:$A$65536)) no =SMALL(IF(Name=MID(CELL("filename",INDIRECT("A1")), FIND("]",CELL("filename"))+1,31),ROW(Name)-1),ROW()-ROW(Name)+1) 應用時 適當調整Name之定義 注意分表工作表之名稱 no應該不用修改 若起始處不是總表的A2 改Name後 no應會調整 可用 "移動或複製工作表" --

※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 218.164.48.204 ※ 編輯: JieJuen 來自: 218.164.48.204 (03/19 23:18)