ГОСУДАРСТВЕННЫЙ ИНСТИТУТ УПРАВЛЕНИЯ

И СОЦИАЛЬНЫХ ТЕХНОЛОГИЙ БГУ

Кафедра управления финансами и недвижимостью

Т. В. Борздова

ТАБЛИЧНЫЙ ПРОЦЕССОР

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я Борздова Т. В., ISBN 978-985-491-033-8 (ч.1) ГИУСТ БГУ, ISBN 978-985-491-032-

ОГЛАВЛЕНИЕ

Тема 1. Создание, заполнение и радактирование таблиц. Автозаполнение............ Тема 2. Форматирование таблиц. Простейшие вычисления в таблицах.

Автосуммирование

Тема 3. Таблицы в Excel. Функции и сложные вычисления

Тема 4. Диаграммы в Excel. Построение и редактирование диаграмм.................. Тема 5. Анализ и распределение данных. Подбор параметра

Тема 6. Массивы в Excel

Тема 7. Решение оптимизационных задач

Тема 8. Шаблоны

Тема 9. Финансовые расчеты и составление отчетных ведомостей

Тема 10. Сортировка данных

Тема 11. Консолидация и фильтрация данных

Литература

Тема 1. СОЗДАНИЕ, ЗАПОЛНЕНИЕ

И РЕДАКТИРОВАНИЕ ТАБЛИЦ. АВТОЗАПОЛНЕНИЕ

Основные термины 1. Лист – это массив ячеек, в которых хранятся данные. Листы являются блоками, из которых образуются книги.

2. Ячейка – минимальный элемент листа, образуемый на пересечении строк и столбцов. Ячейки объединяются в таблицы, которые используются для анализа, хранения и обработки данных. Ячейка имеет адрес, определяющий местоположение ячейки в таблице. Этот адрес задается именем столбца (А, В, С,...,Z, AA,...) и номером строки. Например, В4.

3. Книга – это файл, в котором хранятся листы. Книга состоит из листов.

По умолчанию в каждой книге Excel содержится 3 листа: «Лист 1», «Лист 2»

и «Лист 3». При создании нового документа в Excel создается новая книга. Каждая новая книга по умолчанию имеет имя «Книга 1», «Книга 2» и т. д.

4. Окно листа. Лист обычно состоит из 256 столбцов и 65536 строк. На экране видна всегда лишь небольшая часть листа. Точное количество строк и столбцов, видимых на экране, зависит от размера экрана монитора, видеодрайвера и установленного разрешения. Отображается лишь верхний левый угол всего листа со строкой заголовков столбцов и столбцом заголовков строк. Общий вид окна программы приведен ниже:

В Excel можно вводить следующие основные типы данных:

значения (числа);

текст (например, заголовки таблиц; пояснительная информация);

функции (например, синус или косинус, сумма);

формулы (сочетание значений, функций и текста).

Есть два способа ввода данных в ячейку:

1) прямой ввод данных. Для такого ввода следует установить указатель мыши на ячейку и выполнить одинарный щелчок по ней (либо переместить указатель мыши с помощью клавиатуры). При этом активная ячейка будет обведена толстой границей. После этого можно начать ввод данных; они одновременно будут появляться в ячейке и в строке формул (в строке окна листа, расположенной над строкой заголовков столбцов и снабженной символом «fx»). После окончания набора следует нажать клавишу Enter, чтобы подтвердить ввод данных, или клавишу Esc, чтобы отменить операцию;

2) ввод данных через строку формул. Вначале необходимо щелкнуть мышью на ячейке, в которую следует ввести данные. Затем следует щелкнуть в строке формул и начать набор данных. При этом в строке формул появляются 3 кнопки:

с изображением «Х» – отменяет введенные данные;

с изображением «» – подтверждает (завершает) ввод данных;

с изображением «fx» – вызов Мастера функций.

Excel анализирует вводимые данные, чтобы определить их тип. В одну ячейку можно ввести до 255 символов текста. Если нужно ввести число как текст, следует поставить перед ним апостроф. При вводе чисел в виде десятичной дроби разделителем является десятичная запятая. При вводе дат разделителем является точка или символ «/». Например, 12/03/99. При вводе времени разделителем является «:». Например, 14:25:30.

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

Замечание 2. Если текст не помещается в одной ячейке, он отображается поверх следующей, если она пустая. Если же в следующей ячейке уже находятся данные, то отображение текста как бы обрезается на границе между ячейками. Чтобы увидеть всю информацию, находящуюся в ячейке, следует выделить ее и взглянуть на строку формул. Кроме того, можно просто сделать ячейку шире.

Выполнение большей части команд становится возможным после выделения ячеек, с которыми необходимо произвести те или иные действия.

Группа смежных ячеек называется диапазоном. Диапазон задается путем указания ссылок на левую верхнюю и правую нижнюю ячейки. Между ссылками ставится двоеточие. Например, А3:B9, D3:H16.

а) выделение диапазона ячеек осуществляется протаскиванием мыши;

б) выделение нескольких несмежных диапазонов ячеек осуществляется способом a) при нажатой клавише Ctrl;

в) выделение строк. При выделении одной строки следует щелкнуть на ее заголовке. При выделении нескольких строк следует щелкнуть по их заголовкам, удерживая нажатой клавишу Ctrl;

г) выделение столбцов осуществляется аналогично (в);

д) выделение всех ячеек текущего листа осуществляется щелчком по «чистой» ячейке, образованной пересечением столбца заголовков строк и строки заголовков столбцов.

Изменить содержимое ячейки можно двумя способами:

1) с помощью строки формул. Следует сделать ячейку активной (щелкнуть по ней мышью), а затем щелкнуть в строке формул. После этого можно внести необходимые изменения или дополнения, а затем нажать клавишу Enter. Excel обновит содержимое нужной ячейки;

2) непосредственно в ячейке. Следует дважды щелкнуть по нужной ячейке;

появится текстовый курсор; внести изменения и нажать клавишу Enter.

Автоматический ввод значений в ячейки – Автозаполнение Предположим, необходимо ввести данные, представляющие собой некоторый ряд: числовой (например, 1; 3; 5;...), текстовый (например, январь; март;

май;...) или смешанный (например, 2000 год; 2001 год; 2002 год;...). Для этих целей используется Автозаполнение (заполнение смежных ячеек в одной строке или столбце).

Маркер заполнения – это правый нижний угол рамки активной ячейки (или диапазона). Точнее, это небольшой квадратик, расположенный в этом углу. Указатель мыши при подведении к маркеру заполнения превращается в черный крестик. Перетаскивание маркера заполнения влево, вправо, вверх или вниз позволяет заполнить ячейки выделенного при этом диапазона значениями в возрастающем или убывающем порядке.

Для заполнения последовательности данных в блоке соседних ячеек необходимо выполнить следующие действия:

1) ввести первый (или первые два) элемент последовательности. В случае ввода двух элементов выделить их протаскиванием мыши;

2) перетащить маркер заполнения в нужном направлении на нужное число ячеек;

3) отпустить кнопку мыши для автоматического ввода данных.

В распоряжении пользователя имеются следующие режимы Автозаполнения:

а) простое дублирование данных. Следует вести значение числа или некоторый текст (за исключением элементов стандартного списка) в активную ячейку и выделить с помощью маркера заполнения требуемый диапазон. Введенная информация (например, слово Доходы) будет продублировано в этом диапазоне. Если в активную ячейку введен элемент стандартного списка (например, слово Январь) или введена информация, которую Excel может расценивать как «растущую» (например, Проект 1), то для дублирования данных в соседние ячейки необходимо нажать клавишу Ctrl и, не отпуская ее, перетащить маркер заполнения;

б) простая числовая последовательность. Следует ввести первое число последовательности в активную ячейку, нажать клавишу Ctrl и протащить маркер заполнения до конца диапазона. Последовательность начнется с введенного числа, каждая следующая ячейка будет содержать число, на единицу большее, чем предыдущее;

в) числовые ряды. Следует ввести в соседние ячейки 2 элемента ряда, выделить их и перетащить маркер заполнения через заполняемые ячейки.

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

г) список дат (или временной список). Следует ввести в активную ячейку первую дату (или время) в любом формате и выделить необходимый диапазон путем протаскивания маркера заполнения. При построении списков дат шагом по умолчанию являются одни сутки (например, после ввода 1.01.97 и выделения диапазона вы увидите значения 11.01.97, 12.01.97 и т. д.), а при построении временных списков – один час;

д) списки. Часто используемые последовательности значений оформляются в Excel в виде списков. Например, список месяцев года или список дней недели.

Чтобы просмотреть все стандартные списки, имеющиеся в памяти Excel, необходимо выполнить команду Сервис – Параметры и выбрать вкладку Списки.

Для того, чтобы создать пользовательский список, необходимо выполнить следующие действия:

выполнить команду Сервис – Параметры и выбрать вкладку Списки;

в поле «Списки» выбрать строку «Новый список»;

ввести данные в поле «Элементы списка», разделяя их друг от друга нажатием клавиши Enter;

щелкнуть по кнопке Добавить.

Чтобы получить доступ к списку в рабочей таблице, необходимо ввести название элемента из списка в активной ячейке и протащить маркер заполнения на нужный диапазон;

е) формула. При копировании формул в соседние ячейки при помощи маркера заполнения происходит автоматическая модификация адресов ячеек, входящих в формулу, а именно: происходит приращение значения строки при копировании вверх/вниз или приращение значения столбца при копировании влево/вправо.

Замечание. Для получения доступа к командам контекстного меню Автозаполнение необходимо набрать первые элементы данных и протащить маркер заполнения правой (а не левой) кнопкой мыши до необходимой ячейки. При этом заполнения ячеек не произойдет, а появится меню с соответствующими командами:

копировать ячейки;

заполнить ряды;

заполнить форматы;

заполнить значения;

заполнить по дням;

заполнить по рабочим дням;

заполнить по месяцам;

заполнить по годам;

ряд значений (или Прогрессия).

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

Они видны в нижней части экрана.

1. Активизация листа осуществляется щелчком по ярлычку листа.

2. Переименование листов. Имена «Лист 1», «Лист 2»,..., используемые по умолчанию на ярлычках, не являются информативными. Работа будет эффективной, если присвоить листам более содержательные имена типа «Доходы», «Расходы» или «Итоги».

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

3. Вставка нового листа. Следует активизировать лист, перед которым необходимо вставить новый лист, и выполнить команду Вставка – Лист.

4. Удаление листов из книги. Следует выделить лист и выполнить команду Правка – Удалить лист. Для того чтобы удалить несколько листов, следует их выделить. Для этого при щелчке на ярлычке каждого выделяемого листа, кроме первого, должна быть нажата клавиша Ctrl. Затем можно открыть контекстнозависимое меню (щелчком правой кнопки мыши) и выбрать команду Удалить.

5. Перемещение листа. Сделать лист активным, а затем перетащить его мышью в нужное место (указатель мыши приобретает вид стрелки с листом бумаги).

6. Копирование листа. Так же, как в пункте 5, но при этом должна быть нажата клавиша Ctrl (указатель мыши при этом приобретает вид стрелки с листом бумаги, на котором изображен знак плюс). Копируемый лист получит имя оригинала, к которому добавлено «(2)».

7. Копирование листа из одной книги в другую. При выполнении этой операции удобно использовать контекстно-зависимое меню. Из него следует выбрать команду Переместить/Скопировать. В диалоговом окне Переместить или Скопировать следует указать книгу, в которую копируется лист, выбрав ее из списка Переместить выбранный лист в книгу.

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

Замечание. При выделении более чем одного листа в книге, весь ввод или изменение данных, которые были выполнены на текущем листе, повторяются на всех остальных листах.

Пример 1. Построить в строке следующий ряд информации:

Доход 1997 Доход 1998 Доход 1999 Доход Порядок выполнения задания.

1. Выделить некоторую ячейку, например, А1 и ввести в нее текст: «Доход 1997».

2. Установить курсор в правый нижний угол ячейки А1 (вид курсора «+»).

3. Переместить маркер заполнения вправо вдоль ячеек B1, C1 и D1.

Пример 2. Построить в столбце следующий ряд дат:

Порядок выполнения задания.

1. Выделить некоторую ячейку, например, B2.

2. Ввести дату «1.01.99».

3. Выделить ячейку с адресом В3 и ввести дату «1.04.99».

4. Выделить диапазон ячеек B2:B3.

5. Установить курсор в правый нижний угол выделенного диапазона и протащить маркер заполнения вниз вдоль ячеек B4, B5, B6.

Пример 3. Построить в столбце F следующую геометрическую прогрессию:

Порядок выполнения задания:

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.

ПРОСТЕЙШИЕ ВЫЧИСЛЕНИЯ В ТАБЛИЦАХ.

АВТОСУММИРОВАНИЕ

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

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

2. На вкладке Выравнивание указывается способ выравнивания содержимого ячеек. Обычно числовая информация выравнивается по умолчанию по правому краю ячейки, а текстовая информация – по левому краю. Можно установить один из вариантов вертикального расположения текста (вместо обычного горизонтального), а также задать перенос слов внутри ячейки «по словам».

3. На вкладке Шрифт определяются размеры, форма и толщина символов ячейки. По умолчанию используется шрифт Arial размером 10 пунктов.

4. На вкладке Граница выбираются линии границ для обрамления как отдельных ячеек, так и диапазонов листа.

5. На вкладке Вид можно выбрать средства привлечения внимания к определенным частям листа, таким как заголовки и итоговые суммы.

6. На вкладке Защита можно включить блокировку, которая предотвратит случайное изменение содержимого ячеек.

Быстрое форматирование с помощью команды Автоформат Команда Автоформат помогает быстро изменить внешний вид таблиц, списков и других структур данных. Для ее применения необходимо выполнить следующие шаги:

1. Выделить диапазон для форматирования.

2. Выполнить команду Формат – Автоформат. Раскроется диалоговое окно Автоформат.

3. Выбрать один из встроенных форматов. В поле Образец можно увидеть образец таблицы в этом формате.

4. Щелкнуть по кнопке Параметры и в области Изменить отметить элементы таблицы, подлежащие форматированию: шрифты, рамки и т. п.

5. Щелкнуть по кнопке ОК, чтобы увидеть результаты форматирования.

Excel обеспечивает условное форматирование в зависимости от выполнения заданных условий. Команда Формат – Условное форматирование позволяет задать 1–3 условия, в случае выполнения которых выбирается определенный формат ячеек. В ответ на команду открывается диалоговое окно следующего вида:

Рассмотрим следующий пример. Пусть требуется создать в блоке ячеек Е1:Е15 прогрессию 1, 2,…,15. Используя условное форматирование, выделить полужирным шрифтом, оранжевым цветом, синей границей и бледно-голубой заливкой числа, кратные трем, но не кратные двум.

1. На рабочем листе в блоке Е1:Е15 ввести прогрессию, используя средство Автозаполнение.

2. Выделить блок Е1:Е15 и выполнить команду Формат – Условное форматирование.

3. В открывшемся окне указать для условия Условие 1 в выпадающем списке «формула». В следующем поле ввода ввести формулу =И(ОСТАТ(Е1;3)=0;

ОСТАТ(Е1;2)0).

4. Нажать кнопку Формат и выбрать полужирный шрифт, оранжевый цвет, синюю границу, бледно-голубую заливку (вкладка Вид).

5. Нажать кнопку ОК.

Результат будет иметь следующий вид:

Когда впервые начинает заполняться лист, все строки на нем имеют одинаковую высоту, а столбцы – одинаковую ширину. По мере заполнения листа возникает необходимость установить подходящую ширину столбца или соответствующий размер строки. Для этого применяются следующие приемы:

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

2. Выполнить необходимое выделение и выполнить одну из команд Формат – Строка – Автоподбор высоты или Формат – Столбец – Автоподбор ширины.

3. Щелкнуть дважды на правой кромке заголовка столбца (или на нижней кромке заголовка строки), чтобы автоматически установить ширину столбца (или высоту строки) в соответствии с самой широкой (или самой высокой) записью в нем (в ней).

Если ваш знакомый скажет вам, что ему надо писать письма в город с индексом 141120, вам понадобится много времени, чтобы разобраться, где находится этот город. Но если вам будет сказано: «Я живу в городе Фрязине Московской области», то вы сразу будете знать, где живет ваш собеседник. Аналогично, возможность заменить в формуле адрес ячейки, ничего не говорящий о ее содержимом, более выразительным именем существенно упрощает работу в Excel.

Имя можно присвоить как любой ячейке, так и целому диапазону. Excel одинаково успешно может работать как с адресом ячейки, так и с ее именем.

Правила присвоения имен ячейкам и диапазонам:

первым символом должны быть буква или символ подчеркивания, например, «Доходы_1998»;

в качестве последующих символов могут использоваться буквы, цифры, знаки точки и подчеркивания (пробелы запрещены);

имя ячейки или диапазона не должно быть похожим на адрес ячейки или ее значение;

могут использоваться имена длиной до 256 символов.

Как присвоить имена ячейке или диапазону?

Имеется несколько способов присвоения имен ячейкам или диапазону:

1) использование поля имени (расположенного слева от строки формул):

выделить ячейку или диапазон;

щелкнуть в поле Имя;

ввести имя;

нажать клавишу Enter.

Когда ячейке или диапазону присвоено имя, достаточно выбрать это имя в списке поля Имя, и Excel немедленно перейдет к этому диапазону, выделив его;

2) использование команд меню. Следует выполнить команду Вставка – Имя. Она обеспечивает доступ к каскадному меню, где пользователь получает в распоряжение следующие возможности:

команда Присвоить. В диалоговом окне Присвоение имени следует перейти в поле Формула и выбрать ячейку или диапазон; следует также ввести имя в поле Имя и щелкнуть по кнопке Добавить;

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

Самым простым и быстрым способом перемещения объекта на небольшое расстояние в пределах листа является перетаскивание с помощью мыши. Это делается следующим образом:

выделить диапазон ячеек;

поместить указатель мыши на темную рамку диапазона. Указатель должен превратиться в белую стрелку;

перетащить выделенный объект на новое место. При этом широкая серая рамка будет перемещаться вместе с указателем;

отпустить кнопку мыши; объект займет новое положение.

Если при перетаскивании нажата клавиша Ctrl, указатель мыши превращается в стрелку, отмеченную «+». Это означает, что объект не перемещается, а копируется.

В процессе перетаскивания объекта иногда можно забыть, какая именно операция должна быть выполнена: копирование или перемещение. Поэтому иногда полезно пользоваться не левой, а правой кнопкой мыши. В этом случае после того, как перетащили объект и отпустили кнопку мыши, раскрывается контекстное меню, дающее возможность точно указать приложению Excel, что надо сделать (Копировать или Переместить).

Для вычислений в Excel используются различные формулы, которые могут быть простыми (такими как, например, сложение содержимого двух ячеек) и сложными (например, определение отклонения какой-либо величины от других значений последовательности). При вводе формул используются константы, адреса ячеек, операторы и функции.

В Excel используются следующие операторы:

арифметические операторы: сложение +, вычитание -, умножение *, деление /, возведение в степень ^, проценты %;

операторы сравнения: =,, =, ;

операторы связи: объединение «;» и диапазон «:»;

текстовый оператор & соединения текста.

При вводе формул в ячейки руководствуются следующими правилами:

формула всегда начинается со знака «=»;

в формуле возможно использование имени, присвоенного ячейке или диапазону ячеек;

формулу можно вводить непосредственно в ячейке, а можно в строке формул. При этом в ответ на ввод формулы (после нажатия клавиши Enter) Excel производит вычисления и отображает в ячейке результат; строка формул показывает саму формулу, если ячейка с формулой является активной;

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

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

Примеры записи формул:

Различие между относительными и абсолютными адресами можно понять на следующем простом примере. Ваш новый знакомый объясняет, как пройти к нему домой от того места, где вы в данный момент находитесь: «Пройдите прямо 2 квартала, затем поверните направо и пройдите еще 2 квартала». Это относительный адрес. Ваш маршрут зависит от того, где вы находитесь в данный момент. Однако ваш новый знакомый может использовать и абсолютный адрес, сказав, например, что он живет на проспекте Пушкина в доме номер 5.

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

Абсолютный способ адресации позволяет однозначно определять в формулах адреса ячеек. Эти адреса не должны изменяться ни при каких изменениях таблицы.

Абсолютный адрес всегда отмечается знаком доллара «$». Т. е. запись $B$3 неизменно означает адрес ячейки, стоящей на пересечении столбца В и строки 3.

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

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

1. Выделить ячейку ниже столбца или правее строки чисел, которые необходимо просуммировать.

2. Щелкнуть по кнопке Автосумма.

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

4. Если выбор сделан верно, щелкнуть еще раз по кнопке Автосумма.

Если нужен другой диапазон, то сначала следует выделить его, а затем щелкнуть по кнопке.

Пример 1. Использование операции Автосуммирование.

Подсчитать налоговые платежи фирмы «Орион» за 4 квартала 2008 года, cоставив и отформатировав следующую таблицу:

Порядок выполнения задания.

1. Заполнить таблицу, начиная с ячейки А1 (строку 2 оставить пустой).

2. Сделать активной ячейку В8 и выполнить операцию Автосуммирование.

3. Используя маркер заполнения, скопировать формулу в ячейки C8, D8, E8.

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

4. Отформатировать таблицу, разместив заголовок в центре поля, образованного ячейками A1, B1, C1, D1 и E1 (выделить указанные ячейки, выполнить команду Формат – Ячейки, выбрать вкладку Выравнивание, установить флажок Объединить ячейки и установить выравнивание По центру), изменив толщину и размер шрифта, тип линий границ таблицы и оформив заливку верхней и нижней строк.

Пример 2. Использование относительных адресов. Простейшие формулы Подсчитать общую стоимость групп изделий, заполнив и отформатировав следующую таблицу:

Порядок выполнения задания.

1. Заполнить таблицу, начиная с ячейки А1.

2. Сделать активной ячейку D2 и ввести в нее формулу: =B2*C2.

3. Скопировать формулу в ячейки D3 и D4, используя маркер заполнения.

В результате будет заполнен столбец D и подсчитана «Общая стоимость».

4. Отформатировать таблицу.

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

Налог (%) 17, Порядок выполнения задания.

1. Предположим, что процентая ставка налога находится в ячейке В1.

2. Заполнить таблицу, начиная с ячейки А1.

3. Заполнить столбец Е аналогично тому, как это сделано в примере 2. При этом формула в ячейке Е3 должна иметь вид: =C3*D3.

4. Сделать активной ячейкой ячейку F3 и ввести формулу для вычисления Величины налога: =E3*$B$1/100. При этом адрес $B$1 является абсолютным, так как при копировании формулы он не должен меняться.

5. Отформатировать таблицу.

И СЛОЖНЫЕ ВЫЧИСЛЕНИЯ

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

Функция Excel – это специальная формула, хранящаяся в памяти приложения. Каждая функция включает две части: имя функции (например, СУММ) и ее аргументы. Имя описывает операцию, которую эта функция выполняет. Аргументы – это данные, которые используются функцией для получения результата.

Аргументом функции могут быть ссылки или имена, текст или числа, дата или время. Аргументы всегда задаются в круглых скобках справа от имени функции.

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

Дата и время – выполняют математические преобразования с датами и временем, позволяют получить информацию о датах и времени.

Финансовые – объединяют функции, связанные с финансовыми расчетами:

подсчет чистого дохода, подсчет ставки для страховки, подсчет годового дохода в процентах.

Логические – содержат логические функции, такие как И, ЕСЛИ, НЕ.

Математические – предназначены для выполнения математических и тригонометрических вычислений.

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

Текстовые – используются для объединения нескольких текстовых фрагментов в один, вычисляют количество символов в текстовой строке, преобразуют число в текстовое значение.

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

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

щелкнуть мышью по кнопке Мастер функций на панели инструментов Excel. Она расположена справа от кнопки Автосуммирование, и на ней изображены символы fx;

выполнить команду Вставка – Функция.

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

В поле Категория выбирается нужная категория функций, а в поле Функция выбирается название самой функции. Внизу окна содержится пояснение о том, какие действия реализует данная функция:

Второе окно Мастера функций содержит поля для ввода аргументов выбранной функции. Если функция имеет переменное число аргументов, это окно диалога увеличивается при вводе дополнительных аргументов. Описание поля аргумента, содержащего курсор, выводится в верхней части этого окна диалога.

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

Это очень удобно, когда используются ссылки или имена.

Текущее значение функции отображается в центре и внизу этого окна диалога.

Для окончания диалога следует нажать кнопку ОК, и созданная функция появится в строке формул.

Копирование списка из Word в Excel. Мастер текстов Предположим, что имеется список, подготовленный с помощью текстового редактора Word и содержащий, например, следующие данные: фамилию, имя, отчество, номер отдела, оклад, количество отработанных дней в месяце. Возникает задача преобразовать этот список в таблицу приложения Excel. Для этого необходимо:

1) выделить в Word нужный текст. При этом элементы каждой строки должны быть отделены друг от друга разделителями: пробелом, точкой с запятой, запятой, тире или др.; выполнить команду Правка – Копировать;

2) перейти на нужный лист книги Excel и выполнить команду Правка – Вставить.

Все данные после вставки будут размещены в одном столбце. Для того чтобы разнести их по разным столбцам, следует вызвать Мастер текстов. Для этого необходимо выполнить команду Данные – Текст по столбцам. Появится окно Мастер текстов (разбор) – шаг 1 из 3 и начнется так называемое «расщепление»

текста на отдельные ячейки:

а) на шаге 1 работы Мастера текстов указывается формат данных. Если данные разделены с помощью разделителей, то следует выбрать опцию «с разделителями»;

б) на шаге 2 задается вид разделителей, а также указывается, принимать ли во внимание кавычки в качестве признаков текстовых строк. В поле «Образец разбора данных» можно видеть результат «расщепления» данных по столбцам;

в) шаг 3 Мастера текстов обеспечивает более тщательную настройку. При этом задается формат данных в столбцах. Рекомендуется значение «Общий». В этом случае числа будут отображаться как числа, даты как даты, текст как текст.

Замечание. Всегда можно вернуться на один или несколько шагов назад и все изменить.

г) щелчок по кнопке Готово завершает преобразование.

Пример 1. Использование функций МАКС, МИН и СРЗНАЧ Для следующей ниже таблицы вычислить для каждого продавца его минимальную, максимальную и среднюю сумму продаж.

ИТОГОВЫЕ ПРОДАЖИ ЗА МЕСЯЦ

Порядок выполнения действий.

1. Выделить ячейку в строке для продавца Николая и в столбце, следующем за столбцом «Всего».

2. Щелкнуть по кнопке Мастер функций на панели инструментов. Из списка Категория выбрать Статистические.

3. В списке Функция выбрать функцию МАКС.

4. Щелкнуть по кнопке ОК.

5. Выделить мышью числовые значения в строке «Николай» и столбцах «Январь», «Февраль» и «Март». Выделенный диапазон появится в поле Число1.

6. Щелкнуть по кнопке ОК.

7. Повторить указанные действия в двух соседних столбцах, задавая при этом функции МИН и СРЗНАЧ.

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

Порядок выполнения действий.

1. Выделить ячейку в столбце «Скидка» и строке «Власова».

2. Щелкнуть по кнопке Мастер функций на панели инструментов. Из списка Категория выбрать Логические.

3. В списке Функция выбрать функцию ЕСЛИ.

4. Щелкнуть по кнопке ОК.

5. В поле Логическое выражение ввести условие: > 1000.

6. В поле Значение_если_истина ввести формулу: *20%.

7. В поле Значение_если_ложь ввести формулу: *10%.

8. Щелкнуть по кнопке ОК.

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

10. – это, например, В2, если таблица введена, начиная с ячейки А1.

Пример 3. Использование функций работы с датами Для приведенной ниже таблицы подсчитать возраст сотрудников фирмы.

СОТРУДНИКИ ФИРМЫ

Порядок выполнения действий.

1. Выделить ячейку в строке «Власов» и столбце «Возраст».

2. Ввести формулу: = СЕГОДНЯ()-.

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

4. Выделить полученные значения в столбце «Возраст». Выполнить команду Формат – Ячейки и выбрать вкладку Число. В списке форматов выбрать Все форматы и в поле Тип ввести «ГГ».

5. Нажать клавишу ОК.

Пример 4. Использование функции ППЛАТ (ПЛТ) Предположим, что магазин собирается закупить 100 штук видеомагнитофонов по цене 350 долл. за штуку. Он берет кредит в 350*100=35000 долл. под 12 % годовых на срок 2 года. Каковы будут ежемесячные выплаты магазина при погашении этого кредита? Каковы будут ежемесячные выплаты магазина при покупке другого количества видеомагнитофонов?

Порядок выполнения действий.

1. Составить следующие таблицы:

2. Ввести в ячейку F6 формулу: =F4*F5.

3. Ввести в ячейку C4 формулу: =F6.

4. Ввести в ячейку C8 формулу: =ППЛАТ(C5/12;C6;C4). Появится число –1647,57.

5. Изменить в ячейке F5 число 100 на 80.

6. Посмотреть, каковы будут новые выплаты.

ПОСТРОЕНИЕ И РЕДАКТИРОВАНИЕ ДИАГРАММ

Диаграмма – это способ представления данных. Смысл диаграммы состоит в том, чтобы более наглядно представить информацию, содержащуюся в числах таблиц. Хотя листы книг удобны для ввода информации и выполнения вычислений, тем не менее, данные на них довольно трудно анализировать. Как в течение года изменяется суточная реализация товара? Когда реализация товара достигает максимума?

Ответы на эти и подобные вопросы проще всего получить, используя диаграммы.

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

как рисунок на одном рабочем листе с данными;

на отдельном листе рабочей книги.

Диаграмма, построенная прямо на рабочем листе, называется внедренной.

Если диаграмма расположена на отдельном листе, то этот лист называется листом диаграммы.

Данные на диаграммах представляются точками, линиями, секторами и другими элементами в зависимости от типа диаграммы. Большинство диаграмм упорядочивают данные по горизонтальной (ось Х или ось категорий) и вертикальной (ось Y или ось значений) осям. Но такая привязка не является единственно возможной. Суть не в направлении, а в том, что представляет то или иное направление. Элементами диаграммы являются:

ось значений (обычно вертикальная);

точка на диаграмме соответствует значению, помещенному в ячейке таблицы на листе;

маркер данных. Это графический символ, представляющий конкретное числовое значение;

заголовок диаграммы;

подписи значений;

легенда. Содержит описание маркеров данных (поясняет, какие данные отображаются диаграммой. Например, товар А, товар В);

заголовок оси значений;

ось категорий с масштабными метками;

заголовок оси категорий;

область построения диаграммы.

Создание диаграмм. Работа с Мастером диаграмм Для создания диаграмм в Excel достаточно выполнить следующие действия:

выделить данные для диаграммы;

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

Рассмотрим работу Мастера диаграмм для следующего примера таблицы, содержащей поквартальные количественные показатели продажи товаров:

Требуется получить диаграмму, отражающую товарооборот в первом квартале. Вначале выделим диапазон A4:B6, включающий наименования товаров и их количественные показатели за рассматриваемый квартал. Щелкнем по кнопке Мастер диаграмм на стандартной панели инструментов.

Работа Мастера диаграмм состоит из четырех шагов. На первом шаге выбирается переходящий тип диаграммы. В окне Мастер диаграммы (шаг 1 из 4):

тип диаграммы представляются 14 типов стандартных диаграмм, а каждый тип имеет еще несколько видов. Тип диаграммы выбирается в поле Тип на вкладке Стандартные. Выбрав тип и вид диаграммы, можно просмотреть диаграмму, нажав и удерживая кнопку Просмотр результата.

Кроме того, в Excel предусмотрено несколько шаблонов нестандартных диаграмм. Тип нестандартной диаграммы можно выбрать на вкладке Нестандартные. Выбор типа диаграммы на этой вкладке автоматически приводит к появлению диаграммы в поле Образец. Выбрав тип диаграммы, следует щелкнуть по кнопке Далее.

Какой тип диаграммы лучше выбрать? Это зависит от информации, для отображения которой предназначена диаграмма. Рассмотрим различные типы диаграмм и сформулируем рекомендации по их применению.

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

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

Линейчатые диаграммы это те же гистограммы, но с иной ориентацией осей. В отличие от гистограмм здесь ось категорий расположена вертикально, а ось значений – горизонтально. Линейчатые диаграммы применяются в тех же случаях, что и гистограммы. Горизонтальное расположение оси зависимых переменных делает их особенно удобными для сравнительного представления разных величин в пределах одного временного периода. Скажем, реализацию товара за месяц различными продавцами очень хорошо продемонстрирует линейчатая диаграмма.

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

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

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

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

Поверхностные диаграммы используют для демонстрации взаимосвязей нескольких переменных или для наглядного представления больших объемов данных. Поверхностные диаграммы выглядят как рельефные карты местности, показывая «возвышенности» и «впадины» для большой совокупности данных.

Пузырьковые диаграммы отображают на плоскости наборы из трех значений. Первые два значения определяют точку расположения пузырька. В этом плане эти диаграммы подобны точечным. Третье значение отображается размером пузырька.

Кольцевые диаграммы – это одна из разновидностей круговых диаграмм.

Они также отражают связи между частями и целым. На кольцевых диаграммах, в отличие от круговых, можно представлять разные данные.

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

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

Биржевые диаграммы отображают наборы данных из трех значений (например, самый высокий курс, самый низкий курс и курс закрытия).

Цилиндрические, конические и пирамидальные диаграммы – это те же гистограммы со столбцами соответственно в виде цилиндров, конусов и пирамид.

Задание в диаграммах диапазона, содержащего данные Вторым шагом в создании диаграммы является задание диапазона, содержащего данные, которые будут представлены на диаграмме. Выбор данных уже был сделан выделением нужного диапазона перед вызовом Мастера диаграмм.

Если в диаграмме предполагается использовать заголовки строк и столбцов, то их следовало также включить в выделенный диапазон. Если этого не было сделано или требуется изменить сделанный выбор, то можно воспользоваться окном Мастер диаграмм (шаг 2 из 4): источник данных диаграммы.

Заметим, что диапазон может состоять из нескольких строк или столбцов.

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

По умолчанию каждому набору данных присваивается имя «Ряд 1», «Ряд 2», и т. д. Эти имена можно изменять, вводя новые в поле Имя. Данные имена отображаются в легенде.

После определения диапазона ячеек с данными и задания рядов диаграммы следует перейти к следующему шагу, щелкнув по кнопке Далее.

Задание дополнительных элементов диаграммы На третьем шаге работы Мастера диаграмм в окне Мастер диаграмм (шаг 3 из 4): параметры диаграммы можно добавить легенду, если Мастер этого не сделал; можно подписать оси, озаглавить диаграмму, подписать данные и выполнить некоторые другие операции. Для реализации этих операций в окне Мастера имеются вкладки: «Подписи данных», «Таблица данных», «Заголовки», «Оси», «Линии сетки», «Легенда».

Выбор варианта размещения диаграммы (внедренная диаграмма или на отдельном листе) осуществляется на четвертом шаге работы Мастера диаграмм в окне Мастер диаграмм (шаг 4 из 4): размещение диаграммы.

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

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

Если диаграмма готова, следует щелкнуть по кнопке Готово.

Ниже показан окончательный вариант диаграммы для приведенной выше таблицы с общим заголовком и с заголовками для осей:

1. Преобразование диаграмм к другому типу:

выделить диаграмму, щелкнув по ней левой кнопкой мыши;

щелкнуть правой кнопкой мыши в пределах выделенной области;

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

2. Добавление данных в диаграмму:

выделить нужный диапазон данных в таблице;

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

Замечание. Если Excel не может однозначно определить, как разместить данные, он выведет диалоговое окно Специальная вставка. После сделанных указаний следует щелкнуть по кнопке ОК.

Если диаграмма построена на отдельном листе, то для включения дополнительных данных необходимо выполнить следующие действия:

щелкнуть правой кнопкой мыши в области диаграммы;

выбрать из контекстного меню команду Исходные данные;

выделить соответствующие данные;

щелкнуть на кнопке ОК в диалоговом окне Исходные данные.

3. Выделение (выдвижение) сектора круговой диаграммы:

щелкнуть на секторе, который следует выдвинуть. При этом вокруг него появятся маркеры;

плавно вытащить сектор на требуемое расстояние.

4. Вставка легенды:

активизировать панель инструментов Диаграммы. Для этого выполнить команду Вид – Панель инструментов – Диаграммы;

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

5. Разметка значениями:

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

щелкнуть правой кнопкой на выделенном элементе. Из появившегося контекстного меню выбрать команду Формат ряда данных;

в появившемся диалоговом окне Формат ряда данных выбрать вкладку Подписи данных. Установить переключатель Значение;

щелкнуть по кнопке OK.

6. Размещение подписей данных на диаграмме:

выделить маркерами любой из элементов, представляющих в диаграмме значение (точка, столбец, линии);

щелкнуть правой кнопкой на выделенном элементе. Из появившегося контекстного меню выбрать команду Формат ряда данных;

в появившемся диалоговом окне Формат ряда данных выбрать вкладку Подписи данных. Установить переключатель Категория;

щелкнуть по кнопке OK.

7. Форматирование подписей данных:

выполнить двойной щелчок на любой из подписей данных и активизировать диалоговое окно Формат подписей данных;

перейти на вкладку Шрифт и задать требуемые тип, размер и начертание шрифта;

перейти на вкладку Выравнивание;

задать ориентацию в области Ориентация в поле со словом надпись (можно установить вертикальную ориентацию в поле со словом текст);

щелкнуть по кнопке ОК.

Замечание. Щелчок на подписи приводит к появлению вокруг нее серой рамки с маркерами. Захватив край рамки мышью, можно перетащить ее в подходящее место.

8. Форматирование осей:

дважды щелкнуть на требуемой оси;

в появившемся диалоговом окне Формат оси можно изменить толщину, цвет и тип линии оси, задать другой шрифт, расположить текст вертикально и др.

Для выбора шага приращения по оси необходимо выполнить следующие действия:

в диалоговом окне Формат оси выбрать вкладку Шкала;

щелкнуть на поле значения, используемого для масштабирования оси, и ввести новую величину.

9. Добавление заголовка:

щелкнуть правой кнопкой мыши на любом месте в области диаграммы;

выбрать в контекстном меню команду Параметры диаграммы;

перейти на вкладку Название диаграммы;

ввести имя диаграммы и щелкнуть по кнопке ОК.

10. Оформление диаграммы цветом:

выделить элемент данных на диаграмме;

дважды щелкнуть мышью на этом элементе. Появится диалоговое окно Формат ряда данных;

перейти на вкладку Вид;

выбрать для элемента данных цвет в области Заливка;

щелкнуть по кнопке Способы заливки и выбрать один из способов на вкладке Градиентная, Текстура, Узор, Рисунок;

для возврата в предыдущие окна нажимать кнопку OK.

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

В качестве базового примера рассмотрим следующее уравнение:

Замечание. Так как мы ищем корни многочлена третьей степени, то имеется не более трех действительных корней.

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

Например, протабулируем наш многочлен на интервале [-1;1] с шагом 0,2.

С этой целью:

1) введем в ячейку А2 значение -1, а в ячейку А3 – значение -0,8;

2) выделим диапазон А2:А3, расположим указатель мыши на маркере автозаполнения этого диапазона и протянем его на диапазон А4:А12;

3) в ячейку В2 введем формулу:

=А2^3-0,01*A2^2-0,7044*A2+0,139104.

4) выберем ячейку В2. Расположим указатель мыши на маркере автозаполнения ячейки и протянем его вдоль диапазона В3:В12.

Рис. 4.1. Локализация корней многочлена и диалоговое окно Подбор параметра Из рис. 4.1 видно, что многочлен меняет знак на интервалах [-1;-0,8], [0,2;0,4] и [0,6;0,8], и поэтому на каждом из этих интервалов имеется свой корень. Так как многочлен третьей степени имеет не более трех корней, то они все локализированы.

Прежде чем приступить к нахождению корней при помощи механизма Подбора параметра, необходимо выполнить некоторую подготовительную работу:

следует установить точность, с которой ищется корень. Заметим, что корень при помощи Подбора параметра находится методом последовательных приближений. Для этого нужно выбрать команду Сервис – Параметры и на вкладке Вычисления задать относительную погрешность и предельное число итераций равным, например, 0,00001 и 1000, соответственно;

следует отвести на рабочем столе ячейку, например С2, под искомый корень. Эта ячейка будет играть двойную роль. До применения подбора параметра в ней находится начальное значение приближения к корню уравнения, а после применения – найденное приближение значение корня;

корень при помощи Подбора параметра находим методом последовательных приближений. Поэтому в ячейку С2 надо ввести значение, являющееся приближением к искомому корню. В нашем случае первым отрезком локализации корня является [-1;-0,8]. Следовательно, за начальное приближение к корню разумно взять среднюю точку этого отрезка -0,9;

следует отвести ячейку, например D2, под функцию, для которой ведется поиск корня, причем вместо неизвестной y этой функции должна указываться ссылка на ячейку, отведенную под корень. Таким образом, в ячейку D2 необходимо ввести формулу:

Аналогично надо поступить с двумя другими искомыми корнями:

отвести ячейку С3 под второй корень, ввести в нее начальное приближение 0,3, а в ячейку D3 ввести следующую формулу:

отвести ячейку С4 под третий корень, ввести в нее начальное приближение 0,7, а в ячейку D4 ввести следующую формулу:

Теперь можно переходить к нахождению первого корня уравнения. Для этого следует выполнить последовательность действий:

1) выполнить команду Сервис – Подбор параметра. На экране отобразится диалоговое окно Подбор параметра;

2) в поле Установить в ячейке ввести ссылку на ячейку D2 (рис. 4.1).

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

3) в поле Значение ввести 0. Здесь указывается значение из правой части уравнения;

4) в поле Изменяя значение ячейки ввести С2. В данном поле задается ссылка на ячейку, отведенную под переменную;

5) нажать кнопку ОК.

На экране отображается окно Результат подбора параметра с результатами работы команды Подбор параметра. Кроме того, рассматриваемое средство помещает найденное приближенное значение корня в ячейку С2. В данном случае оно равно -0,71999.

Аналогично в ячейках С3 и С4 находятся два оставшихся корня. Они равны 0,21000 и 0,71999 (рис. 4.2).

Нахождение корней уравнения методом деления отрезка пополам Хорошим упражнением по работе с Excel является программирование на рабочем листе алгоритма нахождения корня уравнения F(x)=0 методом деления отрезка пополам. Пусть непрерывная функция F(x) имеет значения разных знаков на концах отрезка [a;b], т. е.

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

На рис. 4.3 приведены результаты нахождения корня с точностью до 0, методом деления отрезка пополам уравнения х2 – 2 = 0. За первоначальный отрезок локализации корня выбран [0;2].

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

Продемонстрируем использование функций СЧЕТЕСЛИ и СУММЕСЛИ на примере создания отчетной ведомости, в которой необходимо проследить все заказы, размещенные коммивояжерами фирмы, торгующей самоварами, и подсчитать объемы продаж по каждому из них.

Итак, в вашем распоряжении имеется рабочая книга, состоящая из двух рабочих листов (рис. 9.9):

лист Заказы, где приведен список осуществленных продаж за текущий день каждым из коммивояжеров вашей фирмы;

лист Коммивояжер со списком всех коммивояжеров вашей фирмы.

Рис. 9.9. Стоимость и число заказов, выполненных каждым из коммивояжеров Для проведения расчетов введите на рабочем листе Коммивояжеры представленные ниже формулы:

С2 =СЧЕТЕСЛИ(Заказы!$A$2:$A$10;A2) Возвращает число заказов, выполненных =СУММЕСЛИ(Заказы!$A$2:$A$10;A2; Возвращает стоимость заказов, выполD Пример отчетной ведомости по расчету затрат на производство Предположим, что вы являетесь бухгалтером фирмы, производящей компакт-диски. Составим отчетную ведомость по расчету затрат на производство компакт-дисков (рис. 9.10). Упаковка диска фирме обходится в 1 руб./шт., стоимость материалов – 4 руб./шт. Фирма продает готовые диски по цене 10 руб./шт.

Технические возможности позволяют выпускать до 5000 дисков в день. Оплата труда рабочих является сдельной и зависит от количества выпущенных дисков по правилу, представленному в табл. 9.4.

Предположим, что поступил заказ на изготовление 4500 компакт-дисков. Необходимо посчитать суммарные издержки и прибыль после выполнения данного заказа.

Рис. 9.10. Расчет затрат на производство товара Для упрощения чтения формул при помощи команды Вставка – Имя – Присвоить можно установить имена ячейкам и диапазонам ячеек, перечисленные ниже:

Зарплата рабочих в зависимости от объема выпущенных дисков находится в диапазоне F2:F7 по формуле массива (не забудьте завершить ее ввод нажатием комбинации клавиш ++):

=ЕСЛИ(ЗаказШт-1000>ДискиШт;1000*ОплатаРубШт);

ЕСЛИ(ЗаказШт>ДискиШт;(ЗаказШт-ДискиШт)*ОплатаРубШт;0).

Кроме того, для завершения расчетов введите в ячейки следующие формулы:

В10 =ЗаказШт*ПродажнаяЦена-ОбщиеИздержки Суммарная прибыль Сортировкой называется упорядочение элементов таблицы по возрастанию или убыванию. Например, последовательности (а)-(г) упорядочены по возрастанию, а последовательности (д)-(з) – по убыванию:

б) 25.45, 31.33, 44.55, 44.56, 44.57;

