Я создаю электронную таблицу планирования в 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)
Это сместит список вниз и уменьшит его.
Я столкнулся с несколькими проблемами, используя этот подход, и я подведу итог:
Когда #people точно соответствует комнате, он возвращает последний ряд комнаты такого размера. Мы не хотим этого, так как мы все еще можем использовать эту комнату. Решение? Минус один к значению 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)
Он большой, но вписывается в коробку независимо от количества комнат разных размеров.
Поскольку список выглядит так, как показано на последнем изображении, это единственный код, который вам нужен для списка. Удачи!