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

Вход

Регистрация

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

 

= Мир MS Excel/Как найти сумму с изменяемым искомым значением - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как найти сумму с изменяемым искомым значением (Формулы/Formulas)
Как найти сумму с изменяемым искомым значением
PLord Дата: Вторник, 15.05.2018, 09:48 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Доброго времени суток. Зашёл в тупик, прошу помощи.
Делаю выборку из таблицы, строк примерно 2100.
Необходимо выбирать данные из таблицы на основании ГОД МЕСЯЦ ДЕНЬ ТАРИФ НОМ_СЧЁТЧИКА, затем умножать их на соответствующий счётчику коэффициент в данном расчёте (-1, 0, 1) и сложить.
ГОД и МЕСЯЦ в принципе постоянные будут для одного файла, но что бы файл был универсальным то они берутся из ячеек куда вносятся вручную. Месяц закончился, внесли изменения в год и месяц и сохранили под другим именем.

Сделал выборку ВПР для одного счётчика (номер 414), правильно сделал ссылки - всё работает чётко, одну ячейку заполнил - скопировал на всю таблицу (пока данные в базе откуда формируется исходная таблица только с 8 мая). Остаётся только их сложить - но "Слишком сложная формула" - символов более 1024 я так понимаю. Смотрю - вроде как всё просто, но как реализовать и можно ли вообще это сделать без каких либо макросов не нашёл. Можно рассмотреть частный случай, а именно что в приведенном ниже примере меняться будет только $W$14 и $X$14 (от 14 до 30), одна строка работает без проблем, и сумма таких строк тоже пока не достигнет критического размера, а так как количество счётчиков может быть и больше чем в данном примере (17 шт.) то хотелось бы узнать есть ли какой нибудь способ оптимизировать это:

Код
=ВПР(Request!$A$2&" "&Request!$A$3&" "&$L18&" "&M$10&" "&$W$14;Request!$C$2:$D$2201;2;ЛОЖЬ)*$X$14+ВПР(Request!$A$2&" "&Request!$A$3&" "&$L18&" "&M$10&" "&$W$15;Request!$C$2:$D$2201;2;ЛОЖЬ)*$X$15+ВПР(Request!$A$2&" "&Request!$A$3&" "&$L18&" "&M$10&" "&$W$16;Request!$C$2:$D$2201;2;ЛОЖЬ)*$X$16
...
+ВПР(Request!$A$2&" "&Request!$A$3&" "&$L18&" "&M$10&" "&$W$30;Request!$C$2:$D$2201;2;ЛОЖЬ)*$X$30


Пробовал много всего, в основном направлены изыскания были вставить как бы массив, последовательность вместо $W$14, например {$W$14:$W$30} и т.п., но это ник чему не приводило, попробовал реализовтаь этот же запрос через ИНДЕКС и ПОИСКПОЗ - всё получилось запросить, но проблема таже и осталась.

Код
=ИНДЕКС(Request!$C$2:$D$2201;ПОИСКПОЗ(Request!$A$2&" "&Request!$A$3&" "&$L18&" "&P$10&" "&$W$14;Request!$C$2:$C$2201;0);2)*$X$14

Пока вижу решение только через создание промежуточных таблиц, например по 10 ВПР в каждой а потом их сложить, но вдруг есть решение намного проще, помогите пожалуйста.

PS
Excel 2003, в файле:
шрифт зелёный - через ВПР
шрифт синий - через ИНДЕКС и ПОИСКПОЗ
шрифт красный - вставлена сумма ВПР без знака =
К сообщению приложен файл: Kompressornaya.xls(89.5 Kb) · Kompressornaya.txt(3.5 Kb)


Сообщение отредактировал PLord - Вторник, 15.05.2018, 10:50
 
Ответить
СообщениеДоброго времени суток. Зашёл в тупик, прошу помощи.
Делаю выборку из таблицы, строк примерно 2100.
Необходимо выбирать данные из таблицы на основании ГОД МЕСЯЦ ДЕНЬ ТАРИФ НОМ_СЧЁТЧИКА, затем умножать их на соответствующий счётчику коэффициент в данном расчёте (-1, 0, 1) и сложить.
ГОД и МЕСЯЦ в принципе постоянные будут для одного файла, но что бы файл был универсальным то они берутся из ячеек куда вносятся вручную. Месяц закончился, внесли изменения в год и месяц и сохранили под другим именем.

