С помощью панели символы создайте приведенные ниже формулы, разместив их после первого абзаца

                         

Лабораторная работа № 7

Тема работы: Организация расчетов в табличном процессоре. Использование функций в расчетах. Логические функции.

Цель работы: освоить базовые навыки работы в табличном редакторе MS Excel. Научиться использовать логические функции.

Ход работы:

Задание № 1

Создайте таблицу, содержа­щую расписание движения поездов от станции Бологое до станции Тверь. Сохраните таблицу в файле Расписание.xls.

 

 

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

1. Запустите табличный процессор.

2. Создайте новый документ или воспользуйтесь документом, созданным по умолчанию.

3. Заполните таблицу в соответствии с образцом:

• щелчком мыши выберите ячейку, в которую будете вво­дить текст;

• наберите текст и нажмите клавишу Enter;

• выберите следующую ячейку и повторите действия, опи­санные выше.

Чтобы числа при вводе были автоматически распознаны как время в часах и минутах, разделяйте часы и минуты двоето­чием, как показано на образце.

!Обратите внимание! Текст в ячейках автоматически выравнивается по левому краю, а время - по правому. Используйте это свойство табличного процессора, чтобы определить, правильно ли он рас­познал введенный текст.

4. Отредактируйте название станции в ячейке А4:

• выберите ячейку А4;

• для перехода в режим редактирования значения ячейки выполните одно из возможных действий: нажмите клави­шу F2, щелкните мышью в строке формул, где отображено значение ячейки, или дважды щелкните мышью в ячейке;

• замените слово Верхний на слово Вышний и нажмите кла­вишу Enter.

5. Аналогичным образом измените название станции Черноруково в ячейке А8 на Черногубово.

6. Выровняйте ширину столбца А так, чтобы был виден весь текст, помещенный в ячейку:

Способ 1 - с помощью мыши:

• Подведите указатель мыши к верхней строке таблицы, где размещены заголовки столбцов (буквы), и установите его на правой границе ячейки с буквой А, чтобы указатель при­нял вид вертикальной черты, пересеченной двусторонней горизонтальной стрелкой  -

• дважды щелкните левой кнопкой мыши на разделительной черте - в результате ширина столбца будет подобрана ав­томатически по самому длинному слову, размещенному в ячейках этого столбца.

Можно также перетащить границу столбца, нажав левую кнопку мыши и переместив указатель в нужную сторону.

Способ 2 - с помощью меню:

• Выделите столбец А, щелкнув мышью на его заголовке;

• Выберите команду меню Формат ► Столбец ► Автоподбор ширины.

7. Аналогичным образом выровняйте ширину столбцов В и С так, чтобы все значения ячеек были видны полностью.

8. Сохраните документ:

• Выберите команду меню Файл ► Сохранить как;

• В появившемся диалоговом окне выберите папку, в которой следует сохранить файл;

• В поле Имя файла укажите название файла — Расписание;

• В поле Тип файла оставьте тип, предложенный по умолчанию, — Книга Microsoft Office Excel (.xls);

• Щелкните на кнопке Сохранить.

Задание № 2

Дополните таблицу в файле Расписание.xls расчетами времени стоянок поезда в каждом населенном пункте. Вычислите сум­марное время стоянок, общее время в пути и время, затрачи­ваемое поездом на передвижение от одного населенного пункта к другому.

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

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

1. Переместите данные о времени отправления из столбца С в стол­бец D:

· Выделите диапазон С1:С8;

· Выберите команду меню Правка ► Вырезать; с установите курсор в ячейку D1;

· Выберите команду меню Правка ► Вставить; «подберите ширину столбца в соответствии с шириной текста.

2. Введите текст в ячейку С1:

· Выделите ячейку С1;

· Введите текст Стоянка;

· Подберите ширину столбца в соответствии с шириной текста.

3. Создайте формулу, вычисляющую время стоянки в населен­ном пункте:

· Выберите ячейку СЗ;

· Наберите на клавиатуре знак равенства;

· Для ввода адреса ячейки с первым значением (временем от­правления) щелкните левой кнопкой мыши в ячейке D3;

· Наберите на клавиатуре знак «минус»;

· Щелкните мышью в ячейке ВЗ;

· Нажмите клавишу Enter, чтобы завершить ввод формулы.

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

4. Скопируйте формулу из ячейки СЗ в каждую ячейку диапазо­на С4:С8 с помощью команды меню:

· Выделите ячейку СЗ, откуда надо скопировать формулу;

· Выберите команду меню Правка ► Копировать или аналогичную команду контекстного меню;

· Выделите диапазон ячеек С4:С8, куда надо вставить копии формулы;

· Выберите команду меню Правка ► Вставить или аналогичную команду контекстного меню.

!Обратите внимание! При копировании формулы относительные ссылки автоматически изменяются относительно ее нового место­положения в соответствии с правилом их вхождения в формулу.

5. Введите текст в ячейку Е1:

· выделите ячейку Е1;

· введите текст Время в пути;

· выровняйте ширину столбца в соответствии с шириной текста.

6. Создайте формулу расчета времени, затрачиваемого поездом на передвижение от одного населенного пункта к другому:

· выберите ячейку ЕЗ;

· наберите на клавиатуре знак равенства;

· введите адрес ячейки ВЗ;

· наберите на клавиатуре знак «минус»;

· введите адрес ячейки D2;

· нажмите клавишу Enter.

7. Скопируйте формулу из ячейки ЕЗ в каждую ячейку диапазо­на Е4:Е9 с помощью мыши:

· выделите ячейку ЕЗ, откуда надо скопировать формулу;

· наведите указатель мыши на правый нижний угол этой ячейки - указатель примет вид черного креста

· нажмите левую кнопку мыши и протащите рамку вниз на диапазон Е4:Е9.

8. Измените формат ячеек для диапазонов С2:С10 и Е2:Е10 (этот пункт следует выполнять только в том случае, если необходи­мо изменить формат представления времени):

· Выделите диапазон ячеек С2:С10;

· Выберите команду меню Формат ► Ячейки;

· В появившемся диалоговом окне на вкладке Число выберите формат Время, тип 13:30 (часы:минуты);

· Нажмите кнопку ОК;

· Выделите диапазон ячеек Е2:Е10 и повторите указанные дей­ствия.

9. Введите текст в ячейку А10:

· Выберите ячейку А10;

· Введите текст Общее время стоянок;

· Выровняйте ширину столбца в соответствии с шириной текста.

10. Создайте формулу для вычисления общего времени стоянок поезда:

· Выберите ячейку СЮ;

· Нажмите кнопку Автосумма на панели инструментов;

· Выделите мышью диапазон ячеек СЗ:С8 и нажмите клавишу Enter.

11. Удалите содержимое ячейки СЗ:

· Выберите ячейку СЗ;

· Выберите команду меню Правка ► Очистить или нажмите на клавишу Delete на клавиатуре.

Обратите внимание: компьютер автоматически пересчитает сум­му в ячейке С9.

12. Выберите команду меню Правка ► Отменить, чтобы отменить уда­ление.

13. Введите текст в ячейку A11:

· Выберите ячейку A11;

· Введите текст Общее время в пути:.

14. Создайте формулу для вычисления общего времени в пути:

· Выберите ячейку Е11;

· Наберите на клавиатуре знак равенства;

· Щелкните в ячейке В9, содержащей время прибытия в ко­нечный пункт;

· Наберите на клавиатуре знак «минус»;

· Щелкните в ячейке D2, содержащей время отправления из начального пункта;

· Нажмите клавишу Enter.

Задание № 3

Отформатируйте таблицу. Используйте возможности, предоставляемые средой таблично­го процессора:

· форматирование текста;

· изменение ширины и высоты столбца;

· задание формата чисел;

· задание типа выравнивания;

· отрисовку рамки и заливки;

· автоформатирование таблицы;

· организацию защиты данных.

В результате работы должна получиться таблица, представ­ленная на рисунке.

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

1. Вставьте две дополнительные строки для создания заголовка таблицы:

• выделите строку 1;

• выберите два раза команду меню Вставка ► Строки.

2. В ячейку А1 введите курсивом текст Железные дороги:

· выделите ячейку А1;

· нажмите кнопку Курсив на панели инструментов;

· наберите текст Железные дороги.

3. В ячейку А2 введите с клавиатуры текст Расписание движе­ния поезда Бологое -Тверь;

· выделите ячейку А2;

· с помощью панели инструментов оформите ячейку шриф­том размера 12, полужирного начертания;

· наберите текст;

· выровняйте текст в ячейке по центру.

4. Отформатируйте заголовки столбцов расписания:

· выделите строку 3;

· выберите команду меню Формат ► Ячейки;

· в появившемся диалоговом окне перейдите на вкладку Шрифт;

· выберите полужирное начертание шрифта;

· перейдите на вкладку Выравнивание;

· выберите горизонтальное выравнивание По центру;

· примените выбранные параметры, нажав кнопку ОК.

5. Задайте ширину каждого столбца так, чтобы она соответство­вала длине заголовков.

6. Задайте высоту строки 2:

· выделите строку 2;

· выберите команду меню Формат ► Строка ► Высота;

· в появившемся диалоговом окне задайте высоту строки — 22;

· нажмите кнопку ОК.

7. Задайте отрисовку границ и заливку для диапазона ячеек A2:F2, используя контекстное меню:

• выделите диапазон ячеек A2:F2;

• вызовите щелчком правой кнопки мыши контекстное меню;

• выберите в контекстном меню команду Формат ячеек;

• в появившемся диалоговом окне перейдите на вкладку Граница;

• в поле Тип линии выберите образец с самой широкой линией;

• выберите в качестве обрамления внешние границы ячеек;

• перейдите на вкладку Вид;

• выберите зеленый цвет заливки;

• перейдите на вкладку Шрифт;

• выберите белый цвет шрифта;

• примените выбранные параметры, нажав кнопку ОК.

8. Задайте автоформат таблицы:

• выделите диапазон ячеек A3:F13;

• выберите команду меню Формат ► Автоформат;

• в появившемся диалоговом окне выберите подходящий тип автоформата;

• нажмите кнопку ОК.

9. Установите для ячеек А12 и А13 перенос текста по словам:

• выделите диапазон ячеек А12:А13;

• вызовите контекстное меню и выберите в нем команду Фор­мат ячеек;

• в появившемся диалоговом окне выберите вкладку Выравни­вание;

• установите флажок напротив параметра переносить по словам;

• нажмите кнопку ОК.

10. Подберите такую ширину столбца А, чтобы текст в ячейках строк 12 и 13 располагался в 2 строки.

11. Включите защиту таблицы:

• выберите команду меню Сервис ► Защита ► Защитить лист;

• в появившемся диалоговом окне установите флажок Защи­тить лист и содержимое защищаемых ячеек;

• в поле Разрешить всем пользователям этого листа сбросьте все флаж­ки, кроме выделение незаблокированных ячеек и выделение заблоки­рованных ячеек;

• нажмите кнопку ОК.

12. Попробуйте ввести в ячейку D4 новое число — появится окно с сообщением о блокировке. Нажмите кнопку ОК.

13. Отключите защиту листа, выбрав команду меню Сервис ► Защита ► Снять защиту листа.

14. Сделайте ячейку D4 незащищаемой:

• выделите ячейку D4;

• выберите команду меню Формат ► Ячейки;

• перейдите на вкладку Защита;

• сбросьте флажок Защищаемая ячейка;

• щелкните на кнопке ОК.

15. Снова защитите лист, повторив действия, указанные в п. 13.

16. Попробуйте ввести в ячейку D4 другое время отправления, на­пример 19:15, — предупреждение не появится, так как те­перь эта ячейка является незащищенной.

17. Сохраните файл командой Файл ► Сохранить

Задание № 4:

В таблице Расписание.xls рассчитайте стоимость железнодо­рожных билетов для поездов разных категорий: пассажирские, скорые, фирменные. Цена билета зависит от времени движе­ния поезда к пункту назначения и категории поезда. Каждой категории поезда соответствует определенный коэффициент, на который следует умножать время в пути (в минутах) для расчета цены билета: пассажирский — 0,5 р., скорый — 1,5 р., фирменный — 1,7 р. Организуйте таблицу Расписание.xls так, чтобы пересчет цены билета можно было бы осуществлять, изменяя значение ячейки Категория.

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

1. Запустите табличный процессор.

2. Откройте документ Расписанием.

3. Для задания категории поезда выполните следующие дейст­вия:

· выберите ячейку D1 и введите текст Категория;

· выберите ячейку Е1 и введите первоначальное значение ка­тегории, равное 1.

4. Дополните таблицу столбцом Цена (см. рис. 4.11): ф выберите ячейку F3;

· введите текст Цена и нажмите клавишу Enter.

5. Задайте денежный формат чисел для диапазона ячеек F4:F11, используя контекстное меню:

· выделите диапазон ячеек F4:F11;

· вызовите контекстное меню и выберите в нем команду Фор­мат ячеек;

· в появившемся диалоговом окне выберите вкладку Число;

· выберите в списке числовых форматов тип Денежный;

· если обозначение денежных единиц р. не будет выбрано ав­томатически, выберите его в списке Обозначение;

· в поле Число десятичных знаков укажите 2 знака;

· нажмите кнопку ОК.

6. Создайте в ячейке F5 логическую формулу для расчета стои­мости билета.

Договоримся, что значение ячейки Категория для пассажирско­го поезда — 1, для скорого — 2, для фирменного — 3. Струк­тура формулы должна быть следующей:

=ЕСЛИ($Е$1=1;МИНУТЫ(Е5)*0,5;ЕСЛИ($Е$1=2;МИНУТЫ(Е5)*1,5; ЕСЛИ($Е$1=3;МИНУТЫ(Е5)*1,7;"Неверное значение категории")))

Рассмотрим последовательность действий по созданию этой формулы:

· выделите ячейку F5;

· выберите команду меню Вставка ► Функция;

· в открывшемся диалоговом окне выберите категорию - Ло­гические, функцию - ЕСЛИ и нажмите кнопку ОК;

· в окне ввода аргументов функции в поле Лог_выражение на­берите $Е$1=1: щелкните мышью в ячейке Е1, нажмите кла­вишу F4, затем введите =1;

· в поле Значение_если_истина введите МИНУТЫ(Е5)*0,5;

· нажмите кнопку ОК;

· скопируйте полученную формулу ЕСЛИ($Е$1=1;МИНУТЫ(Е5)*0,5), добавьте в нее перед последней скобкой точку с запятой и вставьте после нее скопированную формулу, заменив зна­чение категории на 2, а коэффициент — на 1,5;

· добавьте после числа 1,5 еще одну точку с запятой и снова вставьте скопированную формулу, заменив значение кате­гории на 3, а коэффициент — на 1,7;

· добавьте после числа 1,7 точку с запятой и вставьте текст "Неверное значение категории";

· нажмите клавишу Enter;

· сверьте структуру формулы с образцом.

7. Скопируйте формулу из ячейки F5 в каждую ячейку диапазо­на F6:F11, используя раскрывающееся меню кнопки Вставить на панели инструментов:

· выделите ячейку F5, откуда надо скопировать формулу;

· щелкните на кнопке Копировать на панели инструментов;

· выделите диапазон ячеек F6:F11;

· щелкните на стрелке справа от кнопки Вставить и выберите в открывшемся меню команду Формулы.

8. Изменяя значение категории поезда в ячейке Е1, проанализи­руйте результаты.

9. Измените ширину столбца F так, чтобы его ширина точно со­ответствовала длине заголовка.

10. Измените ширину столбца F так, чтобы числовые данные были отображены полностью.

11. Сохраните документ и закройте табличный процессор коман­дой Файл ► Выход.

 

 

Лабораторная работа № 8