Формулы и регулярные выражения.

Автор — Алексей А. Романенко


Введение

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

Формулы

Формула — это выражение, которое помещается в ячейку табличного процессора (OpenOffice Calc, Microsoft Excel). Результат вычисляется и пользователь видит непосредственно результат. Пользователь может конечно видеть и ошибку, если ввел формулу неправильно, или допустил иную ошибку. Кто-то может подумать, что сейчас пойдет речь о каком-нибудь языке программирования и будет прав. Но могу Вас заверить, что этот язык очень прост и его может легко освоить человек с истино гуманитарным образованием. Тем более, что каждый элемент формулы, хорошо документирован.

Все формулы начинаются со знака равно (=), после чего идет любое математическое выражение. Например, набрав в ячейке таблицы «=1+1» и нажав «Enter» в этой ячейке вы увидите цифру «2», что действительно является результатом сложения двух единиц.

Помимо чисел можно использовать ссылки на значения в других ячейках. Введем в первом столбце числа от 1 до 10, а во втором столбце в первой ячейке напишем «=A1*2» и нажмем «Enter». Если взяться за квадратик в нижнем правом углу выделенной ячейки (той, в которой мы ввели формулу) и потянуть за него вниз, то эту формулу можно распространить на все остальные ячейки второго столбца.







Обратите внимание, что пока Вы вводите формулу, или просто встаете на ячейку с формулой, то все ссылки на другие ячейки подсвечиваются. Обратите так же внимание, что в ячейке B2 формула будет «=A2*2». Т.е. табличный процессор сам определяет какую формулу надо подставить на основе довольно примитивного правила: если Вы тянете за квадратик вниз или вбок, то и все используемые в формуле ссылки также смещаются в ту же сторону.

Если меняется значение в ячейке, то пересчитываются все ячейки, которые в формулах на нее ссылались. Так одна моя знакомая один раз ввела формулу расчета стоимости товара на сайте совместных покупок с учетом комиссии, доставки и теперь вводит только стоимость товара, которая указана на сайте, и сразу видит результат.

А что делать, если ячейка, на которую есть ссылка в формуле, должна быть зафиксирована? Например, у Вас стоимость товара рассчитывается исходя из стоимости у.е. Разумней же ввести это значение один раз. Для фиксации столбца или строки используется символ доллара ($), помещенный перед буквой и\или цифрой в имени ячейки. Например, «=A1*$C$20». При этом куда бы Вы не растаскивали эту формулу, ссылка на ячейку C20 будет зафиксирована.

Теперь Вы легко можете построить таблицу умножения.



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



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



Пример 1. Обращение в письме.

Напомню, что на первом уроке мы делали письмо-приглашение. Такие письма лучше начинать словами «Уважаемый Иван Иванович» или «Дорогой Михаил Петрович», т.е. с обрашения по имени отчеству. При этом первое слово в обращении также зависит от пола того, к кому мы обращаемся. В нашей же базе данных отдельного обращения по имени и отчеству не было, впрочем, и поля под пол мы тоже не заводили.

Как можно выделить требуемое обращение к человеку, если есть только его ФИО целиком? Да очень просто. Необходимо убрать фамилию, а затем по отчеству определить пол получателя.

Для того, чтобы убрать фамилию надо в ФИО найти первый пробел и всю строку, начиная с символа, следующего за пробелом, и до конца строки поместить в новую ячейку. Или, найти первый пробел и удалить в строке все с начала и до данного пробела включительно. Первый и второй вариант формул будет выглядеть следующим образом:


Вариант 1.


Вариант 2.

В примерах функция FIND() ищет первое вхождение искомой строки, MID() - вырезает подстроку, REPLACE() - производит замену подстроки на новую строку.

Для сравнения эти формулы в локализованном MS Excel выглядят так:

  • =ПСТР(A20;НАЙТИ(" ";A20; 1)+1;ДЛСТР(A20))

  • =ЗАМЕНИТЬ(A4;1;НАЙТИ(" ";A4; 1);"")

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

И так, нам остается из ФИО выделить две последние буквы, можно и три, и сравнить их на равенство «вна» или «вич». Выглядеть все будет вот так:



Здесь функция EXACT() сравнивает две строки, а функция IF() выбирает один из двух вариантов, в зависимости от истинности или ложности условия.

Опять же в Excel:

  • =ЕСЛИ(СОВПАД(ЗАМЕНИТЬ(A4;1;ДЛСТР(A4)-2;"");"ич"); "Уважаемый"; "Уважаемая")

Вот собственно и все. Даете теперь новым столбцам правильные имена и вставляете в шаблоны писем ссылки на эти поля.


Пример 2. Календарь расходов.

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

Было время, когда я вел журнал расходов. Искать какие-то программы, устанавливать их було лень, а табличный процессор всегда был под рукой. Под каждый месяц заводился отдельный лист, в каждом листе в первой колонке вводились даты (вводилась одна дата, а остальные получались путем растяжения ячейки, как вначале урока), в первой строку вводились разделы (еда, проезд, дом и пр.), для каждой колонки вводилась формула подсчета суммы и общая сумма за месяц. Работы на две минуты. Для красоты не доставало только выходные подсветить цветом. Порывшись в списке функций OpenOffice Calc нашел функцию STYLE(), которая как утверждалось позволяла применить нужный стиль. В Microsoft Excel такой функции нет.

Чтобы можно было использовать эту функцию, надо завести стиль. В OpenOffice новые стили можно завести в диалоговом окне «Styles and Formatting» (F11 или в меню «Format» → «Styles and Formatting»). Далее все просто: в зависимости от условия применяем тот, или иной стиль. Формула:

=CHOOSE(WEEKDAY(A9);"Вс";"Пн";"Вт";"Ср";"Чт";"Пт";"Сб")&T(IF((WEEKDAY(A9) = 1) OR (WEEKDAY(A9)=7);STYLE("Blue");STYLE("Default")))



Остается отметить, что для применения стиля к текстовым строкам необходимо подключать стиль через «&T(STYLE(стиль))», для численных выражений достаточно стиль прибавить как значение - «+STYLE(стиль)».

Есть еще такое понятие, как условное форматирование, но это не относиться к теме формул и поэтому об этом в одной из будущих лекций.

Пример 3. Ведомости.

Порой возникает необходимость переносит из некоторой базы данных поля в другую. При этом это приходиться делать с завидной популярностью. Например при формировании коммерческих предложений или заполнении ведомостей. И важно при этом не сделать ошибки. Формулы могут помочь и здесь.

Предположим, что необходимо заполнить ведомость по сотрудникам какого-то отдела и в ведомости должны быть не только ФИО, но и дата рождения и зарплата. Если база данных вот такая



то ведомость можно заполнять, вводя только идентификатор человека. Остальное будет заполнено за Вас. В английских версиях функция называется «VLOOKUP», а в русских «ВПР». Последний вариант мало о чем говорит.



Обратите внимание, что можно ссылаться на ячейки в других листах таблицы. В приведенном примере диапазон ячеек берется с листа «Sheet2».


Отладка формул

Когда записывается длинное выражение, то не исключена возможность пропустить точку с запятой, которыми разделяются операнды или забыть одну из скобок. Газами же искать ошибку порой очень проблематично. В офисных пакетах предлагается несколько вариантов отладки. В OpenOffice Calc:

  • Вывод результата по мере заполнения формулы

  • Отображение структуры формулы

В Microsoft Office предлагается только первый вариант.



Раскладка выражения по структуре помогает найти «забытые» скобки.

Более простой способ, на мой взгляд — это собирать формулу по частям. Для примера представленного выше можно было, например, в отдельном столбце вывести все окончания отчеств, а затем использовать значения этого столбца для выбора обращения.

Регулярные выражения

Когда был написан заголовок этого раздела у меня появилось смешанное чувство. Вроде я использую регулярные выражения постоянно в своей работе, а вот где простой пользователь, которую большую часть времени проводит в MS Word\Excel или раскладывая пасьянсы, может с ними столкнуться? И ведь вспомнил же...

В данном разделе я не ставлю задачу научить Вас писать и читать регулярные выражения типа такого: «<([A-Z][A-Z0-9]*)\b[^>]*>(.*?)</\1> »

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

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

В регулярных выражениях используются специальные символы ('*', '?', '+' и т.д.) Подробно о регулярных выражениях можно почитать тут http://ru.wikipedia.org/wiki/Регулярные_выражения

Скажите, как часто бывал так, что вы потеряли файл, который был на вашем компьютере, а названья файла Вы помните только приблизительно? Это первы момент, когда вам регулярные выражения могут пригодиться. Правда в несколько упрощенном виде. Для поиска файла открываем проводник, выбираем наиболее вероятное место расположения файла или директории. Можно, конечно, выбрать и весь компьютер, но вряд ли Вы будете сохранять Ваши документы в папку к Windows или «Program Files». Далее в строке поиска вводим шаблон имени, или просто имя. В шаблоне могут присутствовать спецсимволы «*», которая говорит, что в этом месте может быть любое количество любых допустимых символов, и «?» - один любой их допустимых символов.



Еще с регулярными выражениями можно столкнуться при работе в OpenOffice Writer. Поиск и замену по тексту можно проводить с учетом регулярных выражений. Здесь регулярные выражения раскрываются в полной мере и порой позволяют творить чудеса. :) Например,
«\<word1[ \t]+word2\>» ищет два слова (word1 и word2), которые располагаются рядом. При этом могут быть разделены любым количеством пробелов.

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

Заключение

Надеюсь, что мне удалось показать, что использование формул является довольно простым делом. Главное знать, что Вам необходимо. В заключение хочу отметить, что без практики применения все знания улетучиваются гораздо быстрее, чем они получены. Так что пробуйте применять полученные знания на практике.


До новых встреч!
С уважением,
Алексей А. Романенко