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

Рубрика: 7. Полезняшки 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

Комментарии: 54 комментария

Огромное спасибо!

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

Максим, воспользуйтесь кодом:
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-файл (зазипованный)

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

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

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

итого 0

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

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

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

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

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

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

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

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

Большое спасибо за формулу!

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

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

Огромное спасибо!!!

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

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

Спасибо большое, сейчас попробую)

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

68.319396
60.237774
192.109067, а надо

*68.319396*
*60.237774*
*192.109067*

Формула немного усложняется))
="*"&A1&"*"

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

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

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

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.

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

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.

Извините, не понял…

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.

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

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

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

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

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

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

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

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

Светлана, см. выше в комментарии Andrew

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

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

Извините за опечатки

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

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

Глеб, если я правильно понял вопрос, вам поможет статья Число вхождений подстроки (символа) в текст.

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

Константин, попробуйте подсчитать количество всех букв, а затем вычесть из него количество букв «в». Посмотрите еще заметку Число вхождений подстроки (символа) в текст.

Здравствуйте! У меня вопрос. Формула состоит из суммирования произведений чисел. Например ($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. Что я не так сделала. Спасибо.

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

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

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

Спасибо.

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

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

Попробуйте функцию =СУММЕСЛИ()

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

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


Прокомментировать