Зависимые выпадающие списки (2 уровня, 1 критерий)

[bmg33]

Здравствуйте! Опять очень нужна ваша помощь, справится одному не получается. Текст вы можете не читать, а сразу посмотреть пример (там все расписано).

Как делать выпадающие списки знаю. Как делать зависимые выпадающие списки тоже знаю. Но у меня задача довольно специфичная, с которой еще никогда не сталкивался, а как ее решить - ума и опыта, к сожалению, не хватает.

Есть три вертикальных списка:
  1. ФИО. Содержит больше 3 тысяч фамилий.
  2. Должность. Содержит около тысячи наименований.
  3. Список сотрудников с указанием у кого какая должность (А=ФИО; В=Должность). Содержит данные из 1 и 2 списков.

И есть журнал регистраций, где сначала из выпадающего списка выбирается должность - "А", а затем, соответствующие выбранной должности, ФИО - "В" (из списка №3). Можно было конечно сделать так, чтобы в выпадающем списке просто показывались все ФИО, но их действительно очень много, легко запутаться и ошибиться.
Мой вопрос к знатокам и гуру заключается в следующем: Как без макросов заставить Calc делать такую выборку в выпадающем списке, чтобы показывались только те ФИО, которые соответствуют выбранной должности?

Пожалуйста помогите, очень-очень нужна ваша помощь. Предварительно всегда штудирую форум, гугл и справку и обращаюсь только если найти ничего не удалось или требуется какое-то нестандартное решение.

Файл примера прикреплен.

[JohnSUN]

Как-то так, что ли? Тогда я не понял, что ты имел в виду под

Как делать зависимые выпадающие списки тоже знаю.

Это ведь они и есть - зависимые выпадающие...

[bmg33]

Уважаемый, JohnSUN, извините, что не смог раньше ответить. Спасибо вам большое за неоценимую помощь. Списки вне всяких сомнений тоже зависимые, но я имел в виду, что умею делать списки, где критерии сгруппированы, к примеру первый - в столбцах А...Z и под ними в строках уже значения.
Как вы сделали этот документ пока ума не приложу. Вчера ночью видел подобный пример, но там был через NEXLIST и тоже с "Названиями". Я и так и сяк его ковырял, но не выходит никак. И по-моему там все дело было в макросах.
Я внимательно еще сам ваш файл не смотрел, просто потыкал - вроде работает. Сейчас глянем повнимательнее

[bmg33]

Это просто чудо какое-то, он еще и подсвечивает неправильно выбранную пару! И вроде работает без макросов. И главное, оказалось так просто!
Еще раз огромное вам спасибо!

[kompilainenn]

И главное, оказалось так просто!

только непонятно, как именно =)

[bmg33]

только непонятно, как именно =)

Да, всё так пока непонятно, но там такая коротенькая формула. В общем, я еще пытаюсь осознать механику процесса.

[JohnSUN]

Ну, порядок правок твоей исходной книги был таким:
1. Для ячеек, которые будут использоваться в списках и формулах, задал простые имена. Это действительно удобнее и проще для понимания, чем какой-нибудь $'Список сотрудников'.$B$2:$B$3072. Номер последней строки давал с запасом, ориентируясь на твои комментарии ("около тысячи профессий и должностей"=1К, "около трех тысяч сотрудников"=3К)
2. На листе 'Список сотрудников' создаем первый выпадающий список

Код Выделить Развернуть

IF($A2="";"";Профессия)То есть, если в текущей строке в первой колонке уже есть ФИО, то предоставить выбор должности, иначе оставить список пустым. Так пользователь не сможет навставлять должностей для "мёртвых душ"
3. На листе 'Операции' создаем два выпадающих списка. Для выбора должности формула

Код Выделить Развернуть

IF($B2="";Должность;IF(Сотрудник=$B2;Должность;""))Почти то же самое, что и в прошлый раз, с точностью до наоборот: если ФИО еще не выбрано, то предоставить для выбора полный список профессий, иначе... И вот тут трюк, который не сразу понятен: "просмотреть весь список Сотрудник (это все ФИО на листе 'Список сотрудников' и, в том числе, пустые ячейки ниже него), для каждого упоминания сотрудника из ячейки B2 взять соответствующую ячейку из колонки должностей, для остальных подставить пустую строку". Таким образом, если мы выбрали какую-нибудь "регистратора Смирнову", которая по совместительству ещё и "санитарка", и "медсестра", и "начмед", и "министр здравоохранения", то сможем для неё изменить должность на любую из этих. А вот для "паталогоанатом Сидоров" так и будет паталогоанатомом (если, конечно, мы не добавим нужную строку в таблицу 'Список сотрудников'). Думаю, после такого объяснения формула для второго списка покажется очень простой и понятной.
4. Главная беда связанных списков (без макросов) - существует опасность, что пользователь уже после внесения данных вернется к выбору из первого списка и изменит значение, на которое опиралась формула для следующего списка. По рукам мы ему настучать не можем, но напугать красным цветом - легко. Обычное условное форматирование по условию

Код Выделить Развернуть

NOT(OR($A2="";$B2="";SUMPRODUCT(Должность=$A2;Сотрудник=$B2)))выполнит эту задачу. В чём фокус здесь? Начнем из глубины, с Должность=$A2. Поскольку сама 'Должность' это диапазон ячеек (длинный массив), то и результат сравнения будет длинным массивом вида {нет,нет,нет,ДА,нет...нет,ДА...} (или ЛОЖЬ/ИСТИНА, или - если уж совсем точно - 0/1). В этом массиве единички будут только на тех местах, где выполнилось условие сравнения. Точно такой же массив получим и в результате второго сравнения Сотрудник=$B2. Функция SUMPRODUCT перемножает соответствующие элементы этих двух массивов, получает массив вида {0*0,0*0, 1*0 ,0*0, 1*1 ,0*0, 0*1...} и суммирует все эти произведения. То есть, если ни разу не попалась пара, которая дала 1*1, то и вся сумма будет нулевой (или в логических терминах - FALSE). Ну, проверки ячеек в колонках A и B на пустоту тоже понятны - просто чтобы не раскрашивать еще не заполненную часть таблицы. С помощью OR() формируем условие "хоть кто-то из вас троих скажет TRUE?". И с помощью функции NOT() опрокидываем итоговое "ни один из троих не признался" в действие "фигня введена - закрасить красным".
А пустые ячейки, которые толпами попадают в наши списки выбора, легко давятся флажком "Сортировать записи по возрастанию" в настройках проверки данных.
Как-то так, в общем...