Коми Республикаса йöзöс велöдан министерство
«Сыктывкарса сервис да связь колледж»
уджсикасö велöдан канму учреждение
Министерство образования Республики Коми
государственное профессиональное образовательное учреждение
«Сыктывкарский колледж сервиса и связи»
(ГПОУ «СКСиС»)
Практическое руководство
«Электронные
таблицы
Microsoft Excel»
Подготовила:
преподаватель Информатики и ИКТ
Белева Людмила Федоровна
Сыктывкар,2013 год
Учебно-методическое пособие предназначено для студентов I курса всех специальностей колледжа и может быть использовано в управлении образовательным процессом как одно из дидактических средств обучения.
Изучение содержания данного учебно-методического пособия «Электронные таблицы Microsoft Excel» обеспечит возможность студенту научиться создавать, редактировать, форматировать электронные таблицы, диаграммы, производить вычисления по формулам с использованием относительных, абсолютных и смешанных ссылок, выполнять сортировку и фильтрацию, условное форматирование данных, применять электронные таблицы для решения задач в различных предметных областях.
Содержание:
|
ЛАБОРАТОРНАЯ РАБОТА № 1. |
Электронные таблицы MS Excel. |
4 |
|
|
ЛАБОРАТОРНАЯ РАБОТА № 2. |
Пример проектирования расчетов на рабочем листе. |
7 |
|
|
ЛАБОРАТОРНАЯ РАБОТА № 3. |
Функции MS Excel. |
8 |
|
|
ЛАБОРАТОРНАЯ РАБОТА № 4. |
Создание диаграмм в MS Excel. |
11 |
|
|
ЛАБОРАТОРНАЯ РАБОТА № 5. |
Логические функции в MS Excel. |
18 |
|
|
ЛАБОРАТОРНАЯ РАБОТА № 6. |
Построение графиков функции в MS Excel. |
24 |
|
|
ЛАБОРАТОРНАЯ РАБОТА № 7. |
Графический способ решения систем уравнений в среде MS Excel. |
26 |
|
|
ЛАБОРАТОРНАЯ РАБОТА № 8. |
Абсолютная и относительная адресация в MS Excel. |
29 |
|
|
ЛАБОРАТОРНАЯ РАБОТА № 9. |
Смешанные ссылки в MS Excel. |
32 |
|
|
Контрольная работа№1. |
|
34 |
|
|
Источники и литература |
|
36 |
|
ЛАБОРАТОРНАЯ РАБОТА № 1
Электронные таблицы MS Excel.
Цель работы:
ü Изучение основных понятий и элементов окна электронной таблицы MS Excel.
ü Изучение порядка ввода, форматирования, редактирования данных и работы с формулами в электронной таблице MS Excel.
Основные понятия электронных таблиц.
Excel – это электронная таблица, предназначенная для создания, хранения и обработки баз данных.
Документ Excel называется рабочей книгой. Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены латинскими буквами и, далее, двухбуквенными комбинациями.
Строки последовательно нумеруются цифрами.
По умолчанию рабочий лист имеет 256 столбцов с именами от A до IV и 65536 строк с номерами от 1 до 65536 (этого достаточно в большинстве случаев). Если распечатать всю таблицу понадобится лист шириной 6,4 метра и длиной 104 метра. На экране монитора видно » 4% ширины и 0,1% длины таблицы.
На пересечении столбцов и строк образуются ячейки таблицы. Они являются минимальными элементами для хранения данных. Обозначение ячейки сочетает в себе номер столбца и строки и является адресом ячейки.
Например, ячейка, находящаяся в 4-й строке в столбце C, имеет адрес С4.
Типы данных в ячейках и их форматы
В ячейках могут содержаться: текст, число или формула. Программа автоматически пытается определить, какого именно вида информация содержится в ячейке уже в процессе ввода.
Числовой формат
Формат числа может быть:
целым или с дробной частью (например, 123, –456 или –123,45);
дробным (например, 1/4 или 23/45);
денежным (например, 123 р., –123,45р., 123,4512 р. или 123,4 р.);
датой (например, 15.11.99 или 21 марта 98);
временем (например, 14:30 или 2:30:53 PM).
Формат числа может быть задан: Формат/Ячейки/Вкладка «Число»/Выбрать формат.
Текст
В электронных таблицах текст – это совокупность символов, использующаяся для оформления таблицы (заголовки, пояснения и т.д.).
Формулы
Формулой в Excel называется последовательность символов, начинающихся со знака равенства ”=”. В эту последовательность символов могут входить постоянные значения (константы), ссылки на ячейки, имена, функции или операторы.
Задание №1.
Создайте таблицу с исходными данными и получите результат.
В таблице с исходными данными должны быть поля: «№», «Наименование», «Поставщик», «Количество, л», «Цена», «Стоимость».
Таблица 1.
|
|
A |
B |
C |
D |
E |
F |
|
1 |
№ |
Наименование |
Поставщик |
Количество, л |
Цена |
Стоимость |
|
2 |
1 |
Молоко |
Поставщик 1 |
56 |
34,00 р. |
|
|
3 |
2 |
Кефир |
Поставщик 2 |
54 |
44,00 р. |
|
|
4 |
3 |
Сливки |
Поставщик 3 |
33 |
54,00 р. |
|
|
5 |
4 |
Молоко |
Поставщик 4 |
22 |
37,00 р. |
|
|
6 |
5 |
Молоко |
Поставщик 5 |
78 |
39,00 р. |
|
Задание №2.
Создайте таблицу по образцу.
Отсортируйте данные таблицы по численности населения (по убыванию).
Добавьте к этой таблице новый столбец «Плотность населения, млн/тыс. км2»и вычислите плотность населения для каждой из стран по формуле: "Численность населения" / "Площадь".
Таблица 2.
|
|
A |
B |
C |
D |
|
1 |
Европейские Страны с населением свыше 1 млн. жителей
|
|||
|
2 |
Страна |
Площадь, тыс. км2
|
Население, млн. чел |
Столица |
|
3 |
Финляндия |
338 |
5 |
Хельсинки |
|
4 |
Эстония |
45 |
1,6 |
Талин |
|
5 |
Латвия |
65 |
2,7 |
Рига |
|
6 |
Литва |
65 |
3,7 |
Вильнюс |
|
7 |
Польша |
313 |
38 |
Варшава |
|
8 |
Чехия |
128 |
15,6 |
Прага |
|
9 |
Венгрия |
93 |
10,6 |
Будапешт |
|
10 |
Румыния |
238 |
23,2 |
Бухарест |
|
11 |
Болгария |
111 |
9 |
София |
|
12 |
Сербия |
102 |
10,5 |
Белград |
|
13 |
Хорватия |
57 |
4,7 |
Загреб |
Задание №3.
Данная электронная таблица предназначена для вычисления заработной платы с учетом подоходного налога, отчислений в пенсионный фонд.
Таблица 3.
|
|
A |
B |
C |
D |
E |
F |
|
1 |
№ |
Фамилия |
Зарплата |
Подоходный налог |
Пенсионный фонд |
Сумма к выдаче |
|
2 |
1 |
Иванов И.И. |
15610,50р. |
|
|
|
|
3 |
2 |
Петров П.П. |
13312,40р. |
|
|
|
|
4 |
3 |
Сидоров А.С. |
17744,00р. |
|
|
|
|
5 |
4 |
Иванов К.К. |
12318,00р. |
|
|
|
|
6 |
5 |
Ванин М.М. |
14512,50р. |
|
|
|
|
7 |
6 |
Петров А.М. |
16710,00р. |
|
|
|
|
8 |
7 |
Рожкин С.С. |
17710,50р. |
|
|
|
-
Подоходный
налог вычисляется как 13% начисленной зарплаты:
в столбце D: =C2*0, 13.
-
Отчисление в
пенсионный фонд составляет 1% от начисленной зарплаты:
в столбце E: =C2*0, 01.
Задание №4.
Создайте таблицу следующего вида, Отформатируйте и оформите таблицу цветом, шрифтом и цветными границам. Рассчитайте данные в последнем столбце и в столбце «итого» по формуле.
Таблица 4.
|
|
A |
B |
C |
D |
E |
F |
|
1 |
№ |
Наименование |
Стоимость одного часа |
Количество часов |
Стоимость |
Сумма |
|
2 |
1 |
Побелка |
10,50р. |
2 |
124р. |
|
|
3 |
2 |
Поклейка обоев |
12,40р. |
12 |
2 399р. |
|
|
4 |
3 |
Укладка паркета |
25,00р. |
5 |
4 500р. |
|
|
5 |
4 |
Полировка паркета |
18,00р. |
2 |
500р. |
|
|
6 |
5 |
Покраска окон |
12,50р. |
4 |
235р. |
|
|
7 |
6 |
Уборка мусора |
10,00р. |
1 |
0р. |
|
|
8 |
|
ИТОГО |
|
|
|
|
Задание №5.
Создайте таблицу следующего вида. Пересортируйте данные по дате поставки. Определите суммарный доход.
Таблица 5.
|
|
A |
B |
C |
D |
E |
F |
G |
|
1 |
Район |
Поставка |
Дата поставки |
Количество, кг. |
Опт.цена, руб. |
Розн. цена, руб. |
Доход
|
|
2 |
Западный |
Мясо |
21.01.2013 |
232 |
120 |
300 |
|
|
3 |
Западный |
Молоко |
13.01.2013 |
452 |
20 |
30 |
|
|
4 |
Южный |
Молоко |
23.01.2013 |
332 |
22 |
35 |
|
|
5 |
Восточный |
Мясо |
25.01.2013 |
342 |
200 |
250 |
|
|
6 |
Западный |
Картофель |
21.01.2013 |
1200 |
11 |
20 |
|
|
7 |
Западный |
Капуста |
23.01.2013 |
1111 |
11 |
25 |
|
ЛАБОРАТОРНАЯ РАБОТА №2
Пример проектирования расчетов на рабочем листе.
Цель работы:
ü Закрепить знания студентов по работе с электронными таблицами в программе MS Excel.
Задание №1.
Даны три стороны треугольника а,b,с.
Требуется вычислить его площадь по формуле Герона:
S=
, где
Pp — полупериметр, P p=
Решение.
Таблица 1.
|
|
A |
B |
C |
D |
E |
F |
G |
H |
I |
|
|
1 |
Задача 1.
|
|
|
|
|
|||||
|
2 |
Даны три стороны треугольника а,Ь,с. |
|
|
|
|
|||||
|
3 |
Требуется вычислить его площадь по формуле Герона. |
|
|
|
|
|||||
|
4 |
Дано: |
|
Решение: |
|
|
|
|
|
|
|
|
5 |
a= |
3 |
|
|
|
Чертеж: |
|
|
|
|
|
6 |
b= |
4 |
Р= |
=B5+B6+B7 |
|
|
|
|
|
|
|
7 |
c= |
5 |
Pp= |
= D6/2 |
|
|
|
|
|
|
|
8 |
|
|
S= |
=корень(D7*(D7-B5)*(D7-B6)*(D7-B7))
|
|
|
|
|||
|
9 |
|
|
|
|
|
|
|
|
|
|
|
10 |
Найти: |
|
|
|
|
|
|
|
|
|
|
11 |
P-? |
|
|
|
|
|
|
|
|
|
|
12 |
S-? |
|
|
|
|
|
|
|
|
|
Задание №2.
Найдите гипотенузу прямоугольного треугольника, если его катеты равны a и b см.
|
Таблица 2. |
|
||||||||||||
|
|
A |
B |
C |
D |
E |
F |
G |
H |
I |
|||||
|
1 |
Задача 2.
|
|
|
|
|
|||||||||
|
2 |
|
|
|
|
||||||||||
|
3 |
|
|
|
|
|
|
|
|
|
|||||
|
4 |
Дано |
|
Решение: |
|
|
|
|
|
|
|||||
|
5 |
a= |
|
c= |
|
|
Чертеж: |
|
|
|
|||||
|
6 |
b= |
|
S= |
|
|
|
|
|
|
|||||
|
7 |
Найти: |
|
|
|
|
|
|
|
|
|||||
|
8 |
c=? |
|
|
|
|
|
|
|
|
|||||
|
9 |
S=? |
|
|
|
|
|
|
|
|
|||||
ЛАБОРАТОРНАЯ РАБОТА №3
Функции MS Excel.
Цель работы:
ü Изучение основных функций в ЭТ.
ü Научиться использовать встроенные функции для решения конкретных задач.
Excel содержит более 400 встроенных функций, условно разделенных на несколько категорий: Математические, Статистические, Финансовые, Логические, Текстовые и др. В русифицированных версиях Excel имена многих функций записываются на русском языке. Имена функций лучше набирать строчными буквами.
Математические функции выполняют различные математические операции, например, суммирование чисел (СУММ), вычисление логарифмов (LOG, LN), тригонометрических функций (SIN, COS), преобразование радиан в градусы (ГРАДУСЫ) и т.п.
Статистические функции выполняют операции по вычислению параметров случайных величин или их распределений, представленных множеством чисел, например, среднего значения (СРЗНАЧ), стандартного отклонения (СТАНДОТКЛОН) и т.п.
Финансовые функции используются в сложных финансовых расчетах, например, определение амортизационных отчислений, определение размера ежемесячных выплат для погашения кредита и т.п.
Логические функции используются для построения логических выражений, результат которых зависит от истинности проверяемого условия.
Текстовые функции относятся к функциям, которые не возвращают значение, а выполняют операции над текстовыми строками или последовательностью символов, вычисляя длину строки (ДЛСТР), позволяют соединять (сцеплять) две строки (СЦЕПИТЬ) и т.п. Для облегчения работы с встроенными функциями используется Мастер функций.
Таблица 1.
|
Наименование функции |
Описание |
|
СУММ(B2:B5) |
Вычисление суммы числовых значений |
|
СРЗНАЧ(B2:B5) |
Находит среднее арифметическое аргументов |
|
СЧЁТ(B2:B5) |
Подсчитывает количество чисел в списке аргументов |
|
МАКС(B2:B5) |
Возвращает максимальный аргумент |
|
МИН(B2:B5) |
Возвращает минимальный аргумент |
|
|
Возвращает результат возведения первого аргумента ("число"), в степень, указанную во втором аргументе ("степень") |
|
|
Возвращает значение тригонометрической константы pi =3,1415... |
|
|
Вычисляет сумму чисел, удовлетворяющих определённому условию. |
|
|
Подсчитывает количество ячеек, удовлетворяющих определённому условию. |
Задание №1.
Заполните пустые клетки таблицы произвольными числами.
Используя Мастер функций:
- посчитайте суммарное количество медалей для каждой страны.
- посчитайте общее количество медалей, завоеванных на играх.
- найдите минимальное количество медалей.
- максимальное количество медалей.
- сколько стран заработали больше десяти медалей.
- посчитайте среднее количество медалей.
Таблица 2.
|
|
A |
B |
C |
D |
E |
|
1 |
Страна |
Золото |
Серебро |
Бронза |
Всего медалей |
|
2 |
Канада |
|
|
|
|
|
3 |
Корея |
|
|
|
|
|
4 |
Швейцария |
|
|
|
|
|
5 |
Китай |
|
|
|
|
|
6 |
Швеция |
|
|
|
|
|
7 |
Австрия |
|
|
|
|
|
8 |
Голландия |
|
|
|
|
|
9 |
Россия |
|
|
|
|
|
10 |
Германия |
|
|
|
|
|
11 |
Франция |
|
|
|
|
|
12 |
Австралия |
|
|
|
|
|
13 |
Польша |
|
|
|
|
|
14 |
Италия |
|
|
|
|
|
15 |
Словакия |
|
|
|
|
|
16 |
Беларусь |
|
|
|
|
|
17 |
Великобритания |
|
|
|
|
|
18 |
Япония |
|
|
|
|
|
19 |
Словения |
|
|
|
|
|
20 |
Хорватия |
|
|
|
|
|
21 |
Латвия |
|
|
|
|
|
22 |
Финляндия |
|
|
|
|
|
23 |
Казахстан |
|
|
|
|
|
24 |
Норвегия |
|
|
|
|
|
25 |
США |
|
|
|
|
|
26 |
Общее количество медалей |
|
|
|
|
|
27 |
Минимальное количество медалей |
|
|
|
|
|
28 |
Максимальное количество медалей |
|
|
|
|
|
29 |
Сколько стран заработали больше десяти медалей |
|
|
|
|
|
30 |
Среднее количество медалей |
|
|
|
|
|
31 |
|
|
|
|
|
Задание №2.
Дана последовательность чисел: 25; -61; 0; -82; 18;-11; 0; 30; 15; -31; 0; -58; 22.
В ячейку А1 введите текущую дату, используя мастер функций (категория функции Дата и время). Числа вводите в ячейки третьей строки. Заполните ячейки К5:К15 соответствующими формулами. Отформатируйте таблицу по образцу. Лист1 переименуйте в Числа, остальные листы удалите.
С помощью встроенных функций найдите:
общее количество чисел, количество положительных чисел, количество
отрицательных чисел, количество нулей, максимальное значение, минимальное
значение, среднее значение, сумму всех чисел, сумму положительных чисел, сумму
отрицательных чисел, сумму максимального и минимального значений.
Таблица 3.
|
|
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
L |
M |
N |
|
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
Дана последовательность: |
||||||||||||||
|
3 |
25 |
-61 |
0 |
-82 |
18 |
-11 |
0 |
30 |
15 |
-31 |
0 |
-58 |
22 |
|
|
|
4 |
Найти: |
|
|
|
|
|
|||||||||
|
5 |
Общее количество чисел |
|
|
|
|
|
|||||||||
|
6 |
Количество положительных чисел |
|
|
|
|
|
|||||||||
|
7 |
Количество отрицательных чисел |
|
|
|
|
|
|||||||||
|
8 |
Количество нулей |
|
|
|
|
|
|||||||||
|
9 |
Максимальное значение |
|
|
|
|
|
|||||||||
|
10 |
Минимальное значение |
|
|
|
|
|
|||||||||
|
11 |
Среднее значение |
|
|
|
|
|
|||||||||
|
12 |
Сумма всех чисел |
|
|
|
|
|
|||||||||
|
13 |
Сумма положительных чисел |
|
|
|
|
|
|||||||||
|
14 |
Сумма отрицательных чисел |
|
|
|
|
|
|||||||||
|
15 |
Сумма максимального и минимального значений |
|
|
|
|
|
|||||||||
Задание №3.
Используя Мастер функций:
- Вычислить возраст и стаж работника
- Для вычисления используется формула:=(ГОД (СЕГОДНЯ()-D2)-1900)
|
|
A |
B |
C |
D |
E |
F |
G |
|
1 |
|
ФИО |
Должность |
Дата рождения |
Возраст
|
Дата поступления на работу |
Стаж |
|
2 |
1 |
Иванов И.И. |
программист |
01.02.1973 |
|
01.02.1993 |
|
|
3 |
2 |
Петров П.П. |
техник |
02 .02.1971 |
|
02 .02.1991 |
|
|
4 |
3 |
Сидоров С.С. |
техник |
21.01.1953 |
|
21.01.1973 |
|
|
5 |
4 |
Иванов К.К. |
программист |
13.01.1964 |
|
13.01.1981 |
|
|
6 |
5 |
Пушкин М.М. |
водитель |
23.01.1977 |
|
23.01.1997 |
|
|
7 |
6 |
Петров М.М. |
директор |
25.01.1953 |
|
25.01.1971 |
|
ЛАБОРАТОРНАЯ РАБОТА №4
Создание диаграмм в MS Excel.
Цель работы:
ü Научиться создавать диаграммы на основе табличных данных в сиcтеме ЭТ Excel.
Данные, содержащиеся в клетках электронной таблицы, можно представить в графической форме – в виде диаграмм. Такой способ представления информации обеспечивает наглядность и позволяет удобнее и быстрее анализировать результаты обработки данных.
Диаграмма – это средство наглядного графического изображения информации, предназначенное для сравнения нескольких величин или нескольких значений одной величины, слежения за изменением их значений.
Типы диаграмм:
- Гистограмма – служит для сравнения нескольких величин в нескольких точках (столбики располагаются вертикально);
- Линейчатая – служит для сравнения нескольких величин (столбики располагаются горизонтально);
- График – служит для того, чтобы проследить за изменением нескольких величин;
- Круговая – служит для сравнения нескольких величин в одной точке;
- Кольцевая – аналогична круговой;
- С областями – служит для того, чтобы проследить изменение нескольких величин и изменение их суммы
- И другие.
- Выделить объект, содержащий данные для построения.
- Вызвать мастера диаграмм.
- Выбрать тип диаграммы.
- Задать параметры диаграмм (ответить на вопросы мастера диаграмм).
Задание №1.
Построить диаграмму по данным таблицы.
Таблица 1.
|
|
A |
B |
C |
D |
E |
F |
G |
H |
|
1 |
Виды сырья |
|
|
|
|
|
|
|
|
2 |
|
1980г. |
1992г. |
1995г. |
|
|
|
|
|
3 |
Выработка электроэнергии, млрд.кВт/ч |
167 |
173 |
177 |
|
|
|
|
|
4 |
Нефть, млн.т |
516 |
399 |
307 |
|
|
|
|
|
5 |
Природный газ, млрд.м3 |
641 |
640 |
595 |
|
|
|
|
|
6 |
Уголь, млн.т |
395 |
337 |
262 |
|
|
|
|
|
7 |
Железная руда, млн.т |
107 |
82 |
78 |
|
|
|
|
|
8 |
Деловая древесина, млн. м3 |
242 |
183 |
93 |
|
|
|
|
|
9 |
Рыба и морепродукты, млн.т |
8 |
6 |
4 |
|
|
|
|
Рисунок 1.
 |
Задание №2.
Создайте таблицу по образцу. Построить диаграмму, отображающую численность населения в странах Европы.
Таблица 2.
|
|
A |
B |
C |
D |
|
1 |
|
|
|
|
|
2 |
Страна |
Площадь, тыс. км2
|
Население, млн. чел |
Столица |
|
3 |
Финляндия |
338 |
5 |
Хельсинки |
|
4 |
Эстония |
45 |
1,6 |
Талин |
|
5 |
Латвия |
65 |
2,7 |
Рига |
|
6 |
Литва |
65 |
3,7 |
Вильнюс |
|
7 |
Польша |
313 |
38 |
Варшава |
|
8 |
Чехия |
128 |
15,6 |
Прага |
|
9 |
Венгрия |
93 |
10,6 |
Будапешт |
|
10 |
Румыния |
238 |
23,2 |
Бухарест |
|
11 |
Болгария |
111 |
9 |
София |
|
12 |
Сербия |
102 |
10,5 |
Белград |
|
13 |
Хорватия |
57 |
4,7 |
Загреб |
Рисунок 2.
Задание №3.
Постройте круговую диаграмму, по данным о процентном распределении суши Земли по материкам.
Таблица 3.
|
|
A |
B |
C |
D |
E |
F |
||
|
1 |
Постройте круговую диаграмму, по данным о процентном распределении суши Земли по материкам. |
|||||||
|
2 |
Евразия |
Африка |
Северная Америка |
Южная Америка |
Антарктида |
Австралия и Океания |
||
|
3 |
37% |
20% |
16% |
12% |
9% |
6% |
||
|
4 |
|
|
|
|
|
|
||
|
5 |
|
|
|
|
|
|
||
|
6 |
|
|
|
|
|
|
||
|
7 |
|
|
|
|
|
|
||
|
8 |
|
|
|
|
|
|
||
Рисунок 3.
Задание № 4.
Постройте информационную модель в виде круговой диаграммы для решения следующей задачи: Ученики XI класса при сдаче выпускных экзаменов по алгебре, информатике и физике получили следующие оценки:
Таблица 4.
|
|
A |
B |
C |
D |
E |
F |
||
|
1 |
|
Фамилия, имя ученика |
Предметы |
|||||
|
2 |
|
|
Алгебра |
Информатика |
Физика |
Балл для поступления |
||
|
3 |
1 |
Иванов И. |
3 |
4 |
3 |
|
||
|
4 |
2 |
Петрова И. |
4 |
4 |
3 |
|
||
|
5 |
3 |
Смирнова Г. |
4 |
4 |
4 |
|
||
|
6 |
4 |
Прохорова Е. |
5 |
5 |
4 |
|
||
|
7 |
5 |
Кожевникова В. |
5 |
5 |
5 |
|
||
|
8 |
6 |
Вяткин Д. |
3 |
3 |
3 |
|
||
Какой ученик может с данными результатами поступить в некоторый вуз, если проходной балл равен 110?
Примечание. Чтобы определить балл для поступления, необходимо суммировать полученные оценки и увеличить результат в 10 раз.
Рисунок 4.
Задание №5.
Дана таблица. Примените функции для определения минимальных и максимальных значений по каждому показателю. Постройте диаграмму по данному образцу.
Таблица 5.
|
|
A |
B |
C |
D |
E |
|
1 |
|
|
Площадь (тыс.кв.м) |
Глубина (м) |
Высота над уровнем моря |
|
2 |
1 |
Байкал |
31,5 |
1520 |
456 |
|
3 |
2 |
Таньганьика |
24 |
1470 |
773 |
|
4 |
3 |
Виктория |
68 |
80 |
1134 |
|
5 |
4 |
Гурон |
59,6 |
288 |
177 |
|
6 |
5 |
Аральское море |
51,1 |
61 |
53 |
|
7 |
6 |
Мичиган |
58 |
281 |
177 |
|
8 |
|
Минимальная глубина |
|
|
|
|
9 |
|
Максимальная глубина |
|
|
|
|
10 |
|
Средняя высота |
|
|
|
Рисунок 5.
Задание №6.
Используя таблицу, создайте следующие диаграммы.
Таблица 6.
|
|
A |
B |
|
1 |
Планета |
Среднее расстояние от Солнца,а.е |
|
2 |
Земля |
1,000 |
|
3 |
Меркурий |
0,387 |
|
4 |
Марс |
1,524 |
|
5 |
Венера |
0,723 |
|
6 |
Сатурн |
9,539 |
|
7 |
Плутон |
39,440 |
|
8 |
Юпитер |
5,203 |
Рисунок 6.
Рисунок 7.
ЛАБОРАТОРНАЯ РАБОТА № 5
Логические функции в MS Excel.
Цель работы:
-
ü Изучение принципов использования логических функций при выполнении расчётов.
ЕСЛИ
Синтаксис:
ЕСЛИ (<условие>; <выражение 1>; <выражение 2>).
Условие – это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ; <выражение1> и <выражение2> могут быть числами, формулами или текстами.
Результат:
Условная функция, записанная в ячейку таблицы,
выполняется так: если условие истинно, то значение данной ячейки будет равно
значению выражения <выражение 1>, в противном случае - <выражение
2>.
Логические выражения строятся с помощью операций отношения (<, >, <= (меньше или равно), >= (больше или равно), <> (не равно)) и логических операций (логическое И, логическое ИЛИ, логическое отрицание НЕ).
Сначала записывается имя логической операции (И, ИЛИ, НЕ), а затем в круглых скобках перечисляются логические операнды.
И
Синтаксис:
И (логическое_значение1, логическое_значение2, …)
Результат:
Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
ИЛИ
Синтаксис:
ИЛИ (логическое_значение1, логическое значение2, …)
Результат:
Возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Задание №1.
Создать таблицу, выполнив словесную оценку результатов конкурсного отбора по результатам экзаменов «Принят» - «Не принят» («Принят» - если набрал не менее 12 баллов).
Таблица 1.
|
|
A |
B |
C |
D |
E |
F |
G |
|
1 |
|
|
|
|
|
Проходной балл |
12 |
|
2 |
|
Ф.И.О |
Математика |
Физика |
Русский язык |
общая сумма баллов |
результат решения |
|
3 |
1 |
Исаченко |
4 |
5 |
4 |
|
|
|
4 |
2 |
Сумина |
4 |
4 |
4 |
|
|
|
5 |
3 |
Иванова |
3 |
4 |
3 |
|
|
|
6 |
4 |
Кузнецова |
5 |
4 |
5 |
|
|
|
7 |
5 |
Насонов |
4 |
4 |
3 |
|
|
|
8 |
6 |
Петров |
5 |
5 |
5 |
|
|
|
9 |
7 |
Сидоров |
4 |
2 |
3 |
|
|
- Ячейка G3 содержит выражение: =ЕСЛИ(F3>=12;"принят"; «не принят")
Задание №2.
Студентам, успешно сдавшим экзамен, выдается стипендия в размере 200 $ в месяц. Начислить стипендию хорошим студентам (кто имеет средний балл>=4)
|
|
A |
B |
C |
D |
E |
F |
G |
|
1 |
|
Фамилия |
Математика |
Физика |
Русский язык |
Ср.балл |
Результат |
|
2 |
1 |
Яковлев |
5 |
4 |
5 |
|
|
|
3 |
2 |
Смирнов |
4 |
5 |
5 |
|
|
|
4 |
3 |
Павлов |
4 |
4 |
5 |
|
|
|
5 |
4 |
Иванов |
5 |
4 |
5 |
|
|
|
6 |
5 |
Сидоров |
4 |
3 |
3 |
|
|
|
7 |
6 |
Борисов |
5 |
5 |
5 |
|
|
|
8 |
7 |
Петров |
4 |
4 |
5 |
|
|
|
9 |
8 |
Макаров |
4 |
4 |
4 |
|
|
|
10 |
9 |
Соколов |
4 |
5 |
4 |
|
|
|
11 |
10 |
Воронин |
4 |
5 |
5 |
|
|
Таблица 2.
Задание №3.
Написать функцию «ЕСЛИ» для расчета оплаты электроэнергии. За первые 100 кВт клиент платит 2 руб. за 1 кВт, за превышение расхода свыше 100 кВт в месяц - по 2,5 руб. за каждый кВт.
Таблица 3.
|
|
A |
B |
C |
D |
E |
|
1 |
|
Клиент |
Кол-во электроэнергии |
Оплата |
|
|
2 |
1 |
Коржова |
140 |
|
|
|
3 |
2 |
Давыдова |
100 |
|
|
|
4 |
3 |
Козлов |
80 |
|
|
|
5 |
4 |
Литвинов |
50 |
|
|
|
6 |
5 |
Новиков |
120
|
|
|
|
7 |
6 |
Иванов |
124 |
|
|
|
8 |
7 |
Петров |
342 |
|
|
В ячейке D2 нужно записать следующую функцию: =ЕСЛИ(C2<=100; C 2*2; C 2*2,5)
Задание №4.
Таблица содержит следующие данные об учениках школы: фамилия, возраст и рост ученика. Сколько учеников могут заниматься в баскетбольной секции, если туда принимают детей с ростом не менее 160 см? Возраст не должен превышать 13 лет.
В ячейке F3 нужно записать следующую функцию:
=ЕСЛИ (И(C4<=13; D4>160);"принят"; «не принят")
Таблица 4.
|
|
A |
B |
C |
D |
E |
|
1 |
|
|
|
Максимальный возраст |
13 |
|
2 |
|
|
|
Минимальный рост |
160 |
|
3 |
|
Фамилия |
Возраст |
Рост |
Результат |
|
4 |
1 |
Иванов |
14 |
170 |
Не принят |
|
5 |
2 |
Петров |
11 |
150 |
Не принят |
|
6 |
3 |
Сидоров |
12 |
155 |
Не принят |
|
7 |
4 |
Смирнов |
13 |
165 |
Принят |
|
8 |
5 |
Воронин |
12 |
165 |
Принят |
|
9 |
6 |
Воробьёв |
12 |
162 |
Принят |
|
10 |
7 |
Снегирёв |
11 |
152 |
Не принят |
|
11 |
8 |
Соколов |
13 |
158 |
Не принят |
|
12 |
9 |
Макаров |
13 |
161 |
Принят |
|
13 |
10 |
Павлов |
14 |
167 |
Не принят |
|
14 |
|
Итого |
4 |
||
Задание №5.
10 спортсменов-многоборцев принимают участие в соревнованиях по 5 видам спорта. По каждому виду спорта спортсмен набирает определенное количество очков. Спортсмену присваивается звание мастера, если он набрал в сумме не менее 90 очков. Сколько спортсменов получило звание мастера?
Таблица 5.
|
|
A |
B |
C |
D |
E |
F |
G |
H |
|
1 |
|
Виды спорта |
Звание мастера Мастер? |
|||||
|
2 |
Номер спортсмена |
1 |
2 |
3 |
4 |
5 |
Всего очков |
|
|
3 |
1 |
15 |
10 |
25 |
15 |
30 |
|
|
|
4 |
2 |
20 |
9 |
21 |
14 |
29 |
|
|
|
5 |
3 |
15 |
5 |
19 |
10 |
25 |
|
|
|
6 |
4 |
18 |
6 |
22 |
13 |
30 |
|
|
|
7 |
5 |
11 |
7 |
17 |
11 |
26 |
|
|
|
8 |
6 |
8 |
10 |
24 |
15 |
25 |
|
|
|
9 |
7 |
21 |
7 |
15 |
9 |
27 |
|
|
|
10 |
8 |
19 |
8 |
25 |
15 |
20 |
|
|
|
11 |
9 |
20 |
10 |
24 |
13 |
24 |
|
|
|
12 |
10 |
16 |
4 |
20 |
10 |
28 |
|
|
|
13 |
|
Звание получили |
|
|||||
Задание №6.
Составить и заполнить таблицу по образцу. Заполнить произвольными данными в столбцах “Количество”, “Цена”. Выполнить необходимые расчеты. При покупке на сумму более 500 р. предоставляется скидка 5%.
Таблица 6.
|
|
A |
B |
C |
D |
E |
F |
|
|
1 |
|
|
|
Скидка 5% |
|
||
|
2 |
1 |
Наименование |
Кол-во |
Цена |
Стоимость |
|
|
|
3 |
2 |
Тетрадь |
|
|
|
|
|
|
4 |
3 |
Ручка |
|
|
|
|
|
|
5 |
4 |
Ластик |
|
|
|
|
|
|
6 |
5 |
Бумага офисная |
|
|
|
|
|
|
7 |
6 |
Клей
|
|
|
|
|
|
|
8 |
7 |
Папка |
|
|
|
|
|
|
9 |
8 |
Линейка |
|
|
|
|
|
|
10 |
9 |
Ручка-роллер |
|
|
|
|
|
|
11 |
10 |
Органайзер |
|
|
|
|
|
Задание №7.
10 учеников проходили тестирование по 5 темам какого-либо предмета. Вычислить суммарный (по всем темам) средний балл, полученный учениками. Сколько учеников имеют суммарный балл ниже среднего?
Таблица 7.
|
|
A |
B |
C |
D |
E |
F |
G |
H |
I |
|
1 |
|
Фамилия |
Тема 1 |
Тема 2 |
Тема 3 |
Тема 4 |
Тема 5 |
Ср.балл |
Результат |
|
2 |
1 |
Яковлев |
5 |
4 |
5 |
5 |
5 |
|
|
|
3 |
2 |
Смирнов |
4 |
5 |
4 |
4 |
5 |
|
|
|
4 |
3 |
Павлов |
4 |
4 |
5 |
4 |
4 |
|
|
|
5 |
4 |
Иванов |
5 |
4 |
5 |
3 |
4 |
|
|
|
6 |
5 |
Сидоров |
4 |
4 |
3 |
3 |
3 |
|
|
|
7 |
6 |
Борисов |
3 |
4 |
4 |
3 |
4 |
|
|
|
8 |
7 |
Петров |
4 |
4 |
5 |
4 |
5 |
|
|
|
9 |
8 |
Макаров |
5 |
4 |
4 |
5 |
5 |
|
|
|
10 |
9 |
Соколов |
4 |
5 |
4 |
4 |
4 |
|
|
|
11 |
10 |
Воронин |
4 |
3 |
3 |
3 |
4 |
|
|
|
12 |
|
|
|
|
|
Суммарный ср. балл |
|
|
|
Задание №8.
В сельскохозяйственном кооперативе работают 10 сезонных рабочих. Собирают помидоры. Оплата труда производится по количеству собранных овощей. Дневная норма сбора составляет K килограммов. Сбор 1 кг помидоров стоит Х рублей. Сбор каждого килограмма сверх нормы оплачивается в 2 раза дороже. Сколько денег в день получит каждый рабочий за собранный урожай?
Таблица 8.
|
|
A |
B |
C |
D |
E |
F |
G |
|
1 |
|
|
|
|
Дневная норма |
100 |
|
|
2 |
|
Рабочий |
Собрал |
Деньги за день в(руб.)
|
1 кг. помидоров (руб.)
|
5 |
|
|
3 |
1 |
Иванов А.Е. |
8 |
240 |
|
|
|
|
4 |
2 |
Петров П.П. |
9 |
270 |
|
|
|
|
5 |
3 |
Сидоров А.К. |
7 |
210 |
|
|
|
|
6 |
4 |
Гараев И.И. |
8 |
240 |
|
|
|
|
7 |
5 |
Аверин Р.А. |
13 |
480 |
|
|
|
|
8 |
6 |
Воронин Р.А |
5 |
150 |
|
|
|
|
9 |
7 |
Иванов В.Е. |
4 |
270 |
|
|
|
|
10 |
8 |
Петров Т.П. |
6 |
270 |
|
|
|
Задание №9.
Покупатели магазина пользуются 10% скидками, если покупка состоит более чем из пяти наименований товаров или стоимость покупки превышает K рублей. Составить ведомость, учитывающую скидки: покупатель, количество наименований купленных; товаров, стоимости покупки, стоимость покупки с учетом скидки. Выяснить сколько покупателей сделали покупки, стоимость которых превышает K рублей.
Таблица 9.
|
|
A |
B |
C |
D |
E |
F |
|
1 |
|
|
|
Кол-во тов. необх. для скидки |
5 |
|
|
2 |
|
|
|
Стоимость для скидки |
50 |
|
|
3 |
|
Покупатель |
Кол-во купл. товаров |
Стоимость |
Стоимость со скидкой |
|
|
4 |
1 |
Яковлев |
3 |
60 |
|
|
|
5 |
2 |
Смирнов |
1 |
12 |
|
|
|
6 |
3 |
Павлов |
6 |
65 |
|
|
|
7 |
4 |
Иванов |
5 |
40 |
|
|
|
8 |
5 |
Сидоров |
2 |
100 |
|
|
|
9 |
6 |
Борисов |
4 |
35 |
|
|
|
10 |
7 |
Петров |
3 |
26 |
|
|
|
11 |
8 |
Макаров |
1 |
22 |
|
|
|
12 |
9 |
Соколов |
4 |
55 |
|
|
|
13 |
10 |
Воронин |
8 |
120 |
|
|
|
14 |
|
|
Покупателей потративших больше 50 руб. |
|
||
Задание №10.
Обсуждается вопрос о возобновлении налога на холостяков, под действие налога попадают юноши старше 25 не вступившие в брак, посчитать сумму налога на каждого неженатого мужчину, если налог составляет 15 % от зарплаты (от всех начислений). Посчитать общую сумму к выдаче.
Задание №11.
Коллектив 10 человек, сотрудники имеют фиксированные оклады, если сотрудник отработал больше 5 лет, то получает премию в размере 2000 $, если меньше 5 лет, то получает премию в размере 1000 $ ежемесячно. Посчитать начисления.
Задание №12.
В бутике были организованы скидки на текстильные товары, размер которых больше или равен 40-му, в размере 20 % от цены товара, определить сумму скидки, цену товара после скидки, сумму товара в бутике, убытки владельца из-за несвоевременной реализации товара.
ЛАБОРАТОРНАЯ РАБОТА № 6
Построение графиков функции в MS Excel.
Цели:
ü Познакомить учащихся с графическими возможностями табличного процессора MS Excel .
ü Научить строить графики в MS Excel.
График функции – это кривая линия на координатной плоскости, показывающая зависимость координаты Y от координаты Х.
На практике, как правило, график функции строят
по точкам. Для этого сначала строят таблицу значений х и у. Значения х
выбирают на некотором промежутке ( хнач, хконеч.) и
изменяют их не произвольно, а увеличивая на одно и го же значение,
называемое шагом. Значение у вычисляется в зависимости от вида функции.
Чем больше взято для расчета промежуточных значений х, тем точнее будет
полученный график.
Задание №1.
Построить в среде EXCEL график функции y=x2 на интервале [-2;2] с шагом 0,5
Алгоритм построения графика функции:
1. В ячейку A3 и В3 ввести соответственно Х и У
2. В ячейки А4:А12 с помощью автозаполнения ввести значения от -2 до 2.
3. В ячейку В4 ввести формулу =А4^2 и нажать Enter
4. Скопируйте эту формулу в нижние ячейки до ячейки В12.
5. Для построения графика функции на этом интервале: выделить ячейки В4:В12.
6. На вкладке вставка в меню диаграммы нажать кнопку график и выбрать верхний левый график.
7. После чего щелкнуть правой кнопкой мыши по графику и выбрать - Выбрать Данные.
8. В открывшемся окне нажать Изменить в колонке Подписи горизонтальной оси (категории) и в поле Диапазон подписей оси ввести А4:А12 и нажать ок.
9. Нажать Изменить в колонке Элементы легенды (ряды) и в появившемся окне в поле Имя ряда ввести «График функции y=x2 и нажать ок
10. Переименовать лист. Назвать его - График 1 .
|
|
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
L |
M |
N |
O |
P |
|
|
1 |
Задание 1. |
||||||||||||||||
|
2 |
Построить в среде EXCEL график функции y=x2 на интервале [-2;2] с шагом 0,5
|
||||||||||||||||
|
3 |
x |
у |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
4 |
-2 |
4 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
5 |
-1,5 |
2,25 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6 |
-1 |
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
7 |
-0,5 |
0,25 |
|
|
|
|
|
|
|
|
|||||||
