Подстановка значения по 3 параметрам
Gopronotmore
Дата: Понедельник, 05.09.2016, 11:33 |
Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация:
3
±
Замечаний:
0% ±
Excel 2007
Добрый день форумчане! Вопрос! У меня есть таблица в ней 3 параметра, как сделать подстановку по 3 параметрам ? Параметры в желтых ячейках должны меняться, соответственно и число SUMIFS не подходит VLOOKUP+MATCH можно выбрать только 2 параметра, подскажите пожалуйста всю голову сломал!
Добрый день форумчане! Вопрос! У меня есть таблица в ней 3 параметра, как сделать подстановку по 3 параметрам ? Параметры в желтых ячейках должны меняться, соответственно и число SUMIFS не подходит VLOOKUP+MATCH можно выбрать только 2 параметра, подскажите пожалуйста всю голову сломал! Gopronotmore
Ответить
Сообщение Добрый день форумчане! Вопрос! У меня есть таблица в ней 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
Ответить
Сообщение 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)))
и вам Доброго к примеру вот так Код
=СУММПРОИЗВ($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)))
китин
Не судите очень строго:я пытаюсь научиться ЯД 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
Ответить
Сообщение 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;))
китин , Игорь, конкатенацию лучше через пробел - там похоже числовые значения
тогда не обижайте VLOOKUP+MATCH , ловите: Код
=ИНДЕКС($C$2:$E$4;ПОИСКПОЗ(H8&" "&I8;A2:A4&" "&B2:B4;);ПОИСКПОЗ(J7;C1:E1;))
китин , Игорь, конкатенацию лучше через пробел - там похоже числовые значения buchlotnik
Сообщение отредактировал 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
Ответить
Сообщение Ребята, только вопрос когда меняешь букву ваша формула дает неверное значение Автор - 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 видимо, но не знаю как точно
Вот какие должны прмерно получаться значения ... В желтых ячейках параметры меняются и подставляется значение через INDEX видимо, но не знаю как точно Gopronotmore
Ответить
Сообщение Вот какие должны прмерно получаться значения ... В желтых ячейках параметры меняются и подставляется значение через 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;))
и ещё недавно такое всплывало (для числовых в таблице): Код
=АГРЕГАТ(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
Сообщение отредактировал 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
Ответить
Сообщение Блин, как бы вам объяснить тут получается 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 у меня 2007Gopronotmore
Ответить
Сообщение buchlotnik , Она работает только с Excel 2010 у меня 2007Автор - Gopronotmore Дата добавления - 05.09.2016 в 12:03
китин
Дата: Понедельник, 05.09.2016, 12:05 |
Сообщение № 13
Группа: Модераторы
Ранг: Экселист
Сообщений: 7013
Репутация:
1073
±
Замечаний:
0% ±
Excel 2007;2010;2016
Gopronotmore , а вы почитайте про абсолютные и относительные ссылки. в первом примере ничего не было, что формулы должны протягиваться
Gopronotmore , а вы почитайте про абсолютные и относительные ссылки. в первом примере ничего не было, что формулы должны протягиватьсякитин
Не судите очень строго:я пытаюсь научиться ЯД 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)
Цитата
Она работает только с Excel 2010
так а решение ИНДЕКСОМ чем не устроило - баксы проверили? (см. пост №10)buchlotnik
Сообщение отредактировал 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
Ответить
Сообщение Все спасибо большое, аж в 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
Зачем?
нужно ставить -- перед 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
Ответить
Сообщение китин , Если есть 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 секунды - там же волатильного по факту ничего нет
Цитата
у меня их 1000 там виснуть все начинает
странно - протянул исходную таблицу на 1500 вариантов - сделал по 2500 формул и ИНДЕКСОМ и АГРЕГАТОМ - 1000 пересчётов книги заняли 0,5 секунды - там же волатильного по факту ничего нетbuchlotnik
Сообщение отредактировал 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 не будет брать данные пока не будет открыт другой файл, а если поставить -- перед ним, то он будет это делать автоматически
Не совсем так. СУММПРОИЗВ берет данные из закрытых файлов. Могу предположить, что где-то у Вас числа текстовые, а где-то числовые. При сравнении они дают ЛОЖЬ, а при использовании "--" текстовое число преобразуется в числовое число и сравнение даст ИСТИНА.
с массивом не очень удобно
Еще немассивный вариантКод
=СУММЕСЛИМН(ИНДЕКС(C$2:E$4;;ПОИСКПОЗ(J$7;C$1:E$1;));A$2:A$4;H8;B$2:B$4;I8)
Если есть 2 файла, то SUMPRODUCT не будет брать данные пока не будет открыт другой файл, а если поставить -- перед ним, то он будет это делать автоматически
Не совсем так. СУММПРОИЗВ берет данные из закрытых файлов. Могу предположить, что где-то у Вас числа текстовые, а где-то числовые. При сравнении они дают ЛОЖЬ, а при использовании "--" текстовое число преобразуется в числовое число и сравнение даст ИСТИНА._Boroda_
Скажи мне, кудесник, любимец ба’гов... Платная помощь: 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
Ответить
Сообщение _Boroda_ , Спасибо большое! Для меня через SUMIFS самый лучший вариант, потому что я его понимаю, что и как и откуда берется! Ребятам выше тоже спасибо за помощь!!! Не знал что SUMIFS тоже вяжется с MATCH!Автор - Gopronotmore Дата добавления - 05.09.2016 в 12:45