Пикуза В., Гаращенко А. Экономические и финансовые расчеты в Excel

Подождите немного. Документ загружается.

Написание суммы прописью 171

слов правильное падежное окончание. Для этого

осуществляется

анализ послед-

ней буквы в соответствующем числительном. В ячейке АН находится

формула:

=ЕСЛИ(А4=0;"";ЕСЛИ(ПРАВСИМВ(А5;1)="а";"миллиона";

ЕСЛИ

(ПРАВСИМВ

(А5 ;

="е"

"миллиона" ;

ЕСЛИ(ПРАВСИМВ(А5;1)="и";"миллиона";

ЕСЛИ(ПРАВСИМВ(А5;1)="н";"миллион";"миллионов")))))

Если в ячейке А4 находится 0, то

не

пишется ничего. Если числительное закан-

чивается, например, на букву «а» (два) или

«е»

(четыре), то формула возвращает

текст миллиона. Аналогичная проверка проводится для всех возможных окончаний.

Для определения того, какой буквой заканчивается числительное, используется

текстовая

функция

ПРАВСИМВ (рассмотрена в главе 4).

В ячейке D14 при помощи следующей формулы формируется название для тысяч:

=ЕСЛИ(В4+С4+04=0;"";ЕСЛИ(ПРАВСИМВ(D5;1)="а";"тысяча";

ЕСЛИ(ПРАВСИМВ(D5;1)="е";"тысячи";

ЕСЛИ(ПРАВСИМВ(D5;l)="и";"тысячи";"тысяч"))))

Сначала в формуле суммируются значения, находящиеся в ячейках В4, С4 и D4.

Это делается для того, чтобы

определить,

присутствуют ли в числе разряды

тысяч.

А затем, как и в предыдущей формуле, выполняется анализ окончания числи-

тельного и на основе его результатов выбирается падежное окончание для слова

«тысяча».

Формирование названия единицы измерения

Эта операция осуществляется в пятом блоке, который состоит из одной ячейки

G14.

Алгоритм основан на том же принципе, что и алгоритм предыдущего блока.

Отличие заключается в том, что склоняется единица измерения — рубль.

Формула в ячейке

G14

имеет следующий вид:

=ЕСЛИ(А4+В4+С4+В4+Е4+Г4+С4=0;"Нуль

рублей"

;

ЕСЛИ(ПРАВСИМВ(65;1)="Н";"рубль";

ЕСЛИ(ПРАВСИМВ(G5;1)="а";"рубля";

ЕСЛИ (ПРАВСИМВ

(G5;l)

-="e";

"рубля";

ЕСЛИ(ПРАВСИМВ(С5;1)="и"

;

"рубля";"рублей")))))

Сначала в формуле проверяется, присутствует ли в данном числе целое значение

рублей. Если нет, то выдается надпись Нуль рублей.

Соединение всех компонентов надписи

и их текстовая обработка

В шестом блоке применяются текстовые функции, которые обрабатывают и со-

единяют результаты вычислений предыдущих блоков.

Формулы

блока (диапазон

А15:А21) показаны на рис. 6.21, а алгоритм — на рис. 6.22.

172

Глава 6. Написание числовых данных прописью

'*>,йм

Пи;и«

.Jin

I.A

!}|

Times New

Roman

Cyr

г ....

;

'V-V-!'&

'«•;.

л'л

'&'•№$

•»}}

'»"•-

•

Ш.

-Ш

Та

fTJ:

•%

•«

;?а

•ЕСЛИ(А4«0;"",

ЕСЛИ(ПРАВСИМВ(А5;1)="«";

"мгтпиона",

ЕСЛИ(ПРАВСИМВ(АЗ;1)="е",

'миллиона";

ЕСЛИ(ПРАВСИМВ(АЗ;1)""и";

"миллиона";

ЕСЛИ(ПРАВСИМВ(А5;1)="н";

миллион"

."миллионов")))))

