Note: The other languages of the website are Google-translated. Back to English

Як підсумувати комірки з текстом і цифрами в Excel?

Наприклад, у мене є список значень, що містять числові та текстові рядки, тепер я хочу підсумувати лише цифри на основі того самого тексту, подивіться на наступний знімок екрана. Зазвичай ви не можете безпосередньо підсумовувати значення у списку за допомогою текстового рядка, тут я представив вам деякі формули для вирішення цього завдання.


Суми комірок, що містять текст і цифри, на основі певного тексту з формулою

Ви можете використовувати формулу масиву для підсумовування чисел на основі відповідного текстового рядка в комірці, будь-ласка, зробіть наступне:

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 для Excel надає корисний інструмент - Сума на основі того самого тексту, за допомогою цієї функції ви можете отримати загальний результат у комірці, яка змішана з цифрами та текстом, не запам'ятовуючи жодних формул.

Примітка:Застосувати ці Сума на основі того самого тексту По-перше, ви повинні завантажити Kutools для Excel, а потім швидко та легко застосувати функції.

після установки Kutools для Excel, будь ласка, виконайте наступне:

1. Клацніть, щоб вибрати клітинку, куди потрібно вивести результат, див. Знімок екрана:

чайові: Щоб запобігти круговим посиланням, не вставляйте цю формулу в перший рядок робочого аркуша.

2. А потім клацніть Кутулс > Помічник формули > Помічник формули, див. скріншот:

3, в Помічник формул діалоговому вікні, виконайте такі дії:

  • вибрати Статистичний від Тип формули випадаючий список;
  • В Виберіть формулу списку, натисніть, щоб вибрати Сума на основі того самого тексту варіант;
  • Потім у Введення аргументів розділ, виберіть діапазон комірок, що містять текст і числа, які потрібно підсумувати в Діапазон текстове поле, а потім виберіть текстову комірку, для якої потрібно підсумувати значення на основі в текст текстове вікно.
чайові: В текст текстовому полі, вам слід змінити абсолютне посилання на клітинку за замовчуванням на відносне посилання на комірку для правильного перетягування формули.

4. А потім натисніть Ok , буде розраховано перший результат, а потім перетягніть маркер заповнення для заповнення формули в інші комірки, див. знімок екрана:


Суми комірок, що містять текст і числа, ігноруючи текст, за допомогою функції, визначеної користувачем

Якщо у вас є наступні дані, змішані з текстом і цифрами, і ви просто хочете витягти та скласти лише цифри в кожній комірці, як би ви могли швидко закінчити це в Excel ?.

Для вирішення цієї проблеми ви можете створити файл Визначена користувачем функція, будь ласка, зробіть так:

1. Утримуйте клавішу ALT + F11 ключі, щоб відкрити Вікно Microsoft Visual Basic для програм.

2. Клацання Insert > Модуліта вставте наступний код у Вікно модуля.

Код 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 існує потужна функція - SUMPRODUCT, за допомогою якої ми можемо швидко помножити два стовпці, а потім підсумувати їх. У цій статті я розповім про те, як застосувати цю функцію.
  • Сума 3-х чи N значень на основі критеріїв у Excel
  • Зазвичай ми можемо підсумувати верхні n значень з діапазону комірок за допомогою простої формули, але чи намагалися ви коли-небудь підсумувати верхні n значень на основі деяких критеріїв. У цій статті я представив деякі формули для підсумовування верхніх n значень з певними критеріями в Excel.
  • Сума унікальних значень на основі критеріїв у Excel
  • Наприклад, у мене є діапазон даних, який містить стовпці Ім'я та Порядок, тепер для підсумовування лише унікальних значень у стовпці Замовлення на основі стовпця Ім'я, як показано на наступному знімку екрана. Як швидко та легко вирішити це завдання в Excel?
  • Підрахувати або підсумувати клітинки на основі кольорів шрифтів в Excel
  • Як можна отримати кількість комірок або підсумувати всі комірки, які містять певний колір шрифту в Excel? Наприклад, у мене є ряд даних на аркуші, як показано на наступному знімку екрана, і тепер я хочу підрахувати або підсумувати комірки, які мають червоний, синій та чорний кольори шрифту. Як правило, прямого способу вирішити це завдання немає, тут я розповім про деякі хитрощі для розв’язання цієї роботи.

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (42)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Я думаю, що ти справжній учитель і хороша людина. Щасти.
Цей коментар був мінімізований модератором на сайті
У клітинку A1 Excel я вводжу дані, наприклад 5+6+3+1-2, а клітинка A2 Excel дає результат 13, як це можливо в excel.
Цей коментар був мінімізований модератором на сайті
добре, я зрозумів це. Щасти.
Цей коментар був мінімізований модератором на сайті
Макрос SumNumbers заощадив мені багато годин ручної роботи. Дякую!
Цей коментар був мінімізований модератором на сайті
Чи можна змінити формулу SumNumbers, щоб додати всі числа в стовпці змішаних клітинок? Наприклад, у кожному стовпці є 1A, 2B, 3C, і я хотів би, щоб формула в нижній частині стовпця вказувала мені 6.
Цей коментар був мінімізований модератором на сайті
чи є спосіб переписати код vba, щоб підсумувати кілька клітинок замість однієї? У мене є близько 8 стовпців даних, і я хочу, щоб сума в кінці.
Цей коментар був мінімізований модератором на сайті
Це дуже корисно! Я намагаюся змусити ARRAY працювати зі стовпцем комірок, кожна з яких містить десяткове число та текст (наприклад, .2 A; .5 C; .1 X тощо), і я постійно отримую результат 0. Якщо я змінити числа на цілі числа, результат правильний. Допоможіть?
Цей коментар був мінімізований модератором на сайті
GENUIS... Дуже дякую
Цей коментар був мінімізований модератором на сайті
Як конвертувати між текстом і числом за допомогою KUTOOls?
Цей коментар був мінімізований модератором на сайті
Я зміг ідеально використати цю формулу, але я також хотів би додати умову для включення певних чисел у формулу SUM, якщо значення в іншому стовпці = "дефекти". Моя вихідна формула (з використанням вашої оригінальної публікації) є: =SUM(IF(ISNUMBER(FIND("hrs",E17:E38)),VALUE(LEFT(E17:E38,FIND("hrs",E17:E38)-1 )),0)) Де E17:E38 містить текст і числа, які я хочу підсумувати, а B17:B38 містить значення "характеристики" та "дефекти" Як я можу налаштувати формулу, щоб підсумувати лише ці числа в стовпці E, якщо значення в стовпці B = "дефекти"? Спасибі заздалегідь! Кімберлі,
Цей коментар був мінімізований модератором на сайті
Ось простий сценарій, який приймає одновимірний і двовимірний діапазон вибору комірок, причому кожна клітинка містить рядок або числа, але кожна клітинка зменшується до окремих чисел і повертає суму всіх комірок. Опція Явна функція SUMCELLS(InputRange As Range) As Double Dim Arr() As Variant Arr = InputRange Dim R As Long Dim C As Long SUMCELLS = 2 Для R = 0 To UBound(Arr, 1) ' Перший вимір масиву — це рядки. Для C = 1 To UBound(Arr, 1) ' Другим виміром масиву є стовпці. Debug.Print Arr(R, ​​C) Dim NumericValue As Double Dim StringValue As StringValue = Trim(CStr(Arr(R, ​​C))) Якщо (Not (StringValue = "")), Тоді NumericValue = ONLYDIGITS(StringValue) SUMCELLS = SUMCELLS + NumericValue End If Next C Next R End Функція ONLYDIGITS(s As String) As String Dim retval As String Dim i As Integer Dim periods As Integer periods = 2 retval = "" ' Для i = 0 To Len(s) Dim char Як рядок char = Середина (s, i, 1) Якщо (char >= "1" І char
Цей коментар був мінімізований модератором на сайті
Як включити десяткові дроби до формули "=SumNumbers(A1)"? Дякую!
Цей коментар був мінімізований модератором на сайті
Хтось може допомогти мені з формулою суми? Я намагаюся підсумувати стовпець із клітинками, які містять як числа, так і текст. Я хочу підсумувати числа зліва від текстового листа. зразок: 3P30 2P30 2P30 2P30 1P20 3P30 1P20 3P50 3P30 3P60
Цей коментар був мінімізований модератором на сайті
Привіт, =SUM(IF(ISNUMBER(FIND(D1,$B$2:$B$8)),VALUE(LEFT($B$2:$B$8,FIND(D1,$B$2:$B$8)-1) )),0)) - спрацювало чудово, хіба що мій текст праворуч. Я намагався змінити формулу, але вона не працює... я просто продовжую отримувати #VALUE! Хтось може допомогти?
Цей коментар був мінімізований модератором на сайті
Не забудьте натиснути Ctrl+Shift+Enter після зміни формули, а не просто Enter.
Цей коментар був мінімізований модератором на сайті
Як підвести суму вибраного слова в комірці в excel Exam 1100/656480 1100 – це сума, а 656480 – це recpt. ні. але я хочу всього лише 1100
Цей коментар був мінімізований модератором на сайті
Квітень травень червень липень Разом 1100 / 65658 1200/65657 1500/65656 2100/65650 ? я хочу всього лише 1100+1200+1500+2100 Інше, а не тільки вартість Квитанція №. будь ласка, допоможіть будь-якому
Цей коментар був мінімізований модератором на сайті
Привіт! У мене є два типи чисел в одному стовпці, один — числові числа, інший — символ, за яким слідує число (наприклад, n20, n30 тощо). Я хочу скласти ці числа з «n». Я застосував ваші вказівки щодо "Комірки суми, що містять текст і числа на основі певного тексту з формулою", але це не спрацювало. Чи є інший метод? Спасибі заздалегідь!
Цей коментар був мінімізований модератором на сайті
привіт, чи може хтось допомогти мені, як додати цифри з буквами? я намагався зробити так, але це працює, я думаю, що я щось пропустив у формулі=SUM(IF(ISNUMBER(FIND(D1,$B$2:$B$8)),VALUE(LEFT($B$2:$B$8, ЗНАЙТИ(D1,$B$2:$B$8)-1)),0)). будь ласка, допоможіть. Дякую.

2L
5D
10S
3L
11D
16S
Цей коментар був мінімізований модератором на сайті
Привіт, Анна,
Спочатку ви повинні витягти літери в інший допоміжний стовпець, а потім застосувати наведену вище формулу. Після введення цієї формули не забудьте натиснути разом клавіші Shift + Ctrl + Enter. дивіться наступне зображення екрана:
Цей коментар був мінімізований модератором на сайті
привіт,

Дякую за цей пост! Мені подобається використовувати код VBA, і я думав, чи можна було б мати багатозмінну функцію, щоб ви могли підсумовувати числа з різних клітинок стовпців і написати щось на кшталт SumNumbers(A1:C1), наприклад.


Велике спасибі


Джон
Цей коментар був мінімізований модератором на сайті
Привіт, Джованні,
Чи можете ви навести приклад вашої проблеми?
Ви можете прикріпити скріншот тут.
Дякую!
Цей коментар був мінімізований модератором на сайті
Я також шукаю, чи може ця функція підсумовувати кілька клітинок.
Цей коментар був мінімізований модератором на сайті
Цей коментар був мінімізований модератором на сайті
привіт,
Як помножити 10 шт *200,

не 10*200
There are no comments posted here yet
Load More
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0  Персонажі
Рекомендовані місця