Для суммирования ячеек по одному условию предусмотрена функция
=СУММЕСЛИ(диапазон<wbr />;условие;[диапазон_су<wbr />ммирования])
- диапазон это ячейки входящие в условие
- условие это строка содержащая условие отбора, состоящее из логического оператора и значения, если указано только значение то это означает что значение соответствующей ячейки должно быть равно в точности. Можно использовать подстановочные знаки *(любые символы) и ?(один любой символ)
- диапазон_суммировани<wbr />я это ячейки которые должны суммироваться (указывается только если этот диапазон не совпадает с диапазоном для вычисления условия)
Для суммирования ячеек по многим условиям предусмотрена функция
=СУММЕСЛИ(диапазон<wbr />_суммирования;диапазо<wbr />н1;условие1;диапазон2<wbr />;условие2;....)
- диапазон1,диапазон2 .... это ячейки входящие в условие
- условие1, условие2 ... это строка содержащая условие отбора, состоящее из логического оператора и значения, если указано только значение то это означает что значение соответствующей ячейки должно быть равно в точности. Можно использовать подстановочные знаки *(любые символы) и ? (один любой символ)
- диапазон_суммировани<wbr />я это ячейки которые должны суммироваться (указывается только если этот диапазон не совпадает с диапазоном для вычисления условия)
Пример
Пример
Если в формуле
=(A4-A5)+(A6-A7)+(A8-A9)+(A10-A11)+(A12-A13)+(A14-A15)+(A16-A17)+. . .
раскрыть скобки , то получим
=A4-A5+A6-A7+A8-A9+A10-A11+A12-A13+A14-A15+A16-A17+. . .
Получим сумму элементов, в которой нечетный элемент берётся со знаком минус.
Для продления формулы указываем нужный диапазон
Записать в Excel это можно формулой массива (вводится нажатием CTRL+SHAFT+ENTER):
=СУММ( ЕСЛИ( ОСТАТ( СТРОКА(A4:A17);2)=0 ; A4:A17 ; -A4:A17))
последняя строка диапазона должна быть нечетной.
Допустим, что исходный текст записан в ячейке B3
Сначала надо проверить есть ли в начале текста артикул или условное цифровое обозначение, номер или код.
если текст начинается с цифры, то артикул есть
Формула для проверки (если есть артикул то результат ИСТИНА):
=ЕЧИСЛО( ЗНАЧЕН( ПСТР(B3;1;1) ))
Формула для столбца с цифрами:
=ЕСЛИ( ЕЧИСЛО( ЗНАЧЕН( ПСТР(B3;1;1) )) ; ЛЕВСИМВ( B3; НАЙТИ(" "; B3)) ; "")
Формула для столбца с наименованием товара:
=ЕСЛИ( ЕЧИСЛО( ЗНАЧЕН( ПСТР( B3;1;1) )) ; ПРАВСИМВ( B3 ; ДЛСТР(B3)-НАЙТИ( " ";B3)) ; B3)
После копирования этих формул в свою таблицу надо заменить ячейку B3 на ту, в которой содержится текст, а потом растянуть на весь диапазон значений.
Если у вас всегда заполнены первые ячейки и заполнение идет без разрывов, то попробуйте вот так:
=СУММ("первая ячейка диапазона":ДВССЫЛ(ADDRESS(СЧЕТЗ("весь диапазон")-1;СТОЛБЕЦ("любая ячейка из этого же столбца"))))
Для варианта, что на скриншоте:
=СУММ(A1:ДВССЫЛ(ADDRESS(СЧЕТЗ(A1:A24)-1;СТОЛБЕЦ(A1))))
СЧЕТЗ - подсчитывает количество непустых ячеек в диапазоне?
СЧЕТЗ("весь диапазон")-1 даст число ячеек на 1 меньше
ADDRESS(x;y) - преобразовывает порядковые номера строк и столбцов x и y в текст адреса ячейки.
СТОЛБЕЦ(A1) - возвращает номер столбца
ДВССЫЛ - преобразовывает текст адреса ячейки в ссылку на нее.
СУММ думаю понятно.
Попробуйте у себя, ибо у меня нет екселя, пробовал на либре офисе с теми же функциями (только по другому пишутся) все работает.
Вот моя формула: =SUM(A1:INDIRECT(ADDRESS(COUNTA(A1:A18)-1;COLUMN(A3))))
Как видите
=СУММ(A1:ДВССЫЛ(ADDRESS(СЧЕТЗ(A1:A24)-1;СТОЛБЕЦ(A1)))) и
=SUM(A1:INDIRECT(ADDRESS(COUNTA(A1:A24)-1;COLUMN(A1))))
ничем не отличаются.
Алгоритм формулы позволяющей выбрать из ячейки число такой:
индекс цифры:
=ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)
остаток исходной строки
=ПРАВСИМВ(A1; ДЛСТР(A1)- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1)
Чтобы выбрать из ячейки число получится формула тип результата число:
=--ЛЕВСИМВ( ПРАВСИМВ(A1; ДЛСТР(A1)-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); ПОИСКПОЗ(0; ЕЧИСЛО( --ПСТР( ПРАВСИМВ(A1;ДЛСТР(A1<wbr />)- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); 1;СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( ПРАВСИМВ(A1; ДЛСТР(A1) - ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1) )))))*СТРОКА( ДВССЫЛ( "1:"&ДЛСТР(ПРАВСИМВ( A1; ДЛСТР(A1) - ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1)) ));0)-1)
с учетом возможности обработки строк без цифр:
=ЕСЛИОШИБКА( --ЛЕВСИМВ( ПРАВСИМВ(A1; ДЛСТР(A1)-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); ПОИСКПОЗ(0; ЕЧИСЛО( --ПСТР( ПРАВСИМВ(A1;ДЛСТР(A1<wbr />)- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); 1;СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( ПРАВСИМВ(A1; ДЛСТР(A1) - ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1) )))))*СТРОКА( ДВССЫЛ( "1:"&ДЛСТР(ПРАВСИМВ( A1; ДЛСТР(A1) - ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1)) ));0)-1); "нет цифр")
Чтобы выбрать из ячейки число как текст надо убрать двойной минус(--):
=ЛЕВСИМВ( ПРАВСИМВ(A1; ДЛСТР(A1)-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); ПОИСКПОЗ(0; ЕЧИСЛО( --ПСТР( ПРАВСИМВ(A1;ДЛСТР(A1<wbr />)- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); 1;СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( ПРАВСИМВ(A1; ДЛСТР(A1) - ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1) )))))*СТРОКА( ДВССЫЛ( "1:"&ДЛСТР(ПРАВСИМВ( A1; ДЛСТР(A1) - ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1)) ));0)-1)
с учетом возможности обработки строк без цифр:
=ЕСЛИОШИБКА( ЛЕВСИМВ( ПРАВСИМВ(A1; ДЛСТР(A1)-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); ПОИСКПОЗ(0; ЕЧИСЛО( --ПСТР( ПРАВСИМВ(A1;ДЛСТР(A1<wbr />)- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); 1;СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( ПРАВСИМВ(A1; ДЛСТР(A1) - ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1) )))))*СТРОКА( ДВССЫЛ( "1:"&ДЛСТР(ПРАВСИМВ( A1; ДЛСТР(A1) - ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1)) ));0)-1); "нет цифр")
Пример работы:
