Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Поиск значения в таблице и возврат значения соседней ячейки
 
#1
24.04.2013 14:16:29
Добрый день!

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

В принципе пока что решаю это несколькими вложенными ЕСЛИОШИБКА с ВПР внутри для каждой пары столбцов, но хотелось бы знать, есть ли функция, которая способна искать значение внутри таблицы из многих строк и столбцов и возвращать значение со смещением (типа ВПР только не по одному столбцу - первому, а в любом выбранном диапазоне строк и столбцов)

Пример во вложении.

Спасибо вам за помощь!
Изменено: thalamix - 24.04.2013 18:40:04
 
 
 
#2
24.04.2013 15:22:43
Если все значения уникальны, можно так
Код
=ИНДЕКС(A1:F5;СУММПРОИЗВ(СТРОКА(A1:F5)*(A1:F5=A9));СУММПРОИЗВ(СТОЛБЕЦ(A1:F5)*(A1:F5=A9))+1)
 
 
 
#3
24.04.2013 15:32:55
Спасибо!

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

Иначе будь такая функция, назовем ее ХПОИСК, то выглядело бы как-то так

Код
=ИНДЕКС(таблица; ХПОИСК(номер_строки; номер_столбца+1)


Эх, мечты, мечты  :D  Вообще странно, что нету функции произвольного поиска, у меня Ctrl+F без продыху пашет.
 
 
 
#4
24.04.2013 15:36:07
функции листа нет. В VBA есть Find.
 
 
 
#5
24.04.2013 15:36:36
Макрос пойдет?

См приложение.
 
 
 
#6
24.04.2013 15:41:19
Цитата
...странно, что нету функции произвольного поиска
Есть, но доморощенная (UDF)
 
 
 
#7
24.04.2013 15:44:35
=МАКС(ЕСЛИ(A9=A1:F5;B1:G5))
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#8
24.04.2013 16:20:16
МашА, спасибо за макрос! Но в связи с поголовной боязнью (включая меня ^^) макросов пользуемся только встроенными функциями, макросы живут только во время одной сессии и не сохраняются, в основном для уменьшения рутины: выполнения группы однообразных операций с данными листа.
 
 
 
#9
24.04.2013 16:21:59
Цитата
С.М. пишет:
Есть, но доморощенная (UDF)
У меня =ВПРП(A9;$A$1:$F$5) не сработало - пишет #ИМЯ? (версия программы 2007 сервис пак 1)

Ввел вручную - сработало! Что за функция ВПРП? Гугл не в курсе вроде.

Разобрался - это макрос функция, определяемая пользователем :) К сожалению не подходит, т.к. пользователь не только я, и необходимо пользоваться только встроенными стандартными функциями.
Изменено: thalamix - 25.04.2013 08:43:41 (Разобрался с UDF)
 
 
 
#10
24.04.2013 16:24:48
Почему Владимира игнорируете? Идеальный вариант - только нужно вводить как формулу массива.
 
 
 
#11
24.04.2013 16:26:30
Вариант Владимира сработал при Ctrl+Shift+Enter. Очень интересное для меня решение. Причем вот так ЕСЛИ(A9=A1:F5;B1:G5) возвращает ЛОЖЬ.

Объясните дундуку, как это колдунство работает, и почему МАКС?
 
 
 
#12
24.04.2013 16:28:21
Пока Владимира нет - оно возвращает массив, поэтому без МАКС видите только одно первое значение.
А с МАКС выбранное.


Можно вместо MAX писать MIN или AVERAGE - смотря по задаче :)
Изменено: Hugo - 24.04.2013 16:31:27
 
 
 
#13
24.04.2013 16:28:55
Цитата
Hugo пишет:
Почему Владимира игнорируете? Идеальный вариант - только нужно вводить как формулу массива.
Ничуть не игнорирую - просто отвечал в хронологическом порядке. Согласен с вами, формула весьма простая и работает. Только как, мне не понятно? О_о
 
 
 
#14
24.04.2013 16:30:30
Цитата
.. без МАКС видите только одно первое значение.
А ЕСЛИ не понимает {} ?
 
 
 
#15
24.04.2013 16:33:13
Принимает ЕСЛИ массив - введите ЕСЛИ(A9=A1:F5;B1:G5)  сразу в диапазон 5х6 - увидите кучу ЛОЖЬ и одно число 31.
Изменено: Hugo - 24.04.2013 16:33:43
 
 
 
#16
24.04.2013 16:37:57
Прошу прощения за свою тупость - как понять "диапазон 5х6"? Догнал, ввести = протянуть формулу на 6 столбцов и 5 строк, поставив вместо А9  - $A$9.

Как я это понимаю - приведенная Владимиром функция вычисляет максимум из массива, подходящему согласно условия в ЕСЛИ, то есть максимум из одного числа. По логике строителя коммунизма если одно число, зачем ему искать максимум из себя, и должно бы сработать и без МАКС,

...а не работает. Вот до чего мой ограниченный разум не доходит.  :)
Изменено: thalamix - 24.04.2013 16:40:27 (Сам допер :))
 
 
 
#17
24.04.2013 16:45:12
Про 5х6 - нужно немного иначе делать - выделяем диапазон размером с Ваши данные, затем В СТРОКЕ ФОРМУЛ пишем =ЕСЛИ(A9=A1:F5;B1:G5) Ctrl+Shift+Enter
Получаем массив значений, из которого и выбирает нужное МАКС().
Только она выбирает не из диапазона, а из виртуального массива, который возвращает ЕСЛИ().
Изменено: Hugo - 24.04.2013 16:47:03
 
 
 
#18
24.04.2013 16:47:43
Цитата
Hugo пишет:
Про 5х6 - нужно немного иначе делать

Сработало аналогично. Я так понял МАКС это делает виртуально. Надо будет запомнить.

Что интересно, половину полезных вещей с форума, включая эту, в справке не найдешь. Эт наверно чтоб учебники продавались :)

Всем спасибо еще раз!
 
 
 
#19
08.10.2019 21:15:55
Владимир, добрый день!
Подскажите пожалуйста, как должна выглядеть формула, если требуется суммировать несколько таких значений из диапазона ячеек.

Сама задача: на одном листе Excel есть повторяющиеся по структуре таблицы (ежегодный бюджет). Необходимо вычислить среднее значение за несколько лет, суммировав значения по месяцам. Ячейка с названием расположена слева от ячейки со значением.
Т.е. в первом месяце ячейка с названием A1, а значение в ячейке B1. Ячейка C1 пустая, а потом снова ячейка с названием D1, а значение в ячейке E1. И так 12 раз в году. 3 года.
 
 
 
#20
08.10.2019 21:57:46
Вопрос не по теме
 
 
 
Страницы: 1
Читают тему
Наверх