Сложить текст и число excel
Предположим, что для подготовки массовой рассылки необходимо создать правильное предложение из нескольких столбцов данных. Или, возможно, вам нужно отформатирование чисел с текстом, не влияя на формулы, в которые эти числа используются. В Excel есть несколько способов объединения текста и чисел.
Отображение текста до или после числа в ячейке с помощью числового формата
Используйте пользовательский числовом формате для отображения числа с текстом, не изменяя при этом поведение сортировки числа. Таким образом вы можете изменить способ их изменения, не изменяя значение.
Выделите ячейки, которые нужно отформатировать.
На вкладке Главная в группе Число щелкните стрелку .
В списке Категория выберите категорию, например Настраиваемые, и выберите встроенный формат, похожий на нужный.
В поле Тип введите коды числов, чтобы создать нужный формат.
Чтобы в ячейке отображался как текст, так и числа, заключив текстовые символы в двойные кавычка (" ") или перед числами, заключив их в обратное начертение (\).
ПРИМЕЧАНИЕ. При редактировании встроенного формата формат не удаляется.
Для отображения
Используйте код
Как это работает
Текст в кавычках (включая пробел) отображается перед числом в ячейке. В коде "0" представляет число в ячейке (например, 12).
12:00 по 12:00 по 00:00 по EST
Текущее время отображается с использованием формата даты и времени ч:мм.
-12 из -12,00 долларов США за недостаток и 12 — из-за избыток 12,00 долларов США
$Избыток" 0,00 долларов США;"Недостаток" — 0,00 долларов США
Значение отображается в формате валюты. Кроме того, если ячейка содержит положительное значение (или 0), после него отображается значение "Избыток". Если ячейка содержит отрицательное значение, вместо нее отображается значение "Недостаток".
Объединение текста и чисел из разных ячеек в одной ячейке с помощью формулы
Когда числа и текст объединяются в ячейке, они становятся текстом и перестают работать как числовое значение. Это означает, что с ними больше нельзя выполнять математические операции.
Для объединения чисел используйте функции СОВМЕЩАТЬ или СОВМЕЩАТЬ, ТЕКСТ или ОБЪЕДИНИТЬ, а также оператор амперсанд (&).
В Excel 2016, Excel Mobile и Excel в Интернетефункция С ФУНКЦИИ СОВМЕСТИТЬ была заменена функцией СОВМЕСТИМ. Несмотря на то что функция С ФУНКЦИИ СОВМЕСТИТЬ по-прежнему доступна для обратной совместимости, следует использовать функцию СОВМЕСТИМАЯ, так как функция С ФУНКЦИИ СОВМЕСТИМАЯ может быть недоступна в будущих версиях Excel.
Объединить объединяет текст из нескольких диапазонов или строк и содержит между текстовыми значениями, которые будут объединены. Если в качестве разделителя используется пустая текстовая строка, функция эффективно объединит диапазоны. В Excel 2013 и предыдущих версиях эта Excel 2013 недоступна.
Примеры
На рисунке ниже приведены различные примеры.
Внимательно посмотрите на использование функции ТЕКСТ во втором примере на рисунке. Если вы присоедините число к текстовой строке с помощью оператора секаций, используйте функцию ТЕКСТ для управления тем, как число отображается. В формуле используется значение из ячейки, на которые ссылается ссылка (в данном примере — 0,4), а не отформатированные значения, которые вы видите в ячейке (40 %). Для восстановления числового форматирования используется функция ТЕКСТ.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Как суммировать ячейки с текстом и числами в Excel?
Например, у меня есть список значений, содержащих числовые и текстовые строки, теперь я хочу суммировать только числа на основе одного и того же текста, посмотрите на следующий снимок экрана. Обычно вы не можете напрямую суммировать значения в списке с помощью текстовой строки, здесь я представлю вам несколько формул для решения этой задачи.
Суммировать ячейки, содержащие текст и числа на основе определенного текста с формулой
Вы можете использовать формулу массива для суммирования чисел на основе соответствующей текстовой строки в ячейке, сделайте следующее:
1. Сначала вы можете записать свои текстовые строки, в которых вы хотите просуммировать относительные числа в ячейках столбца.
2. Затем введите формулу ниже в пустой ячейке, в этом примере я введу формулу в ячейку E2, а затем нажмите Ctrl + Shift + Enter вместе, он вернет общее значение из списка на основе определенного текста. Смотрите скриншот:
Внимание: В приведенной выше формуле D2 это критерий, который вы хотите использовать, и B2: B9 указывает диапазон ячеек, который вы хотите суммировать.
3. Затем выберите ячейку формулы и перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, чтобы получить результат:
Суммировать ячейки, содержащие текст и числа на основе определенного текста, с удобной функцией
Если вам сложно понять приведенную выше формулу, Kutools for Excel предоставляет полезный инструмент - Сумма на основе того же текста, с помощью этой функции вы можете получить общий результат в ячейке, смешанной с числами и текстом, не запоминая никаких формул.
Примечание: Чтобы применить эти Сумма на основе того же текста функции, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените функции.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Нажмите, чтобы выбрать ячейку, в которую вы хотите вывести результат, см. Снимок экрана:
2, Затем нажмите Кутулс > Формула Помощник > Формула Помощник, см. снимок экрана:
3. В Помощник по формулам диалоговом окне выполните следующие действия:
- Выберите Статистический из Тип формулы раскрывающийся список;
- В Выберите формулу список, щелкните, чтобы выбрать Сумма на основе того же текста вариант;
- Затем в Ввод аргументов раздел, выберите диапазон ячеек, содержащий текст и числа, которые вы хотите суммировать в Диапазон текстовое поле, а затем выберите текстовую ячейку, значения которой вы хотите суммировать на основе Текст текстовое окно.
Советы: В Текст текстовое поле, вы должны изменить абсолютную ссылку на ячейку по умолчанию на относительную ссылку на ячейку для правильного перетаскивания формулы.
4. А затем нажмите Ok Кнопка, будет вычислен первый результат, затем перетащите маркер заполнения для заполнения формулы в другие ячейки, см. снимок экрана:
Суммировать ячейки, содержащие текст и числа, игнорируя текст, с помощью функции, определяемой пользователем
Если у вас есть следующие данные, смешанные с текстом и числами, и вы просто хотите извлечь и сложить только числа в каждой ячейке, как вы могли бы быстро закончить это в Excel ?.
1. Удерживайте ALT + F11 , чтобы открыть Окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: суммируйте только числа в ячейке, содержащей числа и текст
3. Затем сохраните и закройте этот код, вернитесь на рабочий лист и введите эту формулу = SumNumbers (A2) (A2 указывает ячейку, в которой вы хотите суммировать числа), затем перетащите маркер заполнения вниз к ячейкам, которые вы хотите заполнить формулой, и только числа в каждой ячейке добавляются вместе, см. снимок экрана:
пример на скрине
- Вопрос задан более трёх лет назад
- 7782 просмотра
Простой 8 комментариев
IMHO формула не умеет считать цифры с буквами. Вам нужно хранить буквы и цифры в разных ячейках для формулы.
Saboteur, а если подсчитать сумму только тех ячеек где есть любая буква. Если просто использовать сумм, то суммируются ячейки без текста, а как сделать наоборот, чтобы суммировались только ячейки которые содержат любую букву?
Для буквы "л" формула такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК("л";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, думаю, тут не обойтись без макроса, который будет красить букву в цвет фона ячейки при выборе какого-то значения из списка. Таким образом, в списке будут цифры с буквами, а в ячейке видны только цифры (буквы будут окрашены в фоновый цвет). Других путей не вижу. Макрос надо поместить в модуль листа, диапазон замените на свой, в котором расположены ячейки со списками:
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 1. Функция СУММЕСЛИ, когда одно условие
Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:
Жмем ОК и вводим ее аргументы:
- Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
- Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
- Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.
Способ 2. Функция СУММЕСЛИМН, когда условий много
Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:
При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться - см. следующие способы.
Способ 3. Столбец-индикатор
Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:
=(A2="Копейка")*(B2="Григорьев")
Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:
Способ 4. Волшебная формула массива
Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:
=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)
После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.
Способ 4. Функция баз данных БДСУММ
В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:
Работая с таблицами, часто приходится подбивать общие итоги по конкретному наименованию. В качестве данного наименования может выступать название контрагента, фамилия работника, номер подразделения, дата и т.д. Нередко эти наименования являются заглавием строк и поэтому, чтобы подсчитать общий итог по каждому элементу, приходится суммировать содержимое ячеек конкретной строки. Иногда сложение данных в строках производят и в иных целях. Давайте разберем различные способы, как это можно сделать в программе Excel.
Суммирование значений в строке
По большому счету, просуммировать в Экселе значения в строке можно тремя основными способами: использование арифметической формулы, применение функций и автосуммы. При этом, данные способы могут делиться ещё на ряд более конкретизированных вариантов.
Способ 1: арифметическая формула
Прежде всего, разберем, как при помощи арифметической формулы можно подсчитать сумму в строчке. Давайте посмотрим, как действует данный способ на конкретном примере.
Имеем таблицу, в которой указана выручка пяти магазинов по датам. Наименования магазинов являются названиями строк, а даты – названиями столбцов. Нам нужно рассчитать общую сумму выручки первого магазина за весь период. Для этого нам придется произвести сложение всех ячеек строчки, которая относится к данной торговой точке.
-
Выделяем ячейку, в которую будет выводиться готовый результат подсчета итога. Ставим туда знак «=». Кликаем левой кнопкой мыши по первой ячейке в данной строке, которая содержит числовые значения. Как видим, её адрес тут же отобразится в элементе для вывода суммы. Ставим знак «+». Затем кликаем по следующей ячейке в строке. Таким способом чередуем знак «+» с адресами ячеек строчки, которая относится к первому магазину.
В итоге в конкретно нашем случае получается следующая формула:
Как видим, данный способ довольно простой и интуитивно понятный, но у него имеется один существенный недостаток. На его осуществление нужно потратить много времени, если сравнивать с теми вариантами, которые мы рассмотрим ниже. А если в таблице очень много столбцов, то временные затраты увеличатся ещё больше.
Способ 2: автосумма
Гораздо более быстрым способом сложить данные в строчке является применение автосуммы.
-
Выделяем все ячейки с числовыми значениями первой строчки. Выделение проводим, зажав левую кнопку мыши. Перейдя во вкладку «Главная», жмем на значок «Автосумма», который расположен на ленте в блоке инструментов «Редактирование».
Другим вариантом вызова автосуммы является переход во вкладку «Формулы». Там в блоке инструментов «Библиотека функций» на ленте следует щелкнуть по кнопке «Автосумма».
Как видим, данный способ позволяет произвести подсчет суммы в строке гораздо быстрее, чем предыдущий вариант. Но у него тоже имеется недостаток. Он состоит в том, что сумма будет выводиться только справа от выделенного горизонтального диапазона, а не в том месте, где захочет пользователь.
Способ 3: функция СУММ
Преодолеть недостатки двух вышеописанных способов может вариант с применением встроенной функции Excel под названием СУММ.
Оператор СУММ принадлежит к группе математических функций Эксель. Его задачей является суммирование чисел. Синтаксис этой функции имеет такой вид:
Как видим, аргументами этого оператора являются числа или адреса ячеек, в которых они располагаются. Их количество может быть до 255.
Посмотрим, как можно просуммировать элементы в строке при помощи данного оператора на примере нашей таблицы.
-
Выделяем любую пустую ячейку на листе, куда предполагаем выводить итог вычисления. При желании можно её выбрать даже на другом листе книги. Но подобное бывает все-таки редко, так как в большинстве случаев более удобно традиционно располагать ячейку для вывода итогов в той же строке, в которой находятся расчетные данные. После того, как выделение произведено, щелкаем по значку «Вставить функцию» слева от строки формул.
Как видим, данный способ довольно гибкий и относительно быстрый. Правда, не для всех пользователей он интуитивно понятен. Поэтому те из них, которые не знают о его существовании из различных источников, редко когда находят его в интерфейсе Excel самостоятельно.
Способ 4: массовое суммирование значений в строках
Но что делать, если нужно просуммировать не одну и не две строчки, а, скажем 10, 100 или даже 1000? Неужели для каждой строки требуется в отдельности применять вышеописанные действия? Как оказывается, совсем не обязательно. Для этого нужно просто скопировать формулу суммирования в другие ячейки, в которые вы планируете выводить сумму по остальным строчкам. Сделать это можно при помощи инструмента, который носит наименование маркера заполнения.
-
Производим сложение значений в первой строке таблицы любым из тех способов, которые были описаны ранее. Ставим курсор в нижний правый угол ячейки, в которой отображается результат применяемой формулы или функции. При этом курсор должен изменить свой внешний вид и преобразоваться в маркер заполнения, который выглядит, как небольшой крестик. Затем зажимаем левую кнопку мыши и перетаскиваем курсор вниз, параллельно ячейкам с наименованиями строк.
Как видим, в Экселе существует три основных способа подсчитать сумму значений в строчках: арифметическая формула, автосумма и функция СУММ. Каждый из данных вариантов имеет свои достоинства и недостатки. Наиболее интуитивно простой способ – это применение формулы, самый быстрый вариант – автосумма, а самый универсальный – использование оператора СУММ. Кроме того, с помощью маркера заполнения можно проводить массовое суммирование значений по строкам, выполненное одним из трех способов, которые были перечислены выше.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Читайте также: