Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Проверить набор цифр по определенным параметрам. - Страница 2 - Мир MS Excel

Старая форма входа
  • Страница 2 из 2
  • «
  • 1
  • 2
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Проверить набор цифр по определенным параметрам. (Формулы/Formulas)
Проверить набор цифр по определенным параметрам.
bmv98rus Дата: Воскресенье, 02.12.2018, 23:22 | Сообщение № 21
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Да, с нулями не учел, но не проблема
Цитата Mark1976, 02.12.2018 в 16:37, в сообщении № 1 ()
на наличие идущих подряд 3 или более одинаковых цифр
это не включая контрольную сумму?

Код
=IFERROR(LEN((RIGHT(0&MOD(SUM(MID(SUBSTITUTE(A1;"-";"");ROW($1:$9);1)*(10-ROW($1:$9)));101);2)=RIGHT(A1;2))*(SUM(--ISERR(FIND(REPT(ROW($1:$10)-1;3);A1)))>0)*(1&SUBSTITUTE(A1;"-";"")))=12;1=0)

Если не включая и только в групах то
Код
=IFERROR(LEN((RIGHT(0&MOD(SUM(MID(SUBSTITUTE(A6;"-";"");ROW($1:$9);1)*(10-ROW($1:$9)));101);2)=RIGHT(A6;2))*(SUM(--ISERR(FIND(REPT(ROW($1:$10)-1;3);LEFT(A6;11))))>0)*(1&SUBSTITUTE(A6;"-";"")))=12;1=0)
Если подряд через - считать, то так
Код
=IFERROR(LEN((RIGHT(0&MOD(SUM(MID(SUBSTITUTE(A1;"-";);ROW($1:$9);1)*(10-ROW($1:$9)));101);2)=RIGHT(A1;2))*(SUM(--ISERR(FIND(REPT(ROW($1:$10)-1;3);LEFT(SUBSTITUTE(A1;"-";);9))))>0)*(1&SUBSTITUTE(A1;"-";)))=12;1=0)


Код
=IFERROR((RIGHT(0&MOD(SUM(MID(SUBSTITUTE(A1;"-";);ROW($1:$9);1)*(10-ROW($1:$9)));101);2)=RIGHT(A1;2))*(SUM(--ISERR(FIND(REPT(ROW($1:$10)-1;3);LEFT(SUBSTITUTE(A1;"-";);9))))>0)*(LEN(SUBSTITUTE(A1;"-";))=11);)=1

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Понедельник, 03.12.2018, 08:00
 
Ответить
СообщениеДа, с нулями не учел, но не проблема
Цитата Mark1976, 02.12.2018 в 16:37, в сообщении № 1 ()
на наличие идущих подряд 3 или более одинаковых цифр
это не включая контрольную сумму?

Код
=IFERROR(LEN((RIGHT(0&MOD(SUM(MID(SUBSTITUTE(A1;"-";"");ROW($1:$9);1)*(10-ROW($1:$9)));101);2)=RIGHT(A1;2))*(SUM(--ISERR(FIND(REPT(ROW($1:$10)-1;3);A1)))>0)*(1&SUBSTITUTE(A1;"-";"")))=12;1=0)

Если не включая и только в групах то
Код
=IFERROR(LEN((RIGHT(0&MOD(SUM(MID(SUBSTITUTE(A6;"-";"");ROW($1:$9);1)*(10-ROW($1:$9)));101);2)=RIGHT(A6;2))*(SUM(--ISERR(FIND(REPT(ROW($1:$10)-1;3);LEFT(A6;11))))>0)*(1&SUBSTITUTE(A6;"-";"")))=12;1=0)
Если подряд через - считать, то так
Код
=IFERROR(LEN((RIGHT(0&MOD(SUM(MID(SUBSTITUTE(A1;"-";);ROW($1:$9);1)*(10-ROW($1:$9)));101);2)=RIGHT(A1;2))*(SUM(--ISERR(FIND(REPT(ROW($1:$10)-1;3);LEFT(SUBSTITUTE(A1;"-";);9))))>0)*(1&SUBSTITUTE(A1;"-";)))=12;1=0)


Код
=IFERROR((RIGHT(0&MOD(SUM(MID(SUBSTITUTE(A1;"-";);ROW($1:$9);1)*(10-ROW($1:$9)));101);2)=RIGHT(A1;2))*(SUM(--ISERR(FIND(REPT(ROW($1:$10)-1;3);LEFT(SUBSTITUTE(A1;"-";);9))))>0)*(LEN(SUBSTITUTE(A1;"-";))=11);)=1
Автор - bmv98rus
Дата добавления - 02.12.2018 в 23:22
Mark1976 Дата: Понедельник, 03.12.2018, 05:28 | Сообщение № 22
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
Доброе утро. Для 10000 строк и более как применить формулу? В ней надо менять диапазон данных?
 
Ответить
СообщениеДоброе утро. Для 10000 строк и более как применить формулу? В ней надо менять диапазон данных?
Автор - Mark1976
Дата добавления - 03.12.2018 в 05:28
Mark1976 Дата: Понедельник, 03.12.2018, 07:47 | Сообщение № 23
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
bmv98rus,
Цитата
это не включая контрольную сумму?
включая.
 
Ответить
Сообщениеbmv98rus,
Цитата
это не включая контрольную сумму?
включая.
Автор - Mark1976
Дата добавления - 03.12.2018 в 07:47
bmv98rus Дата: Понедельник, 03.12.2018, 07:59 | Сообщение № 24
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Mark1976, не строчите вы очередями, сообщения можно дополнять и исправлять.
С учетом последнего
Код
=IFERROR((RIGHT(0&MOD(SUM(MID(SUBSTITUTE(A1;"-";);ROW($1:$9);1)*(10-ROW($1:$9)));101);2)=RIGHT(A1;2))*(SUM(--ISERR(FIND(REPT(ROW($1:$10)-1;3);SUBSTITUTE(A1;"-";))))>0)*(LEN(SUBSTITUTE(A1;"-";))=11);)=1

Тянуть можно сколь угодно.
К сообщению приложен файл: 6096561.xlsx (11.1 Kb)

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеMark1976, не строчите вы очередями, сообщения можно дополнять и исправлять.
С учетом последнего
Код
=IFERROR((RIGHT(0&MOD(SUM(MID(SUBSTITUTE(A1;"-";);ROW($1:$9);1)*(10-ROW($1:$9)));101);2)=RIGHT(A1;2))*(SUM(--ISERR(FIND(REPT(ROW($1:$10)-1;3);SUBSTITUTE(A1;"-";))))>0)*(LEN(SUBSTITUTE(A1;"-";))=11);)=1

Тянуть можно сколь угодно.
Автор - bmv98rus
Дата добавления - 03.12.2018 в 07:59
Светлый Дата: Понедельник, 03.12.2018, 08:34 | Сообщение № 25
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
У меня была недоработка с контрольной суммой, когда получалось 100. Исправленная формула:
Код
=ЕСЛИ(И(ДЛСТР(A1)=14;ОСТАТ(ПСТР(ПОДСТАВИТЬ(A1;"-";);СТРОКА($1:$7);3)/111;1)<>0;-ПРАВБ(ОСТАТ(СУММПРОИЗВ(ПСТР(ПОДСТАВИТЬ(A1;"-";);СТРОКА($1:$9);1)*(10-СТРОКА($1:$9)));101);2)=-ПРАВБ(A1;2);ЕЧИСЛО(-ПОДСТАВИТЬ(A1;"-";)));"ОК";"Ошибка")

Программировать проще, чем писать стихи.
 
Ответить
СообщениеУ меня была недоработка с контрольной суммой, когда получалось 100. Исправленная формула:
Код
=ЕСЛИ(И(ДЛСТР(A1)=14;ОСТАТ(ПСТР(ПОДСТАВИТЬ(A1;"-";);СТРОКА($1:$7);3)/111;1)<>0;-ПРАВБ(ОСТАТ(СУММПРОИЗВ(ПСТР(ПОДСТАВИТЬ(A1;"-";);СТРОКА($1:$9);1)*(10-СТРОКА($1:$9)));101);2)=-ПРАВБ(A1;2);ЕЧИСЛО(-ПОДСТАВИТЬ(A1;"-";)));"ОК";"Ошибка")
Автор - Светлый
Дата добавления - 03.12.2018 в 08:34
bmv98rus Дата: Понедельник, 03.12.2018, 10:51 | Сообщение № 26
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Светлый, не корректно проверять на длинну в 14 исходный
009-897-510000 - выдаст корректный результат.

Код
=MOD(MID(SUBSTITUTE(A1,"-",),ROW($1:$7),3)/111,1)<>0
почему не просто
Код
=MOD(MID(SUBSTITUTE(A1,"-",),ROW($1:$7),3);111)<>0


Короткий русский
Код
=IFERROR((RIGHTB(0&MOD(SUM(MID(SUBSTITUTE(A1;"-";);ROW($1:$9);1)*(10-ROW($1:$9)));101);2)=RIGHTB(A1;2))*(SUM(--ISERR(FIND(REPT(ROW($1:$10)-1;3);SUBSTITUTE(A1;"-";))))>0)*(LEN(SUBSTITUTE(A1;"-";))=11);)=1

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеСветлый, не корректно проверять на длинну в 14 исходный
009-897-510000 - выдаст корректный результат.

Код
=MOD(MID(SUBSTITUTE(A1,"-",),ROW($1:$7),3)/111,1)<>0
почему не просто
Код
=MOD(MID(SUBSTITUTE(A1,"-",),ROW($1:$7),3);111)<>0


Короткий русский
Код
=IFERROR((RIGHTB(0&MOD(SUM(MID(SUBSTITUTE(A1;"-";);ROW($1:$9);1)*(10-ROW($1:$9)));101);2)=RIGHTB(A1;2))*(SUM(--ISERR(FIND(REPT(ROW($1:$10)-1;3);SUBSTITUTE(A1;"-";))))>0)*(LEN(SUBSTITUTE(A1;"-";))=11);)=1
Автор - bmv98rus
Дата добавления - 03.12.2018 в 10:51
StoTisteg Дата: Понедельник, 03.12.2018, 12:15 | Сообщение № 27
Группа: Авторы
Ранг: Старожил
Сообщений: 1161
Репутация: 103 ±
Замечаний: 0% ±

Excel 2010
Для разнообразия сделал макросом...[vba]
Код
Sub CheckSums()

   Dim i As Long
   Dim j As Integer
   Dim bErr As Boolean
   Dim s As String
   Dim cs As Integer, csm As Integer
   
   For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
      bErr = False
      Do
         s = Cells(i, 1).Value
         bErr = Not (Len(s) = 14)
         If bErr Then Exit Do
         bErr = Not (Mid(s, 4, 1) = "-" And Mid(s, 8, 1) = "-" And Mid(s, 12, 1) = "-")
         If bErr Then Exit Do
         cs = Val(Right(s, 2))
         s = Left(Replace(s, "-", "", 1, -1, vbTextCompare), 9)
         bErr = Not IsNumeric(s)
         If bErr Then Exit Do
         For j = 0 To 9
            bErr = InStr(1, s, i & i & i, vbTextCompare) > 0
            If bErr Then Exit For
         Next j
         If bErr Then Exit Do
         csm = 0
         For j = 8 To 1 Step -1
            csm = csm + Val(Left(s, 1)) * (j + 1)
            s = Right(s, j)
         Next j
         csm = csm + Val(s)
         csm = IIf(csm Mod 101 = 100, 0, csm Mod 101)
         bErr = Not csm = cs
         Exit Do
      Loop
      If bErr Then Cells(i, 3) = "ошибка"
   Next i

End Sub
[/vba][p.s.]исправил погрешность во вложенном цикле, файл не перевкладывал
К сообщению приложен файл: 3806681.xlsm (16.1 Kb)

Интуитивно понятный код - это когда интуитивно понятно, что это код.

Сообщение отредактировал StoTisteg - Понедельник, 03.12.2018, 12:58
 
Ответить
СообщениеДля разнообразия сделал макросом...[vba]
Код
Sub CheckSums()

   Dim i As Long
   Dim j As Integer
   Dim bErr As Boolean
   Dim s As String
   Dim cs As Integer, csm As Integer
   
   For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
      bErr = False
      Do
         s = Cells(i, 1).Value
         bErr = Not (Len(s) = 14)
         If bErr Then Exit Do
         bErr = Not (Mid(s, 4, 1) = "-" And Mid(s, 8, 1) = "-" And Mid(s, 12, 1) = "-")
         If bErr Then Exit Do
         cs = Val(Right(s, 2))
         s = Left(Replace(s, "-", "", 1, -1, vbTextCompare), 9)
         bErr = Not IsNumeric(s)
         If bErr Then Exit Do
         For j = 0 To 9
            bErr = InStr(1, s, i & i & i, vbTextCompare) > 0
            If bErr Then Exit For
         Next j
         If bErr Then Exit Do
         csm = 0
         For j = 8 To 1 Step -1
            csm = csm + Val(Left(s, 1)) * (j + 1)
            s = Right(s, j)
         Next j
         csm = csm + Val(s)
         csm = IIf(csm Mod 101 = 100, 0, csm Mod 101)
         bErr = Not csm = cs
         Exit Do
      Loop
      If bErr Then Cells(i, 3) = "ошибка"
   Next i

End Sub
[/vba][p.s.]исправил погрешность во вложенном цикле, файл не перевкладывал
Автор - StoTisteg
Дата добавления - 03.12.2018 в 12:15
Mark1976 Дата: Понедельник, 03.12.2018, 12:36 | Сообщение № 28
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
StoTisteg, огромное спасибо.
 
Ответить
СообщениеStoTisteg, огромное спасибо.
Автор - Mark1976
Дата добавления - 03.12.2018 в 12:36
StoTisteg Дата: Понедельник, 03.12.2018, 13:01 | Сообщение № 29
Группа: Авторы
Ранг: Старожил
Сообщений: 1161
Репутация: 103 ±
Замечаний: 0% ±

Excel 2010
Ну или UDF, если блямба посреди листа не нужна:[vba]
Код
Function КСУММА(s As String) As String

   Dim i As Integer, cs As Integer, csm As Integer
   Dim bErr As Boolean
   
   КСУММА = ""
   Do
      If Not (Len(s) = 14) Then
         КСУММА = "#ДЛИНА"
         Exit Do
      End If
      If Not (Mid(s, 4, 1) = "-" And Mid(s, 8, 1) = "-" And Mid(s, 12, 1) = "-") Then
         КСУММА = "#РАЗДЕЛИТЕЛИ"
         Exit Do
      End If
      cs = Val(Right(s, 2))
      s = Left(Replace(s, "-", "", 1, -1, vbTextCompare), 9)
      If Not IsNumeric(s) Then
         КСУММА = "#НЕЧИСЛО"
         Exit Do
      End If
      bErr = False
      For i = 0 To 9
         bErr = InStr(1, s, i & i & i, vbTextCompare) > 0
         If bErr Then Exit For
      Next i
      If bErr Then
         КСУММА = "#ПОДРЯД"
         Exit Do
      End If
      csm = 0
      For i = 8 To 1 Step -1
         csm = csm + Val(Left(s, 1)) * (i + 1)
         s = Right(s, i)
      Next i
      csm = csm + Val(s)
      csm = IIf(csm Mod 101 = 100, 0, csm Mod 101)
      If Not csm = cs Then КСУММА = "#КСУММА"
      Exit Do
   Loop

End Function
[/vba]
К сообщению приложен файл: 9701180.xlsm (17.4 Kb)

Интуитивно понятный код - это когда интуитивно понятно, что это код.

Сообщение отредактировал StoTisteg - Понедельник, 03.12.2018, 13:11
 
Ответить
СообщениеНу или UDF, если блямба посреди листа не нужна:[vba]
Код
Function КСУММА(s As String) As String

   Dim i As Integer, cs As Integer, csm As Integer
   Dim bErr As Boolean
   
   КСУММА = ""
   Do
      If Not (Len(s) = 14) Then
         КСУММА = "#ДЛИНА"
         Exit Do
      End If
      If Not (Mid(s, 4, 1) = "-" And Mid(s, 8, 1) = "-" And Mid(s, 12, 1) = "-") Then
         КСУММА = "#РАЗДЕЛИТЕЛИ"
         Exit Do
      End If
      cs = Val(Right(s, 2))
      s = Left(Replace(s, "-", "", 1, -1, vbTextCompare), 9)
      If Not IsNumeric(s) Then
         КСУММА = "#НЕЧИСЛО"
         Exit Do
      End If
      bErr = False
      For i = 0 To 9
         bErr = InStr(1, s, i & i & i, vbTextCompare) > 0
         If bErr Then Exit For
      Next i
      If bErr Then
         КСУММА = "#ПОДРЯД"
         Exit Do
      End If
      csm = 0
      For i = 8 To 1 Step -1
         csm = csm + Val(Left(s, 1)) * (i + 1)
         s = Right(s, i)
      Next i
      csm = csm + Val(s)
      csm = IIf(csm Mod 101 = 100, 0, csm Mod 101)
      If Not csm = cs Then КСУММА = "#КСУММА"
      Exit Do
   Loop

End Function
[/vba]
Автор - StoTisteg
Дата добавления - 03.12.2018 в 13:01
Mark1976 Дата: Понедельник, 03.12.2018, 13:05 | Сообщение № 30
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
StoTisteg, файл открывается с ошибкой. Сразу с редактора.
 
Ответить
СообщениеStoTisteg, файл открывается с ошибкой. Сразу с редактора.
Автор - Mark1976
Дата добавления - 03.12.2018 в 13:05
StoTisteg Дата: Понедельник, 03.12.2018, 13:12 | Сообщение № 31
Группа: Авторы
Ранг: Старожил
Сообщений: 1161
Репутация: 103 ±
Замечаний: 0% ±

