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

Вход

Регистрация

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

 

= Мир MS Excel/Расчет суммы при поиске требуемых значений - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Расчет суммы при поиске требуемых значений (Формулы/Formulas)
Расчет суммы при поиске требуемых значений
n0leg68 Дата: Понедельник, 26.09.2016, 15:05 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 64
Репутация: 2 ±
Замечаний: 0% ±

Excel 2013
Всем привет!
Где надпись выбрать город выпадающий список городов. При выборе другого города идет расчет совсем другого времени (Общее время) и естественно оно постоянно меняется.
Я вот всю голову переломал, возможно ли сделать заранее расчет. Справа я выделил ячейки желтые, смысл, чтобы в них уже заранее отображалось ОБЩЕЕ ВРЕМЯ за каждый город отдельно.
То есть не важно какой город в ячейке где выбрать город, все равно бы в желтой ячейке стояла сумма времени какая там выпадет при выборе этого города.
К сообщению приложен файл: 1574152.xlsx(43Kb)
 
Ответить
СообщениеВсем привет!
Где надпись выбрать город выпадающий список городов. При выборе другого города идет расчет совсем другого времени (Общее время) и естественно оно постоянно меняется.
Я вот всю голову переломал, возможно ли сделать заранее расчет. Справа я выделил ячейки желтые, смысл, чтобы в них уже заранее отображалось ОБЩЕЕ ВРЕМЯ за каждый город отдельно.
То есть не важно какой город в ячейке где выбрать город, все равно бы в желтой ячейке стояла сумма времени какая там выпадет при выборе этого города.

Автор - n0leg68
Дата добавления - 26.09.2016 в 15:05
китин Дата: Понедельник, 26.09.2016, 15:15 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3423
Репутация: 543 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
брррррр. ничё не понял. откуда брать общее время по городам?


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

Автор - китин
Дата добавления - 26.09.2016 в 15:15
n0leg68 Дата: Понедельник, 26.09.2016, 15:31 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 64
Репутация: 2 ±
Замечаний: 0% ±

Excel 2013
В листе ВРЕМЯ ищется нужное время на каждый город и проставляется в листе Расстановка в ячейках F10:K11, после считается их сумма в ячейку Е6. То есть на каждый город отдельно.
Реально ли задать какое либо условие, вроде ЕСЛИ в ячейке F3 будет стоять например город Сочи, то общее время будет 13:00 и так на каждый отдельно.
 
Ответить
СообщениеВ листе ВРЕМЯ ищется нужное время на каждый город и проставляется в листе Расстановка в ячейках F10:K11, после считается их сумма в ячейку Е6. То есть на каждый город отдельно.
Реально ли задать какое либо условие, вроде ЕСЛИ в ячейке F3 будет стоять например город Сочи, то общее время будет 13:00 и так на каждый отдельно.

