Перейти к содержимому

Excel. Подсчет числа ячеек, содержащих символ (букву)

Недавно, в комментариях к заметке Excel. Суммирование по ячейкам, выделенным цветом меня спросили, как подсчитать число ячеек, содержащих определенный символ / букву. У меня получилось решить задачу без использования кода VBA на основе формулы массива. Если вы не использовали ранее такие формулы, рекомендую начать с заметки Excel. Введение в формулы массива.

Шаг 1. Определим, содержится ли искомый символ в ячейке (рис. 1).

Рис. 1. Содержится ли искомый символ в выбранной ячейке

НАЙТИ($C$1;A1) – ищет символ, хранящийся в $C$1, в строке А1; если находит, возвращает позицию этого символа в строке; если не находит, то возвращает ошибку #ЗНАЧ!

Скачать заметку в формате Word, примеры в формате Excel

Шаг 2. Избавляемся от ошибочных значений (рис. 2).

Рис. 2. Замена ошибочных значений нулями

ЕСЛИОШИБКА(НАЙТИ($C$1;A1);0) – возвращает 0, если значение функции НАЙТИ($C$1;A1) выдает ошибку, в противном случае возвращает само значение функции НАЙТИ($C$1;A1)

Шаг. 3. Заменяем номер позиции на единицу

Рис. 3. Приведение всех положительных значений к единице

=ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A1)/1000;1);0) – преобразование, позволяющее для любого значения позиции (не ошибки) получить 1; номер позиции, возвращаемый функцией НАЙТИ($C$1;A1), делится на 1000 и округляется до ближайшего целого (если быть точным, то ячейка Excel максимально может содержать 32 767 символов, так что «для надежности» можно заменить 1000 на 32 767 :); в этом случае, вы избежите ошибки при любом содержимом ячеек).

В качестве альтернативы можно использовать функцию ЕСЛИ:

=ЕСЛИОШИБКА(ЕСЛИ(НАЙТИ($C$1;A1)>0;1);0) – если значение функции НАЙТИ($C$1;A1) больше нуля, то функция ЕСЛИ заменяем это значение на единицу. Заметьте, что третий аргумент в функции ЕСЛИ отсутствует, так как функция НАЙТИ($C$1;A1) не может вернуть значение меньше или равно 0 (функция НАЙТИ($C$1;A1) возвращает только положительные значения или ошибку #ЗНАЧ!).

Шаг. 4. Суммируем все единички. Напомню, что единица соответствует ячейке, содержащей искомый символ, а ноль – ячейке, не содержащей искомый символ (рис. 4).

Рис. 4. Суммирование ячеек, содержащих искомый символ

В ячейке С2 я использовал формулу массива:

{=СУММ(ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A1:A14)/1000;1);0))}

Функция последовательно вычисляет значения =ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A1)/1000;1);0), =ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A2)/1000;1);0) … и так вплоть до =ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A14)/1000;1);0)

Результат вычисления (0 или 1) заносится в память, формируя виртуальный массив {1,1,1,0,1,1,0,1,1,0,0,0,1}. Функция СУММ просто суммирует все единицы.

Альтернативная функция: =СУММ(ЕСЛИОШИБКА(ЕСЛИ(НАЙТИ($C$1;A1:A14)>0;1);0))

P.S. Не вводите фигурные скобки в строку формул, а наберите всю формулу без фигурных скобок и нажмите одновременно Ctrl+Shift+Enter

