英文:
Sorting by alternate numbered rows without losing data
问题
我想按升序按Sr.No.排序,以便以相同顺序获取名称和ID。由于ID旁边没有编号,我在这样做时遇到了困难,请问有人可以帮忙吗?
英文:
I want to sort by Sr.No. in ascending order in such a way that I get both Name and ID in the same order.Since the ID has no numbering alongside I am facing difficulty in doing so, can anyone please help
答案1
得分: 4
在Microsoft 365中,您可以使用:
`=SORTBY(WRAPROWS(B2:B9,2),TOCOL(A2:A9,1))`
或:
=LET(a,A2:B9,
SORTBY(WRAPROWS(DROP(a,,1),2),TOCOL(TAKE(a,,1),1)))
或者如果您想按原样堆叠它们:
=LET(a,A2:B9,
TOCOL(SORTBY(WRAPROWS(DROP(a,,1),2),TOCOL(TAKE(a,,1),1))))
在旧版本的Excel中,您可以使用以下公式(向下/向左拖动):
=IFERROR(INDEX($B$2:$B$9,
MATCH(SMALL($A$2:$A$9,ROW(A1)),
$A$2:$A$9,
0)
+COLUMN(A1)-1),
"")
或者堆叠(向下拖动):
=IFERROR(INDEX($B$2:$B$9,
MATCH(SMALL($A$2:$A$9,INT((1+ROW(A1))/2)),
$A$2:$A$9,
0)
+ISEVEN(ROW(A1))),
"")
英文:
in Microsoft 365 you could use:
=SORTBY(WRAPROWS(B2:B9,2),TOCOL(A2:A9,1))
or:
=LET(a,A2:B9,
SORTBY(WRAPROWS(DROP(a,,1),2),TOCOL(TAKE(a,,1),1)))
Or if you want them stacked the way they were:
=LET(a,A2:B9,
TOCOL(SORTBY(WRAPROWS(DROP(a,,1),2),TOCOL(TAKE(a,,1),1))))
In older Excel you could use the following formula (dragged down/left):
=IFERROR(INDEX($B$2:$B$9,
MATCH(SMALL($A$2:$A$9,ROW(A1)),
$A$2:$A$9,
0)
+COLUMN(A1)-1),
"")
or stacked (dragged down):
=IFERROR(INDEX($B$2:$B$9,
MATCH(SMALL($A$2:$A$9,INT((1+ROW(A1))/2)),
$A$2:$A$9,
0)
+ISEVEN(ROW(A1))),
"")
答案2
得分: 4
P.b的回答如此优雅,我无法竞争。
但只是为了记录,我将问题理解为需要将每个人的数据保留在两行,所以得出了以下结果:
=LET(range,A2:B9,
ser,INDEX(range,,1),
fill,SCAN(0,ser,LAMBDA(a,c,IF(c="",a,c))),
sort,SORTBY(range,fill),
IF(sort="", "", sort))
英文:
P.b's answer is so elegant I can't compete.
But just for the record I took the question as requiring each person's data to be kept on two rows so came up with this:
=LET(range,A2:B9,
ser,INDEX(range,,1),
fill,SCAN(0,ser,LAMBDA(a,c,IF(c="",a,c))),
sort,SORTBY(range,fill),
IF(sort="","",sort))
答案3
得分: 3
按照每两行排序
在第一个单元格(第2行)中添加一个带有以下公式的辅助列...
=IF(MOD(ROW(),2),C1+1,2*A2-1)
... 然后向下复制。
现在对这一列进行排序。
编辑
以下是另一种将排序后的数据溢出到另一个位置的方法:
=LET(data,A2:B13,iCol,1,
i,INDEX(data,,iCol),
f,IF(i>0,2*i-1,0),
s,VSTACK(0,DROP(IF(f>0,f+1,0),-1)),
SORTBY(IF(data="","",data),f+s))
英文:
Sort By Every Other Row
Add a helper column with the following formula in the first cell (row 2)...
=IF(MOD(ROW(),2),C1+1,2*A2-1)
... and copy down.
Now sort this column instead.
Edit
Here's another way to spill the sorted data in another place:
=LET(data,A2:B13,iCol,1,
i,INDEX(data,,iCol),
f,IF(i>0,2*i-1,0),
s,VSTACK(0,DROP(IF(f>0,f+1,0),-1)),
SORTBY(IF(data="","",data),f+s))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论