Excel. Сложение чисел из текстовой строки

Сегодня рассмотрим задачу, как из текстовой строки в Excel выбрать все числа и сложить их.
Задача эта взята не из головы, а из насущной проблемы, о которой я вам расскажу в одной из ближайших статей.
Поставим условия для решения. Итоговая формула должна быть:

  • максимально компактной,
  • при возрастании количества извлекаемых чисел в строке, формула не должна увеличиваться и уж тем более не должна требовать ввода каких-то дублирующихся частей,
  • универсальной, то есть при соблюдении предыдущих двух условий быть применимой для строк любой длины, содержащих любое произвольное количество чисел.

Как всегда, гугл и яндекс на соответствующие запросы выдают десятки страниц мало полезной копипасты часто с довольно банальными примерами, которые мало чем отличаются от официальной справки в Excel. Поэтому беремся за дело самостоятельно.
Предположим, у нас есть набор текстовых строк, в которых присутствуют числа, которые необходимо сложить и получить общую сумму для каждой строки:

Эта картинка нам понадобится в дальнейшем, для определения положения вырезаемых чисел.
Начнем с самого простого, попробуем извлечь цифровое значение из первой ячейки (A2) с текстом.
Сразу вспоминаем про простейшую функцию ПСТР:
Вот ее код из данного примера:
=ПСТР(A2;12;6),
здесь 12 это порядковый номер символа, с которого, включительно, нам надо вырезать текст, а 6 длина вырезаемого участка.
Вроде бы не плохо? Ведь первое число мы извлекли. Однако, если с ним попробовать произвести какие-то математические операции, то у нас ничего не выйдет, так как, во-первых, в качестве разделителя группы десятичных разрядов у нас стоит точка, а не запятая (это мы исправим функцией ПОДСТАВИТЬ), а во-вторых, даже заменив точку на запятую, Excel продолжит воспринимать наше число как текст, ведь мы ее вырезали из текстовой строки, поэтому следующим шагом в нашей формуле должна стать конвертация текста, отображающего число, в непосредственно числовой формат при помощи функции ЗНАЧЕН. И так, применяем эти функции:
Код формулы:
=ЗНАЧЕН(ПОДСТАВИТЬ(ПСТР(A2;12;6);".";",";1))
Уже лучше — участок вырезанного нами текста мы превратили в полноценное число, с которым можно выполнять любые математические действия. Но не забываем о главной цели этой статьи — просуммировать все числа во всех строках.
Если пытаться решить эту задачу в лоб, то для второй текстовой ячейки (A3), мы должны написать аналогичную формулу, в которой задать расположение следующей цифры в строке, и полученные результаты сложить:
Код:
=СУММ(ЗНАЧЕН(ПОДСТАВИТЬ(ПСТР(A3;12;6);".";",";1));ЗНАЧЕН(ПОДСТАВИТЬ(ПСТР(A3;45;5);".";",";1)))
Уупс, что-то пошло не так… В первой ячейке у нас ошибка #ЗНАЧ! Ничего удивительного, ведь там отсутствует второе число для сложения. На такие случаи Excel приберег для нас функцию ЕСЛИОШИБКА, в которой можно задать аргумент «во что сконвертировать», а преобразовывать ошибку мы будем, как не сложно догадаться, в ноль, чтобы суммы были корректными. Смотрим, что из этого выйдет:
Формула:
=СУММ(ЕСЛИОШИБКА(ЗНАЧЕН(ПОДСТАВИТЬ(ПСТР(A3;12;6);".";",";1));0);ЕСЛИОШИБКА(ЗНАЧЕН(ПОДСТАВИТЬ(ПСТР(A3;45;5);".";",";1));0))
Вроде бы здорово, но формула стала еще больше, а мы ведь хотели универсальную и компактную формулу. Для сложения 4 цифр из одной текстовой строки она станет еще больше в два раза. А если цифр будет еще больше?! Печаль… Но на самом деле мы уже близки к цели. Т.к. по сути всю подготовительную работу уже выполнили, осталось только еще немного подумать.
И так, что мы имеем? Единообразные повторяющиеся части в формуле, которые отличаются только аргументами. Так, так, так… Конечно, эта задача отлично подходит для формул массива!
Попробуем ввести такую формулу:
{=СУММ(ЕСЛИОШИБКА(ЗНАЧЕН(ПОДСТАВИТЬ(ПСТР(A2;{12;45};{6;5});".";",";1));0))}
Не забываем, что формулы массива вводятся при помощи комбинации клавиш Ctrl+Shift+Enter, а фигурные скобки при этом появятся сами. Здесь {12;45} — массив, заданный константами первых символов вырезаемых чисел, а {6;5} — массив длин этих чисел. Кстати, для констант массива фигурные скобки как раз надо ввести руками, иначе Excel не воспримет их как массив.
Отлично, формула стала практически в два раза компактнее, не нужно дублировать большие куски, а достаточно только лишь задать начальные положения цифр и их длин, при этом формула будет оставаться компактной.
Соответственно, нам остается только добавить недостающие значения для двух последних текстовых строк:
{12;45;62;82} — начальные символы всех цифр,
{6;5;5;5} — длина всех цифр по порядку.
Получаем почти итоговую формулу:
{=СУММ(ЕСЛИОШИБКА(ЗНАЧЕН(ПОДСТАВИТЬ(ПСТР(A2;{12;45;62;82};{6;5;5;5});".";",";1));0))}
«Почти» итоговую, потому что конкретно для данного случая полезно вспомнить про функцию СУММПРОИЗВ, которая позволяет работать с массивами без ввода формулы с комбинацией Ctrl+Shift+Enter.
Полным эквивалентом той нашей формулы будет запись:
=СУММПРОИЗВ(ЕСЛИОШИБКА(ЗНАЧЕН(ПОДСТАВИТЬ(ПСТР(A2;{12;45;62;82};{6;5;5;5});".";",";1));0))
Как видите, здесь формула не заключена в фигурные скобки массива, но работает она абсолютно также, как и предыдущая.
Чтобы потом можно было быстро воспользоваться формулой, запишем ее, обозначив аргументы переменными:
=СУММПРОИЗВ(ЕСЛИОШИБКА(ЗНАЧЕН(ПОДСТАВИТЬ(ПСТР(X;{Y};{Z});".";",";1));0))
X — анализируемая строка
{Y} — массив из начальных позиций цифр в строке
{Z} — массив длин цифр в строке
И на последок небольшая гифка с процессом вычисления этой формулы:
MS Excel Img 9В этой статье мы рассмотрели сильно упрощенную ситуацию — все цифры всегда строго на своих местах, и имеют фиксированную длину. На практике же такое представить довольно сложно. Как обойти это столь большое ограничение в следующей статье. Оставайтесь на связи!

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *