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

Вход

Регистрация

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

 

= Мир MS Excel/Функция ПОИСКПОЗ по двум столбцам и строке. - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Функция ПОИСКПОЗ по двум столбцам и строке. (Формулы/Formulas)
Функция ПОИСКПОЗ по двум столбцам и строке.
Anatoly88 Дата: Суббота, 11.06.2016, 15:57 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Добрый день! Ознакомился с функцией ПОИСКПОЗ. Получилось составить форму для нахождения значения по столбцу и строке. Но при более сложной задаче возникли проблемы.
Необходимо найти нужное значение по трем критериям. Возможно ли это? Чтобы значение нашлось по двум столбцам с данными и одной строке.

Есть длина, ширина, толщина. Нужно найти значение соответствующее этим трем критериям. Есть столбец "Длина" значения 500, 1000 и др. В столбце "Ширина" при длине 500, есть значения 100, 150, 200. Так же при следующей длине - 1000. Есть так же столбцы с шириной и значениями 30, 50, 70, 100. Всем этим критериям соответствует определенное значение со временем. Которое и нужно найти.

Возможно ли это при заданной таблице в таком виде или лучше разбить таблицу на несколько различных?
К сообщению приложен файл: 3230310.xlsx(10Kb)


Сообщение отредактировал Anatoly88 - Суббота, 11.06.2016, 16:05
 
Ответить
СообщениеДобрый день! Ознакомился с функцией ПОИСКПОЗ. Получилось составить форму для нахождения значения по столбцу и строке. Но при более сложной задаче возникли проблемы.
Необходимо найти нужное значение по трем критериям. Возможно ли это? Чтобы значение нашлось по двум столбцам с данными и одной строке.

Есть длина, ширина, толщина. Нужно найти значение соответствующее этим трем критериям. Есть столбец "Длина" значения 500, 1000 и др. В столбце "Ширина" при длине 500, есть значения 100, 150, 200. Так же при следующей длине - 1000. Есть так же столбцы с шириной и значениями 30, 50, 70, 100. Всем этим критериям соответствует определенное значение со временем. Которое и нужно найти.

Возможно ли это при заданной таблице в таком виде или лучше разбить таблицу на несколько различных?

Автор - Anatoly88
Дата добавления - 11.06.2016 в 15:57
Pelena Дата: Суббота, 11.06.2016, 16:08 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 9848
Репутация: 2254 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Здравствуйте.
Надо было ещё варианты исходных данных привести. Пока так
Код
=ИНДЕКС($C$6:$F$11;ПОИСКПОЗ(I3;$A$6:$A$11;0)+ПОИСКПОЗ(I4;$B$6:$B$8;0)-1;ПОИСКПОЗ(I5;$C$5:$F$5;0))

или
Код
=СУММПРОИЗВ($C$6:$F$11*($A$6:$A$11=I3)*($B$6:$B$11=I4)*($C$5:$F$5=I5))
К сообщению приложен файл: 1336678.xlsx(11Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Надо было ещё варианты исходных данных привести. Пока так
Код
=ИНДЕКС($C$6:$F$11;ПОИСКПОЗ(I3;$A$6:$A$11;0)+ПОИСКПОЗ(I4;$B$6:$B$8;0)-1;ПОИСКПОЗ(I5;$C$5:$F$5;0))

или
Код
=СУММПРОИЗВ($C$6:$F$11*($A$6:$A$11=I3)*($B$6:$B$11=I4)*($C$5:$F$5=I5))

Автор - Pelena
Дата добавления - 11.06.2016 в 16:08
Anatoly88 Дата: Суббота, 11.06.2016, 16:19 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо Вам большое.
 
Ответить
СообщениеСпасибо Вам большое.

Автор - Anatoly88
Дата добавления - 11.06.2016 в 16:19
Anatoly88 Дата: Суббота, 11.06.2016, 17:39 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Возник еще вопрос. Формула работает при точном сопоставлении значений. А как быть если нужно найти наиболее близкое значение в таблице по отношению к искомому?

Попробовал менять в формуле 0, -1, 1. но результат не являлся почему то верным.
К сообщению приложен файл: 6315338.xlsx(11Kb)
 
Ответить
СообщениеВозник еще вопрос. Формула работает при точном сопоставлении значений. А как быть если нужно найти наиболее близкое значение в таблице по отношению к искомому?

Попробовал менять в формуле 0, -1, 1. но результат не являлся почему то верным.

Автор - Anatoly88
Дата добавления - 11.06.2016 в 17:39
Pelena Дата: Суббота, 11.06.2016, 18:49 | Сообщение № 5
Группа: Модераторы
Ранг: Экселист
Сообщений: 9848
Репутация: 2254 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Брать ближайшее или ближайшее большее? Сколько должно получиться в данном примере?


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеБрать ближайшее или ближайшее большее? Сколько должно получиться в данном примере?

Автор - Pelena
Дата добавления - 11.06.2016 в 18:49
jakim Дата: Суббота, 11.06.2016, 20:33 | Сообщение № 6
Группа: Друзья
Ранг: Ветеран
Сообщений: 679
Репутация: 172 ±
Замечаний: 0% ±

Попробуйте такой вариант.
К сообщению приложен файл: of_6315338.xlsx(11Kb)
 
Ответить
Сообщение
Попробуйте такой вариант.

Автор - jakim
Дата добавления - 11.06.2016 в 20:33
Pelena Дата: Воскресенье, 12.06.2016, 13:08 | Сообщение № 7
Группа: Модераторы
Ранг: Экселист
Сообщений: 9848
Репутация: 2254 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Ответа на свои вопросы не дождалась.
Попробуйте такой вариант. Формула массива
Код
=СУММ($C$6:$F$11*(ABS($A$6:$A$11-I3)=МИН(ABS($A$6:$A$11-I3)))*(ABS($B$6:$B$11-I4)=МИН(ABS($B$6:$B$11-I4)))*(ABS($C$5:$F$5-I5)=МИН(ABS($C$5:$F$5-I5))))
К сообщению приложен файл: 1758246.xlsx(11Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеОтвета на свои вопросы не дождалась.
Попробуйте такой вариант. Формула массива
Код
=СУММ($C$6:$F$11*(ABS($A$6:$A$11-I3)=МИН(ABS($A$6:$A$11-I3)))*(ABS($B$6:$B$11-I4)=МИН(ABS($B$6:$B$11-I4)))*(ABS($C$5:$F$5-I5)=МИН(ABS($C$5:$F$5-I5))))

Автор - Pelena
Дата добавления - 12.06.2016 в 13:08
Anatoly88 Дата: Воскресенье, 12.06.2016, 13:20 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
С нахождением наиболее близкого значения к заданному с вашей помощью я разобрался. Возник последующи вопрос - как соединить несколько обширных формул в одну??? Чтобы не выскакивала ошибка о том, что много аргументов.

Код

ЕСЛИ($D$4=$D$52;ИНДЕКС('[Изготовление.xlsx]Операция2'!$C$5:$L$40;ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция2'!$A$5:$A$40-$A$4));ABS('[Изготовление.xlsx]Операция2'!$A$5:$A$40-$A$4);0)+ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция2'!$B$5:$B$7-$B$4));ABS('[Изготовление.xlsx]Операция2'!$B$5:$B$7-$B$4);0)-1;ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция2'!$C$4:$L$4-$C$4));ABS('[Изготовление.xlsx]Операция2'!$C$4:$L$4-$C$4);0)))


Код
ЕСЛИ($D$4=$D$53;ИНДЕКС('[Изготовление.xlsx]Операция3'!$C$5:$L$60;ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция3'!$A$5:$A$60-$A$4));ABS('[Изготовление.xlsx]Операция3'!$A$5:$A$60-$A$4);0)+ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция3'!$B$5:$B$8-$B$4));ABS('[Изготовление.xlsx]Операция3'!$B$5:$B$8-$B$4);0)-1;ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция3'!$C$4:$L$4-$C$4));ABS('[Изготовление.xlsx]Операция3'!$C$4:$L$4-$C$4);0)))


Код

ЕСЛИ(И(D4=D54;C5<='[Изготовление.xlsx]Операция4'!A5);ИНДЕКС('[Изготовление.xlsx]Операция4'!$B$5:$P$5;ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция4'!$B$4:$P$4-C4));ABS('[Изготовление.xlsx]Операция4'!$B$4:$P$4-C4);0));ЕСЛИ(И(D4=D54;C5>='[Изготовление.xlsx]Операция4'!A6);ИНДЕКС('[Изготовление.xlsx]Операция4'!$B$6:$P$6;ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция4'!$B$4:$P$4-C4));ABS('[Изготовление.xlsx]Операция4'!$B$4:$P$4-C4);0))))

[moder]Не надо обсуждать один вопрос в разных темах. Если эта тема исчерпана, я её закрою. Ответ на первоначальный вопрос получен?[/moder]


Сообщение отредактировал Pelena - Воскресенье, 12.06.2016, 13:28
 
Ответить
СообщениеС нахождением наиболее близкого значения к заданному с вашей помощью я разобрался. Возник последующи вопрос - как соединить несколько обширных формул в одну??? Чтобы не выскакивала ошибка о том, что много аргументов.

Код

ЕСЛИ($D$4=$D$52;ИНДЕКС('[Изготовление.xlsx]Операция2'!$C$5:$L$40;ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция2'!$A$5:$A$40-$A$4));ABS('[Изготовление.xlsx]Операция2'!$A$5:$A$40-$A$4);0)+ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция2'!$B$5:$B$7-$B$4));ABS('[Изготовление.xlsx]Операция2'!$B$5:$B$7-$B$4);0)-1;ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция2'!$C$4:$L$4-$C$4));ABS('[Изготовление.xlsx]Операция2'!$C$4:$L$4-$C$4);0)))


Код
ЕСЛИ($D$4=$D$53;ИНДЕКС('[Изготовление.xlsx]Операция3'!$C$5:$L$60;ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция3'!$A$5:$A$60-$A$4));ABS('[Изготовление.xlsx]Операция3'!$A$5:$A$60-$A$4);0)+ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция3'!$B$5:$B$8-$B$4));ABS('[Изготовление.xlsx]Операция3'!$B$5:$B$8-$B$4);0)-1;ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция3'!$C$4:$L$4-$C$4));ABS('[Изготовление.xlsx]Операция3'!$C$4:$L$4-$C$4);0)))


Код

ЕСЛИ(И(D4=D54;C5<='[Изготовление.xlsx]Операция4'!A5);ИНДЕКС('[Изготовление.xlsx]Операция4'!$B$5:$P$5;ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция4'!$B$4:$P$4-C4));ABS('[Изготовление.xlsx]Операция4'!$B$4:$P$4-C4);0));ЕСЛИ(И(D4=D54;C5>='[Изготовление.xlsx]Операция4'!A6);ИНДЕКС('[Изготовление.xlsx]Операция4'!$B$6:$P$6;ПОИСКПОЗ(МИН(ABS('[Изготовление.xlsx]Операция4'!$B$4:$P$4-C4));ABS('[Изготовление.xlsx]Операция4'!$B$4:$P$4-C4);0))))

[moder]Не надо обсуждать один вопрос в разных темах. Если эта тема исчерпана, я её закрою. Ответ на первоначальный вопрос получен?[/moder]

Автор - Anatoly88
Дата добавления - 12.06.2016 в 13:20
Anatoly88 Дата: Воскресенье, 12.06.2016, 13:29 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Да получен.
 
Ответить
СообщениеДа получен.

Автор - Anatoly88
Дата добавления - 12.06.2016 в 13:29
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Функция ПОИСКПОЗ по двум столбцам и строке. (Формулы/Formulas)
Страница 1 из 11
Поиск:

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