Заполнение пустых ячеек линейными значениями в Excel – (4 эффективных метода)

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

Заполнение пустых ячеек линейными значениями в Excel

• Используя функцию «Заполнение серий»
• Используя Kutools для Excel
• Используя формулу
• Используя код VBA

Заполнение пустых ячеек линейными значениями с помощью функции «Заполнение серий»

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

1. Выберите диапазон от A2 до A8, затем нажмите «Главная» > «Заполнить» > «Серии», см. скриншот:
   
2. В диалоговом окне «Серии» просто нажмите OK — Excel заполнит первый блок пустых ячеек линейными значениями. См. скриншот:
   
3. Чтобы заполнить дополнительные блоки пустых ячеек, выберите следующий диапазон (например, A9:A14) и повторите описанные выше шаги для применения линейной интерполяции.
                     

Заполнение пустых ячеек линейными значениями с помощью Kutools для Excel

Если вы часто работаете с большими или сложными наборами данных, функция «Заполнение пустых ячеек» в Kutools для Excel предоставляет простой и эффективный способ заполнения пустых ячеек линейными значениями. Кроме того, этот инструмент позволяет заполнять пустые ячейки на основе направления соседних ячеек (вверх, вниз, влево или вправо), или даже заполнять их фиксированным значением, обеспечивая большую гибкость.

После установки Kutools для Excel выполните следующие шаги:

1. Выберите диапазон данных, который вы хотите заполнить линейными значениями. Затем нажмите «Kutools» > «Вставить» > «Заполнить пустые ячейки», см. скриншот:
   
2. В диалоговом окне «Заполнение пустых ячеек» отметьте опцию «Линейные значения» и порядок заполнения (слева направо или сверху вниз) в зависимости от ваших данных. См. скриншот:
   
3. Затем нажмите кнопку OK, Kutools автоматически заполнит пустые ячейки на основе линейного тренда между известными значениями. См. скриншот:
   

Заполнение пустых ячеек линейными значениями с помощью формулы

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

Предположим, ваши данные находятся в столбце A (A2:A8), и вы хотите заполнить пробелы на основе линейной интерполяции.

1. Введите или скопируйте следующую формулу в ячейку A3 (в приведенной выше формуле A2 — это начальная ячейка, а A8 — следующая ячейка с данными). См. скриншот:

=A2+($A$8-$A$2)/(ROW($A$8)-ROW($A$2))

2. Перетащите маркер заполнения вниз от A3 до строки прямо перед следующим известным значением (в этом примере A7). После заполнения вы увидите, что пустые ячейки теперь заполнены интерполированными значениями, которые следуют линейному тренду между двумя известными точками данных.

Заполнение пустых ячеек линейными значениями с помощью кода VBA

Если вам часто нужно заполнять пустые ячейки линейными значениями, макрос VBA может сэкономить время, автоматизируя эту задачу.

1. Выберите список данных, в котором вы хотите заполнить пустые ячейки линейными значениями.
2. Нажмите «Alt+F11», чтобы открыть окно «Microsoft Visual Basic for Applications».
3. Нажмите «Вставить» > «Модуль» и вставьте следующий код VBA в окно Модуля.

   Sub LinearFillBlanks()
   'Updateby Extendoffice
       Dim rng As Range
       Dim cell As Range
       Dim startCell As Range
       Dim endCell As Range
       Dim i As Long, countBlank As Long
       Dim startVal As Double, endVal As Double, stepVal As Double
       Set rng = Selection
       For i = 1 To rng.Rows.Count
           If Not IsEmpty(rng.Cells(i, 1).Value) Then
               Set startCell = rng.Cells(i, 1)
               startVal = startCell.Value
               countBlank = 0
               Do While i + countBlank + 1 <= rng.Rows.Count And IsEmpty(rng.Cells(i + countBlank + 1, 1))
                   countBlank = countBlank + 1
               Loop
               If i + countBlank + 1 <= rng.Rows.Count Then
                   Set endCell = rng.Cells(i + countBlank + 1, 1)
                   endVal = endCell.Value
                   stepVal = (endVal - startVal) / (countBlank + 1)
                   For j = 1 To countBlank
                       rng.Cells(i + j, 1).Value = startVal + stepVal * j
                   Next j
               End If
           End If
       Next i
   End Sub
   

4. Затем нажмите клавишу F5, чтобы запустить этот код, все пустые ячейки будут заполнены линейными значениями. См. скриншот:
   

Заключение

Заполнение пустых ячеек линейными значениями в Excel может значительно повысить точность и удобочитаемость ваших данных, особенно при работе с трендами или числовыми паттернами. Будь то использование встроенной функции «Заполнение серий» Excel, мощного дополнения Kutools для Excel, гибкого подхода на основе формул или автоматизированного решения VBA, каждый метод имеет свои преимущества в зависимости от размера данных, сложности и потребностей рабочего процесса.

Выберите метод, который лучше всего подходит для вашего случая:

• Используйте «Заполнение серий» для быстрой ручной интерполяции
• Попробуйте Kutools для быстрых результатов одним кликом по большим диапазонам
• Применяйте формулы для точности и контроля
• Или автоматизируйте с помощью VBA для повторяющихся задач

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

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

• Заполнение пустых ячеек 0 или другим конкретным значением
• Заполнение пустых ячеек значением сверху в Excel