birmaga.ru
добавить свой файл

1 2

Лабораторная работа 5-6. MS EXCEL. Обработка и анализ данных

Форматирование таблицы

Форматирование таблицы – это изменение ее внешнего вида без изменения содержимого ячеек. К форматированию относится изменение ширины столбцов и высоты строк; установка: цвета фона в ячейках; цвета, размера, стиля и начертания шрифта; типа, ширины и цвета границ; формата отображения содержимого ячеек и т.д. Все команды, относящиеся к форматированию ячеек, собраны в меню Формат/Ячейки (рисунок 1).

Вкладка Число отвечает за формат отображаемых данных (дата, денежный, процентный, числовой и т.д.)

вкладка Выравнивание отвечает за выравнивание, ориентацию и отображение текста в ячейках

вкладка Шрифт – за параметры шрифта текста

вкладка Граница настраивает параметры границ выделенных ячеек

вкладка Вид – заливка выделенных ячеек выбранным цветом или узором

вкладка Защита позволяет установить параметры защиты листа.

Также меню Формат позволяет установить параметры строк и столбцов (скрытие, ширину и высоту), листа (подложка, цвет ярлыка, переименование, скрытие), применить Автоформат и установить условное форматирование.

Условное форматирование – это форматирование ячейки в зависимости от некоторого условия.


Рисунок 1. Диалоговое окно Формат ячеек
Если при работе ячейки были отформатированы неправильно, то можно удалить форматирование, выбрав Правка/Очистить/Форматы – в этом случае данные сохранятся, а форматирование будет удалено.

Формулы и функции

Для расчетов в Excel часто используют формулы. Формулы вводятся в ячейки и всегда начинаются со знака равенства, после которого следуют числовые данные и ссылки на ячейки (содержащие данные), объединенные знаками операций (+ - умножение, - - вычитание, * - умножение, / - деление, ^ - возведение в степень), например, =5+A3+2,6^3.


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

Ссылки бывают двух типов: абсолютные и относительные.

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

Абсолютные ссылки при копировании формул не изменяются, их признаком является наличие знака доллара перед именем столбца и номером строки, например, $A$5, $F$67.

Применяется также и смешанная адресация, при которой часть ссылки представляет собой относительную, а вторая абсолютную ссылку, например, A$5 (при копировании формулы будет изменяться только ссылка на столбец), $IV89 (при копировании формулы будет изменяться ссылка на строку).

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

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

На втором шаге Мастера функций задаются параметры выбранной функции (рисунок 3). Для вставки нескольких функций в одну формулу и перехода между их мастерами используют панель формул (рисунок 4).



Рисунок 2. Диалоговое окно первого шага Мастера функций


Рисунок 3. Диалоговое окно функции ЕСЛИ




Рисунок 4. Работа с несколькими функциями

Пример использования формул и функций Excel для расчетов и решения задач


В качестве примера найдем корни квадратных уравнений: , , .

На первом шаге приготовим расчетную таблицу:



На втором шаге в ячейку D2 введем формулу для расчета дискриминанта: =B2^2-4*A2*C2. Для ввода ссылок используют клавиатуру для ввода знаков арифметических операций и мышь для указания ссылок на ячейки. Скопируем формулу на ячейки D3:D4.

На третьем шаге для ячейки E2 вызовем функцию ЕСЛИ для подсчета значения первого корня (рисунок 3). Скопируем формулу на ячейки E3:E4. Аналогично найдем значения второго корня.

Сортировка данных


Для сортировки данных таблицы необходимо выделить ее вместе с заголовком и применить команду Данные/Сортировка и в открывшемся диалоговом окне указать параметры сортировки.

Подведение итогов


Microsoft Excel может автоматически вычислять промежуточные и общие итоги. При вставке автоматических промежуточных итогов Microsoft Excel изменяет разметку списка, что позволяет отображать и скрывать строки каждого промежуточного итога (рисунок 5).

Перед тем как вставить промежуточные итоги, необходимо отсортировать список, чтобы сгруппировать строки, по которым нужно подвести итоги.

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


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







Рисунок 5. Подведение итогов

Автофильтр


С помощью фильтров можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям.

Excel позволяет быстро и удобно просматривать требуемые данные из списка с помощью простого средства – автофильтра.

Чтобы использовать автофильтр, надо сначала выделить область списка для поиска с заголовками полей. Затем выполнить команду Данные/Фильтр/Автофильтр.

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

Расширенный фильтр


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

Для выполнения фильтрации при помощи Расширенного фильтра надо выполнить следующие действия:

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


  1. Сформировать шапку выходного документа. Это означает, что следует скопировать в свободное место рабочего листа имена только тех полей списка, которые определяют вид выходного документа (рисунок 6).

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

Построение диаграмм


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

Создать диаграмму в Excel можно по шагам с помощью Мастера диаграмм, вызов которого осуществляется при помощи команды Вставка/Диаграмма.

Сначала Мастер диаграмм предлагает выбрать тип диаграммы (шаг 1)

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

На шаге 3 определяются основные параметры диаграммы (легенда, заголовки, линии сетки и т.д.).

На последнем шаге (шаг 4) необходимо выбрать место расположения диаграммы: на новом листе или на текущем.

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

Трендовый анализ

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


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

  2. Выделяется ряд данных для построения тренда, щелчком по линии графика (выделение будет произведено черными квадратиками);

  3. На линии графика вызывается контекстное меню, выбирается команда Вставка/Линия тренда. В диалоговом окне выбирается вид линии тренда: линейный, полиномиальный, степенной и др. Для осуществления прогноза и вывода на диаграмму уравнения линии тренда используется вкладка Параметры.

Сводные таблицы


Сводная таблица – это таблица, которая используется для быстрого подведения итогов или объединения больших объемов данных. Меняя местами строки и столбцы, можно создать новые итоги исходных данных; отображая разные страницы, можно осуществить фильтрацию данных. Вызов мастера сводных таблиц осуществляется командой Данные/Сводная таблица.

Мастер предлагает создать сводную таблицу за 3 шага:

Шаг 1: Выбирается источник данных и вид создаваемого отчета (сводная таблица или сводная диаграмма).

Например, необходимо создать сводную таблицу, отражающую для каждого покупателя и для каждого вида товара объем потраченных средств (таблица 1). На первом шаге выберем: Вид создаваемого отчета – сводная таблица.

Таблица 1.



ФИО

Наименование

Цена

Кол-во

Сумма

Дата

1


Иванов

Кирпич

700,00р.

5

3 500,00р.

02.01.2002

2

Громов

Вагонка

900,00р.

6

5 400,00р.

06.01.2002

3

Кузнецов

Вагонка

900,00р.

4

3 600,00р.

12.03.2002

4

Иванов

Кирпич

800,00р.

20

16 000,00р.

15.03.2002

5

Громов

Вагонка

950,00р.

2

1 900,00р.

19.04.2002

6

Иванов

Кирпич

750,00р.

8

6 000,00р.

22.05.2002

7

Иванов

Цемент

500,00р.

3

1 500,00р.

24.06.2002


8

Кузнецов

Вагонка

950,00р.

2

1 900,00р.

24.07.2002

9

Громов

Кирпич

800,00р.

15

12 000,00р.

30.10.2002

10

Кузнецов

Цемент

500,00р.

5

2 500,00р.

18.11.2002


Шаг 2. Указывается диапазон, содержащий исходные данные. На втором шаге укажем диапазон A1:G11.

Шаг 3. Определяется внешний вид сводной таблицы, нажатием на кнопку Макет и перемещением полей в нужные области диаграммы (рисунок 7).



Рисунок 7. Макет сводной таблицы

Нажмем Готово и в результате получим следующую таблицу:
















Сумма по полю Сумма

Наименование

ФИО


Вагонка

Кирпич

Цемент

Общий итог

Громов

7300

12000

 

19300

Иванов

 

22000

1500

23500

Иванов

 

3500




3500

Кузнецов

5500




2500

8000

Общий итог

12800

37500

4000

54300

















Для дальнейшей работы со сводной таблице и для задания дополнительных параметров используют панель инструментов Сводные таблицы.

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


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

    Для консолидации данных по категории выполните следующие действия:


    Шаг 1. Убедитесь, что все диапазоны данных представлены в формате списка: первая строка каждого столбца содержит подпись, остальные строки — однотипные данные, пустые строки или столбцы в списке отсутствуют. Разместите каждый диапазон на отдельном листе. Не помещайте диапазоны на лист, на котором должна выполняться консолидация.

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

    Для начала подготовим данные для решения задачи консолидации (Таблица 2):

    Таблица 2

    Лист 1

    Лист 2

    Лист 3

    Лист Консолидация

    месяц

    Сумма

    Январь

    300

    Февраль

    500

    Март

    450

    Апрель

    900

    Май

    800

    Июнь

    150

    Июль

    450

    Август


    750

    Сентябрь

    600

    Октябрь

    120

    Ноябрь

    900

    Декабрь

    850



    месяц

    Сумма

    Январь

    300

    Февраль

    400

    Март

    450

    Апрель

    1100

    Май

    850

    Июнь

    150

    Июль

    450

    Август

    640

    Сентябрь

    600

    Октябрь

    200

    Ноябрь

    900

    Декабрь

    850



    месяц

    Сумма

    Январь

    300

    Февраль

    400

    Март

    450

    Апрель

    1100

    Май

    850

    Июнь

    150

    Июль

    450

    Август

    640

    Сентябрь

    600

    Октябрь

    200

    Ноябрь

    900

    Декабрь

    850



    месяц

    Сумма

    Январь




    Февраль


    Март





    Апрель




    Май




    Июнь




    Июль




    Август




    Сентябрь




    Октябрь




    Ноябрь




    Декабрь








    Шаг 2. Щелкните левый верхний угол области, в которой требуется разместить консолидированные данные и примените команду Данные/Консолидация. Выберите из раскрывающегося списка Функция функцию, которую требуется использовать для консолидации данных. Щелкните поле Ссылка, откройте лист, содержащий первый диапазон данных для консолидации, выделите его и нажмите кнопку Добавить. Повторите этот шаг для всех диапазонов (рисунок 8).



    Рисунок 8. Диалоговое окно консолидации

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

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


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

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

Пример использования подбора параметра: Найдите корень уравнения x3-x-5=1

x

y

0

=A2^3-A2-5

  1. Выделить ячейку с формулой и выбрать команду Сервис/Подбор параметра.

  2. В поле Установить в ячейке ввести ссылку на ячейку, содержащую необходимую формулу. Ввести искомый результат в поле Значение. В поле Изменяя значение ячейки ввести ссылку на ячейку, значение которой нужно подобрать. Формула в ячейке, указанной в поле Установить в ячейке должна ссылаться на эту ячейку (рисунок 9).



Рисунок 9. Диалоговое окно Подбора параметра

Поиск решения (задачи оптимизации)

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

Пример решения задачи при помощи поиска решения


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

Таблица 3.

Вид корма

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

Общее количество корма

Лисицы

песцы

1

2

3

180

2

4

1

240

3

6

7

426

Прибыль от реализации одной шкурки (у.- е.)

16

12





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


    -целевая функция получаемой прибыли, должная быть максимальна





  1. На втором шаге подготовим расчетную таблицу в Microsoft Excel (рисунок 10).



    Рисунок 10. Расчетная таблица для решения задачи



  1. На третьем шаге вводится целевая функция и функции ограничения (рисунок 11)



    Рисунок 11. Расчетная таблица в режиме формул



  1. На четвертом шаге выделяем ячейку с целевой функцией (D10) и применяем команду Сервис/Поиск решения. В открывшемся диалоговом окне в поле Изменяя ячейки указываем на значения х и у ($B$9:$C$9), при помощи кнопки Добавить добавляем ограничения (рисунок 12) и нажимаем на кнопку Выполнить.



    Рисунок 12. Диалоговое окно Поиска решения



следующая страница >>