ВПР, если существует, использовать другое значение

Question 1

Итак, идея, похоже, в том, что вам нужен список комнат, которые еще не забронированы. Из этого списка вы выберете одну по некоторым полезным критериям.

Я бы подошел к этому иначе, чем вы. Я бы:

Создайте где-нибудь список всех доступных комнат. Возможно, это будет простой список от 1 до 100, возможно, более сложный. Создайте его в таблице на вспомогательном листе, который вы скрываете или не скрываете. Создайте его как именованный диапазон, чтобы он был удобен, но не был виден и легко «искалечен» пользователями.
Возьмите его и удалите комнаты, которые находятся в выделенном столбце («Комната» на Листе 1).
Выберите метод выбора оставшихся комнат для резервирования.

Далее я создаю список комнат 1-12 в C1:C12. Список уже выделенных комнат находится в A1:A4. Вы можете изменить эти диапазоны по своему усмотрению: используйте список всех созданных вами комнат и столбец Room на Sheet1. Затем я сравниваю каждую комнату в списке "exists" с комнатами в списке "committed".

На самом внутреннем уровне это IF()выполнение этого сравнения. У него есть ""результат для ИСТИННЫХ (уже выделенных) комнат. Я дал ему результат для ЛОЖИ, но, по правде говоря, это не имеет значения и его можно было бы не включать, если бы отсутствующий аргумент не сбивал с толку через год, когда вы захотите обновить электронную таблицу. Многие сочтут это сбивающим с толку, поэтому я добавил его. Но некоторые сочтут ЭТО запутанным, так что «приправьте по вкусу».

Причина, по которой это не имеет значения, заключается в том, что тест выдаст ошибку для еще не зафиксированных комнат и остановится, так и не достигнув результата FALSE. На самом деле вам нужны все эти комнаты, которые выдают ошибки. Поэтому я оборачиваю это в , чтобы IFERROR()перехватить их и дать им какой-то полезный результат: любое значение, которое тестируется. Так что все это дает вам кучу пустых мест и кучу незафиксированных комнат.

Я UNIQUE()сократил количество пробелов до 1. Так что теперь у вас есть список неиспользованных комнат и пробел. Если у вас его нет UNIQUE()(вопрос от 2019 года, и у автора он может быть, а может и не быть, но у любого, у кого более ранняя версия, его не будет), вы можете сделать следующий шаг, просто немного по-другому.

Затем я SORT()применил -ed к результату, чтобы упорядочить их. Не обязательно, если это не имеет значения, и не обязательно, если ваш список «существующих» комнат упорядочен с самого начала. Но он помещает пробел в конец. Если вы не можете этого сделать, SORT()потому что у вас нет функции, пробел будет первым. Наличие его в конце позволяет мне выбрать ПЕРВЫЕ «все, кроме одной комнаты», используя, INDEX()чтобы пробел исчез. Это проще, чем выбирать ПОСЛЕДНИЕ «все, кроме одной комнаты», чтобы удалить их. Легче понять. Это не особенно сложно сделать, но более запутанно, поэтому сложнее отслеживать через год.

Если вам не удалось сократить количество пробелов до 1, то вместо того, чтобы делать это COUNTA()над результатами, как я делаю, и вычитать из них 1, сделайте это над COUNTA()результатами и вычтите из них a COUNTA()«занятых» комнат.

Поскольку мне нужен только один столбец ввода в качестве одного столбца результата, использование "1" для аргумента столбца в INDEX()не является строго необходимым... иногда. Если используется форма , ROW(1:xxx)как я делаю здесь (потому что... 2019 и так далее), это не нужно. Но если вы сделаете это сегодня и используете SEQUENCE()функцию nice, это, безусловно, понадобится. По какой-то причине, если использовать его для значения в INDEX(), обычно приходится указывать другое значение, имеет ли это смысл или нет.

Итак, в любом случае, теперь у вас есть упорядоченный (одним или другим способом) список еще не «зарезервированных» комнат. Вы можете выбрать его с другим, INDEX()обернутым вокруг него, используя ,1,1для строки и столбца, или пойти на риск с RANDBETWEEN()использованием «1» и формулы для подсчета оставшихся не «зарезервированных» комнат. Возможно, просто для MIN()выбора самой низкой порядковой не «зарезервированной» комнаты. Или... ну, вы поняли идею. Множество способов. Рандомизируйте, используя RANDBETWEEN()подходящий способ, если рандомизация использования комнат важна, или просто выберите легкий путь, MIN()если нет. Какая идея вам покажется лучшей. Вы даже можете сделать это в списке «существующих» комнат, упорядочив их каким-то непоследовательным образом. Множество способов сделать это.

Answer

Итак, идея, похоже, в том, что вам нужен список комнат, которые еще не забронированы. Из этого списка вы выберете одну по некоторым полезным критериям.

Я бы подошел к этому иначе, чем вы. Я бы:

Создайте где-нибудь список всех доступных комнат. Возможно, это будет простой список от 1 до 100, возможно, более сложный. Создайте его в таблице на вспомогательном листе, который вы скрываете или не скрываете. Создайте его как именованный диапазон, чтобы он был удобен, но не был виден и легко «искалечен» пользователями.
Возьмите его и удалите комнаты, которые находятся в выделенном столбце («Комната» на Листе 1).
Выберите метод выбора оставшихся комнат для резервирования.