Перейти к содержимому

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

Как заполнить IP-адреса с увеличением в Excel?

Author Siluvia Last modified

Эффективное назначение IP-адресов в Excel может быть особенно полезным, будь то управление офисными устройствами, серверами или подготовка к массовому развертыванию IT. Например, вам может потребоваться создать последовательность IP-адресов, таких как от 192.168.1.1 до 192.168.10.1, где часть адреса будет увеличиваться для каждой записи. Ввод этих адресов вручную может быть трудоемким и подверженным ошибкам, а стандартная функция автозаполнения Excel обычно не обрабатывает числовые шаблоны в стиле IP так, как это требуется. Поэтому важно рассмотреть другие методы, которые упрощают эту повторяющуюся задачу, обеспечивая точность и согласованность при распределении IP-адресов. В этой статье мы представим несколько эффективных решений, включая встроенные формулы, продвинутые утилиты, такие как Kutools для Excel, и многое другое, чтобы помочь быстро заполнить IP-адреса с увеличивающимися значениями в Excel.

Заполните IP-адреса с увеличением с помощью формул

Если вы хотите создать диапазон IP-адресов от 192.168.1.1 до 192.168.10.1, где увеличение происходит в третьем октете, вы можете легко сделать это с помощью формулы Excel. Этот метод особенно полезен, если у вас есть регулярный шаблон увеличения и требуется гибкое, основанное на формулах решение, которое зависит только от встроенных функций Excel.

1. Выберите пустую ячейку (например, ячейку B2), и введите следующую формулу. Затем нажмите клавишу Enter, чтобы создать ваш первый IP-адрес в последовательности:

="192.168."&ROWS($A$1:A1)&".1"

enter a formula to Fill down IP Address with increment

2. После создания первого IP-адреса, щелкните по ячейке и перетащите маркер заполнения вниз по столбцу, чтобы автоматически создать дополнительные адреса в последовательности. Число строк должно соответствовать тому, сколько адресов вам нужно между начальным и конечным значениями.

drag the Fill Handle down to the cell until all needed IP Addresses are created

ℹ️ Примечания и практические советы:

  • В приведенной выше формуле, 192, 168 и 1 относятся к фиксированным октетам. Изменяющаяся часть — ROWS($A$1:A1) — генерирует последовательные целые числа, увеличиваясь с каждой строкой, чтобы обновить третий октет. Чтобы начать с другого числа (например, 3), измените ссылку (например, $A$3:A3).
  • Чтобы увеличить первый октет:
    =ROWS($A$1:A192)&".168.2.1"
  • Чтобы увеличить второй октет:
    ="192."&ROWS($A$1:A168)&".1.1"
  • Чтобы увеличить четвертый октет (назначения хостов):
    ="192.168.1."&ROWS($A$1:A1)
  • Всегда корректируйте логику формулы, чтобы она соответствовала требуемому диапазону адресов и начальным значениям.
  • Совет: Если вы собираетесь копировать формулу на много строк, дважды щелкните маркер заполнения, чтобы автоматически заполнить столбец.
  • Меры предосторожности:
    • Убедитесь, что ни один октет не превышает допустимый диапазон (0–255).
    • Результаты являются текстовыми строками. Убедитесь, что они соответствуют требованиям форматирования вашей целевой системы.
  • Устранение неполадок: Если вы видите неожиданные значения, проверьте ссылки на строки и положение начальной ячейки.

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

Заполните IP-адреса с увеличением с помощью Kutools для Excel

Для пользователей, которые предпочитают графический интерфейс или которым нужно создать более сложные последовательности (например, пользовательские начальные номера, увеличения или нестандартное форматирование), утилита Вставить порядковый номер в Kutools для Excel предоставляет быстрое и универсальное решение. Этот метод особенно подходит, если вы работаете с большими списками, нуждаетесь в дополнительных функциях, таких как автоматическое форматирование, и хотите минимизировать ручную корректировку формул.

Kutools для Excel предлагает более 300 продвинутых функций для упрощения сложных задач, повышая креативность и эффективность. Интеграция с возможностями ИИ позволяет Kutools автоматизировать задачи с высокой точностью, делая управление данными без усилий. Подробная информация о Kutools для Excel...  Бесплатная пробная версия...

1. Нажмите Kutools > Вставка > Вставить порядковый номер. См. скриншот:

click Insert Sequence Number feature of kutools

2. В диалоговом окне Вставить порядковый номер настройте последовательность IP-адресов следующим образом:

  • (1) Введите описательное имя для этого правила в поле Имя (например, OfficeIP3rdOctet).
  • (2) Введите начальное значение для увеличивающегося октета в поле Начальный номер. Например, используйте 1 для начала с 192.168.1.x.
  • (3) Укажите, насколько каждый IP должен увеличиваться в поле Увеличение (обычно 1).
  • (4) Установите количество цифр, если вам нужны лидирующие нули в вашей последовательности (например, 001, 002).
  • (5) Заполните фиксированные компоненты (например, 192.168. как Префикс и .1 как Суффикс), обеспечив правильное размещение точек.
  • (6) Нажмите кнопку Добавить, чтобы сохранить это правило для дальнейшего использования.

set a sequence rule in the dialog box

3. Когда вы готовы заполнить лист IP-адресами, выберите ячейки, где должны появиться адреса. Выберите сохраненное правило и нажмите Заполнить диапазон:

click the Fill Range button to fill the IP address

Эта утилита также позволяет генерировать другие пользовательские последовательности, такие как номера счетов, идентификаторы сотрудников или любую повторяющуюся комбинацию строк и чисел.

✅ Преимущества:

  • Высокая настраиваемость – поддерживает фиксированный текст, переменные увеличения и форматирование.
  • Не нужно запоминать или применять формулы вручную.
  • Правила последовательности можно сохранять и повторно использовать в разных книгах.

⚠️ Меры предосторожности:

  • Убедитесь, что префикс, суффикс и количество цифр настроены правильно, чтобы избежать некорректных адресов.
  • Дважды проверьте конфигурацию перед применением к большим диапазонам.

🛠️ Устранение неполадок:

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

  Если вы хотите воспользоваться бесплатной пробной версией (30-день) этой утилиты, пожалуйста, нажмите, чтобы скачать её, а затем перейдите к выполнению операции согласно вышеуказанным шагам.

VBA Код - Программно генерируйте последовательность IP-адресов с увеличением

Если вам требуется гибкий метод для создания диапазонов IP-адресов с пользовательскими начальными, конечными и увеличивающимися значениями или если ваш шаблон адреса сложнее, чем могут обработать формулы и инструменты последовательностей, использование макроса VBA может быть очень эффективным. Это решение идеально подходит для продвинутых пользователей Excel, для автоматизации массового создания и для сценариев, где вы можете захотеть запросить параметры ввода каждый раз при создании последовательности.

1. Чтобы использовать VBA для создания IP-адресов, нажмите Разработчик > Visual Basic, чтобы открыть окно Microsoft Visual Basic для приложений. Затем нажмите Вставить > Модуль и вставьте следующий код в модуль:

Sub GenerateIPSequence()
    Dim startThird As Long
    Dim endThird As Long
    Dim increment As Long
    Dim base1 As String
    Dim base2 As String
    Dim base4 As String
    Dim i As Long
    Dim rowStart As Long
    Dim outCell As Range

    On Error Resume Next
    xTitleId = "KutoolsforExcel"

    base1 = Application.InputBox("Enter the first octet:", xTitleId, "192", Type:=2)
    base2 = Application.InputBox("Enter the second octet:", xTitleId, "168", Type:=2)
    startThird = Application.InputBox("Enter starting value for third octet:", xTitleId, 1, Type:=1)
    endThird = Application.InputBox("Enter ending value for third octet:", xTitleId, 10, Type:=1)
    base4 = Application.InputBox("Enter the fourth octet:", xTitleId, "1", Type:=2)
    increment = Application.InputBox("Increment value for third octet:", xTitleId, 1, Type:=1)

    Set outCell = Application.InputBox("Select the first cell for output:", xTitleId, Type:=8)

    If increment <= 0 Then
        increment = 1
    End If

    rowStart = 0

    For i = startThird To endThird Step increment
        outCell.Offset(rowStart, 0).Value = base1 & "." & base2 & "." & i & "." & base4
        rowStart = rowStart + 1
    Next i
End Sub

