В VBA новичек, пара дней всего, поэтому прошу помощи, помогите плз разобраться с темой. Есть первая книга excel с одним листом, в нем один столбец с случайными IP адресами и два пустых столбца Latitude и Longitude. Есть вторая книга excel, с одним листом с тремя столбцами, Network, Latitude, Longitude. Во второй книге перечислены подсети с маской 16, а на столбцах Latitude и Longitude указаны соответствующие этим подсетям широта и долгота (широта и долгота городов России). Задача следующая, нужно проверить, есть ли для всех ячеек со случайным IP адресом из первой книги совпадение в столбце с подсетями второй книги (совпадение по первым двум октетам IP адреса, остальные два октета игнорируются), если совпадение найдено, скопировать значение широты-долготы из столбцов Latitude и Longitude этой ячейки второй книги в соответствующие ячейки столбцов Latitude и Longitude первой книги. Сейчас делаю это вручную, много времени занимает, может быть есть способ автоматизировать этот процесс?
Работает, но, к сожалению, только с двухзначным октетом в IP. В случае трехзначного октета принимает значения широта-долгота от первых двух цифр октета. А адреса случайные, попадаются как с двузначными так и трехзначными в октетах.
Может быть есть возможность сделать проверку через VBA скрипт, а не через формулу? В таком случае можно будет вывести скрипт на кнопку и пересчет пройдет проще и быстрее.
_Boroda_,пытаюсь, но не очень получается. вот код макроса, что обрабатывает отчет Касперского, который приходит мне на мыло
Код
Sub CombineWorkbooks()
Dim FilesToOpen
Dim x As Integer
On Error GoTo ErrHandler
Application.ScreenUpdating = False
FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
MultiSelect:=True, Title:="Files to Merge")
If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "Файл не выбран!"
GoTo ExitHandler
End If
x = 1
While x <= UBound(FilesToOpen)
Workbooks.Open Filename:=FilesToOpen(x)
Sheets().Move After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)
x = x + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
Sub DeleteLists()
'
' DeleteLists Macros
'
'
Sheets(Array("Summary", "Slave servers summary")).Select
Sheets("Slave servers summary").Activate
ActiveWindow.SelectedSheets.Delete
End Sub
Sub CollectDataFromAllSheets()
Dim ws As Worksheet
Set wbCurrent = ActiveWorkbook
Workbooks.Add
Set wbReport = ActiveWorkbook
'копируем на итоговый лист шапку таблицы из первого листа
'wbCurrent.Worksheets(1).Range("A1:D1").Copy Destination:=wbReport.Worksheets(1).Range("A1")
'проходим в цикле по всем листам исходного файла
For Each ws In wbCurrent.Worksheets
'определяем номер последней строки на текущем листе и на листе сборки
n = wbReport.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
'задаем исходный диапазон, который надо скопировать с каждого листа - на выбор:
'Set rngData = ws.Range("A1:D5") 'фиксированный диапазон
'Set rngData = ws.UsedRange 'все, что есть на листе
'Set rngData = ws.Range("F5").CurrentRegion 'область, начиная от ячейки F5
Set rngData = ws.Range("A6", ws.Range("A6").SpecialCells(xlCellTypeLastCell)) 'от А6 и до конца листа
'копируем исходный диапазон и вставляем в итоговую книгу со следующей строки
rngData.Copy Destination:=wbReport.Worksheets(1).Cells(n + 1, 1)
Next ws
End Sub
Sub DeleteColumnAC()
'
' DeleteColumnAC Macros
'
'
Range("A:A,C:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
End Sub
Sub RenameColumns()
'
' RenameColumns Macros
'
'
Range("A1").Select
ActiveCell.FormulaR1C1 = "id"
End Sub
Sub CopyABtoD()
Dim arr(), arrNew()
Dim I&, J&, N&
With ActiveSheet
arr = .UsedRange.Value
For J = 1 To UBound(arr, 2)
For I = 1 To UBound(arr, 1)
If arr(I, J) <> Empty Then
ReDim Preserve arrNew(N)
arrNew(N) = arr(I, J)
N = N + 1
End If
Next
Next
.Cells(1, UBound(arr, 2) + 1).Resize(UBound(arrNew) + 1) = Application.Transpose(arrNew)
End With
End Sub
Sub DeleteColumnAB()
'
' DeleteColumnAB Macros
'
'
Range("A:A,B:B").Select
Range("B1").Activate
Selection.Delete Shift:=xlToLeft
End Sub
Sub DelDouble()
'
' DelDouble Macros
'
'
Columns("A:A").Select
ActiveSheet.Range("$A$1:$A$8706").RemoveDuplicates Columns:=1, Header:= _
xlYes
End Sub
Sub startproc()
CombineWorkbooks
DeleteLists
CollectDataFromAllSheets
DeleteColumnAC
RenameColumns
CopyABtoD
DeleteColumnAB
End Sub
Макрос startproc я назначил на кнопку. В итоге получается файл excel с одним столбцом со случайными IP адресами. Мне нужен макрос excel
Код
Sub
...
End Sub
чтобы можно было его включить в вышеуказанный список макросов и обратотать отчет целиком макросами, а как включить ваше решение с
RockerMan, Просто интересно и зачем? построить на карте от кель пришла угроза? Чуть опасность где видна, Верный сторож как со сна Шевельнется, встрепенется, К той сторонке обернется И кричит: «Кири-ку-ку. Царствуй, лежа на боку!» Если атака - то из Китая, там ресурс дешевле, даже если проксировано через их сервер. Что даст знание о координатах регистрации провайдера, чей пул IP проверяется?
БМВ,с онлайновыми данными согласен. Сейчас изучаю возможности Gephi работать c БД, чтобы получать сразу данные из баз. Сами исходники отчетов вряд ли можно выложить, там все данные по регионам, адреса, доменные имена, переработанный только если, но он (отчет) очень большой. Словами объяснить можно, один excel файл, в первых двух листах итоговые данные от Касперского, мне они не надо, я их удаляю, затем по отдельным листам данные по каждому региону. В листах 4 столбца, доменное имя хоста, адрес атакующего хоста, вирус (эксплойт,... то что словит Касперский), адрес атакуемого хоста. После обработки визуализирую через Gephi, делаю экспорт (плагин Sigma.js) и выкладываю на веб сервер.
RockerMan написал: Сами исходники отчетов вряд ли можно выложить, там все данные по регионам, адреса, доменные имена, переработанный только если, но он (отчет) очень большой.
все и ненадо, убираются данные с тех листов, что точно ненужны, и с остальных исключая 2. на них оставляются пара десятков строк. с данными. Этого хватит чтоб понять что за исходные. Количество регионов фиксированное?
БМВ, да, количество регионов фиксированное. В атаче файл отчета. Отчет Касперского о сетевых атаках. Сначала идут два листа с итогами работы Касперского, я их удалил, затем регионы, оставил два листа для примера. Количество строк разное по регионам, оставил несколько для примера.