Как суммировать текст в ячейках в excel
помогите найти сумму (количество) ячеек с текстом и сумму ячеек рядом с которой находится текст.
во вложенной файле пример.
Заранее благодарен.
помогите найти сумму (количество) ячеек с текстом и сумму ячеек рядом с которой находится текст.
во вложенной файле пример.
Заранее благодарен. 122334boss
Формулы массива вводятся одновременным нажатием Ctrl/Shift/Enter. Автор - ShAM
Дата добавления - 16.02.2014 в 06:19
AlexM,
можно вопрос для всеобщего развития.
а если нужно все тоже самое сделать, но только например по определенным текстовым значением напр. по фамилии Иванов
AlexM,
можно вопрос для всеобщего развития.
а если нужно все тоже самое сделать, но только например по определенным текстовым значением напр. по фамилии Иванов 122334boss
Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
С ЕСЛИ() понятней (по-моему) и без вопроса, что такое "--" Автор - ShAM
Дата добавления - 16.02.2014 в 15:37
AlexM,
подскажите пожалуйста по этой теме, как можно применить решение (пример прикрепил).
заранее благодарен.
AlexM,
подскажите пожалуйста по этой теме, как можно применить решение (пример прикрепил).
заранее благодарен. 122334boss
122334boss
Чуть выше уважаемый vikttur показал Вам простейшие формулы.
В данном случае подходит СУММЕСЛИ. Просто "*" заменяете на нужное Вам - "Иванов", "Петров", "Сидоров" и т.д.
122334boss
Чуть выше уважаемый vikttur показал Вам простейшие формулы.
В данном случае подходит СУММЕСЛИ. Просто "*" заменяете на нужное Вам - "Иванов", "Петров", "Сидоров" и т.д. ArkaIIIa
ArkaIIIa,
я так тоже знаю, но особенность заключается в том, что нужно фамилии автоматически сформировать в столбик без повторения
ArkaIIIa,
я так тоже знаю, но особенность заключается в том, что нужно фамилии автоматически сформировать в столбик без повторения 122334boss
Pelena
Елена, а Вы не могли бы написать формулу для отображения фамилий без повтора? Просто для общего развития посмотреть, как конструкция будет выглядеть.
Pelena
Елена, а Вы не могли бы написать формулу для отображения фамилий без повтора? Просто для общего развития посмотреть, как конструкция будет выглядеть. ArkaIIIa
а вкладка данные-удалить дубликаты.а потом СУММЕСЛИ не катит?а вообще то в правилах написано:" один вопрос-одна тема "
а вкладка данные-удалить дубликаты.а потом СУММЕСЛИ не катит?а вообще то в правилах написано:" один вопрос-одна тема " китин
Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
а вкладка данные-удалить дубликаты.а потом СУММЕСЛИ не катит?а вообще то в правилах написано:" один вопрос-одна тема " Автор - китин
Дата добавления - 04.07.2014 в 15:30
Как суммировать ячейки с текстом и числами в 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 указывает ячейку, в которой вы хотите суммировать числа), затем перетащите маркер заполнения вниз к ячейкам, которые вы хотите заполнить формулой, и только числа в каждой ячейке добавляются вместе, см. снимок экрана:
Для суммирования значений по одному диапазону на основе данных другого диапазона используется функция СУММЕСЛИ() . Рассмотрим случай, когда критерий применяется к диапазону содержащему текстовые значения.
Пусть дана таблица с перечнем наименований фруктов и их количеством (см. файл примера ).
Если в качестве диапазона, к которому применяется критерий, выступает диапазон с текстовыми значениями, то можно рассмотреть несколько типов задач суммирования:
- суммирование значений, если соответствующие им ячейки в диапазоне поиска соответствуют критерию (простейший случай);
- в критерии применяются подстановочные знаки (*, ?) ;
- критерий сравнивается со значениями в диапазоне поиска с учетом РЕгиСтРА .
Рассмотрим эти задачи подробнее.
Значение соответствует критерию
Найдем количество всех значений "Яблоки" , т.е. просуммируем значения из столбца Количество, для которых соответствующее значение из столбца Фрукты в точности равно "Яблоки" (без учета РЕГИСТРА) .
Для подсчета используем формулу =СУММЕСЛИ(A3:A13;"яблоки";B3:B13)
Критерий яблоки можно поместить в ячейку D 5 , тогда формулу можно переписать следующим образом: =СУММЕСЛИ(A3:A13;D5;B3 :B13 )
В качестве диапазона суммирования можно указать лишь первую ячейку диапазона - функция СУММЕСЛИ() просуммирует все правильно: =СУММЕСЛИ(A3:A13; D5 ;B3)
В критерии применяются подстановочные знаки (*, ?)
Просуммируем значения из столбца Количество, для которых соответствующее значение из столбца Фрукты содержит слово Яблоки (без учета РЕгиСТРА) .
Для решения этой задачи используем подстановочные знаки (*, ?) . Подход заключается в том, что для отбора текстовых значений в качестве критерия задается лишь часть текстовой строки. Например, для отбора всех ячеек, содержащих слова яблоки ( свежие яблоки , яблоки местные и пр.) можно использовать критерии с подстановочным знаком * (звездочка). Для этого нужно использовать конструкцию * яблоки* .
Решение задачи выглядит следующим образом (учитываются значения содержащие слово яблоки в любом месте в диапазоне поиска): =СУММЕСЛИ($A$3:$A$13;"*яблоки*";B3)
Альтернативный вариант без использования подстановочных знаков выглядит более сложно: =СУММПРОИЗВ(B3:B13*НЕ(ЕОШ(ПОИСК("яблоки";A3:A13))))
Примеры, приведенные ниже, иллюстрируют другие применения подстановочных знаков.
Задача . Просуммировать значения, если соответствующие ячейки:
Задача
Критерий
Формула
Результат
Примечание
заканчиваются на слово яблоки , например, Свежие яблоки
11
Использован подстановочный знак * (перед значением)
начинаются на слово яблоки , например, яблоки местные
20
Использован подстановочный знак * (после значения)
начинаются с гру и содержат ровно 6 букв
= СУММЕСЛИ($A$3:$A$13; "гру. ";B3)
56
Использован подстановочный знак ?
Критерий сравнивается со значениями в диапазоне поиска с учетом РЕгиСТРА
Учет РЕгиСТра приводит к необходимости создания более сложных формул. Чаще всего используются формулы на основе функций НАЙТИ() и СОВПАД() учитывающих регистр.
Ниже приведены формулы для суммирования чисел, если соответствующие значения совпадают с критерием с учетом регистра.
Просуммировать значения, если соответствующие ячейки:
Критерий
Формула
Результат
Примечание
в точности равны Яблоки с учетом регистра
содержат значение Яблоки в любом месте текстовой строки с учетом регистра
= СУММ(ЕСЛИ( СОВПАД("Яблоки";A3:A13);1;0) *B3:B13)
СОВЕТ: Для сложения с несколькими критериями воспользуйтесь статьей Функция СУММЕСЛИМН() Сложение с несколькими критериями в MS EXCEL (Часть 2.Условие И) .
В статье Сложение по условию (один Числовой критерий) рассмотрен случай, когда критерий применяется к числовым значениям из диапазона, по которому производится суммирование.
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 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) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:
пример на скрине
- Вопрос задан более трёх лет назад
- 7791 просмотр
Простой 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, думаю, тут не обойтись без макроса, который будет красить букву в цвет фона ячейки при выборе какого-то значения из списка. Таким образом, в списке будут цифры с буквами, а в ячейке видны только цифры (буквы будут окрашены в фоновый цвет). Других путей не вижу. Макрос надо поместить в модуль листа, диапазон замените на свой, в котором расположены ячейки со списками:
Читайте также: