Приобретение практических навыков работы с электронной таблицей Microsoft Excel для Windows

Методические указания к лабораторной работе № 3

по дисциплине "Информатика"

для студентов специальности 200700 "Радиотехника",

200800 "Проектирование и технология радиоэлектронных средств"

Нижний Новгород 2001

Составители Е.Н.Приблудова, С.Б.Сидоров

УДК 621.325.5-181.4

Приобретение практических навыков работы с электронной таблицей Microsoft Excel для Windows: Метод. указания к лаб. работе № 3 по дисциплине "Информатика" для студентов спец. 200700, 200800 / НГТУ; Сост.: Е.Н.Приблудова, С.Б.Сидоров. Н.Новгород, 2001, с.

Изложены краткие сведения о работе с электронной таблицей - Microsoft Exel для Windows. Сформулированы задания и порядок выполнения для лабораторной работы.

Редактор И.М.Морозова

Подп. к печ. . Формат Приобретение практических навыков работы с электронной таблицей Microsoft Excel для Windows - student2.ru . Бумага газетная. Печать

офсетная. Печ.л. . Уч.-изд.л. . Тираж 100 экз. Заказ .

Нижегородский государственный технический университет.

Типография НГТУ. 603600, Н.Новгород, ул.Минина, 24.

Приобретение практических навыков работы с электронной таблицей Microsoft Excel для Windows - student2.ru
Нижегородский государственный

технический университет, 2001

Цель работы

Практически научить студентов основным приемам работы с Microsoft Excel:

- создание собственных таблиц;

- создание и обработка диаграмм;

- печать таблиц и диаграмм;

- анализ и обработка данных;

- связывание рабочих книг.

Краткие сведения

Запуск Microsoft Excel для Windows

Рассмотрим один из способов запуска электронной таблицы Microsoft Excel.Нажав кнопку Пуск, получаем доступ к главному меню, которое тут же раскроется, а через это меню выполняем команду Программыи выбираем необходимую программу - Microsoft Excel.Программа будет загружена, на панели задач появится новая кнопка, на экране будет открыто окно Microsoft Excel, вид которого показан на рис. 1.

Рис. 1

Основные понятия Microsoft Excel

Электронная таблица Microsoft Excel состоит из множества ячеек. Ячейка служит для ввода и хранения данных, а также вычисляемых значений. Каждая ячейка образуется пересечением столбца и строки. Столбцы обозначаются буквами, а строки – цифрами, которые служат им заголовками. Таким образом, каждая ячейка имеет свое уникальное имя (адрес). Например, B11 – ячейка одиннадцатой строки столбца B, поскольку образована пересечением столбца B и одиннадцатой строки.

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

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

Совокупность рабочих листов называется рабочей книгой. Файл Microsoft Excel имеет расширение .xls и является рабочей книгой.

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

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

В нижней части экрана располагается строка состояния, в которой отображаются сведения о выбранной команде или выполняемой операции.

Создание собственных таблиц

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

После ввода исходных данных, можно приступать к введению расчетных формул.

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

Для умножения двух и более ячеек, следует использовать формулу. Например, для умножения ячеек E2 на F2 необходимо сделать следующее:

- выделить ячейку G2;

- активизировать строку формул, установив на нее курсор и щелкнув левой клавишей мыши;

- набрать формулу: =E2*F2 (без пробелов);

- нажать клавишу [Enter] и результат появится в ячейке G2.

Далее можно повторить эту операцию для ячеек G3 (=E3*F3) и G4 (=E4*F4).

Кроме того, можно произвести умножение для всех трех ячеек (G2, G3, G4) сразу. Для этого сделайте следующее:

- выделить ячейки G2, G3, G4;

- активизировать строку формул, установив на нее курсор и щелкнув левой клавишей мыши;

- набрать формулу: =E2:E4*F2:F4 (без пробелов);

- нажать комбинацию клавиш [Ctrl+Enter] и результат появится в ячейках G2, G3, G4.

Анализ и обработка данных

Microsoft Excel обладает удобными возможностями в области анализа и обработки данных. С их помощью можно решать задачи прогнозирования, гибкого подбора новых параметров при изменении ситуации.

Пример подбора параметра

A	B	C	D	E	F	G	H
		январь	февраль	март	Всего	Цена	Сумма	Прибыль
	изделие № 999/01
	изделие № 999/02							29810,2
	изделие № 999/03							10378,5
							Итого:	190884,7

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

2. Подсчет прибыли осуществляется следующим образом:

- в ячейку H2 поместите формулу=G2*0,25;

- в ячейку H3 поместите формулу=G3*0,2;

- в ячейку H4 поместите формулу=G4*0,17.

3. По учету реализации изделий получится итоговая сумма чистой прибыли за квартал $190884,7. Для эффективного роста компании-производителя необходимо получение квартальной прибыли в объеме не менее $200000. Таким образом, не хватает как минимум $9115,3. Необходимо подобрать варианты увеличения прибыли на эту сумму, например, для изделия 01 как наиболее ходового товара.

Последовательность действий:

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

2. Откройте меню Сервис и выполните команду Подбор параметра…, появится диалоговое окно Подбор параметра с тремя полями. Имя выделенной ячейки ($H$2) будет помещено в верхнем поле Установить в ячейке.

3. В поле Значение: введите с клавиатуры результат, который необходимо получить (в данном случае, 159811,3, т.е. 150696+9115,3).

4. Необходимо определить, путем изменения, какого значения это увеличение может быть достигнуто. В данном примере возможны как минимум два варианта: увеличение производства или повышение цены. Для увеличения прибыли, в поле Изменяя значение ячейки: укажите имя той ячейки, числовое значение которой будет подбираться в соответствии с заданными условиями (это ячейка $F$2). Знак $ в имени ячейки означает, что ссылка на данную ячейку абсолютная.

5. Нажмите кнопку OK.

Данные в таблице изменятся. Итоговая сумма увеличилась до 200000. При этом цена изделия 01 повысилась с 299 до 317,1.

Аналогичным образом можно подбирать и иные параметры.

Связывание рабочих книг

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

Представим, что к основному производству изделий добавилось дополнительное, например, открылся новый филиал. Для него также требуется отдельный учет. Затем данные об основном (таблица sale-1.xls) и дополнительном (таблица sale-2.xls) производствах, записанные в исходных рабочих книгах, должны быть объединены в единую итоговую таблицу (таблица sale-all.xls).

Копирование данных

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

Последовательность действий:

1. Откройте исходную книгу (sale-1.xls) .

2. Выделите диапазон ячеек A1:H6.

3. Выполните копирование в буфер.

4. Создайте новую рабочую книгу.

5. Выполните вставку содержимого буфера.

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

7. Создайте третью рабочую книгу (sale-all.xls), которая будет являться итоговой. Для ее создания повторите операции 1-6. Итоговую книгу называют зависящей, и в ней будут автоматически помещаться данные, как из первой, так и из второй рабочих книг, с помощью формул с внешними ссылками.

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

Следующий шаг – создание аналогичных формул для других ячеек таблицы. Чтобы избежать повторения описанных выше операций, необходимо скопировать формулу во все остальные такие ячейки. Это оставшиеся ячейки диапазона B2:D4 книги sale-all.xls.

Последовательность действий:

- скопируйте в буфер обмена ячейку B2, содержащую формулу;

- щелкните по ячейке, куда должна быть скопирована формула, например, B3;

- возьмите содержимое буфера, формула будет скопирована.

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

=[sale-1.xls]Лист1!B3+[sale-2.xls]Лист1!B3.

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

Сохранение рабочей области

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

Последовательность действий:

- откройте меню Файл и выполните команду Сохранить рабочую область…, появится диалоговое окно;

- укажите имя файла области (999-all) и папку, куда он должен быть помещен, нажмите кнопку OK;

- ответьте Да на вопросы о сохранении изменений в других рабочих книгах области (sale-1 и sale-2) и рабочая область будет сохранена.

Задания и порядок выполнения

Задание № 1. Создание таблицы и диаграммы в Microsoft Excel.

1. Введите данные в таблицу 1.

2. С помощью кнопки Автосумма на панели инструментов произведите расчет: всего.

3. Создайте диаграмму (рис. 2) по данным таблицы 1.

4. Результат покажите преподавателю.

Таблица 1.

Приобретение практических навыков работы с электронной таблицей Microsoft Excel для Windows - student2.ru
Рис. 2

Задание № 2. Осуществление подбора параметра

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

2. Результат заполнения покажите преподавателю.

3. Установите в ячейке $H$4 значение для получения квартальной прибыли в объеме не менее $200000, при этом необходимо изменить значение ячейки $C$4.

4. Результаты изменений в таблице покажите преподавателю.

Таблица 2

A	B	C	D	E	F	G	H
		январь	февраль	март	Всего	Цена	Сумма	Прибыль
	изделие № 999/01
	изделие № 999/02
	изделие № 999/03
							Итого:

Задание № 3. Связывание рабочих книг

1. Создайте еще две таблицы путем копирования в них содержимого таблицы 2. При этом получится три рабочие книги. В третьей книге (итоговой таблице) удалите всю информацию в ячейках B2:D4.

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

3. Сохраните рабочую область.

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

1. Опишите структуру типового интерфейса электронной таблицы.

2. Какие операции над ячейками возможны в Microsoft Excel?

3. Как использовать расчетные формулы в Microsoft Excel?

4. Как отформатировать тип диаграммы, область диаграммы, область построения диаграммы, элемента ряда данных, осей диаграммы, сетки, легенды?

5. Каким образом осуществляется Подбор параметра в MS Exsel?

6. Каким образом связывают между собой разные рабочие книги?

7. Как происходит работа с несколькими окнами рабочих книг?

8. Что понимаете под созданием формул с внешними ссылками?

9. Каким образом скопировать созданную формулу для других ячеек таблицы?