Тема 5. Работа с датами и числами.
Excel предоставляет в распоряжение пользователя множество числовых форматов и функций. Однако встроенные средства, позволяющие написать дату и число прописью, в программе отсутствуют.
День недели прописью
Используем функцию ДЕНЬНЕД, которая возвращает день недели, соответствующий аргументу дата_в_числовом_формате.
Microsoft Excel хранит даты как ряд последовательных номеров, что позволяет выполнять над ними вычисления. По умолчанию день 1 января 1900 года имеет номер 1, а 1 января 2008 — номер 39448, так как интервал в днях между этими датами составляет 39448.Алгоритм
Введем в ячейку А1 любую дату.
В ячейке А2 определим день недели с помощью =ДЕНЬНЕД(А1;2).
В ячейке А3 с помощью функции ЕСЛИ запишем название этого дня недели:
=ЕСЛИ(А2=1;"Понедельник";ЕСЛИ(А2=2;"Вторник"; ЕСЛИ(А2=3;" Среда";ЕСЛИ(А2=4;"Четверг"; ЕСЛИ(А2=5;"Пятница";ЕСЛИ(А2=6;"Суббота";"Воскресенье"))))))
С помощью операции копирования заменим ссылку на ячейку А2 формулой, которую она содержит (=ДЕНЬНЕД(А1;2)), в результате получим:
=ЕСЛИ(ДЕНЬНЕД(А 1;2)=1;"Понедельник";
ЕСЛИ(ДЕНЬНЕД(А 1;2)=2;"Вторник";ЕСЛИ(ДЕНЬНЕД(А 1;2)=3;"Среда"; ЕСЛИ(ДЕНЬНЕД(А 1;2)=4;"Четверг";ЕСЛИ(ДЕНЬНЕД(А 1;2)=5;"Пятница"; ЕСЛИ(ДЕНЬНЕД(А1;2);"Суббота";"Воскресенье"))))))
Ш Microsoft Excel - Работа с датами и числами :Ц Файл Правка Вид Вставка формат Сервис Данные Окно Справка Введите вопрос - - в х ; J L3 А л J1J Д1 ? й, 1 * а - У1 & е - йН1 -1> - в I ! Anal Суг .10 - Ж К Ч Ш Ш Ш [JFJ| | % ООО j,™ | IP FFL - <3» - ,A - J А1 -fx 02.10.2007 А В С D Е F G Н 1 02.10.2007 2 -ДЕНЬНЕД(А1,2) ti =ЕСЛИ(ДЕНЬНЕД(А1 ;2)=1 "Понед ельник"; ЕСЛИ(ДЕНЬНЕД(А1 ;2)=2; "Вторник"; ЕСЛИ(ДЕНЬНЕД(А 1 ;2)=3; "Среда" ¦ ЕСЛИ(ДЕНЬНЕД(А1 ;2)=4;" Четеерг";ЕСЛИ(ДЕНЬНЕД(А1 ;2)=5; "Пятница"; 3 ЕСЛИ(ДЕНЬНЕД(А1 ;2)=6; "Суббота"; "Воскресенье")))))) 4 5 V
> I Н 1 > н \День недели прописью / < | JTIJE:IJ NUM 1. Рисунок 34
Дата прописью
Дата прописью имеет широкое применение, например, при составлении платежных поручений и накладных.
Задача состоит в том, чтобы заставить Excel написать дату словами, например, дату 4.10.2004 представить в виде текста от 4 октября 2004 года.Решить эту задачу можно следующим методом: сначала разбить дату на составляющие: день, месяц и год (причем для месяца сформировать не его порядковый номер в году, а название), а затем все компоненты соединить в текстовой строке.
Алгоритм.
В ячейку А1 введите произвольную дату.
В ячейке А2 определите номер дня месяца с помощью функции =ДЕНЬ(А1).
В ячейке А3 определите номер месяца: =МЕСЯЦ(А1).
В ячейке А4 определите год: =ГОД(А1).
Ячейки А5:А6 - обеспечивают написание названия месяца (до 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов, поэтому для формирования названия месяца придется использовать две ячейки):
Ячейка А5:
=ЕСЛИ(A3=7; "июля";ЕСЛИ(A3=8;" августа";ЕСЛИ(A3=9;" сентября";ЕСЛИ(A3=
10;"октября";ЕСЛИ(A3=11;"ноября";"декабря")))))
Ячейка А6:
=ЕСЛИ(A3=1;"января";ЕСЛИ(A3=2;"февраля";ЕСЛИ(A3=3;"марта"; ЕСЛИ(A3=4;"апреля";ЕСЛИ(A3=5;"мая";A5)))))
Ячейка А7 - добавляет нуль перед номером дня месяца, если этот номер меньше или равен 9: =ЕСЛИ(A2<=9;0;"")
Ячейка А8 - содержит дату прописью: =СЦЕПИТЬ("от "^ДЕНЬ^);" ";A6;" '^ГОД^!);" года")
Ш Microsoft Excel - Работа с датами .. ЕЕИ Гщ Файл Правка Вид Вставка формат Сервис 1 1 I Данные Окно Справка . в 1 1: J _ЗЯи11и(1йй1|&-1ч-| Z -| @ м : Ж ш|Ж - -.А • Э А1 -г ? 01 02 2004 А В С л 1 I 01.02.2004 | 2 1 3 2 4 2004 — 5 декабря 6 февраля 7 0 8 от 01 февраля 2004 года 9 V Н <4 > н \Дата прописью/ | < > NUM Рисунок 35
В ячейке А8 можно создать сложную формулу: =СЦЕПИТЬ("от ";ЕСЛИ(А2<=9;0;"");ДЕНЬ(А1);" м;ЕСЛИ(А3=1;мянварям;ЕСЛИ(А3=2;мфевралям;ЕСЛИ(А3=3;ммарта"; ЕСЛИ(А3=4;"апреля";ЕСЛИ(А3=5;"мая";А5)))));" ";ГОД(А1);" года") после чего можно удалить ячейки А6:А7.
Написание суммы прописью
При заполнении различных документов наряду с суммой в числовом виде требуется указывать ее величину прописью.
Создадим электронную таблицу, которая автоматизирует данную работу. В примере рассмотрим только сумму, записываемую трехзначным числом, для больших чисел таблица создается аналогично.Алгоритм работы таблицы
В результате выполнения алгоритма таблица с числовыми значениями для сотен будет иметь вид: Е Microsoft Ехсе - Работа с датами и числами ? НЕ \т файл Правке i Вид Вставка Формат Сервис Данные Окно 1: Справка - & X ! J ЕЗ Я ,JI 1&14а С&Ч Ч Ч й, Е - Ы I ш • т i i10 . Ж ATJ_ й I % | Ш - А - А • i D4 fx =l ECnH(D3<=9;0;" ") А в с D Е 1 341,9 2 Сотни Десятки Единицы Копейки 3 3 34 341 90 4 3 4 1 Г~ 5 триста сорок один 6 триста сорок 7 триста сорок в девятьсот сорок 9 девятьсот девяносто 10 девятьсот девяносто 11 девятьсот девяносто 12 девятьсот девяносто 13 14 рубль 15 триста сорок один рубль 90 коп. 16 триста сорок один рубль 90 коп. 17 т 1В Т 19 риста сорок один рубль 90 коп. 20 Триста сорок один рубль 90 коп. | 21 V 4 > и '¦, Сумма прописью (десятки) ). | < > I 1 Готово NUM Рисунок 36
Введите в ячейку А1 введите произвольное трехзначное число.
Разделение числа на разряды.
Для определения, сколько целых миллионов, тысяч, сотен, десятков имеется в числе, необходимо разделить число на значение соответствующей разрядности и отбросить дробную часть. Например, для определения количества целых сотен число надо разделить на 100 и отбросить дробную часть результата: =ОТБР(А1/100;0)
Если число больше или равно 100, то мы получим количество сотен в этом числе. В противном случае результатом будет 0.
Определение значения каждого разряда.
Для этого выделяется младший разряд в числах, которые находятся в третьей строке. Это делается путем вычитания из них значений, которые находятся в ячейках левее, умноженные на 10. В ячейке В4 для единиц введена формула: =B3-A3*10
Формула для копеек несколько отличается: в случае, когда число копеек не превышает 9, перед цифрой будет добавляться ноль, а значит сумма, в которой указаны только целые рубли, должна выглядеть как «22 рубля 00 копеек».
В ячейку D4 введите формулу: =ЕСЛИ(Б3<=9;0;" ")Формирование числительных для каждого разряда.
Рассмотрим столбец Единицы - ячейка С5. Если в ячейках С4 и В4 находятся числа 1, то функция возвратит текст - одиннадцать. Если же значение 1 находится только в ячейке А4, а ячейка В4 содержит другое число, то возвращается текст 0 - один. Если в ячейке В4 находится число отличное от 1, то управление передается ячейке С6 и т.д. В ячейке С5 содержится формула: =ЕСЛИ(С4=1;ЕСЛИ(В4=1;"одиннадцать";"один");С6).
Рассмотрим столбец Десятки. Вначале проводится проверка ячейки В4 на наличие там значения 0. Если это значение присутствует, т.е. число десятков равно 0, то формула выдает пустое значение. Если же в ячейке В4 находится другое значение, то начинает работать первая функция ЕСЛИ, она проверяет, находится ли в ячейке В4 число 1, если это так, то необходимо посмотреть, какое значение находится в столбце Единицы - ячейка С4, если там находится 0, то формула выдает - десять, если же любое другое значение, то « ». Если в ячейке С4 - число отличное от 1, то управление передается в ячейку - В6. В ячейке В5 содержится формула: =ЕСЛИ(В4=0;" ";ЕСЛИ(В4=1;ЕСЛИ(С4=0;"десять";" ");В6))
Формирование названия разрядов.
Выполняется для тысяч и миллионов.
Формирование названия единицы измерения в соответствующем падеже.
Склоняется единица измерения - рубль. Формула в ячейке С14 имеет следующий вид:
=ЕСЛИ(В4+С4=0;"нуль рублей";ЕСЛИ(ПРАВСИМВ(С5;1)="н";"рубль";
ЕСЛИ(ПРАВСИМВ(С5;1)="а";"рубля";ЕСЛИ(ПРАВСИМВ(С5;1)="е";"рубля";
ЕСЛИ(ПРАВСИМВ(С5;1)="и";"рубля";"рублей")))))
Соединение всех компонентов надписи.
Применяются текстовые функции, которые обрабатывают и соединяют результаты предыдущих вычислений. В ячейку А15 введена формула: =СЦЕПИТЬ(А5;" ";В5;" ";С5;" ";С14;" ";D4;D3;" коп.")
Удаление лишних пробелов.
=СЖПРОБЕЛЫ(А15) - функция оставляет в тексте только одиночные пробелы, ее также можно применять для обработки текстов, полученных из других прикладных программ, если эти тексты содержат избыточные пробелы.
Запись итоговой суммы с большой буквы.
выделяется первый (самый левый символ): =ЛЕВСИМВ(А16;1)
назначается для этого символа прописная буква: =ПРОПИСН(А17)
извлекаются все символы, кроме первого =ПРАВСИМВ(А 18; ДЛСТР(А 18)-1)
соединяются первая прописная буква и оставшийся текст: =СЦЕПИТЬ(А 18; А19)
В итоге таблица c формулами будет иметь вид:
Ш Microsoft Excel - Работа с датами и числами I - Файл Правка Вид Вставка Формат Сервис Данные Окно Справка Введите вопрос - _ ff X1 и jd jj ii a j j ¦ -1 a, z - suj i is о - @ 1 ! Arial Cyr .10 .
Ж К H Ш ш Ш S | Щ % ООО fiS 1 W L B24 - f. A В | С D л 1 341,3 Сотни Десятки Единицы Копейки =ОТБР(А1ЛОО;3) =ОТБР(А1ЛО;3) =ОТБР(А1Л;3) =ОКРУГЛ((А1 -С3)*100;0) 4 -ОТБР(А1 Л 00;0) -В3-А3*13 =СЗ-ВЗ'10 =ЕСЛИ(03<=9;0;"") Ь =ЕСЛИ(А4=3;" ";ЕСЛИ(А4=1 ;"0Т0";А6)) =ЕСЛИ(1В14=3 " ";ЕСЛ И (В4=1; ЕСЛ И(С4=0;"дес ять";" ");В6)) =ЕСЛИ(1С14=1 ;ЕСЛИ(1В14=1 "одиннадцать"; "один"); С6) Ь =ЕСЛ И(1 AJ 4=2;"д в ести"; А7) =ЕСЛИ((В(4=2 "ДВаДЦаТЬ";В7) =ЕСЛ М (J С(4=2;ЕСЛ И (JB J4=1 "д в енадцать"; "д в а");С7) ) -ЕСЛИ((А$4-3;"триста";АВ) -ЕСЛИ((В(4-3 "тридцать";В8) -ЕСЛ И ($С$4-3;ЕСЛ И (JB J4-1 "тринадцать"; "три"); С8) и девятьсот =ЕСЛИ(1В14=4 "сорок";В9) =ЕСЛ И (1СИ=4,ЕСЛ И f JB 14=1 "четырнадцать"; "четыре");С9) 9 девятьсот =ЕСЛИ($ВН=5 "пятьдесят";В10) =ЕСПИ ($С$4=5;ЕСЛ И ($ВЗ;4=1 "п ятнадцать"; "п ять"); С10) I и девятьсот -ЕСЛИ((В(4-6 "шеотьдеоят";В11) -ЕСЛ И ($С$4-6;ЕСЛ И (JB J4-1 " ш еотнадцать";" ш есть"); С11) 11 девятьсот =ЕСЛИ(»В»4=7 "семьдесят"; В12) =ЕСЛ И (|С|4=7,ЕСЛ И (JB J4=1 "семнадцать";"семь");С12) 12 девятьсот =ЕСЛИ(11В114=8 " в осемьдес ят"; "дев яносто") =ЕСПИ(31С314=3;ЕСЛИ(31В314=1 " в осемнадцать";" в осемь"); С1 з; lb =ЕСЛ И ($С$4=9;ЕСЛ И (JB J4=1 "дев ятнадцать"; "дев ять");"") 14 =ЕСЛ И (В4+С4=3, "мул ЕСЛИ(ПРАВСИМВ(С5 ЕСЛ И (ПРАВСИ МВ(С5 ЕСЛ И (ПРАВСИ МВГС5 ЕСЛИГПРАВСИМВ(С5 ь рубл 1 )=""' 1 )-"а' 1 )="е'11='W ей";
"рубль"; "рубля"; "рубля";
"рубл я"; "рублей")))))
lb =ОрПИТЬ(А5;" ";В5;" ";С5;" ";С14;" ";D4;D3;" коп.")
1b =СЖПРОБЕЛЫ(А15)
\l =ЛЕВСИМВ(А16;1)
1B =ПРОПИСН(А17) И
V
19 =ПРАВС И МВ(А16; ДЛСТР(А1 б>1)
20 -ОрПИТЬ(А18;А19)
21
1 l< 4 > н \Сумма прописью (сотни)/ |< > | Готово NUM I
Рисунок 37
Тестирование таблицы
При вложении одной формулы в другую легко допустить ошибку. Для того чтобы избежать этого возможно использование средства Excel, позволяющего проследить зависимость значений в одних ячейках от формул и значений, находящихся в других ячейках.
Для определения зависимостей поместите табличный курсор в рассматриваемую ячейку и вызовите команду Сер- вис^Зависимости^Зависимые ячейки или Влияющие ячейки.
После этого между зависимыми ячейками появляются стрелки. Они показывают непосредственное влияние содержимого одних ячеек на формирование результата в других ячейках.При выборе команды Влияющие ячейки стрелки зависимостей показывают на ячейки, значения которых влияют на данную ячейку.
При выборе команды Зависимые ячейки стрелки будут указывать на ячейки, значения которых зависят от данной ячейки.
В случае, когда нужно проследить большое число зависимостей, удобно применять панель Зависимости.
|-ы I -a \ ф I л I ш a I d а
Использование зависимостей при вложении формул
Поместите табличный курсор в ячейку А3 и нажмите кнопку Зависимые ячейки.
Скопируйте в строке формул формулу из ячейки А3 без знака равенства.
В ячейках, на которые указывают стрелки (А4 и В4), произведите замену адреса ячейки скопированной формулой. После выхода из режима редактирования содержимого ячейки стрелка зависимости должна исчезнуть.
Проделайте эту же процедуру для диапазона ячеек В3:Б3.
Установите стрелки зависимостей для ячейки А4 и произведите в формулах зависимых ячеек аналогичную замену адресов ячеек содержащимися в них формулами.
Еще раз установите табличный курсор в ячейку А4 и проверьте, остались ли еще зависимые ячейки. Если нет, то содержимое ячейки А4 можно удалить.
Проделайте аналогичную операцию с диапазоном В4:Б4. Вложение формул с логическими функциями ЕСЛИ лучше начинать с самой внутренней. Но следует помнить, что для функции ЕСЛИ допускается не более 7 уровней вложения. Таким образом, на определенном этапе ячейку, которая влияет на другие ячейки и в которой находится сложная формула, нужно оставить и выполнить вложение формул в следующих зависимых от нее ячейках.
Фрагмент рабочего листа со стрелками, показывающими зависимость одних ячеек от других представлен на рисунке 38: ЕЗ Microsoft Ехсе - Работа с датами и числами I: l^j] Файл Правка Вид Вставка Формат Сервис Данные Окно Справка _ & х I ш ® J 1 : Arial Cyr .10 А D4 - fx =E(10H(D3<=9;0;" ") А В | С | D Е F G Л 1 r^-JtLS - 2 1 ли н и " Десятки— —Единицы Копейки 3 -—3 34- - " "зят -Я 90 4 Т > 1 1 5 триста сорок один / Б триста сорок / 7 триста сорок / В девятьсот сорок ^ / Э девятьсот девяно^р^о 10 девятьсот дев^осто 11 девятьсот девяносто 1 1 1 О 1 i> 1 12 девятьсот / девяносто 1 , 1 ^^^^^ J 13 / 14 у рубль 15 три?та сорок один рубль 90 коп. 16 триста сорок один рубль 90 коп 17 т 1В Т 19 риста сорок один рубль 9( D коп. 20 Триста сорок один pi/бль 90 коп. V 21 Н 4 > и \ Сумма прописью (сотни) / L< ] NUM Рисунок 38