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

Вход

Регистрация

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

 

= Мир MS Excel/Сумма произведений с условиями ИЗ 2 таблиц в третьей - Мир MS Excel

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

Excel 2016
Имеется 3 таблицы.
В 1 таблице - должности и количество ставок по ним в периоде (отдельный столбец). Должности в строках могут повторяться. относясь к разным объектам.
Во второй - матрица-сопоставление количества единиц спецодежды и должностей. Должности - строки, спецодежда - столбцы. Должности и спецодежда уникальные.
В третьей - необходимо рассчитать количество спецодежды в периоде, используя зависимость
СУММА ПО КАЖДОЙ ДОЛЖНОСТИ("К-во ставок по должности в периоде (из таблицы 1)"*"К-во единиц спецодежды для должности(из таблицы 2)").
Результат нужен именно в таком виде, т.к. будет дальше использоваться в расчетах.
Думал, что могут помочь формулы массива, но с разноразмерными массивами не придумал как их применить.
Прошу помочь написать формулу для такого расчета.
К сообщению приложен файл: 0816102.xlsx (19.9 Kb)
 
Ответить
СообщениеИмеется 3 таблицы.
В 1 таблице - должности и количество ставок по ним в периоде (отдельный столбец). Должности в строках могут повторяться. относясь к разным объектам.
Во второй - матрица-сопоставление количества единиц спецодежды и должностей. Должности - строки, спецодежда - столбцы. Должности и спецодежда уникальные.
В третьей - необходимо рассчитать количество спецодежды в периоде, используя зависимость
СУММА ПО КАЖДОЙ ДОЛЖНОСТИ("К-во ставок по должности в периоде (из таблицы 1)"*"К-во единиц спецодежды для должности(из таблицы 2)").
Результат нужен именно в таком виде, т.к. будет дальше использоваться в расчетах.
Думал, что могут помочь формулы массива, но с разноразмерными массивами не придумал как их применить.
Прошу помочь написать формулу для такого расчета.
Автор - inetguru
Дата добавления - 02.02.2015 в 22:40
_Boroda_ Дата: Понедельник, 02.02.2015, 23:31 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Здесь можно и без формул массива
Код
=СУММПРОИЗВ(ИНДЕКС(спр.Спецодежда!$B$8:$L$16;;ПОИСКПОЗ($A3;спр.Спецодежда!$B$7:$L$7;))*СУММЕСЛИ(расч.Должности!$D$4:$D$12;спр.Спецодежда!$A$8:$A$16;ИНДЕКС(расч.Должности!$E$4:$N$12;;ЛЕВБ(B$2;ПОИСК(" ";B$2)))))
К сообщению приложен файл: 0816102_1.xlsx (23.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЗдесь можно и без формул массива
Код
=СУММПРОИЗВ(ИНДЕКС(спр.Спецодежда!$B$8:$L$16;;ПОИСКПОЗ($A3;спр.Спецодежда!$B$7:$L$7;))*СУММЕСЛИ(расч.Должности!$D$4:$D$12;спр.Спецодежда!$A$8:$A$16;ИНДЕКС(расч.Должности!$E$4:$N$12;;ЛЕВБ(B$2;ПОИСК(" ";B$2)))))
Автор - _Boroda_
Дата добавления - 02.02.2015 в 23:31
inetguru Дата: Вторник, 03.02.2015, 00:00 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Большое спасибо. Помогло.
 
Ответить
СообщениеБольшое спасибо. Помогло.
Автор - inetguru
Дата добавления - 03.02.2015 в 00:00
vikttur Дата: Вторник, 03.02.2015, 00:04 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Не обязательно вычислять столбец, периоды ведь не меняют порядка:
Код
=СУММПРОИЗВ(ИНДЕКС(спр.Спецодежда!$B$8:$L$16;;ПОИСКПОЗ($A3;спр.Спецодежда!$B$7:$L$7;));СУММЕСЛИ(расч.Должности!$D$4:$D$12;спр.Спецодежда!$A$8:$A$16;ИНДЕКС(расч.Должности!E$4:E$12;)))


Сообщение отредактировал vikttur - Вторник, 03.02.2015, 00:29
 
Ответить
СообщениеНе обязательно вычислять столбец, периоды ведь не меняют порядка:
Код
=СУММПРОИЗВ(ИНДЕКС(спр.Спецодежда!$B$8:$L$16;;ПОИСКПОЗ($A3;спр.Спецодежда!$B$7:$L$7;));СУММЕСЛИ(расч.Должности!$D$4:$D$12;спр.Спецодежда!$A$8:$A$16;ИНДЕКС(расч.Должности!E$4:E$12;)))
Автор - vikttur
Дата добавления - 03.02.2015 в 00:04
inetguru Дата: Среда, 04.02.2015, 19:29 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Как можно во второй части вашей формулы использовать несколько условий? Мне необходимо при расчете спецодежды учесть количество ставок, для которых соблюдается условие
Код
остат(количество месяцев между датами (дата периода на листе расч.спецодежды;дата начала периода для должности на листе расч.должности ); длительность носки спецодежды )=0
Т.е. в таблицу с расчетом спецодежды должно попасть еоличество спецодежды, которую необходимо купить в расчетгом периоде. К таким датам относятся дата начала действия должности и повторяющаяся через количество месяцев носки спецодежды. Преобразовать суммесли к суммеслимн не получилось, т.к. размерность диапазонов различная. А для суммеслимн она доджна быть одинаковой.
 
Ответить
СообщениеКак можно во второй части вашей формулы использовать несколько условий? Мне необходимо при расчете спецодежды учесть количество ставок, для которых соблюдается условие
Код
остат(количество месяцев между датами (дата периода на листе расч.спецодежды;дата начала периода для должности на листе расч.должности ); длительность носки спецодежды )=0
Т.е. в таблицу с расчетом спецодежды должно попасть еоличество спецодежды, которую необходимо купить в расчетгом периоде. К таким датам относятся дата начала действия должности и повторяющаяся через количество месяцев носки спецодежды. Преобразовать суммесли к суммеслимн не получилось, т.к. размерность диапазонов различная. А для суммеслимн она доджна быть одинаковой.
Автор - inetguru
Дата добавления - 04.02.2015 в 19:29
vikttur Дата: Среда, 04.02.2015, 20:00 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Наверное, пример нужен.
 
Ответить
СообщениеНаверное, пример нужен.
Автор - vikttur
Дата добавления - 04.02.2015 в 20:00
inetguru Дата: Среда, 04.02.2015, 21:03 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
В этом примере начал писать формулу:
Код
СУММПРОИЗВ(ИНДЕКС(спр.Спецодежда!$B$7:$L$18;;ПОИСКПОЗ($A3;спр.Спецодежда!$B$6:$L$6;))*СУММЕСЛИМН(ИНДЕКС(расч.Должности!$E$4:$N$12;;ЛЕВБ(B$2;ПОИСК(" ";B$2)));расч.Должности!$D$4:$D$12;спр.Спецодежда!$A$7:$A$18;расч.Должности!$B$4:$B$11;
ОСТАТ((ГОД(B1)-ГОД(расч.Должности!$B$4:$B$11))*12+(МЕСЯЦ(B1)-МЕСЯЦ(расч.Должности!$B$4:$B$11)); ИНДЕКС(спр.Спецодежда!$B$2:$L$6;1;ПОИСКПОЗ($A3;спр.Спецодежда!$B$6:$L$6))))=0)

Условие по сравнению даты начала действия должности (столбец B листа расч.должности) и даты рассчитываемого периода (лист расч.спецодежда!B1:K1) со сроком носки конкретного вида спецодежды (лист спр.Спецодежда!B2:L2):
Код
ОСТАТ((ГОД(B1)-ГОД(расч.Должности!$B$4:$B$11))*12+МЕСЯЦ(B1)-МЕСЯЦ(расч.Должности!$B$4:$B$11); ИНДЕКС(спр.Спецодежда!$B$2:$L$6;1; ПОИСКПОЗ($A3;спр.Спецодежда!$B$6:$L$6)))=0

В моей формуле массивы разных размеров, а нужно при суммировании учесть с этими дополнительными условиями.
К сообщению приложен файл: 1015310.xlsx (23.6 Kb)


Сообщение отредактировал inetguru - Среда, 04.02.2015, 21:04
 
Ответить
СообщениеВ этом примере начал писать формулу:
Код
СУММПРОИЗВ(ИНДЕКС(спр.Спецодежда!$B$7:$L$18;;ПОИСКПОЗ($A3;спр.Спецодежда!$B$6:$L$6;))*СУММЕСЛИМН(ИНДЕКС(расч.Должности!$E$4:$N$12;;ЛЕВБ(B$2;ПОИСК(" ";B$2)));расч.Должности!$D$4:$D$12;спр.Спецодежда!$A$7:$A$18;расч.Должности!$B$4:$B$11;
ОСТАТ((ГОД(B1)-ГОД(расч.Должности!$B$4:$B$11))*12+(МЕСЯЦ(B1)-МЕСЯЦ(расч.Должности!$B$4:$B$11)); ИНДЕКС(спр.Спецодежда!$B$2:$L$6;1;ПОИСКПОЗ($A3;спр.Спецодежда!$B$6:$L$6))))=0)

Условие по сравнению даты начала действия должности (столбец B листа расч.должности) и даты рассчитываемого периода (лист расч.спецодежда!B1:K1) со сроком носки конкретного вида спецодежды (лист спр.Спецодежда!B2:L2):
Код
ОСТАТ((ГОД(B1)-ГОД(расч.Должности!$B$4:$B$11))*12+МЕСЯЦ(B1)-МЕСЯЦ(расч.Должности!$B$4:$B$11); ИНДЕКС(спр.Спецодежда!$B$2:$L$6;1; ПОИСКПОЗ($A3;спр.Спецодежда!$B$6:$L$6)))=0

В моей формуле массивы разных размеров, а нужно при суммировании учесть с этими дополнительными условиями.
Автор - inetguru
Дата добавления - 04.02.2015 в 21:03
inetguru Дата: Четверг, 05.02.2015, 14:40 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Коллеги, прошу помочь хотябы с общим направлением мысли.
 
Ответить
СообщениеКоллеги, прошу помочь хотябы с общим направлением мысли.
Автор - inetguru
Дата добавления - 05.02.2015 в 14:40
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сумма произведений с условиями ИЗ 2 таблиц в третьей (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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