Лабораторная работа 1. Начальное знакомство с Ms Excel

Поможем в ✍️ написании учебной работы

Имя

Поможем с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой

Нажимая кнопку "Продолжить", я принимаю политику конфиденциальности

Лабораторная работа 1. Начальное знакомство с Ms Excel

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

Запустите табличный процессор Ms Excel одним из следующих способов:

1. Пуск Þ Программы Þ Ms Office (может отсутствовать) Þ Ms Excel;

2. Если на Рабочем столе есть значок с надписью Ms Excel, щелкните по нему дважды левой кнопкой мыши;

3. Если на экране находится панель Ms Office, то нажмите кнопку, соответствующую Excel.

Если задание выполнено правильно, на экране появится окно, структура которого приведена на рис.1.1.

Рис. 1.1. Структура окна табличного процессора Ms Excel 2000

Документы, с которыми работает Excel, принято называть Рабочими книгами. Они хранятся в файлах с расширением xls. По умолчанию Рабочей книге присваивается имя «Книга» с соответствующим порядковым номером («Книга1», «Книга2» и т.д.).

Рабочая книга состоит из Рабочих листов. Каждый Рабочий лист имеет уникальное имя (по умолчанию «Лист1», «Лист2» и т.д.), указанное на ярлыке листа в нижней части окна Рабочей книги. Для выбора (активизации) Рабочего листа следует щелкнуть левой кнопкой мыши соответствующий ярлык.

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

Пересечения строк и столбцов формируют ячейки, являющиеся основной единицей хранения данных. Каждая ячейка имеет имя, образованное из буквы столбца и номера строки, на пересечении которых находится данная ячейка. Например: A1, B3, D45, W428. Имя ячейки всегда связано с данными, записанными в эту ячейку, и может использоваться вместо соответствующих данных.

На рис.1.1 на ячейке В3 стоит курсор листа. Курсор листа в Excel представляет собой прямоугольник, обрамляющий какую-то ячейку таблицы. Ячейку, на которой стоит курсор листа, принято называть текущей ячейкой.

Для перемещения по рабочему листу используют клавиатуру или мышь.

Использование автозаполнения

Лабораторная работа 2. Форматирование таблицы

Формат ячейки

Форматирование – оформление таблицы всевозможными элементами.

Для форматирования ячеек нужно:

1. Выделить ячейки, оформление которых надо изменить.

2. В меню Формат выбрать команду Ячейки. Появится диалоговое окно Формат ячеек с набором вкладок.

3. Установить необходимые параметры на соответствующих вкладках (их содержимое поясняется ниже).

4. Щелкнуть по кнопке Ok.

Формат числа

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

· Общий – величины отображаются такими, какими Вы их вводите.

· Числовой – представление числа в виде целого или с указанным числом десятичных знаков после запятой.

· Денежный – числа выводятся со знаком валюты.

· Финансовый – используется для выравнивания знаков валюты и десятичных знаков в столбцах.

· Дата и Время – варианты представления данных этих типов.

· Процентный – Excel умножает величину на 100% и отображает результат со знаком %.

· Дробный – числовые величины отображаются как рациональные дроби.

· Экспоненциальный - для отображения больших или очень маленьких чисел, например, число 0,0025 отобразится как 25Е-4.

· Текстовый - используется для отображения текста и чисел как текста.

· Дополнительный - почтовый индекс, номер телефона, табельный номер.

На листе «Смета» для данных столбца «Цена» установите денежный формат с указанием валюты и двумя десятичными знаками после запятой. Для этого:

1. Выделите диапазон ячеек С2:С7.

2. В меню Формат выберите команду Ячейки.

3. На вкладке Число в списке форматов выберите денежный, в поле «Число десятичных знаков» введите число 2, в списке «Обозначение» выберите «р.».

4. Щелкните по кнопке Ok.

Выравнивание

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

По горизонтали данные можно расположить:

· По значению – по умолчанию текст - слева, число, дата, время – справа.

· По левому или правому краю ячейки.

· По центру ячейки.

· По ширине ячейки.

· С заполнением всей ячейки путем повторения содержимого.

· По центру выделенной области.

Для горизонтального выравнивания можно также использовать на панели инструментов форматирования кнопки , , и .

По вертикали данные можно расположить:

· По верхнему или нижнему краю ячейки.

· По центру ячейки.

· По высоте ячейки.

Секция Ориентация позволяет ориентировать содержимое ячейки по вертикали и под углом.

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

На листе «Смета»отцентрируйте таблицу. Для этого:

1. Выделите диапазон ячеек А1:С7.

2. В меню Формат выберите команду Ячейки.

3. На вкладке Выравнивание в списках «По горизонтали» и «По вертикали» выберите по центру.

4. Щелкните по кнопке Ok.

На листе “Планеты” выделите ячейку “Спутники”, в меню Формат выберите команду Ячейки, на вкладке Выравнивание найдите секцию Ориентация, щелкните по маркеру, отражающему нужный угол наклона или введите нужный угол с клавиатуры, например 45 градусов.

Шрифт

Кроме названия шрифта можно указать начертание (стиль) шрифта и его размер. Размер шрифта измеряется в пунктах (1 пункт = 1/72 дюйма = 0,357 мм).

Можно также задать цвет текста, выбрать вид подчеркивания текста и назначить видоизменение (зачеркнутый, верхний индекс – степень, нижний индекс).

Для изменения шрифта, его размера и стиля, цвета текста можно также использовать кнопки на панели инструментов форматирования (рис. 2.2).

Рис. 2.2. Кнопки изменения шрифта

На листе «Смета» оформите заголовки колонок таблицы. Подберите разные шрифты для текстов и чисел.

Граница

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

Для этого в окне Формат ячеек на вкладке Граница следует выбрать тип линий и их цвет, а затем выбрать нужное расположение рамки. На образце будет показан результат преобразования сетки.

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

На листе «Смета» оформим границы таблицы.

Оформим «шапку» таблицы, для этого:

1. Выделите ячейки А1:С1.

2. В меню Формат выберите команду Ячейки.

3. На вкладке Граница в секции «Линия» выберите тип и цвет линий. Например, выберите двойную линию синего цвета.

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

5. В секции «Линия» выберите тип и цвет внутренних линий «шапки» таблицы. Например, выберите тонкую пунктирную линию того же цвета.

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

7. Щелкните по кнопке Ok.

Самостоятельно оформите остальные границы таблицы.

Вид

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

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

Объединение ячеек

Объединение ячеек в основном используется при создании заголовков и многоуровневых «шапок» таблиц, например:

Дополнительные сведения
Домашний адрес					Паспортные данные
Индекс	Город	Улица	Дом	Квартира	Серия	Номер	Дата выдачи

Чтобы создать заголовок с объединенными ячейками, нужно:

1. Набрать текст заголовка в верхней левой ячейке диапазона, который будет использоваться для заголовка. Если заголовок занимает несколько строк, для ввода каждой новой строки нажимать Alt + Enter.

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

3. В меню Формат выбрать команду Ячейки. Появится диалоговое окно Формат ячеек.

4. На вкладке Выравнивание установить флажок Объединение ячеек.

5. Щелкнуть по кнопке Ok.

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

На листе “Измерения” выделите ячейки с С2 по К2 и щелкните мышью по кнопке Объединить и поместить в центр.

Сохраните Вашу Рабочую книгу и завершите работу с Ms Excel.

Контрольная работа №2

1. В файле «Фамилия1» для листов “Планеты” и ”Измерения” подберите необходимые размеры столбцов и строк, оформите шрифтами заголовки таблиц.

2. Назначьте новому листу название “Бенилюкс”, на котором, используя опцию переносить по словам, оформите таблицу по образцу.

Сведения о странах, входящих в объединение Бенилюкс

Показатель

Страна

Всего

Бельгия

Нидерланды

Люксембург

Площадь территории, тыс. кв. км

30,5

Численность населения, тыс. чел.

9858

14560

24785

Плотность населения, чел/кв.км

141,15

533,58

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

Распределение суши и воды на Земном шаре
Поверхность земного шара	Северное полушарие		Южное полушарие		Земля в целом
Поверхность земного шара	в млн. кв. км	в %	в млн. кв. км	в %	в млн. кв. км	в %
Суша	100,41		48,43
Вода	1154,64		206,62
Всего

Далее для всех таблиц используйте создание границ и другое удобное оформление.

Очистка содержимого ячеек

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

1. Выделить ячейки, которые нужно очистить.

2. В меню Правка выбрать команду Очистить.

3. В появившемся подменю выбрать нужную команду:

· Все, чтобы очистить ячейку от содержимого, форматирования и примечаний;

· Форматы;

· Содержимое;

· Примечания.

Столицы

Страны

Форма правления

Площадь, тыс.км²

Берлин

ФРГ

Республика

357

Париж

Франция

Республика

552

Лондон

Великобритания

Конст. монархия

244

Дублин

Ирландия

Республика

Амстердам

Нидерланды

Конст. монархия

Брюссель

Бельгия

Конст. монархия

Берн

Швейцария

Республика

Вена

Австрия

Республика

Копенгаген

Дания

Конст. монархия

Осло

Норвегия

Конст. монархия

387

Стокгольм

Швеция

Конст. монархия

450

2. Таблицу скопируйте ниже, начиная со строки 20.

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

№ п/п	Страны	Площадь, тыс.км²	Население, млн.чел	Столицы	Форма правления
1	ФРГ	357	82	Берлин	Республика
2	Франция	552	59	Париж	Республика
3	Великобритания	244	58	Лондон	Конст. монархия
4	Ирландия	70	3,6	Дублин	Республика
5	Нидерланды	41	15,7	Амстердам	Конст. монархия
6	Бельгия	31	10,2	Брюссель	Конст. монархия
7	Швейцария	41	7,4	Берн	Республика
8	Австрия	84	8,2	Вена	Республика
9	Дания	43	5,4	Копенгаген	Конст. монархия
10	Норвегия	387	4,5	Осло	Конст. монархия
11	Швеция	450	8,9	Стокгольм	Конст. монархия
12	Финляндия	338	5,3	Хельсинки	Республика
13	Эстония	45	1,4	Таллин	Республика
14	Латвия	64	2,4	Рига	Республика
15	Литва	65	3,7	Вильнюс	Республика
16	Польша	313	38,7	Варшава	Республика
17	Чехия	79	10,2	Прага	Республика
18	Словакия	49	5,4	Братислава	Республика
19	Венгрия	93	9,9	Будапешт	Республика
20	Румыния	238	22,6	Бухарест	Республика
21	Болгария	111	8,4	София	Республика
22	Югославия	102	10,4	Белград	Республика
23	Хорватия	57	4,5	Загреб	Республика
24	Словения	20	1,9	Любляна	Республика
25	Босния и Герцеговина	51	3,5	Сараево	Республика
26	Македония	26	2,2	Скопье	Республика
27	Албания	29	3,4	Тирана	Республика
28	Греция	132	10,6	Афины	Республика
29	Италия	301	57,2	Рим	Республика
30	Испания	505	39,8	Мадрид	Конст. монархия
31	Португалия	92	9,8	Лиссабон	Республика

Лабораторная работа 4.
Использование формул и функций

Понятие формулы

Формула – это выражение, вычисляющее новое значение по уже существующим. В состав формул могут входить константы (числа), математические операторы, ссылки на ячейки и встроенные выражения (функции).

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

Правила построения формул

· Формула начинается со знака равенства (=).

· В формуле могут выполняться действия сложения (+), вычитания (-), умножения (*), деления (/), возведения в степень (^).

· В формуле пробелы не допускаются.

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

· Адреса ячеек (ссылки) можно набирать латинскими буквами любого регистра.

· Завершается формула нажатием клавиши Enter или щелчком мыши по кнопке с зеленой галочкой в строке формул (см. рис.1.2).

Примеры простейших формул: =5+10,5 =6^3 =(56+78)/(45-23)

Ввод простейших формул

Для ввода простой формулы нужно:

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

2. Набрать знак равенства (=).

3. Набрать формулу.

4. Нажать клавишу Enter.

Для редактирования формулы нужно:

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

2. Нажать клавишу F2 или щелкнуть в строке формул по тексту формулы.

3. Внести необходимые исправления.

4. Нажать клавишу Enter.

Другой способ исправить формулу: щелкнуть мышью кнопку Изменить формулу в строке формул. При этом строка формул увеличится, помогая исправить формулу. Внесите в формулу изменения и щелкните мышью по кнопке Ok.

Запустите табличный процессор Ms Excel. Откройте созданный ранее файл «Фамилия1» с Вашей Рабочей книгой.

На листе «Смета» в таблицу добавим колонку со стоимостью приобретенных товаров и заполним ее формулами. Для этого:

1. В ячейку Е2 введите текст «Стоимость».

2. В ячейку Е3 введите формулу =12*5,80 и нажмите Enter. В ячейке отобразится результат вычисления по формуле.

3. Аналогично введите формулы в ячейки с Е4 по Е7.

4. Для данных колонки «Стоимость» установите денежный формат. Оформите границы данной колонки и выровняйте ее содержимое по центру в горизонтальном и вертикальном направлении.

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

Коды ошибок

· ###### - колонка узка для введенного числа. Необходимо расширить столбец.

· #ДЕЛ/0! – произошло деление на ноль. Необходимо исправить делитель.

· #ЗНАЧ! – неверный тип аргумента. Введен текст вместо числа (например, в формуле =С5+3 в ячейке С5 содержится текст).

· #ЧИСЛО! – проблема с числом. Аргумент указан вне допустимой области значений, например, корень из отрицательного числа.

· #ИМЯ? – имя не распознано. Неверное название функции или адреса ячейки (возможно ссылка набрана русскими буквами).

· #ССЫЛ! – некорректная ссылка на ячейку. Возникает при копировании формулы в неправильном направлении.

· #Н/Д – значение не допустимо.

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

Копирование (перенос) формул выполняется аналогично копированию (переносу) обычных данных (см. лаб. работу №3, раздел 3.4).

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

В приведенном фрагменте рабочего листа (рис. 4.1) формула из ячейки В1 скопирована в ячейку В4.

Таким образом, копирование формулы выполняется с изменением ссылок, записанных в формуле. Ссылки перестраиваются в соответствии с выбранным направлением копирования. Значение, полученное первоначально, после копирования будет изменено.

Используем копирование формул для заполнения колонки «Стоимость». Для этого:

1. Очистите содержимое ячеек с Е4 по Е7.

2. Выделите ячейку Е3.

3. При нажатой левой кнопке мыши протащите маркер заполнения вниз до ячейки Е7 и отпустите кнопку мыши.

Проконтролируйте и прокомментируйте результаты вычислений в ячейках с Е4 по Е7.

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

1. На листе «Примеры Функций» наберите в ячейках А1, А2, В1, В2 любые числовые значения.

2. Введите в ячейку С1 формулу =А1+В1.

3. Скопируйте формулу из С1 с помощью маркера заполнения в ячейку С2.

4. Скопируйте формулу из С1 в ячейки D1, E1, D2, E2.

5. Перенесите значения из ячеек С1 и С2 в другое место.

Проконтролируйте и прокомментируйте результаты вычислений в ячейках D1, E1, D2, E2.

Использование функций

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

ИМЯ функции (список аргументов)

где имя функции – это фиксированный набор символов;

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

Например, функция СУММ(С7:С10;D7:D10) содержит список из двух аргументов, каждый из которых представляет собой диапазон ячеек.

Приведем наиболее часто употребляющиеся функции.

Статистические:

МИН – возвращает минимальное значение в заданном диапазоне ячеек;

МАКС - возвращает максимальное значение в заданном диапазоне ячеек;

СРЗНАЧ – вычисляет среднее арифметическое нескольких чисел;

СЧЕТ – подсчитывает количество ячеек с числами в указанном диапазоне;

СЧЕТЗ – подсчитывает все непустые ячейки в указанном диапазоне.

Математические:

СУММ – подсчитывает сумму всех чисел в диапазоне ячеек;

ПРОИЗВЕД - подсчитывает произведение всех чисел в диапазоне ячеек.

Даты и времени:

СЕГОДНЯ() – возвращает текущую дату (не имеет аргументов).

Для нахождения значения функции нужно:

1. Выделить ячейку для формулы.

2. Ввести знак равенства.

3. Набрать имя функции, а затем в круглых скобках ввести ее аргументы.

4. Нажать клавишу Enter.

1. Удалите все данные с листа «Примеры функций».

2. В диапазон ячеек А1:В3 введите любые числовые значения.

3. В диапазон ячеек С1:С3 введите любые текстовые значения.

4. В столбце D вычислите значения приведенных выше функций для диапазона ячеек А1:С3. Например, в ячейку D1 введите формулу =МИН(А1:С3) и т.д.

Напоминаем, что функция возврата текущей даты не имеет аргументов, т.е. формула будет выглядеть так: =СЕГОДНЯ().

Мастер функций

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

Для создания формулы с помощью мастера функций нужно:

1. Выделить ячейку для формулы.

2. Ввести знак равенства и набрать формулу до момента использования функции.

3. Чтобы вставить в формулу функцию, щелкнуть мышью кнопку Мастер функций на стандартной панели инструментов или в меню Вставка выбрать команду Функция. Появится окно Мастера функций.

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

5. Щелкнуть мышью по кнопке Ok. Появится следующее окно Мастера функций – панель формул.

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

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

Замечания. 1. При использовании Мастера функций для каждой выделяемой функции можно получить справку. Для этого в окне Мастера функций нужно щелкнуть мышью по кнопке Помощник . В появившемся окне щелкнуть мышью Справка по этой теме, а затем – Справка по выделенной функции.

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

	A	B	C
1

Математические функции

2 3

Исходные данные

Результат 4 4,287 -7,89 5 57,689 6,3 6 4,23 4756 7 8755 575

Рис. 4.2.

Удалите все данные с листа «Примеры функций».

Создайте таблицу, приведенную на рис. 4.2.

1. В ячейку С4 введите формулу расчета квадратного корня из произведения содержимого ячейки А4 на абсолютное значение из ячейки В4.

2. В ячейку С5 введите формулу для возведения содержимого ячейки А5 в степень числа, содержащегося в ячейке В5.

3. В ячейку С6 введите формулу расчета абсолютного значения целой части разности содержимого ячеек А6 и В6.

4. В ячейку С7 введите формулу расчета остатка от деления содержимого ячейки А7 на содержимое ячейки В7.

5. Сверьте свои результаты: С4=5,815877406; С5=1,24419Е+11; С6=4751; С7=130.

6. Измените значения в ячейках с исходными данными и проанализируйте получившиеся результаты вычислений.

Левее создайте таблицу, приведенную на рис. 4.3.

E	F	G
Статистические функции

Исходные данные		Результат
270	1215
160	508
195	819
Рис. 4.3.

1. В ячейку G4 введите формулу расчета суммы значений из диапазона ячеек Е4:Е6.

2. В ячейку G5 введите формулу расчета среднего арифметического диапазона ячеек Е4:F6.

3. В ячейку G6 введите формулу поиска максимального значения для данных диапазона ячеек F4:F6.

4. Сверьте свои результаты: G4=625; G5=527,8333; G6=1215.

5. Измените значения в ячейках с исходными данными и проанализируйте получившиеся результаты вычислений.

Сохраните Рабочую книгу.

Логические функции

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

Примером логической функции может служить функция ЕСЛИ, которую в общем виде можно записать так:

Логические функции

11 12

Исходные данные

Результат 13 2 3 14 5 1 15 слово 4 16 15 -3

Рис. 4.4.

1. В ячейку С13 введите большее из значений, содержащихся в соседних слева ячейках.

2. В ячейку С14 введите слово «да», если значение ячейки А14 больше значения ячейки В14. В противном случае введите слово «нет».

3. Создайте формулу, записывающую в ячейку С15 слово «да», если ячейка А15 пуста или содержит текст. В противном случае в ячейке С15 должно появиться слово «нет».

4. Сформируйте формулу, определяющую значение ячейки С16 как:

· -1, если содержимое ячейки В16 меньше нуля;

· 0, если содержимое ячейки В16 равно нулю;

· формулу А16/В16, если В16 больше нуля.

5. Сверьте получившиеся результаты: С13=3; С14=«да»; С15=«да»; С16=-1.

6. Измените значения в ячейках с исходными данными и проанализируйте получившиеся результаты вычислений.

Сохраните Вашу Рабочую книгу и завершите работу с Ms Excel.

Контрольная работа № 4

1. На листе “Плотность” подсчитать плотность каждого материала (в кг/куб. дм) с точностью 2 знака после запятой (используя форматирование ячеек).

Материал	масса, кг	Объем, куб. дм	Плотность
Сталь	973,4	124
Чугун	320,85	45
Алюминий	102,7	39,5
Цинк	813,6	113
Медь	284,8	32
Свинец	42,03	3,7
Дуб	8,5	10
Сосна	62,16	111
Бетон	159,12	66,3
Стекло	33,54	12,9

2. На листе “Сумма” найти сумму всех целых чисел от 10 до 20 (использовать автозаполнение ячеек). Решение оформить в следующем виде.

	10
	…
	20
Сумма=

3. На листе “Сумма” оформите таблицу. С клавиатуры вводится возраст двух людей. Определить старшего по возрасту человека. Указание: ответ не должен выводиться, если возраст еще не задан (используйте логическую функцию ЕПУСТО).

Введите возраст первого человека
Введите возраст второго человека
Старше		человек

4. На листе “Бенилюкс” заполните пустые клетки, выполнив необходимые вычисления.

5. Подготовьте на листе “Функция” таблицу для расчета:

а) значения функции у=7х²-3х+6 при данном значении х;

б) значения функции f=12а²+7а-16 при данном значении а.

Указание: ответ не должен выводиться, если аргумент функции еще не задан (используйте логическую функцию ЕПУСТО).

Введите значение х:
Введите значение а:
Значение функции y:
Значение функции f:

6. На листе “Вода” заполните пустые клетки, выполнив необходимые вычисления.

7. Решить на листе “Число”. Дано двузначное число (вводится с клавиатуры). Найти: а) число десятков в нем, б) число единиц в нем, в) сумму его цифр, г) произведение его цифр.

8. На листе “Строка” оформить таблицу для определения количества символов в строке текста.

Введите строку символов:
Число символов в строке:

9. Известны коэффициенты квадратного уравнения вида ax²+bx+c=0, (a≠0). Определить, имеет ли уравнение вещественные корни. Найти корни, если они есть. Решение оформить на листе “Уравнение” в следующем виде:

Решение квадратных уравнений
Введите значение коэффициента а
Введите значение коэффициента b
Введите значение коэффициента с
Есть ли корни?		значение первого корня
		значение второго корня

10. На листе “Функция” рассчитать таблицу значений f(x,y)=x²-y², где х меняется от -2 до 3 с шагом 0,25, а у – от 0 до 2 с шагом 0,1. Результаты отражать с тремя знаками после запятой.

11. На заводе “Прогресс” каждому сотруднику зарплату за месяц выдают дважды: сначала часть оклада в виде аванса, а по истечении месяца – остальную часть оклада. При этом при окончательном расчете удерживают также подоходный налог и профсоюзный взнос. По известному окладу сотрудника П.П.Копейкина произвести расчет выплат на листе ”Оклад” в виде:

Оклад	Аванс	Подоходный налог	Пенсионный налог	Профсоюзный взнос	Сумма к выдаче

Примечания:

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

Б) Аванс составляет 40% оклада.

В) Подоходный налог определяется по формуле 13%(Оклад-МРОТ-Пенсионный налог), где МРОТ- минимальный размер оплаты труда.

Г) Профсоюзный взнос и пенсионный налог составляют по 1% от оклада.

13. Решить на листе “Функция”. Вычислить значения функции у=sin(x/2)+2 , для х от 5 до 5,5 с шагом 0,05. С помощью стандартных функций определить, при каком х функция принимает наибольшее значение.

вариант

Ряд 1 Ряд 2 … Ряд N Категория 1 число число … число Категория 2 число число … число … … … … … Категория N число число … число

Й вариант		Категория 1	Категория 2		Категория N
	Ряд 1	число	число	…	число
	Ряд 2	число	число	…	число
	…	…	…	…	…
	Ряд N	число	число	…	число

Рис. 5.1. Варианты представления данных

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

Запустите табличный процессор Ms Excel. Откройте созданный ранее файл с Вашей Рабочей книгой.

Добавьте в Рабочую книгу новый лист и назначьте ему имя «Изменение успеваемости».

В первую строку листа поместите заголовок таблицы «Изменение успеваемости учебных групп по информатике».

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

В столбце А, начиная с третьей строки, содержатся учебные группы (131 и т.д. – 3 строки) (это названия рядов данных).

На пересечении строк и столбцов располагается процент успеваемости группы по дисциплине «Информатика» (это элементы рядов данных). Заполните таблицу любыми числовыми данными.

Построение диаграммы

Для построения диаграммы изменения успеваемости воспользуйтесь следующим алгоритмом:

1. Выделите данные, по которым будет строиться диаграмма, причем если нужно, чтобы диаграмма в качестве подписей на осях координат содержала названия категорий (в нашем случае месяцы) и рядов данных (номера групп), выделите их вместе с числовыми данными. Таким образом, на листе «Изменение успеваемости» выделите диапазон ячеек А2: F5.

2. В меню Вставка выберите команду Диаграмма или в стандартной панели инструментов щелкните кнопку Мастер диаграмм . Появится диалоговое окно Мастер диаграмм (шаг 1 из 4).

3. Выберите тип диаграммы (например, График) и ее вид (например, график с маркерами, помечающими точки данных). Укажите мышью на кнопку Просмотр результата и, удерживая нажатой левую кнопку мыши, посмотрите промежуточный результат построения диаграммы.

Щелкните мышью по кнопке Далее.

4. На вкладке Диапазон данных в поле «Диапазон» указан диапазон ячеек, выделенный нами на первом шаге. По умолчанию Excel считает, что ряды данных расположены по строкам. Измените расположение рядов, щелкнув мышью поле метки ряды в столбцах. Посмотрите на образце, как это отражается на диаграмме. Верните настройку «Ряды в строках».

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

Щелкните мышью по кнопке Далее.

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

На вкладке Заголовки введите в поле название диаграммы - «Изменение успеваемости», в поле Ось Х (категорий) - «Месяцы» и в поле Ось Y (значений) - «Процент успеваемости».

На вкладке Линии сетки включите для оси категорий – основные линии и для оси значений – основные линии.

На вкладке Легенда выберите размещение - справа.

Щелкните мышью по кнопке Далее.

6. В последнем окне Мастера диаграмм нужно указать место размещения диаграммы. Для внедрения диаграммы на текущий лист нужно щелкнуть поле «имеющемся» и выбрать из списка нужный лист книги. Для создания для диаграммы нового листа нужно щелкнуть мышью поле «отдельном» и ввести название для листа диаграммы.

Создайте диаграмму на текущем листе.

7. Щелкните мышью по кнопке Готово.

На листе «Изменение успеваемости» под таблицей появится диаграмма, соответствующая всем заданным параметрам.

Сохраните Вашу Рабочую книгу.

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

Основные элементы диаграммы

· Область диаграммы – вся диаграмма и ее элементы.

· Область построения – ограниченная координатными осями область, содержащая все ряды данных.

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

· Линии сетки – линии, отображающиеся параллельно осям от делений диаграммы и облегчающие просмотр и анализ данных.

· Легенда – подпись, определяющая закраску или цвета точек данных.

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

Рис. 5.2. Элементы диаграммы

При остановке указателя над каким-либо элементом диаграммы появляется подсказка с названием элемента. Имя элемента диаграммы будет выводиться в подсказке в случае, если установлен флажок Показывать имена (меню Сервис, команда Параметры, вкладка Диаграмма).

Редактирование диаграмм

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

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

Удаление рядов данных

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

Удалите из диаграммы один ряд данных по Вашему усмотрению.

Изменение типа диаграммы

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

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

2. В меню Диаграмма выберите команду Тип диаграммы.

3. Выберите нужный тип и вид диаграммы на вкладке Стандартные или Нестандартные, а затем щелкните мышью по кнопке Ok.

1. На диаграмме «Изменение успеваемости» выделите один ряд по Вашему усмотрению.

2. В меню Диаграмма выберите команду Тип диаграммы.

3. На вкладке Стандартные выберите тип Гистограмма.

4. Щелкните мышью по кнопке Ok.

Форматирование диаграммы

Форматировать можно любой элемент диаграммы, который предварительно нужно выделить.

Для заголовков диаграммы и осей и для легенды можно изменить шрифт, выравнивание и вид (закраска области, рамка и ее стиль, тень).

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

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

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

Для форматирования любого элемента диаграммы нужно:

1. Выделить изменяемый элемент диаграммы.

2. В меню Формат выбрать команду Выделенный элемент или щелкнуть правой кнопкой мыши по изменяемому элементу диаграммы и выбрать в контекстном меню команду Формат.

3. На соответствующих вкладках внести необходимые изменения.

4. Щелкнуть мышью по кнопке Ok.

На листе диаграммы «Изменение успеваемости»:

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

· оформите название диаграммы и оси Y;

· для осей и таблицы данных установите размер шрифта в 9 пунктов, выберите цвет текста, настройте вид линий;

· для каждого ряда данных настройте вид линии и маркера.

Изменение объемных диаграмм

1. Выделите изменяемую объемную диаграмму.

2. В меню Диаграмма выберите команду Объемный вид. Появится диалоговое окно Формат трехмерной проекции.

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

4. Щелкните мышью по кнопке Ok.

Для диаграммы «Изменение успеваемости» измените тип диаграммы на График, вид графика – объемный. Уберите таблицу данных и отобразите легенду.

В меню Диаграмма выберите команду Объемный вид и поэкспериментируйте с настройками окна диалога.

Сохраните Вашу Рабочую книгу и завершите работу с Ms Excel.

Контрольная работа №5

1. На листе ”Вода” постройте диаграмму, отражающую соотношение воды и суши по каждому полушарию и на Земном шаре в целом.

2. На листе “График” построить график функции у=0,5х+4 на отрезке [-4,4] с шагом 0,3.

3. На листе “Система” найти графически корни системы уравнений , с точностью 0,1.

4. Треугольник на плоскости задан координатами своих вершин. Изобразите его на листе “Треугольник”. Например:

Точка

-3

-5

5. Решить на листе “Ресурсы”. В таблице отображены Сдвиги в структуре мирового потребления первичных энергоресурсов на протяжении XX века.

	Годы
	1900	1913	1938	1950	1960	1970	1980	1990	2000
Уголь	76%	80%	65%	56%	47%	34%	30%	32%	32%
Нефть	4%	5%	16%	24%	31%	41%	41%	38%	37%
Природный газ	2%	1%	5%	7%	10%	15%	17%	20%	19%
Дрова и торф	15%	10%	8%	6%	5%	4%	3%	2%	2%
Гидроэнергия	3%	4%	6%	7%	7%	5%	6%	4%	5%
Атомная энергия	0%	0%		0%	0%	1%	3%	3%	4%
Новые (альтернативные) источники	0%	0%	0%	0%	0%	0%	0%	1%	2%

Получить следующую диаграмму.

6. На листе “Плотность” построить диаграмму, позволяющую наглядно определить самый плотный материал.

7. На листе ”Зерно” отразить данные таблицы в виде диаграммы.

Проиводство	Весь мир	В том числе
Проиводство	Весь мир	СНГ	Зарубежная Европа	Зарубежная Азия	Африка	Северная Америка	Латинская Америка	Австралия и Океания
Пшеница, млн. т	600	80	120	240	15	100	25	20
Рис, млн. т	570	3	10	510	15	10	20	2
Кукуруза, млн. т	580	20	70	150	25	245	70	незначит.

8. На листе “Поверхность” рассчитать таблицу значений функции F(х)= , где х меняется от -2 до 3 с шагом 0,5, а у – от 0 до 1 с шагом 0,2. Построить график поверхности полученных значений.

9. На листе “Поверхность 2” построить полусферу в изометрической проекции по формуле .

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

а) в ячейки В3:В19 введите числа от -4 до 4 включительно с шагом 0,5;

б) аналогичным образом заполните ячейки С2:S2 (в ячейку С2 вводится число -4);

в) в ячейку С3 введите формулу =КОРЕНЬ(16-B3^2-$C$2^2) и распространите ее с помощью маркера заполнения вниз до ячейки С19;

г) далее в ячейках С3:С19 в расположенных там формулах необходимо поменять относительные адреса ячеек, на которые ссылается формула, на абсолютные, а абсолютные – на относительные. Это нужно для того, чтобы при горизонтальном распространении формул ссылки в формулах на столбец В3:В19 не менялись.

Замечание. Поменять относительные адреса ячеек, на которые ссылается формула, на абсолютные и наоборот можно следующим образом:

– выделите ячейку, в которой необходимо поменять адреса;

– щелкните мышью в строке формул на конкретном адресе, подлежащем изменению;

– нажимая несколько раз клавишу F4, измените адрес на нужный;

– завершить изменение в формуле нажатием клавиши Enter.

д) распространяя формулы, получите матрицу размера 17×17;

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

	-4	-3,5	-3	-2,5	-2	-1,5	-1	-0,5	0	0,5	1	1,5	2	2,5	3	3,5	4
-4									0
-3,5						1,22	1,66	1,87	1,94	1,87	1,66	1,22
-3				0,87	1,73	2,18	2,45	2,6	2,65	2,6	2,45	2,18	1,73	0,87
-2,5			0,87	1,87	2,4	2,74	2,96	3,08	3,12	3,08	2,96	2,74	2,4	1,87	0,87
-2			1,73	2,4	2,83	3,12	3,32	3,43	3,46	3,43	3,32	3,12	2,83	2,4	1,73
-1,5		1,22	2,18	2,74	3,12	3,39	3,57	3,67	3,71	3,67	3,57	3,39	3,12	2,74	2,18	1,22
-1		1,66	2,45	2,96	3,32	3,57	3,74	3,84	3,87	3,84	3,74	3,57	3,32	2,96	2,45	1,66
-0,5		1,87	2,6	3,08	3,43	3,67	3,84	3,94	3,97	3,94	3,84	3,67	3,43	3,08	2,6	1,87
0	0	1,94	2,65	3,12	3,46	3,71	3,87	3,97	4	3,97	3,87	3,71	3,46	3,12	2,65	1,94	0
0,5		1,87	2,6	3,08	3,43	3,67	3,84	3,94	3,97	3,94	3,84	3,67	3,43	3,08	2,6	1,87
1		1,66	2,45	2,96	3,32	3,57	3,74	3,84	3,87	3,84	3,74	3,57	3,32	2,96	2,45	1,66
1,5		1,22	2,18	2,74	3,12	3,39	3,57	3,67	3,71	3,67	3,57	3,39	3,12	2,74	2,18	1,22
2			1,73	2,4	2,83	3,12	3,32	3,43	3,46	3,43	3,32	3,12	2,83	2,4	1,73
2,5			0,87	1,87	2,4	2,74	2,96	3,08	3,12	3,08	2,96	2,74	2,4	1,87	0,87
3				0,87	1,73	2,18	2,45	2,6	2,65	2,6	2,45	2,18	1,73	0,87
3,5						1,22	1,66	1,87	1,94	1,87	1,66	1,22
4									0

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

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

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

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

1. Список выполнен в виде колонок однотипных данных.

2. Список отделяется от других данных хотя бы одной строкой и одним столбцом.

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

4. Первая строка списка является заголовочной и содержит метки столбцов (имена полей).

5. Вместо ссылки на область списка можно использовать имя при выполнении каких-либо операций.

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

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

При сортировке по возрастанию сначала сортируются числа от наименьших до наибольших, затем текст по алфавиту от А до Я и от A до Z (латинские символы ранее русских), даты от ранних до поздних, пустые всегда становятся последними. При сортировке по убыванию порядок противоположный, за исключением пробелов, которые всегда на последнем месте. Можно сортировать символы с учетом регистра, тогда сначала располагаются прописные, а затем строчные буквы.

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

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

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

Выполняется сортировка одним из методов:

1. Быстрая сортировка по одному ключу – кнопки По возрастанию и По убыванию панели инструментов стандартная.

2. Команда Сортировка меню Данные позволяет в диалоговом окне установить уровни и методы сортировки для выбранных меток столбцов.

Запустите табличный процессор Ms Excel. Откройте созданный ранее файл с Вашей Рабочей книгой.

Вставьте в Рабочую книгу новый лист (назовите его «Компьютеры») и оформите следующую таблицу:

Фирма	Частота ЦП	ОЗУ	Кэш	Жесткий диск	Гибкий диск	Монитор	Гарантия	Цена $
MT- Computers	40	4	256	210	1	Sony	12	805
RAMEC	80	8	256	260	2	Sony	24	918
АнтЭн	150	16	128	530	2	Panasonic	12	848
Аскод / Эврика	133	4	256	420	1	Samsung	12	897
Аскод / Эврика	40	24	128	530	1	LG	12	602
АСКОН – ЭВМ	150	16	128	210	1	LG	24	403
Ингресс СП	133	16	128	530	2	Sony	12	844
Ингресс СП	80	8	256	420	2	Panasоnic	24	465
Кей	80	8	256	530	1	Viewsonic	18	463
Кей	150	4	128	420	1	LG	36	596
Кей	40	16	256	210	2	Sony	24	605
МАС Электроник	133	24	256	210	1	Samsung	18	756
Оникс	80	8	128	210	1	Samsung	36	456
Оникс	40	4	128	420	2	LG	24	652

Выполните быструю сортировку поля «ОЗУ» по возрастанию. Для этого установите курсор в любую ячейку этого столбца и щелкните на панели инструментов стандартная кнопку По возрастанию.

Выполните сортировку по ключам: 1 ключ - Цена по возрастанию; 2 ключ - Частота по убыванию; 3 ключ - ОЗУ по возрастанию. Для этого:

1. Установите курсор в любую ячейку списка.

2. В меню Данные выберите команду Сортировка. Появится окно диалога Сортировка диапазона.

3. В секции Сортировать по установите первый ключ сортировки, выбрав из списка имя поля «Цена». Далее в секциях Затем по и В последнюю очередь, по аналогичным образом установите второй и третий ключи сортировки.

4. Щелкните мышью по кнопке Ok.

Выполните сортировку по ключам: 1 ключ - Монитор по убыванию; 2 ключ - Гарантия по возрастанию; 3 ключ – Гибкий диск по убыванию.

Лабораторная работа 1. Начальное знакомство с Ms Excel

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

Запустите табличный процессор Ms Excel одним из следующих способов:

1. Пуск Þ Программы Þ Ms Office (может отсутствовать) Þ Ms Excel;

2. Если на Рабочем столе есть значок с надписью Ms Excel, щелкните по нему дважды левой кнопкой мыши;

3. Если на экране находится панель Ms Office, то нажмите кнопку, соответствующую Excel.

Если задание выполнено правильно, на экране появится окно, структура которого приведена на рис.1.1.

Рис. 1.1. Структура окна табличного процессора Ms Excel 2000

Для перемещения по рабочему листу используют клавиатуру или мышь.

Дата: 2018-11-18, просмотров: 528.

12 3 4 5 6 7 8 9 10 Следующая ⇒