|
avsm  Пользователь Сообщений: 21 |
Здравствуйте, вот такой пример |
|
ber$erk  Пользователь Сообщений: 2735 |
в одной ячейке используете формулу СУММ, а во второй ячейке формула будет посложнее Учимся сами и помогаем другим… |
|
Ivan.kh  Пользователь Сообщений: 2024 |
=СУММ(ЕСЛИОШИБКА(—(ПСТР(A1;СТРОКА($1:$999);1));0)) Массивная, А1 — ячейка со значениями 2 3 1в 1 4в 4 |
|
Владимир Пользователь Сообщений: 8196 |
Если значения в а1:а7, то формула массива: =СУММ(ЕСЛИ(ЕТЕКСТ(A1:A7);МУМНОЖ(Ч(ЕСЛИОШИБКА(ПСТР(A1:A7;СТОЛБЕЦ(A1:J1);1)*СТРОКА(A1:A7)^0;));СТРОКА(1:10)^0))) «..Сладку ягоду рвали вместе, горьку ягоду я одна.» |
|
ber$erk Пользователь Сообщений: 2735 |
Согласно ТЗ эти значения находятся в разных ячейках Учимся сами и помогаем другим… |
|
ber$erk Пользователь Сообщений: 2735 |
#6 25.12.2014 08:27:51 моя формула массива для второго случая если ячейки в ряд:
все согласно примера Изменено: ber$erk — 25.12.2014 08:28:34 Учимся сами и помогаем другим… |
||
|
avsm Пользователь Сообщений: 21 |
Ivan.kh
, Владимир по вашим формулам 0 получается ber$erk по вашей #ЗНАЧ! |
|
Ivan.kh Пользователь Сообщений: 2024 |
avsm, а Вы ее как вводили? Изменено: Ivan.kh — 25.12.2014 08:36:56 |
|
ber$erk Пользователь Сообщений: 2735 |
и вы конечно же знаете как заполняется формула массива Учимся сами и помогаем другим… |
|
avsm Пользователь Сообщений: 21 |
вот пример, нужно чтобы в ячейке H1 была сумма цифр, которые с буквой в, в данном случае должно получиться 5 Прикрепленные файлы
|
|
ber$erk Пользователь Сообщений: 2735 |
сразу надо было начинать с этого примера. Тогда бы и ответ пришел раньше и люди бы не тратили свое время. Учимся сами и помогаем другим… |
|
Ivan.kh Пользователь Сообщений: 2024 |
=СУММ(НЕ(ЕОШ(НАЙТИ(«в»;A1:F1)))*ПОДСТАВИТЬ(A1:F1;»в»;»»)) |
|
ber$erk Пользователь Сообщений: 2735 |
#13 25.12.2014 09:11:28 сократил свою формулу — были скобки лишние
Учимся сами и помогаем другим… |
||
|
Ivan.kh Пользователь Сообщений: 2024 |
ber$erk, что-то я тупонул, у Вас действительно проще. Но можно и без массивного ввода обойтись: |
|
ber$erk Пользователь Сообщений: 2735 |
ну это же длиннее получается )) Учимся сами и помогаем другим… |
|
avsm Пользователь Сообщений: 21 |
всем спасибо за помощь ! ) |
|
Johny  Пользователь Сообщений: 2737 |
#17 25.12.2014 10:58:08 Выражусь регулярно.
Прикрепленные файлы
There is no knowledge that is not power |
||
|
avsm Пользователь Сообщений: 21 |
в общем если одна из ячеек окажется пустой, то ошибка #ЗНАЧ! |
|
Johny Пользователь Сообщений: 2737 |
avsm, Мой файл открывали? There is no knowledge that is not power |
|
avsm Пользователь Сообщений: 21 |
Johny,да, но у вас в примере значения в одной ячейке,а нужно чтобы в одной ячейке по значению было |
|
ber$erk Пользователь Сообщений: 2735 |
#21 25.12.2014 12:28:30 тогда только формулой массива
Учимся сами и помогаем другим… |
||
|
avsm Пользователь Сообщений: 21 |
ругается на =СУММ(—ПОДСТАВИТЬ(ЕСЛИ(A1:G1=»»;0;A1:G1);»в»;»»))-СУММ(ЕСЛИ(A1:G1=»»;0;A1:G1)) |
|
ber$erk Пользователь Сообщений: 2735 |
а я вставил одну ячейку с пустотой Изменено: ber$erk — 25.12.2014 12:42:43 Учимся сами и помогаем другим… |
|
vikttur Пользователь Сообщений: 47199 |
Формулы массива. |
|
Ivan.kh Пользователь Сообщений: 2024 |
До кучи |
|
avsm Пользователь Сообщений: 21 |
сумма чисел с буквой просто =СУММ(A1:F1) |
|
vikttur Пользователь Сообщений: 47199 |
avsm
, я понял , что и с буквой, и без буквы — все в одной ячейке. И таких ячеек несколько. Формулы показал именно для такого случая. |
|
avsm Пользователь Сообщений: 21 |
vikttur, ругается на диапазон A1:F1 =СУММ(ЕСЛИ(ДЛСТР(A1:F1)=2;—ЛЕВСИМВ(A1:F1))) |
|
vikttur Пользователь Сообщений: 47199 |
У меня не ругается. Родная потому что? |
|
Ivan.kh Пользователь Сообщений: 2024 |
#30 25.12.2014 13:44:31 avsm, введите как формулу массива Прикрепленные файлы
Изменено: Ivan.kh — 25.12.2014 13:45:47 |
|
Как посчитать сумму ячеек где буквы и цифры |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
You can use the following basic formula to sum cells in Excel that contain both numbers and text:
=SUM(SUBSTITUTE(B2:B8, "some_text", "")+0)
This particular formula removes the text string “some_text” from each cell in the range B2:B8 and then calculates the sum of the values in the range B2:B8.
The following examples show how to use this formula in practice.
Example 1: Calculate Sum of Cells with Text and Numbers
Suppose we have the following dataset that shows the total number of sales at seven different stores:
To calculate the sum of sales, we can type the following formula into cell B10:
=SUM(SUBSTITUTE(B2:B8, " items", "")+0)
Once we press Enter, the sum of the items will be shown:
The sum of the items sold is 97.
This formula simply substituted a blank where “items” used to be in each cell and then calculated the sum of the values remaining in the cells.
Example 2: Calculate Sum of Cells with Different Text and Numbers
Suppose we have the following dataset that shows the total number of sales at seven different stores:
To calculate the sum of sales, we can type the following formula into cell B10:
=SUM(SUBSTITUTE(SUBSTITUTE(B2:B8, " items", ""), "things", "")+0)
Once we press Enter, the sum of the values in column B will be shown:
The sum of the items sold is 97.
This formula simply substituted a blank where “items” and “things” used to be in each cell and then calculated the sum of the values remaining in the cells.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Replace Blank Cells with Zero in Excel
How to Replace #N/A Values in Excel
How to Sum If Cells Contain Text in Excel
17 авг. 2022 г.
читать 2 мин
Вы можете использовать следующую базовую формулу для суммирования ячеек в Excel, содержащих как числа, так и текст:
=SUM(SUBSTITUTE( B2:B8 , "some_text", "")+0)
Эта конкретная формула удаляет текстовую строку «some_text» из каждой ячейки в диапазоне B2:B8 , а затем вычисляет сумму значений в диапазоне B2:B8 .
Следующие примеры показывают, как использовать эту формулу на практике.
Пример 1: вычислить сумму ячеек с текстом и числами
Предположим, у нас есть следующий набор данных, который показывает общее количество продаж в семи разных магазинах:
Чтобы рассчитать сумму продаж, мы можем ввести следующую формулу в ячейку B10 :
=SUM(SUBSTITUTE( B2:B8 , " items", "")+0)
Как только мы нажмем Enter , будет показана сумма элементов:
Сумма проданных товаров равна 97 .
Эта формула просто заменяла пробел вместо «элементов» в каждой ячейке, а затем вычисляла сумму значений, оставшихся в ячейках.
Пример 2: вычислить сумму ячеек с разным текстом и числами
Предположим, у нас есть следующий набор данных, который показывает общее количество продаж в семи разных магазинах:
Чтобы рассчитать сумму продаж, мы можем ввести следующую формулу в ячейку B10 :
=SUM(SUBSTITUTE(SUBSTITUTE( B2:B8 , " items", ""), "things", "")+0)
Как только мы нажмем Enter , будет показана сумма значений в столбце B:
Сумма проданных товаров равна 97 .
Эта формула просто подставляла пробел вместо «предметов» и «вещей» в каждой ячейке, а затем вычисляла сумму значений, оставшихся в ячейках.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как заменить пустые ячейки нулем в Excel
Как заменить значения #N/A в Excel
Как суммировать, если ячейки содержат текст в Excel
Как в excel суммировать ячейки с цифрой и текстом?
т.е.
в ячейке G2 должна получиться сумма всех ячеек строки 2 где есть буква «л»,
а в ячейке H2 сумма всех ячеек строки 2 где есть буква «п»
пример на скрине
-
Вопрос заданболее трёх лет назад
-
9424 просмотра
Для буквы «л» формула такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(«л»;B4:G4);—ПОДСТАВИТЬ(B4:G4;»л»;»»));0))
Для «п» соответственно такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(«п»;B4:G4);—ПОДСТАВИТЬ(B4:G4;»п»;»»));0))
Обе — формулы массива. Вводятся одновременным нажатием клавиш Ctrl+Shift+Enter
Пригласить эксперта
-
Показать ещё
Загружается…
14 апр. 2023, в 04:52
5000 руб./за проект
14 апр. 2023, в 01:55
1000 руб./в час
13 апр. 2023, в 23:50
3000 руб./за проект
Минуточку внимания
Например, у меня есть список значений, содержащих числовые и текстовые строки, теперь я хочу суммировать только числа на основе одного и того же текста, посмотрите на следующий снимок экрана. Обычно вы не можете напрямую суммировать значения в списке с помощью текстовой строки, здесь я представлю вам несколько формул для решения этой задачи.
Суммировать ячейки, содержащие текст и числа на основе определенного текста с формулой
Вы можете использовать формулу массива для суммирования чисел на основе соответствующей текстовой строки в ячейке, сделайте следующее:
1. Сначала вы можете записать свои текстовые строки, в которых вы хотите просуммировать относительные числа в ячейках столбца.
2. Затем введите формулу ниже в пустой ячейке, в этом примере я введу формулу в ячейку E2, а затем нажмите Ctrl + Shift + Enter вместе, он вернет общее значение из списка на основе определенного текста. Смотрите скриншот:
=SUM(IF(ISNUMBER(FIND(D2,$B$2:$B$9)),VALUE(LEFT($B$2:$B$9,FIND(D2,$B$2:$B$9)-1)),0))
Внимание: В приведенной выше формуле D2 это критерий, который вы хотите использовать, и B2: B9 указывает диапазон ячеек, который вы хотите суммировать.
3. Затем выберите ячейку формулы и перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, чтобы получить результат:
Суммировать ячейки, содержащие текст и числа на основе определенного текста, с удобной функцией
Если вам сложно понять приведенную выше формулу, Kutools for Excel предоставляет полезный инструмент — Сумма на основе того же текста, с помощью этой функции вы можете получить общий результат в ячейке, смешанной с числами и текстом, не запоминая никаких формул.
Примечание:Чтобы применить эти Сумма на основе того же текста функции, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените функции.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Нажмите, чтобы выбрать ячейку, в которую вы хотите вывести результат, см. Снимок экрана:
Tips: Чтобы избежать циклических ссылок, не вставляйте эту формулу в первую строку рабочего листа.
2, Затем нажмите Кутулс > Формула Помощник > Формула Помощник, см. снимок экрана:
3. В Помощник по формулам диалоговом окне выполните следующие действия:
- Выберите Статистический из Тип формулы раскрывающийся список;
- В разделе Выберите формулу список, щелкните, чтобы выбрать Сумма на основе того же текста вариант;
- Затем в Ввод аргументов раздел, выберите диапазон ячеек, содержащий текст и числа, которые вы хотите суммировать в Диапазон текстовое поле, а затем выберите текстовую ячейку, значения которой вы хотите суммировать на основе Текст текстовое окно.
Tips: В Текст текстовое поле, вы должны изменить абсолютную ссылку на ячейку по умолчанию на относительную ссылку на ячейку для правильного перетаскивания формулы.
4. А затем нажмите Ok Кнопка, будет вычислен первый результат, затем перетащите маркер заполнения для заполнения формулы в другие ячейки, см. снимок экрана:
Суммировать ячейки, содержащие текст и числа, игнорируя текст, с помощью функции, определяемой пользователем
Если у вас есть следующие данные, смешанные с текстом и числами, и вы просто хотите извлечь и сложить только числа в каждой ячейке, как вы могли бы быстро закончить это в Excel ?.
Чтобы справиться с этой проблемой, вы можете создать Функция, определяемая пользователем, пожалуйста, сделайте так:
1. Удерживайте ALT + F11 , чтобы открыть Окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: суммируйте только числа в ячейке, содержащей числа и текст
Function SumNumbers(rngS As Range, Optional strDelim As String = " ") As Double
'Updateby Extendoffice
Dim xNums As Variant, lngNum As Long
xNums = Split(rngS, strDelim)
For lngNum = LBound(xNums) To UBound(xNums) Step 1
SumNumbers = SumNumbers + Val(xNums(lngNum))
Next lngNum
End Function
3. Затем сохраните и закройте этот код, вернитесь на рабочий лист и введите эту формулу = SumNumbers (A2) (A2 указывает ячейку, в которой вы хотите суммировать числа), затем перетащите маркер заполнения вниз к ячейкам, которые вы хотите заполнить формулой, и только числа в каждой ячейке добавляются вместе, см. снимок экрана:
Больше относительных статей:
- Объедините повторяющиеся строки и просуммируйте значения в Excel
- В Excel ， вы всегда можете столкнуться с этой проблемой, если у вас есть диапазон данных, содержащий несколько повторяющихся записей, и теперь вы хотите объединить повторяющиеся данные и суммировать соответствующие значения в другом столбце, как показано на следующих снимках экрана. Как бы вы могли решить эту проблему?
- Умножьте два столбца, а затем просуммируйте в Excel
- В Excel большинство из нас может страдать от умножения двух столбцов, а затем их сложения, конечно, мы можем умножить каждый элемент и затем суммировать их, но это будет проблематично, если нужно вычислить сотни или тысячи строк. В Excel есть мощная функция — СУММПРОИЗВ, с ее помощью мы можем быстро умножить два столбца и затем суммировать их. В этой статье я расскажу о том, как применить эту функцию.
- Суммируйте верхние 3 или N значений на основе критериев в Excel
- Обычно мы можем суммировать верхние n значений из диапазона ячеек, используя простую формулу, но пробовали ли вы когда-нибудь суммировать верхние n значений на основе некоторых критериев. В этой статье я представлю некоторые формулы для суммирования первых n значений с определенными критериями в Excel.
- Суммируйте уникальные значения на основе критериев в Excel
- Например, у меня есть диапазон данных, который содержит столбцы Name и Order, теперь для суммирования только уникальных значений в столбце Order на основе столбца Name, как показано на следующем снимке экрана. Как быстро и легко решить эту задачу в Excel?
- Подсчет или суммирование ячеек на основе цветов шрифта в Excel
- Как можно узнать количество ячеек или суммировать все ячейки, содержащие шрифт определенного цвета в Excel? Например, у меня есть диапазон данных на листе, как показано на следующем снимке экрана, и теперь я хочу соответственно подсчитать или суммировать ячейки, которые имеют красный, синий и черный цвет шрифта. Обычно нет прямого способа справиться с этой задачей, здесь я расскажу о некоторых хитростях для решения этой задачи.
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
