МЕТОДИЧЕСКИЕ УКАЗАНИЯ
по выполнению домашнего задания
«Информационные технологии управления»
Список литературы
:
1. Введение в информационный бизнес: Учебное пособие/ О. В. Голосов, С. А. Охрименко, А. В. Хорошилов и др./ –М.: Финансы и статистика, 1996. – 240 с.
2. Информационные технологии управления: Учебное пособие для студентов вузов, обучающихся по экономическим специальностям; Под ред. проф. Г. А. Тоторенко. –М.: ЮНИТИ-Дана, 2002. – 279 с.
3. Михеева В. Д., Харитонова И. А. Microsoft Access 2002. – СПб.:БХВ-Петербург, 2002. – 1040 с.: ил.
4. Управление проектами в Microsoft Project 2002: Учебный курс/ В.В.Богданов. – СПб.: Питер, 2003. – 640 с.: ил.
5. Excel, VBA, Internet в экономике и финансах: [Экон. и фин. расчеты. Решение оптимизационных задач. Секреты офис. и Web-программирования. Работа с базами данных]/Ю. А. Гарнаев. –СПб.: БХВ-Петербург, 2001. –XV, [1], 796 с.: ил.
6. Microsoft Excel 2002: [Наиболее полное руководство]/Виктор Долженков, Юлий Колесников. –СПб.:БХВ-Петербург, 2002. – 1053 с.: ил.
Создание реляционной базы данных средствами
MS
Access
Для обеспечения удобства доступа к информации, ее часто организуют в виде базы данных
– упорядоченной совокупности данных, предназначенных для хранения, накопления и обработки с помощью ЭВМ. Для создания и ведения базы данных (обновления, обеспечения доступа к ним по запросам и выдачи их пользователю) используется набор языковых и программных средств, называемых системой управления базы данных.
Одной из наиболее известных систем управления базами данных (СУБД) является MS Access. В базах данных Access информация хранится в виде таблиц. Основным отличием таблиц Access от таблиц Excel является то, что в них можно заносить данные строго определенного при создании базы типа. Это обеспечивает дополнительный контроль правильности ввода информации. Другой мерой направленной на снижение вероятности ошибок при вводе информации является создание реляционных баз данных.
В реляционной базе данных часто повторяющаяся информация (например, название страны изготовителя товара в складской базе данных) сводится в отдельные таблицы. После этого устанавливаются связи между таблицами таким образом, что при вводе очередной записи сотруднику, работающему с базой, достаточно выбрать нужный пункт из списка.
В MS Access существует несколько способов создания таблиц, но наиболее простой способ – это создание таблиц с помощью Мастера таблиц
. Чтобы начать создавать таблицу данным способом, необходимо осуществить двойной щелчок левой кнопкой мыши на строчке «Создание таблицы с помощью мастера в окне новой базы данных (см. рис. 4).
После этого пользователю будет предложено совершить ряд последовательных действий посредством диалоговых окон.
В первом диалоговом окне предлагается сформировать набор полей, из которых будут состоять записи (строки) будущей таблицы. Для этого пользователю предлагается обширный набор стандартных полей, сгруппированных по тематикам (например, при создании таблицы «Сотрудники» предлагаются поля «Фамилия», «Должность», «Домашний телефона» и др.). При необходимости любое поле можно переименовать.
В следующем окне предлагается задать имя таблицы и способ генерации ключевых полей (номеров) записей – ручной или автоматический.
Если в базе данных уже существуют таблицы, то появится окно, предлагающее установить связи между таблицами – будет ли записи в создаваемой таблице соответствовать много записей в существующей таблице или наоборот.
В последнем окне предлагается выбор дальнейших действий – начало ввода данных или редактирование структуры таблицы.
Для редактирования структуры таблиц используется режим «Конструктор», включающийся соответствующей кнопкой в окне базы данных (при этом, естественно, в списке объектов базы должен быть отмечен пункт «Таблицы», а среди таблиц отмечена нужная). В этом режиме удобно создавать реляционные базы данных с помощью Мастера подстановок
.
Процедура использования мастера подстановок такова:
– во втором столбце активируется (щелчком левой кнопки мыши) для редактирования ячейка, содержащая данные о формате нужного поля;
– при нажатии на появившуюся в правой части ячейки кнопку отображается список существующих форматов – в списке надо щелкнуть левой кнопкой мыши на элементе «Мастер подстановок…» (см. рис. 5);
–
в появившемся окне надо выбрать источник подставляемых значений (выбор таблицы в этом случае предпочтительней, т. к. облегчается задача последующего изменения и дополнения списка подстановок);
– далее выбирается одна из существующих таблиц и нужное поле;
– следующим шагом является выбор с помощью кнопки непосредственно отображаемой в поле информации – код поля или его содержимое (второе обычно удобнее для будущего пользователя базы данных);
– в последнем окне предлагается изменить название поля исходной таблицы.
Естественно, перед использованием мастера подстановок в головной таблице необходимо создать все необходимые вспомогательные таблицы.
Для внесения информации и просмотра таблицы база данных открывается двойным щелчком на их названии в окне базы данных или с помощью кнопки «Открыть» (см. рис. 4).
создание запросов в
MS
Access
Помимо таблиц в СУБД существуют объекты, называемые запросами. В MS Access существуют целый ряд запросов: запросы на изменение, которые позволяют добавлять и изменять записи в одной или нескольких таблицах; запросы на создание таблиц, создающие новую таблицу на основе одной или нескольких существующих таблиц; запросы на автоподстановку, автоматически заполняющие поля для новой записи, а также другие типы запросов.
При работе с базами данных одной из наиболее часто выполняемых операций является отбор множества записей, удовлетворяющих набору условий. Для выполнения данной операции в СУБД используется механизм запросов на выборку.
Запросы на выборку позволяют просматривать подмножество записей без предварительного открытия таблицы, объединять данные из нескольких таблиц и выполнять вычисления над значениями полей.
Один из методов создания запроса на выборку заключается в создании простого запроса с помощью Мастера запросов и последующей его доработке в режиме Конструктора. Чтобы создать простой запрос с помощью Мастера запросов, необходимо:
1. В окне базе данных на панели объектов выбрать ярлык «Запросы».
2. Дважды щелкнуть левой кнопкой мыши на строчке «Создание запроса с помощью мастера».
3. В появившемся окне выбрать с помощью кнопок со стрелками те поля из имеющихся таблиц или запросов, которые будут отображаться при выполнении данного запроса.
4. После нажатия кнопки «Далее» в следующем окне задать имя для нового запроса и выбрать дальнейшие действия (для задания условий отбора записей следует выбрать вариант «Изменить макет запроса»).
Изменение макета запросов производится в режиме Конструктора запросов. На рис. 6 в качестве примера показан вид запроса на выборку данных о сотрудниках с высшим образованием, родившихся в период с 1971 по 1976 гг.
В столбцах бланка отображаются названия полей, имена исходных таблиц, флажки «Вывод на экран» и условия отбора.
Если условия отбора в различных столбцах размещены в одной строке, то они объединяются по логическому оператору И (And). Если условия
должны объединятся по логическому оператору ИЛИ (Or), их следует размещать в разных строках бланка запроса.
Сложное условие отбора в одном столбце может создаваться с применением логических операторов (табл. 3.5), которые выполняют действия над логическими значениями True (ИСТИНА) и False (Ложь) или над их битовыми эквивалентами «1» и «0».
Таблица 3.5
Значения логических операторов
| A=1
B=0
|
A=1
B=1
|
A=0
B=0
|
A=0
B=1
|
|
| A And B |
0 |
1 |
0 |
0 |
| A Or B |
1 |
1 |
0 |
1 |
| Not A |
0 |
0 |
1 |
1 |
| A Xor B |
1 |
0 |
0 |
1 |
| A Eqv B |
0 |
1 |
1 |
0 |
| A Imp B |
0 |
1 |
1 |
1 |
Кроме логических операторов возможно использование операторов сравнения с образцом (табл. 3.6). В MS Access имеется возможность задавать значение критерия отбора в диалоговом окне. Для этого вместо конкретного значения в условии отбора следует поместить фразу в квадратных скобках (во втором столбце запроса, показанного на рис.6, можно в качестве условия ввести фразу [Образование сотрудника:]
).
Эта фраза будет выдаваться в виде приглашения в диалоговом окне при выполнении запроса. Часто требуется не просто вывести на экран определенное подмножество записей, но и осуществить те или иные вычисления. В качестве примера можно рассмотреть определение среднего возраста сотрудника с высшим образованием.
Таблица 3.6
Операторы сравнения с образцом
| Оператор
|
Пример
|
Описание
|
| Between |
Between (-100) And (100) |
Определяет, находится ли числовое значение в определенном диапазоне |
| In |
In ("Россия", "Украина", "Белоруссия") |
Определяет, является ли строковое значение элементом списка |
| Like |
Like "Ива*" Like "Ива???" |
Проверяет, начинается ли строковое значение с указанных символов (символ «*» замещает произвольное число знаков, если требуется указать точное число неизвестных букв, то следует использовать соответствующее количество символов «?») |
В отличие от электронных таблиц MS Excel в запросах нет возможности отображать одновременно подробные данные и итоговые результаты. Поэтому в запрос следует включать только поля, необходимые для получения итогов.
В рассматриваемом примере имеет смысл включить следующие поля: поле «Образование» для того, чтобы задать условие; поле «Год рождения» для вычисления среднего возраста; поле «Фамилия» (или любое другое) для подсчета числа сотрудников в отобранной группе.
Возможность указать действия, совершаемые над группой записей, появляется после нажатия на кнопку (Групповые операции). В появившейся в макете запроса дополнительной строке можно, нажав кнопку ▼ в правой части поля, выбрать операцию, которая будет выполняться по отношению к значениям соответствующего поля (см. рис. 7).
Для проверяемого поля выбирается вариант «Условие». Для других полей может быть выбрана одна из стандартных статистических функций (например, для поля «Фамилия» - функция Count, подсчитывающая количество записей в группе). Значения стандартных статистических функций MS Access указаны в табл. 3.7. В данной СУБД имеется возможность создавать свои (пользовательские) функции для обработки данных, используя язык Visual Basic for Applications (VBA), но для этого требуется быть специалистом в программировании.
Если использовать значение поля в качестве аргумента статистической функции недостаточно (например, требуется усреднять возраст сотрудника, а в поле записан год его рождения) или требуется осуществить более сложные вычисления, следует воспользоваться Построителем выражений
(рис. 8).
Таблица 3.7
Статистические функции MS Access
| Функция
|
Описание
|
| Avg |
Вычисляет среднее арифметическое набора чисел, содержащихся в указанном поле запроса |
| Count |
Вычисляет количество непустых записей, возвращаемых запросом |
| First |
Возвращает значение поля первой записи результирующего набора |
| Last |
Возвращает значение поля последней записи результирующего набора |
| Max |
Возвращает максимальное значение из набора, содержащегося в указанном поле |
| Min |
Возвращает минимальное значение из набора, содержащегося в указанном поле |
| StDev (StDevP) |
Возвращает смещенное (несмещенное) значение среднеквадратического отклонения, вычисляемого по набору значений, содержащихся в указанном поле |
| Sum |
Возвращает сумму набора значений, содержащихся в заданном поле |
| Var (VarP) |
Возвращает значение смещенной (несмещенной) дисперсии, вычисляемой по набору значений, содержащихся в указанном поле |
Построитель выражения вызывается специальной кнопкой на панели инструментов Конструктора запросов или путем выбора пункта «Построить» во всплывающем при щелчке правой кнопкой мыши на названии поля меню.
В этом случае в качестве групповой операции для данного поля автоматически установится вариант «Выражение», а название поля запроса станет соответствовать названию выражения. Название выражения (это название будет выводиться на экран при выполнении запроса) в Построителе отделяется от самой формулы двоеточием. Для облегчения процесса создания сложных формул в Построителе выражений предусмотрены навигационные окна (сходные с Проводником MS Windows).
Для вставки в формулу функции или поля таблицы нужный элемент следует найти через систему навигационных окон и нажать кнопку «Вставить» – функция или ссылка на поле таблицы автоматически вставятся в формулу. Как и в условия, в формулы можно вставлять и параметры, задаваемые пользователем при выполнении запроса, в примере на рис. 8 таким образом включено значение текущего года (фраза в квадратных скобках).
Дополнительные параметры поля запроса (формат поля, число десятичных знаков после запятой, название поля и др.) могут быть изменены в режиме Конструктора при выборе пункта «Свойства» меню, всплывающего при нажатии правой кнопки мыши в области поля.
создание форм и отчетов в
MS
Access
Одной из основных задач создания и использования базы данных является предоставление пользователям необходимой информации на основе существующих данных. В MS Access 2002 для этих целей предназначены формы
и отчеты
.
Основным отличием форм и отчетов является то, что с помощью форм можно не только просматривать, но и вводить информацию, а отчеты предназначены лишь для просмотра и печати данных.
Самым простым и быстрым способом создания формы или отчета является использование соответствующего. Чтобы с помощью мастера создать простую форму:
1. Щелкните по ярлыку «Формы» в окне базы данных (рис. 4).
2. Дважды щелкните по строчке «Создание формы с помощью мастера».
3. В появившемся диалоговом окне с помощью кнопок со стрелками выберите отображаемые поля из таблиц или запросов и нажмите кнопку «Далее».
4. В следующем окне можно выбрать различные виды форм (вид «Выровненный» является одним из наиболее удобных).
5. В третьем окне можно выбрать стиль оформления формы, после чего следует нажать кнопку «Далее».
6. В последнем окне предлагается задать имя новой формы и выбрать вариант дальнейших действий (открыть форму или изменить ее макет).
Открывается созданная форма двойным щелчком на ее имени в окне базы данных или с помощью кнопки «Открыть». Для изменения ее макета следует (как и в случае с таблицами и запросами) открыть ее в режиме Конструктора.
Конструктор форм позволяет изменять размеры и положения полей в области данных формы, изменять размеры и начертания шрифтов, вводить различные элементы управления.
Простейшим элементом управления является надпись, содержащая какой-либо текст (название базы данных или отдельного поля, фамилию разработчика и т. п.). Для вставки надписи служит кнопка Aa
на панели элементов Конструктора форм (см рис.9). Изменять свойства надписи (как и других элементов управления) можно стандартным для офисных приложений способом – вызвать щелчком правой кнопки мыши на выбранном элементе всплывающее меню.
С помощью панели элементов можно вставить и другие элементы управления – поле, поле со списком, список, группы переключателей, кнопки, рисунки, линии, прямоугольники и др.
Элемент «Поле» автоматически создается Мастером форм для полей таблицы, заполняемых без использования Мастера подстановок, и служит для отображения или ручного ввода информации. При вставке этого элемента в режиме Конструктора появляется диалоговое окно, предлагающее сопоставить новое поле формы с одним из полей в таблице или запросе.
Элемент «Поле со списком» создается Мастером форм для полей таблицы, заполняемых подстановкой значений из вспомогательной таблицы. С помощью данного элемента можно отображать информацию или вводить ее путем подстановки из всплывающего списка.
В отличие от предыдущего элемента, эле
Группы переключателей используются для отображения или ввода информации в поля, предназначенные для хранения натуральных чисел. Такими натуральными числами являются номера (коды) записей вспомогательных таблиц, которые реально и хранятся в главных таблицах в полях, созданных с применением Мастера подстановок. При этом число в связанном с группой переключателей поле будет соответствовать порядковому номеру включенного переключателя. В появляющихся после вставки элемента диалоговых окнах предлагается задать подписи переключателей, выбрать их графический дизайн (флажки, кнопки, круглые окошки с точкой), и задать общее имя группы.
Для ввода и отображения логических значений True и False или битовых «1» и «0» могут применяться отдельные флажки или переключатели.
Кнопки в формах предназначены для выполнения различных команд (запуск программы, вызов другой формы, выполнение запроса и т. д.). В зависимости от выбранного в диалоговом окне варианта действий могут появиться другие окна, в которых потребуется указать имя таблицы, запроса, полный путь к запускаемой программе, (например, для стандартного калькулятора Windows XP – «С:WindowsSystem32calc.exe»).
Если форма представляет собой красивый и удобный для визуального восприятия способ представления таблиц и запросов на экране монитора, то отчет является подготовленным для печати документом, содержащим результаты выполнения запроса или данные из таблиц.
Как и все остальные объекты, простые отчеты быстрее всего создаются с помощью Мастера. На первом этапе в диалоговом окне формируется перечень отображаемых в отчете полей. Если в отчет включались данные нескольких таблиц, то в диалоговом окне будет предложено выбрать вид представления отчета (структуру отчета).
Выбранная структура отчета может быть дополнена дополнительной группировкой данных по совпадению содержимого одного из полей. Варианты группировок задаются также задаются в диалоговом окне Мастера отчетов.
В следующем диалоговом окне предлагается включить в отчет итоговые данные по группам (сумму, среднее, минимальное и максимальное значение).
На заключительном этапе можно выбрать стиль оформления отчета и его имя.
В нашем случае требуется создать отчет для передачи всех данных из основной таблицы в Excel. Отчет можно составлять и по одной главной таблице, но если это сделать, то в поле, использующем подстановку вместо реального текста будет стоять код поля (порядковый номер во вспомогательной таблице). По этой причине включаем в него все поля основной таблицы «Сотрудники», кроме тех, которые используют подстановку. Эти пропущенные поля следует включить из вспомогательных таблиц «Должности» и «Образование». Пояснения на рисунке на следующей странице.
Когда отчет будет создан его надо экспортировать в Excel, щелкнув правой кнопкой по названию отчета в окне базы данных на странице «Отчеты» - рисунок приводится ниже по тексту.
После этого сохранить отчет на диске в формате Excel
Методические указания к выполнению
аналитической части
Microsoft Excel – один из самых распространенных программных продуктов класса «электронные таблицы». Программы данного класса предназначены для хранения и графического представления данных, кроме того, электронные таблицы предлагают средства для анализа.
В MS Excel данные располагаются в ячейках таблицы, имеющих уникальное обозначение. По умолчанию это обозначение включает буквенный код столбца и номер строки (F3 – обозначение ячейки, находящейся в третьей строке шестого столбца). При необходимости увеличить размер ячейки можно путем изменения ширины всего столбца, высоты всей строки или путем объединения нескольких соседних ячеек, причем объединенная ячейка будет иметь обозначение аналогичное обозначению левой верхней ячейки группы.
Совокупность ячеек образует лист,
который является частью книги
. В ячейку могут быть записаны данные различных типов – текст, дата, число, денежная сумма, логические выражения «Истина» или «Ложь» и др.
Помимо этого, ячейка может содержать формулу – символическое выражение, начинающееся со знака « = », в соответствии с которым проводятся вычисления. Формулы могут содержать константы
, ссылки, операторы и функции.
Константа представляет собой готовое (не вычисляемое) значение, например, число «10» или текст «Прибыль».
Ссылка – выражение, указывающее на расположение ячейки, содержащей необходимые для вычислений данные. Абсолютная ссылка
содержит непосредственный адрес ячейки, не изменяющийся при копировании формулы в другую ячейку, например, «$F$5».
Относительные ссылки
записываются без значков «$», так, «A1» – относительная ссылка на ячейку из первой строки первого столбца. При копировании формулы с относительной ссылкой сохраняется относительное расположение ячейки с формулой и ячейки, на которую указывает ссылка. Например, при копировании из ячейки B2 формулы с относительной ссылкой «A1» в ячейку B3, ссылка автоматически изменяется на «A2».
Часто возникает необходимость применения смешанных ссылок, в которых при копировании неизменным остается только номер столбца (например, «$F5») или номер строки (например, «F$5).
Таблица 3.1.
Значения операторов
| Оператор
|
Значение (пример)
|
| Текстовый
|
|
| & (амперсанд) |
Объединение последовательностей знаков в одну последовательность ("Северный"&"ветер") |
| Ссылки
|
|
| : (двоеточие) |
Ставится между ссылками на первую и последнюю ячейки диапазона. Такое сочетание является ссылкой на диапазон (B5:B15) |
| ; (точка с запятой) |
Оператор объединения. Объединяет несколько ссылок в одну ссылку (B5:B15;D5:D15) |
| (пробел) |
Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8) |
Операторами обозначаются операции, которые следует выполнить над данными. Помимо широко известных арифметических операторов ("+", "–" и т. д.) и операторов сравнения (">", "<" и т. д) в Excel имеются дополнительные операторы, приведенные в табл. 3.1.
Функции – заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, например, функция EXP
(аргумент)
возводит константу e
= 2,718282 в степень, равную аргументу. В общем случае аргументами могут быть числовые константы, ссылки на ячейки или диапазоны ячеек.
В MS Excel доступно большое количество других функций, объединенных в группы – математические, статистические, логические и др. Для ускорения их ввода используется кнопка меню «fx
», вызывающая диалоговое окно со списком всех имеющихся функций.
Ряд функций входят в состав надстройки «Пакет анализа», вызывающейся командой «Анализ данных» из меню «Сервис». Если указанная команда в меню отсутствует, необходимо подключить нужную надстройку командой «Надстройки» из меню «Сервис» (при инсталляции пакета MS Office XP необходимо установить надстройки MS Excel).
Построение круговых диаграмм, отражающих структуру статистической совокупности в
Excel
Часто при анализе данных нужно произвести разделение всех данных на группы в зависимости от выполнения тех или иных условий. Сами условия записываются в MS Excel с помощью специальных логических операторов. Простейшая формула «=A1>B1» возвращает значения «Истина» или «Ложь» в зависимости от содержимого ячеек A1 и B1. Для формирования более сложных условных формул используются стандартные логические функции, приведенные в табл. 3.2.
Для организации сложных проверок логические функции могут использоваться в качестве аргументов других логических функций. В качестве примера можно привести формулу «=ЕСЛИ(A1<15; "мальчик"; ЕСЛИ(A1<25; "юноша"; ЕСЛИ(A1<65; "мужчина"; "старик")))», возвращающую характеристику человека мужского пола в зависимости от количества прожитых лет, указанного в ячейке А1. Указанным методом до семи функций ЕСЛИ могут быть вложены друг в друга.
Обычно при анализе производится обработка данных, удовлетворяющих заданному условию. Для этих целей в MS Excel предусмотрены специальные функции, приведенные в табл. 3.3.
Таблица 3.2
Назначение логических функций
| Функция
|
Назначение
|
| И (условие 1
|
Возвращает значение «Истина» при одновременном выполнении всех заданных условий (до 30), в противном случае возвращает значение «Ложь» |
| ИЛИ (условие 1
|
Возвращает значение «Истина» при выполнении хотя бы одного из заданных условий (до 30), в противном случае возвращает значение «Ложь» |
| ЕСЛИ (условие
|
Возвращает значение 1
|
| НЕ (логическое значение
|
Возвращает «Истина», если аргумент «Ложь» и наоборот |
Таблица 3.3
Функции условного счета и суммирования
| Функция
|
Тип функции
|
Назначение
|
| СЧЕТЕСЛИ (диапазон
критерий
|
Статистическая |
Подсчитывает количество ячеек, удовлетворяющих критерию
|
| СУММЕСЛИ (диапазон
|
Математическая |
Находит сумму ячеек, входящих в диапазон суммирования
|
Особенно удобно использование данных формул при разделении общей массы на группы или при вычислении среднего значения некоторого параметра для группы записей, удовлетворяющей условию.
Если предположить, что столбец F содержит данные о половой принадлежности ста сотрудников, то для построения структурной круговой диаграммы надо предварительно определить количество работников каждого пола. Для определения количества женщин можно использовать формулу: среднего возраста сотрудниц фирмы можно воспользоваться формулой «=СЧЕТЕСЛИ (F1:
F
100;"женский")».
Для построения круговой диаграммы необходимо вызвать Мастер диаграмм специальной кнопкой на панели инструментов (см. рис. А).
Рис. А
Затем надо выбрать нужный тип диаграммы (рис. Б).
Рис. Б
После этого нажать «Далее» и указать те ячейки с результатами, которые необходимо отразить на диаграмме. Это проще сделать мышью, предварительно щелкнув по специальной кнопке (обведена красным на рис. В).
Рис. В
Некоторые пользователи этот этап предпочитают осуществлять по-другому – сначала выделить мышью ячейки с результатами (у нас это В2:В3), а потом вызывать Мастер диаграмм. В этом случае Диапазон
заполнится автоматически.
На закладке «Ряд» этого окна подобным образом следует указать названия секторов (рис. Г).
Рис. Г
В следующем окне можно задать название диаграммы (закладка «Заголовки», а на закладке «Подписи данных» отметить необходимый вариант подписи секторов (на рис. Д подписываются проценты).
Рис. Д
После этого надо нажать «Далее» и выбрать, на каком листе (текущем, новом и т. п.) следует поместить график. Все готово!
Построение выборочных характеристик на линейчатой диаграмме
С применением вышеописанных функций можно определять средние значения параметра в группе. Если предположить, что столбец E содержит данные о возрасте ста работников фирмы, а столбец F – об их половой принадлежности, то для определения среднего возраста сотрудниц фирмы можно воспользоваться формулой «=СУММЕСЛИ (F1:F100;"женский"; Е1:Е100)/СЧЕТЕСЛИ (F1:F100;"женский")».
По данным о средней зарплате можно построить график. Для этого повторить описанные выше шаги в мастере диаграмм (естественно, в первом окне выбрать нужный тип диаграммы – линейчатую).
Пример заполнения окна исходных данных показан на рис. Е
Рис. Е
Не удивляйтесь – ось Х в данном случае проходит вертикально.
Нажав «Далее», следует указать название диаграммы и осей, а затем выбрать лист для графика.
Построение гистограммы
При исследованиях рынка, при контроле качества продукции и в ряде других случаев возникает необходимость построения статистических распределений анализируемых данных, т. е. разделения данных на группы по диапазонам значений. Так можно распределить всех потребителей продукции по возрастные группы, продукцию по ценовым группам, выпускаемые заготовки по массе и т. п.
В принципе, эту задачу можно решить, применяя логические функции, но в MS Excel для этих целей предусмотрены два стандартных инструмента – функция ЧАСТОТА и инструмент «Гистограмма» из надстройки «Пакет анализа».
С помощью функции ЧАСТОТА (см. табл. 3.4) может быть записана так называемая формула массива
, возвращающая не одно значение, а целый массив значений, отражающих количество попаданий исходных данных в различные интервалы.
Процедура ввода подобных формул имеет следующие особенности:
– перед началом ввода формулы необходимо выделить диапазон ячеек, в которые будет записан возвращаемый функцией массив значений;
– формулу следует вводить не в ячейки, а в специальную строку формул, расположенную в области панели инструментов;
– после завершения набора формулы она вводится во все выделенные ячейки одновременным нажатием клавиш Ctrl+Shift+Enter.
Таблица 3.4
Синтаксис функции ЧАСТОТА
| Функция
|
Аргументы
|
| ЧАСТОТА(диапазон данных
|
диапазон данных –
|
| диапазон интервалов –
|
При использовании функции ЧАСТОТА следует учитывать, что количество элементов массива, возвращаемого функцией, на единицу превосходит число ячеек, содержащих значения границ интервалов. Так, если в качестве диапазона интервалов задать диапазон из двух ячеек, содержащих числа ′10′ и ′20′, то функция возвратит массив из трех значений - количество чисел из диапазона данных, соответственно, меньших или равных 10, находящихся в диапазоне от 10 до 20 (включая 20) и больших 20.
С помощью «Мастера диаграмм» по результатам, найденным с помощью этой функции, может быть построена диаграмма, графически иллюстрирующая статистическое распределение столбиками, соответствующими по высоте частотам попадания данных в интервалы (подобные диаграммы называют гистограммами).
Аналогичный результат может быть получен применением инструмента «Гистограмма» (см. рис. 3), выбор которого становится доступен после выполнения команды «Анализ данных» меню «Сервис».
В диалоговом окне можно выбрать способ разбиения области значений на интервалы – автоматический или с использованием диапазона карманов (интервалов), задать диапазоны данных и карманов, выбрать месторасположение результатов и вид диаграммы (диаграмма Парето строится в порядке убывания высоты столбиков). Подробное описание элементов окна доступно в справочной системе Excel.
При выборе способа построения распределения следует учитывать тот факт, что распределение, основанное на использовании функции ЧАСТОТА, автоматически пересчитывается при корректировке исходных данных (в этом случае инструмент «Гистограмма» приходится вызывать повторно).
Кроме того, инструмент «Гистограмма» выдает сообщение об ошибке при наличии в диапазоне данных ячеек, содержащих символ «пустая строка». Данная ситуация часто возникает при построении распределения данных ранее отфильтрованных по другому признаку с применением логических функций.
Диаграмма, построенная в автоматическом режиме может не устраивать пользователя своим оформлением. В этом случае можно для нее отдельно вызвать одно из окон мастера диаграмм (рис. Б – Е). Для этого щелкнуть правой кнопкой мыши на белом поле в углу диаграммы и из появившегося меню выбрать нужный пункт (Тип диаграммы, Параметры диаграммы, Исходные данные).
Построение точечной диаграммы и тренда
Точечная диаграмма строится аналогичным образом. Обратите внимание на заполнение окна «Исходные данные»
А закладка «Ряд» может быть заполнена так
После стандартного завершения всех этапов построения графика на нем следует добавить тренд – график математической функции, похожей на статистическую закономерность. Для этого надо выделить диаграмму и щелкнуть правой кнопкой мыши по одной из точек графика. В появившемся меню следует выбрать «Добавить линию тренда». Далее в диалоговом окне отметить то, что нужно в Вашем случае. Пример – на рисунке.