Решение уравнений методом подбора параметра

Пусть требуется решить уравнение . Для его решения необходимо привести уравнение к виду f(x)=0. В нашем случае к виду . Далее в ячейке A1 необходимо ввести значение «0». В ячейке B1 нужно найти значение функции в точке A1, т.е. в нашем случае ввести формулу = A1+3-COS(A1)+2* A1. Табличный курсор необходимо разместить в ячейке B1. Далее в пункте меню «Сервис» необходимо выбрать команду «Подбор параметра…». В появившемся диалоговом окне «Подбор параметра» необходимо установить следующие значения:

- установить в ячейке B1;

- значение 0;

- изменяя значение ячейке $A$1.

Далее необходимо нажать на кнопку «OK»

Если решение существует, то в появившемся далее окне «Результат подбора параметра» появится соответствующее сообщение. Решение необходимо искать в ячейке A1.

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

1. Запустить табличный процессор Microsoft Excel.

2. Используя математические функции электронных таблиц решить следующие задания (каждое задание на отдельном листе с именами «Задание №1», «Задание №2» и т.д.):

а) Найти значение функции в точке .

Организовать возможность ввода любого значение .

б) Протабулировать функцию на отрезке [-4;5) с шагом h=0,4 и построить ее график, используя точечную диаграмму.

в) Построить на одной точечной диаграмме графики функций

и на отрезке [-6;7) с шагом h=0,6.

г) Решить уравнение и выполнить проверку графическим методом.

3. Сохранить документ Microsoft Excel под именем Лабораторная работа №8_Фамилия.xls, указав в имени файла свою фамилию.

Лабораторная работа №9
«Проведение расчетов в электронной таблице с использованием математических и статистических функций»

Цели работы:

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

Теоретическая часть

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

СУММ

Суммирует все числа в интервале ячеек.

СУММ (число1;число2;...)

Число1, число2,... — от 1 до 30 аргументов, для которых требуется определить итог или сумму.

СУММЕСЛИ

Суммирует ячейки, заданные критерием.

СУММЕСЛИ (диапазон; критерий;диапазон_суммирования)

Диапазон — диапазон вычисляемых ячеек.

Критерий — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, "32", ">32", "яблоки".

Диапазон_суммирования — фактические ячейки для суммирования.

• Ячейки в «диапазон_суммирования» суммируются, только если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию.
• Если «диапазон_суммирования» опущен, то суммируются ячейки в аргументе «диапазон».

ПРОИЗВЕД

Перемножает числа, заданные в качестве аргументов и возвращает их произведение.

ПРОИЗВЕД (число1;число2;...)

Число1, число2,... — от 1 до 30 перемножаемых чисел.

ОКРУГЛ

Округляет число до указанного количества десятичных разрядов.

ОКРУГЛ (число; число_разрядов)

Число — округляемое число.

Число_разрядов — количество десятичных разрядов, до которого нужно округлить число.

• Если число_разрядов больше 0, то число округляется до указанного количества десятичных разрядов справа от десятичной запятой.
• Если число_разрядов равно 0, то число округляется до ближайшего целого.

Если число_разрядов меньше 0, то число округляется слева от десятичной запятой.

НЕЧЁТ

Возвращает число, округленное до ближайшего нечетного целого.

НЕЧЁТ (число)

Число — округляемое значение.

• Если аргумент число не является числом, то функция НЕЧЁТ возвращает значение ошибки #ЗНАЧ!.
• Независимо от знака числа, округление всегда производится с избытком. Если число является нечетным целым, то округления не происходит.

ЧЁТН

Возвращает число, округленное до ближайшего четного целого. Эту функцию можно использовать при обработке объектов, которые поступают парами. Например, упаковочный ящик позволяет упаковывать по два объекта в ряд. Ящик будет заполнен, если количество объектов, округленное до ближайшего четного числа, равняется вместимости ящика.

ЧЁТН (число)

Число — это округляемое значение.

• Если аргумент число числом не является, то ЧЁТН возвращает значение ошибки #ЗНАЧ!.
• Независимо от знака числа округление производится с избытком. Если число уже является четным целым, то никакого округления не производится.

ЦЕЛОЕ

Округляет число до ближайшего меньшего целого.

ЦЕЛОЕ (число)

Число — это вещественное число, округляемое до ближайшего меньшего целого.

ОКРВВЕРХ

Возвращает результат округления с избытком до ближайшего числа, кратного точности. Например, если в значениях цен необходимо избежать рублей, а товар стоит 442 рубля, используйте формулу =ОКРВВЕРХ(442;10), чтобы округлить цену с точностью до 10 рублей.

ОКРВВЕРХ (число; точность)

Число — это округляемое значение.

Точность — это кратное, до которого требуется округлить.

• Если один из аргументов не является числом, то ОКРВВЕРХ возвращает значение ошибки #ЗНАЧ!.
• Независимо от знака числа, округление производится с избытком. Если число уже кратно точности, то округления не производится.
• Если число и точность имеют разные знаки, то функция ОКРВВЕРХ возвращает значение ошибки #ЧИСЛО!.

ОКРВНИЗ

Округляет число до кратного заданной точности с недостатком.

ОКРВНИЗ (число; точность)

Число — это округляемое числовое значение.

Точность — это кратное, до которого требуется округлить.

• Если любой из аргументов не число, то ОКРВНИЗ возвращает значение ошибки #ЗНАЧ!.
• Если число и точность имеют разные знаки, то ОКРВНИЗ возвращает значение ошибки #ЧИСЛО!.
• Независимо от знака числа, округление всегда производится с недостатком. Если число уже кратно точности, то никакого округления не производится.

ОКРУГЛВВЕРХ

Округляет число до ближайшего большего по модулю.

ОКРУГЛВВЕРХ (число; число_разрядов)

Число — любое вещественное число, которое нужно округлить с избытком.

Число_разрядов — количество цифр, до которого округляется число.

• Функция ОКРУГЛВВЕРХ подобна функции ОКРУГЛ, за тем исключением, что округление всегда производится с избытком.
• Если число_разрядов больше 0 (нуля), то число округляется с избытком до заданного количества десятичных разрядов после десятичной запятой.
• Если число_разрядов равно 0, то число округляется до ближайшего целого.
• Если число_разрядов меньше 0, то число округляется с избытком, с учетом десятичных разрядов слева от десятичной запятой.

ОКРУГЛВНИЗ

Округляет число до ближайшего меньшего по модулю значения.

ОКРУГЛВНИЗ (число; число_разрядов)

Число — любое вещественное число, которое нужно округлить с недостатком.

Число_разрядов — количество цифр, до которого округляется число.

• Функция ОКРУГЛВНИЗ подобна функции ОКРУГЛ, за тем исключением, что число всегда округляется с недостатком.
• Если число_разрядов больше 0 (нуля), то число округляется с недостатком до заданного количество десятичных разрядов после запятой.
• Если число_разрядов равно 0, то число округляется вниз до ближайшего целого.
• Если число_разрядов больше 0, то число округляется с недостатком до заданного количеств десятичных разрядов слева от запятой.

ЧИСЛКОМБ

Возвращает количество комбинаций для заданного числа объектов. Функция ЧИСЛКОМБ используется для определения числа всех возможных сочетаний объектов в группы.

ЧИСЛКОМБ (число; число_выбранных)

Число — это число элементов.

Число_выбранных — это число объектов в каждой комбинации.

 

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

1) МАКС - возвращает наибольшее значение из набора значений.

МАКС (число1;число2;...)

Число1, число2,... — от 1 до 30 чисел, среди которых требуется найти наибольшее.

МАКСА

Возвращает наибольшее значение в списке аргументов. Наряду с числовыми значениями выполняется также сравнение текстовых и логических (таких как ИСТИНА и ЛОЖЬ) значений.

МАКСА (значение1;значение2;...)

Значение1, значение2,... — от 1 до 30 значений, среди которых требуется найти наибольшее.

Аргументы, содержащие значение ИСТИНА интерпретируются как 1, аргументы, содержащие текст или значение ЛОЖЬ интерпретируются как 0 (ноль).

МИН

Возвращает наименьшее значение в списке аргументов.

МИН (число1;число2;...)

Число1, число2,... — от 1 до 30 чисел, среди которых требуется найти наименьшее.

МИНА

Возвращает наименьшее значение в списке аргументов. Наряду с числовыми значениями выполняется также сравнение текстовых и логических, таких как ИСТИНА и ЛОЖЬ, значений.

МИНА (значение1;значение2;...)

Значение1, значение2,... — от 1 до 30 значений, среди которых требуется найти наименьшее.

Аргументы, содержащие значение ИСТИНА интерпретируются как 1, аргументы, содержащие текст или значение ЛОЖЬ, интерпретируются как 0 (нуль).

МЕДИАНА

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

МЕДИАНА (число1;число2;...)

Число1, число2,... — от 1 до 30 чисел, для которых определяется медиана.

МОДА

Возвращает наиболее часто встречающееся или повторяющееся значение в массиве или интервале данных. Как и функция МЕДИАНА, функция МОДА является мерой взаимного расположения значений.

МОДА (число1;число2;...)

Число1, число2,... — от 1 до 30 аргументов, для которых вычисляется мода. Можно использовать один массив или одну ссылку на массив вместо аргументов, разделяемых точкой с запятой.

НАИБОЛЬШИЙ

Возвращает k-ое по величине значение из множества данных. Эта функция позволяет выбрать значение по его относительному местоположению. Например, функцию НАИБОЛЬШИЙ можно использовать для определения наилучшего, второго или третьего результатов тестирования в баллах.

НАИБОЛЬШИЙ (массив; k)

Массив — массив или интервал данных, для которых определяется k-ое наибольшее значение.

k — позиция (начиная с наибольшей) в массиве или интервале ячеек данных.

НАИМЕНЬШИЙ

Возвращает k-ое наименьшее значение в множестве данных. Эта функция используется для определения значения, занимающего определенное относительное положение в множестве данных.

Синтаксис

НАИМЕНЬШИЙ (массив;k)

Массив — массив или диапазон числовых данных, для которого определяется k-ое наименьшее значение.

k — позиция (начиная с наименьшей) в массиве или интервале ячеек данных.

СРОТКЛ

Возвращает среднее абсолютных значений отклонений точек данных от среднего. СРОТКЛ является мерой разброса множества данных.

СРОТКЛ (число1; число2;...)

Число1, число2,... — это от 1 до 30 аргументов, для которых определяется среднее абсолютных отклонений. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой.

СЧЁТ

Подсчитывает количество чисел в списке аргументов. Функция СЧЁТ используется для получения количества числовых ячеек в интервалах или массивах ячеек.

СЧЁТ (значение1; значение2;...)

Значение1, значение2,... — это от 1 до 30 аргументов, которые могут содержать или ссылаться на данные различных типов, но в подсчете участвуют только числа.

СЧЁТЕСЛИ

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

СЧЁТЕСЛИ (диапазон; критерий)

Диапазон — диапазон, в котором нужно подсчитать ячейки.

Критерий — критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки".

СЧЁТЗ

Подсчитывает количество непустых значений в списке аргументов. Функция СЧЁТЗ используется для подсчета количества ячеек с данными в интервале или массиве.

СЧЁТЗ (значение1; значение2;...)

Значение1, значение2,... — это от 1 до 30 аргументов, количество которых требуется сосчитать. В данном случае значением считается значение любого типа, включая пустую строку (""), но не включая пустые ячейки. Если аргументом является массив или ссылка, то пустые ячейки в массиве или ссылке игнорируются.

СЧИТАТЬПУСТОТЫ

Подсчитывает количество пустых ячеек в заданном диапазоне.

СЧИТАТЬПУСТОТЫ (диапазон)

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

Ячейки с формулами, которые возвращают значение "" (пустой текст), учитываются при подсчете. Ячейки с нулевыми значениями не учитываются.

СРЗНАЧ

Возвращает среднее (арифметическое) своих аргументов.

СРЗНАЧ(число1; число2;...)

Число1, число2,... — это от 1 до 30 аргументов, для которых вычисляется среднее.

СРЗНАЧА

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

Помимо чисел в расчете могут участвовать текст и логические значения, такие как ИСТИНА и ЛОЖЬ.

СРЗНАЧА(значение1; значение2;...)

Значение1, значение2,... — это от 1 до 30 ячеек, интервалов ячеек или значений, для которых вычисляется среднее.

Массивы и ссылки, содержащие текст, интерпретируются как 0 (ноль). Пустой текст ("") интерпретируется как 0 (ноль). Если при расчете не требуется учитывать текстовые значения, следует использовать функцию СРЗНАЧ.

Аргументы, содержащие значение ИСТИНА, интерпретируются как 1. Аргументы, содержащие значение ЛОЖЬ, интерпретируются как 0 (ноль).

 

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

1. Запустить табличный процессор Microsoft Excel.

2. На первом листе с именем «Оценки» создать таблицу вида:

Оценки учащихся 3 "А" класса

Предмет

Фамилия

Ученик 1

Ученик 2

Ученик 3

Ученик 4

Ученик 5

Ученик 6

Ученик 7

Ученик 8

…

Ученик 25

Ученик 26

Ученик 27

Математика

1 четверть

2 четверть

3 четверть

4 четверть

Год

Русский язык

1 четверть

2 четверть

3 четверть

4 четверть

Год

Информатика

1 четверть

2 четверть

3 четверть

4 четверть

Год

3. Заполнить таблицу любыми 27 фамилиями учащихся и их оценками за 1, 2, 3, 4 четверть по 5 предметам.

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

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

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

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

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

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

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

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

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

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

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

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

16. Посчитать среднее отклонение от среднего по каждому предмету за каждую четверть среди всех учащихся.

17. Посчитать максимальную годовую оценку каждого учащегося.

18. Посчитать минимальную годовую оценку каждого учащегося.

19. Посчитать наибольшую третью годовую оценку каждого учащегося.

20. Посчитать наименьшую третью годовую оценку каждого учащегося.

21. Определить число, которое является серединой среди всех годовых оценок для каждого учащегося.

22. Определить наиболее часто повторяющуюся годовую оценку у каждого учащегося.

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

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

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

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

27. Посчитать количество учащихся, получивших оценку «3» за каждый предмет.

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

29. Посчитать количество учащихся, получивших оценку «5» за каждый предмет.

30. Проверить, у скольких учащихся не стоит годовая оценка или «н/а» по каждому предмету.

31. На втором листе с именем «Сочетания» вычислить, сколько всего существует групп учащихся по 3 человека из 27 учащихся для выполнения творческих работ.

32. На третьем листе с именем «Размещения» вычислить, сколькими способами 6 учащихся, сдающих ЕГЭ по иностранному языку, могут занять места в классе, в котором стоит 20 одноместных столов. Для вычисления использовать математическую формулу:

, где . В нашем случае, n =20, k =6.

33. Сохранить документ Microsoft Excel под именем Лабораторная работа №9_Фамилия.xls, указав в имени файла свою фамилию.

Лабораторная работа №10
«Использование логических функций для решения задач»

Цели работы:

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

Теоретическая часть

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

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

Пусть для поступления в институт абитуриенту необходимо набрать в сумме 130 баллов. Тогда в ячейке E2 должна размещаться формула, которая бы проверяла сумму баллов и в случае истинности условия (сумма баллов достигнута 130) возвращала бы значение «Зачислен» или «Не зачислен» в случае ложности условия. Такой функций является логическая функция ЕСЛИ.

Формат записи данной функции:

ЕСЛИ(логическое выражение; значение_если_истина; значение_если_ложь)

Логическое выражение — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае — ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения.

Значение_если_истина — это значение, которое возвращается, если логическое выражение равно ИСТИНА. Значение_если_истина может быть формулой.

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

Таким образом, в нашем примере в ячейке E2 должна быть указана формула:

=ЕСЛИ((B2+C2+D2)>=130;”Зачислен”;”Не зачислен”)

Изменим условие нашей задачи. Пусть для поступления в институт нужно не только набрать в сумме 130 баллов, но еще пройти собеседование. Таблица будет такой:

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

Тогда условие поступления в институт будет зависеть от двух критериев:

1) сумма баллов достигнута 130;

2) собеседование равно «Да».

Для того чтобы в функции ЕСЛИ связать два или несколько условий в одно необходимо логические функции ИЛИ, И, НЕ.

Формат записи функции ИЛИ:

ИЛИ(логическое_значение1;логическое_значение2;...)

Логическое_значение1, логическое_значение2,... — от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

Даная функция возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Формат записи функции И:

И(логическое_значение1;логическое_значение2;...)

Логическое_значение1, логическое_значение2,... — от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

Даная функция возвращает ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает ЛОЖЬ, если хотя бы один из аргументов имеет значение ЛОЖЬ.

Формат записи функции НЕ:

НЕ (логическое_значение)

Логическое_значение — величина или выражение, которые могут принимать два значения: ИСТИНА или ЛОЖЬ.

Если логическое_значение имеет значение ЛОЖЬ, то функция НЕ возвращает значение ИСТИНА; если логическое_значение имеет значение ИСТИНА, то функция НЕ возвращает значение ЛОЖЬ.

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

ЕСЛИ(И((B2+C2+D2)>=130;E2=”Да”);”Зачислен”;”Не зачислен”)

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

1. Запустить табличный процессор Microsoft Excel.

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

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

4. В соответствующие ячейки ввести вопросы.

5. В столбце «Результат ответа» напротив значения ответа ввести формулу, которая бы возвращала значение пустой строки, если не был введен ответ, значение «правильно», если был введен правильный ответ и значение «не правильно», если был введен неправильный ответ.

6. В ячейке «Сумма баллов» ввести формулу, которая считает количество ячеек столбца «Результат ответа», значение которых равно «правильно» (соответствующую функцию найти в лабораторной работе №9).

7. В ячейке «Ваша оценка» ввести формулу, которая бы выставляла оценку за тест согласно следующим условиям (критериям оценки):

Сумма баллов	Оценка
менее 7
7-10
11-13
14-15

8. Сохранить документ Microsoft Excel под именем Лабораторная работа №10_Фамилия.xls, указав в имени файла свою фамилию.

Лабораторная работа №11
«Создание и заполнение базы данных с помощью СУБД Access»

Цели работы:

- отработка навыков и умений создания структуры таблицы базы данных;

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

Теоретическая часть

База данных – это организованная структура, предназначенная для хранения информации.

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

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

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

Запись – строка таблицы, определяющая набор конкретных значений полей базы данных.

Ключевое поле – поле базы данных, которое однозначно идентифицирует запись. Например, номер паспорта, гос. номер машины и пр.

Для работы с базами данных необходимы специальные прикладные программы, называемые системами управления базами данных (СУБД).

Одной из наиболее распространенных СУБД является Microsoft Access.

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