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

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

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

§ 26. Введение
§ 27. Редактирование и форматирование
таблицы
§ 28. Стандартные функции
§ 29. Сортировка данных
§ 30. Относительные и абсолютные ссылки
§ 31. Диаграммы
§ 32. Условные вычисления
§ 33. Обработка больших массивов данных
§ 34. Численные методы
§ 35. Оптимизация
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru
1

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

2
Электронные
таблицы
§ 26. Введение
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
3
Что такое электронная таблица?
Электронная таблица (табличный процессор) — это
программа, которая хранит данные в виде таблиц и
автоматически пересчитывает результаты по
введённым формулам при изменении этих данных.
Microsoft Excel
Google Таблицы
(онлайн-офис)
К.Ю. Поляков, Е.А. Ерёмин, 2017
OpenOffice Calc
бесплатно!
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
4
Из чего состоит таблица?
столбцы
A
строки
B
C
1
2
3
адрес ячейки – B2
К.Ю. Поляков, Е.А. Ерёмин, 2017
активная
ячейка
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
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
C
Текст
Число
Денежная сумма
Дата
Время
F2 – редактирование активной
ячейки без удаления данных
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
6
Строка редактирования
строка
редактирования
адрес активной
ячейки
B1
A
1
2
К.Ю. Поляков, Е.А. Ерёмин, 2017
fx
1968
B
C
1968
http://kpolyakov.spb.ru

7. Формулы

Электронные таблицы, 9 класс
7
Формулы
!
Любая формула начинается знаком «=»!
A
1
2
3
4
15
12
=A1+A2
B
A
1
2
3
4
B
15
12
27
ссылки
Ссылка — это адрес ячейки в записи формулы.
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

8. Формулы

Электронные таблицы, 9 класс
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)
!
К.Ю. Поляков, Е.А. Ерёмин, 2017
Внимание! Скобки!
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
9
Пример решения задачи
Задача. Автомобиль проехал 120 км за 2 часа. Найти
среднюю скорость автомобиля.
A
A
1
120
1
Расстояние
2
2
2
Время
3
Скорость
3
=А1/А2
?
Что плохо?
К.Ю. Поляков, Е.А. Ерёмин, 2017
B
120
2
=B1/B2
http://kpolyakov.spb.ru

10. Диапазоны

Электронные таблицы, 9 класс
10
Диапазоны
Диапазон — прямоугольная часть таблицы.
A
1
120
2
2
3 =A1/A2
4
выделить
диапазон
мышкой!
B
C
A
1
2
3
4
перетащить
за рамку
К.Ю. Поляков, Е.А. Ерёмин, 2017
B
120
2
=B1/B2
C
ссылка сама
поменялась!
http://kpolyakov.spb.ru

11. Ещё пример

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

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

12
Электронные
таблицы
§ 27. Редактирование и
форматирование таблицы
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
13
Редактирование и форматирование
Редактирование — изменение данных и структуры
таблицы.
Форматирование — изменение внешнего вида ячеек.
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
14
Выделение ячеек
Ячейка: щелчок ЛКМ
Диапазон:
A
+ЛКМ
B
D
C
1
2
3
4
– ЛКМ
вся
Столбцы:
таблица
Строки:
A
B
1
2
3
4
5
К.Ю. Поляков, Е.А. Ерёмин, 2017
C
A
B
C
D
1
2
3
4
5
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
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
Утка
Гусь
Телёнок
B
C
Количество Цена 1 шт
10
2 000р.
12
1 200р.
2
9 000р.
перетащить за
рамку + Shift
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
16
Форматирование
OpenOffice Calc: панель Свойства
Microsoft Excel: панель Главная
фон
рамка
денежный
объединение ячеек
A
1
2
3
B
C
D
Весна
E
проценты
F
Лето
март
апрель
май
июнь
июль
август
31
30
31
30
31
31
К.Ю. Поляков, Е.А. Ерёмин, 2017
знаки в
дробной
части
http://kpolyakov.spb.ru

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

17
Электронные
таблицы
§ 28. Стандартные функции
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

18. Сумма

Электронные таблицы, 9 класс
18
Сумма
= A1 + A2 + A3 + A4 + A5
?
А если 1000 ячеек?
=SUM(A1:A5)
A
B
1
2
3
4
5
6
К.Ю. Поляков, Е.А. Ерёмин, 2017
=СУММ(A1:A5)
C
D
=SUM(B2:C5)
=СУММ(B2:C5)
http://kpolyakov.spb.ru

19. Сумма

Электронные таблицы, 9 класс
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)
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

20. Сумма

Электронные таблицы, 9 класс
20
Сумма
Пример:
A
B
1
7
5
2 =SUM(A1:B1) =SUM(A1:A2)
3
=SUM(A1:B2)
?
A
1
2
3
7
12
B
5
19
43
Что получится?
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
21
Минимум, максимум, среднее
MIN (МИН) – минимальное значение
MAX (МАКС) – максимальное значение
AVERAGE (СРЗНАЧ) – среднее значение
1
2
3
!
A
1
ФЫВА
=MIN(A1:B2)
1
B
3
=MAX(A1:B2) =AVERAGE(A1:B2)
3
2
Пустые и нечисловые ячейки не учитываются!
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

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

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

23
Электронные
таблицы
§ 29. Сортировка данных
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
24
Простая сортировка
OpenOffice Calc:
!
?
Microsoft Excel:
Сортировка по первому столбцу выделенного
диапазона!
Почему нельзя сортировать только один столбец?
A
1
2
3
4
5
Сотрудник
Иванов
Петров
Акимов
Дубов
К.Ю. Поляков, Е.А. Ерёмин, 2017
B
Зарплата
12 000 р.
15 000 р.
17 000 р.
11 000 р.
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
25
Сортировка по любому столбцу
1
2
3
4
5
A
B
Сотрудник
Акимов
Петров
Иванов
Дубов
Зарплата
17 000 р.
15 000 р.
12 000 р.
11 000 р.
?
Подходит ли простая
сортировка?
OpenOffice Calc: Данные – Сортировать
Microsoft Excel: Данные – Сортировка
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
26
Многоуровневая сортировка
1
2
3
4
5
6
7
A
B
Группа
Альбом
Город 312 Новая музыка
Город 312 Обернись
Город 312 Вне зоны доступа
Любэ
Свои
Любэ
Давай за…
ЛюбэСначала
Комбат
– по группе
(по алфавиту)
К.Ю. Поляков, Е.А. Ерёмин, 2017
С
Год
2010
2007
2006
2009
2002
1996
Для одной
группы – по
убыванию года
http://kpolyakov.spb.ru

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

27
Электронные
таблицы
§ 30. Относительные и
абсолютные ссылки
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
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
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

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

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

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

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

Электронные таблицы, 9 класс
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
числа
формулы
Как ввести одну формулу и скопировать её
во все ячейки?
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

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

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

Электронные таблицы, 9 класс
33
Смешанные ссылки
D8: =C$13+$F4 при копировании в
D12: =C$13+$F8 D5:
F8:
=C$13+$F1
=E$13+$F4 B8:
=A$13+$F4
E10: =D$13+$F6 C6:
=B$16+$F2
Быстрое изменение ссылок:
OpenOffice Calc: Shift+F4
Microsoft Excel: F4
B1 $B$1 B$1 $B1
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

34
Электронные
таблицы
§ 31. Диаграммы
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
35
Что такое диаграмма?
Диаграмма – это графическое изображение данных.
Основные типы диаграмм:
столбчатая (гистограмма)
линейчатая
график
круговая
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

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

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

Электронные таблицы, 9 класс
37
Столбчатая диаграмма
А
В
овцы
1
4
2
1
2 Аськин
3 Баськин
4 Сенькин
C
кролики
2
2
3
D
куры
5
5
4
легенда
5
овцы
кролики
куры
4
3
2
?
1
0
Аськин
Баськин
Сенькин
ряды
Какие данные
проще
считывать?
категории
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

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

39. График

Электронные таблицы, 9 класс
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
название оси
К.Ю. Поляков, Е.А. Ерёмин, 2017
5
6
7
8
9
10 11 12
Месяц
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
40
Графики функций
y x 3 на отрезке [0;2]
a)
1
2
3
4
5
6
7
A
X
B
Y
б)
0
0,2
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
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
y=x3
OpenOffice Calc:
Диаграмма XY
Microsoft Excel:
Точечная диаграмма
8
7
6
5
4
3
2
1
0
0
К.Ю. Поляков, Е.А. Ерёмин, 2017
0,5
1
1,5
2
http://kpolyakov.spb.ru

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

41
Электронные
таблицы
§ 32. Условные вычисления
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
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)
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
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
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

44. Пример

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

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

Электронные таблицы, 9 класс
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%))
)
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
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%)
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

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

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

Электронные таблицы, 9 класс
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); "+"; "")
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

49
Электронные
таблицы
§ 33. Обработка больших
массивов данных
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
50
Выделение больших диапазонов
Протаскивание мыши
+ЛКМ
A
B
C
D
1
2
3
4
– ЛКМ
Два щелчка в противоположных углах:
A
1
2
3
4
К.Ю. Поляков, Е.А. Ерёмин, 2017
ЛКМ
B
C
D
Shift+ЛКМ
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
51
Выделение больших диапазонов
До первой пустой ячейки в нужном направлении:
A
1
2
3
4
5
B
C
1
11
111
2
22
222
D
Ctrl+ Shift+↓
В поле Имя над таблицей:
ввести адрес
диапазона
A5:B1000
A
B
C
D
1
2
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
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)
?
Ответ?
К.Ю. Поляков, Е.А. Ерёмин, 2017
=SUM(D2:D1001)
=СУММА(D2:D1001)
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
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)
Ответ?
К.Ю. Поляков, Е.А. Ерёмин, 2017
=СРЗНАЧ(D2:D1001)
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
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; "яблоко")
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
55
Функции COUNTIF и COUNTIFS
Количество ячеек, удовлетворяющих условию:
=COUNTIF(D2:D1001; ">2004")
=СЧЁТЕСЛИ(D2:D1001; ">2004")
!
Нельзя использовать сложное условие!
=COUNTIFS(D2:D101; ">2004";
E2:E101; "<59")
=СЧЁТЕСЛИМН(D2:D101; ">2004";
E2:E101; "<59")
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

56. Пример

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

57. Функция SUMIF

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

58. Функция AVERAGEIF

Электронные таблицы, 9 класс
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)
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
59
Работа с листами
ПКМ
Обращение к данным другого листа:
OpenOffice Calc:
=Январь.B2+Февраль.B2+Март.B2
=SUM('К оплате'.B2:С4)
Microsoft Excel:
=Январь!B2+Февраль!B2+Март!B2
=СУММ('К оплате'!B2:С4)
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

60
Электронные
таблицы
§ 34. Численные методы
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
61
Методы решения уравнений
Точные (аналитические) методы:
ax b 1,
!
a 0
1 b
x
a
Можем понять зависимость x от a и b!
a x b cos x
?
Как решать?
Численное решение – это решение задачи для
конкретных исходных данных.
a 1, b 1, x cos x
Численный метод – это метод, который применяется
для поиска численного решения.
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
62
Численные методы
!
Численные методы часто дают неточное
(приближенное решение)!
Приближённый метод – это метод, который позволяет
найти решение задачи с некоторой (допустимой)
ошибкой (погрешностью).
Погрешность — отклонение значения величины,
полученного в результате измерений или вычислений,
от её истинного (действительного) значения.
x = 0,517
К.Ю. Поляков, Е.А. Ерёмин, 2017
x 0,517
http://kpolyakov.spb.ru

63. Пример

Электронные таблицы, 9 класс
63
Пример
x cos x
?
Как решать?
Графический метод:
!
Можно поручить такой поиск компьютеру!
?
Можно ли получить точное решение?
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
64
Как работают численные методы?
Сжатие отрезка:
1) выбрать начальный отрезок [a0, b0] (одно решение!)
2) уточнить решение с помощью некоторого
алгоритма: [a, b]
3) повторять шаг 2, пока длина отрезка [a, b] не станет
достаточно мала
a
?
x
a b
2
?
b
Как оценить ошибку?
Завершение работы:
К.Ю. Поляков, Е.А. Ерёмин, 2017
Что лучше выбрать в
качестве решения?
b a
x x
2
b a 2
*
допустимая
ошибка
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
65
Как работают численные методы?
По одной точке:
1) выбрать начальное приближение x0
Начальное приближение – это начальное значение
неизвестной величины, которое уточняется с помощью
приближённого метода.
2) уточнить решение с помощью некоторого алгоритма:
x1 x2 x3 …
3) повторять шаг 2, пока два последовательных
приближения не будут отличаться достаточно мало
Завершение работы:
К.Ю. Поляков, Е.А. Ерёмин, 2017
xi xi 1
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
66
Численные методы: «за» и «против»
дают практическое решение задачи
неточное решение x 0,517
неясно, как зависит от исходных данных
(параметров)
объём вычислений может быть велик
не всегда легко оценить ошибку
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

