# 警告：

How it works:

• For Helper data,

Formula in Cell `F55:`

``````=COUNTIF(B\$55:B\$60,"<="&B55)
``````

Formula in Cell `G55:`

``````=SMALL(\$F\$55:\$F\$60,ROW(A1))
``````

Fill both formula down.

• Sort by group, formula in Cell `A63:`, fill it down.

`````` =INDEX(B\$55:B\$60,MATCH(G55,F\$55:F60),0)
``````
• An array (CSE) formula,

In Cell `B63:`

`````` {=INDEX(\$A\$55:\$A\$60, MATCH(0, IF(\$A63=\$B\$55:\$B\$60, COUNTIF(\$B\$62:\$B62, \$A\$55:\$A\$60), ""), 0)}
``````

N.B. Finish with Ctrl+Shift+Enter & fill down.

In Cell `C63:`

``````  {=INDEX(C\$55:C\$60, MATCH(0, IF(\$A63=\$B\$55:\$B\$60, COUNTIF(C\$62:C62, C\$55:C\$60), ""), 0))}
``````

N.B. Finish with Ctrl+Shift+Enter & fill Down then Right.

Adjust cell references in the formula as needed.

• For neatness you may hide Helper Data.

Method 2:

How it works:

• Select Range `A71:D77` and in Name Box (top left corner) write name `Mydata`.
• From Developer TAB hit Design then Insert.
• 现在，从一个ctiveX控制选择ç ommand按钮，然后绘制在工作表。
• d ouble点击ç ommand按钮打开VB编辑器。
• 在以下位置复制并粘贴此代码

Ë第二子

``````    Range("MyData").Sort Key1:=Range("MyData").Cells(2, 2), _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
``````
• 保存并返回到工作表。

• 关闭d ESIGN模式，然后d ouble单击命令按钮。

• 您将按以下顺序获取数据：

NB源数据区域`A71:D77`ř安格名称`Mydata`是可编辑的。

0 条评论