close
標題:
EXCEL 如何遞減形式排序
發問:
Owner Peter ABC Ltd. $123,456 CDE Ltd. $23,456 EE Ltd. $678,900 GGFF Group $2,122,224 HHGG $678,900若Peter名下管理一大郡公司,第一組係ABC至EE Ltd., 第二組係GGFF及HHGG, 點樣不用自己計算總額,... 顯示更多 Owner Peter ABC Ltd. $123,456 CDE Ltd. $23,456 EE Ltd. $678,900 GGFF Group $2,122,224 HHGG $678,900 若Peter名下管理一大郡公司,第一組係ABC至EE Ltd., 第二組係GGFF及HHGG, 點樣不用自己計算總額, 但又可以以用組別形式 排列出最多$既公司呢?
Suggest to use the Pivot Table feature: 1. Add a header row A1:B1 – Company, Value; 2. Add a new leftmost column with header Group, assign 1 for the first 3 companies, 2 for the next 2 companies; 3. Remove the empty row between the 2 groups as data list has to be contiguous; 4. Add a new column for Group Value next to Value; 5. Now the data list lies in A1:D6 (A1:D1 is header, A2:D6 is data), insert Pivot Table; 6. Put Group Value in “Column Labels”, Group Value and Group in “Row Labels”, Sum of Values in “Values”; 7. Change the sequence of Rows and Columns by changing the “more Sort options” to obtain the desire report. 2015-03-25 20:18:41 補充： 4. Add a new column for Group Value next to Value, put in cell D2 this formula =sumif(A$2:A$6,A2,C$2:C$6) and copy to D3:D6; 2015-03-26 10:36:30 補充： If a simple sorted list is required, just sort the data list after step 4, data sort levels: Level 1: Group Value - descending Level 2: Group (separate different groups having same Group Value) - can be ascending or descending Level 3: Value - descending
其他解答:
EXCEL 如何遞減形式排序
發問:
Owner Peter ABC Ltd. $123,456 CDE Ltd. $23,456 EE Ltd. $678,900 GGFF Group $2,122,224 HHGG $678,900若Peter名下管理一大郡公司,第一組係ABC至EE Ltd., 第二組係GGFF及HHGG, 點樣不用自己計算總額,... 顯示更多 Owner Peter ABC Ltd. $123,456 CDE Ltd. $23,456 EE Ltd. $678,900 GGFF Group $2,122,224 HHGG $678,900 若Peter名下管理一大郡公司,第一組係ABC至EE Ltd., 第二組係GGFF及HHGG, 點樣不用自己計算總額, 但又可以以用組別形式 排列出最多$既公司呢?
此文章來自奇摩知識+如有不便請留言告知
最佳解答:Suggest to use the Pivot Table feature: 1. Add a header row A1:B1 – Company, Value; 2. Add a new leftmost column with header Group, assign 1 for the first 3 companies, 2 for the next 2 companies; 3. Remove the empty row between the 2 groups as data list has to be contiguous; 4. Add a new column for Group Value next to Value; 5. Now the data list lies in A1:D6 (A1:D1 is header, A2:D6 is data), insert Pivot Table; 6. Put Group Value in “Column Labels”, Group Value and Group in “Row Labels”, Sum of Values in “Values”; 7. Change the sequence of Rows and Columns by changing the “more Sort options” to obtain the desire report. 2015-03-25 20:18:41 補充： 4. Add a new column for Group Value next to Value, put in cell D2 this formula =sumif(A$2:A$6,A2,C$2:C$6) and copy to D3:D6; 2015-03-26 10:36:30 補充： If a simple sorted list is required, just sort the data list after step 4, data sort levels: Level 1: Group Value - descending Level 2: Group (separate different groups having same Group Value) - can be ascending or descending Level 3: Value - descending
其他解答:
文章標籤
全站熱搜
留言列表
