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

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выпадающий список с несколькими условиями (Формулы/Formulas)
Выпадающий список с несколькими условиями
lebensvoll Дата: Вторник, 20.11.2018, 14:07 | Сообщение № 1
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
Доброго времени многоуважаемые форумчане!!!
Помогите пожалуйста с формулой...
На листе "Отгрузка" в ячейке B6 (действующий выпадающий список "Контрагента"). В ячейке B10 (также действующий выпадающий список согласно выбранного "Контрагента" предоставляется выпадающий список "Продукции")
Код
=СМЕЩ(Спецификация[Контрагент];ПОИСКПОЗ(Отгрузка!$B$6;Спецификация[Контрагент];0)-1;1;СЧЁТЕСЛИ(Спецификация[Контрагент];Отгрузка!$B$6);1)

ЗАДАЧА
На листе "Спецификация" имеется таблица согласованной продукции по контрагенту. Которая имеет свое НАЧАЛО и свое ОКОНЧАНИЕ.
[img][/img]
Как сделать так чтоб: на листе "Отгрузка" в ячейке B10 предлагался выпадающий список с учетом "Контрагента" а также с учетом (Начала и Окончания согласования продукции) на момент записи отгрузки (ячейка B2 на листе "Отгрузка").

Возможно ли это формулой!? Или же это только макросом возможно!?
Заранее спасибо Вам огромнейшее за помощь...
К сообщению приложен файл: 3315404.xlsm (69.8 Kb)


Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Вторник, 20.11.2018, 16:01
 
Ответить
СообщениеДоброго времени многоуважаемые форумчане!!!
Помогите пожалуйста с формулой...
На листе "Отгрузка" в ячейке B6 (действующий выпадающий список "Контрагента"). В ячейке B10 (также действующий выпадающий список согласно выбранного "Контрагента" предоставляется выпадающий список "Продукции")
Код
=СМЕЩ(Спецификация[Контрагент];ПОИСКПОЗ(Отгрузка!$B$6;Спецификация[Контрагент];0)-1;1;СЧЁТЕСЛИ(Спецификация[Контрагент];Отгрузка!$B$6);1)

ЗАДАЧА
На листе "Спецификация" имеется таблица согласованной продукции по контрагенту. Которая имеет свое НАЧАЛО и свое ОКОНЧАНИЕ.
[img][/img]
Как сделать так чтоб: на листе "Отгрузка" в ячейке B10 предлагался выпадающий список с учетом "Контрагента" а также с учетом (Начала и Окончания согласования продукции) на момент записи отгрузки (ячейка B2 на листе "Отгрузка").

Возможно ли это формулой!? Или же это только макросом возможно!?
Заранее спасибо Вам огромнейшее за помощь...

Автор - lebensvoll
Дата добавления - 20.11.2018 в 14:07
dude Дата: Вторник, 20.11.2018, 17:31 | Сообщение № 2
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 193
Репутация: 28 ±
Замечаний: 0% ±

2016
объем заказа не учитывается?
 
Ответить
Сообщениеобъем заказа не учитывается?

Автор - dude
Дата добавления - 20.11.2018 в 17:31
lebensvoll Дата: Вторник, 20.11.2018, 17:47 | Сообщение № 3
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
dude, Нет объем заказов тут не важен.
Главное чтоб
если оператор указал дату записи и контрагента предоставлялся выпадающий список продукции (с учетом актуальности на эту дату (записи), его подписания и окончания)


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщениеdude, Нет объем заказов тут не важен.
Главное чтоб
если оператор указал дату записи и контрагента предоставлялся выпадающий список продукции (с учетом актуальности на эту дату (записи), его подписания и окончания)

Автор - lebensvoll
Дата добавления - 20.11.2018 в 17:47
dude Дата: Вторник, 20.11.2018, 22:26 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 193
Репутация: 28 ±
Замечаний: 0% ±

2016
Код
=СМЕЩ(Спецификация!E1;АГРЕГАТ(15;6;Спецификация!A2:A21/(B2>=Спецификация!B2:B21)/(B2<=Спецификация!C2:C21)/(Спецификация!D2:D21=B6);1);;СЧЁТЕСЛИМН(Спецификация!D2:D21;B6;Спецификация!B2:B21;"<="&B2;Спецификация!C2:C21;">="&B2);)
К сообщению приложен файл: 1974121.xlsm (62.0 Kb)
 
Ответить
Сообщение
Код
=СМЕЩ(Спецификация!E1;АГРЕГАТ(15;6;Спецификация!A2:A21/(B2>=Спецификация!B2:B21)/(B2<=Спецификация!C2:C21)/(Спецификация!D2:D21=B6);1);;СЧЁТЕСЛИМН(Спецификация!D2:D21;B6;Спецификация!B2:B21;"<="&B2;Спецификация!C2:C21;">="&B2);)

Автор - dude
Дата добавления - 20.11.2018 в 22:26
lebensvoll Дата: Среда, 21.11.2018, 08:40 | Сообщение № 5
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
dude, Спасибо огромное за отзывчивость и ответ...
Но что в Вашем примере что (если я скопирую формулу в ячейку) ответ #ИМЯ? :'(
А если в диспетчере имен создать (к примеру: СМЕЩ) и вставить формулу предложенную. А в нужной ячейке В10 (создать выпадающий список (СМЕЩ) то его попросту нет ((((


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщениеdude, Спасибо огромное за отзывчивость и ответ...
Но что в Вашем примере что (если я скопирую формулу в ячейку) ответ #ИМЯ? :'(
А если в диспетчере имен создать (к примеру: СМЕЩ) и вставить формулу предложенную. А в нужной ячейке В10 (создать выпадающий список (СМЕЩ) то его попросту нет ((((

Автор - lebensvoll
Дата добавления - 21.11.2018 в 08:40
dude Дата: Среда, 21.11.2018, 09:05 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 193
Репутация: 28 ±
Замечаний: 0% ±

2016
никаких имен, только предложенная формула как источник выпад списка. фориулы в ячейках - этапы стр-ва формулы
 
Ответить
Сообщениеникаких имен, только предложенная формула как источник выпад списка. фориулы в ячейках - этапы стр-ва формулы

Автор - dude
Дата добавления - 21.11.2018 в 09:05
lebensvoll Дата: Среда, 21.11.2018, 09:20 | Сообщение № 7
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
dude, нужен выпадающий список :'(
Потому как у одного контрагента может быть согласовано несколько видов продукции на определенный период.
И если оператор выбирает контрагента (примерно ООО "АТИКА") и указывает что запись в "журнал-таблицу" (примерно 3.03.2018) то ему предлагается в ячейке В10 "выпадающий список" из нескольких продукции "актуальных" на данный момент записи. (которые были согласованы на период с 03.03.2018 по 5.06.2018 они выделены зеленым цветом в таблице на листе "Спецификация")


Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Среда, 21.11.2018, 09:21
 
Ответить
Сообщениеdude, нужен выпадающий список :'(
Потому как у одного контрагента может быть согласовано несколько видов продукции на определенный период.
И если оператор выбирает контрагента (примерно ООО "АТИКА") и указывает что запись в "журнал-таблицу" (примерно 3.03.2018) то ему предлагается в ячейке В10 "выпадающий список" из нескольких продукции "актуальных" на данный момент записи. (которые были согласованы на период с 03.03.2018 по 5.06.2018 они выделены зеленым цветом в таблице на листе "Спецификация")

Автор - lebensvoll
Дата добавления - 21.11.2018 в 09:20
sboy Дата: Среда, 21.11.2018, 09:51 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
Не оптимизировал
Код
=СМЕЩ(ИНДЕКС(ИНДЕКС(Спецификация[Начало];СЧЁТЕСЛИ(Спецификация[Контрагент];Отгрузка!$B$6)-1+ПОИСКПОЗ(Отгрузка!$B$6;Спецификация[Контрагент];)):ИНДЕКС(Спецификация[Начало];ПОИСКПОЗ(Отгрузка!$B$6;Спецификация[Контрагент];));ПОИСКПОЗ(Отгрузка!$B$2;Спецификация[Окончание])+1):ИНДЕКС(ИНДЕКС(Спецификация[Начало];СЧЁТЕСЛИ(Спецификация[Контрагент];Отгрузка!$B$6)-1+ПОИСКПОЗ(Отгрузка!$B$6;Спецификация[Контрагент];)):ИНДЕКС(Спецификация[Начало];ПОИСКПОЗ(Отгрузка!$B$6;Спецификация[Контрагент];));ПОИСКПОЗ(Отгрузка!$B$2;Спецификация[Начало]));;3)
К сообщению приложен файл: 3315404-1-.xlsm (63.9 Kb)


Яндекс: 410016850021169
 
Ответить
СообщениеДобрый день.
Не оптимизировал
Код
=СМЕЩ(ИНДЕКС(ИНДЕКС(Спецификация[Начало];СЧЁТЕСЛИ(Спецификация[Контрагент];Отгрузка!$B$6)-1+ПОИСКПОЗ(Отгрузка!$B$6;Спецификация[Контрагент];)):ИНДЕКС(Спецификация[Начало];ПОИСКПОЗ(Отгрузка!$B$6;Спецификация[Контрагент];));ПОИСКПОЗ(Отгрузка!$B$2;Спецификация[Окончание])+1):ИНДЕКС(ИНДЕКС(Спецификация[Начало];СЧЁТЕСЛИ(Спецификация[Контрагент];Отгрузка!$B$6)-1+ПОИСКПОЗ(Отгрузка!$B$6;Спецификация[Контрагент];)):ИНДЕКС(Спецификация[Начало];ПОИСКПОЗ(Отгрузка!$B$6;Спецификация[Контрагент];));ПОИСКПОЗ(Отгрузка!$B$2;Спецификация[Начало]));;3)

Автор - sboy
Дата добавления - 21.11.2018 в 09:51
lebensvoll Дата: Среда, 21.11.2018, 10:45 | Сообщение № 9
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
sboy, спасибо за ответ!!!
Цитата
Не оптимизировал

Что это означает!?
Если я вбиваю дату другую (не 4.03.2018) к примеру 1.01.2018 или же другую то выпадающий список не активен ((((
Возможно Ваша фраза "Не оптимизировал" и является решение в выпадающем списке!? Подскажите пожалуйста...
Просто если оператор вносит (На листе "Отгрузка") дату в ячейке В2 к примеру 1.01.2018 и указывает контрагента в ячейке В6 ООО "АТТИКА" то ему предлагается вот этот список
[img][/img]
Если же он указывает дату 3.03.2018 и того же контрагента то выпадающий список будет содержать вот эту продукцию
[img][/img]
А если же дата указана к примеру 6.06.2018 и контрагент ООО"АТТИКА" то выпадающий список не предоставляется потому как на эту дату не было согласовано продукции
Данная таблица на листе "Спецификация" в дальнейшем будет огромная как по количеству контрагентов так и дат начала и окончания с ним подписания...
Спасибо заранее за ответ...


Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Среда, 21.11.2018, 10:56
 
Ответить
Сообщениеsboy, спасибо за ответ!!!
Цитата
Не оптимизировал

Что это означает!?
Если я вбиваю дату другую (не 4.03.2018) к примеру 1.01.2018 или же другую то выпадающий список не активен ((((
Возможно Ваша фраза "Не оптимизировал" и является решение в выпадающем списке!? Подскажите пожалуйста...
Просто если оператор вносит (На листе "Отгрузка") дату в ячейке В2 к примеру 1.01.2018 и указывает контрагента в ячейке В6 ООО "АТТИКА" то ему предлагается вот этот список
[img][/img]
Если же он указывает дату 3.03.2018 и того же контрагента то выпадающий список будет содержать вот эту продукцию
[img][/img]
А если же дата указана к примеру 6.06.2018 и контрагент ООО"АТТИКА" то выпадающий список не предоставляется потому как на эту дату не было согласовано продукции
Данная таблица на листе "Спецификация" в дальнейшем будет огромная как по количеству контрагентов так и дат начала и окончания с ним подписания...
Спасибо заранее за ответ...

Автор - lebensvoll
Дата добавления - 21.11.2018 в 10:45
sboy Дата: Среда, 21.11.2018, 10:57 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Что это означает!?

Что нет времени, подумать над решением, возможностью его упростить и каким способом.
список не активен

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


Яндекс: 410016850021169
 
Ответить
Сообщение
Что это означает!?

Что нет времени, подумать над решением, возможностью его упростить и каким способом.
список не активен

поправил

Автор - sboy
Дата добавления - 21.11.2018 в 10:57
lebensvoll Дата: Среда, 21.11.2018, 11:20 | Сообщение № 11
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
sboy, я вас понял...
Не не работает... :'( Работает но с не точностями
Если указать 2.03.2018 то выпадающий список состоит лишь из двух продукций ((((
А если указать 5.06.2018 то выпадающий список не активен (((
А если указать 28.05.2018 и выбрать ООО "БИТОНИР" то выпадающий список также не активен.
Возможно это сложновато формулами сделать ((((
К сообщению приложен файл: 6501536.xlsm (62.1 Kb)


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщениеsboy, я вас понял...
Не не работает... :'( Работает но с не точностями
Если указать 2.03.2018 то выпадающий список состоит лишь из двух продукций ((((
А если указать 5.06.2018 то выпадающий список не активен (((
А если указать 28.05.2018 и выбрать ООО "БИТОНИР" то выпадающий список также не активен.
Возможно это сложновато формулами сделать ((((

Автор - lebensvoll
Дата добавления - 21.11.2018 в 11:20
sboy Дата: Среда, 21.11.2018, 11:33 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Работает но с не точностями

согласен, некорректная формула. Если никто решения не даст, попозже постараюсь подумать над решением


Яндекс: 410016850021169
 
Ответить
Сообщение
Работает но с не точностями

согласен, некорректная формула. Если никто решения не даст, попозже постараюсь подумать над решением

Автор - sboy
Дата добавления - 21.11.2018 в 11:33
dude Дата: Среда, 21.11.2018, 13:29 | Сообщение № 13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 193
Репутация: 28 ±
Замечаний: 0% ±

2016
Мое предложение рабочее
список на листе спецификация д/б отсортирован по возрастанию
демонстрация: один выбор для вставленной строки
К сообщению приложен файл: 3307427.xlsm (62.0 Kb)
 
Ответить
СообщениеМое предложение рабочее
список на листе спецификация д/б отсортирован по возрастанию
демонстрация: один выбор для вставленной строки

Автор - dude
Дата добавления - 21.11.2018 в 13:29
lebensvoll Дата: Среда, 21.11.2018, 15:08 | Сообщение № 14
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
dude, нет :'(
немного не то потому как нужен "выпадающий список в ячейке В10


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщениеdude, нет :'(
немного не то потому как нужен "выпадающий список в ячейке В10

Автор - lebensvoll
Дата добавления - 21.11.2018 в 15:08
dude Дата: Среда, 21.11.2018, 15:40 | Сообщение № 15
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 193
Репутация: 28 ±
Замечаний: 0% ±

2016
а он разве в другой ячейке?
К сообщению приложен файл: 5338787.xlsm (62.0 Kb)


Сообщение отредактировал dude - Среда, 21.11.2018, 15:40
 
Ответить
Сообщениеа он разве в другой ячейке?

Автор - dude
Дата добавления - 21.11.2018 в 15:40
lebensvoll Дата: Среда, 21.11.2018, 16:00 | Сообщение № 16
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
dude, наверное вы не правильно поняли...
Цитата
Дата: Среда, 21.11.2018, 10:45 | Сообщение № 9


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщениеdude, наверное вы не правильно поняли...
Цитата
Дата: Среда, 21.11.2018, 10:45 | Сообщение № 9

Автор - lebensvoll
Дата добавления - 21.11.2018 в 16:00
lebensvoll Дата: Пятница, 23.11.2018, 09:11 | Сообщение № 17
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
dude, доброе утро!!!
Скорее всего Ваша формула работает. Но вот не задача, в 2007 экселе нет данной функции ((((( "АГРЕГАТ"


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщениеdude, доброе утро!!!
Скорее всего Ваша формула работает. Но вот не задача, в 2007 экселе нет данной функции ((((( "АГРЕГАТ"

Автор - lebensvoll
Дата добавления - 23.11.2018 в 09:11
Pelena Дата: Воскресенье, 25.11.2018, 21:25 | Сообщение № 18
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
АГРЕГАТ можно заменить на МИН
Код
=СМЕЩ(Спецификация!E1;МИН(ЕСЛИ((B2>=Спецификация!B2:B23)*(B2<=Спецификация!C2:C23)*(Спецификация!D2:D23=B6);Спецификация!A2:A23));;СЧЁТЕСЛИМН(Спецификация!D2:D23;B6;Спецификация!B2:B23;"<="&B2;Спецификация!C2:C23;">="&B2);)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеАГРЕГАТ можно заменить на МИН
Код
=СМЕЩ(Спецификация!E1;МИН(ЕСЛИ((B2>=Спецификация!B2:B23)*(B2<=Спецификация!C2:C23)*(Спецификация!D2:D23=B6);Спецификация!A2:A23));;СЧЁТЕСЛИМН(Спецификация!D2:D23;B6;Спецификация!B2:B23;"<="&B2;Спецификация!C2:C23;">="&B2);)

Автор - Pelena
Дата добавления - 25.11.2018 в 21:25
lebensvoll Дата: Воскресенье, 25.11.2018, 22:36 | Сообщение № 19
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
Pelena, СПАСИБО ОГРОМНЕЙШЕЕ!!!
hands ГЕНИАЛЬНО!!!
Но.
Решил не указывать диапазоны (в виду того что на листе "Спецификация" умная таблица) сделал так:
Код
=СМЕЩ(Спецификация!E1;МИН(ЕСЛИ(($B$2>=Спецификация[Начало])*($B$2<=Спецификация[Окончание])*(Спецификация[Продукция]=$B$6);Спецификация[Счет]));;СЧЁТЕСЛИМН(Спецификация[Продукция];$B$6;Спецификация[Начало];"<="&$B$2;Спецификация[Окончание];">="&$B$2);)

Ответ #ССЫЛКА :'(
Просто данный список (на листе "Спецификация") будет в дальнейшим огроменным %)
Хотяя наверное ведь можно просто указать диапазон и до 2:10000 а умную таблицу просто продолжать и все


Кто бы ты ни был, мир в твоих руках
 
Ответить
СообщениеPelena, СПАСИБО ОГРОМНЕЙШЕЕ!!!
hands ГЕНИАЛЬНО!!!
Но.
Решил не указывать диапазоны (в виду того что на листе "Спецификация" умная таблица) сделал так:
Код
=СМЕЩ(Спецификация!E1;МИН(ЕСЛИ(($B$2>=Спецификация[Начало])*($B$2<=Спецификация[Окончание])*(Спецификация[Продукция]=$B$6);Спецификация[Счет]));;СЧЁТЕСЛИМН(Спецификация[Продукция];$B$6;Спецификация[Начало];"<="&$B$2;Спецификация[Окончание];">="&$B$2);)

Ответ #ССЫЛКА :'(
Просто данный список (на листе "Спецификация") будет в дальнейшим огроменным %)
Хотяя наверное ведь можно просто указать диапазон и до 2:10000 а умную таблицу просто продолжать и все

Автор - lebensvoll
Дата добавления - 25.11.2018 в 22:36
Pelena Дата: Воскресенье, 25.11.2018, 23:03 | Сообщение № 20
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Где ответ #ССЫЛКА? Мы вроде про проверку данных говорим, нет?


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеГде ответ #ССЫЛКА? Мы вроде про проверку данных говорим, нет?

Автор - Pelena
Дата добавления - 25.11.2018 в 23:03
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выпадающий список с несколькими условиями (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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