г) Арбузов, Борисов, Владимиров, Глебов, Данченко;

е) 44.57, 44.56, 44.55, 31.33, 25.45;

з) Данченко, Глебов, Владимиров, Борисов, Арбузов.

Excel позволяет выполнять сортировку данных, хранящихся в таблицах.

Можно выделить два основных режима сортировки – одноуровневый и многоуровневый. В первом случае данные упорядочиваются по указанному пользователем признаку, например, по номеру зачетной книжки студентов. Во втором случае, упорядочение идет сразу по нескольким ключам, например, сначала по первому, затем по второму.

Например, таблица 10.1 содержит варианты упорядочения списка по трем ключам: по фамилиям, именам, отчествам.

1. Выделить таблицу.

2. Выполнить команду Данные – Сортировка.

На экране появится окно (рис. 10.1):

3. Указать:

в области 1 – как идентифицировать поля:

используя подписи, то есть первая строка таблицы рассматривается как ее «шапка»;

по номерам (обозначениям) столбца, то есть «Столбец А», «Столбец В» и т. п.;

в области 2 – первый ключ сортировки (например, «Столбец А»

(см. рис. 10.1) или «Фамилия» (см. табл. 10.1);

в области 3 – правило упорядочения информации в первом столбце (по возрастанию или по убыванию);

если необходимо, то:

в области 4 – второй ключ сортировки (например, «Столбец В»

(см. рис. 10.1) или «Имя» (см. табл. 10.1);

в области 5 – правило упорядочения информации во втором столбце (по возрастанию или по убыванию);

в области 6 – третий ключ сортировки (например, «Столбец С»

(см. рис. 10.1) или «Отчество» (см. табл. 10.1);

в области 7 – правило упорядочения информации в третьем столбце (по возрастанию или по убыванию) и щелкнуть по кнопке ОК.

Замечание. Имейте в виду, что при сортировке взаимосвязанных строк, необходимо выделять всю таблицу. Иначе информация после сортировки не будет соответствовать истинной.

Тема 11. КОНСОЛИДАЦИЯ И ФИЛЬТРАЦИЯ ДАННЫХ Консолидация (то есть объединение) позволяет подводить итоги по данным, расположенным в различных областях таблицы, например, несмежных, находящихся на разных листах таблицы и т. п. Например, можно обрабатывать сведения, поступающие из различных отделов компании, и, таким образом, получить общую картину. Однако, консолидация – это не только суммирование. В ходе этого процесса можно вычислить такие статистические величины, как среднее, стандартное отклонение, количество значений. Совместно с консолидацией полезно использовать структурирование, причем структура может создаваться автоматически. Предназначенные для консолидации рабочие листы совсем не обязаны иметь одну и ту же структуру.

В качестве примера консолидации рассмотрим обработку данных об объемах продаж некоторой фирмы за первый квартал текущего года (рис. 11.1):

Существует несколько способов консолидации.

Способ «По расположению» предполагает, что данные объединяемых областей находятся в одном и том же месте на рабочих листах и размещены в одном и том же порядке. Его рекомендуется использовать для консолидации данных нескольких листов, созданных на основе одного шаблона.

1. Выделить область для размещения результата операции консолидации (на том же или новом листе).

2. Выполнить операцию Данные – Консолидация.

3. На экране появится окно Консолидация (рис. 11.2):

4. Выполнить в этом окне следующие действия:

а) в поле 1 указать операцию вычисления итогов;

б) в поле 2 ввести область для консолидации (заметим, что данную область можно также выделить мышью);

в) щелкнуть по кнопке Добавить; в поле 3 появится адрес выделенного диапазона;

г) повторить шаги (б) и (в) для всех консолидируемых областей;

д) в поле 4 указать, что использовать в качестве имен (подписи верхней строки или значения левого столбца);

е) в поле 5 отметить флажок Создавать или не создавать связи между данными;

ж) щелкнуть мышью по кнопке ОК.

Опишем процесс консолидации данных приведенного примера пошагово.

1. Укажите диапазон назначения, где должны располагаться консолидируемые данные. Можно указывать не весь диапазон, а только его левую верхнюю ячейку. В нашем случае выделите ячейку А1 рабочего листа Консолидация.

2. Выполните команду Данные – Консолидация. На экране отобразится диалоговое окно Консолидация (рис. 11.2).

3. В списке Функция укажите тип консолидации. Допустимы типы: Сумма, Кол-во значений, Среднее, Максимум, Минимум, Произведение, Кол-во чисел, Смещенное отклонение, Несмещенное отклонение, Смещенная дисперсия, Несмещенная дисперсия. В данном случае выберите значение Сумма.

4. В поле Ссылка укажите исходный диапазон данных, который должен быть консолидирован. В данном случае январь!$В$2:$В$7. Нажмите кнопку Добавить. В результате диапазон будет добавлен в список Список диапазонов.

5. Повторите п. 3, 4 для других консолидируемых диапазонов. В данном случае для диапазонов Февраль!$В$2: $В$7 и Март!$В$2: $В$7.

6. Укажите, что должен содержать диапазон назначения: фиксированные значения, которые в дальнейшем не будут изменяться при изменениях в исходных данных, или связанные величины, обновляющиеся при изменениях в исходных данных. Если фиксированные значения, то снимите флажок Создавать связи с исходными данными. В данном случае этот флажок надо установить.

7. Нажмите кнопку ОК.

Способ «По категориям» рекомендуется использовать для неупорядоченных данных, имеющих разную структуру, но одинаковые заголовки.

Общая схема:

1. Выделить верхнюю левую ячейку области консолидируемых данных.

2. Выполнить команду Данные – Консолидация.

3. Выбрать в поле Функция необходимую функцию.

4. Ввести адрес области консолидируемых данных в поле Ссылка (вручную или выделить мышью – см. описание способа «По расположению»). Данная область должна иметь заголовок!

5. Щелкнуть мышью по кнопке Добавить.

6. Повторить предыдущие шаги для всех объединяемых областей, установив в поле «Использовать в качестве имен» флажки, соответствующие верхней строке, левому столбцу или верхней строке и левому столбцу одновременно.

Способ «С помощью сводной таблицы» позволяет использовать Мастер сводных таблиц (команда Данные – Сводная таблица).

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

позволяют создавать обобщающие таблицы, которые предоставляют возможность группировки однотипных данных, подведения итогов, нахождения статических характеристик записей;

легко преобразуются;

разрешают выполнять автоматический отбор информации;

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

Пошаговое описание создания сводной таблицы Опишем пошаговый процесс создания сводной таблицы на примере таблицы Продавцы, имеющей структуру, приведенную на рис. 11.3:

1. Установите курсор в любую ячейку таблицы и выполните команду Данные – Сводная таблица. На экране появится первое окно Мастера сводных таблиц (рис. 11.4):

Рис. 11.4. Первое окно Мастера сводных таблиц 2. На шаге 1 Мастера убедитесь, что в качестве ответа на первый вопрос выбран параметр в списке или базе данных Microsoft Office Excel.

