Как удалить пустые ячейки и переместить данные влево в Excel?
Работа с наборами данных в Excel часто приводит к появлению нежелательных пустых ячеек, разбросанных среди ваших данных. Эти пустые места могут нарушать вычисления, сортировку или анализ, особенно если вы хотите, чтобы ваши записи были аккуратно выровнены без пробелов. Удаление каждой пустой ячейки вручную и смещение оставшегося содержимого влево может быть крайне трудоемким и подверженным ошибкам при работе с большим объемом информации. В этом руководстве представлены несколько быстрых и эффективных методов для удаления пустых ячеек в диапазоне данных и перемещения оставшихся ячеек влево, гарантируя, что ваш рабочий лист останется аккуратным и более подходящим для дальнейшей обработки или анализа. Представленные ниже решения учитывают разные потребности — будь то ручной, формула-ориентированный или программируемый подход. Понимание преимуществ и компромиссов каждого метода поможет вам выбрать наиболее подходящий для вашей ситуации.
 |  |  |
Функция Перейти - Удаление пустых ячеек и перемещение влево
Формула Excel - Удаление пустых значений и сдвиг данных влево
Код VBA - Автоматическое удаление пустых ячеек и сдвиг данных влево
Функция Перейти - Удаление пустых ячеек и перемещение влево
Когда вы хотите удалить пустые ячейки и переместить оставшиеся данные влево, Excel предоставляет встроенную функцию, которая работает быстро для относительно небольших и последовательных диапазонов. Этот подход особенно практичен, если вы предпочитаете не использовать формулы или программирование. Однако он наиболее подходит для базовой однократной очистки, а не для автоматизированных решений или очень больших наборов данных. Вот как выполнить эту задачу пошагово:
1. Выберите диапазон данных, содержащий пустые ячейки, которые вы хотите удалить. Это может быть одна строка, несколько строк или целая область. Затем нажмите Ctrl + G чтобы отобразить окно Перейти В этом окне нажмите кнопку Специальное для более точного уточнения вашего выбора. См. скриншот:
2. В окне Перейти к специальному диалоговое окно, выберите опцию Пустые и нажмите OKЭтот шаг выделит все пустые ячейки в выбранном диапазоне. См. скриншот:
3. Когда все пустые ячейки выделены, щелкните правой кнопкой мыши на любой из выбранных пустых ячеек и выберите Удалить в контекстном меню. См. скриншот:
4. В открывшемся диалоговом окне Удалить выберите опцию Сдвинуть ячейки влево и нажмите OKЭто действие приведет к тому, что все данные будут смещены влево, заполняя пробелы, созданные пустыми ячейками. См. скриншот:
После завершения этих шагов пустые ячейки будут удалены, а оставшиеся данные будут сдвинуты влево, как показано ниже:
| | |
Формула Excel - Удаление пустых значений и сдвиг данных влево
Для ситуаций, когда вам нужна динамическая, управляемая формулами система — например, исключение пустых значений из строк или столбцов с автоматическим обновлением результатов при изменении исходных данных — формула FILTER (Excel 365 и Excel 2021) может быть очень эффективной. Этот подход особенно полезен для создания нового диапазона без пустых ячеек, позволяя сохранить исходные данные нетронутыми, получая при этом версию без пробелов в другом месте вашего листа.
Примечание: Конкретные доступные формулы зависят от версии Excel. Функция FILTER доступна только в новых версиях (Excel 365, Excel 2021 и выше).
1. Предположим, что ваши данные находятся в A1:E10 (горизонтально). Чтобы создать новую строку без пробелов, выберите пустую ячейку, где вы хотите получить выходные данные, например F1, и введите следующую формулу:
=FILTER(A1:E1, A1:E1 <> "")Эта формула собирает только непустые ячейки из A1:E1 и перечисляет их слева направо, начиная с F1.
2. Нажмите Enter, и непустые данные автоматически заполнят новый диапазон слева. Затем протяните или скопируйте формулу вниз.
Применимый сценарий: Этот метод рекомендуется, когда вы хотите сохранить исходные данные без изменений и автоматически создать очищенный диапазон в другом месте. Однако одним из ограничений является то, что он не перезаписывает исходные данные, а предоставляет "виртуальную" смещенную версию данных.
Практический совет: Если вы хотите сохранить только значения в новом диапазоне, после применения формулы скопируйте результаты и используйте Специальная вставка > Значения, чтобы перезаписать исходный диапазон при необходимости. Всегда убедитесь, что ссылки на ячейки скорректированы, если вы копируете формулы в другие области.
Код VBA - Автоматическое удаление пустых ячеек и сдвиг данных влево
Если вам часто нужно удалять пустые ячейки и сдвигать оставшиеся записи влево — особенно в больших или неправильных наборах данных — макрос VBA может автоматизировать эту повторяющуюся задачу. Этот подход обрабатывает ячейки на месте, физически удаляя пустые, чтобы данные "уплотнились" влево внутри каждой строки. Макросы экономят время и помогают избежать ручных ошибок при работе со сложными листами.
1. Откройте редактор VBA, нажав Разработчик > Visual Basic. В открывшемся окне Microsoft Visual Basic for Applications нажмите Вставить > Модуль и вставьте код ниже в область модуля:
Sub DeleteBlanksShiftLeft()
Dim WorkRng As Range
Dim RowRng As Range
Dim xTitleId As String
Dim i As Long, c As Long
Dim TempList As Collection
Dim Cell As Range
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select range to process (rows will be processed individually):", xTitleId, WorkRng.Address, Type:=8)
On Error GoTo 0
If WorkRng Is Nothing Then Exit Sub
Application.ScreenUpdating = False
' Process each row individually
For Each RowRng In WorkRng.Rows
Set TempList = New Collection
' Collect non-empty values in the row
For Each Cell In RowRng.Cells
If Trim(Cell.Value) <> "" Then
TempList.Add Cell.Value
End If
Next Cell
' Clear entire row first
RowRng.ClearContents
' Refill row with collected values from left to right
For i = 1 To TempList.Count
RowRng.Cells(1, i).Value = TempList(i)
Next i
Next RowRng
Application.ScreenUpdating = True
MsgBox "All blank cells in rows have been removed and values shifted left.", vbInformation
End Sub
2После вставки кода нажмите кнопку 
или нажмите F5 чтобы запустить макрос. При запросе выберите целевой диапазон. Макрос будет обрабатывать каждую строку, сдвигая значения влево, чтобы заполнить пробелы и удаляя пустые ячейки по ходу.
Применимый сценарий: Макросы лучше всего подходят для пользователей, которые умеют включать и использовать VBA, особенно когда требуется регулярное удаление пробелов в больших диапазонах данных или шаблонах, которые используются повторно. Этот метод фактически изменяет исходные данные, поэтому рекомендуется предварительно сделать резервную копию вашего листа. Будьте осторожны, если ваша структура данных нерегулярна или содержит формулы, поскольку макрос работает со значениями и может нарушить зависимости.
Совет по устранению неполадок: Если возникают неожиданные результаты, это может быть связано с объединенными ячейками, выходом формул или защищенными ячейками. Разъедините, разблокируйте или очистите формулы по мере необходимости перед запуском макроса.
Рекомендация по итогу: По возможности выбирайте формулы для динамического, неразрушающего преобразования данных и макросы для пакетного редактирования на месте. Всегда сохраняйте копию своих данных перед запуском автоматизации и проверяйте результат, чтобы убедиться, что макет вашего листа остается как задумано.
Пакетная вставка пустых строк или столбцов через определенный интервал в диапазоне Excel |
| Если вы хотите вставлять пустые строки через каждую другую строку, вам, возможно, придется делать это по одной, но функция Вставить пустые строки и столбцы из Kutools для Excel может решить эту задачу за считанные секунды. Нажмите для 30 дней бесплатной пробной версии! |
| Kutools для Excel: более 300 удобных надстроек для Excel, доступных для бесплатного тестирования без ограничений в течение 30 дней. |
Связанные статьи
- Как копировать и вставлять только непустые ячейки в Excel?
- Как считать данные игнорируя нули или пустые ячейки в Excel?
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в 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 и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек