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

Вход

Регистрация

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

 

= Мир MS Excel/Использование функции массивов и ВПР - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Использование функции массивов и ВПР (Формулы/Formulas)
Использование функции массивов и ВПР
lebensvoll Дата: Четверг, 02.06.2016, 20:35 | Сообщение № 1
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 2 ±
Замечаний: 60% ±

Excel 2010
Добрый вечер уважаемые форумчане!!!
Прошу Вас помощи в решении. Имеется ЛИСТ ЗАЯВКА по установленным данным в таблице (выделил желтым цветом) предоставляется возможность выбрать определенный шифр данной бетонной смеси (выделил зеленым цветом). В соседней таблице в столбце Q (выделил фиолетовым цветом) планируется подсчет затраченного материала на заказанную продукцию.
Я думал что возможно это произвести через данную формулу:
Код
{=СУММПРОИЗВ(ВПР(J14:J30;'Нормы расхода'!E5:AD47;5;0)*(H14:H30))}
а также
Код
{=СУММПРОИЗВ(ЕСЛИ(G14:G30=P8;ВПР(J14:J30;'Нормы расхода'!E5:AD46;8;ЛОЖЬ)+ВПР(J14:J30;'Нормы расхода'!E5:AD46;9;ЛОЖЬ)*(H14:H30);""))}
но выяснилось что через формулу массива функция ВПР будет выдавать ЗНАЧ или же брать лишь первое найденное значение.
Что можно было бы использовать в замен функции ВПР???
К сообщению приложен файл: ___2016.xlsx(49Kb)


Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Четверг, 02.06.2016, 21:16
 
Ответить
СообщениеДобрый вечер уважаемые форумчане!!!
Прошу Вас помощи в решении. Имеется ЛИСТ ЗАЯВКА по установленным данным в таблице (выделил желтым цветом) предоставляется возможность выбрать определенный шифр данной бетонной смеси (выделил зеленым цветом). В соседней таблице в столбце Q (выделил фиолетовым цветом) планируется подсчет затраченного материала на заказанную продукцию.
Я думал что возможно это произвести через данную формулу:
Код
{=СУММПРОИЗВ(ВПР(J14:J30;'Нормы расхода'!E5:AD47;5;0)*(H14:H30))}
а также
Код
{=СУММПРОИЗВ(ЕСЛИ(G14:G30=P8;ВПР(J14:J30;'Нормы расхода'!E5:AD46;8;ЛОЖЬ)+ВПР(J14:J30;'Нормы расхода'!E5:AD46;9;ЛОЖЬ)*(H14:H30);""))}
но выяснилось что через формулу массива функция ВПР будет выдавать ЗНАЧ или же брать лишь первое найденное значение.
Что можно было бы использовать в замен функции ВПР???

Автор - lebensvoll
Дата добавления - 02.06.2016 в 20:35
lebensvoll Дата: Четверг, 02.06.2016, 21:16 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 2 ±
Замечаний: 60% ±

