Лабораторная работа №3 логические функции в excel цель работы: Изучить логические функции табличного процессора excel

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

ЛОГИЧЕСКИЕ ФУНКЦИИ В EXCEL

Цель работы:

Изучить логические функции табличного процессора EXCEL.
Научиться использовать логические функции для построения таблиц истинности.
Освоить описание функций с условиями и построение их графиков.
Создать таблицу вычисления арифметических выражений в позиционных системах счисления.
Освоить применение функций ЕСЛИ, СЧЕТЕСЛИ для обработки табличной информации.

Задание 1: Построить таблицу истинности логического выражения (ИСТИНА или ЛОЖЬ).

Выполнение:

Дать рабочему листу название «Лог. выражение».
Заполнить ячейки рабочего листа значениями логических переменных X, Y, Z.
Вычислить значения элементарных логических действий И(), ИЛИ(), НЕ() (столбцы D-G).
В последнем столбце записать логическое выражение XˆYˇ(¬XˇZ) (значения двух последних столбцов должны совпадать).
Отформатировать ячейки таблицы (использовать переносить по словам).

Задание 2: табулировать заданную функцию.

Выполнение:

Дать рабочему листу название «Лог. функция».
Используя арифметическую прогрессию сформировать ряд значений аргумента (столбец А).
В строке формул для столбца B задать значение логической функции (использовать автозаполнение с относительной адресацией).

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

Задание 3: создать таблицу значений арифметических действий в восьмеричной системе счисления (допустимо использовать инженерный калькулятор).

Выполнение:

Дать рабочему листу название «Восьмеричная».
Заполнить таблицу сложения восьмеричных чисел согласно образцу.
Дополнить две строки и два столбца таблицы, выделить результаты «неправильные» в десятичной системе счисления.
Составить таблицу вычитания отличную от таблицы сложения (размерность 5x5).
Составить таблицу умножения (размерность 4x4).
Записать формулы для перевода действительного числа из восьмеричной системы счисления в десятичную и обратно.
Отформатировать таблицы.

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

минимальное месячное количество осадков, выпавшее за 3 года;
суммарное количество осадков, выпавшее за три года;
среднемесячное количество осадков по каждому году;
среднемесячное количество осадков по итогам трехлетних наблюдений;
максимальное месячное количество осадков по итогам трехлетних наблюдений;
количество засушливых месяцев за все 3 года, в которые выпало меньше 10 мм осадков;
количество месяцев в каждом году с количеством осадков в пределах нормы (>20; <80 мм);
Количество месяцев в каждом году с кол-вом осадков вне нормы (<10; >100 мм);
построить столбчатую диаграмму по данным наблюдения за осадками в течение трех лет, позволяющую провести сравнение по количеству осадков.

Выполнение:

Дать рабочему листу название «Погода».
Для выполнения заданий использовать функции МИН; МАКС; СРЗНАЧ; СУММ категории Статистические.
Для выполнения заданий 6,7,8 функцией СЧЕТЕСЛИ, которая подсчитывает количество непустых ячеек внутри интервала, удовлетворяющих заданному критерию. При ее использовании нужно заполнить две строки: интервал поиска, т.е. диапазон ячеек, и критерий подсчета, в качестве которого может применяться проверка условия. Функция СЧЁТЕСЛИ имеет только два аргумента!

Например, =СЧЁТЕСЛИ(А1:С15;”<25”).

Отформатировать таблицу по своему усмотрению.

Таблица построена на основании наблюдений метеостанции

г. Екатеринбурга

	2002 г.	2003 г.	2004 г.		2002 г.	2003 г.	2004г.
Январь	37,2	34,5	8,0	Июль	57,1	152,9	50,6
Февраль	11,4	51,3	1,2	Август	43,8	96,6	145,2
Март	16,5	20,5	3,8	Сентябрь	85,7	74,8	79,9
Апрель	19,5	26,9	11,9	Октябрь	86,0	14,5	74,9
Май	11,7	45,5	66,3	Ноябрь	12,5	21,0	56,6
Июнь	129,1	71,5	60,0	Декабрь	21,2	22,3	9,4

Задания для самостоятельного выполнения.

Выполнить задания 1-3 лабораторной работы.

Вар	Составить таблицу истинности	Построить график функции	Составить таблицы в системе счисления
1			Семеричная
2			Девятеричная
3			Пятеричная
4			Шестеричная
5			Троичная

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

РЕШЕНИЕ НЕЛИНЕЙНЫХ УРАВНЕНИЙ и систем уравнений ПРОГРАММНЫМИ СРЕДСТВАМИ EXCEL

Цель работы:

Освоить инструмент Подбор параметра для решения нелинейных уравнений вида f(x) = 0.
Освоить программный инструмент Поиск решения для решения систем нелинейных уравнений в среде EXCEL .
Освоить программные средства для поиска координат и значений экстремумов функции одной переменной с помощью инструмента Поиск решения.

Задание: 1. Найти все корни уравнения

.

Выполнение:

Дать рабочему листу название «Уравнение».
Настроить текущий документ Еxcel на вычисления с заданной точностью, открыв Настройку панели быстрого доступа и выбрав Другие команды. На вкладке Формулы задать предельное число итераций, равное 10000, и относительную погрешность, равную 0,000001.
Найти решение нелинейного уравнения в два этапа.

Этап ЛОКАЛИЗАЦИЯ КОРНЕЙ

Создать таблицу значений функции f(x) для х  [2,5; 2,5], шаг изменения 0,1.
Построить график функции f(x) (тип График).
Основываясь на данных таблицы и графика, выделить интервалы, на которых функция меняет знак (существует корень).

Этап  УТОЧНЕНИЕ КОРНЕЙ

Задать начальные приближения к корням точки из отрезков локализации корней, например: 2,2; 0 и 1,5. Ввести эти значения в ячейки F16, F17 и F18 соответственно.
В ячейку G16 ввести формулу =(F16^3-4*F16+1)/(ABS(F16)+1) и с помощью маркера заполнения размножьте эту формулу в ячейки G17 и G18;
На вкладке Данные выберите Анализ «что-если» открыть диалоговое окно Подбор параметра и заполнить его поля: в поле Установить в ячейке ввести G16 (в этом поле дается ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения); в поле Значение ввести значение 0 (здесь указывается правая часть уравнения); в поле Изменяя значение ячейки ввести F16 (в этом поле дается ссылка на ячейку, отведенную под переменную). После нажатия кнопки ОК средство подбора параметра находит приближенное значение корня с заданной точностью.
Повторить указанные в предыдущем абзаце действия для ячеек G17, F17 и G18, F18 соответственно. В ячейках F16:F18 будут содержаться значения корней уравнения, а в ячейках G16:G18  значения функции в этих точках, близкие к нулю.
Оформить созданный документ заголовками и комментариями, как в образце.

$c:\documents and settings\dvdalex\local settings\temporary internet files\content.word\новый рисунок (5).png$

Задание: 2. Найти решение системы нелинейных уравнений

на интервале [-1,7; 1,7] с помощью Excel, используя инструмент Поиск решения.

Выполнение:

Дать рабочему листу название «Система».
Преобразовать уравнения системы относительно переменной у, получив две полуокружности в положительной и отрицательной полуплоскостях, пересекающих прямую.
Столбец А (аргумент x) заполнить с помощью арифметической прогрессии на интервале [-1,7; 1,7] с шагом 0,1.
Столбцы B, C, D озаглавить как у1, у2, у3 и заполнить формулами соответствующими полуокружностям и прямой, используя относительную адресацию и маркер заполнения.
Построить графики функций системы уравнений для диапазона А2:D36 в одной системе координат и определить интервалы локализации корней.
Отредактировать диаграмму, согласно образцу.
На втором этапе  УТОЧНЕНИЕ КОРНЕЙ найти корни системы уравнений с помощью инструмента Поиск решения.
Для нахождения первого корня в ячейку F25 ввести начальное приближение для первого корня по х. В ячейку G25 – начальное приближение для первого корня по у. В ячейку H25 ввести формулу: = (F25^2+G25^2–3)^2+(2*F25+3*G25–1)^2
Чтобы получить уточненное значение первого корня, открыть диалоговое окно Поиск решения вкладки Данные. В поле Изменяя ячейки ввести диапазон ячеек F25:G25. В группе Равной установить переключатель в положение Значению, в поле ввода которого ввести 0. Убедитесь, что в диалоговом окне Параметры поиска решения снят флажок Линейная модель. Затем нажать кнопку Выполнить.

$c:\documents and settings\dvdalex\local settings\temporary internet files\content.word\новый рисунок (9).bmp$

Вы получили приближенное решение в ячейках F25 и G25, а в ячейке H25 достаточно близкое к нулю значение и с заданной точностью приблизились к точке пересечения окружности с прямой линией. Если в ячейке H25 будет большое число, то решение найдено неверно.
Аналогично находится второе решение. Но в качестве начального приближения принимается точка, близкая по координатам ко второму корню.
Проверьте пару значений (1,5; 0). В область притяжения какого корня Вы при этом попадаете?
Оформить созданный документ заголовками и комментариями, как в образце.

$c:\documents and settings\dvdalex\local settings\temporary internet files\content.word\новый рисунок.png$

Задание: 3. Найти экстремумы функции

на интервале [ 2; 2] с шагом 0,1. Точность поиска  = 10^-6

Выполнение:

Дать рабочему листу название «Экстремум».
Задав значения аргумента (столбец А) и рассчитав значения функции (столбец В), построить график функции и определить интервалы локализации экстремумов.
На этапе  УТОЧНЕНИЕ ЭКСТРЕМУМОВ найти уточненные значения координат экстремумов и значения функции в этих точках с помощью инструмента Поиск решения.
Для нахождения первого экстремума (МАКСИМУМА) в ячейку F17 ввести начальное приближение, в ячейку G17 ввести формулу: = F17^3 F17^2 + 4
Чтобы получить уточненное значение максимума, открыть диалоговое окно Поиск решения вкладки Данные. В поле Установить целевую ячейку ввести адрес ячейки, содержащей формулу G17. В группе Равной установить переключатель в положение Максимальному значению. В поле Изменяя ячейки ввести адрес ячейки F17. Затем нажать кнопку Выполнить.
Вы получили приближенное значение координаты экстремума и максимальное значение функции в ячейках F17 и G17 соответственно.
Аналогично находится второй экстремум (МИНИМУМ). Но при настройке диалогового окна Поиск решения в группе Равной установить переключатель в положение Минимальному значению.
Оформить созданный документ заголовками и комментариями, как в образце.

Примечание: Если на вкладке Данные не оказалось Поиска решения, то его нужно подгрузить, используя Настройку панели быстрого доступа.

$c:\documents and settings\dvdalex\local settings\temporary internet files\content.word\новый рисунок (8).png$

Задания для самостоятельного выполнения.

Выполнить задания 1-3 лабораторной работы для заданных вариантов функций.

Вар	Решить уравнение и найти экстремум функции	Решить систему нелинейных уравнений
1
2
3
4
5

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

Формулы EXCEL для обработки массивов данных

Цель работы:

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

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

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

Ввод матричной формулы завершается нажатием комбинации клавиш Ctrl+Shift+Enter, а не просто Enter, как при обычных вычислениях.

Задание: 1. Рассчитать требуемые характеристики квадратной матрицы и определить решение системы линейных уравнений.

Выполнение:

Дать рабочему листу название «Матрицы».
Вычислить определитель квадратной матрицы (числовая характеристика) с помощью функции МОПРЕД категории Математические (например, =МОПРЕД(B2:D4)).
Вычислить обратную матрицу для заданной с помощью функции МОБР категории Математические (заметим, что матричное произведение исходной матрицы и ее обратной матрицы дает единичную матрицу).
Транспонировать матрицу (поменять местами строки и столбцы) с помощью функции ТРАНСП категории Ссылки и массивы. (после транспонирования вектор-столбец преобразуется в строку, а вектор-строка в столбец).
Выполнить операции сложения, вычитания, умножения и деления матрицы и числа посредством арифметических операторов: +, , *, / (например, =B2:D4 * 0,5).
Операции поэлементного сложения, вычитания, умножения и деления применимы только к матрицам одинаковой размерности и выполняются посредством арифметических операторов +, , *, /. (например, =F2:F4+H2:H4).
Вычислить матричное произведение двух матриц с помощью функции МУМНОЖ категории Математические (число столбцов первой матрицы равно числу строк второй матрицы). В результате этой операции получается матрица, число строк которой равно числу строк первой умножаемой матрицы, а число столбцов числу столбцов второй матрицы.

$c:\documents and settings\dvdalex\local settings\temporary internet files\content.word\новый рисунок (20).png$

Решить систему линейных алгебраических уравнений (СЛАУ).

В алгебраической форме СЛАУ порядка n записываются в виде:

Или в матричной форме: АХ = В, где А матрица коэффициентов; В вектор-столбец свободных членов; Х вектор-столбец неизвестных:

Решение СЛАУ в матричном виде вычисляется по формуле Х = А^-1В, где А^-1 матрица, обратная матрице коэффициентов А.

На рабочем листе Excel записаны матрица коэффициентов А и вектор-столбец свободных членов В. Для получения решения выделить ячейки, соответствующие вектору-столбецу из n элементов для неизвестных и записать матричную формулу решения системы (например: =МУМНОЖ(МОБР(B2:D4);F2:F4))

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

Выполнение:

Дать рабочему листу название «Ведомость».

Копировать таблицу ведомости по зарплате на лист Excel (см. образец).

№ п/п	Фамилия	Размер оклада, руб.	Подоходный налог (12 % от оклада)	Отчисления в пенсионный фонд (1 % от оклада)	Профсоюзный взнос (1 % от оклада)	К выдаче


1	Ушков А.С.	9 500,00
2	Карпов В.А.	7 000,00
3	Вилков И.И.	15 000,00
4	Абрамов С.Т.	18 000,00
5	Иванова С.И.	13 500,00
6	Кукушкина С.А.	7 000,00
7	Ларин В.Н.	10 000,00
8	Машин С.И.	8 000,00
9	Зуев А.И.	15 000,00
10	Кошкин А.Н.	9 500,00
	Итого		0,00р.	0,00р.	0,00р.	0,00р.

Отсортировать сотрудников по алфавиту.
Используя матричные формулы, рассчитать значения подоходного налога, отчисления в пенсионный фонд и профсоюзный взнос (например: {=C4:C23*0,12}).
Рассчитать итоговую сумму к выдаче (матричная формула).
В дополнительном столбце восстановить первоначальные величины окладов без вычетов налогов и взносов.
Удалить первую строку ведомости (сотрудник Абрамов).
Добавить нового работника Юшкова А.Ф., размер оклада которого составляет 13570 руб. и рассчитать для него значения по всем столбцам ведомости.

Примечание: Табличные формулы редактируются не как обычные формулы рабочего листа Excel. Ниже приведены алгоритмы редактирования табличных формул.

Редактирование формулы

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

2. Нажать клавишу F2.

3. Внести изменения в формулу.

4. Нажать клавиши Ctrl + Shift + Enter.

Изменение размеров блок (удаление/вставка строк)

