Основные функции MS Excel. Практикум №4

Практикум № 4

1. Функции даты и времени

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

·                    Технологии создания и форматирования табличного документа;

·                    Использовать встроенные  функции даты и времени Excel для расчетов.

Задание.

Технология выполнения работы:

1.       Запустить приложение Excel.

2.       В ячейку A1 введите следующую формулу, которая при помощи функции СЕГОДНЯ отображает текущую дату: =”Сегодня:”&ТЕКСТ(СЕГОДНЯ();”ДДММММГГГГ”). В ячейку A2:A4 введите текст по образцу

3.       В ячейки введите числа, которые соответствуют сегодняшнему дню, месяцу, году.

4.       Для того чтобы из года (ячейка В2), месяца (ячейка ВЗ) и дня (ячейка В4) собрать дату, введите в ячейку В5 формулу =ДАТА(В2;ВЗ;В4).

5.       А чтобы из даты (ячейка В5) извлечь год, месяц и день, в ячейки В6, В7 и В8 введите соответственно следующие формулы: =”Год:”&ГОД(В5), =”Месяц:”&МЕСЯЦ(В5), =”День:”&ДЕНЬ(В5).

6.       Введите в ячейки C1 и С2 текст, а в ячейки D1 и D2 даты, выделив эти ячейки и измените тип данных. Для этого: Формат ячеек – Число – Дата – ЧЧ.ММ.ГГГГ (14.03.2001).

7.       Предположим, вам надо посчитать продолжительность работы в днях между начальной (ячейка D1) и конечной датами (ячейка D2). Для этого можно ввести в ячейку D3 формулу

8.       =D2-D1+1. Но ведь нам надо знать количество дней в числовом формате. Тогда формула должна быть вот такой: =ТЕКСТ(D2-D1+1;"###"). Здесь мы применяем автоматическое форматирование.

А если мы хотим узнать, каким по порядку днем года является данная дата (ячейка D4) (см. как в образце)? Очень просто: в ячейку D5 введите формулу =D4-ДАТА(ГОД(D4);1;0), которая находит разность между данной датой и 1 января того же года. В результате может получиться значение в виде даты, выделив ячейку D5 переведем в числовой формат. Формат – Ячеек – Число – Числовой тип и Знаков после запятой – 0.

9.       Или вот такая задача: определить, является ли данная дата (ячейка D4) субботой или воскресеньем? Введите в ячейку D6 следующую формулу: =ЕСЛИ(ИЛИ(ДЕНЬНЕД  (D4;2)=6;ДЕНЬНЕД(D4;2)=7);”Суббота или Воскресенье”;””). В ней применена функция ДЕНЬНЕД, которая возвращает номер дня недели заданной даты. Если указанная дата попала на субботу или воскресенье, то именно это и будет указано в ячейке, если нет, то не будет указано ничего.

10.   Для определения текущего времени надо из текущих даты и времени, возвращаемых в числовом формате функцией ТДАТА, вычесть текущую дату (функция СЕГОДНЯ), а результат (ячейка В11) перевести в формат времени: =ТЕКСТ(ТДАТА()-СЕГОДНЯ();"ЧЧ:ММ:СС").

11.   Введите в ячейки B12, B13, B14 значения часов, минут и секунд в соответствии с образцом (рис. 1). Чтобы из часов (В12), минут (В13) и секунд (В14) «собрать» время, введите в ячейку В15 формулу =ВРЕМЯ (В12;В13;В14). В ячейки A17 – A19, B17 – B19 введите данные в соответствии с образцом (рис. 1). А чтобы найти суммарный промежуток времени (В17, В18, В19), в ячейку В20 введите формулу =СУММ(В17:В19). Если суммарный промежуток времени, как для данных из диапазона D17:D19, больше 24 часов, то простого суммирования будет недостаточно. В этом случае результат надо отформатировать (D20), причем часовую составляющую заключить в квадратные скобки, как это сделано в формуле =ТЕКСТ(СУММ (D17:D19);“[Ч]:ММ”).

1.    Форматирование ячеек в табличном процессоре Excel.

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

·                     Изучить основные возможности форматирования таблиц на примере простого бланка-счета за ремонт телевизора.

Задание.

1.                  Запустить приложение Excel.

2.                  Изучим основные возможности форматирования таблиц на примере простого бланка-счета за ремонт телевизора.

Технология выполнения:

•   Введите в ячейку А1 строку «Счет за ремонт телевизора».

•   Введите в ячейку A3 символ «№», в А4 - 1, в А5 - 2.

•   Введите в ячейку ВЗ строку «Наименование работ», в В4 - Замена кинескопа, в В5 - Ремонт антенны, в В6 - Итого:, в В7 - НДС:, в В8 - Спецналог, в В9 - К оплате:.

•   Введите в ячейку СЗ строку «Стоимость работ», в С4 - 200, в С5 -15.

Изменение ширины столбцов.

Обратите внимание, строка «Стоимость работ» закрывает собой строку «Наименование работ». Исправим это. Но сначала уменьшите ширину столбца А вручную, так, чтобы можно было наблюдать содержимое столбца. Столбцы В и C подгоните с помощью Автоподбор ширины по содержимому столбцов.

Выбор ширины и выравнивание текста.

Первое, что обычно делают при форматировании таблиц, - это выбирают шрифт для заголовков, чисел и пояснений.

•   Щелкните мышью по ячейке А1 и выберите шрифт Arial и размер шрифта 14.

•   Щелкните мышью на заголовке третьей строки. Выделится вся строка. Назначьте выделенной строке шрифт Times New Roman размером 12 пунктов. При этом строки в ячейках ВЗ и СЗ выйдут за пределы столбцов. Подберите ширину столбцов В и C.

•   Отцентрируйте текст в ячейках строки 3, нажав кнопку По центру.

•   Выделите диапазон ячеек А1:С1 и нажмите кнопку - Объединить и поместить в центре на панели инструментов Выравнивание. Текст будет выровнен по центру выделенного диапазона.

•   Выделите диапазон ячеек В6:В9 и выровняйте текст в ячейках по правому краю, нажав кнопку По правому краю. Начертание шрифта Полужирное.

•   Подсчитайте, сколько будет стоить замена кинескопа и ремонт антенны, и результат занесите в ячейку Итого:. Для этого выделите диапазон ячеек С4:С6 и щелкните по кнопке Автосумма на панели Стандартная. В ячейке С6 появится результат вычислений «215».

•   Введите в ячейку С7 формулу для вычисления НДС «=С6*0,2».

•   Введите в ячейку С8 формулу для вычислений спецналога «=С6*0,015».

Выделите диапазон ячеек С6:С9 и щелкните мышью по кнопке Автосумма. В ячейку С9 будет вставлен результат вычислений «261,225»

Наш бланк почти готов. Осталось отформатировать числа и установить обрамление и фон ячеек, чтобы придать бланку «товарный вид».

Форматирование чисел.

Суммы чисел в нашем бланке плохо различимы из-за большого количества нулей. Непонятно также, в какой валюте выписан счет.

•   Выделите диапазон ячеек С4:С9.

•   Щелкните по выделенному фрагменту правой кнопкой мыши и выберите из контекстного меню команду Формат ячеек.

•   В диалоговом окне щелкните мышью на ярлычке с надписью Число, в списке Числовые форматы выберите формат Денежный.

•   Выделенные суммы отобразятся с разделением тысяч и символом «р.» в конце.

Обрамление и фон ячеек.

Заключительный этап оформления нашего бланка - в подчеркивание заголовков и выделение цветом итоговой суммы.

•   Выделите диапазон ячеек А1 :С9.

•   Выберите команду меню Формат ячеек. В диалоговом окне выберите вкладку Граница.
•   Щелкните мышью на изображении двойной линии в поле Тип линии и затем в поле Все щелкните по кнопке Внешние. В поле Отдельные появится образец обрамления двойной линией. - ОК.

•   Выделите диапазон ячеек АЗ:СЗ.

•   Щелкните мышью на панели Форматирование по пиктограмме Границы и выберите там шаблон с толстой черной линией снизу. Выделенный диапазон ячеек будет подчеркнут толстой черной линией.

•   Выделите диапазон ячеек ВЗ:В9 и создайте тонкую черную черту справа с помощью пиктограммы Границы.

•   Выделите диапазон ячеек А9:С9.

•   Откройте цветовую палитру (на панели Шрифт пиктограмма Цвет заливки) для выбора цвета фона. Щелкните мышью на квадратике желтого цвета в палитре. Фон выделенного диапазона ячеек станет желтым. Черные цифры на желтом фоне хорошо видны на экране, а при печати на черно-белом принтере желтый цвет будет выглядеть как светло-серый. На этом создание нашего бланка закончено.

Изменение информации в бланке.

Посмотрите внимательно на свой бланк и представьте, что в связи с изменением цен вам нужно изменить стоимость работ по замене кинескопа с 200 на 250. Для этого выделите ячейку С4 и введите новую стоимость 250- Enter. Проанализируйте, как изменились остальные значения.

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

•   Выберите вкладку Разметка страница - Параметры страницы. Появится диалоговое окно.
•   Выберите вкладку Страница. Установите опцию Книжная в поле Ориентация.

•   Откройте список Размер бумаги и выберите строку А4 210*297 мм.

•   Выберите вкладку Поля. Установите желаемые отступы от краев листа бумаги с помощью полей Верхнее, Нижнее, Левое, Правое или оставьте прежние.

•   В поле Центрировать на странице установите флажок Горизонтально и сбросьте флажок Вертикально. Теперь наш бланк будет размещен в верхней части листа и выровнен по центру ширины листа.

•   Выберите вкладку Колонтитулы. Колонтитулы - это первая и последняя строки документа, которые используются для размещения служебной информации: номеров страниц, названия документа, даты его создания и т. д. Для нашего бланка ничего этого не нужно, поэтому просто уберем колонтитулы. Для этого откройте список Верхний колонтитул и выберите строку (нет). То же самое сделайте со списком Нижний колонтитул.
•   Выберите вкладку Лист и в группе Печать сбросьте флажок Сетка (если он не сброшен), чтобы на печать не выводилась сетка, разделяющая ячейки таблицы.

•   Нажмите кнопку Просмотр. На экране появится изображение листа бумаги с нашим бланком. Это режим предварительного просмотра. Для выхода из просмотра - кнопка Закрыть.

Если вы сделали все правильно, то должны получить бланк следующего вида.

Выведем бланк-счет на печать, нажав на панели Стандартная кнопку Печать.

Контрольные вопросы

1.                  Как изменить ширину столбца двумя способами?

2.                  Как вычислить сумму нескольких значений в столбце?

3.                  Как придать числу денежный стиль?

4.                  Как обрамить ячейки?

5.                  Как придать ячейкам фон?