Электронные таблицы: назначение, структура, адресация. Применение формул.

Областное государственное бюджетное образовательное учреждение

среднего профессионального образования

Томский политехнический техникум

План открытого урока

Специальности:

Общетехнические специальности

Дисциплина: Информатика и ИКТ

Преподаватель: Самсонова О.В.

Тема урока: Электронные таблицы: назначение, структура, адресация. Применение формул.

Тип урока: комбинированный

Форма проведения урока: фронтальная и практическая индивидуальная работа.

Методы и приемы обучения: дидактических задач.

Межпредметные связи: обеспечивающие дисциплины – математика; обеспечиваемые дисциплины – информационные технологии в профессиональной деятельности.

Формируемые компетенции: общие – понимание сущности и значимости своей будущей профессии; организация собственной деятельности, выбор методов и способов выполнения профессиональных задач; владение информационной культурой, анализ и оценка информации с использованием информационно-коммуникационных технологий; работа в коллективе; профессиональные – обработка первичной информации, оформление документов.

Материально-техническое обеспечение: компьютеры, аудиторная доска, маркеры.

Программное обеспечение: MS Excel, «Экзаменатор».

Методическое обеспечение: медиаресурсы: «Электронный учебник по информатике», презентация «Электронные таблицы: назначение, структура, адресация. Применение формул», АПИМ - тесты, представленные в электронном виде - программы: «Экзаменатор», «Ассистент2», методические указания для выполнения практических работ, конспекты.

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

Задачи:

1. Образовательные:

1. формирование навыков создания и оформления таблиц в Excel;

2. формирование навыков работы с абсолютными, относительными и смешанными ссылками;

3. формирование навыков работы с формулами и стандартными функциями в Excel;

2. Воспитательные:

2.1. привитие навыков делового общения;

2.2. воспитание трудолюбия, аккуратности;

2.3. формирование эстетического наслаждения от выполненной работы.

3. Развивающие:

3.1. способствовать развитию познавательного интереса при изучении возможностей электронных таблиц;

3.2. развитие профессиональной самостоятельности, логического мышления, умения анализировать полученные результаты;

3.3. развитие творческих способностей студента.

Содержание урока

(общее время 90 мин)

1. Организационный момент (2 мин)

2. Сообщение темы занятия, цели, плана работы (3 мин)

3. Объяснение нового материала с использованием презентации (15 мин)

4. Практическая работа студентов в программе MS Excel (50 мин)

5. Проверка знаний (15 мин)

6. Подведение итогов (5 мин)

Ход урока

1. Организационный момент (2 мин)

Приветствие, отмечаются отсутствующие.

1. Сообщение темы занятия, цели, плана работы (3 мин)

2. Объяснение нового материала с использованием презентации (15 минут)

Преподаватель представляет презентацию, в которой объясняется новый материал и представляются задания, которые необходимо выполнить обучающимися.

Объяснение нового материала происходит в виде беседы учителя с учениками.

Преподаватель:

Электронные таблицы предназначены для представления и обработки числовой информации. Числовые данные, организованные в таблицы можно легко и удобно обрабатывать с помощью программы MS Excel. С ее помощью можно выполнять сложные расчеты – экономические, инженерные, научные и т.д., создавать математические модели, строить графики и диаграммы, создавать тестовые программы.

Среда табличного процессора MS Excel имеет традиционный вид окна MS Windows и включает в себя следующие элементы:

При запуске программы Excel открывается окно, содержащее новую рабочую книгу.

Рабочая книга – это многостраничный документ Excel, каждая страница которого называется рабочим листом. По умолчанию книга содержит три рабочих листа с именами Лист1, Лист2 и ЛистЗ. Пользователь может вставить в книгу дополнительные листы (максимально возможное их количество – 255).

Размер таблицы MS Excel составляет 65536 строк и 256 столбцов: заголовки столбцов задаются буквами латинского алфавита сначала от A до Z, затем от АА до AZ, от ВА до ВZ и т.д. (последний IV).

