Страницы: 1
RSS
Как посчитать количество (в т.ч. диапазон) целых чисел в одной ячейке разделенных запятой?
 
Здравствуйте!
Прошу помочь. Надо считать количество целых чисел в одной ячейке разделенных запятой?
Восемь ячеек, в одной, в нескольких или во всех, может быть ряд чисел (от 1 до 15), например: 1,2,10-15. Они указывают в каких рядах что-то размещено. Надо подсчитывать общее количество мест в отдельную ячейку.
В примере: 1,2,10-15, ответ = 8 (1, 2, 10, 11, 12, 13, 14, 15).
Возможны различные варианты:
2-5, 11-15 = 9
1-15 = 15
2-4,5,6,12-15 = 9
и т.д.
Возможно, подскажите другой вариант решения.
 
Здравствуйте!
Для одной ячейки вот такая формула массива:
Код
=СУММ(ЕСЛИ(ЕЧИСЛО(ПОИСК(","&СТРОКА($1:$14)&"-"&СТОЛБЕЦ($B:$O)&",";","&H1&","));СТОЛБЕЦ($C:$P)-СТРОКА($1:$14));--ЕЧИСЛО(ПОИСК(","&СТРОКА($1:$15)&",";","&H1&",")))

*Но, если есть пробелы, их надо удалить:

Код
=СУММ(ЕСЛИ(ЕЧИСЛО(ПОИСК(","&СТРОКА($1:$14)&"-"&СТОЛБЕЦ($B:$O)&",";","&ПОДСТАВИТЬ(G4;" ";)&","));СТОЛБЕЦ($C:$P)-СТРОКА($1:$14));--ЕЧИСЛО(ПОИСК(","&СТРОКА($1:$15)&",";","&ПОДСТАВИТЬ(G4;" ";)&",")))

**Чуть сократим:

Код
=СУММ(ЕЧИСЛО(ПОИСК(","&СТРОКА($1:$15)&-СТОЛБЕЦ($A:$O)&",";","&ПОДСТАВИТЬ(B1;" ";)&","))*(СТОЛБЕЦ($B:$P)-СТРОКА($1:$15));СЧЁТ(ПОИСК(","&СТРОКА($1:$15)&",";","&ПОДСТАВИТЬ(B1;" ";)&",")))
Изменено: Светлый - 15.09.2021 10:25:21
 
Цитата
в т.ч. диапазон
А у меня в вашем примере получилось 25
 
Kuzmich, 24= 15+5+2+2

для указанного примера
=SUM(LEN(B1:E2)-LEN(SUBSTITUTE(SUBSTITUTE(B1:E2;",";);"-";))+(LEN(B1:E2)-LEN(SUBSTITUTE(SUBSTITUTE(B1:E2;",";);"-";))>0)-(IFERROR(MID(B1:E2;FIND("-";B1:E2);15)+LEFT(B1:E2;FIND("-";B1:E2)-1)+1;)))
но если в одной ячейке микс, то  формульное решение сорее только для новыз функций
Изменено: БМВ - 14.09.2021 23:04:29
По вопросам из тем форума, личку не читаю.
 
БМВ,
Я сам же добавил в первую ячейку 1-15,20 и забыл. Склероз
 
Спасибо добрым людям!
Но, что то у меня ума не хватает(
Kuzmich, если в А1 Ваша формула, то в В1 любое число >1 дает 0.
скрин https://yadi.sk/i/pU28nXBQt5qxrA
Я не написал сразу и в файле только один пример, но у меня таких ячеек 8+1(итог) много, вниз одна за одной и справа/слева есть другие данные- мне не понятно как применить СТРОКА и СТОЛБЕЦ.
БМВ, я не знаю как это исправить, подскажите пожалуйста.
скрин https://yadi.sk/i/nyo7GVBIYsJ73Q
автоматически переведенная формула https://yadi.sk/i/SdSfzCHEB3HvaA
 
Андрей Алексеевич, Предложенные Светлый,  и БМВ,  формулы массивные. Вводятся тремя кнопками Ctrl+Shift+Enter
 
Андрей Алексеевич,
Цитата
в файле только один пример, но у меня таких ячеек 8+1(итог) много
Приведите реальный пример.
Да и формулы я не писал.
 
Как писал ранее, из-за ограничений в 2 измерения, моя формула считает отдельные значения в нескольких ячейках, ну разве что 1,3,5 учтется, а формула Светлый,  наоборот считает в одной ячейке микс . 1,3,5-10

А вот тут микс
=SUM(LEN(B1:E2)+1-LEN(SUBSTITUTE(B1:E2&",";",";)))-COUNTIF(B1:E2;"=")
-SUM(
SIGN(COUNTIF(B1:E2;"*,"&ROW(1:15)&"-"&TRANSPOSE(ROW(1:15))&",*")+
COUNTIF(B1:E2;ROW(1:15)&"-"&TRANSPOSE(ROW(1:15))&",*")+
COUNTIF(B1:E2;"*,"&ROW(1:15)&"-"&TRANSPOSE(ROW(1:15)))+
COUNTIF(B1:E2;ROW(1:15)&"-"&TRANSPOSE(ROW(1:15))))
*(ROW(1:15)-TRANSPOSE(ROW(1:15))))

или чуть короче
=SUM((B1:E2<>"")*(LEN(B1:E2)+1-LEN(SUBSTITUTE(B1:E2&",";",";))))
-SUM(
SIGN(COUNTIF(B1:E2;"*,"&ROW(1:15)&"-"&COLUMN(A:O)&",*")+
COUNTIF(B1:E2;ROW(1:15)&"-"&COLUMN(A:O)&",*")+
COUNTIF(B1:E2;"*,"&ROW(1:15)&"-"&COLUMN(A:O))+
COUNTIF(B1:E2;ROW(1:15)&"-"&COLUMN(A:O)))
*(ROW(1:15)-COLUMN(A:O)))


Естественно пробелы и прочее катастрофически неприемлемы.
а тут приемлемы
=SUM((SUBSTITUTE(INDEX(B1:E2;N(INDEX(INT((ROW(A1:INDEX(A:A;ROWS(B1:E2)*COLUMNS(B1:E2)))-1)/COLUMNS(B1:E2))+1;));N(INDEX(MOD(ROW(A1:INDEX(A:A;ROWS(B1:E2)*COLUMNS(B1:E2)))-1;COLUMNS(B1:E2))+1;)));" ";)<>"")*(LEN(SUBSTITUTE(INDEX(B1:E2;N(INDEX(INT((ROW(A1:INDEX(A:A;ROWS(B1:E2)*COLUMNS(B1:E2)))-1)/COLUMNS(B1:E2))+1;));N(INDEX(MOD(ROW(A1:INDEX(A:A;ROWS(B1:E2)*COLUMNS(B1:E2)))-1;COLUMNS(B1:E2))+1;)));" ";))+1-LEN(SUBSTITUTE(SUBSTITUTE(INDEX(B1:E2;N(INDEX(INT((ROW(A1:INDEX(A:A;ROWS(B1:E2)*COLUMNS(B1:E2)))-1)/COLUMNS(B1:E2))+1;));N(INDEX(MOD(ROW(A1:INDEX(A:A;ROWS(B1:E2)*COLUMNS(B1:E2)))-1;COLUMNS(B1:E2))+1;)));" ";)&",";",";))))
-SUM(
ISNUMBER(FIND(","&INT((COLUMN(A:HQ)-1)/15)+1&"-"&MOD(COLUMN(A:HQ)-1;15)+1&",";","&SUBSTITUTE(INDEX(B1:E2;N(INDEX(INT((ROW(A1:INDEX(A:A;ROWS(B1:E2)*COLUMNS(B1:E2)))-1)/COLUMNS(B1:E2))+1;));N(INDEX(MOD(ROW(A1:INDEX(A:A;ROWS(B1:E2)*COLUMNS(B1:E2)))-1;COLUMNS(B1:E2))+1;)));" ";)&","))
*(INT((COLUMN(A:HQ)-1)/15)-MOD(COLUMN(A:HQ)-1;15)))
Изменено: БМВ - 15.09.2021 16:25:32
По вопросам из тем форума, личку не читаю.
 
Мне даже страшно смотреть на такие формулы  8-0
Попробуйте макросом
Код
Sub iSummaKol()
Dim arr
Dim iSummaKol As Long
Dim cell As Range
Dim i As Integer
    iSummaKol = 0
  For Each cell In Range("B1:E2")
    If Not IsEmpty(cell) Then
        arr = Split(cell, ",")
        For i = 0 To UBound(arr)
          If InStr(1, arr(i), "-") > 0 Then
              iSummaKol = iSummaKol + Split(arr(i), "-")(1) - Split(arr(i), "-")(0) + 1
          Else
              iSummaKol = iSummaKol + 1
          End If
        Next
    End If
  Next
    Range("A1") = iSummaKol
End Sub
 
Цитата
Kuzmich написал:
Мне даже страшно смотреть на такие формулы
мне завтра тоже будет страшно :-) сейчас еще помню, что к чему.
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
а тут приемлемы
У меня так работает:
Код
=СУММ(ЕСЛИОШИБКА(ВПР(ПОДСТАВИТЬ(Т(ИНДЕКС(","&СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(Т(СМЕЩ(A1;ОСТАТ(СТРОКА(2:9);2);СТРОКА(2:9)/2));",";ПОВТОР(" ";30));СТОЛБЕЦ(A:O)*30-29;30))&",";));" ";);ЕСЛИ({1;0};","&ПОДСТАВИТЬ(ОСТАТ(СТРОКА($15:$254);15)+1&-ОТБР(СТРОКА($15:$254)/15);-16;)&",";ЕСЛИ(СТРОКА($15:$254)>239;1;ОТБР(СТРОКА($15:$254)/15)-ОСТАТ(СТРОКА($15:$254);15)));2;);)) 

*Лишнюю функцию убрал:

Код
=СУММ(ЕСЛИОШИБКА(ВПР(Т(ИНДЕКС(","&ПОДСТАВИТЬ(ПСТР(ПОДСТАВИТЬ(Т(СМЕЩ(A1;ОСТАТ(СТРОКА(2:9);2);СТРОКА(2:9)/2));",";ПОВТОР(" ";99));СТОЛБЕЦ(A:O)*99-98;99);" ";)&",";));ЕСЛИ({1;0};","&ПОДСТАВИТЬ(ОСТАТ(СТРОКА($15:$254);15)+1&-ОТБР(СТРОКА($15:$254)/15);-16;)&",";ЕСЛИ(СТРОКА($15:$254)>239;1;ОТБР(СТРОКА($15:$254)/15)-ОСТАТ(СТРОКА($15:$254);15)));2;);))
Изменено: Светлый - 16.09.2021 15:08:28
 
Цитата
Светлый написал:
У меня так работает:
ну опять полетели :-) а 1- или 1, -2  ;)
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх