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

Вход

Регистрация

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

 

= Мир MS Excel/Зависимые выпадающие списки - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Зависимые выпадающие списки (Формулы/Formulas)
Зависимые выпадающие списки
albertikhsanov00 Дата: Воскресенье, 30.10.2022, 16:02 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 20% ±

Здравствуйте, помогите пожалуйста с выпадающим списком. Суть такая: на листе "Перечень" в ячейках C4 и D4 вставлены выпадающие списки (ячейка D4 зависит от выбора С4), так вот проблема заключается в том, что при копировании ячейки D4 на ячейку D5, список остается зависит от ячейки С4, хотя при копировании ячейки С4 на С5 выпадающий список меняется. Так как копировать такие списки придется на 150 строк, то решение замены в формуле на правильную ячейку не подходит + такие перечни будут создаваться по несколько штук в день. Так вот как сделать чтобы при простом копировании ячейка D5 зависела от выбора С5 и на последующие строки? Заранее спасибо!!!
К сообщению приложен файл: 5330040.xls(94.0 Kb)
 
Ответить
СообщениеЗдравствуйте, помогите пожалуйста с выпадающим списком. Суть такая: на листе "Перечень" в ячейках C4 и D4 вставлены выпадающие списки (ячейка D4 зависит от выбора С4), так вот проблема заключается в том, что при копировании ячейки D4 на ячейку D5, список остается зависит от ячейки С4, хотя при копировании ячейки С4 на С5 выпадающий список меняется. Так как копировать такие списки придется на 150 строк, то решение замены в формуле на правильную ячейку не подходит + такие перечни будут создаваться по несколько штук в день. Так вот как сделать чтобы при простом копировании ячейка D5 зависела от выбора С5 и на последующие строки? Заранее спасибо!!!

Автор - albertikhsanov00
Дата добавления - 30.10.2022 в 16:02
Gustav Дата: Воскресенье, 30.10.2022, 17:54 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Можно провернуть такой финт c минимальным использованием VBA. На листе "Перечень" нужно будет выделить ячейку, в которую будет постоянно записываться адрес в колонке C, соответствующий текущей строке активной ячейки, получаемый с помощью события листа SelectionChange.

Пусть такой служебной ячейкой будет ячейка J1 листа "Перечень". Тогда в модуль этого листа помещаем следующий несложный код события:
[vba]
Код
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Range("J1") = Me.Cells(Target.Row, 3).Address
End Sub
[/vba]
После этого идём в "Диспетчер имён" (по Ctrl+F3) и в нём для имени "Виды_оборудований" меняем в поле "Диапазон" формулу со старой, жёстко зафиксированной на ячейку Перечень!$C$4:
Код
=СМЕЩ(Виды!$A$2;ПОИСКПОЗ(Перечень!$C$4;Виды!$A$2:$A$39;0)-1;1;СЧЁТЕСЛИ(Виды!$A$2:$A$39;Перечень!$C$4);1)

на новую, с динамически изменяемым адресом:
Код
=СМЕЩ(Виды!$A$2;ПОИСКПОЗ(ДВССЫЛ(Перечень!$J$1);Виды!$A$2:$A$39;0)-1;1;СЧЁТЕСЛИ(Виды!$A$2:$A$39;ДВССЫЛ(Перечень!$J$1));1)

Всё! Можно пользоваться! Если мелькание адресов в ячейке J1 будет раздражать, можно либо скрыть ее значение белым цветом шрифта, либо переместить её куда-нибудь на правый конец таблицы ("с глаз долой")

В прилагаемом файле все вышеперечисленные манипуляции уже выполнены.
К сообщению приложен файл: LinkedDDL.xls(79.0 Kb)


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеМожно провернуть такой финт c минимальным использованием VBA. На листе "Перечень" нужно будет выделить ячейку, в которую будет постоянно записываться адрес в колонке C, соответствующий текущей строке активной ячейки, получаемый с помощью события листа SelectionChange.

Пусть такой служебной ячейкой будет ячейка J1 листа "Перечень". Тогда в модуль этого листа помещаем следующий несложный код события:
[vba]
Код
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Range("J1") = Me.Cells(Target.Row, 3).Address
End Sub
[/vba]
После этого идём в "Диспетчер имён" (по Ctrl+F3) и в нём для имени "Виды_оборудований" меняем в поле "Диапазон" формулу со старой, жёстко зафиксированной на ячейку Перечень!$C$4:
Код
=СМЕЩ(Виды!$A$2;ПОИСКПОЗ(Перечень!$C$4;Виды!$A$2:$A$39;0)-1;1;СЧЁТЕСЛИ(Виды!$A$2:$A$39;Перечень!$C$4);1)

на новую, с динамически изменяемым адресом:
Код
=СМЕЩ(Виды!$A$2;ПОИСКПОЗ(ДВССЫЛ(Перечень!$J$1);Виды!$A$2:$A$39;0)-1;1;СЧЁТЕСЛИ(Виды!$A$2:$A$39;ДВССЫЛ(Перечень!$J$1));1)

Всё! Можно пользоваться! Если мелькание адресов в ячейке J1 будет раздражать, можно либо скрыть ее значение белым цветом шрифта, либо переместить её куда-нибудь на правый конец таблицы ("с глаз долой")

В прилагаемом файле все вышеперечисленные манипуляции уже выполнены.

Автор - Gustav
Дата добавления - 30.10.2022 в 17:54
albertikhsanov00 Дата: Воскресенье, 30.10.2022, 18:17 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 20% ±

=СМЕЩ
 
Ответить
Сообщение
=СМЕЩ

Автор - albertikhsanov00
Дата добавления - 30.10.2022 в 18:17
albertikhsanov00 Дата: Воскресенье, 30.10.2022, 18:21 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 20% ±

Gustav, огромное спасибо!!! Я правильно понял, чтобы для добавления еще несколько зависимых списков в строке нужно в коде дописать эти ячейки и изменить соответственно ячейку. Например, в ячейку Е4 добавляем список зависимый от ячейки D4, в код добавляем строчку про еще один служебную ячейку, и в "Диспетчер имён" изменяем соответствующие ячейки?
 
Ответить
СообщениеGustav, огромное спасибо!!! Я правильно понял, чтобы для добавления еще несколько зависимых списков в строке нужно в коде дописать эти ячейки и изменить соответственно ячейку. Например, в ячейку Е4 добавляем список зависимый от ячейки D4, в код добавляем строчку про еще один служебную ячейку, и в "Диспетчер имён" изменяем соответствующие ячейки?

Автор - albertikhsanov00
Дата добавления - 30.10.2022 в 18:21
Gustav Дата: Воскресенье, 30.10.2022, 18:28 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Цитата albertikhsanov00, 30.10.2022 в 18:21, в сообщении № 4 ()
Например, в ячейку Е4 добавляем список зависимый от ячейки D4, в код добавляем строчку про еще один служебную ячейку, и в "Диспетчер имён" изменяем соответствующие ячейки?

Вполне возможно. Но точно пока не знаю. Я сегодня делал это первый раз на практике. Будет конкретная задача - можно будет конкретно проверить гипотезу.


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Цитата albertikhsanov00, 30.10.2022 в 18:21, в сообщении № 4 ()
Например, в ячейку Е4 добавляем список зависимый от ячейки D4, в код добавляем строчку про еще один служебную ячейку, и в "Диспетчер имён" изменяем соответствующие ячейки?

Вполне возможно. Но точно пока не знаю. Я сегодня делал это первый раз на практике. Будет конкретная задача - можно будет конкретно проверить гипотезу.

Автор - Gustav
Дата добавления - 30.10.2022 в 18:28
Gustav Дата: Понедельник, 31.10.2022, 19:23 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Маленькое дополнение вдогонку. Еще вчера подумалось, что хорошо было бы, если бы при изменении значения в колонке C значение из подчиненного списка в колонке D тут же стиралось бы, поскольку остающееся в ней значение, скорее всего, противоречило бы новому значению в колонке C (т.е. становилось бы "не из того подчиненного списка").

Сегодня прикрутил этот несложный обработчик - на событие Change листа "Перечень" (раз уж мы уже задействовали VBA для нужд задачи, то почему бы не продолжить это делать):
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim newValue, oldValue
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.EnableEvents = False
    With Target
        newValue = .Value
        Application.Undo
        oldValue = .Value
        .Value = newValue
    End With
    If newValue <> oldValue Then
        Target.Next.ClearContents 'очищаем ячейку справа - в колонке D
        Target.Next.Activate 'и позиционируемся в нее - для выбора из подсписка
        SendKeys "%{DOWN}" 'и сразу открываем этот подсписок (Alt + стрелка вниз)
    End If
    Application.EnableEvents = True
End Sub
[/vba]Как говорится, аппетит приходит во время еды. Вот и мне во время "еды" захотелось, чтобы после выбора из списка нового значения в колонке C табличный курсор (активная ячейка) сразу прыгал бы в соседнюю справа колонку D, да еще и сразу бы любезно открывал зависимый подчиненный список. И, вроде, всё клёво получилось!
К сообщению приложен файл: 9339224.xls(76.0 Kb)


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеМаленькое дополнение вдогонку. Еще вчера подумалось, что хорошо было бы, если бы при изменении значения в колонке C значение из подчиненного списка в колонке D тут же стиралось бы, поскольку остающееся в ней значение, скорее всего, противоречило бы новому значению в колонке C (т.е. становилось бы "не из того подчиненного списка").

Сегодня прикрутил этот несложный обработчик - на событие Change листа "Перечень" (раз уж мы уже задействовали VBA для нужд задачи, то почему бы не продолжить это делать):
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim newValue, oldValue
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.EnableEvents = False
    With Target
        newValue = .Value
        Application.Undo
        oldValue = .Value
        .Value = newValue
    End With
    If newValue <> oldValue Then
        Target.Next.ClearContents 'очищаем ячейку справа - в колонке D
        Target.Next.Activate 'и позиционируемся в нее - для выбора из подсписка
        SendKeys "%{DOWN}" 'и сразу открываем этот подсписок (Alt + стрелка вниз)
    End If
    Application.EnableEvents = True
End Sub
[/vba]Как говорится, аппетит приходит во время еды. Вот и мне во время "еды" захотелось, чтобы после выбора из списка нового значения в колонке C табличный курсор (активная ячейка) сразу прыгал бы в соседнюю справа колонку D, да еще и сразу бы любезно открывал зависимый подчиненный список. И, вроде, всё клёво получилось!

Автор - Gustav
Дата добавления - 31.10.2022 в 19:23
albertikhsanov00 Дата: Вторник, 01.11.2022, 06:37 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 20% ±

Gustav, ооо, круто, я как раз размышлял, как сделать так чтобы стиралось значение. Я, кстати, проверил теорию на практике для нескольких зависимых списков, и если делать служебные ячейки для каждого зависимого списка то все работает
 
Ответить
СообщениеGustav, ооо, круто, я как раз размышлял, как сделать так чтобы стиралось значение. Я, кстати, проверил теорию на практике для нескольких зависимых списков, и если делать служебные ячейки для каждого зависимого списка то все работает

Автор - albertikhsanov00
Дата добавления - 01.11.2022 в 06:37
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Зависимые выпадающие списки (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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