87 комментариев для “Excel. Подсчет числа ячеек, содержащих символ (букву)”

  1. День Добрый подскажите а как нужно прописать чтобы формула считала по цвету и тому что в ней написано пример: ячейки разноцветные и месяца в них разные нужно нужно посчитать кол-во всех зелённых ячеек с надписью СЕНТЯБРЬ ? заранее СПАСИБО

  2. Максим, воспользуйтесь кодом:
    Function КолЦвет(диапазон As Range, критерий As Range, номер_месяца As String) As Double
    Application.Volatile True
    Dim i As Range
    For Each i In диапазон
    If i.Interior.Color = критерий.Interior.Color And i = номер_месяца Then
    КолЦвет = КолЦвет + 1
    End If
    Next
    End Function
    См. также Excel-файл (зазипованный)

  3. Огромное человеческое спасибо !!!!! спасибо

  4. Добрый день какой можно воспользоваться формулой чтобы в нужной ячейки проставлялось определённое число при определённом Условии(текст) например : погрузка стекла стоит 2000, а плитки 3000 и гравия 3500 погруженный материал цена за погрузку прайс по погрузке одного тс
    стекло стекло 2000
    плитка плитка 3000
    гравий гравий 3500
    стекло
    гравий
    плитка

    итого 0

  5. Максим, трудно понять по неотформатированному тексту, о чем речь. Пришлите в личку Excel-файл с подробным описанием задания.

  6. Формула не считает, выдает 1
    {=СУММ(ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ ($C$1;A1:A14)/1000;1);0))}

  7. Роман, вы правильно ввели формулу массива?
    >P.S. Не вводите фигурные скобки в строку формул, а наберите всю формулу без фигурных скобок и нажмите одновременно Ctrl+Shift+Enter
    Если вы поступили именно так, и формула не работает, пришлите Excel-файл в личку, посмотрю, что можно сделать…

  8. да, иначе не считала бы. Отправил Вам ваш файл, лист "рис.4" все видно

  9. Подскажите пожалуйста: в ячейке есть значение с цифрами и буквой(С), как просуммировать ячейки с буквой (С) или другими буквами?

  10. Скажите, а можно сделать еще проще: =СЧЁТЕСЛИ(A1:A14;"*а*") ? Или я недопонял условие задачи…

  11. Андрей, супер! Всё верно! Простое и изящное решение. Отмечу Ваш коммент среди лучших))

  12. Виктория

    Подскажите пожалуйста, мне надо в столбце с числами сделать изменения, перед началом цифры выставить везде *

  13. Виктория, если я правильно понял ваш вопрос, воспользуйтесь формулой ="*"&A1

  14. Виктория

    68.319396
    60.237774
    192.109067, а надо

    *68.319396*
    *60.237774*
    *192.109067*

  15. Виктория

    Получилось, спасибо!!! Столько время теперь с экономлю

  16. Виктория

    Да, чуть усложнилась, но до этого я додумалась))))

  17. zdrastvuyte izvinite umneya neskolka vobrosov est. 1 kak mojno v excel summirovat bukvi na primer a.b.c.d. i nasummu nado viti 4. isho odin primer a.a.a.a.a. i na summu 5 nado viti/ pojalosta pomogite.

  18. Анвар, если я правильно понял вопрос, вам нужно определить число символов в текстовой строке. Для этого в Excel применяется функция ДЛСТР().

  19. zdrastvuyte gospodin Baguzin. u menya odin vopros kak mojno v odin yacheyke 16 stoit i na drugom yacheyke 8 stoit. U nas rabote yest tabel tam nochniye chasov shitat nada. 16=2 i 8=6 i tak dale. odin mesyes 30 den est ili 31 den ili 29 den. yacheyki nado i summirovat. vot na primer tabel
    1. 2. 3. 4. 5. 6. 7. 8. 9.-dni
    o 16 8 o 16 8 o 16 8 -rabochiye chasov
    na nemu nado summirovat yesli mi budem nochniye schitat ruchnoy o eta oddix, 16 ravno na 2, 8 ravno 6. i tak nam nam na 9 dnyey poluchitsya 2*3=6 i 6*3=18 i tak 6+18=24 chasov vxodit vot etova mnya nado formula stobi ekcel sam nam summiroval eti nochniye chasov avtomaticheskiy. Yesli vopros ponyatno pojalosta dayte nam otvet.

  20. vopros tokoy na primer
    yacheyka
    A B
    1 акмал ишчи
    2 акмал укувчи
    3 жасур ишчи
    4 илхом алочи
    5 акмал домла
    6 илхом ишчи
    7 акмал балочи
    8 жасур укувчи
    9 жасур балочи
    vot takiye slovi dano nam. Eti 2 stolbik. Pervoy A stolbik i vtoroye B stolbik. Kajdiy slova iz odin yacheyki sostoitsya.
    Vopros takoye. Menya nado na primer
    s pervogo tablitsi акмал i vtorogo tablitsi sve kotoriye prenadlejit акмалю nado sobirat na odin yacheyki.
    ишчи, укувчи, домла, балочи. vot tak nado menya sobirat v odin yacheyki.
    Vot ishyo primer
    s pervogo tablitse жасур. syo kakiye slovi prenadlejit nado menya sobirat v odin yacheyku s stolbika B.
    ишчи, укувчи, балочи.
    isho mojno primer. илхом
    sobirayem na odin yacheyku vse prenadlejayushi к илхому с stolbika B.
    eto nam tak poluchayetsya.
    алочи, ишчи.
    vopros po moy zglaydu vam ponyatno dumayu.
    Yesli zdes (формула впр) rom ispolzuvali bi to nam togta tolko pervoye naydet. na primer
    акмал ишчи a ostalniye ne naydet ili ne sobirayet.
    1 акмал ишчи
    2 акмал укувчи
    5 акмал домла
    7 акмал балочи
    yesli mesta slovi sifri stoil bi tokda nam s formuley суммесли sobirali bi.
    Primer
    1 акмал 6
    2 акмал 9
    5 акмал 12
    7 акмал 22
    a nam nado slovi sobirat na odin yacheyke.
    ODIN FORMULA NADO SHTOBI. IZ PERVOGO TABLITS TAKOVO (АКМАЛ) I S VTOROGOVA TABLITSA SVE SLOVI PODLEJAYESHI K АКМАЛЕ SOBIRAT NADO.
    tOLKO NAM S FORMULEY NAYTI I SOBIRAT NADO.
    potamushta perechen ochen bolshaya.
    yasli vopros vam ne ponayatno pojalusta skajite menya vashu pochta ya tam s excelskiy faylom otpravlyu.

  21. yacheyka
    A
    акмал
    жасур
    илхом
    акмал
    илхом
    акмал
    жасур
    жасур
    B
    укувчи
    ишчи
    алочи
    домла
    ишчи
    балочи
    укувчи
    балочи
    yesli vam ne ponyao dumal. za etogo takogo variantom toje otpravlyayu.

  22. Здравствуйте.Нуждаюсь в вашей помощи.Есть табель учета рабочего времени,в котором проставляется время в пути на вахту «2П» или «4П» и т.д.,что означает 2 часа или 4 часа соответственно,бывают и др.значения в зависимости от расстояния,но буква должна присутствовать.Необходимо просуммировать количество часов в пути в строке.Еще учитывать цвет ячейки,поскольку идет разбивка по объектам где работали и часы в пути приписываются непосредственно к объекту.Часы работы также отмечены этим цветом что и путь туда.

  23. Инна, пришлите файл с примером в личку. Попробую помочь.

  24. Александр

    Здравствуйте. Подскажите пожалуйста что поменять в формуле чтобы дата оставалась на момент ввода, а не изменялась под новое время и дату.
    =ЕСЛИ(F780>0;ТДАТА();"")

  25. Александр, насколько я знаю, в Excel есть две возможности: ввести фиксированную дату (и время) или функции СЕГОДНЯ, ТДАТА, отображающие текущую дату и время, и обновляющиеся при введении новых данных. Вам же нужно сначала воспользоваться формулой, а потом отключить формулу и зафиксировать данные. Могу предложить только такой вариант: сначала воспользоваться вашей формулой, а потом преобразовать результат работы формулы в константу, например, нажав F9.

  26. Огромное Вам и создателю сайта человеческое спасибо!!!

  27. Спасибо большое на свой вопрос нашла у Вас ответ!!!

  28. Светлана

    здравствуйте. подскажите пожалуйста, как произвести подсчет ячеек в столбце, содержащих определенный символ, например «√». ячейка содержит только этот символ или не содержит его вовсе

  29. Дмитрий

    Добрый день. Подскажите как написать: есть кабель выхода на работу за месяц. у человека могут быть разные дни и разные часы выходов. Нужно в одной ячейке что бы считало сколько дней выходил человек а не количество часов.

  30. Татьяна

    Здравствуйте! Помогите, пожалуйста. пытаюсь писать диссертацию Данные (числовые) пищк по столбцам. Столбцов много. В строках пишу ФИО. ФИО соответствуют 4 стадиям заболевания и трем критериям по цвету (т.о. одному критерию по цвету соответствут 4 критерия по стадиям) В каждом столбце необходимо считать среднее значение, стандартное отклонение и ошибку по всем 12 параметрам последовательно. Применяю фильтр Обычная формула считает только один раз. есть ли возможность составить формулу таким образом, чтобы при применении фильтра правильно происходил расчет всех отфильтрованных значений по всем трем параметрам (желателен и Стьюдент). Сможете помочь:! А то так трудно!!! Спасибо!

  31. Татьяна, для решения задачи я бы рекомендовал использовтаь не цвет для выделения, а добавить столбцы, в которых словами описать стадии и другие параметры. Таким образом, одна строка будет отвечать уникальному набору параметров. Если информацию о том же человеке нужно внести лишь с одним отличающимся параметром, то использовать новую строку, в которой сдублировать все повторяющиеся параметры и указать один новый. Затем использовать сводную таблицу для агрегирования данных по любому критерию. А уже затем проводить статистический анализ. См. Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013, Левин. Статистика для менеджеров с использованием Microsoft Excel

  32. Добрый день, очень полезная статья! Огромное спасибо!
    Воспользовался формулой:
    =СЧЁТЕСЛИ(RC[-4]:R[11]C[-4];"*а*")
    но так он считает если в ячейке имеется этот символ.И даже если их там несколько.
    Вопрос: а как сделать так чтобы он посчитал общее количество таких символов, даже если их не один в ячейке, то есть если в ячейке будет стоять слово «арарат» чтобы он прибавил не +1, а +3.
    Если такая возможность? Заранее спасибо.

  33. Спасибо огромное!!! очень помог!!простое и понятное решение .

  34. Добрый день.
    Большинство пользователей оперирую стандартными функциями и для них не совсем понятны выражения, написанные вручную — это, как мне кажется, для достаточно опытных пользователей.
    В текстовых функциях есть такая: =СЦЕПИТЬ()

    а для вашего случая будет так =СЦЕПИТЬ("*";M6;"*").

  35. если цвет ячеек однозначен по отношению ко времени, я бы воспользовался текстовой функцией =ЛЕВСИМВ(). Возвращает заданное количество символов СЛЕВА. получится так:
    2п 2
    4п 4
    2п 2
    4п 4
    2п 2
    4п 4
    2п 2
    4п 4
    4п 4
    4п 4
    4п 4

    Можно аналогично сделать в строке. сумматоры либо под столбцом, либо справа в конце.

  36. Константин

    Здравствуйте! Нужна помощь. Есть диапазон. В каждой клетке по одной букве (ф, б, а и т.д.). Надо посчитать количество букв в этом диапазоне, игнорируя при этом букву «в». Такое возможно? Спасибо.

  37. Виктория

    Здравствуйте! У меня вопрос. Формула состоит из суммирования произведений чисел. Например ($AU$4*AU147)+($AQ$4*AQ147)+($AR$4*AR147)+($AT$4*AT147)+($AS$4*AS147)+($AV$4*AV147)+($AP$4*AP147)+($AO$4+AO147)+($AN$4*AN147)+($AM$4*AM147)+($AL$4*AL147)+($AK$4*AK147)+($AJ$4*AJ147)+($AI$4*AI147)+($AH$4*AH147)+($AG$4*AG147)+($AF$4*AF147)+($AE$4*AE147)+($AD$4*AD147)+($AC$4*AC147)+($AB$4*AB147)+($AA$4*AA147)+($Z$4*Z147)+($Y$4*Y147)+($X$4*X147)+($W$4*W147)+($V$4*V147)+($U$4*U147)+($T$4*T147)+($S$4*S147)+($R$4*R147)+($Q$4*Q147)+($P$4*P147)+($O$4*O147)+($N$4*N147)+($M$4*M147)+($L$4*L147)+($K$4*K147)+($J$4*J147)+($I$4*I147), но если в ячейках стоят нули, то результат выдает 1. Что я не так сделала. Спасибо.

  38. добрый день у меня такой вопрос!возможно ли сделать так что бы формула считала так как надо мне,т.е.допустим формула суммирует 2*500=1000+2*500=1000+2*500=1000ответ будет 3000,а мне надо подставить вместо 3000 допустим 4000,если я так делаю то формула перестаёт подсчитывать общую сумму,а считает только 2 *500=1000,а общую уже не выдаёт,вопрос как можно сделать так что бы формула продолжала считать но уже не правильно!а так как нужно мне?

  39. Виктория

    Здравствуйте подскажите пожалуйста как сравнить вот эти две таблицы, надо найти по одинаковым строчкам отличие в количестве.

  40. Виктория

    Подскажите пожалуйста, если конечно это можно сделать.

    Число написано в сдвоенной ячейке, мне надо её разбить, но чтоб это число повторилось во второй разбитой ячейке.

    Спасибо.

  41. Здравствуйте. Нужна помощь, нужно по схожим названиям суммировать их цены. В excel разбираюсь очень плохо поэтому обьясните пожалуйста по полочкам. Заранее спасибо.

  42. Махабат

    Добрый день! Нужна Ваша помощь.
    У меня в таблице есть поступление всего товара на разные склады. В конце суммируется итого поступило. Но нужно чтобы автоматически считалось сколько на каждый отдельный склад. По каждой отдельной строке я указываю какой склад. И теперь чтобы вручную это все не суммировать, я так понимаю необходима формула с «если, то». Вот только ничего не получается у меня….

  43. Добрый день мне необходима формула для суммирования по двум условиям, второе условие

    банан 2свежих
    апельсин 2свежих
    сумма свежих

  44. Подскажите пожалуйста, как подсчитать количество ячеек, начинающихся с определенного символа?

  45. Елена, воспользуйтесь формулой
    =СУММПРОИЗВ(--(ЛЕВСИМВ(A1:A7)=C1))

    См. также Excel-файл

  46. Подскажите, верна ли формула на английском
    =SUM(IFERROR(CEILING(FIND($I$1;G5:G120)/1000;1);0))

    Резульаьа выводт — 0

  47. Светлана

    Подскажите,если в ячейках стоит буква к примеру Я..как формулой подсчитать количество букв?

  48. Добрый вечер. Подскажите какую формулу нужно использовать для подсчета только суммы чисел если в строке есть значения и числа?

    пример:

    #Н/Д #Н/Д 1 0 2 0 1 #Н/Д

  49. Денис, попробуйте, =АГРЕГАТ(9;6;диапазон), где вместо «диапазон» задайте ссылку на суммируемый диапазон

  50. Добрый вечер.извените можно вопрос не по темt
    Как отличить страницы-боты от истинного сайта?

  51. Александра

    Здравствуйте! Подскажите, как посчитать количество ячеек с цифрами до определенной ( например от 7 до 14 включительно)

  52. Подскажите пожалуйста, как сделать внешнюю формулу чтобы она считала колличество определенной буквы в ворде, находящимся в этой же папке?

  53. подскажите пожалуйста, как сложить значения 4к+5к чтобы получилось 9к?

  54. Если всегда используется буква «к», предлагаю отражать ее с помощью формата, а реально в ячейках будут только числа:
    Буква в ячейке с помощью формата
    Чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
    См. также Excel-файл.

  55. Как в табеле подсчитать только 8 ?
    Смена выглядит так 12 4 8\4 8.
    Спасибо.

  56. Елена, в вашем примере, вам нужно найти и подсчитать обе восьмерки, или только вторую?

  57. Мне нужно подсчитать обе восьмерки, вернее из всех сменных часов нужны только восьмерки.

  58. Подскажите пожалуйста можно ли посчитать такую формулу =0,53* 20 км. Есть какой либо способ посчитать такие данные?

  59. подскажите в табеле учета посещений воспитанников как подсчитать буквы в строке в.б.н. и пустая клетка это посещение.Чтоб в конце табеля был подсчет дни посещений ,дни по болезни,без причины.

  60. Добрый день. Подскажите как прописать формулу если необходимо посчитать пример: буква К8-командировка 8 часов, чтоб в колонке К отдельной считались именно часы, а не количество к примеру 1?

  61. Спасибо вам и создателю темы! Очень помогли:)

  62. Здравствуйте помогите, если я Вам сброшу таблицу внесете в нее нужную формулу, увидите таблицу поймете. С оплатой определимся. Спасибо

  63. А вы платно помогаете с формулами или бесплатно ?

  64. Подскажите формулу для табеля выходов чтобы пустая клетка считалась как единица

  65. Александр

    Здравствуйте у меня вопрос как сделать в табеле учёте времени считались отдельно буквы и цифры к примеру в одной ячейке стоит 8 а вследуещей стоит В(выходной). А то когда ставишь букву сразу выдоет ошибку на часы

  66. Сергей Багузин

    Александр, если диапазон суммирования А1:Е1, то функция =СУММ(А1:Е1) игнорирует ячейки с текстом, и возвращает сумму только для ячеек, содержащих числа. Если у вас всё же функция =СУММ(А1:Е1) не работает, попробуйте =АГРЕГАТ(9;6;A1:E1).

  67. Добрый день. Спасибо за информацию. А можно ли добавить дополнительное условие, считать только видимые ячейки с определенным символом или буквой после фильтрации? То есть добавить промежуточный итог? Спасибо

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *