Table Of Content

ГОСУДАРСТВЕННЫЙ ИНСТИТУТ УПРАВЛЕНИЯ И СОЦИАЛЬНЫХ ТЕХНОЛОГИЙ БГУ Кафедра управления финансами и недвижимостью Т. В. Борздова ТАБЛИЧНЫЙ ПРОЦЕССОР MICROSOFT EXCEL Учебное пособие В 2 частях Часть 1 Теоретические сведения Минск ГИУСТ БГУ 2010 1 УДК 004.31.(075.8) ББК 32.973.26-04я73 Б82 Рекомендовано кафедрой управления финансами и недвижимостью Государственного института управления и социальных технологий БГУ Автор: кандидат технических наук, доцент Т. В. Борздова Рецензенты: кандидат экономических наук, доцент Е. Г. Кобзик; кандидат физико-математических наук, доцент В. А. Прокашева Борздова, Т. В. Б82 Табличный процессор Microsoft Excel. В 2 ч. Ч. 1. Теоретические сведе- ния : учеб. пособие / Т. В. Борздова. – Минск : ГИУСТ БГУ, 2010. – 104 c. ISBN 978-985-491-033-8. Лабораторный практикум ориентирован на поддержку лабораторных и практиче- ских занятий по курсам «Информационные технологии», «Основы информационных технологий» и «Современные информационные технологии». Содержит лабораторные работы, нацеленные на формирование навыков использования программы Microsoft Excel, а также перечень заданий для самостоятельной работы студентов. УДК 004.31.(075.8) ББК 32.973.26-04я73  Борздова Т. В., 2010 ISBN 978-985-491-033-8 (ч.1)  ГИУСТ БГУ, 2010 ISBN 978-985-491-032-1 2 ОГЛАВЛЕНИЕ Тема 1. Создание, заполнение и радактирование таблиц. Автозаполнение ............ 4 Тема 2. Форматирование таблиц. Простейшие вычисления в таблицах. Автосуммирование .................................................................................................... 10 Тема 3. Таблицы в Excel. Функции и сложные вычисления ........................................ 17 Тема 4. Диаграммы в Excel. Построение и редактирование диаграмм .................. 22 Тема 5. Анализ и распределение данных. Подбор параметра ................................... 37 Тема 6. Массивы в Excel .......................................................................................................... 43 Тема 7. Решение оптимизационных задач ........................................................................ 53 Тема 8. Шаблоны ........................................................................................................................ 67 Тема 9. Финансовые расчеты и составление отчетных ведомостей ........................ 69 Тема 10. Сортировка данных .................................................................................................... 85 Тема 11. Консолидация и фильтрация данных .................................................................. 87 Литература...................................................................................................................................... 101 3 Тема 1. СОЗДАНИЕ, ЗАПОЛНЕНИЕ И РЕДАКТИРОВАНИЕ ТАБЛИЦ. АВТОЗАПОЛНЕНИЕ Основные термины 1. Лист – это массив ячеек, в которых хранятся данные. Листы являются блоками, из которых образуются книги. 2. Ячейка – минимальный элемент листа, образуемый на пересечении строк и столбцов. Ячейки объединяются в таблицы, которые используются для анали- за, хранения и обработки данных. Ячейка имеет адрес, определяющий местопо- ложение ячейки в таблице. Этот адрес задается именем столбца (А, В, С,...,Z, AA, ...) и номером строки. Например, В4. 3. Книга – это файл, в котором хранятся листы. Книга состоит из листов. По умолчанию в каждой книге Excel содержится 3 листа: «Лист 1», «Лист 2» и «Лист 3». При создании нового документа в Excel создается новая книга. Ка- ждая новая книга по умолчанию имеет имя «Книга 1», «Книга 2» и т. д. 4. Окно листа. Лист обычно состоит из 256 столбцов и 65536 строк. На эк- ране видна всегда лишь небольшая часть листа. Точное количество строк и столбцов, видимых на экране, зависит от размера экрана монитора, видеодрай- вера и установленного разрешения. Отображается лишь верхний левый угол все- го листа со строкой заголовков столбцов и столбцом заголовков строк. Общий вид окна программы приведен ниже: 4 Ввод данных В Excel можно вводить следующие основные типы данных:  значения (числа);  текст (например, заголовки таблиц; пояснительная информация);  функции (например, синус или косинус, сумма);  формулы (сочетание значений, функций и текста). Есть два способа ввода данных в ячейку: 1) прямой ввод данных. Для такого ввода следует установить указатель мы- ши на ячейку и выполнить одинарный щелчок по ней (либо переместить указа- тель мыши с помощью клавиатуры). При этом активная ячейка будет обведена толстой границей. После этого можно начать ввод данных; они одновременно будут появляться в ячейке и в строке формул (в строке окна листа, расположен- ной над строкой заголовков столбцов и снабженной символом «f »). После окон- x чания набора следует нажать клавишу Enter, чтобы подтвердить ввод данных, или клавишу Esc, чтобы отменить операцию; 2) ввод данных через строку формул. Вначале необходимо щелкнуть мы- шью на ячейке, в которую следует ввести данные. Затем следует щелкнуть в строке формул и начать набор данных. При этом в строке формул появляют- ся 3 кнопки:  с изображением «Х» – отменяет введенные данные;  с изображением «» – подтверждает (завершает) ввод данных;  с изображением «f » – вызов Мастера функций. x Excel анализирует вводимые данные, чтобы определить их тип. В одну ячей- ку можно ввести до 255 символов текста. Если нужно ввести число как текст, следует поставить перед ним апостроф. При вводе чисел в виде десятичной дро- би разделителем является десятичная запятая. При вводе дат разделителем явля- ется точка или символ «/». Например, 12/03/99. При вводе времени разделителем является «:». Например, 14:25:30. Замечание 1. При вводе даты и времени Excel преобразует это значение в некоторое специальное числовое представление. Отсчет дат ведется с 1 января 1900 года. Любые две последовательные даты интерпретируются как два после- довательных числа. Однако информация в ячейке отображается в стандартном формате даты и времени. Над ними можно производить вычисления так же, как и над любыми числами. Замечание 2. Если текст не помещается в одной ячейке, он отображается поверх следующей, если она пустая. Если же в следующей ячейке уже нахо- дятся данные, то отображение текста как бы обрезается на границе между ячейками. Чтобы увидеть всю информацию, находящуюся в ячейке, следует выделить ее и взглянуть на строку формул. Кроме того, можно просто сде- лать ячейку шире. 5 Способы выделения ячеек на листе Выполнение большей части команд становится возможным после выделения ячеек, с которыми необходимо произвести те или иные действия. Группа смежных ячеек называется диапазоном. Диапазон задается путем указания ссылок на левую верхнюю и правую нижнюю ячейки. Между ссылками ставится двоеточие. Например, А3:B9, D3:H16. а) выделение диапазона ячеек осуществляется протаскиванием мыши; б) выделение нескольких несмежных диапазонов ячеек осуществляется спо- собом a) при нажатой клавише Ctrl; в) выделение строк. При выделении одной строки следует щелкнуть на ее за- головке. При выделении нескольких строк следует щелкнуть по их заголовкам, удерживая нажатой клавишу Ctrl; г) выделение столбцов осуществляется аналогично (в); д) выделение всех ячеек текущего листа осуществляется щелчком по «чис- той» ячейке, образованной пересечением столбца заголовков строк и строки за- головков столбцов. Изменение данных в ячейках Изменить содержимое ячейки можно двумя способами: 1) с помощью строки формул. Следует сделать ячейку активной (щелкнуть по ней мышью), а затем щелкнуть в строке формул. После этого можно внести необходимые изменения или дополнения, а затем нажать клавишу Enter. Excel обновит содержимое нужной ячейки; 2) непосредственно в ячейке. Следует дважды щелкнуть по нужной ячейке; появится текстовый курсор; внести изменения и нажать клавишу Enter. Автоматический ввод значений в ячейки – Автозаполнение Предположим, необходимо ввести данные, представляющие собой некото- рый ряд: числовой (например, 1; 3; 5;...), текстовый (например, январь; март; май;...) или смешанный (например, 2000 год; 2001 год; 2002 год;...). Для этих це- лей используется Автозаполнение (заполнение смежных ячеек в одной строке или столбце). Маркер заполнения – это правый нижний угол рамки активной ячейки (или диапазона). Точнее, это небольшой квадратик, расположенный в этом углу. Ука- затель мыши при подведении к маркеру заполнения превращается в черный кре- стик. Перетаскивание маркера заполнения влево, вправо, вверх или вниз позво- ляет заполнить ячейки выделенного при этом диапазона значениями в возрас- тающем или убывающем порядке. Для заполнения последовательности данных в блоке соседних ячеек необхо- димо выполнить следующие действия: 1) ввести первый (или первые два) элемент последовательности. В случае ввода двух элементов выделить их протаскиванием мыши; 6 2) перетащить маркер заполнения в нужном направлении на нужное число ячеек; 3) отпустить кнопку мыши для автоматического ввода данных. В распоряжении пользователя имеются следующие режимы Автозаполнения: а) простое дублирование данных. Следует вести значение числа или неко- торый текст (за исключением элементов стандартного списка) в активную ячейку и выделить с помощью маркера заполнения требуемый диапазон. Вве- денная информация (например, слово Доходы) будет продублировано в этом диапазоне. Если в активную ячейку введен элемент стандартного списка (на- пример, слово Январь) или введена информация, которую Excel может расце- нивать как «растущую» (например, Проект 1), то для дублирования данных в соседние ячейки необходимо нажать клавишу Ctrl и, не отпуская ее, пере- тащить маркер заполнения; б) простая числовая последовательность. Следует ввести первое число по- следовательности в активную ячейку, нажать клавишу Ctrl и протащить маркер заполнения до конца диапазона. Последовательность начнется с введенного чис- ла, каждая следующая ячейка будет содержать число, на единицу большее, чем предыдущее; в) числовые ряды. Следует ввести в соседние ячейки 2 элемента ряда, выде- лить их и перетащить маркер заполнения через заполняемые ячейки. Для построения рядов по типу Прогрессия необходимо выполнить следую- щие команды Excel: Правка – Заполнить – Прогрессия. Открывается диалого- вое окно Прогрессия, в котором можно задать тип прогрессии (арифметическая, геометрическая), шаг изменения значений элементов ряда, расположение данных (по строкам или по столбцам), а также предельное значение. Перед использова- нием режима Прогрессия в активную ячейку необходимо ввести значение пер- вого элемента; г) список дат (или временной список). Следует ввести в активную ячейку первую дату (или время) в любом формате и выделить необходимый диапазон путем протаскивания маркера заполнения. При построении списков дат шагом по умолчанию являются одни сутки (например, после ввода 1.01.97 и выделения диапазона вы увидите значения 11.01.97, 12.01.97 и т. д.), а при построении вре- менных списков – один час; д) списки. Часто используемые последовательности значений оформляются в Excel в виде списков. Например, список месяцев года или список дней недели. Чтобы просмотреть все стандартные списки, имеющиеся в памяти Excel, необхо- димо выполнить команду Сервис – Параметры и выбрать вкладку Списки. Для того, чтобы создать пользовательский список, необходимо выполнить следующие действия:  выполнить команду Сервис – Параметры и выбрать вкладку Списки;  в поле «Списки» выбрать строку «Новый список»;  ввести данные в поле «Элементы списка», разделяя их друг от друга на- жатием клавиши Enter;  щелкнуть по кнопке Добавить. 7 Чтобы получить доступ к списку в рабочей таблице, необходимо ввести на- звание элемента из списка в активной ячейке и протащить маркер заполнения на нужный диапазон; е) формула. При копировании формул в соседние ячейки при помощи марке- ра заполнения происходит автоматическая модификация адресов ячеек, входя- щих в формулу, а именно: происходит приращение значения строки при копиро- вании вверх/вниз или приращение значения столбца при копировании вле- во/вправо. Замечание. Для получения доступа к командам контекстного меню Автоза- полнение необходимо набрать первые элементы данных и протащить маркер за- полнения правой (а не левой) кнопкой мыши до необходимой ячейки. При этом за- полнения ячеек не произойдет, а появится меню с соответствующими командами:  копировать ячейки;  заполнить ряды;  заполнить форматы;  заполнить значения;  заполнить по дням;  заполнить по рабочим дням;  заполнить по месяцам;  заполнить по годам;  ряд значений (или Прогрессия). Работа с листами книги Каждый лист книги имеет свой ярлычок. Ярлычки играют роль закладок, разделяющих страницы книги, служат для идентификации листов и содержат их имена. Ярлычками удобно пользоваться при поиске конкретных листов в книге. Они видны в нижней части экрана. 1. Активизация листа осуществляется щелчком по ярлычку листа. 2. Переименование листов. Имена «Лист 1», «Лист 2»,..., используемые по умолчанию на ярлычках, не являются информативными. Работа будет эффектив- ной, если присвоить листам более содержательные имена типа «Доходы», «Рас- ходы» или «Итоги». Для переименования листа необходимо щелкнуть на нужном ярлычке и вы- полнить команду Формат – Лист – Переименовать. Возможен вариант исполь- зования контекстно-зависимого меню. При этом достаточно щелкнуть правой кнопкой мыши на ярлычке и выбрать команду Переименовать. 3. Вставка нового листа. Следует активизировать лист, перед которым не- обходимо вставить новый лист, и выполнить команду Вставка – Лист. 4. Удаление листов из книги. Следует выделить лист и выполнить команду Правка – Удалить лист. Для того чтобы удалить несколько листов, следует их выделить. Для этого при щелчке на ярлычке каждого выделяемого листа, кроме первого, должна быть нажата клавиша Ctrl. Затем можно открыть контекстно- зависимое меню (щелчком правой кнопки мыши) и выбрать команду Удалить. 8 5. Перемещение листа. Сделать лист активным, а затем перетащить его мышью в нужное место (указатель мыши приобретает вид стрелки с листом бумаги). 6. Копирование листа. Так же, как в пункте 5, но при этом должна быть на- жата клавиша Ctrl (указатель мыши при этом приобретает вид стрелки с листом бумаги, на котором изображен знак плюс). Копируемый лист получит имя ори- гинала, к которому добавлено «(2)». 7. Копирование листа из одной книги в другую. При выполнении этой опе- рации удобно использовать контекстно-зависимое меню. Из него следует вы- брать команду Переместить/Скопировать. В диалоговом окне Переместить или Скопировать следует указать книгу, в которую копируется лист, выбрав ее из списка Переместить выбранный лист в книгу. В этом списке перечислены книги, открытые в приложении Excel в данный момент. Следует также указать место расположения листа, выбрав элемент из списка Перед листом. Если выполняется копирование, а не перемещение, нужно установить флажок Создавать копию. Замечание. При выделении более чем одного листа в книге, весь ввод или изменение данных, которые были выполнены на текущем листе, повторяются на всех остальных листах. Примеры выполнения заданий Пример 1. Построить в строке следующий ряд информации: Доход 1997 Доход 1998 Доход 1999 Доход 2000 Порядок выполнения задания. 1. Выделить некоторую ячейку, например, А1 и ввести в нее текст: «Доход 1997». 2. Установить курсор в правый нижний угол ячейки А1 (вид курсора «+»). 3. Переместить маркер заполнения вправо вдоль ячеек B1, C1 и D1. Пример 2. Построить в столбце следующий ряд дат: 1.01.99 1.04.99 1.07.99 1.10.99 1.01.00 Порядок выполнения задания. 1. Выделить некоторую ячейку, например, B2. 2. Ввести дату «1.01.99». 3. Выделить ячейку с адресом В3 и ввести дату «1.04.99». 4. Выделить диапазон ячеек B2:B3. 5. Установить курсор в правый нижний угол выделенного диапазона и про- тащить маркер заполнения вниз вдоль ячеек B4, B5, B6. Пример 3. Построить в столбце F следующую геометрическую прогрессию: 1 5 25 125 625 3125 9 Порядок выполнения задания: 1. Выделить некоторую ячейку, например, F2. 2. Ввести первое число «1». 3. Выполнить команду Правка – Заполнить – Прогрессия. 4. Установить шаг геометрической прогрессии «5». 5. Установить максимальное значение элементов прогрессии «3125». 6. Установить флажок геометрической прогрессии. 7. Установить флажок «По столбцам». 8. Нажать кнопку ОК. Пример 4. Построить в строке с номером 20 следующий список столиц госу- дарств Европы: Москва Вена Лондон Мадрид Лиссабон Варшава Порядок выполнения задания. 1. Выполнить команду Сервис – Параметры; выбрать вкладку Списки. 2. В поле Списки выбрать Новый список. 3. Ввести названия столиц в поле Элементы списка, разделяя их нажатием клавиши Enter. 4. Щелкнуть по кнопке Добавить. 5. Щелкнуть по кнопке ОК. 6. Выделить некоторую ячейку в строке с номером 20, например, A20. 7. Ввести произвольный элемент списка. 8. Протащить маркер заполнения вправо вдоль ячеек B20, C20, D20, E20, F20. Тема 2. ФОРМАТИРОВАНИЕ ТАБЛИЦ. ПРОСТЕЙШИЕ ВЫЧИСЛЕНИЯ В ТАБЛИЦАХ. АВТОСУММИРОВАНИЕ Форматирование таблиц Различные режимы форматирования доступны пользователю через диалого- вое окно Формат ячеек. Для его вызова можно щелкнуть правой кнопкой мыши на любой ячейке. Активизируется контекстное меню, из которого следует вы- брать команду Формат ячеек. Откроется диалоговое окно, которое содержит множество вкладок. Рассмотрим возможности форматирования, предоставляе- мые на этих вкладках. 1. На вкладке Число можно задавать формат отображения числовой инфор- мации в ячейке. Например, можно задать количество десятичных знаков, которое будет отображаться у числа; можно дать указание отображать число в виде даты или в виде процентов, либо выводить рядом с числом знак денежной единицы. 2. На вкладке Выравнивание указывается способ выравнивания содержимо- го ячеек. Обычно числовая информация выравнивается по умолчанию по право- му краю ячейки, а текстовая информация – по левому краю. Можно установить 10