Excel合并单元格:排序
(2013-09-14 12:54:48)
标签:
excel合并单元格排序 |
分类: excel基础应用 |
Excel合并单元格:排序
EXCEL中最好不用使用合并单元格,用跨列居中和跨行居中即可代替合并单元格的功能。但是在现实中总会碰到一些使用了合并单元格的表格,当需要排序时,会出现无法解决的问题。
请看下面的表格:
EquipmentID |
cnh code |
CNHJILPJS1DT030 |
cnh21074 |
CNHJILPJS1DT029 |
cnh21077 |
CNHJILPJS1DT126 |
cnh21137 |
CNHJILPJS1DT115 |
cnh21123 |
CNHJILPJS1DT110 |
cnh21095 |
CNHJILPJS1DT096 |
cnh21096 |
cnh21253 |
|
cnh21255 |
|
cnh21256 |
|
CNHJILPJS1DT038 |
cnh21197 |
CNHJILPJS1DT027 |
cnh21043 |
cnh21134 |
|
CNHJILPJS1DT031 |
cnh21020 |
cnh21024 |
|
cnh21254 |
|
CNHJILPJS1DT140 |
cnh21080 |
cnh21135 |
|
cnh21143 |
|
cnh21195 |
|
cnh21196 |
|
cnh21215 |
|
cnh21255 |
|
cnh21256 |
|
CNHJILPJS1DT131 |
cnh21080 |
cnh21135 |
|
cnh21143 |
|
cnh21195 |
|
cnh21196 |
|
cnh21255 |
|
cnh21256 |
|
CNHJILPJS1DT128 |
cnh21134 |
cnh21227 |
|
cnh21253 |
|
CNHJILPJS1DT125 |
cnh21134 |
cnh21197 |
|
cnh21254 |
|
CNHJILPJS1DT124 |
cnh21133 |
cnh21197 |
|
cnh21252 |
|
CNHJILPJS1DT102 |
cnh21096 |
cnh21215 |
|
cnh21254 |
|
cnh21255 |
|
cnh21256 |
|
CNHJILPJS1DT044 |
cnh21020 |
cnh21147 |
|
cnh21252 |
|
CNHJILPJS1DT043 |
cnh21043 |
cnh21198 |
|
cnh21226 |
|
cnh21252 |
|
CNHJILPJS1DT034 |
cnh21020 |
cnh21073 |
|
cnh21123 |
|
cnh21133 |
|
cnh21198 |
|
CNHJILPJS1DT033 |
cnh21020 |
cnh21047 |
|
CNHJILPJS1DT032 |
cnh21080 |
cnh21135 |
|
cnh21143 |
|
cnh21195 |
|
cnh21196 |
|
cnh21253 |
|
cnh21255 |
|
cnh21256 |
|
CNHJILPJS1DT028 |
cnh21020 |
cnh21024 |
|
cnh21227 |
|
cnh21243 |
不管你是选取“扩展选定区域”还是“以当前选定区域排序”,点击“排序”后,都会出现下图:
请看下面的解决方案:
方法一:VBA宏
Sub 排序()
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With
Selection.FormulaR1C1 = "=R[-1]C"
Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Range("A:B").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin, DataOption1:=xlSortNormal
End Sub
该宏的主要部分是全部取消合并单元格后,选定A列,然后手工填充空值:选择要填充的范围》格式》单元格》取消合并单元格》定位》空值》按“=”》按方向键“上”》Ctrl+回车》再选择上述范围》复制》编辑》选择性粘贴》,,,,等等。
方法二:
函数方法:
1、插入—名称—定义:
原始数据:
A =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$B:$B))
B =OFFSET(Sheet1!$B$1,,,COUNTA(Sheet1!$B:$B))
根据A列构造新的数组,用同组数据填充空白单元格:
New_A =LOOKUP(ROW(A),IF(A<>"",ROW(A)),A)
排序:
x =MMULT(--(New_A>TRANSPOSE(New_A)),ROW(A)^0)*10000+ROW(A)
2、单元格公式
D1=INDEX(A:A,RIGHT(SMALL(x,ROW(1:1)),4))&""
E1=INDEX(B:B,RIGHT(SMALL(x,ROW(1:1)),4))
方法三:数据透视表
先取消A列的合并
在数据透视表的A列行标题点右键
选择表格选项
排序方法:
1、复制第一行,然后其下插入复制单元格,以后第一行不参与排序等操作,只是最后用来刷格式用。
2、最左边加一列序号。
3、选中要排序的列,先用替换,把空值换成“****”之类表中没有的值。(防止排序列本来就有空值,如没空值就不做这步)
4、把除第一行的数据去掉合并。
5、选定排序列,注意不含第一行,然后用ctrl+G定位空值,输入=D37(D37指第一个空值上面格子)然后按ctrl+enter把所有空值置为上面的值。
6、排序,其中次要关键字选序号列。
7、用格式刷复制第一行,复制到其他行。
8、把“****”替换回原来的空值,(如果第3步没做就不用了)
9、去掉多余的第一列序号和第一行等。
注:不考虑用类似参考数值的列排序,因为那样逻辑上无法排。例如某单位两个参考数值有高有低,会造成排序时要一分为二了。
请再看网友提供的一个动态图示: