Обработка и анализ табличных данных. Табличный процессор. Статистическая обработка данных

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

Для копирования  формулы подведите указатель  мыши к тому месту границы ячейки или блока, где изображение указателя изменяется с белого крестика на белую стрелку. Затем нажмите клавишу Ctrl и левую кнопку мыши и перемещайте ячейку в нужное место таблицы. Для завершения копирования отпустите кнопку мыши и клавишу Ctrl. Если в записи формулы есть относительные адреса ячеек, при копировании формулы они изменятся. Р аспространение формул. Помимо копирования и перемещения формулу можно распространить на часть строки или столбца. При этом происходит изменение относительных ссылок. Для распространения формулы необходимо выполнить следующие действия: 
1. Установите курсор в ячейку с формулой.

2. Подведите указатель мыши к маркеру заполнения. Изображение указателя изменяется на черный крестик.

3. Нажмите левую кнопку мыши и, удерживая ее нажатой, перемещайте курсор до нужного места. Для завершения распространения формулы отпустите кнопку.

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

Функции Excel

Функции Excel —  это специальные, заранее созданные  формулы для сложных вычислений, в которые пользователь должен ввести только аргументы. Функции состоят  из двух частей: имени функции и одного или нескольких аргументов. Имя функции описывает операцию, которую эта функция выполняет, например, СУММ. Аргументы функции Excel - задают значения или ячейки, используемые функцией, они всегда заключены в круглые скобки. Открывающая скобка ставится без пробела сразу после имени функции. Например, в формуле «=СУММ(A2;A9)», СУММ — это имя функции, а A2 и A9  — ее аргументы. Эта формула суммирует числа в ячейках A2, и A9. Даже если функция не имеет аргументов, она все равно должна содержать круглые скобки, например функция ПИ(). При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. В функции можно использовать до 30 аргументов. Ввод функций в рабочем листе. Вы можете вводить функции в рабочем листе прямо с клавиатуры или с помощью команды Функция меню Вставка. Если вы выделите ячейку и выберете команду Вставка/Функция, Excel выведет окно диалога Мастер функций – шаг 1 из 2. Открыть это окно можно также с помощью кнопки Вставка функции на строке ввода формул.

 

 

В этом окне сначала  выберите категорию в списке Категория  и затем в алфавитном списке Функция  укажите нужную функцию.

Excel введет знак  равенства (если вы вставляете  функцию в начале формулы), имя  функции и круглые скобки. Затем  Excel откроет второе окно диалога мастера функций, в котором необходимо установить аргументы функции (в нашем случае ссылки на A2 и A9).

 

 

Второе окно диалога Мастера функций содержит по одному полю для каждого аргумента  выбранной функции. Справа от каждого  поля аргумента отображается его текущее значение (21 и 33). Текущее значение функции отображается внизу окна диалога (54). Нажмите кнопку ОК или клавишу Enter, и созданная функция появится в строке формул.

 

Диаграммы в Excel

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

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

 

 

Значения оси. Excel создает значения оси на основе указанных данных. В данном случае значения оси изменяются от 0 до 70, что соответствует значениям ячеек диапазона на листе.

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

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

Маркеры данных. Маркеры данных одного цвета представляют один ряд данных. Каждый маркер соответствует  одному значению данных листа. В приведенном  примере самый правый маркер данных соответствует фактическому значению за четвертый квартал, равному 60.

Подсказки. При  остановке указателя над каким-либо элементом диаграммы появляется подсказка с названием элемента. Например, при остановке указателя  над легендой появляется подсказка «Легенда».

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

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

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

Шаг 1. Выбор  типа диаграммы.

Первое окно диалога Мастера диаграмм, предлагает выбрать тип диаграммы.

 

Это окно диалога  содержит две вкладки: одну для стандартных  и другую для нестандартных типов  диаграмм.

Шаг 2. Задание  исходных данных диаграммы.

Во втором окне диалога мастера диаграмм можно  задать данные, используемые Excel при построении диаграммы.

 

Второе окно диалога Мастера диаграмм позволяет  задать исходный диапазон и расположение в нем рядов данных. Если перед  запуском Мастера был выделен  диапазон с исходными данными, то это поле будет содержать ссылку на выделенный диапазон.

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

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

Шаг 3. Задание  параметров диаграммы.

Третье окно диалога Мастера диаграмм содержит шесть вкладок. Они позволяют  задать характеристики осей, название диаграммы и заголовки для  ее осей, легенду, подписи значений в рядах данных и т.д. Все это  можно выполнить при создании диаграммы или после ее построения.

 

Шаг 4. Размещение диаграммы. Excel может внедрить диаграмму  в рабочий лист или помесить ее на отдельном листе, так называемом листе диаграммы.

 

 

После построения диаграммы ее можно отредактировать  в режиме редактирования диаграммы. Для этого нужно дважды щелкнуть кнопку мыши на диаграмме или воспользоваться контекстным меню.

Сортировка  и фильтрация данных.

К средствам, которые  предназначены для обработки  и анализа данных в списке относятся  команды из меню Данные: Сортировка, Фильтр, Форма, Итоги, Проверка. При выполнении этих команд, редактор автоматически распознает список как базу данных и осуществляет обработку и анализ данных в списке как в базе данных.

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

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

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

 

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

 

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

• описательный стстистический анализ (описательная статистика);
• ранжирование данных (Ранг и персентиль);
• графический анализ (Гистограмма);
• прогнозирование данных (Скользящее среднее. Экспоненциальное сглаживание);
• регрессионный анализ (Регрессия) и т.д.  
   
  Статистические функции для регрессионного анализа из категории Статистические в окне мастера функций:
• ЛИНЕЙН(знач. У; знач. Х; константа; стат.) - Определяет параметры линейного тренда для заданного массива;
• ТЕНДЕНЦИЯ(знач. У; знач. Х; новые знач. Х; константа;) - Определяет предсказанные значения в соответствии с линейным трендом для заданного массива (метод наименьших квадратов) и многие другие.

 

Создание сценария

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

•  Выполните команду Сервис/Сценарии. Открывается изображение окна диалога Диспетчер сценариев.

 

 

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

 

 

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

•  Нажмите кнопку Добавить, чтобы создать второй сценарий. Введите название Худший вариант. После завершения создания двух сценарием можно приступить к просмотру результатов.

•  Закройте окно диалога Диспетчер сценариев кнопкой Закрыть.

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

•  Выполните команду Сервис/Сценарии. Открывается окно диалога:

•  Выберите из списка сценарий для просмотра.

•  Нажмите кнопку Вывести. Excel заменяет содержимое ячеек листа значениями из сценария и отображает результаты на листе.

•  Выберите из списка другие сценарии и воспользуйтесь кнопкой Вывести для сравнения результатов моделей «что – если». После завершения нажмите кнопку Закрыть. Значения последнего активного сценария остаются в ячейках листа.

Создание отчетов  по сценарию

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

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

•  Выполните команду Сервис/Сценарии. Откроется окно диалога Диспетчер сценариев.