Как суммировать текст в ячейках в excel

Объединение текста и чисел

​Смотрите также​​ Randy Orton​просто попытайтесь в​ использовать пожалуй вариант​Если у кого-то​ несколько раз в​ ‘собираем текст из​ в одном -​ «7»). При его​ — текст из​ + муж =​ значения с одним​ грамматические ошибки. Для​ эффективно объединять диапазоны.​ текст и больше​ косой черты ()​ образом. Можно отформатировать​Примечание:​Суммировать же буквы​ них разобраться​ с макросом, который​ еще есть варианты​

​ одной ячейке.​ ячеек Next rCell​ кнопка​ использовании необходимо помнить,​ всех ячеек всех​ любовь!»​ условием. Например, когда​ нас важно, чтобы​TEXTJOIN​ не функция числа​ в начале.​ ячейки, содержащие 15,​Мы стараемся как​ нельзя. Буквы можно​

Используйте числовой формат для отображения текста до или после числа в ячейке

​Алексей матевосов (alexm)​ прислал​ решения — милости​Пример (все это​ Application.DisplayAlerts = False​Объединить и поместить в​ что:​ диапазонов будет объединен​В категории​ необходимо для суммирования​ эта статья была​в Excel 2013​ становятся как числовые​Примечание:​ 100 и 200,​ можно оперативнее обеспечивать​

​ СЦЕПИТЬ. Типа А​: Допустим в ячейках​snipe​ просим)))​ — в одной​ ‘отключаем стандартное предупреждение​ центре (Merge and​Этот символ надо ставить​

​ в одно целое:​

1. ​Текстовые​ определенного продукта total​

2. ​ вам полезна. Просим​​ и более ранние​​ значения. Это означает,​​изменение встроенного формата​​ чтобы они отображались​
   

3. ​ вас актуальными справочными​​ и В сидели​​ столбца с А1​​.​​snipe​ ячейке, в А2):​ о потере текста​ Center)​

4. ​ в каждой точке​​Для массового объединения также​​есть функция​ sales.​ вас уделить пару​ версии не поддерживается.​

   ​ что больше не​ не приводит к​ на листе как​ материалами на вашем​ на трубе.​ по А10 числа,​Благодарю!​: Public Function iSumma(Текст​
   ​154 р. -​​ .Merge Across:=False ‘объединяем​в Excel объединять-то​ соединения, т.е. на​

Объединение текста и чисел из разных ячеек в одной ячейке с помощью формулы

​ условий, используйте функцию​ вам, с помощью​Внимательно посмотрите на использование​ них.​Используйте код​ 200 # продукта.​ ее текст может​ ячейке В1 буква​Формула суммирования =СУММ​ uuu в A1​

​ As String a()​ р. — молоко;​ Mid(sMergeStr, 1 +​ сложность — в​ вы ставите несколько​, появившуюся начиная с​

​ 255) в одно​​ СУММЕСЛИМН . Например​

• ​ кнопок внизу страницы.​ функции​Для объединения чисел с​​Принцип действия​​Использование пользовательского числового формата​​ содержать неточности и​​ В​ (A1:A10)​​Function uuu(t$) Dim​​ = Split(Текст, «;»)​ 65 р. -​ Len(sDELIM)) ‘добавляем к​​ живых остается только​​ плюсов при сложении​​ Excel 2016. У​​ целое, позволяя комбинировать​ нужно добавить вверх​ Для удобства также​

• ​текст​​ помощью функции СЦЕПИТЬ​12 как Продукт №12​ для отображения номера​ грамматические ошибки. Для​Формулы​Формула пропускает пустые​ i% With CreateObject(«VBScript.RegExp»):​ For i =​ яблоки.​ объед.ячейке суммарный текст​​ текст из верхней​​ нескольких чисел (2+8+6+4+8)​ нее следующий синтаксис:​ их с произвольным​

Примеры

​ total sales определенного​ приводим ссылку на​

​во втором примере​ или функции ОБЪЕДИНЕНИЯ,​​»Продукт № » 0​​ с текстом, не​ нас важно, чтобы​=A1&B1 ( точно​ ячейки и с​ .Pattern = «d+»:​ 0 To UBound(a())​​Далее, из ячейки​​ End With End​ левой ячейки.​Если нужно приклеить произвольный​=ОБЪЕДИНИТЬ(Разделитель; Пропускать_ли_пустые_ячейки; Диапазон1; Диапазон2​ текстом. Например, вот​ продукта в рамках​ оригинал (на английском​ на рисунке. При​ текст и TEXTJOIN​Текст, заключенный в кавычки​ изменяя порядок сортировки​ эта статья была​​ как в считалке)​​ текстом. Суммирует только​

См. также

• ​ .Global = True​

• ​ asd = Trim(a(i))​

• ​ с такими данными​

• ​ Sub​

support.office.com

Способы добавления значений на листе

​Чтобы объединение ячеек происходило​​ текст (даже если​ … )​ так:​ определенной области продаж.​ языке) .​ присоединении к числа​ и амперсанд (&)​ (включая пробел) отображается​ число. Таким образом​ вам полезна. Просим​=СЦЕПИТЬ (A1;B1)​ числовые значения.​ For i =​ asd = Mid(asd,​ нужно вытащить и​Теперь, если выделить несколько​ с объединением текста​ это всего лишь​где​Нюанс: не забудьте о​Общие сведения о том,​

​Один быстрый и простой​ в строку текста​ оператор.​ в ячейке перед​ изменение способа отображения​ вас уделить пару​Randy orton​Kleom​​ 0 To .Execute(t).Count​​ 1, InStr(1, asd,​​ сложить все числа,​​ ячеек и запустить​​ (как в таблицах​​ точка или пробел,​Разделитель​ пробелах между словами​ как сложение и​ для добавления значений​ с помощью оператор​Примечания:​ числом. В этом​

​ номера без изменения​

​ секунд и сообщить,​: Вот: =СЧЁТЗ (A1:A3)​: что значит буквенное?​ — 1 uuu​ » «) -​ в ячейке, например,​ этот макрос с​

Добавление на основе условий

• ​ Word) придется использовать​ не говоря уж​- символ, который​ — их надо​ вычитание дат можно​ в Excel всего​ объединения, используйте функцию​

• ​ ​ коде «0» обозначает​ значения.​ помогла ли она​ В скобках диапазон​ если числа написаны​ = uuu +​ 1) iSumma =​

Сложение или вычитание дат

​ А1.​ помощью сочетания клавиш​ макрос. Для этого​ о целом слове),​ будет вставлен между​ прописывать как отдельные​ найти Добавление и​ воспользоваться функцией Автосумма.​текст​

Сложение и вычитание значений времени

​В Excel 2016Excel Mobile и​ число, которое содержится​Выполните следующие действия.​ вам, с помощью​ укажи.​ словами, то придется​ .Execute(t)(i) Next End​ iSumma + CDbl(asd)​Т.е. в А1​

support.office.com

3 способа склеить текст из нескольких ячеек

​ Alt+F8 или кнопкой​ откройте редактор Visual​ то этот текст​ фрагментами​ аргументы и заключать​
​ вычитание дат. Более​ Выделите пустую ячейку​, чтобы управлять​ Excel Online с​
​ в ячейке (например,​Выделите ячейки, формат которых​ кнопок внизу страницы.​
​Pulse​ сначала их вручную​ With End Function​

Способ 1. Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ

​ Next i End​​ должно получиться число​​Макросы​​ Basic на вкладке​​ надо заключать в​Второй аргумент отвечает за​ в скобки, ибо​ сложные вычисления с​ непосредственно под столбцом​ способом отображения чисел.​ помощью функции​

​ 12).​ требуется изменить.​ Для удобства также​: Нужно, чтобы складывались​ ввести в числовом​Svsh2015​ Function​

​ 889.​на вкладке Разработчик​Разработчик -​ кавычки. В предыдущем​ то, нужно ли​ текст.​ датами, читайте в​ данных. На вкладке​ В формуле используется​ОБЪЕДИНЕНИЯ​12:00 как 12:00 центральноевропейское​​На вкладке​​ приводим ссылку на​ только те ячейки​ виде, а потом​: добавлю еще вариант​AlexM​​Получается, у нас​​(Developer — Macros)​Visual Basic (Developer -​ примере с функцией​ игнорировать пустые ячейки​Очевидно, что если нужно​ статье даты и​ «​ базовое значение из​заменена​ время​

​Главная​ оригинал (на английском​ в которых содержится​​ складывать, иначе никак…​​ функции,в файл примере​: Можно массивной формулой​ ячейка А1 -​

​, то Excel объединит​ Visual Basic)​

​ СЦЕПИТЬ о кавычках​

• ​ (ИСТИНА или ЛОЖЬ)​​ собрать много фрагментов,​ операций со временем.​формулы​
• ​ ячейки, на который​функции СЦЕПИТЬ​ч:мм «центральноевропейское время»​в группе​
• ​ языке) .​​ числа. Т.е если​Demetry​ uuu (и есть​

​ Код =СУММ(—ЕСЛИ(ПРАВБ(ПСТР(A1;СТРОКА($1:$99);6);2)=»р.»;ПСТР(A1;СТРОКА($1:$99);3)))​

Способ 2. Символ для склеивания текста (&)

​ итого, а ячейка​ выделенные ячейки в​или сочетанием клавиш​ заботится сам Excel​

​Диапазон 1, 2, 3​​ то использовать эту​​Общие сведения о том,​» нажмите кнопку​ указывает ссылка (в​​. Несмотря на​​Текущее время показано в​​число​​Предположим, что нужно создать​ в ячейке текст,​​: =СЧЁТЕСЛИ (A1:A100;»*н*»)​​ uuu1)​Floyd73​ А2 — детализация.​ одну, слив туда​ Alt+F11, вставим в​

• ​ — в этом​… — диапазоны​ функцию уже не​ как сложение и​Автосумма​ данном примере.4) —​ то, что функция​ формате даты/времени ч:мм​
• ​щелкните стрелку.​ предложение грамматически правильные​ нужно, чтобы она​Считает в диапазоне​Function uuu#(t$) Dim​: Спасибо за столь​При небольшом гуглении​ же и текст​ нашу книгу новый​ же случае их​ ячеек, содержимое которых​ очень удобно, т.к.​ вычитание значений времени​>​ не форматированное значение,​

​СЦЕПИТЬ​ AM/PM, а текст​В списке​ из нескольких столбцов​ не учитывалась. Справку​

​ от А1 до​ i%, s# With​ разнообразные варианты решений!​ варианта решения не​​ через пробелы.​​ программный модуль (меню​ надо ставить вручную.​ хотим склеить​

Способ 3. Макрос для объединения ячеек без потери текста.

​ придется прописывать ссылки​ отображается Добавление и​Сумма​ отображаемое в ячейке​по-прежнему доступен для​ «московское время» отображается​категории​ данных для подготовки​​ смотрел, даже пример​ А100 количество ячеек,​ CreateObject(«VBScript.RegExp»): .Pattern =​​Теперь думаю, какой​ нашел, может быть​Floyd73​Insert — Module​Вот, например, как можно​Например:​ на каждую ячейку-фрагмент​

​ вычитание значений времени.​. Excel автоматически будут​ (40%). Чтобы восстановить​ обеспечения обратной совместимости,​ после времени.​выберите категорию, например​ массовой рассылки. Или,​​ есть, но почему​ ​ в тексте которых​ «d+»: .Global =​​ из них удобнее​ неправильно формулирую запрос,​: День добрый!​) и скопируем туда​​ собрать ФИО в​​Это универсальный и компактный​ по отдельности. Поэтому,​ Другие вычисления времени,​

​ определения диапазона, который​ форматов чисел используйте​ следует использовать​-12 как -12р. дефицит​настраиваемые​ возможно, вам нужно​ то он у​ есть буква «н».​ True For i​ будет использовать)))​ конечно…​Имеется небольшая задачка,​ текст такого простого​ одну ячейку из​ способ сцепки, работающий​ начиная с 2016​ можно просмотреть даты​ необходимо суммировать. (Автосумма​ функцию​ОБЪЕДИНЕНИЯ​ и 12 как​и нажмите кнопку встроенный​ форматирование чисел с​ меня не работает.​Алексей матевосов (alexm)​ = 0 To​Казанский​Подозреваю, что можно​

​ которую нужно реализовать​ макроса:​ трех с добавлением​ абсолютно во всех​ версии Excel, на​​ и операций со​​ также можно работать​​текст​​, так как​ 12р. избыток​ формат, который похож​ текстом, не затрагивая​_Boroda_​

planetaexcel.ru

Вытащить из ячейки числа и сложить их

​: После двух часового​​ .Execute(t).Count — 1​
​:​ сделать скриптом, но​ в экселе.​
​Sub MergeToOneCell() Const​ пробелов:​
​ версиях Excel.​ замену функции​ временем.​
​ по горизонтали при​.​функции СЦЕПИТЬ​0.00р. «избыток»;-0.00р. «дефицит»​ на то, которое​ формулы, которые могут​: =СУММ(I3;I5;I7;I10)​ раздумья по данному​
​ s = s​Floyd73​ работать с ними​
​Суть задачки вот​ sDELIM As String​Если сочетать это с​
​Для​СЦЕПИТЬ​Надпись на заборе: «Катя​ выборе пустую ячейку​Функция СЦЕПИТЬ​могут быть недоступны​
​Это значение отображается в​ вы хотите.​ использовать эти числа.​Итог 43​ вопросу появилась желание​ + CDbl(.Execute(t)(i)) Next​
​, до кучи Function​ не умею =(​ в чем.​
​ = » «​ функцией извлечения из​суммирования​пришла ее более​
​ + Миша +​ справа от ячейки,​СЦЕП​ в будущих версиях​ формате «Денежный». Кроме​
​В поле​ В Excel существует​Справка вражеская. Там​ предложить организаторам проекта​
​ uuu = s​ Fl(x) As Double​
​Можно решить задачу​В ячейке, например,​

​ ‘символ-разделитель Dim rCell​​ текста первых букв​​содержимого нескольких ячеек​​ совершенная версия с​ Семён + Юра​ чтобы суммировать.)​

​Функция ТЕКСТ​​ Excel.​
​ того, если в​Тип​ несколько способов для​ разделитель — запятая.​

​ «Вопросы и ответы»​​ End With End​ Dim y For​ обычными формулами?​ А2, записываются данные,​ As Range Dim​ -​ используют знак плюс​ похожим названием и​ + Дмитрий Васильевич​»Сумма».» />​Функция TEXTJOIN​TEXTJOIN​ ячейке находится положительное​измените коды числовых​ объединения текста и​

​ А у нас​​ усовершенствовать систему начисления​ Function​

​ Each y In​​Подскажите, пожалуйста, люди​ по следующему шаблону:​
​ sMergeStr As String​ЛЕВСИМВ (LEFT)​ «​

​ тем же синтаксисом​​ +​​Автосумма создает формулу для​​Примечание:​Объединение текста из​ значение (или 0),​ форматов в формате,​ чисел.​ — точка с​ баллов.​snipe​

​ Split(Replace(x, «,», «.»),​​ добрые!​число, пробел, текст,​

​ If TypeName(Selection) <>​​, то можно получить​+​ — функция​товарищ Никитин +​​ вас, таким образом,​​Мы стараемся как​
​ нескольких диапазонах и/или​

​ после него будет​​ который вы хотите​Если столбец, который вы​
​ запятой.​Только за прочтения​:​ «;») Fl =​OLEGOFF​ пробел, тире, пробел,​ «Range» Then Exit​ фамилию с инициалами​», а для​СЦЕП (CONCAT)​

​ рыжий сантехник +​​ чтобы вас не​ можно оперативнее обеспечивать​ строки, а также​ показан текст «(излишек)»,​
​ создать.​ хотите отсортировать содержит​Pulse​ таких вопросов начислять​Floyd73​ Fl + Val(y)​:​ текст, точка с​ Sub ‘если выделены​ одной формулой:​склеивания​. Ее принципиальное отличие​

​ Витенька + телемастер​​ требуется вводить текст.​​ вас актуальными справочными​​ разделитель, указанный между​ а если ячейка​Для отображения текста и​
​ числа и текст​:​ баллы, а за​
​, все присланные вам​ Next End Function​

CyberForum.ru

Подскажите пожалуйста как в excel посчитать сумму если значение в ячейке буквенное но нужно сложить все ячейки!???

​Floyd73​​ запятой (или точка),​ не ячейки -​Имеем текст в нескольких​содержимого ячеек используют​ в том, что​
​ Жора +​ Однако при желании​
​ материалами на вашем​ каждой парой значений,​ содержит отрицательное значение,​ чисел в ячейке,​

​ — 15 #​​_Boroda_​ ответ баллы умножать​ примеры заслуживают изучения​AlexM​, с помощью опции​ пробел (ставится после​ выходим With Selection​

​ ячейках и желание​​ знак «​
​ теперь в качестве​сволочь Редулов +​ введите формулу самостоятельно​ языке. Эта страница​ который будет добавляться​

Excel. Как сложить ячейки в excel , если в ячейках забита буква а не цифра ?

​ после него будет​​ заключите текст в​ продукта, продукт #100​, Спасибо! Выходит в​ на коэффициент.​ с целью понимания​: Вариант с макросом​ » Текст по​
​ точки с запятой,​ For Each rCell​ — объединить эти​&​ аргументов можно задавать​
​ не вспомнить имени,​ просматривать функцию сумм.​ переведена автоматически, поэтому​ текст. Если разделитель​
​ показан текст «(недостаток)».​ двойные кавычки (»​ и 200 #​ справке ошибка?!​И так. Ячейки,​
​помимо моей функции​ универсальный​ столбцам» можно так​ после точки не​
​ In .Cells sMergeStr​
​ ячейки в одну,​» (расположен на большинстве​
​ не одиночные ячейки,​

​ длинноволосый такой +​​Используйте функцию СУММЕСЛИ ,​ ее текст может​ пустую текстовую строку,​

Сложение ячеек содержащих текст (Сложение ячеек содержащих текст)

​При объединении чисел и​​ «), или чисел​ продукта — не​Pulse​ содержащие значения, можно​ вам предложены более​Floyd73​Floyd73​ ставится).​ = sMergeStr &​ слив туда же​ клавиатур на цифре​

​ а целые диапазоны​​ещё 19 мужиков​
​ если нужно суммировать​
​ содержать неточности и​ эта функция будет​ текста в ячейке,​ с помощью обратной​ может сортировать должным​

​: Капец!​​ считать, как показал​​ красивые решения​​: Всем спасибо, буду​: Спасибо за решение.​

​И так далее,​​ sDELIM & rCell.Text​

excelworld.ru

​ их текст. Проблема​

Сумма, если ячейки содержат звездочку

=СУММЕСЛИ(диапазон;»*~**»;ранг_суммы)

Чтобы посчитать сумму, если ячейки содержат звездочку, можно использовать функцию СУММЕСЛИ со специальным символом «тильда» — «~».

В примере показано, ячейка G6 содержит эту формулу:

=СУММЕСЛИ(С5:С11;»*~**»;D5:D11)

Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит «*».

Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».

Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символов» и так далее.

Потому как звездочки и вопросительные знаки, сами по себе являются подстановочными знаками, если вы хотите искать эти символы в частности, вы должны защитить их с тильдой (~). Тильда причины Excel следует рассматривать следующий символ буквально.

В этом случае мы используем «~*», чтобы соответствовать символу звездочки, но он окружен звездочками по бокам, для того чтобы соответствовать звездочкам в любом месте ячейки. Если вы просто хотите, чтобы соответствовало звездочкам на конце ячейки, используйте: «*~**» по критериям.

Альтернатива с СУММЕСЛИМН

Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. С СУММЕСЛИМН, диапазон сумма всегда стоит на первом месте в списке аргументов, затем пара/диапазон критериев:

=СУММЕСЛИМН(D5:D11;С5:С11;»*~**»)

Сумма, если ячейки содержат X и Y

=СУММЕСЛИМН(диап1;диап2;»*кошка*»;диап2;»*крыса*»)

Чтобы посчитать сумму, если ячейки содержат X и Y (т. е. содержат «кошка» и «крыса», в одной ячейке) можно использовать функцию СУММЕСЛИМН.

В показанном примере, формула в F5:

=СУММЕСЛИМН(С5:С9;В5:В9;»*кошка*»;В5:В9;»*крыса*»)

Функция СУММЕСЛИМН основана на логике и такое поведение является автоматическим. Нам просто нужно поставить два диапазона/критерии пары, работающие на одном диапазоне (В5:В9).

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

Когда оба критерия рассчитывает Истина в том же ряду, СУММЕСЛИМН суммирует значения в столбец C.

Обратите внимание, что СУММЕСЛИМН не чувствителен к регистру.

Сумма, если ячейки содержат либо x, либо Y

=СУММПРОИЗВ(—((ЕЧИСЛО(ПОИСК(«кошка»;диап1)) + ЕЧИСЛО(ПОИСК(«крыса»;диап1)))>0);диап2)

Чтобы посчитать сумму, если ячейки содержат либо одну текстовую строку или другую (т. е. содержащие «кошка» или «крыса») вы можете использовать функция СУММПРОИЗВ.

Когда вы суммируете ячейки с критерием «или», вы должны быть осторожны, чтобы не произошел двойной счет, когда существует вероятность того, что оба критерия будет рассчитывать Истина. В показанном примере, мы хотим просуммировать значения в столбце C, когда ячейки в столбце B содержат слово «кошка» или «крыса». Мы не можем использовать СУММЕСЛИМН по двум критериям, т. к. СУММЕСЛИМН основан на логике. И если мы попытаемся использовать две функции СУММЕСЛИМН (т.е. СУММЕСЛИМН + СУММЕСЛИМН) у нас будет двойной счет, потому что есть клетки, которые содержат как «кошка», так и «крыса»

Одно из решений-использовать функцию СУММПРОИЗВ с ЕЧИСЛО + ПОИСК или НАЙТИ.

=СУММПРОИЗВ(—((ЕЧИСЛО(ПОИСК(«кошка»;В5:В9)) + ЕЧИСЛО(ПОИСК(«крыса»;В5:В9)))>0);С5:С9)

Эта формула основана на формуле, что находит текст внутри ячейки:

ЕЧИСЛО(ПОИСК(«Азбука»;В4:В10)

При заданном диапазоне ячеек, этот фрагмент будет рассчитывать массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Поскольку мы делаем это дважды (один раз для «кошки» и один раз для «крысы»), мы получим два массива.

Далее, мы добавляем эти массивы вместе (с +), которая создает новый единый массив чисел. Каждое число в этом массиве является результатом добавления истинных и ложных значений в исходных двух массивах вместе. В показанном примере, массив выглядит так:

{2;0;2;1;0}

Нам нужно добавить эти цифры, но мы не хотим, чтобы удвоился счет. Поэтому мы должны убедиться, что любое значение больше нуля, всего лишь раз пересчитать. Чтобы сделать это, мы превращаем все значения Истина или Ложь, проверяя массив с «>0». Это рассчитывает Истина или Ложь:

{ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ}

Которые мы затем преобразовываем в 1/0 с помощью двойного отрицания (—):

{1;0;1;1;0}

Параметр чувствителен к регистру

Функция поиска не учитывает регистр. Если вам нужен чувствительный вариант, замените поиск на найти.

Сумма, если ячейки содержат определенный текст

=СУММЕСЛИ(диапазон;»*текст*»;ранг_суммы)

Чтобы посчитать сумму, если ячейки содержат определенный текст, вы можете использовать функцию СУММЕСЛИ с подстановкой.

В примере показано, ячейка G4 содержит эту формулу:

=СУММЕСЛИ(С5:С11;»*футболка*»; D5:D11)

Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит «футболка». Обратите внимание, что СУММЕСЛИ-это не регистр.

Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».

Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символа» и так далее.

Нужно, чтобы соответствовали все элементы, которые содержат слово «футболка», критериям»*футболка*». Обратите внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки («»).

Альтернатива с СУММЕСЛИМН

Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:

=СУММЕСЛИМН(D5:D11;С5:С11;»*футболка*»)

Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.

Сумма, если ячейка заканчивается определенным текстом

=СУММЕСЛИ(диапазон;»текст»;ранг_суммы)

В сумме, если ячейки заканчиваются определенным текстом, можно использовать функцию СУММЕСЛИ.

В примере показано, ячейка G5 содержит эту формулу:

=СУММЕСЛИ(C5:C11; «*шапка»; D5:D11)

Эта формула суммирует ячейки именованного диапазона сумма (D5:D11), только если ячейки именованного диапазона (С5:С11) оканчиваются на «шапка».

Обратите внимание, что СУММЕСЛИ не поддерживает регистр. Критерию «*шапка» соответствует любой текст, который заканчивается «Шапка» или «шапка».

Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».

Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символа» и так далее.

Чтобы соответствовали все элементы, которые оканчиваются на «шапка» перед текстом нужно поставить звездочку (*):

пункт;»*шапка»

Обратите внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки («»).

Альтернатива с СУММЕСЛИМН

Вы также можете использовать функцию СУММЕСЛИМН в сумме, если клетки начинаются с. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:

=СУММЕСЛИМН(объем; номенклатура;»*шапка»)

Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.

Сумма, если равен х или у

Если вам необходимо суммировать числа, основанные на других ячейках равных либо одному значению или другому (х или у), вы можете использовать функцию СУММЕСЛИ.

В примере, мы подсчитываем все продажи либо от Западного или Северного региона. Формула в ячейке G6 является:

=СУММЕСЛИ(C5:C14; «Западный»; E5:E14)+СУММЕСЛИ(C5:C14; «Северный»; E5:E14)

Каждый экземпляр СУММЕСЛИ обеспечивает промежуточный итог, один для продаж на Западе, один для продаж на Севере. Формула просто добавляет эти два результата вместе.

СУММЕСЛИ с аргументом массива

Более элегантное решение, чтобы дать функции СУММЕСЛИ более одного значения для критериев, использовать константу массива. Чтобы сделать это, постройте нормальный СУММЕСЛИ, но пакет критериев в синтаксисе массива — фигурные скобки, с отдельными элементами, разделенные запятыми. И, наконец, обернуть всю функцию СУММЕСЛИ в функцию СУММ. Это необходимо, потому что СУММЕСЛИ будет рассчитывать один результат для каждого элемента массива критериев. Они должны быть добавлены вместе, чтобы получить один результат.

Формула:

= СУММ(СУММЕСЛИ( область ; { «Западный» ; «Северный» } ; сумма ))

СУММПРОИЗВ альтернатива

Вы можете также использовать СУММПРОИЗВ для подсчета ячеек с логикой ИЛИ. Формула:

=СУММПРОИЗВ( количество * (( регион = «Западный» ) + ( регион = «Северный» )))

Это также может быть записано в виде:

=СУММПРОИЗВ( количество * ( регион = { «Западный» ; «Северный» } ))

СУММПРОИЗВ не так быстра, как СУММЕСЛИ, но разница в скорости не заметна с меньшими наборами данных.

Сумма, если ячейки не пустые

=СУММЕСЛИ( диапазон ; «<>» ; суммарный_диапазон )

Для подсчита ячеек, когда определенные значения не являются пустыми, вы можете использовать функцию СУММЕСЛИ.

В показанном примере, ячейка G4 содержит следующую формулу:

=СУММЕСЛИ( C5: C11 ; «<>» ; D5: D11 )

Эта формула суммирует суммы в колонке D только тогда, когда значение в столбце С не пусто

Функция СУММЕСЛИ поддерживает все стандартные операторы Excel, в том числе не равно к, который вводится в <>.

При использовании оператора в критериях функции как СУММЕСЛИ, необходимо заключить его в двойные кавычки ( «»). При использовании только «<>» в качестве критерия, вы можете думать о значении как «не равно пустой», или «не пусто».

Альтернатива с СУММЕСЛИМН

Вы можете также использовать функцию суммы СУММЕСЛИМН, если ячейки не являются пустыми. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эта формула СУММЕСЛИМН эквивалентна формуле СУММЕСЛИ выше:

=СУММЕСЛИМН( D5: D11 ; C5: C11 ; «<>» )

С СУММЕСЛИМН диапазон сумма всегда стоит первым.

Объединение текста и чисел

Допустим, вам нужно создать грамматически правильное предложение из нескольких столбцов данных для подготовки массовой рассылки. Или, возможно, вам нужно форматировать числа с помощью текста, не влияя на формулы, в которых используются эти номера. В Excel есть несколько способов объединения текста и чисел.

Использование числового формата для отображения текста до или после числа в ячейке

Если столбец, который нужно отсортировать, включает в себя числа и текст, например #15 продуктов, #100 продуктов, #200 товаров — она может не отсортировать так, как ожидалось. Ячейки, содержащие 15, 100 и 200, можно форматировать так, чтобы они отображались на листе в виде #15 продуктов, #100 продуктов и #200 продуктов.

Использование настраиваемого числового формата для отображения числа с текстом без изменения режима сортировки номера. Таким образом, вы измените способ отображения номера без изменения значения.

Выполните указанные ниже действия:

Выделите ячейки, которые нужно отформатировать.

На вкладке Главная в группе число щелкните стрелку.

В списке Категория выберите категорию, например «Настраиваемая «, а затем — встроенный формат, похожий на нужный.

В поле Type (тип ) измените коды форматов чисел, чтобы создать нужный формат.

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

Примечание. При редактировании встроенного формата формат не удаляется.

12 как #12 продукта

Текст, заключенный в кавычки (включая пробелы), отображается перед числом в ячейке. В коде 0 — число, содержащееся в ячейке (например, 12).

12:00 в качестве 12:00 AM

Текущее время отображается в формате даты и времени, которое не входит в отчет, а текст «EST» отображается после времени.

-12 в виде $-12,00 недостачи и 12 в $12,00 излишков

$0,00 «излишки»; $-0,00 «недостачи»

Значение отображается в денежном формате. Кроме того, если ячейка содержит положительное значение (или 0), после значения отображается «излишек». Если ячейка содержит отрицательное значение, вместо этого отображается «нехватка».

Объединение текста и чисел из разных ячеек в одну и ту же ячейку с помощью формулы

Если в ячейке объединяются числа и текст, числа становятся текстом и больше не заключаются в виде числовых значений. Это означает, что на них больше нельзя выполнять математические операции.

Для объединения чисел используйте функции сцепить или concat, Text или объединить и оператор амперсанд (&).

В Excel 2016, Excel Mobile и Excel Online функция сцепить была заменена функцией concat . Несмотря на то, что функция сцепить по-прежнему доступна для обеспечения обратной совместимости, следует использовать функцию concat, так как функция сцепить может быть недоступна в будущих версиях Excel.

Объединить сочетает текст из нескольких диапазонов и (или) строк и включает разделитель, заданный между каждым текстовым значением, которое будет объединено. Если в качестве разделителя используется пустая текстовая строка, функция эффективно объединит диапазоны. Объединить недоступен в Excel 2013 и предыдущих версиях.

Посмотрите различные примеры на рисунке ниже.

Внимательно посмотрите на использование функции текст во втором примере на рисунке. При присоединении номера к текстовой строке с помощью оператора конкатенации используйте функцию текст для управления способом отображения числа. Формула использует основное значение из ячейки, на которую указывает ссылка (4 в этом примере), а не отформатированного значения, которое вы видите в ячейке (40%). Чтобы восстановить форматирование чисел, используйте функцию текст .

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Как в excel суммировать ячейки с цифрой и текстом?

Как в excel суммировать ячейки с цифрой и текстом?

т.е.
в ячейке G2 должна получиться сумма всех ячеек строки 2 где есть буква «л»,

а в ячейке H2 сумма всех ячеек строки 2 где есть буква «п»

пример на скрине

• Вопрос задан 08 апр.
• 545 просмотров

Для буквы «л» формула такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(«л»;B4:G4);—ПОДСТАВИТЬ(B4:G4;»л»;»»));0))
Для «п» соответственно такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(«п»;B4:G4);—ПОДСТАВИТЬ(B4:G4;»п»;»»));0))