Сделал выборку ВПР для одного счётчика (номер 414), правильно сделал ссылки - всё работает чётко, одну ячейку заполнил - скопировал на всю таблицу (пока данные в базе откуда формируется исходная таблица только с 8 мая). Остаётся только их сложить - но "Слишком сложная формула" - символов более 1024 я так понимаю. Смотрю - вроде как всё просто, но как реализовать и можно ли вообще это сделать без каких либо макросов не нашёл. Можно рассмотреть частный случай, а именно что в приведенном ниже примере меняться будет только $W$14 и $X$14 (от 14 до 30), одна строка работает без проблем, и сумма таких строк тоже пока не достигнет критического размера, а так как количество счётчиков может быть и больше чем в данном примере (17 шт.) то хотелось бы узнать есть ли какой нибудь способ оптимизировать это:

Код
=ВПР(Request!$A$2&" "&Request!$A$3&" "&$L18&" "&M$10&" "&$W$14;Request!$C$2:$D$2201;2;ЛОЖЬ)*$X$14+ВПР(Request!$A$2&" "&Request!$A$3&" "&$L18&" "&M$10&" "&$W$15;Request!$C$2:$D$2201;2;ЛОЖЬ)*$X$15+ВПР(Request!$A$2&" "&Request!$A$3&" "&$L18&" "&M$10&" "&$W$16;Request!$C$2:$D$2201;2;ЛОЖЬ)*$X$16
...
+ВПР(Request!$A$2&" "&Request!$A$3&" "&$L18&" "&M$10&" "&$W$30;Request!$C$2:$D$2201;2;ЛОЖЬ)*$X$30


Пробовал много всего, в основном направлены изыскания были вставить как бы массив, последовательность вместо $W$14, например {$W$14:$W$30} и т.п., но это ник чему не приводило, попробовал реализовтаь этот же запрос через ИНДЕКС и ПОИСКПОЗ - всё получилось запросить, но проблема таже и осталась.

Код
=ИНДЕКС(Request!$C$2:$D$2201;ПОИСКПОЗ(Request!$A$2&" "&Request!$A$3&" "&$L18&" "&P$10&" "&$W$14;Request!$C$2:$C$2201;0);2)*$X$14

Пока вижу решение только через создание промежуточных таблиц, например по 10 ВПР в каждой а потом их сложить, но вдруг есть решение намного проще, помогите пожалуйста.

PS
Excel 2003, в файле:
шрифт зелёный - через ВПР
шрифт синий - через ИНДЕКС и ПОИСКПОЗ
шрифт красный - вставлена сумма ВПР без знака =

Автор - PLord
Дата добавления - 15.05.2018 в 09:48
_Boroda_ Дата: Вторник, 15.05.2018, 09:54 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 12772
Репутация: 5232 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
- Прочитайте Правила форума
- Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)

И уберите из названия темы ВПР,он, как Вы сами же пишете, в решении вовсе не обязателен


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

И уберите из названия темы ВПР,он, как Вы сами же пишете, в решении вовсе не обязателен

Автор - _Boroda_
Дата добавления - 15.05.2018 в 09:54
китин Дата: Вторник, 15.05.2018, 10:00 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4957
Репутация: 790 ±
Замечаний: 0% ±

Excel 2007;Excel 2010


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщениекросс

Автор - китин
Дата добавления - 15.05.2018 в 10:00
_Boroda_ Дата: Вторник, 15.05.2018, 10:10 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 12772
Репутация: 5232 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Ответ готов. Исправляйте теги и он будет покладен сюда


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

Автор - _Boroda_
Дата добавления - 15.05.2018 в 10:10
PLord Дата: Вторник, 15.05.2018, 10:46 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Еле вышел со ступора, в режиме предварительного просмотра всё никак ссылки "цветными" не становились и всё искал что я делаю не так, нажал сохранить и стало вроде всё ок ))), сори за оффтоп )
 
Ответить
СообщениеЕле вышел со ступора, в режиме предварительного просмотра всё никак ссылки "цветными" не становились и всё искал что я делаю не так, нажал сохранить и стало вроде всё ок ))), сори за оффтоп )

Автор - PLord
Дата добавления - 15.05.2018 в 10:46
_Boroda_ Дата: Вторник, 15.05.2018, 10:51 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 12772
Репутация: 5232 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Ничего, бывает
Так нужно?
Код
=СУММПРОИЗВ(СУММЕСЛИ(Request!$C$2:$C$999;Request!$A$2&" "&Request!$A$3&" "&$L18&" "&M$10&" "&$W$14:$W$30;Request!$D$2:$D$999)*$X$14:$X$30)
К сообщению приложен файл: Kompressornaya_.xls(98.0 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеНичего, бывает
Так нужно?
Код
=СУММПРОИЗВ(СУММЕСЛИ(Request!$C$2:$C$999;Request!$A$2&" "&Request!$A$3&" "&$L18&" "&M$10&" "&$W$14:$W$30;Request!$D$2:$D$999)*$X$14:$X$30)

Автор - _Boroda_
Дата добавления - 15.05.2018 в 10:51
PLord Дата: Вторник, 15.05.2018, 11:53 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Снимаю шляпу ))
Как то всё так быстро разрешилось, как будто и не было этих долгих бессоных трёх дней )))
Только в этом году начал серьёзно относиться к Excel - до этого думал что это word с калькулятором, как я был не прав...
Благодарю ещё раз, думал решения нет, спасибо !!!

PS
Хотел хоть какое то решение, а тут целых два получил - на разных форумах, хорошо что мир не без добрых людей ))


Сообщение отредактировал PLord - Вторник, 15.05.2018, 11:56
 
Ответить
СообщениеСнимаю шляпу ))
Как то всё так быстро разрешилось, как будто и не было этих долгих бессоных трёх дней )))
Только в этом году начал серьёзно относиться к Excel - до этого думал что это word с калькулятором, как я был не прав...
Благодарю ещё раз, думал решения нет, спасибо !!!

PS
Хотел хоть какое то решение, а тут целых два получил - на разных форумах, хорошо что мир не без добрых людей ))

Автор - PLord
Дата добавления - 15.05.2018 в 11:53
AlexM Дата: Вторник, 15.05.2018, 13:39 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3750
Репутация: 947 ±
Замечаний: 0% ±

Excel 2003
хорошо что мир не без добрых людей
И Мир и Planeta Excel и еще много аналогичных форумов с добрыми людьми. И часто это одни и те же люди.
Не забывайте сообщать, что вопрос задан на нескольких форумах.



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
хорошо что мир не без добрых людей
И Мир и Planeta Excel и еще много аналогичных форумов с добрыми людьми. И часто это одни и те же люди.
Не забывайте сообщать, что вопрос задан на нескольких форумах.

Автор - AlexM
Дата добавления - 15.05.2018 в 13:39
bmv98rus Дата: Вторник, 15.05.2018, 15:19 | Сообщение № 9
Группа: Проверенные
Ранг: Старожил
Сообщений: 1261
Репутация: 214 ±
Замечаний: 0% ±

Excel 2013/2016
[offtop]
И Мир и Planeta Excel и еще много аналогичных форумов с добрыми людьми. И часто это одни и те же люди.

Алексей, В Вашем стиле сокращать формулыировки . И Мир и Планета и остальные, с теми же добрыми людьми. :-)[/offtop]
 
Ответить
Сообщение[offtop]
И Мир и Planeta Excel и еще много аналогичных форумов с добрыми людьми. И часто это одни и те же люди.

Алексей, В Вашем стиле сокращать формулыировки . И Мир и Планета и остальные, с теми же добрыми людьми. :-)[/offtop]

Автор - bmv98rus
Дата добавления - 15.05.2018 в 15:19
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как найти сумму с изменяемым искомым значением (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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