Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

20 Август 2019, 15:35 *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

Войти
Новости: Вы можете задать вопрос по LibreOffice или Apache OpenOffice  без регистрации, используя форму
 
   Начало   Помощь Поиск Войти Регистрация    задать вопрос  
Страниц: 1   Вниз
  Печать  
Автор Тема: Зависимые выпадающие списки (2 уровня, 1 критерий)  (Прочитано 3964 раз)
0 Пользователей и 1 Гость смотрят эту тему.
bmg33
Участник
**
Offline Offline

Сообщений: 38



« Стартовое сообщение: 18 Август 2016, 00:24 »

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

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

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

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

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

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

* Список сотрудников и профессий.ods (56.59 Кб - загружено 63 раз.)
Записан
JohnSUN
Капитана в тот день называли на "ты"
Гуру
*******
Offline Offline

Пол: Мужской
Расположение: Киев
Сообщений: 2 764


Помогаю людям и компьютерам понимать друг друга


WWW
« Ответ #1: 18 Август 2016, 08:12 »

Как-то так, что ли? Тогда я не понял, что ты имел в виду под
Как делать зависимые выпадающие списки тоже знаю.
Это ведь они и есть - зависимые выпадающие... Смеющийся

Записан

Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне
bmg33
Участник
**
Offline Offline

Сообщений: 38



« Ответ #2: 18 Август 2016, 19:15 »

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

Сообщений: 38



« Ответ #3: 18 Август 2016, 19:29 »

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

Сообщений: 2 699



« Ответ #4: 18 Август 2016, 19:43 »

И главное, оказалось так просто!
только непонятно, как именно =)
Записан

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут
bmg33
Участник
**
Offline Offline

Сообщений: 38



« Ответ #5: 18 Август 2016, 19:50 »

только непонятно, как именно =)
Да, всё так Улыбка пока непонятно, но там такая коротенькая формула. В общем, я еще пытаюсь осознать механику процесса.
Записан
JohnSUN
Капитана в тот день называли на "ты"
Гуру
*******
Offline Offline

Пол: Мужской
Расположение: Киев
Сообщений: 2 764


Помогаю людям и компьютерам понимать друг друга


WWW
« Ответ #6: 18 Август 2016, 21:07 »

Ну, порядок правок твоей исходной книги был таким:
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() опрокидываем итоговое "ни один из троих не признался" в действие "фигня введена - закрасить красным".
А пустые ячейки, которые толпами попадают в наши списки выбора, легко давятся флажком "Сортировать записи по возрастанию" в настройках проверки данных.
Как-то так, в общем...

« Последнее редактирование: 18 Август 2016, 21:10 от JohnSUN » Записан

Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне
Страниц: 1   Вверх
  Печать  
 
Перейти в:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!