Адрес ячейки таблицы задается именем столбца и номером строки, на пересечении которых она находится: например А1, С24, ВD5.

Диапазон ячеек – это группа смежных ячеек, охватывающих прямоугольную область таблицы, задается указанием адресов первой и последней его ячеек, разделенных двоеточием. Например: адрес диапазона, образованного частью строки 3 –  Е3:G3; адрес диапазона, имеющего вид прямоугольника с начальной ячейкой F5 и конечной ячейкой G8 – F5:G8.

В ячейках таблицы могут храниться следующие данные: текст – любая последовательность символов, числа и формулы.

Табличные данные в MS Excel можно также представить наглядно в виде диаграммы, которая представляет собой вставной объект, внедренный на один из листов рабочей книги.

Файлы с электронными таблицами MS Excel в памяти компьютера сохраняются с расширением xls или xlsх.

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

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

Сумма продаж

	A	B	C	D	E
1	Секция	Январь	Февраль	Март	Итого
2	Ковры	10255	12145	13285	=СУММ(B2:D2)
3	Сувениры	23262	24142	25357	=СУММ(B3:D3)
4	Электрические изделия	15266	13577	14256	=СУММ(B4:D4)
5	Парфюмерия	85025	10265	12145	=СУММ(B5:D5)
6	Бытовая химия	89220	95423	10524	=СУММ(B6:D6)
7	Итого:	=СУММ(B2:B6)	=СУММ(C2:C6)	=СУММ(D2:D6)	=СУММ(B7:D7)
8	Максимальная сумма продаж	= МАКС(B2:B6)	= МАКС(C2:C6)	= МАКС(D2:D6)	=МАКС(B7:D7)
9	Минимальная сумма продаж	= МИН(B2:B6)	= МИН(C2:C6)	= МИН(D2:D6)	= МИН(B7:D7)
10	Средняя сумма продаж	= СРЗНАЧ(B2:B6)	= СРЗНАЧ(C2:C6)	= СРЗНАЧ(D2:D6)	= СРЗНАЧ(B7:D7)

1. Оформление таблицы

Для оформления заголовка необходимо объединить ячейки. Для этого необходимо выделить диапазон ячеек А1:Е1 и нажать кнопку - Объединить и поместить в центре на панели инструментов в группе Выравнивание на вкладке Главная. Текст будет выровнен по центру выделенного диапазона.

Для ячеек, содержащих текст в две и более строк необходимо на панели инструментов нажать кнопку Перенос текста в группе Выравнивание на вкладке Главная.

или в контекстном меню (правая кнопка мыши) Формат ячеек, вкладка Выравнивание поставить галочку Переносить по словам

 

  

 

2. Ввод формул

- Формулы можно вводить вручную: для этого нужно нажать знак = и указать курсором на ячейки, в которых хранятся нужные значения.

Например, выражение: 2х + 3,5у³ вычисляется     в    Excel   по формуле:    =2*А1 + 3,5*В2^3, где ^  - возведение в степень, *  - умножение, /  - деление.

- Формулы также можно вводить с помощью встроенных функций, например, нажимаем кнопку Автосумма , получаем формулу СУММ(А1:А3). Перед тем как нажать Enter необходимо проверить, правильно ли указан диапазон ячеек, например А1:А3, т. е. адреса первой и последней ячеек, разделенных двоеточием.

3. Копирование формул

Если в вычислениях используются одни и те же формулы (например, в столбце Итого), то формулы можно копировать: для этого в правом нижнем углу ячейки нужно найти маленький черный крестик, нажать левую кнопку мыши и потянуть вниз или в любую другую сторону, куда необходимо скопировать формулу.

При этом ссылки на ячейки в формуле автоматически меняются, например, при копировании формулы из строки 2 в строку 3, поменяются соответственно ссылки: =СУММ(B2:D2) на =СУММ(B3:D3).

Если необходимо, чтобы при копировании какая-либо ссылка не менялась, то ее необходимо зафиксировать с помощью знака доллар $, например, =В6*$C$3, т. е. ссылка $C$3 является абсолютной ссылкой. Знак доллар $ при фиксировании ссылки можно ставить вручную с клавиатуры или нажав на клавиатуре кнопку F4. Повторное нажатие кнопки F4 снимает фиксацию с ячейки.

Таким образом, ссылки на ячейки бывают трех видов:

- относительные, которые меняются при копировании - C3;

- абсолютные или фиксированные ячейки, которые не меняются при копировании - $C$3;

- смешанные, у которых зафиксированы и не меняются при копировании либо строка -С$3, либо столбец - $C3.

4. Практическая работа студентов в программе MS Excel (50 минут)

Обучающимся раздаются практические задания на компьютере.

При выполнении задания можно пользоваться презентацией преподавателя, в которой представлен материал по данной теме. Презентация выкладывается в сети или на каждом компьютере.

Методические указания для студентов представлены в Приложении 1.

5. Проверка знаний (15 мин)

Повторение учебной информации проводится в форме компьютерного тестирования по теме «Электронные таблицы Excel». На тест предлагается 10 вопросов, случайным образом сгенерированных из 35 вопросов по этой теме. Время прохождения теста 10 минут.

Шкала набранных баллов (процент правильных ответов): менее 50% – 2 балла, от 50% до 74 % – 3 балла; от 75% до 89% – 4 балла; от 90% до 100% – 5 баллов.

6. Подведение итогов (5 мин)

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

Преподаватель проверяет выполненные задания, сверяя самооценку обучающегося со своей оценкой и объявляет полученные результаты.

Приложение 1

Методические указания для выполнения практической работы

1. Задание №1

1.1. Оформить таблицу «Тестирование».

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

1.3. Вычислить Средний балл по каждому предмету.

1.4. Вычислить Максимальный балл по каждому предмету.

1.5. Вычислить Минимальный балл по каждому предмету.

1.6. Вычислить Разницу между лучшим результатом и средним баллом, т. е. разность между максимальным баллом и средним баллом.

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

2.1. Оформить таблицу «Тестирование» по образцу (см. табл. 1), для этого нужно:

2.1.1. Поставить курсор в ячейку A1 и набрать слово «Задача», сделать его жирным.

2.1.2. Сделать одну длинную ячейку, в которую нужно набрать текст задачи: поставить курсор в ячейку A2, выделить белым крестом во 2-ой строке 6 ячеек (по количеству столбцов в таблице) и объединить их, нажав кнопку - Объединить и поместить в центре на панели инструментов в группе Выравнивание на вкладке Главная. Текст будет выровнен по центру выделенного диапазона.

2.1.3. Для этой объединенной ячейки установить перенос по словам: нажать кнопку -Перенос текста в группе Выравнивание на вкладке Главная, или в контекстном меню (правая кнопка мыши) Формат ячеек, вкладка Выравнивание поставить галочку Переносить по словам.

2.1.4. Набрать текст задачи.

2.1.5. Выровнять текст задачи по ширине по горизонтали и по центру по вертикали: в контекстном меню (правая кнопка мыши) Формат ячеек, вкладка Выравнивание.

2.1.6. Аналогично создать объединенную ячейку для заголовка «Тестирование», установить размер заголовка 14 пт., сделать жирным.

2.1.7. Оформить остальную таблицу в соответствии с образцом.

2.1.8. Включить границы в таблице, нажав кнопку Границы на панели инструментов в группе Шрифт на вкладке Главная.

Таблица 1. «Тестирование»

Задача

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

Тестирование
№ п/п	ФИО	Математика	Русский язык	Иностранный язык	Всего баллов
1	Афанасьев И.А.	45	50	65
2	Бобров Е.А.	58	78	58
3	Воробьев И.С.	78	56	81
4	Есина А.А.	85	45	73
5	Елисеева О.Н.	63	55	70
6	Милютин А.Д.	70	68	62
7	Миронов А.С.	50	72	55
8	Никонов Е.Н.	65	81	71
9	Петров В.В.	53	65	58
10	Сидоров И.Л.	70	72	69
Средний балл
Максимальный балл
Минимальный балл
Разница между лучшим результатом и средним баллом

2.2. Ввести формулы в пустые ячейки таблицы в соответствии с заголовками столбца и строк.

Для ввода формул использовать математические и статистические функции СУММ, СРЗНАЧ, МАКС, МИН, применяя команду Автосумма или Мастер функций f_x.

Замечание. Перед тем как нажимать Enter при вводе формул необходимо проверять, правильно ли указан диапазон ячеек в данной формуле.

При выполнении задания можно пользоваться презентацией преподавателя, в которой представлен материал по данной теме.

3. Задание №2.

3.1. Оформить таблицу «Набор студента».

3.2. Рассчитать по формуле Стоимость.

3.3. Рассчитать по формуле Сумму скидки.

3.4. Рассчитать по формуле Стоимость со скидкой.

3.5. Рассчитать по формуле итоговые значения в строке Итого.

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

4.1. На Листе2 оформить таблицу «Набор студента» по образцу (см. табл. 2).

Таблица 2. «Набор студента»

Набор студента

(список учебных принадлежностей)

Наименование	Количество	Цена	Стоимость	Сумма скидки	Стоимость со скидкой
Рюкзак, сумка	1	1340 р.
Общие тетради в клетку 48 л. – 10 шт.	10	18 р.
Пенал школьный	1	200 р.
Ручки шариковые синие 2 шт.	2	10 р.
Карандаши простые ТМ (НВ) – 2 шт. или разной мягкости	2	15 р.
Линейка 25 см.	1	7 р.
Точилка с контейнером	1	15 р.
Ластик	2	8 р.
Бумага для черчения (формат А3)	1	50 р.
Циркуль	1	20 р.
Калькулятор	1	100 р.
Корректор	1	30 р.
Скидка:	5%
Итого:

4.2. Установить в столбце Цена формат ячеек Денежный, нажав кнопку Финансовый числовой формат на панели инструментов в группе Число на вкладке Главная.

4.3. Ввести значение скидки 5% и установить для этой ячейки формат Процентный.

4.4. Ввести формулы в столбец Стоимость, например, =В5*С5. Так как, в данном столбце формулы будут однотипные, то можно воспользоваться копированием формул.

Копирование формул

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

При этом ссылки на ячейки в формуле автоматически меняются, например, при копировании формулы из строки 2 в строку 3, поменяются соответственно ссылки: =СУММ(B2:D2) на =СУММ(B3:D3).

4.5. В колонку Сумма скидки ввести формулу =D5*$В$17, так как скидка составляет 5% от стоимости. Скопировать эту формулу вниз. Ячейка $В$17 является фиксированной и не будет меняться при копировании.

Чтобы зафиксировать ячейку, необходимо поставить знак доллар $ перед буквой, обозначающей столбец и номером строки, на пересечении которых находится данная ячейка. Знак $ можно поставить вручную с клавиатуры или нажав на клавиатуре кнопку F4. Повторное нажатие кнопки F4 снимает фиксацию с ячейки.

Таким образом, ссылки на ячейки бывают трех видов:

- относительные, которые меняются при копировании, например, C3;

- абсолютные или фиксированные ячейки, которые не меняются при копировании, например, $C$3;

- смешанные, у которых зафиксированы и не меняются при копировании либо строка, например, С$3, либо столбец, например, $C3.

4.6. В колонку Стоимость со скидкой ввести формулу =D5-E5. Скопировать эту формулу вниз.

4.7. В строку Итого ввести формулы суммирования.

4.8. Сохранить на диске в своей рабочей папке файл под тем же именем - Тестирование.

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

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

5.1. Какие существуют ссылки?

5.2. Чем отличаются абсолютные и относительные ссылки?

5.3. Каким образом можно зафиксировать ссылку?

5.5. Как работают статистические функции МАКС, МИН, СРЗНАЧ ?

5.6. Как работает математическая функция СУММ ?