当前位置：首页 > 网站新闻 >

Excel-资料重组依原顺序呈现(SUMPRODUCT,OFFSET,ROW,CO

时间：2017-04-26 05:34:47 文章作者：www.cpdj.net 点击：95 查看次

在一个 Excel 中有一个资料表(以下图左)，若是想要在另外一个资料表依编号重组资料，并且依原挨次显现，可是由于编号会重覆，所以要若何能依原挨次列出资料呢？(参考下图右)

例如资料清单中编号1者(位于贮存格A2,A3,A7,A10,A13,…)，当在重组资料时是置于贮存格G2,G5,G9,G12,G15,…。以下图中的箭号唆使，显现时必需依本来的挨次呈现，该若何处置？

【公式设计与解析】

为申明便利，先拔取贮存格A1:A25，按 Ctrl+Shift+F3 键，勾选「顶端列」，界说名称：编号。

重组资料时(参考上图右)，『编号』栏位的内容是自行输入的。

贮存格H2：=OFFSET($B$1,SUMPRODUCT(SMALL((编号=$G2)*ROW(编号),
SUM(–(((编号=G2)*ROW(编号))=0))+COUNTIF($G$2:G2,G2)))-1,,,)

(编号=$G2)*ROW(编号)：在 SUMPRODUCT 函数中传回编号和贮存格G2不异者的『列号』，若是不不异者，传回 0。本例传回：2,3,0,0,0,7,0,0,10, …。

SUM(–(((编号=G2)*ROW(编号))=0))：计较上式传回 0 者的个数，本例传回 15。

COUNTIF($G$2:G2,G2)：计较由贮存格G2肇端至今朝贮存格範围中，和贮存格G2不异者的个数，本例传回 1。

SMALL((编号=$G2)*ROW(编号),SUM(–(((编号=G2)*ROW(编号))=0))+COUNTIF($G$2:G2,G2))：将以上三式代入 SMALL 函数，可以求得合适前提的zui小列号，本例传回 1。

将上式的传回值代入 OFFSET 函数，获得：OFFSET($B$1,传回『列号』-1,,,)。

同理：

贮存格I2：=OFFSET($C$1,SUMPRODUCT(SMALL((编号=$G2)*ROW(编号),
SUM(–(((编号=G2)*ROW(编号))=0))+COUNTIF($G$2:G2,G2)))-1,,,)

存格J2：=OFFSET($D$1,SUMPRODUCT(SMALL((编号=$G2)*ROW(编号),
SUM(–(((编号=G2)*ROW(编号))=0))+COUNTIF($G$2:G2,G2)))-1,,,)

贮存格K2：=OFFSET($E$1,SUMPRODUCT(SMALL((编号=$G2)*ROW(编号),
SUM(–(((编号=G2)*ROW(编号))=0))+COUNTIF($G$2:G2,G2)))-1,,,)

创作者先容

vincent

学不完．教不断．用不尽

▲top