close

Вход

Забыли?

вход по аккаунту

?

Excel

код для вставкиСкачать
1
Работа в Excel 2007
1.
2.
3.
4.
5.
6.
Основы
Диаграммы
Численные методы
Статистика
Восстановление зависимостей
Моделирование
© К.Ю. Поляков, 2009-2012
2
Работа в Excel 2007
Тема 1. Основы
© К.Ю. Поляков, 2009-2012
Электронные таблицы
Основная задача – автоматические вычисления с
данными в таблицах.
Кроме того:
• хранение данных в табличном виде
• представление данных в виде диаграмм
• анализ данных
• составление прогнозов
• поиск оптимальных решений
• подготовка и печать отчетов
Примеры:
• Microsoft Excel – файлы *.xls, *.xlsx
• OpenOffice Calc – файлы *.ods – бесплатно
3
4
Электронные таблицы
имена столбцов
активная
ячейка
номера
строк
неактивная
ячейка
строка
•
•
•
•
•
текст
числа
формулы
время
дата
столбец
5
Начало работы с Microsoft Excel
Программы – Microsoft Office – Excel 2007
Файлы:
*.xlsx (старая версия – *.xls)
Вася.xlsx рабочая книга
Лист 1
переходы
по листам
Лист 2
План
по валу
Вал
по плану
ЛКМ
ПКМ
новый лист
6
Адреса
адрес активной ячейки
диапазон B2:С7
B2
ячейка B2
Ссылки в формулах:
=B2+2*C3
=A2+2*СУММ(B2:C7)
!
Формула всегда начинается знаком «=»!
С7
7
Ввод данных
адрес
активной
ячейки
отменить (Esc)
принять (Enter)
строка
редактирования
ЛКМ
F2 – редактировать прямо в ячейке
8
Выделение данных
ячейка:
ЛКМ
диапазон:
+ЛКМ
– ЛКМ
строки:
ЛКМ
несвязанные диапазоны:
+Ctrl и выделять второй
столбцы:
ЛКМ
вся таблица:
ЛКМ
Операции со строками и столбцами
размеры
высота
строк
ширина
столбцов
добавление, удаление
ПКМ
9
Перемещение и копирование
перетащить ЛКМ
за рамку (!)
+Ctrl = копирование
+Alt = на другой лист
перемещение со сдвигом (+Shift)
10
11
Типы ссылок
относительные
(меняются так же, как и адрес формулы )
формула «переехала»
на один столбец вправо
и на одну строку вниз;
имя столбца на 1
номер строки на 1
абсолютные
смешанные
(не меняются)
(меняется только относительная часть)
12
Заполнение рядов
арифметическая прогрессия
копирование формул
маркер
заполнения
ЛКМ
даты
ЛКМ
время
списки
13
Оформление ячеек
размер
все свойства
направление
в несколько
строк
денежный
формат
количество знаков
в дробной части
14
Функции
ввод в строке редактирования
изменение
диапазона
ввод в ячейке
диапазон
мастер
функций
ячейка
!
Можно мышкой!
Некоторые функции
СУММ – сумма значений ячеек и диапазонов
СРЗНАЧ – среднее арифметическое
МИН – минимальное значение
МАКС – максимальное значение
15
16
Функция ЕСЛИ
ЕСЛИ – выбор из двух вариантов
условие
если «да»
если «нет»
=ЕСЛИ(B2="сдал";
ЕСЛИ(A2>80;
5; 4); "–")
=ЕСЛИ(A2>=70;
"сдал";
"не сдал")
Логические операции
НЕ – обратное условие, НЕ(B2<10) ?B2>=10
И – одновременное выполнение всех условий
=ЕСЛИ( И(B2>1994; C2>175);"да";"–")
17
Логические операции
18
ИЛИ – выполнение хотя бы одного из условий
=ЕСЛИ( ИЛИ(B2=100; C2=100; B2+C2>=180);"да";"–")
Подсчёт числовых значений
СЧЁТ – считает ячейки с числами или формулами,
которые дают числа
=A1+1
2
19
20
Подсчёт значений по условию
СЧЁТЕСЛИ – считает ячейки, удовлетворяющие условию
2
3
2
1
Сортировка
Сортировка – это расстановка
элементов в заданном порядке.
Сортировка одного столбца
21
22
Сортировка связанных данных
?
критерий
Почему нельзя
сортировать по
столбцу?
строки или
столбцы
первая
строка – это
заголовки
Многоуровневая сортировка
Задача: расставить фамилии по
алфавиту, а людей с одинаковыми
фамилиями расставить в
алфавитном порядке по именам.
ЛКМ
23
Имена ячеек и диапазонов
Присвоить имя
ввести имя
Работа с именами
Имена в формулах
24
25
Работа в Excel 2007
Тема 2. Диаграммы
© К.Ю. Поляков, 2009-2012
Общий подход
• диаграммы строятся на основе данных
таблицы
• проще всего сначала выделить все нужные
данные, а потом…
• все данные, которые должны обновляться
автоматически, нужно выделить
• для выделения несвязанных диапазонов
используем +Ctrl
26
Основные типы диаграмм
Гистограмма (столбчатая диаграмма):
сравнение значений одного или
нескольких рядов данных
График: показывает изменение
процесса во времени
(равномерные отсчеты)
27
Круговая: доли в сумме
Точечная: связь между
парами значений (график функции)
28
Элементы диаграмм
название
диаграммы
сетка
подписи
данных
легенда
ряды
данных
ось
названия осей
Настройка диаграммы и ее элементов
Конструктор: общие свойства
Макет: настройка свойств отдельных элементов
Формат: оформление отдельных элементов
29
30
Графики функций
Задача: построить график функции
y x
2
для 5 Таблица значений функции: шаг 0,5
ЛКМ
ЛКМ
!
Что зависит от шага?
x5
.
Графики функций
Вставка диаграммы «Точечная»:
выделить данные
результат:
31
32
Работа в Excel 2007
Тема 3. Численные
методы
© К.Ю. Поляков, 2009-2012
Решение уравнений
33
Задача: найти все решения уравнения x 2 5 cos x
на интервале [-5,5]
?
Как решить математическими методами?
Методы решения уравнений:
• аналитические: решение в виде формулы x ...
• численные: приближенное решение, число
1) выбрать начальное приближение x 0 «рядом» с
решением
?
Как выбрать начальное приближение?
2) по некоторому алгоритму вычисляют первое
приближение, затем – второе и т.д. x 0 x1 x 2 ...
3) вычисления прекращают, когда значение меняется очень
*
мало (метод сходится) x 0 ... x15 x16 x
Решение уравнения
34
x 5 cos x
2
1. Таблица значений функций на интервале [-5,5]
2. Графики функций (диаграмма «Точечная»)
2 решения:
начальные приближения
x 0 1,5
x 0 1,5
Решение уравнения
35
x 5 cos x
2
3. Подготовка данных
начальное
приближение
целевая
ячейка
Цель: H2=0
?
Зачем нужна разность?
Решение уравнения
36
x 5 cos x
2
4. Подбор параметра
ошибка
решение
уравнения
?
?
Как найти второе решение?
Почему
не нуль?
37
Оптимизация
Оптимизация – это поиск оптимального (наилучшего)
варианта в заданных условиях.
Оптимальное решение – такое, при котором некоторая
заданная функция (целевая функция) достигает
минимума или максимума.
Постановка задачи:
• целевая функция
(расходы, потери, ошибки)
f ( x ) min
f ( x ) max
(доходы, приобретения)
• ограничения, которые делают задачу осмысленной
Задача без ограничений: построить дом
при минимальных затратах.
Решение: не строить дом вообще.
38
Оптимизация
f ( x)
локальный
минимум
глобальный
минимум
x
• обычно нужно найти глобальный минимум
• большинство численных методов находят только
локальный минимум
• минимум, который найдет Excel, зависит от выбора
начального приближения («шарик на горке скатится в
ближайшую ямку»)
39
Поиск минимума функции
y x 6 sin x 5 cos x
2
1. Строим график функции (диаграмма «Точечная»)
?
Зачем нужен
график?
начальное приближение
x0 2
2. Подготовка данных
начальное
приближение
!
целевая
ячейка
Изменение E2 должно влиять на F2!
40
Поиск минимума функции
3. Надстройка «Поиск решения»
изменяемые
ячейки:
E2
D2:D6
D2:D6; C5:C8
ограничения
A1 <= 20
B2:B8 >= 5
A1 = целое
целевая
ячейка
Параметры оптимизации
41
Оптимизация
?
Подбор параметра – это оптимизация?
Надстройка «Поиск решения» позволяет:
• искать минимум и максимум функции
• использовать несколько изменяемых ячеек и
диапазонов
• вводить ограничения (<=, >=, целое, двоичное)
?
Как влияет ограничение «A1-целое» на
сложность решения задачи?
42
43
Работа в Excel 2007
Тема 4. Статистика
© К.Ю. Поляков, 2009-2012
Ряд данных и его свойства
44
Ряд данных – это упорядоченный набор значений
x1 , x 2 , ..., x n
Основные свойства (ряд A1:A20):
• количество элементов =СЧЕТ(A1:A20)
• количество элементов, удовлетворяющих
некоторому условию:
= СЧЕТЕСЛИ(A1:A20;"<5")
• минимальное значение =МИН(A1:A20)
• максимальное значение =МАКС(A1:A20)
• сумма элементов =СУММ(A1:A20)
• среднее значение =СРЗНАЧ(A1:A20)
45
Дисперсия
Для этих рядов одинаковы МИН, МАКС, СРЗНАЧ
?
В чем различие?
Дисперсия («разброс») – это величина, которая
характеризует разброс данных относительно
среднего значения.
46
Дисперсия
n
( x1 x ) ( x 2 x ) ( x n x )
2
Dx x 2
n
x1 x 2 x n
n
2
( xi x )
2
i 1
n
среднее арифметическое
( x1 x )
2
квадрат
отклонения x 1
от среднего
D x средний квадрат
отклонения от
среднего значения
47
Дисперсия и СКВО
Стандартная функция
=ДИСПР(A1:A20)
Функции – Другие – Статистические
Что неудобно:
если x измеряется в метрах,
то D x – в м2
?
В каких
единицах
измеряется?
СКВО = среднеквадратическое отклонение
x Dx
=СТАНДОТКЛОНП(A1:A20)
Взаимосвязь рядов данных
Два ряда одинаковой длины:
x1 , x 2 , ..., x n
y1 , y 2 , ..., y n
Вопросы:
• есть ли связь между этими рядами (соответствуют
ли пары ( x i , y i ) какой-нибудь зависимости y f ( x ) )
• насколько сильна эта связь?
48
49
Взаимосвязь рядов данных
Ковариация:
n
x
K xy ?
i
x y i y i1
n
Если x и y – один и тот же ряд?
K xx D x
в среднем!
Как понимать это число?
• если K xy 0 увеличение x приводит к увеличению y
• если K xy 0 увеличение x приводит к уменьшению y
• если K xy 0 связь обнаружить не удалось
Что плохо?
• единицы измерения: если x в метрах, y в литрах,
то K xy – в мл
• K xy зависит от абсолютных значений x и y , поэтому
ничего не говорит о том, насколько сильна связь
Взаимосвязь рядов данных
Коэффициент корреляции:
xy ?
K xy
x x, y
– СКВО рядов x и y
y
Какова размерность?
безразмерный!
1 xy 1
Как понимать это число?
• если xy 0 : увеличение x приводит к увеличению y
• если xy 0 : увеличение x приводит к уменьшению y
• если xy 0 : связь обнаружить не удалось
=КОРРЕЛ(A1:A20;B1:B20)
50
Взаимосвязь рядов данных
51
Как понимать коэффициент корреляции?
0 xy 0 , 2 : очень слабая корреляция
0 , 2 xy 0 ,5 : слабая
0 ,5 xy 0 , 7 : средняя
0 , 7 xy 0 ,9 : сильная
0 ,9 xy 1 : очень сильная
xy 1 : линейная зависимость y ax b , a 0
xy 1 : линейная зависимость y ax b , a 0
?
Если xy
!
Метод для определения линейной зависимости!
0
, то связи нет?
52
Работа в Excel 2007
Тема 5. Восстановление
зависимостей
© К.Ю. Поляков, 2009-2012
Восстановление зависимостей
53
Два ряда одинаковой длины:
x1 , x 2 , ..., x n
y1 , y 2 , ..., y n
задают некоторую неизвестную функцию y f ( x )
Зачем:
• найти y в промежуточных точках
(интерполяция)
y f ( x)
y2
y1
• найти y вне диапазона
измерений
(экстраполяция,
прогнозирование)
x1
x2
xn
Какое решение нам нужно?
y f2 (x)
y f1 ( x )
y2
y1
x1
!
x2
xn
Через заданный набор точек проходит
бесконечно много разных кривых!
Вывод: задача некорректна, поскольку решение
неединственно.
54
55
Восстановление зависимостей
Корректная задача: найти функцию заданного вида,
которая лучше всего соответствует данным.
Примеры:
y f ( x)
• линейная y a x b
y2
• полиномиальная
y1
y a 3 x a 2 x a1 x a 0
3
2
• степенная y a x
• экспоненциальная
b
x1
!
x2
xn
График функции не
обязательно проходит
через заданные точки!
y a e
bx
• логарифмическая
y a ln x b
?
Как выбрать
функцию?
Что значит «лучше всего соответствует»?
Метод наименьших квадратов (МНК):
y f ( x)
y2
y1
Yi f ( x i )
Y2
Y1
( x i , y i ) заданные пары
значений
n
( y i Yi ) min
2
i 1
x1
?
x2
xn
Зачем возведение в квадрат?
1) чтобы складывать положительные значения
2) решение сводится к системе линейных
уравнений (просто решать!)
56
Электронные таблицы Excel
57
МНК для линейной функции
неизвестно!
y f ( x)
Yi k x i
n
y2
y1
(k ) n
( y i Yi ) 2
i 1
i 1
n
n
Y1
k x k 2 xi yi Y2
2
2
i
i 1
x1
i 1
xn
x2
a
( k ) ak bk c min
2
-b
2
n
2
yi
i 1
c
n
k *
b
2a
k
К. Поляков, 2009-2012
( y i kx i )
*
k
x
i
yi
i 1
n
x
2
i
i 1
http://kpolyakov.narod.ru
Коэффициент достоверности
n
R 1
2
( y i Yi )
2
( yi y )
2
i 1
n
i 1
( x i , y i ) заданные пары
значений
Yi f ( x i )
y – среднее значение y i
Крайние случаи:
• если график проходит через точки:
R 1
• если считаем, что y не меняется и Yi y :
2
R 0
2
!
Фактически – метод наименьших квадратов!
58
Восстановление зависимостей
Диаграмма «График»:
ПКМ
59
Восстановление зависимостей
тип
функции
60
61
Восстановление зависимостей
?
!
?
Что такое x ?
В диаграмме «График»
x 1 для первой точки,
x 2 для второй и т.д.
Насколько хорошо выбрана функция?
62
Восстановление зависимостей
Сложные случаи (нестандартная функция):
f ( x ) a sin kx b
?
Что делать?
Алгоритм:
1) выделить ячейки для хранения a , k , b
2) построить ряд Yi f ( x i ) для тех же x i
3) построить на одной диаграмме ряды y i и Yi
4) попытаться подобрать a , k , b так, чтобы
два графика были близки
2
5) вычислить R в отдельной ячейке
функции: СУММКВРАЗН – сумма квадратов разностей рядов
ДИСПР – дисперсия
6) Поиск решения: R min
2
!
Это задача оптимизации!
63
Работа в Excel 2007
Тема 6. Моделирование
(по материалам учебника Н.В. Макаровой)
© К.Ю. Поляков, 2009-2012
64
Модель деления
N 0 – начальная численность
N
N 2 N0
i
N 1 2 N 0 – после 1 цикла деления
N 2 2 N 1 4 N 0 – после 2-х циклов
N i 2 N i 1 2 N 0
i
N0
i
Особенности модели:
1) не учитывается смертность
2) не учитывается влияние внешней среды
3) не учитывается влияние других видов
65
Рождаемость и смертность
N i N i 1 K p N i 1 K c N i 1
K
p
– коэффициент рождаемости
K c – коэффициент смертности
N
N i K N i 1
Коэффициент изменения
численности
K 1 K p Kc
K 1
K 1
N0
K 1
Особенности модели:
1) не учитывается влияние численности N и внешней
среды на K
2) не учитывается влияние других видов на K
i
Влияние численности и внешней среды
66
N i K N i 1 K A (1 B N i 1 )
A – коэффициент устойчивости вида
B – коэффициент среды обитания
Варианты:
• устанавливается
постоянная численность
• постоянно меняется
(колебания)
• вымирание
67
Влияние других видов
Ni – численность белок, Mi – численность бурундуков
N i N i 1 ( 2 K 1 N i 1 K 2 M i 1 )
M i M i 1 ( 2 K 3 M i 1 K 4 N i 1 )
?
Откуда видно
влияние?
K2, K4 – взаимное влияние
если K2 >K1 или K4 >K3 – враждующие виды
Моделирование двух популяций
N0
M0
N i N i 1 ( 2 K 1 N i 1 K 2 M i 1 )
?
Как скопировать формулы «вниз»?
68
Конец фильма
69
Документ
Категория
Презентации
Просмотров
68
Размер файла
2 734 Кб
Теги
1/--страниц
Пожаловаться на содержимое документа