Практическое занятие № 6

Тема: Табличный процессор MS Excel.

План:

1. Электронные таблицы и их возможности.

2. Программа Microsoft Excel. Внешний вид окна MS Excel.

3. Структура документа.

4. Адресация ячеек.

5. Работа в программе MS Excel.

6. Математические вычисления.

7. Графики, диаграммы, построение линии тренда.

8. Решение фининсовых задач, поиск решения.

1. Электронные таблицы и их возможности

Компьютерные программы, предназначенные для хранения и обработки данных, представленных в табличном виде, называются электронными таблицами. Основное назначение электронных таблиц состоит в хранении и обработке числовой информации. Основное отличие электронных таблиц от таблиц баз данных состоит в том, что в них удобнее реализованы автоматические вычисления значений в ячейках. Первые программы электронных таблиц появились вместе с персональным компьютером (ЭТ Суперкалк).

Наиболее распространенными средствами управления электронными таблицами являются программы Calk (OpenOffice) и Excel (Microsoft).

Возможности современных электронных таблиц

1.Поиск и сортировка данных.

2.Удобные средства для создания диаграмм, имеющих наглядный вид.

3.Статистическая обработка данных.

4.Создание таблиц со встроенными ссылками и гиперссылками.

5.Поиск оптимального решения.

6.Использование как базы данных и т.д.

Анализ и прогноз

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

2. Программа Microsoft Excel

Программа Microsoft Excel входит в пакет Microsoft Office и предназначена для подготовки и обработки электронных таблиц под управлением ОС Windows.

Возможности MS Excel

ü Редактор формул, включая самые сложные математические формулы.

ü Статистическая обработка данных.

ü Создание таблиц с встроенными ссылками и гиперссылками.

ü Быстрый и точный подсчет больших массивов чисел.

ü Использование как простой базы данных (с операциями сортировки, выборки, импорта-экспорта информации).

Запуск, завершение программы Microsoft Excel, а также создание, открытие, загрузка и сохранение производится стандартным способом.

Внешний вид окна MS Excel

Окно программы содержит стандартные элементы управления: 1) Строку заголовка; 2) Системные кнопки; 3) Горизонтальное меню; 4) Панели инструментов; 5) Строку формул, в которой отображен адрес и содержимое текущей ячейки; 6) Полосы прокрутки; 7) Строку состояния; 8) Рабочую площадь.

Пункты горизонтального меню программы Excel

ü Файл – содержит команды по созданию, открытию существующих файлов, сохранению вновь созданных файлов; вывод документов на печать с определением параметров и предварительным просмотром документов перед печатью.

ü Правка – содержит команды по редактированию таблицы: копирование, удаление, очистка, вставка (части таблицы или таблиц целиком), удаление или вставка рабочих листов книги.

ü Вид – команды, определяющие внешний вид окна программы Excel (наличие строки состояния, строки формул, панели инструментов).

ü Вставка – столбцов, строк, диаграмм, мастера функций (статистика), макросов (выполнение одних и тех же команд, возможность программирования).

ü Формат – определение высоты и ширины строк, столбцов; формат ячейки (числовой, текстовый, денежный, % и т.д.).

ü Сервис – команды по созданию баз данных (определение зависимостей, ссылок и гиперссылок, настройка параметров данных, вычислений, печати т.д.).

ü Данные – команды по обработке базы данных (сортировка, фильтрация, поиск, подведение итогов), определение консолидации ЭТ, т.е. связи многих таблиц между собой.

ü Окно – команды по внешнему виду окна документа (возможность разбиения основного окна на 4 автономных).

ü Справка(?) – справочная информация по Excel.

3. Структура документа MS Excel

Документ Excel представляет собой книгу, состоящую из таблиц, называемых листами. Книга может содержать до 255 листов. Каждый лист можно переименовывать, выполнив ДвЩЛКМ по ярлычку листа и затем осуществив ввод нового имени. Список возможных действий с листом (переименование, добавление, перемещение, удаление и т.д.) появляется на экране после ЩПКМ по ярлычку листа или через команды меню «Вставка» и «Правка».

Столбцы таблицы обозначаются заглавными латинскими буквами, а строки нумеруются целыми числами. На пересечении столбцов и строк образуются ячейки. Ячейка – это основной структурный элемент электронных таблиц. Каждая ячейка может содержать до 256 символов. Каждая ячейка имеет свой адрес (например, А1; СВ14105).

Формат указания адреса ячейки называется ссылкой. Всегда на рабочем листе одна из ячеек является текущей. Она обведена жирной рамкой и с ней в данный момент работает пользователь.

Диапазоны

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

Ячейка воспринимает информацию трех видов –

ü Числовая – числа интерпретируются как константы, автоматически выравниваются по правому краю ячейки;

ü Текстовая – любая последовательность, состоящая из цифр, пробелов и нецифровых символов, автоматически выравнивается по левому краю ячейки;

ü Формула– математическое выражение, начинающееся со знака равно «=» и определяющее порядок вычисления значения в ячейке на основе значений из других ячеек. Ссылки на ячейки можно задать разными способами. По умолчанию ссылки на ячейку в формулах рассматриваются как относительные.

4. Адресация ячеек

Адресация может быть абсолютной, относительной и смешанной.

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

Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, используются абсолютные ссылки, для ее создания используется знак ($), например абсолютная ссылка на ячейку В11 выглядит так – $В$11.

Если знак доллара стоит в таких вариантах, как A$4 или $A4, то адресация называется смешанной. Та часть адреса, у которого стоит знак доллара, при копировании формулы не изменяется, а та часть, где нет знака доллара, настраивается на новое местоположение.

5. Работа в программе MS Excel

Выделение диапазонов

- выделение столбца – ЩЛКМ на названии столбца;

- выделение строки – ЩЛКМ на номере строки;

- выделение диапазона внутри листа производится белым крестом Ì.

Копирование диапазонов

Выделить копируемый диапазон → Меню «Правка» ® Команда «Копировать» → выделить одну ячейку с которой начинается диапазон вставки копии → Меню «Правка» ® Команда «Вставить».

Изменение размеров строк и столбцов

Навести белый крест между названиями столбцов или между номерами строк, когда он превратится в двойную стрелку, ЩЛКМ и удерживая ее увеличить или уменьшить строку (столбец).

Вставка строк и столбцов

Выделить столбец (строку) → Меню «Вставка» → Команда «Столбцы» («Строки») → ЩЛКМ.

Редактирование содержимого ячейки

Выделить ячейку → установить текстовый курсор в строку формул или нажать клавишу F2 → ввести изменения → «Enter».

Форматирование содержимого ячейки

Выделить ячейку → Меню «Формат» → Команда «Ячейки…» → установить требуемое «выравнивание» (например. По горизонтали и по вертикали – «По-центру», переносить по словам) → установить необходимый «шрифт» → установить другие необходимые форматы → «ОК».

Автоматическое заполнение списков

Набрать первые два пункта списка → выделить эти две ячейки (белым крестом Ì) → растянуть список до нужной позиции (чёрным крестом É).

6. Математические вычисления

Создание формул

Выделить ячейку, где будет располагаться результат формулы → напечатать знак равно «=» → ЩЛКМ в ячейке, содержащей первую переменную из формулы → с клавиатуры NumLock ввести нужный знак операции (+; -; /; *; ^) → ЩЛКМ в ячейке, содержащей вторую переменную из формулы и т.д. → в конце создания формулы проверить её синтаксис → «Enter».

Автосуммирование

Выделить ячейку, где будет располагаться результат суммы → ЩЛКМ по значку Автосуммы на панели инструментов « »→ на рабочем листе выделить диапазон суммируемых значений (белым крестом) → «Enter» .

Вычисления с помощью мастера функций

Выделить ячейку, где будет располагаться результат функции → щелкнуть значок Мастера функций в строке формул – « »→ в диалоговом окне «шаг 1 из 2» выбрать нужную категорию и имя функции. (Например, категория: «Статистические», функция «СРЗНАЧ») →

→ прочитать описание выбранной функции → «ОК» → в следующем окне «Аргументы функции» ввести необходимые данные, для этого на рабочем листе белым крестом растянуть диапазон нужных значений →

→ внимательно и аккуратно проверить введённые адреса ячеек → «ОК».

Так как по названию функции трудно определить, что именно будет считать «Мастер функций», поэтому в диалоговых окнах всегда написано подробное описание выбранной функции, и пренебрегать чтением всего, что пишет пользователю программа нельзя.

7. Графики, диаграммы, построение линии тренда

В программе Excel термин «диаграмма» используется для обозначения всех видов графического представления числовых данных. Диаграмма представляет собой вставной объект, внедренный на лист рабочей книги. Диаграмма сохраняет связь с данными, на основе которых она построена, и при обновлении этих данных немедленно изменяет свой вид.

Создание и оформление диаграммы

Выделить пустую ячейку (несоприкасающуюся с какими-либо данными) → ЩЛКМ значок «Мастер диаграмм» на панели инструментов «Стандартная» → в диалоговом окне «шаг 1 из 4» выбрать нужные тип и вид диаграммы → ЩЛКМ → в диалоговом окне «шаг 2 из 4» ввести необходимые данные, для этого на рабочем листе белым крестом растянуть диапазон нужных значений (дополнительные данные и подписи можно задать на вкладке Ряд) → ЩЛКМ → в диалоговом окне «шаг 3 из 4» выбрать оформление диаграммы →ЩЛКМ → в последнем окне «шаг 4 из 4» выбрать размещение диаграммы → ЩЛКМ .

Готовую диаграмму можно изменить. Она состоит из набора отдельных элементов. Форматировать каждый элемент можно через контекстное меню этого элемента. Для редактирования или форматированиядиаграммы ДвЩЛКМ по ней → меню «Диаграмма» → команда «Тип диаграммы» или «Параметры диаграммы».

Если же требуется внести в диаграмму существенные изменения, то следует вновь воспользоваться мастером диаграмм.

Построение линии тренда

С помощью MS Excel можно проводить автоматический анализ тренда на основе диаграмм. Линию тренда можно добавить к ряду данных в том случае, если они представляют собой диаграмму с областями, график, гистограмму, линейчатую или точечную диаграмму. Предлагается выбрать одну из пяти типов аппроксимирующих линий или вычисление линии, показывающей скользящее среднее. Для добавления линии тренда к ряду данных диаграммы необходимо: выделить диаграмму, для данных которой нужно добавить линию тренда → Меню «Диаграмма» → «Добавить линию тренда»→ в окне «Линия тренда» отметьте ряд, для которого строится линия тренда, и выберите тип аппроксимации (вкладка Тип), а также установите параметры линии тренда (вкладка Параметры) → «ОК».

8. Решение финансовых задач, поиск решения

Встроенные экономические функции MS Excel служат для проведения соответствующих расчетов (нахождение платы по процентам, расчет регулярных выплат по займу, оценка эффективности капиталовложений и т.д.). Финансовые функции MS Excel предназначены для вычисления базовых величин, необходимых при проведении сложных расчетов. Для вывода полного перечня финансовых функций нужно установить флажок Пакет анализа(меню Сервис → команда Надстройки → список Надстройки). Нахождение решений финансовых задач выполняется с помощью мастера функций (см. страница 4), но используется категория: «Финансовые».

Пример. Ожидается, что ежегодные доходы от реализации проекта составят 54 000 000 руб. рассчитать срок окупаемости проекта, если инвестиции к началу поступления доходов составили 140 000 000 руб., а норма дисконтирования 7,67%. Воспользоваться функцией КПЕР.

Решение. Для определения срока платежа используется функция

КПЕР (Ставка; Плт; Пс; Бс; Тип),

которая в задаче выглядит следующим образом:

КПЕР (7,67%; 54000000; -140000000) = 3.

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

Программа Поиск решения является инструментом оптимизации. Она позволяет найти оптимальное или заданное значение некоторой ячейки путем подбора значений нескольких ячеек так, чтобы они удовлетворяли нескольким граничным условиям.

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

Изменяемые – ячейки, от которых зависит значение целевой ячейки.

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

Для установки программы Поиск решения: меню «Сервис» → команда «Надстройки», для вызова: меню «Сервис» → команда «Поиск решения».

В поле Установить целевую ячейку вводится адресс или имя ячейки, в которой находится формула оптимизируемой модели.

Для макимизации/минимизации значения целевой ячейки путем изменения значений влияющих ячеек установить Максимальное значение/ минимальное значение. Если значение в целевой ячейке должны быть равным некоторому числу, то выбрать положение Значение и ввести нужное число.

В поле Изменяя ячейки вводятся имена или адреса изменяемых ячеек через запятую. Эти ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Кнопка Предположить используется для автоматического нахождения всех ячеек, влияющих на формулу модели.

В поле Ограничения вводятся все ограничения, накладываемые на поиск решения. Кнопка Выполнить инициирует процесс решения. Через некоторое время в диалоговом окне появится сообщение о том, что решение найдено или нет.

При выборе Тип отчета → Результаты, в рабочей книге появится новый лист «Отчет по результатам 1». В нем приводится исходное и результирующее значкния целевой функции и изменяемых ячеек (аргументов), а также информация по каждому ограничению о том, является оно связанным в оптимальной точке или нет (ограничение <= или >= называется связанным, если оно превращается в точное равенство).

Для сохранения найденного решения в окне Результаты поиска решения выбрать Сохранить найденное решение. Для восстановления исходных данных → Восстановить исходные значения.