Профессиональные приемы работы в Microsoft Excel

Пожалуйста, войдите или зарегистрируйтесь.


Расширенный поиск  

Новости:

Из правил форума: Тема должна отражать суть вопроса, топики типа "help please" будут удаляться!

Автор Тема: =СУММЕСЛИ() с критерием НЕ ПУСТО.  (Прочитано 26475 раз)

0 Пользователей и 1 Гость просматривают эту тему.

Shadowmaker

  • Постоялец
  • ***
  • Уважение: +1/-0
  • Оффлайн Оффлайн
  • Сообщений: 154

прошу помощи по формуле СУММЕСЛИ()
Есть 2 столбика А и В: в А числовые значения - в В разные данные. Необходимо в ячейке С1 сложить все значения из столбика А, напротив которых в столбике В есть непустые ячейки.

Как должна выглядеть эта формула. Если бы данные в В были всегда одинаковы - всё очевидно... =СУММЕСЛИ(А:А;"данные";В:В) но эти самые данные всегда разные - а формулу =СУММЕСЛИ(А:А;<>"";В:В) эксель не принимает. Что делать?
« Последнее редактирование: 03.09.2014, 16:47:08 от ShAM »
Записан

vikttur

  • Глобальный модератор
  • Ветеран
  • *****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 015
Re: =СУММЕСЛИ(...
« Ответ #1 : 03.09.2014, 13:51:54 »

О названии темы из правил:
Цитировать
Тема должна отражать суть вопроса
Записан

zs

  • Старожил
  • ****
  • Уважение: +12/-0
  • Оффлайн Оффлайн
  • Сообщений: 577
Re: =СУММЕСЛИ(...
« Ответ #2 : 03.09.2014, 14:52:12 »

Здравствуйте, Shadowmaker
а формулу =СУММЕСЛИ(А:А;<>"";В:В) эксель не принимает. Что делать?
=СУММЕСЛИ(B1:B5;"<>*";A1:A5)
С уважением, ZS5
Записан

IKor

  • Старожил
  • ****
  • Уважение: +49/-0
  • Оффлайн Оффлайн
  • Сообщений: 902
Re: =СУММЕСЛИ(...
« Ответ #3 : 03.09.2014, 14:53:17 »

В формуле СУММЕСЛИ(), а также в ряде других формул допускается использовать символы "*" и "?" для создания подходящей маски (* = любая последовательность символов; ? = любой символ)
Т.к. пустые ячейки не содержат ни одного символа, то вам поможет формула
=СУММЕСЛИ(B:B;"*";A:A)
Записан

IKor

  • Старожил
  • ****
  • Уважение: +49/-0
  • Оффлайн Оффлайн
  • Сообщений: 902
Re: =СУММЕСЛИ(...
« Ответ #4 : 03.09.2014, 14:56:35 »

Пардон, пропустил ключевую частицу НЕ.
Отдавая должное лаконичности формулы предложенной ZS рискну предложить еще вариант
=СУММ(A:A)-СУММЕСЛИ(B:B;"*";A:A)
Записан

zs

  • Старожил
  • ****
  • Уважение: +12/-0
  • Оффлайн Оффлайн
  • Сообщений: 577
Re: =СУММЕСЛИ(...
« Ответ #5 : 03.09.2014, 15:17:34 »

Здравствуйте, IKor!
формулы предложенной ZS )
Разрешите уточнить, IKor. ZS5 )
Записан

Shadowmaker

  • Постоялец
  • ***
  • Уважение: +1/-0
  • Оффлайн Оффлайн
  • Сообщений: 154
Re: =СУММЕСЛИ(...
« Ответ #6 : 03.09.2014, 16:40:15 »

...
У меня получилось что-то вроде СУММЕСЛИ() наоборот:
=СУММ($G$10:$G$1000)-СУММЕСЛИ(N$10:N$1000;"";$G$10:$G$1000)
Записан

Pelena

  • Постоялец
  • ***
  • Уважение: +35/-0
  • Оффлайн Оффлайн
  • Сообщений: 282
Re: =СУММЕСЛИ(...
« Ответ #7 : 03.09.2014, 16:45:43 »

Вариант
=СУММЕСЛИ(B1:B5;"<>";A1:A5)
Записан

ShAM

  • Глобальный модератор
  • Постоялец
  • *****
  • Уважение: +32/-0
  • Оффлайн Оффлайн
  • Сообщений: 294
Re: =СУММЕСЛИ() с критерием НЕ ПУСТО.
« Ответ #8 : 03.09.2014, 16:49:00 »

Ребята, помогаете нарушителям! Поправил название темы, как смог.
У меня до варианта Елены было:
=СУММЕСЛИ(B1:B5;"<>"&"";A1:A5)А вариант от ZS5 у меня игнорирует пустые ячейки.
« Последнее редактирование: 03.09.2014, 16:51:37 от ShAM »
Записан

Pelena

  • Постоялец
  • ***
  • Уважение: +35/-0
  • Оффлайн Оффлайн
  • Сообщений: 282
Re: =СУММЕСЛИ() с критерием НЕ ПУСТО.
« Ответ #9 : 03.09.2014, 16:58:53 »

Алишер, прошу прощения, больше не повторится)
Записан

zs

  • Старожил
  • ****
  • Уважение: +12/-0
  • Оффлайн Оффлайн
  • Сообщений: 577
Re: =СУММЕСЛИ() с критерием НЕ ПУСТО.
« Ответ #10 : 04.09.2014, 10:01:17 »

Формула =СУММЕСЛИ(B1:B5;"<>*";A1:A5)
была к теме СУММЕСЛИ(
Мне показалось, она звучала так)
прошу помощи по формуле СУММЕСЛИ()
Есть 2 столбика А и В: в А числовые значения - в В разные данные. Необходимо в ячейке С1 сложить все значения из столбика А, напротив которых в столбике В есть ПУСТЫЕ ячейки

Как должна выглядеть эта формула. Если бы данные в В были всегда одинаковы - всё очевидно... =СУММЕСЛИ(А:А;"данные";В:В) но эти самые данные всегда разные - а формулу =СУММЕСЛИ(А:А;<>"";В:В) эксель не принимает. Что делать?
Ну а если наоборот, то мне опять же кажется, подойдет формула

=СУММЕСЛИ(B1:B5;">?";A1:A5)
С уважением, ZS5
Записан

vikttur

  • Глобальный модератор
  • Ветеран
  • *****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 015
Re: =СУММЕСЛИ() с критерием НЕ ПУСТО.
« Ответ #11 : 04.09.2014, 10:27:52 »

Да, тема звучала так, но это название функции, но никак не название темы, отражающее суть вопроса.
Цитировать
сложить все значения из столбика А, напротив которых в столбике В есть ПУСТЫЕ ячейки
=СУММЕСЛИ(B:B;"=";A:A)
Записан

zs

  • Старожил
  • ****
  • Уважение: +12/-0
  • Оффлайн Оффлайн
  • Сообщений: 577
Re: =СУММЕСЛИ() с критерием НЕ ПУСТО.
« Ответ #12 : 04.09.2014, 11:01:14 »

Здравствуйте, vikttur!
Всякая экономика сводится к экономии ресурсов.
У Вас формула эффективней на 5 знаков. Respect, vikttur!
P.S. Но суть вопрос тажа ;)
Записан

vikttur

  • Глобальный модератор
  • Ветеран
  • *****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 015
Re: =СУММЕСЛИ() с критерием НЕ ПУСТО.
« Ответ #13 : 04.09.2014, 11:05:53 »

Я отвечал на вопрос. Значит, не понял, в чем этот вопрос состоит.
Нужно "как наоборот"? Т.е. ненулевые? Или не пустые?
Записан

ShAM

  • Глобальный модератор
  • Постоялец
  • *****
  • Уважение: +32/-0
  • Оффлайн Оффлайн
  • Сообщений: 294
Re: =СУММЕСЛИ() с критерием НЕ ПУСТО.
« Ответ #14 : 05.09.2014, 17:30:15 »

У ТС было "непустые":
Есть 2 столбика А и В: в А числовые значения - в В разные данные. Необходимо в ячейке С1 сложить все значения из столбика А, напротив которых в столбике В есть непустые ячейки.
У ZS5 стало "ПУСТЫЕ":
Есть 2 столбика А и В: в А числовые значения - в В разные данные. Необходимо в ячейке С1 сложить все значения из столбика А, напротив которых в столбике В есть ПУСТЫЕ ячейки
Но, по-моему, разобрали оба варианта :)
Записан

sst

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 3
Re: =СУММЕСЛИ() с критерием НЕ ПУСТО.
« Ответ #15 : 15.01.2020, 13:39:08 »

Ничто из указанного не работает на последнем excel
работает следующее:
=SUMPRODUCT((B1:B5<>"")*(A1:A5))
и следующее(формула массива):
{=SUM((B1:B5<>"")*(A1:A5))}
лучше использовать sumproduct - он быстрее, не заморочнее, а синтаксис такой же как в формуле массива
« Последнее редактирование: 15.01.2020, 14:34:02 от sst »
Записан

boa

  • Глобальный модератор
  • Старожил
  • *****
  • Уважение: +32/-0
  • Оффлайн Оффлайн
  • Сообщений: 594
  • Доброта спасет мир...
Re: =СУММЕСЛИ() с критерием НЕ ПУСТО.
« Ответ #16 : 15.01.2020, 15:31:59 »

Ничто из указанного не работает на последнем excel
вы меня аж удивили своим постом...
все работает! (Проверено в Excel 365)
хотя, ваши формулы тоже имеют право на жизнь, но это уже следующий уровень.
Записан
Ничто не обходится нам так дешево и не ценится так дорого, как вежливость...  Мигель Сервантес де Сааведра

sst

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 3
Re: =СУММЕСЛИ() с критерием НЕ ПУСТО.
« Ответ #17 : 15.01.2020, 18:17:50 »

Вот сделал небольшое изыскание
Результат интересный. Не всегда очевидный
Записан

vikttur

  • Глобальный модератор
  • Ветеран
  • *****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 015
Re: =СУММЕСЛИ() с критерием НЕ ПУСТО.
« Ответ #18 : 15.01.2020, 19:41:53 »

Цитировать
Ничто из указанного не работает на последнем excel
Да ну? Работает же.
Ваши изыскания не во всем понятны и новичка могут вместо помощи только запутать...

Зачем применять СУММЕЛИМН, если можно СУММЕСЛИ, которая работает быстрее?
Попутно - СУММЕСЛИ умеет определять используемый диапазон, а СУММЕСЛИМН и СУММПРОИЗВ не умеют. Это к чему? Так, как Вы указали диапазоны - неправильно, формулы лопатят все строки листа.

Цитировать
SUMPRODUCT(($A:$A<>"")*($C:$C)) - Здесь суммирует даже первую строку
пытается умножить на текст из С1, отчего и получается ошибка.

Цитировать
SUMIFS($C:$C;$A:$A;"*") - Считает все символьные данные, включая формулы
Функция не считает, она суммирует по условию.
Определение "символьные данные", применяемое в примере, некорректно. Число 5 - тоже символ. Данные могут быть числовыми или текстовыми.
"?"  - служебный символ, подразумевается один символ.
"*" - служебный символ, подразумевается текст любой длины, включая нулевую.
"?*" - текст любой длины, исключая нулевую
Так как формула не может возвращать пустоту ("" - не пустота, это текст нулевой длины), то ячейка с формулой тоже принимается в расчет

"Каждой фуфайке свой гвоздик" :)
Например, "<>" и "<>0"  тоже имеют разное назначение, СУММЕСЛИ умеет преобразовывать текстовую запись числа в нормальное число, СУММПРОИЗВ такому не научена. Тонкостей много и говорить, что "формула не работает" можно, но правильно ли такое утверждение (какие данные, как расположены, какая задача решается и т.д.)


« Последнее редактирование: 15.01.2020, 19:46:58 от vikttur »
Записан

sst

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 3
Re: =СУММЕСЛИ() с критерием НЕ ПУСТО.
« Ответ #19 : 16.01.2020, 12:34:59 »

повторюсь еще раз - ни одна из указанных выше формул не решает задачу, если в столбце с данными стоит не значение а формула (что чаще всего и есть).
почему, я указал чуть выше. Мои изыскания не для новичков, а для понимания, что результат может быть разным, и что ни одна из вышеуказанных формул не решает задачу.

Записан
 



Темы без ответов

24.01.2020 14:03 На диаграмме Ганта несоответствие оси Y 1131
09.08.2019 14:09 Макрос для заполнения таблиц через форму 2879
18.07.2019 16:02 Рассылка почты из Excel при помощи почтовой программы TheBAT! 2497
09.07.2019 20:39 Кредит с уменьшением периода выплат 2593
28.05.2019 21:09 Сделать несколько скриптов для рабочей таблицы 3305
05.03.2019 17:00 Последовательный вывод таблиц Excel в один документ Word без шаблона 3198
05.03.2019 09:29 Нежелательные изменение размеров колонтитула при редактировании 2958
07.02.2019 01:36 Как удалить дубликаты из выпадающего связанного списка? 3121
20.01.2019 12:38 Все варианты частичного суммирования 3279
13.01.2019 12:24 Заполнение диапазона числами - в виде кластеров 2627





Яндекс цитирования msexcel.ru Яндекс.Метрика

Страница сгенерирована за 0.193 секунд. Запросов: 140.