=ЕСЛИ(В44С4+04=0;'"';

ЕСЛИ(ПРАВСИМВ(О};1)="

а"

;

тысяча";

СЛИ(ПР

ABCHMB(D5

;

1)"

;

тысячи";

ЕСЛИ(ПРАВСИМВ(р.5,1)="и"

"тысячи",

"тысяч"))))

ЕСЛИ(А4+В4+С4+04+Е4+М-Ю4=0;

"Нуль

рублей"

ЕСЛИ(ПРАВСИМВ(05;1)=

н',

"рубль",

ЕСЛИ(ПРАВСИМВ(03,1)-"

а"

;"ру6пя"

ЕС

ЛИ(ПР

АВСИМВ(О5

1)=

;

рубля"

;

ЕС

ЛИ(ПРАВСИМВ(05

1)=

и'

рубля"

;

"рублей")))))

СЦЕПИТЬ(А5;"

";А

:ЖПРОБЕЛЫ<:А13)

-ЛЕВСИМВ(А1б;1)

ПРОПИСН(А17)

ДЛСТР(А1б)

=ПРАВСИМВ(Д16;А19-1)

шцт

Рис.

6.21.

Рабочий лист с

формулами,

формирующими

названия разрядов и единицы измерения

ЯчейкаА15

Соединить

весь

полученный текст

Ячейка

А16

Убрать лишние пробелы

Ячейка

А17

Взять первую

левую

букву

Ячейка

А19

Определить длину полученного

текста

Ячейка А18

Сделать первую букву прописной

Ячейка

А20

Взять все правые символы

без первого левого

ЯчейкаА21

Соединить весь текст

Рис. 6.22. Алгоритм завершающей обработки текста

В ячейке

А15

находится следующая формула:

=СЦЕПИТЬ(А5;"

";А14;"

";В5;"

";С5;"

";D5;"

";D14;"

";E5;"

";F

";G14;"

";H3;H4;"

коп")

";G5;

Написание суммы прописью

173

Она объединяет в единое целое все компоненты, предназначенные для написа-

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

которых находятся сформированные числительные и названия разрядов. Между

ссылками на ячейки в функции СЦЕПИТЬ нужно обязательно поставить пробелы.

Например, при формировании прописью числа

111

111,11

аргументы функции

будут иметь такие значения:

^СЦЕПИТЬ("один";"

";"миллион";"

";"сто";"

";"одиннадцать";"

"/"тысяч";"

...

и т.

д.

)

В ячейке А16 производится удаление из текста лишних пробелов:

=СЖПРОБЕЛЫ(А15)

Для этого применяется текстовая функция

СЖПРОБЕЛЫ

(рис. 6.23), которая

оставляет в тексте только одиночные пробелы. Функцию СЖПРОБЕЛЫ можно

применять

для обработки текстов, полученных из других прикладных программ,

если эти тексты содержат избыточные пробелы. Синтаксис функции имеет следую-

щий

вид:

СЖПРОБЕЛЫ(текст)

где текст — это текст, из которого удаляются пробелы.

""""'""

"""""'

'"""

.-.....•...-

•-..-.-•..-л

lC!<f.i

теп:

г,

союриго

уда'Могся

пр

Щ1|

Значение:

один

миадн;,::

ст-•

ол---г.

йена

-У

'•

I '. .

>—

[

.'>!№Ы

Рис. 6.23.

Панель

функции СЖПРОБЕЛЫ

Однако зачем удалять пробелы, если между всеми ссылками на ячейки заданы

одиночные пробелы? Это делается для того, чтобы при написании прописью не-

большого числа, например «Три рубля 45 коп.» перед словом «Три» не было лиш-

них пробелов. Если пробелы не удалить, надпись будет выглядеть так:

Три

рубля 45

коп.».

Теперь надо обеспечить правильное употребление строчных и прописных букв в

надписи. Надпись будет начинаться с прописной буквы. Это делается при помо-

щи формул, которые находятся в ячейках А17 и А18.

В ячейке

А17

содержится следующая формула:

=ЛЕВСИМВ(А16;1)

Она выделяет самый левый (то есть первый) символ строки после того, как из

нее удалены лишние пробелы.

174

Глава 6. Написание числовых данных прописью

Формула в ячейке

А18

назначает для этого символа прописную букву:

«ПРОПИСИ(А17)

Здесь использована функция

ПРОПИСН

(рис. 6.24), которая делает все буквы R

тексте прописными. Синтаксис функции таков:

ПРОПИСН(текст)

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

быть ссылкой на

ячейку

или текстовой строкой.

Рис. 6.24. Панель функции ПРОПИСН

На данном этапе можно производить окончательное формирование надписи. Для

этого надо соединить первую букву надписи (ячейка А18) с остальной ее частью.

Сначала определяем, сколько символов находится

строке после удаления лиш-

них пробелов. Это делается в ячейке

А19:

=ДЛСТР(А16)

После этого извлекаем из строки все символы, кроме первого. Предназначенная

для этого формула

находится,

в ячейке А20:

=ПРАВСИМВ(А16/А19-1)

Для того чтобы определить количество извлекаемых символов, необходимо из об-

щего количества символов текстовой строки (содержится в ячейке

А19)

вычесть

Теперь компоненты надписи можно объединить. Для этого запишите в ячейку

А21

следующую формулу:

-СЦЕПИТЬ(А18;А20)

Тестирование таблицы

Когда все необходимые формулы будут готовы, обязательно протестируйте таб-

лицу,

так как при ее создании велика вероятность орфографических ошибок.

Кроме того, вы можете ошибиться при вводе ссылок. Конечно же, это приведет

к сбою в работе. Поэтому данную таблицу желательно проверить при помощи

разных числовых значений, в том числе максимального и минимального.

Модуль для написания суммы прописью

175

Модуль для написания суммы прописью

К достоинствам созданной таблицы

можно

отнести возможность расширить диа-

пазон значений (например, до миллиардов и более). Недостатками таблицы яв-

ляются

ее относительно большие размеры и трудность внедрения в другие

таб-

лицы. Воспользовавшись методом вложения формул, мы попытаемся уменьшить

размеры таблицы.

Зависимости

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

поможет средство Excel, позволяющее проследить зависимость значений в одних

ячейках от формул и значений,

находящихся

в других ячейках.

Для определения зависимостей поместите табличный курсор в рассматриваемую

ячейку и вызовите команду Сервис/Зависимости/Зависимые ячейки или Влияющие

ячейки (рис. 6.25). После этого между зависимыми ячейками появятся стрелки.

Они показывают непосредственное влияние содержимого одних ячеек на фор-

мирование результата в других ячейках.

Microsoft Excel -

Книга!

-л

г-'х

' *

sft

.,..•—:-.. I j л

-n

хмч

™''

-...;

- ж

•

:.;

:й

,^';;,^,A.

Л^:;'Л

гиигсч.

Н;пр-Ц."1'.-Н(*.!

•'••

шшв

Рис. 6.25. Список команд меню Сервис и подменю Зависимости

При выполнении команды Влияющие ячейки стрелки зависимостей показывают

на ячейки, значения которых влияют на данную ячейку.

176

Глава 6. Написание числовых данных прописью

Если же вы воспользуетесь командой Зависимые ячейки, то стрелки будут ука-

зывать на ячейки, значения которых зависят от данной ячейки.

В случае, когда нужно проследить большое число зависимостей, удобно приме-

нить панель Зависимости (рис. 6.26).

эвисимости

Рис. 6.26. Панель Зависимости

На этой панели расположены пять нужных нам кнопок:

Влияющие

ячейки, Убрать

стрелки к

влияющим

ячейкам, Зависимые ячейки, Убрать стрелки к зависимым ячей-

кам и Убрать все стрелки (первые пять кнопок слева).

На рис. 6.27 показаны стрелки, которые появляются при выполнении команды

Влияющие ячейки. Они указывают, формулы каких ячеек оказывают непосред-

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

на вычисления в ячейке ВЗ влияет значение ячейки А1. На ячейку С4 влияют

значения в ячейках СЗ и

ВЗ,

на которые, в свою очередь, влияет ячейка

А1.

Microsoft

Excel - 06. Таблица

написания

суммы

прописью.xls

;^j

Файл

lip,№K.;i

Ё«ц

Рс-т.оьм

Ф-opwrfr

Cefiew.

Длины*

IJKH,

ь#

yhiOVv^

}Гс&

si<?f-

Times

New

Roman

Суг

(lO

ill ^j-а-'*-

A,-

Рис. 6.27. Фрагмент рабочего листа со стрелками, показывающими

влияние одних

ячеек

на другие

На рис. 6.28 показаны стрелки, которые появляются при выполнении команды

Зависимые ячейки. Они указывают, на

формулы

каких ячеек оказывают влияние

Модуль

для

написания суммы прописью

177

формулы или значения, находящиеся в исходной ячейке. Так, ячейка

А1

влияет

на вычисления в ячейках

Al,

Bl,

C1 и т. д, а ячейка ВЗ влияет на вычисления в

ячейках С4 и В4.

Microsoft

Excel - 06. Таблица написания суммы

прописью

xls

|Times

New Roman

Cyr

"=OTBP(Al7i"(I)6o6

Рис. 6.28. Фрагмент рабочего листа со стрелками, показывающими

зависимость одних ячеек от других

Использование зависимостей

при вложении формул

Опираясь на стрелки зависимостей, легче выполнить работу по вложению фор-

мул. Продемонстрируем это на примере.

Поместите табличный курсор в ячейку A3 и нажмите кнопку Зависимые ячейки

панели зависимостей.

2. Скопируйте в строке формул формулу из ячейки A3 без знака равенства.

3. В ячейках, на которые указывают стрелки (А4 и В4), произведите замену ад-

реса ячейки скопированной формулой. После выхода из режима редактиро-

вания содержимого ячейки стрелка зависимости должна исчезнуть.

4. Проделайте эту процедуру для ячеек диапазона ВЗ:НЗ.

5. Установите стрелки зависимостей для ячейки А4 и произведите в формулах

зависимых ячеек аналогичную замену адресов ячеек содержащимися в них

формулами (ячейки А5:А12, А14 и G14).

178

Глава 6. Написание числовых данных прописью

6. Еще раз установите табличный курсор в ячейку А4 и проверьте, остались ли

еще зависимые ячейки. Если

нет,

то содержимое ячейки А4 можно удалить.

7. Проделайте аналогичную операцию с диапазоном ячеек В4:Н4. Вложение фор-

мул с логическими функциями ЕСЛИ лучше начинать с самой внутренней,

поэтому начните с ячейки

А12,

а затем перейдите к ячейке

АИ

и т. д.

Однако не следует создавать слишком сложные вложенные формулы. Не забы-

вайте, что для функции ЕСЛИ допускается

не

более семи уровней вложения. Та-

ким образом, на определенном этапе ячейку, которая влияет на другие ячейки и

в которой находится сложная формула, нужно оставить и выполнить вложение

формул в следующих зависимых от нее ячейках.

Минимизация размера таблицы

После вложения формул переместите ячейки с формулами таким образом, чтобы

минимизировать

область, занимаемую модулем. У нас получился прямоугольный

модуль,

занимающий

область

А1

:G4

(рис. 6.29), где ячейка

АI

— это вход, а ячей-

ка А4 — выход модуля. Ячейки с промежуточными расчетами можно окрасить в

черный цвет (получится «черный ящик»), ячейку входа — в зеленый, а ячейку

выхода — в красный. В этом случае модуль будет хорошо заметен после вставки

его в другие листы.

Microsoft

Excel - 07. Модули написаний

Счммы

прописью,

xls

'•'

'Рай!,

М[.|(лжп

{::ц,

;;..!

••.•,;!..

Tims

New Roman

*10

Девятьсот

девяносто

девять тысяч девятьсот девяносто девять

рублей

09 коп

Семьдесят

сеид

рл^лен

коп

Шлсеиъйихип

четыре

тылмчн

пятьсот

шестьдесят

шесть

рублей

коп

Лист!

Рис. 6.29. Три

модуля

написания суммы прописью

Резюме

179

Разные варианты расположения модуля представлены на рис. 6.29:

• прямоугольный (диапазон ячеек A1:G4), вход — ячейка

Al,

выход —

ячей-

ка А4;

• горизонтальный (диапазон ячеек А8:АВ8), вход — ячейка А8. выход — ячей-

ка В8;

• вертикальный (диапазон ячеек А12:А39), вход —

ячейка

А12, выход — ячей-

ка

А13.

Резюме

При составлении многих документов (платежных поручений, накладных, счетов-

фактур, кассовых ордеров и прочего) необходимо указывать даты и денежные

суммы прописью. Однако далеко не на всех предприятиях делопроизводство ав-

томатизировано настолько, что эти документы генерируются специальными про-

граммами.

Если ваше предприятие не относится к разряду последних, возьмите на воору-

жение готовые модули, описанные в этой главе. С их помощью вы можете гене-

рировать представление дат и числовых величин в нужном виде и избавите себя

от утомительной работы по вводу сумм прописью с клавиатуры.

Выполняя задания, поставленные в этой главе, вы научились реализовывать на

Excel сложные алгоритмы, ознакомились с новыми функциями и средствами, в

частности с командами подменю Зависимости.

НА ДИСКЕТЕ

Все файлы, создание которых описано в этой

главе,

вы найдете в папке

06-На-

писание

числовых данных прописью.

за прошлый месяц);

• с помощью формул, позволяющих взять информацию из других таблиц.

Третий способ будет рассматриваться при создании однострочного табеля. Сей-

час же мы предполагаем, что данные вводятся с клавиатуры.

182

Глава 7. Электронный табель учета рабочего времени

Процесс форматирования рассматривался в предыдущих главах и не должен вы-

звать затруднений.

Для столбцов C:AG необходимо подобрать такую ширину, чтобы в

строке

6 дни

месяца отображались полностью как на экране монитора, так и при выводе на

печать. Для достижения желаемого результата выделите столбцы C:AG и поэкс-

периментируйте с полем

Ширина

столбца

одноименного

диалогового окна.

Заполнение области ввода

Область ввода табеля занимает ячейки

С12:AG23

(рис.

7.1).

Для каждого сотруд-

ника отведено две строки: в нижней указывается количество часов, отработанных

в ночное время, а в верхней

осуществляется

ввод остальных данных. Информация

может быть представлена в

числовом

(количество отработанных часов) или тек-

стовом виде. Текст (обычно одна или

две

буквы) является условным обозначением,

например: «к» — командировка, «от» — отпуск, «у» — учебный отпуск, «б» -

дни временной

нетрудоспособности,

«п»

— дни неявки

на

работу по неуважи-

тельной причине, «в» — выходные дни.

Microsoft

Excel

Двчкстрочный

табель

-']

|Ц

ЁИЙ

BcTJtwa

'"ittjjr

U}.«K-

Дм**

:;••'"

С'

-^_-

_щ

' i

*'|

Предприятие

i и

«i Ж

,:'..

••>

•

"•

ill

ИМЯ,

1Н«И!ЁП1«,

Иванов

//вам

Иваынюч

Петров

Петр

Петрович.

Иваненхи

Я<гаи

Петрович

Сидорова

Елена

Сидороена

Петренко

Петр

Сийороеым,

Сидоренко

Qalop

Ивановом

Готовя

ООО

"Тю

л*

далчик

ТЙЖ

Ъ;7

у7,"7Гу'

Сгруюуриое

подразделение

Месяц

АО АН

"SffiH-Z

Й1

•

!П

•

. •

• !

г;

;;

от

1,1

••

ti>

•

п.

.-!

•

£:

;

•

.м

'•

.'<••'--•::

0123

;

2345

4567

5678

6789

го«

;

1^1

•i

;-((•

145

151

!-...

'.-••-•

,.,

|Й

Рис.

7.1.

Область ввода двухстрочного табеля

Одним из главных параметров в табеле является период его заполнения: год, ме-

сяц и дни. Эти сведения располагаются

шапке табеля.