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

Вход

Регистрация

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

 

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

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

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

Вопрос! У меня есть таблица в ней 3 параметра, как сделать подстановку по 3 параметрам ? Параметры в желтых ячейках должны меняться, соответственно и число SUMIFS не подходит VLOOKUP+MATCH можно выбрать только 2 параметра, подскажите пожалуйста всю голову сломал!
К сообщению приложен файл: Example.xlsx (7.6 Kb)
 
Ответить
СообщениеДобрый день форумчане!

Вопрос! У меня есть таблица в ней 3 параметра, как сделать подстановку по 3 параметрам ? Параметры в желтых ячейках должны меняться, соответственно и число SUMIFS не подходит VLOOKUP+MATCH можно выбрать только 2 параметра, подскажите пожалуйста всю голову сломал!

Автор - Gopronotmore
Дата добавления - 05.09.2016 в 11:33
buchlotnik Дата: Понедельник, 05.09.2016, 11:48 | Сообщение № 2
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
Gopronotmore, а каким должен быть результат для сочетания f и 563? или они парой всегда идут?
 
Ответить
СообщениеGopronotmore, а каким должен быть результат для сочетания f и 563? или они парой всегда идут?

Автор - buchlotnik
Дата добавления - 05.09.2016 в 11:48
китин Дата: Понедельник, 05.09.2016, 11:50 | Сообщение № 3
Группа: Модераторы
Ранг: Экселист
Сообщений: 7013
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
и вам Доброго к примеру вот так
Код
=СУММПРОИЗВ($C$2:$E$4*($A$2:$A$4=$H$8)*($B$2:$B$4=$I$8)*($C$1:$E$1=$J$7))
или так (массивная)
Код
=ИНДЕКС($C$2:$E$4;ПОИСКПОЗ(H8&I8;A2:A4&B2:B4;0);ПОИСКПОЗ(J7;$C$1:$E$1;0))

[p.s.]а вообщ то Миша правильно спросил
Цитата
а каким должен быть результат для сочетания f и 563

а можно и так
Код
=ПРОСМОТР(1;1/((H8=A2:A4)*(I8=B2:B4));ИНДЕКС(C2:E4;;ПОИСКПОЗ(J7;C1:E1;0)))
К сообщению приложен файл: 23232323232.xlsx (8.6 Kb)


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852


Сообщение отредактировал китин - Понедельник, 05.09.2016, 11:53
 
Ответить
Сообщениеи вам Доброго к примеру вот так
Код
=СУММПРОИЗВ($C$2:$E$4*($A$2:$A$4=$H$8)*($B$2:$B$4=$I$8)*($C$1:$E$1=$J$7))
или так (массивная)
Код
=ИНДЕКС($C$2:$E$4;ПОИСКПОЗ(H8&I8;A2:A4&B2:B4;0);ПОИСКПОЗ(J7;$C$1:$E$1;0))

[p.s.]а вообщ то Миша правильно спросил
Цитата
а каким должен быть результат для сочетания f и 563

а можно и так
Код
=ПРОСМОТР(1;1/((H8=A2:A4)*(I8=B2:B4));ИНДЕКС(C2:E4;;ПОИСКПОЗ(J7;C1:E1;0)))

Автор - китин
Дата добавления - 05.09.2016 в 11:50
Gopronotmore Дата: Понедельник, 05.09.2016, 11:51 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
buchlotnik, Я просто в примере не указал возможность того что 1 параметр в столбце может повторяться, например в 1 столбце параметры
a b c
1 101
1 102
2 103
3 104

1 параметр может повторяться ....
 
Ответить
Сообщениеbuchlotnik, Я просто в примере не указал возможность того что 1 параметр в столбце может повторяться, например в 1 столбце параметры
a b c
1 101
1 102
2 103
3 104

1 параметр может повторяться ....

