Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Microsoft Office и VBA программирование > Microsoft Office Excel
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 12.05.2016, 12:12   #1
rick1177
Пользователь
 
Регистрация: 15.02.2010
Сообщений: 59
По умолчанию Сложнейшая модель с выпадающим списком с целью контроля пользователей при формировании матриц.

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

Планета Excel молчит... видимо задача действительно сложна.
У админов прошу прощения, если тему воткнул не в то место...
Очень на Вас надеюсь.


Дополнительно кратко формулирую к описанию темы...: "Требуется ограничить и предписывать возможности выбора для пользователей с использованием меняющихся по условию выпадающих списков "
Вложения
Тип файла: docx Описание проблемы.docx (22.1 Кб, 27 просмотров)
rick1177 вне форума Ответить с цитированием
Старый 12.05.2016, 12:33   #2
EducatedFool
Программист VBA
СуперМодератор
 
Аватар для EducatedFool
 
Регистрация: 13.07.2008
Сообщений: 6,856
По умолчанию

Цитата:
видимо задача действительно сложна
нет, задача скорее трудоемкая
файла Excel нет, а описание больше смахивает на техзадание
(типа, разберитесь что к чему, набросайте результат в Excel, - а я посмотрю, что получится)

вижу 2 варианта:

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

2) вы делаете файл Excel, и обращаетесь с конкретной проблемой
(вот есть это, надо получить то)


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

в Excel есть кнопка «Проверка данных» на ленте на вкладке ДАННЫЕ, - там можно задать список допустимых значений
Можно ограничить пользователя вводом только допустимых вариантов
Список этот может быть в именованном диапазоне на другом листе
В этом списке можно формулами вычислять допустимые значения

Соответственно, - 9 ячеек, - 9 именованных диапазонов с хитрыми формулами.
Но при копировании блока 3*3 все правила проверки данных будут ссылаться на те же именованные диапазоны, - т.е. правила будут одни и те же для всех блоков (что в вашем случае не подходит)
Т.е. с масштабированием - проблема

Есть шанс (но не уверен, что будет работать) намутить с проверкой данных через динамически вычисляемые диапазоны - сложные формулы в диспетчере имён.
Тогда масштабировать можно будет путем копирования
Но это ОЧЕНЬ сложные формулы, - и бесплатно такое делать точно никто не возьмётся.

PS: всё вышеописаннное намного проще делается макросами (но тоже возни много)
EducatedFool вне форума Ответить с цитированием
Старый 12.05.2016, 13:07   #3
rick1177
Пользователь
 
Регистрация: 15.02.2010
Сообщений: 59
По умолчанию

Спасибо Вам за такой развёрнутый ответ...
Оплаты предложить не могу (тому есть много причин, но основная, что это исключительно для науки, некоммерческая задача). Да и просто с деньгами туго.

Файлик я начал делать... но мне показался он позорненьким, потому и не стал выкладывать. Но раз можно и так, то давайте попробуем.

В файле, на листе "Модель" в диапазоне D3:F6 представлена матрица желаемого вида. В ячейках сделан выбор по списку. Список на "Лист 9".
Вопрос...
Требуется в зависимости от того, какое значение выберет пользователь в ячейке F6 изменить список для ячеек E6 и F5.
Для E6:
- если в F6 выбрал значение F0, то для E6 допустимыми остаются значения: F0, F1, F2;
- если в F6 выбрал значение F1, то для E6 допустимыми остаются значения: F3, F4, F5;
- если в F6 выбрал значение F2, то для E6 допустимыми остаются значения: F0, F1;
- если в F6 выбрал значение F3, то для E6 допустимыми остаются значения: F4, F5;
- если в F6 выбрал значение F4, то для E6 допустимыми остаются значения: F0, F1, F2;
- если в F6 выбрал значение F5, то для E6 допустимыми остаются значения: F3, F4, F5;
Для F5:
- если в F6 выбрал значение F0, то для F5 допустимыми остаются значения: F0, F1, F3;
- если в F6 выбрал значение F1, то для F5 допустимыми остаются значения: F2, F4, F5;
- если в F6 выбрал значение F2, то для F5 допустимыми остаются значения: F4, F5;
- если в F6 выбрал значение F3, то для F5 допустимыми остаются значения: F0, F1;
- если в F6 выбрал значение F4, то для F5 допустимыми остаются значения: F0, F1, F3;
- если в F6 выбрал значение F5, то для F5 допустимыми остаются значения: F2, F4, F5.

При это, если в ячейке F6 ещё ничего не выбрано, то и нельзя ничего выбирать в E6 и F5.
Вложения
Тип файла: rar Excel Dekon.rar (147.5 Кб, 9 просмотров)

Последний раз редактировалось rick1177; 12.05.2016 в 13:09. Причина: Забыл файл
rick1177 вне форума Ответить с цитированием
Старый 12.05.2016, 13:10   #4
IgorGO
Новичок
СтарожилДжуниор
 
Аватар для IgorGO
 
Регистрация: 05.02.2008
Сообщений: 9,487
По умолчанию

rick1177,
последняя картинка в Вашем описании "все до чего я дошел"
где в ячейку 1 пользователю разрешено вставить Ф0,Ф1...Ф5!!! никак не вяжется с двумя предыдущими фразами:
1. Ф5 в диагонали = 2
2. Сумма по гл.диагонали (ячейки 1, 4, 9) должна быть 3

если пользователь в яч.1 поставит Ф5(=2), у него будет возможность всунуть в диагональ Ф0?
если нет, то чтобы он теперь не поставил в ячейки 4 и 9 - сумма диагонали будет 4!

знаете,
как Снежная королева вернулась в свое царство и спрашивает Кая:
-- тебе удалось выполнить мое задание?
на что Кай ответил:
-- не удалось! как я не пытался - я не смог из букв О, Ж, П и А сложить сложить слово "ВЕЧНОСТЬ"

Вы хотите масштабируемую задачу?
потрудитесь тогда
либо алгоритмически обьяснить принципы запретов на ввод данных в ячейки
либо на примерах для 4х4
и возможно для 5х5

и желательно не делать ошибок в заполнении (обьяснении) чтобы не сбивать с толку, тех кто потенциально может это сделать


и последнее:
Цитата:
Планета Excel молчит... видимо задача действительно сложна
- это хороший прием, подстегнуть интерес к задаче, но с другой стороны - это дешевый прием! типа - "на Планете не смогли!, а вам слабо!"
тут не покупаются "на слабо"
если задача интересна и понятна - может быть сделана
а когда не интересна, или когда задача не понятна (она автоматически не интересна)... с соотв. вероятностью быть решенной кем-то
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
IgorGO вне форума Ответить с цитированием
Старый 12.05.2016, 13:31   #5
rick1177
Пользователь
 
Регистрация: 15.02.2010
Сообщений: 59
По умолчанию

IgorGO, рад видеть именно ВАС!
Именно Вы уже помогали мне и критиковали меня.
Сразу хочу сказать, что аналогично и нашим прошлым перепискам Вы говорили, что стимулировать, указывая на то, что на других Форумах задача не решена - плохая затея. Но я и не имел ввиду ничего такого. Просто я констатировал, что ищу помощи и результата пока нет.
Не принимайте плохо...

Цитата:
Сообщение от IgorGO Посмотреть сообщение
rick1177,
последняя картинка в Вашем описании "все до чего я дошел"
где в ячейку 1 пользователю разрешено вставить Ф0,Ф1...Ф5!!! никак не вяжется с двумя предыдущими фразами:
1. Ф5 в диагонали = 2
2. Сумма по гл.диагонали (ячейки 1, 4, 9) должна быть 3

