Лабораторные работы по Excel


Серия «Элективный курс * Профильное обучение»



            Л. А. Анеликова







Лабораторные работы по Excel



            с компакт-диском




Рекомендовано Академией повышения квалификации и профессиональной переподготовки работников образования министерства образования и науки Российской Федерации


Книга предназначена для проведения элективного курса и профильного обучения в школах и колледжах






Москва СОЛОН-ПРЕСС 2020

УДК 32.882
ББК 621.399
      А 64

      Л. А. Анеликова

        A64      Лабораторные работы по Excel. — М.: СОЛОН-ПРЕСС,
        2020. — 112 с.: ил. — (Элективный курс * Профильное обучение).
        ISBN 978-5-91359-257-6

         Сборник лабораторных работ по Excel предназначен для проведения практических занятий при прохождении темы «Обработка числовой информации» для элективных и профильных курсов. Данное пособие может быть использовано как учителями в качестве дидактического материала, так и учащимися 9—11 классов для подготовки к сдаче ЕГЭ и ГИА, а также средних специальных и высших образовательных учреждений. В помощь преподавателю в сборнике представлено почасовое планирование курса. Количество лабораторных работ и их объем (ряд лабораторных работ состоит из нескольких законченных частей) по предложенным темам могут быть адаптированы каждым преподавателем согласно количеству часов, выделенных на изучение данной темы. Исходные файлы для работ находятся на компакт-диске, а задания, результаты и требуемые оформления — в сборнике. Файлы доступны и могут открываться с пятой версии Excel и выше. Каждой теме предшествует небольшой информационный блок основных понятий, определений и расположение рассматриваемых операций в меню табличного процессора.
         Виртуальный компакт-диск на сайте solon-press.ru



КНИГА - ПОЧТОЙ
    Книги издательства «СОЛОН-ПРЕСС» можно заказать и оплатить в издательстве с пересылкой Почтой РФ. Заказ можно оформить одним из перечисленных способов:
    1. Оформить заказ на сайте www.solon-press.ru в разделе «Книга — почтой».
    2.  Заказать книгу по тел. (495) 617-39-64, (495) 617-39-65.
    3.  Отправив заявку на e-mail: kniga@solon-press.ru (указать наименование издания, обратный адрес и ФИО получателя).
    4.  Послать открытку или письмо по адресу: 123001, Москва, а/я 82.
    При оформлении заказа следует правильно и полностью указать адрес, по которому должны быть высланы книги, а также фамилию, имя и отчество получателя. Желательно указать дополнительно свой телефон и адрес электронной почты.
    Через Интернет вы можете в любое время получить свежий каталог издательства «СОЛОН-Пресс», считав его с адреса http://www.solon-press.ru/katalog.
    Интернет-магазин размещен на сайте www.solon-press.ru.


По вопросам приобретения обращаться: ООО «СОЛОН-Пресс» Тел: (495) 617-39-64, (495) 617-39-65, е-mail: kniga@solon-press.ru, www.solon-press.ru


ISBN 978-5-91359-257-6  © Анеликова Л. А., 2020
© Макет и обложка «СОЛОН-ПРЕСС», 2020

                Лабораторные работы «Электронные таблицы Excel»




   Цель: познакомить с основными возможностями современных электронных таблиц, их применением на практике.
Требования к знаниям учащихся
   Знать основные приемы работы с электронными таблицами, структуру электронной таблицы, типы данных, основные различия между базами данных и электронными таблицами.
   Уметь заполнять таблицы данными и формулами, указывать тип ячейки, оформлять шрифтами, обрамлять, выделять цветом, выравнивать данные в ячейках, связывать данные с помощью формул, пользоваться функциями, составлять простые и сложные условия, обмениваться данными между листами рабочей книги.

Примерное тематическое планирование (36—40 часов)
   Тема 1. Назначение, применение, основные возможности, приемы работы, форматы данных.
   Тема 2. Ввод данных, формул, оформление ячеек (обрамление, затенение), л/р № 1.
   Тема 3. Редактирование таблицы, л/р № 2.
   Тема 4. Автозаполнение, абсолютные и относительные ссылки, л/р № 3.
   Тема 5. Условия в электронных таблицах, л/р № 4, 5.
   Тема 6. Работа с датами, л/р № 6.
   Тема 7. Сортировка данных, фильтры, автофильтры, л/р № 7.
   Тема 8. Листы рабочей книги. Группировка листов, занесение информации на сгруппированные листы, л/р № 8, 9.
   Тема 9. Диаграммы, типы диаграмм, построение, оформление, редактирование, л/р № 10.
   Тема 10. Скрытие данных в строках и столбцах, л/р № 11.
   Тема 11. Встраивание объектов со связью, л/р № 12.
   Тема 12. Связывание данных между листами, л/р № 13, 14.
   Тема 13. Статистические и математические функции, л/р № 15.
   Тема 14. Обобщение полученных знаний, зачет, л/р № 16, 17.


3

Лабораторные работы «Электронные таблицы Excel»

   Ниже приведен список лабораторных работ, которые нашли отражение в данной книге.
Лабораторная работа № 1
   Основные приемы работы, ввод данных и формул, оформление таблиц.
Лабораторная работа № 2
   Часть 1. Создание электронной таблицы и заполнение формулами, форматирование ячеек.
   Часть 2. Редактирование таблиц.
Лабораторная работа № 3
   Работа с автозаполнением и составление формул, абсолютные и относительные ссылки.
Лабораторная работа № 4
   Составление условий в электронных таблицах.
Лабораторная работа № 5
   Абсолютные и относительные ссылки, составление сложных условий.
Лабораторная работа № 6
   Работа с датами, объединение текста с данными ячеек, создание собственных форматов.
Лабораторная работа № 7
   Сортировка данных, фильтры, работа с листами рабочей книги.
Лабораторная работа № 8
   Сортировка данных, фильтры, работа с листами рабочей книги.
Лабораторная работа № 9
   Сортировка данных, фильтры, работа с листами рабочей книги.
Лабораторная работа № 10
   Часть 1. Создание и редактирование диаграмм.
   Часть 2. Построение графиков функций и диаграмм по данным таблицы.
   Части 3, 4. Оформление диаграмм.
Лабораторная работа № 11
   Формулы, автозаполнение, скрытие данных, построение диаграмм.

4

Лабораторные работы «Электронные таблицы Excel»

Лабораторная работа № 12
   Формулы, диаграммы, автозапопнение, скрытие данных, встраивание объектов со связью, построение диаграмм.
Лабораторная работа № 13
   Связывание данных между ёистами.
Лабораторная работа № 14
   Связывание данных между ёистами.
Лабораторная работа № 15
   Статистические и математические фужции.
Лабораторная работа № 16
   Предварительный зачет.
Лабораторная работа № 17
   Зачет.

5

                Немного теории





   Электронные таблицы — это идеальный вычислительный инструмент. Используются для деловых и научных расчетов. С помощью электронных таблиц можно очень быстро:
   •  подвести итог успеваемости;
   •  подсчитать стоимость обоев для комнаты;
   •  вычислить процентные ставки;
   •  произвести валютные операции;
   •  пересчитать премию для работников целого предприятия за считанные секунды и т. п.
   Таблица представляет собой двумерную матрицу. Рабочая область — это размеченная таблица, состоящая из ячеек одинакового размера (рис. 1).

  А B C D E
1          
2          
3          
4          

Рис. 1. Вид электронной таблицы

   Передвигаться по таблице можно с помощью стрелок или мышью.
   Та ячейка, в которой находится курсор, называется активной, в нее можно вносить информацию или изменять уже имеющуюся. После ввода данных в ячейку их необходимо зафиксировать нажатием клавиши Enter. В поле имени, расположенном выше заголовка столбца А, высвечивается адрес выделенной ячейки и ее содержимое.
   Каждая ячейка — это независимый по типу элемент таблицы (в сравнении с базой данных — в одном поле нельзя помещать данные разных типов). Обозначается ячейка именем столбца и номером строки, на пересечении которых она находится. Например: А1, С24 и т. д.
   В ячейку можно помещать текст, числа, величины логического типа, дату, а также любые формулы и условия. Если в ячейке на

6

Немного теории

ходится формула или условие, то на экране мы видим результат выполнения этой формулы, а саму формулу можно увидеть в строке состояния.
   Написание любой формулы должно начинаться со знака равенства. В ней употребляются различные математические знаки (+ - * / Л), а также функции (тригонометрические, статистические и т. п.), которые имеются в наборе данной электронной таблицы. В процессе выполнения лабораторных работ мы познакомимся с некоторыми из них.
   К данным ячейки можно обращаться по адресу ячейки (т. е. ее обозначению). Обращение к данным ячейки называется ссылкой. Не забывайте, что имена столбцов обозначаются латинскими буквами и русские А, С, В не являются равноценной заменой.

   Ссылки могут быть
   —  относительные — А1;
   —  абсолютные — $А$1.

   Большинство ссылок в формулах записываются в относительной форме, например С3, А7, где буквой обозначен столбец, а цифрой строка.
   Относительными называются ссылки, которые при копировании в составе формулы в другую ячейку автоматически изменяются.
   При копировании формулы с относительной ссылкой (столбец) (строка) на n строк ниже и на m столбцов правее ссылка изменяется на (столбец+т)(строка+п).
   Абсолютными называются ссылки, которые при копировании в составе формулы в другую ячейку не изменяются.
   Для того чтобы превратить относительную ссылку в абсолютную, достаточно в режиме редактирования формулы установить курсор непосредственно за ссылкой и нажать клавишу F4.
   Можно вставить в ссылку знаки доллара и при помощи обычных приемов редактирования. Если знак доллара стоит и перед именем столбца, и перед номером строки ($А$1), то это значит, что будет обращение только к этой единственной ячейке. А вот, например,запись $B11 означает, что при копировании формул будет изменяться только адресация строки ячейки, а при обозначении B$11 — только столбца. Такая адресация называется смешанной. Другой способ абсолютной адресации заключается в назначении имен ячейкам и использовании их в формулах.


7

Немного теории

   Например, если ячейка Е6 содержит сумму итогов за квартал, можно в дальнейших формулах подставлять не Е6, а, например, имя «Итог квартала». Чтобы присвоить имя ячейке, необходимо:
   —  выделить ячейку;
   —  выбрать в меню Формулы в разделе Определенные имена — Присвоить имя (рис. 2);
   —  в диалоговом окне ввести имя, которое должно начинаться с буквы, не содержать пробелов, иметь длину не более 255 символов.


Рис. 2. Определение имени ячейки

   Для выделения целого столбца достаточно щелкнуть мышью по его заголовку; чтобы выделить строку целиком, нужно щелкнуть мышью по ее номеру.
   Для изменения ширины столбца подведите указатель мыши к правой границе заголовка столбца. Когда указатель мыши примет вид черной двойной стрелки, удерживая нажатой левую клавишу мыши, переместите границу столбца вправо или влево. Аналогично можно изменять высоту строки.
   Часто бывает нужно выделить блок ячеек (несколько ячеек, расположенных рядом).
   Для этого установите указатель мыши в крайней ячейке выделения и, при нажатой левой клавише, переместите мышь к противоположному краю выделения (весь выделенный блок «охвачен» рамкой, все ячейки, кроме той, с которой начали выделение, окрашены).
   Если необходимо в несколько последовательных ячеек ввести числа или надписи, отличающиеся друг от друга определенной закономерностью, то такой процесс можно автоматизировать. Для этого надо:
   —  воспроизвести минимум две первые записи;
   —  выделить эти ячейки;
   —  установить курсор в нижний угол последней выделенной ячейки;

8

Немного теории

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

   Автозаполнением вы за секунды можете заполнить ряды:
   —  чисел: 100, 200, 300, ...
   —  дат: 1-4-06, 2-4-06, 3-4-06 ...
   —  месяцев: Ноя, Дек, Янв, ...
строки типа:
   —  Цех 1, Цех 2, ...
   —  Квартал 1, Квартал 2, ...
   Например, можно добиться заполнения следующих прогрессий:

Начальные значения Расширенный ряд

Время, дата

понедельник, вторник среда, четверг, пятница...       
январь, февраль      март, апрель, май...             
январь 99, апрель 99 июль 92, октябрь 92, январь 93...
15 января, 15 апреля 15 июля, 15 октября...           

арифметическая прогрессия

1, 2    3, 4, 5, ...   
1, 3    5, 7, 9, ...   
100, 95 90, 85, 80, ...

геометрическая прогрессия

2 ( знаменатель 2) 4, 8, 16, ... 
2 (знаменатель 3)  6, 18, 54, ...

запись слов с цифрами

кв. 2, кв. 3           кв. 4, кв. 5, ...        
квартал 3, квартал 4   квартал 1, квартал 2, ...
1-й период, 2-й период 3-й период, ...          
товар 1, товар 2       товар 3, ...             

повторение слов

Задолжено: Задолжено:, Задолжено:, ...      
В акциях:  В акциях: В акциях: В акциях: ...

Объединение данных в ячейках
   Если ячейка Е1 содержит просто текст, например «Кварт. 4 2010 г.», а в ячейке А20 формула = «Итог за» &Е1, то в ячейке А20 мы увидим объединенный текст «Итог за Кварт. 4 2010 г.». Длина текста не должна превышать 1024 символа.

9

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





Тема: Основные приемы работы, ввод данных и формул, оформление таблиц.


   Создайте простейшую таблицу под названием «Анализ показателей производства».

   A                       B      C       D    E            
1                                                           
2  Показатели производства                                  
3  Пок/месяцам             январь февраль март              
4  План                    1000   2000    3000 =сумм(В4:Г34)
5  Фактически              900    2000    3200              
6  %                       =B5/B4                           
7                                                           
8                                                           
9                                                           
10                                                          
11                                                          
12                                                          

Рис. 3. Вид таблицы к лабораторной работе № 1

   Для этого:
   Введите в столбец А данные по образцу (рис. 3) (для автоперемещения вниз вместо ENTER нажимайте стрелку «вниз»).

   Теперь заполните шапку таблицы:
   —  поместите курсор в ячейку В3;
   — наберите слово ЯНВАРЬ.
   Ввод в клетки С3 и D3 выполняйте методом автозаполнения.

   Формулы
   Чем больше в таблице формул, тем эффективнее ее применение. А если формул вовсе нет, то пользуйтесь текстовым редактором.


10