Практическая работа «ТАБЛИЧНЫЕ ПРОЦЕССОРЫ» 1. Цель работы

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

2. Порядок выполнения работы

1.       Самостоятельно изучить основные сведения о табличных процессорах (см. раздел 3).

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

3.       Выполнить задания к практической работе, приведенные в разделе 4.

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

5.       Оформить отчет по результатам выполнения индивидуального задания.

6.       Защитить результаты работы.

3. Основные сведения о табличных процессорах

Интерфейс табличного процессора

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

Результат вычисления формулы в клетке является изображением этой клетки. Числовые данные и даты могут рассматриваться как частный случай формул. Для управления электронной таблицей используется специальный комплекс программ — табличный процессор.

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

Строки, столбцы, ячейки и их адреса

Рабочая область электронной таблицы состоит из строк и столбцов, имеющих свои имена. Имена строк — это их номера. Нумерация строк начинается с 1 и заканчивается максимальным числом, установленным для данной программы. Имена столбцов — это буквы латинского алфавита сначала от А до Z , затем от АА до AZ , ВА до BZ и т. д.

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

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

Ячейка — область, определяемая пересечением столбца и строки электронной таблицы.

Адрес ячейки определяется названием (номером) столбца и номером строки.

Ссылка — способ (формат) указания адреса ячейки.

Указание блока ячеек

В электронной таблице существует понятие блока (диапазона) ячеек, также имеющего свой уникальный адрес. В качестве блока ячеек может рассматриваться строка или часа строки, столбец или часть столбца, а также прямоугольник, состоящий из нескольких строк и столбцов или их частей (рис. 1.1). Адрес блока ячеек задается указанием ссылок первой и последней его ячеек, между которыми, например, ставится разделительный символ — двоеточие <:> или две точки подряд <..>.

Согласно примеру, приведенному на рисунке 1.1, адрес ячейки, образованной на пересечении столбца G и строки 3, будет выражаться ссылкой G3. Адрес блока, образованного в виде части строки 1, будет А1..Н1. Адрес блока, образованный в виде столбца В, будет В1..В10. Адрес блока, образованный в виде прямоугольника, будет D4..F5.

Каждая команда электронной таблицы требует указания блока (диапазона) ячеек, в отношении которых она должна быть выполнена.

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

Типичными установками, принимаемыми по умолчанию на уровне всех ячеек таблицы, являются::

ширина ячейки в 9 разрядов, левое выравнивание для символьных данных и основной формат для цифровых данных с выравниванием вправо.

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

Типовая структура интерфейса

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

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

Вспомогательная область управления включает:

§  строку состояния;

§  панели инструментов;

§  вертикальную и горизонтальную линейки прокрутки.

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

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

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

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

Рабочее поле — пространство электронной таблицы, состоящее из ячеек, названий столбцов и строк.

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

Текущая ячейка и экран

Текущей (активной) называется ячейка электронной таблицы, в которой в данный момент находится курсор. Адрес и содержимое текущей ячейки выводятся в строке ввод электронной таблицы. Перемещение курсора как по строке ввода, так и по экрану осуществляется при помощи клавиш движения курсора.

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

Окно, рабочая книга, лист

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

Рабочая книга представляет собой документ, содержащий несколько листов, в которые могут входить таблицы, диаграммы или макросы. Вы можете создать книгу для совместного хранения в памяти интересующих вас листов и указать, какое количество листов она должна содержать. Все листы рабочей книги сохраняются в одном файле. Заметим, что термин "рабочая книга" не является стандартным. Так, например, табличный процессор Framework вместо него использует понятие Frame (рамка).

Данные, хранимые в ячейках электронной таблицы

Типы входных данных

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

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

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

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

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

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

§  ДД-МММ-ГГ (04-Янв-95);

§  МММ-ДД-ГГ (Янв-04-95); § ДД-МММ (04-Янв); § МММ-ГГ (Янв-95).

Тип входных данных, содержащихся в каждой ячейке, определяется первым символом, который должен трактоваться не как часть данных, а как команда переключения режима:

§  если в ячейке содержатся числа, то первый их символ является либо цифрой, либо десятичной точкой, либо знаком числа (плюсом или минусом);

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

§  ячейка, содержащая функцию, всегда использует в качестве первого специальный символ @;

§  если ячейка содержит символьные данные, ее первым символом может быть одинарная (апостроф) или двойная кавычка, а также пробел.

Форматирование числовых данных в ячейках

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

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

§  Формат с фиксированным количеством десятичных знаков обеспечивает представление чисел в ячейках с заданной точностью, определяемой установленным пользователем количеством десятичных знаков после запятой (десятичной точки). Например, если установлен режим форматирования, включающий два десятичных знака, то вводимое в ячейку число 12345 будет записано как 12345.00, а число 0.12345 — как .12.

§  Процентный формат обеспечивает представление введенных данных в форме процентов со знаком % (в соответствии с установленным количеством десятичных знаков). Например, если установлена точность в один десятичный знак, то при вводе 0.123 на экране появится 12.3%, а при вводе 123 — 12300.0%.

§  Денежный формат обеспечивает такое представление чисел, где каждые три разряда разделены запятой. При этом пользователем может быть установлена определенная точность представления (с округлением до целого числа или в два десятичных знака). Например, введенное число 12345 будет записано в ячейке как 12,345 (с округлением до целого числа) и 12,345.00 (с точностью до двух десятичных знаков).

§  Научный формат, используемый для представления очень больших или очень маленьких чисел, обеспечивает представление вводимых чисел в виде двух компонентов:

— мантиссы, имеющей один десятичный разряд слева от десятичной точки, и некоторого (определяемого точностью, заданной пользователем) количества десятичных знаков справа от нее; — порядка числа.

Введенное число 12345 будет записано в ячейке как 1.2345Е +04 (если установленная точность составляет 4 разряда) и как 1.23Е +04 (при точности в 2 разряда). Число .0000012 в научном формате будет иметь вид 1.2Е -06.

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

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

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

§  Выравнивание к правому краю ячейки располагает последний символ вводимых в ячейку данных в ее крайней правой позиции.

§  Выравнивание по центру ячейки располагает вводимые данные по центру ячейки.

Форматирование данных — выбор формы представления числовых или символьных данных в ячейке.

Изменение ширины колонки

Отображение числовых данных зависит не только от выбранного формата, но также и от ширины колонки (ячейки), в которой эти данные располагаются. Ширина колонки при текстовом режиме экрана устанавливается в знаках, а при графическом режиме экрана — в независимых единицах. Количество знаков в ячейке зависит от ее ширины, кегля, гарнитуры, а также от конкретного текста. Так, например, не составляет проблемы расположить число 12345 в формате с запятой без дробной части в ячейке шириной в 9 знаков. Однако вы не сможете его расположить там в денежном формате с двумя десятичными знаками, поскольку число $12,345.00 занимает 10 разрядов, превышая тем самым ширину ячейки. В данном случае необходимо изменить используемый формат представления числа либо увеличить ширину колонки.

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

Формулы

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

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

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

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

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

- вычитание,

* умножение,

/ деление,

^ возведение в степень.

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

Арифметические формулы могут также содержать операторы сравнения: равно (=), не равно (<>), больше (>), меньше (<), не более (<=), не менее (>=). Результатом вычисления арифметической формулы является число.

Логические формулы могут содержать указанные операторы сравнения, а также специальные логические операторы:

#NOT# — логическое отрицание "НЕ", #AND# — логическое "И", #OR# — логическое "ИЛИ".

Логические формулы определяют, выражение истинно или ложно. Истинным выражениям присваивается численная величина 1, а ложным —0. Таким образом, вычисление логической формулы заканчивается получением оценки "Истинно" (1) или

"Ложно" (0).                                                                                                                                                                  Таблица 1.1.

Приведем несколько примеров вычисления арифметических и логических формул по следующим данным таблицы 1.1:

Резуль

положительные числа, всему выражению присваивается численная величина 1 ("Истинно").

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

Функции

Под функцией понимают зависимость одной переменной (у) от одной (х) или нескольких переменных (x₁, x₂, …, x_n). Причем каждому набору значений переменных x₁, x₂, …, x_n будет соответствовать единственное значение определенного типа зависимой переменной у. Функции вводят в таблицу в составе формул либо отдельно. В электронных таблицах могут быть представлены следующие виды функций:

§  математические;

§  статистические;

§  текстовые;

§  логические;

§  финансовые;

§  функции даты и времени и др.

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

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

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

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

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

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

СУММ(Список) — статистическая функция определения суммы всех числовых значений в Списке. Список может состоять из адресов ячеек и блоков, а также числовых значений.

СУММ(B5..E5)

СУММ(A3..E3, 230)

СРЗНАЧ(Список) — статистическая функция определения среднего арифметического значения всех перечисленных в Списке величин.

СРЗНАЧ(5, 20,10, 5)

СРЗНАЧ (B10..B13,B17)

ЕСЛИ(Условие, Истинно, Ложно) — логическая функция, проверяющая на истинность заданное логическое условие. Если условие выполняется, то результатом функции является значение аргумента "Истинно". Если условие не выполняется, то результатом функции становится значение аргумента "Ложно".

ЕСЛИ(B4<100,100, 200) — если ячейка В4 содержит число меньше 100, то функции присваивается значение 100, если же это условие не выполняется (т.е. содержимое ячейки В4 больше или равно 100), функции присваивается значение 200.

Автоматическое изменение относительных ссылок при копировании и перемещении формул

Буфер промежуточного хранения

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

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

Относительная и абсолютная адресация

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

Абсолютная ссылка — это не изменяющийся при копировании и перемещении формулы адрес ячейки, содержащий исходное данное (операнд).

Для указания абсолютной адресации вводится символ $. Различают два типа абсолютной ссылки: полная и частичная.

§  Полная абсолютная ссылка указывается, если при копировании или перемещении адрес клетки, содержащий исходное данное, не меняется. Для этого символ $ ставится перед наименованием столбца и номером строки. $В$5; $D$12 — полные абсолютные ссылки.

§  Частичная абсолютная ссылка указывается, если при копировании и перемещении не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором — перед наименованием столбца. В$5, D$12 — частичная абсолютная ссылка, не меняется номер строки; $В5, $D12 — частичная абсолютная ссылка, не меняется наименование столбца.

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

Форма написания относительной ссылки совпадает с обычной записью.

Правило относительной ориентации клетки

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

Рассмотрим правило относительной ориентации клетки на следующем примере.

Клетка со ссылкой С2 содержит формулу-шаблон сложения двух чисел, находящихся в ячейках А1 и В4. Эти ссылки являются относительными и отражают ситуацию взаимного расположения исходных данных в ячейках А1 и В4 и результата вычисления по формуле в ячейке С2. По правилу относительной ориентации клеток ссылки исходных данных воспринимаются системой не сами по себе, а так, как они расположены относительно клетки С2: ссылка А1 указывает на клетку, которая смещена относительно клетки С2 на одну клетку вверх и на две клетки влево; ссылка В4 указывает на клетку, которая смещена относительно клетки С2 на две клетки вниз и одну

клетку влево.

Копирование формул

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

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

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

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

На рисунке 1.2 приведен результат копирования формулы, содержащейся в ячейке A2, при использовании относительных, полностью абсолютных и частично абсолютных ссылок. При копировании формулы с использованием относительных ссылок происходит их автоматическая подстройка (рис.1.2 а). Результаты копирования с использованием абсолютных ссылок со знаком $ приведены на рис. 1.2 б. Как нетрудно заметить, применение абсолютных ссылок запрещает автоматическую настройку адресов, и копируемая формула сохраняет свой первоначальный вид. В приведенном на рис. 1.2 в примере для запрещения автоматической подстройки адресов используются смешанные ссылки.

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

                       ДО КОПИРОВАНИЯ                                                                                ПОСЛЕ КОПИРОВАНИЯ

A                                                                     B             C             A             B             C а)

б)

1                              3       6              1             3             6

2                              +$C1+A$1     2             +$C1+A$1           +$C1+B$1            +$C1+C$1 в)

Рис. 1.2. Копирование формул: а — с относительными ссылками; б — с абсолютными ссылками; в — с частично абсолютными ссылками

Перемещение формул

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

На рис. 1.3 а приведен пример перемещения содержимого отдельной ячейки A3 в ячейку С3. В этом случае содержимое исходной ячейки, не изменяясь, перемещается в ячейку назначения, а исходная ячейка остается пустой.

Рисунок 1.3 б иллюстрирует случай перемещения содержимого трех ячеек Al, A2 и A3. При этом ячейки взаимосвязаны — содержимое третьей ячейки включает в себя содержимое первых двух. После перемещения мы видим, что в результате автоматической подстройки ссылок содержащаяся в ячейке A3 формула изменилась, чтобы отразить произошедшие в электронной таблице изменения (теперь компоненты содержащейся в ячейке С3 суммы находятся в других ячейках). Так же как и в предыдущем случае диапазон исходных ячеек после выполнения операции перемещения опустел.

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

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

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

          ДО ПЕРЕМЕЩЕНИЯ                                                                               ПОСЛЕ ПЕРЕМЕЩЕНИЯ

A                                                                     B             C             A             B             C а)

б)

1                              3       +A3*5    1             3             +C3*5

2                              6       2              6

3                              +A1+A2          3             +A1+A2 в)

Рис. 1.3. Перемещение содержимого ячеек: а — одной ячейки; б — колонки; в — зависимых ячеек

Контрольные вопросы:

1.       Опишите структуру типового интерфейса электронной таблицы.

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

3.       Перечислите и поясните существующие форматы представления числовых данных в ячейках электронной таблицы.

4.       Перечислите и поясните существующие форматы представления символьных данных в ячейках.

5.       Что такое формула в электронной таблице и ее типы. Приведите примеры.

6.       Что такое функция в электронной таблице и ее типы. Приведите примеры.

7.       Поясните очередность выполнения операций в арифметических формулах.

8.       Как указывается блок (диапазон) ячеек при выполнении какой-либо команды?

9.       Поясните, для чего используются абсолютные и относительные адреса ячеек.

10.   В чем смысл правил автоматической настройки формул при выполнении операций копирования и перемещения?

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

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

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

4.1 Настройка новой рабочей книги

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

Рабочая книга по аналогии с обычной книгой может содержать расположенные в произвольном порядке листы, которые служат для организации и анализа данных. Листы могут быть разного типа: рабочими листами, модулями VBA, диаграммами. Можно вводить и изменять данные на любых листах, выполнять вычисления на основе данных из нескольких листов. При создании диаграммы ее можно поместить на лист с соответствующими данными или на отдельный лист диаграммы. Имена листов отображаются на ярлыках в нижней части окна книги. Для перехода с одного листа на другой следует щелкнуть мышью по соответствующему ярлыку. Название текущего (активного) листа выделено.

Рабочее поле Excel — это электронная таблица, состоящая из столбцов и строк. Названия столбцов — буква или две буквы латинского алфавита. Каждая строка таблицы пронумерована. Размер таблицы фиксирован: число столбцов — 256, строк — 16384.

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

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

Для просмотра электронной таблицы используются линейки прокрутки.

Ниже представлены типовые технологические операции с рабочими книгами и листами.

Создается рабочая книга командой Файл, Создать или кнопкой <Создать>, при этом используется один из готовых шаблонов. Для изменения установок среды Excel, что повлечет за собой изменение установок текущей (активной) книги, можно воспользоваться командой Сервис, Параметры, вкладка Общие. На вкладке Общие указывается:

§  режим, при котором ссылки записываются в стиле R1C1 (адрес клетки — номер строки и номер столбца);

§  максимальное количество элементов в списке (с которыми работали в предыдущих сеансах); § защита от макровирусов;

§  звуковое сопровождение событий;

§  надо ли предлагать заполнение свойств файла (таких, как тематическое назначение рабочей книги, ключевые слова, автор рабочей книги, комментарии);

§  надо ли Игнорировать DDE-запросы от других приложений, т.е. для игнорирования запросов с использованием Динамического Обмена Данными (DDE) из других приложений;

§  количество листов при создании новой книги;

§  тип и размер шрифта, используемого при вводе данных в таблицу;

§  рабочий каталог (папку); § каталог (папку) автозагрузки; § имя пользователя.

Назначение других вкладок команды Сервис, Параметры:

§  Вкладка Вид определяет внешний вид экрана, что обеспечивается заданием режима отображения (да ли нет) строки формул в верхней части окна; строки состояния в нижней части окна; графических объектов; формул или их значений; сетки, заголовков строк и столбцов таблицы; постранично; горизонтальной и вертикальной полос прокрутки, ярлычков листов.

§  Вкладка Вычисления управляет процессом вычисления в таблице: задается автоматический либо под управлением пользователя режим изменения значении аргументов в формуле; задается число итерации при поиске решений и для прерывания циклической ссылки; определяется точность вычислений, система дат и пр.

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

§  Вкладка Цвет обеспечивает выбор цветовой палитры для оформления диаграмм и других графических объектов.

§  Вкладка Диаграмма обеспечивает задание параметров вывода активной диаграммы и режим отображения (названий и значений) во всплывающих подсказках, при установке указателя мыши на элементе диаграммы.

§  Вкладка Списки обеспечивает выбор конкретного списка названий для редактирования.

§  Вкладка Переход задает установку формата записи файлов Excel, параметры вывода на экран таблицы в режиме просмотра и открытия книги, правила вычислений и преобразования формул при открытии файлов Lotus 1-2-3 в Microsoft Excel.

Типовые технологические операции с рабочими книгами и листами

Название технологической операции                                     Технология выполнения операции

Сохранить книгу, которая ранее уже Выполнить команду Файл, Сохранить или нажать на панели

сохранялась                                                        Стандартная кнопку <Сохранить>

Скрыть рабочую книгу                       1. Установить курсор на любом листе книги

2. Выполнить команду Окно, Скрыть

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

2. Выбрать книгу из списка скрытых

Поиск файлов                                        1. Выполнить команду Файл, Открыть, Найти

2. Осуществить расширенный поиск файлов с помощью нажатия

кнопки <Отбор> в окне Открытие документа

Выделить рабочий лист                                 1. Установить курсор мыши на ярлык рабочего листа 2. Нажать левую кнопку мыши

Вызов контекстного меню команд листа 1. Установить курсор мыши на ярлык рабочего листа

2. Нажать правую кнопку мыши

Выделить несколько смежных рабочих 1. Выделить первый рабочий лист

листов                                                        2. Выделить последний рабочий лист диапазона листов при нажатой

клавише <Shift>

Выделить          несколько                                             несмежных 1. Выделить первый рабочий лист

рабочих листов 2. Последующие листы выделять при нажатой клавише <Ctrl> Снять выделение рабочих листов      1. Вызвать контекстное меню команд

2. Выполнить команду Разгруппировать листы

Вставить рабочий лист (несколько 1. Выделить рабочий лист, перед которым надо вставить новый лист рабочих листов) 2. Вызвать контекстное меню и выполнить команду Добавить

Переименовать рабочий лист           1. Выделить рабочий лист

2. Выполнить команду Формат, Лист, Переименовать

Альтернатива

Вызвать контекстное меню и выполнить команду Переименовать

Удалить рабочий лист (несколько 1. Выделить рабочий лист или группу листов рабочих листов)    2. Выполнить команду Правка, Удалить лист

Альтернатива

Вызвать контекстное меню и выполнить команду Удалить

Скрыть рабочие листы                                   1. Выделить рабочие листы 2. Выполнить команду Формат, Лист, Скрыть

Показать скрытый рабочий лист      1. Выполнить команду Формат, Лист, Отобразить

2.  Выбрать из списка скрытых листов нужный лист

3.  Нажать кнопку <ОК>

Переместить или скопировать рабочий 1. Выделить рабочий лист, щелкнув по нему левой кнопкой мыши лист (несколько рабочих листов) 2. Выполнить команду Правка, Переместить/ скопировать лист или команду Переместить/ скопировать из контекстного меню 3. В диалоговом окне:

•  из списка выбрать имя книги, куда идет перемещение или копирование;

•  выбрать лист, перед которым будет помещена копия;

•  установить (снять) флажок при копировании (перемещении);

•  нажать кнопку <ОК>Переместить или скопировать рабочий 1. Выделить рабочий лист

лист (несколько рабочих листов) 2. Выполнить команду Правка, Переместить/ скопировать лист или команду Переместить/ скопировать из контекстного меню

3. Указать книгу, куда идет перемещение или копирование (в том числе новая книга). Место вставки — перед определенным листом 4. Выбрать переключатель Создавать Копию (при копировании листа)

Задание 1. Создайте электронную таблицу учета результатов экзаменационной сессии студентов:

1.       Создайте рабочую книгу.

2.       Выполните настройку книги.

3.       Освойте технологию переименования листов книги.

4.       Сохраните рабочую книгу.

5.       Для приобретения навыков работы в среде Excel необходимо выполнить все технологические операции, приведенные выше.

Порядок выполнения задания 1

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

1-й вариант. При загрузке среды Excel на экране появляется новая книга со стандартным именем Книга

(номер);

2-й вариант. На экране уже отображена созданная ранее книга с уникальным именем. В этом случае для создания новой книги воспользуйтесь командой Файл, Создать.

2.       Сделайте настройку среды Excel для рабочей книги, в которой будет вестись учет результатов экзаменационной сессии студентов:

§  выполните команду Сервис, Параметры и в диалоговом окне выберите вкладку Общие, установив следующие параметры:

Стиль ссылок: А1, т.е. нет флажка

Защита от макровирусов — есть флажок

Листов в новой книге — 5

Стандартный шрифт — Arial Cyr, размер 10

Выберите рабочий каталог для сохранения новых книг

Введите имя пользователя

§  выберите вкладку Вид, установив флажки следующих параметров:

Отображать: строку формул, строку состояния

Примечания: не отображать

Объекты: отображать

Параметры окна: сетка, заголовки строк и столбцов, горизонтальная и вертикальная полосы прокрутки, ярлычки листов, авторазбиение на страницы

§  выберите вкладку Вычисления, установив флажки следующих параметров:

Автоматически производить вычисления

Точность: как на экране

§  выберите вкладку Правка, установив флажки следующих параметров:

Правка прямо в ячейке

Перетаскивание ячеек

Переход к другой ячейке после ввода в направлении вниз

Число десятичных цифр — 2

Автозаполнение значений ячеек

         3.    Переименуйте рабочий лист, выполнив следующие действия:

§ установите указатель мыши на Лист 1 и вызовите контекстное меню, щелкнув правой клавишей мыши; § выберите в контекстном меню команду Переименовать; § введите в диалоговом меню новое имя листа.

4.       Сохраните созданную рабочую книгу под именем Session.xls в личном каталоге рабочего диска, выполнив команду Файл, Сохранить как. В диалоговом окне установите следующие параметры: Папка: имя личного каталога

Имя файла: Session

Тип файла: книга Microsoft Excel

5.       Тренинг работы с листами и книгами. Проделайте приведенные выше типовые технологические операции.

4.2 Создание и заполнение таблицы постоянными данными и формулами

Формат ячеек таблицы

Ячейки рабочего листа имеют заданный формат, который устанавливается командой Формат, Ячейки или командой контекстного меню Формат ячеек. Эти команды имеют несколько вкладок: Число, Выравнивание, Шрифт, Граница, Вид, Защита.

§ Вкладка Число — задает форматы представления данных в ячейке:

Общий — обеспечивает отображение числовых и текстовых данных произвольного типа;

Числовой — включает цифры и символы-разделители: десятичная точка, процент, знак мантиссы, знак числа, круглые скобки, денежное обозначение (р. или $);

Денежный или Финансовый — для отображения денежных величин;

Дата/время — для отображения даты и времени в выбранном формате;

Процентный — для вывода чисел, предварительно умноженных на 100, с символом процента; Дробный — для вывода дробных чисел;

Экспоненциальный — для вывода чисел в экспоненциальном формате, например 1-,65Е+044;

Текстовый — последовательность букв, цифр, специальных символов;

Дополнительный — нестандартные дополнительные форматы, например номер телефона, почтовый индекс и пр.

Все форматы — показывает все имеющиеся в Excel форматы.

§  Вкладка Выравнивание определяет:

Выравнивание — способ выравнивания данного в ячейке по горизонтали (по левому или правому

краю, по значению, по центру выделения, по центру, по ширине, с заполнением) или по вертикали (по нижнему или верхнему краю, по центру или высоте);

Отображение — определяет, можно ли переносить в ячейке текст по словам, разрешает или запрещает объединение ячеек, задает автоподбор ширины ячейки.

§  Вкладка Шрифт — изменяет шрифт, начертание, размер, цвет, подчеркивание и эффекты текста в выделенных ячейках;

§  Вкладка Граница — создает рамки (обрамление) вокруг выделенного блока ячеек;

§  Вкладка Вид — позволяет задать закраску ячейки (цвет и узор);

§  Вкладка Защита — управляет скрытием формул и блокировкой ячеек (запрет редактирования данных ячеек). Устанавливать защиту можно в любой момент, но действовать она будет только после того, когда введена защита листа или книги с помощью команды Сервис, Защитить лист.

Технологические операции с ячейками таблицы

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

1.       Первоначально выделить блок ячеек — объект действия, а затем выбрать команду меню для исполнения действия.

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

<Ctrl>.

3.       Операции копирования, вставки, удаления, перемещения не выполняются для блока с несмежными ячейками.

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

Заполнение таблицы постоянными значениями

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

Постоянные значения — это числа, символы, текст. В ячейку константа записывается следующим образом: курсор устанавливается в ячейку и с клавиатуры вводится значение. Редактирование введенного значения проводится после установки курсора в нужную ячейку, а далее следует либо нажать клавишу <F2>, либо щелкнуть кнопкой мыши в строке ввода в нужном месте.

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

Формулы в таблице и технология их использования

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

В качестве операндов используются:

§  числа;

§  тексты (вводятся в двойных кавычках, например «Неявка»);

§  логические значения (например, ИСТИНА и ЛОЖЬ, условия типа А23=А45 и т.д.);

§  значения ошибки (типа # ДЕЛ/О!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!);

§  ссылки — адреса ячеек. При перечислении ссылки разделяются точкой с запятой, например: А4; С5; С10: Е20;

§  встроенные функции Excel.

Операнды в формулах соединяются с помощью символов операций:

§  арифметических операций: + (сложение), — (вычитание), / (деление), * (умножение), ^ (возведение в степень);

§  операций отношения: >, >= (не меньше), <, <= (не больше), =, о (не равно).

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

§  относительные ссылки, например А2 или С23, которые всегда изменяются так, чтобы отобразить правило их вхождения в формулу относительно ее нового местоположения. При копировании формулы в новую книгу и лист перед ссылкой, входящей в скопированную формулу, появляется имя книги и листа, откуда производилось копирование (BOOKЛИСТ5!F4);

§  абсолютные ссылки, которые перед именем столбца и номером строки имеют символ $. Назначение ссылки абсолютной производится следующим образом: в строке ввода перед ссылкой устанавливается курсор и нажимается клавиша <F4>, например SAS4. Можно сделать то же самое, вводя символ $ с клавиатуры. При копировании абсолютные ссылки остаются неизменными;

§  частично абсолютные ссылки, которые при копировании корректируются частично. Символ $ стоит или перед именем столбца, или перед номером строки ($R2, F$5). Например, при копировании формулы, содержащей $F5, сохранится имя столбца F, а номер строки будет изменен;

§  имена блоков, например ЦЕНА. Имя связывается с данными блока, а не с его местоположением. Можно блок перенести в другое место, что не повлияет на его имя.

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

Использование функций в таблице

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

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

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

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

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

Правила построения формул с помощью Мастера функций:

§  состав аргументов функций, порядок задания и типы значений фиксированы и не подлежат изменению;

§  аргументы вводятся в специальных строках ввода;

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

§  для ввода имени блока ячеек используется команда Вставка, Имя, Вставить с выбором имени блока;

§  для построения ссылки следует установить курсор в поле ввода, а затем перевести указатель мыши на требуемый рабочий лист для выделения ячейки или блока;

§  абсолютные ссылки формируются при установке курсора перед адресом ячейки в строке ввода и нажатии клавиши <F4>.

Задание 2. Формирование структуры таблицы и заполнение ее постоянными значениями — числами, символами, текстом

В качестве примера таблицы рассматривается экзаменационная ведомость (рис. 4.1). Для каждой группы создаются типовые ведомости, которые содержат списки студентов (фамилия, имя, отчество, № зачетной книжки) и полученные ими оценки на экзамене.

В данном задании требуется на базе созданной в работе 1 таблицы подготовить для каждой группы электронную экзаменационную ведомость (см. рис. 4.1).

В любой таблице всегда можно выделить минимум две структурные части - название и ее шапку.

Название таблицы вводится в любую ячейку и оформляется шрифтами.

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

§  задайте способ выравнивания названия граф (при больших текстах необходимо обеспечить перенос по словам);

§  в каждую ячейку одной строки введите названия граф таблицы; § установите ширину каждого столбца таблицы.

После окончания оформления шапки таблицы введите в таблицу постоянные данные:

§  фамилии студентов и полученные ими оценки по конкретной дисциплине;

§  заголовки в нижней части таблицы для итоговых данных, которые будут подсчитаны впоследствии при выполнении задания 3.

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

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

ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ

Группа № _____    Дисциплина ________________________________

«отлично» ________________________________________

«хорошо» _________________________________________

«удовлетворительно» _______________________________

«не явилось» ______________________________________

ИТОГО ___________________________________________

Рис. 4.1. Форма экзаменационной ведомости

Порядок выполнения задания 2

1. Загрузите с жесткого диска созданный в работе 1 файл с именем Session:

§  выполните команду Файл, Открыть;

§  в диалоговом окне установите следующие параметры:

Папка: имя вашего каталога

Имя файла: Session

Тип файла: Все файлы Microsoft Excel

2. В соответствии с рисунком 4.1 введите тексты заголовка и шапки таблицы в следующие ячейки:

Для этого:

§  установите указатель мыши в ячейку, куда будете вводить текст, например в ячейку A1, и щелкните левой кнопкой, появится рамка;

§  введите текст и нажмите клавишу ввода <Enter>;

§  переместите указатель мыши в следующую ячейку, например в ячейку A3, и щелкните левой кнопкой; § введите текст, нажмите клавишу ввода <Enter> и т.д.

         3.    Отформатируйте ячейки А1:F1:

§  выделите блок ячеек, нажмите правую кнопку мыши для вызова контекстного меню; § введите команду контекстного меню Формат ячеек; § на вкладке Выравнивание выберите опции:

По горизонтали: по центру выделения

По вертикали: по верхнему краю

§  нажав кнопку <Размер>, выберите размер шрифта, например 14 пт;

§  выделите текст жирным шрифтом, нажав на панели инструментов кнопку <Ж>.

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

§ выделите блок ячеек A3:E5, где располагается шапка таблицы; § вызовите контекстное меню и выберите команду Формат ячеек; § на вкладке Выравнивание задайте параметры:

По горизонтали: по значению

По вертикали: по верхнему краю

Переносить по словам: поставить флажок Ориентация: горизонтальный текст (по умолчанию) § нажмите кнопку <ОК>.

         5.    Установите ширину столбцов таблицы в соответствии с рис. 4.1. Для этого:

§  подведите указатель мыши к правой черте клетки с именем столбца, например В, так, чтобы указатель изменил свое изображение на прямую черту с двумя стрелками;

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

§  аналогичные действия проделайте со столбцами А, С, D, Е, F-J.

6.       Заполните ячейки столбца В данными о студентах учебной группы, приблизительно 10 — 15 строк.

Отформатируйте данные.

7.       Присвойте каждому студенту порядковый номер:

§  введите в ячейку А6 число 1;

§  установите курсор в нижний правый угол ячейки А6 так, чтобы указатель мыши приобрел изображение креста и, нажав правую кнопку мыши, протяните курсор на требуемый размер; выполните команду локального меню Заполнить.

8.       После списка студентов в нижней части таблицы согласно рис. 4.1 введите в ячейки столбца А текст итоговых строк: Отлично, Хорошо, Удовлетворительно, Неудовлетворительно, Не явилось, ИТОГО.

9.       Объедините две соседние ячейки для более удобного представления текста итоговых строк. Технологию объединения покажем на примере объединения двух ячеек столбцов А и В, в которых будет расположена надпись Отлично:

§  выделите две ячейки;

§  вызовите контекстное меню и выберите команду Формат ячеек;

§  на вкладке Выравнивание установите флажок Объединение ячеек и нажмите кнопку <ОК>; § аналогичные действия проделайте с остальными ячейками, где хранятся названия итоговых ячеек.

10. Сохраните рабочую книгу, для которой файл будет иметь тип xls:

§  выполните команду Файл, Сохранить как;

§  в диалоговом окне установите следующие параметры:

Папка: имя личного каталога на рабочем диске

Имя файла: Session

Тип файла: Книга Microsoft Excel

11. Тренинг работы с ячейками таблицы. Выполните технологические операции, приведенные в таблице раздела «Технологические операции с ячейками таблицы».

Задание 3. Технология работы с формулами на примере подсчета количества разных оценок в группе в экзаменационной ведомости

В созданной в предыдущем задании 2 рабочей книге с экзаменационной ведомостью, хранящейся в файле с именем Session, рассчитайте:

§  количество оценок (отлично, хорошо, удовлетворительно, неудовлетворительно), неявок, полученных в данной группе;

§  общее количество полученных оценок.

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

Предлагается использовать следующий алгоритм.

1.       Ввести дополнительное количество столбцов, по одному на каждый вид оценки (всего 5 столбцов).

2.       В каждую ячейку столбца ввести формулу. Суть формулы состоит в том, что напротив фамилии студента в ячейке соответствующего вспомогательного столбца вид полученной им оценки отмечается как 1. В остальных ячейках этой строки в других дополнительных столбцах будет стоять 0. Таким образом, полученная оценка в каждом столбце будет отмечаться по следующему условию: в столбце пятерок — если студент получил 5, то отображается 1, иначе — 0; в столбце четверок — если студент получил 4, то отображается 1, иначе — 0; в столбце троек — если студент получил 3, то отображается 1, иначе — 0; в столбце двоек — если студент получил 2, то отображается 1, иначе — 0; в столбце неявок — если не явился на экзамен, то отображается 1, иначе — 0.

3.       В нижней части таблицы ввести формулы подсчета суммарного количества полученных оценок определенного вида и общее количество оценок.

4.       Сверить полученные общий вид таблицы, результаты и структуры формул с тем, что показано на рис.

4.2 (в режиме отображения значений) и на рис. 4.3 (в режиме показа формул).

5.       Скопировать несколько раз (по числу экзаменов в сессию) этот шаблон на другие листы и провести коррекцию оценок по каждому предмету.

Рис. 4.2. Электронная таблица Экзаменационная ведомость в режиме отображения значений

Рис. 4.3. Электронная таблица Экзаменационная ведомость в режиме отображения формул

Порядок выполнения задания 3

1. Загрузите с жесткого диска рабочую книгу с именем Session:

§  выполните команду Файл, Открыть;

§  в диалоговом окне установите следующие параметры:

Папка: имя личного каталога на рабочем диске

Имя файла: Session

Тип файла: Книга Microsoft Excel

2.       Проделайте подготовительную работу, вводя названия (5, 4, 3, 2, неявки) соответственно в ячейки F5,

G5, Н5,15, J5 вспомогательных столбцов (см. рис. 4.2).

3.       В эти столбцы F - J введите вспомогательные формулы (см. ниже). Суть формулы состоит в том, что вид оценки фиксируется напротив фамилии студента в ячейке соответствующего вспомогательного столбца как 1. Например, студентка Бирючкова получила оценку 5, тогда в ячейке F6 должна стоять 1, а в остальных вспомогательных столбцах G - J в данной строке — 0.

Для ввода исходных формул воспользуйтесь Мастером функций. Рассмотрим эту технологию на примере ввода формулы в ячейку F6:

§  установите курсор в ячейку F6 и выберите мышью на панели инструментов кнопку Мастера функций; § в 1-м диалоговом окне выберите вид функции

Категория — логические

Имя функции — ЕСЛИ

§  щелкните по кнопке <ОК>;

§  во 2-м диалоговом окне, устанавливая курсор в каждой строке, введите соответствующие операнды логической функции:

Логическое выражение — D6 = 5

Значение, если истина, — 1

Значение, если ложно, — О

§  щелкните по кнопке <ОК>

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

4. С помощью Мастера функции введите формулы аналогичным способом в остальные ячейки данной строки. В результате в ячейках F6 - J6 должно быть:

                                                                      Адрес ячейки               Формула

                                                                      F6                         ЕСЛИ(D6=5;1;0)

                                                                      G6                         ЕСЛИ(D6=4;1;0)

                                                                      Н6                         ЕСЛИ(D6=3;1;0)

                                                                      I6                          ЕСЛИ(D6=2;1;0)

                                                                      J6                          ЕСЛИ(D6="н/я";1;0)

         5.    Скопируйте эти формулы во все остальные ячейки дополнительных столбцов:

§  выделите блок ячеек F6:J6;

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

6. Определите имена блоков ячеек по каждому дополнительному столбцу. Рассмотрите это на примере дополнительного столбца F:

§  выделите все значения дополнительного столбца, например F6:адрес ячейки в столбце, в которой находится последнее значение;

§  введите команду Вставка, Имя, Присвоить;

§  в диалоговом окне в строке Имя введите слово ОТЛИЧНО;

§  щелкните по кнопке <Добавить>;

§  проводя аналогичные действия с остальными столбцами, вы создадите еще несколько имен блоков ячеек: ХОРОШО, УДОВЛЕТВОРИТЕЛЬНО, НЕУДОВЛЕТВОРИТЕЛЬНО, НЕЯВКА.

7.       Выделите столбцы F-J целиком и сделайте их скрытыми: § установите курсор на названии столбцов и выделите столбцы F-J; § введите команду Формат, Столбец, Скрыть.

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

§  установите указатель мыши в ячейку С18 подсчета количества отличных оценок;

§  щелкните по кнопке Мастер функций;

§  в диалоговом окне Мастер функций выберите: Категория — Математические, функция — СУММ; щелкните по кнопке <ОК>;

§  в следующем диалоговом окне в строке Число 1 установите курсор и введите команду Вставка, Имя, Вставить;

§  в появившемся диалоговом окне выделите имя блока ячеек Отлично, щелкните по кнопке <ОК>; § повторите аналогичные действия для подсчета количества других оценок в ячейках С19-С22.

         9.    Подсчитайте общее количество (ИТОГО) всех полученных оценок другим способом (см. рис.4.2):

§  установите курсор в пустой ячейке С23. Эта ячейка должна обязательно находиться под ячейками, где подсчитывались суммы по всем видам оценок;

§  щелкните по кнопке <∑>;

§  выделите блок ячеек, где подсчитывались суммы по всем видам оценок, и нажмите клавишу <Enter>.

10. Переименуйте текущий лист:

§  установите курсор на имени текущего листа и вызовите контекстное меню;

§  выберите параметр Переименовать и введите новое имя, например Экзамен 1.

11. Скопируйте несколько раз текущий лист Экзамен 1:

§  установите курсор на имени текущего листа и вызовите контекстное меню;

§  выберите параметр Переместить/Скопировать, поставьте флажок Создавать копию и параметр Переместить в конец, нажмите <ОК>. Обратите внимание на автоматическое наименование ярлыков новых листов.

12.   Выполните команду Сервис, Параметры, вкладка Вид и установите флажок Формулы. Сравните ваш результат с рис. 4.3, а затем, повторно выполнив команду Сервис, Параметры и сняв флажок Формулы, сравните ваши результаты с рис. 4.2.

13.   Сохраните рабочую книгу с экзаменационными ведомостями:

§  выполните команду Файл, Сохранить как;

§  в диалоговом окне установите следующие параметры:

Папка: имя личного каталога на рабочем диске

Имя файла: Session

Тип файла: Книга Microsoft Excel

14. Закройте рабочую книгу командой Файл, Закрыть.

Задание 4. Подготовка ведомости назначения студентов на стипендию по результатам экзаменационной сессии

Алгоритмом действий по технологии выполнения данного задания:

1.       Загрузите экзаменационную ведомость.

2.       На новом листе создайте ведомость стипендии (см. рис. 4.4) и скопируйте в нее список группы из экзаменационной ведомости, отображенный на рис. 4.2.

3.       Вычислите средний балл по результатам сдачи экзаменов по каждому студенту.

4.       Используя минимальное значение стипендии и учитывая, что сданы все экзамены, введите формулу начисления стипендии по условию:

§ если средний балл не менее 4,5, выплачивается 50%-ная надбавка к минимальной стипендии; § если средний балл от 3 (включительно) до 4,5, выплачивается минимальная стипендия; § если средний балл меньше 3, стипендия не выплачивается.

5. Подсчитайте сумму стипендиального фонда для всей группы.

ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ Группа № _____ Минимальный размер стипендии -

Итого стипендиальный фонд по группе Рис. 4.4. Форма стипендиальной ведомости

Порядок выполнения задания 4

1.       Загрузите с жесткого диска рабочую книгу с именем Session, выполнив команду Файл, Открыть.

2.       Создайте в этой книге новый лист — Стипендия, на который из столбцов А и В листа Экзамен 1 скопируйте фамилии и порядковые номера студентов.

3.       Оформите название и шапку ведомости назначения на стипендию согласно рис. 4.4. Для этого введите название таблицы — ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ Группа № и названия столбцов - № п/п; Фамилия, имя, отчество; Стипендия, задайте шрифт и тип выделения — полужирный.

4.       Укажите размер минимальной стипендии в ячейке D3.

5.       Вставьте два дополнительных столбца перед столбцом Стипендия и введите их названия — Средний балл и Кол-во сданных экзаменов. Сверьте полученное изображение электронной таблицы с рис. 4.5. Скорректируйте расхождение.

6.       Введите формулу вычисления среднего балла студента в ячейку С6 для первого студента, например Бирючковой (см. рис. 4.5). Для этого:

§  установите курсор в ячейке С6;

§  щелкните по кнопке <Мастер функций> на панели Стандартная и выберите в диалоговом окне параметры:

Категория: Статистические

Имя: СРЗНАЧ

§  щелкните по кнопке <ОК>, появится панель ввода аргументов функции СРЗНАЧ;

§  установите курсор в 1-й строке (имя Число 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;

§  установите курсор во 2-й строке (имя Число 2), щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену;

§  установите курсор в 3-й строке (имя Число 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по второму экзамену;

§  щелкните по кнопке <ОК>;

§  в ячейке С6 появится значение, рассчитанное по формуле =СРЗНАЧ('Экзамен 1'!D6;'Экзамен 1 (2)'!D6;'Экзамен 1 (3)'!D6).

Рис. 4.5. Электронная таблица Ведомость назначения на стипендию в режиме отображения значений

         7.    Скопируйте формулу по всем ячейкам столбца С. Для этого:

§  установите курсор в ячейке С6;

§  наведите указатель мыши на правый нижний угол этой ячейки, добившись появления черного крестика;

§  нажмите левую кнопку мыши и протащите ее до конца этого столбца;

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

8. Введите в столбец D формулу подсчета количества сданных каждым студентом экзаменов с учетом неявок. При этом технология ввода будет аналогична описанной в п.6:

§  установите курсор в ячейке D6;

§  щелкните по кнопке <Мастер функций> на панели Стандартная и выберите в диалоговом окне параметры:

Категория: Статистические

Имя: СЧЕТ

§  щелкните по кнопке <ОК>, появится панель ввода аргументов функции СЧЕТ;

§  установите курсор в 1-й строке (имя Значение 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;

§  установите курсор во 2-й строке (имя Значение 2), щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену;

§  установите курсор в 3-й строке (имя Значение 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по второму экзамену;

§  щелкните по кнопке <ОК>; в ячейке D6 появится значение, рассчитанное по формуле =СЧЁТ('Экзамен 1'!D6;'Экзамен 1 (2)'!D6;'Экзамен 1 (3)'!D6).

9.       Скопируйте формулу по всем ячейкам столбца D так же, как вы делали в п.7.

10.   Введите формулу для вычисления размера стипендии студента в ячейку Е6. Эта формула должна иметь следующий вид:

=ЕСЛИ(И(C6>=4,5;D6=3);$D$3*1,5;ЕСЛИ(И(C6>=3;D6=3);$D$3;0)), при вводе формулы необходимо учитывать следующее:

§  В структуре формулы имеются вложенные функции И(...), ЕСЛИ(...). Для ввода этих функций надо воспользоваться кнопкой вызова функции, находящейся в строке ввода под панелями.

§  При наборе формулы автоматически расставляются круглые скобки и разделительный символ — точка с запятой.

§  В процессе набора формулы постоянно сравнивайте ее с выражением, которое приведено в этом пункте выше.

§  В числах для отделения целой части от дробной используется либо точка, либо запятая, что зависит от установок Excel.

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

§  установите курсор в ячейке Е6;

§  щелкните по кнопке <Мастер функций> на панели Стандартная и выберите в диалоговом окне параметры:

Категория: Логические

Имя: ЕСЛИ

§  щелкните по кнопке <ОК>, появится панель ввода аргументов функции ЕСЛИ;

§  курсор будет находиться в 1-й строке (имя — Логическое выражение) панели ввода аргументов функции;

§  нажмите кнопку вызова функции в строке ввода, выберите категорию Другие функции и функцию И, нажмите кнопку <ОК>;

§  появится второе окно ввода аргументов функции И, курсор автоматически будет установлен в строке Логическое_значение1;

§  щелкните в ячейке С6, где показан средний балл этого студента, и наберите с клавиатуры условие >=4,5. В результате в этой строке должно быть выражение С6>=4,5

§  установите курсор на второй строке Логическое_значение2 и аналогично сформируйте выражение, которое указывает необходимое количество сданных экзаменов (в данном примере — это число 3) D6=3

§  щелкните по кнопке <ОК>. В результате в строке ввода должно появиться выражение =ЕСЛИ(И(C6>=4,5;D6=3))

§  щелкните мышью на строке ввода, появится первое окно ввода аргументов для функции ЕСЛИ;

§  установите курсор во 2-й строке (имя — Значение_если_истина), щелкните в ячейке D3 и нажмите клавишу <F4>. Появится символ $ перед именем столбца и номером строки. Введите выражение *1,5. В результате в этой строке будет выражение $D$3*1,5

§  установите курсор в 3-й строке (имя Значение_если_ложь) и по аналогичной технологии введите оставшуюся часть формулы ЕСЛИ(И(C6>=3;D6=3);$D$3;0

§  после окончания формирования формулы нажмите кнопку <ОК>.

11.   Скопируйте эту формулу в другие ячейки столбца Е так же, как вы делали в п. 7 и 9.

12.   Введите в ячейку E18 формулу для расчета стипендиального фонда по группе.

13.   Установите в ячейках D3, E6:E16 и E18 формат представления – Денежный. Для этого:

§ выделите блок ячеек, нажмите правую кнопку мыши для вызова контекстного меню; § введите команду контекстного меню Формат ячеек; § на вкладке Число выберите опцию: Числовые форматы: Денежный.

14. Проверьте работоспособность таблицы:

§ вводите другие оценки в экзаменационные ведомости; § измените минимальный размер стипендии.

15.   Сохраните рабочую книгу командой Файл, Сохранить.

16.   Закройте рабочую книгу командой Файл, Закрыть.

4.3 Построение, редактирование и форматирование диаграмм

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

Диаграммы — это графическое представление данных. Они используются для анализа и сравнения данных, представления их в наглядном виде.

Диаграмма состоит из элементов: линий, столбиков, секторов, точек и т.п. Каждому элементу диаграммы соответствует число в таблице. Числа и элементы диаграммы связаны между собой таким образом, что при изменении чисел автоматически изменяется изображение элементов диаграммы и наоборот.

Различают два вида диаграмм: внедренные диаграммы — сохраняются на рабочем листе вместе с данными; диаграммные листы — диаграмма в формате полного экрана на новом листе.

Диаграмма создается с помощью Мастера диаграмм, вызываемого командой Вставка, Диаграмма или кнопкой Мастер диаграмм на панели Стандартная, либо кнопкой Тип диаграммы на панели Диаграмма.

Мастер диаграмм позволяет строить диаграммы 14 стандартных типов плоскостного и объемного представления (с областями, линейчатая, гистограмма, график, кольцевая, лепестковая, точечная, пузырьковая, поверхностная и др.) и 22 нестандартных типа.

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

Этап 1 . Выбор типа и формата диаграммы. На этом этапе необходимо выбрать тип диаграммы и задать формат, который делает ее более выразительной. После выбора надо нажать кнопку <Далее> и перейти на следующий этап.

Этап 2. Выбор и указание диапазона данных для построения диаграммы. На этом этапе задается диапазон данных, для которого будет построена диаграмма. Для этого в таблице с помощью переключателя <Ряды в:> укажите расположение данных — по строкам или по столбцам будет строиться выбранный тип диаграммы. Далее с помощью мыши выделите необходимый блок ячеек, адрес которого автоматически отобразится в строке Диапазон. В окне образца будет отображаться выбранный тип диаграммы для заданного диапазона данных.

Указание диапазона данных, расположенных в несмежных рядах или столбцах, должно производиться при нажатой клавише <Ctrl>. В строке Диапазон адреса несмежных интервалов данных будут разделяться точкой с запятой.

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

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

Рис. 4.7. Блоки несмежных ячеек для построения диаграммы

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

Этап 3 . Задание параметров диаграммы. Задание параметров диаграммы осуществляется в окнах вкладок Мастера диаграмм.

На вкладке Заголовки вводятся поочередно на соответствующую строку название диаграммы, название оси X, название оси Y, название оси Z.

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

На вкладке Линии сетки устанавливаются переключатели отображения сетки на диаграмме.

На вкладке Легенда указывается место расположения легенды.

На вкладке Таблица данных устанавливается переключатель отображения на диаграмме таблицы исходных данных.

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

Этап 4. Размещение диаграммы. Созданную диаграмму можно разместить на том же листе, где находится таблица с исходными данными, либо на отдельном листе. В диалоговом окне Мастер диаграмм на этапе 4 для этого надо установить соответствующий переключатель и нажать кнопку <Готово>.

Редактирование диаграмм

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

переопределить исходный интервал ячеек, на основании которых построена диаграмма; переопределить ориентацию рядов и их название; изменить данные, используемые для подписей оси X; изменить параметры диаграммы (заголовки, оси, линии сетки, легенду, подписи данных); изменить размещение диаграммы.

Форматирование типов диаграмм

Табличный процессор Excel обеспечивает удобный режим работы для «подбора» наиболее подходящего типа диаграммы в целом, для группы рядов или отдельного ряда данных. Изменение типа диаграммы можно выполнить для любой построенной диаграммы, как внедренной, так и отдельного диаграммного листа. Подобные изменения могут быть осуществлены либо посредством кнопки «Тип диаграммы» на панели инструментов Диаграмма, либо за счет перехода к первому этапу построения диаграмм и выбора команды Тип диаграммы в контекстном меню. Кроме того, контекстное меню дает также возможность корректировать вид трехмерных диаграмм. Для изменения формата трехмерной проекции необходимо выполнить команду <Объемный вид> контекстного меню. В результате перед пользователем предстает диалоговое окно <Формат трехмерной проекции>, которое позволяет изменить угол поворота рядов вокруг своей оси, уменьшить или увеличить масштаб, изменить угол перспективы и возвышения всей диаграммы, представить проекцию в изометрии.

Форматирование элементов диаграммы

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

1.       Указать курсором мыши на любой компонент диаграммы (ряд, ось, подписи, легенду и т.д.), щелкнуть по нему правой кнопкой и в появившемся контекстном меню выполнить команду Формат.

2.       В окне «Элементы диаграммы» панели инструментов Диаграмма выбрать необходимый компонент и нажать соседний с окном значок Формат.

Форматирование области диаграммы и области построения диаграммы

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

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

Форматирование рядов данных

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

При выделении отдельного ряда в поле имен появляется стандартное обозначение Р(номер) — номер ряда данных, в строке формул выводится формула ряда, например: =РЯД(Лист1!$С$1;Лист1!$А$2:$А$6;Лист1!$С$2:$С$6;2),

где Лист1!$С$1 — абсолютная ссылка на ячейку, содержащую название ряда, которое используется

при построении легенды;

Лист1!$А$2:$А$6 — блок ячеек, содержащий метки (категории) значений оси X; Лист1!$С$2:$С$6 — блок ячеек, содержащий элементы значений ряда; 2 — порядковый номер ряда.

Выделенный ряд можно удалить.

Форматирование рядов диаграмм осуществляется при помощи диалогового окна «Формат ряда данных».

Существует некоторое отличие в составе вкладок диалогового окна «Формат ряда данных», обусловленное типом диаграммы, который воспроизводят данные ряды, а именно плоскими и объемными диаграммами. Так вкладки Вид, Подписи данных. Порядок рядов, Параметры являются общими для двухмерных и объемных (трехмерных) диаграмм. При этом для двухмерных диаграмм добавляются вкладки Ось и У-погрешности, а для трехмерных — добавляется вкладка Фигура.

На вкладке Вид задаются установки внешнего вида элементов ряда.

На вкладке Подписи данных выполняется настройка типа подписей (отсутствие, значения, категория и т.п.) для элементов ряда.

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

Вкладка Параметры позволяет задать важнейшие характеристики диаграмм. Состав настраиваемых параметров зависит от типа диаграммы:

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

0 — маркеры различных рядов находятся рядом;

-100 — маркеры рядов отстоят друг от друга на максимальном расстоянии; 100 — маркеры рядов сливаются вместе.

Параметр Ширина зазора устанавливает величину интервала между кластерами (группой элементов данных различных рядов, отнесенной к одной и той же категории оси X), изменяется в пределах от 0 до 500. Доступна для групп линейчатых диаграмм, графиков, гистограмм, объемных линейчатых диаграмм и объемных гистограмм.

Параметр <Соединять значения ряда> обеспечивает соединение линией вершин маркеров данных одного ряда. Применима только к наложенным линейчатым диаграммам и наложенным гистограммам.

Параметр <Разноцветные точки> элементов данных задается только для диаграмм, содержащих один ряд данных. Доступна для групп линейчатых диаграмм, гистограмм, графиков, круговых и кольцевых диаграмм, радаров, XY-точечных диаграмм, объемных линейчатых диаграмм, объемных гистограмм и объемных круговых диаграмм.

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

На вкладке <Ось> задается связь ряда данных с основной или вспомогательной осью Y диаграммы.

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

Х— порядковый номер элемента или определенная категория; Y— значение элемента в соответствии с его единицей измерения.

Различные ряды диаграммы могут представлять данные, несопоставимые по масштабу. Поэтому при построении диаграмм можно привязать определенный ряд к основной или вспомогательной оси Y, причем к одной оси Y может быть сразу отнесено несколько рядов. Для каждой оси Y выполняется независимая от другой оси Т настройка.

На вкладке <Y-погрешности> определяется тип планок погрешностей для элементов ряда согласно его статистическим характеристикам.

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

Форматирование осей диаграммы

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

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

Объемная диаграмма стоит на основании и окружена стенками, которые можно самостоятельно форматировать.

При форматировании осей активизируется:

вкладка Вид — выбирается внешнее оформление оси (линии), указывается наличие и расположение основных и вспомогательных засечек и их меток; вкладка Шрифт — выбирается вид и размер шрифта меток засечек;

вкладка Шкала — задается масштаб значений элементов данных ряда на оси и разметка оси (расстояние между засечками и линиями сетки), значение точки пересечения с осью Х (для плоских) или с основанием (для объемных) диаграмм; выбирается при необходимости логарифмическая шкала и обратный порядок значений (для оси категорий — изменение порядка следования меток слева направо, для оси значений — изменение значения от меньшего к большему); вкладка Число — задается формат меток засечек или самих числовых значений на оси; вкладка Выравнивание — изменяется ориентация текста меток засечек.

Форматирование сетки

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

Форматирование легенды

Легенда — специальное окно, содержащее для каждого ряда данных ключ и поле-название ряда. Ключ легенды повторяет цвет и узор, заданный для элементов данных ряда. Легенда может создаваться в автоматическом режиме, если при построении диаграммы интервал ячеек включал названия рядов. Если имена рядов явно не заданы, то легенда использует стандартное имя Ряд (номер).

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

Тренды

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

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

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

§  тренд в анализируемый период должен развиваться эволюционно;

§  процесс, представленный диаграммой, должен обладать определенной инерционностью. Тренд можно строить для диаграмм типа:

§  линейчатый график;

§  гистограмма;

§  диаграмма с областями;

§  XY-точечная диаграмма.

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

§  построить диаграмму для одного ряда данных;

§  выделить эту диаграмму, щелкнув по ней левой кнопкой мыши. На диаграмме должны появиться маркеры;

§  вызвать контекстное меню и выполнить команду Добавить линию тренда;

§  в диалоговом окне «Линия тренда» на вкладке Тип выбрать тип тренда, а на вкладке Параметры установить параметры: Количество периодов прогноза, Показывать уравнение на диаграмме, Поместить на диаграмму величину достоверности аппроксимации; § нажать кнопку <ОК>.

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

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

Для таблицы на рис. 3.27 постройте два вида диаграмм — внедренную на лист с исходными данными и на отдельном листе. Для этого вам необходимо выполнить следующие действия:

1. Провести подготовительную работу, которая состоит в следующем:

§  создать рабочую книгу;

§  сохранить рабочую книгу;

§  переименовать Лист1 на Статистика.

2.       Создать таблицу в соответствии с рис. 4.8 и вычислить средние значения.

3.       Построить внедренную диаграмму, оформив ее так, как показано на рис. 4.9.

4.       Построить диаграмму другого типа и разместить ее на отдельном листе.

Рис. 4.8. Половой состав ВИЧинфицированных в Саратовской области по состоянию на 01.01.2003г.

Рис. 4.9. Диаграмма типа Гистограмма

Порядок выполнения задания 5

1. Проделайте подготовительную работу:

§  создайте новую рабочую книгу командой Файл, Создать;

§  сохраните в выбранной папке созданную книгу под именем Diag командой Файл, Сохранить как; § переименуйте Лист1:

установите курсор на Лист 1 и вызовите правой кнопкой мыши контекстное меню; выполните команду Переименовать и введите новое имя — Статистика.

2. Создайте таблицу согласно рис. 4.8, например начиная с ячейки А1. В пустые ячейки с названием Среднее введите формулу вычисления среднего, например в ячейку В9 столбца Мужчины:

§  вызовите Мастер функций, щелкнув по его кнопке на панели инструментов;

§  выберите категорию функций Статистическая, имя функции — СРЗНАЧ, щелкните по кнопке <ОК>;

§  введите в первую строку диалогового окна адреса первой и последней ячеек столбца со значениями, используя для этого мышь, например В2:В8;

§  скопируйте формулу в ячейку С9 столбца Женщины.

3. Постройте внедренную диаграмму, выполнив следующие операции:

§  нажмите кнопку Мастер диаграмм или выполните команду Вставка, Диаграмма. Этап 1 . Выбор типа и формата диаграммы:

§  на вкладке Стандартные выберите тип диаграммы Гистограмма и вид диаграммы — номер 1; § щелкните по кнопке <Далее>.

Этап 2. Выбор и указание диапазона данных для построения диаграммы:

§  на вкладке Диапазон данных установите переключатель Ряды в столбцах,

§  выделите диапазон данных А2:С9;

§  в том же диалоговом окне щелкните по вкладке Ряд,

§  в окне Ряд выделена строка с названием Ряд1, установите курсор в строке Имя и щелкните в ячейке В1 с названием Мужчины;

§  в окне Ряд щелкните по названию Ряд2, установите курсор в строке Имя и щелкните в ячейке С1 с названием Женщины;

§  для создания подписей по оси Х щелкните в строке Подписи оси Х и выделите данные первого столбца таблицы, т.е. диапазон А2:A9;

§  щелкните по кнопке <Далее>.

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

§  на вкладке Заголовки введите названия в соответствующих строках:

Название диаграммы: Половой состав ВИЧ-инфицированных в Саратовской области Ось X: Годы

Ось Y: Количество ВИЧ-инфицированных

§  на вкладке Легенда поставьте флажок Добавить легенду и переключатель Справа; § щелкните по кнопке <Далее>.

Этап 4. Размещение диаграммы:

§  установите переключатель Поместить диаграмму на имеющемся листе и выберите из списка лист Статистика;

§  щелкните по кнопке <Готово>;

§  в результате на рабочем листе будет создана внедренная диаграмма. Сравните результат с рис. 4.8.

Для изменения размера диаграммы установите курсор мыши в поле диаграммы и один раз щелкните левой кнопкой на контуре диаграммы. На контуре появятся выделенные черные метки (квадраты). Установите курсор мыши на эти метки. Курсор мыши изменит свое начертание на черную тонкую двустороннюю стрелку ↔. Удерживая нажатой левую кнопку, протащите мышь для изменения размеров поля диаграммы.

4. Постройте диаграмму другого типа на отдельном листе. Для этого выполните действия, аналогичные описанным в п.3, но на четвертом шаге установите переключатель На отдельном листе.

Задание 6. Редактирование диаграмм

Отредактируйте построенную по данным рис. 4.8 диаграмму в соответствии с заданием. Для этого:

1.       Выполните подготовительную работу — скопируйте диаграмму в другое место листа.

2.       В исходную таблицу добавьте столбец Дети со значениями, приведенными на рис. 4.10.

3.       Измените формат диаграммы на объемный.

4.       Вставьте в диаграмму столбец с данными о ВИЧ-инфицированных детях и измените диаграмму так, чтобы она отражала количество ВИЧ-инфицированных (ось Y) в каждом году (ось Z) в зависимости от состава (ось X).

5.       Измените параметры диаграммы — названия осей, уберите легенду.

6.       Разместите диаграмму на отдельном листе.

Порядок выполнения задания 6

         1.    Скопируйте всю область диаграммы:

§  выделите внедренную диаграмму, щелкнув левой клавишей мыши в области диаграммы один раз. Появятся метки на контуре области диаграммы;

§  выполните команду Правка, Копировать;

§  переместите курсор в новое место на рабочем листе; § выполните команду Правка, Вставить.

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

3.       Измените формат диаграммы, сделав ее объемной. Для этого:

§  установите курсор мыши во внутренней незаполненной области диаграммы и, щелкнув правой кнопкой, вызовите контекстное меню диаграммы;

§  выполните команду Тип диаграммы и выберите на вкладке Стандартные тип Гистограмма, последний из представленных форматов (3-мерная гистограмма);

§  нажмите кнопку <ОК> и убедитесь в изменении формата диаграммы.

         4.    Вставьте в диаграмму столбцы, отражающие количество ВИЧ-инфицированных детей. Для этого:

§  установите курсор мыши во внутренней незаполненной области диаграммы и, щелкнув правой кнопкой, вызовите контекстное меню диаграммы;

§  выполните команду Исходные данные и измените параметры;

§  во вкладке Диапазон данных укажите весь диапазон данных A2:D9, включив информацию столбца Дети;

§  установите переключатель Ряды в положение столбцах и нажмите клавишу <ОК>; § на вкладке Ряд в окне Подписи оси Х введите диапазон ячеек B1:D1; § нажмите кнопку <ОК>.

5. Измените параметры диаграммы:

§  установите курсор мыши во внутренней незаполненной области диаграммы и, щелкнув правой кнопкой, вызовите контекстное меню диаграммы; § выполните команду Параметры диаграммы; § укажите на вкладке Заголовки:

Название диаграммы — без изменений

Ось X: Годы

Ось Y: Пол

Ось Z: Количество ВИЧ-инфицированных

§  на вкладке Легенда снимите флажок Добавить легенду; § щелкните по кнопке <Далее>; § Нажмите кнопку <ОК>.

6. Активизируйте контекстное меню диаграммы и выполните команду Размещение. Установите переключатель Поместить диаграмму на листе в положение отдельном и нажмите кнопку <ОК>. Сравните свой результат с рис.4.11.

Рис. 4.10. Количество ВИЧинфицированных в Саратовской области по состоянию на 01.01.2003г.

Рис. 4.11. Итоговый результат задания по редактированию диаграмм

Задание 7. Форматирование диаграмм

Проведите форматирование диаграммы. Для этого:

1.       Измените настройку объемного вида трехмерной диаграммы.

2.       Измените настройку области диаграммы и области построения диаграммы.

3.       Измените форму представления данных на диаграмме: рядов данных и их элементов.

4.       Измените отображение осей диаграммы.

5.       Проведите форматирование сетки в области построения диаграммы.

6.       На любой ранее созданной диаграмме вставьте новую легенду и проведите ее форматирование.

7.       Сравните диаграмму, полученную по результатам форматирования ее элементов, с рис. 4.12.

Порядок выполнения задания 7

1. Измените настройки параметров диаграммы:

§  активизируйте внедренную диаграмму (см. рис. 4.11), щелкнув правой кнопкой мыши в пустой области диаграммы;

§  в появившемся меню выберите команду Объемный вид;

§  в появившемся диалоговом окне установите следующие параметры:

Возвышение: 15

Поворот:20

Изометрия: флажок

Автомасштаб: флажок

§  нажмите кнопку <ОК>.

2. Проведите форматирование области диаграммы и области построения диаграммы:

§  активизируйте внедренную диаграмму, щелкнув правой кнопкой мыши в любом из 4-х углов диаграммы, и в появившемся меню выберите команду Формат области диаграммы; § задайте на вкладках диалогового окна установки:

Вкладка Вид: Рамка — невидимая, с тенью

Заливка: желтый цвет

Вкладка Шрифт: Шрифт: Times New Roman Cyr

Начертание: Обычный

Размер: 14

§  выйдите из диалогового окна нажатием кнопки <ОК>;

§  установите курсор в области построения диаграммы и выделите ее, щелкните правой кнопкой мыши для вызова контекстного меню;

§  в контекстном меню выберите команду Формат области построения; § задайте в диалоговом окне «Вид» установки:

Рамка: цвет - авто

Заливка: белый цвет

§  выйдите из диалогового окна нажатием кнопки <ОК>.

         3.    Проведите форматирование рядов данных и их элементов:

§  установите указатель мыши на ряде 1 (Мужчины) и нажмите правую кнопку мыши. В контекстном меню выполните команду Формат рядов данных; § на вкладке Параметры произведите настройку:

Глубина зазора: 200

Ширина зазора: 170

Глубина диаграммы: 90

§  на вкладке Порядок рядов в окне установите курсор на название Дети и щелкните по кнопке <Вверх> два раза, затем установите курсор на название Женщины и щелкните один раз по кнопке <Вверх>; § на вкладке Подписи данных установите переключатель Значения, § на вкладке Вид установите параметры:

Граница: цвет - авто

Заливка: синий цвет

§  нажмите клавишу <ОК>;

§  повторите установку параметров на вкладке Вид для остальных рядов диаграммы: для ряда 2 — фиолетовый, ряда 3 — зеленый цвета заливки.

         4.    Проведите форматирование осей диаграммы:

§  выделите ось X, установив на ней курсор мыши и щелкнув один раз правой кнопкой. В контекстном меню выполните команду Формат оси и установите параметры на вкладках:

Вкладка Вид: Метки делений — внизу, основные — наружу

Вкладка Шкала: Число категорий между подписями делений — 1, число категорий между делениями — 2

Вкладка Выравнивание: 30 слева направо

§  выделите ось Y и выполните ее форматирование:

Вкладка Вид: Метки делений — внизу, основные — наружу

Вкладка Шкала: Число категорий между подписями делений — 1, число категорий между делениями — 2 Вкладка

Выравнивание: Авто

§  выделите ось Z и выполните ее форматирование:

Вкладка Вид: Метки делений — рядом с осью, основные — наружу

Вкладка Шкала: минимальное значение — 0, максимальное значение — 1400, цена основных делений — 200, цена промежуточных делений — 50, плоскость ХУ пересекает в значении 0;

Вкладка Число: Числовые форматы — общий

Вкладка Выравнивание: слева направо

Рис. 4.12. Диаграмма после выполнения задания по форматированию ее элементов

         5.    Проведите форматирование сетки, стен и основания:

§  установите указатель мыши в один из четырех углов диаграммы и нажмите правую кнопку. В контекстном меню выберите команду Параметры диаграммы. Во вкладке Линии сетки установите параметры:

Ось X: флажки — основные линии и промежуточные линии

Ось Y: флажки — основные линии и промежуточные линии

Ось Z: флажок — основные линии

§  установите указатель мыши в область стен диаграммы и нажмите правую кнопку мыши. В появившемся меню выберите команду Формат стенок. Во вкладке Вид выберите светло-желтый цвет заливки;

§  установите указатель мыши в область основания диаграммы и нажмите правую кнопку мыши. В появившемся меню выберите команду Формат основания. Во вкладке Вид выберите светло-желтый цвет заливки.

6. Проведите форматирование легенды:

§  установите указатель мыши в один из четырех углов диаграммы и вызовите контекстное меню нажатием правой кнопки мыши. В меню выберите команду Параметры диаграммы и во вкладке Легенда поставьте флажок Добавить легенду и переключатель Справа;

§  установите указатель мыши на созданное окно легенды и щелкните правой кнопкой. Выполните команду Формат легенды и установите следующие параметры:

Вкладка Вид: рамка — обычная, заливка — светло-желтый цвет

Вкладка Размещение: в верхнем правом углу

7. Сравните созданную вами диаграмму с образцом на рис. 4.12. В случае необходимости измените размеры Области построения диаграммы, Легенды, Заголовка диаграммы и других элементов диаграммы. Для этого установите курсор мыши на элементе диаграммы и один раз щелкните левой кнопкой на контуре элемента. На контуре появятся выделенные черные метки (квадраты). Установите курсор мыши на эти метки. Курсор мыши изменит свое начертание на черную тонкую двустороннюю стрелку ↔. Удерживая нажатой левую кнопку, протащите мышь для изменения размеров поля элемента.

Задание 8. Построение тренда

1.       Постройте гистограмму распределения количества ВИЧ-инфицированных мужчин по годам, на основании данных, приведенных на рис. 4.8.

2.       Постройте линейный тренд для гистограммы.

3.       Постройте полиномиальный тренд для гистограммы.

4.       Оформите диаграмму и линии тренда так, как представлено на рис. 4.13.

Порядок выполнения задания 8

1. Постройте диаграмму распределения по годам ВИЧ-инфицированных мужчин:

§  вызовите Мастер диаграмм, нажав соответствующую кнопку на панели инструментов;

§  выберите на вкладке Стандартные обычный тип гистограммы и нажмите кнопку <Далее>;

§  в строку Диапазон установите курсор и выделите в таблице блок ячеек А2:В8; § оформите заголовки и названия осей так, как показано на рис. 4.13; § закончите построение диаграммы.

2. Постройте линейный тренд для гистограммы. Для этого:

§  установите указатель мыши на один из столбиков гистограммы и щелкните левой кнопкой мыши так, чтобы появились на всех столбиках черные метки;

§  для выделенной гистограммы вызовите контекстное меню, щелкнув правой кнопкой мыши; • выполните команду Добавить линию тренда;

§  в диалоговом окне «Линия тренда» на вкладке Тип выберите окошко Линейная; § на вкладке Параметры установите параметры:

Прогноз: вперед на 1 период

Показывать уравнение на диаграмме: установите флажок

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

§  • нажмите кнопку <ОК>; на диаграмме появится линия тренда и описывающее ее уравнение.

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

4.       Оформите диаграмму и линии тренда так, как представлено на рис. 4.13.

Рис. 3.32. Гистограмма и тренды