Запрещённые комбинации

[LuckyOwner]

Хочу организовать конфигуратор некоторого объекта.
Каждая конфигурация/исполнение/модификация объекта

определяется набором ключевых парметров.

В приложенном файле приведён пример описания объекта. В

данном примере использовано 4 параметра (в общем случае

число параметров заранее неизвестно).
В строке №2 содержатся имена параметров.
В строке №3 приведены списки возможных значений для каждого

из параметров (количество значений в каждом списке может

варьироваться).

Начиная со строки №5 заданы запрещённые комбинации т.е.

разрешённые значения парметров набранные в определённом

порядке которые не имеют право на существование.
Например, строку 4 следует читать так: Объект не может быть

длины 50, ширины 20 и высоты 10 независимо от цвета.

Подразумевается что пользователь должен сконфигурировать

объект задав набор параметров на втором листе в ячейках A1-D1

Вопрос:
возможно ли (и если да то как) ограничить пользователя в выборе

параметров с учётом запрещённых комбинаций?

Я не очень уверен что такое возможно реализовать в excel а

потому приветствуются любые идеи (не только excel).

[EducatedFool]

возможно ли (и если да то как) ограничить пользователя в выборе параметров с учётом запрещённых комбинаций?

Да, конечно.
Вот только не совсем понятно, как должен работать конфигуратор, и как должен выглядеть его интерфейс.

Вопрос: Пользователь может ввести любой набор значений (даже недопустимый), и увидеть сообщение об ошибке (к примеру, вся строка перекрашивается в красный цвет, сигнализируя об ошибке ввода);
или же надо вообще не допускать ошибочного ввода (к примеру, ввел пользователь первые 2 параметра 50 и 20 - и программа автоматически удаляет из выпадающего списка третьего параметра значение 10 - как недопустимое)?

Во втором случае - список допустимых значений для каждого параметра (для выпадающих списков) желательно иметь в виде таблички (а не список значений через запятую в ПроверкеДанных) - поскольку макрос будет сложным, и ввод данных проще будет реализовать через пользовательскую форму.

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

Обязательно выбор конфигурации делать на другом листе?
Может, сделать на том же, в строке 3 ?
Просто при вашем способе ввода списков значений для параметров аналогичные списки для Листа3 (настройки) и Листа2 (конфигуратор) не будут никак связаны между собой, и смысл строки 3 на Листе3 теряется.

[EducatedFool]

Сделал вам пример первого варианта:



Если выбранная конфигурация допустима, условное форматирование окрашивает поля ввода в зеленый цвет, если недопустима - в красный.

Для проверки допустимости конфигурации используются макросы:

Const ОбрабатываемыеСтолбцы = "A:D"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 3 Then Проверка
End Sub

Sub Проверка()
    Range("запрет").Interior.ColorIndex = 0: Dim ro As Range: КолвоЗапретов = 0
    For Each ro In Range("запрет").Rows
        If ЗапретНарушен(Range("конфигурация"), ro) Then
            КолвоЗапретов = КолвоЗапретов + 1
            Intersect(ro, Range(ОбрабатываемыеСтолбцы)).Interior.Color = vbRed
        End If
    Next ro
    ThisWorkbook.Names("КоличествоЗапретов").Value = КолвоЗапретов
End Sub

Function ЗапретНарушен(ByVal Данные As Range, ByVal Запрет As Range) As Boolean
    Set Данные = Intersect(Данные, Range(ОбрабатываемыеСтолбцы))
    Set Запрет = Intersect(Запрет, Range(ОбрабатываемыеСтолбцы))
    Dim cell As Range: ЗапретНарушен = True
    For i = 1 To Данные.Cells.Count
        ЗапретНарушен = ЗапретНарушен And (Данные.Cells(i) Like Запрет.Cells(i))
    Next i
End Function

Строки со сработавшими запретами окрашиваются в красный цвет.
В имени КоличествоЗапретов хранится количество сработавших запретов для текущего набора значений.

Проверяйте. Что не так?

[LuckyOwner]

Уважаемый EducatedFool,
Второй вариант это конечно был бы высший пилотаж,
но и первый вполне сгодится если сделать некоторые улучшения, а именно:

- Как-то надо обеспечить возможность изменения числа параметров (вероятно анализом заполненности ячеек строки 2). В вашем примере я попробовал добавить столбец E "Материал" со списком значений "Латунь; Сталь" а в E5-E9 вписал "Сталь". Попытка установки в 3ей строке "50/30/18/Белый/Латунь" закрашивает красным ячейки A9-D9 т.е. видно что столбец E не учитывается или учитывается некорректно.

- Как-то надо обеспечить возможность изменения числа запрещённых комбинаций. Аналогично добавил строку 10 и не смог по ней добиться закрашивания красным. Анализом чего этого добиться - пожалуй вопрос открытый. Дело в том что в идеале один лист XLS будет содержать данные по нескольким объектам. Слева будет добавлен столбец (или несколько столбцов) с названием объектов Ячейка A2 объединенная со всеми ячейками того же столбца относящимися к конкретному объекту.
В связи с этим также возникнет необходимость переключения между полным и кратким видами (в кратком надо будет прятать запрещённые комбинации). Думаю логично это (переключение полный/краткий) будет реализовать на "промежуточных итогах" или как там оно называется. С учётом этих вновь-открывшихся обстоятельств это может оказаться не такой уж и простой задачкой ... Может объединённая ячейка это ключ к подзадаче?

