Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Пустая ячейка воспринимается Excel'ем как 0. Как исправить?, Функция СУММ(A1<5;A3<5; и т.д.)
 
#1
19.02.2017 23:21:59
Подскажите, пожалуйста, как решить проблему с тем, что Excel воспринимает пустые ячейки как 0?

Мне нужно посчитать несмежные ячейки по критерию <5. Среди несмежных ячеек есть временно пустые, но которые со временем будут заполнены. Почему-то Excel учитывает эти пустые ячейки, как будто в них содержится значение 0 и тоже считает их.

Как сделать так, чтобы пустые ячейки не считались? Функция =СУММ(A1<5;A3<5;A8<5; и т.д. очень много ячеек, среди которых есть пустые).

Update после поста № 25: На данный момент лучшее решение предложил Сергей, а именно формулу =СУММПРОИЗВ((A1:N1<5)*НЕ(ЕПУСТО(A1:N1))*{1;0;1;0;1;0;1;0;1;0;1;1;0;1})  
Изменено: Eugen_excel - 20.02.2017 13:43:42
Excel 2010
 
 
 
#2
19.02.2017 23:27:38
Могу предложить делать проверку, пусто в ячейке или нет:
=ЕСЛИ(A1<>"";A1<5;0)
Но если увидеть Ваш файл, то может быть можно использовать другую, более удобную формулу.

Еще вариант придумал:
=СУММ(И(A1<>"";A1<5);И(A3<>"";A3<5);И(A8<>"";A8<5))
Изменено: Karataev - 19.02.2017 23:34:49
 
 
 
#3
19.02.2017 23:38:41
Цитата
Eugen_excel написал:
СУММ
сумма нулей  обычно равна нулю. "счетесли" пропускает пустые.

Приложите файл, всем понятнее будет.  
По вопросам из тем форума, личку не читаю.
 
 
 
#4
19.02.2017 23:59:29
Снимите галочку)
Параметры>Дополнительные>Показывать нули в ячейках, которые содержат нулевые значения
 
 
 
#5
20.02.2017 00:01:06
elnur_quba, и чего Вы добьетесь?
 
 
 
#6
20.02.2017 00:24:31
Eugen_excel,
сотни миллионов людей пользуются Excel'ем и не видят проблемы в том, что пустые ячейки воспринимаются как нули.
проблема не в том, что и как воспринимает Excel, а в том, что Вы не понимаете, как с этим справиться
покажете данные, обьясните что хотите сосчитать -  тогда и решение найдется...
а пока ничего не известно, может кто-то и угадает что нужно, но скорее всего будут продолжать поступать советы типа снять птичку у "показывать нули в ячейках, которые ..."
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#7
20.02.2017 08:29:30
Спасибо всем за ответы! Прикладываю файл-пример, из которого вы все поймете  8)

Прошу помочь решить задачу!
Excel 2010
 
 
 
#8
20.02.2017 08:40:28
вариант
Код
=СУММПРОИЗВ((A1:N1<5)*НЕ(ЕПУСТО(A1:N1)))
Лень двигатель прогресса, доказано!!!
 
 
 
#9
20.02.2017 08:53:37
Цитата
Сергей написал:
вариантКод ? 1=СУММПРОИЗВ((A1:N1<5)*НЕ(ЕПУСТО(A1:N1)))
Вы выделили диапазон A1:N1, но ячейка B1, например, мне не нужна. Там будет другое значение и ее будет использовать другая функция, скажем "больше 2". Да и пустые ячейки со временем заполнятся и "поломают" предложенную функцию. Мне нужны только отдельные несмежные ячейки.

Неужели придется суммировать миллион функций СЧЕТЕСЛИ для каждой отдельной ячейки?  :cry:
Изменено: Eugen_excel - 20.02.2017 08:56:42
Excel 2010
 
 
 
#10
20.02.2017 08:56:51
еще вариант
Код
=СУММПРОИЗВ((A1:N1<5)*(A1:N1>0)*{1;0;1;0;1;0;1;0;1;0;1;1;0;1})
Лень двигатель прогресса, доказано!!!
 
 
 
#11
20.02.2017 09:00:29
смотрите файл
Код
=СЧЁТЕСЛИМН(A1:N1;"<5";A2:N2;"нужна")
Лень двигатель прогресса, доказано!!!
 
 
 
#12
20.02.2017 09:01:12
=СУММЕСЛИ(A1,"<5",A1)+СУММЕСЛИ(C1,"<5",C1)+СУММЕСЛИ(E1,"<5",E1)+СУММЕСЛИ(G1,"<5",G1)+СУММЕСЛИ(I1,"<5",I1)+СУММЕСЛИ(K1,"<5",K1)+СУММЕСЛИ(L1,"<5",L1)+СУММЕСЛИ(N1,"<5",N1)
 
 
 
#13
20.02.2017 09:23:04
@Сергей Функция с массивом =СУММПРОИЗВ((A1:N1<5)*(A1:N1>0)*{1;0;1;0;1;0;1;0;1;0;1;1;0;1}) выглядит интересно. Но проблема в том, что пока еще пустая ячейка может получить значение 0 и не будет посчитана по данной формуле. А нужно, чтобы если в ячейке стоит 0, то ячейка тоже считалась, ведь она удовлетворяет критерию "<5".

Функция с необходимостью дополнительно добавлять новую строку с подписью-критерием работает, но выглядит вычурно. Не хотелось бы лишних нагромождений.

@elnur_quba
Хотелось бы избежать множественных повторений "СУММЕСЛИ", "СЧЕТЕСЛИ", а прописать все несмежные ячейки одной формулой. Конечно, если решения не найдется, буду суммировать отдельные функции СЧЕТЕСЛИ для каждой отдельной ячейки.
Excel 2010
 
 
 
#14
20.02.2017 09:29:51
вам уже показано несколько вариантов играйтесь экспериментируйте вместо  (A1:N1>0) поставьте НЕ(ЕПУСТО(A1:N1)
Лень двигатель прогресса, доказано!!!
 
 
 
#15
20.02.2017 09:30:05
Цитата
Eugen_excel написал: но выглядит вычурно. Не хотелось бы лишних нагромождений.
OFF Так будьте сами последовательны - зачем в ваших данных "черезполосица"?! В чем ее ЦЕЛЕСООБРАЗНОСТЬ?.. ;)
 
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#16
20.02.2017 09:36:10
Целесообразность в том, что необходимо посчитать кол-во ячеек, удовлетворяющих определенному критерию, которые несмежны между собой. У вас ведь не всегда смежные диапазоны. Необходимые данные находятся в разных несмежных ячейках и их никак не поместить в соседние ячейки.
Excel 2010
 
 
 
#17
20.02.2017 10:00:45
Цитата
Eugen_excel написал: ... необходимые данные находятся в разных несмежных ячейках и их никак не поместить в соседние ячейки...
OFF Нет лишь одного пути к цели, варианты при желании можно найти и применить. Главное - не зашориваться, а уметь взглянуть на ситуацию с разных сторон. В вашем случае мы имеем лишь кусок из задачи, о которой вы и не упоминаете, а это не помешало бы более приемлемому построению данных и поиску решения... ;)
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#18
20.02.2017 10:51:32
Цитата
Z написал:
OFF Нет лишь одного пути к цели, варианты при желании можно найти и применить.
Я ищу наиболее рациональный и простой способ решить задачу подсчета количества несмежных ячеек по определенному критерию.Я верю, что существует простой способ решить эту проблему, просто еще не нашелся человек, который это умеет и знает как делать правильно.

Надеюсь, вам тоже интересно найти простой способ решения этой, на первый взгляд, детской задачи, а не использовать километровые формулы с кучей повторяющихся "СЧЕТЕСЛИ" и т.д., или нагромождать таблицу лишними вспомогательными данными.

У меня 40 таких несмежных ячеек. Представьте как это будет выглядеть: прописать формулу с 40 "СЧЕТЕСЛИ+СЧЕТЕСЛИ+СЧЕТЕСЛИ". Неужли нет более простого способа подсчета несмежных ячеек?
Excel 2010
 
 
 
#19
20.02.2017 11:48:56
Сами придумали себе проблему и героически с ней боретесь чем не устроил вариант =СУММПРОИЗВ((A1:N1<5)*НЕ(ЕПУСТО(A1:N1)*{1;0;1;0;1;0;1;0;1;0;1;1;0;1}), или вы даже его не пробовали а говорите
Цитата
Eugen_excel написал:
просто еще не нашелся человек, который это умеет и знает как делать правильно
Лень двигатель прогресса, доказано!!!
 
 
 
#20
20.02.2017 12:02:35
Eugen_excel, ваша формула в файле примере посчитала все верно.
Код
=СУММ(A1<5;C1<5;E1<5;G1<5;I1<5;K1<5;L1<5;N1<5)

Условие у вас "Нужно посчитать количество ячеек...", а формулу пишете "Сумм".

Например, у меня формула

Код
=СЧЁТЕСЛИ(A1:N1;"<5")

дает результат 3. Что верно. Пустые ячейки не считает. Если добавить в этот диапазон 0. то результат станет 4.

Какой результат хотите получить в итоге?
 
 
 
#21
20.02.2017 12:51:44
Цитата
Сергей написал:
=СУММПРОИЗВ((A1:N1<5)*НЕ(ЕПУСТО(A1:N1)*{1;0;1;0;1;0;1;0;1;0;1;1;0;1}), или вы даже его не пробовали а говорите
Как это не пробовал? Указанная функция считает неправильно.
Excel 2010
 
 
 
#22
20.02.2017 12:55:35
читайте название файла
Лень двигатель прогресса, доказано!!!
 
 
 
#23
20.02.2017 13:02:36
Цитата
AlbertSF написал:
Код ? 1=СЧЁТЕСЛИ(A1:N1;"<5")дает результат 3. Что верно. Пустые ячейки не считает. Если добавить в этот диапазон 0. то результат станет 4.Какой результат хотите получить в итоге?
Да вы поймите, что нельзя использовать в функции СЧЕТЕСЛИ диапазон A1:N1.

Нужно посчитать определенное кол-во несмежных ячеек, а не целый диапазон смежных ячеек! Завтра ячейка B1 будет иметь значение "4" и тоже посчитается, но она мне не нужна! Функция СЧЕТЕСЛИ не работает с несмежными ячейками/диапазонами.

Уже столько неудачных попыток было предложено, что видимо забыли, в чем состоит суть проблемы. Нужно посчитать количество несмежных ячеек по 1 конкретному критерию. В примере критерий - это <5. Интересуют только ячейки A1, C1, E1, G1, I1, K1, L1 и N1. Но никак не диапазон от A до N. Еще раз прикладываю файл-пример.

Неужели придется искать какой-нибудь техникум с соответствующей кафедрой, где изучают excel и там просить какого-нибудь студента первокурсника помочь? Думал, для людей, хорошо разбирающихся в программе (к числу которых я, к сожалению, не отношусь), такая пустяковая задача не составит трудностей. Тем более подобная задача (работа с несмежными ячейками) должна возникать довольно часто на практике. Не всегда же у вас идеальные смежные диапазоны ячеек. Или я не прав?
Excel 2010
 
 
 
#24
20.02.2017 13:07:01
да нафига козе баян, что вы уткнулись в эти не смежные ячейки вам показали как отсечь ненужные из диапазона че еще надо то, если такая любовь прописывать ячейки по отдельности для чего вы тему эту создали когда предложенные решения вас не устраивают
Лень двигатель прогресса, доказано!!!
 
 
 
#25
20.02.2017 13:30:06
Цитата
Сергей написал: читайте название файла
Вот так работает =СУММПРОИЗВ((A1:N1<5)*НЕ(ЕПУСТО(A1:N1))*{1;0;1;0;1;0;1;0;1;0;1;1;0;1})

Я когда вводил предыдущую формулу мне почему-то excel сам подправил и получилось неправильно. Сравнил с Вашей, удалил лишние скобки и получилось.

Подводим промежуточный итог:
Лучшее решение предложил Сергей с формулой =СУММПРОИЗВ((A1:N1<5)*НЕ(ЕПУСТО(A1:N1))*{1;0;1;0;1;0;1;0;1;0;1;1;0;1}) за что ему большое спасибо!  :idea:
Excel 2010
 
 
 
#26
20.02.2017 13:47:06
Я обновил первый пост, чтобы другим людям было легче найти решение, которое предложил Сергей - вполне достойное решение для работы с отдельными несмежными ячейками, которые находятся в одной таблице на разумном расстоянии друг от друга.
Excel 2010
 
 
 
Страницы: 1
Наверх