Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще…Меньше
В этом разделе перечислены распространенные причины ошибки #ЗНАЧ! в функциях СЧЁТЕСЛИ и СЧЁТЕСЛИМН.
Проблема: формула ссылается на ячейки в закрытой книге
Функции СЧЁТЕIF и СЧЁТЕЛИФS, ссылайтесь на ячейку или диапазон в закрытой книге, при этом #VALUE! ошибку «#ВЫЧИС!».
Примечание. Это известная проблема с несколькими другими функциями Excel, такими как СУММЕ ЕСЛИ, СУММЕ ЕСЛИМН и СЧИТАТЬ СЧИТАТЬБЛАН. См. функции СУММЕ ЕСЛИ, СЧЁТЕ ЕСЛИ и СЧИТАТЬ #VALUE! .
Решение: Откройте связанную книгу, указанную в формуле, и нажмите клавишу F9, чтобы обновить формулу.
Эту проблему также можно обойти, используя функции СУММ и ЕСЛИ вместе в формуле массива. См. функции СУММЕ ЕСЛИ, СЧЁТЕ ЕСЛИ и СЧИТАТЬ #VALUE! .
Проблема: длина строки превышает 255 символов
Решение: Сократите строку, если это возможно. Если сократить строку нельзя, используйте функцию СЦЕПИТЬ или оператор-амперсанд (&), чтобы разделить значение на несколько строк. Пример:
-
=СЧЁТЕСЛИ(B2:B12;»длинная строка»&»еще одна длинная строка»)
У вас есть вопрос об определенной функции?
Задать вопрос на форуме сообщества, посвященном Excel
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Исправление ошибки #ЗНАЧ! #BUSY!
Функция СЧЁТЕСЛИ
Функция СЧЁТЕСЛИМН
Функция ЕСЛИ — вложенные формулы и типовые ошибки
Посмотрите видео о расширенных функциях ЕСЛИ
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Обнаружение ошибок в формулах
Все функции Excel (по алфавиту)
Функции Excel (по категориям)
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
|
Владислав  Пользователь Сообщений: 69 |
Доброго дня господа форумчане! Документ для примера прилагаю вложением. Прикрепленные файлы
|
|
grand68 Пользователь Сообщений: 93 |
Все нормально считает. Формат ячейки К1 измените на числовой. |
|
Hugo Пользователь Сообщений: 23371 |
Там ячейки с временем в текстовом формате (все правда не изучал) — если поменять формат на время то считает. |
|
Владислав Пользователь Сообщений: 69 |
В ячейках B1 F1 H1 не считает ни при каких условиях. |
|
Hugo Пользователь Сообщений: 23371 |
#5 27.12.2017 15:01:38
— я написал при каких считает. Изменено: Hugo — 27.12.2017 15:02:53 |
||
|
Владислав Пользователь Сообщений: 69 |
#6 28.12.2017 10:31:09
спасибо за совет! Увы, не знал. Помогите пожалуйста пофиксить книгу. Вчера пол дня ковырял- все безрезультатно. Если бы получилось, не стал серьезных людей беспокоить. |
||
|
Hugo Пользователь Сообщений: 23371 |
Я пас — не могу сейчас качать/постить файлы. |
|
Karataev Пользователь Сообщений: 2308 |
Действия для столбца «B»: Проделайте это для остальных столбцов. Изменено: Karataev — 28.12.2017 21:42:50 |
|
Hugo Пользователь Сообщений: 23371 |
1.1) Выделите столбец B Прикрепленные файлы
Изменено: Hugo — 28.12.2017 21:43:04 |
|
Karataev Пользователь Сообщений: 2308 |
Еще под таблицей во многих строках есть оформление. Я правда не понял какое оформление влияет на то, что последняя строка так далеко. |
|
Юрий Кулик  Пользователь Сообщений: 7 |
Добрый день! Помогите, пожалуйста, разобраться с функцией СЧЕТЕСЛИ. Я только «пытаюсь» познать азы экселя. Проблема в следующем: необходимо подсчитать интервылы и суммировать их. Например: 9-10 будет один, 9-11, два и так далее. Это рабочее время сотрудника в графике. Формула следующая: СЧЁТЕСЛИ(A1;»9-10″)*1. и ВСЁ РАБОТАЕТ!!! Кроме одного….. Если в формуле указать «1-9»)*8, а время будет стоять 9-21, то эксель посчитает, что 9-21 это 8….. Прикрепленные файлы
|
|
vikttur Пользователь Сообщений: 47199 |
#12 26.08.2021 13:17:48 Создайте тему с названием, отражающим суть Вашей задачи. |
Приветы. Хочу подсчитать количество повторяющихся значений в столбце А. Напрмер, в ячейке В3 видно количество 18, хотя, если проверить, таких значений в А только одно… Это косяк? Или ограничение по количеству знаков, о котором нигде не сказано? А что тогда делать? Изменение формата ячеек на текстовые эффекта не даёт.
1 не у всех есть 2007 офис
2 используйте сводную таблицу
Здравствуйте.
Да, СЧЁТЕСЛИ обрезает числа до 15 знаков, даже если они записаны как текст. Используйте такую формулу:
=СУММПРОИЗВ(($A$2:$A$2038=A2)*1)
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…
www.excel-vba.ru
Просто СПАСИБО [+оказать+]
Считаешь СПАСИБО мало? Яндекс.Деньги: 41001332272872; WM: R298726502453
Если быть точнее, то
округляет
. Кстати, в справке о преобразовании типов, судя по всему, намекается здесь: «критерий может быть выражен следующим образом: 32, «32»,…».
Можно выражать аргумент значением, которое не_может быть преобразовано в число:
- B3: =СЧЁТЕСЛИ(A:A;A3&«*»)
Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли
Спасибо, оба способа сработали! 
Я тут в течение дня тоже решил эту проблему путём добавления к каждому значению в столбце одной и той же буквы, явно давая понять глупому (в данном случае) Экселю, что это текст. Но, бесспорно, ваш способ, Шпец Докапыч, гораздо изысканнее и практичнее
Стоп, поторопился хвалить, =СЧЁТЕСЛИ(A:A;A3&»*») не работает если изменять значения в столбце А, показывает ноль, что бы я ни вписал, при этом форула Pristа считает нормально
Ещё так можно:
=СУММ(ЕСЛИ($A$2:$A$2039=A2;1))
формула массива.
Цитата: Димычч от 03.09.2010, 11:46
показывает ноль, что бы я ни вписал
Действительно, нечёткое сравнение работает с текстом и ошибками, а с цифрами не прокатывает. Правда, я и не рассчитывал, что они там появятся.
Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли
Спасибо всем! Ещё одной проблемой в жизни стало меньше
Цитата: Serge 007 от 03.09.2010, 12:04
Ещё так можно:=СУММ(ЕСЛИ($A$2:$A$2039=A2;1))
формула массива.
Серега, упростим формулу?
Тоже массива:
=СУММ(—($A$2:$A$2039=A2))
Без массива:
=СУММПРОИЗВ(—($A$2:$A$2039=A2))
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…
www.excel-vba.ru
Просто СПАСИБО [+оказать+]
Считаешь СПАСИБО мало? Яндекс.Деньги: 41001332272872; WM: R298726502453
Цитата: Prist от 03.09.2010, 13:57
Серега, упростим формулу?
Тоже массива:
=СУММ(—($A$2:$A$2039=A2))
Да, чёт не подумал про так…
ЗЫ Сделаешь так, а потом всегда начинаются вопросы: «А что это за минусы перед… ?» 
А что это за минусы перед ($A$2:$A$2039=A2)?
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…
www.excel-vba.ru
Просто СПАСИБО [+оказать+]
Считаешь СПАСИБО мало? Яндекс.Деньги: 41001332272872; WM: R298726502453
Спасибо! А умение предвидеть вопрос — уже искусство мастера
- Remove From My Forums
Функции СЧЁТЕСЛИ(МН) и СУММЕСЛИ(МН) возвращают неправильный результат
-
Question
-
Здравствуйте! При использовании функций СЧЁТЕСЛИ(МН) и СУММЕСЛИ(МН) в Excel 2007 и Excel 2010 столкнулся со следующей проблемой: в случае когда значения ячеек в аругменте «диапазон» и значение ячейки в аргументе «критерий»
представляют собой набор из 20 цифр (формат ячеек «текстовый»), указанные выше функции в ряде случаев возвращают некорректный результат. Опытным путём выявил, что при вычислении результата указанные выше функции не учитывают последние
четыре цифры значений ячеек, т.е. например, если «критерий» равен «40702810200270006621», а диапазон включает в себя следующие значения: «40702810200270000960», «40702810200270006621», «40702810200270006197»,
«40702810200270006249», «40702810200270006126», то, например, функция СЧЁТЕСЛИ вернёт значение «5», а не «1», как должно быть.
Answers
-
Согласно
техническому ограничению, значимое количество знаков не должно превышать 15:
В Вашем случае, Вам необходимо это учитывать, и использовать в подсчётах только изменяемую его часть, видимо расчётных счетов. Так как первые 15 знаков у всего представленного Вами диапазона — одинаков, при изменяемом 15 знаке всё правильно:
Да, я Жук, три пары лапок и фасеточные глаза :))
-
Proposed as answer by
Wednesday, November 20, 2013 12:30 PM
-
Unproposed as answer by
ЖукMVP, Moderator
Friday, November 22, 2013 4:29 PM -
Proposed as answer by
ЖукMVP, Moderator
Friday, November 22, 2013 4:30 PM -
Marked as answer by
Иван ПродановMicrosoft contingent staff, Moderator
Monday, November 25, 2013 9:31 AM
-
Proposed as answer by
-
Ваша задача решается заменой не участвующих чисел (или любого числа), знаком «?» формула будет выглядеть так: =СЧЁТЕСЛИ (или СЧЁТЕСЛИМН)(A15:A19;»???????????????????1″). В
Вашем примере, так как для сравнения необходимо сравнивать последние четыре цифры, их и оставьте для сравнения формулой.Для проверки двадцатого числа:
Или так:
P.S. Это верно как для Функции СЧЁТЕСЛИ(), так и для Функции
СЧЁТЕСЛИМН()
Да, я Жук, три пары лапок и фасеточные глаза :))
-
Proposed as answer by
ЖукMVP, Moderator
Friday, November 22, 2013 4:29 PM -
Unproposed as answer by
ЖукMVP, Moderator
Saturday, November 23, 2013 12:38 PM -
Proposed as answer by
ЖукMVP, Moderator
Saturday, November 23, 2013 12:38 PM -
Edited by
ЖукMVP, Moderator
Saturday, November 23, 2013 12:43 PM
внесение ясности -
Marked as answer by
Иван ПродановMicrosoft contingent staff, Moderator
Monday, November 25, 2013 9:32 AM
-
Proposed as answer by
-
Я правильно понял Вашу задачу?, проверьте:
Да, я Жук, три пары лапок и фасеточные глаза :))
-
Edited by
ЖукMVP, Moderator
Tuesday, November 26, 2013 5:28 AM
корректировка -
Marked as answer by
bagaevdm
Tuesday, November 26, 2013 6:14 AM
-
Edited by
-
Да, такой способ гораздо лучше, спасибо. Но первые символы всех критериев и всех значений диапазона на деле могут отличаться, поэтому я пошел немного другим путем — добавил перед номером счета букву:
В целом задачу можно считать решенной, хоть и per rectum, но как всё-таки довести до сведения разработчиков, что для большинства пользователей такая работа рассматриваемых функция является большим «косяком», который надо бы поправить (здесь еще раз стоит упомянуть
OpenOffice.org Calc, в котором подобный недостаток отсутствует)?
Дмитрий Багаев
-
Marked as answer by
bagaevdm
Tuesday, November 26, 2013 6:14 AM
-
Marked as answer by
Функция СЧЕТЕСЛИ входит в группу статистических функций. Позволяет найти число ячеек по определенному критерию. Работает с числовыми и текстовыми значениями, датами.
Синтаксис и особенности функции
Сначала рассмотрим аргументы функции:
- Диапазон – группа значений для анализа и подсчета (обязательный).
- Критерий – условие, по которому нужно подсчитать ячейки (обязательный).
В диапазоне ячеек могут находиться текстовые, числовые значения, даты, массивы, ссылки на числа. Пустые ячейки функция игнорирует.
В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.
Рекомендации для правильной работы функции:
- Если функция СЧЕТЕСЛИ ссылается на диапазон в другой книге, то необходимо, чтобы эта книга была открыта.
- Аргумент «Критерий» нужно заключать в кавычки (кроме ссылок).
- Функция не учитывает регистр текстовых значений.
- При формулировании условия подсчета можно использовать подстановочные знаки. «?» — любой символ. «*» — любая последовательность символов. Чтобы формула искала непосредственно эти знаки, ставим перед ними знак тильды (~).
- Для нормального функционирования формулы в ячейках с текстовыми значениями не должно пробелов или непечатаемых знаков.
Функция СЧЕТЕСЛИ в Excel: примеры
Посчитаем числовые значения в одном диапазоне. Условие подсчета – один критерий.
У нас есть такая таблица:
Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:
Если условие подсчета внести в отдельную ячейку, можно в качестве критерия использовать ссылку:
Посчитаем текстовые значения в одном диапазоне. Условие поиска – один критерий.
Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:
Во втором случае в качестве критерия использовали ссылку на ячейку.
Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).
Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:
Формула посчитала «кровати» и «банкетки».
Используем в функции СЧЕТЕСЛИ условие поиска «не равно».
Формула: =СЧЁТЕСЛИ(A1:A11;»<>»&»стулья»). Оператор «<>» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».
При применении ссылки формула будет выглядеть так:
Часто требуется выполнять функцию СЧЕТЕСЛИ в Excel по двум критериям. Таким способом можно существенно расширить ее возможности. Рассмотрим специальные случаи применения СЧЕТЕСЛИ в Excel и примеры с двумя условиями.
- Посчитаем, сколько ячеек содержат текст «столы» и «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»). Для указания нескольких условий используется несколько выражений СЧЕТЕСЛИ. Они объединены между собой оператором «+».
- Условия – ссылки на ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2). Текст «столы» функция ищет в ячейке А1. Текст «стулья» — на базе критерия в ячейке А2.
- Посчитаем число ячеек в диапазоне В1:В11 со значением большим или равным 100 и меньшим или равным 200. Формула: =СЧЁТЕСЛИ(B1:B11;»>=100″)-СЧЁТЕСЛИ(B1:B11;»>200″).
- Применим в формуле СЧЕТЕСЛИ несколько диапазонов. Это возможно, если диапазоны являются смежными. Формула: =СЧЁТЕСЛИ(A1:B11;»>=100″)-СЧЁТЕСЛИ(A1:B11;»>200″). Ищет значения по двум критериям сразу в двух столбцах. Если диапазоны несмежные, то применяется функция СЧЕТЕСЛИМН.
- Когда в качестве критерия указывается ссылка на диапазон ячеек с условиями, функция возвращает массив. Для ввода формулы нужно выделить такое количество ячеек, как в диапазоне с критериями. После введения аргументов нажать одновременно сочетание клавиш Shift + Ctrl + Enter. Excel распознает формулу массива.
СЧЕТЕСЛИ с двумя условиями в Excel очень часто используется для автоматизированной и эффективной работы с данными. Поэтому продвинутому пользователю настоятельно рекомендуется внимательно изучить все приведенные выше примеры.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ
Посчитаем количество реализованных товаров по группам.
- Сначала отсортируем таблицу так, чтобы одинаковые значения оказались рядом.
- Первый аргумент формулы «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» — «Номер функции». Это числа от 1 до 11, указывающие статистическую функцию для расчета промежуточного результата. Подсчет количества ячеек осуществляется под цифрой «2» (функция «СЧЕТ»).
Скачать примеры функции СЧЕТЕСЛИ в Excel
Формула нашла количество значений для группы «Стулья». При большом числе строк (больше тысячи) подобное сочетание функций может оказаться полезным.