Excel 2010
Прошу прощение файл не тот прикладывал (((( вот сейчас именно тот


Кто бы ты ни был, мир в твоих руках
 
Ответить
СообщениеПрошу прощение файл не тот прикладывал (((( вот сейчас именно тот

Автор - lebensvoll
Дата добавления - 02.06.2016 в 21:16
Manyasha Дата: Четверг, 02.06.2016, 21:39 | Сообщение № 3
Группа: Модераторы
Ранг: Старожил
Сообщений: 1589
Репутация: 669 ±
Замечаний: 0% ±

Excel 2007, 2010
lebensvoll, посмотрите вариант с доп. столбцом
Код
=СУММПРОИЗВ(('Нормы расхода'!$K$5:$K$46=G14)*('Нормы расхода'!$E$5:$E$46=J14)*('Нормы расхода'!$I$5:$I$46)*H14)

тогда в верхней таблице будет формула
Код
=СУММЕСЛИ($G$14:$G$20;P2;$P$14:$P$20)
К сообщению приложен файл: _2016-1.xlsx(51Kb)


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
Сообщениеlebensvoll, посмотрите вариант с доп. столбцом
Код
=СУММПРОИЗВ(('Нормы расхода'!$K$5:$K$46=G14)*('Нормы расхода'!$E$5:$E$46=J14)*('Нормы расхода'!$I$5:$I$46)*H14)

тогда в верхней таблице будет формула
Код
=СУММЕСЛИ($G$14:$G$20;P2;$P$14:$P$20)

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

2003; 2007; 2010; 2013 RUS
Формула массива
Код
=СУММ((ЕСЛИ(G$14:G$30=P2;J$14:J$30)=ТРАНСП('Нормы расхода'!E$5:E$46))*ТРАНСП('Нормы расхода'!I$5:I$46)*H$14:H$30)


Если не так, то ВРУЧНУЮ прямыми ССЫЛКАМИ посчитайте для пары значений и объясните почему именно такой подсчет. Примерно вот так
Код
='Нормы расхода'!I5*Заявка!H14+'Нормы расхода'!I12*Заявка!H15
К сообщению приложен файл: _2016_1.xlsx(51Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеФормула массива
Код
=СУММ((ЕСЛИ(G$14:G$30=P2;J$14:J$30)=ТРАНСП('Нормы расхода'!E$5:E$46))*ТРАНСП('Нормы расхода'!I$5:I$46)*H$14:H$30)


Если не так, то ВРУЧНУЮ прямыми ССЫЛКАМИ посчитайте для пары значений и объясните почему именно такой подсчет. Примерно вот так
Код
='Нормы расхода'!I5*Заявка!H14+'Нормы расхода'!I12*Заявка!H15

Автор - _Boroda_
Дата добавления - 02.06.2016 в 21:55
lebensvoll Дата: Четверг, 02.06.2016, 21:57 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 2 ±
Замечаний: 60% ±

Excel 2010
Manyasha, добрый вечер, спасибо за отклик но решение немного не то (((((.
После того как оператор указал все параметры составленной заявки. Я делаю подсчет затраченного материала на данную заявку. Т.е. по установленному шифру, в столбце Q (фиолетовый цвет) я произвожу суммирование каждого шифра (каждого материала в бетоне или растворе: цемента; песка; щебня; добавки) в заявке:
Цемента
Щебня
Песка
Добавки
Т.е. получается он должен найти шифр на листе "Норма расхода" и взять кол-во цемента в нем и умножить его на заказанный объем. И так по всем (материалам в бетоне или растворе) а также по всему заказанному продукту (зеленый цвет)


Кто бы ты ни был, мир в твоих руках
 
Ответить
СообщениеManyasha, добрый вечер, спасибо за отклик но решение немного не то (((((.
После того как оператор указал все параметры составленной заявки. Я делаю подсчет затраченного материала на данную заявку. Т.е. по установленному шифру, в столбце Q (фиолетовый цвет) я произвожу суммирование каждого шифра (каждого материала в бетоне или растворе: цемента; песка; щебня; добавки) в заявке:
Цемента
Щебня
Песка
Добавки
Т.е. получается он должен найти шифр на листе "Норма расхода" и взять кол-во цемента в нем и умножить его на заказанный объем. И так по всем (материалам в бетоне или растворе) а также по всему заказанному продукту (зеленый цвет)

Автор - lebensvoll
Дата добавления - 02.06.2016 в 21:57
lebensvoll Дата: Четверг, 02.06.2016, 22:22 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 2 ±
Замечаний: 60% ±

Excel 2010
_Boroda_, я понял данную функцию. Но как найти цемент и другие наполнители ((((( получается что щебень у нас есть а ПЕСКА; ЦЕМЕНТА и добавки у нас нет (((( в этих смесях
примерно мы выбрали эти шифры
[img][/img]
теперь мы должны в каждом из них узнать сколько в них содержится
цемента
щебня
песка
добавки
и умножить на количество заказанного продукта (у нас его каждого заказали 1 куб)


Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Четверг, 02.06.2016, 22:25
 
Ответить
Сообщение_Boroda_, я понял данную функцию. Но как найти цемент и другие наполнители ((((( получается что щебень у нас есть а ПЕСКА; ЦЕМЕНТА и добавки у нас нет (((( в этих смесях
примерно мы выбрали эти шифры
[img][/img]
теперь мы должны в каждом из них узнать сколько в них содержится
цемента
щебня
песка
добавки
и умножить на количество заказанного продукта (у нас его каждого заказали 1 куб)

Автор - lebensvoll
Дата добавления - 02.06.2016 в 22:22
_Boroda_ Дата: Четверг, 02.06.2016, 22:26 | Сообщение № 7
Группа: Модераторы
Ранг: Экселист
Сообщений: 9377
Репутация: 3950 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
мы должны в каждом из них узнать сколько в них содержится цемента

Прекрасно, а как узнать-то? Я не про формулы, я словесное описание прошу. Где написано, что в рецепте nnn содержится, например, 325 кг/м3 цемента? Должна быть какая-то табличка.

Ааа, кажись дошло. Это подумать нужно.


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

Прекрасно, а как узнать-то? Я не про формулы, я словесное описание прошу. Где написано, что в рецепте nnn содержится, например, 325 кг/м3 цемента? Должна быть какая-то табличка.

Ааа, кажись дошло. Это подумать нужно.

Автор - _Boroda_
Дата добавления - 02.06.2016 в 22:26
lebensvoll Дата: Четверг, 02.06.2016, 22:50 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 2 ±
Замечаний: 60% ±

Excel 2010
_Boroda_, ваша функция то что нужно я немного подправил ее как должен был производиться расчет. НО, проблема с двумя ячейками ((((( так и осталась я их выделил красным цветом в столбце Q
При выбранных мною шифров щебня должно было получится 1078 (выделил красным)

Код
=СУММ((ЕСЛИ(G$14:G$30=P8;J$14:J$30)=ТРАНСП('Нормы расхода'!E$5:E$46))*(ТРАНСП('Нормы расхода'!I$5:I$46)+ТРАНСП('Нормы расхода'!M$5:M$46))*H$14:H$30)


а добавки 3,03 (выделил красным)
Код
=СУММ((ЕСЛИ(G$14:G$30="";"";J$14:J$30)=ТРАНСП('Нормы расхода'!E$5:E$46))*ТРАНСП('Нормы расхода'!T$5:T$46)*ТРАНСП('Нормы расхода'!W$5:W$46)*ТРАНСП('Нормы расхода'!Z$5:Z$46)*ТРАНСП('Нормы расхода'!AC$5:AC$46)*H$14:H$30)


может снова я со скобками напутал что то или нужно не умножать ((((
К сообщению приложен файл: 3154014.xlsx(49Kb)


Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Четверг, 02.06.2016, 22:51
 
Ответить
Сообщение_Boroda_, ваша функция то что нужно я немного подправил ее как должен был производиться расчет. НО, проблема с двумя ячейками ((((( так и осталась я их выделил красным цветом в столбце Q
При выбранных мною шифров щебня должно было получится 1078 (выделил красным)

Код
=СУММ((ЕСЛИ(G$14:G$30=P8;J$14:J$30)=ТРАНСП('Нормы расхода'!E$5:E$46))*(ТРАНСП('Нормы расхода'!I$5:I$46)+ТРАНСП('Нормы расхода'!M$5:M$46))*H$14:H$30)


а добавки 3,03 (выделил красным)
Код
=СУММ((ЕСЛИ(G$14:G$30="";"";J$14:J$30)=ТРАНСП('Нормы расхода'!E$5:E$46))*ТРАНСП('Нормы расхода'!T$5:T$46)*ТРАНСП('Нормы расхода'!W$5:W$46)*ТРАНСП('Нормы расхода'!Z$5:Z$46)*ТРАНСП('Нормы расхода'!AC$5:AC$46)*H$14:H$30)


может снова я со скобками напутал что то или нужно не умножать ((((

Автор - lebensvoll
Дата добавления - 02.06.2016 в 22:50
lebensvoll Дата: Четверг, 02.06.2016, 23:01 | Сообщение № 9
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 2 ±
Замечаний: 60% ±

Excel 2010
_Boroda_, СПАСИБО ВАМ ОГРОМНОЕ У МЕНЯ ВСЕ ПОЛУЧИЛОСЬ.
так нужно было )))) в первую очередь скобки и поставить плюсы а во вторую очередь невнимательность столбцы не те выбирал )))))
Код
=СУММ((ЕСЛИ(G$14:G$30=P8;J$14:J$30)=ТРАНСП('Нормы расхода'!E$5:E$46))*(ТРАНСП('Нормы расхода'!L$5:L$46)+ТРАНСП('Нормы расхода'!M$5:M$46))*H$14:H$30)
= 1078
Код
=СУММ((ЕСЛИ(G$14:G$30="";"";J$14:J$30)=ТРАНСП('Нормы расхода'!E$5:E$46))*(ТРАНСП('Нормы расхода'!T$5:T$46)+ТРАНСП('Нормы расхода'!W$5:W$46)+ТРАНСП('Нормы расхода'!Z$5:Z$46)+ТРАНСП('Нормы расхода'!AC$5:AC$46))*H$14:H$30)
= 3,03
ФУНКЦИЯ ВАША БЛЕСК спасла просто....


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщение_Boroda_, СПАСИБО ВАМ ОГРОМНОЕ У МЕНЯ ВСЕ ПОЛУЧИЛОСЬ.
так нужно было )))) в первую очередь скобки и поставить плюсы а во вторую очередь невнимательность столбцы не те выбирал )))))
Код
=СУММ((ЕСЛИ(G$14:G$30=P8;J$14:J$30)=ТРАНСП('Нормы расхода'!E$5:E$46))*(ТРАНСП('Нормы расхода'!L$5:L$46)+ТРАНСП('Нормы расхода'!M$5:M$46))*H$14:H$30)
= 1078
Код
=СУММ((ЕСЛИ(G$14:G$30="";"";J$14:J$30)=ТРАНСП('Нормы расхода'!E$5:E$46))*(ТРАНСП('Нормы расхода'!T$5:T$46)+ТРАНСП('Нормы расхода'!W$5:W$46)+ТРАНСП('Нормы расхода'!Z$5:Z$46)+ТРАНСП('Нормы расхода'!AC$5:AC$46))*H$14:H$30)
= 3,03
ФУНКЦИЯ ВАША БЛЕСК спасла просто....

Автор - lebensvoll
Дата добавления - 02.06.2016 в 23:01
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Использование функции массивов и ВПР (Формулы/Formulas)
Страница 1 из 11
Поиск:

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