Формули і регулярні вирази.

Автор - Олексій А. Романенко

У першому уроці ми зіткнулися з тим, що нам було потрібно звернення до кожного одержувача листа, потрібно його ім'я та по батькові, а не ПІБ цілком. У нашому прикладі ми руками ввели всі необхідні дані. У тому випадку у нас була невелика база даних і це було дозволено, а якщо таких записів сотні і тисячі. У цьому уроці я Вами розповім як за допомогою табличного процесора можна автоматизувати цей процес. І не тільки цей. На кількох прикладах я покажу, що використання формул є простим і захоплюючим справою.

Формула - це вираз, який поміщається в комірку табличного процесора (OpenOffice Calc, Microsoft Excel). Результат обчислюється і користувач бачить безпосередньо результат. Користувач може звичайно бачити і помилку, якщо ввів формулу неправильно, або допустив іншу помилку. Хтось може подумати, що зараз піде мова про яке-небудь мові програмування і буде правий. Але можу Вас запевнити, що ця мова дуже простий і його може легко освоїти людина з истино гуманітарною освітою. Тим більше, що кожен елемент формули, добре документований.

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

Крім чисел можна використовувати посилання на значення в інших осередках. Введемо в першому стовпці числа від 1 до 10, а в другому стовпці в першій клітинці напишемо «= A1 * 2» і натиснемо «Enter». Якщо взятися за квадратик в нижньому правому куті виділеної комірки (тієї, в якій ми ввели формулу) і потягнути за нього вниз, то цю формулу можна поширити на всі інші осередки другого шпальти.

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

Якщо змінюється значення в осередку, то перераховуються всі осередки, які в формулах на неї посилалися. Так одна моя знайома один раз ввела формулу розрахунку вартості товару на сайті спільних покупок з урахуванням комісії, доставки і тепер вводить тільки вартість товару, яка вказана на сайті, і відразу бачить результат.

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

Тепер Ви легко можете побудувати таблицю множення.

Крім математичних операцій в формулах можуть бути присутніми функції. Мабуть якщо не морочитися, то функції є на всі випадки життя. Це і логічні, математичні, фінансові, роботи з текстовими рядками, базами даних тощо. Під кожну з них є опис. Рекомендую Вам хоча б раз пробігтися по списку, щоб мати уявлення про надані можливості.

Формули можуть бути вкладені, але про це трохи пізніше. Залежно від локалізації офісного пакету функції можуть називатися по різному. Так, наприклад, в російськомовній версії MS Excel Мохно бачити функції як з латинських букв, як з літер російського алфавіту, так і упереміш. Підозрюю, що в кінцевому підсумку все зберігається виключно в латиниці, але, по крайней мере для меня такой зоопарк здається зайвим.

Нагадаю, що на першому уроці ми робили Лист на фірмовому бланку. Такі листи краще починати словами «Шановний Іван Іванович» або «Дорогий Михайло Петрович», тобто з обрашения на ім'я по батькові. При цьому перше слово в зверненні також залежить від статі того, до кого ми звертаємося. У нашій же базі даних окремого звернення на ім'я та по батькові не було, втім, і поля під підлогу ми теж не заводили.

Як можна виділити необхідну звернення до людини, якщо є тільки його ПІБ цілком? Та дуже просто. Необхідно прибрати прізвище, а потім по батькові визначити стать одержувача.

Для того, щоб прибрати прізвище треба в ПІБ знайти перший пробіл і весь рядок, починаючи з символу, наступного за пропуском, і до кінця рядка помістити в новий осередок. Або, знайти перший пробіл і видалити в рядку все з початку і до даного пробілу включно. Перший і другий варіант формул буде виглядати наступним чином:

Варіант 1.


Варіант 2.

У прикладах функція FIND () шукає перше входження шуканої рядка, MID () - вирізає подстроку, REPLACE () - робить заміну підрядка на новий рядок.

Для порівняння ці формули в локалізованому MS Excel виглядають так:

З виявленням статі одержувача листа теж все йде досить просто. У російській мові по батькові всіх чоловіків закінчуються на «вич», а жінок на «вна». З цього приводу в КВН-е була навіть жарт. Спроби знайти такі батькові, які б задовольняли цим правилом у мене успіхом не увінчалися. В інших мовах, напевно будуть інші критерії, але ми в даному випадку працюємо з російською мовою і тому приймемо дане правило.

І так, нам залишається з ПІБ виділити дві останні букви, можна і три, і порівняти їх на рівність «вна» або «вич». Виглядати все буде ось так:

Тут функція EXACT () порівнює два рядки, а функція IF () вибирає один з двох варіантів, в залежності від істинності чи хибності умови.

Знову ж в Excel:

Ось власне і все. Даєте тепер новим стовпцями правильні імена і вставляєте в шаблони листів посилання на ці поля.

За допомогою формул можна не тільки заповнювати значення полів, але при цьому ще виробляти і форматування документів, наприклад, розфарбовувати окремі поля.

Був час, коли я вів журнал витрат. Шукати якісь програми, встановлювати їх Було лінь, а табличний процесор завжди був під рукою. Під кожен місяць заводився окремий лист, в кожному аркуші в першій колонці вводилися дати (вводилася одна дата, а решта виходили шляхом розтягування комірки, як спочатку уроку), в першій рядок вводилися розділи (їжа, проїзд, будинок тощо.), Для кожної колонки вводилася формула підрахунку суми і загальна сума за місяць. Роботи на дві хвилини. Для краси не вистачало тільки вихідні підсвітити кольором. Порившись в списку функцій 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 (стиль)».

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

Часом виникає необхідність переносить з деякою бази даних поля в іншу. При цьому це доводиться робити із завидною популярністю. Наприклад при формуванні комерційних пропозицій або заповненні відомостей. І важливо при цьому не зробити помилки. Формули можуть допомогти і тут.

Припустимо, що необхідно заповнити відомість по співробітниках якогось відділу і в відомості повинні бути не тільки ПІБ, але і дата народження і зарплата. Якщо база даних ось така

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

Зверніть увагу, що можна посилатися на осередки в інших листах таблиці. У наведеному прикладі діапазон комірок береться з листа «Sheet2».

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

У Microsoft Office пропонується тільки перший варіант.

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

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

Коли був написаний заголовок цього розділу у мене з'явилося змішане почуття. Начебто я використовую регулярні вирази постійно в своїй роботі, а ось де простий користувач, яку велику частину часу проводить в MS Word \ Excel або розкладаючи пасьянси, може з ними зіткнутися? І адже згадав же ...

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

Мені хотілося задати лише напрямок, в якому Ви можете рухатися в плані підвищення ефективності своєї роботи.

Регулярні вирази - шаблони, які використовуються для перевірки відповідності текстового рядка деякого шаблоном і для виділення подстрок, які цього шаблону задовольняють.

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

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

Ще з регулярними виразами можна зіткнутися при роботі в OpenOffice Writer. Пошук і заміну по тексту можна проводити з урахуванням регулярних виразів. Тут регулярні вирази розкриваються в повній мірі і часом дозволяють творити чудеса. :) Наприклад,
«\ <Word1 [\ t] + word2 \>» шукає два слова (word1 і word2), які розташовуються поруч. При цьому можуть бути розділені будь-якою кількістю пробілів.

У Microsoft Word регулярних виразів мені знайти не вдалося, але там є пошук всіх словоформ або пошук слова за вимовою, що швидше за все через регулярні вирази і реалізується.

Сподіваюся, що мені вдалося показати, що використання формул є досить простою справою. Головне знати, що Вам необхідно. На закінчення хочу відзначити, що без практики застосування все знання випаровуються набагато швидше, ніж вони отримані. Так що пробуйте застосовувати отримані знання на практиці.