Теми статей
Обрати теми

Створюємо реєстр договорів

Редакція БК
Стаття

Створюємо реєстр договорів

 

img 1

Робота з документами забирає в бухгалтера левову частку корисного часу. Акти, кошториси, договори, службові записки — їх кількість постійно зростає. А єдиним інструментом для наведення ладу в цьому господарстві зазвичай залишаються… папки на диску. Такий підхід, безумовно, має право на життя. Ось тільки здебільшого він малоефективний. Особливо коли йдеться про групову обробку документів. У такій ситуації без ведення реєстру вже не обійтися. І тут саме час пригадати про можливості пакета MS Office, що пропонує всі необхідні інструменти для вирішення цього завдання. Про те, як ними скористатися щодо MS Office 2010, які переваги отримає бухгалтер від ведення реєстру документів, ми поговоримо в цій статті.

Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства

 

А для прикладу розробимо невелику систему для ведення реєстру договорів. Відразу зауважу: у межах однієї статті ми не зможемо охопити все різноманіття варіантів вирішення цього завдання. Документи в реальному житті бувають різні, та і цілі їх обробки можуть відрізнятися. Але основні принципи організації реєстру ми розглянемо, ключові прийоми реалізуємо на практиці. Після цього (а я в цьому абсолютно впевнений!) застосування їх для вирішення вашої конкретної проблеми не викличе труднощів.

Тепер із приводу того, чому саме MS Office? Сьогодні на ринку України представлено десятки програм для автоматизації документообігу — від найпростіших і до вишукано складних. Тут, на мій погляд, проблему потрібно поділити на дві частини. Коли йдеться про комплексну автоматизацію документообігу великого підприємства, без спеціальних програм часто не обійтися. І це зрозуміло. Але коли потрібно автоматизувати якусь частину своєї персональної роботи або вирішити конкретне локальне завдання, я б віддав перевагу офісним прикладним рішенням, і ось чому. Насамперед це дешевше, додаткових витрат у цьому випадку ніяких. Вам не знадобляться сторонні розробники. Виконавши всю роботу самостійно, ви досконально володітимете питанням та в будь-який момент перебудуєте свою систему під змінені умови або нові завдання. І, нарешті, найголовніше: інструменти для ведення реєстру документів в MS Office настільки прості, що користуватися ними зможе будь-який бухгалтер з базовими навичками роботи на комп’ютері. Тепер власне про завдання.

 

Що ми хочемо отримати

Отже, є бланк документа. Для прикладу я вибрав стандартний договір купівлі-продажу. Цей документ первісно зображено у форматі MS Word. Крім бланка, у нас є список контрагентів (покупців), для яких ми оформлятимемо договори купівлі-продажу. Список організовано як базу даних MS Excel, що періодично поповнюватиметься. Ми хочемо мати можливість швидко формувати і роздруковувати договір купівлі-продажу для будь-якого контрагента з нашого списку. Крім того, нам може знадобитися формувати і друкувати договори для цілої групи контрагентів із бази даних.

 

Що ми використовуватимемо

Для вирішення завдання ми застосуємо стандартні функції роботи з текстом програми Excel 2010, а також засоби організації розсилок Word 2010. Про можливості цих інструментів ми вже писали на сторінках нашого видання (наприклад, у «Б & К», 2009, № 6, № 23; 2011, № 3). Тому все, що нам зараз доведеться зробити, — це освіжити наші знання та комплексно застосувати їх для вирішення поставленого завдання в новій редакції MS Office. Розпочнемо.

 

Формуємо базу даних

Роботу починаємо з бланка договору купівлі-продажу, фрагмент якого показано на рис 1. Перше, що потрібно зробити, — це виділити в бланку всі частини договору, що змінюються. Зараз наша мета — з’ясувати, які атрибути договору змінюватимуться для різних контрагентів, а що залишатиметься незмінним. Це можна зробити безпосередньо на екрані, підсвітивши частини, що змінюються, яскравим фоном (як показано на рис. 1), або виконати все відразу на папері. Різниці немає жодної. Повторюю: нас поки що цікавить лише склад змінної інформації.

img 2

Закінчивши з цією справою, присвоїмо кожному атрибуту змінної частини договору коротке, але зрозуміле ім’я. Ці імена й будуть заголовками бази даних у програмі Excel, де ми сформуємо реєстр. Виходячи зі складу документа «Договір купівлі-продажу» наш реєстр складатиметься з 23 полів, які перелічено в табл. 1.

Тепер, знаючи склад полів реєстру, формуємо в Excel базу даних, фрагмент якої показано на рис. 2. Ці дані в нашому прикладі я розташував на листі з ім’ям «Реестр».

Важливо! У реєстрі потрібно вказати всі варіанти змінної інформації точно в тому вигляді, як вона наводиться в договорі.

Наприклад, назва посади фігурує в базі даних двічі. Один раз — з маленької літери в родовому відмінку. Саме в такому вигляді посаду покупця записано в першому абзаці документа. Удруге в реєстрі назву посади зазначено в називному відмінку та з великої літери (поле «Должность2»). У такому вигляді цей елемент використовується в завершальному підписі у кінці договору.

 

Таблиця 1. Поля реєстру документів

Найменування поля

Адреса комірки

Призначення

1

2

3

Пр

A1

Робоча колонка, з документом вона ніяк не пов’язана

№ п/п

B1

Порядковий номер запису в реєстрі

№ дог.

С1

Номер договору

Дата

D1

Дата договору

Предприятие

E1

Назва контрагента (покупця)

Адрес

F1

Юридична адреса покупця

Тел.

G1

Контактний телефон покупця

eMail

H1

Адреса електронної пошти покупця

Р/С

I1

Номер розрахункового рахунка покупця

Банк

J1

Назва банку, де зареєстровано рахунок покупця

МФО

K1

МФО банку покупця

ЕДРПОУ

L1

Код ЄДРПОУ покупця

Должность_1

M1

Посада першої особи на підприємстві покупця в родовому відмінку

Должность_2

N1

Те саме, але в називному відмінку

ФИО_1

O1

Прізвище, ім’я та по батькові першої особи від покупця в родовому відмінку

ФИО_2

P1

Те саме, але в називному відмінку

Основание

Q1

Назва документа, на підставі якого покупець підписав договір

Товар, услуга

R1

Найменування товару в договорі купівлі-продажу

ЕдИзм

S1

Одиниця виміру товару

Цена

T1

Ціна за одиницю товару

Кол.

U1

Кількість товару, що передається за договором

Сумма

V1

Сума договору

НДС

W1

Сума ПДВ

 

Створивши базу даних, заповнюємо реєстр відомостями про контрагентів (наприклад, як показано на рис. 2). Вихідна інформація для нашого прикладу готова. Можна починати роботу над текстом договору.

img 3

Зараз текст документа зображено у форматі програми Word. Цей документ нам ще стане у пригоді — ми скористаємося ним для організації розсилки. А поки що наше завдання скромніше. Я хочу з наявної бази даних роздрукувати один конкретний договір. Це можна зробити таким чином. У робочій колонці «Пр» я поставлю символ «*» — ознака друкування договору для відповідного контрагента. Після цього на окремому листі я хочу отримати текст договору, куди Excel автоматично підставить усі атрибути контрагента з відповідного рядка. Щоб реалізувати такий алгоритм на практиці, ми спочатку маємо перенести на окремий лист Excel текст документа, а підставляння значень з бази здійснити згодом за допомогою функцій «ВПР()». Подивимося, як це реалізувати в Excel 2010.

 

ФОРМУЄМО БЛАНК ДОГОВОРУ В MS EXCEL

Спочатку перенесемо дані з програми Word на лист MS Excel. Для цього виконуємо такі дії:

1) відкриваємо документ з реєстром, додаємо новий робочий лист;

2) двічі клацаємо лівою кнопкою миші по ярличку нового листа, щоб увійти до режиму перейменування;

3) уводимо назву листа (я вибрав ім’я «Дог»);

4) викликаємо «Файл печать» — відкриється вікно попереднього перегляду;

5) натискуємо на «Esc» — закриваємо вікно попереднього перегляду. Цю дію ми виконали тільки для того, щоб на листі з’явилася пунктирна лінія для позначення правої межі друкованого листа;

6) через буфер обміну переносимо текст із програми «Word» до комірок Excel. Дані потрібно переносити по рядках, причому вставляти текст я пропоную виключно до комірок колонки «А». При цьому потрібно стежити за тим, щоб рядки документа не виходили за праву межу друкованого листа (знаходилися в межах області друку);

7) текст із підписами наприкінці документа відразу розміщуємо в окремих колонках. Слово «Директор» (підпис від продавця) записуємо до комірки «A24». Комірку «B24» залишаємо порожньою — це місце для самого підпису. Прізвище директора від підприємства-продавця (В. М. Петров) записуємо до комірки «C24»;

8) аналогічно оформляємо область із підписами від покупця. У нашому прикладі текст «от Покупателя» записано до комірки «E23». Слово «Ген. директор» знаходиться в комірці «E24», а прізвище «В.И. Авилов» займає комірку «G24»;

9) підбираємо ширину колонок, орієнтуючись на значення в області підписів завершальної частини договору, тобто ширина колонки «А» має бути такою, щоб у ній помістилося слово «Директор». Ширина колонок «B» та «F» має бути достатньою для фізичних підписів сторін тощо;

10) в останню чергу включаємо до тексту документа дані про найменування товару, обсяг продажів, одиницю виміру, ціну та суму договору. У прикладі на рис. 3 я розмістив ці значення в комірках починаючи з «D7» і до «D12». Такий вибір було зроблено виключно виходячи з ширини колонок, що склалася, на робочому листі. Адже поки що ці дані нам потрібні лише для того, щоб оцінити реальний розмір документа і завчасно підігнати його під ширину сторінки;

11) через вікно форматування включаємо для комірок «B24» і «F24» нижню межу;

12) виділяємо комірки «A1:G1», форматуємо заголовок (рядок « ДОГОВОР…») по центру виділення;

13) послідовно виділяємо всі назви пунктів першого рівня та форматуємо їх по центру виділення щодо колонок «A:G»;

14) виділяємо заголовки третього рівня (п.п. 1.1.1, 1.1.2 і так далі);

15) викликаємо вікно «Формат ячеек» на закладці «Выравнивание» вибираємо формат «по левому краю (отступ)» і вводимо значення відступу «2»;

16) виділяємо напівжирним зображенням ключові елементи нашого договору. Зовнішній вигляд документа в першому наближенні показано на рис. 3.

img 4

Ми створили на робочому листі «Дог» тільки шаблон — заготівку у вигляді тексту договору купівлі-продажу. Тепер наше завдання — доповнити його формулами, щоб підставити до документа дані стосовно конкретного контрагента. Нагадаю, що рядок з параметрами контрагента, для якого формується документ, ми позначимо в колонці «Пр» листа «Реестр» символом «*».

Для вирішення завдання ми скористаємося функцією «ВПР()». Як працює ця функція, ми розглянемо на прикладі третього рядка документа з номером договору «6а-09/11» (комірка «С4»). Зараз у заголовку документа (рис. 1) знаходиться текст «ДОГОВОР № 1 от 9/1/2011». Замість номера «1» ми повинні підставити значення з бази даних (лист «Реестр») із колонки «D». При цьому номер рядка ми визначимо з тієї умови, що в колонці «A» його позначено символом «*». На рис. 2 цей символ знаходиться в комірці «A4». Формула для підставляння номера договору матиме такий вигляд: «=ВПР("*";Реестр!A:X;3)». У формулі три параметри: спочатку — шуканий текст (у нашому випадку — це символ «*»). Потім блок, з якого «ВПР()» зробить підставляння. У нашому випадку функція переглядатиме першу ліворуч колонку блока, тобто колонку «А». Як тільки в одній із комірок цієї колонки функція виявить символ «*», вона поверне як результат значення з того ж рядка, але зі зсувом на 3 колонки праворуч. Для прикладу на рис 2 (символ «*» знаходиться в комірці «A4») результатом роботи формули буде значення «6а-09/11».

У принципі формулу можна залишити як є. Але якщо в колонці «A» бази даних не буде жодного символу «*», функція «ВПР()» відпрацює з помилкою: результатом її роботи буде значення «Н/А». На робочому листі воно виглядає, відверто скажемо, безглуздо. Щоб удосконалити формулу, ми можемо проаналізувати помилку, скориставшись функціями «ЕОШИБКА()» і «ЕСЛИ()». За їх допомогою ми перевіримо результат пошуку символу «*» функцією «ВПР». Якщо пошук неуспішний, як результат ми повернемо до комірки слово «нет». Інакше до комірки запишемо результат пошуку. Тоді вираз для підставляння номера договору до документа матиме такий вигляд: «=”ДОГОВОР № “ & ЕСЛИ(ЕОШИБКА(ВПР(“*”;Реестр! A:X;3));” Нет “;ВПР(“*”;Реестр!A:X;3))». Подивимося на роботу цієї формули докладніше. Функція «ВПР(“*”;Реестр!A:X;3)» знаходить у базі значення номера договору для контрагента, позначеного символом «*» у колонці «А». Функція «ЕОШИБКА()» аналізує результат пошуку. Якщо пошук успішний, функція поверне значення «ЛОЖЬ» (немає помилки). Інакше вона поверне значення «ИСТИНА». Цей результат перевіряється функцією «ЕСЛИ()». Коли пошук успішний і результат «ЕОШИБКА(ВПР(“*”;Реестр!A:X;3))» дорівнює «ЛОЖЬ», функція «ЕСЛИ()» повертає до комірки значення «ВПР(“*”;Реестр!A:X;3))». Інакше до комірки буде записано слово «нет».

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

 

Таблиця 2. Формули для підставляння значень до тексту договору

Адреса комірки

Формула

Призначення

1

2

3

A1

="ДОГОВОР № "&ЕСЛИ(ЕОШИБКА(ВПР("*";Реестр!A:X;3));" нет ";ВПР("*";Реестр!A:X;3))

Заголовок з номером договору

G2

=ЕСЛИ(ЕОШИБКА(ВПР("*";Реестр!A:X;4)); " нет "; ТЕКСТ(ВПР("*";Реестр!A:X;4); "ДД/ ММ /ГГ"))

Дата договору

A3

=" ООО «Стрим» в дальнейшем «ПРОДАВЕЦ» в лице директора Петрова Р.М. с одной стороны, и "&"ООО «Техресурсы»"&" в дальнейшем «ПОКУПАТЕЛЬ» в лице "&ВПР("*";Реестр!A:X;13)&" "&ВПР("*";Реестр!A:X;15)&", действующего на основании Устава, с другой стороны заключили Договор о нижеследующем:"

Перший параграф з даними керівників та найменуваннями підприємств

D7

=ЕСЛИ(ЕОШИБКА(ВПР("*";Реестр!A:X;18));" нет "; ВПР("*";Реестр!A:X;18))

Назва товару

D8

=ЕСЛИ(ЕОШИБКА(ВПР("*";Реестр!A:X;19));" нет "; ВПР("*";Реестр!A:X;19))

Одиниця виміру

D9

=ЕСЛИ(ЕОШИБКА(ВПР("*";Реестр!A:X;20));" нет "; ВПР("*";Реестр!A:X;20))

Ціна за одиницю

D10

=ЕСЛИ(ЕОШИБКА(ВПР("*";Реестр!A:X;21));" нет "; ВПР("*";Реестр!A:X;21))

Кількість

D11

=ЕСЛИ(ЕОШИБКА(ВПР("*";Реестр!A:X;22));" нет "; ВПР("*";Реестр!A:X;22))

Вартість партії

D12

=ЕСЛИ(ЕОШИБКА(ВПР("*";Реестр!A:X;23));" нет "; ВПР("*";Реестр!A:X;23))

Сума ПДВ

A19

="6.2.2. ПОКУПАТЕЛЬ: "&ВПР("*";Реестр!A:X;5)&" , "&ВПР("*";Реестр!A:X;6)&" ,"&ВПР("*";Реестр!A:X;7)&", e-mail: "&ВПР("*";Реестр!A:X;7)&" , р/с "&ВПР("*";Реестр!A:X;9)&" в "&ВПР("*";Реестр!A:X;10)&", "&ВПР("*";Реестр!A:X;11)&", код ЕГРПОУ: "& ВПР("*";Реєстр!A:X;12)&"."

Юридична адреса, телефон, платіжні реквізити контрагента

E24

=ЕСЛИ(ЕОШИБКА(ВПР("*";Реестр!A:X;14));" нет "; ВПР("*";Реестр!A:X;14))

Посада того, хто підписав договір

G24

=ЕСЛИ(ЕОШИБКА(ВПР("*";Реестр!A:X;16));" нет "; ВПР("*";Реестр!A:X;16))

П. І. Б. того, хто підписав договір

 

Основну частину документа ми створили. Невирішеною залишилася одна проблема — запис суми договору прописом. Для цього на окремому листі ми побудуємо таблицю, де за допомогою формул вирішимо це завдання.

 

СУМА ПРОПИСОМ У ДОКУМЕНТІ EXCEL

Алгоритм запису числа прописом ми реалізуємо за допомогою вбудованих функцій MS Excel. Усі розрахунки оформимо на окремому листі. У нашому прикладі я назвав його «Проп». Далі виконуємо такі дії:

1) переходимо на лист «Проп»;

2) у комірці «A1» друкуємо слово «Сумма». До комірки «D1» вводимо вираз «=ВПР(“*”;Реестр!$A:$X;22)». Ця формула вставляє на робочий лист суму договору, який позначено символом «*» у базі на листі «Реестр». Посилання на суму договору ми поставили, тепер розберемося з алгоритмом.

Щоб написати число прописом, нам потрібно кожну цифру цього числа рознести по окремих комірках. Для цього ми послідовно будемо ділити вихідне число на «10» і кожного разу відкидати дробову частину за допомогою функції «ОТБР()». У результаті ми зможемо визначити кількість одиниць, десятків, сотень, тисяч тощо у вихідному числі. Завершивши таку обробку числа, ми проаналізуємо його порозрядно та напишемо результат прописом. Подивимося, як це виглядає на практиці. Почнемо із завершальної частини таблиці:

1) залишаючись на листі «Проп», заповнюємо блок «A32:G41» значеннями, як зображено на рис. 4. Це будуть текстові заготовки для зображення прописом;

img 5

2) до комірки «B11» вводимо формулу «=ОТБР(D1)». Результат її роботи — ціла частина числа з комірки «D1», тобто ціла частина від суми за договором;

3) починаючи з «A12», заповнюємо лист формулами відповідно до табл. 3.

У результаті роботи цих формул у комірках «B12:B27» ми отримаємо вихідне число, розподілене на розряди, а у блоці «E12:E27» — текстову назву кожного розряду нашої суми.

 

Таблиця 3. Формули для обробки цілої частини суми договору

Адреса комірки

Формула

1

2

A12

=ОТБР(A13/10)

A13

=ОТБР(A14/10)

B13

=ОТБР(ПРАВСИМВ(A13))

C13

=B13

E13

=ЕСЛИ(B13=1;E41;ЕСЛИ(B13=2;G33;ЕСЛИ(B13=3;G34;ЕСЛИ(B13=4;G35;ЕСЛИ (B13=5;G36;

ЕСЛИ(B13=6;G37;ЕСЛИ(B13=7;G38; ЕСЛИ(B13=8;G39;G40))))))))

A14

=ОТБР(A15/10)

B14

=ОТБР(ПРАВСИМВ(A14))

C14

=ЕСЛИ(B14=1;"";B14)

E14

=ЕСЛИ(ИЛИ(C14=0;B14=1);"";ЕСЛИ(B14=2;E33;ЕСЛИ(B14=3;E34;ЕСЛИ(B14=4;E35;

ЕСЛИ(B14=5;E36;ЕСЛИ(B14=6;E37; ЕСЛИ(B14=7;E38;ЕСЛИ(B14=8;E39;E40))))))))

A15

=ОТБР(A17/10)

B15

=ОТБР(ПРАВСИМВ(A15))

C15

=ЕСЛИ(B14=1;B15+10;ЕСЛИ(B15=0;0;B15))

D15

=ЕСЛИ(И(C15>9;C15<16);ЕСЛИ(C15=10;D32;ЕСЛИ(C15=11;D33;ЕСЛИ(C15=12;D34;

ЕСЛИ(C15=13;D35;ЕСЛИ(C15=14;D36; ЕСЛИ(C15=15;D37;))))));"")

E15

=ЕСЛИ(B15=1;A32;ЕСЛИ(B15=2;A33;ЕСЛИ(B15=3;A34;ЕСЛИ(B15=4;A35;ЕСЛИ(B15=5;A36;

ЕСЛИ(B15=6;A37;ЕСЛИ(B15=7;A38; ЕСЛИ(B15=8;A39;A40))))))))

E16

=B15+B14*10+B13*100

A17

=ОТБР(A18/10)

B17

=ОТБР(ПРАВСИМВ(A17))

C17

=B17

E17

=ЕСЛИ(B17=1;E41;ЕСЛИ(B17=2;G33;ЕСЛИ(B17=3;G34;ЕСЛИ(B17=4;G35;ЕСЛИ (B17=5;G36;

ЕСЛИ(B17=6;G37;ЕСЛИ(B17=7;G38; ЕСЛИ(B17=8;G39;G40))))))))

A18

=ОТБР(A19/10)

B18

=ОТБР(ПРАВСИМВ(A18))

C18

=ЕСЛИ(B18=1;"";B18)

E18

=ЕСЛИ(ИЛИ(C18=0;B18=1);"";ЕСЛИ(B18=2;E33;ЕСЛИ(B18=3; 34; ЕСЛИ(B18=4;E35;

ЕСЛИ(B18=5;E36; ЕСЛИ(B18=6;E37; ЕСЛИ(B18=7;E38;ЕСЛИ(B18=8;E39;E40))))))))

A19

=ОТБР(A21/10)

B19

=ОТБР(ПРАВСИМВ(A19))

C19

=ЕСЛИ(B18=1;B19+10;ЕСЛИ(B19=0;0;B19))

D19

=ЕСЛИ(И(C19>9;C19<16);ЕСЛИ(C19=10;D32;ЕСЛИ(C19=11;D33;ЕСЛИ(C19=12;D34;ЕСЛИ(C19=13;D35;ЕСЛИ(C19=14;D36; ЕСЛИ(C19=15;D37;))))));"")

E19

=ЕСЛИ(B19=1;A32;ЕСЛИ(B19=2;A33;ЕСЛИ(B19=3;A34;ЕСЛИ(B19=4;A35;

ЕСЛИ(B19=5;A36;ЕСЛИ(B19=6;A37;ЕСЛИ(B19=7;A38; ЕСЛИ(B19=8;A39;A40))))))))

E20

=B19+B18*10+B17*100

A21

=ОТБР(A22/10)

B21

=ОТБР(ПРАВСИМВ(A21))

C21

=B21

E21

=ЕСЛИ(B21=1;E41;ЕСЛИ(B21=2;G33;ЕСЛИ(B21=3;G34;ЕСЛИ(B21=4;G35;ЕСЛИ (B21=5;G36;

ЕСЛИ(B21=6;G37;ЕСЛИ(B21=7;G38; ЕСЛИ(B21=8;G39;G40))))))))

A22

=ОТБР(A23/10)

B22

=ОТБР(ПРАВСИМВ(A22))

C22

=ЕСЛИ(B22=1;"";B22)

E22

=ЕСЛИ(ИЛИ(C22=0;B22=1);"";ЕСЛИ(B22=2;E33;ЕСЛИ(B22=3;E34;ЕСЛИ(B22=4;E35;

ЕСЛИ(B22=5;E36;ЕСЛИ(B22=6;E37; ЕСЛИ(B22=7;E38;ЕСЛИ(B22=8;E39;E40))))))))

A23

=ОТБР(A25/10)

B23

=ОТБР(ПРАВСИМВ(A23))

C23

=ЕСЛИ(B22=1;B23+10;ЕСЛИ(B23=0;0;B23))

D23

=ЕСЛИ(И(C23>9;C23<16);ЕСЛИ(C23=10;D32;ЕСЛИ(C23=11;D33;ЕСЛИ(C23=12;D34;

ЕСЛИ(C23=13;D35;ЕСЛИ(C23=14;D36; ЕСЛИ(C23=15;D37;))))));"")

E23

=ЕСЛИ(B23=1;B32;ЕСЛИ(B23=2;B33;ЕСЛИ(B23=3;A34;ЕСЛИ(B23=4;A35;

ЕСЛИ(B23=5;A36;ЕСЛИ(B23=6;A37;ЕСЛИ(B23=7;A38; ЕСЛИ(B23=8;A39;A40))))))))

E24

=B21*100+B22*10+B23

A25

=ОТБР(A26/10)

B25

=ОТБР(ПРАВСИМВ(A25))

C25

=B25

E25

=ЕСЛИ(B25=1;E41;ЕСЛИ(B25=2;G33;ЕСЛИ(B25=3;G34;ЕСЛИ(B25=4;G35;ЕСЛИ(B25=5;G36;ЕСЛИ(B25=6;G37;ЕСЛИ(B25=7;G38;ЕСЛИ(B25=8;G39;G40))))))))

A26

=ОТБР(A27/10)

B26

=ОТБР(ПРАВСИМВ(A26))

C26

=ЕСЛИ(B26=1;"";B26)

E26

=ЕСЛИ(ИЛИ(C26=0;B26=1);"";ЕСЛИ(C26=2;E33;ЕСЛИ(C26=3;E34;ЕСЛИ(C26=4;E35;

ЕСЛИ(C26=5;E36;ЕСЛИ(C26=6;E37;ЕСЛИ(C26=7;E38;ЕСЛИ(C26=8;E39;E40))))))))

A27

=B11

B27

=ОТБР(ПРАВСИМВ(A27))

C27

=ЕСЛИ(B26=1;B27+10;ЕСЛИ(B27=0;0;B27))

D27

=ЕСЛИ(И(C27>9;C27<16);ЕСЛИ(C27=10;D32;ЕСЛИ(C27=11;D33;ЕСЛИ(C27=12;D34;

ЕСЛИ(C27=13;D35;ЕСЛИ(C27=14;D36; ЕСЛИ(C27=15;D37;))))));"")

E27

=ЕСЛИ(B27=1;A32;ЕСЛИ(B27=2;A33;ЕСЛИ(B27=3;A34;ЕСЛИ(B27=4;A35;ЕСЛИ(B27=5;A36;

ЕСЛИ(B27=6;A37;ЕСЛИ(B27=7;A38;ЕСЛИ(B27=8;A39;A40))))))))

E28

=B25*100+B26*10+B27

 

Досі йшлося тільки про цілу частину числа, тобто про гривні. Переходимо до копійок. Для цього до комірки «A29» вводимо формулу: «=ОКРУГЛ(100*(D1-B11);0)» Переводити в текстове зображення ми її не будемо. При формуванні пропису до цього значення ми просто додамо текст «коп.».

Отже, усі дані для формування числа прописом у нас є. Залишається об’єднати інформацію та отримати остаточний результат. Виконуємо такі дії:

1) стаємо на комірку «A10» (рис. 4), уводимо формулу «=СЦЕПИТЬ(ЕСЛИ(A29=0;”00”;A29);” коп.”)». Це кількість копійок у сумі за договором;

2) до комірки «A9» вводимо формулу «=СЦЕПИТЬ(ЕСЛИ(B25=0;””;E25); ЕСЛИ(B26=0;””;ЕСЛИ(C27<20; ЕСЛИ(C27<16;ЕСЛИ(C27<10;E26;D27);F27);E26));ЕСЛИ(B27=0;””;ЕСЛИ(НЕ(B26=1);E27;””));F28)». Це кількість гривень прописом;

3) формули в комірках «A6», «A7», «A8», по суті, такі самі, але вони обробляють розряди сотень, тисяч та мільйонів гривень у вихідному числі. А самі вирази в цих комірках мають вигляд, як у табл. 4;

 

Таблиця 4. Формули для формування суми прописом

Адреса комірки

Формула

A6

=СЦЕПИТЬ(ЕСЛИ(B13=0;"";E13);ЕСЛИ(B14=0;""; ЕСЛИ(C15<20;ЕСЛИ(C15<16;

ЕСЛИ(C15<10;E14;D15);F15);E14)); ЕСЛИ(B15=0;"";ЕСЛИ(НЕ(B14=1);E15;""));F16)

A7

=СЦЕПИТЬ(ЕСЛИ(B17=0;"";E17);ЕСЛИ(B18=0;"";ЕСЛИ(C19<20; ЕСЛИ(C19<16;

ЕСЛИ(C19<10;E18;D19);F19);E18)); ЕСЛИ(B19=0;"";ЕСЛИ(НЕ(B18=1);E19;""));F20)

A8

=СЦЕПИТЬ(ЕСЛИ(B21=0;"";E21);ЕСЛИ(B22=0;"";ЕСЛИ(C23<20; ЕСЛИ(C23<16;

ЕСЛИ(C23<10;E22;D23);F23);E22)); ЕСЛИ(B23=0;"";ЕСЛИ(НЕ(B22=1);E23;""));F24)

 

4) до комірки «A4» вводимо: «=СЦЕПИТЬ(A6;A7;A8;A9;A10)». Це сума прописом з маленької літери;

5) до комірки «A3» вводимо вираз «=ПОДСТАВИТЬ(A4;СИМВОЛ(КОДСИМВ(A4));ПРОПНАЧ(СИМВОЛ (КОДСИМВ (A4)));1)». Це — сума числа прописом із заголовної літери.

Результат з комірки «A3» ми й повернемо до бланка договору. Для цього виконуємо такі дії:

1) переходимо на лист «Дог»;

2) стаємо на комірку «E11»;

3) вводимо формулу: «=ЕСЛИ(ЕОШИБКА(Проп!A3);» нет «;»(«&Проп!A3&»).»)». Бланк договору готовий.

 

Що в нас вийшло

Щоб скористатися плодами нашої праці, виконуємо такі дії:

1) переходимо на лист «Реестр», знаходимо в списку потрібного контрагента;

2) у відповідному рядку в колонці «А» ставимо символ «*»;

3) переходимо на лист «Дог» — на ньому можна побачити примірник договору з параметрами вибраного контрагента;

4) друкуємо отриманий документ стандартними засобами Excel 2010.

Нам залишається розглянути ситуацію, коли в реєстрі позначено декілька контрагентів. Виконуємо такі дії:

1) стаємо на комірку «А1» листа «Реестр». Викликаємо меню «Главная»;

2) у групі «Стили» клацаємо по іконці «Условное форматирование». З меню, що відкрилося, вибираємо «Создать правило…». З’явиться вікно «Создание правила форматирования»;

3) у цьому вікні вибираємо варіант «Использовать формулу для определения форматируемых ячеек»;

4) у вікні настройок параметрів правила вводимо формулу «=СЧЁТЗ(A2:A65000)>1»;

5) клацаємо по кнопці «Формат» та вибираємо червоний колір для символів та жовтий фон;

6) у вікні «Создание правила форматирования» натискуємо на «ОК»;

7) стаємо на комірку «A2»;

8) викликаємо меню «Вид», у групі «Окно» клацаємо по іконці «Закрепить области»;

9) зі списку, що з’явився, вибираємо варіант «Закрепить области».

Тепер верхній рядок буде завжди перед очима. Як тільки в колонці «A» листа «Реестр» буде позначено двох контрагентів, комірка «А1» набуде жовтого кольору. Це стане сигналом про можливу проблему.

З першим етапом завдання ми впоралися. Однак нам доведеться вирішити ще одну (і дуже важливу!) проблему — підключити до реєстру бланк договору та обробити відразу декількох контрагентів. Якраз цим ми й займемося в нашій наступній статті.

 

Чекаю ваших листів, запитань та пропозицій на bk@id.factor.ua або nictomkar@rambler.ru. Успішної роботи!

App
Завантажуйте наш мобільний додаток Factor

© Factor.Media, 1995 -
Всі права захищені

Використання матеріалів без узгодження з редакцією заборонено

Ознайомитись з договором-офертою

Приєднуйтесь
Адреса
м. Харків, 61002, вул. Сумська, 106а
Ми приймаємо
ic-privat ic-visa ic-visa

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

Дякуємо, що читаєте нас Увійдіть і читайте далі