我有一张出勤 Excel 表，如下所示。每个学生占一行，每个上课日期占一列。我总共有 48 个班级，总共 66 名学生（之所以把这个放进去，是因为解决方案的复杂性必须是可行的）。
现在，对于每一行，即每个学生，我想知道缺课的日期。对于学生 6，我希望在该行的最后一个单元格中显示“9,19,23”。
对于每一行，我想要当天缺课的学生的学号。对于日期 = 24，我想要“7,8,9,10”显示在该列的最后一个单元格中。
如何进行条件连接而不对每个单元格进行硬编码？是否有一些我可以使用的范围公式？
答案1
以下两个公式返回所需的结果：
行：
=LEFT(CONCAT(IF(B6:L6="a",(B1:K1&","),"")),LEN(CONCAT(IF(B6:L6="a",(B1:L1&","),"")))-1)
列：
=LEFT(CONCAT(IF(L2:L11="a",(A2:A11&","),"")),LEN(CONCAT(IF(L2:L11="a",(A2:A11&","),"")))-1)
现在，使用 2016 年可用的函数已完成。（我无法测试这部分，但可能需要使用条目使它们成为数组公式{CSE}。）
显然它们很相似。其想法是使用范围作为测试基础来测试行（或列）中是否存在所需值（在本例中为“a”）IF()。如果为 FALSE，则返回“”，棘手的部分是……如何返回值并在它们之间添加逗号？
奇怪的是，如果您指定要从中绘制输出的范围并添加&","到其末尾，您不会得到类似的结果91923,，但 Excel 会理解您希望在每次返回后添加逗号。有两件事：您必须将该字符串放在括号中，否则 Excel 可能会误解您的意图。您不会得到连续多个逗号（如“,,9,,,,,19,23,,”）的原因是，该构造仅返回 TRUE 结果，因此所有会导致“空白”的空白，显示连续逗号的条目根本不会发生。
不过，最后还是会有一个逗号，因此LEFT()公式长度会增加一倍，但会去掉最后一个逗号。
另一个有趣的技巧是不要将测试范围指定为输出范围，而是将列（或行）标题指定为输出，以便直接获得所需的输出。
如今（超现代的 2021 年...）我们拥有TEXTJOIN()和SPILL功能。所以绝对不需要{CSE}，只是一个简单的公式，因为TEXTJOIN()末尾不会有逗号：
=TEXTJOIN(",",TRUE,IF(B6:L6="a",B1:L1,""))
在这一个中，那些看似可能出现的空白返回确实发生了，因此您肯定需要为第二个参数指定 TRUE，以TEXTJOIN()避免出现连续逗号问题。但您仍然需要测试以决定哪些标题单元格要返回值，哪些不返回值。对于列：
=TEXTJOIN(",",TRUE,IF(L2:L11="a",A2:A11,""))
再次，显然与行非常相似。
答案2
好吧，这有点混乱，但只要您愿意制作和隐藏一些额外的列，它就可以起作用。
基本上，这个想法是创建一个完整的 IF 语句网格（主出勤数据中的每个单元格一个），然后隐藏所有这些列并将它们的值连接成一列。
在主要数据的右侧，第二行（第一个学生的行）的单元格中添加以下公式：
=IF(B2="a",B$1&",","")
这句话的意思是，如果此行的学生在给定的日期缺席，则在此处写下日期，否则留空。（B$1 中的 $ 至关重要，因为这将使输出始终查看第一行，即使在公式的复制/粘贴版本中也是如此。）
然后，将该公式复制/粘贴到与考勤数据的单元格数量相等的完整单元格区域（因此，如果考勤数据是 48 x 66 个单元格，则 IF 语句也应该填充 48 x 66 个单元格）。
现在您应该看到 48 列，其中大部分为空白单元格，但有些单元格已填写以表示学生缺席的比赛日。
然后，在所有这些右侧的第二行中添加一个如下公式：
=CONCATENATE(X2,Y2,Z2,...etc...)
用 IF 语句的单元格替换 X2、Y2、Z2 等（您可以通过单击每个单元格，然后按逗号键来快速完成此操作）。
当你按下回车键时，该单元格应如下所示：6,13,18，
将此公式填入工作表以计算每一行/学生。
现在，如果结尾的逗号看起来太难看，你可以创建其他列来查看连接列并返回该列除最后一个字符之外的所有字符。
=IF(LEN(X2)>0,LEFT(X2,LEN(X2)-1),"")
将 X2 替换为包含连接字符串的单元格。（包括 IF(LEN(X2)>0 使得如果学生全勤，您不会收到错误。）
然后只需隐藏除最后一列之外的所有列就可以了！
或者，我认为 VBA 有一个 concatenateif 函数可以提供帮助，但我不知道如何使用它。