Автор - Gopronotmore
Дата добавления - 05.09.2016 в 11:51
buchlotnik Дата: Понедельник, 05.09.2016, 11:52 | Сообщение № 5
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
тогда не обижайте VLOOKUP+MATCH , ловите:
Код
=ИНДЕКС($C$2:$E$4;ПОИСКПОЗ(H8&" "&I8;A2:A4&" "&B2:B4;);ПОИСКПОЗ(J7;C1:E1;))

китин, Игорь, конкатенацию лучше через пробел - там похоже числовые значения
К сообщению приложен файл: 9865351.xlsx (8.9 Kb)


Сообщение отредактировал buchlotnik - Понедельник, 05.09.2016, 11:53
 
Ответить
Сообщениетогда не обижайте VLOOKUP+MATCH , ловите:
Код
=ИНДЕКС($C$2:$E$4;ПОИСКПОЗ(H8&" "&I8;A2:A4&" "&B2:B4;);ПОИСКПОЗ(J7;C1:E1;))

китин, Игорь, конкатенацию лучше через пробел - там похоже числовые значения

Автор - buchlotnik
Дата добавления - 05.09.2016 в 11:52
Gopronotmore Дата: Понедельник, 05.09.2016, 11:55 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
Ребята, только вопрос когда меняешь букву ваша формула дает неверное значение
 
Ответить
СообщениеРебята, только вопрос когда меняешь букву ваша формула дает неверное значение

Автор - Gopronotmore
Дата добавления - 05.09.2016 в 11:55
китин Дата: Понедельник, 05.09.2016, 11:55 | Сообщение № 7
Группа: Модераторы
Ранг: Экселист
Сообщений: 7013
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
онкатенацию лучше через пробел - там похоже числовые значения

мдя. походу ты прав


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщение
онкатенацию лучше через пробел - там похоже числовые значения

мдя. походу ты прав

Автор - китин
Дата добавления - 05.09.2016 в 11:55
китин Дата: Понедельник, 05.09.2016, 11:56 | Сообщение № 8
Группа: Модераторы
Ранг: Экселист
Сообщений: 7013
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
когда меняешь букву ваша формула дает неверное значение

пример как может что меняться плз


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщение
когда меняешь букву ваша формула дает неверное значение

пример как может что меняться плз

Автор - китин
Дата добавления - 05.09.2016 в 11:56
Gopronotmore Дата: Понедельник, 05.09.2016, 12:00 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
Вот какие должны прмерно получаться значения ...
В желтых ячейках параметры меняются и подставляется значение через INDEX видимо, но не знаю как точно
К сообщению приложен файл: example_2.xlsx (8.0 Kb)
 
Ответить
СообщениеВот какие должны прмерно получаться значения ...
В желтых ячейках параметры меняются и подставляется значение через INDEX видимо, но не знаю как точно

Автор - Gopronotmore
Дата добавления - 05.09.2016 в 12:00
buchlotnik Дата: Понедельник, 05.09.2016, 12:00 | Сообщение № 10
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
и ещё недавно такое всплывало (для числовых в таблице):
Код
=АГРЕГАТ(15;6;($C$2:$E$4)/($C$1:$E$1=$J$7)/($A$2:$A$4=H8)/($B$2:$B$4=I8);1)

А вы похоже баксы при протяжке не поставили:
Код
=ИНДЕКС($C$2:$E$4;ПОИСКПОЗ(H8&" "&I8;$A$2:$A$4&" "&$B$2:$B$4;);ПОИСКПОЗ($J$7;$C$1:$E$1;))
К сообщению приложен файл: 1581865.xlsx (9.2 Kb)


Сообщение отредактировал buchlotnik - Понедельник, 05.09.2016, 12:04
 
Ответить
Сообщениеи ещё недавно такое всплывало (для числовых в таблице):
Код
=АГРЕГАТ(15;6;($C$2:$E$4)/($C$1:$E$1=$J$7)/($A$2:$A$4=H8)/($B$2:$B$4=I8);1)

