我正在 Excel 中创建一个调度电子表格。假设我们有 5 个房间，两个房间可容纳 10 人，其他三个房间可容纳 20 人。
我想创建一个列表，根据团体人数显示哪些房间可用。所以如果我有 8 个人，列表将显示 5 个可用房间。但如果我有 15 个人，列表将只显示 3 个可用房间。我知道这可以通过依赖下拉列表来实现。但为每个房间创建表格，每个房间容纳一行，这似乎不太有效。有没有更简单的方法？我希望能够将行返回到下拉列表中，IF其大小是>=用户的输入。
答案1
if在下拉列表的验证中放入一个是没有问题的。
首先，像平常一样创建列表：选择您想要下拉列表的单元格，数据->数据验证->允许列表。
然后在源中写下你的条件。假设你的 5 个房间列在 R1 到 R5 中，并在 A1 中输入你有多少人，那么你可能会得到类似这样的结果：
=IF(A1>10,R3:R5,R1:R5)
第 2 部分嵌套 IF：
当条件太多，甚至无法放入框中时，我们需要另一种解决方案。第一个也是最简单的方法是尝试使其更短。
=IF($E$96<=3,Sheet1!$A$2:$A$22, IF($E$96<=6, Sheet1!$A$3:$A$22, IF($E$96<= 8, Sheet1!$A$5:$A$22, IF($E$96<= 9, Sheet1!$A$6:$A$22, IF($E$96<=10, Sheet1!$A$7:$A$22, IF($E$96<=15, Sheet1!$A$8:$A$22, IF($E$96<=16,Sheet1!$A$10:$A$22, IF($E$96<=18,Sheet1!$A$11:$A$22, IF($E$96<=20,Sheet1!$A$14:$A$22, IF($E$96<=34,Sheet1!$A$17:$A$22, IF($E$96<=100,Sheet1!$A$18:$A$22,)))))))))))
确实很长。我们可以做一些事情来缩短它，比如去掉绝对引用并使用函数IFS。代码将如下所示：
=IFS(E96<=3,Sheet1!A2:A22, E96<=6, Sheet1!A3:A22, E96<= 8, Sheet1!A5:A22, E96<= 9, Sheet1!A6:A22, E96<=10, Sheet1!A7:A22, E96<=15, Sheet1!A8:A22, E96<=16,Sheet1!A10:A22, E96<=18,Sheet1!A11:A22, E96<=20,Sheet1!A14:A22, E96<=34,Sheet1!A17:A22, E96<=100,Sheet1!A18:A22)
好多了，但还是太长了。当然，如果我们把它放在同一张纸上，它可能会更短。但从长远来看，这不是一个好的解决方案。
解决列表输入框限制的一种方法是将代码放在单元格中，然后使用 引用它INDIRECT()。
下图显示了它的外观：
列表在C5并且仅包含代码=INDIRECT(E3)
并且在图片中，列表包含Room15-20。
E3我使用 在每个检查的代码中使用换行符ALT + Enter。
包含代码的单元格可以隐藏或放在另一张表上，或者按照您的意愿。您可能希望它与房间列表放在同一张表上，只是为了减少代码的混乱。
第 3 部分 OFFSET()/MATCH()
使代码更加动态（但复杂性不减）的一种方法是使用函数MATCH()来搜索我们可以使用的房间。
在这种情况下，我们需要按升序指定每个房间可容纳的人数，如下所示：
接下来是最难的部分。在列表中，我想指定我们可以使用哪些单元格，因此我使用函数来执行此OFFSET()操作。看起来，OFFSET(reference, rows, cols, [height], [width])显示整个列表的基本代码将是OFFSET(A2, 0, 0, 20, 1)A2 和 20 行。
到目前为止很简单，但我们也需要将其与人相匹配。
匹配语法MATCH(lookup_value, lookup_array, [match_type])如下MATCH(C2, B2:B21, 1)
该MATCH函数将仅返回一个数字，即容纳该人数的房间的行号。因此，我们希望用该数字更改偏移量，以跳过之前的房间，我们还希望用相同的数字缩小列表范围，因此我们不会将整个列表向下移动。
只需将其视为数字并将其放入函数中即可，如下所示：
OFFSET(A2, MATCH(C2, B2:B21, 1), 0, 20-MATCH(C2, B2:B21, 1), 1)
这会使列表偏移并缩小。
我在使用这种方法时遇到了几个问题，我将总结一下：
当人数与房间完全匹配时，它将返回该大小房间的最后一行。我们不希望出现这种情况，因为我们仍然可以使用那个房间。解决方案？将 C2 的值减一，或者将列表更改为“无法容纳该房间的人数”，这有点愚蠢。
当少于 3* 人时，就没有房间可以返回（因为我们加了减一，所以是 4），所以我必须IFERROR在代码中添加检查。
最终结果是这样的：
=OFFSET(A2,IFERROR(MATCH(C2-1,B2:B21,1),0),0,20-IFERROR(MATCH(C2-1,B2:B21,1),0),1)
它很大，但无论有多少个不同大小的房间，它都可以放在盒子里。
列表看起来与上一个示例图像类似，这是列表所需的唯一代码。祝你好运！