Удаление строк из большого отфильтрованного диапазона в Microsoft Excel может занять много времени, временно перевести программу в состояние «не отвечает» или — в худшем случае — привести к полному сбою Excel. Однако выполнение одного простого шага позволяет Excel продолжать работать без сбоев.
Почему удаление отфильтрованных строк приводит к сбою Excel
Предположим, вы хотите навести порядок в наборе данных из 50,000 XNUMX товаров, удалив строки, содержащие снятые с производства товары.
Чтобы следовать по мере чтения этого руководства, загрузите бесплатную копию книги Excel, используемой в примере. После того, как вы нажмете на ссылку, вы найдете кнопку загрузки в правом верхнем углу экрана.
Один из способов выполнить эту задачу — использовать кнопки фильтров, которые появляются по умолчанию при форматировании данных в виде таблицы Excel. В качестве альтернативы, если ваши данные не отформатированы как таблица Excel, выберите любую ячейку в диапазоне и нажмите Ctrl+Shift+L, чтобы вручную добавить кнопки фильтров.
Сначала нажмите кнопку фильтра в столбце «Статус», снимите флажок «Активно» и нажмите «ОК», чтобы в таблице отображались только снятые с производства товары.
Затем выберите одну ячейку в диапазоне и нажмите Ctrl+A, чтобы выбрать весь диапазон. Затем щелкните правой кнопкой мыши выбранный диапазон и нажмите Delete > Entire Sheet Row. Это удалит только отображаемые строки, то есть вы сможете позже очистить фильтры, чтобы снова отобразить активные элементы.
Однако на этом этапе Excel может предупредить вас, что выполнение операции может занять много времени.
И это правда — когда я нажал «ОК», Excel завис примерно на 42 секунды, включая переход в состояние «не отвечает», прежде чем, наконец, снова активировался и отобразил предполагаемый результат.
Более того, когда я нажал Ctrl+Z, чтобы отменить операцию, Excel полностью вылетел, и мне пришлось закрыть и перезапустить программу.
Это происходит потому, что большинство удаленных строк не находятся непосредственно рядом друг с другом. Действительно, если посмотреть на заголовки строк с левой стороны данных, можно увидеть не непрерывный набор меток строк, поскольку отфильтрованные данные занимают все остальные строки.
В результате, когда Excel собирается удалить отображаемые строки, ему приходится проходить по каждой группе смежных ячеек (также известной как область) одну за другой. Так, в этом случае он удалит строку 4, затем строки с 12 по 14, затем строку 16 и т. д. Всего в этих данных 11,873 11,873 области, поэтому Excel должен выполнить операцию удаления XNUMX XNUMX раза!
Сначала отсортируйте данные, чтобы предотвратить сбой Excel
Чтобы предотвратить сбой Excel при удалении строк из большого отфильтрованного набора данных, просто сначала отсортируйте соответствующий столбец.
Однако, прежде чем сделать это, стоит подумать о том, как вы хотите, чтобы ваши данные были представлены после удаления соответствующих строк. В этом примере продукты перечислены в том порядке, в котором они были впервые добавлены в инвентарь, поэтому важно сохранить этот порядок в долгосрочной перспективе.
Чтобы это произошло, добавьте еще один столбец с названием «Индекс» и введите 1, 2 и 3 в три верхние ячейки этого нового столбца.
Как только вы введете заголовок столбца в столбец D, отформатированная таблица Excel расширится на один столбец вправо.
Затем выберите эти три ячейки и дважды щелкните маркер заполнения в правом нижнем углу выделения, чтобы применить эти индексные номера к оставшимся ячейкам в диапазоне.
Затем нажмите кнопку фильтра в столбце «Статус» и выберите «Сортировать от А до Я» или «Сортировать от Я до А». При нажатии «ОК» элементы с одинаковым статусом группируются вместе.
Теперь снова нажмите кнопку фильтра, снимите флажок «Активно» и нажмите «ОК», чтобы в таблице отображались только снятые с производства товары.
Обратите внимание, что все снятые с производства продукты теперь находятся в соседних строках. Это означает, что когда вы удалите эти строки на следующем шаге, Excel придется обрабатывать только одну область, а не 11,873 XNUMX области, которые пришлось бы обрабатывать, если бы вы сначала не отсортировали данные.
В результате, если выделить все ячейки в диапазоне (Ctrl+A), щелкнуть правой кнопкой мыши по выбранному диапазону и нажать «Удалить» > «Всю строку листа», Excel выполнит операцию менее чем за секунду.
Теперь, когда операция завершена, нажмите кнопку фильтра в столбце «Состояние», очистите фильтры и нажмите «ОК», чтобы снова отобразить активные элементы.
Наконец, верните данные в исходный порядок, отсортировав таблицу по столбцу «Индекс», и удалите этот столбец, если он вам больше не нужен.
Знание того, что может вызвать замедление Excel, может избавить вас от разочарования в программе. Например, слишком много энергозависимых функций или ненужное переформатирование листа заставляют Excel (и ваш компьютер) работать больше, чем нужно, поэтому избегайте этого, где это возможно!
