Работа в Excel 2007

Работа в 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

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

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

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

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

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

Некоторые функции
СУММПРОИЗВ – сумма произведений двух массивов

Некоторые функции
СЧЁТ – количество числовых ячеек
СЧЁТЕСЛИ – количество ячеек, удовлетворяющих заданному условию (2-ой параметр)

Некоторые функции
СУММЕСЛИ – сумма ячеек диапазона, удовлетворяющих условию, которое накладывается на другой диапазон
Найти общий вес учащихся 9-А класса.
диапазон для проверки условия
условие
диапазон для суммирования

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

Логические операции
НЕ – обратное условие, НЕ(B2<10) ⇔ ?
И – одновременное выполнение всех условий
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 <= 20
B2:B8 >= 5
A1 = целое

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

МНК для линейной функции
неизвестно!
a
-b
c

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

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

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

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

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

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

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

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

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

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

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