Упражнение 2: Построение диаграмм

1. Создайте на листе 2 следующую таблицу:

2. Введите в столбец Итого формулу Сумм, укажите диапазон ячеек и автозаполнением заполните оставшиеся ячейки.

3. Построите для этой таблицы диаграммы двух типов:

§ Круговую диаграмму, демонстрирующую премиальные выплаты Иванову по месяцам.

1) Выделите ячейки с числовыми данными и ячейки, которые берутся в качестве подписей к секторам

2) На панели инструментов или в меню Вставка выберите Диаграммы и тип диаграммы Круговая

3) В появляющихся диалоговых окнах задаем параметры диаграммы. Диаграммы поместите на данном листе.

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

4. Лист 2 переименуйте как Диаграммы.

Задания для самостоятельной работы:

1. Постройте график функций с шагом 0, 1… см.таблицу вариантов

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

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

Таблица вариантов заданий

№ варианта	Задание 1	Задание 2
	x ;	нужно показать изменения в течение времени и отобразить соотношение величин.
	y=	нужно показать какую часть занимает каждый вид сырья от общего производства на 2003 г
	y=	нужно показать количество добываемой нефти за каждый период производства.
	y=	нужно показать сравнительные показатели добычи угля за каждый период.
	y=	нужно показать сравнительные показатели всех видов сырья за каждый период производства.

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

1. Какими способами можно ввести функции в табличном процессоре Excel?
2. Какие категории функций вы знаете?
3. Какие статистические функции вы знаете?
4. Что такое диаграмма? Как стоят диаграммы?
5. Какие действия относятся к редактированию диаграмм?

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

ТЕМА: «Логические функции. Построение графиков функции с двумя и тремя условиями»

Цель работы: сформировать умение работать с логическими функциями.

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

Логические функции служат для выполнения вычислений в зависимости от выполнения некоторого условия. В условиях могут использоваться операции сравнения =, >, <, < > (не равно), > = (больше или равно), < = (меньше или равно).

Функция ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь) результатом является значение1, если логическое_выражение истинно и значение2 в противном случае.

Лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Значение_если_истина – это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА. Если лог_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА.

Значение_если_ложь – это значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ. Если лог_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ.

Пример: ЕCЛИ (логическое_выражение; значение1; значение2) - Пример: в ячейке A1 набрано число 30000, а в ячейке B1 формула =ЕСЛИ(A1< 20000; 12; 15). Результатом будет число 15, т.к. условие не выполняется.

Функции И, ИЛИ служат для создания сложных условий:

И (логическое_выражение1; логическое_выражение2;....) - возвращает значение “ИСТИНА”, если все аргументы имеют значение “ИСТИНА”, а в противном случае -“ЛОЖЬ”.

ИЛИ (логическое_выражение1; логическое_выражение2;...) - возвращает значение “ИСТИНА”, если хотя бы один из аргументов имеет значение “ИСТИНА”, а противном случае - “ЛОЖЬ”.

П/ры: =ЕСЛИ (И(A1> =20000; A1< 40000); 15; 18) - вычисленное значение равно 15 при величине A1 от 20000 до 40000 и равно 18 в противном случае.

Можно создавать сложные условия и вложением функций ЕСЛИ. Пример:

=ЕСЛИ (A1< 20000; 12; ЕСЛИ (A1< 40000; 15; 18)) - если величина A1 меньше 20000, вычисленное значение равно 12, иначе если она меньше 40000, то результат равен 15, а в противном случае (то есть, А1 больше 40000), формула вернет значение 18.

Упражнение 1. З адан рейтинг по какому-либо предмету в процентах. Если он не ниже 45%, то студент аттестован по данному предмету, в противном случае не аттестован.

1. Лист1 переименовать на Задание 1.

2. Заполнить таблицу по образцу.

3. Определить аттестован ли студент. Выделить ячейку С3. Выполнить следующую команду. Вставка→ Функция. В окне Мастера функции выбрать категорию Логические, имя функции ЕСЛИ.

4. В появившимся окне функции ЕСЛИ. В поле Логическое выражение устанавливаем условие, проверяем ячейку В3, является ли оно больше 45%, если да, то студент аттестован, если нет то не аттестован. Для этого:

В поле Логическое выражение записываем условие: В3> =45%

• В поле Значение если_истина: «аттестован»
• В поле Значение если_ложь: «не аттестован»
• Нажимаем на ОК.

5. Копируем формулу для остальных.

Упражнение 2. Аттестация проводилась по 3 предметам и надо определить аттестован ли студент по всем предметам.

1. Перейти на Лист2. Переименовать его на Задание2.

2. Заполнить таблицу по образцу.

3. Значения необходимо записать в процентах. Для этого

§ Выделить диапазон В3: D7. Вызвать контекстное меню→ Формат ячеек. Перейти на вкладку Числа. Числовой формат выбрать Процентный, Число десятичных знаков установить 0.

4. В ячейке Е3 вызвать функцию ЕСЛИ.

5. На уровне строки формулы с левой стороны щелкнуть на

6. В списке выбрать Другие функции, в категории Логические выбрать функцию И.

7. В поле Логическое_значение1: B3> =45%, Логическое_значение2: C3> =45%, Логическое_значение3: D3> =45%.

8. Щелкнуть в строке формул на ЕСЛИ.

9. В поле Значение если_истина набрать слова: «аттестован»

10. В поле Значение если_ложь набрать слова: «не аттестован»3

11. Нажать на ОК

12. Результат протянуть и для остальных ячеек.

1. В столбце А запишите все значения Х с шагом 0, 2, начиная с ячейки А2.

2. В столбце В2 запишите формулу:

=Если(А2< =0; (1+2*A2^2-sin(A2)^2)^(1/2); (2+A2)/(2+EXP(-0.1*A2))^(1/3)) или с помощью Мастера функции выберите функцию Если, в появившемся диалоговом окне заполните условие, выражения 1 и выражения 2.

3. Автозаполнением скопируйте формулы в остальные ячейки.

4. Постройте график функции.

Задания для самостоятельной работы:

Задание 1. Постройте графики функций с шагом 0, 2:

№ вар	При x	Задание 1	Задание 2
			Найдите дискриминант D= b2-4ac квадратного уравнения ax2+bx+c=0 … и с помощью функции Если выведите на экран количество корней квадратного уравнения. (Если D> 0, то два корня,)
			Если вес пушного зверька в возрасте от 6-ти до 8-ми месяцев превышает 7 кг, то необходимо снизить дневное потребление витаминного концентрата на 125 г. Количество зверьков, возраст и вес каждого известны. Выяснить на сколько килограммов в месяц снизится потребление витаминного концентрата.
			Отдел работает над двумя проектами, причем каждый сотрудник (ввести фамилии 5 сотрудников на усмотрение) работает только над одним проектом. По каждому проекту начисляется премию (% от оклада). Проценты премий для каждого проекта различны. За 1-й проект – 0, 25, за 2-ой проект – 0, 35.
			Покупатели магазина пользуются 10% скидками, если покупка состоит более, чем из пяти товаров или стоимость покупки превышает 5000 тг. Составить ведомость, учитывающую скидки: покупатель, количество купленных товаров, стоимость покупки, стоимость покупки со скидкой. Выяснить сколько покупателей сделало покупки, стоимость которых превышает 5000 тг.
			Торговый склад производит уценку хранящейся продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг 10 месяцев, то – в 1, 5 раза. Получить ведомость уценки товара, которая должна включать следующую информацию: наименование товара, срок хранения, цена товара до уценки, цена товара после уценки.

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

1. Какие логические функции вы знаете?
2. Какое выражение будет выполняться в функции Если при истинном значений логического выражения?
3. Когда функция И дает истину?
4. Когда функция ИЛИ дает истину?
5. Какие операции используются при построении логического выражения в функции Если?

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

Тема: «Построение поверхности. Решение уравнений методом подбора параметров»

Цель работы: сформировать умения строить поверхности, решать уравнения методом подбора.

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

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

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

1. В своей папке создайте рабочую книгу Лаб_Ex_8

2. C ячейки А2 введите в столбец все значения Х, начиная с –1 с шагом 0, 2.

3. С ячейки В1 введите в строку все значения У, начиная с –1 с шагом 0, 2.

4. На пересечении в ячейку В2 введите формулу: =3*А2^2 – 2* SIN(B1)^2*B1^2

Здесь нужно подумать: какую адресацию нужно использовать, чтобы при копировании формула была правильной. Поэкспериментируйте, если вы скопируйте в столбец, что изменяется, если вы скопируйте в строку, что изменится? Получается, что Х всегда должны находиться в столбце А, значит фиксируем столбец $А, а У всегда должны находиться в строке 1, значит фиксируем строку $1.

5. В ячейке В2 редактируем формулу: =3*$А2^2 – 2* SIN(B$1)^2*B$1^2

6. С помощью маркера автозаполнения, заполняем остальные ячейки столбца В.

7. Выделяем диапазон В2: В12. Удерживая черный маркер копируем для остальных столбцов.

8. Выделите всю таблицу данных вместе со значениями Х, У, Z.

9. Вызовите Мастер диаграмм и выберите тип диаграммы – Поверхность (1-й вид).

10. Установите свои параметры диаграммы.

11. Разместите диаграмму на данном листе и переименуйте как Поверхность.

Упражнение 2: Решение уравнения х³-0, 01х²-0, 7044х+0, 139104=0 методом подбора.

1. Возьмем интервал [-1; 1] с шагом 0, 2. Для этого: Введите в ячейку А2 значение –1, а в ячейку А3- значение –0, 8. Выделив две ячейки, протяните маркером заполнения до значения 1.

2. В ячейку В2 введите формулу: =А2^3-0.01*A2^2-0.7044*A2+0.139104 (левая часть уравнения),

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

3. Обратите внимание что функция меняет знак на интервалах [-1; -0.8], [0.2; 0.4], [0.6; 0.8], за начальные приближения к корню разумно взять средние точки интервалов.

4. Занесите их в ячейки С2; С3; С4

5. В ячейку D2 введите формулу: =С2^3-0.01*С2^2-0.7044*С2+0.139104, маркером заполнения протяните в остальные ячейки.

6. Выделите ячейку D2.

7. Выберите команду Сервис/Подбор параметра. На экране отобразится окно Подбор параметра.

8. В поле Установить в ячейке отобразится ячейка D2.

9. В поле Значения введите 0. Здесь указывается значение правой части уравнения.

10.
В поле Изменяя значение ячейки введите С2. В данном поле приводится ссылка на ячейку, отведенную под переменную.

11. Нажмите кнопку ОК.

12. Аналогично в ячейках С3 и С4 находятся два оставшихся корня.

13. Графическим способом покажите корни уравнения, т. е. построите график функции у.

Задания для самостоятельной работы:

1. Построите поверхность при х, у Î [-1; 1]…

2. Найти все корни уравнения …

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

1. Как расположить данные в таблице, чтобы построить поверхность в трехмерном пространстве?
2. Какого типа адресация используется при записи формулы поверхности?
3. В чем заключается метод подбора при решений уравнений?
4. Для чего служит команда Подбор параметра?
5. С помощью каких команд можно построить поверхность?

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

Тема: «Сортировка и фильтрация данных»

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

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

Электронные таблицы Excel можно использовать в качестве базы данных. В этом случае электронную таблицу называют списком или базой данных Excel и используют соответствующую терминологию: Строка списка – запись базы данных; Столбец списка – поле базы данных.

Сортировка это переупорядочивание строки в таблице по любому полю и выполняется командой Данные ð Сортировка с установкой необходимых параметров. Целью сортировки является упорядочивание данных.

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

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