Электронные таблицы
Электронные таблицы
Что такое электронная таблица?
Из чего состоит таблица?
Данные в таблице
Строка редактирования
Формулы
Формулы
Пример решения задачи
Диапазоны
Ещё пример
Электронные таблицы
Редактирование и форматирование
Выделение ячеек
Что можно делать с ячейками?
Форматирование
Электронные таблицы
Сумма
Сумма
Сумма
Минимум, максимум, среднее
Сумма произведений
Электронные таблицы
Простая сортировка
Сортировка по любому столбцу
Многоуровневая сортировка
Электронные таблицы
Что происходит при копировании?
Заполнение больших таблиц
Абсолютные ссылки
Смешанные ссылки
Смешанные ссылки
Смешанные ссылки
Электронные таблицы
Что такое диаграмма?
Столбчатая диаграмма
Столбчатая диаграмма
Круговая диаграмма
График
Графики функций
Электронные таблицы
Что такое условные вычисления?
Символьные данные
Пример
Вложенные вызовы ЕСЛИ
Сложные условия – «И»
Сложные условия – «ИЛИ»
Больше двух условий
Электронные таблицы
Выделение больших диапазонов
Выделение больших диапазонов
Вспомогательные столбцы
Вспомогательные столбцы
Функции COUNT и COUNTIF
Функции COUNTIF и COUNTIFS
Пример
Функция SUMIF
Функция AVERAGEIF
Работа с листами
Ответьте на вопросы:
Электронные таблицы
Методы решения уравнений
Численные методы
Пример
Как работают численные методы?
Как работают численные методы?
Численные методы: «за» и «против»
Пример решения уравнения
Пример решения уравнения
Пример решения уравнения
Пример решения уравнения
Электронные таблицы
Что такое оптимизация?
Что такое минимум?
Оптимальный раскрой листа
Оптимальный раскрой листа
Оптимизация в табличном процессоре
Оптимизация в табличном процессоре
Оптимизация в табличном процессоре
4.86M
Категория: ИнформатикаИнформатика

Электронные таблицы (§26-35)

1. Электронные таблицы

§ 26. Введение
§ 27. Редактирование и форматирование
таблицы
§ 28. Стандартные функции
§ 29. Сортировка данных
§ 30. Относительные и абсолютные ссылки
§ 31. Диаграммы
§ 32. Условные вычисления
§ 33. Обработка больших массивов данных
§ 34. Численные методы
§ 35. Оптимизация
1

2. Электронные таблицы

2
Электронные
таблицы
§ 26. Введение

3. Что такое электронная таблица?

3
Что такое электронная таблица?
Электронная таблица (табличный процессор) — это
программа, которая хранит данные в виде таблиц и
автоматически пересчитывает результаты по
введённым формулам при изменении этих данных.
Microsoft Excel
Google Таблицы
(онлайн-офис)
OpenOffice Calc
бесплатно!

4. Из чего состоит таблица?

4
Из чего состоит таблица?
столбцы
A
строки
B
C
1
2
3
адрес ячейки – B2
активная
ячейка

5. Данные в таблице

5
Данные в таблице
1
2
научный 3
формат
4
1,2345 102 5
6
7
A
Типы данных
123,45
1,2345Е+02
123,45 p.
12.09.2017
12:18:58
B
Текст
Число
Денежная сумма
Дата
Время
F2 – редактирование активной
ячейки без удаления данных
C

6. Строка редактирования

6
Строка редактирования
строка
редактирования
адрес активной
ячейки
fx
B1
A
1
2
1968
B
C
1968

7. Формулы

7
Формулы
! Любая формула начинается знаком «=»!
A
1
2
3
4
15
12
=A1+A2
B
A
1
2
3
4
B
15
12
27
ссылки
Ссылка — это адрес ячейки в записи формулы.

8. Формулы

8
Формулы
Знаки математических действий:
* – умножение
/ – деление
A
1
2
3
4
B
2 =A1*A2
10 =A1/A2
=A2^3
=A1^A2
A1 A2
C1
B1 B2
^ – степень
A
1
2
3
4
C1:
=A1+A2/B1+B2
B
2
10
20
0,2
1000
1024
? Что плохо?
=(A1+A2)/(B1+B2)
! Внимание! Скобки!

9. Пример решения задачи

9
Пример решения задачи
Задача. Автомобиль проехал 120 км за 2 часа. Найти
среднюю скорость автомобиля.
A
A
1
120
1
Расстояние
2
2
2
Время
3
Скорость
3
=А1/А2
? Что плохо?
B
120
2
=B1/B2

10. Диапазоны

10
Диапазоны
Диапазон — прямоугольная часть таблицы.
A
1
120
2
2
3 =A1/A2
4
выделить
диапазон
мышкой!
B
C
A
1
2
3
4
перетащить
за рамку
B
120
2
=B1/B2
C
ссылка сама
поменялась!

11. Ещё пример

11
Ещё пример
Задача. Автомобиль сначала проехал 200 км за 2 часа, а
потом ещё 150 км за 3 часа. Найти среднюю скорость
автомобиля.
A
1 Расстояние
2 Время
3 Скорость
B
120
2
C
170
3
=(B1+C1)/(B2+C2)
? Какую формулу записать в C3?

12. Электронные таблицы

12
Электронные
таблицы
§ 27. Редактирование и
форматирование таблицы

13. Редактирование и форматирование

13
Редактирование и форматирование
Редактирование — изменение данных и структуры
таблицы.
Форматирование — изменение внешнего вида ячеек.

14. Выделение ячеек

14
Выделение ячеек
Ячейка: щелчок ЛКМ
Диапазон:
A
+ЛКМ
B
C
D
1
2
3
4
– ЛКМ
вся
Столбцы:
таблица
Строки:
A
1
2
3
4
5
B
C
A
1
2
3
4
5
B
C
D

15. Что можно делать с ячейками?

15
Что можно делать с ячейками?
Ctrl+C: Копировать
Ctrl+X: Вырезать
Ctrl+V: Вставить
Delete: Очистить
ПКМ – Удалить ячейки
ПКМ – Вставить ячейки
Перенос со вставкой:
1
2
3
4
B
C
Цена 1 шт. Количество
Утка
2 000р.
10
Гусь
1 200р.
12
Телёнок
9 000р.
2
A
A
1
2
3
4
Утка
Гусь
Телёнок
перетащить за
рамку + Shift
B
C
Количество Цена 1 шт
10
2 000р.
12
1 200р.
2
9 000р.

16. Форматирование

16
Форматирование
OpenOffice Calc: панель Свойства
Microsoft Excel: панель Главная
фон
рамка
денежный
объединение ячеек
A
1
2
3
B
C
D
Весна
E
знаки в
дробной
части
проценты
F
Лето
март
апрель
май
июнь
июль
август
31
30
31
30
31
31

17. Электронные таблицы

17
Электронные
таблицы
§ 28. Стандартные функции

18. Сумма

18
Сумма
= A1 + A2 + A3 + A4 + A5
? А если 1000 ячеек?
=SUM(A1:A5)
A
1
2
3
4
5
6
B
=СУММ(A1:A5)
C
D
=SUM(B2:C5)
=СУММ(B2:C5)

19. Сумма

19
Сумма
A
1
2
3
4
5
B
C
D
Бригада 1
Бригада 1
Иванов
25 000р. Сидоров
30 000р.
Петров
17 000р. Макеев
35 000р.
Пименов
Всего
15 000р.
122 000 р.
? Как найти сумму расходов?
=SUM(B2:B3)+SUM(D2:D4)
=SUM(B2:B3;D2:D4)
=СУММ(B2:B3;D2:D4)
диапазоны
можно
выделять
мышью

20. Сумма

20
Сумма
Пример:
A
B
1
7
5
2 =SUM(A1:B1) =SUM(A1:A2)
3
=SUM(A1:B2)
? Что получится?
1
2
3
A
B
7
12
5
19
43

21. Минимум, максимум, среднее

21
Минимум, максимум, среднее
MIN (МИН) – минимальное значение
MAX (МАКС) – максимальное значение
AVERAGE (СРЗНАЧ) – среднее значение
1
2
3
A
1
ФЫВА
=MIN(A1:B2)
1
B
3
=MAX(A1:B2) =AVERAGE(A1:B2)
3
2
! Пустые и нечисловые ячейки не учитываются!

22. Сумма произведений

22
Сумма произведений
1
2
3
4
5
6
A
Товар
Молоко
Сметана
Пряник
B
C
Цена
Количество
50 р.
3
25 р.
1
15 р.
4
Сумма
235 р. =B2*С2+B3*С3+B4*C4
? Как найти сумму расходов?
=SUMPRODUCT(B2:B4;C2:C4)
=СУММПРОИЗВ(B2:B4;C2:C4)
? А если 1000?

23. Электронные таблицы

23
Электронные
таблицы
§ 29. Сортировка данных

24. Простая сортировка

24
Простая сортировка
OpenOffice Calc:
Microsoft Excel:
! Сортировка по первому столбцу выделенного
диапазона!
? Почему нельзя сортировать только один столбец?
1
2
3
4
5
A
B
Сотрудник
Иванов
Петров
Акимов
Дубов
Зарплата
12 000 р.
15 000 р.
17 000 р.
11 000 р.

25. Сортировка по любому столбцу

25
Сортировка по любому столбцу
1
2
3
4
5
A
B
Сотрудник
Акимов
Петров
Иванов
Дубов
Зарплата
17 000 р.
15 000 р.
12 000 р.
11 000 р.
? Подходит ли простая
сортировка?
OpenOffice Calc: Данные – Сортировать
Microsoft Excel: Данные – Сортировка

26. Многоуровневая сортировка

26
Многоуровневая сортировка
1
2
3
4
5
6
7
A
B
Группа
Альбом
Город 312 Новая музыка
Город 312 Обернись
Город 312 Вне зоны доступа
Любэ
Свои
Любэ
Давай за…
– по группе
ЛюбэСначала
Комбат
(по алфавиту)
С
Год
2010
2007
2006
2009
2002
1996
Для одной
группы – по
убыванию года

27. Электронные таблицы

27
Электронные
таблицы
§ 30. Относительные и
абсолютные ссылки

28. Что происходит при копировании?

28
Что происходит при копировании?
1
2
А
1
2
В
3
4
С
D
=A1+B1 =B1+C1
=A2+B2 =B2+C2
Скопируем формулу из C1 в другие ячейки
Адрес ячейки в относительной ссылке при
копировании изменяется так же, как изменяется адрес
ячейки, в которой записана формула.
D8: =C13+F4
при копировании в
D12: =C17+F8
D5:
=C10+F1
F8:
=E13+H4
B8:
=A13+D4
E10: =D15+G6
C6:
=B11+E2

29. Заполнение больших таблиц

29
Заполнение больших таблиц
1
2
3
4
A
B
Месяц
январь
февраль
март
Доходы
530 000 р.
532 200 р.
635 000 р.
С
D
Расходы
Прибыль
120 000 р. =B2-C2
125 800 р.
маркер
224 000 р.
заполнения
Скопировать формулу на весь столбец:
• протащить вниз маркер заполнения
• или 2×ЛКМ по нему.

30. Абсолютные ссылки

30
Абсолютные ссылки
1
2
3
4
5
6
А
Размер налога
В
13%
С
Сотрудник
Иванов И.И.
Петров П.П.
Сидоров С.С.
Зарплата
23 000 р.
18 000 р.
32 000 р.
К выдаче
=B4*(1-B1)
? Что плохо?
=B4*(1-$B$1)
! Знак $ защищает от изменений при копировании
номер строки или имя столбца справа от него!
Абсолютные ссылки при копировании не изменяются.
$B$1 – обе части адреса защищены от изменений!

31. Смешанные ссылки

31
Смешанные ссылки
Таблица умножения:
А
числа
1
2
3
4
5
6
1
2
3
4
5
В
1
1
2
3
4
5
С
D E
2
3
4
2
3
4
4
6
8
6
9 12
8 12 16
10 15 20
F
5
5
10
15
20
25
числа
формулы
? Как ввести одну формулу и скопировать её
во все ячейки?

32. Смешанные ссылки

32
Смешанные ссылки
1
2
3
4
5
6
А
В
1
С
2
1
2
3
4
5
=A2*B1
D
3
=$A2*B$1
E
4
F
5
нужно защитить
от изменений
=A6*F1
В смешанной ссылке одна часть (номер строки или имя
столбца) защищена от изменений, а другая – нет.
• ввести =$A2*B$1 в ячейку B2
• растянуть формулу на диапазон B2:F2
• растянуть диапазон B2:F2 на весь диапазон B2:F6

33. Смешанные ссылки

33
Смешанные ссылки
D8: =C$13+$F4 при копировании в
D12: =C$13+$F8 D5:
=C$13+$F1
F8:
=E$13+$F4 B8:
=A$13+$F4
E10: =D$13+$F6 C6:
=B$16+$F2
Быстрое изменение ссылок:
OpenOffice Calc: Shift+F4
B1 $B$1 B$1 $B1
Microsoft Excel: F4

34. Электронные таблицы

34
Электронные
таблицы
§ 31. Диаграммы

35. Что такое диаграмма?

35
Что такое диаграмма?
Диаграмма – это графическое изображение данных.
Основные типы диаграмм:
столбчатая (гистограмма)
линейчатая
график
круговая

36. Столбчатая диаграмма

36
Столбчатая диаграмма
Высота, м
А
1
2
3
4
5
Гора
Эверест
Чогори (К2)
Пик Сомони
Эльбрус
В
Высота, м
8848
8614
7495
5642
10000
8000
6000
4000
2000
0
Эверест
Чогори
(К2)
1) выделить данные (с заголовками)
2) OpenOffice Calc: Вставка – Диаграмма
Microsoft Excel: Вставка – Гистограмма
Пик
Сомони
Эльбрус

37. Столбчатая диаграмма

37
Столбчатая диаграмма
А
1
2 Аськин
3 Баськин
4 Сенькин
В
овцы
1
4
2
C
кролики
2
2
3
D
куры
5
5
4
легенда
5
овцы
кролики
куры
4
3
2
? Какие данные
1
0
ряды
Аськин
Баськин
категории
Сенькин
проще
считывать?

38. Круговая диаграмма

38
Круговая диаграмма
показывает доли частей в целом
1
2
3
4
5
6
А
В
Питание
Квартплата
Одежда
Проезд
Другое
Сумма
12 800р.
4 200р.
8 800р.
5 100р.
3 600р.
Семейный бюджет
Другое
10%
Проезд
15%
Питание
37%
Одежда
26%
Квартплата
12%
подписи
данных

39. График

39
График
показывает изменение во времени (много данных)
А
1 Месяц
2 Заказов
В
1
13
C
2
15
D
3
11
E
4
10
F
5
15
G
6
17
H
7
25
I
8
20
J
9
23
K
10
21
L
11
27
M
12
35
Рост числа заказов за первый год работы сайта
название
диаграммы
Число заказов
название оси
40
30
20
маркер
10
1
2
3
4
название оси
5
6
7
Месяц
8
9
10 11 12

40. Графики функций

40
Графики функций
y x 3 на отрезке [0;2]
a)
1
2
3
4
5
6
7
A
X
0
0,2
B
Y
б)
1
2
3
4
5
6
7
A
X
в)
B
Y
1
2
3
4
5
6
7
0
0,2
0,4
0,6
0,8
1,0
A
X
B
Y
0 =A2^3
0,2
0,4
0,6
0,8
1,0
г)
1
2
3
4
5
6
7
y=x3
OpenOffice Calc:
Диаграмма XY
Microsoft Excel:
Точечная диаграмма
8
7
6
5
4
3
2
1
0
0
0,5
1
1,5
2
A
X
B
Y
0
0,2
0,4
0,6
0,8
1,0
0
0,008
0,064
0,216
0,512
1

41. Электронные таблицы

41
Электронные
таблицы
§ 32. Условные вычисления

42. Что такое условные вычисления?

42
Что такое условные вычисления?
Доставка = 20% от стоимости заказа, если > 500р. –
бесплатно.
1
2
3
4
5
A
Заказ
1234
1345
1456
1565
B
C
Сумма Доставка
256 р.
51 р.
128 р.
26 р.
1024 р.
0 р.
512 р.
0 р.
условие
если «да»
if B2>500 then
C2:=0
else
C2:=B2*0.2;
если «нет»
=IF(B2>500; 0; B2*0,2)
=ЕСЛИ(B2>500; 0; B2*0,2)

43. Символьные данные

43
Символьные данные
Скидка 20% на все товары фирмы «Салют».
1
2
3
4
5
6
A
Код товара
1234
1345
1456
1565
1576
B
Фирма
Салют
Звезда
Гамбит
Салют
Гамбит
C
Цена
3999 р.
2799 р.
6290 р.
3750 р.
1234 р.
D
Скидка
800 р.
750 р.
=IF(B2="Салют"; B2*20%; "")
=ЕСЛИ(B2="Салют"; B2*20%; "")
B2*0,2

44. Пример

44
Пример
Работник получает премию, составляющую 10% от его
зарплаты, только тогда, когда на него не было жалоб.
A
1 Фамилия
2 Иванов
3 Петров
B
С
Зарплата Жалобы
12 000 р.
0
14 000 р.
2
=IF(C2=0; B2*10%; 0)
=ЕСЛИ(C2=0; B2*10%; 0)
D
Премия
1 200 р.
0 р.
?

45. Вложенные вызовы ЕСЛИ

45
Вложенные вызовы ЕСЛИ
Доставка = если > 500р. – бесплатно; если более 200р.
(но <= 500р.), то 10% от стоимости заказа, если <= 200р.,
то 20% от стоимости заказа.
1
2
3
4
A
Заказ
1234
1345
1456
B
C
Сумма Доставка
256 р.
26 р.
128 р.
26 р.
1024 р.
0 р.
? Сколько вариантов?
if B2>500 then
C2:=0
else
if B2>200 then
C2:=B2*0.1
else C2:=B2*0.2;
=IF(B2>500; 0; IF(B2>200;B2*10%;B2*20%))
)
=ЕСЛИ(B2>500; 0; ЕСЛИ(B2>200;B2*10%;B2*20%))
)

46. Сложные условия – «И»

46
Сложные условия – «И»
Доставка бесплатна, если номер заказа < 1500 и сумма >
500р., иначе 20% от стоимости заказа.
1
2
3
4
A
Заказ
1234
2345
1456
B
C
Сумма Доставка
256 р.
52 р.
1580 р.
316 р.
1024 р.
0 р.
условие 1
if (A2<1500) and
(B2>500) then
C2:=0
else
C2:=B2*0.2;
условие 2
=IF(AND(A2<1500;B2>500); 0; B2*20%)
=ЕСЛИ(И(A2<1500;B2>500); 0; B2*20%)

47. Сложные условия – «ИЛИ»

47
Сложные условия – «ИЛИ»
Разрешается ехать со скоростью от 40 км/ч до 110 км/ч.
При другой скорости – штраф 500р.
1
2
3
4
5
6
A
Номер
A134AA
B235BB
A157AB
A198CX
K754MM
условие 1
B
Скорость
150
80
90
30
180
C
Штраф
500 р.
500 р.
500 р.
условие 2
=IF( OR(B2<40;B2>110); 500; "")
=ЕСЛИ( ИЛИ(B2<40;B2>110); 500; "")

48. Больше двух условий

48
Больше двух условий
Проход на III тур: набрать по сумме двух первых туров не
менее 180 баллов или получить 100 баллов хотя бы в
одном туре.
1
2
3
4
5
6
A
Участник
Иванов И.И.
Петров П.П.
Сидоров С.С.
Куницын К.К.
Васильев В.В.
B
I тур
100
80
65
95
80
C
D
II тур III тур
70
+
75
100
+
90
+
90
?
=IF(OR(B2+C2>=180;B2=100;C2=100); "+"; "")
=ЕСЛИ(OR(B2+C2>=180;B2=100;C2=100); "+"; "")

49. Электронные таблицы

49
Электронные
таблицы
§ 33. Обработка больших
массивов данных

50. Выделение больших диапазонов

50
Выделение больших диапазонов
Протаскивание мыши
+ЛКМ
A
B
C
D
1
2
3
4
– ЛКМ
Два щелчка в противоположных углах:
A
1
2
3
4
ЛКМ
B
C
D
Shift+ЛКМ

51. Выделение больших диапазонов

51
Выделение больших диапазонов
До первой пустой ячейки в нужном направлении:
A
1
2
3
4
5
B
C
1
11
111
2
22
222
D
Ctrl+ Shift+↓
В поле Имя над таблицей:
ввести адрес
диапазона
A5:B1000
A
1
2
B
C
D

52. Вспомогательные столбцы

52
Вспомогательные столбцы
Не используя сортировку, найти количество спортсменов
2004 года рождения.
1
2
3
4
5
A
Участник
Иванов И.И.
Петров П.П.
Сидоров С.С.
...
B
Год рождения
2004
2003
2004
...
C
Вес, кг
56
62
58
...
D
1
0
1
?
=IF(B2=2004; 1; 0)
=ЕСЛИ(B2=2004; 1; 0)
? Ответ?
=SUM(D2:D1001)
=СУММА(D2:D1001)

53. Вспомогательные столбцы

53
Вспомогательные столбцы
Не используя сортировку, найти средний вес
спортсменов 2004 года рождения.
1
2
3
4
A
Участник
Иванов И.И.
Петров П.П.
Сидоров С.С.
B
Год рождения
2004
2003
2004
=IF(B2=2004; С2; "")
=ЕСЛИ(B2=2004; С2; "")
? Ответ?
C
Вес, кг
56
62
58
D
56
?
58
? Почему не 0?
=AVERAGE(D2:D1001)
=СРЗНАЧ(D2:D1001)

54. Функции COUNT и COUNTIF

54
Функции COUNT и COUNTIF
Количество числовых ячеек в диапазоне:
=COUNT(D2:D1001)
=СЧЁТ(D2:D1001)
Количество ячеек с заданным значением:
=COUNTIF(D2:D1001; 2004)
=СЧЁТЕСЛИ(D2:D1001; 2004)
или так
=COUNTIF(D2:D1001; "=2004")
=СЧЁТЕСЛИ(D2:D1001; "=2004")
текстовое значение:
=COUNTIF(D2:D1001; "яблоко")
=СЧЁТЕСЛИ(D2:D1001; "яблоко")

55. Функции COUNTIF и COUNTIFS

55
Функции COUNTIF и COUNTIFS
Количество ячеек, удовлетворяющих условию:
=COUNTIF(D2:D1001; ">2004")
=СЧЁТЕСЛИ(D2:D1001; ">2004")
! Нельзя использовать сложное условие!
=COUNTIFS(D2:D101; ">2004";
E2:E101; "<59")
=СЧЁТЕСЛИМН(D2:D101; ">2004";
E2:E101; "<59")

56. Пример

56
Пример
Определить, какую долю (в процентах) от общего
количества составляют спортсмены 2004 года рождения:
=COUNTIF(D2:D1001;2004)/COUNT(D2:D1001)
=СЧЁТЕСЛИ(D2:D1001;2004)/СЧЁТ(D2:D1001)
! И установить процентный формат!
настройка количества знаков

57. Функция SUMIF

57
Функция SUMIF
Найти общий вес спортсменов 2004 года рождения:
A
Участник
Иванов И.И.
Петров П.П.
Сидоров С.С.
B
Год рождения
2004
2003
2004
диапазон проверки
условия
условие
1
2
3
4
C
Вес, кг
56
62
58
диапазон
суммирования
=SUMIF(B2:B1001;2004;C2:C1001)
=СУМЕСЛИ(B2:B1001;2004;C2:C1001)

58. Функция AVERAGEIF

58
Функция AVERAGEIF
Найти средний вес спортсменов 2004 года рождения:
1
2
3
4
A
Участник
Иванов И.И.
Петров П.П.
Сидоров С.С.
B
Год рождения
2004
2003
2004
диапазон проверки
условия
условие
C
Вес, кг
56
62
58
диапазон для
вычисления
среднего
=AVERAGEIF(B2:B1001;2004;C2:C1001)
=СРЗНАЧЕСЛИ(B2:B1001;2004;C2:C1001)

59. Работа с листами

59
Работа с листами
ПКМ
Обращение к данным другого листа:
OpenOffice Calc:
=Январь.B2+Февраль.B2+Март.B2
=SUM('К оплате'.B2:С4)
Microsoft Excel:
=Январь!B2+Февраль!B2+Март!B2
=СУММ('К оплате'!B2:С4)

60. Ответьте на вопросы:

60
Ответьте на вопросы:
1. Какое логическое выражение, соответствует условию
“дети до 12 лет или старики от 70 лет”.
Варианты ответов:
1. ИЛИ(дети<=12; старики>=70)
2. ЕСЛИ (ИЛИ(дети<=12;старики>=70)
3. И(дети<=12; старики>=70)
2. Для заданного значения X нужно вычислить значение Y
по одной из формул: если x>5, то y:=x-8, иначе y:=x+3.
Как будет выглядеть формула:
Варианты ответов:
1. =ЕСЛИ(В1>5;B1-8;B1+3)
2. =ЕСЛИ(И(В1>5;B1-8;B1+3))
3. =ЕСЛИ(ИЛИ(В1>5;B1-8;B1+3))

61. Электронные таблицы

61
Электронные
таблицы
§ 34. Численные методы

62. Методы решения уравнений

62
Методы решения уравнений
Точные (аналитические) методы:
ax b 1,
a 0
1 b
x
a
! Можем понять зависимость x от a и b!
a x b cos x
? Как решать?
Численное решение – это решение задачи для
конкретных исходных данных.
a 1, b 1, x cos x
Численный метод – это метод, который применяется
для поиска численного решения.

63. Численные методы

63
Численные методы
методы часто дают неточное
! Численные
(приближенное решение)!
Приближённый метод – это метод, который позволяет
найти решение задачи с некоторой (допустимой)
ошибкой (погрешностью).
Погрешность — отклонение значения величины,
полученного в результате измерений или вычислений,
от её истинного (действительного) значения.
x = 0,517
x 0,517

64. Пример

64
Пример
x cos x
? Как решать?
Графический метод:
! Можно поручить такой поиск компьютеру!
? Можно ли получить точное решение?

65. Как работают численные методы?

65
Как работают численные методы?
Сжатие отрезка:
1) выбрать начальный отрезок [a0, b0] (одно решение!)
2) уточнить решение с помощью некоторого
алгоритма: [a, b]
3) повторять шаг 2, пока длина отрезка [a, b] не станет
достаточно мала
a
x
a b
2
лучше выбрать в
? Что
качестве решения?
b
? Как оценить ошибку?
Завершение работы:
b a
x x
2
b a 2
*
допустимая
ошибка

66. Как работают численные методы?

66
Как работают численные методы?
По одной точке:
1) выбрать начальное приближение x0
Начальное приближение – это начальное значение
неизвестной величины, которое уточняется с помощью
приближённого метода.
2) уточнить решение с помощью некоторого алгоритма:
x1 x2 x3 …
3) повторять шаг 2, пока два последовательных
приближения не будут отличаться достаточно мало
Завершение работы:
xi xi 1

67. Численные методы: «за» и «против»

67
Численные методы: «за» и «против»
дают практическое решение задачи
неточное решение x 0,517
неясно, как зависит от исходных данных
(параметров)
объём вычислений может быть велик
не всегда легко оценить ошибку

68. Пример решения уравнения

,
.
68
Пример решения уравнения
2
x
cos x
Найти все решения на интервале [-2; 2]:
f1 ( x) x
f 2 ( x) cos x
2
? Сколько решений?
строим графики
Заполнение таблицы
1
2
3
4
5
6
7
8
A
x
–2
–1,75
B
f1(x)
C
f2(x)
1
2
3
4
5
6
7
8
A
x
B
f1(x)
–2 =A2^2
–1,75
–1,5
–1,25
–1
–0,75
–0,5
C
f2(x)
=COS(A2)

69. Пример решения уравнения

69
Пример решения уравнения
Построение графиков
OpenOffice Calc:
Диаграмма X-Y
Microsoft Excel:
Точечная
y
4
3
• всего 2 решения
• начальные приближения:
решение
x1 = –1
x2 = 1
y = x2
2
решение
1
y = cos x
-2
-1
0
1
начальные
приближения
2
x

70. Пример решения уравнения

70
Пример решения уравнения
Подготовка данных
начальное
приближение
1
2
3
E
x
F
f1(x)
G
f2(x)
–1
=E2^2
=COS(E2)
H
f1(x) – f2(x)
=F2–G2
? Зачем нужна разность функций?
уравнения f (x) = f (x), то
! Если x – решение
f (x) – f (x) = 0!
1
1
2
2

71. Пример решения уравнения

71
Пример решения уравнения
Подбор параметра
изменяемая
ячейка 1
2
3
E
x
F
f1(x)
G
f2(x)
–1
=E2^2
=COS(E2)
H
f1(x) – f2(x)
целевая
ячейка
=F2 – G2
?
OpenOffice Calc:
Как найти второе
Сервис – Подбор параметра
решение?
Microsoft Excel:
Данные – Анализ «что-если» – Подбор параметра

72. Электронные таблицы

72
Электронные
таблицы
§ 35. Оптимизация

73. Что такое оптимизация?

73
Что такое оптимизация?
Оптимизация – это поиск наилучшего (оптимального)
решения задачи в заданных условиях.
1) Цель: выбрать неизвестный x, так чтобы
f ( x) min
или
целевая функция
f ( x) max
f ( x) min
2) Ограничения
задача
оптимизации
? Почему неправильно «самый оптимальный»?

74. Что такое минимум?

74
Что такое минимум?
f (x )
локальный
минимум
x
глобальный
минимум
• обычно нужно найти глобальный минимум
• большинство численных методов находят только
локальный минимум
! Результат локальной оптимизации зависит от
начального приближения!

75. Оптимальный раскрой листа

75
Оптимальный раскрой листа

z
x
x
z
Цель:
V ( x) max
V ( x) x (1 2 x) 2 max
? Какие ограничения?
Ограничения:
0 x 0,5
? Какой результат ожидаете (по интуиции)?

76. Оптимальный раскрой листа

76
Оптимальный раскрой листа
В табличном процессоре:
V
V ( x) x (1 2 x) max
2
0
0,1
0,2
0,3
0,4
x
? Какая формула в F2?
начальное
приближение 0,2
1
2
0,5
E
x
0,2
F
Объём
0,072

77. Оптимизация в табличном процессоре

77
Оптимизация в табличном процессоре
Задача оптимизации: найти максимум (или минимум)
целевой функции в ячейке …, изменяя значения ячеек
… при ограничениях ….
OpenOffice.org Calc:
Сервис – Поиск решения
Microsoft Excel:
надстройка Данные – Поиск решения

78. Оптимизация в табличном процессоре

78
Оптимизация в табличном процессоре
OpenOffice.org Calc:
целевая
ячейка
изменяемые
ячейки:
E2
D2:D6
D2:D6; C5:C8
ограничения
A1 <= 20
A1 = целое
B2:B8 >= 5
B2:B4>=C2:C4

79. Оптимизация в табличном процессоре

79
Оптимизация в табличном процессоре
Excel:
целевая
ячейка
изменяемые
ячейки:
E2
D2:D6
D2:D6; C5:C8
ограничения
A1 <= 20
A1 = целое
B2:B8 >= 5
B2:B4>=C2:C4
English     Русский Правила