Обратите внимание! При автозаполнении адрес ячейки B1 не меняется, т.к. установлена абсолютная ссылка.

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

Работа в табличном редакторе MS Excel

Цель работы:освоить основные возможности табличных редакторов.

Основные вопросы:

1.Создание и редактирование таблиц в табличном редакторе.

2.Форматирование данных и формул.

3.Создание и редактирование относительных и абсолютных формул для расчётов;

4.Работа с основными функциями по расчёту и аналитики данных MS Excel.

5.Статистические функции.

Выполнение лабораторной работы

Внимание:После выполнения лабораторной работы должно получиться 4 таблицы на разных листах в одном документе. Другими словами – каждая таблица создается на отдельном листе в одном документе.

1-й Лист: Расписание пассажирского поезда, 2-й лист: Потребление электроэнергии ТСЖ, 3-й лист: мой возраст; 4-й Лист: Возраст моих одногрупников.

После выполнения задания и самостоятельной работы необходимо отправить готовый документ (название: №группы, ФИО) с электронной почты в Вашем личном кабинете портала mfua.ru на электронную почту преподавателя: 29203617@s.mfua.ru с темой в письме: № группы, ФИО студента, дата.

Задание №1 «MS Excel. Создание и редактирование табличного документа»

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

· Создавать и заполнять данными таблицу;

· Форматировать и редактировать данные в ячейке;

· Использовать в таблице простые формулы;

· Копировать формулы в другое место.

1) Задание: Создайте таблицу, содержащую расписание движения поездов от станции Нью-Васюки до станции Новобобановск. Общий вид таблицы «Расписание» отображен на рисунке.

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

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

2. Заполните таблицу в соответствии с образцом, приведенным на рисунке. Для этого выполните следующие действия:
• выберите ячейку А1(щелкните по ней левой кнопкой мыши) куда будете вводить текст;
• наберите текст и нажмите клавишу Enter;
• заполните столбец А;

Внимание!Текст в ячейках выравнивается по левому краю, а числа – по правому!

• Выберите ячейкуВ1, наберите текст по образцу рисунка. Заполните столбец В.Текст столбца А закрывается столбцом В, не пугайтесь и оставьте все, как есть, далее мы все отформатируем.
• Заполните столбец С.

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

С помощью мыши:
• Подведите указатель мыши к верхней строке таблицы, где размещены заголовки столбцов (буквы), установите указатель на границе (черте) ячейки А, так чтобы указатель изменился на черную вертикальную черту с двусторонней горизонтальной стрелкой ; Два раза щелкните кнопкой мыши по разделительной черте, в результате чего ширина столбца выровняется по размещенной в нем самой длинной строке (название станции).
• Можно добиться того же самого, если нажать левую кнопку мыши и удерживая протащить границу столбца вправо или влево.

Через главное меню: • Выделите столбец А, щелкнув мышью по его заголовку; • Выполните команду основного меню Главная – группа ячейки – Автоподбор ширины столбца (Автоподбор высоты строки); • Аналогично выровняйте ширину столбцов В и С.

Секреты выравнивания ячеек, столбцов, строк

В вышеизложенном практикуме, мы выравнивали разными способами столбцы по содержимому в ячейках, предположим что нам надо выровнять все столбцы в таблице так, чтобы все столбцы были одинаковы по размеру.
Это можно сделать очень легко и просто. Для этого выделяем нужное количество столбцов и регулируем размер любого из них (это может быть и крайний, и расположенный в центре). Вот и всё, остальные приняли размер того столбца, который Вы подправили.
Со строками дело обстоит точно также.

2) Задание: Дополните таблицу «Расписание» расчетами времени стоянок поезда в каждом населенном пункте. Вычислите суммарное время стоянок, общее время в пути, время, затрачиваемое поездом на передвижение от одного населенного пункта к другому.

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

1. Переместите столбец «Время отправления» из столбца С в столбец D. Для этого выполните следующие действия:
• Выделите блок C1:C7; выберите команду Главная – группа Буфер обмена – Вырезать.
• Установите курсор в ячейку D1;
• Выполните команду Главная – группа Буфер обмена – Вставить;
• Выровняйте ширину столбца в соответствии с размером заголовка.;

2. Введите текст «Стоянка» в ячейку С1. Выровняйте ширину столбца в соответствии с размером заголовка.

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

• Выберите ячейку С3;
• Наберите знак «=»;
• Для ввода адреса ячейки, содержащей необходимое значение, щелкните левой кнопкой по ячейке D3;
• Наберите на клавиатуре «-»;
• Введите адрес ячейки В3, щелкнув по ней левой кнопкой мыши; в результате в строке формулы появится формула =D3-B3, нажмите клавишу Enter; и в ячейке С3 – результат вычисления по формуле.

4. Необходимо скопировать формулу в блок С4:С7, используя маркер заполнения. Для этого выполните следующие действия:

• Вокруг активной ячейки имеется рамка, в углу которой есть маленький прямоугольник, ухватив его, распространите формулу вниз до ячейки С7.

5. Введите в ячейку Е1 текст «Время в пути». Выровняйте ширину столбца в соответствии с размером заголовка.

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

• Выберите ячейку Е3;
• Наберите знак «=»;
• Введите адрес ячейки В3;
• Наберите на клавиатуре «-»;
• Введите адрес ячейки D2, в результате в строке формулы появится формула =B3-D2, нажмите клавишу Enter; и в ячейке E3 – результат вычисления по формуле.
• Заполните блок ячеек Е4:Е8, с помощью команд основного меню (Главная – группа Буфер обмена – Копировать и Главная – группа Буфер обмена – Вставить).

7. Измените формат чисел для блоков С2:С9 и Е2:Е9. Для этого выполните следующие действия:

• Выделите блок ячеек С2:С9;
• Выполните команду основного меню Главная – группа Число – Числовые форматы – Другие числовые форматы - Время – установите параметры тип 13:30 (часы:минуты).
• Нажмите клавишу Enter.

8. Вычислите суммарное время стоянок.
• Выберите ячейку С9;
• Щелкните кнопку Автосумма на ленте Главная – группа Редактирование;
• Подтвердите выбор блока ячеек С3:С8 и нажмите клавишу Enter.

9. Введите текст в ячейку В9. Для этого выполните следующие действия:

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

10. Удалите содержимое ячейки С3.

• Выберите ячейку С3;
• Выполните команду основного меню на ленте Главная – группа Редактирование – Очиститьили нажмите Delete на клавиатуре;
Внимание!Компьютер автоматически пересчитывает сумму в ячейке С9!!!

• Выполните команду основного меню ленты Главное – группа Отменить – стрелка влево или нажмите соответствующую кнопку на панели инструментов.

11. Введите текст «Общее время в пути» в ячейку D9.

12. Вычислите общее время в пути. Для этого выполните следующие действия:

• Выберите ячейку Е9;
• Наберите знак «=»;
• Введите адрес ячейки В8, содержащей время прибытия в конечный пункт;
• Наберите на клавиатуре «-»;
• Введите адрес ячейки D2, содержащей время отправления.
• Нажмите клавишу Enter.

13. Сохраните документ, нажав кнопку Сохранить на панели инструментов.

Задание 2. "Ссылки. Встроенные функции MS Excel".

Данное задание выполняется на Листе 2!

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

· Выполнять операции по копированию, перемещению и автозаполнению отдельных ячеек и диапазонов.

· Знакомство со ссылками на данные: абсолютной, относительной, смешанной и их использование в расчетах.

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

MS Excel содержит 320 встроенных функций. Простейший способ получения полной информации о любой из них заключается в использовании меню Справка (Файл - Справка). Для удобства функции в MS Excel разбиты по категориям (математические, финансовые, статистические и т.д.). Обращение к каждой функции состоит из двух частей: имени функции и аргументов в круглых скобках.

Таблица. Встроенные функции MS Excel

Функции	Вид записи	Назначение
Математические	КОРЕНЬ(...)	Вычисление квадратного корня
ABS(...)	Вычисление абсолютного значения (модуля) числа
ЦЕЛОЕ(...)	Округление числа или результата выражения, указанного в скобках, до ближайшего меньшего (!) целого
ПИ( ) *	Значение математической константы «ПИ» (3,1415926...)
НОД(…)	Наибольший общий делитель нескольких чисел
НОК(…)	Наименьшее общее кратное нескольких чисел
СЛЧИС( ) *	Вычисление случайного числа в промежутке между 0 и 1
Статистические	МИН(...)	Определение минимального из указанных чисел
МАКС(…)	Определение максимального из указанных чисел
СРЕДНЕЕ(...)	Определение среднего значения указанных чисел
СУММ(...)	Определение суммы указанных чисел
Дата и время	СЕГОДНЯ ( ) *	Значение сегодняшней даты в виде даты в числовом формате
МЕСЯЦ(дата)	Вычисление порядкового номера месяца в году по указанной дате
ДЕНЬ(дата)	Вычисление порядкового номера дня в месяце по указанной дате
ГОД(дата)	Вычисление года по указанной дате
Логические	И(условие1; условие2;...)	Вычисление значения (ИСТИНА, ЛОЖЬ) логической операции И
ИЛИ(условие1; условие2;...)	Вычисление значения (ИСТИНА, ЛОЖЬ) логической операции ИЛИ
ЕСЛИ(условие; знач_ИСТИНА; знач_ЛОЖЬ)	Вычисление значения в зависимости от выполнения условия

* Записывается без аргументов.

Заданы стоимость 1 кВт./ч. электроэнергии и показания счетчика за предыдущий и текущий месяцы. Необходимо вычислить расход электроэнергии за прошедший период и стоимость израсходованной электроэнергии.

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

1. Введите текст в строку 1.

2. Введите текст в строку 3.

3. Задайте фиксированную ширину строк. Выделите столбцы А:Е. Затем щелкните правой кнопкой мыши по названию одного из выделенных столбцов (на букву), после чего в выпадающем меню выберете Ширина столбца. Введите значение 15 в открывшемся окне Ширина столбцаи нажмите ОК.

4. Выровняйте текст в ячейках. Выделите ячейки А3:Е3. Главная – группа Выравнивание – Выравнивание: по горизонтали – по центру, по вертикали – по центру, отображение – переносить по словам.

5. В ячейку А4 введите: Кв. 127, в ячейку А5 введите: Кв. 128. Выделите ячейки А4:А5 и с помощью маркера автозаполнения (после выделения ячеек подведите курсор мыши в нижний правый угол ячейки А5, где точка, курсор мыши должен стать в виде чёрного крестика, затем нажмите на левую кнопку мыши и потяните курсор мыши вниз, пустые ячейки снизу начнут заполняться сами) заполните нумерацию квартир по 157 включительно.

6. Заполните ячейки B4:C6 по рисунку.

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

8. В ячейку E4 введите формулу =D4*$B$1. И заполните строки ниже с помощью маркера автозаполнения.

Обратите внимание! При автозаполнении адрес ячейки B1 не меняется, т.к. установлена абсолютная ссылка.

Таблица. Виды ссылок

Название	Запись	При копировании	Технология ввода
Относительная	C3	Меняется в соответствии с новым положением ячейки	Щелкнуть в ячейке
Абсолютная	$C$3	Не меняется	Щелкнуть в ячейке и нажимать F4 до преобразования адреса к нужному виду
Смешанная	С$3	Не меняется номер строки
$C3	Не меняется имя столбца

9. В ячейке А35 введите текст «Статистические данные» выделите ячейки A35:B35 и щелкните на ленте Главная – группа Выравнивание кнопку «Объединить и поместить в центре».

10. В ячейках A36:A39 введите текст, указанный на рисунке.

11. Щелкнуть мышью по ячейке B36 и ввести математическую функцию СУММ, для этого необходимо щелкнуть в строке формул по знаку fx и выбрать функцию, а также подтвердить диапазон ячеек.

12. Аналогично функции задаются и в ячейках B37:B39.

13. Расчеты вы выполняли на Листе 2, переименуйте его в Потребление электроэнергии ТСЖ.

14. Сохраните результат своей работы.

Задание 3. «MS Excel. Статистические функции»

На Листе 3 выполняется 1-е задание (расчет моего возраста), 2-е задание выполняется на листе 4 – Возраст моих одногрупников.

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

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

· Присваивать тип к используемым данным;

· Созданию формулы и правилам изменения ссылок в них;

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

1) Задание. Рассчитать количество прожитых дней.

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

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

2. В ячейку A1 ввести дату своего рождения (число, месяц, год – 20.12.81). Зафиксируйте ввод данных.

3. Просмотреть различные форматы представления даты (Главная – группа Число – Числовой формат – Другие числовые форматы – Числовые форматы - Дата). Перевести дату в тип ЧЧ.ММ.ГГГГ.Пример, 14.10.2016

4. Рассмотрите несколько типов форматов даты в ячейке А1.

5. В ячейку A2 ввести сегодняшнюю дату.

6. В ячейке A3 вычислить количество прожитых дней по формуле =A2-A1. Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой тип. (Главная – группа Число – Числовой формат – Другие числовые форматы – Числовые форматы – Числовой – число знаков после запятой – 0).

7. Переименуйте Лист 3 в мой возраст. Сохраните Ваши результаты работы.

Задание 2.

Возраст моих одногруппников. По списку студентов и дат их рождения. Определить, кто родился раньше (позже), определить кто самый старший (младший).

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

На своё усмотрение заполните таблицу «Возраст учащихся». Количество учащихся должно быть не менее 18, дату рождения укажите любую.

2. Рассчитаем возраст учащихся. Чтобы рассчитать возраст необходимо с помощью функции СЕГОДНЯ выделить сегодняшнюю текущую дату из нее вычитается дата рождения учащегося, далее из получившейся даты с помощью функции ГОД выделяется из даты лишь год. Из полученного числа вычтем 1900 – века и получим возраст учащегося. В ячейку D3 записать формулу =ГОД(СЕГОДНЯ()-С3)-1900. Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой тип. (Главная – группа Число – Числовой формат – Другие числовые форматы – – Числовые форматы – Числовой – число знаков после запятой – 0).

3. Определим самый ранний день рождения. В ячейку C22 записать формулу =МИН(C3:C21);

4. Определим самого младшего учащегося. В ячейку D22 записать формулу =МИН(D3:D21);

5. Определим самый поздний день рождения. В ячейку C23 записать формулу =МАКС(C3:C21);

6. Определим самого старшего учащегося. В ячейку D23 записать формулу =МАКС(D3:D21);

7. После выполнения задания переименуйте Лист 4 в Возраст моих одногрупников. Сохраните результаты своей работы.

После выполнения задания и самостоятельной работы необходимо отправить готовый документ (название: №группы, ФИО) с электронной почты в Вашем личном кабинете портала mfua.ru на электронную почту преподавателя: 29203617@s.mfua.ru с темой в письме: № группы, ФИО студента, дата.

Вопросы по выполнению лабораторной работы:

Назначение основных элементов интерфейса MS Excel.

Чем отличается лист от книги в MS Excel?

3. Основные понятия: ячейка, строка, столбец, адрес ячейки;

4. Ввод данных в ячейку;

5. Строка формул;

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

Сколько столбцов содержится в таблице? Укажите адрес последнего столбца.

Сколько строк содержится в таблице? Укажите адрес последней строки.

Как выйти из документа, не закрывая окна самого приложения?

Введите в ячейку N35 свое имя, выровняйте его в ячейке по центру и примените начертание полужирное. Опишите порядок ваших действий.

Введите в ячейку С5 текущий год. Опишите порядок ваших действий.

Формат данных в ячейке.

13. Использование в таблице простых формул;

Копирование формулы в другое место. Порядок действий.

Как определяется адрес ячейки?

Перечислите три способа выравнивания ширины столбца.

Перечислите три способа редактирования данных, введенных в ячейку.

Как выделить всю таблицу целиком?

Как выделить целиком всю строку?

Как выделить целиком весь столбец?

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

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

Как выглядит указатель мыши в процессе выделения блока (диапазона) ячеек?

Как выглядит указатель мыши в процессе автозаполнения ячеек?

25. Работа со ссылками на данные: абсолютной, относительной, смешанной и их использование в расчетах.

26. Особенность абсолютной ссылки на ячейку.

27. Смешанная ссылка на ячейку.

Использование в расчетах встроенных математических и статистические функций Excel. Какие из них Вы использовали в данной Лабораторной работе?

Типы данных.

Присвоение типа к используемым данным.

Тип данных дата.