Обе — формулы массива. Вводятся одновременным нажатием клавиш Ctrl+Shift+Enter

Игорь, может еще поможете с одним вопросом, правда не знаю возможно ли вообще такое.

Суть в следующем. Для каждой ячейки хочу сделать выпадающий список с этими же значениями, например 8л;2п;3л и т.д

Как сделать чтобы в ячейке при выборе значения была видна только цифра? например я выбрал 8л из выпадающего списка, но в ячейке было видно только цифру 8, но при этом работала ваша формула

Для буквы «л» формула такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(«л»;B4:G4);—ПОДСТАВИТЬ(B4:G4;»л»;»»));0))
Для «п» соответственно такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(«п»;B4:G4);—ПОДСТАВИТЬ(B4:G4;»п»;»»));0))

Надеюсь понятно написал, что я хочу?

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

3 способа склеить текст из нескольких ячеек

Надпись на заборе: «Катя + Миша + Семён + Юра + Дмитрий Васильевич +
товарищ Никитин + рыжий сантехник + Витенька + телемастер Жора +
сволочь Редулов + не вспомнить имени, длинноволосый такой +
ещё 19 мужиков + муж = любовь!»

Способ 1. Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ

В категории Текстовые есть функция СЦЕПИТЬ (CONCATENATE) , которая соединяет содержимое нескольких ячеек (до 255) в одно целое, позволяя комбинировать их с произвольным текстом. Например, вот так:

Нюанс: не забудьте о пробелах между словами — их надо прописывать как отдельные аргументы и заключать в скобки, ибо текст.

Очевидно, что если нужно собрать много фрагментов, то использовать эту функцию уже не очень удобно, т.к. придется прописывать ссылки на каждую ячейку-фрагмент по отдельности. Поэтому, начиная с 2016 версии Excel, на замену функции СЦЕПИТЬ пришла ее более совершенная версия с похожим названием и тем же синтаксисом — функция СЦЕП (CONCAT) . Ее принципиальное отличие в том, что теперь в качестве аргументов можно задавать не одиночные ячейки, а целые диапазоны — текст из всех ячеек всех диапазонов будет объединен в одно целое:

Для массового объединения также удобно использовать новую функцию ОБЪЕДИНИТЬ (TEXTJOIN) , появившуюся начиная с Excel 2016. У нее следующий синтаксис:

=ОБЪЕДИНИТЬ( Разделитель ; Пропускать_ли_пустые_ячейки ; Диапазон1 ; Диапазон2 . )

• Разделитель — символ, который будет вставлен между фрагментами
• Второй аргумент отвечает за то, нужно ли игнорировать пустые ячейки (ИСТИНА или ЛОЖЬ)
• Диапазон 1, 2, 3 . — диапазоны ячеек, содержимое которых хотим склеить

Например:

Способ 2. Символ для склеивания текста (&)

Это универсальный и компактный способ сцепки, работающий абсолютно во всех версиях Excel.

Для суммирования содержимого нескольких ячеек используют знак плюс «+«, а для склеивания содержимого ячеек используют знак «&» (расположен на большинстве клавиатур на цифре «7»). При его использовании необходимо помнить, что:

• Этот символ надо ставить в каждой точке соединения, т.е. на всех «стыках» текстовых строк также, как вы ставите несколько плюсов при сложении нескольких чисел (2+8+6+4+8)
• Если нужно приклеить произвольный текст (даже если это всего лишь точка или пробел, не говоря уж о целом слове), то этот текст надо заключать в кавычки. В предыдущем примере с функцией СЦЕПИТЬ о кавычках заботится сам Excel — в этом же случае их надо ставить вручную.

Вот, например, как можно собрать ФИО в одну ячейку из трех с добавлением пробелов:

Если сочетать это с функцией извлечения из текста первых букв — ЛЕВСИМВ (LEFT) , то можно получить фамилию с инициалами одной формулой:

Способ 3. Макрос для объединения ячеек без потери текста.

Имеем текст в нескольких ячейках и желание — объединить эти ячейки в одну, слив туда же их текст. Проблема в одном — кнопка Объединить и поместить в центре (Merge and Center) в Excel объединять-то ячейки умеет, а вот с текстом сложность — в живых остается только текст из верхней левой ячейки.

Чтобы объединение ячеек происходило с объединением текста (как в таблицах Word) придется использовать макрос. Для этого откройте редактор Visual Basic на вкладке Разработчик — Visual Basic (Developer — Visual Basic) или сочетанием клавиш Alt + F11 , вставим в нашу книгу новый программный модуль (меню Insert — Module) и скопируем туда текст такого простого макроса:

Теперь, если выделить несколько ячеек и запустить этот макрос с помощью сочетания клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer — Macros) , то Excel объединит выделенные ячейки в одну, слив туда же и текст через пробелы.

Пример функции СУММЕСЛИ для суммирования в Excel по условию

Функция СУММЕСЛИ в Excel используется для расчета суммы числовых значений, содержащихся в диапазоне ячеек, с учетом критерия, указанного в качестве одного из аргументов, и возвращает соответствующее числовое значение. Данная функция является альтернативой совместного использования функций СУММ и ЕСЛИ. Ее использование позволяет упростить формулы, поскольку критерий, по которому производится суммирование значений, предусмотрен непосредственно в ее синтаксисе.

Примеры использования функции СУММЕСЛИ в Excel

Пример 1. В таблице Excel записаны члены геометрической прогрессии. Какую часть (в процентах) составляет сумма первых 15 членов прогрессии от общей суммы ее членов.

Вид исходной таблицы данных:

Выполним расчет с помощью следующей формулы:

• A3:A22 – диапазон ячеек, содержащих порядковые номера членов прогрессии, относительно которых задается критерий суммирования;
• » СУММЕСЛИ(C3:C21;»Петров»;B3:B21);»Иванов»;»Петров»)’ class=’formula’>

Функция ЕСЛИ выполняет проверку возвращаемых значений функциями СУММЕСЛИ с условиями проверки «Иванов» и «Петров» соответственно и возвращает текстовую строку с фамилией продавца, суммарная прибыль которого оказалась больше.

В итоге получим следующее значение:

Как в Excel суммировать ячейки только с определенным значением

Пример 3. В таблице указаны данные о зарплате сотрудника на протяжении 12 месяцев прошлого года. Рассчитать доходы работника за весенние месяцы.

Вид таблицы данных:

Весенними месяцами являются месяца с номерами 3, 4 и 5. Для расчета используем формулу:

Сумма зарплат с 6-го по 12-й месяц является подмножеством множества суммы зарплат с 3-го по 12-й месяц. Разница этих сумм является искомой величиной – суммой зарплат за весенние месяцы:

Функцию СУММЕСЛИ можно использовать если требуется определить сразу несколько значений для различных критериев. Например, для расчета суммы зарплат за три первых и три последних месяца в году соответственно составим следующую таблицу:

Для расчетов используем следующую формулу:

В результате получим:

Особенности использования функции СУММЕСЛИ в Excel

Функция СУММЕСЛИ имеет следующий синтаксис:

=СУММЕСЛИ( диапазон; условие; [диапазон_суммирования])

• диапазон – обязательный для заполнения аргумент, принимающий ссылку на диапазон ячеек с данными, в отношении которых применяется определенный критерий. В ячейках данного диапазона могут содержаться имена, текстовые строки, данные ссылочного типа, числовые значения, логические ИСТИНА или ЛОЖЬ, даты в формате Excel. Если данный диапазон также является диапазоном суммирования (третий аргумент опущен), на итоговый результат не влияют пустые ячейки и ячейки, содержащие текстовые данные.
• условие – обязательный для заполнения аргумент, который может быть указан в виде числа, текстовой строки, логического выражения, результата выполнения какой-либо функции. Переданное в качестве данного аргумента значение или выражение является критерием суммирования рассматриваемой функции.
• [диапазон_суммирования] – необязательный для заполнения аргумент, принимающий ссылку на диапазон ячеек, содержащих числовые значения, для которых будет рассчитана сумма с учетом критерия суммирования (условие).

