Подбор значения из двух списков

Дата: Четверг, 31.08.2017, 14:31 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 236

Репутация: 3 ±

Замечаний: 0% ±

Excel 2007

Добрый день уважаемые форумчане!

Помогите пожалуйста столкнулся с такой проблемой

как сделать так что бы при выборе значения из 1 списка во 2 список подставлялись значения автоматически из листа 2

Файл примера во вложении, знаю что делать нужно через Data->Data Validation

Формула =OFFSET но вот я могу только сделать для 1 конкретного столбца, а при изменении параметра не могу сделать, помогите пожалуйста

К сообщению приложен файл: example.xlsx (11.1 Kb)

buchlotnik

Дата: Четверг, 31.08.2017, 14:51 | Сообщение № 2

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

В диспетчер имён такую формулу:

Код

=СМЕЩ(Sheet2!$A$3;1;ПОИСКПОЗ($A2;Sheet2!$B$2:$H$2;0);3;1)

К сообщению приложен файл: 0384066.xlsx (11.2 Kb)

Сообщение отредактировал buchlotnik - Четверг, 31.08.2017, 14:51

Дата: Четверг, 31.08.2017, 14:56 | Сообщение № 3

Группа: Админы

Ранг: Местный житель

Сообщений: 16880

Репутация: 6593 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Цитата Gopronotmore, 31.08.2017 в 14:31, в сообщении № 1 ()

Формула =OFFSET

Не, формула ИНДЕКС
См. Диспетчер имен (Контрл F3)

К сообщению приложен файл: example-16-1.xlsx (11.3 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Дата: Четверг, 31.08.2017, 14:58 | Сообщение № 4

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 236

Репутация: 3 ±

Замечаний: 0% ±

Excel 2007

buchlotnik, спасибо огромное + к карме!

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

напрмер если в одном столбце будет 2 ячейки что бы в списке было тоже 2 ячейки, предполагаю что через COUNTIF но не знаю куда дописать

Дата: Четверг, 31.08.2017, 15:08 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 236

Репутация: 3 ±

Замечаний: 0% ±

Excel 2007

_Boroda_, Спасибо большое а теперь можно как-то что бы считалось количество выбранных вариантов от количества значений в поле через Count но как привязать к конкретному столбцу ?

Дата: Четверг, 31.08.2017, 15:25 | Сообщение № 6

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 236

Репутация: 3 ±

Замечаний: 0% ±

Excel 2007

_Boroda_, вот я прикрепил файл

COUNTIFS ? но как прописать критерии отбора для ?

Выбран вторник, и получается что только 2 значения, а отображается 3 в выборе как скрыть пустые ячейки что бы он не давал их выбрать ?

К сообщению приложен файл: example_2.xlsx (10.3 Kb)

Сообщение отредактировал Gopronotmore - Четверг, 31.08.2017, 15:26

Дата: Четверг, 31.08.2017, 16:10 | Сообщение № 7

Группа: Админы

Ранг: Местный житель

Сообщений: 16880

Репутация: 6593 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Тогда утренняя формула чуть усложнится

Код

=ИНДЕКС(Sheet2!$B$4:ИНДЕКС(Sheet2!$H:$H;3+МАКС(1;СЧЁТЗ(ИНДЕКС(Sheet2!$B$4:$H$6;;ПОИСКПОЗ(Sheet1!$A2;Даты;)))));;ПОИСКПОЗ(Sheet1!$A2;Даты;))

Обед сами

К сообщению приложен файл: example_2-1-1.xlsx (51.5 Kb)

Дата: Четверг, 31.08.2017, 16:30 | Сообщение № 8

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 236

Репутация: 3 ±

Замечаний: 0% ±

Excel 2007

_Boroda_, Спасибо большое! То что хотел, но ппц =) как понять логику формулы ?

Дата: Четверг, 31.08.2017, 17:14 | Сообщение № 9

Группа: Админы

Ранг: Местный житель

Сообщений: 16880

Репутация: 6593 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

1. ИНДЕКС(Sheet2!$B$4:$H$6;;ПОИСКПОЗ(Sheet1!$A2;Даты;)) - ищем ПОИСКПОЗом нашу дату в датах и ИНДЕКСОМ с нулевым параметром в строказ получаем строки 4-6 столбца с нужной датой
2. МАКС(1;СЧЁТЗ(п.1)) - считаем, сколько в диапазоне из п.1 реальных значений. Если нисколько, то все равно говорим, что одно
3. ИНДЕКС(Sheet2!$H:$H;3+п.2) - дает нам ссылку на ячейку столбца Н и строки 3+то, что мы нашли в п.2
4. ИНДЕКС(Sheet2!$B$4:п.3;;ПОИСКПОЗ(Sheet1!$A2;Даты;)) - полностью аналогично п.1, но мы получаем уже не строки 4-6, а строки 4-последняя заполненная в нужном столбце

Заполнение обязательно производить последовательно по строкам. Вот так нельзя
Е4 - 55
Е5 - пусто
Е6 - 66
А вот так льзя
Е4 - 55
Е5 - 66
Е6 - пусто

Дата: Пятница, 01.09.2017, 09:40 | Сообщение № 10

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 236

Репутация: 3 ±

Замечаний: 0% ±

Excel 2007

_Boroda_,

Спасибо вам большое! Теперь стало понятнее, и можно еще вопрос, вы с 1 раза ее вбили ? И у вас заработало или где-то проверяли ее ?

Просто я пытался вбить у меня писало ошибку, а узнать где конкретно ошибка я не смог, или вы вбиваете просто в ячейке формулу, а затем копируете ее ?

Спасибо, с ув.!

Дата: Пятница, 01.09.2017, 09:52 | Сообщение № 11

Группа: Админы

Ранг: Местный житель

Сообщений: 16880

Репутация: 6593 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Так сначала ж была формула из п.1 объяснялки, а потом мы ее в нее же вовнутрь всунули и чуть изменили. Поэтому да, сразу
Если неискабельную ошибку дает, то да, проще в ячейку формулу перенести и там проверять

Дата: Пятница, 01.09.2017, 09:59 | Сообщение № 12

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 236

Репутация: 3 ±

Замечаний: 0% ±

Excel 2007

_Boroda_,

У меня с синтаксисом проблемы по неопытности ) У меня с первого раза не получается как у вас пришлось переписывать формулу раза с 4 только =) думал есть универсальное решение проверки, что бы потом просто перенести готовую формулу ... В любом случае спасибо! Реально очень дельная штука, подчерпнул для себя нового в данном решении