- Необходимо запретить сохранение файла с запрещённой комбинацией заданной в 3ей строке. Также неплохо было бы кроме подкрашивания красным цветом вывести явное предупрежение что-то типа "Заданная комбинация конфликтует с запрещённой комбинацией выделенной красным. Попытка сохранения файла с данной комбинацией будет блокирована. Пожалуйста задайте другой набор параметров." Ну и кнопка <ОК>.

PS: EducatedFool, а вы опасный человек - нельзя же с такой молниеносной скоростью думать над чьими-то чужими проблемами.

[EducatedFool]

Это был лишь пробный вариант.
Если он вас устроил - то первые 2 пожелания воплотить в коде очень легко.

Второй вариант это конечно был бы высший пилотаж

Да сложно очень его делать - времени надо очень много. (кода раз в 10-20 больше)
И над алгоритмом придётся немало потрудиться.
Да и не всегда стабильно он будет работать.
Так что, не советую второй. Первый вариант проще и удобнее.

Как-то надо обеспечить возможность изменения числа параметров

Сейчас это можно сделать, изменив константу в первой строке кода:
Const ОбрабатываемыеСтолбцы = "A:D"
Достаточно заменить эту строку на Const ОбрабатываемыеСтолбцы = "A:E" - и будут обрабатываться 5 столбцов.

В доработанном варианте вообще убрал эту строку кода - теперь кол-во стобцов определяется по последней заполненной ячейке в первой строке.

Как-то надо обеспечить возможность изменения числа запрещённых комбинаций.

В тестовом варианте для этого использовался именованный диапазон запрет - достаточно было через меню Вставка - Имя - Присвоить изменить размеры этого диапазона.

В доработанном варианте вообще убрал этот именованный диапазон - теперь используется встроенный в Excel объект "список" (в терминологии Excel2007 - "таблица").
Этому списку присвоено имя СПИСОК.
При добавлении данных снизу или справа от этого списка он автоматически расширяется (обратите внимание на синюю рамку вокруг таблицы с запретами).
При желании, можете вручную изменить размеры этого списка - в правом нижнем углу списка есть треугольничек, за который можно потянуть мышом)

Необходимо запретить сохранение файла с запрещённой комбинацией заданной в 3ей строке

Реализовано:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Val([КоличествоЗапретов]) > 0 Then
        Cancel = True    ' отменяем сохранение файла
        txt = "Заданная комбинация конфликтует с запрещённой комбинацией выделенной красным." & vbNewLine & _
              "Попытка сохранения файла с данной комбинацией будет блокирована." & vbNewLine & vbNewLine & _
              "Пожалуйста задайте другой набор параметров."
        MsgBox txt, vbCritical, "Сохранение невозможно"
    End If
End Sub

Ячейка A2 объединенная со всеми ячейками того же столбца относящимися к конкретному объекту.

А вот этого делать не советую. Придётся, возможно, немного переделывать макрос.

См. доработанный вариант:



Проверьте, отпишитесь.

[LuckyOwner]

Правильно ли я понимаю что крайняя версия макроса ориентирована на Excel2007?
Я исходно открывал 2003м и не мог понять почему нету синей рамки.
Сейчас уже установил 2007й. Рамки всё равно нет, но в правом нижнем углу правой нижней ячейки диапазона описывающего запрещённые комбинации есть синий уголочек (см JPG).

Автоматического расширения диапазона ячеек запрещённых комбинаций при добавлении данных в соседние с диапазоном ячейки почему-то не наблюдаю. Ручное перетаскивание синего уголочка в сторону охвата большего количества ячеек тоже не приводят к желаемому эффекту (мои потуги внести изменения в XLS прилагаю).

Кстати почему-то не могу убрать зелёную заливку у ячеек нового столбца.

Так много несоответствий что впору задуматься "А ровно ли у меня установился Ecxel"

Поясните пожалуйста назначение строки 5.

[doober]

Здесь описано,что означает синий уголочек.

[EducatedFool]

Насчёт синей рамки - не знал, что в 2007-м она не отображается.
Я делал в 2003 - там её видно: (особенно при выделении любой ячейки ВНУТРИ списка запретов)

Ручное перетаскивание синего уголочка в сторону охвата большего количества ячеек тоже не приводят к желаемому эффекту

Исправил недоработку - теперь эффект будет.

Кстати почему-то не могу убрать зелёную заливку у ячеек нового столбца.

В условном форматировании для этих ячеек удалите все правила - и не будет заливки.

Поясните пожалуйста назначение строки 5.

Мне проще было скрыть эту строку, чем пояснить )

А ровно ли у меня установился Excel

Да, ровно. Проблема исключительно в файле.

Проверяйте обновленную версию (см. скриншот)