Как удалить пустые ячейки и сдвинуть данные влево в Excel?
При работе с наборами данных в Excel нередко возникают нежелательные пустые ячейки, разбросанные среди ваших данных. Такие пробелы могут нарушать вычисления, сортировку и анализ, особенно если важно, чтобы записи шли сплошным, аккуратно выровненным блоком без разрывов. Ручное удаление каждой пустой ячейки с последующим сдвигом содержимого влево становится крайне трудоёмким и ошибкоопасным при работе с большими объёмами информации. В этом руководстве представлены несколько быстрых и эффективных способов удалить пустые ячейки в заданном диапазоне и автоматически сдвинуть оставшиеся данные влево — чтобы ваш лист оставался чистым, структурированным и готовым к дальнейшей обработке или анализу. Мы рассмотрим разные подходы: ручной, основанный на формулах и программируемый — чтобы вы могли выбрать оптимальное решение под свои задачи. Понимание сильных и слабых сторон каждого метода поможет вам принять взвешенное решение именно для вашей ситуации.
 |  |  |
Функция «Перейти» — Удаление пустых ячеек и Переместить влево
Формула Excel — Удаление пустых ячеек и сдвиг данных влево
Код VBA — Автоматическое удаление пустых ячеек и сдвиг данных влево
Функция «Перейти» — Удаление пустых ячеек и Переместить влево
Если вы хотите удалить пустые ячейки и сдвинуть оставшиеся данные влево, Excel предлагает встроенный инструмент, который быстро справляется с относительно небольшими и однородными диапазонами. Этот метод особенно удобен, если вы предпочитаете обходиться без формул или программирования. Однако он лучше всего подходит для разовых базовых операций очистки, а не для автоматизированных решений или работы с очень большими наборами данных. Ниже приведено пошаговое руководство:
1. Выделите диапазон данных, содержащий пустые ячейки, которые нужно удалить. Это может быть одна строка, несколько строк или вся область. Затем нажмите Ctrl + G, чтобы открыть диалоговое окно «Перейти». В этом окне нажмите кнопку «Выделить», чтобы дополнительно уточнить выделение. См. снимок экрана:
2. В диалоговом окне «Выделить» установите флажок напротив пункта «Пустые ячейки» и нажмите кнопку «ОК». На этом этапе будут выделены все пустые ячейки в выбранном вами диапазоне. См. снимок экрана:
3. После выделения всех пустых ячеек щёлкните правой кнопкой мыши по любой из них и выберите в контекстном меню команду «Удалить». См. снимок экрана:
4. В диалоговом окне «Удалить» выберите опцию «Сдвинуть ячейки влево» и нажмите кнопку «ОК». В результате все данные сдвинутся влево, аккуратно заполнив ранее существовавшие промежутки. См. снимок экрана:
После выполнения этих шагов пустые ячейки будут удалены, а оставшиеся данные сдвинуты влево, как показано ниже:
| | |
Формула Excel — Удаление пустых ячеек и сдвиг данных влево
Когда нужно динамическое решение на основе формул — например, удаление пустых ячеек из строк или столбцов с автоматическим обновлением результатов при изменении исходных данных — функция Excel, такая как ФИЛЬТР (Excel 365 и Excel 2021), становится особенно эффективной. Такой подход идеально подходит для создания нового диапазона без пустых ячеек: он сохраняет исходные данные в неизменном виде и одновременно предоставляет их «сжатую» версию без пропусков, сдвинутую влево, в другом месте листа.
Примечание: Наличие конкретных функций зависит от вашей версии Excel. Функция ФИЛЬТР доступна только в более новых версиях — 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек