Графические возможности Excel

6.1.5. Графические  возможности Excel

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

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

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

 В зависимости от  места расположения и особенностей  построения и редактирования  различают два вида диаграмм:

- внедренные диаграммы  - помещается на том же рабочем  листе, где и данные, по которым  они построены;

- диаграммы в формате  полного экрана на новом рабочем  листе.

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

 Удобным средством  для создания графических представлений  в Excel является Мастер диаграмм, который вызывается специальной кнопкой на панели Стандартная или из меню Вставка > Диаграмма

 

 

6.2. Экономико-математические  приложения MS Excel

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

 Среди типичных экономико-математических приложений Excel могут быть названы:

- структуризация и первичная  логическая обработка данных;

- статистическая обработка  данных, анализ и прогнозирование;

 - проведение финансово-экономических расчетов;

- решение уравнений и  оптимизационных задач.

 

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

 

Статистическая  обработка данных, анализ и прогнозирование

 Функции, реализующие  статистические методы обработки  и анализа данных, в Excel реализованы в виде специальных программных средств - надстройки Пакета анализа, которая входит в поставку Microsoft Office и может устанавливаться по желанию пользователей. Установка надстройки Пакет анализа осуществляется так же, как и установка других надстроек с помощью команды Сервис/Надстройка. Далее необходимо установить флажок перед пунктом Пакет анализа и нажать ОК.

 После успешной установки  надстройки в меню Сервис появится  пункт: Анализ данных, а в окне  мастера функций становится доступной  категория функций - Статистические.

 Целью статистического  исследования является обнаружение  и исследование соотношений между  статистическими (экономическими) данными  и их использование для изучения, прогнозирования и принятия решений.  Фундаментальным понятием статистического  анализа являются понятия вероятности  и случайной величины. Excel не предназначен для комплексного статистического анализа и обработки данных, но с помощью команд, доступных из окна Анализ данных можно провести:

описательный стстистический анализ (описательная статистика);

ранжирование данных (Ранг и персентиль);

графический анализ (Гистограмма);

прогнозирование данных (Скользящее среднее. Экспоненциальное сглаживание);

регрессионный анализ (Регрессия) и т.д.

 

Проведение финансово-экономических  расчетов

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

 В Excel реализованы встроенные и дополнительные финансовые функции. Для применения дополнительных функций необходимо установить надстройку Пакет анализа.

 По типу решаемых  задач все финансовые функции  Excel можно разделить на условные группы:

функции для анализа инвестиционных проектов;

функции для анализа ценных бумаг;

функции для расчета амортизационных  платежей;

вспомогательные функции.

 

 Применение функций  для финансовых расчетов осуществляется  с помощью мастера функций  (Вставка/Функция, выбрать категорию  Финансовые)

 

Для решения задач  оптимизации широкое променение находят различные средства Excel.

 В этом разделе рассмотрим  команды:

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

Надстройку Поиск решения  для расчета оптимальной величины по нескольким переменным и ограничениям;

Диспетчер сценариев для  создания и оценки наборов сценариев  «что – если» с несколькими  вариантами исходных данных.

 

 

 

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

 

 

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

1) выполнить команду меню Данные > Сортировка;

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

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

 Ограничимся кратким  описанием последовательности действий:

1. Выделить всю область  списка (включая заголовок).

2. Выполнить команду меню  Данные > Фильтр > Автофильтр, после чего в строке заголовков появятся кнопки с раскрывающимися списками значений.

3. С помощью появившихся  элементов управления кнопок  могут быть заданы стандартные  критерии отбора (Все, Первые 10..., все строки с конкретным значением)

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

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

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

Важно.   Для получения более точных результатов рекомендуется не смешивать водном столбце данные разных форматов, например текст и числа, числа и даты, поскольку для каждого столбца может использоваться только один тип команды фильтра. Если в столбце представлено несколько форматов, отображена будет команда для преобладающего формата. Например, если столбец содержит три значения в числовом формате и четыре — в текстовом, то отображается команда фильтра Текстовые фильтры.

 

 

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

Форматирование  всех ячеек с использованием двухцветной  шкалы

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

Проблема: условное форматирование, примененное  к ячейкам в диапазоне, не отображается.

Если одна или  несколько ячеек в диапазоне  содержат формулу, возвращающую ошибку, условное форматирование не применяется  ко всему диапазону. Чтобы гарантировать  применение условного форматирования ко всему диапазону, воспользуйтесь функцией «ЕСТЬ» или «ЕСЛИОШИБКА» для  возврата значения, отличного от ошибки.

Быстрое форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.

На вкладке Начальная страница в группе Стили щелкните стрелку рядом с пунктом Условное форматирование, а затем выберите команду Шкалы цветности.

 

 

 

Формулы

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

 Некоторые операции  в формуле имеют более высокий  приоритет и выполняются в  такой последовательности:

возведение в степень  и выражения в скобках;

умножение и деление;

сложение и вычитание.

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

 Внесение изменений  в формулу

 Для внесения изменений  в формулу щелкните мышью на  строке формул или клавишу  F2. Затем внесите изменения и нажмите кнопку Ввода в строке формул или клавишу Enter. Если вы хотите внести изменения в формулу непосредственно в ячейке, где она записана, то дважды щелкните мышью на ячейке с этой формулой. Для отмены изменений нажмите кнопку Отмена в строке формул или клавишу Esc.

 Использование ссылок

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

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

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

 При копировании формул  можно управлять изменением адресов  ячеек или ссылок. Если перед  всеми атрибутами адреса ячейки  поставить символ “$” (например, $A$1), то это будет абсолютная ссылка, которая при копировании формулы не изменится. Изменятся только те атрибуты адреса ячейки, перед которыми не стоит символ “$”, т.е. относительные ссылки. Для быстрой установки символов “$” в ссылке ее необходимо выделить в формуле и нажать клавишу F4.

 

 

Функции Excel

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

 Функции состоят из  двух частей: имени функции и  одного или нескольких аргументов. Имя функции описывает операцию, которую эта функция выполняет,  например, СУММ.

 Аргументы функции  Excel - задают значения или ячейки, используемые функцией, они всегда заключены в круглые скобки. Открывающая скобка ставится без пробела сразу после имени функции. Например, в формуле «=СУММ(A2;A9)», СУММ — это имя функции, а A2 и A9  — ее аргументы.

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