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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск значения в двумерном массиве (матрице) по двум парамет - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск значения в двумерном массиве (матрице) по двум парамет (Формулы/Formulas)
Поиск значения в двумерном массиве (матрице) по двум парамет
hathory Дата: Вторник, 31.05.2016, 16:21 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Доброе время суток, господа.
Появилась проблема - есть двумерный массив, в котором нужно найти значение на пересечение строки и столбца (пример прилагаю). Поиск ведется по заголовкам столбцов и первым ячейкам строк, значения в них текстовые. Задача была решена при помощи функций ИНДЕКС и ПОИСКПОЗ, однако оказалось, что у ПОИСКПОЗ есть ограничения на количество символов (пример в файле), к сожалению это ограничение сводит на "нет" преимущества формулы, а сократить текстовую часть нет возможности, она должна находиться в неизменном виде.
Подскажите пожалуйста как можно реализовать поиск при описанных условиях, буду рад любым решениям и макросам и формулам.
Заранее спасибо
К сообщению приложен файл: 3351166.xlsx(36Kb)


hathory
 
Ответить
СообщениеДоброе время суток, господа.
Появилась проблема - есть двумерный массив, в котором нужно найти значение на пересечение строки и столбца (пример прилагаю). Поиск ведется по заголовкам столбцов и первым ячейкам строк, значения в них текстовые. Задача была решена при помощи функций ИНДЕКС и ПОИСКПОЗ, однако оказалось, что у ПОИСКПОЗ есть ограничения на количество символов (пример в файле), к сожалению это ограничение сводит на "нет" преимущества формулы, а сократить текстовую часть нет возможности, она должна находиться в неизменном виде.
Подскажите пожалуйста как можно реализовать поиск при описанных условиях, буду рад любым решениям и макросам и формулам.
Заранее спасибо

Автор - hathory
Дата добавления - 31.05.2016 в 16:21
_Boroda_ Дата: Вторник, 31.05.2016, 16:34 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 9377
Репутация: 3950 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=СУММПРОИЗВ((Матрица!A$3:A$56=B2)*(Матрица!B$2:BC$2=B2)*Матрица!$B$3:$BC$56)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Код
=СУММПРОИЗВ((Матрица!A$3:A$56=B2)*(Матрица!B$2:BC$2=B2)*Матрица!$B$3:$BC$56)

Автор - _Boroda_
Дата добавления - 31.05.2016 в 16:34
hathory Дата: Вторник, 31.05.2016, 16:55 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
=СУММПРОИЗВ((Матрица!A$3:A$56=B2)*(Матрица!B$2:BC$2=B2)*Матрица!$B$3:$BC$56)

Извините, уважаемый, я очень плохо знаю excel, Ваше решение работает. Спасибо. правда я пока не понял как это происходит. Объясните пожалуйста, в данной формуле "*" это умножение или слияние "И"?


hathory

Сообщение отредактировал hathory - Вторник, 31.05.2016, 17:06
 
Ответить
Сообщение
=СУММПРОИЗВ((Матрица!A$3:A$56=B2)*(Матрица!B$2:BC$2=B2)*Матрица!$B$3:$BC$56)

Извините, уважаемый, я очень плохо знаю excel, Ваше решение работает. Спасибо. правда я пока не понял как это происходит. Объясните пожалуйста, в данной формуле "*" это умножение или слияние "И"?

Автор - hathory
Дата добавления - 31.05.2016 в 16:55
_Boroda_ Дата: Вторник, 31.05.2016, 17:08 | Сообщение № 4
Группа: Модераторы
Ранг: Экселист
Сообщений: 9377
Репутация: 3950 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
но мне нужно не произведение массива, а двумерный поиск по массиву

А какая разница?
Смотрите - есть числовой массив, допустим, 4х4, и есть строка над ним и столбец слева от него. Может быть и внизу-справа, и в любом другом месте, главное (для этой конструкции), чтобы размеры строки совпадали с кол-вом столбцов массива и размеры столбца совпадали с кол-вом строк массива. Допустим, искомое на 3-й позиции. Мы пишем для строки Матрица!B$2:BC$2=B2, получаем строку ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ. Аналогично для столбца Матрица!A$3:A$56=B2 даст нам столбец ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ. Теперь умножаем полученное на наш числовой массив и получаем все нули, кроме того числа, которое находится на пересечении ИСТИН. (ЛОЖЬ * число = 0, ИСТИНА * число = число)
А затем все это складываем и получаем искомое число.
Смотрите файл

в данной формуле "*" это умножение или слияние "И"?
Умножение
К сообщению приложен файл: 3351166_1.xlsx(36Kb)


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

А какая разница?
Смотрите - есть числовой массив, допустим, 4х4, и есть строка над ним и столбец слева от него. Может быть и внизу-справа, и в любом другом месте, главное (для этой конструкции), чтобы размеры строки совпадали с кол-вом столбцов массива и размеры столбца совпадали с кол-вом строк массива. Допустим, искомое на 3-й позиции. Мы пишем для строки Матрица!B$2:BC$2=B2, получаем строку ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ. Аналогично для столбца Матрица!A$3:A$56=B2 даст нам столбец ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ. Теперь умножаем полученное на наш числовой массив и получаем все нули, кроме того числа, которое находится на пересечении ИСТИН. (ЛОЖЬ * число = 0, ИСТИНА * число = число)
А затем все это складываем и получаем искомое число.
Смотрите файл

в данной формуле "*" это умножение или слияние "И"?
Умножение

Автор - _Boroda_
Дата добавления - 31.05.2016 в 17:08
hathory Дата: Вторник, 31.05.2016, 17:25 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Boroda, еще раз спасибо, Ваше решение очень изящно, честно, я бы никогда не догадался использовать подобную конструкцию для поиска, косность ума, знаете ли.


hathory
 
Ответить
СообщениеBoroda, еще раз спасибо, Ваше решение очень изящно, честно, я бы никогда не догадался использовать подобную конструкцию для поиска, косность ума, знаете ли.

Автор - hathory
Дата добавления - 31.05.2016 в 17:25
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск значения в двумерном массиве (матрице) по двум парамет (Формулы/Formulas)
Страница 1 из 11
Поиск:

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