Предотвращение сбоя или зависания Excel при выборе ячеек в больших книгах.

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

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

Почему Excel зависает или аварийно завершает работу при выборе ячеек в больших файлах?

Никто, даже Microsoft, не может сказать вам точную причину, по которой Excel зависает и перестает работать при одновременном выборе больших объемов данных. Однако вот типичный сценарий, с которым столкнулись многие пользователи Excel:

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

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

Как избежать зависания и сбоя Excel при выделении ячеек

Ключом к предотвращению сбоя Excel при выборе ячеек в больших наборах данных является использование условного форматирования вместо Excel Find для выделения ячеек на основе определенных критериев. Вот пошаговое руководство:

Шаг 1: Выделите ячейки. Начните с создания нового правила условного форматирования, чтобы выделить ячейки, соответствующие вашим критериям. Например, выделим все ячейки, содержащие звездочку

Выделите целевые ячейки с условным форматированием.

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

Вот как можно отсортировать данные по цвету:
На вкладке «Главная» нажмите «Сортировка и фильтр» и выберите параметр «Пользовательская сортировка…». Появится диалоговое окно Сортировка.
В раскрывающемся списке Столбец выберите целевой столбец.
В раскрывающемся списке «Сортировать по» выберите «Цвет ячейки».

Сортировка ячеек по цвету.

Кончик. Как восстановить исходный порядок строк на листе результатов после сортировки, если в вашей таблице отсутствует выделенный столбец, например «Номер» или «Идентификатор»? Просто перед сортировкой создайте вспомогательный столбец с последовательными номерами. Это можно легко сделать с помощью функции автозаполнения Excel или функции ПОСЛЕДОВАТЕЛЬНОСТЬ.

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

Фильтровать ячейки по цвету.

Шаг 3: Сделайте свой выбор. Теперь, когда ваши данные аккуратно отсортированы или отфильтрованы по цвету, а нужные значения сгруппированы, вы можете легко выбирать их или выполнять различные действия, такие как копирование, форматирование или перенос их на другой лист, не беспокоясь о сбое Excel.

Например, чтобы выбрать цветные ячейки, отсортированные внизу столбца, просто щелкните первую цветную ячейку и нажмите комбинацию клавиш Ctrl + Shift + End, чтобы расширить выбор до последней использованной ячейки.

Выберите отфильтрованные ячейки.

Безошибочный способ выбора ячеек на больших листах разных цветов

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

Примечание. Инструменты обрабатывают только один столбец за раз. Результат отображается в отдельном столбце, который автоматически вставляется справа от столбца, в котором осуществлялся поиск.

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

Ultimate Suite for Excel предлагает набор полезных функций для работы со строками и подстроками. Параметр «Извлечь подстроки» особенно полезен, когда вам нужно найти и извлечь все значения, содержащие определенный символ или текст (подстроку). Вот как это сделать:
Введите критерии поиска в пустую ячейку(и) на текущем или новом листе.
На вкладке «Данные Ablebits» в группе «Текст» выберите параметр «Извлечь подстроки» в меню «Инструменты для подстрок».
В поле «Источник» укажите диапазон, в котором вы хотите выполнить поиск.
В поле Диапазон подстроки укажите ячейку или диапазон, содержащий критерии поиска.
При необходимости добавьте разделитель в поле Разделить с помощью.
Для поиска с учетом регистра установите флажок С учетом регистра.

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

Все найденные значения вынесены в отдельный столбец.

Найдите данные в определенных позициях в строках с помощью инструментов Regex.

Для тех, кто знаком с регулярными выражениями, наши инструменты Regex станут настоящим благом. А для новичков мы создали шпаргалку по Excel Regex, чтобы облегчить процесс обучения.

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

Чтобы найти данные, соответствующие определенному шаблону, выполните следующие действия:
Перейдите на вкладку Ablebits и в группе «Текст» нажмите «Инструменты регулярных выражений».
Выберите столбец для поиска значений. Выбранный диапазон будет отображаться в самом верхнем поле на панели «Инструменты регулярных выражений».
- В поле «Регулярное выражение» введите регулярное выражение. Например, чтобы сопоставить звездочки, расположенные в разных позициях строки, вы можете использовать следующие шаблоны:[*] ^
- – соответствует звездочке в начале строки.[*].+
- [*].+ — соответствует звездочке в середине строки.
- [*] $ — соответствует звездочке в конце строки.
– соответствует звездочке в любой позиции.
Выберите вариант «Сопоставить» или «Извлечь». Первый будет отмечать записи, соответствующие/не соответствующие шаблону, метками ИСТИНА/ЛОЖЬ, а второй будет извлекать совпадающие значения в отдельный столбец.