Excel сумма ячеек с текстом
Объединение текста и чисел
Допустим, вам нужно создать грамматически правильное предложение из нескольких столбцов данных для подготовки массовой рассылки. Или, возможно, вам нужно форматировать числа с помощью текста, не влияя на формулы, в которых используются эти номера. В 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 в Интернете функция сцепить была заменена функцией concat . Несмотря на то, что функция сцепить по-прежнему доступна для обеспечения обратной совместимости, следует использовать функцию concat, так как функция сцепить может быть недоступна в будущих версиях Excel.
Объединить сочетает текст из нескольких диапазонов и (или) строк и включает разделитель, заданный между каждым текстовым значением, которое будет объединено. Если в качестве разделителя используется пустая текстовая строка, функция эффективно объединит диапазоны. Объединить недоступен в Excel 2013 и предыдущих версиях.
Примеры
Посмотрите различные примеры на рисунке ниже.
Внимательно посмотрите на использование функции текст во втором примере на рисунке. При присоединении номера к текстовой строке с помощью оператора конкатенации используйте функцию текст для управления способом отображения числа. Формула использует основное значение из ячейки, на которую указывает ссылка (4 в этом примере), а не отформатированного значения, которое вы видите в ячейке (40%). Чтобы восстановить форматирование чисел, используйте функцию текст .
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
См. также
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).
Функция СУММЕСЛИ в Excel с примерами
В предыдущей статье мы рассмотрели синтаксис функции СУММЕСЛИ в Excel, теперь давайте закрепим знания на практике при помощи ряда примеров формулы СУММЕСЛИ:
СУММЕСЛИ в Excel примеры с логическими операторами (больше, меньше или равно)
Давайте рассмотрим несколько примеров формул СУММЕСЛИ, которые вы можете использовать для суммирования значений для условий больше чем, меньше чем или равно заданному значению.
Примечание . Обратите внимание, что в формулах Excel СУММЕСЛИ оператор сравнения, за которым следует число или текст, всегда должен быть заключен в двойные кавычки («»).
Пример формулы СУММЕСЛИ
Сумма, если больше
Суммирует значения больше 5 в ячейках A2:A10.
Сумма, если меньше
Суммирует значения в ячейках B2:B10, если соответствующая ячейка в столбце A не равна значению в ячейке D1.
Сумма если больше или равно
Суммирует значения, которые больше или равны 5 в диапазоне A2:A10.
Сумма если меньше или равно
Обратите внимание , что вам понадобятся разные формулы СУММЕСЛИ для точного и частичного совпадения, как показано в таблице ниже.
Пример формулы СУММЕСЛИ
Сумма, если равно
=СУММЕСЛИ(A2:A8; «бананы»; C2:C8)
Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит точное слово «бананы» и никакие другие слова или символы. Ячейки, содержащие «зеленые бананы», «бананы зеленые» или «бананы!» не будут считаться.
=СУММЕСЛИ(A2:A8; «*бананы*»; C2:C8)
Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит слово «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «зеленые бананы» или «бананы зеленые», будут учитываться для суммирования.
Сумма, если не равно
=СУММЕСЛИ(A2:A8; «<>бананы»; C2:C8)
Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит любое значение, отличное от слова «бананы». Если ячейка содержит «бананы» вместе с некоторыми другими словами или символами, такими как «желтые бананы» или «бананы желтые», такие ячейки будут учитываться для суммирования.
=СУММЕСЛИ(A2:A8; «<>*бананы*»; C2:C8)
Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A не содержит слова «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «желтые бананы» или «бананы желтые», не суммируются.
Для получения дополнительной информации о частичном совпадении см. пункт СУММЕСЛИ примеры формул с подстановочными знаками.
А теперь, давайте посмотрим пример формулы «Сумма, если не равно» в действии. Как показано на изображении ниже, формула суммирует количество всех продуктов, кроме «Банана Дамский пальчик»:
=СУММЕСЛИ(A2:A8; «<>Банан Дамский пальчик»; C2:C8)
Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с проверкой на неравенство
Примечание . Как и большинство других функций Excel, СУММЕСЛИ нечувствительна к регистру, что означает, что «<> бананы», «<> Бананы» и «<> БАНАНЫ» будут давать точно такой же результат.
СУММЕСЛИ в Excel примеры операторов сравнения со ссылками на ячейки
Если вы хотите получить более универсальную формулу Excel СУММЕСЛИ, вы можете заменить числовое или текстовое значение в критериях ссылкой на ячейку, например:
В этом случае вам не придется менять формулу СУММЕСЛИ, основанную на другом критерии – вы просто вводите новое значение в ссылочной ячейке.
Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ, суммирование исключая значение в ячейке F1
Примечание. Когда вы используете логическое выражение с ссылкой на ячейку, вы должны использовать двойные кавычки («»), чтобы начать текстовую строку и амперсанд (&), чтобы объединить и завершить строку, например «<>» и F1.
Оператор «равенства» (=) можно не использовать до ссылки на ячейку, поэтому обе приведенные ниже формулы эквивалентны и правильны:
Формула 1: =СУММЕСЛИ(A2:A8; «=» & F1; C2:C8)
Формула 2: =СУММЕСЛИ(A2:A8; F1; C2:C8)
СУММЕСЛИ примеры формул с подстановочными знаками
Если вы намерены условно суммировать ячейки на основе «текстовых» критериев и хотите суммировать путем частичного совпадения, вам нужно использовать подстановочные знаки в формуле СУММЕСЛИ.
Доступны следующие подстановочные знаки:
Звездочка (*) — представляет любое количество символов
Знак вопроса (?) — представляет один символ в определенном месте
Пример 1. Суммирование значений, основанные на частичном совпадении
Предположим, вы хотите суммировать количество, относящиеся ко всем видам бананов. Следующие формулы СУММЕСЛИ будут очень эффективны в таких случаях:
=СУММЕСЛИ(A2:A8; «*бананы*»;C2:C8) — критерий включает текст, заключенный в звездочки (*).
=СУММЕСЛИ(A2:A8; «*»&F1&»*»; C2:C8) — критерий включает ссылку на ячейку, заключенную в звездочки, обратите внимание на использование амперсанда (&) до и после ссылки на ячейку для конкатенации строки.
Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с подстановочными знаками для суммирования по частичному совпадению
Если вы хотите считать только те ячейки, которые начинаются или заканчиваются определенным текстом, добавьте только один * до или после текста:
=СУММЕСЛИ(A2:A8; «бананы*»; C2:C8) — значения суммы в C2:C8, если соответствующая ячейка в столбце A начинается со слова «бананы».
=СУММЕСЛИ(A2:A8; «*бананы»; C2:C8) — значения суммы в C2:C8, если соответствующая ячейка в столбце A заканчивается словом «бананы».
Функция СУММЕСЛИ в Excel с примерами – Пример использования функции СУММЕСЛИ с текстовым условием
Пример 2. Суммирование по заданному количеству символов
Если вы хотите суммировать некоторые значения длиной в шесть букв, вы должны использовать следующую формулу:
Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с условием суммирования, если длина текстовой строки в шесть букв
Пример 3. Сумма ячеек, соответствующих текстовым значениям
Если ваш рабочий лист содержит разные типы данных, и вы хотите только суммировать ячейки, соответствующие текстовым значениям, пригодится следующая формула СУММЕСЛИ:
=СУММЕСЛИ(A2:A8; «?*»; C2:C8) – суммирует значения из ячеек C2:C8, если соответствующая ячейка в столбце A содержит не менее 1 символа.
=СУММЕСЛИ(A2:A8; «*»; C2:C8) – учитывает пустые ячейки, содержащие строки нулевой длины, возвращаемые некоторыми другими формулами, например =»».
Обе приведенные выше формулы игнорируют нетекстовые значения, такие как ошибки, логические значения, числа и даты.
Пример 4. Использование * или ? как обычные символы
Если вы хотите использовать либо *, либо ? для обработки в функции СУММЕСЛИ как литерала, а не подстановочного знака, то используйте перед этим знаком тильду (
). Например, следующая формула СУММЕСЛИ просуммирует значения в ячейках C2:C8, если ячейка в столбце A в той же строке содержит знак вопроса:
Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с суммированием значений, соответствующие знаку вопроса в другом столбце
СУММЕСЛИ в Excel примеры с датами
Как правило, функцию СУММЕСЛИ используют для условного суммирования значений на основе дат так же, как и с текстовыми и числовыми критериями.
Если вы хотите суммировать значения, соответствующие датам, которые больше или меньше указанной вами даты, используйте операторы сравнения, которые мы рассматривали выше. Ниже приведены примеры формул Excel СУММЕСЛИ с датами:
Пример формулы СУММЕСЛИ
Сумма по определенной дате
Суммирует значения в ячейках C2:C9, если соответствующая дата в столбце B равна 29.10.2017.
Сумма, если дата больше либо равна заданной в формуле дате
Суммирует значения в ячейках C2:C9, если соответствующая дата в столбце B больше или равна 29.10.2017.
Сумма, если дата больше даты, указанной в ячейке
Суммирует значения в ячейках C2:C9, если соответствующая дата в столбце B больше даты, указанной в ячейке F1.
Если вы хотите суммировать значения на основе текущей даты, вам необходимо использовать СУММЕСЛИ в сочетании с функцией СЕГОДНЯ(), как показано ниже:
Пример формулы СУММЕСЛИ
Суммирование значений, за текущую дату
=СУММЕСЛИ(B2:B9; СЕГОДНЯ (); C2:C9)
Суммирование значений, меньше текущей даты, то есть до сегодняшнего дня.
Суммирование значений за неделю от текущей даты. (т.е. сегодня + 7 дней).
Изображение ниже показывает, как вы можете использовать последнюю формулу, чтобы найти общее количество всех продуктов, которые будут отправлены через неделю:
Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с суммированием количества продуктов, которые будут отправлены через неделю
СУММЕСЛИ в заданном диапазоне дат
Если вам необходимо суммировать значения между двумя датами, то необходимо использовать комбинацию, а точнее разницу двух функций СУММЕСЛИ. В версиях старше Excel 2007 вы можете использовать функцию СУММЕСЛИМН, которая позволяет использовать несколько условий. Эту функцию мы рассмотрим в следующей статье. А так как данная статья посвящена функции СУММЕСЛИ, то приведем пример использования СУММЕСЛИ в диапазоне дат:
=СУММЕСЛИ(B2:B9; «>=01.11.2017»; C2:C9) — СУММЕСЛИ(B2:B9; «>=01.12.2017»; C2:C9)
Эта формула суммирует значения в ячейках C2:C9, если дата в столбце B находится между 1 ноября 2017 года и 30 ноября 2017, включительно.
Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ дата в диапазоне
Эта формула может показаться немного сложной с первого взгляда, но при более близком рассмотрении это выглядит довольно просто. Первая функция СУММЕСЛИ объединяет все ячейки в C2:C9, где соответствующая ячейка в столбце B больше или равна дате начала (в данном примере 1 ноября). Затем вам просто нужно вычесть значения, которые попадают после даты окончания (30 ноября), с помощью второй функции СУММЕСЛИ.
В данной статье мы разобрали множество примеров функции СУММЕСЛИ с разными условиями, такими как числовые, текстовые, даты и другие. В следующей статье мы рассмотрим функцию СУММЕСЛИМН, которая является аналогом функции СУММЕСЛИ с несколькими условиями.
Пример функции СУММЕСЛИ для суммирования в Excel по условию
Функция СУММЕСЛИ в Excel используется для расчета суммы числовых значений, содержащихся в диапазоне ячеек, с учетом критерия, указанного в качестве одного из аргументов, и возвращает соответствующее числовое значение. Данная функция является альтернативой совместного использования функций СУММ и ЕСЛИ. Ее использование позволяет упростить формулы, поскольку критерий, по которому производится суммирование значений, предусмотрен непосредственно в ее синтаксисе.
Примеры использования функции СУММЕСЛИ в Excel
Пример 1. В таблице Excel записаны члены геометрической прогрессии. Какую часть (в процентах) составляет сумма первых 15 членов прогрессии от общей суммы ее членов.
Вид исходной таблицы данных:
Выполним расчет с помощью следующей формулы:
- A3:A22 – диапазон ячеек, содержащих порядковые номера членов прогрессии, относительно которых задается критерий суммирования;
- » Пример 2. В таблице Excel указаны данные о работе двух продавцов небольшого магазина. Определить, какой из сотрудника принес больший доход за 19 рабочих дней).
Исходная таблица имеет следующий вид:
Для расчета используем функцию в формуле:
Функция ЕСЛИ выполняет проверку возвращаемых значений функциями СУММЕСЛИ с условиями проверки «Иванов» и «Петров» соответственно и возвращает текстовую строку с фамилией продавца, суммарная прибыль которого оказалась больше.
В итоге получим следующее значение:
Как в Excel суммировать ячейки только с определенным значением
Пример 3. В таблице указаны данные о зарплате сотрудника на протяжении 12 месяцев прошлого года. Рассчитать доходы работника за весенние месяцы.
Вид таблицы данных:
Весенними месяцами являются месяца с номерами 3, 4 и 5. Для расчета используем формулу:
Сумма зарплат с 6-го по 12-й месяц является подмножеством множества суммы зарплат с 3-го по 12-й месяц. Разница этих сумм является искомой величиной – суммой зарплат за весенние месяцы:
Функцию СУММЕСЛИ можно использовать если требуется определить сразу несколько значений для различных критериев. Например, для расчета суммы зарплат за три первых и три последних месяца в году соответственно составим следующую таблицу:
Для расчетов используем следующую формулу:
В результате получим:
Особенности использования функции СУММЕСЛИ в Excel
Функция СУММЕСЛИ имеет следующий синтаксис:
=СУММЕСЛИ( диапазон; условие; [диапазон_суммирования])
- диапазон – обязательный для заполнения аргумент, принимающий ссылку на диапазон ячеек с данными, в отношении которых применяется определенный критерий. В ячейках данного диапазона могут содержаться имена, текстовые строки, данные ссылочного типа, числовые значения, логические ИСТИНА или ЛОЖЬ, даты в формате Excel. Если данный диапазон также является диапазоном суммирования (третий аргумент опущен), на итоговый результат не влияют пустые ячейки и ячейки, содержащие текстовые данные.
- условие – обязательный для заполнения аргумент, который может быть указан в виде числа, текстовой строки, логического выражения, результата выполнения какой-либо функции. Переданное в качестве данного аргумента значение или выражение является критерием суммирования рассматриваемой функции.
- [диапазон_суммирования] – необязательный для заполнения аргумент, принимающий ссылку на диапазон ячеек, содержащих числовые значения, для которых будет рассчитана сумма с учетом критерия суммирования (условие).
СуммаЯчеек_Шрифт
Данная функция является частью надстройки MulTEx
|
Суммирование ячеек по цвету шрифта
Данная функция суммирует данные по цвету шрифта данных, записанных в ячейке. Многие любят выделять важные данные не только заливкой, но и шрифтом. И как раз, чтобы после этого можно было хоть как-то просуммировать подобную красоту и предназначена данная функция.
Вызов команды через стандартный диалог:
Мастер функций—Категория «MulTEx»— СуммаЯчеек_Шрифт
Вызов с панели MulTEx:
Сумма/Поиск/Функции — Математические — СуммаЯчеек_Шрифт
Синтаксис:
=СуммаЯчеек_Шрифт( $E$2:$E$20 ; I6 ; I6 ; $A$2:$A$20 )
ДиапазонСуммирования( $E$2:$E$20 ) — диапазон значений для суммирования. Можно указать несколько столбцов. Столбец с критерием(если планируется суммировать еще и по критерию) не обязательно должен входит в диапазон. Именно в этом диапазоне ищутся совпадения цвета шрифта с указанным.
ЯчейкаОбразец( I6 ) — ячейка-образец цвета шрифта. Ссылка на ячейку с цветом шрифта(ячейки именно с цветом шрифта будут просуммированы).
Критерий( I6 ) — необязательный аргумент. Если указан, то суммируются ячейки с указанным критерием и цветом шрифта. Допускается применение в критерии символов подстановки — «*» и «?». Если не указан, то суммируются все ячейки с указанным цветом шрифта. Если в диапазоне суммирования находятся ячейки с текстом, то они будут игнорироваться.
Так же данный аргумент может принимать в качестве критерия символы сравнения ( , =, <>, ):
- «>0» — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых больше нуля;
- «>=2» — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых больше или равно двум;
- » — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых меньше нуля;
- » — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых меньше или равно 60;
- «<>0″ — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых не равно нулю;
- «<>» — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых не пустые;
Вместо нуля может быть любое число или текст. Так же можно добавить ссылку на ячейку со значением: «<>«&D$1
ДиапазонКритерия( $A$2:$A$20 ) — Необязательный аргумент. Указывается диапазон, в котором следует искать критерий(если критерий указан). ДиапазонКритерия должен быть равен по количеству ячеек ДиапазонуСуммирования. Если ДиапазонКритерия не указан, то критерий просматривается в ДиапазонеСуммирования.
ИспУФ() — Необязательный аргумент. Допускается указание логических значений ИСТИНА(TRUE) или ЛОЖЬ(FALSE). По умолчанию принимает значение ИСТИНА. Если указан как ИСТИНА, то функция будет суммировать ячейки с учетом примененного к ним условного форматирования. Если указан как ЛОЖЬ, то функция будет суммировать ячейки без учета примененного условного форматирования, т.е. даже если условное форматирование применено и ячейка окрашена с его помощью, а реальный цвет заливки не соответствует цвету ЯчейкиОбразца — то её значение не будет суммироваться.
Важно: Функция не вычисляется при изменении цвета шрифта. Для пересчета функции после изменения параметров необходимо выделить ячейку и нажать F2—Enter. Либо нажать сочетания клавиш Shift+F9(пересчет функций активного листа) или клавишу F9(пересчет функций всей книги)
Примечание: данная функция будет корректно работать даже при примененном к ячейке Условном форматировании. Однако если в ячейке/диапазоне присутствуют условия, формат для которых задан при помощи шкал, градиентов, гистограмм и значков — функция может вернуть некорректный результат. Связано это с тем, что Excel не предоставляет доступ к данным типам УФ извне