Автор - n0leg68
Дата добавления - 26.09.2016 в 15:31
_Boroda_ Дата: Понедельник, 26.09.2016, 17:57 | Сообщение № 4
Группа: Модераторы
Ранг: Экселист
Сообщений: 9352
Репутация: 3924 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно? :D :D :D
Код
=СУММ(СУММЕСЛИ(Время!D$6:D$35;ПОДСТАВИТЬ(ТРАНСП(ИНДЕКС(Города!F$4:AJ$6;ПОИСКПОЗ(P6;Города!D$4:D$6;);));"!";)&"";Время!R$6:R$35)-СУММПРОИЗВ(Общее!$I$5*ЕТЕКСТ(ЕСЛИ((F$3=Города!D$4:D$6)*(Общее!I14:I44=Города!F$3:AJ$3);Города!F$4:AJ$6)))-СУММЕСЛИ(Время!D$6:D$35;ПОДСТАВИТЬ(ТРАНСП(ИНДЕКС(Города!F$4:AJ$6;ПОИСКПОЗ(P6;Города!D$4:D$6;);));"!";)&"";Время!O$6:O$35)-СУММЕСЛИ(Время!D$6:D$35;ПОДСТАВИТЬ(ТРАНСП(ИНДЕКС(Города!F$4:AJ$6;ПОИСКПОЗ(P6;Города!D$4:D$6;);));"!";)&"";Время!V$6:V$35)+СУММЕСЛИ(Время!D$6:D$35;ПОДСТАВИТЬ(ТРАНСП(ИНДЕКС(Города!F$4:AJ$6;ПОИСКПОЗ(P6;Города!D$4:D$6;);));"!";)&"";Время!U$6:U$35))
К сообщению приложен файл: 1574152_1.xlsx(44Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно? :D :D :D
Код
=СУММ(СУММЕСЛИ(Время!D$6:D$35;ПОДСТАВИТЬ(ТРАНСП(ИНДЕКС(Города!F$4:AJ$6;ПОИСКПОЗ(P6;Города!D$4:D$6;);));"!";)&"";Время!R$6:R$35)-СУММПРОИЗВ(Общее!$I$5*ЕТЕКСТ(ЕСЛИ((F$3=Города!D$4:D$6)*(Общее!I14:I44=Города!F$3:AJ$3);Города!F$4:AJ$6)))-СУММЕСЛИ(Время!D$6:D$35;ПОДСТАВИТЬ(ТРАНСП(ИНДЕКС(Города!F$4:AJ$6;ПОИСКПОЗ(P6;Города!D$4:D$6;);));"!";)&"";Время!O$6:O$35)-СУММЕСЛИ(Время!D$6:D$35;ПОДСТАВИТЬ(ТРАНСП(ИНДЕКС(Города!F$4:AJ$6;ПОИСКПОЗ(P6;Города!D$4:D$6;);));"!";)&"";Время!V$6:V$35)+СУММЕСЛИ(Время!D$6:D$35;ПОДСТАВИТЬ(ТРАНСП(ИНДЕКС(Города!F$4:AJ$6;ПОИСКПОЗ(P6;Города!D$4:D$6;);));"!";)&"";Время!U$6:U$35))

Автор - _Boroda_
Дата добавления - 26.09.2016 в 17:57
n0leg68 Дата: Понедельник, 26.09.2016, 22:19 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 64
Репутация: 2 ±
Замечаний: 0% ±

Excel 2013
Так нужно?

Да Ваш файл правильно показывает, но все же я что, то не пойму... В формуле в городах я вроде разобрался, а вот с временем я что то ни как не разберусь. Почему в формуле время берется только за первое число. Я вот попробовал увеличить, допустим городов 100 и времени (ну если за месяц, то должно быть 930 ячеек) естественно должно быть больше. Как только не щелкал в формуле с листом ВРЕМЯ, но результат не правильно.
К сообщению приложен файл: 0854095.xlsx(48Kb)
 
Ответить
Сообщение
Так нужно?

Да Ваш файл правильно показывает, но все же я что, то не пойму... В формуле в городах я вроде разобрался, а вот с временем я что то ни как не разберусь. Почему в формуле время берется только за первое число. Я вот попробовал увеличить, допустим городов 100 и времени (ну если за месяц, то должно быть 930 ячеек) естественно должно быть больше. Как только не щелкал в формуле с листом ВРЕМЯ, но результат не правильно.

Автор - n0leg68
Дата добавления - 26.09.2016 в 22:19
_Boroda_ Дата: Понедельник, 26.09.2016, 22:31 | Сообщение № 6
Группа: Модераторы
Ранг: Экселист
Сообщений: 9352
Репутация: 3924 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Продлите формулы на листе Расстановка (столбцы С:М) вниз и будет Вам счастье. Вы ж у себя считаете только за 2 дня, а в Городах у Вас заполнено за 3. И моя формула, конечно же, считает за 3.
К сообщению приложен файл: 0854095_1.xlsx(52Kb)


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

Автор - _Boroda_
Дата добавления - 26.09.2016 в 22:31
n0leg68 Дата: Среда, 28.09.2016, 00:06 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 64
Репутация: 2 ±
Замечаний: 0% ±

Excel 2013


Да нет я был прав. Не правильно считает. Ячейки в вашей формуле берутся только - лист ВРЕМЯ с 6 по 35, а должны с 6 по 95. И если поставить разное время, то расчет по вашей формуле идет неправильно. Здесь вот тогда в формуле надо ставить не расчет, а уже как то наверное брать их рассчитанные с листа ВРЕМЯ ячейки J. И их вот суммировать уже. Но вот как???
К сообщению приложен файл: _0854095_1-7.xlsx(50Kb)


Сообщение отредактировал n0leg68 - Среда, 28.09.2016, 00:09
 
Ответить
Сообщение

Да нет я был прав. Не правильно считает. Ячейки в вашей формуле берутся только - лист ВРЕМЯ с 6 по 35, а должны с 6 по 95. И если поставить разное время, то расчет по вашей формуле идет неправильно. Здесь вот тогда в формуле надо ставить не расчет, а уже как то наверное брать их рассчитанные с листа ВРЕМЯ ячейки J. И их вот суммировать уже. Но вот как???

Автор - n0leg68
Дата добавления - 28.09.2016 в 00:06
Pelena Дата: Среда, 28.09.2016, 07:59 | Сообщение № 8
Группа: Модераторы
Ранг: Экселист
Сообщений: 9849
Репутация: 2254 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
n0leg68, а почему Вы, если введены данные для 2 партии, продолжительность двух партий не складываете, а вычитаете? В чём логика?


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщениеn0leg68, а почему Вы, если введены данные для 2 партии, продолжительность двух партий не складываете, а вычитаете? В чём логика?

Автор - Pelena
Дата добавления - 28.09.2016 в 07:59
pabchek Дата: Среда, 28.09.2016, 10:46 | Сообщение № 9
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 145 ±
Замечаний: 0% ±

Excel 2007
Здравствуйте!
Мой вариант, наверняка можно компактнее. Если формула считает так как надо, можно укоротить( формула массива)

Код
=СУММ(ЕСЛИ(Время!D$5:D$94=ИНДЕКС((Города!D$4:D$18=T6)*Города!F$4:H$18;ПОИСКПОЗ(T6;Города!D$4:D$18;0))&"";(Время!F$5:F$94-Время!E$5:E$94)+(Время!H$5:H$94-Время!G$5:G$94)))


[p.s.]почему то формула в теги некорректно вставляется((. Решил оставить корявую, может модераторы скажут почему?
К сообщению приложен файл: _-2.xlsb(32Kb)


"Учиться, учиться и еще раз учиться!"
WM: R399923528092


Сообщение отредактировал pabchek - Среда, 28.09.2016, 11:10
 
Ответить
СообщениеЗдравствуйте!
Мой вариант, наверняка можно компактнее. Если формула считает так как надо, можно укоротить( формула массива)

Код
=СУММ(ЕСЛИ(Время!D$5:D$94=ИНДЕКС((Города!D$4:D$18=T6)*Города!F$4:H$18;ПОИСКПОЗ(T6;Города!D$4:D$18;0))&"";(Время!F$5:F$94-Время!E$5:E$94)+(Время!H$5:H$94-Время!G$5:G$94)))


[p.s.]почему то формула в теги некорректно вставляется((. Решил оставить корявую, может модераторы скажут почему?

Автор - pabchek
Дата добавления - 28.09.2016 в 10:46
Pelena Дата: Среда, 28.09.2016, 11:03 | Сообщение № 10
Группа: Модераторы
Ранг: Экселист
Сообщений: 9849
Репутация: 2254 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Виктор, пока не починят теги, можно при вставке формул снимать флажок Включить смайлы чуть ниже формы сообщения


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеВиктор, пока не починят теги, можно при вставке формул снимать флажок Включить смайлы чуть ниже формы сообщения

Автор - Pelena
Дата добавления - 28.09.2016 в 11:03
pabchek Дата: Среда, 28.09.2016, 11:11 | Сообщение № 11
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 145 ±
Замечаний: 0% ±

Excel 2007
снимать флажок Включить смайлы

Ага, получилось ))


"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
Сообщение
снимать флажок Включить смайлы

Ага, получилось ))

Автор - pabchek
Дата добавления - 28.09.2016 в 11:11
pabchek Дата: Среда, 28.09.2016, 11:15 | Сообщение № 12
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 145 ±
Замечаний: 0% ±

Excel 2007
ну и не массивная
Код
=СУММПРОИЗВ((Время!D$5:D$94=ИНДЕКС((Города!D$4:D$18=T6)*Города!F$4:H$18;ПОИСКПОЗ(T6;Города!D$4:D$18;0))&"")*(Время!F$5:F$94-Время!E$5:E$94+Время!H$5:H$94-Время!G$5:G$94))


"Учиться, учиться и еще раз учиться!"
WM: R399923528092


Сообщение отредактировал pabchek - Среда, 28.09.2016, 11:15
 
Ответить
Сообщениену и не массивная
Код
=СУММПРОИЗВ((Время!D$5:D$94=ИНДЕКС((Города!D$4:D$18=T6)*Города!F$4:H$18;ПОИСКПОЗ(T6;Города!D$4:D$18;0))&"")*(Время!F$5:F$94-Время!E$5:E$94+Время!H$5:H$94-Время!G$5:G$94))

Автор - pabchek
Дата добавления - 28.09.2016 в 11:15
n0leg68 Дата: Среда, 28.09.2016, 22:23 | Сообщение № 13
Группа: Пользователи
Ранг: Участник
Сообщений: 64
Репутация: 2 ±
Замечаний: 0% ±

Excel 2013
Если формула считает так как надо, можно укоротить

Да нет не тот результат, результат должен быть равен тому, который выпадает в строке Е6, когда выбираешь город.
То есть я выбираю например Сочи, результат 24:12 и в результате формулы мне требуется что бы он был заранее посчитан, т.е. тоже 24:12.


Сообщение отредактировал n0leg68 - Среда, 28.09.2016, 22:23
 
Ответить
Сообщение
Если формула считает так как надо, можно укоротить

Да нет не тот результат, результат должен быть равен тому, который выпадает в строке Е6, когда выбираешь город.
То есть я выбираю например Сочи, результат 24:12 и в результате формулы мне требуется что бы он был заранее посчитан, т.е. тоже 24:12.

Автор - n0leg68
Дата добавления - 28.09.2016 в 22:23
_Boroda_ Дата: Четверг, 29.09.2016, 10:19 | Сообщение № 14
Группа: Модераторы
Ранг: Экселист
Сообщений: 9352
Репутация: 3924 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так?
Код
=СУММ(СУММЕСЛИМН(Время!J$6:J$95;Время!D$6:D$95;ПОДСТАВИТЬ(ТРАНСП(ИНДЕКС(Города!F$4:AJ$104;ПОИСКПОЗ(T7;Города!D$4:D$104;);));"!";)&"";Время!C$6:C$95;Общее!I$15:I$45))-СУММ(Общее!$I$5*ЕТЕКСТ(ЕСЛИ((F$3=Города!D$4:D$104)*(Общее!I15:I45=Города!F$3:AJ$3);Города!F$4:AJ$104)))
К сообщению приложен файл: _0854095_1-7-1-.xlsx(51Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак?
Код
=СУММ(СУММЕСЛИМН(Время!J$6:J$95;Время!D$6:D$95;ПОДСТАВИТЬ(ТРАНСП(ИНДЕКС(Города!F$4:AJ$104;ПОИСКПОЗ(T7;Города!D$4:D$104;);));"!";)&"";Время!C$6:C$95;Общее!I$15:I$45))-СУММ(Общее!$I$5*ЕТЕКСТ(ЕСЛИ((F$3=Города!D$4:D$104)*(Общее!I15:I45=Города!F$3:AJ$3);Города!F$4:AJ$104)))

Автор - _Boroda_
Дата добавления - 29.09.2016 в 10:19
n0leg68 Дата: Пятница, 30.09.2016, 11:35 | Сообщение № 15
Группа: Пользователи
Ранг: Участник
Сообщений: 64
Репутация: 2 ±
Замечаний: 0% ±

Excel 2013

Ну почти, сейчас разбирался и нашел одно но...

Эта формула работает правильно, только тогда, когда в листе ГОРОДА в заполнении не ставится - ! (восклицательный знак), а вот если его поставить, то формула ведет не верный расчет. Например если выбрать Вологда, то показывает правильно, а если другой - Ялта или Сочи, то не правильно, потому что там добавлены ! (восклицательные знаки) - при которых ко времени не должно добавляеться 45 мин.

То есть заморочка осталась добавлением времени с листа Общее I5 если в листе города есть ! (восклицательные знаки), то есть оно не должно добавляться если стоит этот знак.
Подскажите какое следует внести изменение в формуле в выложенном файле.
К сообщению приложен файл: -0854095_1-7-1-.xlsx(51Kb)


Сообщение отредактировал n0leg68 - Пятница, 30.09.2016, 11:48
 
Ответить
Сообщение
Ну почти, сейчас разбирался и нашел одно но...

Эта формула работает правильно, только тогда, когда в листе ГОРОДА в заполнении не ставится - ! (восклицательный знак), а вот если его поставить, то формула ведет не верный расчет. Например если выбрать Вологда, то показывает правильно, а если другой - Ялта или Сочи, то не правильно, потому что там добавлены ! (восклицательные знаки) - при которых ко времени не должно добавляеться 45 мин.

То есть заморочка осталась добавлением времени с листа Общее I5 если в листе города есть ! (восклицательные знаки), то есть оно не должно добавляться если стоит этот знак.
Подскажите какое следует внести изменение в формуле в выложенном файле.

Автор - n0leg68
Дата добавления - 30.09.2016 в 11:35
_Boroda_ Дата: Понедельник, 03.10.2016, 20:45 | Сообщение № 16
Группа: Модераторы
Ранг: Экселист
Сообщений: 9352
Репутация: 3924 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так?
Код
=СУММ(СУММЕСЛИМН(Время!J$6:J$95;Время!D$6:D$95;ПОДСТАВИТЬ(ТРАНСП(ИНДЕКС(Города!F$4:AJ$104;ПОИСКПОЗ(T6;Города!D$4:D$104;);));"!";)&"";Время!C$6:C$95;Общее!I$15:I$45))-СУММ(Общее!$I$5*ЕТЕКСТ(ИНДЕКС(Города!F$4:AJ$104;ПОИСКПОЗ(T6;Города!D$4:D$104;);)))
К сообщению приложен файл: 6535154.xlsx(54Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак?
Код
=СУММ(СУММЕСЛИМН(Время!J$6:J$95;Время!D$6:D$95;ПОДСТАВИТЬ(ТРАНСП(ИНДЕКС(Города!F$4:AJ$104;ПОИСКПОЗ(T6;Города!D$4:D$104;);));"!";)&"";Время!C$6:C$95;Общее!I$15:I$45))-СУММ(Общее!$I$5*ЕТЕКСТ(ИНДЕКС(Города!F$4:AJ$104;ПОИСКПОЗ(T6;Города!D$4:D$104;);)))

Автор - _Boroda_
Дата добавления - 03.10.2016 в 20:45
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Расчет суммы при поиске требуемых значений (Формулы/Formulas)
Страница 1 из 11
Поиск:

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