Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Выпадающий список с мультивыбором и исключением выбранного - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выпадающий список с мультивыбором и исключением выбранного (Формулы/Formulas)
Выпадающий список с мультивыбором и исключением выбранного
rick1177 Дата: Пятница, 03.06.2016, 11:06 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Друзья, добрый вечер.
Сразу хочу отметить, что у этой темы есть кросс: здесь и здесь 2

Обращаюсь к Вам, так как повисли на решении проблемы.
Обсуждение началось с того, что я искал способ решения следующей задачи:
"Вот на основе этого удачного примера, но с использованием "умных" таблиц (имеется ввиду, когда используется опция "форматировать как таблицу") сделан список с мультивыбором.
Возник вопрос... А можно ли сделать так, чтобы выбранные элементы из вариантов выбора для этой конкретной ячейки пропадали?"

Проблема была весьма успешно решена Уважаемым Ренатом и Hugo.

Решение практически полное, однако в решении есть проблема:
При использовании в одной из ячеек в Умной таблице при протягивании в столбце не происходит распространения. Конечно проблема решается копированием/вставкой. Однако, поскольку я пытаюсь сделать удобным для обывателей, я спросил, нет ли возможности сделать это решение в виде функции, которая будет вставляться в качестве формулы при создании списка.

В качестве примера я привёл достаточно классическую ситуацию, например, при создании списка в формуле указываем
Код
=ДВССЫЛ("Таблица1[Таблица 1]")
и получаем этот список, который распространяется при протягивании.
Хотелось, чтобы вместо
Код
=ДВССЫЛ("Таблица1[Таблица 1]")
писалось что-то типа
Код
=Validation("Таблица1[Таблица 1]")
и получалось, что проверялись бы уже существующие значения, записанные из списка, а в списке отражались уже только те, что ещё не использовались.

Т.е. ячейка пополняемая из списка с исключением уже использованных значений.

Наша работа приостановилась на таком варианте исполнения (прилагаю):

Что же пытались добиться:
1) есть таблица 1 и таблица 2 - базы для будущих списков;
2) В таблицу 3 заносятся значения и удаляются использованные из списка, доступного именно этой ячейкой;
Решение не работает при протягивании таблицы 3.
Предложено реализовать это в виде некой макрофункции (получилось вот что):

[vba]
Код

Public Function Validation(ByRef Target As Range, ByRef Massiv As Range) As String
    curval = Split(Target.Value, "; ")
    For a = 1 To Massiv.Rows.Count
        For b = LBound(curval) To UBound(curval)
            If CStr(Massiv.Item(a)) = curval(b) Then Exit For
        Next b
        If b > UBound(curval) Then
            If ValidForm = "" Then ValidForm = Massiv.Item(a) _
                Else ValidForm = ValidForm & "," & Massiv.Item(a)
        End If
    Next a
    If Len(ValidForm) > 0 Then Validation = ValidForm Else Validation = ""
End Function
[/vba]
Дальше хотелось в "Формула - Диспетчер имёл" создать формулу, что-то типа :
Код
=Validation(АДРЕС(СТРОКА();СТОЛБЕЦ());Таблица1)


а в формуле для списка написать "=Формула" и чтобы всё работало...
Но не работает.

Сам автор указал: "Если я правильно понял, то это будет примерно так, но у меня знаний маловато по пользовательским функциям, чего-то не работает (именованная переменная "Формула")... Не соображу правильно ли передаю в неё данные и возможно ли вообще такое."

Помогите, пожалуйста, дорешать....
К сообщению приложен файл: ____.xlsm(21Kb)


Сообщение отредактировал rick1177 - Пятница, 03.06.2016, 11:07
 
Ответить
СообщениеДрузья, добрый вечер.
Сразу хочу отметить, что у этой темы есть кросс: здесь и здесь 2

Обращаюсь к Вам, так как повисли на решении проблемы.
Обсуждение началось с того, что я искал способ решения следующей задачи:
"Вот на основе этого удачного примера, но с использованием "умных" таблиц (имеется ввиду, когда используется опция "форматировать как таблицу") сделан список с мультивыбором.
Возник вопрос... А можно ли сделать так, чтобы выбранные элементы из вариантов выбора для этой конкретной ячейки пропадали?"

Проблема была весьма успешно решена Уважаемым Ренатом и Hugo.

Решение практически полное, однако в решении есть проблема:
При использовании в одной из ячеек в Умной таблице при протягивании в столбце не происходит распространения. Конечно проблема решается копированием/вставкой. Однако, поскольку я пытаюсь сделать удобным для обывателей, я спросил, нет ли возможности сделать это решение в виде функции, которая будет вставляться в качестве формулы при создании списка.

В качестве примера я привёл достаточно классическую ситуацию, например, при создании списка в формуле указываем
Код
=ДВССЫЛ("Таблица1[Таблица 1]")
и получаем этот список, который распространяется при протягивании.
Хотелось, чтобы вместо
Код
=ДВССЫЛ("Таблица1[Таблица 1]")
писалось что-то типа
Код
=Validation("Таблица1[Таблица 1]")
и получалось, что проверялись бы уже существующие значения, записанные из списка, а в списке отражались уже только те, что ещё не использовались.

Т.е. ячейка пополняемая из списка с исключением уже использованных значений.

Наша работа приостановилась на таком варианте исполнения (прилагаю):

Что же пытались добиться:
1) есть таблица 1 и таблица 2 - базы для будущих списков;
2) В таблицу 3 заносятся значения и удаляются использованные из списка, доступного именно этой ячейкой;
Решение не работает при протягивании таблицы 3.
Предложено реализовать это в виде некой макрофункции (получилось вот что):

[vba]
Код

Public Function Validation(ByRef Target As Range, ByRef Massiv As Range) As String
    curval = Split(Target.Value, "; ")
    For a = 1 To Massiv.Rows.Count
        For b = LBound(curval) To UBound(curval)
            If CStr(Massiv.Item(a)) = curval(b) Then Exit For
        Next b
        If b > UBound(curval) Then
            If ValidForm = "" Then ValidForm = Massiv.Item(a) _
                Else ValidForm = ValidForm & "," & Massiv.Item(a)
        End If
    Next a
    If Len(ValidForm) > 0 Then Validation = ValidForm Else Validation = ""
End Function
[/vba]
Дальше хотелось в "Формула - Диспетчер имёл" создать формулу, что-то типа :
Код
=Validation(АДРЕС(СТРОКА();СТОЛБЕЦ());Таблица1)


а в формуле для списка написать "=Формула" и чтобы всё работало...
Но не работает.

Сам автор указал: "Если я правильно понял, то это будет примерно так, но у меня знаний маловато по пользовательским функциям, чего-то не работает (именованная переменная "Формула")... Не соображу правильно ли передаю в неё данные и возможно ли вообще такое."

Помогите, пожалуйста, дорешать....

Автор - rick1177
Дата добавления - 03.06.2016 в 11:06
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выпадающий список с мультивыбором и исключением выбранного (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!