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

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир 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)

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

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

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

Код

=ДВССЫЛ("Таблица1[Таблица 1]")

и получаем этот список, который распространяется при протягивании.
Хотелось, чтобы вместо

Код

=ДВССЫЛ("Таблица1[Таблица 1]")

писалось что-то типа

Код

=Validation("Таблица1[Таблица 1]")

Код

[/vba]
Дальше хотелось в "Формула - Диспетчер имёл" создать формулу, что-то типа :

Код

=Validation(АДРЕС(СТРОКА();СТОЛБЕЦ());Таблица1)

К сообщению приложен файл: ____.xlsm (21.0 Kb)

Сообщение отредактировал rick1177 - Пятница, 03.06.2016, 11:07

Ответить

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

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

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

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

В качестве примера я привёл достаточно классическую ситуацию, например, при создании списка в формуле указываем

Код

=ДВССЫЛ("Таблица1[Таблица 1]")

и получаем этот список, который распространяется при протягивании.
Хотелось, чтобы вместо

Код

=ДВССЫЛ("Таблица1[Таблица 1]")

писалось что-то типа

Код

=Validation("Таблица1[Таблица 1]")

Код

[/vba]
Дальше хотелось в "Формула - Диспетчер имёл" создать формулу, что-то типа :

Код

=Validation(АДРЕС(СТРОКА();СТОЛБЕЦ());Таблица1)

Автор - rick1177
Дата добавления - 03.06.2016 в 11:06

Страница 1 из 1
1