Типичная ошибка, которая встречается в электронных таблицах, связана с невидимыми символами пробелов. [1] В ячейке В2 (рис. 1) находится формула, берущая название цвета в ячейке В1 и возвращающая соответствующий код из таблицы D1:E6: =ВПР(B1;D1:E6;2;ЛОЖЬ).
Рис. 1. Формула поиска возвращает цвет, введенный в ячейку В1
Скачать заметку в формате Word или pdf, примеры в формате Excel
В следующем примере формула в ячейке В2 возвращает ошибку, указывая, что значение Красный не было найдено в таблице. Сотни и тысячи пользователей Excel потратили массу времени, чтобы понять, почему подобные операции не работают. Ответ прост: в ячейке D5 написано не «Красный», а «Красный », то есть за словом следует пробел. Для Excel это две разные последовательности символов.
Рис. 2. Формула поиска не может найти в таблице слово «Красный»
Если на рабочем листе тысячи текстовых записей, а вам нужно выполнять сравнения с применением этого текста, то вы, вероятно, захотите найти ячейки с лишними пробелами и исправить их. Под лишними пробелами понимается любое следующее содержимое текстовых записей:
- один ведущий пробел и более;
- один завершающий пробел и более;
- два и более последовательных пробела в самом тексте.
Один из способов идентификации таких ячеек связан с использованием условного форматирования. Чтобы настроить его, выполните следующие действия.
- Выделите все ячейки с текстом, в которых хотите применить условное форматирование.
- Выполните команду Главная —> Условное форматирование —> Создать правило, чтобы открыть диалоговое окно Создание правила форматирования (рис. 3).
- В верхней части этого окна выберите параметр Использовать формулу для определения форматируемых ячеек.
- В области Измените описание правила введите формулу: =D2<>СЖПРОБЕЛЫ(D2). Данная формула предполагает, что ячейка D2 является верхней левой ячейкой в диапазоне. Если это не так, замените адрес верхней левой ячейки, который вы указали в шаге 1.
- Нажмите кнопку Формат, чтобы отобразить диалоговое окно Формат ячеек, и выберите тип форматирования, которое хотите применить к ячейкам, содержащим лишние пробелы, — например, желтую заливку.
- Нажмите Ok два раза.
В результате во всем диапазоне, который вы выделили на шаге 1, ячейки с лишними пробелами будут выделены с помощью заданного вами форматирования, и вы сможете легко их найти и исправить (рис. 4).
Рис. 3. Параметры условного форматирования для выделения ячеек, содержащих лишние пробелы
Рис. 4. Условное форматирование выделило ячейки с лишними пробелами
Функция СЖПРОБЕЛЫ действует так, что формула, описанная в шаге 4, также применяет условное форматирование ко всем числовым ячейкам. Если в вашем диапазоне встречаются числа, используйте на шаге 4 формулу: =ЕСЛИ(НЕ(ЕНЕТЕКСТ(D2));D2<>СЖПРОБЕЛЫ(D2)). Кроме того, нужно иметь в виду, что функция СЖПРОБЕЛЫ не удаляет (и не воспринимает при условном форматировании) знак неразрывного пробела.
[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. — СПб.: Питер, 2014. — С. 183–185.
