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

Вход

Регистрация

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

 

= Мир MS Excel/сумма столбца по нескольким условиям - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » сумма столбца по нескольким условиям (Формулы/Formulas)
сумма столбца по нескольким условиям
ovechkin1973 Дата: Четверг, 16.03.2017, 21:52 | Сообщение № 1
Группа: Проверенные
Ранг: Обитатель
Сообщений: 429
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
Всем привет. Прошу помощи в написании формулы для расчета суммы столбца по нескольким условиям. Сумма должна подставляться в желтые ячейки. Сумма должна состоять в зависимости от букв в столбце. В зависимости от букв в ячейка данные берутся из зеленых столбцов. Самое главное, что при подсчете суммы не учитывались строки, в которых содержится слова "мотор", "весло" - столбец "В"(в любой комбинации эти слова не должны учитываться, т.е. "мотор редуктор" тоже учитывать нельзя), а так же не должно быть слов "умник" в строках (столбец "E"), из которых берутся данные для общей суммы. Ну и самое главное - нужно иметь одну формулу только в желтых ячейках.. без дополнительных столбцов для расчета.. Помогите пожалуйста для образца для столбца "F" .. дальше сам разберусь.
PS - надеюсь внятно объяснил свою хотелку. Сумма в F3 должна получиться 2590 (надеюсь не ошибся, пока фильтрами баловался)
К сообщению приложен файл: 9295433.xlsx (17.0 Kb)

Плохо когда не знаешь, да еще забудешь. Правильно сформулированный вопрос содержит половину ответа.
 
Ответить
СообщениеВсем привет. Прошу помощи в написании формулы для расчета суммы столбца по нескольким условиям. Сумма должна подставляться в желтые ячейки. Сумма должна состоять в зависимости от букв в столбце. В зависимости от букв в ячейка данные берутся из зеленых столбцов. Самое главное, что при подсчете суммы не учитывались строки, в которых содержится слова "мотор", "весло" - столбец "В"(в любой комбинации эти слова не должны учитываться, т.е. "мотор редуктор" тоже учитывать нельзя), а так же не должно быть слов "умник" в строках (столбец "E"), из которых берутся данные для общей суммы. Ну и самое главное - нужно иметь одну формулу только в желтых ячейках.. без дополнительных столбцов для расчета.. Помогите пожалуйста для образца для столбца "F" .. дальше сам разберусь.
PS - надеюсь внятно объяснил свою хотелку. Сумма в F3 должна получиться 2590 (надеюсь не ошибся, пока фильтрами баловался)
Автор - ovechkin1973
Дата добавления - 16.03.2017 в 21:52
Pelena Дата: Четверг, 16.03.2017, 22:46 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19511
Репутация: 4620 ±
Замечаний: ±

Excel 365 & Mac Excel
Код
=СУММПРОИЗВ((F$4:F$101="Т")*$W$4:$W$101+(F$4:F$101="К")*$V$4:$V$101;ЕОШИБКА(ПОИСК("мотор";$B$4:$B$101))*ЕОШИБКА(ПОИСК("весло";$B$4:$B$101))*ЕОШИБКА(ПОИСК("умник";$E$4:$E$101)))
К сообщению приложен файл: 9350003.xlsx (16.6 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Код
=СУММПРОИЗВ((F$4:F$101="Т")*$W$4:$W$101+(F$4:F$101="К")*$V$4:$V$101;ЕОШИБКА(ПОИСК("мотор";$B$4:$B$101))*ЕОШИБКА(ПОИСК("весло";$B$4:$B$101))*ЕОШИБКА(ПОИСК("умник";$E$4:$E$101)))
Автор - Pelena
Дата добавления - 16.03.2017 в 22:46
bmv98rus Дата: Четверг, 16.03.2017, 22:56 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
С фильтром вы ошиблись
вараинт фактически повторяет вариант от Pelena
Код
=SUMPRODUCT(ISERROR((SEARCH({"мотор";"весло"};$B$4:$B$101)))*ISERROR(SEARCH("умник";$E$4:$E$101))*((F$4:F$101="Т")*$W$4:$W$101+(F$4:F$101="К")*$V$4:$V$101))

И СОДЕРЖИТ ОШИБКУ

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Пятница, 17.03.2017, 11:13
 
Ответить
СообщениеС фильтром вы ошиблись
вараинт фактически повторяет вариант от Pelena
Код
=SUMPRODUCT(ISERROR((SEARCH({"мотор";"весло"};$B$4:$B$101)))*ISERROR(SEARCH("умник";$E$4:$E$101))*((F$4:F$101="Т")*$W$4:$W$101+(F$4:F$101="К")*$V$4:$V$101))

И СОДЕРЖИТ ОШИБКУ
Автор - bmv98rus
Дата добавления - 16.03.2017 в 22:56
ovechkin1973 Дата: Пятница, 17.03.2017, 09:49 | Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 429
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
Спасибо.. пока к своему файлу эти формулы "приделать" не могу.. дождусь сына их института.. может ткнет, где я ошибся

Плохо когда не знаешь, да еще забудешь. Правильно сформулированный вопрос содержит половину ответа.
 
Ответить
СообщениеСпасибо.. пока к своему файлу эти формулы "приделать" не могу.. дождусь сына их института.. может ткнет, где я ошибся
Автор - ovechkin1973
Дата добавления - 17.03.2017 в 09:49
Pelena Дата: Пятница, 17.03.2017, 10:18 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 19511
Репутация: 4620 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата bmv98rus, 16.03.2017 в 22:56, в сообщении № 3 ()
вараинт фактически повторяет вариант от Pelena

не совсем.
У меня отсутствие слов "мотор" и "весло" проверяется через И. То есть нет ни того, ни другого. А у Вас - через ИЛИ, что по сути вернет всегда ИСТИНА

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата bmv98rus, 16.03.2017 в 22:56, в сообщении № 3 ()
вараинт фактически повторяет вариант от Pelena

не совсем.
У меня отсутствие слов "мотор" и "весло" проверяется через И. То есть нет ни того, ни другого. А у Вас - через ИЛИ, что по сути вернет всегда ИСТИНА
Автор - Pelena
Дата добавления - 17.03.2017 в 10:18
bmv98rus Дата: Пятница, 17.03.2017, 11:12 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата Pelena, 17.03.2017 в 10:18, в сообщении № 5 ()
А у Вас - через ИЛИ,

ДОУПРОЩАЛСЯ :-(

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата Pelena, 17.03.2017 в 10:18, в сообщении № 5 ()
А у Вас - через ИЛИ,

ДОУПРОЩАЛСЯ :-(
Автор - bmv98rus
Дата добавления - 17.03.2017 в 11:12
AlexM Дата: Пятница, 17.03.2017, 11:39 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Код
=СУММПРОИЗВ(МУМНОЖ((F4:F101={"К";"Т"})*$V4:$W101;{1:1});Ч(МУМНОЖ(--ЕОШИБКА(ПОИСК({"мотор";"весло";"умник"};$B4:$B101&$E4:$E101));{1:1:1})=3))
К сообщению приложен файл: 9295433_01.xlsx (17.5 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Пятница, 17.03.2017, 11:43
 
Ответить
Сообщение
Код
=СУММПРОИЗВ(МУМНОЖ((F4:F101={"К";"Т"})*$V4:$W101;{1:1});Ч(МУМНОЖ(--ЕОШИБКА(ПОИСК({"мотор";"весло";"умник"};$B4:$B101&$E4:$E101));{1:1:1})=3))
Автор - AlexM
Дата добавления - 17.03.2017 в 11:39
bmv98rus Дата: Пятница, 17.03.2017, 12:25 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
AlexM,
Вот умеете Вы "унизить" :-). . СПС. очень интересный и хороший вариант.

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Пятница, 17.03.2017, 12:25
 
Ответить
СообщениеAlexM,
Вот умеете Вы "унизить" :-). . СПС. очень интересный и хороший вариант.
Автор - bmv98rus
Дата добавления - 17.03.2017 в 12:25
ovechkin1973 Дата: Пятница, 17.03.2017, 14:54 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 429
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
Еще раз огромное спасибо.. разобрался почему в моем файле формула не работала. У меня в столбцах под "К" и "Т" были в ячейках не только числа, но и символы.. От такого "дурака" защита бывает? Я в смысле, чтобы формула игнорировала эти символы? Если нет, то не велика проблема.. будет индикатор ошибки при заполнении данных.
Но формулы, которые делал мне сын для подсчета с использованием резервных столбцов дают к сожалению разные результаты, с AlexM и Pelena. Где он ошибся?
К сообщению приложен файл: 8727609.xlsx (32.2 Kb)

Плохо когда не знаешь, да еще забудешь. Правильно сформулированный вопрос содержит половину ответа.
 
Ответить
СообщениеЕще раз огромное спасибо.. разобрался почему в моем файле формула не работала. У меня в столбцах под "К" и "Т" были в ячейках не только числа, но и символы.. От такого "дурака" защита бывает? Я в смысле, чтобы формула игнорировала эти символы? Если нет, то не велика проблема.. будет индикатор ошибки при заполнении данных.
Но формулы, которые делал мне сын для подсчета с использованием резервных столбцов дают к сожалению разные результаты, с AlexM и Pelena. Где он ошибся?
Автор - ovechkin1973
Дата добавления - 17.03.2017 в 14:54
Pelena Дата: Пятница, 17.03.2017, 15:35 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 19511
Репутация: 4620 ±
Замечаний: ±

Excel 365 & Mac Excel
Не вижу отличий. Все результаты одинаковые

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеНе вижу отличий. Все результаты одинаковые
Автор - Pelena
Дата добавления - 17.03.2017 в 15:35
bmv98rus Дата: Пятница, 17.03.2017, 16:12 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Отличий небыло, разве что Елена, Вас усыновили :-)

если от дурака защита то немного по иному.
массивная
Код
=SUM(((F$6:F$103="Т")*IF(ISNUMBER($W$6:$W$103);$W$6:$W$103;0)+(F$6:F$103="К")*IF(ISNUMBER($V$6:$V$103);$V$6:$V$103;0))*ISERROR(SEARCH("мотор";$B$6:$B$103))*ISERROR(SEARCH("весло";$B$6:$B$103))*ISERROR(SEARCH("умник";$E$6:$E$103)))


или (позволили себе объединить два варианта)
Код
=SUM(((F$6:F$103="Т")*IF(ISNUMBER($W$6:$W$103);$W$6:$W$103;0)+(F$6:F$103="К")*IF(ISNUMBER($V$6:$V$103);$V$6:$V$103;0))*N(MMULT(--ISERROR(SEARCH({"мотор"\"весло"\"умник"};$B6:$B103&$E6:$E103));{1;1;1})=3))
К сообщению приложен файл: Copy_of_8727609.xlsx (29.7 Kb)

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Пятница, 17.03.2017, 16:16
 
Ответить
СообщениеОтличий небыло, разве что Елена, Вас усыновили :-)

если от дурака защита то немного по иному.
массивная
Код
=SUM(((F$6:F$103="Т")*IF(ISNUMBER($W$6:$W$103);$W$6:$W$103;0)+(F$6:F$103="К")*IF(ISNUMBER($V$6:$V$103);$V$6:$V$103;0))*ISERROR(SEARCH("мотор";$B$6:$B$103))*ISERROR(SEARCH("весло";$B$6:$B$103))*ISERROR(SEARCH("умник";$E$6:$E$103)))


или (позволили себе объединить два варианта)
Код
=SUM(((F$6:F$103="Т")*IF(ISNUMBER($W$6:$W$103);$W$6:$W$103;0)+(F$6:F$103="К")*IF(ISNUMBER($V$6:$V$103);$V$6:$V$103;0))*N(MMULT(--ISERROR(SEARCH({"мотор"\"весло"\"умник"};$B6:$B103&$E6:$E103));{1;1;1})=3))
Автор - bmv98rus
Дата добавления - 17.03.2017 в 16:12
AlexM Дата: Пятница, 17.03.2017, 16:37 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Если от дурака, то формула массива
Код
=СУММПРОИЗВ(МУМНОЖ((F4:F101={"К";"Т"})*ЕСЛИ(ЕЧИСЛО($V4:$W101);$V4:$W101);{1:1});Ч(МУМНОЖ(--ЕОШИБКА(ПОИСК({"мотор";"весло";"умник"};$B4:$B101&$E4:$E101));{1:1:1})=3))
К сообщению приложен файл: 9295433_02.xlsx (17.9 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеЕсли от дурака, то формула массива
Код
=СУММПРОИЗВ(МУМНОЖ((F4:F101={"К";"Т"})*ЕСЛИ(ЕЧИСЛО($V4:$W101);$V4:$W101);{1:1});Ч(МУМНОЖ(--ЕОШИБКА(ПОИСК({"мотор";"весло";"умник"};$B4:$B101&$E4:$E101));{1:1:1})=3))
Автор - AlexM
Дата добавления - 17.03.2017 в 16:37
Мир MS Excel » Вопросы и решения » Вопросы по Excel » сумма столбца по нескольким условиям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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