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

Вход

Регистрация

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

 

= Мир MS Excel/подтянуть ВПРом несколько значений - Мир MS Excel

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

Excel 2007
Здравствуйте, описание задачи в приложенном файле. Возможно решить формулами? Или только макросом?
К сообщению приложен файл: qwerty.xlsx (9.0 Kb)
 
Ответить
СообщениеЗдравствуйте, описание задачи в приложенном файле. Возможно решить формулами? Или только макросом?

Автор - SergeyKorotun
Дата добавления - 25.03.2015 в 11:12
_Boroda_ Дата: Среда, 25.03.2015, 11:31 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Вариант 1, немассивный. только значения снизу вверх ставятся
Код
=ЕСЛИОШИБКА(ПРОСМОТР(;-1/($H$2:$H$8=$A2)/ЕНД(ПОИСКПОЗ($I$2:$I$8;$A2:A2;));$I$2:$I$8);"")

вариант 2, массивный
Код
=ЕСЛИОШИБКА(ИНДЕКС($I$2:$I$8;НАИМЕНЬШИЙ(ЕСЛИ($H$2:$H$8=$A2;СТРОКА(H$1:H$7));СТОЛБЕЦ(A2)));"")
К сообщению приложен файл: qwerty_2.xlsx (10.5 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеВариант 1, немассивный. только значения снизу вверх ставятся
Код
=ЕСЛИОШИБКА(ПРОСМОТР(;-1/($H$2:$H$8=$A2)/ЕНД(ПОИСКПОЗ($I$2:$I$8;$A2:A2;));$I$2:$I$8);"")

вариант 2, массивный
Код
=ЕСЛИОШИБКА(ИНДЕКС($I$2:$I$8;НАИМЕНЬШИЙ(ЕСЛИ($H$2:$H$8=$A2;СТРОКА(H$1:H$7));СТОЛБЕЦ(A2)));"")

Автор - _Boroda_
Дата добавления - 25.03.2015 в 11:31
Pelena Дата: Среда, 25.03.2015, 12:15 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 19166
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Обязательно по горизонтали располагать значения? Если нет, то вариант со сводной
К сообщению приложен файл: 8248009.xlsx (13.0 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеОбязательно по горизонтали располагать значения? Если нет, то вариант со сводной

Автор - Pelena
Дата добавления - 25.03.2015 в 12:15
SergeyKorotun Дата: Среда, 25.03.2015, 12:48 | Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, можете кратко описать первую формулу. Хотелось бы осознанно и в других задачах использовать такое решение.

В описании функции ПРОСМОТР требуется упорядоченность просматриваемого вектора. Перед вставкой формул я отсортирую таблицу по возрастанию фамилий. Но поскольку много фамилий в исходном списке с ошибками, то им не будет найдено соответствующее id. Во многих фамилиях ошибки очевидны, и после внесения правок список стает неупорядоченным. А вносить правки после вставки формул удобнее, т.к. не нужно перечитывать те фамилии, id которых заполнился с помощью формул. Можно отключить применение формул, внести правки и снова включить, но в неправильной фамилии может быть несколько вариантов правильной и сразу не будет видно ту ли именно фамилию получили.

Во второй формуле наверно А2 нужно заменить на А19
 
Ответить
Сообщение_Boroda_, можете кратко описать первую формулу. Хотелось бы осознанно и в других задачах использовать такое решение.

В описании функции ПРОСМОТР требуется упорядоченность просматриваемого вектора. Перед вставкой формул я отсортирую таблицу по возрастанию фамилий. Но поскольку много фамилий в исходном списке с ошибками, то им не будет найдено соответствующее id. Во многих фамилиях ошибки очевидны, и после внесения правок список стает неупорядоченным. А вносить правки после вставки формул удобнее, т.к. не нужно перечитывать те фамилии, id которых заполнился с помощью формул. Можно отключить применение формул, внести правки и снова включить, но в неправильной фамилии может быть несколько вариантов правильной и сразу не будет видно ту ли именно фамилию получили.

Во второй формуле наверно А2 нужно заменить на А19

Автор - SergeyKorotun
Дата добавления - 25.03.2015 в 12:48
krosav4ig Дата: Среда, 25.03.2015, 13:12 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 2346
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
если добавить доп.столбец, то можно сводной и по горизонтали расположить
К сообщению приложен файл: qwerty-1-.xlsx (14.2 Kb)


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщениеесли добавить доп.столбец, то можно сводной и по горизонтали расположить

Автор - krosav4ig
Дата добавления - 25.03.2015 в 13:12
SergeyKorotun Дата: Среда, 25.03.2015, 13:19 | Сообщение № 6
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
Pelena, любой вариант решения подойдет. Но вы наверно неверно поняли условие задачи.
Есть большая база данных (зеленая таблица, 30 000 записей). Одно и то же ФИО там встречается много раз, но это разные люди (у каждого уникальное id).
Дали список из 1500 фамилий (желтая таблица). В нее надо для каждого ФИО из зеленой таблицы подтянуть все id.
 
Ответить
СообщениеPelena, любой вариант решения подойдет. Но вы наверно неверно поняли условие задачи.
Есть большая база данных (зеленая таблица, 30 000 записей). Одно и то же ФИО там встречается много раз, но это разные люди (у каждого уникальное id).
Дали список из 1500 фамилий (желтая таблица). В нее надо для каждого ФИО из зеленой таблицы подтянуть все id.

Автор - SergeyKorotun
Дата добавления - 25.03.2015 в 13:19
SergeyKorotun Дата: Среда, 25.03.2015, 13:35 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
krosav4ig, я наверно плохо сформулировал условие задачи. Переформулировал в предыдущем сообщении.
Когда то я задавал существует ли в екселе формулы наподобии ранжирующих в оракле, ответ тогда не получил. А вы сегодня аналогичную функцию применили: счетели.

PS решение в столбик будет более удобным, т.к. потом по id нужно будет подтягивать и другие данные.
 
Ответить
Сообщениеkrosav4ig, я наверно плохо сформулировал условие задачи. Переформулировал в предыдущем сообщении.
Когда то я задавал существует ли в екселе формулы наподобии ранжирующих в оракле, ответ тогда не получил. А вы сегодня аналогичную функцию применили: счетели.

PS решение в столбик будет более удобным, т.к. потом по id нужно будет подтягивать и другие данные.

Автор - SergeyKorotun
Дата добавления - 25.03.2015 в 13:35
VEKTORVSFREEMAN Дата: Среда, 25.03.2015, 13:47 | Сообщение № 8
Группа: Друзья
Ранг: Ветеран
Сообщений: 772
Репутация: 130 ±
Замечаний: 0% ±

MS Office Excel 2010
Но вы наверно неверно поняли условие задачи

а где указано
Есть большая база данных (зеленая таблица, 30 000 записей). Одно и то же ФИО там встречается много раз, но это разные люди (у каждого уникальное id).
Дали список из 1500 фамилий (желтая таблица). В нее надо для каждого ФИО из зеленой таблицы подтянуть все id.

посмотрите тут


"Опыт - это то, что получаешь, не получив того, что хотел"
 
Ответить
Сообщение
Но вы наверно неверно поняли условие задачи

а где указано
Есть большая база данных (зеленая таблица, 30 000 записей). Одно и то же ФИО там встречается много раз, но это разные люди (у каждого уникальное id).
Дали список из 1500 фамилий (желтая таблица). В нее надо для каждого ФИО из зеленой таблицы подтянуть все id.

посмотрите тут

Автор - VEKTORVSFREEMAN
Дата добавления - 25.03.2015 в 13:47
SergeyKorotun Дата: Среда, 25.03.2015, 13:59 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
а где указано

Вот здесь:
описание задачи в приложенном файле
 
Ответить
Сообщение
а где указано

Вот здесь:
описание задачи в приложенном файле

Автор - SergeyKorotun
Дата добавления - 25.03.2015 в 13:59
VEKTORVSFREEMAN Дата: Среда, 25.03.2015, 14:07 | Сообщение № 10
Группа: Друзья
Ранг: Ветеран
Сообщений: 772
Репутация: 130 ±
Замечаний: 0% ±

MS Office Excel 2010
может у меня со зрением плохо, но в файле всё описано "в двух словах", а хотите ... (ну в общем, я уже цитировал выше)
К сообщению приложен файл: 2078887.jpg (22.3 Kb)


"Опыт - это то, что получаешь, не получив того, что хотел"
 
Ответить
Сообщениеможет у меня со зрением плохо, но в файле всё описано "в двух словах", а хотите ... (ну в общем, я уже цитировал выше)

Автор - VEKTORVSFREEMAN
Дата добавления - 25.03.2015 в 14:07
_Boroda_ Дата: Среда, 25.03.2015, 14:08 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
можете кратко описать первую формулу

Объяснялка для ПРОСМОТР
Давайте посмотрим на несколько выдержек из справки:
1. "Если функции ПРОСМОТР не удается найти искомое_значение, то в просматриваемом_векторе выбирается наибольшее значение, которое меньше искомого_значения или равно ему.";
2. "Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания:".

Теперь рассмотри кусок формулы -1/($H$2:$H$8=$A2)/ЕНД(ПОИСКПОЗ($I$2:$I$8;$A2:A2;)).
а) $H$2:$H$8=$A2 - если равно, то ИСТИНА, если не равно, то ЛОЖЬ.
б) ПОИСКПОЗ(...) - если нашел то, что слева в заданном массиве ID, то какое-то число, если не нашел - ошибка Н/Д. ЕНД преобразует полученное ПОИСКПОЗом в ИСТИНА, если ПОИСКПОЗ дает Н/Д и ЛОЖЬ в остальных случаях.
в) минус единица, деленная на а) и деленная на б) даст нам или ошибки деления на ноль (если хотя бы что-то из а) или б) равно ЛОЖЬ), или какие-то отрицательные числа. Нам сейчас без разницы, что за числа, нам важно, что они отрицательны.
г) ПРОСМОТР(;... можно перевести на нормальный язык как ПРОСМОТР(0;..., следовательно, мы ищем ноль в массиве из пункта в) - массиве из отрицательных чисел и ошибок. Ошибки не считаются вообще в данном случае, следовательно, имеем массив отрицательных чисел. Ноль мы там никак не сможем найти - он заведомо больше любого отрицательного числа.
А теперь смотрим на цитату 2. - по умолчанию полагается, что самое последнее значение - это самое большое значение (массив-то упорядочен по возрастанию).
Теперь смотрим на цитату 1. и п. г) и делаем вывод, что нам покажут то значение, которое находится самым последним из удовлетворяющих условиям пунктов а) и б).


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

