Excel.ppt

Основная задача – автоматические вычисления с данными в таблицах.
Кроме того:
хранение данных в табличном виде
представление данных в виде диаграмм
анализ данных
составление прогнозов
поиск оптимальных решений
подготовка и печать отчетов
Примеры:
Microsoft Excel – файлы *.xls, *.xlsx
OpenOffice Calc – файлы *.ods – бесплатно

номера
строк

строка

столбец

имена столбцов

активная ячейка

неактивная ячейка

текст
числа
формулы
время
дата

Программы – Microsoft Office – Excel 2007

Файлы: *.xlsx (старая версия – *.xls)

Вася.xlsx рабочая книга

переходы по листам

ЛКМ

ПКМ

новый лист

адрес активной ячейки

ячейка B2

диапазон B2:С7

Ссылки в формулах:  =B2+2*C3 =A2+2*СУММ(B2:C7)

адрес активной ячейки

отменить (Esc)

принять (Enter)

строка редактирования

ЛКМ

F2 – редактировать прямо в ячейке

ячейка:

+ЛКМ

– ЛКМ

диапазон:

вся таблица:

ЛКМ

ЛКМ

строки:

ЛКМ

столбцы:

ЛКМ

несвязанные диапазоны:

+Ctrl и выделять второй

высота строк

ширина
столбцов

ПКМ

перетащить ЛКМ
за рамку (!)

+Ctrl = копирование

+Alt = на другой лист

относительные (меняются так же, как и адрес формулы )

формула «переехала» на один столбец вправо и на одну строку вниз;

абсолютные
(не меняются)

смешанные(меняется только относительная часть)

имя столбца  на 1
номер строки  на 1

арифметическая прогрессия

маркер заполнения

копирование формул

ЛКМ

даты

списки

время

ЛКМ

все свойства

 размер

направление

в несколько строк

денежныйформат

количество знаковв дробной части

ввод в ячейке

ввод в строке редактирования

диапазон

ячейка

мастер функций

ЕСЛИ – выбор из двух вариантов

условие

если «да»

если «нет»

НЕ – обратное условие, НЕ(B2<10)  ?
И – одновременное выполнение всех условий

ИЛИ – выполнение хотя бы одного из условий

СЧЁТ – считает ячейки с числами или формулами, которые дают числа

СЧЁТЕСЛИ – считает ячейки, удовлетворяющие условию

Сортировка – это расстановка элементов в заданном порядке.

критерий

строки или столбцы

первая строка – это заголовки

Задача: расставить фамилии по алфавиту, а людей с одинаковыми фамилиями расставить в алфавитном порядке по именам.

ЛКМ

ввести имя

диаграммы строятся на основе данных таблицы
проще всего сначала выделить все нужные данные, а потом…




все данные, которые должны обновляться автоматически, нужно выделить
для выделения несвязанных диапазонов используем +Ctrl

Гистограмма (столбчатая диаграмма): сравнение значений одного или нескольких рядов данных

График: показывает изменение процесса во времени (равномерные отсчеты)

Круговая: доли в сумме

Точечная: связь между парами значений (график функции)

название диаграммы

легенда

ряды данных

ось

сетка

подписи данных

Конструктор: общие свойства

Макет: настройка свойств отдельных элементов

Формат: оформление отдельных элементов

Задача: построить график функции для .

шаг 0,5

ЛКМ

ЛКМ

выделить данные

результат:

Задача: найти все решения уравнения  на интервале [-5,5]

Методы решения уравнений:
аналитические: решение в виде формулы
численные: приближенное решение, число
выбрать начальное приближение «рядом» с решением


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

1. Таблица значений функций на интервале [-5,5]

2. Графики функций (диаграмма «Точечная»)

2 решения: начальные приближения

начальное приближение

целевая ячейка

ошибка

решение уравнения

Оптимизация – это поиск оптимального (наилучшего) варианта в заданных условиях.

Оптимальное решение – такое, при котором некоторая заданная функция (целевая функция) достигает минимума или максимума.

Постановка задачи:
целевая функция



ограничения, которые делают задачу осмысленной

(расходы, потери, ошибки)

(доходы, приобретения)

Задача без ограничений: построить дом
при минимальных затратах. Решение: не строить дом вообще.

локальный минимум

обычно нужно найти глобальный минимум
большинство численных методов находят только локальный минимум
минимум, который найдет Excel, зависит от выбора начального приближения («шарик на горке скатится в ближайшую ямку»)

начальное приближение

начальное приближение

целевая
ячейка

изменяемые ячейки:
E2
D2:D6
D2:D6; C5:C8

целевая
ячейка

ограничения
A1 <= 20
B2:B8 >= 5
A1 = целое

Надстройка «Поиск решения» позволяет:
искать минимум и максимум функции
использовать несколько изменяемых ячеек и диапазонов
вводить ограничения (<=, >=, целое, двоичное)

Ряд данных – это упорядоченный набор значений

Основные свойства (ряд A1:A20):
количество элементов =СЧЕТ(A1:A20)
количество элементов, удовлетворяющих некоторому условию:  = СЧЕТЕСЛИ(A1:A20;"<5")
минимальное значение =МИН(A1:A20)
максимальное значение =МАКС(A1:A20)
сумма элементов =СУММ(A1:A20)
среднее значение =СРЗНАЧ(A1:A20)

Для этих рядов одинаковы МИН, МАКС, СРЗНАЧ

Дисперсия («разброс») – это величина, которая характеризует разброс данных относительно среднего значения.

среднее арифметическое

квадрат отклонения от среднего

средний квадрат отклонения от среднего значения

Что неудобно:
если измеряется в метрах,  то – в м2

Вопросы:
есть ли связь между этими рядами (соответствуют ли пары какой-нибудь зависимости )
насколько сильна эта связь?

Как понимать это число?
если
если
если

увеличение приводит к увеличению

в среднем!

увеличение приводит к уменьшению

связь обнаружить не удалось

Что плохо?
единицы измерения: если в метрах, в литрах, то – в мл
зависит от абсолютных значений и , поэтому ничего не говорит о том, насколько сильна связь

– СКВО рядов и

безразмерный!

Как понимать это число?
если : увеличение приводит к увеличению
если : увеличение приводит к уменьшению
если : связь обнаружить не удалось

Как понимать коэффициент корреляции?
: очень слабая корреляция
: слабая
: средняя
: сильная
: очень сильная
: линейная зависимость
: линейная зависимость

задают некоторую неизвестную функцию

Зачем:
найти в промежу-точных точках(интерполяция)
найти вне диапазона измерений(экстраполяция,  прогнозирование)

Вывод: задача некорректна, поскольку решение  неединственно.

Корректная задача: найти функцию заданного вида,  которая лучше всего соответствует данным.

Примеры:
линейная
полиномиальная

степенная
экспоненциальная

логарифмическая

заданные пары значений

чтобы складывать положительные значения
решение сводится к системе линейных уравнений (просто решать!)

заданные пары значений

Крайние случаи:
если график проходит через точки:

если считаем, что y не меняется и :

– среднее значение

ПКМ

Сложные случаи (нестандартная функция):

Алгоритм:
выделить ячейки для хранения
построить ряд для тех же
построить на одной диаграмме ряды и
попытаться подобрать так, чтобы два графика были близки
вычислить в отдельной ячейке
функции: СУММКВРАЗН – сумма квадратов разностей рядов ДИСПР – дисперсия
Поиск решения:

Тема 6. Моделирование
(по материалам учебника Н.В. Макаровой)

– начальная численность

– после 1 цикла деления

– после 2-х циклов

Особенности модели:
не учитывается смертность
не учитывается влияние внешней среды
не учитывается влияние других видов

– коэффициент рождаемости

– коэффициент смертности

Особенности модели:
не учитывается влияние численности N и внешней среды на K
не учитывается влияние других видов на K

Коэффициент изменения численности

A – коэффициент устойчивости вида

B – коэффициент среды обитания

Варианты:
устанавливается постоянная численность
постоянно меняется (колебания)
вымирание

Ni – численность белок, Mi – численность бурундуков

K2, K4 – взаимное влияние

если K2 >K1 или K4 >K3 – враждующие виды