если пользователь в яч.1 поставит Ф5(=2), у него будет возможность всунуть в диагональ Ф0?
если нет, то чтобы он теперь не поставил в ячейки 4 и 9 - сумма диагонали будет 4!
Все именно так... Пользователь, установив F5 обязует себя далее по главной диагонали где-то выставить F0. Сумма должна быть именно 3!
Ваше рассуждение полностью верно.

Цитата:
Сообщение от IgorGO Посмотреть сообщение
rick1177,
Вы хотите масштабируемую задачу?
потрудитесь тогда
либо алгоритмически обьяснить принципы запретов на ввод данных в ячейки
либо на примерах для 4х4
и возможно для 5х5
Масштадировать задачу не предполагалось.
Однако, предвосхищая этот момент, можно сказать, что ни одна формулировка не именится, кроме той, что:
По главной диагонали при матрице 4x4 сумма обязательно составила 4;
По главной диагонали при матрице 5x5 сумма обязательно составила 5... и т.д.

Последний раз редактировалось rick1177; 12.05.2016 в 13:32. Причина: ошибка
rick1177 вне форума Ответить с цитированием
Старый 12.05.2016, 14:37   #6
rick1177
Пользователь
 
Регистрация: 15.02.2010
Сообщений: 59
По умолчанию

Начал с того, что попытался на отдельном листе прописать правило для ячейки E6 в зависимости от того, что находится в ячейке F6, чтобы это попадало в выпадающий список - на "Лист9" столбец А2:А7 и столбец B2:B7. В2:В7 зависит того, что поставить в ячейку F6 и является диапазоном для ячейки E6.
Вложения
Тип файла: rar Excel Dekon.rar (147.6 Кб, 11 просмотров)
rick1177 вне форума Ответить с цитированием
Старый 17.05.2016, 09:27   #7
rick1177
Пользователь
 
Регистрация: 15.02.2010
Сообщений: 59
По умолчанию Результат

Добрый день, Друзья.
Хотел поделиться своим опытом и способами решения тех проблем, с которыми я сталкивался, выполняя поставленную задачу.
Суть задачи изложена в первом прикреплённом файле. Некоторые несущественные моменты изменились в процессе выполнения, т.к. я обнаруживал собственные методические ошибки, но основные положения остались в силе.
Требовалось создать без использования макросов весьма сложную форму для заполнения матрицы по определённым принципам.
Сама матрица должна была иметь возможность многократного копирования с сохранением функционала, с пониманием того, что все матрицы на листе могут быть заполнены совершенно разным образом.
Благодарю ArtHome, за некоторые подсказки.
В общем, без лишних слов, выкладываю свой файл.

ПРЕЖДЕ ЧЕМ ОТКРЫВАИТЬ ФАЙЛ ОТКРОЙТЕ УЧСУД И УЧТАНОВИТЬ ФОРМАТ ФОРМУЛ "R1C1"
ДЛЯ КОРРЕКТНОГО ОТОБРАЖЕНИЯ ПРЕДВАРИТЕЛЬНО УСТАНОВИТЕ СОЗДАННЫЙ МНОЙ ШРИФТ!

Теперь, что касается возникавших по дороге проблем:
1) Поскольку для заполнения матрицы использовалась проверка данных через список, а формулы, контролирующие предоставляемые варианты выбора были крайне велики (как Вы знаете, а я не знал, есть ограничение на длину формулы при обозначении списка), то я долго пытался понять, как загнать страшного размера формулу в поле формулы списка. Оказалось, что есть обход. Создав свою формулу и записав её, например, в текстовом формате, можно добавить её через диспетчер имён, а в списках просто на неё сослаться! Это очень полезное явление!
2) Использование именно относительных ссылок формата R1C1 позволили реализовать бесконечное копирование и вставку.

Поскольку на данном форуме есть ограничения по размеру прикрепляемых файлов, то я выкладываю ссылку на архив со всем содержание:
1) Собственно, созданный файл.
2) Пример работы в виде файла GIF (производится сравнение использования отдельной программы и созданной модели в EXCEL)
3) Установочный файл шрифта.

