Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Функция СУММЕСЛИ
 
Есть функция СУММЕСЛИ , которая складывает числа по заданному условию. Например подситай количество груш. А как критерием условия задать "считай груши и сливы".
 
Этой функцией никак.  
В 2007 экселе есть функция суммеслимн(), там можно задавать несколько критериев.  
 
Но есть целая куча других вариантов, к примеру:  
Самый удобный, но и самый "тяжёлый" вариант:  
=СУММПРОИЗВ(((A2:A11="груши")+(A2:A11="бананы"))*B2:B11)  
Этой же функцией можно задавать и несколько критериев.  
Вариант "или" представлен выше.  
для "и" нужно просто умножать.  
К примеру, в столбце С представлены поставщики.  
Нам нужно сумма груш и бананов от Васи, тогда формула будет следующей:  
=СУММПРОИЗВ(((A2:A11="груши")+(A2:A11="бананы"))*(C2:C11="вася")*B2:B11)  
 
 
Ну и другие варианты:  
=суммесли(груши)+суммесли(бананы)  
Разные извращения с сумм() и если(), массивы и т. п.
 
Большое спасибо!
 
Этой функцией очень даже "как" и главное в разы быстрее, чем конструкцией с СУММПРОИЗВ();-)  
 
с константами в качестве критериев:  
 
=СУММ(СУММЕСЛИ(A2:A11;{"груши";"сливы";"бананы"};B2:B11))  
 
если критерии находятся в смежных ячейках, то так:  
 
=СУММПРОИЗВ(СУММЕСЛИ(A2:A11;D1:D3;B2:B11))
KL
 
Угу, только это таки не суммесли() в чистом виде :) Это то, что я описал как "разные извращения с сумм() и если(), массивы и т. п."  
Но согласись, объяснить даже на таком примере как обращаться намного сложнее, а для добавления второго критерия "и", что входит в топ10 вопросов этого форума :) , требуются извращения уже совершенно другого порядка :)  
 
 
PS: если кто-то будет использовать, то там очепятка в функции сумм(), "а" на конце не нужно.
 
а можно формулу СЧЁТЕСЛИ подстроить под два условия?  
Мне например надо найти количество дней отпуска, но без Сб и Вс.
 
{quote}{login=}{date=27.07.2009 10:43}{thema=}{post}Угу, только это таки не суммесли() в чистом виде :) Это то, что я описал как "разные извращения с сумм() и если(), массивы и т. п."  
Но согласись, объяснить даже на таком примере как обращаться намного сложнее, а для добавления второго критерия "и", что входит в топ10 вопросов этого форума :) , требуются извращения уже совершенно другого порядка :)  
 
 
PS: если кто-то будет использовать, то там очепятка в функции сумм(), "а" на конце не нужно.{/post}{/quote}  
Видите ли, к извращениям я-то как раз отношу использование тяжеловесной конструкции с СУММПРОИЗВ() там, где без нее можно обойтись, и заставлять других тратить ресурсы компьютера, безответственно утверждая что "Этой функцией никак".    
 
СУММПРОИЗВ() - это грубый перебор и место ему там, где нельзя использовать быстрые функции типа СУММЕСЛИ().    
 
Насчет чистоты - в формулах не важна чистота - важен результат. Если хотите чистоты, то вот вам чистая СУММЕСЛИ(), также в разы превосходящая по скорости пересчета СУММПРОИЗВ(), намного легче объяснимая и почти столь же длинная что и предложенная вами формула:  
 
=СУММЕСЛИ(A2:A11;"груши";B2:B11)+СУММЕСЛИ(A2:A11;"сливы";B2:B11)+СУММЕСЛИ(A2:A11;"бананы";B2:B11)  
 
Мой опыт показывает, что объяснить это не сложнее, чем СУММПРОИЗВ(), а даже проще - было бы желание и понимание у объясняющего ;-)
KL
 
{quote}{login=Дмитрий Д}{date=27.07.2009 12:20}{thema=}{post}а можно формулу СЧЁТЕСЛИ подстроить под два условия?  
Мне например надо найти количество дней отпуска, но без Сб и Вс.{/post}{/quote}  
 
Посмотрите функцию ЧИСТРАБДНИ() в Пакете Инструментоа Анализа
KL
 
Кстати, об извращениях: конструкция {СУММ(ЕСЛИ())} - родная сестра (причем старшая) конструкции СУММПРОИЗВ(), столь же неповоротливая, и отличающаяся вводом Ctrl+Shift+Enter, ростом размера файла и некоторой дополнительной потерей в скорости ;-)  
 
И еще, умножение внутри СУММПРОИЗВ() еще больше ее замедляет. Поэтому, в тех случаях когда применение СУММПРОИЗВ() целесообразно, эффективнее использовать конструкцию:  
 
=СУММПРОИЗВ(--((A2:A11="груши")+(A2:A11="бананы"));--(C2:C11="вася");B2:B11)  
 
Данная конструкция не будет работать с перпендикулярными и двухмерными массивами (матрицами).
KL
 
Во первых, давай немного спокойнее разговаривать.  
Во вторых, я суммесли()+суммесли() тоже указал, именно по обозначенной выше причине. Никак не из-за "чистоты". Каким бы "ламерским" это решение по мнению, возможно, многих не являлось. Тут имело значение исключительно сложность самой конструкции и понимания оной конечным пользователем. А от этого сильно зависит дальнейшая работа. Ты разве будешь с этим спорить?  
И так же указал, что суммпроизв() таки да, один из самых "тяжёловесных" вариантов. Кто ж спорит?  
 
