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

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Использование функции массивов и ВПР (Формулы/Formulas)
Использование функции массивов и ВПР
lebensvoll Дата: Четверг, 02.06.2016, 20:35 | Сообщение № 1
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

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 (48.6 Kb)

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

Сообщение отредактировал 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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

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

Кто бы ты ни был, мир в твоих руках
 
Ответить
СообщениеПрошу прощение файл не тот прикладывал (((( вот сейчас именно тот
Автор - lebensvoll
Дата добавления - 02.06.2016 в 21:16
Manyasha Дата: Четверг, 02.06.2016, 21:39 | Сообщение № 3
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация: 898 ±
Замечаний: 0% ±

Excel 2010, 2016
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 (51.2 Kb)

ЯД: 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
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16672
Репутация: 6479 ±
Замечаний: 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 (51.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

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

Кто бы ты ни был, мир в твоих руках
 
Ответить
СообщениеManyasha, добрый вечер, спасибо за отклик но решение немного не то (((((.
После того как оператор указал все параметры составленной заявки. Я делаю подсчет затраченного материала на данную заявку. Т.е. по установленному шифру, в столбце Q (фиолетовый цвет) я произвожу суммирование каждого шифра (каждого материала в бетоне или растворе: цемента; песка; щебня; добавки) в заявке:
Цемента
Щебня
Песка
Добавки
Т.е. получается он должен найти шифр на листе "Норма расхода" и взять кол-во цемента в нем и умножить его на заказанный объем. И так по всем (материалам в бетоне или растворе) а также по всему заказанному продукту (зеленый цвет)
Автор - lebensvoll
Дата добавления - 02.06.2016 в 21:57
lebensvoll Дата: Четверг, 02.06.2016, 22:22 | Сообщение № 6
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

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

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

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

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 (48.7 Kb)

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

Сообщение отредактировал 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 | Сообщение № 8
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

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 из 1
  • 1
Поиск:

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