1. Если третий необязательный аргумент явно не указан, диапазон ячеек, указанных в качестве первого аргумента, также является диапазоном суммирования.
2. Условия, представленные в виде текстовой строки или выражения, содержащего символы «>», « <», «=», должны быть указаны в кавычках. Если аргумент условие представлен в виде числа, кавычки не требуются.
3. Если аргумент условие указан в виде текстовой строки, можно использовать жесткий критерий (точное совпадение с указанной подстрокой) или выполнить поиск значений с неточным совпадением, заменив недостающие символы звездочкой «*» (любое количество символов) или вопросительным знаком «?» (один любой символ). В качестве примеров могут быть критерии «ст?л» (стол либо стул при поиске наименований мебели), «Ива*» (фамилии Иванов, Иваненко, Иванищев и другие, которые начинаются на «Ива»).
4. Если функции ссылаются на ячейки, содержащие коды ошибок #ЗНАЧ! или текстовые строки длиной свыше 255 символов, функция СУММЕСЛИ может возвращать некорректный результат.
5. Аргументы могут ссылаться на диапазоны с разным количеством ячеек. Функция СУММЕСЛИ рассчитывает сумму значений для такого количества ячеек из диапазона суммирования, которое соответствует количеству ячеек, содержащихся в диапазоне. Расчет выполняется с левой верхней ячейки диапазона суммирования.
6. Функция СУММЕСЛИ позволяет использовать только один критерий суммирования. Если необходимо указать сразу несколько критериев, следует использовать функцию СУММЕСЛИМН.
7. Критерий суммирования не обязательно должен относиться к диапазону суммирования. Например, для расчета общей зарплаты сотрудника за год в таблице, в которой содержатся данные о зарплате всех сотрудников, можно ввести формулу =СУММЕСЛИ(A1:A100;”Петренко”;B1:B100), где:

• a. A1:A100 – диапазон ячеек, в которых хранятся фамилии сотрудников;
• b. «Петренко» – критерий поиска (фамилия работника) для диапазона A1:A100;
• c. B1:B100 – диапазон ячеек, в которых хранятся данные о зарплатах работников (диапазон суммирования).

3 способа склеить текст из разных ячеек Excel

Очень часто возникает необходимость собрать текст из разных ячеек и соединить в одну. Самое худшее решение — ручное копирование и вставка в итоговую ячейку. Если ячеек много, то такая работа превратится в нудное мучение, да и вероятность ошибки весьма высока. Сегодня мы рассмотрим 4 способа склеивания/сцепки текста формулами Excel.

Оператор конкатенации

В Excel есть специальный символ, который используется для склеивания частей текста. Этот символ — амперсанд ( & ). Сама операция соединения называется конкатенацией (от англ. слова Concatinate). Амперсанд нужно использовать в формулах, вставляя его между каждой парой соединяемых строк. Например, чтобы сцепить текст из ячеек A1, A2 и A3 нужно ввести формулу:

Вместо ссылок на ячейки могут быть и текстовые константы (это относится и к двум следующим способам). Только не забывайте, что любой текст в формулах Excel должен быть заключен в кавычки. Например, чтобы сцепить текст «До дедлайна осталось » и значение из ячейки A1 нужна формула:

=»До дедлайна осталось «&A1

Минус этого способа — отсутствие разделителей между соединяемыми частями текста. Если нужно отделить куски пробелами — их придется добавлять вручную, например:

Этот же оператор используется для склеивания текста в синтаксисе языка VBA

Функции СЦЕПИТЬ / СЦЕП

Второй способ — использование функции СЦЕПИТЬ . Она, по сути, имитирует работу оператора конкатенации, но от нас не требуется вводить его вручную. Части соединяемого текста нужно указать в качестве аргументов функции, например:

=СЦЕПИТЬ(«До дедлайна осталось «;A1)

Минус у данного способа тот же, что и у предыдущего — нужно вручную вводить разделители при необходимости. Кроме того, функция СЦЕПИТЬ требует указания каждой ячейки по отдельности. Ее наследница, функция СЦЕП , которая появилась в новых версиях Excel, умеет соединить весь текст в указанном диапазоне, что гораздо удобнее. Вместо того, чтобы кликать каждую ячейку, можно выделить сразу весь диапазон, например:

Формула выше склеит последовательно текст из 20 ячеек диапазона A1:B10. Склеивание происходит в следующем порядке: слева направо до конца строки, а потом переход на следующую строку.

Кроме того, данную функцию можно использовать как формулу массива, передавая ей в качестве аргумента условие для соединения строк. Например, формула

соединит между собой только те ячейки столбца B, рядом с которыми в столбце A указано «ОК».

Функция ОБЪЕДИНИТЬ

В Excel 2016 появилась еще одна функция, которая умеет соединять текстовые строки. Она является усовершенствованной версией функции СЦЕП. В нее добавлена возможность задавать разделитель (1-ый аргумент) и указывать ей, как обрабатывать пустые ячейки (2-ой аргумент). Например, формула

соединит ячейки диапазона A2:A10, проставив после каждой запятую и пробел, но при этом пустые ячейки будут пропущены. Если заменить ИСТИНА на ЛОЖЬ, то пустые ячейки также буду учтены и отделены от других частей текста указанным в первом аргументе разделителем.

Разумеется, в качестве ячеек для склеивания можно задавать диапазоны, одиночные ячейки, текстовые строки.

Видеоверсию данной статьи смотрите на нашем канале на YouTube

Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel — подписывайтесь на наш канал в Telegram Excel Everyday

Много интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point, Visio и т.д.) — на нашем канале в Telegram Office Killer

Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot

Вопросы по другому ПО (кроме Excel) задавайте второму боту — @KillOfBot