Gif просто откройте с помощью Internet Explorer.

https://drive.google.com/file/d/0B84...ew?usp=sharing
rick1177 вне форума Ответить с цитированием
Старый 17.05.2016, 10:58   #8
rick1177
Пользователь
 
Регистрация: 15.02.2010
Сообщений: 59
По умолчанию После первого результата - продолжаем разработку

Коллеги, прошу подсказать Ваше видение по следующему вопросу...

Имеется, например, созданная с использованием данного файла модель на листе. Имеется, так называемая, "рабочая точка", представляющая собой внесённые в модель значения.(на представлено рисунке - красного цвета значения в нижнем ряду - 3,39; 2,82; 2,16; 2,94; 3,14). Эти значения получены путём "приведения" "физических значений" к "качественным оценкам" через линейные функции (80; 40; 20; 65; 15) соответственно.
Исходя из качественных значений (3,39; 2,82; 2,16; 2,94; 3,14) производится свёртка и получается комплексная оценка (на самом верху - 3,00 (красного цвета).
Что требуется (опять желательно без использования макросов, но здесь я думаю это точно не возможно)...
Требуется построить на отдельном листе так называемые функции чувствительности. Функция чувствительности – зависимость Итоговой (комплексной оценки) от изменения одного из факторов модели при прочих фиксированных.
Таблица, получаемая в результате проведения анализа чувствительности по одному из факторов представляла бы собой 3 столбца (качественная оценка фактора, физическая оценка фактора, значение комплексной оценки).
Поскольку на листе с моделями их может быть бесконечное количество, функций чувствительности таже неограниченно много, то как бы я это видел….
Есть на экране кнопка «Провести анализ чувствительности». При нажатии кнопки открывается форма, где тебя просят указать физическое значение фактора, качественное значение фактора, комплексную оценку. (просто ткнуть ячейки). Кнопка «+» позволяла бы добавить второй, третий и т.д. такой набор.
После того как все наборы были бы добавлены., но нажатие на кнопке «начать» производило бы следующие действия:
1) Создавался новый лист;
2) Генерировалась бы шапка таблицы.
3) В ячейку, указанную ранее на форме, вставлялось бы значение и копировался в таблицу результат. Далее действие бы продолжалось до тех пор, пока один критерий не пробежит от 1 до 4.
Здесь надо отметить, что когда пользователь с использованием созданной формы для создания моделей создаёт модель, то вводит он физические значения критериев, а форма сама считает качественную оценку.
Анализ же чувствительности сразу проводится в качественных оценках, что требует обратного вычисления количественных оценок.

Вот как-то так….
Помогите советом или делом, кто может.
Может базовый какой-то вариант…
Спасибо.
Изображения
Тип файла: jpg 17-05-2016 11-45-21.jpg (25.9 Кб, 120 просмотров)
rick1177 вне форума Ответить с цитированием
Старый 17.05.2016, 11:01   #9
rick1177
Пользователь
 
Регистрация: 15.02.2010
Сообщений: 59
По умолчанию

Игорь, взгляните, пожалуйста, на полученный результат и продолжение...
Моет Ваша светлая мысль озарит нас...
rick1177 вне форума Ответить с цитированием
Старый 17.05.2016, 11:36   #10
IgorGO
Новичок
СтарожилДжуниор
 
Аватар для IgorGO
 
Регистрация: 05.02.2008
Сообщений: 9,487
По умолчанию

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


Купить рекламу на форуме - 42 тыс руб за месяц

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Помогите с выпадающим списком Дима1984 Microsoft Office Excel 10 14.07.2015 08:51
Помогите с выпадающим списком при редактировании БД saigaf-mun PHP 2 26.04.2015 08:41
Работа с выпадающим списком Anubys C/C++ Базы данных 4 02.11.2011 10:58
Проблема с выпадающим списком pusik Помощь студентам 0 09.05.2011 23:29
Проблемма с выпадающим списком mma БД в Delphi 1 24.01.2008 18:31