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

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

Порядок выполнения.

1. 1. Прочитать текст на страницах 17- 24.

2. В папке Группа_N, где N -номер вашей группы создать папку Е2, скопировать в нее файл 00000_371\Inform\Excel\Ex_2.xls.

3. Запустить табличный процессор Excel и открыть в нем рабочую книгу Ex_2.xls.

4. При помощи кнопки Автосумма панели инструментов Стандартная ввести в диапазон ячеек В8: Е8 на листе Таблица функции для расчета общего количества преступлений и количества краж в городе N за предыдущий и текущий годы.

5. Определить долю краж за предыдущий год (используя строку формул, ввести в ячейку F4 формулу =D4/B4*100, затем скопировать формулу из ячейки F4 в диапазон ячеек F5: F8).

6. Посчитать долю краж за текущий год (введите в ячейку G4 - формулу =Е4/С4, перетаскиванием маркера заполнения при нажатой правой кнопке мыши (правое перетаскивание), скопировать формулу из ячейки G5 в диапазон G6: G9 (после отпускания правой кнопки мыши следует выбрать в контекстном меню команду Заполнить значения), а затем установить для ячеек диапазона G5: G9 процентный формат).

7. Построить на листе Таблица в диапазоне А9: Е11 таблицу для расчета минимального, максимального и среднего значения количества преступлений в городе N за два последних года и объединить новую таблицу с имеющейся таблицей. Для этого в ячейки А10, А11 и А12 ввести заголовки новых строк, соответственно МИН, МАКС и СРЗНАЧ. Затем обе таблицы обвести снаружи общей границей средней толщины, для внутренних границ (использовать тонкую линию). При помощи Мастера функций ввести в пустые ячейки новой части таблицы требуемые статистические функции.

8. Сохранить измененную таблицу в своей папке.

9. Заполнить столбцы, содержащие информацию о доли (в%), которую составляют кражи за предыдущий и текущий года, расположенные на листе Копия2. Для этого, используя строку формул, ввести в ячейку D4 на листе Копия2 формулу =В4/КОПИЯ1! В4*100, а в ячейку Е4 - формулу =С4/КОПИЯ1! С4.

10. При помощи буфера обмена, скопировать формулы из ячеек D4: E4 в диапазон ячеек D5: E8. У становить для ячеек диапазона Е4: Е8 процентный формат.

11. На листе Таблица скопировать диапазон ячеек А2: С8 в диапазон ячеек I2: O4, при этом столбцы транспонируются в строки. Затем, добавить в диапазон I5: О5 строку, в которой посчитать процент краж от общего числа краж за текущий год. Чтобы скопированная формула была верной, следует установить для некоторых адресов ячеек абсолютные ссылки.

12. Для ячеек К5, L5, М5, N5 на листе Таблица отобразить влияющие ячейки (команда Влияющие ячейки из меню Сервис-Зависимости).

13. Вставить в ячейку G2 примечание - «Удельный вес краж в текущем году, процентный формат, абсолютные ссылки».

14. Присвоить листу Лист2 имя Логические функции. В ячейки D1 и Е1 этого листа ввести соответственно заголовки «Набрано баллов» и «Результат». Имя «Балл» присвоить ячейке D2. В ячейку Е2 этого листа ввести логическую функцию ЕСЛИ обеспечивающую проверку содержимого ячейки с именем «Балл» и выдающую на экран сообщение «прошел», если число в этой ячейке больше 17, и «не прошел» в противном случае (текстовые выражения в функцию вводятся в двойных кавычках).

15. На листе «Логические функции» создать таблицу по приведенному ниже образцу. В ячейку В5 ввести логическую функцию ЕСЛИ, обеспечивающую проверку содержимого ячеек диапазона В2: В4 и выдающую на экран сообщение «Сдал», если все ячейки диапазона содержат числа больше 2 и сообщение «Не сдал», если хотя бы в одной ячейке содержится число 2 (для составления условия использовать логическую функцию И).

16. Сохранить рабочую книгу в своей папке под именем Задание_2.

17. Найти в справочной системе Microsoft Excel ответ на вопрос: Можно ли посчитать количество дней между двумя датами. Ответ проиллюстрировать примером и предъявить преподавателю.

Диаграммы и графики

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

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

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

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

Мастер диаграмм строит диаграмму за четыре шага:

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

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

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

4. Выбор места размещения диаграммы.

На первом шаге (рис. 15) выбирается тип диаграммы. Разные диаграммы по-разному представляют одни и те же данные, поэтому выбор правильного типа сказывается на ясности и информативности отображения данных. Например, с помощью гистограммы можно показать изменение данных за определенный период времени и соотношение отдельных значений. Мастер диаграмм позволяет строить плоские и объемные диаграммы (14 стандартных типов и 22 нестандартного типа).

Рисунок 15. Первый шаг. Выбор типа диаграммы

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

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

Рисунок 16. Второй шаг. Ввод области данных.

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

Рисунок 17. Третий шаг. Оформление диаграммы.

Рисунок 18. Четвертый шаг. Размещение диаграммы.

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

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

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

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

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

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

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

Рисунок 19. Редактирование диаграммы.

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

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

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

Форматирование позволяет изменить вид объектов и элементов диаграммы (цвет фона, способ заливки, параметры шрифта, параметры линий и т.д.).

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

· двойным щелчком мыши по объекту или элементу диаграммы;

· командой Формат... из контекстного меню объекта или элемента диаграммы (контекстное меню выводится на экран щелчком правой кнопки мыши по соответствующей части диаграммы);

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

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