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

Вход

Регистрация

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

 

= Мир MS Excel/Сортировка IP адресов - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сортировка IP адресов (Формулы/Formulas)
Сортировка IP адресов
hitman1316 Дата: Четверг, 13.07.2017, 22:16 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Доброго времени, уважаемые форумчане.
На работе почти ежедневно приходится работать с огромным количеством данных (списки АРМ, IP адреса).

Суть такова, есть 2 листа, которые генерит сервер по опросу в один файл ехель, на выходе имеем 1 лист с адресом и диапазоном портов, на втором листе АРМы с конкретным IP адресом.
Так вот, очень упростило бы жизнь, если кто-нибудь помог написать формулу, которая проверяла принадлежность IP адреса диапазону и если он принадлежит, то подтягивала к нему адрес, где он находится на 2 лист.
Если же нет, то к примеру выделяла его красным.

по-сути скорее всего это пару простых if, сам не могу никак додуматься, кидаю чистое тело в приложения.
Куча сотрудников будет очень благодарна!

P.S. Диапазон выводится в формате ХХХ.ХХХ.ХХХ.ХХХ-ХХХ.ХХХ.ХХХ.ХХХ на 1 ячейку, поменять возможности нет...
К сообщению приложен файл: 6529003.xlsx(9Kb)


Сообщение отредактировал hitman1316 - Четверг, 13.07.2017, 22:21
 
Ответить
СообщениеДоброго времени, уважаемые форумчане.
На работе почти ежедневно приходится работать с огромным количеством данных (списки АРМ, IP адреса).

Суть такова, есть 2 листа, которые генерит сервер по опросу в один файл ехель, на выходе имеем 1 лист с адресом и диапазоном портов, на втором листе АРМы с конкретным IP адресом.
Так вот, очень упростило бы жизнь, если кто-нибудь помог написать формулу, которая проверяла принадлежность IP адреса диапазону и если он принадлежит, то подтягивала к нему адрес, где он находится на 2 лист.
Если же нет, то к примеру выделяла его красным.

по-сути скорее всего это пару простых if, сам не могу никак додуматься, кидаю чистое тело в приложения.
Куча сотрудников будет очень благодарна!

P.S. Диапазон выводится в формате ХХХ.ХХХ.ХХХ.ХХХ-ХХХ.ХХХ.ХХХ.ХХХ на 1 ячейку, поменять возможности нет...

Автор - hitman1316
Дата добавления - 13.07.2017 в 22:16
vikttur Дата: Четверг, 13.07.2017, 22:59 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2262
Репутация: 389 ±
Замечаний: 0% ±

Цитата
Диапазон выводится в формате ХХХ.ХХХ.ХХХ.ХХХ-ХХХ.ХХХ.ХХХ.ХХХ на 1 ячейку

Нет, последний фрагмент границы диапазона может быть Х, ХХ, ХХХ.

Увы, пары ЕСЛИ (даже нескольких пар) недостаточно.

Все проверяемые IP принадлежат к указанным диапазонам? Если да, формулу можно облегчить.
Преобразуем IP в число:
Код
=--ПОДСТАВИТЬ(ЛЕВБ(B3;9)&ТЕКСТ(ПСТР(B3;11;3);"000");".";)

Преобразуем начала диапазонов в числа:
Код
=--ПОДСТАВИТЬ(ЛЕВБ(B3;9)&ТЕКСТ(ПСТР(ПОДСТАВИТЬ(B3;"-";"   ");11;3);"000");".";)

Ищем территорию:
Код
=ИНДЕКС(ТЕР!$A$3:$A$5;ПОИСКПОЗ(C3;ТЕР!$C$3:$C$5))

Преобразовывать IP в число можно сразу в формуле с ВПР:
Код
=ИНДЕКС(ТЕР!$A$3:$A$5;ПОИСКПОЗ(--ПОДСТАВИТЬ(ЛЕВБ(B3;9)&ТЕКСТ(ПСТР(B3;11;3);"000");".";);ТЕР!$C$3:$C$5))


Или такие вольности (с доп. вычислениями) недопустимы? Тогда получите тяжелую формулу массива.
Код
=ИНДЕКС(ТЕР!$A$3:$A$5;ПОИСКПОЗ(--ПОДСТАВИТЬ(ЛЕВБ(B3;9)&ТЕКСТ(ПСТР(B3;11;3);"000");".";);--ПОДСТАВИТЬ(ЛЕВБ(ТЕР!$B$3:$B$5;9)&ТЕКСТ(ПСТР(ПОДСТАВИТЬ(ТЕР!$B$3:$B$5;"-";"   ");11;3);"000");".";)))


Если же в диапазоне проверяемых будут IP, которые не принадлежат ни одному из диапазонов, формула усложнится проверкой верхней границы диапазона.
К сообщению приложен файл: 4482363.xlsx(10Kb)


Сообщение отредактировал vikttur - Четверг, 13.07.2017, 23:21
 
Ответить
Сообщение
Цитата
Диапазон выводится в формате ХХХ.ХХХ.ХХХ.ХХХ-ХХХ.ХХХ.ХХХ.ХХХ на 1 ячейку

Нет, последний фрагмент границы диапазона может быть Х, ХХ, ХХХ.

Увы, пары ЕСЛИ (даже нескольких пар) недостаточно.

Все проверяемые IP принадлежат к указанным диапазонам? Если да, формулу можно облегчить.
Преобразуем IP в число:
Код
=--ПОДСТАВИТЬ(ЛЕВБ(B3;9)&ТЕКСТ(ПСТР(B3;11;3);"000");".";)

Преобразуем начала диапазонов в числа:
Код
=--ПОДСТАВИТЬ(ЛЕВБ(B3;9)&ТЕКСТ(ПСТР(ПОДСТАВИТЬ(B3;"-";"   ");11;3);"000");".";)

Ищем территорию:
Код
=ИНДЕКС(ТЕР!$A$3:$A$5;ПОИСКПОЗ(C3;ТЕР!$C$3:$C$5))

Преобразовывать IP в число можно сразу в формуле с ВПР:
Код
=ИНДЕКС(ТЕР!$A$3:$A$5;ПОИСКПОЗ(--ПОДСТАВИТЬ(ЛЕВБ(B3;9)&ТЕКСТ(ПСТР(B3;11;3);"000");".";);ТЕР!$C$3:$C$5))


Или такие вольности (с доп. вычислениями) недопустимы? Тогда получите тяжелую формулу массива.
Код
=ИНДЕКС(ТЕР!$A$3:$A$5;ПОИСКПОЗ(--ПОДСТАВИТЬ(ЛЕВБ(B3;9)&ТЕКСТ(ПСТР(B3;11;3);"000");".";);--ПОДСТАВИТЬ(ЛЕВБ(ТЕР!$B$3:$B$5;9)&ТЕКСТ(ПСТР(ПОДСТАВИТЬ(ТЕР!$B$3:$B$5;"-";"   ");11;3);"000");".";)))


Если же в диапазоне проверяемых будут IP, которые не принадлежат ни одному из диапазонов, формула усложнится проверкой верхней границы диапазона.

Автор - vikttur
Дата добавления - 13.07.2017 в 22:59
_Boroda_ Дата: Четверг, 13.07.2017, 23:18 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 10829
Репутация: 4473 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
У меня тоже с допстолбцами, но немного более другие формулы
Преобразование произвольного IP в числовой вид (можно не 1000^, а 256^, но тогда числовой вид визуально не будет похож на исходный) (в файле точки поставлены форматом ячейки)
Код
=СУММПРОИЗВ(ПСТР(ПОДСТАВИТЬ("."&B3;".";ПОВТОР(" ";20));20*(5-СТРОКА($1:$4));20)*1000^(СТРОКА($1:$4)-1))

В листе ТЕР аналогично, но для обоих IP
А затем просто ищем по двум параметрам
Код
=ЕСЛИОШИБКА(ПРОСМОТР(;-1/(C3>=ТЕР!C$3:C$9)/(C3<=ТЕР!D$3:D$9);ТЕР!A$3:A$9);"")

При несовпадении красим Условным форматированием
К сообщению приложен файл: 6529003_1.xlsx(10Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеУ меня тоже с допстолбцами, но немного более другие формулы
Преобразование произвольного IP в числовой вид (можно не 1000^, а 256^, но тогда числовой вид визуально не будет похож на исходный) (в файле точки поставлены форматом ячейки)
Код
=СУММПРОИЗВ(ПСТР(ПОДСТАВИТЬ("."&B3;".";ПОВТОР(" ";20));20*(5-СТРОКА($1:$4));20)*1000^(СТРОКА($1:$4)-1))

В листе ТЕР аналогично, но для обоих IP
А затем просто ищем по двум параметрам
Код
=ЕСЛИОШИБКА(ПРОСМОТР(;-1/(C3>=ТЕР!C$3:C$9)/(C3<=ТЕР!D$3:D$9);ТЕР!A$3:A$9);"")

При несовпадении красим Условным форматированием

Автор - _Boroda_
Дата добавления - 13.07.2017 в 23:18
hitman1316 Дата: Пятница, 14.07.2017, 06:51 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Нет, последний фрагмент границы диапазона может быть Х, ХХ, ХХХ

Да, вы правы, может и 10.20.1.100 и все остальные возможные варианты.
К сожалению посмотреть смогу только вечером. А если преобразовывать в числовой формат, то он будет считать до 255? Тк айпишники имеют вид 255.255.255.255. Наверно нужно будет как-то отрезать числа, после 255

IP могут и вовсе быть вне диапазона, их в идеале как-то выделить.
Можно красным, как предлагают выше


Сообщение отредактировал hitman1316 - Пятница, 14.07.2017, 06:55
 
Ответить
Сообщение
Нет, последний фрагмент границы диапазона может быть Х, ХХ, ХХХ

Да, вы правы, может и 10.20.1.100 и все остальные возможные варианты.
К сожалению посмотреть смогу только вечером. А если преобразовывать в числовой формат, то он будет считать до 255? Тк айпишники имеют вид 255.255.255.255. Наверно нужно будет как-то отрезать числа, после 255

IP могут и вовсе быть вне диапазона, их в идеале как-то выделить.
Можно красным, как предлагают выше

Автор - hitman1316
Дата добавления - 14.07.2017 в 06:51
vikttur Дата: Пятница, 14.07.2017, 10:39 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 2262
Репутация: 389 ±
Замечаний: 0% ±

Цитата
может и 10.20.1.100 и все остальные возможные варианты

Варианты надо показывать сразу.
Увы, мое решение можете не мотреть - оно работает по первым 9-ти символам показанных ранее вариантов.

А дальше - интерес пропал...


Сообщение отредактировал vikttur - Пятница, 14.07.2017, 10:40
 
Ответить
Сообщение
Цитата
может и 10.20.1.100 и все остальные возможные варианты

Варианты надо показывать сразу.
Увы, мое решение можете не мотреть - оно работает по первым 9-ти символам показанных ранее вариантов.

А дальше - интерес пропал...

Автор - vikttur
Дата добавления - 14.07.2017 в 10:39
hitman1316 Дата: Пятница, 14.07.2017, 11:08 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Варианты надо показывать сразу.


Все варианты показаны во вложенном EXEL файле.
 
Ответить
Сообщение
Варианты надо показывать сразу.


Все варианты показаны во вложенном EXEL файле.

Автор - hitman1316
Дата добавления - 14.07.2017 в 11:08
vikttur Дата: Пятница, 14.07.2017, 11:14 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 2262
Репутация: 389 ±
Замечаний: 0% ±

может и 10.20.1.100 и все остальные возможные варианты

В файле нет вариантов типа ХХ.ХХ.Х.ХХХ и нигде ранее не упоминалось, что IP в этой конкретной задаче могут отличаться от маски ХХ.ХХХ.ХХ.здесь_до_трех_Х
 
Ответить
Сообщение
может и 10.20.1.100 и все остальные возможные варианты

В файле нет вариантов типа ХХ.ХХ.Х.ХХХ и нигде ранее не упоминалось, что IP в этой конкретной задаче могут отличаться от маски ХХ.ХХХ.ХХ.здесь_до_трех_Х

Автор - vikttur
Дата добавления - 14.07.2017 в 11:14
hitman1316 Дата: Пятница, 14.07.2017, 11:33 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
В файле нет вариантов типа ХХ.ХХ.Х.ХХХ и нигде ранее не упоминалось, что IP в этой конкретной задаче могут отличаться от маски ХХ.ХХХ.ХХ.здесь_до_трех_Х


Когда речь идет о IP, мне кажется итак поня но, что он может быть всевозможным, и 2 и 3 и 1 символьный на одну точку.
 
Ответить
Сообщение
В файле нет вариантов типа ХХ.ХХ.Х.ХХХ и нигде ранее не упоминалось, что IP в этой конкретной задаче могут отличаться от маски ХХ.ХХХ.ХХ.здесь_до_трех_Х


Когда речь идет о IP, мне кажется итак поня но, что он может быть всевозможным, и 2 и 3 и 1 символьный на одну точку.

Автор - hitman1316
Дата добавления - 14.07.2017 в 11:33
vikttur Дата: Пятница, 14.07.2017, 11:47 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 2262
Репутация: 389 ±
Замечаний: 0% ±

Цитата
IP в этой конкретной задаче

Решение давал исходя из условий и примера.
 
Ответить
Сообщение
Цитата
IP в этой конкретной задаче

Решение давал исходя из условий и примера.

Автор - vikttur
Дата добавления - 14.07.2017 в 11:47
Pelena Дата: Пятница, 14.07.2017, 11:49 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11261
Репутация: 2503 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
hitman1316, решение от _Boroda_ подошло или нет?


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщениеhitman1316, решение от _Boroda_ подошло или нет?

Автор - Pelena
Дата добавления - 14.07.2017 в 11:49
hitman1316 Дата: Пятница, 14.07.2017, 11:52 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
hitman1316, решение от _Boroda_ подошло или нет?

Смогу проверить только вечером, сразу отпишусь.
 
Ответить
Сообщение
hitman1316, решение от _Boroda_ подошло или нет?

Смогу проверить только вечером, сразу отпишусь.

Автор - hitman1316
Дата добавления - 14.07.2017 в 11:52
vikttur Дата: Пятница, 14.07.2017, 17:03 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 2262
Репутация: 389 ±
Замечаний: 0% ±

Я вернулся :)
Формулы мучить не стал, на основе предыдущей задумки соорудил UDF
[vba]
Код
Function AddrIP(r1 As Range, r2 As Range) As String
Dim sAddr As String, sTxt As String, sBrdr As String
Dim i As Long, j As Long
    For j = 0 To 3: sAddr = sAddr & Format(Split(r1, ".")(j), "000"): Next j
    
    For i = 1 To r2.Rows.Count
        sBrdr = "": sTxt = Split(r2(i, 2).Value, "-")(0)
        For j = 0 To 3: sBrdr = sBrdr & Format(Split(sTxt, ".")(j), "000"): Next j

        If Val(sAddr) >= Val(sBrdr) Then
            sBrdr = "": sTxt = Split(r2(i, 2).Value, "-")(1)
            For j = 0 To 3: sBrdr = sBrdr & Format(Split(sTxt, ".")(j), "000"): Next j
            
            If Val(sAddr) <= Val(sBrdr) Then AddrIP = r2(i, 1).Value: Exit Function
        End If
    Next i
End Function
[/vba]
К сообщению приложен файл: IP.xlsm(18Kb)


Сообщение отредактировал vikttur - Пятница, 14.07.2017, 17:21
 
Ответить
СообщениеЯ вернулся :)
Формулы мучить не стал, на основе предыдущей задумки соорудил UDF
[vba]
Код
Function AddrIP(r1 As Range, r2 As Range) As String
Dim sAddr As String, sTxt As String, sBrdr As String
Dim i As Long, j As Long
    For j = 0 To 3: sAddr = sAddr & Format(Split(r1, ".")(j), "000"): Next j
    
    For i = 1 To r2.Rows.Count
        sBrdr = "": sTxt = Split(r2(i, 2).Value, "-")(0)
        For j = 0 To 3: sBrdr = sBrdr & Format(Split(sTxt, ".")(j), "000"): Next j

        If Val(sAddr) >= Val(sBrdr) Then
            sBrdr = "": sTxt = Split(r2(i, 2).Value, "-")(1)
            For j = 0 To 3: sBrdr = sBrdr & Format(Split(sTxt, ".")(j), "000"): Next j
            
            If Val(sAddr) <= Val(sBrdr) Then AddrIP = r2(i, 1).Value: Exit Function
        End If
    Next i
End Function
[/vba]

Автор - vikttur
Дата добавления - 14.07.2017 в 17:03
hitman1316 Дата: Пятница, 14.07.2017, 18:25 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
У меня тоже с допстолбцами, но немного более другие формулы

Спасибо большое!

Я вернулся
Формулы мучить не стал, на основе предыдущей задумки соорудил UDF

Цитату порезал. Зачем она такая большая? Да и противоречит Правилам форума
_Boroda_

И вам огромное спасибо!
Оба решения подошли, только это с одной стороны без громоздких формул, а с другой, я пока не понимаю, что такое UDF, как оно работает и как это сделать на раб машине)
Ни флешек ни почты там нету, все отрубили СБСники.
Будем разбираться)
 
Ответить
Сообщение
У меня тоже с допстолбцами, но немного более другие формулы

Спасибо большое!

Я вернулся
Формулы мучить не стал, на основе предыдущей задумки соорудил UDF

Цитату порезал. Зачем она такая большая? Да и противоречит Правилам форума
_Boroda_

И вам огромное спасибо!
Оба решения подошли, только это с одной стороны без громоздких формул, а с другой, я пока не понимаю, что такое UDF, как оно работает и как это сделать на раб машине)
Ни флешек ни почты там нету, все отрубили СБСники.
Будем разбираться)

Автор - hitman1316
Дата добавления - 14.07.2017 в 18:25
Gustav Дата: Пятница, 14.07.2017, 18:59 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1391
Репутация: 540 ±
Замечаний: 0% ±

начинал с Excel 4.0...
Ни флешек ни почты там нету, все отрубили СБСники.

[offtop]Если есть собственный опыт или умеющие это делать друзья, то можно вынуть винчестер из раб.машины и вставить его как slave в недоменный компьютер - и СБСники нервно курят.
А если в биосе старт с CD или USB прописан раньше HDD, то можно попробовать загрузиться с LiveCD или LiveUSB. Или еще проще - с флэшки с Линуксом.
Это всё к вопросу о закрытых USB и путях переноса информации из/в компьютер в этих условиях.


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
Ни флешек ни почты там нету, все отрубили СБСники.

[offtop]Если есть собственный опыт или умеющие это делать друзья, то можно вынуть винчестер из раб.машины и вставить его как slave в недоменный компьютер - и СБСники нервно курят.
А если в биосе старт с CD или USB прописан раньше HDD, то можно попробовать загрузиться с LiveCD или LiveUSB. Или еще проще - с флэшки с Линуксом.
Это всё к вопросу о закрытых USB и путях переноса информации из/в компьютер в этих условиях.

Автор - Gustav
Дата добавления - 14.07.2017 в 18:59
hitman1316 Дата: Пятница, 14.07.2017, 19:37 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Ни флешек ни почты там нету, все отрубили СБСники.


Имеется в виду для внешки)
 
Ответить
Сообщение
Ни флешек ни почты там нету, все отрубили СБСники.


Имеется в виду для внешки)

Автор - hitman1316
Дата добавления - 14.07.2017 в 19:37
vikttur Дата: Пятница, 14.07.2017, 20:06 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 2262
Репутация: 389 ±
Замечаний: 0% ±

что такое UDF, как оно работает

Функция пользователя, написанная на VBA. Посмотреть: войти в редактор VBA (Alt+F11), слева в дереве поектов заглянуть в общий модуль.
 
Ответить
Сообщение
что такое UDF, как оно работает

Функция пользователя, написанная на VBA. Посмотреть: войти в редактор VBA (Alt+F11), слева в дереве поектов заглянуть в общий модуль.

Автор - vikttur
Дата добавления - 14.07.2017 в 20:06
hitman1316 Дата: Пятница, 14.07.2017, 21:27 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Функция пользователя, написанная на VBA. Посмотреть: войти в редактор VBA (Alt+F11), слева в дереве поектов заглянуть в общий модуль.

А как я могу ее добавить в новый пустой Excel?
Как я понимаю, нужно скрипт этот сохранить в каком-то редакторе Excel
 
Ответить
Сообщение
Функция пользователя, написанная на VBA. Посмотреть: войти в редактор VBA (Alt+F11), слева в дереве поектов заглянуть в общий модуль.

А как я могу ее добавить в новый пустой Excel?
Как я понимаю, нужно скрипт этот сохранить в каком-то редакторе Excel

Автор - hitman1316
Дата добавления - 14.07.2017 в 21:27
Pelena Дата: Пятница, 14.07.2017, 21:43 | Сообщение № 18
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11261
Репутация: 2503 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
Про модули можно почитать здесь


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеПро модули можно почитать здесь

Автор - Pelena
Дата добавления - 14.07.2017 в 21:43
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сортировка IP адресов (Формулы/Formulas)
Страница 1 из 11
Поиск:

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