1. Выделить блок с табличной формулой.

2. Нажать клавишу F2.

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

4. Вводим текст во все ячейки с помощью клавиш Ctrl + Enter. Табличная формула прекратила существование.

5. Очистить строку таблицы.

6. Выделить блок с табличной формулой.

7. Нажать клавишу F2.

8. Удалить апостроф.

9. Нажать клавиши Ctrl + Shift + Enter.

Задания для самостоятельного выполнения.

Вариант 1.

Заданы матрицы

Решить систему линейных уравнений А Х=В по формуле Х= А^-1 В.

Вычислить:

Сумму матрицы А, и каждой строки матрицы А.

Произведение 1-й строки и 2-го столбца матрицы А (поэлементно).

Транспонировать матрицы А, 4В.

Найти сумму А и 3.

Найти произведение матрицы Х на число 5.

Найти сумму элементов В и 1-й строки матрицы А

Вариант 2.

Заданы матрицы

Решить систему линейных уравнений А Х=В по формуле Х= А^-1 В.

Вычислить:

Разность 3-й строки и 1-го столбца матрицы А (поэлементно).

Обратную матрицу для матрицы А-3.

Транспонировать матрицы А, В.

Найти сумму элементов А, В и Х.

Найти произведение матрицы А на число 1/3.

Найти сумму элементов В и 1-й строки матрицы А.

Вариант 3.

Заданы матрицы

Решить систему линейных уравнений А Х=В по формуле Х= А^-1 В.

Вычислить:

Сумму 1-й строки и 2-го столбца матрицы А (поэлементно).

Обратную матрицу для матрицы А.

Транспонировать матрицы 3Х, В.

Найти сумму элементов В и 1-й строки матрицы А.

Найти произведение матрицы А на число 0,5.

Определитель матрицы А .

Вариант 4.

Заданы матрицы

Решить систему линейных уравнений А Х=В по формуле Х= А^-1 В.

Вычислить:

Сумму матрицы А, и каждого столбца матрицы А.

Разность 3-й строки и 2-го столбца матрицы А (поэлементно).

Транспонировать матрицы Х, 2В.

Найти разность В и 1-го столбца матрицы А.

Найти произведение матрицы Х на число 3.

Найти матричное произведение А и 2-го столбца матрицы А.
Вариант 5.

Заданы матрицы

Решить систему линейных уравнений А Х=В по формуле Х= А^-1 В.

Вычислить:

Сумму матрицы А, и каждой строки матрицы А.

Обратную матрицу для матрицы А-3.

Транспонировать матрицы А, В.

Найти сумму А и 10.

Найти произведение матрицы Х на число 2.

Найти матричное произведение А и 3-го столбца матрицы А.

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

ПРОГНОЗ ЗНАЧЕНИЙ ЧИСЛОВЫХ ВЕЛИЧИН С ПОМОЩЬЮ СТАТИСТИЧЕСКИХ ФУНКЦИЙ EXCEL

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

Задание: 1. Для имеющихся статистических данных о численности населения России на период с 1993 по 1999 годы сделать прогноз на 2001 и 2003 г.г.

Выполнение:

Дать рабочему листу название «Прогноз».

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

	A	B	C	D	E	F
1	Годы	1993	1995	1997	1999
2	Численность населения, млн.чел.	148,3	147,9	147,5	146,3

Сделать предположение о численности населения России в 2001 году с помощью функции ПРЕДСКАЗ, которая позволяет вычислить теоретическое значение зависимой переменной (в данном случае это численность населения) в фиксированной точке аргумента (т.е. для определенного года). Для этого в ячейку F1 ввести дату 2001 , а в ячейку F2 формулу: = ПРЕДСКАЗ(F1;B2:E2;B1:E1)
Аналогичным способом предсказать численность российских граждан в 2003 году, но уже учитывая рассчитанное ранее значение в 2001 г.
Используя условное форматирование, выделить столбцы с минимальными и максимальными показателями.

