Методические рекомендации по выполнению практических работ в Excel
методическая разработка по информатике и икт на тему

УПРАВЛЕНИЕ ОБРАЗОВАНИЯ

АДМИНИСТРАЦИИ МУНИЦИПАЛЬНОГО ОБРАЗОВАНИЯ

ГОРОДСКОГО ОКРУГА «ВОРКУТА»

МУНИЦИПАЛЬНОЕ ОБЩЕОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ «ЛИЦЕЙ №1»

ЛАБОРАТОРНЫЕ РАБОТЫ ПО ИНФОРМАТИКЕ

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

Составитель Яменко Ольга Петровна, учитель информатики

Воркута 2012

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

Методические указания предназначены как для обучающихся основного общего (полного) образования., так и для студентов очно-заочной и заочной форм обучения всех специальностей.

Содержание:

Лабораторная работа №1 «Excel. Основные понятия. Простые таблицы»        

Лабораторная работа №2 «Таблица значений функции двух переменных. Сумма ряда»        

Лабораторная работа №3 «Логические функции. Разветвляющие алгоритмы»        

Лабораторная работа №4. «Обработка массивов в Еxcel»        

Лабораторная работа №5 «Графики и диаграммы»        

Лабораторная работа №6. «Обработка списка. Расширенный фильтр. Сводная таблица»        

Лабораторная работа №1 «Excel. Основные понятия. Простые таблицы»

Задание1. Основные элементы окна

Запустите программу Excel. Ознакомьтесь с основными элементами окна согласно рисунку. Убедитесь, что стандартная панель и панель форматирования включены. (ВидПанель инструментов). 

Для того, чтобы перейти к ячейке, например АА50, достаточно в поле имени ввести этот адрес (адрес ячейки вводить на английском ). При работе с данными удобно использовать сочетание клавиш. Используя справку, ознакомьтесь   с «горячими клавишами» для перемещения по рабочему листу и вводу данных.

Задание 2. Вычисление списка значений. Оформление рабочего листа.

Необходимо рассчитать теплопроводность кремния в интервале от 200 до 700 К с шагом 50 градусов. Теплопроводность описывается следующей формулой: , где К0=350Вт/см, Т0=68К

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

Вставьте новый лист и назовите его Теплопроводность кремния

Введите  в ячейку А1 название таблицы  Теплопроводность кремния нажмите клавишу .

В ячейку А3 введите букву К.

Щелкните на меню Формат, выберите команду Ячейки….

 В открывшемся окне активизируйте функцию нижний индекс и нажмите кнопку ОК.  

Введите с клавиатуры 0

Выполните пункт 4

 В открывшемся окне отключите функцию нижний индекс и нажмите кнопку ОК.  

Введите с клавиатуры знак =

Выполняя аналогичные действия, в ячейку А4 введите Т0=

В ячейку А6 введите Т(К)

В ячейку B6 введите К(Вт/см*К)

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

В ячейку А7 введите число 200

В ячейку А8 введите число 250

Чтобы не вводить остальные значения , заполните нижележащие ячейки с помощью маркера заполнения, для этого:

Выделите ячейки А7:A8.

Установите указатель в правый нижний угол и когда он примет вид тонкого черного креста  удерживая нажатой левую клавишу мыши,  перетащите  его вниз до ячейки А17

В ячейку В7 введите формулу =$В$3/(A7-$B$4). Здесь адрес ячеек В3 и В4 объявлен абсолютным, т.к. величины, которые хранятся в этих ячейках постоянны для любой температуры.

Скопируйте формулу из ячейки В7 в ячейки В8:В17. (перетащите маркер заполнения вниз)

Соответствующие вычисления выполнены. Расчертите таблицу как на рисунке.

Задание 3. Использование имен.

Скопируйте ячейки А1:В17 на другой лист2 и назовите его Имена

Присвоим ячейке А3 имя К0:

установите курсор в ячейку А3

щелкните мышкой в поле Имя

удалите содержимое поля

введите с клавиатуры К0 (на английском)

нажмите

Выполняя аналогичные действия присвойте ячейка А4 имя Т0, а диапазону А7:A17 имя Т (сначала выделите диапазон, а потом вводите имя).

Введите в ячейку В7 следующую формулу =К0/(Т-Т0) и скопируйте её в ячейки В8:В17.

Результат вычислений тот же, но формула более читабельная.

Если теперь необходимо увеличить диапазон вычислений, то следует вставить строки перед строкой А17. Тогда вставленные ячейки в столбец А будут тоже иметь имя Т

Задание

Оформить таблицу значений функции. При наборе формул использовать адреса ячеек и их имена.

Лабораторная работа №2 «Таблица значений функции двух переменных. Сумма ряда»

Задание 1. Для того, чтобы построить таблицу значений функции f(x,y)=x2+y, выполните следующие действия:

В ячейку А1 введите х

Ячейки В1:D1 объедините и введите y

В ячейки A3:A5 введите значения переменной х

В ячейки B2:D2 введите значения переменной y

В ячейку B3 введите формулу =$A3^2+B$2 и скопируйте её сначала в ячейки C3:D3, а затем в ячейки B4:D5.

Оформите таблицу как на рис.3.

Постройте таблицу значений функции f(x,y), но в формуле используйте имена ячеек. Значения х и у задайте самостоятельно.

Задание2. Суммирование ряда.

Простейшим способом вычисления суммы ряда является расположение всех членов ряда в соседних ячейках и их суммирование. Например, необходимо вычислить  , где х=2. Другими словами необходимо найти сумму трех членов ряда, т.е.  . Для выполнения этой задачи вExcel выполните следующие действия:

В ячейку А1 введите х

В ячейку В1 введите 2

В ячейку А2 введите к

В ячейку В2 введите ак

В ячейки А3:А5 введите числа от 1 до 3

В ячейку В3 введите формулу =$B$1^A3/ФАКТР(А3) и скопируйте её в ячейки В4:В5.

В этой формуле использована функция ФАКТР(), вычисляющая факториал. Для того, чтобы её вставить в формулу нужно вызвать мастер функций. Сначала наберите  в строке формул =$B$1^A3/, затем щелкните на кнопке Вставка функций. Откроется диалоговое окно функции ФАКТР(). Установите курсор в поле Число и чтобы не вводить адрес с клавиатуры просто щелкните мышкой на ячейке А3 (если окно мешает, установите указатель мыши в любую область окна и нажав левую кнопку мыши, отодвиньте его в сторону)

В ячейку А6 введите Sum

В ячейку В6 введите =СУММ(В3:B5). Для того, чтобы вызвать функцию суммирования просто щелкните на кнопке Автосумма и подтвердите диапазон суммирования.

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

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

Лабораторная работа №3 «Логические функции. Разветвляющие алгоритмы»

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

В Excel существует логическая функция ЕСЛИ(), которая позволяет использовать разветвляющийся алгоритм в вычислениях: ЕСЛИ(условие; оператор1; оператор2). Оператор1 выполняется, если Условие принимает значение Истина, а  Опрератор2, если Условие принимает значение ЛОЖЬ. Рассмотрим несколько примеров:

Вычислить .  

Оформим таблицу вычислений как на рис.1.

В ячейки А2:A8 введите некоторые значения переменной х, а в ячейку В1 введите =ЕСЛИ(А2>0;1;-1), скопируйте содержимое ячейки В1 в ячейки В3:B8

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

Вычислить   

В этом случае формула в ячейке В1 будет иметь следующий вид: =ЕСЛИ(А2<0;-1;ЕСЛИ(А2>0;1;0))

В качестве аргументов функции ЕСЛИ() использоваться другие функции.

Вычислить  

Второе условие из этого примера в Excel записывается следующим образом:=И(х>-1;x<1).

Итак, чтобы вычислить значение такой функции у, в ячейку В1 введем следующую формулу =ЕСЛИ(A2>=1;LN(A2);ЕСЛИ(И(A2>-1;A2<1);1;EXP(A2))). Эту формулу можно упростить, если рассматривать второе условие последним. Тогда в ячейку В1 надо ввести формулу =ЕСЛИ(A2>=1;LN(A2);ЕСЛИ(A2<=-1;EXP(A2);1))

Задание1. Вычислите корни квадратного уравнения ах2+bх+c=0. Начертите блок-схему решения .

Задание 2. Вычислите значение функции.

Лабораторная работа №4. «Обработка массивов в Еxcel»

Рассмотрим работу с массивами на следующем примере. Вычислим Р=(А+В)-1А, где А и В – это матрицы [3;3]. Решение оформим как на рис.5.

Ввод исходных данных оформите самостоятельно как на рисунке

Сначала вычислим матрицу А+В. Понятно, что результатом сложения двух матриц [3;3] будет матрица такой же размерности, поэтому выделим ячейки А11:C13, затем в строку формул введите =А4:C6+E4:G6 и нажмите комбинацию клавиш  (при наборе формулы диапазоны лучше выделять, а не вводить с клавиатуры).

Для вычисления матрицы (А+В)-1 воспользуемся стандартной функцией МОБР(), которая вычисляет обратную матрицу. Выделите ячейки Е11:G13 , щелкаем на кнопке Вставка функций, выберите функцию МОБР(). В диалоговом окне функции в поле Массив введите А11:C13 и нажмите комбинацию клавиш

На последнем шаге осталось вычислить произведение матриц. Для этих целей в Excel существует функция МУМНОЖ(). Итак, выделите ячейки I11:K13, щелкните на кнопке Вставка функций, выберите функцию МУМНОЖ(). В диалоговом окне функции в поле Массив1 введите E11:G13, в поле Массив2 введите А4:C6  и нажмите комбинацию клавиш

В результате расчетов может случиться, что определитель матрицы (А+В) равен 0. Тогда делать вычисление обратной матрицы и матрицы Р не имеет смысла. В этом случае достаточно вывести на экран сообщение, что матрица Р не существует. Для этого необходимо использовать функцию ЕСЛИ().

Подумайте, как это оформить на рабочем листе.

Задание1. Вычислить матрицу Р. 

Задание 2:Решить систему уравнений матричным методом , методом Крамера, методом Гаусса и с помощью модуля поиск решения .

Лабораторная работа №5 «Графики и диаграммы»

Задание1. Табулирование функции. Построение графика функции.

Построим график функции где -1х3 и число точек разбиения n=10. Для построения графика функции обычно используют Точечную диаграмму. Но для построения любой диаграммы сначала нужно построить таблицу, в которой будут располагаться данные. Данные для точечной диаграммы обычно располагаются в смежных ячейках. Если данные организованы по столбцам, то в первом из них чаще всего содержаться координаты по оси х, а в остальных – по оси y для одного или нескольких графиков.

Выполните следующее:

В ячейку В3 введите значение начала интервала- –1

В ячейку В4 введите значение конца интервала –3

В ячейку В5 введите число точек графика –10

Ячейкам В3:В5 присвойте соответственно имена а,b,n

В ячейку В6 введите формулу, вычисляющую шаг =(b-a)/n

Ячейке В6 присвойте имя h

В ячейку А9 введите ссылку на а -  =а

В ячейку А10 введите формулу =A9+h и скопируйте её в ячейки А11:A19

В ячейку В9 введите формулу =1/(A9^2-A9+1) и скопируйте её в ячейки В10:B19

Выделите ячейки А8:B19 и щелкните на кнопке Мастер диаграмм. Откроется первое диалоговое окно Мастера диаграмм. Выберите точечный тип диаграммы и щелкните на кнопке Далее. 

Откроется второе диалоговое окно. Оно состоит из двух вкладок – Диапазон данных  и Ряд. Первая из них предназначена для определения группы ячеек, на основании которой будет построена диаграмма. На вкладке Ряд определяют, какие из выделенных наборов данных будут отражены на диаграмме.

Названия наборов данных содержаться в списке Ряд. Этот список можно сокращать и расширять, не изменяя данные на листе. Каждый набор данных характеризуется тремя параметрами: именем, диапазоном значений аргумента и диапазоном значений функции. По умолчанию считается, что значения аргумента одинаковы для всех функций и расположены в левом столбце или в верхней строке выделенного диапазона.

Т.к. мы предварительно выделили  данные для построения диаграммы , в полях уже  содержится необходимая информация. Щелкните на кнопке Далее

Третье диалоговое окно состоит из пяти вкладок.

Вкладка Заголовки предназначена для ввода названий осей координат и всей диаграммы. Введите в поле Название диаграммы  График функции. 

На вкладке Оси  можно определить, какие метки будут выводиться возле делений координатных осей. Если сбросить флаг ось Х (категорий) или ось Y (значений), то соответственно ось абсцисс или ординат останется без подписей.

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

категории. Использование стандартного формата оси х, даже если соответствующие данные имеют формат времени

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

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

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

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

Отредактируйте диаграмму в соответствии с рисунком.

Постройте на одной диаграмме графики следующих функций:

Лабораторная работа №6. «Обработка списка. Расширенный фильтр. Сводная таблица»

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

Задание 1. Расширенная фильтрация

Если необходимо создать копию данных не зависящую от смены критериев фильтрации, то обычно используют расширенный фильтр. Для выполнения такой фильтрации необходимо выполнить команду ДанныеФильтрРасширенный фильтр. Рассмотрим работу расширенного фильтра на следующем примере. Есть следующий список данных:

Рассмотрим следующие примеры выборок.

Пример №1. Выбрать  всех у кого сумма баллов от 12 до 15

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

После этого выполняем команду ДанныеФильтрРасширенный фильтр. Открывается диалоговое окно в котором устанавливаем переключатель скопировать результат в другое место в списке  

Обработка,  затем в поле Исходный диапазон указываем диапазон нашего списка $A$1:$G$8, в поле Диапазон условий указываем ячейки $A$11:$H$12,  в поле Поместить результат в диапазон - $A$14:$G$14. И щелкаем на кнопке ОК.

В результате получаем следующий список данных

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

Пример №2. Выбрать всех, кто родился после 1980 года.

В этом случае критерий выборки можно задать двумя способами. Например, в следующем виде:

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

После ввода формулы, условие примет значение либо ИСТИНА, либо ЛОЖЬ. Это зависит от содержимого ячейки, находящейся в столбце по которому идет выборка и первой строке списка, в нашем примере от ячейки С2.  

Результате выборки получиться следующий список

Пример №3. Выбрать всех, у кого средний балл выше 4

При создании критерия необходимо использовать формулу:

Задание 2. Использование стандартных функций баз данных.

Стандартные функции баз данных находятся в категории Работа с базой данных. Все эти функции используют три аргумента: база_данных, поле и критерий. Синтаксис БДФункция(база_данных;поле;критерий), где

база_данных  — это интервал ячеек, формирующих список или базу данных;

поле   определяет столбец, используемый функцией;

критерий  — это ссылка на интервал ячеек, задающих условия для функции.

Пример №1. Найти число успешно сдавших экзамены.

Критерием поиска будет следующее условие

Далее в нужную ячейку вводим следующую формулу =БСЧЁТА(А1:G8;A1;A51:C52)

Пример№2. Найти средний балл по математике на экономическом факультете

Оформим это в виде небольшой таблицы

Задание №3. Сводная таблица.

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

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

Пример. Создать сводную таблицу среднего балла по каждому предмету и по каждому факультету.

Устанавливаем курсор на базу данных. Выполняем команду ДанныеСводная таблица. После щелчка на кнопке Далее, уточняем выбранный диапазон и сново щелкаем на кнопке Далее. На третьем шаге строим макет таблицы.

Перетащим кнопку с названием поля Факультет в область Строка. Перетащим кнопку с названием поля Математика в Область Данные и дважды щелкнем на кнопке Сумма по полю Математика. В открывшемся окне выберем операцию Среднее и щелкнем на кнопке ОК. Аналогично перетащим в область Данные кнопки с названием полей Физика и Русский. После того, как макет создан нажмем на кнопке ОК. Установим переключатель новый лист и нажмем на кнопку Готово.

Строка заголовка

Строка меню

Стандартная панель

Панель форматирования

Линии прокрутки

Поле имени

Рабочая область

Ярлыки листов

Строка формул

Активная ячейка

Рис.1. Окно Ехсеl

Рис.2. Таблица в Excel

Рис.3. Таблица значений функции f(x,y)

Рис.4. Функция ЕСЛИ()

Рис.5. Обработка массивов в Excel

Рис.6. Построение графика функции

Рис.7. Окно расширенного фильтра