Алгоритм работы таблицы
Алгоритм работы таблицы
Входной информацией для нашей таблицы должно быть числовое значение. На выходе мы хотим получить представление этого числа прописью. Понятно, что нам надо разбить число на разряды, сформировать для каждого из них количественное числительное, а затем соединить эти текстовые компоненты. Кроме того, надо позаботиться о том, чтобы были указаны названия разрядов (тысячи, миллионы и т. д.), а также соответствующая единица измерения (руб., грн, доллар, кг и др.). Алгоритм, по которому работает наша таблица, представлен на рис. 6.17.
Дата прописью
Дата прописью
Модуль для написания даты прописью, который мы сейчас рассмотрим, может найти более широкое применение (в частности, он пригодится при составлении платежных поручений и накладных). Задача состоит в том, чтобы заставить Excel написать дату словами, например, дату 12.02.2002 представить в виде текста "от 12 февраля 2002 года".
Решить эту задачу можно следующим методом: сначала разбить дату на составляющие, день месяц и год (причем для месяца сформировать не его порядковый номер в году, а название), а после этого все компоненты соединить в текстовой строке.
Таблица, построенная на базе этого алгоритма, с формулами и числовым примером показана на рис. 6.7.
Ячейки таблицы выполняют следующие функции:
- ячейка А1 - служит входом (содержит дату в формате Excel);
- ячейка А2 - определяет номер дня месяца;
- ячейка A3 - определяет номер месяца;
- ячейка А4 - определяет год;
- ячейки А5:А15 - обеспечивает написание названия месяца;
- ячейка А16 - добавляет нуль перед номером дня месяца, если этот номер меньше или равен 9;
- ячейка А17 - служит выходом (содержит дату прописью).
День недели прописью
День недели прописью
Предположим, вам необходимо определить, какому дню недели соответствует дата, находящаяся в интервале от 1900 до 9999 года. Сделать это в Excel довольно просто, но в качестве ответа вы получите порядковый номер дня недели. К тому же в зависимости от установок номеру могут отвечать разные дни. Конечно, это не очень удобно. Поэтому давайте составим модуль, который будет определять по дате день недели и возвращать не его порядковый номер, а название.
День недели позволяет определить функция ДЕНЬНЕД, которую можно ввести, вызвав мастер функций и выделив в списке Категория элемент Дата и время (рис. 6.1). Синтаксис функции следующий:
ДЕНЬНЕД (дата_в_числовом_формате; тип)
Она возвращает день недели, соответствующий первому аргументу дата_в_чи-словом_формате. День недели будет представлен как целое число в интервале от 1 (воскресенье) до 7 (суббота).
Аргумент дата_в_числовом_формате - это код дата-время, который Excel применяет для операций в датами. Данный аргумент можно задавать и в текстовом виде, заключив строку в кавычки. Тип - это число, определяющее нумерацию дней недели. Аргумент Тип может принимать следующие значения:
- 1 (либо опущено) - число от 1 (воскресенье) до 7 (суббота);
- 2 - число от 1 (понедельник) до 7 (воскресенье);
- 3 - число от 0 (понедельник) до 6 (воскресенье).
Таким образом, в зависимости от типа, указанного в функции ДЕНЬНЕД, 5-й день недели может быть четвергом, пятницей или субботой. Чтобы не задавать второй аргумент, будем считать, что 5-й день недели - это четверг.
Формирование числительных
Формирование числительных
Формирование количественных числительных осуществляется в третьем блоке таблицы, в диапазоне ячеек А5:Н13 (рис. 6.19). Описание его работы начнем со столбца "Единицы". Формулы данного столбца составлены с учетом особенности образования числительных для значений с 11 по 19. В ячейке G5 содержится такая формула:
=ЕСЛИ (G4=1;ЕСЛИ(F4=1; "одиннадцать"; "один") ;G6)
Если в ячейках G4 и F4 находится число 1, то функция ЕСЛИ возвратит текст одиннадцать. Если же значение 1 находится только в ячейке F4, а ячейка G4 содержит другое число, то возвращается текст один (рубль). Если в ячейке F4 находится значение, отличное от 1 , то формула переадресовывает решение задачи в ячейку G6. В ячейке G6 находится аналогичная формула:
=ЕСЛИ (G4-2 ; ЕСЛИ (F4=1 ; " двенадцать " ; "два" ) ; G7 )
В ней производится анализ значений ячеек G4 и F4 на предмет того, какое числительное писать: двенадцать или два. Если условия не выполняются, то осуществляется переадресация в ячейку G7 и т. д. Анатогичными формулами заполняются ячейки столбца G по 13-ю включительно.
В столбце "Десятки" находятся формулы, которые формируют количественные числительные для десятков. Рассмотрим, по какому алгоритму они работают. В ячейке F5 содержится такая формула:
=ЕСЛИ ( F4=0 ;"";ЕСЛИ(F4=1; ЕСЛИ (G4-0;" десять";"") ;F6) )
Сначала она производит проверку ячейки F4 на предмет наличия там значения 0. Если это значение присутствует (то есть в числе нуль десятков), формула выдает пустое значение.
Если же в ячейке F4 находится другое значение, то начинает работу первая функция ЕСЛИ. Она проверяет, находится ли в ячейке F4 значение 1. Если это так, то надо посмотреть, какое значение находится в столбце "Единицы" (ячейка G4). Это делает вторая функция ЕСЛИ. В случае, когда там находится значение О, формула выдает текст десять. Если же в ячейке G4 находится любое другое значение, формула выдает пустое значение (""). Таким образом учитывается тот факт, что числительные для значений с 1 1 по 19 уже сформированы в столбце "Единицы".
Если в ячейке F4 находится значение, отличное от 1, управление передается в ячейку F6. В ячейке F6, в свою очередь, записана следующая формула:
=ЕСЛИ(F4=2; "двадцать"; F7)
Когда в ячейке F4 находится значение 2, формула возвращает текст двадцать. В противном случае происходит переадресация в ячейку F7.
Формулы, аналогичные этой, необходимо занести в ячейки столбца F по 12-ю включительно.
Числительные для сотен формируются в столбце Е. В ячейке Е5 содержится такая формула:
=ЕСЛИ(F4=0; "" ;ЕСЛИ (Е4=1; "сто";Е6) )
Сначала она производит проверку ячейки Е4 на предмет нахождения там значения 0. Если это значение присутствует, формула выдает пустое значение (в числе нуль сотен).
Если же там находится другое значение, формула продолжает проверку при помощи второй логической функции ЕСЛИ. Когда в ячейке Е4 находится значение 1, выдается текст сто. В противном случае выполняется переадресация в ячейку Е6.
В ячейке Е6 находится следующая формула:
=ЕСЛИ(Е4=2;"двести";Е7)
Она выдает текст двести, если в ячейке Е4 находится значение 2, и передает управление в ячейку Е7 в противном случае.
Аналогичными формулами следует заполнить ячейки столбца Е по 12-ю включительно.
ПРИМЕЧАНИЕ
Примите во внимание тот факт, что в последних формулах, формирующих единицы, десятки и прочие разряды, при невыполнении условия передача управления в расположенную ниже ячейку не производится.Формирование названий разрядов
Формирование названий разрядов
В четвертом блоке, включающем ячейки А14 и D14, производится формирование названий разрядов - миллионов и тысяч. При этом надо определить для этих слов правильное падежное окончание. Для этого осуществляется анализ последней буквы в соответствующем числительном. В ячейке А14 находится следующая формула:
=ЕСЛИ(А4=0;"";ЕСЛИ(ПРАВСИМВ(А5;1)="а";"миллиона";
ЕСЛИ(ПРАВСИМВ(А5;1)="е";"миллиона";
ЕСЛИ(ПРАВСИМВ(А5;1)="и";"миллиона";
ЕСЛИ(ПРАВСИМВ(А5;1)="н";"миллион";"миллионов")))))
Если в ячейке А4 находится 0, то не пишется ничего. Если числительное заканчивается, например, на букву "а" (два) или "е" (четыре), то формула возвращает текст миллиона. Аналогичная проверка проводится для всех возможных окончаний.
Для определения того, какой буквой заканчивается числительное, используется текстовая функция ПРАВСИМВ (рассмотрена в главе 4).
В ячейке D14 при помощи следующей формулы формируется название для тысяч:
=ЕСЛИ(В4+С4+D4=0;"";ЕСЛИ(ПРАВСИМВ(D5;1)="а";"тысяча";
ЕСЛИ(ПРАВСИМВ(D5;1)="е";"тысячи";
ЕСЛИ(ПРАВСИМВ(D5;1)="и";"тысячи";"тысяч"))))
Сначала в формуле суммируются значения, находящиеся в ячейках В4, С4 и D4. Это делается для того, чтобы определить, присутствуют л и в числе разряды тысяч. А затем, как и в предыдущей формуле, выполняется анализ окончания числительного и на основе его результатов выбирается падежное окончание для слова "тысяча".
Формирование названия единицы измерения
Формирование названия единицы измерения
Эта операция осуществляется в пятом блоке, который состоит из одной ячейки G14. Алгоритм основан на том же принципе, что и алгоритм предыдущего блока. Отличие заключается в том, что склоняется единица измерения - рубль.
Формула в ячейке G14 имеет следующий вид:
=ЕСЛИ(А4+B4+С4+D4+Е4+F4+G4=0;"Нуль рублей";
ЕСЛИ(ПРАВСИМВ(G5;1)="H";"рубль";
ЕСЛИ(ПРАВСИМВ(G5;1)="a";"рубля";
ЕСЛИ(ПРАВСИМВ(G5;1)="е";"рубля";
ЕСЛИ(ПРАВСИМВ(G5;1)="и";"рубля";"рублей")))))
Сначала в формуле проверяется, присутствует ли в данном числе целое значение рублей. Если нет, то выдается надпись Нуль рублей.
Написание числовых данных прописью
Написание числовых данных прописью
День недели прописью Дата прописью Преобразование даты в формат Excel Написание суммы прописью Тестирование таблицы Модуль для написания суммы прописью Excel предоставляет в распоряжение пользователя множество числовых форматов и функций. Однако встроенные средства, позволяющие написать дату и число прописью, в программе отсутствуют. Давайте попытаемся устранить эти недостатки. В процессе работы вы научитесь применять новые функции и реализовать сложные алгоритмы.
Использование зависимостей при вложении формул
Использование зависимостей при вложении формул
Опираясь на стрелки зависимостей, легче выполнить работу по вложению формул. Продемонстрируем это на примере.
1. Поместите табличный курсор в ячейку A3 и нажмите кнопку Зависимые ячейки панели зависимостей.
2. Скопируйте в строке формул формулу из ячейки A3 без знака равенства.
3. В ячейках, на которые указывают стрелки (А4 и В4), произведите замену адреса ячейки скопированной формулой. После выхода из режима редактирования содержимого ячейки стрелка зависимости должна исчезнуть.
4. Проделайте эту процедуру для ячеек диапазона ВЗ:НЗ.
5. Установите стрелки зависимостей для ячейки А4 и произведите в формулах зависимых ячеек аналогичную замену адресов ячеек содержащимися в них формулами (ячейки А5:А12, А14 и G14).
6. Еще раз установите табличный курсор в ячейку А4 и проверьте, остались ли еще зависимые ячейки. Если нет, то содержимое ячейки А4 можно удалить.
7. Проделайте аналогичную операцию с диапазоном ячеек В4:Н4. Вложение формул с логическими функциями ЕСЛИ лучше начинать с самой внутренней, поэтому начните с ячейки А12, а затем перейдите к ячейке АН и т. д.
Однако не следует создавать слишком сложные вложенные формулы. Не забывайте, что для функции ЕСЛИ допускается не более семи уровней вложения. Таким образом, на определенном этапе ячейку, которая влияет на другие ячейки и в которой находится сложная формула, нужно оставить и выполнить вложение формул в следующих зависимых от нее ячейках.
Минимизация размера таблицы
Минимизация размера таблицы
После вложения формул переместите ячейки с формулами таким образом, чтобы минимизировать область, занимаемую модулем. У нас получился прямоугольный модуль, занимающий область Al :G4 (рис. 6.29), где ячейка А1 - это вход, а ячейка А4 - выход модуля. Ячейки с промежуточными расчетами можно окрасить в черный цвет (получится "черный ящик"), ячейку входа - в зеленый, а ячейку выхода - в красный. В этом случае модуль будет хорошо заметен после вставки его в другие листы.
Модуль для написания суммы прописью
Модуль для написания суммы прописью
К достоинствам созданной таблицы можно отнести возможность расширить диапазон значений (например, до миллиардов и более). Недостатками таблицы являются ее относительно большие размеры и трудность внедрения в другие таблицы. Воспользовавшись методом вложения формул, мы попытаемся уменьшить размеры таблицы.
Модуль, формирующий дату прописью
Модуль, формирующий дату прописью
Как и в случае определения названия дня недели, воспользуемся методом вложения формул и создадим модуль, формирующий дату прописью. Этот модуль будет располагаться в диапазоне А1:АЗ и состоять из трех ячеек (рис. 6.12). Модуль из двух ячеек в данном случае не получится, поскольку в Excel допускается только 7 уровней вложения функций.
В ячейке А2 модуля должна находиться следующая формула:
=ЕСЛИ(МЕСЯЦ(С1)=5;"мая";
ЕСЛИ(МЕСЯЦ(С1)= 6;"июня";
ЕСЛИ(МЕСЯЦ(С1)= 7;"июля";
ЕСЛИ(МЕСЯЦ(С1)=8;"августа";
ЕСЛИ(МЕСЯЦ(С1)= 9;"сентября";
ЕСЛИ(МЕСЯЦ(С1)=10;"октября";
ЕСЛИ(МЕСЯЦ(С1)=11;"ноября";"декабря")))))))
В ячейке A3 при помощи приведенной ниже формулы будет формироваться результат:
=СЦЕПИТЬ(" от ";ЕСЛИ(ДЕНЬ(С1)< = 9;0; "") ;
ДЕНЬ (C1);" ";
ЕСЛИ (МЕСЯЦ (С1)=1 ; "января" ;
ЕСЛИ (МЕСЯЦ (С1)=2 ; "февраля" ;
ЕСЛИ (МЕСЯЦ (C1)=3; "марта";
ЕСЛИ (МЕСЯЦ (С1)=4; "апреля" ;С2) ) ) ) ;
" ";ГОД(С1)x ;" года.")
Написание суммы прописью
Написание суммы прописью
При заполнении различных документов наряду с суммой в числовом виде требуется указывать ее величину прописью. Конечно, это утомительно. Поэтому давайте создадим небольшую электронную таблицу, которая поможет автоматизировать данную работу.
Существует мнение, что имеется только один способ заставить Excel производить эту операцию - написать программу на языке Visual Basic for Applications, который является встроенным языком программирования для пакета Microsoft Office. Однако мы покажем, как решить эту задачу, не прибегая к программированию.
Преобразование даты в формат Excel
Преобразование даты в формат Excel
Давайте попытаемся решить обратную задачу. Предположим, что элементы даты находятся в разных ячейках и записаны в следующем виде: месяц - прописью, а день месяца и год - числами. Элементы даты нужно соединить и представить в формате Excel. Алгоритм решения этой задачи показан на рис. 6.13.
Разбивка числа на разряды
Разбивка числа на разряды
В строке 3 определяется, сколько целых миллионов, тысяч, десятков и т. д. имеется в числе. Для этого следует разделить число на значение соответствующей разрядности и отбросить дробную часть. Например, для определения количества целых сотен число надо разделить на 100 и с помощью математической функции ОТБР отбросить дробную часть результата. Таким образом, в ячейке ЕЗ должна находиться такая формула:
=ОТБР(А1/100;0)
Если число больше или равно 100, то мы получим количество сотен в этом числе. В противном случае результатом будет 0. Аналогичные формулы используем и для других разрядов. Значение знаменателя дроби в этих формулах изменяется в зависимости от разряда: для тысяч - 1000, для миллионов - 1000000 и т. д.
В строке 4 определяются значения каждого из разрядов. Для этого надо выделить младший разряд в числах, которые находятся в строке 3. Это делается путем вычитания из них значений, которые находятся в ячейке левее, умноженных на 10. Например, в ячейке С4 содержится формула для определения десятков тысяч:
=СЗ-ВЗ*10
Как видите, из определенного в ячейке СЗ количества десятков тысяч вычитается установленное в ячейке ВЗ количество сотен тысяч, умноженное на 10. В случае числа 1111111,11 это будут следующие значения:
111 - 11*10
В результате мы получим в разряде сотен тысяч значение 1.
Формула для копеек (ячейка НЗ) несколько отличается от остальных формул строки 3. Мы хотим, чтобы в случае, когда число копеек не превышает 9, перед цифрой добавлялся 0. А следовательно, сумма, в которой указаны только целые рубли, должна выглядеть как "20 руб. 00 коп.". Это обеспечивается путем занесения в ячейку Н4 следующей формулы:
=ЕСЛИ(НЗ<=9;0;"")
и прочего) необходимо указывать даты
Резюме
При составлении многих документов (платежных поручений, накладных, счетов-фактур, кассовых ордеров и прочего) необходимо указывать даты и денежные суммы прописью. Однако далеко не на всех предприятиях делопроизводство автоматизировано настолько, что эти документы генерируются специальными программами. Если ваше предприятие не относится к разряду последних, возьмите на вооружение готовые модули, описанные в этой главе. С их помощью вы можете генерировать представление дат и числовых величин в нужном виде и избавите себя от утомительной работы по вводу сумм прописью с клавиатуры. Выполняя задания, поставленные в этой главе, вы научились реализовывать на Excel сложные алгоритмы, ознакомились с новыми функциями и средствами, в частности с командами подменю Зависимости.
Мастер функций: категория Дата и время
Рис. 6.1.Мастер функций: категория Дата и время
Введите в ячейку А1 любую догу, например 6.01.2002 г. Для наглядности задайте для этой ячейки формат даты. Перейдите в ячейку А2 и вызовите панель функции ДЕНЬНЕД (рис. 6.2). В поле Дата_как_число введите адрес ячейки А1.
Панель функции ДЕНЬНЕД
Рис. 6.2. Панель функции ДЕНЬНЕД
После этих действий ячейка А2 будет содержать следующую формулу:
=ДЕНЬНЕД(А1)
Эта формула вернет результат, равный 1. Значит, интересующий нас день - воскресенье.
Теперь приступим к реализации алгоритма, позволяющего написать прописью название этого дня недели. Введите в ячейку A3 формулу, созданную на основе логической функции ЕСЛИ:
=ЕСЛИ(А2=1;"Воскресенье";А4)
Формула сравнивает номер дня недели со значением 1. Если результатом сравнения является ИСТИНА, то в ячейке A3 появится текст Воскресенье. В противном случае значение ячейки A3 будет определяться содержимым ячейки А4.
В ячейке А4 должна находиться аналогичная формула:
=ЕСЛИ(А2=2;"Понедельник";А5)
Если номер дня недели отвечает понедельнику (равен 2), то в этой ячейке появится текст Понедельник. Данное значение затем передается в ячейку A3.
Заполните аналогичными формулами для других дней недели ячейки А5:А7. А вот в ячейку А8 поместите следующую формулу:
=ЕСЛИ(А2=6;"Пятница";"Суббота")
Она не содержит ссылок на другие ячейки, поскольку мы уже перечислили все дни недели.
Алгоритм, реализованный при помощи этих формул, представлен на рис. 6.3.
Алгоритм работы таблицы, формирующей название дня недели
Рис. 6.3. Алгоритм работы таблицы, формирующей название дня недели
Разработанная нами таблица располагается в диапазоне ячеек А1:А8, ее входом является ячейка А1, выходом - ячейка A3. На рис. 6.4 показана таблица с формулами, а на рис. 6.5 - с числовыми значениями.
Воспользуемся методом вложения формул и минимизируем эту таблицу. На ее базе можно создать модуль, состоящий всего из двух ячеек. Ячейка А1 будет служить входом модуля, а ячейка А2, в которой производятся вычисления, - выходом. Ячейка А2 должна содержать следующую формулу:
=ЕСЛИ(ДЕНЬНЕД(А1)=1;
"Воскресенье";
ЕСЛИ(ДЕНЬНЕД(А1)=2;"
Понедельник";
ЕСЛИ(ДЕНЬНЕД(А1)=3;
"Вторник";
ЕСЛИ(ДЕНЬНЕД(А1)=4;"Среда";
ЕСЛИ(ДЕНЬНЕД(А1)=5;"Четверг";
ЕСЛИ(ДЕНЬНЕД(А1)=6;"Пятница","Суббота"))))))
Таблица, формирующая название дня недели (с формулами)
Рис. 6.4. Таблица, формирующая название дня недели (с формулами)
Таблица, формирующая название дня недели (с числовыми значениями)
Рис. 6.5. Таблица, формирующая название дня недели (с числовыми значениями)
Данный модуль можно перемещать по рабочему листу и копировать в другие книги (файлы) и рабочие листы. Подкорректировав ссылку в формуле, вы можете изменить расположение ячеек входа и выхода: разместить их не в одной строке, а в одном столбце (рис. 6.6).
модули, формирующие название дня недели (с числовыми данными и формулами)
Рис. 6.6. модули, формирующие название дня недели (с числовыми данными и формулами)
Таблица, формирующая написание даты прописью
Рис. 6.7. Таблица, формирующая написание даты прописью
Итак, нам необходимо выделить в дате день, месяц и год (ячейки А2, A3 и А4). Для определения дня задействуем функцию ДЕНЬ, принадлежащую к категории Дата и время. Функция возвращает номер дня в месяце для даты, указанной в числовом формате. День возвращается как целое число из диапазона от 1 до 31. Синтаксис функции имеет следующий вид:
ДЕНЬ(дата_в_числовом_формате)
Поместите табличный курсор в ячейку А2 и вызовите панель функции ДЕНЬ (рис. 6.8). В поле Дата_как_число введите адрес ячейки А1. Вы должны получить в ячейке А2 формулу, которая определяет день месяца, указанный во введенной в ячейку А1 дате:
=ДЕНЬ(А1)
Для определения года используем функцию ГОД, которая также принадлежит к категории Дата и время. Функция возвращает год, соответствующий аргументу дата_в_числовом_формате. Год определяется как целое число в интервале от 1900 до 9999. Синтаксис функции следующий:
ГОД(дата_в_числовом_формате)
Панель функции ДЕНЬ
Рис. 6.8. Панель функции ДЕНЬ
Разместите табличный курсор в ячейке A3 и вызовите панель функции ГОД (рис. 6.9). В поле Дата_как_число введите адрес ячейки А1. Формула в ячейке A3 должна имеет такой вид:
=ГОД(А1)
Таким образом, в этой ячейке будет содержаться номер года от рождества Христова в дате, указанной в ячейке А1.
Панель функции ГОД
Рис. 6.9. Панель функции ГОД
Теперь приступим к формированию названия месяца. Для определения номера месяца воспользуемся функцией МЕСЯЦ, принадлежащей к категории Дата и время. Функция возвращает месяц, соответствующий аргументу дата_в_число-вом_формате. Месяц определяется как целое в интервале от 1 (январь) до 12 (декабрь). Синтаксис функции такой:
МЕСЯЦ(дата_в_числовом_формате)
Установите табличный курсор в ячейку А4 и вызовите панель функции МЕСЯЦ (рис. 6.10). В поле Дата_как_число введите адрес ячейки А1. Полученная формула должна иметь вид:
=МЕСЯЦ(А1)
В данном случае функция МЕСЯЦ определяет, какой номер месяца указан в дате, находящейся в ячейке А1.
Панель функции МЕСЯЦ
Рис. 6.10. Панель функции МЕСЯЦ
В области А5:А15 необходимо разместить формулы, построенные на базе логической функции ЕСЛИ. С их помощью реализуется алгоритм, который аналогичен алгоритму, использованному для определения названия дня недели. Однако теперь вместо номеров дней недели указаны номера месяцев в году. В ячейке А5 формируется название месяца в родительном падеже. Занесите в ячейку А5 следующую формулу:
=ЕСЛИ(А4=1;"января";А6)
В ячейках А6:А14 расположите аналогичные формулы для других месяцев, а в ячейку А15 введите несколько другую формулу:
=ЕСЛИ(А4=11;"ноября";"декабря")
Мы хотим, чтобы номер дня, если он меньше 10, был представлен с предшествующим нулем. Реализовать это можно с применением помещенной в ячейку А16 формулы
=ЕСЛИ(А2Если порядковый номер дня месяца меньше или равен 9, функция ЕСЛИ возвращает 0. В противном случае она выдает пустую строку, потому что между кавычками в третьем аргументе ничего не введено.
Результат формируется в ячейке А17 с использованием формулы
=" от "&А16&А2&" "&А5&" "&АЗ&" года."
В этой формуле при помощи символов "&" производится конкатенация (соединение) текстовых строк и содержимого ячеек, в которых находятся определенные ранее элементы даты. Перед датой добавляется предлог "от" (это нужно, в частности, для платежного поручения). Далее следует адрес ячейки А16. Она предназначена для вставки предшествующего нуля перед датой в случае, когда номер дня меньше 10. Указание адреса ячейки А2 приводит к вставке дня месяца. Дальше следует пробел (" "), перед названием месяца. В ячейке А5 находится название месяца. После него опять надо вставить пробел (" "). Ячейка A3 содержит год. Предлог " от " в начале формулы можно удалить или заменить другим.
Для конкатенации элементов можно использовать не только символ "&". В Excel есть текстовая функция СЦЕПИТЬ. Для ее вызова также применяется мастер функций. Панель функции приведена на рис. 6.11. С помощью этой функции можно получить формулу, которая аналогична предыдущей, но более удобна для восприятия:
=СЦЕПИТЬ(" от ";А16;А2;" ";А5;" ";АЗ;" года.")
Панель функций СЦЕПИТЬ
Рис. 6.11. Панель функций СЦЕПИТЬ
Модуль написания даты прописью (с формулами и числовым примером)
Рис. 6.12. Модуль написания даты прописью (с формулами и числовым примером)
ПРИМЕЧАНИЕ
Данный модуль легко транспонировать. Для этого нужно либо переместить ячейки на рабочем листе, либо скопировать рабочий лист и при его вставке использовать диалоговое окно Специальная вставка, отметив в нем опцию Транспонировать.
Алгоритм составления даты из отдельных компонентов
Рис. 6.13. Алгоритм составления даты из отдельных компонентов
Входами таблицы являются ячейки А1 (год), А2 (число месяца) и A3 (название месяца), а выходом- ячейка А4. Она содержит дату в формате Excel. В диапазоне ячеек А5:А15 определяется номер месяца (рис. 6.14).
Таблица, которая из отдельных составляющих формирует дату в формате Excel
Рис. 6.14. Таблица, которая из отдельных составляющих формирует дату в формате Excel
Рассмотрим, как формируется номер месяца. Функция ЕСЛИ проверяет, совпадает ли значение в ячейке A3 с названием месяца, указанным в функции. Если совпадает, то выдается соответствующий месяцу порядковый номер. В противном случае осуществляется переход в ячейку, расположенную ниже. В этой ячейке выполняется аналогичная проверка. Переход к ячейке ниже происходит до тех пор, пока не будет найден месяц или не будет обнаружена ошибка в названии месяца. Таким образом, в ячейку А4 необходимо ввести следующую формулу:
=ЕСЛИ(А3="Январь";1;А5)
В первом аргументе функции ЕСЛИ содержимое ячейки A3 сравнивается с текстом Январь. Если они одинаковы, то функция возвращает значение 1 - первый месяц. В противном случае ячейка A3 будет содержать значение, находящееся в ячейке А5, адрес которой указан в третьем аргументе функции. Формула в ячейке А5 сравнивает значение ячейки A3 с текстом Февраль. Аналогичные формулы содержатся в ячейках по А1.4 включительно. В ячейке А15 расположена формула, которая несколько отличается от предыдущих:
=ЕСЛИ(А3="Декабрь";12;"Уточните месяц!")
Несовпадение значения в ячейке A3 со словом Декабрь означает, что месяц был введен неправильно. Поэтому в данном случае третьим аргументом является не адрес ячейки, а сообщение Уточните месяц!.
Для формирования даты из отдельных элементов в числовом формате воспользуемся функцией категории Дата и время - ДАТА. Поместите табличный курсор в ячейку А16 и вызовите панель функции ДАТА (рис. 6.15). Введите в поле Год ссылку на ячейку А1, где указан номер года, в поле Месяц - ссылку на ячейку А4, где определен номер месяца в году, а в поле День - ссылку на ячейку А2, где указан день месяца.
Модуль для формирования из отдельных составляющих даты в формате Excel
Рис. 6.16. Модуль для формирования из отдельных составляющих даты в формате Excel
В ячейке А4 модуля должна содержаться такая формула:
=ДАТА ( А1 ; ЕСЛИ ( А3="Январь" ; 1 ; ЕСЛИ ( А3="Февраль" ; 2 ;
ЕСЛИ ( А3 = "Март";3;ЕСЛИ(А3="Апрель";4;А5) ) ) ) ; А2 )
В ячейку А5, которая служит выходом модуля, занесите формулу, приведенную ниже:
=ЕСЛИ (А3="Май" ; 5; ЕСЛИ (А3 = "Июнь" ; 6;
ЕСЛИ(А3="Июль" ; 7 ; ЕСЛИ (А3="Август" ; 8 ;
ЕСЛИ(А3="Сентябрь";9;ЕСЛИ(А3="Октябрь";10;
ЕСЛИ(А3="Ноябрь";11;
ЕСЛИ(А3="Декабрь"; 12; "Уточните месяц! "))))))))
Панель функции ДАTА
Рис. 6.16. Панель функции ДАTА
Построив таблицу, методом вложения формул создайте модуль для формирования даты в формате Excel. Модуль можно разместить в диапазоне размером 5 ячеек (рис. 6.16).
Алгоритм формирования суммы прописью
Рис. 6.17. Алгоритм формирования суммы прописью
Таблица занимает диапазон А 1:Н21 и состоит из шести областей (по числу блоков в схеме алгоритма).
1. Ячейка А1 - входная. Сюда вводится число или ссылка на ячейку, в которой находится число.
2. Ячейки А2:Н4 - здесь выполняется разделение числа на разряды и определение значения каждого разряда.
3. Ячейки А5:Н13 - блок формирования числительного для каждого разряда. Построен по тому же принципу, что и предыдущие таблицы. Пишет прописью числа: от одного до девятнадцати, двадцать, тридцать, ..., сто, двести, ..., девятьсот и т. д.
4. Ячейки А14 (тысячи) и D14 (миллионы) - формируют названия разрядов.
5. Ячейка G14 - формирует название единицы измерения в соответствующем падеже. Не обязательно использовать в качестве единицы измерения названия валют. Это могут быть штуки, килограммы и т. п. Язык также может быть любой. Таблицу легко преобразовать так, что она будет формировать текст для валютного платежного поручения, в котором числа записываются прописью на английском или других языках.
6. Ячейки А15:А21 - здесь выполняется объединение и завершающая обработка и результатов, полученных во всех предыдущих блоках. Ячейка А21 является выходом таблицы.
Таблица написании суммы прописью (с числовым примером)
Рис. 6.18. Таблица написании суммы прописью (с числовым примером)
Максимальное число, с которым может работать таблица, равно 9999999,99. Однако ее легко модифицировать, с тем чтобы обрабатывать и большие числа.
ПРИМЕЧАНИЕ
Если вы предполагаете, что число, подаваемое на вход таблицы, будет содержать больше двух знаков после запятой, задайте ссылку на адрес ячейки с входными данными через функцию округления.Рассмотрим принципы работы таблицы на числе 1111111 руб. 11 коп. Это число введено в ячейку А1 (рис. 6.18). В строке 2 таблицы содержатся надписи, которые указывают, для какого разряда производятся вычисления в данном столбце. После отладки таблицы эту строку можно удалить.
Рабочий лист с формулами написания чисел из диапазона от единиц до сотен
Рис. 6.19. Рабочий лист с формулами написания чисел из диапазона от единиц до сотен
Рабочий лист с формулами написания чисел от тысяч до миллионов
Рис. 6.20. Рабочий лист с формулами написания чисел от тысяч до миллионов
Скопируйте диапазон ячеек E5:G13, в котором формируются числительные для единиц, десятков и сотен (0-999), в диапазон B5:D13 (рис. 6.20), формирующий написание прописью следующих разрядов (тысяч). Исправлений вносить не требуется.
Итак, мы заполнили формулами область B5:G13, в которой осуществляется запись прописью чисел от 0 до 999999.
Теперь приступим к формированию числительных для разряда миллионов. Эта операция выполняется в столбце А. В ячейке А5 находится следующая формула:
=ЕСЛИ(А4=0;"";ЕСЛИ(А4=1;"один";А6))
Она анализирует значение в ячейке А4. Если оно равно 0, то ничего не пишется. Если в ячейке А4 находится 1, то пишется текст один (миллион). Если в ячейке А4 содержится другое значение, происходит переадресация в ячейку А6 и т. д. Аналогичными формулами заполнены ячейки столбца А по 12-ю включительно.
Итак, числительные для разрядов мы сформировали, теперь приступим к созданию названий разрядов.
Рабочий лист с формулами, формирующими названия разрядов и единицы измерения
Рис. 6.21. Рабочий лист с формулами, формирующими названия разрядов и единицы измерения
Алгоритм завершающей обработки текста
Рис. 6.22. Алгоритм завершающей обработки текста
В ячейке А15 находится следующая формула:
=СЦЕПИТЬ(А5;" ";А14;" ";В5;" ";С5;" ";D5;" ";D14;" ";Е5;" ";F5;" ";G5;" ";G14;" ";H3;H4;" коп")
Она объединяет в единое целое все компоненты, предназначенные для написания суммы прописью. В данной формуле содержатся ссылки на все ячейки, в которых находятся сформированные числительные и названия разрядов. Между ссылками на ячейки в функции СЦЕПИТЬ нужно обязательно поставить пробелы.
Например, при формировании прописью числа 1 111 111,11 аргументы функции будут иметь такие значения:
=СЦЕПИТЬ("один";" ";"миллион";" ";"сто";" ";"одиннадцать";" ";"тысяч";" "; ... и т. д. }
В ячейке А16 производится удаление из текста лишних пробелов:
=СЖПРОБЕЛЫ(А15)
Для этого применяется текстовая функция СЖПРОБЕЛЫ (рис. 6.23), которая оставляет в тексте только одиночные пробелы. Функцию СЖПРОБЕЛЫ можно применять для обработки текстов, полученных из других прикладных программ, если эти тексты содержат избыточные пробелы. Синтаксис функции имеет следующий вид:
СЖПРОБЕЛЫ(текст)
где текст - это текст, из которого удаляются пробелы.
Панель функции СЖПРОБЕЛЫ
Рис. 6.23. Панель функции СЖПРОБЕЛЫ
Однако зачем удалять пробелы, если между всеми ссылками на ячейки заданы одиночные пробелы? Это делается для того, чтобы при написании прописью небольшого числа, например "Три рубля 45 коп." перед словом "Три" не было лишних пробелов. Если пробелы не удалить, надпись будет выглядеть так: " Три рубля 45 коп.".
Теперь надо обеспечить правильное употребление строчных и прописных букв в надписи. Надпись будет начинаться с прописной буквы. Это делается при помощи формул, которые находятся в ячейках А17 и А18.
В ячейке А17 содержится следующая формула:
=ЛЕВСИМВ(A16;1)
Она выделяет самый левый (то есть первый) символ строки после того, как из нее удалены лишние пробелы.
Формула в ячейке А18 назначает для этого символа прописную букву:
=ПРОПИСH(А17)
Здесь использована функция ПРОПИСН (рис. 6.24), которая делает все буквы в тексте прописными. Синтаксис функции таков:
ПРОПИСН(текст)
где текст - это текст, символы которого преобразуются в прописные. Текст может быть ссылкой на ячейку или текстовой строкой.
Панель функции ПРОПИСН
Рис. 6.24. Панель функции ПРОПИСН
На данном этапе можно производить окончательное формирование надписи. Для этого надо соединить первую букву надписи (ячейка А18) с остальной ее частью.
Сначала определяем, сколько символов находится в строке после удаления лишних пробелов. Это делается в ячейке А19:
=ДЛСТР (А16)
После этого извлекаем из строки все символы, кроме первого. Предназначенная для этого формула находится в ячейке А20:
=ПРАВСИМВ(А16;А19-1)
Для того чтобы определить количество извлекаемых символов, необходимо из общего количества символов текстовой строки (содержится в ячейке А19) вычесть 1.
Теперь компоненты надписи можно объединить. Для этого запишите в ячейку А21 следующую формулу:
=СЦЕПИТЬ(А18;А20)
Список команд меню Сервис и подменю Зависимости
Рис. 6.25. Список команд меню Сервис и подменю Зависимости
При выполнении команды Влияющие ячейки стрелки зависимостей показывают на ячейки, значения которых влияют на данную ячейку.
Если же вы воспользуетесь командой Зависимые ячейки, то стрелки будут указывать на ячейки, значения которых зависят от данной ячейки.
В случае, когда нужно проследить большое число зависимостей, удобно применить панель Зависимости (рис. 6.26).
Панель Зависимости
Рис. 6.26. Панель Зависимости
На этой панели расположены пять нужных нам кнопок: Влияющие ячейки, Убрать стрелки к влияющим ячейкам, Зависимые ячейки, Убрать стрелки к зависимым ячейкам и Убрать все стрелки (первые пять кнопок слева).
На рис. 6.27 показаны стрелки, которые появляются при выполнении команды Влияющие ячейки. Они указывают, формулы каких ячеек оказывают непосредственное влияние на формулы, находящиеся в анализируемой ячейке. Например, на вычисления в ячейке ВЗ влияет значение ячейки А1. На ячейку С4 влияют значения в ячейках С3 и В3, на которые, в свою очередь, влияет ячейка А1.
Фрагмент рабочего листа со стрелками, показывающими влияние одних ячеек на другие
Рис. 6.27. Фрагмент рабочего листа со стрелками, показывающими влияние одних ячеек на другие
На рис. 6.28 показаны стрелки, которые появляются при выполнении команды Зависимые ячейки. Они указывают, на формулы каких ячеек оказывают влияние формулы или значения, находящиеся в исходной ячейке. Так, ячейка А1 влияет на вычисления в ячейках A1, B1, C1 и т. д, а ячейка ВЗ влияет на вычисления в ячейках С4 и В4.
Фрагмент рабочего листа со стрелками, показывающими зависимость одних ячеек от других
Рис. 6.28. Фрагмент рабочего листа со стрелками, показывающими зависимость одних ячеек от других
Три модуля написания суммы прописью
Рис. 6.29. Три модуля написания суммы прописью
Разные варианты расположения модуля представлены на рис. 6.29:
- прямоугольный (диапазон ячеек A1:G4), вход - ячейка А1, выход - ячейка А4;
- горизонтальный (диапазон ячеек А8:АВ8), вход - ячейка А8, выход - ячейка В8;
- вертикальный (диапазон ячеек А12:А39), вход - ячейка А12, выход - ячейка А13.
Соединение всех компонентов надписи и их текстовая обработка
Соединение всех компонентов надписи и их текстовая обработка
В шестом блоке применяются текстовые функции, которые обрабатывают и соединяют результаты вычислений предыдущих блоков. Формулы блока (диапазон А15:А21) показаны на рис. 6.21, а алгоритм - на рис. 6.22.
Тестирование таблицы
Тестирование таблицы
Когда все необходимые формулы будут готовы, обязательно протестируйте таблицу, так как при ее создании велика вероятность орфографических ошибок. Кроме того, вы можете ошибиться при вводе ссылок. Конечно же, это приведет к сбою в работе. Поэтому данную таблицу желательно проверить при помощи разных числовых значений, в том числе максимального и минимального.
Зависимости
Зависимости
При вложении одной формулы в другую легко допустить ошибку. Избежать этого поможет средство Excel, позволяющее проследить зависимость значений в одних ячейках от формул и значений, находящихся в других ячейках.
Для определения зависимостей поместите табличный курсор в рассматриваемую ячейку и вызовите команду Сервис/Зависимости/Зависимые ячейки или Влияющие ячейки (рис. 6.25). После этого между зависимыми ячейками появятся стрелки. Они показывают непосредственное влияние содержимого одних ячеек на формирование результата в других ячейках.
