Презентация на тему: Excel

* Работа в Excel 2007 © К.Ю. Поляков, 2009-2012 Основы Диаграммы Численные методы Статистика Восстановление зависимостей Моделирование

* Работа в Excel 2007 Тема 1. Основы © К.Ю. Поляков, 2009-2012

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

* Электронные таблицы номера строк строка столбец имена столбцов активная ячейка неактивная ячейка текст числа формулы время дата

* Начало работы с Microsoft Excel Программы – Microsoft Office – Excel 2007 Файлы: *.xlsx (старая версия – *.xls) Вася.xlsx рабочая книга Лист 1 Лист 2 План по валу Вал по плану переходы по листам ЛКМ ПКМ новый лист

* Адреса адрес активной ячейки ячейка B2 диапазон B2:С7 Ссылки в формулах: =B2+2*C3 =A2+2*СУММ(B2:C7) B2 С7

* Ввод данных адрес активной ячейки отменить (Esc) принять (Enter) строка редактирования ЛКМ F2 – редактировать прямо в ячейке

* Выделение данных ячейка: +ЛКМ – ЛКМ диапазон: вся таблица: ЛКМ ЛКМ строки: ЛКМ столбцы: ЛКМ несвязанные диапазоны: +Ctrl и выделять второй

* Операции со строками и столбцами размеры высота строк ширина столбцов добавление, удаление ПКМ

* Перемещение и копирование перетащить ЛКМ за рамку (!) +Ctrl = копирование +Alt = на другой лист перемещение со сдвигом (+Shift)

* Типы ссылок относительные (меняются так же, как и адрес формулы ) формула «переехала» на один столбец вправо и на одну строку вниз; абсолютные (не меняются) смешанные (меняется только относительная часть) имя столбца на 1 номер строки на 1

* Заполнение рядов арифметическая прогрессия маркер заполнения копирование формул ЛКМ даты списки время ЛКМ

* Оформление ячеек все свойства размер направление в несколько строк денежный формат количество знаков в дробной части

* Функции ввод в ячейке ввод в строке редактирования диапазон ячейка мастер функций

* Некоторые функции СУММ – сумма значений ячеек и диапазонов СРЗНАЧ – среднее арифметическое МИН – минимальное значение МАКС – максимальное значение

* Функция ЕСЛИ ЕСЛИ – выбор из двух вариантов =ЕСЛИ(A2>=70; "сдал"; "не сдал") условие если «да» если «нет» =ЕСЛИ(B2="сдал"; ЕСЛИ(A2>80; 5; 4); "–")

* Логические операции НЕ – обратное условие, НЕ(B2=10 =ЕСЛИ( И(B2>1994; C2>175);"да";"–")

* Логические операции ИЛИ – выполнение хотя бы одного из условий =ЕСЛИ( ИЛИ(B2=100; C2=100; B2+C2>=180);"да";"–")

* Подсчёт числовых значений СЧЁТ – считает ячейки с числами или формулами, которые дают числа =A1+1 2

* Подсчёт значений по условию СЧЁТЕСЛИ – считает ячейки, удовлетворяющие условию 2 3 1 2

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

* Сортировка связанных данных критерий строки или столбцы первая строка – это заголовки

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

* Имена ячеек и диапазонов Присвоить имя ввести имя Имена в формулах Работа с именами

* Работа в Excel 2007 Тема 2. Диаграммы © К.Ю. Поляков, 2009-2012

диаграммы строятся на основе данных таблицы проще всего сначала выделить все нужные данные, а потом… все данные, которые должны обновляться автоматически, нужно выделить для выделения несвязанных диапазонов используем +Ctrl * Общий подход

* Основные типы диаграмм Гистограмма (столбчатая диаграмма): сравнение значений одного или нескольких рядов данных График: показывает изменение процесса во времени (равномерные отсчеты) Круговая: доли в сумме Точечная: связь между парами значений (график функции)

* Элементы диаграмм название диаграммы легенда ряды данных ось сетка подписи данных

* Настройка диаграммы и ее элементов Конструктор: общие свойства Макет: настройка свойств отдельных элементов Формат: оформление отдельных элементов

* Графики функций Задача: построить график функции для . Таблица значений функции: шаг 0,5 ЛКМ ЛКМ

* Графики функций Вставка диаграммы «Точечная»: выделить данные результат:

* Работа в Excel 2007 Тема 3. Численные методы © К.Ю. Поляков, 2009-2012

* Решение уравнений Задача: найти все решения уравнения на интервале [-5,5] Методы решения уравнений: аналитические: решение в виде формулы численные: приближенное решение, число выбрать начальное приближение «рядом» с решением по некоторому алгоритму вычисляют первое приближение, затем – второе и т.д. вычисления прекращают, когда значение меняется очень мало (метод сходится)

* Решение уравнения 1. Таблица значений функций на интервале [-5,5] 2. Графики функций (диаграмма «Точечная») 2 решения: начальные приближения

* Решение уравнения 3. Подготовка данных начальное приближение целевая ячейка Цель: H2=0

* Решение уравнения 4. Подбор параметра ошибка решение уравнения

* Оптимизация Оптимизация – это поиск оптимального (наилучшего) варианта в заданных условиях. Оптимальное решение – такое, при котором некоторая заданная функция (целевая функция) достигает минимума или максимума. Постановка задачи: целевая функция ограничения, которые делают задачу осмысленной (расходы, потери, ошибки) (доходы, приобретения) Задача без ограничений: построить дом при минимальных затратах. Решение: не строить дом вообще.

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

* Поиск минимума функции 1. Строим график функции (диаграмма «Точечная») 2. Подготовка данных начальное приближение начальное приближение целевая ячейка

* Поиск минимума функции 3. Надстройка «Поиск решения» изменяемые ячейки: E2 D2:D6 D2:D6; C5:C8 целевая ячейка ограничения A1 = 5 A1 = целое

* Параметры оптимизации

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

* Работа в Excel 2007 Тема 4. Статистика © К.Ю. Поляков, 2009-2012

* Ряд данных и его свойства Ряд данных – это упорядоченный набор значений Основные свойства (ряд A1:A20): количество элементов =СЧЁТ(A1:A20) количество элементов, удовлетворяющих некоторому условию: = СЧЁТЕСЛИ(A1:A20;"

* Дисперсия Для этих рядов одинаковы МИН, МАКС, СРЗНАЧ Дисперсия («разброс») – это величина, которая характеризует разброс данных относительно среднего значения.

* Дисперсия среднее арифметическое квадрат отклонения от среднего средний квадрат отклонения от среднего значения

* Дисперсия и СКВО Стандартная функция =ДИСПР(A1:A20) Что неудобно: если измеряется в метрах, то – в м2 Функции – Другие – Статистические СКВО = среднеквадратическое отклонение =СТАНДОТКЛОНП(A1:A20)

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

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

* Взаимосвязь рядов данных Коэффициент корреляции: – СКВО рядов и безразмерный! Как понимать это число? если : увеличение приводит к увеличению если : увеличение приводит к уменьшению если : связь обнаружить не удалось =КОРРЕЛ(A1:A20;B1:B20)

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

* Работа в Excel 2007 Тема 5. Восстановление зависимостей © К.Ю. Поляков, 2009-2012

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

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

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

* Что значит «лучше всего соответствует»? заданные пары значений Метод наименьших квадратов (МНК): чтобы складывать положительные значения решение сводится к системе линейных уравнений (просто решать!)

МНК для линейной функции * неизвестно! a -b c Электронные таблицы Excel К. Поляков, 2009-2012 http://kpolyakov.narod.ru

* Коэффициент достоверности заданные пары значений Крайние случаи: если график проходит через точки: если считаем, что y не меняется и : – среднее значение

* Восстановление зависимостей Диаграмма «График»: ПКМ

* Восстановление зависимостей

* Восстановление зависимостей

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

* Работа в Excel 2007 Тема 6. Моделирование (по материалам учебника Н.В. Макаровой) © К.Ю. Поляков, 2009-2012

* Модель деления – начальная численность – после 1 цикла деления – после 2-х циклов Особенности модели: не учитывается смертность не учитывается влияние внешней среды не учитывается влияние других видов

* Рождаемость и смертность – коэффициент рождаемости – коэффициент смертности Особенности модели: не учитывается влияние численности N и внешней среды на K не учитывается влияние других видов на K Коэффициент изменения численности

* Влияние численности и внешней среды A – коэффициент устойчивости вида B – коэффициент среды обитания Варианты: устанавливается постоянная численность постоянно меняется (колебания) вымирание

* Влияние других видов Ni – численность белок, Mi – численность бурундуков K2, K4 – взаимное влияние если K2 >K1 или K4 >K3 – враждующие виды

* Моделирование двух популяций

* Конец фильма