3. Убедитесь, что в качестве ответа на следующий вопрос выбран вариант сводная таблица.

4. Нажмите кнопку Готово.

Процедура завершена. При нажатии кнопки Готово Мастер организует область нового отчета сводной таблицы на основе стандартных настроек.

На новом листе книге Excel появятся три элемента: Список полей сводной таблицы, макет отчета, содержащий выделенные контуром области, на которые нужно перетаскивать поля, а также панель инструментов Сводные таблицы (рис. 11.5):

Теперь можно сформировать отчет, чтобы определить суммы продаж для каждого продавца. Макет отчета видоизменяется при перетаскивании в него полей:

1. Из окна Список полей сводной таблицы перетащите поле Продавец в область Перетащите сюда поля строк (рис. 11.6). Для перетаскивания можно щелкнуть на имени поля или находящуюся рядом с ним кнопку. После перетаскивания в область макета имена полей остаются в списке, но выделяются жирным шрифтом.

2. Из окна Список полей сводной таблицы перетащите поле Сумма заказа в область Перетащите сюда элементы данных:

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

Вот как должны выглядеть первые строки отчета сводной таблицы:

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

Автофильтр позволяет выбирать отдельные записи непосредственно в рабочем листе.

1. Выделить строку «шапки» таблицы (или одну из ячеек первой строки) и выполнить команду Данные – Фильтр – Автофильтрация.

2. Первая строка таблицы примет вид строки с раскрывающимися списками (рис. 11.7):

Рис. 11.7. Окно Excel-таблицы после выполнения 3. Щелкнуть мышью в поле раскрывающегося списка справа от ячейки, используемой для отбора информации, например, справа от ячейки Год рождения.

4. На экране появится список элементов, предлагаемых для фильтрации (рис. 11.8):

5. Выделить:

а) необходимую строку для фильтрации (например, 1956); на экране появится окно со списком строк, удовлетворяющим данному условию (например, список сотрудников 1956 года рождения – см. рис. 11.9):

Рис. 11.9. Пример фильтрации по году рождения «1956»

б) строку (Условие…); на экране появится окно (рис. 11.10), в котором необходимо указать критерий выбора информации (например, выберем сотрудников, год рождения которых равен 1956 или меньше 1945).

6. На экране появится окно с отфильтрованным списком (рис. 11.11).

Замечание. Чтобы отменить фильтрацию, достаточно повторно выполнить команду Данные – Фильтр – Автофильтр и таблица примет исходный вид.

Указать, как связываются условия («И» – то есть оба условия должны выполняться; «ИЛИ» – выполняется одно из указанных условий Рис. 11.10. Пример условного пользовательского фильтра Расширенный фильтр. Расширенная фильтрация позволяет использовать больше критериев для отбора требуемой информации. Необходимым условием использования расширенного фильтра является наличие заголовков столбцов.

Выше исходной таблицы на листе должно быть не менее трех пустых строк для указания критерия отбора информации.

1. Скопировать заголовки фильтруемых столбцов и вставить их в первой пустой строке, зарезервированной для ввода критерия отбора (рис. 11.12):

Рис. 11.12. Пример таблицы с выделенными над ней строками 2. Ввести в строки под этими заголовками требуемые условия для отбора (между значениями условий и основной таблицей должна находиться хотя бы одна пустая строка). Например, на рис. 11.13 показан критерий отбора информации о книгах по году издания (под заголовком «Год» мы ввели требуемые годы издания – 1998, 1999, то есть будем отбирать информацию о книгах, изданных в указанные годы).

Рис. 11.13. Верхняя часть таблицы Excel с введенными критериями фильтрации 3. Выполнить команду Данные – Фильтр – Расширенный фильтр (рис. 11.14).

Рис. 11.14. Команда Данные – Фильтр – Расширенный фильтр 4. На экране появится окно расширенного фильтра (рис. 11.15).

5. В области Обработка отметить вариант Скопировать результаты в другое место.

6. Щелкнуть мышью в области Исходный диапазон, а затем или ввести в этом поле адрес анализируемого диапазона, или выделить его с помощью мыши.

7. Щелкнуть мышью в области Диапазон условий, а затем или ввести в этом поле адрес, или выделить с помощью мыши строки с заголовками столбцов и введенными на шаге 2 (см. рис. 11.13) условиями отбора.

8. Щелкнуть мышью в области Поместить результат в диапазон, а затем или ввести в этом поле адрес, или выделить с помощью мыши строки для размещения результата фильтрации. Завершить процесс расширенной фильтрации щелчком по ОК.

На листе появится таблица с результатами фильтрации (рис. 11.16).

Рис. 11.16. Результаты расширенной фильтрации с критерием отбора:

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

Общая схема:

1. Выделить таблицу.

2. Выполнить команду Данные – Итоги.

На экране появится окно (рис. 11.17).

3. Указать:

а) в поле 1 – наименование столбца, при каждом изменении в котором будут изменяться результаты итогов;

б) в поле 2 – операцию вычисления итогов;

в) в поле 3 – в каких столбцах печатать итоги;

г) в поле 4 – вариант формирования итогов (заменять текущие итоги, устанавливать конец страницы между группами итогов или выводить итоги под данными).

4. Щелкнуть по кнопке ОК.

Для следующей таблицы:

на рис. 11.18 показан результат подведения итогов с параметрами, установленными в окне Промежуточные итоги (рис. 11.17).

ЛИТЕРАТУРА

Стоцкий, Ю. Самоучитель Office 2000 / Ю. Стоцкий. СПб.: Питер, 2000.

Новиков, Ф. Microsoft Office 2000 в целом / Ф. Новиков, А. Яценко. СПБ.:

БХВ-Санкт-Петербург, 2002.

Морозевич, А.Н. Основы информатики / А.Н. Морозевич, Н.Н. Говядинова, В.Г. Левашенко. Минск: Новое знание, 2003.

Информатика: учебник / под ред. проф. Н.В. Макаровой. М.: Финансы и статистика, 2000.

Симонович, С.В. Информатика для юристов и экономистов / С.В. Симонович.

СПб.: Питер, 2001.

Экономическая информатика / под ред. П.В. Конюховский, Д.Н. Колесова.

СПб.: Питер, 2001.

Экономическая информатика. М.: Финансы и статистика, 2001.

Колин, К.К. Социальная информатика: учеб. пособие для вузов / К.К. Колин.

М.: Академический Проект; Фонд «Мир», 2003.

Крейнак, Д. Microsoft Office XP / Д. Крейнак. М.: Изд-во АСТ: Изд-во Астрель, 2004.

Пасько, В. MicroSoft Office 2000 / В. Пасько. К.: Издательская группа BHV, 2000.

Ключников, М.В. Применение MS Word и Excel в финансовых расчетах: учеб.

пособие / М.В. Ключников. М.: Market DS, 2006.

Никольская, Ю.П. Excel в помощь бухгалтеру и экономисту / Ю. Никольская, А. Спиридонов. М.: Вершина, 2006.

Гельман, В.Я. Решение математических задач средствами Excel: практикум / В.Я. Гельман. СПб.: Питер, 2003.

Гарнаев, А.Ю. Excel, VBA, Internet в экономике и финансах / А.Ю. Гарнаев.

СПб.: БХВ-Петербург, 2005.

ТАБЛИЧНЫЙ ПРОЦЕССОР MICROSOFT EXCEL

Подписано в печать 14.01.2010. Формат 6084/8. Бумага офсетная.

Гарнитура Times New Roman. Ризография. Усл. печ. л. 12,09.

учреждение образования «Государственный институт управления и социальных технологий БГУ». ЛИ № 02330/0494050 от 03.02.2009.