Загрузка...Как удалить лишние строки в excel
Очистка лишнего форматирования ячеек на листе
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .
Форматирование ячеек на листе позволяет выделить нужные сведения, чтобы их было легко заметить, но при этом форматирование неиспользуемых ячеек (особенно целых строк и столбцов) может привести к быстрому росту размера файла рабочей книги. Если у вас открыт избыточно отформатированный лист, то это может замедлить работу не только Excel, но и снизить общую производительность компьютера. Также из-за лишнего форматирования в Excel могут возникать проблемы при печати.
Используйте команду Clean Excess Cell Formatting (Удалить лишнее форматирование ячеек), которая доступна в Excel на вкладке Inquire (Запрос) в Microsoft Office 365 и Office профессиональный плюс 2013. Если вкладка Inquire (Запрос) в Excel недоступна, вот как можно включить ее:
Щелкните Файл > Параметры > Надстройки.
Выберите в списке Управление пункт Надстройки COM и нажмите кнопку Перейти.
В поле Надстройки COM установите флажок Inquire (Запрос) и нажмите кнопку ОК.
Теперь вкладка Inquire (Запрос) должна отображаться на ленте.
Важно: Перед очисткой лишнего форматирования ячейки мы рекомендуем создать резервную копию файла, так как иногда это может привести к увеличению размера файла, а отменить эти изменения невозможно.
Чтобы удалить лишнее форматирование на текущем листе:
На вкладке Inquire (Запрос) выберите команду Clean Excess Cell Formatting (Удалить лишнее форматирование ячеек).
Выберите очистку только открытого листа или очистку всех листов. После выполнения операции очистки нажмите кнопку Да для сохранения изменений на листах или кнопку Нет, чтобы отменить сохранение.
Влияние операции очистки на условное форматирование
При очистке лишнего форматирования из листа удаляются ячейки, расположенные после последней непустой ячейки. Например, если вы применили условное форматирование к целой строке, но ваши данные располагаются только до столбца V, условное форматирование может быть удалено из столбцов, расположенных за столбцом V.
Для получения дополнительных сведений об устранении других проблем, связанных с лишним форматированием, читайте в статье службы поддержки Майкрософт обнуление последней ячейки в Excel.
Удаление пустых строк в таблице Microsoft Excel
Таблицы, в которых присутствуют пустые строки, выглядят не очень эстетично. К тому же, из-за лишних строк навигация по ним может усложниться, так как придется прокручивать больший диапазон ячеек для перехода из начала таблицы в конец. Давайте выясним, какие существуют способы удаления пустых строк в программе Microsoft Excel, и как их убрать быстрее и проще.
Стандартное удаление
Самым известным и популярным способом удаления пустых строк, является использование контекстного меню программы Эксель. Чтобы убрать строки таким способом, выделяем диапазон ячеек, который не содержит данных, и кликаем правой кнопкой мыши. В раскрывшемся контекстном меню делаем переход по пункту «Удалить…». Можно не вызывать контекстное меню, а набрать на клавиатуре сочетание клавиш «Ctrl+-».
Появляется небольшое окошко, в котором нужно указать, что конкретно мы хотим удалить. Выставляем переключатель в позицию «строку». Жмем на кнопку «OK».
После этого, все строки выделенного диапазона будут удалены.
Как альтернативный вариант, можно выделить ячейки в соответствующих строках, и находясь во вкладке «Главная», кликнуть по кнопке «Удалить», которая расположена в блоке инструментов «Ячейки» на ленте. После этого, сразу произойдет удаление без дополнительных диалоговых окон.
Конечно, способ очень простой и известный. Но, является ли он самым удобным, быстрым и безопасным?
Сортировка
Если пустые строки расположены в одном месте, то их удаление будет довольно легким. Но, если они раскиданы по всей таблице, то их поиск и удаление может занять значительное время. В этом случае, должна помочь сортировка.
Выделяем всю табличную область. Кликаем по ней правой кнопкой мыши, и в контекстном меню выбираем пункт «Сортировка». После этого, появляется ещё одно меню. В нём нужно выбрать один из следующих пунктов: «Сортировка от А до Я», «От минимального к максимальному», или «От нового к старому». Какой именно из перечисленных пунктов будет находиться в меню, зависит от типа данных, которые размещены в ячейках таблицы.
После того, как вышеуказанная операция будет проделана, все пустые ячейки переместятся в самый низ таблицы. Теперь, мы можем удалить эти ячейки любым из тех способов, о которых шла речь в первой части урока.
Если критически важен порядок размещения ячеек в таблице, то перед тем как совершить сортировку, вставляем в середину таблицы ещё один столбец.
Все ячейки этого столбца нумеруем по порядку.
Затем, производим сортировку по любому другому столбцу, и удаляем перемещенные вниз ячейки, как уже описывалось выше.
После этого, чтобы вернуть порядок строк к тому, который уже был перед сортировкой, проводим сортировку в колонке с номерами строк «От минимального к максимальному».
Как видим, строки выстроились в прежнем порядке, исключая пустые, которые удалены. Теперь, нам осталось только удалить добавленный столбец с порядковыми номерами. Выделяем этот столбец. Затем кликаем по кнопке на ленте «Удалить». В раскрывшемся меню выбираем пункт «Удалить столбцы с листа». После этого, нужный столбец будет удален.
Применение фильтра
Ещё одним вариантом скрыть пустые ячейки является применение фильтра.
Выделяем всю область таблицы, и, располагаясь во вкладке «Главная», делаем клик по кнопке «Сортировка и фильтр», которая расположена в блоке настроек «Редактирование». В появившемся меню, делаем переход по пункту «Фильтр».
В ячейках шапки таблицы появляется характерный значок. Кликаем по этому значку в любом на ваш выбор столбце.
В появившемся меню, снимаем галочку с пункта «Пустые». Жмем на кнопку «OK».
Как видим, после этого, все пустые строки исчезли, так как они были отфильтрованы.
Выделение ячеек
Ещё один способ удаления использует выделение группы пустых ячеек. Чтобы использовать этот способ, сначала выделяем всю таблицу. Затем, находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая расположена на ленте в группе инструментов «Редактирование». В появившемся меню, кликаем по пункту «Выделение группы ячеек…».
Открывается окно, в котором переставляем переключатель в позицию «пустые ячейки». Жмем на кнопку «OK».
Как видим, после этого, все строки, содержащие пустые ячейки, выделились. Теперь жмем на уже знакомую нам кнопку «Удалить», расположенную на ленте в группе инструментов «Ячейки».
После этого, все пустые строки будут удалены из таблицы.
Важное замечание! Последний способ нельзя использовать в таблицах с перекрывающимися диапазонами, и с пустыми ячейкам, которые находятся в строках, где имеются данные. В этом случае, может произойти смещение ячеек, и таблица нарушится.
Как видим, существует несколько способов удаления пустых ячеек из таблицы. Каким именно способом лучше воспользоваться, зависит от сложности таблицы, и от того, как именно по ней разбросаны пустые строки (располагаются одним блоком, или перемешаны со строками, заполненными данными).
Отблагодарите автора, поделитесь статьей в социальных сетях.
Как удалить строки по условию?
Предположу, что почти каждый сталкивался с ситуацией, когда необходимо удалить только определенные строки: имеется большая таблица и необходимо удалить из неё только те строки, которые содержат какое-то слово (цифру, фразу). Для выполнения подобной задачи можно воспользоваться несколькими способами.
Способ первый:
Использовать встроенное средство Excel — фильтр. Сначала его необходимо «установить» на листе:
- Выделяем таблицу с данными, включая заголовки. Если их нет — то выделяем с самой первой строки таблицы, в которой необходимо удалить данные
- устанавливаем фильтр:
- для Excel 2003 : Данные—Фильтр—Автофильтр
- для Excel 2007-2010 : вкладка Данные (Data) —Фильтр (Filter)(или вкладка Главная (Home) —Сортировка и фильтр (Sort&Filter) —Фильтр (Filter) )
Теперь выбираем условие для фильтра:
- в Excel 2003 надо выбрать Условие и в появившейся форме выбрать непосредственно условие(«равно», «содержит», «начинается с» и т.д.), а напротив значение в соответствии с условием.
- Для 2007-2010 Excel нужно выбрать Текстовые фильтры (Text Filters) и либо сразу выбрать одно из предлагаемых условий, либо нажать Настраиваемый фильтр (Custom Filter) и ввести значения для отбора в форме
После этого удалить отфильтрованные строки. В 2007 Excel могут возникнуть проблемы с удалением отфильтрованных строк, поэтому рекомендую сначала так же прочитать статью: Excel удаляет вместо отфильтрованных строк — все?! Как избежать.
Способ второй:
применить код VBA, который потребует только указания значения, которое необходимо найти в строке и номер столбца, в котором искать значение.
Sub Del_SubStr() Dim sSubStr As String ‘искомое слово или фраза(может быть указанием на ячейку) Dim lCol As Long ‘номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim lMet As Long Dim arr sSubStr = InputBox(«Укажите значение, которое необходимо найти в строке», «Запрос параметра», «») If sSubStr = «» Then lMet = 0 Else lMet = 1 lCol = Val(InputBox(«Укажите номер столбца, в котором искать указанное значение», «Запрос параметра», 1)) If lCol = 0 Then Exit Sub lLastRow = ActiveSheet.UsedRange.Row — 1 + ActiveSheet.UsedRange.Rows.Count arr = Cells(1, lCol).Resize(lLastRow).Value Application.ScreenUpdating = 0 Dim rr As Range For li = 1 To lLastRow ‘цикл с первой строки до конца If -(InStr(arr(li, 1), sSubStr) > 0) = lMet Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If Next li If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub
Если значение sSubStr не будет указано, то будут удалены строки, ячейки указанного столбца которых, пустые.
Данный код необходимо поместить в стандартный модуль. Вызвать с листа его можно нажатием клавиш Alt + F8 , после чего выбрать Del_SubStr и нажать Выполнить. Если в данном коде в строке
If -(InStr(Cells(li, 1), sSubStr) > 0) = lMet Then
вместо = lMet указать <> lMet , то удаляться будут строки, не содержащие указанное для поиска значение. Иногда тоже удобно.
Но. Данный код просматривает строки на предмет частичного совпадения указанного значения. Например, если Вы укажете текст для поиска «отчет», то будут удалены все строки, в которых встречается это слово(«квартальный отчет», «отчет за месяц» и т.д.). Это не всегда нужно. Поэтому ниже приведен код, который будет удалять только строки, указанные ячейки которых равны конкретно указанному значению:
Sub Del_SubStr() Dim sSubStr As String ‘искомое слово или фраза(может быть указанием на ячейку) Dim lCol As Long ‘номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim arr sSubStr = InputBox(«Укажите значение, которое необходимо найти в строке», «Запрос параметра», «») lCol = Val(InputBox(«Укажите номер столбца, в котором искать указанное значение», «Запрос параметра», 1)) If lCol = 0 Then Exit Sub lLastRow = ActiveSheet.UsedRange.Row — 1 + ActiveSheet.UsedRange.Rows.Count arr = Cells(1, lCol).Resize(lLastRow).Value Application.ScreenUpdating = 0 Dim rr As Range For li = 1 To lLastRow ‘цикл с первой строки до конца If CStr(arr(li, 1)) = sSubStr Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If Next li If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub
Здесь так же, как и в случае с предыдущим кодом можно заменить оператор сравнения( Cells(li, lCol) = sSubStr ) с равно на неравенство( Cells(li, lCol) <> sSubStr ) и тогда удаляться будут строки, значения ячеек которых не равно указанному.
УДАЛЕНИЕ СТРОК НА ОСНОВАНИИ СПИСКА ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
Иногда бывают ситуации, когда необходимо удалить строки не по одному значению, а по нескольким. Например, если строка содержит или Итог или Отчет. Ниже приведен код, при помощи которого можно удалить строки, указав в качестве критерия диапазон значений.
Значения, которые необходимо найти и удалить перечисляются на листе с именем «Лист2». Т.е. указав на «Лист2» в столбце А(начиная с первой строки) несколько значений — они все будут удалены. Если лист называется иначе(скажем «Соответствия») в коде необходимо будет «Лист2» заменить на «Соответствия». Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.
Sub Del_Array_SubStr() Dim sSubStr As String ‘искомое слово или фраза Dim lCol As Long ‘номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim avArr, lr As Long Dim arr lCol = Val(InputBox(«Укажите номер столбца, в котором искать указанное значение», «Запрос параметра», 1)) If lCol = 0 Then Exit Sub Application.ScreenUpdating = 0 lLastRow = ActiveSheet.UsedRange.Row — 1 + ActiveSheet.UsedRange.Rows.Count ‘заносим в массив значения листа, в котором необходимо удалить строки arr = Cells(1, lCol).Resize(lLastRow).Value ‘Получаем с Лист2 значения, которые надо удалить в активном листе With Sheets(«Лист2») ‘Имя листа с диапазоном значений на удаление avArr = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With ‘удаляем Dim rr As Range For lr = 1 To UBound(avArr, 1) sSubStr = avArr(lr, 1) For li = 1 To lLastRow ‘цикл с первой строки до конца If CStr(arr(li, 1)) = sSubStr Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If DoEvents Next li DoEvents Next lr If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub
Чтобы код выше удалял строки не по точному совпадению слов, а по частичному(например, в ячейке записано «Привет, как дела?», а в списке есть слово «привет» — надо удалить, т.к. есть слово «привет»), то надо строку:
Четыре способа, как в Excel удалить пустые строки
Работая с огромным объемом данных в таблицах «Эксель», ненароком можно оставить пустые строки, что в некоторых случаях совсем недопустимо. В этой статье пойдет речь о том, как в Excel удалить пустые строки. Будет представлено четыре способа, которые помогут вам это сделать. Советуется дочитать статью до финала, чтобы в конце принять для себя решение, каким способом пользоваться, так как они в достаточной степени отличаются друг от друга.
Простое удаление
Для начала разберемся, как в Excel удалить пустые строки самым простым способом, который используют практически все неопытные пользователи, но в некоторых случаях именно такое удаление использовать лучше всего.
Итак, чтобы удалить лишние пустые строки в программе, вам необходимо:
- Изначально выделить ту область таблицы, которая содержит ненужные строки.
- Затем нажать правую кнопку мыши, чтобы вызвать контекстное меню.
- В списке надо нажать на строку «Удалить».
После этого появится окошко, в котором необходимо выбрать, что именно нужно удалить. В нашем случае это строки, значит, выбирайте пункт «Строку» и нажимайте «ОК». Теперь все лишние строки будут удалены из таблицы. Однако можно ускорить этот процесс, пропустив этапы по вызову контекстного меню. Для этого просто выделите ненужные строки и нажмите сочетание клавиш «CTRL+-«.
Также есть альтернатива. Вы можете после выделения строк перейти во вкладку «Главная» и там на панели инструментов из выпадающего списка «Удалить» выбрать пункт «Удалить строки с листа», что также довольно удобно.
Сортировка
Первый способ, как в Excel удалить пустые строки, мы разобрали. Однако он не очень удобен, если нужно удалить много строк, ведь выделять их придется довольно долго. Именно поэтому мы сейчас расскажем, как в Excel удалить пустые строки при помощи инструмента «Сортировка».
Итак, вам необходимо сделать следующее:
- Выделите всю таблицу. Для этого можно нажать кнопку «CTRL+A».
- Нажмите правую кнопку мыши на таблице и в меню кликните по строке «Сортировка».
- Из дополнительного меню вам необходимо выбрать один из пунктов, в соответствии с тем, какой тип данных находится в таблице.
Как только вы сделаете выбор, все пустые строки, которые были в таблице, переместятся в ее низ и их будет легче выделить и удалить, используя способ, который был приведен самым первым.
Использование фильтра
В Excel удалить повторяющиеся строки, которые пустые, можно при помощи фильтра. Много не разглагольствуя, перейдем непосредственно к самой инструкции:
- Изначально вам необходимо выделить область вашей таблицы. Для этого, зажав левую кнопку мыши в левом верхнем ее углу, перетащите ее в нижний правый.
- Далее перейдите во вкладку «Главная» и кликните по кнопке «Сортировка и фильтр», а в контекстном меню выберите «Фильтр».
- Как только вы это сделаете, в шапке столбцов появятся выдающие списки, вам необходимо развернуть один из них (любой).
- В меню, которое появилось, надо снять галку с пункта «Пустые» и нажать «ОК».
Вот и все, как видно, третий способ, как удалить лишние строки в Excel, намного удобнее, чем некоторые из предыдущих.
Выделение
Напоследок расскажем еще об одном интересном способе, как удалить из таблицы пустые строки. На сей раз будем использовать выделение ячеек, которое осуществляется с помощью специального инструмента. Но обо всем по порядку:
- Снова выделите всю таблицу, в которой находятся нужные вам данные и непосредственно пустые строки.
- Перейдите во вкладку «Главная» и нажмите по «Найти и выделить».
- В меню, которое появится, вам необходимо нажать на строку «Выделение группы ячеек».
- В появившемся меню, вам нужно установить переключатель на «пустые ячейки» и нажать кнопку «ОК».
Теперь все пустые строки выделены и вы можете их удалить. Для этого, как и в первом способе, нажмите на кнопку «Удалить» в панели инструментов вкладки «Главная».
Как удалить все пустые строки в Excel
В этой статье я объясню, почему удаление пустых строк в Excel при помощи выделить пустые ячейки > удалить строку – это плохая идея, и покажу Вам 2 быстрых и правильных способа, как удалить пустые строки не разрушая данные. Все эти способы работают в Excel 2013, 2010 и в более старых версиях.
Если Вы читаете эту статью, то, скорее всего, постоянно работаете в Excel с большими таблицами. Вы знаете, что пустые строки периодически появляются среди данных, ограничивая работу большинства инструментов Excel с таблицами (сортировка, удаление дубликатов, промежуточные итоги и так далее), не позволяя им правильно определять диапазон данных. И каждый раз приходится вручную определять границы, иначе итогом будет неверный результат и уйма потраченного времени на исправление ошибок.
Существует множество причин, почему появляются пустые строки. К примеру, Вы получили рабочую книгу Excel от другого человека или в результате экспорта из корпоративной базы данных, или ненужные данные в строках были удалены вручную. В любом случае, если Ваша цель – удалить все эти пустые строки и получить чистую и аккуратную таблицу, то следуйте простым шагам, приведённым ниже:
Никогда не удаляйте пустые строки, при помощи выбора пустых ячеек
Повсюду в интернете Вы можете встретить простой совет, якобы позволяющий удалить пустые строки:
- Выделите данные от первой до последней ячейки.
- Нажмите F5, чтобы открыть диалоговое окно Go to (Переход).
- В диалоговом окне нажмите кнопку Special (Выделить).
- В диалоговом окне Go to special (Выделить группу ячеек) отметьте пункт Blanks (Пустые ячейки) и нажмите ОК.
- Кликните правой кнопкой мыши по любой из выделенных ячеек и нажмите Delete (Удалить).
- В диалоговом окне Delete (Удаление ячеек) выберите Entire row (Строку) и нажмите ОК.
Это очень плохой способ, делайте так только с очень простыми таблицами с парой дюжин строк, которые помещаются на одном экране, а ещё лучше – не делайте так совсем! Главная причина – если строка с важными данными содержит хотя бы одну пустую ячейку, то удалена будет вся строка.
Например, у нас есть таблица клиентов, всего 6 строк. Мы хотим удалить строки 3 и 5, потому что они пустые.
Сделайте так, как предложено выше, и получите такой результат:
Строка 4 (Roger) тоже исчезла, потому что ячейка D4 в столбце Traffic source оказалась пуста 
Если Ваша таблица не велика, то Вы заметите потерю данных, но в реальных таблицах с тысячами строк Вы можете неосознанно удалить дюжины нужных строк. Если повезёт, Вы обнаружите потерю в течение нескольких часов, восстановите рабочую книгу из резервной копии и продолжите работу. А что если не повезёт, и у Вас не будет резервной копии?
Далее в этой статье я покажу Вам 2 быстрых и надёжных способа удалить пустые строки из листов Excel.
Удаление пустых строк при помощи ключевого столбца
Этот метод работает, если в Вашей таблице есть столбец, который помогает определить, пуст рассматриваемый столбец или нет (ключевой столбец). К примеру, это может быть ID покупателя или номер заказа, или что-то подобное.
Нам важно сохранить порядок строк, поэтому мы не сможем просто отсортировать таблицу по этому столбцу, чтобы переместить все пустые строки вниз.
- Выделите всю таблицу, от первой до последней строки (нажмите Ctrl+Home, а затем Ctrl+Shift+End).
- Добавьте автофильтр к таблице. Для этого на вкладке Data (Данные) нажмите кнопку Filter (Фильтр).
- Примените фильтр к столбцу Cust#. Для этого нажмите кнопку со стрелкой в заголовке столбца, снимите галочку в опции Select All (Выделить все), пролистайте вниз к концу списка (на практике этот список может быть весьма длинным) и поставьте галочку в строке Blanks (Пустые) в самом низу списка. Нажмите ОК.
- Выделите все отфильтрованные строки: нажмите Ctrl+Home, затем стрелку вниз, чтобы перейти к первой строке с данными, а затем нажмите Ctrl+Shift+End.
- Кликните правой кнопкой мыши по любой выделенной ячейке и из контекстного меню выберите Delete row (Удалить строку) или просто нажмите Ctrl+-(знак минус).
- В появившемся окне с вопросом Delete entire sheet row? (Удалить всю строку листа?) нажмите ОК.
- Очистите применённый фильтр: на вкладке Data (Данные) нажмите кнопку Clear (Очистить).
- Отлично! Все пустые строки полностью удалены, а строка 3 (Roger) по-прежнему на месте (сравните с результатом предыдущей попытки).
Удаление пустых строк в таблице без ключевого столбца
Воспользуйтесь этим способом, если в Вашей таблице присутствуют многочисленные пустые ячейки, разбросанные по разным столбцам, а Вам нужно удалить только те строки, которые не имеют ни одной ячейки с данными.
В этом случае у нас нет ключевого столбца, который бы помог определить пуста строка или нет. Поэтому, мы добавляем вспомогательный столбец к таблице:
- В конце таблицы добавьте столбец с именем Blanks и вставьте следующую формулу в первую ячейку столбца:
Эта формула, как и обещает её имя, подсчитывает пустые ячейки в заданном диапазоне. A2 и C2 – это соответственно первая и последняя ячейки текущей строки.
