Избавляемся от латиницы
Уважаемая редакция! Помогите решить такую задачу. У меня есть база данных, в которой находится как числовая, так и текстовая информация. Причем в тексте местами встречаются латинские буквы. Отличить на глаз русскую «о» от английской «о» практически невозможно. Из-за такой неразберихи неправильно работает сортировка, ошибается автофильтр, а про консолидацию данных в данном случае вообще говорить не приходится! Как можно решить эту проблему? Заранее признателен.
В. Фирсов, г. Харьков
Отвечает Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
Просмотрев список стандартных функций для работы с текстом в Excel, я не смог подобрать подходящий набор инструментов для решения вашей задачи. Проблема заключается в том, что для поиска латинских (или русских, без разницы) символов в тексте нужно просматривать строку по буквам. Одну конкретную букву из текстовой строки в Excel получить можно. А вот функции, которая циклически выбирает все символы из заданной строки и сравнивает их с образцом, в Excel нет. Но это не проблема, потому что есть язык VBA, на котором можно реализовать любую функцию, в том числе и нашу. Причем уйдет на это пять минут и буквально десять строк текста. Делаем так:
1) открываем документ, например базу данных «
БД», изображенную на рис. 1;2) вызываем «
Сервис → Макрос → Редактор Visual Basic» или нажимаем «Alt+F11». Появится окно редактора VBA;3) в левом верхнем углу окна редактора находим окошко с заголовком «
Project»;4) в этом окне подсвечиваем строку с надписью «
VBAProject(БД.xls)» (рис. 2 на с. 30);5) вызываем меню «
Insert → Module». В ветке «VBAProject(БД.xls)» появится строка «Modules», а в ней — элемент «Module1» (рис. 2);6) делаем двойной щелчок левой кнопкой мыши на «
Module1», чтобы подсветить его;7) в правой части окна редактора VBA печатаем такой текст:
Public Function IsLat(str As String)
str = LCase(str)
LatinAlphbet = "*[abcdefghijklmnopqrstuvwxyz]*"
If str Like LatinAlphbet Then
IsLat = True
Else
IsLat = False
End If
End Function
8) закрываем окно редактора VBA и возвращаемся к базе данных «
БД».Мы создали пользовательскую функцию с именем «
IsLat». В детали ее работы вдаваться не будем. Главное — понять, что она делает. В качестве исходного параметра функция получает строку (в тексте программы она называется «Str»). Программа просматривает каждый символ этой строки. Результатом ее работы будет значение «ИСТИНА», если в тексте «Str» есть хотя бы одна буква латинского алфавита. В противном случае функция «IsLat» вернет значение «ЛОЖЬ».Посмотрим, как воспользоваться нашей функцией. Делаем так:
1) открываем файл «
БД», в ячейку «G1» печатаем заголовок колонки (на рис. 3 это «РусЛат»);2) становимся на ячейку «
G1», вызываем Мастер функций через «Вставка → Функция…». Появится окно с перечнем функций;3) в списке «
Категория:» выбираем «Определенные пользователем»;4) в окошке «
Выберите функцию» находим «IsLat», щелкаем на ней левой кнопкой мыши;5) в качестве параметра указываем ячейку «
B2» базы «БД». Выбор адреса «B2» делаем щелчком мыши на рабочем листе;6) в окне «
Мастер функций» нажимаем «ОК»;7) копируем созданную функцию на всю высоту таблицы. Результат показан на рис. 3 в колонке «
G». Строки рабочего листа, в которых поле «Контрагент» содержит латинские символы, помечены в колонке «РусЛат» как «ИСТИНА». На рис. 3 это строки «3», «4», «9».Остается перепечатать эти фамилии и скопировать их по всей базе с использованием автофильтра. Как видите, ничего сложного.
Теперь один технический момент. Сейчас функция «
IsLat» будет доступна только в текущей рабочей книге. Чтобы убедиться в этом, сделайте так:1) закройте файл «
БД»;2) создайте новый документ;
3) выполните «
Вставка → Функция…», в перечне укажите категорию «Определенные пользователем». Функции «IsLat» в списке вы не обнаружите.Проблему решить очень просто: нужно поместить функцию в автоматически загружаемую библиотеку, например в файл «
Personal.xls». На рис. 2 этот файл есть в окошке «Project». Если у вас его нет, сделайте так:1) откройте документ;
2) выполните команду «
Сервис → Макрос → Начать запись». Появится окно, как на рис. 4 на с. 31;3) в поле «
Имя макроса» введите любое название (на рис. 4 это символ «Т»);4) нажмите «
ОК». Станет доступным лист Excel;5) щелкните на любой ячейке рабочего листа;
6) введите в эту ячейку любой символ или число;
7) выполните «
Сервис → Макрос → Остановить запись».Теперь вы можете вызвать редактор VBA, в окошке «
Project» будет строка «VBAProject(Personal.xls)». Все, что остается сделать, — это добавить модуль к этому проекту и ввести в него текст функции «IsLat». То есть теперь действия будут такими:1) открываем документ;
2) нажимаем «
Alt+F11»;3) в окне редактора VBA в окошке «
Project» находим «VBAProject(Personal.xls)»;4) вызываем меню «
Insert → Module». В ветке «VBAProject(Personal.xls)» появится строчка «Modules», а в ней — элемент «Module1»;5) выбираем «
Module1»;6) в правой части окна редактора VBA печатаем текст функции «
IsLat». Кстати, если в правой части окна есть текст созданного макроса, то его можно смело стереть, хотя это делать не обязательно;7) сохраняем файл из окна редактора VBA;
8) закрываем редактор и возвращаемся в Excel.
Теперь функция «
IsLat» будет доступна в любой рабочей книге.В принципе задачу мы решили. Но есть одно неудобство: мы видим строки, содержащие латинские символы, но не видим сами эти буквы. Нет ничего проще:
1) откройте уже знакомый редактор VBA («
Alt+F11»);2) выберите элемент «
Module1» для «VBAProject(Personal.xls)». В правой части окна вы увидите текст нашей функции «IsLat». Его мы не трогаем;3) в правой части окна редактора VBA перед функцией «
IsLat» (или после нее) введите такой текст (всего 10 строчек):Sub ShowLat()
For Each c In Selection
For i = 1 To Len(c)
If (Asc(Mid(c, i, 1)) >= 65 And Asc(Mid(c, i, 1)) <= 90) Or _
(Asc(Mid(c, i, 1)) >= 97 And Asc(Mid(c, i, 1)) <= 122) Then
c.Characters(Start:=i, Length:=1).Font.ColorIndex = 3
End If
Next i
Next c
End Sub
4) сохраните файл из окна VBA. Вернитесь в окно Excel;
5) в этом окне выделите колонку «
B» (в ней у нас записаны названия контрагентов);6) вызовите «
Сервис → Макрос → Макросы» или «Alt+F8». Появится окно «Макрос» (рис. 5);7) в этом окне выберите «
PERSONAL.XLS!ShowLat»;8) нажмите кнопку «
Выполнить». Латинские символы в колонке «B» станут красного цвета. Приятное зрелище…И последнее. Если вы пользуетесь макросом часто, имеет смысл организовать его вызов горячей клавишей. Для этого в окне «
Макрос» (рис. 5) щелкните кнопку «Параметры». В предложенном окне укажите букву, например «L», и нажмите «ОК». Теперь макрос «ShowLat» можно вызывать комбинацией «Ctrl+L».
Жду ваших вопросов, замечаний и предложений на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Удачной работы!