,
Электронные таблицы, 9 класс
.
67
Пример решения уравнения
Найти все решения на интервале [-2; 2]:
f1 ( x) x
?
f 2 ( x) cos x
2
Сколько решений?
x 2 cos x
строим графики
Заполнение таблицы
1
2
3
4
5
6
7
8
A
x
B
f1(x)
–2
–1,75
К.Ю. Поляков, Е.А. Ерёмин, 2017
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)
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
68
Пример решения уравнения
Построение графиков
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
начальные
приближения
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
69
Пример решения уравнения
Подготовка данных
начальное
приближение
1
2
3
!
E
x
F
f1(x)
G
f2(x)
H
f1(x) – f2(x)
–1
=E2^2
=COS(E2)
?
Зачем нужна разность функций?
=F2–G2
Если x – решение уравнения f1(x) = f2(x), то
f1(x) – f2(x) = 0!
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
70
Пример решения уравнения
Подбор параметра
изменяемая
ячейка 1
2
3
E
x
–1
F
f1(x)
=E2^2
H
f1(x) – f2(x)
G
f2(x)
целевая
ячейка
=F2 – G2
=COS(E2)
?
OpenOffice Calc:
Как найти второе
Сервис – Подбор параметра
решение?
Microsoft Excel:
Данные – Анализ «что-если» – Подбор параметра
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

71
Электронные
таблицы
§ 35. Оптимизация
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
72
Что такое оптимизация?
Оптимизация – это поиск наилучшего (оптимального)
решения задачи в заданных условиях.
1) Цель: выбрать неизвестный x, так чтобы
f ( x) min
или
целевая функция
f ( x) max
f ( x) min
2) Ограничения
задача
оптимизации
?
Почему неправильно «самый оптимальный»?
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
73
Что такое минимум?
f (x)
локальный
минимум
x
глобальный
минимум
• обычно нужно найти глобальный минимум
• большинство численных методов находят только
локальный минимум
!
Результат локальной оптимизации зависит от
начального приближения!
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
74
Оптимальный раскрой листа

z
x
x
z
Цель:
V ( x) max
?
Ограничения:
?
V ( x) x (1 2 x) 2 max
Какие ограничения?
0 x 0,5
Какой результат ожидаете (по интуиции)?
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
75
Оптимальный раскрой листа
В табличном процессоре:
V
V ( x) x (1 2 x) max
2
0
0,1
0,2
0,3
0,4
?
начальное
приближение 0,2
1
2
К.Ю. Поляков, Е.А. Ерёмин, 2017
0,5
E
x
0,2
x
Какая формула в F2?
F
Объём
0,072
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
76
Оптимизация в табличном процессоре
Задача оптимизации: найти максимум (или минимум)
целевой функции в ячейке …, изменяя значения ячеек
… при ограничениях ….
OpenOffice.org Calc:
Сервис – Поиск решения
Microsoft Excel:
надстройка Данные – Поиск решения
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
77
Оптимизация в табличном процессоре
OpenOffice.org Calc:
целевая
ячейка
изменяемые
ячейки:
E2
D2:D6
D2:D6; C5:C8
ограничения
A1 <= 20
A1 = целое
B2:B8 >= 5
B2:B4>=C2:C4
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

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

Электронные таблицы, 9 класс
78
Оптимизация в табличном процессоре
Excel:
целевая
ячейка
изменяемые
ячейки:
E2
D2:D6
D2:D6; C5:C8
ограничения
A1 <= 20
A1 = целое
B2:B8 >= 5
B2:B4>=C2:C4
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

79. Конец фильма

Электронные таблицы, 9 класс
79
Конец фильма
ПОЛЯКОВ Константин Юрьевич
д.т.н., учитель информатики
ГБОУ СОШ № 163, г. Санкт-Петербург
kpolyakov@mail.ru
ЕРЕМИН Евгений Александрович
к.ф.-м.н., доцент кафедры мультимедийной
дидактики и ИТО ПГГПУ, г. Пермь
eremin@pspu.ac.ru
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru

80. Источники иллюстраций

Электронные таблицы, 9 класс
80
Источники иллюстраций
1.
2.
иллюстрации художников издательства «Бином»
авторские материалы
К.Ю. Поляков, Е.А. Ерёмин, 2017
http://kpolyakov.spb.ru
English     Русский Rules