- •Общие сведения о табличном процессоре Excel
- •Рабочая книга Excel и ее элементы
- •Понятие объекта, свойства, методы и события
- •Элементы окна приложения Excel
- •Окно документа
- •Настройка Excel
- •Настройка интерфейса Excel
- •Настройка параметров Excel
- •Создание новой рабочей книги
- •Открытие существующей рабочей книги
- •Сохранение рабочей книги
- •Удаление файла рабочей книги
- •Операции над рабочими листами
- •Связывание рабочих листов таблицы
- •Прямое связывание
- •Операции с элементами рабочего листа
- •Ввод данных и формул в ячейки рабочего листа
- •Выражения и операции Excel
- •Типы данных Excel
- •Ввод данных
- •Ввод формул
- •Организация ссылок
- •Ввод специальных данных
- •Автоматическое вычисление сумм
- •Ввод в ячейки таблицы текущих значений даты и времени
- •Режимы вычислений
- •Выбор ручного или автоматического режима вычислений
- •Вычисления с точностью как на экране
- •Сортировка данных таблицы
- •Корректировка табличных документов
- •Перемещение по табличному документу
- •Выделение фрагментов таблицы
- •Редактирование данных
- •Копирование данных и формул
- •Автозаполнение
- •Оформление табличного документа
- •Изменение ширины столбцов
- •Операции над длинным текстом ячейки
- •Изменение высоты строки
- •Выбор шрифта
- •Ввод затенений и цветов
- •Изменение вида выравнивания по горизонтали
- •Изменение расположения текста относительно вертикали
- •Изменение ориентации вывода текста
- •Подготовка табличного документа к печати
- •Предварительный просмотр таблицы перед печатью
- •Печать
- •Работа с диаграммами
- •Создание диаграмм
- •Объекты диаграммы
- •Форматирование объектов
- •Изменение типа диаграммы
- •Добавление к диаграмме новых данных
- •Справочная система MS Excel
- •Использование встроенных функций Excel
- •Суммирование ячеек, удовлетворяющих определенному критерию
- •Подсчет количества значений в диапазоне
- •Подсчет количества пустых ячеек в диапазоне
- •Расчет среднего значения
- •Определение максимального значения
- •Определение минимального значения
- •Генерация случайного числа
- •Функции прогнозирования
- •Определение ранга числа
- •Определение процентной нормы числа
- •Функции для работы с матрицами
- •Функции даты и времени
- •Функция текущей даты
- •Функция текущей даты и времени
- •Функция определения дня недели
- •Функция определения номера месяца
- •Функция определения количества дней между двумя датами
- •Функция проверки условия
- •Использование функции И / ИЛИ
- •Использование функции И
- •Использование функции ИЛИ
- •Функция поиска данных в некотором диапазоне
- •Оценка ежемесячных выплат
- •Работа с именами ячеек
- •Создание имен
- •Замена адресов ячеек их именами
- •Вставка имен в формулы
- •Использование примечаний
- •Подбор параметра
- •Таблицы автоматической подстановки данных
- •Поиск решения
- •Консолидация данных
- •Консолидация по расположению
- •Консолидация по категориям
- •Удаление результатов ошибочной консолидации
- •Одновременная работа с несколькими табличными документами
- •Обмен данными через буфер
- •Динамический обмен данными DDE
- •Связь и внедрение объектов OLE (Active-X)
- •Общие принципы работы с большими таблицами
- •Фиксация на экране титулов
- •Создание нескольких окон для одного табличного документа
- •Разбиение документа на страницы вручную
- •Печать повторяющихся заголовков
- •Работа со списками (базами данных)
- •Создание списка (базы данных)
- •Поиск записей в списке
- •Редактирование записей с помощью формы
- •Удаление записей с помощью формы
- •Добавление записей
- •Подведение промежуточных итогов в списке
- •Фильтрация списка
- •Выбор элементов списка с помощью автофильтра
- •Функции для работы со списками
- •Сводные таблицы
- •Импорт данных в список Excel из внешних источников
2. Использование примечаний
Примечания являются важным элементом документирования таблиц. Они могут оказаться весьма полезными в тех случаях, когда таблица является достаточно большой и сложной, когда она требуется для работы нескольким пользователям и во многих других случаях.
Для добавления примечаний к ячейкам таблицы надо установить подсветку в требуемую ячейку и задать команду ПРИМЕЧАНИЕ из меню ВСТАВКА. В появившемся окне ввести текст. Завершив ввод щелкнуть на рабочем листе вне этого окна. Ячейка, имеющая примечание, помечается в верхнем правом углу красным треугольником. Для создания примечаний также можно использовать команду ПРИМЕЧАНИЕ из меню ВИД. В этом случае появляется панель инструментов РЕЦЕНЗИРОВАНИЕ. Используя кнопки этой панели можно создавать, просматривать, корректировать и удалять примечания.
Для удаления примечаний в ячейке также можно использовать команду ОЧИСТИТЬ из меню ПРАВКА (параметр ПРИМЕЧАНИЯ), предварительно выделив соответствующую ячейку.
Для печати примечаний надо задать команду ПАРАМЕТРЫ СТРАНИЦЫ из меню ФАЙЛ, нажать кнопку ЛИСТ и в поле ПРИМЕЧАНИЯ выбрать соответствующий параметр.
Задание для самостоятельной работы:
Загрузите с диска obmen файлы zad_name.xls и выполните предлагаемые в них задания.
Контрольные вопросы
1.Какие способы существуют для задания имен ячейкам?
2.Чем отличается команда Вставка/Имя/Создать от команды Вставка/Имя Присвоить? В каких случаях каждая из них может быть применена?
3.Можно ли пользоваться именами ячеек при написании формулы?
4.Какие способы существуют для вставки имен ячеек в формулы?
5.В каких случаях используются примечания?
6.Как вставить в ячейку примечание?
7.Как изменить примечание?
8.Как удалить примечание?
9.Как вывести примечание на печать?
Упражнение 3
1.Создать новую книгу и ввести указанную таблицу, начиная с третьей строки:
•шапку таблицы ввести в две строки, задав для ячеек, содержащих названия граф расположение по центру столбца;
•текст "Объем страховых сделок" расположить по центру четырех столбцов;
51
•названия граф «№ п/п», «Комиссионное вознаграждение» расположить в 2 ячейки по вертикали, предварительно объединив эти ячейки;
•для граф «№ п/п», «за III квартал», «Комиссионное вознаграждение» - задать перенос по словам;
•названия граф "июль", "август", "сентябрь" ввести используя автозаполнение.
2.Ввести фамилии, после этого переопределить ширину столбца В, сделав его равным максимальной длине фамилии.
3.Перед заполнением названий итоговых показателей внизу указать для соответствующих им ячеек перенос по словам. Возможно, несколько переопределить ширину столбца В.
ОТЧЕТ О ДЕЯТЕЛЬНОСТИ СТРАХОВЫХ АГЕНТОВ
№ |
|
Объем страховых сделок |
Комиссионное |
||||
Фамилия |
за III |
июль |
август |
сентябрь |
|||
п/п |
вознаграждение |
||||||
|
|
квартал |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Иванов |
|
400 |
120 |
390 |
|
|
|
Федоров |
|
250 |
200 |
270 |
|
|
|
Антонов |
|
|
370 |
410 |
|
|
|
Орлов |
|
290 |
|
360 |
|
|
|
Смирнов |
|
350 |
210 |
280 |
|
|
|
Владимиров |
|
410 |
380 |
|
|
|
|
Егоров |
|
360 |
290 |
340 |
|
|
|
Громов |
|
|
450 |
420 |
|
|
|
Антонов |
|
420 |
75 |
470 |
|
|
|
Борисов |
|
360 |
|
420 |
|
|
|
|
|
|
|
|
|
|
|
Всего: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Средний объем |
|
|
|
|
|
|
|
сделок |
|
|
|
|
|
|
|
Максимальный |
|
|
|
|
|
|
|
объем |
|
|
|
|
|
|
|
Минимальный |
|
|
|
|
|
|
|
объем |
|
|
|
|
|
|
|
Количество |
|
|
|
|
|
|
|
неотчитавшихся |
|
|
|
|
|
|
|
|
|
|
|
|
|
4.Ввести числовые данные в столбцы D, E и F.
5.Автоматически пронумеровать все фамилии в столбце А.
6.Выполнить сортировку всей таблицы по фамилиям (без столбца А).
7.Вычислить для каждого страхового агента объем сделок за 3-ий квартал как сумму сделок за июль, август и сентябрь.
8.Рассчитать сводные показатели (расположенные в нижней части таблицы) по соответствующим функциям.
9.Ввести Тарификационную таблицу, предварительно задав для диапазона столбца со значениями процентов процентный формат. Диапазону ячеек Тарификационной таблицы, содержащему данные, представленные в числовом и процентном форматах присвоить имя "Тариф".
52
Тарификационная
таблица
200 |
8% |
300 |
10% |
400 |
12% |
500 |
15% |
600 |
18% |
800 |
20% |
1000 |
25% |
10. |
По каждой фамилии рассчитать данные в графе "Комиссионное |
|
|
вознаграждение" как произведение "Объема страховых сделок за III |
|
|
квартал" на значение процента, вычисленного функцией ПРОСМОТР по |
|
|
Тарификационной таблице. Для функции ПРОСМОТР выбрать |
|
|
синтаксическую форму ПРОСМОТР(искомое_значение;массив) |
и |
|
указать в формуле имя массива - Тариф. Для уточнения действий |
|
|
функции ПРОСМОТР воспользоваться Справочной системой Excel. |
|
11. |
Ввести текст примечаний в ячейки с фамилиями агентов, имеющих |
|
|
максимальный и минимальный объем сделок за 3 квартал. |
|
12. |
Создать имя для диапазона ячеек с данными за 3 квартал. |
|
13.Справа от графы «Комиссионное вознаграждение» рассчитать |
ранг |
|
|
каждого страхового агента по показателям 3 квартала, используя в |
|
|
формуле имя диапазона. |
|
14. |
Рассчитать процентную норму ранга за 3 квартал, используя функцию |
|
|
Процентранг(диапазон; адрес ячейки). |
|
15. |
Разграфить таблицу, написать заголовок по центру таблицы более |
|
|
крупным шрифтом. |
|
Лабораторная работа №2
Загрузите с диска obmen файл tamog.xls и выполните предлагаемые в нем задания.
Тема V. Анализ данных
1. Подбор параметра
Инструмент Подбор параметра предназначен для решения задач анализа данных, когда заданное значение исследуемой функции может быть достигнуто путем изменений (перебора) значений одного из ее параметров.
Математическая суть задачи состоит в решении уравнения f(x)=a, где функция f(x) описывается заданной формулой, x – искомый параметр, a – требуемый результат формулы.
Для решения этой задачи необходимо выполнить следующие действия:
1.Поскольку процесс решения основан на итерациях, то на точность решения задачи влияют количество итераций и величина относительной погрешности. Поэтому предварительно следует задать эти параметры, используя команду Параметры из меню Сервис (вкладка Вычисления).
2.Выделить ячейку, содержащую формулу, для которой нужно найти определенное решение.
53
3.В меню Сервис выбрать команду Подбор параметра.
4.В поле Установить в ячейке ввести ссылку на ячейку, содержащую формулу (по умолчанию в это поле вводится адрес текущей ячейки).
5.В поле Значение ввести значение, которое нужно получить по заданной формуле.
6.В поле Изменяя ячейку ввести ссылку на ячейку, содержащую значение изменяемого параметра (эта ячейка называется изменяемой).
7.Щелкнуть по кнопке ОК.
После выполнения команды в изменяемой ячейке появится значение параметра, при котором результат формулы равняется заданной величине. При этом будет пересчитана вся таблица, т.е. изменятся значения, прямо или косвенно зависящие от изменяемого параметра.
Подбор параметра можно выполнять графически, перетаскивая точки данных на диаграмме.
Пример V.1. Дано уравнение x2+3x-2=A, где А – требуемый результат формулы; х – искомый параметр. Определить такое значение параметра х, при котором А будет равно 20.
1.Введем в ячейку В2 указанную формулу, сделав ссылку на ячейку, где условно находится параметр х.
2.Зададим команду Подбор параметра из меню Сервис.
3.В поле Установить в ячейке отображается адрес текущей ячейки.
4.В поле Значение введем 20.
5.В поле Изменяя ячейку укажем ячейку А2.
6.После нажатия ОК в ячейке А2 появится значение параметра х, при котором результат формулы равняется заданной величине.
Упражнение 4
Требуется проанализировать итоги деятельности фирмы за год. |
|
|
Итоги могут быть представлены в виде следующей |
n |
|
функциональной зависимости: |
y = ∑ xi , |
|
|
i = |
1 |
где xi – ежемесячные итоги, n в данном случае равно 12.
Ежемесячные итоги могут иметь как положительное значение – доходы, так и – отрицательное (убытки).
1.Создайте новую книгу и заполните в соответствии с рисунком диапазон А1:В13. Для ввода названий месяцев воспользуйтесь автозаполнением.
2.В ячейке В14 подсчитайте итоговую сумму.
3.По данным таблицы постройте гистограмму Итоги, оформив ее в соответствии с рисунком.
4.С помощью команды ПОДБОР ПАРАМЕТРА из меню СЕРВИС проведите анализ деятельности фирмы за сентябрь: Какой должен был быть показатель в сентябре, чтобы годовой итог равнялся 600? Для этого установите курсор в ячейку В14, вызовите указанную команду и в поле
Значение укажите 600, в поле Изменяя ячейку – В10.
54