Но, возвращаясь к, собственно, причинам, по которым я привёл именно функцию суммпроизв():  
Чаще всего, по моим наблюдениям, вопрос заключается именно в возможности применения нескольких критериев "и" и "или". А объяснять нужно, начиная, с, в твоём случае, понятия констант, скобок и массивов. А потом переделывать формулу 10 раз, отвечая на возникающие вопросы, т к требуемая конструкция может меняться. А суммпроизв() всё же будет универсальнее и, по моему мнению, проще.    
 
Я допустил, что скорее всего понадобится так же и "и", сразу и объяснив.  
В твоём случае, объяснения заняли бы намного больше времени. Причём, стоит заметить, не только твоего времени.  
Да и удобство часто бывает важнее производительности. Не всегда, конечно же.  
 
Upd: спасибо, было интересно прочитать твой последний пост. Но ты уверен, что это всем было бы интересно и, главное, практически полезно и понятно?
 
{quote}{login=Дмитрий Д}{date=27.07.2009 12:20}{thema=}{post}а можно формулу СЧЁТЕСЛИ подстроить под два условия?  
Мне например надо найти количество дней отпуска, но без Сб и Вс.{/post}{/quote}  
Вот Вам и СЧЕТ, и ЕСЛИ, но раздельно :)  
Формула массива:  
=СЧЁТ(ЕСЛИ((B4:AF4="О")*(B3:AF3<>"Сб")*(B3:AF3<>"Вс");))
 
{quote}{login=}{date=27.07.2009 01:06}{thema=}{post}Во первых, давай немного спокойнее разговаривать.  
Во вторых, я суммесли()+суммесли() тоже указал, именно по обозначенной выше причине. Никак не из-за "чистоты". Каким бы "ламерским" это решение по мнению, возможно, многих не являлось. Тут имело значение исключительно сложность самой конструкции и понимания оной конечным пользователем. А от этого сильно зависит дальнейшая работа. Ты разве будешь с этим спорить?  
И так же указал, что суммпроизв() таки да, один из самых "тяжёловесных" вариантов. Кто ж спорит?  
 
Но, возвращаясь к, собственно, причинам, по которым я привёл именно функцию суммпроизв():  
Чаще всего, по моим наблюдениям, вопрос заключается именно в возможности применения нескольких критериев "и" и "или". А объяснять нужно, начиная, с, в твоём случае, понятия констант, скобок и массивов. А потом переделывать формулу 10 раз, отвечая на возникающие вопросы, т к требуемая конструкция может меняться. А суммпроизв() всё же будет универсальнее и, по моему мнению, проще.    
 
Я допустил, что скорее всего понадобится так же и "и", сразу и объяснив.  
В твоём случае, объяснения заняли бы намного больше времени. Причём, стоит заметить, не только твоего времени.  
Да и удобство часто бывает важнее производительности. Не всегда, конечно же.  
 
Upd: спасибо, было интересно прочитать твой последний пост. Но ты уверен, что это всем было бы интересно и, главное, практически полезно и понятно?{/post}{/quote}  
Во первых переходи на Вы или разговаривай сам с собой.  
1) Я спокоен как танк - ирония форма моего общения, я вас не заставлял говорить об извращениях и вообще комментировать мое решение ;-)  
2) Чистота была приведена Вами как аргумент дисквлафицирующий утверждение о том, что решение с суммесли() возможно  
3) По т.н. несложности конструкции всех бъет =суммесли()+суммесли()  
4) Формулу с суммесли() в сочетании с константами массива нужно переделывать не больше раз, чем формулу с суммпроизв() с константами. То же самое работает для вариантов с критериями в диапазонах.  
5) Насчет времени необходимого на объяснение - это все сослагательное наклонение: если б да кабы... Мне обычно не требуется много "моего" времени на объяснения.  
6) Удобство понятие субъективное и я его не обсуждаю. Вы расскажите об удобстве тому, у кого из-за советов использовать сумпроизв() направо и налево, книга считает по 5 минут после каждого пересчета ;-)  
7) Нет не уверен (что это всем было бы интересно), но мне и не нужно, чтобы это было интересно всем. Интернет чем хорош: не интересно - не читаем. Мне важно, чтобы тот кому интересно не был обделен информацией.
KL
 
Ок, всадник белого носорога, ты победил.
 
жжёте :-)
 
{quote}{login=vikttur}{date=27.07.2009 02:47}{thema=Re: }{post}  
Вот Вам и СЧЕТ, и ЕСЛИ, но раздельно :)  
Формула массива:  
=СЧЁТ(ЕСЛИ((B4:AF4="О")*(B3:AF3<>"Сб")*(B3:AF3<>"Вс");)){/post}{/quote}  
Спасибо, если пакет анализа на работе не установлю, буду пользоваться. Да и где еще пригодится.
 
Мне необходимо посчитать в таблице число троечников, то есть людей со средним балом выше 2,5 и ниже 3,8... Какую формулу мне применять и как вообще это сделать???
 
Опять контрольная?  
Разница между СЧЕТЕСЛИ_для_>=3,8 и СЧЕТЕСЛИ_для_>2,5
 
Подскажите, пожалуйста, как в Excel сделать выборку из одного столбца по какому-то значению (например 2) и внести данные из другого столбца этой же строки (форматы разные) на другой лист. Другими словами нужно отсортировать данные по значению одного столбца  и результаты сортировки представить на другом листе.  
Реально ли это сделать, не прибегая к макросам?
 
{quote}{login=Сергей}{date=10.11.2011 06:34}{thema=}{post}Подскажите... нужно отсортировать данные по значению одного столбца  и результаты сортировки представить на другом листе. Реально ли это сделать, не прибегая к макросам?{/post}{/quote}  
Да, как вариант - расширенный фильтр (вызывать РФ с листа, на котором надо получить данные)... ;)  
-14986-
Страницы: 1
Читают тему (гостей: 1)
Наверх