2. Нажмите Run button кнопку для запуска макроса. Вас проведут через серию запросов ввода данных:

  • Первый октет – Введите начальную часть вашего IP-адреса (например, 192).
  • Второй октет – Обычно фиксированное значение, например, 168, в зависимости от вашей подсети.
  • Начальное значение для третьего октета – Это определяет, где начинается ваш блок с увеличением (например, 1).
  • Конечное значение для третьего октета – Определяет, когда последовательность прекращается (например, 10 для создания от 192.168.1.1 до 192.168.10.1).
  • Четвертый октет – Это часто фиксированное значение (например, 1) и представляет собой часть адреса хоста.
  • Увеличивающееся значение – Контролирует, как третий октет увеличивается между каждым рядом (обычно 1 для последовательных адресов).
  • Ячейка вывода – Выберите первую ячейку, куда должны быть записаны сгенерированные IP-адреса. Макрос будет заполнять вниз от этой ячейки.

После ввода всех значений макрос автоматически создаст и заполнит IP-адреса в формате: первый.второй.третий.четвертый (например, 192.168.3.1, 192.168.4.1 и т.д.).

✅ Советы по использованию:

  • Всегда сохраняйте свою книгу перед запуском новых макросов, чтобы избежать случайной потери данных.
  • Запускайте макрос несколько раз с разными параметрами для создания разных блоков адресов — нет необходимости изменять код.
  • Используйте этот метод, когда другие формулы или инструменты графического интерфейса не могут обработать сложные или переменные форматы IP.

⚠️ Меры предосторожности:

  • Все пользовательские входные данные проверяются — отрицательные увеличения автоматически сбрасываются на 1.
  • Убедитесь, что каждый октет остается в допустимом диапазоне (0–255).
  • Убедитесь, что в выходном столбце достаточно пустых строк, чтобы избежать перезаписи данных.
  • Для выполнения макроса требуется включение вкладки Разработчик и разрешение макросов.

🛠️ Устранение неполадок:

  • Если вы видите ошибки, проверьте настройки безопасности макросов в Разработчик > Безопасность макросов.
  • Если результат не появился, убедитесь, что выбранная ячейка вывода находится на правильном рабочем листе и не заблокирована.

Заполните IP-адреса с увеличением с помощью Kutools для Excel

 

Связанные статьи:

Лучшие инструменты для повышения продуктивности в Office

🤖 Kutools AI Aide: Совершенно новый подход к анализу данных благодаря: Интеллектуальное выполнение |  Генерация кода  |  Создание пользовательских формул |  Анализ данных и построение диаграмм  |  Вызов Kutools Functions…
Популярные функции: Поиск, выделение или отметка дубликатов | Удалить пустые строки | Объединить столбцы или адреса без потери данных | Округлить ...
Супер ПОИСК: VLOOKUP по нескольким критериям | VLOOKUP по нескольким значениям | Многолистовой поиск | Распознавание нечетких соответствий ...
Расширенный раскрывающийся список: Быстро создать раскрывающийся список | Зависимый раскрывающийся список | Множественный выбор в раскрывающемся списке ...
Менеджер столбцов: Добавить определённое количество столбцов | Переместить столбцы | Переключить видимость скрытых столбцов | Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формулы | Управление книгой и листами | Библиотека автотекста | Выбор даты | Объединить данные | Зашифровать/расшифровать ячейки | Отправить письмо по списку | Супер фильтр | Специальный фильтр (фильтр жирный/курсив/зачеркнутый...) ...
Топ15 наборов инструментов:12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...) |50+ типов диаграмм (Диаграмма Ганта, ...) |40+ полезных формул (Расчет возраста на основе даты рождения, ...) |19 инструментов для вставки (Вставить QR-код, Вставить изображение по пути, ...) |12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...) |7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...) | ... и многое другое
Используйте Kutools на предпочитаемом вами языке — поддерживает Английский, Испанский, Немецкий, Французский, Китайский и более40 других языков!

Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...

Office Tab добавляет вкладки в Office и делает вашу работу намного проще

  • Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
  • Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!

Все надстройки Kutools. Один установщик

Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.

Excel Word Outlook Tabs PowerPoint
  • Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
  • Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
  • Совместная работа — максимальная эффективность между приложениями Office
  • 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
  • Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек