microbik.ru
1 2 3

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


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

Цель работы:

  1. Изучить логические функции табличного процессора EXCEL.

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

  3. Освоить описание функций с условиями и построение их графиков.

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

  5. Освоить применение функций ЕСЛИ, СЧЕТЕСЛИ для обработки табличной информации.

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

Выполнение:

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

  2. Заполнить ячейки рабочего листа значениями логических переменных X, Y, Z.

  3. Вычислить значения элементарных логических действий И(), ИЛИ(), НЕ() (столбцы D-G).

  4. В последнем столбце записать логическое выражение XˆYˇ(¬XˇZ) (значения двух последних столбцов должны совпадать).

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



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

Выполнение:

  1. Дать рабочему листу название «Лог. функция».

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

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



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

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

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

Выполнение:

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

  2. Заполнить таблицу сложения восьмеричных чисел согласно образцу.

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

  4. Составить таблицу вычитания отличную от таблицы сложения (размерность 5x5).

  5. Составить таблицу умножения (размерность 4x4).

  6. Записать формулы для перевода действительного числа из восьмеричной системы счисления в десятичную и обратно.

  7. Отформатировать таблицы.

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

  1. минимальное месячное количество осадков, выпавшее за 3 года;

  2. суммарное количество осадков, выпавшее за три года;

  3. среднемесячное количество осадков по каждому году;

  4. среднемесячное количество осадков по итогам трехлетних наблюдений;

  5. максимальное месячное количество осадков по итогам трехлетних наблюдений;

  6. количество засушливых месяцев за все 3 года, в которые выпало меньше 10 мм осадков;

  7. количество месяцев в каждом году с количеством осадков в пределах нормы (>20; <80 мм);

  8. Количество месяцев в каждом году с кол-вом осадков вне нормы (<10; >100 мм);

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

Выполнение:

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

  2. Для выполнения заданий использовать функции МИН; МАКС; СРЗНАЧ; СУММ категории Статистические.

  3. Для выполнения заданий 6,7,8  функцией СЧЕТЕСЛИ, которая подсчитывает количество непустых ячеек внутри интервала, удовлетворяющих заданному критерию. При ее использовании нужно заполнить две строки: интервал поиска, т.е. диапазон ячеек, и критерий подсчета, в качестве которого может применяться проверка условия. Функция СЧЁТЕСЛИ имеет только два аргумента!

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

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

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

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




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

Цель работы:

  1. Освоить инструмент Подбор параметра для решения нелинейных уравнений вида f(x) = 0.

  2. Освоить программный инструмент Поиск решения для решения систем нелинейных уравнений в среде EXCEL .

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

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

Выполнение:

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

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

  3. Найти решение нелинейного уравнения в два этапа.

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

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

  2. Построить график функции f(x) (тип  График).

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

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

  1. Задать начальные приближения к корням  точки из отрезков локализации корней, например: 2,2; 0 и 1,5. Ввести эти значения в ячейки F16, F17 и F18 соответственно.

  2. В ячейку G16 ввести формулу =(F16^3-4*F16+1)/(ABS(F16)+1) и с помощью маркера заполнения размножьте эту формулу в ячейки G17 и G18;

  3. На вкладке Данные выберите Анализ «что-если» открыть диалоговое окно Подбор параметра и заполнить его поля: в поле Установить в ячейке ввести G16 (в этом поле дается ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения); в поле Значение ввести значение 0 (здесь указывается правая часть уравнения); в поле Изменяя значение ячейки ввести F16 (в этом поле дается ссылка на ячейку, отведенную под переменную). После нажатия кнопки ОК средство подбора параметра находит приближенное значение корня с заданной точностью.

  4. Повторить указанные в предыдущем абзаце действия для ячеек G17, F17 и G18, F18 соответственно. В ячейках F16:F18 будут содержаться значения корней уравнения, а в ячейках G16:G18  значения функции в этих точках, близкие к нулю.

  5. Оформить созданный документ заголовками и комментариями, как в образце.

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

Задание: 2. Найти решение системы нелинейных уравнений на интервале [-1,7; 1,7] с помощью Excel, используя инструмент Поиск решения.

Выполнение:

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

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

  3. Столбец А (аргумент x) заполнить с помощью арифметической прогрессии на интервале [-1,7; 1,7] с шагом 0,1.

  4. Столбцы B, C, D озаглавить как у1, у2, у3 и заполнить формулами соответствующими полуокружностям и прямой, используя относительную адресацию и маркер заполнения.

  5. Построить графики функций системы уравнений для диапазона А2:D36 в одной системе координат и определить интервалы локализации корней.

  6. Отредактировать диаграмму, согласно образцу.

  7. На втором этапе  УТОЧНЕНИЕ КОРНЕЙ  найти корни системы уравнений с помощью инструмента Поиск решения.

  8. Для нахождения первого корня в ячейку F25 ввести начальное приближение для первого корня по х. В ячейку G25 – начальное приближение для первого корня по у. В ячейку H25 ввести формулу: = (F25^2+G25^2–3)^2+(2*F25+3*G25–1)^2

  9. Чтобы получить уточненное значение первого корня, открыть диалоговое окно Поиск решения вкладки Данные. В поле Изменяя ячейки ввести диапазон ячеек F25:G25. В группе Равной установить переключатель в положение Значению, в поле ввода которого ввести 0. Убедитесь, что в диалоговом окне Параметры поиска решения снят флажок Линейная модель. Затем нажать кнопку Выполнить.

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

  1. Вы получили приближенное решение в ячейках F25 и G25, а в ячейке H25 достаточно близкое к нулю значение и с заданной точностью приблизились к точке пересечения окружности с прямой линией. Если в ячейке H25 будет большое число, то решение найдено неверно.

  2. Аналогично находится второе решение. Но в качестве начального приближения принимается точка, близкая по координатам ко второму корню.

  3. Проверьте пару значений (1,5; 0). В область притяжения какого корня Вы при этом попадаете?

  4. Оформить созданный документ заголовками и комментариями, как в образце.

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

Задание: 3. Найти экстремумы функции на интервале [ 2; 2] с шагом 0,1. Точность поиска  = 10-6

Выполнение:

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

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

  3. На этапе  УТОЧНЕНИЕ ЭКСТРЕМУМОВ  найти уточненные значения координат экстремумов и значения функции в этих точках с помощью инструмента Поиск решения.

  4. Для нахождения первого экстремума (МАКСИМУМА) в ячейку F17 ввести начальное приближение, в ячейку G17 ввести формулу: = F17^3  F17^2 + 4

  5. Чтобы получить уточненное значение максимума, открыть диалоговое окно Поиск решения вкладки Данные. В поле Установить целевую ячейку ввести адрес ячейки, содержащей формулу  G17. В группе Равной установить переключатель в положение Максимальному значению. В поле Изменяя ячейки ввести адрес ячейки F17. Затем нажать кнопку Выполнить.

  6. Вы получили приближенное значение координаты экстремума и максимальное значение функции в ячейках F17 и G17 соответственно.

  7. Аналогично находится второй экстремум (МИНИМУМ). Но при настройке диалогового окна Поиск решения в группе Равной установить переключатель в положение Минимальному значению.

  8. Оформить созданный документ заголовками и комментариями, как в образце.

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

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

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

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

Вар

Решить уравнение и найти экстремум функции

Решить систему нелинейных уравнений

1





2





3





4





5






следующая страница >>