☰

创建在相邻单元格中具有值的单元格列表

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
创建在相邻单元格中具有值的单元格列表

我在一个格式如下的文档中有几个（准确地说是 10 个）工作表，每个工作表对应一个类：

| Name | Time 1 | Time 2 | Time 3 | Time 4 |
--------------------------------------------
| Jack | Place1 | Place2 | Place3 | Place4 |
| John | Place4 | Place6 | Place2 | Place9 |
| Dave | Place8 | Place2 | Place5 | Place1 |

其中包含人员信息，包括他们在给定时间所属的位置（如时间表）。每个班级大约有 25 个人，在 9 个不同的地方。我需要做的是创建 4x9 表（针对每个地点和时间），它将列出当时在给定地点的所有人。所以它会像这样：

On one sheet:
Place 1 - Time 1
| Name | Signature |
--------------------
| Jack |           |
| Some |           | <--- this guy is from a different class (sheet)


On another sheet:
Place 2 - Time 2
| Name |           |
--------------------
| Jack |           |
| Dave |           |
| Mark |           | <--- again, another class

它可以是任何东西，宏、VBS、函数，无论什么，这真的不重要。如果它能以任何方式帮助我正确调整新表的大小，我不知道，我已经有一个包含每个地点和时间的人数的表。不幸的是，我无法手动完成，数据太多了……

感谢大家的帮助！

答案1

所以我明白了，这是通过这个公式完成的。我将用它们的解释替换变量。它很长，但实际上相当简单。请忽略捷克语版本的函数KDYŽ= IF、ŘÁDEK=ROW和ŘÁDKY= ROWS。

IFERROR(INDEX(Prima;SMALL(KDYŽ(A$1=Prima[Blok 1];ŘÁDEK(Prima[Blok 1])-3);ŘÁDKY(A$3:A3));1);
IFERROR(INDEX(Sekunda;SMALL(KDYŽ(A$1=Sekunda[Blok 1];ŘÁDEK(Sekunda[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1));1);
IFERROR(INDEX(TercieA;SMALL(KDYŽ(A$1=TercieA[Blok 1];ŘÁDEK(TercieA[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1));1);
IFERROR(INDEX(TercieB;SMALL(KDYŽ(A$1=TercieB[Blok 1];ŘÁDEK(TercieB[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1)-COUNTIF(TercieA[Blok 1];A$1));1);
IFERROR(INDEX(Kvarta;SMALL(KDYŽ(A$1=Kvarta[Blok 1];ŘÁDEK(Kvarta[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1)-COUNTIF(TercieA[Blok 1];A$1)-COUNTIF(TercieB[Blok 1];A$1));1);
IFERROR(INDEX(KvintaA;SMALL(KDYŽ(A$1=KvintaA[Blok 1];ŘÁDEK(KvintaA[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1)-COUNTIF(TercieA[Blok 1];A$1)-COUNTIF(TercieB[Blok 1];A$1)-COUNTIF(Kvarta[Blok 1];A$1));1);
IFERROR(INDEX(KvintaB;SMALL(KDYŽ(A$1=KvintaB[Blok 1];ŘÁDEK(KvintaB[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1)-COUNTIF(TercieA[Blok 1];A$1)-COUNTIF(TercieB[Blok 1];A$1)-COUNTIF(Kvarta[Blok 1];A$1)-COUNTIF(KvintaA[Blok 1];A$1));1);
IFERROR(INDEX(Sexta;SMALL(KDYŽ(A$1=Sexta[Blok 1];ŘÁDEK(Sexta[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1)-COUNTIF(TercieA[Blok 1];A$1)-COUNTIF(TercieB[Blok 1];A$1)-COUNTIF(Kvarta[Blok 1];A$1)-COUNTIF(KvintaA[Blok 1];A$1)-COUNTIF(KvintaB[Blok 1];A$1));1);
IFERROR(INDEX(Septima;SMALL(KDYŽ(A$1=Septima[Blok 1];ŘÁDEK(Septima[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1)-COUNTIF(TercieA[Blok 1];A$1)-COUNTIF(TercieB[Blok 1];A$1)-COUNTIF(Kvarta[Blok 1];A$1)-COUNTIF(KvintaA[Blok 1];A$1)-COUNTIF(KvintaB[Blok 1];A$1)-COUNTIF(Sexta[Blok 1];A$1));1);
IFERROR(INDEX(Oktáva;SMALL(KDYŽ(A$1=Oktáva[Blok 1];ŘÁDEK(Oktáva[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1)-COUNTIF(TercieA[Blok 1];A$1)-COUNTIF(TercieB[Blok 1];A$1)-COUNTIF(Kvarta[Blok 1];A$1)-COUNTIF(KvintaA[Blok 1];A$1)-COUNTIF(KvintaB[Blok 1];A$1)-COUNTIF(Sexta[Blok 1];A$1)-COUNTIF(Septima[Blok 1];A$1));1);""

))))))))))

主要部分在那里重复了几次，作为该IFERROR()功能的替代文本：

IFERROR(
    INDEX(
        <table range>;
        SMALL(
            IF(
                <value to find>=<range in table to look in>;
                ROW(<range in table to look in>)-<number of rows above the first row of the table>
            );
            ROWS(<first-cell-in-results-column-to-this-cell range>) <note here>
        );
        <column number (starting from 1) with the return value>
    );
    <alternative for IFERROR>
)

如果值设置正确，则上述公式将在搜索范围内找到正确值时从返回列返回所有值。现在，如果您需要完成我所需要的操作，即将多个表中的值放入单个列表中，则需要将此公式设置为替代公式IFERROR，每个源表设置一次。

这很好，但这不是我们唯一需要做的事情。此公式的工作原理是在列表的每个单元格内内部创建一个返回值数组，然后从中选择第 N 个最小索引（函数INDEX），其中 N 由ROWS内的函数INDEX设置。因此，如果我们不执行以下操作，当继续下一个表时，它会再次尝试选择第 N 个索引，但是前面的表中已经有一些值。我们需要使用函数减去表中已经存在的项目数量COUNTIF。因此，在我们添加公式的第二次迭代后，整个过程将如下所示：

IFERROR(
    INDEX(
        <table range>;
        SMALL(
            IF(
                <value to find>=<range in table to look in>;
                ROW(<range in table to look in>)-<number of rows above the first row of the table>
            );
            ROWS(<first-cell-in-results-column-to-this-cell range>)
        );
        <column number (starting from 1) with the return value>
    );
    IFERROR(
        INDEX(
            <table 2 range>;
            SMALL(
                IF(
                    <value to find>=<range in table 2 to look in>;
                    ROW(<range in table 2 to look in>)-<number of rows above the first row of the table 2>
                );
                ROWS(<first-cell-in-results-column-to-this-cell range>)
                -COUNTIF(<range in table 2 to look in>; <value to find>)
            );
            <column number (starting from 1) with the return value>
        );
        <another iteration or something else>
    )
)

并且，在下一个迭代中，IFERROR我们必须添加另一个-COUNTIF函数并保留先前的函数。然后，当一切设置完毕后，您只需将公式拖到板上并创建您想要的列表即可！

我真的希望这对某些人有帮助，如果确实如此，请随意发表评论:) 我花了一些时间研究这个公式并弄清楚事情是如何运作的，所以如果我为你节省了一些时间，我很乐意提供帮助！:)

相关内容