Новости:

Теперь на форум можно залогиниться / зарегистрироваться с помощью ВКонтакте. Уже существующие пользователи могут связать свою учетную запись с аккаунтом ВКонтакте одним кликом в профиле пользователя http://forum.msexcel.ru/index.php?action=profile;area=account

Главное меню

Посчитать кол-во номеров, записанных через запятые, тире

Автор Сергей Биличенко, 09.10.2014, 09:31

« назад - далее »

Сергей Биличенко

Есть номера предметов, которые записаны не по уникальному правилу, то есть могут быть пробелы между ними, могут быть разделены запятыми или точками, могут быть диапазоны введенные через "-". Известно что точно первый номер будет пятизначный и введен пятью цифрами, а все остальные без ввода первых двух разрядов, то есть тремя цифрами. Необходимо сосчитать кол-во этих предметов, номера которых перечислены.

iron priest

я ничо не понял. от куда взялись цифры "посчитано вручную для примера"

Сергей Биличенко

глазами посмотрел и в голове посчитал, чтобы было понятно, что нужно получить в итоге

zs

Предлагаю вариант - рационализация за Вами ;)
С уважением, ZS5

vikttur

#4
Заменить разделители - не проблема. Но "все остальные" - сколько их? Количество ограничено или... пишем, сколько хотим?
Такое лучше кодом обрабатывать.
zs, добавляем диапазон - и формула "поплыла"

zs

=ЕСЛИОШИБКА(ПСТР(D4;НАЙТИ("-";D4;1)+1;3)-ПСТР(D4;НАЙТИ("-";D4;1)-3;3);0)+ЕСЛИОШИБКА(ПСТР(D4;НАЙТИ("-";D4;НАЙТИ("-";D4;1)+1)+1;3)-ПСТР(D4;НАЙТИ("-";D4;НАЙТИ("-";D4;1)+1)-3;3);0)+ДЛСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ((ПОДСТАВИТЬ(ПОДСТАВИТЬ(D4;", ";",");". ";"."));",";" ");".";" ");" ";"!!"))-ДЛСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(D4;", ";",");". ";"."))+1

zs

Цитата: vikttur от 09.10.2014, 12:16
zs, добавляем диапазон - и формула "поплыла"
Не плывет, работает
Рационализация за Вами.
С уважением, ZS5

vikttur

Не нужно рационализировать, пока автор молчит :)
10456-459, 461, 463-468, 555-566 - такой вариант возможен?

zs

Цитата: vikttur от 09.10.2014, 13:08
Не нужно рационализировать, пока автор молчит :)
10456-459, 461, 463-468, 555-566 - такой вариант возможен?

Сергей Биличенко

Цитата: zs от 09.10.2014, 13:15
Цитата: vikttur от 09.10.2014, 13:08
Не нужно рационализировать, пока автор молчит :)
10456-459, 461, 463-468, 555-566 - такой вариант возможен?
Возможен и такой вариант. Кол-во вводов ограничить можно 20 насильно, запретив оператору вводить кучу ерунды, захочет больше - пусть следующе строкой вводит.

Сергей Биличенко

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

Сергей Биличенко

Я не совсем новичок в екселе, но вы маньяки!!! ;)

zs

Цитата: vikttur от 09.10.2014, 13:08
Не нужно рационализировать, пока автор молчит :)
10456-459, 461, 463-468, 555-566 - такой вариант возможен?
=ЕСЛИОШИБКА(ПСТР(ЕСЛИ(ДЛСТР(D4)-ДЛСТР(ПОДСТАВИТЬ(D4;"-";""))>2;ПОДСТАВИТЬ(ПОДСТАВИТЬ(D4;"-";" ";1);"-";" ";1);0);НАЙТИ("-";ЕСЛИ(ДЛСТР(D4)-ДЛСТР(ПОДСТАВИТЬ(D4;"-";""))>2;ПОДСТАВИТЬ(ПОДСТАВИТЬ(D4;"-";" ";1);"-";" ";1);0);1)+1;3)-ПСТР(D4;НАЙТИ("-";ЕСЛИ(ДЛСТР(D4)-ДЛСТР(ПОДСТАВИТЬ(D4;"-";""))>2;ПОДСТАВИТЬ(ПОДСТАВИТЬ(D4;"-";" ";1);"-";" ";1);0);1)-3;3);0)+ЕСЛИОШИБКА(ПСТР(D4;НАЙТИ("-";D4;1)+1;3)-ПСТР(D4;НАЙТИ("-";D4;1)-3;3);0)+ЕСЛИОШИБКА(ПСТР(D4;НАЙТИ("-";D4;НАЙТИ("-";D4;1)+1)+1;3)-ПСТР(D4;НАЙТИ("-";D4;НАЙТИ("-";D4;1)+1)-3;3);0)+ДЛСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ((ПОДСТАВИТЬ(ПОДСТАВИТЬ(D4;", ";",");". ";"."));",";" ");".";" ");" ";"!!"))-ДЛСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(D4;", ";",");". ";"."))+1

vikttur

#13
zs, а если "Кол-во вводов... 20"? :)
Функция пользователя, для любого количества кодов:
Function CountCode(rRng As Range)
Dim ArrSplit
Dim sStr As String
Dim i As Long, n As Long
    ' убираем первые два знака, запятые, точки
    sStr = Replace(Mid(rRng.Value, 3, 999), ",", " ")
    sStr = Replace(sStr, ".", " ")
    ArrSplit = Split(sStr, " ") ' отдельные записи в массив
   
    ' циклом по записям
    For i = 0 To UBound(ArrSplit)
        If Len(ArrSplit(i)) > 0 Then
            If Len(ArrSplit(i)) = 3 Then
                n = n + 1 ' если код один, добавляем 1
            Else
                ' если два - добавляем количество
                n = n + Mid(ArrSplit(i), 5, 3) - Mid(ArrSplit(i), 1, 3) + 1
            End If
        End If
    Next i
   
    CountCode = n  ' итог работы функции
End Function

(правка: подправил функцию, заменил файл)

zs

Количество диапазонов введенные через "-"
=ДЛСТР(D4)-ДЛСТР(ПОДСТАВИТЬ(D4;"-";""))