Задание: 2. С помощью функций ТЕНДЕНЦИЯ и РОСТ предсказать изменение численности населения на периоды с 2001 по 2015 и с 1985 по 1993.

Выполнение:

Дать рабочему листу название «Тенденция».
Копировать часть таблицы «Прогноз».
Спрогнозировать дальнейшую динамику изменения численности на период с 2003 по 2023 г.г. при помощи функции ТЕНДЕНЦИЯ. Эта функция позволяет предсказать значения зависимой переменной для целого диапазона значений независимой переменной по линейному закону.
Ввести в ячейки Н1:L1 временной период с 2005 по 2023 г.г. с шагом 2 года. Выделить диапазон H2:L2 и ввести формулу =ТЕНДЕНЦИЯ(A2:G2;A1:G1;H1:L1). Завершить ввод нажатием комбинации клавиш Ctrl+Shift+Enter.
Вычислить с помощью функции ТЕНДЕНЦИЯ предположительную численность населения России на период с 1987 по 1993 г.г.
Аналогичным образом спрогнозировать изменение численности населения с помощью функции РОСТ по экспоненциальному закону.
По заданным экспериментальным точкам и полученным модельным данным построить диаграмму типа График, в виде плавной линии. Сделать вывод о изменении линейной и экспоненциальной модели изменения численности населения.

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

Выполнение:

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

x

1,5

2

3

4,5

5

6

7,5

y

12

7

3

11

17

18

23
Построить диаграмму для экспериментальных точек. Тип диаграммы График, но точки не соединены линиями.
Выделив график и щелкнув на нем правой клавишей мыши, вызвать контекстное меню и выбрать в нем строку Добавить линию тренда.
Добавить Линейный тренд, Полиномиальный 2-го и 3-го порядка, Степенной. В процессе построения тренда указать уравнение, которому подчиняется зависимая величина. Для этого при построении линии тренда на вкладке Параметры установить флажок в строке Показывать уравнение на диаграмме.
Выполнить для линий тренда: Поместить на диаграмму величину достоверности аппроксимации.
Оформить линии трендов по своему усмотрению, меняя цветовую гамму и форму маркеров.
Сформировать сводную таблицу для анализа качества приближения к экспериментальным точкам, указав в столбцах тип тренда, уравнение зависимости и значение достоверности аппроксимации.
Сделать вывод: Какой из трендов дает наилучшее приближение к экспериментальным точкам?
Выполнить условное форматирование таблицы с учётом сделанного вывода.

Задания для самостоятельного выполнения.

По следующим данным с помощью функции ПРЕДСКАЗ спрогнозируйте стоимость киловатта электроэнергии до 2015 года.

Годы

1996

1997

1998

1999

2000

2001

2002

2003

Стоим.
кВт, коп.

4

13

24

38

50

70

80

98

Выполните графическую оценку данных, используя функции РОСТ и ТЕНДЕНЦИЯ.

Вариант	1	2	3	4	5
Использовать годы	1996,1998,2000,2002	1999,2001,2002,2003	1997,1999,2001,2003	1998,1999,2000,2001	1996,1997,1998,1999

Построить Линейный, Полиномиальный 2-го и 3-го порядка и Степенной тренды для следующих экспериментальных данных. Выбрать наилучший вид зависимости.

вар	1	2	3	4	5	6	7	8	9	10	11	12
1	2	5	6	8	3	2	1	3	5	7	8	13
2	0.2	0.1	0.3	0.5	0.6	0.8	0.9	1.2	1.3	1.5	1.6	1.7
3	22	25	31	35	37	45	38	36	35	28	27	25
4	0.2	0.6	0.8	0.9	1.2	1.3	1.5	1.6	1.7	1.9	2.0	2.5
5	37	37	25	21	9	10	20	24	37	41	45	46