Как удалить пустые ячейки в Excel

Обновлено: 21.11.2024

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

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

В этом посте мы рассмотрим 9 способов удаления пустых строк из данных Excel.

Удалить пустые строки вручную

Первый способ — ручной.

Не волнуйтесь, позже мы перейдем к более простым методам. Но если у нас всего пара строк, ручной способ может быть быстрее.

Выберите пустые строки, которые мы хотим удалить. Удерживая нажатой клавишу Ctrl, щелкните строку, чтобы выбрать ее.

Когда строки, которые мы хотим удалить, выбраны, мы можем щелкнуть правой кнопкой мыши и выбрать в меню Удалить.

Мы также можем удалять строки с помощью команды на ленте. Перейдите на вкладку «Главная» ➜ нажмите команду «Удалить» ➜, затем выберите «Удалить строки листа».

Есть также очень удобная комбинация клавиш для удаления строк (столбцов или ячеек). Нажмите Ctrl + – на клавиатуре.

Вот оно! Наших пустых строк больше нет.

Удалить пустые строки с помощью функции «Перейти к специальному»

Выбор и удаление строк вручную допустимо, если нам нужно удалить только пару строк.

Что делать, если в наших данных есть много пустых строк? Выбор вручную был бы мучением!

Не волнуйтесь, в Excel есть команда, которая выделяет для нас все пустые ячейки.

Во-первых, нам нужно выбрать столбец наших данных, включая все пустые строки. Самый простой способ сделать это — выделить первую ячейку (в данном примере A1), затем, удерживая клавишу Shift, выбрать последнюю ячейку (в данном примере A14).

Теперь мы можем использовать команду «Перейти к специальному», чтобы выбрать только пустые ячейки. Перейдите на вкладку «Главная» ➜ нажмите команду «Найти и выбрать» ➜ выберите «Перейти к специальному» в меню.

Есть также удобное сочетание клавиш для меню "Перейти". Нажмите Ctrl + G, чтобы открыть меню «Перейти», затем нажмите кнопку «Специальные», чтобы открыть меню «Перейти к специальным».

Независимо от того, откроем ли мы меню «Перейти» и нажмем «Специальные» или перейдем непосредственно к меню «Перейти к специальным», мы попадем в одно и то же меню «Перейти к специальным».

Теперь все, что нам нужно сделать, это выбрать «Пробелы» в параметрах и нажать кнопку «ОК». Это выберет только пустые ячейки из нашего первоначального выбора столбца.

Теперь нам нужно удалить эти выбранные строки.

  1. Используйте любой метод удаления строк из раздела "Удаление пустых строк вручную".
    • Щелкните правой кнопкой мыши ➜ Удалить
    • Вкладка "Главная" ➜ "Удалить" ➜ "Удалить строки листа".
    • Ctrl + – сочетание клавиш
  2. В меню "Удалить" выберите "Вся строка" и нажмите кнопку "ОК".

Как по волшебству, мы можем найти и удалить сотни пустых строк в наших данных за несколько секунд. Это особенно удобно, когда у нас много пустых строк, разбросанных по длинному набору данных.

Удалить пустые строки с помощью команды «Найти»

Этот метод будет очень похож на описанный выше метод удаления пустых строк с помощью перехода к специальному методу. Единственная разница в том, что мы будем выбирать пустые ячейки с помощью команды «Найти».

Как и раньше, нам нужно выбрать столбец в наших данных.

Перейдите на вкладку "Главная" ➜ нажмите команду "Найти и выбрать" ➜ выберите "Найти" в меню.

Есть также сочетание клавиш, которое мы можем использовать, чтобы открыть меню поиска. Нажмите Ctrl + F на клавиатуре.

В любом случае это откроет для нас меню "Найти и заменить".

  1. Разверните дополнительные параметры в меню поиска.
  2. Оставьте поле ввода «Найти» пустым.
  3. Выберите параметр «Соответствовать всему содержимому ячейки».
  4. Поиск на листе.
  5. Посмотрите на значения.
  6. Нажмите кнопку "Найти все", чтобы вернуть все пустые ячейки.

Появится список всех пустых ячеек, найденных в выбранном диапазоне, в нижней части меню "Найти".

Мы можем выбрать их все, нажав Ctrl + A. Затем мы можем закрыть меню «Найти», нажав кнопку «Закрыть». Теперь мы можем удалить все пустые ячейки, как и раньше.

Удалить пустые строки с помощью фильтров

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

Во-первых, нам нужно добавить фильтры к нашим данным.

  1. Выберите весь диапазон данных, включая пустые строки.
  2. Перейдите на вкладку "Данные".
  3. Нажмите кнопку "Фильтр" в разделе "Сортировка и фильтр".

Мы также можем добавить фильтры к диапазону, используя сочетание клавиш Ctrl + Shift + L.

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

  1. Нажмите переключатель фильтра в одном из столбцов.
  2. Используйте переключатель "Выбрать все", чтобы отменить выбор всех элементов.
  3. Проверьте пробелы.
  4. Нажмите кнопку ОК.

Когда наши данные отфильтрованы, номера строк отображаются синим цветом, а в отфильтрованных строках отсутствуют номера.

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

Затем мы можем нажать кнопку OK, когда Excel спросит нас, хотим ли мы удалить всю строку листа.

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

Мы можем использовать фильтры немного по-другому, чтобы избавиться от пустых строк. На этот раз мы будем фильтровать пробелы. Щелкните переключатель фильтра в одном из столбцов ➜ снимите флажок «Пробелы» ➜ нажмите кнопку «ОК».

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

Удалить пустые строки с помощью расширенных фильтров

Подобно методу фильтрации, мы можем использовать параметр «Расширенные фильтры», чтобы получить копию наших данных за вычетом пустых строк.

Чтобы использовать функцию расширенных фильтров, нам потребуется выполнить небольшую настройку.

  1. Нам нужно настроить диапазон критериев фильтрации. Мы собираемся фильтровать только на основе одного столбца, поэтому нам нужен один заголовок столбца из наших данных (в этом примере F1). Под заголовком столбца нам нужны наши критерии (в этом примере F2), нам нужно ввести ="" в эту ячейку в качестве наших критериев.
  2. Выберите диапазон данных для фильтрации.
  3. Перейдите на вкладку "Данные".
  4. Выберите «Дополнительно» в разделе «Сортировка и фильтрация».

Теперь нам нужно настроить меню расширенного фильтра.

  1. Выберите Копировать в другое место.
  2. Выберите диапазон данных для фильтрации. Это поле уже должно быть заполнено, если диапазон был выбран до открытия меню расширенных фильтров.
  3. Добавьте критерии в диапазон критериев (в данном примере F1:F2).
  4. Выберите место на листе для копирования отфильтрованных данных.
  5. Нажмите кнопку ОК.

Теперь мы получаем копию наших данных в новом месте без пробелов.

Удалить пустые строки с помощью функции фильтра

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

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

Синтаксис функции FILTER

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

Функция FILTER для фильтрации пробелов

Вышеуказанная функция должна быть введена только в одну ячейку, и результаты будут распространяться на остальные ячейки по мере необходимости. Функция отфильтрует CarData в столбце Make и отфильтрует все пробелы.

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

Удалить пустые строки путем сортировки

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

  1. Выберите диапазон данных.
  2. Перейдите на вкладку "Данные".
  3. Нажмите команду сортировки. Подойдет как восходящий, так и нисходящий порядок.

Теперь все наши пустые строки появятся внизу, и мы можем их игнорировать.

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

Удалить пустые строки с помощью Power Query

Power может легко удалить пустые строки в наших данных.

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

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

  1. Перейдите на вкладку "Главная" в редакторе расширенных запросов.
  2. Нажмите кнопку "Удалить строки".
  3. Выберите в меню параметр "Удалить пустые строки".

При этом будет сгенерирован указанный выше M-код с использованием функции Table.SelectRows для выбора ненулевых строк. Это приведет к удалению только тех строк, в которых вся запись имеет нулевые значения.

Мы также могли бы получить тот же результат, отфильтровав нулевые значения в наших данных. Щелкните правой кнопкой мыши любой из переключателей сортировки и фильтрации, затем снимите флажок с нулевого значения и нажмите кнопку ОК.

Запрос Power снова создаст шаг с функцией Table.SelectRows, возвращая ненулевые значения в определенном столбце.

Удалить пустые строки с помощью Power Automate

Возможно, этот способ не такой быстрый, простой и практичный, как другие методы, но его можно сделать.

Мы можем использовать Power Automate для удаления пустых строк в наших таблицах Excel.

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

Для этого мы можем настроить небольшую автоматизацию Flow.

  1. Мы будем использовать ручную кнопку для запуска потока, но мы можем использовать любое количество триггеров.
  2. Затем нам нужно составить список строк, присутствующих в таблице, чтобы получить все строки данных из нашей таблицы Excel. Лучшим вариантом также было бы использовать фильтры Odata в разделе Показать дополнительные параметры для фильтрации пустых строк, но в настоящее время фильтрация пустых строк невозможна.
  3. Поскольку мы не можем фильтровать пустые значения с помощью фильтров Odata, для этого нам нужно использовать действие операции с данными массива Filter. Мы можем отфильтровать значения из строк списка, присутствующих в табличном действии, и установить условие, что Make равно пустому (оставьте значение пустым). Это даст нам все строки с пустыми ячейками.
  4. Теперь мы можем использовать действие "Удалить строку", чтобы удалить эти пустые строки. Мы можем выбрать наш столбец идентификатора в качестве ключевого столбца, а затем добавить поле идентификатора из действия массива фильтров. Это должно обернуть действие в Применить к каждому шагу, чтобы удалить все пробелы.

При запуске этой автоматизации все пустые строки в таблице будут удалены.

Выводы

Пустые строки в наших данных могут доставлять неудобства.

Удалить их легко, и у нас есть много вариантов.

Возможно, мой любимый способ – это сочетание клавиш "Перейти".Это быстро, просто и эффективно.

Я пропустил какие-либо методы? Дайте мне знать в комментариях ниже!

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

С помощью этого метода удалить пустые строки в диапазоне данных Excel легко, но следите за непредвиденными последствиями.

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

Выбрав диапазон данных в Excel, выполните следующие действия:

При выборе «Удалить ячейки» удаляются только пустые ячейки в ранее выбранном диапазоне. Если вы выберете «Удалить строки листа» на шаге 4, вы потенциально можете уничтожить данные (часто невидимые) справа. Будьте внимательны при использовании этого параметра для удаления пустых строк, когда вы действительно хотите удалить только пустые ячейки. Легко думать о строках и выбрать неправильный вариант!

Отправьте свой вопрос по Microsoft Office

Примечание редактора от 11 февраля 2019 г. Впервые этот совет по Excel был опубликован в январе 2012 года. С тех пор мы добавили версию этого совета в формате видеоурока и обновили соответствующие ресурсы.

Информационный бюллетень TechRepublic Premium Exclusives

Экономьте время с последними загрузками TechRepublic Premium, включая оригинальные исследования, настраиваемые шаблоны ИТ-политики, готовые презентации для обучения, инструменты для найма ИТ-специалистов, калькуляторы рентабельности инвестиций и многое другое. Эксклюзивно для вас!

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

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

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

Хотите узнать больше об Excel? Посетите наш виртуальный класс или онлайн-курсы Excel >

Если вы хотите удалить одну строку, вы можете удалить ее вручную:

  1. Выберите строку. Нажмите на его заголовок или выберите ячейку в строке и нажмите Shift + пробел.
  2. Щелкните правой кнопкой мыши заголовок выбранной строки. Появится раскрывающееся меню.
  3. Выберите Удалить.

1. Удаление пустых строк с помощью контекстного меню

Чтобы удалить несколько смежных пустых строк с помощью контекстного меню:

  1. Перетащите мышью заголовки строк или выберите заголовок первой строки, а затем щелкните заголовок последней строки, удерживая клавишу Shift.
  2. Щелкните правой кнопкой мыши один из заголовков строк. Появится раскрывающееся меню.
  3. Выберите Удалить.

Чтобы удалить несколько несмежных пустых строк с помощью контекстного меню:

  1. Чтобы выбрать несмежные строки, щелкните заголовок первой строки, а затем, удерживая клавишу Ctrl, щелкните заголовки других строк, которые нужно выделить.
  2. Щелкните правой кнопкой мыши один из заголовков строк. Появится раскрывающееся меню.
  3. Выберите Удалить.

2. Удаление пустых строк с помощью сочетания клавиш

Чтобы удалить несколько смежных пустых строк с помощью сочетания клавиш:

  1. Перетащите мышью заголовки строк или выберите заголовок первой строки, а затем щелкните заголовок последней строки, удерживая клавишу Shift.
  2. Нажмите Ctrl + – (знак минус в правом верхнем углу клавиатуры), чтобы удалить выбранные строки.

Чтобы удалить несколько несмежных пустых строк с помощью сочетания клавиш:

  1. Чтобы выбрать несмежные строки, щелкните заголовок первой строки, а затем, удерживая клавишу Ctrl, щелкните заголовки других строк, которые нужно выделить.
  2. Нажмите Ctrl + – (знак минус в правом верхнем углу клавиатуры), чтобы удалить выбранные строки.

3. Удаление пустых строк путем сортировки

Легкий способ удалить пустые строки — отсортировать данные так, чтобы пустые строки отображались внизу, а затем их можно было игнорировать.

Чтобы удалить пустые строки путем сортировки:

  1. Выберите весь диапазон данных (а не только столбец, который нужно отсортировать).
  2. Перейдите на вкладку "Данные" на ленте.
  3. Выберите «Сортировка» в группе «Сортировка и фильтр». Появится диалоговое окно. Если у вас есть строка заголовка, выберите Мои данные имеют заголовки.
  4. В поле «Сортировать по» выберите поле или столбец с пробелами, которые вы хотите удалить, а затем выберите соответствующий параметр сортировки (например, AZ, от меньшего к большему, по возрастанию или убыванию), чтобы отобразить пробелы в нижней части набора данных.
  5. Нажмите "ОК". Пустые строки теперь будут отображаться в нижней части набора данных, и их можно будет игнорировать.

Ниже показано диалоговое окно "Сортировка":

4. Удаление пустых строк с помощью функции "Перейти к специальному" для выделения пустых строк

Отличный способ удалить пустые строки в диапазоне данных – использовать функцию "Переход к специальному".

В диалоговом окне "Перейти к специальному" отображаются следующие параметры:

Чтобы найти и удалить пустые строки с помощью функции "Перейти к специальному":

  1. Выберите один столбец, в котором есть пустые ячейки (мы предполагаем, что остальная часть строки пуста). Если есть конфиденциальные данные выше или ниже списка, которые вы не хотите удалять, выберите ячейки в столбце от первой ячейки в диапазоне до последней ячейки в диапазоне (вы можете щелкнуть в первой ячейке и Shift- щелкните в последней ячейке).
  2. Нажмите Ctrl + G. Появится диалоговое окно "Перейти".
  3. Нажмите «Специальный», чтобы открыть диалоговое окно «Перейти к специальному». Кроме того, вы можете щелкнуть вкладку "Главная" на ленте, а затем выбрать "Перейти к специальному" в раскрывающемся меню "Найти и выбрать".
  4. Выберите «Пробелы» в диалоговом окне «Перейти к специальному» и нажмите «ОК». Excel выберет все пустые ячейки в выбранном диапазоне.
  5. Щелкните правой кнопкой мыши одну из выделенных пустых ячеек и выберите "Удалить". Появится диалоговое окно.
  6. Выбрать всю строку.
  7. Нажмите "ОК".

Диалоговое окно "Удалить" выглядит следующим образом:

Хотя вы также можете использовать команду "Найти" для поиска пробелов, гораздо проще использовать "Перейти к специальному".

5. Удаление пустых строк с помощью фильтрации

Вы также можете удалить пустые строки с помощью фильтрации (традиционно называемой автофильтрацией).

Чтобы удалить пустые строки с помощью фильтра:

  1. Выберите диапазон ячеек, включающий все данные в наборе данных (включая пустые строки).
  2. Перейдите на вкладку "Данные" на ленте.
  3. Выберите «Фильтр» в группе «Сортировка и фильтр». Кроме того, вы можете нажать Ctrl + Shift + L. Рядом с именами полей появятся стрелки.
  4. Нажмите стрелку рядом с названием поля с пустыми ячейками в строках, которые вы хотите удалить.
  5. Отключите или снимите флажок «Выбрать все».
  6. Выберите «Пробелы». Скорее всего, вам придется прокрутить страницу вниз, чтобы выбрать «Пробелы».
  7. Выберите заголовки отфильтрованных строк, щелкнув заголовок первой строки и, удерживая клавишу Shift, щелкнув заголовок последней строки.
  8. Щелкните правой кнопкой мыши один из выбранных заголовков. Появится раскрывающееся меню.
  9. Выберите Удалить строку.
  10. Перейдите на вкладку "Данные" на ленте и выберите "Очистить" в группе "Сортировка и фильтр", чтобы отменить фильтрацию.

В приведенном ниже примере поле "Реклама" отфильтровано для отображения пустых значений:

Теперь вы сможете сортировать, фильтровать и создавать сводные таблицы со списком.

Подпишитесь, чтобы получать больше статей, подобных этой

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

Пустые ячейки — это неплохо, если вы намеренно оставляете их в правильных местах из эстетических соображений.А вот пустые ячейки в неправильных местах, конечно, нежелательны. К счастью, есть относительно простой способ удаления пробелов в Excel, и через мгновение вы узнаете все подробности этой техники.

Как удалить пустые ячейки в Excel

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

С резервной копией, сохраненной в месте сохранения, выполните следующие действия, чтобы удалить пустые ячейки в Excel:

Вот и все. Вы успешно удалили пробелы в своей таблице:

Когда нельзя удалять пустые ячейки, выбирая пробелы

Техника Перейти к специальному > Пробелы прекрасно работает для одного столбца или строки. Он также может успешно удалять пустые ячейки в диапазоне независимых строк или столбцов, как в приведенном выше примере. Однако это может нанести ущерб структурированным данным. Чтобы этого не произошло, будьте очень осторожны при удалении пробелов в рабочих листах и ​​помните о следующих предостережениях:

1. Удалить пустые строки и столбцы вместо ячеек

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

2. Не работает для таблиц Excel

Невозможно удалить какие-либо отдельные ячейки в таблице Excel (в отличие от диапазона), вам разрешено удалять только целые строки таблицы. Или вы можете сначала преобразовать таблицу в диапазон, а затем удалить пустые ячейки.

3. Может повредить формулы и именованные диапазоны

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

Как извлечь список данных, игнорируя пробелы

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

С исходным списком в A2:A11 введите приведенную ниже формулу массива в C2, нажмите Ctrl + Shift + Enter, чтобы завершить ее правильно, а затем скопируйте формулу вниз еще на несколько ячеек. Количество ячеек, в которые вы копируете формулу, должно быть равно или больше количества элементов в вашем списке.

Формула для извлечения непустых ячеек:

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$11, МАЛЕНЬКИЙ(ЕСЛИ(НЕ(НЕПУСТО($A$2:$A$11)), СТРОКА($A$1:$A$10),""), СТРОКА (А1))),"")

На следующем снимке экрана показан результат:

Как работает формула

Сложная на первый взгляд, при ближайшем рассмотрении логика формулы проста. На простом английском языке формула в C2 выглядит следующим образом: вернуть первое значение в диапазоне A2:A11, если эта ячейка не пуста. В случае ошибки вернуть пустую строку ("").

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

У вас есть функция ИНДЕКС, возвращающая значение из $A$2:$A$11 на основе указанного номера строки (не реального номера строки, а относительного номера строки в диапазоне). В более простом сценарии мы могли бы поместить INDEX($A$2:$A$11, 1) в C2, и это принесло бы нам значение в A2. Проблема в том, что нам нужно учесть еще 2 вещи:

  • Убедитесь, что A2 не пуст.
  • Возвращает второе непустое значение в C3, третье непустое значение в C4 и т. д.

Обе эти задачи выполняются функцией МАЛЕНЬКИЙ(массив,k):

МАЛЕНЬКИЙ(ЕСЛИ(НЕ(ПУСТО($A$2:$A$11)), СТРОКА($A$1:$A$10),""), СТРОКА(A1))

В нашем случае аргумент массив генерируется динамически следующим образом:

  • NOT(ISBLANK($A$2:$A$11)) определяет, какие ячейки в целевом диапазоне не являются пустыми, и возвращает для них TRUE, в противном случае FALSE. Полученный массив ИСТИНА и ЛОЖЬ отправляется на логическую проверку функции ЕСЛИ.
  • IF оценивает каждый элемент массива TRUE/FALSE и возвращает соответствующее число для TRUE и пустую строку для FALSE:
    IF(, ROW($A$1:$A$10),"")

ROW($A$1:$A$10) требуется только для возврата массива чисел от 1 до 10 (поскольку в нашем диапазоне 10 ячеек), из которого IF может выбрать число для значений TRUE.

В результате мы получаем массив, а наша сложная МАЛЕНЬКАЯ функция преобразуется в вот такую ​​простую:

Как видите, аргумент массив содержит только номера непустых ячеек (заметьте, это взаимное расположение элементов в массиве, т.е. A2 — элемент 1, A3 — элемент 2 и т. д.).

В аргументе k мы помещаем ROW(A1), что указывает функции НАИМЕНЬШИЙ возвращать наименьшее число из 1. Из-за использования относительной ссылки на ячейку номер строки увеличивается с шагом 1, когда вы копируете формулу вниз. Таким образом, в C3 k изменится на ROW(A2), и формула вернет номер второй непустой ячейки и т. д.

Однако на самом деле нам не нужны непустые номера ячеек, нам нужны их значения. Итак, мы движемся дальше и вкладываем функцию МАЛЕНЬКИЙ в аргумент номер_строки функции ИНДЕКС, заставляя ее возвращать значение из соответствующей строки в диапазоне.

В качестве последнего штриха мы заключаем всю конструкцию в функцию ЕСЛИОШИБКА, чтобы заменить ошибки пустыми строками. Ошибки неизбежны, потому что вы не можете знать, сколько непустых ячеек находится в целевом диапазоне, поэтому вы копируете формулу в большее количество ячеек.

Учитывая вышеизложенное, мы можем построить эту общую формулу для извлечения значений без учета пробелов:

Где "диапазон" – это диапазон ваших исходных данных. Обратите внимание, что ROW($A$1:$A$10) и ROW(A1) являются постоянными частями и никогда не меняются, независимо от того, где начинаются ваши данные и сколько ячеек они содержат.

Как удалить пустые ячейки после последней ячейки с данными

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

Как найти последнюю использованную ячейку на листе

Чтобы перейти к последней ячейке на листе, содержащей данные или форматирование, щелкните любую ячейку и нажмите Ctrl + End .

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

Удалить ячейки после последней ячейки с данными

Чтобы очистить все содержимое и форматирование после последней ячейки с данными, сделайте следующее:

  1. Нажмите заголовок первого пустого столбца справа от данных и нажмите Ctrl + Shift + End . При этом будет выбран диапазон ячеек между вашими данными и последней использованной ячейкой на листе.
  2. На вкладке Главная в группе Редактирование нажмите Очистить >Очистить все. Или щелкните правой кнопкой мыши выделение и нажмите «Удалить…» >Весь столбец:

Проверьте используемый диапазон, чтобы убедиться, что теперь он содержит только ячейки с данными и не содержит пробелов. Если сочетание клавиш Ctrl + End снова выбирает пустую ячейку, сохраните книгу и закройте ее. Когда вы снова откроете лист, последняя использованная ячейка должна быть последней ячейкой с данными.

Совет. Учитывая, что Microsoft Excel с 2007 по 2019 год содержит более 1 000 000 строк и более 16 000 столбцов, вы можете уменьшить размер рабочей области, чтобы пользователи не могли непреднамеренно вводить данные в неправильные ячейки. Для этого вы можете просто удалить пустые ячейки из их представления, как описано в разделе Как скрыть неиспользуемые (пустые) строки и столбцы.

Вот как вы удаляете пробел в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Читайте также: