Формулы

Обработка данных средствами электронных таблиц.

СОДЕРЖАНИЕ

Обработка данных средствами электронных таблиц. 2

Формулы.. 5

Ссылки. 6

Автоматизации ввода. 6

Выделение ячеек и диапазона ячеек. 8

Выделение ячеек по заданным критериям.. 9

Перемещение и копирование. 10

Перенос (копирование) данных из одной книги в другую.. 10

Перенос (копирование) данных с одного листа на другой в пределах одной книги. 11

Специальная вставка. 11

Транспонирование таблицы.. 11

Оформление таблицы.. 12

Мастер условного форматирования. 12

Использование стилей. 13

Создание иерархической структуры таблицы.. 13

Вычисления. 14

Арифметические операторы: 15

Текстовый оператор: 15

Операторы отношения: 15

Мастер формул. 16

Ошибки в формулах. 16

Построение диаграмм и графиков. 17

Работа со списками.. 19

Редактирование записей. 20

Поиск записей. 20

Анализ данных. 21

Обобщение данных. 21

Сортировка. 22

Фильтрация. 23

Автофильтрация. 23

Расширенная фильтрация. 24

Итоги. 27

Консолидация данных. 30

Сводная таблица. 33

Анализ и оптимизация. 36

Подбор параметра. 36

Пример 1. Процент по вкладу. 36

Пример 2. Бухгалтерский расчет. 37

Пример 3. Решение уравнений. 39

Поиск решения. 39

Решение систем нелинейных уравнений. 40

Решение систем линейных уравнений. 42

Задачи линейного программирования. 42

Задача распределения ресурсов. 42

Транспортная задача. 47

Аппроксимация зависимостей. 49

Линейная регрессия. 50

Полиномиальная аппроксимация. 54

Работа с макросами.. 58

Макрорекордер. 58

Безопасность макросов. 60

Создание функций, определенных пользователем.. 60

Для представления данных в удобном виде используются таблицы. Компьютер позволяет представлять их в электронной форме, а это дает возможность не только отображать, но и обрабатывать данные. Класс программ, используемых для этой цели, называется электронными таблицами.

Особенность электронных таблиц заключается в возможности применения формул для описания связи между значениями различных ячеек. Расчет по заданным формулам производится автоматически. Изменение содержимого какой-либо ячейки приводит к пересчету значений всех ячеек, которые с ней связаны формульными отношениями и, тем самым, к обновлению всей таблицы в соответствии с изменившимися данными.

Применение электронных таблиц упрощает работу с данными и позволяет получать результаты без проведения расчетов вручную или специального программирования. Наиболее широкое применение электронные таблицы нашли в экономических и бухгалтерских расчетах, но и в научно-технических задачах электронные таблицы можно использовать эффективно, например для:

· проведения однотипных расчетов над большими наборами данных;

· автоматизации итоговых вычислений;

· решения задач путем подбора параметров, табулирования формул;

· обработки результатов экспериментов;

· проведения поиска оптимальных значений параметров;

· подготовки табличных документов;

· построения диаграмм и графиков по имеющимся данным.

Одним из наиболее распространенных средств работы с документами, имеющими табличную структуру, является программа Microsoft Excel.

Программа Excel предназначена для работы с таблицами данных, преимущественно числовых. При формировании таблицы выполняют ввод, редактирование и форматирование текстовых и числовых данных, а также формул. Наличие средств автоматизации облегчает эти операции. Созданная таблица может быть выведена на печать.

Документ Excel называется рабочей книгой. Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет табличную структуру и может содержать одну или несколько таблиц. В окне документа отображается только текущий лист, с которым и ведется работа. Каждый рабочий лист имеет название, которое отображается на ярлычке листа, отображаемом в его нижней части. С помощью ярлычков можно переключаться к другим рабочим листам, входящим в ту же самую книгу. Чтобы переименовать рабочий лист, надо дважды щелкнуть на его ярлычке.

Книга Excel содержит листы 5 типов:

1. рабочие листы

2. листы диаграмм

3. модули Visual Basic

4. листы диалога

5. листы макросов Microsoft Excel.

Листов может быть до 255.

Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего рабочий лист может содержать до 256 столбцов. Строки последовательно нумеруются цифрами, от 1 до 65 536.

На пересечении столбцов и строк образуются ячейки таблицы. Они являются минимальными элементами для хранения данных. Обозначение отдельной ячейки сочетает в себе номера столбца и строки (в этом порядке), на пересечении которых она расположена, например: A1 или DE234. Обозначение ячейки (ее номер) выполняет функцию ее адреса. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках. Одна из ячеек всегда является активной и выделяется рамкой активной ячейки. Эта рамка в программе играет роль курсора. Операции ввода и редактирования всегда производятся в активной ячейке. Переместить рамку активной ячейки можно с помощью курсорных клавиш или указателя мыши. На данные, расположенные в соседних ячейках, можно ссылаться в формулах, как на единое целое. Такую группу ячеек называют диапазоном. Диапазон ячеек обозначают, указывая через двоеточие номера ячеек, расположенных в противоположных углах прямоугольника, например: A1:C15. Если нужно выделить прямоугольный диапазон ячеек, это можно сделать протягиванием указателя от одной угловой ячейки до противоположной по диагонали. Чтобы выбрать строку или столбец целиком, следует щелкнуть на заголовке строки (столбца). Протягиванием указателя по заголовкам можно выбрать несколько идущих подряд столбцов или строк.

Отдельная ячейка может содержать данные, относящиеся к одному из трех типов: текст, число и формула, - а также оставаться пустой.

Ввод данных осуществляется непосредственно в текущую ячейку или в строку формул, располагающуюся в верхней части окна программы непосредственно под панелями инструментов. Место ввода отмечается текстовым курсором. Чтобы завершить ввод, сохранив введенные данные, используют кнопку Enter в строке формул или клавишу Enter.Чтобы отменить внесенные изменения и восстановить прежнее значение ячейки, используют кнопку Отмена в строке формул или клавишу ESC. Для очистки текущей ячейки или выделенного диапазона проще всего использовать клавишу Delete. Текстовые данные по умолчанию выравниваются по левому краю ячейки, а числа - по правому. Чтобы изменить формат отображения данных в текущей ячейке или выбранном диапазоне, используют команду Формат®Ячейки. Вкладки этого диалогового окна позволяют выбирать формат записи данных (количество знаков после запятой, указание денежной единицы, способ записи даты и т.д.), задавать направление текста и метод его выравнивания, определять шрифт и метод начертания символов, управлять отображением и видом рамок, задавать фоновый цвет.

Для перемещения по листам рабочей книги используются следующие сочетания клавиш.

Вычисление в таблицах программы Excel осуществляется при помощи формул. Формула может содержать числовые константы, ссылки на ячейки и функции Excel, соединенные знаками математических операций. Если ячейка содержит формулу, то в рабочем листе отображается текущий результат вычислений по этой формуле. Правило использования формул в программе состоит в том, что, если значение ячейки действительнозависит от других ячеек таблицы, всегда следует использовать формулу, даже если операцию легко можно выполнить «в уме».