Excel 2010
Перевложил, посмотрите сейчас. УМВР.

Интуитивно понятный код - это когда интуитивно понятно, что это код.
 
Ответить
СообщениеПеревложил, посмотрите сейчас. УМВР.
Автор - StoTisteg
Дата добавления - 03.12.2018 в 13:12
Светлый Дата: Понедельник, 03.12.2018, 17:59 | Сообщение № 32
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата bmv98rus, 03.12.2018 в 10:51, в сообщении № 26 ()
почему не просто
=ОСТАТ(ПСТР(ПОДСТАВИТЬ(A1;"-";);СТРОКА($1:$7);3);111)<>0
Действительно! Как-то замылился глаз. Оптимизировать не стал. Тогда и <>0 тоже не нужно.
Код
=И(ОСТАТ(ПСТР(ПОДСТАВИТЬ(A1;"-";);СТРОКА($1:$9);3);111))
И причёсанная массивная формула:
Код
=ЕСЛИ(И(ДЛСТР(A1)=14;ДЛСТР(ПОДСТАВИТЬ(A1;"-";))=11;ОСТАТ(ПСТР(ПОДСТАВИТЬ(A1;"-";);СТРОКА($1:$9);3);111);ПРАВБ(0&ОСТАТ(СУММ(ПСТР(ПОДСТАВИТЬ(A1;"-";);10-СТРОКА($1:$9);1)*СТРОКА($1:$9));101);2)=ПРАВБ(A1;2));"ОК";"Ошибка")

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата bmv98rus, 03.12.2018 в 10:51, в сообщении № 26 ()
почему не просто
=ОСТАТ(ПСТР(ПОДСТАВИТЬ(A1;"-";);СТРОКА($1:$7);3);111)<>0
Действительно! Как-то замылился глаз. Оптимизировать не стал. Тогда и <>0 тоже не нужно.
Код
=И(ОСТАТ(ПСТР(ПОДСТАВИТЬ(A1;"-";);СТРОКА($1:$9);3);111))
И причёсанная массивная формула:
Код
=ЕСЛИ(И(ДЛСТР(A1)=14;ДЛСТР(ПОДСТАВИТЬ(A1;"-";))=11;ОСТАТ(ПСТР(ПОДСТАВИТЬ(A1;"-";);СТРОКА($1:$9);3);111);ПРАВБ(0&ОСТАТ(СУММ(ПСТР(ПОДСТАВИТЬ(A1;"-";);10-СТРОКА($1:$9);1)*СТРОКА($1:$9));101);2)=ПРАВБ(A1;2));"ОК";"Ошибка")
Автор - Светлый
Дата добавления - 03.12.2018 в 17:59
bmv98rus Дата: Понедельник, 03.12.2018, 21:57 | Сообщение № 33
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Светлый,
Цитата bmv98rus, 03.12.2018 в 10:51, в сообщении № 26 ()
не корректно проверять на длинну в 14 исходный
009-897-510000 - выдаст корректный результат


Код
=IF(IFERROR((RIGHTB(0&MOD(SUM(MID(SUBSTITUTE(A1;"-";);ROW($1:$9);1)*(10-ROW($1:$9)));101);2)=RIGHTB(A1;2))*SUM(--ISERR(FIND(REPT(ROW($1:$10)-1;3);SUBSTITUTE(A1;"-";))))*(LEN(SUBSTITUTE(A1;"-";))=11););"Ок";"Ошибка")

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Понедельник, 03.12.2018, 21:58
 
Ответить
СообщениеСветлый,
Цитата bmv98rus, 03.12.2018 в 10:51, в сообщении № 26 ()
не корректно проверять на длинну в 14 исходный
009-897-510000 - выдаст корректный результат


Код
=IF(IFERROR((RIGHTB(0&MOD(SUM(MID(SUBSTITUTE(A1;"-";);ROW($1:$9);1)*(10-ROW($1:$9)));101);2)=RIGHTB(A1;2))*SUM(--ISERR(FIND(REPT(ROW($1:$10)-1;3);SUBSTITUTE(A1;"-";))))*(LEN(SUBSTITUTE(A1;"-";))=11););"Ок";"Ошибка")
Автор - bmv98rus
Дата добавления - 03.12.2018 в 21:57
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Проверить набор цифр по определенным параметрам. (Формулы/Formulas)
  • Страница 2 из 2
  • «
  • 1
  • 2
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!