Формула для суммы в EXCEL
Как посчитать сумму чисел в строке, если рядом с числом стоят символы, в данном случае "н/"?
Ответы (2 шт):
Ленивый метод в Office 365 - привлечение REGEXTRACT для вычленения чисел. Также понадобятся IFERROR для замены n/a на нули в ячейках без чисел и VALUE для преобразования строк в числа перед суммированием. Пример:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| н/2 | н/3 | н/6 | н/7 | =SUM(IFERROR(VALUE(REGEXEXTRACT(A1:E1, "[0-9]+", 0)), 0)) |
Эта же формула работает и в Google Spreadsheets, если поместить её в ARRAYFORMULA.
Чтобы было легче контролировать изменяемую область, можно привлечь LET:
=LET(rec, A1:E1, SUM(IFERROR(VALUE(REGEXEXTRACT(rec, "[0-9]+", 0)), 0)))
В старых же версиях основная трудность в том, что формулы для обработки строк работают со скалярами. Для обработки нескольких ячеек одновременно формулу нужно активизировать по окончании редактирования нажатием Ctrl+Shift+Enter. Также изменится набор доступных функций. В данном случае, чтобы обойтись без REGEXEXTRACT, достаточно отсечь первые два символа, если они равны заданной строке:
=SUM(VALUE(IF(LEFT(A1:E1, 2) = "н/", REPLACE(A1:E1, 1, 2, ""), A1:E1)))
В частности, этот подход приемлем в LibreOffice версии 24.
Предположим, что исходные данные расположены в ячейках A1:I1
Если под каждой ячейкой ниже разместить формулу
=ЕСЛИ(ДЛСТР(A1)>0; ПРАВСИМВ(A1;1)+0;0)
В результате, если длина строки больше нуля, формула возьмёт правый символ, прибавит 0, преобразовав таким образом формат значения в числовой.
Если длина строки нуль (ячейка пустая), то, чтобы избежать ошибки #ЗНАЧ!, формула пишет 0
И в конце строки, в ячейке J2 суммируем эту вспомогательную строку со значениями с использованием обычной функции =СУММ(A2:I2)