Файл: metoda2010.pdf

43

Обозначение ячейки, составленное из номера столбца и номера строки, называется относительным адресом. При копировании формул Excel автоматически изменяет этот адрес, настраивая его на новое место расположения формулы. Чтобы отменить автоматическое изменение адреса данной ячейки, ей следует назначить абсолютный адрес. Для этого необходимо перед номером столбца и (или) перед номером строки указать знак $. Например, если указан адрес $D4, то при копировании формул не будет изменяться номер столбца, если задан адрес $F$5 не будет изменяться ни номер строки, ни номер столбца. Вместо абсолютного адреса можно воспользоваться именем ячейки.

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

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

Если(Логическое Выражение; Выражение 1;Выражение 2)

и определяется следующим образом: если значение логического выражения равно 1(истина), то вычисляется выражение 1, иначе вычисляется выражение 2. Более сложные логические выражения строятся с помощью логических операций не, и, или. Возможно использование вложенных ЕСЛИ.

Модификация таблицы.

Команды Главная/Вставить ячейки, (Вставить строки на лист,

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

Удалить выделенные столбцы и строки можно с помощью команд

Главная /Удалить.

Команды, предназначенные для модификации таблицы, могут быть вызваны из контекстного меню, вызываемого нажатием на правую кнопку мыши (Вставить - для вставки строк или столбцов, Удалить - для удаления строк или столбцов, Очистить содержимое - для очистки содержимого строк или столбцов)

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

Оформление таблиц.

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

44

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

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

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

Число - выбор одного из встроенных или создание нового числового формата;

Выравнивание - определение горизонтального или вертикального выравнивания содержимого ячейки;

Шрифт - Выбор вида шрифта, размера, и стиля начертания символов; Границы - добавление рамки, определение ее вида и цвета; Заливка - выбор узора и цвета узора ячейки.

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

Настройка экрана Excel и печать .

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

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

КОНТРОЛЬНЫЕ ВОПРОСЫ.

1.Перечислить основные функциональные возможности ЭТ.

2.Что такое рабочая книга, рабочий лист?

3.Чем отличаются абсолютная и относительная адресация ячеек, как задается абсолютный адрес ячейки?

4.Каким образом задаются форматы числовых данных в Excel, какие числовые форматы Вам известны?

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

6.Как задать режим отображения формул в ЭТ?

45

ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ.

Текст заданий

Рисунок 3

Рис. 4

1. Создание таблицы.

Ввод данных. Введите название таблицы в строку 1( рис.4)

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

нажать клавишу ввода;

щёлкнуть мышью на другой ячейке;

покинуть активную ячейку с помощью клавиши стрелки;

щелкнуть по кнопке Ввод в строке формул.

Если необходимо исправить введённый текст после подтверждения ввода, следует снова выделить ячейку с ошибкой. Её содержимое будет отображено в строке формул. Для исправления ошибок - нажать клавишу F2 или щёлкнуть мышью в строке формул.

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

Введите названия граф в строку 2.

В ячейку А2 ввести “№”, в ячейку В2 –“Наименование”, в ячейку С2 – “Количество, тыс.шт.”, в ячейку D2 – “Цена, тыс. руб.”, в ячейку Е2 – “Стоимость, тыс. руб.”.

Отформатируйте текст в заголовках столбцов таблицы.

Расширьте строку, перетащив нижнюю границу в столбце заголовков строк. Выполнить вертикальное и горизонтальное выравнивание текста в строке заголовков (Главная, меню группы Выравнивание, окно Вертикальное, установить флажок “по центру”, окно Горизонтальное установить флажок “по центру”, установить флажок “переносить по словам”.) После форматирования строки заголовков, она должна выглядеть так, как на рис. 4.

Введите порядковые номера в столбец А (в ячейку a3 введите 1, в

ячейку a4 введите 2, выделите ячейки a3 и a4, установите курсор в правый нижний угол выделенной области и протяните левой кнопкой мыши до ячейки a7), данные о наименовании канцелярских принадлежностей в

46

столбец В (всего 5 наименований). Для расширения столбца В растянуть границу столбца в строке заголовков столбцов.

Введите цены в столбец С и количество - в столбец D.

2.Ввод формул.

Встолбец E введите формулы для вычисления стоимости. В ячейке E3 задать формулу = С3*D3.

3.Модификация структуры таблицы

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

Добавьте в таблицу два столбца и дайте им названия “Стоимость дорогостоящих товаров” и “Доля в общей стоимости, %”.

4. Копирование с помощью мыши.

Скопируйте формулу с помощью мыши перечисленными ниже способами.

Выделить ячейку Е3 и установить курсор в правый нижний угол на маркер заполнения. Протащить знак “+” по всем ячейкам, в которые должна быть скопирована формула.

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

Подведите итог по графе стоимость, найдите среднюю цену канцтоваров в графе “Цена”, используя функции Excel.

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

Вычислите среднюю цену товаров в графе “Цена”.

Установить курсор в ячейку, для которой должно быть выполнено вычисление. Выбрать в меню команду Формулы/Вставить функцию. В открывшемся окне выбрать категорию статистические. Пролистать перечень функций, относящихся к данной группе. Найдя нужную функцию (СРЗНАЧ), вызвать её щелчком на имени. Задать аргументы функции. Результат отображается в строке "Значение". Завершив ввод формул, щёлкнуть по кнопке ОК.

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

Подвести итог по графе “Cтоимость дорогостоящих товаров”.

В ячейку F3 ввести формулу =еслиС3>100;С3*D3;0) (функция “если” находится в группе логических функций).

Скопировать формулу во все ячейки столбца F.

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

В графе ”Доля в общей стоимости, %” подсчитайте долю каждого

товара в общей стоимости.

Для этого в ячейку G3 ввести формулу =Е3/E$10 (E$10 - ячейки, содержащей итоговую стоимость, знак $ после номера строки обеспечивает сохранение неизменным номера строки при копировании формулы),

47

скопировать формулу. Для перевода долей в проценты используйте кнопку “%” на панели форматирования. Отменить результат кнопкой отмены.

Присвойте ячейке, содержащей итоговую стоимость, имя “St” (Команда Формулы/Присвоить имя). Введите в ячейку G3 формулу для

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

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

Расположите список канцтоваров в порядке возрастания цен. Для этого выделите диапазон B3:G7, выберите в меню команду Данные/Сортировка. При выполнении команды открывается диалоговое окно. Выбрать в списке сортировать по имя столбца, по которому будет производиться сортировка и направление сортировки.

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

Измените числовой формат в графе “Цена “ на денежный, формат в графе “Стоимость” на числовой с 3 знаками после запятой.

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

Форматирование.

9. Переключатель формул.

Отобразите формулы в созданной таблице.

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

10. Вставка в таблицу разграничительных рамок.

Задайте для таблицы обрамляющую рамку толщиной 2

пункта и разграничительные линии ячеек толщиной 1 пункт.

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

Формат Ячеек (Главная/Выравнивание/Граница). Для создания рамки следует в поле линия задать тип линий, затем указать, с каких сторон выделенного блока ячеек необходимо нарисовать линии;

11. Отмена линий сетки.

Удалите изображение разграничительных линий сетки в таблице.

Выбрать в меню команду Вид/ снять флажок Сетка.

12. Вывод таблицы на печать.

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

48

ЛАБОРАТОРНАЯ РАБОТА №11

Тема: построение диаграмм и графиков в MS Excel

Цель работы: Освоить основные графические возможности табличного процессора MS Excel.

ОСНОВНЫЕ СВЕДЕНИЯ

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

На рис. 5 показаны элементы диаграммы.

Рис. 5

Категории - совокупность значений данных на оси категорий.

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

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

Линии сетки - необязательное горизонтальное или вертикальное продолжение засечек осей.

Легенда - элемент диаграммы, в котором представлен образ маркера данных для каждого ряда данных.

Область построения - площадь, занимаемая категориями и

осями. Она содержит маркеры данных и линии сетки. Построение диаграмм осуществляется с помощью Мастера диаграмм,

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

49

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

Рассмотрим пример построения графика функции y=x3-12x2+3. Исходные данные для построения графика находятся в столбцах А и

В.

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

Готово.