Экономическая информатика. Ч. 2. Электронные таблицы Microsoft Excel и их использование для экономических расчетов

32 Pages·2005·0.614 MB·Russian

by  Г.С. Гусева, М.В. Ищенко, Т.В. Федорченко, Н.В. Шевченко

Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Федеральное агентство по образованию УДК 519.72
ББК 32.81
Омский государственный университет им. Ф.М. Достоевского
Э 400

Рекомендовано к изданию редакционно-издательским советом ОмГУ

Рецензенты:

зав. кафедрой математического моделирования ОмГУ,
д-р физ.-мат. наук, проф. Н.В. Перцев; ст. преподаватель кафедры
экономики и социологии труда К.И. Богомазов

Э 400 Экономическая информатика: Учеб.-метод. пособие
(для студентов экономического факультета): в 2 ч. / Сост.:
Г.С. Гусева, М.В. Ищенко, Т.В. Федорченко, Н.В. Шевчен-
ЭКОНОМИЧЕСКАЯ ИНФОРМАТИКА ко. – Омск: Изд-во ОмГУ, 2005. – Ч. 2. Электронные табли-
цы Microsoft Excel и их использование для экономических

расчетов. – 64 с.
Учебно-методическое пособие
ISBN 5-7779-0594-3
(для студентов экономического факультета)
Разработано для закрепления теоретических знаний по дис-

циплине «Экономическая информатика» и решения возникающих
Часть 2
на практике задач. Включает комплекс практических заданий, на-
Электронные таблицы Microsoft Excel правленных на формирование у студентов навыков использования
и их использование для экономических расчетов электронных таблиц Microsoft Excel в экономике.
Пособие рекомендуется использовать на практических заня-
тиях, в самостоятельной работе, а также для расчетов в курсовых и
дипломных работах и т. п.
Для студентов экономического факультета очной формы обу-

чения.

УДК 519.72
ББК 32.81

Изд-во Омск
ISBN 5-7779-0594-3 © Омский госуниверситет, 2005
ОмГУ 2005
2
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Содержание ПРЕДИСЛОВИЕ

Предисловие...................................................................................................4 Предлагаемое учебно-методическое пособие, охватывающее
I. Форматирование рабочих листов.............................................................5 основные разделы дисциплины «Экономическая информатика»,
составлено в соответствии с «Государственными образовательными
Занятия 1, 2. Знакомство с табличным процессором MS Excel............5
стандартами высшего профессионального образования» для эконо-
Занятие 3. Форматирование рабочих листов.......................................11
мических специальностей: 060100 «Экономическая теория», 060200
Занятие 4. Способы адресации табличного процессора MS Excel.....14
«Экономика труда», 060400 «Финансы и кредит», 060500 «Бухгал-
II. Создание и использование формул.......................................................18
терский учет, анализ и аудит», 061000 «Государственное и муници-
Занятие 5. Ввод формул в таблицы с использованием
пальное управление», 061100 «Менеджмент организаций», 062100
абсолютной и относительной адресации.............................................18 «Управление персоналом», 351200 «Налоги и налогообложение».
Занятие 6. Знакомство с различными возможностями Основная цель пособия – научиться обрабатывать таблично
организации вычислений в таблицах. Формулы массива. организованную информацию, проводить экономические расчеты
Межстраничные ссылки........................................................................20 и представлять результаты обработки в виде графиков и диаграмм.
III. Использование функций табличного процесса MS Excel...................25 Пособие, состоящее из 6 частей, разбитых на занятия по раз-
личным темам, посвящено изучению способов адресации ячеек, вы-
Занятие 7. Функции категорий – математические,
числениям в MS Excel, сортировке и фильтрации данных, исполь-
статистические, массивы и даты..........................................................25
зованию функций, работе с диаграммами и сводными таблицами.
Занятие 8. Функции категорий – текстовые, логические,
Исходным материалом для реализации заданий является
финансовые............................................................................................29
электронная база данных, доступ к которой имеют все студенты
Занятие 9. Самостоятельная работа по использованию
экономического факультета ОмГУ.
функций..................................................................................................34 По своей структуре каждое занятие состоит из нескольких
IV. Графическое представление табличных данных................................38 частей. Вначале представлено краткое описание теоретических
Занятие 10. Построение и редактирование диаграмм.........................38 основ и определение основных терминов по изучаемой теме. За-
V. Обработка данных организованных в списки. Сортировка. тем предложены практические задания по закреплению теорети-
Фильтрация..................................................................................................43 ческих знаний, а также даны рекомендации по выполнению зада-
ний, причем может быть предложено несколько вариантов, или
Занятие 11. Знакомство с методами обработки данных,
способов, решения.
организованных в списки, сортировкой, редактированием,
При выполнении практических заданий на занятии по эко-
просмотром, поиском и извлечением данных по различным
номической информатике в компьютерных классах экономическо-
критериям...............................................................................................43
го факультета ОмГУ рабочие файлы (архивы) выставляются пре-
Занятие 12. Фильтрация табличных данных, организованных
подавателем на сетевой диск G:\ и студенты работают в последо-
в списки..................................................................................................48 вательности, указанной в учебно-методическом пособии.
VI. Создание и обработка сводных таблиц. Консолидация При выполнении работ самостоятельно используйте рабочие
данных..........................................................................................................54 файлы (архивы) с диска S (For Study)/Информатика/Задания по
Занятие 13. Сводные таблицы..............................................................54 практике, копируя их на локальный диск Н:\, если работаете в
Библиографический список........................................................................63 компьютерных классах, и копируя на диск А:\, если работаете не в
компьютерных классах экономического факультета ОмГУ.
3 4
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
I. ФОРМАТИРОВАНИЕ РАБОЧИХ ЛИСТОВ Редактирование данных: выделить ячейку, нажать F2 (ре-
жим редактирования), внести исправления, нажать клавишу ввода.
Занятия 1, 2. Знакомство с табличным процессором Excel При вводе данных научитесь перемещаться в таблице че-
тырьмя способами: с помощью манипулятора «мышь», с исполь-
1. Создайте на диске H:\ папку «Рабочая» и скопируйте в нее
зованием клавиш со стрелками, с помощью поля имени, с помо-
архив «G:/Задание1&2/Таблицы.rar», распакуйте архив в папку
щью пункта меню Правка/Перейти (Ctrl + G).
«Рабочая».
Запустите табличный процессор MS Excel (Пуск/ MS Excel). 5. Сохранение табличного документа, добавление листов
Ознакомьтесь с элементами интерфейса: строка заголовка, в рабочую «Книгу».
верхнее меню, панель инструментов, строка ввода, табличное по- Табличный файл является одно- или многолистовым и по
ле, строка состояния. умолчанию называется «Книга1». Если документ сохраняется
Электронная таблица – совокупность строк и столбцов, впервые, то: Файл/Сохранить как, ввести имя файла, тип файла
столбцы обозначены буквами латинского алфавита, а строки циф- *.XLS и нажать кнопку «Сохранить». Если документ сохраняется
рами. Пересечение строки и столбца называется ячейкой. Каждая не впервые, то: Файл/Сохранить (без ввода имени файла).
ячейка имеет свой адрес, который состоит из буквы столбца и 5.1. Сохраните в папке «Рабочая» под именем «ФЛ1» файл-
цифры строки (например, А5, В7, К4). книгу MS Excel, включающую один лист с таблицей «Таблица 1».
2. Пользуясь манипулятором «мышь», просмотрите назна- 5.2. Введите таблицу, представленную на рис. 2 (файл «Таб-
чение всех элементов управления окном, а именно всплывающие лицы.rtf»), на второй лист первой книги. Добавление второго лис-
подсказки для всех кнопок стандартной панели инструментов и та: курсор мыши в поле «Лист1» – контекстное меню – «Доба-
панели форматирования, всплывающие подсказки для всех эле- вить».
ментов окна: поля имени, строки формул, заголовки столбцов, за- Сохраните файл с двумя таблицами под именем «ФЛ2» в
головки строк, строки состояния, панели ярлычков листов, кнопок папке «Рабочая».
прокрутки ярлычков листов, полосы прокрутки.
6. Работа с листами книги. Создание новой книги на ос-
3. Откройте файл «Таблицы.rtf» и оставьте его развернутым
нове существующей.
примерно на пол-экрана (это образцы тех таблиц, которые вы бу-
6.1. Откройте документ «ФЛ2» (сохраненный в формате
дете вводить в MS Excel).
книги Excel), созданный ранее.
4. Перейдите к окну MS Excel и введите таблицу, представ-
6.2. Введите на третий лист книги таблицу, приведенную на
ленную на рис. 1* (файл «Таблицы.rtf»), на первый лист открытой
рис. 3 (файл «Таблицы.rtf»), и сохраните книгу.
(первой) книги. Движение по табличному полю можно осуществ-
6.3. Откройте новое окно документа для создания новой
лять с помощью мыши, с помощью клавиш управления курсором,
книги (Файл/Создать/Книгу).
с помощью клавиш листания «PgUp», «PgDn», с помощью клавиш
6.4. Скопируйте во вновь открытую книгу первый и третий
«Home», «End», с помощью поля имени, с помощью пункта меню
лист исходной книги «ФЛ2», разместив их соответственно в нача-
Правка/Перейти.
ле книги.
Ввод данных: выделить нужную ячейку, ввести текст, число
Для копирования, а в дальнейшем и для переноса листов
или формулу, нажать клавишу ввода.
таблицы из книги в книгу используйте контекстное меню или
пункт меню Правка/Переместить/скопировать лист, не забудьте
ставить флажок «Создавать копию».
* Рисунки представлены в указанных файлах.
5 6
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
6.5. Расположите рядом окна двух документов: исходного и 8. Перемещение по таблице.
нового (Меню «Окно»/Расположить/Слева направо). Перенесите с Научитесь произвольно перемещаться по таблице, исполь-
помощью мыши второй лист исходного документа в новый доку- зуя для перемещения комбинации управляющих клавиш и соот-
мент и поместите его между первым и вторым листом (используя ветствующие команды меню (См. рис. 3 в файле «Таблицы.rtf»).
левую кнопку мыши).
9. Выделение ячеек и областей.
6.6. Активизируйте окно исходного документа (книги) и
Научитесь выделять произвольные области таблицы (ячей-
проверьте правильность выполненного переноса (в исходной кни-
ку, строки, столбцы, диапазоны ячеек и произвольные комбинации
ге должно остаться два листа – первый и третий).
ячеек) для выполнения последующих операций редактирования
6.7. Скопируйте «Лист2(2)» из новой книги в исходную, раз-
(копирование, перенос, удаление) над выделенными областями,
местив его в самом начале книги – перед первым листом (переме-
пользуясь комбинациями управляющих клавиш и мышью:
щение листов осуществляется с помощью контекстного меню яр-
– выделите всю таблицу (заполненные клетки), пользуясь
лычка листа).
манипулятором «мышь»;
6.8. Сохраните книги в папке «Рабочая», новую под именем
– выделите весь лист;
«ФЛ3», а исходную – «ФЛ4».
– выделите область ячеек от ячейки B6 до верхнего левого
6.9. Активизируйте окно, содержащее книгу «ФЛ4». Пере-
угла рабочего листа (клетка А1);
местите листы в пределах данной книги (контекстное меню) таким
– выделите область ячеек от ячейки B6 до нижнего правого
образом, чтобы листы изменили порядок следования: первый лист
угла рабочего листа (клетка IV65536), используя клавишу F8 (ре-
должен стать последним (третьим), а третий – первым (а если у
жим выделения, убедиться в строке состояния) и поле имени;
вас был порядок следования листов 3, 2, 1, то расположите их в
– выделите несмежные области таблицы (с помощью кнопки
порядке 1, 2, 3).
CTRL). F8 – снято.
6.10. Сохраните результат работы под именем «ФЛ5» в пап-
ке «Рабочая». 10. Создание таблицы по образцу, приведенному на рис. 4
(файл «Таблицы.rtf»).
7. Именование листов книги.
Вычисления в таблицах выполняются с помощью формул.
7.1. Уберите с экрана все окна за исключением окна доку-
Результатом выполнения формулы есть некоторое новое значение,
мента «ФЛ5».
содержащееся в ячейке, где находится формула. Формула начина-
7.2. Дайте всем трем листам документа краткие содержа-
ется со знака равенства «=», при адресации ячеек используется
тельные наименования, раскрывающие содержание таблиц.
латинский алфавит. В формуле могут использоваться арифметиче-
Для именования листов книги используйте контекстное ме-
ские операторы +, –, *, /. Порядок вычислений определяется обыч-
ню или пункт меню Формат/Лист/Переименовать.
ными математическими законами.
7.3. Последовательно удалите из книги «ФЛ5» первый и тре-
10.1. Запишите в клетки Е4, F4, E13, E14 соответствующие
тий листы, пользуясь контекстным меню или пунктом меню
формулы, представленные на рис. 4, используя только латинский
Правка/Удалить лист.
алфавит.
7.4. Сохраните результат в документе «ФЛ6» в папке «Рабо-
10.2. Научитесь просматривать записи формул (в строке
чая».
формул) и результаты вычислений (значения) в ячейках таблицы.
Просматривать записи формул можно не только в строке
формул, но и в самих ячейках после нажатия комбинации клавиш
7 8
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Ctrl+Ё (или сделав соответствующую пометку в пункте меню/ ласть ячеек, начинающуюся с ячейки А1). Назовите ее «Склад 2
Сервис/Параметры/Вид/Параметры окна/Формулы). (Лист 1)».
10.3. Скопируйте формулы в диапазоны Е5:Е10, F5:F10 и 15. Для клеток B6, B7 «Листа1» создайте примечания, пояс-
проследите за модификацией адресов при копировании. няющие, какие именно изделия (размер, сорт, цвет) приведены в
10.4. Сохраните таблицу в книге «ФА1» в папке «Рабочая». таблице (для создания примечаний используйте контекстное ме-
ню). Просмотрите, каким образом отмечаются ячейки, имеющие
11. Модификация созданной таблицы.
примечания.
11.1. Вставьте новый столбец «Коэфф.» между столбцами
Сохраните книгу под именем «ФА3» в папке «Рабочая».
«Цена» и «Стоимость» (с помощью контекстного меню). Заполни-
16. В новой книге создайте таблицу в соответствии с образ-
те его данными (значения 0,5, 0,6, и т. д. до 1,1).
цом (см. рис. 5, «файл Таблицы.rtf»).
11.2. Отредактируйте формулу в столбце «Стоимость» так,
16.1. Введите суммы оплаты и срок задержки (по своему ус-
чтобы она учитывала значения столбца «Коэфф.», например для
мотрению).
четвертой строки C4*D4*E4 (редактирование проводите в строке
16.2. Введите формулы для начисления пеней (0,01% от на-
формул).
численной суммы за каждый просроченный день), итоговых сумм
11.3. Перенесите две первые значащие строки таблицы и конечной суммы оплаты.
(строки 4-го и 5-го листа) в конец списка изделий. 16.3. Переименуйте текущий рабочий лист, назвав его «Оп-
11.4. Вставьте перед 8-й строкой листа («Доски») две новые лата».
строки и заполните их («Гипсокартон-50-48», «Панели-43-32»). Сохраните созданный файл под именем «Оплата.xls» в пап-
11.5. Сохраните таблицу в книге «ФА2» в папке «Рабочая». ке «Рабочая».
12. Сформируйте из исходной таблицы «ФА2.xls» новую 16.4. Добавьте в файл «Оплата.xls» второй лист. Скопируйте
таблицу, состоящую из столбцов «Номер», «Наименование» и на этот лист вашу таблицу, поместив ее в диапазоне ячеек В3:F11.
«Цена», пользуясь командами «Очистка» и «Удаление». Запишите Измените количество дней, на которое задержана оплата, на дру-
различие в результатах работы этих команд. Поместите новую гое число и скорректируйте формулы в соответствие с этим изме-
таблицу, начиная с клетки С3, и сохранить ее в книге «ФА3». нением. Сохраните изменения.
13. В таблице «ФА3.xls» сформируйте столбец номеров, 17. В книгу «Оплата.xls» добавьте 3-й лист и создайте табли-
пользуясь автозаполнением, а именно: сначала введите два первых цу (см. рис. 6). Заполните строки 8-ю и 9-ю (введите данные свои и
номера (указав шаг заполнения), выделите введенные номера, за- соседа). В ячейку D4 введите формулу определяющую возраст в
тем подведите курсор мыши в правый нижний угол выделенной годах, а в Е4 – пересчитайте возраст в днях (считая, что в году 365
области, и, когда курсор примет форму черного крестика, протя- дней).
ните его вниз, не отпуская левую кнопку мыши. Скопируйте формулы из ячеек D4 и Е4 вниз по столбцу
14. Поместите (перенесите) таблицу в область ячеек, начи- (щелкните в ячейку с формулой и, поместив курсор мыши на чер-
нающуюся с ячейки С20, и назовите ее «Склад 2». ный квадратик с рамки, протяните мышью вниз по столбцу при
14.1. Скопируйте таблицу «Склад 2» на 2-й лист рабочей кни- нажатой левой клавише мыши).
ги в область ячеек, начинающуюся с ячейки В6. Назовите ее 18. На 1-й лист книги «Оплата.xls» в ячейку В12 введите
«Склад 2 (Лист 2)». свою фамилию и номер группы (выделите цветом).
14.2. Скопируйте таблицу «Склад 2 (Лист 2)» на первый 19. Удалите файл «Таблицы.rtf». Заархивируйте папку «Ра-
лист рабочей книги на место исходной таблицы «Склад» (в об- бочая» (имя архива – ваша фамилия) и переместите архив на диск
G:\ в папку с номером вашей группы.
9 1 0
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Занятие 3. Форматирование рабочих листов 2.2. Шрифтовое оформление текста.
Воспользовавшись режимом форматирования ячеек (Фор-
1. Создайте на диске H:\ папку «Рабочая». Скопируйте в нее
мат/Ячейки/Шрифт), оформите тексты в таблице 2-го листа так,
архив «G:/Задание3/Таблицы.rar», распакуйте архив в папку «Ра-
как представлено на рис. 3.
бочая».
В таблице на рис. 3 использованы следующие варианты
Форматирование – это комплекс действий, направленных на
шрифтового оформления текста: жирный, подчеркнутый, курсив,
улучшение восприятия табличной информации.
жирный курсив, перечеркнутый, а также верхний и нижний ин-
Формат устанавливается для выделенной области или ячей-
дексы. Для выделения данных в таблице используйте различные
ки и снимается командой Правка/Очистить/Форматы. Формати-
варианты оформления из меню Формат/Ячейка/Вид («Заливка» и
рование включает в себя следующие элементы:
«Узор») и меню Формат/Ячейка/Граница.
• установку формата чисел;
3. Форматирование числовых данных.
• установку формата строк и столбцов;
Числовые данные, которые вы вводите, как правило, не от-
• выбор шрифтов;
форматированы. Другими словами, они состоят из последователь-
• рисование рамок;
ности цифр. На панели «Форматирование» находится несколько
• заполнение клеток цветом и узором;
кнопок, позволяющих применить самые распространенные форма-
• выравнивание данных;
ты. Если возникает необходимость использовать другие варианты
• защиту данных.
форматирования чисел, то используют команду Формат/Ячейки/
2. Форматирование текста. Число, либо «Формат ячеек» в «Контекстном меню».
2.1. Выравнивание текста. Форматы дат содержат несколько вариантов представления
Создайте на 1-м рабочем листе таблицу по приведенному введенной даты (например, «16.4», «16.04.97», «16 апр», «16 апр
образцу (рис. 1) и сохраните ее в файле «ФА1.xls» для дальнейше- 97» и т. д.). Базовой единицей является день. Каждой дате соот-
го использования. ветствует свое сериальное число, показывающее, на сколько дней
Скопируйте на 2-й рабочий лист содержимое 1-го рабочего введенная дата отстоит от 1 января 1900 г.
листа. 3.1. На третьем рабочем листе создайте таблицу, приведен-
Отформатируйте тексты таблицы по образцу, приведенному ную на рис. 4. Заполните все столбцы, кроме столбца F и ячеек
на рис. 2. На этом примере научитесь выравнивать текст всеми дос- D15 и E15. Отформатируйте числовые данные с использованием
тупными способами. Перед выполнением этого пункта установите числовых форматов, как показано на рисунке. В ячейки D15 и E15
для всего рабочего листа стандартную высоту строк (для шрифта запишите формулы, вычисляющие сумму значений в соответст-
размером 10 пт стандартная высота строки составляет 12,75 пт). вующем столбце. Столбец F заполните формулами, вычисляющи-
Включите режим автоматической установки ширины столб- ми отношение значений столбца E («Продано шт.») к общей сум-
цов (Формат/Столбец). Просмотрите, как изменится внешний вид ме, записанной в клетке E15.
таблицы. Подстройте параметры таблицы (ширину столбцов) так, Метод курсорного копирования. При вводе формулы мож-
чтобы внешний вид таблицы соответствовал рис. 2. Для выравни- но пользоваться следующим методом: при нажатии знака «=»
вания используйте пункт меню Формат/Ячейки/Выравнивание. осуществляется переход в режим ввода формулы, аргументы фор-
мул указываются курсором, т. е. нужно выбрать курсором ячейку
и нажать левую кнопку мыши. При этом в формулу автоматически
1 1 1 2
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
запишется полный адрес данной ячейки (с указанием имен листов сколько строк можно применить вкладку «выравнивание» команды
и книг, если ячейка расположена не на текущем листе). Формат/Ячейки и установить флажок. Переносить по словам.
3.2. Очистите формат ячеек B4:B5 (Правка/Очистить/Фор- 6. На первом листе книги «Форм. Текста.xls» создайте об-
маты) и убедитесь, что Excel, после снятия форматирования дат, разцы таблиц, приведенных на рис. 5.
интерпретирует даты как числа 35 802 и 35 807, которые являются Для рисования рамок используйте команду Формат/Ячейки/
порядковыми номерами введеных дат в памяти Excel, начиная с 1 Граница.
января 1900 г. 7. Удалите архив и файл «Таблицы.rtf». Заархивируйте пап-
3.3. Отформатируйте ячейку F4, как показано на рис. 4, и ку «Рабочая» (имя архива – ваша фамилия) и переместите архив
скопируйте созданный формат в диапазон F5:F8. на диск G:/ в папку с номером вашей группы.
3.4. Отформатируйте ячейку F9, как показано на рис. 4, и
скопируйте созданный формат в диапазон F10:F14. Занятие 4. Способы адресации табличного процессора
Excel
4. Защита данных.

При работе над одним документом совместно с другими При копировании и перемещение формул происходит пре-
пользователями можно защитить от изменений ячейки на рабочем образование содержащихся в них ссылок на другие ячейки. В од-
листе. Если на листе должны быть защищены не все ячейки, то них случаях адреса должны оставаться неизменными, в других –
перед выполнением команды Сервис/Защита/Защитить лист нуж- адреса должны быть пересчитаны с учетом относительного изме-
но во вкладке «Защита» диалогового окна «Формат ячеек» снять нения их местоположения. Для этого в MS Excel поддерживается
флажок «Защищаемая ячейка» для предварительно выделенных, система абсолютных и относительных ссылок. Когда вы исполь-
не нуждающихся в защите ячеек. зуете относительную адресацию, ссылки в формулах автоматически
Защитите заголовки строк и столбцов таблицы, приведенной изменяются при копировании формулы в другое место. При копи-
на рис. 4, и оставьте возможным изменение числовых данных таб- ровании формул относительные ссылки изменяются на размер пе-
лицы (Формат/Ячейки/Защита, установите флажок «Защищае- ремещения. Чтобы ссылки в формуле не изменялись при копирова-
мые ячейки» на тех ячейках, которые хотите защитить, а на диапа- нии формулы в другую ячейку, используйте абсолютную ссылку.
зоне данных этот флажок должен быть снят). Затем Сервис/ За-
1. Создайте на диске H:\ папку «Рабочая». Скопируйте в нее
щита/Защитить лист (в отношении содержимого).
архив «G:/Задание4/Таблицы.rar», распакуйте архив в папку «Ра-
Сохраните результат работы.
бочая».
5. Создайте в папке «Рабочая» новую книгу «Форм. Тек-
2. Создайте таблицу, приведенную на рис. 1 файл «Табли-
ста.xls», состоящую из 2 листов. Введите в одну ячейку A1 листа 2
цы.rtf»), заполнив только ячейки A3:E7 в соответствии с образцом,
представленное в рамке предложение и отформатируйте его сле-
приведенным на рис. 1.
дующим образом:
3. Запишите формулы, позволяющие выполнить следующие
ЭЛЕКТРОННЫЙ ПРОЦЕССОР
вычисления (при этом необходимо помнить, что формула всегда
EXCEL
начинается знаком равенства (=), ввод формулы заканчивается
ПРЕДНАЗНАЧЕН ДЛЯ ОБРАБОТКИ ДАННЫХ, представ-
нажатием клавиши «Enter», а операнды в формулу можно встав-
ленных в ТАБЛИЧНОЙ ФОРМЕ.
лять с помощью метода курсорного копирования: аргументы фор-
Для добавления новой строки в ячейку используйте комби- мул указываем курсором, т. е. выбираем курсором ячейку и нажи-
нация клавиш Alt + Enter. Для расположения текста в ячейке в не- маем левую кнопку мыши. При этом в формулу автоматически
1 3 1 4
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
запишется полный адрес данной ячейки (с указанием имен листов Для заполнения ячеек постоянными значениями нужно про-
и книг, если ячейка расположена не на текущем листе)): тянуть маркер автозаполнения по диапазону ячеек. Это же можно
3.1. Сумму всех элементов первой строки (разместить в получить, выполнив протяжку правой кнопкой мыши и выбрав
клетке Н3). пункт меню «Заполнить значения».
3.2. Сумму всех элементов второй строки (разместить в Абсолютные и относительные адреса ячеек. Иногда тре-
клетке Н4). буется, чтобы при копировании формул из ячейки в ячейку адреса
3.3. Для всех оставшихся строк записать аналогичные форму- не модифицировались. Для этого в формуле необходимо использо-
лы, разместив их в соответствующих строках столбца Н. Для записи вать абсолютные адреса ячеек. Чтобы указать, что адрес абсолют-
формул в 3-й и последующих строках можно пользоваться коман- ный, пользуйтесь символом $. Например, $B$4 означает, что при
дой копирования блока клеток. Обратить внимание, как модифици- копировании координаты столбца и строки модифицироваться не
руются адреса клеток при выполнении команды копирования. будут, запись B$4 означает, что модифицироваться будет только
3.4. Сумму всех элементов первого столбца разместите в координата столбца и т. д. Изменить тип ссылки можно последо-
клетке А10. вательным нажатием клавиши F4 при установке курсора перед
3.5. Сумму всех элементов второго столбца разместите в ссылкой, подвергающейся изменению.
клетке В10. 5. Для вычисления суммы элементов блока клеток А20:С21
3.6. Для всех оставшихся столбцов запишите аналогичные запишите в ячейках А25, С25, Е25 и G25 следующие формулы:
формулы, разместив их в соответствующих ячейках строки 10. • в клетке А25 адреса должны быть записаны как относи-
При записи формул пользоваться командой копирования. тельные;
3.7. В клетке Н11 запишите формулу для вычисления суммы • в клетке С25 адреса должны быть записаны как абсолют-
элементов главной диагонали данной матрицы, которая будет ные;
включать только ячейки с числовыми данными. • в клетке Е25 адреса должны быть записаны с абсолютным
3.8. В клетке Н12 запишите формулу для вычисления суммы указанием строки и относительным указанием столбца;
элементов главной диагонали матрицы, которая будет включать
• в клетке G25 адреса клеток должны быть записаны с абсо-
все ячейки диагонали (в том числе и ячейку C5), не содержащие
лютным указанием столбца и относительным указанием строки.
данных. Сравнить результаты в ячейках Н11 и Н12.
6. В ячейки H20, H21, H22 и H23 поместите аналогичные
3.9. В ячейках А11, А12, А13 разместить формулы, позво-
формулы.
ляющие выполнить следующие вычисления:
7. Перейдя в режим отображения результатов, просмотрите
(cid:190) сумму элементов матрицы, расположенных в блоке кле-
вычисленные значения и проверьте их правильность.
ток от А3 до В4,
Все значения сумм должны быть равны 6.
(cid:190) сумму элементов матрицы, расположенных в блоке кле-
8. Скопируйте формулы из строки 25 в строку 27.
ток от D6 до Е7,
Проверьте, как ведут себя относительные и абсолютные
(cid:190) разность сумм элементов, расположенных в блоках
адреса клеток. Просмотрите результаты вычислений по формулам
А3:В4 и D6:Е7.
строк 25 и 27. Проанализируйте результаты.
Сравните полученные результаты с результатами, приве-
9. Скопируйте формулы из столбца H в столбец J.
денными на рис. 1.
Проверьте, как ведут себя относительные и абсолютные ад-
4. Пользуясь операциями копирования, создайте, начиная с
реса клеток. Просмотрите результаты вычислений по формулам
ячейки А20, таблицу, приведенную на рис. 2 («Таблицы.rtf»).
столбцов H и J. Проанализируйте результаты.
1 5 1 6
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
В электронных таблицах можно работать как с отдельными II. СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ ФОРМУЛ
таблицами, так и с группой ячеек, которые образуют блок. В каче-
стве блока может рассматриваться строка или ее часть, столбец Занятие 5. Ввод формул в таблицы с использованием аб-
или часть столбца, а также прямоугольник из нескольких строк и солютной и относительной адресации
столбцов. Адрес блока ячеек задается указанием ссылок первой и
1. Создайте на диске H:\ папку «Рабочая». Скопируйте в нее
последней его ячеек, между которыми ставится разделительный
архив «G:/Задание5/Таблицы.rar», распакуйте архив в папку «Ра-
символ – двоеточие.
бочая».
10. Определите имена для областей A20:C21; A22:C23;
2. Откройте файл «Отчет».xls». Составьте отчет о начисле-
D20:F21; D22:F23 соответственно как One, Two, Three, Four
нии заработной платы сотрудникам АО «Рога и копыта».
(Вставка/Имя/Присвоить).
Исходные данные: должностной оклад, количество отрабо-
11. Вычислите суммы элементов заданных блоков, исполь-
танных дней за год, процент подоходного налога.
зуя функцию =Сумм (имя диапазона), расположенную на стан-
Найти:
дартной «Панели инструментов» и команду Вставка/Имя, выбрав
• в графе «Начислено» сумму, начисленную каждому со-
имя нужного диапазона в качестве аргумента, функции «Сумма».
труднику за год;
Разместите результаты вычислений в ячейках B30:B33.
12. Используя имена диапазонов, перейдите в области Three • в графе «Налог» (12 % от «Начислено») сумму подоходно-
и Four (Правка/Перейти) и очистите их содержимое. го налога;
13. Сохраните результат работы под именем «ФЛ1» в папке • в графе «К выдаче» сумму, выдаваемую каждому сотруд-
«Рабочая». нику;
14. Откройте файл «Показания счетчика.xls». Определите сум- • в графе «% от общей суммы» % зарплаты работника от
му, которую придется заплатить за потребленную электроэнергию. суммарной зарплаты всего коллектива;
Выполните расчет, введя свои исходные данные (в столбцы «Дата» • суммарную величину выплат всех сотрудников.
и «Показания счетчика»). 3. Введите в первую клетку столбца «Начислено» расчетную
15. Отформатируйте таблицу по образцу (рис. 3 файла «Таб- формулу для вычисления суммы, начисленной первому в списке
лицы»). сотруднику (12 х (оклад) x (число дней) / (расчетное число дней)):
16. На втором листе книги «Показания счетчика.xls» создай- (при вводе расчетного количества дней используйте ссылку на
те таблицу, внеся в нее исходные данные (условия см. ниже). ячейку D16 (абсолютный адрес)). Формат ячеек денежный, с од-
Торговая фирма имеет в своем ассортименте следующий товар: ним знаком после запятой.
телевизоры по цене $300, видеомагнитофоны – $320, муз. центры – $550, 4. Скопируйте формулу в остальные клетки столбца «На-
видеокамеры – $700, видеоплееры – $198, аудиоплееры – $40. В январе
числено».
было продано телевизоров – 10, видеомагнитофонов – 5, муз. центров –
5. Проверьте автоматическое изменение формул в клетках
6, видеокамер – 2, видеоплееров – 7, аудиоплееров – 4.
Используя возможности MS Excel, найдите сумму выручки столбца «Начислено»: относительные адреса клеток в числителе
от продаж в рублях и долларах (курс доллара – 29,2 руб). Лист на- должны измениться, а абсолютный адрес клетки в знаменателе
зовите «Выручка за январь». должен остаться неизменным.
17. Удалите из папки «Рабочая» файл и архив Таблицы. 6. Аналогично создайте и скопируйте формулы для вычис-
18. Заархивируйте папку «Рабочая» со всеми созданными ления подоходного налога в столбце «Налог» (12 % от «Начисле-
документами (имя архива – ваша фамилия) и переместите архив но») и суммы к выдаче в столбце «К выдаче» («Начислено» минус
на диск G:/ в папку с номером вашей группы. «Налог»).
1 7 1 8
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
7. В ячейке F13 запишите формулу для подсчета суммарной Формат ячеек денежный, рублевый, ноль знаков после запя-
зарплаты всех сотрудников. той.
8. В графе «% от общей суммы» создайте и скопируйте фор- 14. Удалите из папки «Рабочая» файл и архив «Таблицы».
мулу, показывающую какую часть составляет годовая зарплата 15. Заархивируйте папку «Рабочая» со всеми созданными
каждого сотрудника от суммарных выплат (ячейка F13, формат документами (имя архива – ваша фамилия) и переместите архив
ячеек процентный). на диск G:/ в папку с номером вашей группы.
9. После выполнения задания сравните результат с рис. 1
файла «Таблицы». Отформатируйте таблицу по образцу рис. 1. Занятие 6. Знакомство с различными возможностями
10. Откройте файл «Доставка.xls» и запишите формулы в организации вычислений в таблицах. Формулы массива. Меж-
ячейки F3:F8. страничные ссылки
Формула в ячейке F3 вводится с учетом того, что стоимость
1. Создайте на диске H:\ папку «Рабочая». Скопируйте в нее
доставки груза складывается из следующих трех величин (наце-
архив «G:/Задание6/Таблицы.rar», распакуйте архив в папку «Ра-
нок):
бочая».
а) 5% от стоимости груза,
2. Групповое заполнение листов.
б) от расстояния (3 руб./км),
MS Excel позволяет вводить и редактировать данные на не-
в) номера этажа (2,5 руб./этаж).
скольких листах сразу – групповым методом. Пользуясь методом
11. Перенесите лист из книги «Доставка» вторым листом в
группового заполнения листов, создайте на трех листах нового
книгу «Отчет.xls».
документа таблицу, приведенную на рис. 1 (файл «Таблицы»), вве-
12. Сравните результат с рис. 2 файла «Таблицы». Отформа-
дя данные в диапазон В4:F8. Назовите листы «Таб1», «Таб2»,
тируйте таблицу по образцу рис. 2.
«Таб3».
13. На 3-м листе книги «Отчет.xls» рассчитайте зарплату
Для этого активизируйте рабочий лист и выделите диапазон
(суммы к получению для каждой категории сотрудников для двух
для работы. Затем нажмите клавишу «Shift» и щелчком по ярлыч-
филиалов) (условия см. ниже). Результатом должны быть две таб-
кам листков выберите группу соседних листов (клавиша «Ctrl» –
лицы. Лист назовите «Филиалы».
для выбора отдельных листов). В строке заголовка появится над-
На предприятии работники имеют следующие оклады: на-
пись «Группа» – это напоминание о том, что вы находитесь в ре-
чальник отдела – 5 000 руб., инженер 1 кат. – 3 500 руб., инженер
жиме работы с группой листов.
– 3 200 руб., техник – 2 000 руб., лаборант – 1 200 руб. Предпри-
Сохраните книгу под именем «ФА1» в папке «Рабочая».
ятие имеет два филиала – в Средней полосе и в условиях Крайнего
3. Различные приемы заполнения ячеек формулами. Ис-
Севера. Все работники получают надбавку 10 % от оклада за вред-
пользование формул массива.
ный характер работы, 25 % от оклада ежемесячной премии. Со
Массив – это набор ячеек или значений, которые обрабаты-
всех работников удерживают подоходный налог – 20 %, профсо-
ваются как одна группа. Формула массива – формула, в которой
юзный взнос – 3 % и 1 % в пенсионный фонд (% от всего совокуп-
используется один или несколько массивов непосредственно или
ного дохода). Работники филиала, расположенного в Средней по-
как аргумент функции. В MS Excel массивы могут быть одно- или
лосе, имеют 15 %-ный районный коэффициент, работники филиа-
двухмерными. Операции над массивами производятся с помощью
ла, расположенного в районе Крайнего Севера – 70 %-ный район-
формул массивов.
ный коэффициент и 50 % северной надбавки от оклада.
1 9 2 0