close

Вход

Забыли?

вход по аккаунту

?

922.Экономическая информатика. Ч. 2

код для вставкиСкачать
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Федеральное агентство по образованию
Омский государственный университет им. Ф.М. Достоевского
УДК 519.72
ББК 32.81
Э 400
Рекомендовано к изданию редакционно-издательским советом ОмГУ
Рецензенты:
зав. кафедрой математического моделирования ОмГУ,
д-р физ.-мат. наук, проф. Н.В. Перцев; ст. преподаватель кафедры
экономики и социологии труда К.И. Богомазов
Э 400
ЭКОНОМИЧЕСКАЯ ИНФОРМАТИКА
Учебно-методическое пособие
(для студентов экономического факультета)
Часть 2
Электронные таблицы Microsoft Excel
и их использование для экономических расчетов
Экономическая информатика: Учеб.-метод. пособие
(для студентов экономического факультета): в 2 ч. / Сост.:
Г.С. Гусева, М.В. Ищенко, Т.В. Федорченко, Н.В. Шевченко. – Омск: Изд-во ОмГУ, 2005. – Ч. 2. Электронные таблицы Microsoft Excel и их использование для экономических
расчетов. – 64 с.
ISBN 5-7779-0594-3
Разработано для закрепления теоретических знаний по дисциплине «Экономическая информатика» и решения возникающих
на практике задач. Включает комплекс практических заданий, направленных на формирование у студентов навыков использования
электронных таблиц Microsoft Excel в экономике.
Пособие рекомендуется использовать на практических занятиях, в самостоятельной работе, а также для расчетов в курсовых и
дипломных работах и т. п.
Для студентов экономического факультета очной формы обучения.
УДК 519.72
ББК 32.81
Изд-во
ОмГУ
Омск
2005
ISBN 5-7779-0594-3
© Омский госуниверситет, 2005
2
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Содержание
ПРЕДИСЛОВИЕ
Предисловие................................................................................................... 4
I. Форматирование рабочих листов ............................................................. 5
Занятия 1, 2. Знакомство с табличным процессором MS Excel............ 5
Занятие 3. Форматирование рабочих листов....................................... 11
Занятие 4. Способы адресации табличного процессора MS Excel..... 14
II. Создание и использование формул ....................................................... 18
Занятие 5. Ввод формул в таблицы с использованием
абсолютной и относительной адресации ............................................. 18
Занятие 6. Знакомство с различными возможностями
организации вычислений в таблицах. Формулы массива.
Межстраничные ссылки........................................................................ 20
III. Использование функций табличного процесса MS Excel................... 25
Занятие 7. Функции категорий – математические,
статистические, массивы и даты .......................................................... 25
Занятие 8. Функции категорий – текстовые, логические,
финансовые ............................................................................................ 29
Занятие 9. Самостоятельная работа по использованию
функций .................................................................................................. 34
IV. Графическое представление табличных данных................................ 38
Занятие 10. Построение и редактирование диаграмм......................... 38
V. Обработка данных организованных в списки. Сортировка.
Фильтрация .................................................................................................. 43
Занятие 11. Знакомство с методами обработки данных,
организованных в списки, сортировкой, редактированием,
просмотром, поиском и извлечением данных по различным
критериям ............................................................................................... 43
Занятие 12. Фильтрация табличных данных, организованных
в списки .................................................................................................. 48
VI. Создание и обработка сводных таблиц. Консолидация
данных .......................................................................................................... 54
Занятие 13. Сводные таблицы .............................................................. 54
Библиографический список........................................................................ 63
Предлагаемое учебно-методическое пособие, охватывающее
основные разделы дисциплины «Экономическая информатика»,
составлено в соответствии с «Государственными образовательными
стандартами высшего профессионального образования» для экономических специальностей: 060100 «Экономическая теория», 060200
«Экономика труда», 060400 «Финансы и кредит», 060500 «Бухгалтерский учет, анализ и аудит», 061000 «Государственное и муниципальное управление», 061100 «Менеджмент организаций», 062100
«Управление персоналом», 351200 «Налоги и налогообложение».
Основная цель пособия – научиться обрабатывать таблично
организованную информацию, проводить экономические расчеты
и представлять результаты обработки в виде графиков и диаграмм.
Пособие, состоящее из 6 частей, разбитых на занятия по различным темам, посвящено изучению способов адресации ячеек, вычислениям в MS Excel, сортировке и фильтрации данных, использованию функций, работе с диаграммами и сводными таблицами.
Исходным материалом для реализации заданий является
электронная база данных, доступ к которой имеют все студенты
экономического факультета ОмГУ.
По своей структуре каждое занятие состоит из нескольких
частей. Вначале представлено краткое описание теоретических
основ и определение основных терминов по изучаемой теме. Затем предложены практические задания по закреплению теоретических знаний, а также даны рекомендации по выполнению заданий, причем может быть предложено несколько вариантов, или
способов, решения.
При выполнении практических заданий на занятии по экономической информатике в компьютерных классах экономического факультета ОмГУ рабочие файлы (архивы) выставляются преподавателем на сетевой диск G:\ и студенты работают в последовательности, указанной в учебно-методическом пособии.
При выполнении работ самостоятельно используйте рабочие
файлы (архивы) с диска S (For Study)/Информатика/Задания по
практике, копируя их на локальный диск Н:\, если работаете в
компьютерных классах, и копируя на диск А:\, если работаете не в
компьютерных классах экономического факультета ОмГУ.
3
4
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
I. ФОРМАТИРОВАНИЕ РАБОЧИХ ЛИСТОВ
Занятия 1, 2. Знакомство с табличным процессором Excel
1. Создайте на диске H:\ папку «Рабочая» и скопируйте в нее
архив «G:/Задание1&2/Таблицы.rar», распакуйте архив в папку
«Рабочая».
Запустите табличный процессор MS Excel (Пуск/ MS Excel).
Ознакомьтесь с элементами интерфейса: строка заголовка,
верхнее меню, панель инструментов, строка ввода, табличное поле, строка состояния.
Электронная таблица – совокупность строк и столбцов,
столбцы обозначены буквами латинского алфавита, а строки цифрами. Пересечение строки и столбца называется ячейкой. Каждая
ячейка имеет свой адрес, который состоит из буквы столбца и
цифры строки (например, А5, В7, К4).
2. Пользуясь манипулятором «мышь», просмотрите назначение всех элементов управления окном, а именно всплывающие
подсказки для всех кнопок стандартной панели инструментов и
панели форматирования, всплывающие подсказки для всех элементов окна: поля имени, строки формул, заголовки столбцов, заголовки строк, строки состояния, панели ярлычков листов, кнопок
прокрутки ярлычков листов, полосы прокрутки.
3. Откройте файл «Таблицы.rtf» и оставьте его развернутым
примерно на пол-экрана (это образцы тех таблиц, которые вы будете вводить в MS Excel).
4. Перейдите к окну MS Excel и введите таблицу, представленную на рис. 1* (файл «Таблицы.rtf»), на первый лист открытой
(первой) книги. Движение по табличному полю можно осуществлять с помощью мыши, с помощью клавиш управления курсором,
с помощью клавиш листания «PgUp», «PgDn», с помощью клавиш
«Home», «End», с помощью поля имени, с помощью пункта меню
Правка/Перейти.
Ввод данных: выделить нужную ячейку, ввести текст, число
или формулу, нажать клавишу ввода.
*
Рисунки представлены в указанных файлах.
5
Редактирование данных: выделить ячейку, нажать F2 (режим редактирования), внести исправления, нажать клавишу ввода.
При вводе данных научитесь перемещаться в таблице четырьмя способами: с помощью манипулятора «мышь», с использованием клавиш со стрелками, с помощью поля имени, с помощью пункта меню Правка/Перейти (Ctrl + G).
5. Сохранение табличного документа, добавление листов
в рабочую «Книгу».
Табличный файл является одно- или многолистовым и по
умолчанию называется «Книга1». Если документ сохраняется
впервые, то: Файл/Сохранить как, ввести имя файла, тип файла
*.XLS и нажать кнопку «Сохранить». Если документ сохраняется
не впервые, то: Файл/Сохранить (без ввода имени файла).
5.1. Сохраните в папке «Рабочая» под именем «ФЛ1» файлкнигу MS Excel, включающую один лист с таблицей «Таблица 1».
5.2. Введите таблицу, представленную на рис. 2 (файл «Таблицы.rtf»), на второй лист первой книги. Добавление второго листа: курсор мыши в поле «Лист1» – контекстное меню – «Добавить».
Сохраните файл с двумя таблицами под именем «ФЛ2» в
папке «Рабочая».
6. Работа с листами книги. Создание новой книги на основе существующей.
6.1. Откройте документ «ФЛ2» (сохраненный в формате
книги Excel), созданный ранее.
6.2. Введите на третий лист книги таблицу, приведенную на
рис. 3 (файл «Таблицы.rtf»), и сохраните книгу.
6.3. Откройте новое окно документа для создания новой
книги (Файл/Создать/Книгу).
6.4. Скопируйте во вновь открытую книгу первый и третий
лист исходной книги «ФЛ2», разместив их соответственно в начале книги.
Для копирования, а в дальнейшем и для переноса листов
таблицы из книги в книгу используйте контекстное меню или
пункт меню Правка/Переместить/скопировать лист, не забудьте
ставить флажок «Создавать копию».
6
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
6.5. Расположите рядом окна двух документов: исходного и
нового (Меню «Окно»/Расположить/Слева направо). Перенесите с
помощью мыши второй лист исходного документа в новый документ и поместите его между первым и вторым листом (используя
левую кнопку мыши).
6.6. Активизируйте окно исходного документа (книги) и
проверьте правильность выполненного переноса (в исходной книге должно остаться два листа – первый и третий).
6.7. Скопируйте «Лист2(2)» из новой книги в исходную, разместив его в самом начале книги – перед первым листом (перемещение листов осуществляется с помощью контекстного меню ярлычка листа).
6.8. Сохраните книги в папке «Рабочая», новую под именем
«ФЛ3», а исходную – «ФЛ4».
6.9. Активизируйте окно, содержащее книгу «ФЛ4». Переместите листы в пределах данной книги (контекстное меню) таким
образом, чтобы листы изменили порядок следования: первый лист
должен стать последним (третьим), а третий – первым (а если у
вас был порядок следования листов 3, 2, 1, то расположите их в
порядке 1, 2, 3).
6.10. Сохраните результат работы под именем «ФЛ5» в папке «Рабочая».
7. Именование листов книги.
7.1. Уберите с экрана все окна за исключением окна документа «ФЛ5».
7.2. Дайте всем трем листам документа краткие содержательные наименования, раскрывающие содержание таблиц.
Для именования листов книги используйте контекстное меню или пункт меню Формат/Лист/Переименовать.
7.3. Последовательно удалите из книги «ФЛ5» первый и третий листы, пользуясь контекстным меню или пунктом меню
Правка/Удалить лист.
7.4. Сохраните результат в документе «ФЛ6» в папке «Рабочая».
7
8. Перемещение по таблице.
Научитесь произвольно перемещаться по таблице, используя для перемещения комбинации управляющих клавиш и соответствующие команды меню (См. рис. 3 в файле «Таблицы.rtf»).
9. Выделение ячеек и областей.
Научитесь выделять произвольные области таблицы (ячейку, строки, столбцы, диапазоны ячеек и произвольные комбинации
ячеек) для выполнения последующих операций редактирования
(копирование, перенос, удаление) над выделенными областями,
пользуясь комбинациями управляющих клавиш и мышью:
– выделите всю таблицу (заполненные клетки), пользуясь
манипулятором «мышь»;
– выделите весь лист;
– выделите область ячеек от ячейки B6 до верхнего левого
угла рабочего листа (клетка А1);
– выделите область ячеек от ячейки B6 до нижнего правого
угла рабочего листа (клетка IV65536), используя клавишу F8 (режим выделения, убедиться в строке состояния) и поле имени;
– выделите несмежные области таблицы (с помощью кнопки
CTRL). F8 – снято.
10. Создание таблицы по образцу, приведенному на рис. 4
(файл «Таблицы.rtf»).
Вычисления в таблицах выполняются с помощью формул.
Результатом выполнения формулы есть некоторое новое значение,
содержащееся в ячейке, где находится формула. Формула начинается со знака равенства «=», при адресации ячеек используется
латинский алфавит. В формуле могут использоваться арифметические операторы +, –, *, /. Порядок вычислений определяется обычными математическими законами.
10.1. Запишите в клетки Е4, F4, E13, E14 соответствующие
формулы, представленные на рис. 4, используя только латинский
алфавит.
10.2. Научитесь просматривать записи формул (в строке
формул) и результаты вычислений (значения) в ячейках таблицы.
Просматривать записи формул можно не только в строке
формул, но и в самих ячейках после нажатия комбинации клавиш
8
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Ctrl+Ё (или сделав соответствующую пометку в пункте меню/
Сервис/Параметры/Вид/Параметры окна/Формулы).
10.3. Скопируйте формулы в диапазоны Е5:Е10, F5:F10 и
проследите за модификацией адресов при копировании.
10.4. Сохраните таблицу в книге «ФА1» в папке «Рабочая».
11. Модификация созданной таблицы.
11.1. Вставьте новый столбец «Коэфф.» между столбцами
«Цена» и «Стоимость» (с помощью контекстного меню). Заполните его данными (значения 0,5, 0,6, и т. д. до 1,1).
11.2. Отредактируйте формулу в столбце «Стоимость» так,
чтобы она учитывала значения столбца «Коэфф.», например для
четвертой строки C4*D4*E4 (редактирование проводите в строке
формул).
11.3. Перенесите две первые значащие строки таблицы
(строки 4-го и 5-го листа) в конец списка изделий.
11.4. Вставьте перед 8-й строкой листа («Доски») две новые
строки и заполните их («Гипсокартон-50-48», «Панели-43-32»).
11.5. Сохраните таблицу в книге «ФА2» в папке «Рабочая».
12. Сформируйте из исходной таблицы «ФА2.xls» новую
таблицу, состоящую из столбцов «Номер», «Наименование» и
«Цена», пользуясь командами «Очистка» и «Удаление». Запишите
различие в результатах работы этих команд. Поместите новую
таблицу, начиная с клетки С3, и сохранить ее в книге «ФА3».
13. В таблице «ФА3.xls» сформируйте столбец номеров,
пользуясь автозаполнением, а именно: сначала введите два первых
номера (указав шаг заполнения), выделите введенные номера, затем подведите курсор мыши в правый нижний угол выделенной
области, и, когда курсор примет форму черного крестика, протяните его вниз, не отпуская левую кнопку мыши.
14. Поместите (перенесите) таблицу в область ячеек, начинающуюся с ячейки С20, и назовите ее «Склад 2».
14.1. Скопируйте таблицу «Склад 2» на 2-й лист рабочей книги в область ячеек, начинающуюся с ячейки В6. Назовите ее
«Склад 2 (Лист 2)».
14.2. Скопируйте таблицу «Склад 2 (Лист 2)» на первый
лист рабочей книги на место исходной таблицы «Склад» (в об9
ласть ячеек, начинающуюся с ячейки А1). Назовите ее «Склад 2
(Лист 1)».
15. Для клеток B6, B7 «Листа1» создайте примечания, поясняющие, какие именно изделия (размер, сорт, цвет) приведены в
таблице (для создания примечаний используйте контекстное меню). Просмотрите, каким образом отмечаются ячейки, имеющие
примечания.
Сохраните книгу под именем «ФА3» в папке «Рабочая».
16. В новой книге создайте таблицу в соответствии с образцом (см. рис. 5, «файл Таблицы.rtf»).
16.1. Введите суммы оплаты и срок задержки (по своему усмотрению).
16.2. Введите формулы для начисления пеней (0,01% от начисленной суммы за каждый просроченный день), итоговых сумм
и конечной суммы оплаты.
16.3. Переименуйте текущий рабочий лист, назвав его «Оплата».
Сохраните созданный файл под именем «Оплата.xls» в папке «Рабочая».
16.4. Добавьте в файл «Оплата.xls» второй лист. Скопируйте
на этот лист вашу таблицу, поместив ее в диапазоне ячеек В3:F11.
Измените количество дней, на которое задержана оплата, на другое число и скорректируйте формулы в соответствие с этим изменением. Сохраните изменения.
17. В книгу «Оплата.xls» добавьте 3-й лист и создайте таблицу (см. рис. 6). Заполните строки 8-ю и 9-ю (введите данные свои и
соседа). В ячейку D4 введите формулу определяющую возраст в
годах, а в Е4 – пересчитайте возраст в днях (считая, что в году 365
дней).
Скопируйте формулы из ячеек D4 и Е4 вниз по столбцу
(щелкните в ячейку с формулой и, поместив курсор мыши на черный квадратик с рамки, протяните мышью вниз по столбцу при
нажатой левой клавише мыши).
18. На 1-й лист книги «Оплата.xls» в ячейку В12 введите
свою фамилию и номер группы (выделите цветом).
19. Удалите файл «Таблицы.rtf». Заархивируйте папку «Рабочая» (имя архива – ваша фамилия) и переместите архив на диск
G:\ в папку с номером вашей группы.
10
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Занятие 3. Форматирование рабочих листов
1. Создайте на диске H:\ папку «Рабочая». Скопируйте в нее
архив «G:/Задание3/Таблицы.rar», распакуйте архив в папку «Рабочая».
Форматирование – это комплекс действий, направленных на
улучшение восприятия табличной информации.
Формат устанавливается для выделенной области или ячейки и снимается командой Правка/Очистить/Форматы. Форматирование включает в себя следующие элементы:
• установку формата чисел;
• установку формата строк и столбцов;
• выбор шрифтов;
• рисование рамок;
• заполнение клеток цветом и узором;
• выравнивание данных;
• защиту данных.
2. Форматирование текста.
2.1. Выравнивание текста.
Создайте на 1-м рабочем листе таблицу по приведенному
образцу (рис. 1) и сохраните ее в файле «ФА1.xls» для дальнейшего использования.
Скопируйте на 2-й рабочий лист содержимое 1-го рабочего
листа.
Отформатируйте тексты таблицы по образцу, приведенному
на рис. 2. На этом примере научитесь выравнивать текст всеми доступными способами. Перед выполнением этого пункта установите
для всего рабочего листа стандартную высоту строк (для шрифта
размером 10 пт стандартная высота строки составляет 12,75 пт).
Включите режим автоматической установки ширины столбцов (Формат/Столбец). Просмотрите, как изменится внешний вид
таблицы. Подстройте параметры таблицы (ширину столбцов) так,
чтобы внешний вид таблицы соответствовал рис. 2. Для выравнивания используйте пункт меню Формат/Ячейки/Выравнивание.
11
2.2. Шрифтовое оформление текста.
Воспользовавшись режимом форматирования ячеек (Формат/Ячейки/Шрифт), оформите тексты в таблице 2-го листа так,
как представлено на рис. 3.
В таблице на рис. 3 использованы следующие варианты
шрифтового оформления текста: жирный, подчеркнутый, курсив,
жирный курсив, перечеркнутый, а также верхний и нижний индексы. Для выделения данных в таблице используйте различные
варианты оформления из меню Формат/Ячейка/Вид («Заливка» и
«Узор») и меню Формат/Ячейка/Граница.
3. Форматирование числовых данных.
Числовые данные, которые вы вводите, как правило, не отформатированы. Другими словами, они состоят из последовательности цифр. На панели «Форматирование» находится несколько
кнопок, позволяющих применить самые распространенные форматы. Если возникает необходимость использовать другие варианты
форматирования чисел, то используют команду Формат/Ячейки/
Число, либо «Формат ячеек» в «Контекстном меню».
Форматы дат содержат несколько вариантов представления
введенной даты (например, «16.4», «16.04.97», «16 апр», «16 апр
97» и т. д.). Базовой единицей является день. Каждой дате соответствует свое сериальное число, показывающее, на сколько дней
введенная дата отстоит от 1 января 1900 г.
3.1. На третьем рабочем листе создайте таблицу, приведенную на рис. 4. Заполните все столбцы, кроме столбца F и ячеек
D15 и E15. Отформатируйте числовые данные с использованием
числовых форматов, как показано на рисунке. В ячейки D15 и E15
запишите формулы, вычисляющие сумму значений в соответствующем столбце. Столбец F заполните формулами, вычисляющими отношение значений столбца E («Продано шт.») к общей сумме, записанной в клетке E15.
Метод курсорного копирования. При вводе формулы можно пользоваться следующим методом: при нажатии знака «=»
осуществляется переход в режим ввода формулы, аргументы формул указываются курсором, т. е. нужно выбрать курсором ячейку
и нажать левую кнопку мыши. При этом в формулу автоматически
12
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
запишется полный адрес данной ячейки (с указанием имен листов
и книг, если ячейка расположена не на текущем листе).
3.2. Очистите формат ячеек B4:B5 (Правка/Очистить/Форматы) и убедитесь, что Excel, после снятия форматирования дат,
интерпретирует даты как числа 35 802 и 35 807, которые являются
порядковыми номерами введеных дат в памяти Excel, начиная с 1
января 1900 г.
3.3. Отформатируйте ячейку F4, как показано на рис. 4, и
скопируйте созданный формат в диапазон F5:F8.
3.4. Отформатируйте ячейку F9, как показано на рис. 4, и
скопируйте созданный формат в диапазон F10:F14.
4. Защита данных.
При работе над одним документом совместно с другими
пользователями можно защитить от изменений ячейки на рабочем
листе. Если на листе должны быть защищены не все ячейки, то
перед выполнением команды Сервис/Защита/Защитить лист нужно во вкладке «Защита» диалогового окна «Формат ячеек» снять
флажок «Защищаемая ячейка» для предварительно выделенных,
не нуждающихся в защите ячеек.
Защитите заголовки строк и столбцов таблицы, приведенной
на рис. 4, и оставьте возможным изменение числовых данных таблицы (Формат/Ячейки/Защита, установите флажок «Защищаемые ячейки» на тех ячейках, которые хотите защитить, а на диапазоне данных этот флажок должен быть снят). Затем Сервис/ Защита/Защитить лист (в отношении содержимого).
Сохраните результат работы.
5. Создайте в папке «Рабочая» новую книгу «Форм. Текста.xls», состоящую из 2 листов. Введите в одну ячейку A1 листа 2
представленное в рамке предложение и отформатируйте его следующим образом:
ЭЛЕКТРОННЫЙ ПРОЦЕССОР
EXCEL
ПРЕДНАЗНАЧЕН ДЛЯ ОБРАБОТКИ ДАННЫХ, представленных в ТАБЛИЧНОЙ ФОРМЕ.
Для добавления новой строки в ячейку используйте комбинация клавиш Alt + Enter. Для расположения текста в ячейке в не13
сколько строк можно применить вкладку «выравнивание» команды
Формат/Ячейки и установить флажок. Переносить по словам.
6. На первом листе книги «Форм. Текста.xls» создайте образцы таблиц, приведенных на рис. 5.
Для рисования рамок используйте команду Формат/Ячейки/
Граница.
7. Удалите архив и файл «Таблицы.rtf». Заархивируйте папку «Рабочая» (имя архива – ваша фамилия) и переместите архив
на диск G:/ в папку с номером вашей группы.
Занятие 4. Способы адресации табличного процессора
Excel
При копировании и перемещение формул происходит преобразование содержащихся в них ссылок на другие ячейки. В одних случаях адреса должны оставаться неизменными, в других –
адреса должны быть пересчитаны с учетом относительного изменения их местоположения. Для этого в MS Excel поддерживается
система абсолютных и относительных ссылок. Когда вы используете относительную адресацию, ссылки в формулах автоматически
изменяются при копировании формулы в другое место. При копировании формул относительные ссылки изменяются на размер перемещения. Чтобы ссылки в формуле не изменялись при копировании формулы в другую ячейку, используйте абсолютную ссылку.
1. Создайте на диске H:\ папку «Рабочая». Скопируйте в нее
архив «G:/Задание4/Таблицы.rar», распакуйте архив в папку «Рабочая».
2. Создайте таблицу, приведенную на рис. 1 файл «Таблицы.rtf»), заполнив только ячейки A3:E7 в соответствии с образцом,
приведенным на рис. 1.
3. Запишите формулы, позволяющие выполнить следующие
вычисления (при этом необходимо помнить, что формула всегда
начинается знаком равенства (=), ввод формулы заканчивается
нажатием клавиши «Enter», а операнды в формулу можно вставлять с помощью метода курсорного копирования: аргументы формул указываем курсором, т. е. выбираем курсором ячейку и нажимаем левую кнопку мыши. При этом в формулу автоматически
14
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
запишется полный адрес данной ячейки (с указанием имен листов
и книг, если ячейка расположена не на текущем листе)):
3.1. Сумму всех элементов первой строки (разместить в
клетке Н3).
3.2. Сумму всех элементов второй строки (разместить в
клетке Н4).
3.3. Для всех оставшихся строк записать аналогичные формулы, разместив их в соответствующих строках столбца Н. Для записи
формул в 3-й и последующих строках можно пользоваться командой копирования блока клеток. Обратить внимание, как модифицируются адреса клеток при выполнении команды копирования.
3.4. Сумму всех элементов первого столбца разместите в
клетке А10.
3.5. Сумму всех элементов второго столбца разместите в
клетке В10.
3.6. Для всех оставшихся столбцов запишите аналогичные
формулы, разместив их в соответствующих ячейках строки 10.
При записи формул пользоваться командой копирования.
3.7. В клетке Н11 запишите формулу для вычисления суммы
элементов главной диагонали данной матрицы, которая будет
включать только ячейки с числовыми данными.
3.8. В клетке Н12 запишите формулу для вычисления суммы
элементов главной диагонали матрицы, которая будет включать
все ячейки диагонали (в том числе и ячейку C5), не содержащие
данных. Сравнить результаты в ячейках Н11 и Н12.
3.9. В ячейках А11, А12, А13 разместить формулы, позволяющие выполнить следующие вычисления:
¾ сумму элементов матрицы, расположенных в блоке клеток от А3 до В4,
¾ сумму элементов матрицы, расположенных в блоке клеток от D6 до Е7,
¾ разность сумм элементов, расположенных в блоках
А3:В4 и D6:Е7.
Сравните полученные результаты с результатами, приведенными на рис. 1.
4. Пользуясь операциями копирования, создайте, начиная с
ячейки А20, таблицу, приведенную на рис. 2 («Таблицы.rtf»).
Для заполнения ячеек постоянными значениями нужно протянуть маркер автозаполнения по диапазону ячеек. Это же можно
получить, выполнив протяжку правой кнопкой мыши и выбрав
пункт меню «Заполнить значения».
Абсолютные и относительные адреса ячеек. Иногда требуется, чтобы при копировании формул из ячейки в ячейку адреса
не модифицировались. Для этого в формуле необходимо использовать абсолютные адреса ячеек. Чтобы указать, что адрес абсолютный, пользуйтесь символом $. Например, $B$4 означает, что при
копировании координаты столбца и строки модифицироваться не
будут, запись B$4 означает, что модифицироваться будет только
координата столбца и т. д. Изменить тип ссылки можно последовательным нажатием клавиши F4 при установке курсора перед
ссылкой, подвергающейся изменению.
5. Для вычисления суммы элементов блока клеток А20:С21
запишите в ячейках А25, С25, Е25 и G25 следующие формулы:
• в клетке А25 адреса должны быть записаны как относительные;
• в клетке С25 адреса должны быть записаны как абсолютные;
• в клетке Е25 адреса должны быть записаны с абсолютным
указанием строки и относительным указанием столбца;
• в клетке G25 адреса клеток должны быть записаны с абсолютным указанием столбца и относительным указанием строки.
6. В ячейки H20, H21, H22 и H23 поместите аналогичные
формулы.
7. Перейдя в режим отображения результатов, просмотрите
вычисленные значения и проверьте их правильность.
Все значения сумм должны быть равны 6.
8. Скопируйте формулы из строки 25 в строку 27.
Проверьте, как ведут себя относительные и абсолютные
адреса клеток. Просмотрите результаты вычислений по формулам
строк 25 и 27. Проанализируйте результаты.
9. Скопируйте формулы из столбца H в столбец J.
Проверьте, как ведут себя относительные и абсолютные адреса клеток. Просмотрите результаты вычислений по формулам
столбцов H и J. Проанализируйте результаты.
15
16
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
В электронных таблицах можно работать как с отдельными
таблицами, так и с группой ячеек, которые образуют блок. В качестве блока может рассматриваться строка или ее часть, столбец
или часть столбца, а также прямоугольник из нескольких строк и
столбцов. Адрес блока ячеек задается указанием ссылок первой и
последней его ячеек, между которыми ставится разделительный
символ – двоеточие.
10. Определите имена для областей A20:C21; A22:C23;
D20:F21; D22:F23 соответственно как One, Two, Three, Four
(Вставка/Имя/Присвоить).
11. Вычислите суммы элементов заданных блоков, используя функцию =Сумм (имя диапазона), расположенную на стандартной «Панели инструментов» и команду Вставка/Имя, выбрав
имя нужного диапазона в качестве аргумента, функции «Сумма».
Разместите результаты вычислений в ячейках B30:B33.
12. Используя имена диапазонов, перейдите в области Three
и Four (Правка/Перейти) и очистите их содержимое.
13. Сохраните результат работы под именем «ФЛ1» в папке
«Рабочая».
14. Откройте файл «Показания счетчика.xls». Определите сумму, которую придется заплатить за потребленную электроэнергию.
Выполните расчет, введя свои исходные данные (в столбцы «Дата»
и «Показания счетчика»).
15. Отформатируйте таблицу по образцу (рис. 3 файла «Таблицы»).
16. На втором листе книги «Показания счетчика.xls» создайте таблицу, внеся в нее исходные данные (условия см. ниже).
II. СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ ФОРМУЛ
Занятие 5. Ввод формул в таблицы с использованием абсолютной и относительной адресации
Используя возможности MS Excel, найдите сумму выручки
от продаж в рублях и долларах (курс доллара – 29,2 руб). Лист назовите «Выручка за январь».
17. Удалите из папки «Рабочая» файл и архив Таблицы.
18. Заархивируйте папку «Рабочая» со всеми созданными
документами (имя архива – ваша фамилия) и переместите архив
на диск G:/ в папку с номером вашей группы.
1. Создайте на диске H:\ папку «Рабочая». Скопируйте в нее
архив «G:/Задание5/Таблицы.rar», распакуйте архив в папку «Рабочая».
2. Откройте файл «Отчет».xls». Составьте отчет о начислении заработной платы сотрудникам АО «Рога и копыта».
Исходные данные: должностной оклад, количество отработанных дней за год, процент подоходного налога.
Найти:
• в графе «Начислено» сумму, начисленную каждому сотруднику за год;
• в графе «Налог» (12 % от «Начислено») сумму подоходного налога;
• в графе «К выдаче» сумму, выдаваемую каждому сотруднику;
• в графе «% от общей суммы» % зарплаты работника от
суммарной зарплаты всего коллектива;
• суммарную величину выплат всех сотрудников.
3. Введите в первую клетку столбца «Начислено» расчетную
формулу для вычисления суммы, начисленной первому в списке
сотруднику (12 х (оклад) x (число дней) / (расчетное число дней)):
(при вводе расчетного количества дней используйте ссылку на
ячейку D16 (абсолютный адрес)). Формат ячеек денежный, с одним знаком после запятой.
4. Скопируйте формулу в остальные клетки столбца «Начислено».
5. Проверьте автоматическое изменение формул в клетках
столбца «Начислено»: относительные адреса клеток в числителе
должны измениться, а абсолютный адрес клетки в знаменателе
должен остаться неизменным.
6. Аналогично создайте и скопируйте формулы для вычисления подоходного налога в столбце «Налог» (12 % от «Начислено») и суммы к выдаче в столбце «К выдаче» («Начислено» минус
«Налог»).
17
18
Торговая фирма имеет в своем ассортименте следующий товар:
телевизоры по цене $300, видеомагнитофоны – $320, муз. центры – $550,
видеокамеры – $700, видеоплееры – $198, аудиоплееры – $40. В январе
было продано телевизоров – 10, видеомагнитофонов – 5, муз. центров –
6, видеокамер – 2, видеоплееров – 7, аудиоплееров – 4.
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
7. В ячейке F13 запишите формулу для подсчета суммарной
зарплаты всех сотрудников.
8. В графе «% от общей суммы» создайте и скопируйте формулу, показывающую какую часть составляет годовая зарплата
каждого сотрудника от суммарных выплат (ячейка F13, формат
ячеек процентный).
9. После выполнения задания сравните результат с рис. 1
файла «Таблицы». Отформатируйте таблицу по образцу рис. 1.
10. Откройте файл «Доставка.xls» и запишите формулы в
ячейки F3:F8.
Формула в ячейке F3 вводится с учетом того, что стоимость
доставки груза складывается из следующих трех величин (наценок):
а) 5% от стоимости груза,
б) от расстояния (3 руб./км),
в) номера этажа (2,5 руб./этаж).
11. Перенесите лист из книги «Доставка» вторым листом в
книгу «Отчет.xls».
12. Сравните результат с рис. 2 файла «Таблицы». Отформатируйте таблицу по образцу рис. 2.
13. На 3-м листе книги «Отчет.xls» рассчитайте зарплату
(суммы к получению для каждой категории сотрудников для двух
филиалов) (условия см. ниже). Результатом должны быть две таблицы. Лист назовите «Филиалы».
На предприятии работники имеют следующие оклады: начальник отдела – 5 000 руб., инженер 1 кат. – 3 500 руб., инженер
– 3 200 руб., техник – 2 000 руб., лаборант – 1 200 руб. Предприятие имеет два филиала – в Средней полосе и в условиях Крайнего
Севера. Все работники получают надбавку 10 % от оклада за вредный характер работы, 25 % от оклада ежемесячной премии. Со
всех работников удерживают подоходный налог – 20 %, профсоюзный взнос – 3 % и 1 % в пенсионный фонд (% от всего совокупного дохода). Работники филиала, расположенного в Средней полосе, имеют 15 %-ный районный коэффициент, работники филиала, расположенного в районе Крайнего Севера – 70 %-ный районный коэффициент и 50 % северной надбавки от оклада.
19
Формат ячеек денежный, рублевый, ноль знаков после запятой.
14. Удалите из папки «Рабочая» файл и архив «Таблицы».
15. Заархивируйте папку «Рабочая» со всеми созданными
документами (имя архива – ваша фамилия) и переместите архив
на диск G:/ в папку с номером вашей группы.
Занятие 6. Знакомство с различными возможностями
организации вычислений в таблицах. Формулы массива. Межстраничные ссылки
1. Создайте на диске H:\ папку «Рабочая». Скопируйте в нее
архив «G:/Задание6/Таблицы.rar», распакуйте архив в папку «Рабочая».
2. Групповое заполнение листов.
MS Excel позволяет вводить и редактировать данные на нескольких листах сразу – групповым методом. Пользуясь методом
группового заполнения листов, создайте на трех листах нового
документа таблицу, приведенную на рис. 1 (файл «Таблицы»), введя данные в диапазон В4:F8. Назовите листы «Таб1», «Таб2»,
«Таб3».
Для этого активизируйте рабочий лист и выделите диапазон
для работы. Затем нажмите клавишу «Shift» и щелчком по ярлычкам листков выберите группу соседних листов (клавиша «Ctrl» –
для выбора отдельных листов). В строке заголовка появится надпись «Группа» – это напоминание о том, что вы находитесь в режиме работы с группой листов.
Сохраните книгу под именем «ФА1» в папке «Рабочая».
3. Различные приемы заполнения ячеек формулами. Использование формул массива.
Массив – это набор ячеек или значений, которые обрабатываются как одна группа. Формула массива – формула, в которой
используется один или несколько массивов непосредственно или
как аргумент функции. В MS Excel массивы могут быть одно- или
двухмерными. Операции над массивами производятся с помощью
формул массивов.
20
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
3.1. Откройте файл «Объем сделок» и запишите в столбец G
формулу для подсчета общего объема сделок для каждого сотрудника за четыре квартала.
3.2. В столбце H подсчитаем то же самое, но с использованием формулы массива.
Для этого выделите диапазон Н4:Н15 и в строке формул
введите формулу =C4:C15+D4:D15+E4:E15+F4:F15, затем нажмите одновременно клавиши «Shift», «Ctrl» и «Enter» для того, чтобы
указать, что введенная формула является формулой массива. В
строке формул введенная формула будет записана в фигурных
скобках.
Сравните результаты столбцов Н и G.
Сохраните книгу под именем «Объем» в папке «Рабочая».
3.3. Перейдите к книге «ФА1». В диапазон G4:G8 запишите
формулу для вычисления суммарной нагрузки (часов занятий),
пользуясь формулой массива. (Не забудьте активизировать групповое заполнение листов, чтобы изменения проходили в каждой
из таблиц.)
Если вам надо отредактировать формулу массива, выделите
все ячейки массива и активизируйте строку формул (щелкните на
ней или нажмите F2). При редактировании редактор удаляет фигурные скобки. Закончив редактирование формулы, нажмите
«Shift», «Ctrl» и «Enter», чтобы внести изменения.
Нельзя:
• изменять содержимое одной из ячеек, содержащих формулу массива;
• перемещать отдельные ячейки, на которые распространяется формула массива;
• удалять отдельные ячейки, на которые распространяется
формула массива;
• вставлять новые ячейки в массив.
3.4. Убедитесь, что в каждой ячейке диапазона G4:G8 используется одна формула массива.
4. Копирование формул.
4.1. Активизируйте групповое заполнение листов. Запишите
формулу для вычисления суммарной нагрузки (часов занятий) по
лекциям (ячейка B10).
Скопируйте данную формулу в диапазон C10:F10, пользуясь
командами меню Правка/Копировать, Правка/Вставить.
4.2. Запишите формулу для суммирования нагрузки по строкам в ячейку G9.
4.3. Запишите формулу для суммирования нагрузки по
столбцам в ячейку G10.
4.4. Запишите формулу для вычисления доли содержания
нагрузки для группы ЕС61-63 в общей сумме часов (ячейка H4),
учитывая абсолютную и относительную адресацию ячеек. Скопируйте данную формулу в диапазон H5:H8, пользуясь автозаполнением. При использовании автозаполнения мышь устанавливается
на маркер автозаполнения первой ячейки диапазона и протаскивается по всему диапазону.
5. Автовычисления.
Пользуясь автовычислением, определите среднее, минимальное и максимальное значения нагрузки для групп ЕС61-63 и СУ61
и зафиксируйте полученные результаты. Последовательность действий:
5.1. Добавьте к таблице 1 три строки, которые назовите
«Средн», «Макс», «Мин».
5.2. В строке состояния есть поле автовычисления, щелкните
по нему правой кнопкой мышки – появится меню. Выберите функцию «среднее». Выделите ячейки B4 и B7, в поле автовычисления
появится результат, зафиксируйте его в ячейке B12. Аналогично
запишите среднее значение для остальных столбцов. Подобным
образом заполните строки «Макс» и «Мин» (столбцы C, D, E, F).
5.3. Активизируйте режим ручного пересчета формул (Сервис/Параметры/Вычисления/Вручную).
Измените несколько значений в таблице и выполните ручной пересчет, например в ячейке B6 измените значение на 28 и
убедитесь, что значения в ячейках G6 и B10 не изменились. Нажмите клавишу F9 («Вычислить») и убедитесь, что значения в
указанных ячейках изменились.
Верните режим автоматических вычислений.
5.4. Снимите групповое заполнение листов. Отформатируйте
таблицу на листе «Таб2» по образцу, представленному на рис. 2,
обратив внимание на центровку строки заголовка и формат процентного представления чисел в ячейках H4:H8 и В11:F11.
21
22
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
5.5. Отформатируйте таблицу на листе «Таб3», пользуясь
функцией автоформатирования, а именно: выделите таблицу и,
пользуясь меню Формат/Автоформат, выберите из списка формат «Классический 2». Сохраните изменения.
5.6. Пользуясь меню Сервис/Зависимости, на листе «Таб1»
выявите влияющие и зависимые ячейки для ячейки G9.
6. Межстраничные ссылки.
Межстраничные ссылки необходимы в случае, когда для
вычисления формулы требуется сослаться на ячейки других рабочих листов. Для этого перед адресом ячейки ставиться имя рабочего листа (если имя из нескольких слов, то в одинарных кавычках), восклицательный знак и адрес ячейки. Например, выражение
‘Должностные оклады’!A4 означает ссылку на ячейку A4 листа
«Должностные оклады». Если ячейка из другой книги, то ссылка
будет выглядеть так:
[Отчет за 1999 год]’Первый квартал’!B14 ссылка на ячейку
B14, листа «Первый квартал», книги «Отчет за 1999 год.xls».
6.1. Откройте файл «Объем сделок» и скопируйте на 2-й
лист таблицу с листа 1. В таблице на 1-м листе удалите столбцы Е
и F (данные за 3-й и 4-й кварталы). На 2-м листе удалите столбцы
С, D, G.
6.2. Запишите в столбец «Всего за год» 1-го листа формулу
для подсчета общего объема сделок для каждого сотрудника за
четыре квартала, используя ссылки на ячейки первого и второго
листа. Для ввода ссылок с другого листа:
– выделите ячейку, в которую нужно ввести формулу и введите знак равенства (=);
– щелкните на ярлычке листа, содержащего нужную ячейку;
– выделите ячейку, на которую нужно установить ссылку (в
строке формул появится полный адрес);
– завершите ввод формулы, затем нажмите «Enter».
Сохраните изменения.
6.3. Перейдите к книге «ФА1». Пользуясь «объемной» формулой =СУММ (Таб1:Таб3!G9), вычислите сумму значений в
клетках G9 трех листов и зафиксируйте полученный результат в
клетке G15 листа «Таб1».
Для создания такой формулы необходимо ввести в ячейку
формулу =СУММ, выделить первый лист диапазона щелчком мыши, затем при нажатой клавише «Shift» щелкнуть мышью на ярлычке «Листа3», выделить ячейку G9 и нажать клавишу «Enter».
7. Использование команды «Специальная вставка» для
арифметических операций при вставке.
Пользуясь командой Правка/Специальная вставка, уменьшите значения в диапазоне B10:F10 в три раза. Для этого занесите
в буфер обмена число «3» и выделите необходимый диапазон
B10:F10, после чего в меню Правка/Специальная вставка выберите операцию «Разделить». В строке формул убедитесь, что в формуле для ячеек B10: F10 нашло отражение произведенного деления.
8. Сохраните изменения в книге «ФА1». Удалите из папки
«Рабочая» файл и архив «Таблицы».
9. Заархивируйте папку «Рабочая» (имя архива – ваша фамилия) и переместите архив на диск G:/ в папку с номером вашей
группы.
23
24
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
III. ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ
ТАБЛИЧНОГО ПРОЦЕССА EXCEL
Задание 7. Функции категорий – математические, статистические, массивы и даты.
Функции – это встроенные объекты пакета Excel, призванные облегчить работу при взаимодействии с электронными таблицами. Пакет предоставляет большое число функций, которые объединены в группы по назначению и характеру выполняемых операций:
– математические;
– статистические;
– даты и времени;
– логические;
– просмотра и ссылки и др.
В общем виде любая функция должна быть записана так:
«Имя функции (список аргументов)»:
• имя функции – это фиксированный набор символов, выбираемый из списка функций;
• список аргументов (который в отдельных случаях может
состоять только из одного аргумента) – это данные, над которыми
будут выполняться операции, реализуемые функцией. В качестве
аргументов могут быть использованы константы, адреса ячеек,
другие функции.
Ввод функций удобно осуществлять с помощью диалога, называемого «Мастером функций». Этот диалог доступен после то-
2. Математические и статистические функции.
2.1. Откройте книгу «Имя.xls». В столбце «А» указаны те
функции, которые вам нужно будет выбрать, используя «Мастер
функций».
2.2. Введите в клетку B3 функцию СЛЧИС(), возвращающую случайное число из диапазона {0,1}, и скопируйте ее в клетки C3:E3 (для выбора нужной функции используйте кнопку
«Вставка функции» на стандартной панели инструментов, категории «Математические» и «Статистические»).
2.3. Скопируйте значения клеток B3:E3 в клетки B4:E4, используя специальную вставку (Правка/Специальная вставка/Значения). Увеличьте значения клеток B4:E4 в 1 000 раз и разместить
результаты в диапазоне B5:E5.
Введите функции, указанные в клетках столбца А, в соответствующие клетки (B6:B22) столбца В и, где это указано, скопируйте их в столбцы С:Е. (рис. 1, файл «Таблицы»).
2.4. Сохраните изменения в таблице в книге «Имя1» и сравните с рис. 1 (файл «Таблицы»).
го, как будет нажата кнопка
на панели инструментов.
Диалог «Мастера функций» построен по тематическому
принципу. В левом списке находятся названия групп функций.
Щелкнув на нужном вам названии, в правой части вы получите
полный список имен функций, содержащихся в данной группе.
Вызов самой функции осуществляется щелчком на ее имени. Затем вы получите возможность ввести аргументы функции.
1. Создайте на диске H:\ папку «Рабочая». Скопируйте в нее
архив «G:/Задание7/Таблицы.rar», распакуйте архив в папку «Рабочая».
3. Функции даты и времени, просмотра и ссылки.
3.1. Добавьте 2-й лист книги «Имя1».
3.2. Введите в клетку С2 функцию, отображающую сегодняшнюю дату (используйте категорию «Дата и время»).
3.3. Введите в клетку С3 функцию «Дата», отображающую
произвольно выбранную дату.
3.4. В клетку С5 запишите функцию «Выбор» (категория
«Ссылки» и «Массивы»), позволяющую вывести название дня недели для даты, введенной в клетку С2 (понедельник, вторник, среда...), т. е. формат ячеек С5, С6 (все форматы ДДДД):
ВЫБОР(номер_индекса;значение1;значение2;…
Данная функция возвращает значение элемента из списка
аргументов, порядковый номер которого совпадает со значением
аргумента «номер_индекса»: =выбор(1;С2;С3).
3.5. В клетку С6 запишите аналогичную функцию для даты,
введенной в клетку С3.
3.6. На 3-м листе книги создайте таблицу, состоящую из двух
столбцов. В первый столбец ввести номера дней недели (1, 2, 3, 4,
5, 6), а во второй – перечень предметов (например, математика,
25
26
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
история и т. д.). В клетку А8 введите день недели, расписание занятий которого необходимо отобразить (любой из 6). В клетку В8
запишите функцию «Просмотр», выполняющую поставленную задачу (Категория «Ссылки и Массивы»):
ПРОСМОТР (искомое_значение; массив).
Функция просматривает первую строку или первый столбец
массива и находит указанное значение (искомое значение). Если
массив имеет больше строк, чем столбцов, то функция «Просмотр»
ищет в первом столбце.
«Искомое_значение» может быть текстом, числом или логическим значением.
3.7. На 4-м листе книги создайте таблицу, приведенную на
рис. 2.
3.8. В клетку В8 запишите функцию, дающую ответ на вопрос: «Какую стипендию в 4-м семестре получил студент Иванов?» Значения 4-го семестра и фамилия студента должны быть
введены в клетки А8 и А9. Для решения поставленной задачи использовать функцию «Выбор».
4. Статистические функции «Ранг» и «Предсказание».
4.1. На 5-м листе книги создайте таблицу, приведенную на
рис. 3.:
РАНГ (число; ссылка; порядок).
Эта функция возвращает ранг числа в списке чисел (порядок
определяет порядок сортировки). Ранг числа – это его величина
относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией.)
Число – это число, для которого определяется ранг.
Ссылка – это массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются.
Порядок – это число, определяющее способ упорядочения.
4.2. Используя функцию «Ранг», определите ранги цехов в
зависимости от объема продаж по каждому году и поместите результаты в соответствующие клетки таблицы.
4.3. Пользуясь информацией об объемах продаж, спрогнозируйте объемы продаж для каждого цеха в 1999 г., пользуясь функцией «Предсказание».
27
Эта функция вычисляет или предсказывает будущее значение по существующим.
Предсказываемое значение – это y-значение, соответствующее заданному x-значению.
Известные значения – это x- и y-значения, а новое значение
предсказывается с использованием линейной регрессии.
Эту функцию можно использовать для предсказания будущих продаж, потребностей в оборудовании или тенденций потребления:
ПРЕДСКАЗ (x; известные_значения_y;известные_значения_x)
X – это точка данных, для которой предсказывается значение.
Известные_значения_y – это зависимый массив или интервал данных.
Известные_значения_x – это независимый массив или интервал данных.
Например: =предсказ($Е$2;В3:D3; $B$2:$D$2).
4.5. Используя функцию «Ранг», определите ранги цехов в
зависимости от объема продаж в 1999 г. и поместите результаты в
соответствующие клетки таблицы.
4.6. В ячейки J3:J7 запишите формулы для вычисления средних значений рангов цехов (используя функцию «Средзнач»).
5. Примеры для самостоятельной работы.
5.1. В новой книге создайте таблицу по образцу рис. 4. Переименуйте «Лист1» в «Примеры функций» (ячейки С4:С7 оставьте незаполненными для ввода расчетных формул).
5.2. В ячейку С4 введите формулу расчета квадратного корня из произведения содержимого клетки А4 на абсолютное значение числа из клетки В4.
5.3. В ячейку С5 введите формулу для возведения содержимого ячейки А5 в степень числа, содержащегося в ячейке В5.
5.4. В ячейку С6 введите формулу расчета абсолютного значения целой части разности содержимого ячеек А6 и В6.
5.5. В ячейку С7 запишите формулу расчета остатка от деления содержимого ячейки А7 на содержимое ячейки В7.
Сверьте свои результаты с данными, представленными в
графе «Результаты».
28
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
5.6. Левее создайте еще одну таблицу (рис. 5) (графу «Результаты» по-прежнему не заполняйте).
5.7. В ячейку G4 введите формулу расчета суммы значений
из диапазона Е4:Е7.
5.8. В ячейку G5 введите формулу расчета среднего арифметического диапазона ячеек c E4 по F7.
5.9. В ячейку G6 введите формулу поиска максимального
значения для данных диапазона F4:F7.
5.10. В ячейку G7 введите формулу расчета коэффициента
корреляции значений, содержащихся в колонках E и F.
Сверьте свои результаты с данными, представленными в
графе «Результаты».
Сохраните книгу «Примеры.xls» в папке «Рабочая».
симв» и оператор «&»). И разместите его под исходным списком,
начиная с ячейки В13 (пробелы и точки выделяйте кавычками).
Функция «Левсимв» выделяет строку символов заданной
длины из другой строки, начиная с крайней левой позиции. Эта
функция имеет два аргумента. Первый – строка, а второй необязательный аргумент – число символов. Если второй аргумент опущен, то MS Excel по умолчанию выделяет первый символ текста.
2. Текстовые функции.
Текстовые функции позволяют выполнять различные преобразования над строками символов. Например, можно извлечь фрагменты текста из длинных строк.
В качестве примера рассмотрим функцию «Сцепить»
(текст1; текст2; ...), которая является эквивалентом текстового
оператора «&». Пример: СЦЕПИТЬ(«Cow»; «Boy») → Cowboy.
2.1. Откройте файл «Список.xls», где дан список сотрудников фирмы, содержащий фамилию, имя, отчество. По этому списку составьте, начиная с ячейки F2, новый список, где будут объединены фамилия, имя и отчество (используя функцию «Сцепить»).
2.2. В файле «Список.xls», используя список сотрудников
фирмы, содержащий фамилию, имя, отчество полностью, составьте
новый список, содержащий фамилию и инициалы (функция «Лев-
3. Логические функции.
Логические функции предназначены для реализации многовариантных алгоритмов. Функция «Если» полезна для условной
проверки значений и формул. Функции «И» и «Или» могут применятся для проверки и объединений различных условий при использовании с функцией «Если».
Функция «И» (логическое1; логическое2; ...) возвращает
значение «Истина», если все аргументы имеют значение «Истина», возвращает «Ложь», если хотя бы один аргумент имеет значение «Ложь».
3.1. Добавьте второй лист книги «Список.xls». Введите в
клетки А2, В2, С2 произвольные числа.
3.2. В клетке А3 запишите функцию «Если», выполняющую
следующие действия:
если содержимое клетки А2<100, то записать в клетку А3
содержимое клетки А2, в противном случае записать число 100
(для этого щелкните «Функции» на кнопке «Вставка» и выберите
ее среди логических функций).
П о я с н е н и е . В функции «Если» – 3 аргумента: 1-й аргумент – это логическое выражение, которое может принимать значение «Истина» или «Ложь»; 2-й и 3-й аргументы – это выражения, которые вычисляются в случае, если 1-й аргумент принимает
истинное или ложное значение.
3.3. В клетке А4 запишите функцию «Если ()», выполняющую следующие действия:
• если содержимое клетки А2<100 и >50, то записать в клетку А4 содержимое клетки А2;
• если содержимое клетки А2>=100, то записать в клетку А4
содержимое клетки B2;
• в противном случае – С2.
29
30
6. Удалите из папки «Рабочая» файл и архив «Таблицы». Заархивируйте папку «Рабочая» (имя архива – ваша фамилия) и переместите архив на диск G:/ в папку с номером вашей группы.
Задание 8. Функции категорий – текстовые, логические,
финансовые.
1. Создайте на диске H:\ папку «Рабочая». Скопируйте в нее
архив «G:/Задание8/Таблицы.rar», распакуйте архив в папку «Рабочая».
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Для выполнения этого задания рекомендуется сначала проанализировать логическое выражение, изобразить его графически
и записать систему неравенств:
=ЕСЛИ (А2<50; C2; ЕСЛИ (А2<100;A2;B2)).
3.4. Занесите в клетки Е8:E10 три имени: Лена, Зина, Вера, а
в клетки F8:F10 занесите даты их рождений. В клетку E4 введите
одно из упомянутых имен.
Пользуясь конструкцией «вложенного» оператора для функции «Если», выполните следующие действия: проанализировав имя
в клетке Е4, запишите в клетку С12 функцию «Если», обеспечивающую вывод даты рождения, взятой из соответствующей клетки, если же введено неподходящее имя, записать текст сообщения:
«нет такого имени» (кавычки обязательны).
=если(Е4=Е8;F8;если(Е4=Е9;F9; …продолжите формулу
(формат ячейки С12 должен быть «Дата»).
3.5. Откройте книгу «Комиссия.xls» и заполните столбцы
«Комиссионные» и «Категория», используя функцию «Если». Сохраните изменения.
4. Некоторые финансовые функции.
Финансовые функции используются для анализа финансовохозяйственной деятельности. Среди финансовых функций можно
выделить следующие блоки функций:
• вычисление амортизации;
• анализ инвестиций;
• анализ ценных бумаг;
• вычисление скорости оборота вложений (нормы прибыли).
4.1. На втором листе книги «Комиссия» вычислите ежегодную сумму амортизации оборудования, приобретенного за 80 000
руб. и имеющего остаточную стоимость 1 000 руб. Оборудование
имеет срок эксплуатации – 10 лет. Для вычислений использовать
функцию АМР. Запишите данные в ячейки А5:С5, функцию – в
ячейку D5. Используйте следующую функцию:
АМР (стоимость; ликвидная_стоимость; время_амортизации).
Функция АМР возвращает величину непосредственной амортизации имущества за один период. «Стоимость» – начальная стоимость основных фондов; «Ликвидная_стоимость» – остаточная
31
стоимость в конце периода амортизации; «Время_амортизации» –
число периодов, за которое амортизируется имущество.
4.2. Грузовик куплен за 30 000 руб. Он имеет срок эксплуатации 10 лет и остаточную стоимость 7 500 руб. Вычислите амортизацию за 1-й год и 2-й год. Запишите данные в ячейки А7:С7,
функцию – в ячейки D7 и Е7.
АМГД(30 000;7 500;10;1) → 4 090,91 руб., за второй год:
АМГД(30 000;7 500;10;2) → 3 681, 82 руб.
АМГД (стоимость; ликвидная_стоимость; жизнь; период).
Функция АМГД возвращает общегодовую величину амортизации для указанного периода.
Аргументы «Стоимость» и «Ликвидная_стоимость» аналогичны соответствующим аргументам функции АМР; «Жизнь» –
это число периодов, за которое амортизируется имущество; «Период» – период, за который рассчитывается амортизация.
Функции выплат по займам и вкладам.
Все эти функции используют одни и те же аргументы, хотя
некоторые применяются в зависимости от конкретной функции.
Функция
БЗ
ПЗ
ППЛАТ
КПЕР
Назначение
Будущее
значение вклада
Текущий
объем вклада
Величина
выплаты
Количество
периодов
Аргументы
Норма; Кпер; Выплата;
[Нз]; [Тип]
Норма; Кпер; Выплата;
[Бз]; [Тип]
Норма; Кпер; Нз; [Бз];
[Тип]
Норма; Выплата; Нз; [Бз];
[Тип]
Ниже приведено краткое описание аргументов:
Норма (ставка) – это процентная ставка за период.
Кпер (число периодов) – это общее количество платежей
или периодов выплат.
Выплата – это плата, производимая в каждый период и не
меняющаяся за все время займа; параметр «Тип» определяет время
выплаты.
32
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
БЗ – это будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если аргумент БЗ
опущен, он полагается равным нулю (например, будущая стоимость займа равна нулю).
НЗ – текущее значение – начальная стоимость вложения или
ссуды. Так, начальная стоимость ссуды равна, собственно, сумме
займа.
Тип – задает режим выплат. Для выплат в конце месяца указывается значение ноль, для выплат в начале месяца – единица.
Если аргумент «тип» опущен, то он полагается равным нулю.
4.3. Вычислите сумму ежемесячных процентных выплат за
кредит, взятый в размере 50 000 $ под 13 % годовых со сроком погашения 5 лет. Для вычислений использовать функцию «ППЛАТ».
Запишите данные в ячейки А13:С13, функцию – в ячейку
D13 =ППЛАТ(13%/12;60;50 000).
4.4. Вычислите количество платежей при ежемесячных выплатах в 800 руб. за кредит 60 000 руб., взятый под 12 % годовых.
Для вычислений использовать функцию «КПЕР» (количество периодов выплат для данного вклада):
=КПЕР(12%/12;–800;60000). Запишите данные в ячейки
А15:С15, функцию – в ячейку D15.
4.5. Вычислите накопленную сумму, если в течение трех лет
в конце каждого месяца был произведен вклад в 400 руб. под 6 %
годовых.
Для
вычислений
используйте
функцию БЗ.
=Б3(6%/12;3*12;–400;0). Запишите данные в ячейки А17:С17,
функцию – в ячейку D17.
Функция Б3 возвращает будущее значение вклада на основе
периодических постоянных платежей и постоянной процентной
ставки.
4.6. Вычислите сумму кредита, который вы можете получить, если хотите взять кредит в немецких марках на 3 года под
10 % годовых, выплачивая проценты один раз в конце года. Вы
можете выплатить не более 1200 DM. Для вычислений используйте функцию ПЗ: =ПЗ(10%;3;–400).
Результаты вычислений запишите в ячейки А19:С19, функцию – в ячейке D19 «Листа 2».
Функция П3 возвращает текущий объем вклада. Текущий
объем – это общая сумма, которую составят будущие платежи при
постоянной процентной ставке. Платежи должны иметь равные
значения (выплата). Общее число выплат задается аргументом
«КПЕР». Если «БЗ» или «Тип» опущены, они полагаются равными
нулю.
5. Удалите из папки «Рабочая» файл и архив «Таблицы». Заархивируйте папку «Рабочая» (имя архива – ваша фамилия) и переместите архив на диск G:/ в папку с номером вашей группы.
33
34
Задание 9. Самостоятельная работа по использованию
функций.
1. Создайте на диске H:\ папку «Рабочая». Скопируйте в нее
архив «G:/Задание9/Таблицы.rar», распакуйте архив в папку «Рабочая».
2. Расчет амортизационных отчислений.
2.1. Откройте файл «Справочник.xls», ознакомьтесь с приведенными таблицами. Нормы амортизации выберите из справочной таблицы «Нормы» с помощью функции «Просмотр».
Добавьте рядом со столбцом «Нормы аморт.» столбец с таким же названием и выберите из справочной таблицы «Нормы»
годовую норму амортизации с помощью функции ВПР (группа
функций «Массивы и ссылки»). В общем виде функцию можно
записать так:
= ВПР (адрес ячейки, содержащей «ключевое» значение;
диапазон справочной таблицы; порядковый номер графы справочной таблицы, содержащей искомое значение).
Если предварительно обозначить диапазон ‘Нормы‘ !А3:D13
как «справ», то формула для выборки нормы амортизации из справочника будет иметь вид (например, для ячейки D4):
=ВПР(C4;справ;3), где С4 – «ключевое» значение для которого выбирается соответствующая норма амортизации; 3 – номер графы
справочной таблицы, содержащей годовую норму амортизации.
Амортизация основных средств рассчитывается исходя из
балансовой стоимости средства, срока его эксплуатации и годовой
нормы амортизации этого основного средства. При этом под сро-
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ком эксплуатации понимают количество полных месяцев, прошедших со дня ввода его в эксплуатацию до даты, на которую выполняется расчет.
Аморт. = Баланс.стоимость *Норма аморт.* Понижающий коэфф. (в зависимости от срока эксплуатации).
2.2. Вам будет значительно проще сформировать формулу,
если вы вставите дополнительную колонку перед графой «Амортизация». Назовите ее «Срок эксплуатации, мес.»
2.3. Если дата, на которую выполняется расчет амортизации,
внесена в ячейку К1, а срок ввода в эксплуатацию содержится в
ячейке G3, то количество полных месяцев эксплуатации основного средства можно определить так:
= ОТБР(ДНЕЙ360(G3;$K$1)/30), где:
• функция ДНЕЙ360 (группа функций «Дата и время») позволяет определить период между двумя датами в расчете на условный год, состоящий из 360 дней, иначе говоря игнорирует 31-е
число для «длинных» месяцев;
• функция ОТБР (группа функций «Математические») позволит отбросить остаток от деления на 30, получив в результате
количество полных месяцев эксплуатации.
2.4. Рассчитайте величину амортизации (см. определение
амортизации в п. 2.1.), учитывая срок эксплуатации: если он более
2 месяцев, то понижающий коэффициент 0,03; если больше 1 месяца, но меньше 2, то 0,02, если менее 1 месяца, то 0,01.
Расчет остаточной стоимости.
Остаточная стоимость основного средства определяется как
разница между балансовой стоимостью и амортизацией, начисленной с начала его эксплуатации. Сформируйте формулу самостоятельно.
Спрячьте столбец таблицы «Инв. номер». Добавьте итоги в
графах «Балансовая стоимость», «Амортизация», «Остаточная
стоимость». Установите формат ячеек F, J и I – денежный, рублевый. Сохраните изменения, сравните результат с рисунком.
35
3. Расчет затрат на подготовку отчета.
3.1. Откройте «Лист1» книги «Затраты.xls» и сделайте расчет затрат на подготовку отчета.
Рассчитайте стоимость машинного времени с учетом того,
что если затраты времени на выполнение одного из этапов менее
20 минут, то стоимость машинного времени определите как затраты времени*0,35, если свыше 20 мин., то затраты времени*0,2.
Формат ячеек – рублевый, один знак после запятой.
3.2. В ячейке D17 рассчитайте общее время на подготовку
отчета, в ячейке Е17 – общую сумму (руб). Заполните ячейки
D18:D20 и Е18:Е20, используя «Мастер функций».
3.3. Рассчитайте проценты затрат времени и денег от общей
суммы в столбцах Е и D. «Лист1» назовите «Отчет». В диапазон
ячеек G2:H2 на оба листа книги вставьте сегодняшнюю дату
(функцию), а в I2:J2 – вашу фамилию. Сделайте заливку желтым
цветом. Сохраните изменения.
4. Расчет зарплаты за год.
4.1. Перейдите ко второму листу книги «Затраты». Рассчитайте, какая сумма начислена каждому из сотрудников за год,
учитывая его оклад, количество отработанных дней и расчетное
количество дней в году (ячейка F5).
4.2. В столбце Е рассчитайте удерживаемый налог, который
составит 12 % от начисленной суммы, если она менее 2 000 руб.,
13 % – если менее 10 000 руб., и 15 % – если более 10 000 руб. в год.
36
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
4.3. В столбце «Получено» введите формулу полученной за
год суммы как разницу между начисленной суммой и налогом.
Лист назовите «Зарплата».
4.4. В ячейке F20 рассчитайте общую сумму зарплаты, а в
столбце G – % от общей суммы.
5. Использование финансовых функций.
5.1. На листе «Зарплата» с помощью «Мастера функции»
рассчитайте, сколько денег будет на счету в конце срока, если вы
вложили 1 000 рублей под 8 % годовых. Вы собираетесь вкладывать по 200 рублей в начале каждого следующего месяца в течение следующих 5 лет. В диапазон А23:Е23 введите данные и результат вычислений, в диапазон А22:Е22 – названия столбцов.
5.2. В следующую строку внесите данные и вычислите общую сумму ссуды на покупку автомобиля, которую вы можете
себе позволить при ежемесячных выплатах по ней в 900 рублей в
течение 4 лет при годовой процентной ставке в 11 %.
6. Заархивируйте папку «Рабочая» (имя архива – ваша фамилия) и переместите архив на диск G:/ в папку с номером вашей
группы.
IV. ГРАФИЧЕСКОЕ ПРЕДСТАВЛЕНИЕ
ТАБЛИЧНЫХ ДАННЫХ
Занятие 10. Построение и редактирование диаграмм
1. Очистите диск H: Создайте на диске H:\ папку «Рабочая».
Скопируйте в нее архив G:/Задание10/Рисунки.rar и распакуйте
архив в папку «Рабочая».
2. Введите таблицу, представленную на рис.1, на первый и
второй листы книги «Диаграммы». Дайте имя «Листу 2» – «Внедренная диаграмма». В меню Вид/Панель инструментов выберите
панель «Диаграмма».
3. Создание диаграммы на рабочем листе.
3.1. Постройте гистограмму, отображающую сравнение плановых и фактических показателей производства за первое полугодие 1999 г.
Используйте данные таблицы на первом листе. Выделите
диапазон введенных данных, включая заголовки строк и столбцов.
Нажмите клавишу F11.
MS Excel построил на основе выделенных данных диаграмму и поместил ее на новый лист под названием «Диаграмма 1».
При этом MS Excel использовал предусмотренный по умолчанию
тип диаграммы (это двухмерная гистограмма).
3.2. Гистограмму озаглавьте «Показатели производства»
(контекстное меню: Параметры диаграммы/Заголовки/Название
диаграммы). Замените название листа «Диаграмма 1» на «Гистограмма».
3.3. Изучите все возможности, которые предоставляет панель инструментов «Диаграмма».
4. Создание внедренной диаграммы одним нажатием
клавиши.
На листе «Внедренная диаграмма» создайте объемную круговую диаграмму плановых показателей, используя панель инструментов «Диаграмма» и данные таблицы. Выделите диапазон
введенных данных, включая заголовки строки «План» и заголовки
столбцов.
37
38
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Нажмите «Тип диаграммы» на панели инструментов «Диаграмма», выберите указанный в задании тип. Поместите полученную диаграмму под таблицей, уменьшив ее по ширине до размеров таблицы.
Сохраните книгу под именем «Диаграммы» в папке «Рабочая».
5. Создание диаграмм различных типов с использованием «Мастера диаграмм».
Создайте диаграммы разных типов на отдельных листах.
Каждый лист должен иметь название, соответствующее типу диаграммы, расположенной на нем – диаграмма с областями («Области»), диаграмма типа график («График»), круговая диаграмма
(«Круговая»), кольцевая диаграмма («Кольцевая»), объемная трехмерная гистограмма («Трехмерная гистограмма»). Последовательность действий:
• используя данные таблицы на первом листе, выделите диапазон необходимых данных, включая заголовки строк и столбцов;
• нажмите клавишу «Мастер диаграмм» на панели «Стандартная» или в меню «Вставка» пункт «Диаграмма». Выберите
необходимый тип диаграммы. Нажмите кнопку «Далее». В открывшемся окне изучите закладки, затем снова нажмите «Далее».
Снова изучите предложенные закладки, нажмите «Далее». На последнем шаге создания диаграммы укажите название листа, куда
будет помещена созданная диаграмма.
6. Несколько диаграмм на одном листе.
6.1. Создайте в рабочем листе «Внедренная диаграмма» две
круговые диаграммы, отображающие плановые и фактические показатели производства по месяцам.
6.2. Вставьте в книгу новый лист диаграмм и назовите его
«Две диаграммы». Переместите созданные диаграммы на лист
«Две диаграммы», предварительно уменьшив их размеры.
7. Редактирование диаграммы.
7.1. В диаграмме на листе «Области» замените тип диаграммы для данных, обозначающих «План», на график с маркерами и
назовите лист «Области_График».
39
Выделите ряд «План». В контекстном меню выберите пункт
«Тип диаграммы» и выберите заданный тип.
7.2. Отредактируйте круговую диаграмму, созданную на
листе «Круговая», так, как показано на рис. 2 (надпись «Показатели производства» оформляете, если останется время). Последовательность действий:
• выделите ряд «План». С помощью контекстного меню или
на панели инструментов «Диаграмма» выберите «Формат рядов
данных», а на закладке «Подписи данных» – пункт «Категория и
доля»;
• аналогично выберите «Формат подписей данных», выделив
предварительно подпись данных; на закладке «Выравнивание» в
пункте «Положение подписи» – «Внутренний конец»;
• выберите «Формат названия диаграммы» и отформатируйте шрифт заголовка диаграммы. Затем захватите мышкой рамку
выделения заголовка и переместите ее под диаграмму;
• сектор диаграммы за июнь выделите двойным щелчком
левой кнопки мышки и, зацепив мышкой, отодвиньте на необходимое расстояние. Надпись «Мax» оформите с помощью панели
рисования (Автофигуры/Выноски/Надпись).
7.3. Отредактируйте линейные графики так, как показано на
рис. 3. Последовательность действий:
• выделите ось категорий (Х), выберите «Формат оси», на
вкладке «Вид» выберите значение Метки делений/Вверху, а Основные/Наружу;
• выделите ось значений (У), выберите «Формат оси». На
вкладке «Шкала» снимите флажки в окнах «Авто» и установите
флажок в окне «Пересечение с осью Х в максимальном значении».
В окне «Минимальное значение» установите значение – 800, в окне «Максимальное значение» – 2 200, в окне «Цена основных делений» – 200;
• с помощью панели «Рисование» («Надпись») подпишите
линии «План», «Факт», а диаграмму – «Линейные графики». Удалите легенду диаграммы;
• увеличьте толщину линий графика с помощью «Формата
рядов данных».
40
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
7.4. Редактирование объемных диаграмм.
7.4.1. Измените порядок рядов, представленных в диаграмме
на листе «Трехмерная». Для этого выделите ось ряда данных (ось
значений), выберите «Формат оси», на вкладке «Шкала» поставьте
флажок в окне «Обратный порядок значений».
7.4.2. Поверните диаграмму с помощью мыши, поместив
курсор мыши на один из углов параллелепипеда, описанного вокруг диаграммы после щелчка мыши на стенке диаграммы.
7.4.3. Изменить параметры «Возвышение», «Поворот», «Перспектива» для объемной гистограммы. Для этого выделите «Область построения диаграммы», в контекстном меню или в строке
основного меню («Диаграмма») выберите строку «Объемный вид».
Появится «Формат трехмерной проекции».
Просмотрите изменение параметра «Возвышение», соответствующий взгляду «сверху» (угол 90°); «сбоку» (угол 0°); «снизу»
(угол 90°). Установите параметр равным 10°.
Просмотрите изменение параметра «Поворот» диаграммы
вокруг оси Z для просмотра фронтально расположенных рядов
(угол 0°); под углом в 30°; под углом в 180°. Установите параметр,
равный 30°.
Установите параметр, изменяющий перспективу, равный 30.
7.5. Добавьте новую строку в исходную таблицу на первом
листе, в которой рассчитайте среднее значение между плановыми
и фактическими показателями. Отредактируйте кольцевую диаграмму на листе «Кольцевая», указав координаты данных, которыми будет дополнена диаграмма. Последовательность действий:
• выделите «Область построения диаграммы». В контекстном меню или в строке основного меню («Диаграмма») выберите
строку «Исходные данные». Появится окно «Исходные данные»;
• на вкладке «Ряд» нажмите кнопку «Добавить». В окне «Имя»
введите имя «Среднее». В окне «Значения» нажать кнопку со стрелкой. В исходной таблице на первом листе выделите строку средних значений, задав тем самым необходимые значения для дополнения исходной диаграммы. Вновь нажмите кнопку со стрелкой,
вернитесь в окно «Исходные данные» и нажмите «ОК»;
• в ряд данных «Среднее» введите подписи значений категории и доли.
41
7.6. На листе «Гистограмма» на ряду данных «Факт» создайте линию тренда следующим образом: выделите ряд данных
«Факт», в контекстном меню или в основном меню («Диаграмма»)
выберите параметр «Добавить линию тренда», в окне «Линия
тренда» на закладке «Тип» выберите тип «Скользящее среднее».
Для сравнения, аналогичным образом постройте экспоненциальную линию тренда для ряда «Факт».
Сохраните изменения.
8. Откройте книгу «Ведомость.xls». Рассчитайте средний
балл по всем предметам (формат ячеек числовой с 1 знаком после
запятой).
Вставьте на том же листе под таблицей круговую диаграмму
с отображением среднего балла по предметам. Озаглавьте диаграмму, сделайте подписи данных (величину среднего балла), в
легенде должны быть отображены названия предметов.
9. Удалите архив «Рисунки.rar» из папки «Рабочая». Заархивируйте папку «Рабочая» (имя архива – ваша фамилия) и переместите архив на диск G:/ в папку с номером вашей группы.
42
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
V. ОБРАБОТКА ДАННЫХ ОРГАНИЗОВАННЫХ
В СПИСКИ. СОРТИРОВКА. ФИЛЬТРАЦИЯ
Задание 11. Знакомство с методами обработки данных,
организованных в списки, сортировкой, редактированием, просмотром, поиском и извлечением данных по различным критериям
1. Создайте на диске H:\ папку «Рабочая» для хранения созданных вами файлов. Скопируйте в нее архив «G:/Задание11/Таблицы.rar», распакуйте архив в папку «Рабочая».
Изменение порядка строк в списке называется сортировкой.
При создании списка не рекомендуется создавать дополнительные
строки, отделяющие заголовки столбцов, так как MS Excel может
рассматривать такие строки как данные.
Строки списка называются записями, а столбцы – полями.
Основными видами обработки баз данных являются сортировка с анализом полученных результатов, поиск и извлечение
данных. MS Excel предоставляет различные способы сортировки
данных: можно сортировать строки или столбцы в возрастающем
или убывающем порядке (текстовые данные – в алфавитном или
обратном алфавитном порядке). Кроме того, MS Excel позволяет
создать свой собственный порядок сортировки. При сортировке по
строкам изменяется порядок их расположения в списке, в то время
как порядок расположения столбцов остается прежним. При сортировке по столбцам соответственно изменяется порядок расположения столбцов. При одном обращении к команде «Сортировка»
MS Excel дает возможность осуществить три уровня сортировки.
2. Сортировка списка по строкам.
2.1. Откройте табличный редактор MS Excel. Пользуясь методом группового заполнения листов, создайте на пяти листах таблицу, приведенную на рис. 1.
2.2. Пользуясь командами Данные/Сортировка, на первом
листе отсортируйте список по должности так, чтобы в начале списка были женщины-секретари.
Для этого установите курсор в любую ячейку в таблице, затем выберете Данные/Сортировка. В списке «Сортировать по»
43
выберите «Должность», порядок сортировки по убыванию, в поле
«Затем по» выберете «Пол». Переключатель «Идентифицировать
поля по» в нижней части окна диалога устанавливается в положение «Подписям». В этом случае заголовки столбцов (строк) не будут включены в сортировку. Щелкните «ОК», чтобы выполнить
сортировку.
Выделите строки, относящиеся к женщинам-секретарям, желтым цветом. Если результат сортировки оказался неудовлетворительным, можно использовать команду Правка/Отменить сортировку для восстановления предыдущего состояния списка».
Сохраните книгу с именем «Список. Xls» в папке «Рабочая».
2.3. На втором листе отсортируйте список так, чтобы было
видно, сколько Ивановых работают в фирме и кто из них самый
молодой.
Выделите строки, относящиеся к Ивановым, желтым цветом.
3. Использование промежуточных итогов для анализа
списка.
Команда Данные/Итоги выбирается после использования
команды Данные/Сортировка, когда список отсортирован по заданным критериям.
Команда Данные/Итоги добавляет строки промежуточных
итогов для каждой группы элементов списка, а также создает общие итоги. При этом можно использовать различные функции для
вычисления итогов (например, «Сумм» или «СРЗНАЧ»).
При выводе промежуточных итогов MS Excel создает структуру списка. Чтобы вывести интересующий пользователя уровень
детализации данных, нужно щелкнуть мышью на соответствующем символе структуры.
3.1. Перейдите к «Листу 3». Пользуясь командами Данные/
Сортировка и Данные/Итоги, определите каков средний возраст
мужчин и женщин, работающих в фирме. Для этого сначала отсортируйте список по полу. После столбца «Пол» добавьте столбец «Возраст» и заполните его (формулу = 2004 – «год рожд.»
скопируйте в ячейки Е3:Е11). Затем выделите ячейки D1:E11 и
выберете команду Данные/Итоги. Заполните диалоговое окно следующим образом: при каждом изменении выберите «Пол»; операция – «Среднее»; Добавить итоги по/Возраст. Результат работы
44
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
команды дает средний возраст для мужчин и женщин и общий
средний возраст. При выполнении этой команды список был структурирован. Щелчок мыши на символе строки 1-го уровня дает
возможность просмотреть только общие итоги, на символе строки
2-го уровня – вывести только промежуточные и общие итоги.
3.2. Перейдите к «Листу 4». Пользуясь командами Данные/
Сортировка и Данные/Итоги, определите сколько в фирме менеджеров, инженеров, водителей и представителей других должностей. (Сначала сделайте сортировку по должностям, затем выберите команду Данные/Итоги; при каждом изменении – Должность; операция – Кол-во значений/Добавить итоги по/Должность.)
3.3. Перейдите к «Листу 5». Отсортируйте список так, чтобы
в начале списка были женщины, начиная с самой младшей. Выделите этот диапазон желтым цветом. Сохраните изменения.
Выделите желтым цветом 7-ю строку.
Скопируйте таблицу на 10-й лист.
4.6. Переставьте столбцы так, чтобы в первых колонках были учащиеся, хорошо успевающие по математике, но с провалами
по физкультуре: Сортировать по/Строка 3/По убыванию, Затем
по/Строка 5/По возрастанию.
Выделите желтым цветом строки «Математика» и «Физкультура». Скопируйте таблицу на 11-й лист.
4.7. Переставьте столбцы так, чтобы в начале таблицы оказались отличники по физике, причем первыми должны идти те, у
кого наилучший средний балл.
Выделите желтым цветом строки «Физика» и «Средний
балл». Сохраните изменения.
4.8. Откройте файл «Группы.xls» и выполните инструкции,
приведенные там.
Сохраните изменения.
4. Сортировка списка по столбцам.
4.1. Введите таблицу, представленную на рис. 2, на 6-й лист
книги «Cписок. xls».
4.2. Отсортируйте ее так, чтобы предметы (названия полей)
располагались в алфавитном порядке. Для этого выделите диапазон В1:F10; выберете команду Данные/ Сортировка/ Параметры
и установите флажок «Сортировать столбцы диапазона».
Скопируйте ее на 7-й лист. На этом листе транспортируйте
таблицу, установив курсор в ячейку А12 (Правка/Специальная
вставка/Транспонировать). Добавьте в конец таблицы строку
«Средний балл» и заполните ее, введя соответствующую формулу.
В ячейке Н9 укажите свою фамилию.
4.3. Выполните сортировку, полученной после транспонирования, таблицы по столбцам. Выделите желтым цветом 1-ю строку. Скопируйте транспонированную таблицу на 8-й лист, начиная
с ячейки А1.
4.4. Переставьте столбцы так, чтобы фамилии студентов расположились в обратном алфавитном порядке.
Скопируйте таблицу на 9-й лист.
4.5. Переставьте столбцы так, чтобы в первых колонках были худшие учащиеся (с минимальным средним баллом): Сортировать по/Строка 7/По возрастанию.
5. Обработка списков с помощью формы.
5.1. Активизируйте «Лист1» книги «Список.xls» и убедитесь, что первая строка таблицы содержит заголовки полей. Поместите курсор внутри списка и вызовите команду Данные/Форма.
В форме отображается одна запись списка, при этом поля
расположены вертикально. В заголовке формы выводится имя
листа, ниже перечислены заголовки всех столбцов списка. В верхнем правом углу выводится информация об общем количестве записей в списке и номере записи, отображенной в форме. Рядом с
заголовком столбца располагается поле ввода, если столбец не содержит значений, вычисленных с помощью формул. Форма позволяет выполнять следующие действия с записями:
• просмотр;
• удаление;
• добавление;
• редактирование;
• поиск по критерию.
Для просмотра записей списка можно использовать полосу
прокрутки, либо кнопки «Далее» и «Назад». Удаление записей производится посредством кнопки «Удалить» без возможности вос-
45
46
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
становления записи. Добавление записи производится с помощью
кнопки «Добавить». Новая запись присоединяется к концу списка.
Поиск записей по критерию осуществляется с помощью
кнопки «Критерий».
5.2. Используя окно «Форма», просмотрите значения в списке, примите на работу одного инженера и увольте одного водителя. Бегунок на полосе прокрутке опустите вниз – появится заголовок «Новая запись».
5.3. Сотруднице Абрамовой поменяйте фамилию на «Иванова».
6. Изменение структуры таблицы.
6.1. Введите дополнительное поле «Оклад» после поля
«Пол» (выделите столбец и добавьте ячейки со сдвигом вправо) и
заполните его осмысленными значениями. Заполнение производить в окне «Форма» после поиска записей с заданными должностями. При задании критерия поиска использовать минимальное
количество букв и символы шаблона ?, * (например, инженер –
и*), а также команды «Добавить», «Далее» и «Назад».
6.2. После поля «Оклад» добавьте еще три поля – «Надбавки», «Налоги», «К выплате».
6.3. Установите надбавки в размере 100 руб. женщинам
старше 50 лет и мужчинам старше 60. Директору и референту –
200 руб. Поиск соответствующих лиц выполнять через форму.
6.4. В обычном режиме редактирования заполните поле
«Налог» – 5 % от суммы оклада и надбавки, если сумма не превышает 2 тыс. руб., и 10 %, если свыше. При заполнении поля использовать функцию «Если».
6.5. Запишите формулу и заполните поле «К выплате» («Оклад» + «Надбавки» – «Налоги»). Сохраните изменения.
7. Поиск записей.
Выделите любую ячейку в таблице, затем выберите Данные/
Форма. Щелкните на кнопке «Критерии». Введите условие поиска
в одно или несколько текстовых полей. Щелкайте на кнопке «Далее», пока не найдете нужную запись, затем щелкните на кнопке
«Закрыть».
47
Выполните поиск сотрудников по следующим критериям:
• женщин с низким окладом (меньше 2 000 руб.);
• мужчин (моложе 30 лет) получающих больше 3 тыс. руб.;
• женщин, имеющих надбавки, номера телефонов которых
начинаются на «31».
8. Удалите из папки «Рабочая» файл и архив «Таблицы». Заархивируйте папку «Рабочая» (имя архива – ваша фамилия) и переместите архив на диск G:/ в папку с номером вашей группы.
Задание 12. Фильтрация табличных данных, организованных в списки
1. Создайте на диске H:\ папку «Рабочая» для хранения созданных вами файлов. Скопируйте в нее архив «G:/Задание12/Таблицы.rar», распакуйте архив в папку «Рабочая».
Фильтрация списка – это процесс сокрытия всех строк, кроме тех, которые удовлетворяют определенным критериям. В
MS Excel списки можно фильтровать двумя способами:
1) автофильтр используется для фильтрации по простым
критериям;
2) расширенный фильтр используется для фильтрации по
более сложным критериям.
2. Фильтрация записей с помощью функции автофильтра.
2.1. Откройте файл «Список.xls» (список сотрудников).
Чтобы автоматически отфильтровать список, сначала установите курсор на одну из его ячеек. Установите автофильтр (меню
Данные/Фильтр/Автофильтр).
MS Excel добавит в строку заголовков полей кнопки раскрывающихся списков (кнопки автофильтра). Выберите кнопку автофильтра, например у столбца «Надбавка». Раскрывающийся список содержит все значения столбца и кроме этого еще пять пунктов:
а) «Все» – отображает все элементы столбца и используется
для отмены фильтрации;
б) «Первые 10» – выбирает группу первых значений списка,
наибольших или наименьших, по указанному пользователем критерию;
48
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
в) «Условие» – позволяет фильтровать список по нескольким условиям;
г) «Пустые» – фильтрует список, отображая только строки с
пустыми ячейками в данном столбце;
д) «Непустые» – фильтрует список, отображая только строки с непустыми ячейками в данном столбце.
2.2. Установите автофильтр и, выбирая соответствующие
должности, примите на работу новых сотрудников.
Штат должен содержать троих менеджеров, четырех инженеров, двух секретарей, двух референтов, одного водителя, директора и заместителя директора (с одинаковыми окладами для одинаковых должностей).
Установите надбавки в размере 100 руб. женщинам старше
50 лет и мужчинам старше 60, референту – 200 руб, налоги и выплаты – путем копирования формул. Сохраните изменения. Когда
вы отфильтруете список, в строке состояния появится сообщение
о том, сколько строк отобрано, и изменится цвет кнопки автофильтра.
Используя автофильтр, можно применять несколько разных
функций («Срзнач», «Счет», «Макс», «Произвед», «Сумм» и т. д.)
для выделенных автофильтром значений, но делать это можно
только с помощью функции «Промежуточные. итоги» (категория
«Математические»). Выбор необходимой функции осуществляется путем указания номера этой функции в списке аргументов функции «Промежуточные. итоги» (см. справку по функции «Промежуточные. итоги»). Функция «Промежуточные. итоги» определяет значение выбранной функции только для тех данных, которые
получаются в результате фильтрации, игнорируя все скрытые
строки.
2.3. Добавьте в книгу «Список.xls» 8 листов и скопируйте
таблицу на 2–9 листы книги «Список.xls» (всего 8 таблиц). Применяя автофильтр, пункт «Условие» раскрывающегося списка и
функцию «Промежуточные. итоги», для таблицы 1-го листа определите, сколько в фирме женщин и каков их средний заработок.
Для этого щелкните на кнопке раскрывающегося списка в
поле «Пол» и выберите «Ж» – из списка будут выбраны все женщины этой фирмы. В столбце «Оклад» установите курсор в сво-
бодную ячейку и щелкните по значку ƒx на панели инструментов
«Стандартная». Выбрав функцию «Промежуточные. итоги», введите в качестве первого аргумента число «1», что означает выбор
функции «Ср.знач», а в качестве следующего аргумента выделите
содержимое столбца «Оклад». Таким образом будет вычислен
средний оклад для всех женщин этой фирмы. В следующей строке
также рассчитайте количество женщин.
Для таблицы 2-го листа определите, каков суммарный заработок у менеджеров и инженеров.
Для этого в поле «Должность» выберите «Условие» – появится «Пользовательский автофильтр». Выберите в левом верхнем
окне «Равно» и в правом верхнем окне должность «Инженер». Установите флажок «Или». Выберите в левом нижнем окне «Равно»
и в правом нижнем окне должность «Менеджер». Таким образом
будет постановлено условие одновременного выбора инженеров и
менеджеров.
Выберите следующую свободную ячейку и вставьте функцию «Промежуточные итоги».
Введите в качестве первого аргумента число «9», что означает выбор функции «Сумма», а в качестве следующего аргумента
выделите содержимое столбца «Оклад».
Таким образом будет подсчитан суммарный заработок инженеров и менеджеров.
Для 3-го листа определите трех самых старших сотрудников,
независимо от пола (Автофильтр/Год рождения/Первые10…).
Для таблицы 4-го листа: какой максимальный оклад имеет
сотрудница, не получающая надбавку. Для определения максимального оклада использовать «Промежуточные итоги».
Для таблицы 5-го листа: кто из мужчин живет в районе, где
телефоны начинаются с 310.. до 315…
Для таблицы 6-го листа: сколько в фирме работает Ивановых и каков их суммарный оклад.
Для таблицы 7-го листа: сколько сотрудников получают
больше 1 500 руб., но меньше 3 тыс. руб. (функция Промежуточные итоги/Счет/ графа «Оклад»).
49
50
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Для таблицы 8-го листа: сколько сотрудников получают
больше 4 000 руб. или меньше 2 000 руб., и кто из них не получает
надбавки.
Для таблицы 9-го листа: составьте список двух самых молодых инженеров. Сохраните изменения в книге «Список. xls».
3. Расширенная фильтрация.
Расширенный фильтр дает возможность использовать разнообразные критерии, связанные логическими функциями «И»
или «Или», а также задавать вычисляемые условия для поиска и
извлечения данных. При использовании расширенного фильтра
результат фильтрации может быть помещен в другую область листа, а может быть оставлен по желанию пользователя в самой таблице.
Расширенный фильтр активизируется командой Данные/
Фильтр/Расширенный фильтр.
В отличие от автофильтра расширенный фильтр требует задания условий отбора записей в отдельном диапазоне рабочего
листа. Диапазон критериев должен содержать, по крайней мере,
две строки. В первой строке помещаются заголовки столбцов, а
условия отбора – во второй и последующих строках. Заголовки в
диапазоне критериев должны точно совпадать с заголовками
столбцов, и поэтому их лучше создавать копированием соответствующих заголовков столбцов.
Для реализации функции «Или» условия отбора записываются в разных строках, а функции «И» – в одной.
Диапазон исходных данных – это табличная область, включающая заголовки столбцов.
Диапазон выходных данных, если он не совпадает с исходным диапазоном, задает область (вне таблицы и критериев), куда
должны быть помещены результаты фильтрации. Все три области
не должны пересекаться.
«Диапазон условий» должен располагаться над или под списком, чтобы его строки не были скрыты при фильтрации.
Для выполнения задания откройте книгу «Ведомость. xls» и
скопируйте таблицу на листы 2–6 данной книги.
51
На 1-м листе вам необходимо найти студентов, имеющих
тройки по математике. Для этого в ячейки С12, С13 введите диапазон критериев:
Математ
3
Затем выберите Данные/Фильтр/Расширенный фильтр. Появиться диалоговое окно «Расширенный фильтр».
«Исходный диапазон» – это ваш список.
Диапазон условий: $C$12:$C$13; установите флажок «Скопировать результат в другое место» и укажите диапазон выходных
данных, например $A$14 (можно указывать только адрес первой
ячейки диапазона).
Нажмите «ОК», и список будет отфильтрован по заданному
критерию. Сохраните изменения в книге «Ведомость. xls».
На 2-м листе перечислите студентов, имеющих тройки по
математике, но четверки по физике (разместите диапазон выходных данных под исходной таблицей); диапазон критериев:
Математ
3
Физика
4
На 3-м листе нужно выбрать студентов, имеющих тройки по
математике, но четверки по физике и средний балл больше 3,5
(предварительно добавив в таблицу столбец «Средний балл»).
На 4-м листе нужно перечислить студентов, имеющих тройки по математике или тройки по физике (множественный критерий отбора – «Или»).
На 5-м листе перечислите студентов, имеющих двойку по
любому предмету (хотя бы одну). Сохраните изменения.
На 6-м листе извлеките данные о студентах, имеющих четверки и пятерки по всем предметам (список фильтровать на месте); диапазон критериев:
Биология
>=4
Математ
>=4
Физика
>=4
52
Физкультура
>=4
Химия
>=4
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
На 7–10 листы скопируйте таблицу с листа 3 (где есть столбец «Средний балл») и
• на 7-м листе извлеките данные о студентах, у которых
средний балл меньше 4;
• на 8-м листе – средний балл больше, чем 3,5, и оценку по
математике больше тройки;
• на 9-м листе – средний балл не меньше, чем 4, или меньше
3,5, но по физике – 5;
• на 10-м листе – средний балл больше, чем 3,5, но меньше
4,2. Сохраните изменения.
Реализацию логической функции «И» для данных одного
столбца осуществлять повторным размещением в области критериев заголовка данного столбца.
4. Заархивируйте папку «Рабочая» (имя архива – ваша фамилия) и переместите архив на диск G:/ в папку с номером вашей
группы.
VI. СОЗДАНИЕ И ОБРАБОТКА СВОДНЫХ ТАБЛИЦ.
КОНСОЛИДАЦИЯ ДАННЫХ
Занятие 13. Cводные таблицы
Сводная таблица – это динамическая таблица итоговых данных, извлеченных или рассчитанных на основе информации, содержащейся в базе данных. Она позволяет создавать динамические, с легко изменяемой структурой, перекрестные таблицы, где
данные обобщаются по нескольким измерениям. Сводные таблицы полезны для обобщения информации из баз данных, которые
могут храниться как в рабочих книгах MS Excel, так и во внешних
файлах.
1. Очистите диск H:\ Создайте на диске H:\ папку «Рабочая».
Скопируйте в нее архив «G:\Задание13\Рисунки.rar» и распакуйте
архив в папку «Рабочая».
2. Создайте таблицу, приведенную на рис. 12.1 (файл «Рисунки»), содержащую данные о продажах строительных материалов. Назовите лист «Продажи» и сохраните книгу под именем
«ЗД_13.xls» в папке «Рабочая».
3. Создание сводной таблицы.
3.1. Создайте на новом листе книги «ЗД_13.xls» сводную таблицу, аналогичную приведенной на рис. 12.2. Для создания сводной таблицы можно использовать панель инструментов «Сводные
таблицы» (Вид/Панели инструментов/Сводные таблицы), а также
можно использовать «Мастер сводных таблиц» (Данные/Сводная
таблица). Воспользуемся средством «Мастер сводных таблиц» по
шагам:
1-й шаг – определение источника данных («Создать таблицу
на основе списка или базы данных MS Excel»);
2-й шаг – выбор диапазона исходных данных (диапазон
«$A$1:$G$11» листа «Продажи»);
3-й шаг – создание макета сводной таблицы. В этом окне
поля базы данных представлены в виде кнопок, которые расположены в столбце в правой части окна. Необходимо перетащить нужные кнопки в соответствующие области макета, задав тем самым
структуру будущей сводной таблицы. В каждую область можно
53
54
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
перетащить любое количество полей, все поля использовать не
обязательно. Неиспользованные поля не будут присутствовать в
сводной таблице.
В области данных по умолчанию применяется функция
«Сумм», если поле содержит числовые значения, и функция
«Счет», если поле содержит нечисловые значения.
Если вы случайно перетащили поле не в ту область, выведите поле за пределы макета.
В нашем случае в область «Строка» поместите поле «Дата»,
в область «Столбец» поместите поле «Название», в область «Данные» – поле «Сумма» (в области «Данные» появится текст «Сумма
по полю "Сумма"»).
4 шаг – завершение построения сводной таблицы («Поместить таблицу в новый лист» и нажать кнопку «Готово»).
3.2. Просмотрите полученную сводную таблицу. Проанализируйте ее структуру и сравните с образцом, представленным на
рис. 12.2. Дайте листу со сводной таблицей 1 название «Сводка_общая».
4. Работа со сводными таблицами.
Созданная сводная таблица не является статическим объектом. Ее можно менять и настраивать, пока она не будет выглядеть
так, как нужно.
4.1. Изменение структуры сводной таблицы.
Обратите внимание, что сводная таблица имеет кнопки полей (у нас – «Название» и «Дата»). Любую из кнопок можно перетащить в другое место сводной таблицы, например поменять местами строки и столбцы, изменить порядок полей в любой области.
Можно удалить поле из сводной таблицы, перетащив его за пределы сводной таблицы.
4.1.1. Зацепив мышкой, перетащите поле «Дата» и поставьте
рядом с полем «Название». Оцените удобство работы с такой таблицей.
4.1.2. Поменяйте местами поля «Дата» и «Название». Оцените удобство работы с такой таблицей.
4.1.3. Верните поле «Дата» в область строк. Таблица должна
принять первоначальный вид.
55
4.2. Группировка элементов сводной таблицы.
Иногда полезно группировать определенные элементы поля,
например объединить даты в месяцы или кварталы. Это удобно
делать с помощью инструментальной панели «Сводные таблицы».
Активизируйте инструментальную панель «Сводные таблицы».
4.2.1. Сгруппируйте данные о датах продаж по месяцам. Для
этого:
• установив курсор в любую ячейку, содержащую даты
продаж, щелкните кнопкой «Группировать» панели «Сводные таблицы»;
• в открывшемся диалоговом окне укажите в качестве критерия группировки месяцы;
• убедитесь, что результат группировки соответствует представленному на рис. 12.3.
4.2.2. Сгруппируйте данные о датах продаж по кварталам.
Выполните задание аналогично п. 4.2.1., выбрав в качестве критерия группировки квартал. Сравните полученный результат с представленным на рис. 12.4.
4.2.3. Сгруппировать данные о датах продаж по кварталам и
месяцам одновременно. Повторите группировку, выбрав в качестве критерия кварталы и месяцы одновременно. Сравните полученный результат с представленным на рис. 12.5.
4.3. Просмотр деталей сводной таблицы.
Каждая ячейка в области данных сводной таблицы представляет несколько записей исходной базы данных. Иногда нужно
выяснить, из каких полей состоит суммарное значение. Для этого
дважды щелкните на нужной ячейке области данных сводной таблицы. MS Excel создаст новый лист с теми записями, которые были использованы для получения информации в выбранной ячейке
сводной таблицы.
Просмотрите детализированную информацию о содержимом ячеек сводной таблицы таким образом:
• повторите группировку данных сводной таблицы по кварталам (рис. 12.4);
• для этой сводной таблицы установите курсор в непустую
ячейку, например ячейку D4 («Доски» – «Кв-л4»), и нажмите в
панели «Сводные таблицы» кнопку «Отобразить детали»;
56
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
• просмотрите в открывшемся листе детализированные
данные по наименованию «Доски», проданные в 4-м квартале.
Лист назовите «Доски». Сравните результат с представленным на
рис. 12.6.
4.4. Изменение функции подведения итогов в области данных.
Постройте на новом листе сводную таблицу аналогично п. 3.,
поместив в область «Столбец» поле «Название», в область «Строка» – поле «Дата», а в область «Данные» – поле «Цена». Лист назовите «Цены». «Мастер сводных таблиц» применит по умолчанию функцию «Сумм» для области данных.
Определите минимальную цену в области данных, изменив
функцию подведения итогов таким образом:
• выделите непустую ячейку в поле данных;
• на панели инструментов «Сводные таблицы» нажмите
кнопку «Поле сводной таблицы» – появится окно «Вычисление
поля сводной таблицы»;
• в поле «Операция» выделите «Минимум» и нажмите «ОК»
– в ячейке А1 появится надпись «Минимум по полю "Цена"».
Изучите таблицу. Общий итог в ячейке G11 равен 100, т. е. дешевле всего были проданы доски 40 мм 12.10.99;
• поменяйте функцию на «Максимум». Изучите таблицу.
Общий итог равен 850, т. е. дороже всего были проданы окна.
4.5. Вставка вычисляемого поля в сводную таблицу.
Сводная таблица – это особый тип диапазона данных, в который нельзя вставлять новые строки и столбцы. Это означает, что
вы не сможете вставить в сводную таблицу формулы, выполняющие операции над данными. Однако есть возможность создания
новых вычисляемых полей для сводной таблицы. В вычисляемом
поле выполняются вычисления, использующие значения из других
полей.
Например, вам необходимо рассмотреть структуру доходов
при увеличении цен на 10 %. Для этого вы можете вставить новое
вычисляемое поле и впишите туда необходимую формулу. Порядок действий следующий:
• постройте на новом листе сводную таблицу аналогично
п. 3. Назовите лист «Рост цен»;
57
• сгруппируйте данные по месяцам (п. 4.2.1.);
• щелкнув по полю «Название», откройте контекстное меню,
выберите Формулы/Вычисляемое поле – откроется окно «Вставка
вычисляемого поля»;
• в окне «Имя» введите «Рост цен». В окне «Поля» выберите
«Сумма» и щелкните «Добавить поле». Поле «Сумма» появится в
окне «Формулы». Продолжайте вводить формулу (ввести *1,1).
Нажмите «Добавить». Изучите таблицу. «Итог. сумма по полю рост
цен» должна быть равна 203 555.
5. Откройте файл «Объем продаж». Выполните инструкции,
приведенные в нем.
6. Удалите архив и файл «Рисунки». Заархивируйте папку
«Рабочая» (имя архива – ваша фамилия) и переместите архив на
диск G:/ в папку с номером вашей группы.
Задание 14. Связывание и консолидация данных
Связывание – это использование ссылок на ячейки из внешних рабочих книг для получения из них данных для своего рабочего листа.
Консолидация – это объединение или накопление информации из нескольких рабочих листов, которые могут находиться в
разных рабочих книгах.
1. Очистите диск H:\. Создайте на диске H:\ папку «Рабочая». Скопируйте в нее архив «G:\Задание14\Связ_конс.rar» и распакуйте архив в папку «Рабочая».
2. Создание связанных между собой таблиц.
Создайте две таблицы, содержащие сведения о ценах на
программные продукты, по образцу, приведенному на рис. 10.1
(файл «Рис10__1.2.rtf»). Для каждого месяца на отдельном листе
книги «ЗД_10_1.xls» создается собственная таблица с названием
«Прайс-лист (Месяц)», где месяца – январь, февраль.
2.1. При создании таблиц организуйте связь между таблицами «Прайс-лист (Январь)» и таблицами «Прайс-лист (Февраль)»,
для чего скопируйте диапазон ячеек А3:В13 январской таблицы
58
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
цен в буфер, перейдите в таблицу «Прайс-лист (Февраль)» и воспользуйтесь режимом Правка/Специальная вставка/Вставить
связь. Обратите внимание, что при копировании связь не создается.
2.2. Переменную часть таблиц (столбец «Цена») отредактируйте согласно данным, приведенным на рис. 10.1. Переименуйте
листы, дав им соответствующие имена («Январь», «Февраль»).
2.3. Просмотрите, как выглядят ссылки в строке формул при
активизации связанных ячеек в таблице января и февраля: измените содержимое ячейки А7 в январской таблице, просмотрите, как
изменится соответствующая ячейка в февральской таблице. Попытайтесь изменить текст в ячейке А7 февральской таблицы, просмотрите сообщения и сделайте выводы о направленности установленной связи (в обратную сторону связь не работает).
2.4. Сохраните созданную книгу с двумя листами под именем «ЗД_10_1.xls».
2.5. Сохраните копию книги под именем «ЗД_10_2.xls».
применяя ссылку на таблицу «Прайс_лист (Февраль)» книги
«ЗД_10_1.xls». Скопируйте формулу в ячейки D5:D13. Запишите в
ячейку D14 формулу, выполняющую суммирование по столбцу
«Итого» (ячейки D4:D13).
3.5. На новом листе «Сумм_доход» (книги ЗД_10_2.xls) в
ячейке А3 запишите «Итого», а в ячейке В3 – «объемную» формулу
подсчета общей суммы дохода: =СУММ(Отгр_янв:Отгр_фев!D14).
Сравнить полученный результат с приведенным (344 200).
3.6. Сохраните книгу «ЗД_10_2.xls».
3.1. В книге «ЗД_10_2.xls» на новых листах «Отгр_янв» и
«Отгр_фев» создайте таблицу отгрузки, по образцу, приведенному
на рис.10.2 (файл «Рис10_1.2.rtf»), скопировав первые два столбца
с соответствующего прайс-листа, а столбец «Количество» заполните в соответствии с рис. 10.2. Удалите лист «Февраль» в книге
«ЗД_10_2.xls».
3.2. В ячейке D4 листа «Отгр_янв» запишите формулу произведения количества продаж на цену соответствующего товара,
применяя ссылку на таблицу «Прайс_лист (Январь)» книги
ЗД_10_2.xls.
Скопируйте формулу в ячейки D5:D13.
Запишите в ячейку D14 формулу, выполняющую суммирование по столбцу «Итого» (ячейки D4:D13).
3.3. Установите курсор в ячейку D14. Отобразите и просмотрите влияющие ячейки для ячейки D14 (меню Сервис/Зависимости). Аналогично просмотрите влияющие ячейки для ячейки
D4.
3.4. В ячейке D4 листа «Отгр_фев» запишите формулу произведения количества продаж на цену соответствующего товара,
4. Знакомство с механизмом консолидации данных в одной книге.
4.1. Откройте файл «ЗД_11_1.xls». Подсчитайте сумму объема поставок в каждом месяце. Сравните полученные результаты с
приведенными на рис.11.1 в файле «Рисунки.rtf».
4.2. Вставьте новый лист, дав ему имя «КД_сумм». Скопируйте в него заголовок таблицы с листа «Янв.».
Проведите консолидацию (с суммированием) данных по
всем трем месяцам таким образом:
• установите указатель активной ячейки в первую свободную ячейку (А3);
• вызовите диалоговое окно «Консолидация» (меню Данные/
Консолидация);
• в поле «Функция» выберите функцию вычисления суммы;
• в поле «Ссылка» укажите первый необходимый адрес, а
именно: перейдите на лист «Янв» и обведите курсором область
А3:В11, нажмите «Добавить», и этот адрес появится в окне «Список диапазонов». Аналогично укажите адреса для диапазонов на
листах «Фев» и «Мар». Неверно набранный адрес можно удалить,
выделив адрес и нажав «Удалить»;
• укажите флажком, что в качестве имен (названий строк)
будут выбираться данные из левого столбца (А) консолидируемой
области;
• укажите флажком на необходимость создания динамической связи с исходными данными, иначе в итоговой таблице данные не будут меняться при изменении исходных данных;
• нажмите «ОК»; сравните полученные результаты с приведенными на рис. 11.2 в файле «Рисунки».
59
60
3. Использование ссылок на ячейки из разных рабочих
книг.
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
4.3. Просмотрите созданную структуру, последовательно
показывая или скрывая уровни этой структуры. Откройте второй
(внутренний) уровень для поставщиков из Санкт-Петербурга и
Череповца. Сравните полученный результат с представленным на
рис. 11.3 в файле «Рисунки».
4.4. Пользуясь меню Сервис/Зависимости, проследите влияющие ячейки для ячеек С7, С10, С38. Убедитесь в правильности полученных результатов.
4.5. Меняя данные в ячейках листов «Янв», «Фев», «Мар»,
проследить за автоматическим пересчетом общей итоговой суммы
(ячейка С38) и частичных сумм в ячейках С10, С26 и т. д. Восстановите измененные данные.
4.6. Аналогично на новом листе «КД_срзн» выполните консолидацию данных из трех таблиц за январь, февраль и март, но
задав в качестве обработки данных вычисление среднего значения
консолидируемых данных (диапазон выделения А3:В11). Сравните полученный результат с представленным на рис. 11.4 в файле
«Рисунки».
4.7. Сохраните созданную книгу с пятью листами под именем «ЗД_11_1.xls!».
5. Знакомство с механизмом консолидации данных из разных книг.
5.1. Откройте книгу «ЗД_11_2.xls» (лист «2кварт»), содержащую данные за второй квартал. Подсчитайте сумму поставок в
квартале. Сравните полученный результат с приведенным на
рис. 11.5 в файле «Рисунки». Cохраните изменения. Закройте книгу «ЗД_11_2.xls».
5.2. На новом листе книги «ЗД_11_1. Xls» (назовите лист
«КД_2кн», таблицу – «Поставка товаров из 2-х книг») выполните
консолидацию по сумме, включив в итог данные за первый квартал (с января по март) из книги «ЗД_11_1.xls» (аналогично предыдущему заданию) и за второй квартал из закрытой книги
«ЗД_11_2.xls». Обратите внимание на структуру ссылки при задании области консолидации из закрытой книги, а именно:
• нажмите кнопку «Обзор» и выберите файл «ЗД_11_2. Xls» –
в поле «Ссылка» появится ссылка на выбранную книгу;
61
• установите курсор в поле «Ссылка», обозначив тем самым,
что ввод ссылки не закончен;
• закончите ввод ссылки, обведя необходимую (А3:В11) или
большую область в открытой книге («ЗД_11_1.xls»), указав тем
самым, что такая же область нас интересует и в закрытой книге;
• нажмите «Добавить», и этот адрес появится в окне «Список диапазонов».
5.3. Проверьте, правильно ли проведена консолидация (сумма должна быть 13 621). Обратите внимание, что при указании в
ссылке на закрытую книгу, которая содержит несколько листов,
консолидация будет проведена с первым слева листом. Если необходимо провести консолидацию с другим листом, то переставьте
листы или укажите название необходимого листа в поле «Ссылка»
после имени книги. Сохраните книгу «ЗД_11_1. Xls».
6. Знакомство с механизмом консолидации данных в случае некоторого отличия информации на разных рабочих листах, а также в случае с разными способами размещения информации на разных рабочих листах.
6.1. Откройте книгу «ЗД_11_2. Xls» (лист «Апр»), посчитайте сумму поставок, обратите внимание на порядок записи городов
и их количество.
6.2. На новом листе книги «ЗД_11_1. Xls» (назовите лист
«КД_4мес», таблицу – «Поставки за 4 месяца») выполните консолидацию по сумме, включив в итог данные за первый квартал (с
января по март) из книги «ЗД_11_1. Xls» и данные за апрель из
книги «ЗД_11_2. Xls» (лист «Апр»). Обратите особое внимание:
• в диалоговом окне «Консолидация» необходимо установить флажок не только в поле «значения левого столбца», но и в
поле «Подписи верхней строки» – в результате MS Excel будет
подбирать данные по заголовкам;
• в поле «Ссылка», показывая курсором область данных, необходимо охватить и заголовки столбцов.
6.3. Проверьте, правильно ли проведена консолидация (сумма – 9 376 руб.), сохраните книгу «ЗД_11_1. Xls».
7. Заархивируйте папку «Рабочая». Удалив все файлы кроме
«*.xls» (имя архива – ваша фамилия), переместите архив на диск
G:/ в папку с номером вашей группы.
62
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
БИБЛИОГРАФИЧЕСКИЙ СПИСОК
1. Беленький Ю.М., Власенко С.Ю. Microsoft Word 2000 / Ю.М. Беленький, С.Ю. Власенко. СПб.: БХВ – Cанкт-Петербург, 1999. 992 с.
2. Богумирский Б. Windows 98: справ. / Б. Богумирский. СПб.: Питер, 1999. 448 с.
3. Бойс Дж. и др. Расширенное техническое руководство по
Windows NT Workstation 4.0: в 2 кн. / Дж. Бойс. М.: СК Пресс, 1998. Кн. 1.
480 с.
4. Информатика. Базовый курс / С.В. Симонович и др. СПб.: Питер, 2001. 640 с.
5. Информатика: учеб. / под ред. Н.В. Макаровой. М.: Финансы и
статистика, 1997. 768 с.
6. Компьютерные системы и сети: учеб. пособие / под ред. В.П. Косарева и Л.В. Еремина. М.: Финансы и статистика, 1999.
7. Компьютерные технологии обработки информации / под ред.
С.В. Назарова. М.: Финансы и статистика, 1995.
8. Кэмпбелл М. WORD / М. Кэмпбелл: пер. с англ. М.: Бином,
1996. 432 с.
9. Лабораторный практикум по информатике: учеб. пособие для
вузов / В.С. Микшина, Г.А. Еремеева, Н.Б. Назина и др.; под ред.
В.А. Острейковского. М.: Высш. шк., 2003. 376 с.
10. Леонтьев Б. Операционная система Microsoft Windows 98 для
начинающих и не только / Б. Леонтьев. М.: Оверлей, 1998. 448 с.
11. Могилев А.В., Пак Н.И., Хеннер Е.К. Информатика: учеб. пособие для студентов / А.В. Могилев, Н.И. Пак, Е.К. Хеннер / под ред.
Е.К. Хеннера. М.: Академия, 1999.
12. Мicrosoft Excel 97: справ. СПб: Питер Ком, 1999. 320 с.
13. Нортон П., Менсфилд Р., Мюллер Дж. Руководство Питера
Нортона: Microsoft Windows 2000 Professional / П. Нортон, Р. Менсфилд,
Дж. Мюллер: пер. с англ. М.: Изд.-торг. дом «Русская редакция»;
ООО «Логрус. РУ», 2000. Кн. 1. 480 с.
14. Острейковский В.А. Информатика: учеб. для вузов / В.А. Острейковский. М.: Высш. шк., 1999. 267 с.
15. Уокенбах Дж. Мicrosoft Excel 2000. Библия пользователя: учеб.
пособие / Дж. Уокенбах. М., 2001. 873 с.
16. Фигурнов В.Э. IBM PC для пользователя / В.Э. Фигурнов. изд. 5-е,
испр. и доп. М.: Финансы и статистика; НПО «Информатика и компьютеры», 1994. 368 с.
17. Экономическая информатика и вычислительная техника: учеб. /
Л.В. Еремин, А.Ю. Королев, В.П. Косарев и др. М: Финансы и статистика, 1996. 328 с.
63
Учебное издание
Составители:
Гусева Галина Степановна, Ищенко Марьяна Валерьевна,
Федорченко Татьяна Витальевна, Шевченко Наталья Васильевна
ЭКОНОМИЧЕСКАЯ ИНФОРМАТИКА
Учебно-методическое пособие
(для студентов экономического факультета)
Часть 2
Электронные таблицы Microsoft Excel
и их использование для экономических расчетов
Технический редактор Н.В. Москвичёва
Редактор Е.С. Радионова
Подписано в печать 23.05.05. Формат бумаги 60х84 1/16.
Печ. л. 4,2. Уч.-изд. л. 3,6. Тираж 200 экз. Заказ 230.
Издательство Омского государственного университета
644077, г. Омск-77, пр. Мира, 55а, госуниверситет
64
Документ
Категория
Информатика и программирование
Просмотров
304
Размер файла
629 Кб
Теги
информатика, экономическая, 922
1/--страниц
Пожаловаться на содержимое документа