А вы похоже баксы при протяжке не поставили:
Код
=ИНДЕКС($C$2:$E$4;ПОИСКПОЗ(H8&" "&I8;$A$2:$A$4&" "&$B$2:$B$4;);ПОИСКПОЗ($J$7;$C$1:$E$1;))

Автор - buchlotnik
Дата добавления - 05.09.2016 в 12:00
Gopronotmore Дата: Понедельник, 05.09.2016, 12:02 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
Блин, как бы вам объяснить тут получается 1 параметр константа, меняются значения только в 2 диапазонах один по горизонтали другой буквенный по вертикали, а 3 постоянный остается неизменным
 
Ответить
СообщениеБлин, как бы вам объяснить тут получается 1 параметр константа, меняются значения только в 2 диапазонах один по горизонтали другой буквенный по вертикали, а 3 постоянный остается неизменным

Автор - Gopronotmore
Дата добавления - 05.09.2016 в 12:02
Gopronotmore Дата: Понедельник, 05.09.2016, 12:03 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
buchlotnik, Она работает только с Excel 2010 у меня 2007
 
Ответить
Сообщениеbuchlotnik, Она работает только с Excel 2010 у меня 2007

Автор - Gopronotmore
Дата добавления - 05.09.2016 в 12:03
китин Дата: Понедельник, 05.09.2016, 12:05 | Сообщение № 13
Группа: Модераторы
Ранг: Экселист
Сообщений: 7013
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Gopronotmore, а вы почитайте про абсолютные и относительные ссылки. в первом примере ничего не было, что формулы должны протягиваться
К сообщению приложен файл: 323232323.xlsx (8.9 Kb)


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
СообщениеGopronotmore, а вы почитайте про абсолютные и относительные ссылки. в первом примере ничего не было, что формулы должны протягиваться

Автор - китин
Дата добавления - 05.09.2016 в 12:05
buchlotnik Дата: Понедельник, 05.09.2016, 12:07 | Сообщение № 14
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
Цитата
Она работает только с Excel 2010
так а решение ИНДЕКСОМ чем не устроило - баксы проверили? (см. пост №10)


Сообщение отредактировал buchlotnik - Понедельник, 05.09.2016, 12:08
 
Ответить
Сообщение
Цитата
Она работает только с Excel 2010
так а решение ИНДЕКСОМ чем не устроило - баксы проверили? (см. пост №10)

Автор - buchlotnik
Дата добавления - 05.09.2016 в 12:07
Gopronotmore Дата: Понедельник, 05.09.2016, 12:11 | Сообщение № 15
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
Все спасибо большое, аж в 3 вариантах предложили, с массивом не очень удобно потому что я в примере указал 3 значения а если у меня их 1000 там виснуть все начинает, SUMPRODUCT можно попробывать если не ошибаюсь значение будет ссылаться на другой лист нужно ставить -- перед SUMPRODUCT, а то 1 раз когда я не знал этого нюанса, то у меня значения не обновлялись автоматически ... про LOOKUP не знаю, это для меня новый метод!

Спасибо большое за помощь
 
Ответить
СообщениеВсе спасибо большое, аж в 3 вариантах предложили, с массивом не очень удобно потому что я в примере указал 3 значения а если у меня их 1000 там виснуть все начинает, SUMPRODUCT можно попробывать если не ошибаюсь значение будет ссылаться на другой лист нужно ставить -- перед SUMPRODUCT, а то 1 раз когда я не знал этого нюанса, то у меня значения не обновлялись автоматически ... про LOOKUP не знаю, это для меня новый метод!

Спасибо большое за помощь

Автор - Gopronotmore
Дата добавления - 05.09.2016 в 12:11
китин Дата: Понедельник, 05.09.2016, 12:13 | Сообщение № 16
Группа: Модераторы
Ранг: Экселист
Сообщений: 7013
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
нужно ставить -- перед SUMPRODUCT

Зачем?


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщение
нужно ставить -- перед SUMPRODUCT

Зачем?

Автор - китин
Дата добавления - 05.09.2016 в 12:13
Gopronotmore Дата: Понедельник, 05.09.2016, 12:21 | Сообщение № 17
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
китин, Если есть 2 файла, то SUMPRODUCT не будет брать данные пока не будет открыт другой файл, а если поставить -- перед ним, то он будет это делать автоматически .... Такой лайф хак, не знаю почему только
 
Ответить
Сообщениекитин, Если есть 2 файла, то SUMPRODUCT не будет брать данные пока не будет открыт другой файл, а если поставить -- перед ним, то он будет это делать автоматически .... Такой лайф хак, не знаю почему только

Автор - Gopronotmore
Дата добавления - 05.09.2016 в 12:21
buchlotnik Дата: Понедельник, 05.09.2016, 12:21 | Сообщение № 18
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
Цитата
у меня их 1000 там виснуть все начинает
странно - протянул исходную таблицу на 1500 вариантов - сделал по 2500 формул и ИНДЕКСОМ и АГРЕГАТОМ - 1000 пересчётов книги заняли 0,5 секунды - там же волатильного по факту ничего нет


Сообщение отредактировал buchlotnik - Понедельник, 05.09.2016, 12:27
 
Ответить
Сообщение
Цитата
у меня их 1000 там виснуть все начинает
странно - протянул исходную таблицу на 1500 вариантов - сделал по 2500 формул и ИНДЕКСОМ и АГРЕГАТОМ - 1000 пересчётов книги заняли 0,5 секунды - там же волатильного по факту ничего нет

Автор - buchlotnik
Дата добавления - 05.09.2016 в 12:21
_Boroda_ Дата: Понедельник, 05.09.2016, 12:37 | Сообщение № 19
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
с массивом не очень удобно

Еще немассивный вариант
Код
=СУММЕСЛИМН(ИНДЕКС(C$2:E$4;;ПОИСКПОЗ(J$7;C$1:E$1;));A$2:A$4;H8;B$2:B$4;I8)


Если есть 2 файла, то SUMPRODUCT не будет брать данные пока не будет открыт другой файл, а если поставить -- перед ним, то он будет это делать автоматически
Не совсем так. СУММПРОИЗВ берет данные из закрытых файлов. Могу предположить, что где-то у Вас числа текстовые, а где-то числовые. При сравнении они дают ЛОЖЬ, а при использовании "--" текстовое число преобразуется в числовое число и сравнение даст ИСТИНА.
К сообщению приложен файл: example_21.xlsx (8.9 Kb)


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

Еще немассивный вариант
Код
=СУММЕСЛИМН(ИНДЕКС(C$2:E$4;;ПОИСКПОЗ(J$7;C$1:E$1;));A$2:A$4;H8;B$2:B$4;I8)


Если есть 2 файла, то SUMPRODUCT не будет брать данные пока не будет открыт другой файл, а если поставить -- перед ним, то он будет это делать автоматически
Не совсем так. СУММПРОИЗВ берет данные из закрытых файлов. Могу предположить, что где-то у Вас числа текстовые, а где-то числовые. При сравнении они дают ЛОЖЬ, а при использовании "--" текстовое число преобразуется в числовое число и сравнение даст ИСТИНА.

Автор - _Boroda_
Дата добавления - 05.09.2016 в 12:37
Gopronotmore Дата: Понедельник, 05.09.2016, 12:45 | Сообщение № 20
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, Спасибо большое! Для меня через SUMIFS самый лучший вариант, потому что я его понимаю, что и как и откуда берется!

Ребятам выше тоже спасибо за помощь!!! Не знал что SUMIFS тоже вяжется с MATCH!
 
Ответить
Сообщение_Boroda_, Спасибо большое! Для меня через SUMIFS самый лучший вариант, потому что я его понимаю, что и как и откуда берется!

Ребятам выше тоже спасибо за помощь!!! Не знал что SUMIFS тоже вяжется с MATCH!

Автор - Gopronotmore
Дата добавления - 05.09.2016 в 12:45
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подстановка значения по 3 параметрам (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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