Объяснялка для ПРОСМОТР
Давайте посмотрим на несколько выдержек из справки:
1. "Если функции ПРОСМОТР не удается найти искомое_значение, то в просматриваемом_векторе выбирается наибольшее значение, которое меньше искомого_значения или равно ему.";
2. "Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания:".

Теперь рассмотри кусок формулы -1/($H$2:$H$8=$A2)/ЕНД(ПОИСКПОЗ($I$2:$I$8;$A2:A2;)).
а) $H$2:$H$8=$A2 - если равно, то ИСТИНА, если не равно, то ЛОЖЬ.
б) ПОИСКПОЗ(...) - если нашел то, что слева в заданном массиве ID, то какое-то число, если не нашел - ошибка Н/Д. ЕНД преобразует полученное ПОИСКПОЗом в ИСТИНА, если ПОИСКПОЗ дает Н/Д и ЛОЖЬ в остальных случаях.
в) минус единица, деленная на а) и деленная на б) даст нам или ошибки деления на ноль (если хотя бы что-то из а) или б) равно ЛОЖЬ), или какие-то отрицательные числа. Нам сейчас без разницы, что за числа, нам важно, что они отрицательны.
г) ПРОСМОТР(;... можно перевести на нормальный язык как ПРОСМОТР(0;..., следовательно, мы ищем ноль в массиве из пункта в) - массиве из отрицательных чисел и ошибок. Ошибки не считаются вообще в данном случае, следовательно, имеем массив отрицательных чисел. Ноль мы там никак не сможем найти - он заведомо больше любого отрицательного числа.
А теперь смотрим на цитату 2. - по умолчанию полагается, что самое последнее значение - это самое большое значение (массив-то упорядочен по возрастанию).
Теперь смотрим на цитату 1. и п. г) и делаем вывод, что нам покажут то значение, которое находится самым последним из удовлетворяющих условиям пунктов а) и б).

Автор - _Boroda_
Дата добавления - 25.03.2015 в 14:08
SergeyKorotun Дата: Среда, 25.03.2015, 15:20 | Сообщение № 12
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
может у меня со зрением плохо, но в файле всё описано "в двух словах", а хотите ... (ну в общем, я уже цитировал выше)

Но _Boroda_ все понял и два варианта решения предоставил.

Давайте посмотрим на несколько выдержек из справки:
Распечатал, вечером дома почитаю, надеюсь разберусь.
В будущем буду использовать ваше решение.
А пока, используя вариант krosav4ig, создам сводную таблицу из справочной базы (зеленой, для каждого ФИО получу в соседних колонках все ID), а затем несколькими простыми ВПРами подтяну все ID в желтую таблицу.
 
Ответить
Сообщение
может у меня со зрением плохо, но в файле всё описано "в двух словах", а хотите ... (ну в общем, я уже цитировал выше)

Но _Boroda_ все понял и два варианта решения предоставил.

Давайте посмотрим на несколько выдержек из справки:
Распечатал, вечером дома почитаю, надеюсь разберусь.
В будущем буду использовать ваше решение.
А пока, используя вариант krosav4ig, создам сводную таблицу из справочной базы (зеленой, для каждого ФИО получу в соседних колонках все ID), а затем несколькими простыми ВПРами подтяну все ID в желтую таблицу.

Автор - SergeyKorotun
Дата добавления - 25.03.2015 в 15:20
SergeyKorotun Дата: Среда, 25.03.2015, 16:31 | Сообщение № 13
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
Подскажите, в krosav4ig в сводной таблице в названиях строк стоят FIO, в названиях столбцов - №, а значения заполнены значениями ID. При этом в списке полей перед полем ID тычка стоит, а в окне "значения" этого поля нет.
Когда я в своей таблице ставлю тычку перед этим полем, оно попадает в окно "названия строк", если перетаскиваю его в окно значения и выставляю параметр "сумма", то у меня в сводной таблице отображается количество ID, а не их значения, если же я снимаю тычку перед полем ID, то оно исчезает с окна "значения" и данные в сводной таблице исчезают.

Если создаю сводную на таблице krosav4ig, то все выходит автоматом.
Свою таблицу приложить не могу.
 
Ответить
СообщениеПодскажите, в krosav4ig в сводной таблице в названиях строк стоят FIO, в названиях столбцов - №, а значения заполнены значениями ID. При этом в списке полей перед полем ID тычка стоит, а в окне "значения" этого поля нет.
Когда я в своей таблице ставлю тычку перед этим полем, оно попадает в окно "названия строк", если перетаскиваю его в окно значения и выставляю параметр "сумма", то у меня в сводной таблице отображается количество ID, а не их значения, если же я снимаю тычку перед полем ID, то оно исчезает с окна "значения" и данные в сводной таблице исчезают.

Если создаю сводную на таблице krosav4ig, то все выходит автоматом.
Свою таблицу приложить не могу.

Автор - SergeyKorotun
Дата добавления - 25.03.2015 в 16:31
Hugo Дата: Среда, 25.03.2015, 17:54 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3250
Репутация: 707 ±
Замечаний: 0% ±

2019
Код
=VLOOKUPCOUPLE($H$2:$I$8,1,A19,2,", ")

Ищите код на форуме - это UDF


excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
Сообщение
Код
=VLOOKUPCOUPLE($H$2:$I$8,1,A19,2,", ")

Ищите код на форуме - это UDF

Автор - Hugo
Дата добавления - 25.03.2015 в 17:54
SergeyKorotun Дата: Среда, 25.03.2015, 21:50 | Сообщение № 15
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
Умножил в реальной таблице значения ID через специальную вставку на 1 и сводная таблица заполнилась значениями ID, а не нулями. Но ведущие нули потерял.
Вопрос в приложенном файле.
[moder]Это уже совсем другой вопрос, значит, другая тема[/moder]
К сообщению приложен файл: qwerty2.xlsx (12.6 Kb)


Сообщение отредактировал Pelena - Среда, 25.03.2015, 22:02
 
Ответить
СообщениеУмножил в реальной таблице значения ID через специальную вставку на 1 и сводная таблица заполнилась значениями ID, а не нулями. Но ведущие нули потерял.
Вопрос в приложенном файле.
[moder]Это уже совсем другой вопрос, значит, другая тема[/moder]

Автор - SergeyKorotun
Дата добавления - 25.03.2015 в 21:50
SergeyKorotun Дата: Среда, 25.03.2015, 22:33 | Сообщение № 16
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
Ну почему же другая? Я применил решение, предложенное krosav4ig, но не на приложенном файле, а на реальном, и получил не то, что должно быть.
Но если вы будете настаивать, мне не трудно создать тему, только в этом случае помогающим придется снова разбираться с нуля.
 
Ответить
СообщениеНу почему же другая? Я применил решение, предложенное krosav4ig, но не на приложенном файле, а на реальном, и получил не то, что должно быть.
Но если вы будете настаивать, мне не трудно создать тему, только в этом случае помогающим придется снова разбираться с нуля.

Автор - SergeyKorotun
Дата добавления - 25.03.2015 в 22:33
Serge_007 Дата: Суббота, 07.11.2015, 22:04 | Сообщение № 17
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

Excel 2016
ведущие нули потерял
Это уже совсем другой вопрос, значит, другая тема
Верно, это другая тема

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


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
ведущие нули потерял
Это уже совсем другой вопрос, значит, другая тема
Верно, это другая тема

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

Автор - Serge_007
Дата добавления - 07.11.2015 в 22:04
Мир MS Excel » Вопросы и решения » Вопросы по Excel » подтянуть ВПРом несколько значений (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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