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

ПРОЦЕССОР MS EXCEL

Гедранович Бронислав Адамович

Гедранович Валентина Васильевна

27 июня 2012 г.

Аннотация

Глава 10 из УМК: Гедранович, В.В. Основы компьютерных информационных технологий: учеб.-метод. комплекс / В.В. Гедранович, Б.А. Гедранович,

И.Н. Тонкович. – 2-е изд., стереотип. – Минск: Изд-во МИУ, 2011. – 344 с.

1.1 Общая характеристика табличных процессоров. Табличный процессор MS Excel Процессоры электронных таблиц (табличные процессоры) – это класс программ, основным назначением которых является обработка табличных данных, преимущественно числовых.

Наиболее широкое применение электронные таблицы нашли в экономических, бухгалтерских и научнотехнических задачах.

Активное развитие программ данного вида началось с наступлением эпохи персональных компьютеров, хотя в той или иной форме они существовали и раньше. Среди программных продуктов, предназначенных для работы с электронными таблицами, можно назвать SuperCalc, Lotus-1-2-3, Quattro Pro, StarCalc, Gnumeric, MS Excel, OpenOffice.org Calc.

К основным функциям табличных процессоров относят:

создание таблиц;

выполнение специальных расчетов (математических, статистических, бухгалтерских и пр.);

построение по данным таблицы разнообразных графиков и диаграмм;

взаимодействие с другими офисными продуктами, текстовыми редакторами, системами управления базами данных (СУБД) и пр.

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

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

Запуск приложения Excel может осуществляться любым из стандартных способов запуска приложений Windows.

Пользовательский интерфейс Excel показан на рисунке 10.1.

Гедранович Б.А., Гедранович В.В.

Рисунок 10.1 – Основные элементы рабочего листа MS Excel Основными элементами интерфейса являются:

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

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

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

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

Если поместить курсор в поле для ввода и редактирования данных, то слева от него появятся три кнопОтмена, Ввод и Вставка функции.

ки:

Рабочее поле (рабочий лист) окна приложения Excel.

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

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

Excel подобно большинству приложений MS Windows имеет обширную справочную систему. Для получения справки можно воспользоваться одним из следующих способов:

1. Выполнить команду Справка – Справка: Microsoft Excel.

2. Нажать кнопку Справка: Microsoft Excel на панели инструментов Стандартная.

3. Нажать клавишу.

4. Вызвать помощника.

1.2 Основные понятия табличного процессора Основными понятиями табличного процессора являются: книга, рабочий лист, электронная таблица.

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

Каждая книга может состоять из нескольких листов, поэтому в одном файле можно поместить разнообразные сведения (прямоугольные таблицы данных («электронные таблицы»), внедренные или связанные стр. 2 из Гедранович Б.А., Гедранович В.В.

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

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

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

Она состоит из 256 столбцов с именами от A до IV и 65 536 строк, нумерация которых начинается с единицы.

Каждая ячейка имеет адрес, формат, отображение, содержимое.

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

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

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

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

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

Над листами в книге можно выполнять следующие операции: вставлять, удалять, переименовывать, перемещать, копировать, присваивать имена, объединять в группу.

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

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

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

Переименование листа. По умолчанию Excel присваивает листам рабочей книги стандартные имена Лист1, Лист2 и т.д. Однако такие имена ничего не говорят о содержимом рабочего листа. Поэтому лучше использовать информативные имена. Имя листа не может содержать более 31 символа, включая пробелы.

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

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

Для того чтобы переместить или скопировать лист, необходимо:

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

из меню Правка или из контекстного меню ярлычка листа выбрать команду Переместить/скопировать;

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

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

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

Группировка листов. Можно одновременно выполнять операции на нескольких рабочих листах, создав групповое выделение. Это помогает единообразно отредактировать, отформатировать и реорганизовать группу листов. Excel обозначает режим группового редактирования, добавляя [Группа] в строке заголовка окна книги.

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

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

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

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

Гедранович Б.А., Гедранович В.В.

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

Для изменения цвета ярлычка используют команду Цвет ярлычка… из контекстного меню ярлычка или из меню Формат (Формат – Лист).

1.3 Адресация ячеек таблицы Имя столбца и номер строки в совокупности однозначно определяют адрес ячейки (используется также термин «ссылка»). Ссылка указывает на ячейку или диапазон ячеек листа и передает в Microsoft Excel сведения о расположении значений или данных, которые требуется использовать в формуле. При помощи ссылок можно использовать в одной формуле данные, находящиеся в разных частях листа, а также использовать в нескольких формулах значение одной ячейки. Кроме того, можно задавать ссылки на ячейки других листов той же или другой книги, а также на данные других приложений. Ссылки на ячейки других книг называются внешними. Ссылки на данные других приложений называются удаленными.

Excel поддерживает два вида стилей ссылок: стиль ссылок A1 (используется по умолчанию) и R1C1 (R – Row, строка, C – Column, столбец), который используется крайне редко.

При использовании стиля ссылок А1 для ссылки на ячейку следует ввести имя столбца, а затем номер строки, которым одновременно принадлежит ячейка. Например, ссылка Е9 указывает на активную ячейку, расположенную на пересечении столбца с именем Е и строки с номером 9 (см. рисунок 8.1).

При использовании стиля ссылок R1C1 ячейки адресуются по номерам строк и столбцов. Например, адрес ячейки Е9 можно записать как R9C5 (девятая строка, пятый столбец).

Для активизации стиля ссылок R1C1 необходимо выбрать команду Параметры из меню Сервис и на вкладке Общие установить флажок Стиль ссылок R1C1.

Диапазон ячеек задается указанием адреса левой верхней ячейки и правой нижней ячейки, разделенных символом двоеточие (:).Так, на рабочем листе Расходы (см. рисунок 10.1) таблица целиком расположена в связном диапазоне ячеек А5:Е9, а только текстовая часть таблицы – в несвязном диапазоне A5:E5;A6:A (его также можно записать как A5:A9;B5:E5).

Ниже приведены примеры ссылок (таблица 10.1).

Таблица 10.1 – Примеры ссылок в MS Excel диапазон ячеек в столбце А и строках с 10 по 20 A10:A диапазон ячеек в строке 15 и столбцах с B по E B15:E диапазон ячеек в столбцах с А по E и строках с 10 по ссылка в стиле R1C1: ячейка, расположенная во втоR2C рой строке и пятом столбце В зависимости от выполняемых задач в Excel можно использовать относительные ссылки, определяющие положение ячейки относительно положения ячейки с формулой, или абсолютные ссылки, которые всегда указывают на конкретные ячейки. Относительные ссылки автоматически корректируются при их копировании, а абсолютные ссылки – нет. Если перед буквой или номером стоит знак $, например, $A$1, то ссылка является абсолютной, в противном случае – относительной.

Для создания абсолютной ссылки на ячейку необходимо поставить знак $ перед той частью ссылки, которая не должна изменяться при копировании. Для этого в строке формул следует выделить ссылку, которую необходимо изменить, и нажать F4. Каждое нажатие F4 переключает тип ссылки в следующей последовательности: абсолютный столбец и абсолютная строка (например, $C$1); относительный столбец и абсолютная строка (C$1); абсолютный столбец и относительная строка ($C1); относительный столбец и относительная строка (C1). Например, если в формуле выбран адрес $A$1 и нажата F4, ссылка становится A$1.

Нажав F4 еще раз, получаем $A1 и т.д.

Трехмерные ссылки применяются при необходимости использования данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Excel использует все листы, хранящиеся между Гедранович Б.А., Гедранович В.В.

начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах от Лист2 до Лист13 включительно.

Excel позволяет задавать ссылки на ячейки других рабочих книг. В расширенном формате адрес ячейки имеет вид:

[Имя_файла_рабочей_книги]Имя_рабочего_листа!Адрес_ячейки.

Например, расширенный формат адреса активной ячейки Е9 рабочего листа Расходы из рабочей книги Книга2 выглядит так:

Заголовки и имена. Для ссылки на ячейки в столбцах и строках таблицы можно использовать заголовки этих столбцов и строк (не путать с именами столбцов и номерами строк рабочего листа!). Примерами заголовков являются «Цена», «Количество» и «Итого». Рабочий лист может содержать заголовки, размещенные вверху столбца и слева от строки, с помощью которых описываются данные внутри листа. При ссылке на необходимые данные можно использовать эти заголовки в формулах.

По умолчанию Microsoft Excel не распознает заголовки в формулах. Чтобы использовать заголовки в формулах, следует применять команду Параметры в меню Сервис. На вкладке Вычисления в группе Параметры книги устанавливают флажок Допускать названия диапазонов.

Следующий пример (рисунок 10.2) содержит столбцы, имеющие названия «Изделие1», «Изделие2», «Изделие3», и строки с названиями «Восточное» и «Западное». Для подсчета общей суммы для столбца «Изделие1» можно использовать формулу =СУММ(Изделие1).

Рисунок 10.2 – Использование заголовков при работе с данными Если нужно сослаться на количество «Изделие3» в Восточном филиале (значение которого равно 110,00), можно использовать формулу =Изделие3 Восток. Пробел в формуле между «Изделие3» и «Восток» – это оператор пересечения диапазонов. Этот оператор предписывает Excel вернуть значение ячейки, находящейся на пересечении строки, помеченной «Восток», и столбца, помеченного «Изделие3».

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

"=СУММ(Продажа!C20:C30)". В этом примере имя «Продано_в_первом_квартале» представляет группу ячеек "C20:C30" на листе «Продажа».

Имена можно использовать в любом листе книге. Например, если имя «Контракты» ссылается на группу ячеек "A20:A30" в первом листе рабочей книге, то это имя можно применить в любом другом листе той же рабочей книги для ссылки на эту группу.

Имена ячейкам и диапазонам присваиваются в диалоге команды Вставка – Имя – Присвоить.

По умолчанию имена являются абсолютными ссылками.

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

Ввод чисел. В Excel числа могут включать только следующие символы:

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

Перед рациональной дробью следует вводить 0 (ноль), чтобы избежать ее интерпретации как формата даты. Например, нужно вводить 0 1/ Перед отрицательным числом необходимо вводить знак минус или заключать его в круглые скобки: например, -5 или (5).

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

Ввод дат и времени суток. В Excel даты и время суток интерпретируются как числа. Внешнее представление времени или даты на листе зависит от числового формата, назначенного ячейке. При вводе значений даты или времени происходит их автоматическое распознавание, и общий формат ячейки заменяется на Гедранович Б.А., Гедранович В.В.

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

Параметры, установленные с помощью панели управления, определяют формат для текущих даты и времени и символы, использующиеся в качестве разделителей, например двоеточие (:) и точка (.) в стандарте России.

Ввод текста. В Excel текстом является любая последовательность, состоящая из цифр, пробелов и нецифровых символов, например, приведенные ниже записи интерпретируются как текст: 10AA109, 127AXY, 12-976, 208 4675. Введенный текст выравнивается в ячейке по левому краю.

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

Таблица 10.1 – Выделение текста, ячеек, диапазонов, строк и столбцов Чтобы выделить Отдельную Указать ячейку или перейти к ней, используя клавиши ячейку перемещения Если режим редактирования в ячейке включен, выделить ячейку, выполнить двойной щелчок левой кнопТекст в ячейке Если режим редактирования в ячейке выключен, выделить ячейку, а затем выделить текст в строке формул Диапазон Протянуть указатель от первой ячейки диапазона к поячеек следней Все ячейки Нажать кнопку Выделить все (в левом верхнем углу листа рабочего листа) Несмежные Выделить первую ячейку или первый диапазон ячеек, ячейки или затем, удерживая нажатой клавишу CTRL, выделить диапазоны ячеек Указать первую ячейку, затем, удерживая нажатой клаБольшой вишу SHIFT, указать последнюю ячейку диапазона.

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

1. Прямо в ячейке. Для этого нужно перейти в режим редактирования – два раза щелкнуть мышкой в ячейке. Появится курсор, после чего можно править содержимое. Но править в ячейке можно не всегда, а только в том случае, если это разрешено. Устанавливается разрешение на правку прямо в ячейке с помощью команды Сервис – Параметры. В появившемся окне на вкладке Правка устанавливается флажок Правка прямо в ячейке.

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

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

Для удаления ячеек необходимо выделить ячейки, строки или столбцы, которые следует удалить, а затем в меню Правка выбрать команду Удалить. При удалении в диалоговом окне Удаление ячеек выбрать один из вариантов: а) ячейки, со сдвигом влево; б) ячейки, со сдвигом вверх; в) строку или г) столбец.

Для удаления содержимого текущей ячейки достаточно нажать клавишу.

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

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

2. Выполнить команду Вставка-Строки или Вставка Столбцы.

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

Операции копирования (перемещения) содержимого ячеек выполняют по следующему алгоритму стандартным для MS Office образом:

1. Выделить нужную область листа.

Гедранович Б.А., Гедранович В.В.

2. Копировать (вырезать) ее содержимое в буфер.

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

4. Произвести вставку содержимого буфера.

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

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

Автозаполнение позволяет копировать содержимое ячеек в смежные ячейки. При выполнении этой операции можно использовать:

маркер заполнения;

команду Правка-Заполнить.

Команда Правка-Заполнить-Прогрессия позволяет заполнить диапазон ячеек одним или несколькими рядами чисел или дат.

Разделение областей. Чтобы разделить области необходимо:

1. Навести указатель на вешку, расположенную в верхней части вертикальной полосы прокрутки или в правой части горизонтальной полосы прокрутки.

2. Когда указатель примет вид двунаправленной стрелки, перетащить его вниз или влево.

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

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

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

Чтобы закрепить область, необходимо:

1. Выделить требуемую область:

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

Левую вертикальную область – выделить столбец, слева от которого требуется разбить лист.

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

Для снятия закрепления областей выбирается команда Снять закрепление областей в меню Окно.

1.5 Использование формул и функций Вычисления в таблицах выполняются с помощью формул. Формула может содержать операторы, константы, ссылки и функции. Результатом выполнения формулы есть некоторое новое значение, содержащееся в ячейке, где находится формула. Порядок вычислений определяется обычными математическими законами. Порядок операций может быть изменен с помощью скобок. В следующем примере =(B4+25)/СУММ(D5:F5) скобки вокруг первой части формулы определяют следующий порядок вычислений: определяется значение B4+25, затем полученный результат делится на сумму значений в ячейках D5, E5 и F5.

Формула в Excel всегда начинается со знака равенства =. Элементы, следующие за знаком равенства, являются операндами, разделяемыми операторами.

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

Операторами обозначаются операции, которые следует выполнить над операндами формулы. В Excel включено четыре вида операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.

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

Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ.

Гедранович Б.А., Гедранович В.В.

(знак меньше или равно) меньше или равно (A1> в нижней части диалогового окна Условное форматирование и повторить действия, описанные выше.

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

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

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

Каждому типу диаграмм соответствует несколько видов (форматов) диаграмм: плоскостные, объемные, составные и т.д.

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

Основными элементами диаграмм являются:

ось – линия, используемая как основа измерений для построения данных на диаграмме. Ось категорий (Х) и ось значений (У) образуют границы диаграммы и имеют шкалу для нанесения данных. Ось значений Z используется в трехмерных диаграммах;

точка данных – элемент данных;

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

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

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

область построения – это прямоугольная область, ограниченная осями и содержащая на себе все ряды диаграммы;

область диаграммы – вся диаграмма вместе со всеми ее элементами;

формула рядов – формула с внешними ссылками на данные определенного листа, по которым построена диаграмма;

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

деление – деление шкалы вдоль оси категорий (Х) и осей значений (Y и Z );

таблица данных – таблица, содержащая отображаемые на диаграмме данные.

Excel позволяет строить внедренные диаграммы и диаграммы на отдельных листах.

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

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

Диаграммы создаются на основе одного или нескольких рядов данных. Если диаграмма создается на основе несмежных рядов, то при выделении нужного диапазона следует нажимать клавишу.

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

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

кнопки Чтобы создать диаграмму с помощью Мастера диаграмм, необходимо:

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

2. Вызвать Мастер диаграмм, позволяющий задать исходные данные для построения диаграммы с помощью четырех шагов:

Шаг1: тип диаграммы. В диалоговом окне Мастер диаграмм (Шаг 1 из 4): тип диаграммы задается тип (гистограмма, график, линейчатая и т.д.) и вид (обычная гистограмма, гистограмма с накоплением и т.д.) диаграммы. Это окно диалога содержит две вкладки для стандартных и нестандартных типов диаграмм.

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

Шаг3: параметры диаграммы. В диалоговом окне Мастер диаграмм (Шаг 3 из 4): параметры диаграммы задаются следующие параметры диаграммы:

- название диаграммы и заголовки для ее осей;

Гедранович Б.А., Гедранович В.В.

- характеристики осей;

- подписи значений в рядах данных;

- таблица данных.

Шаг4: размещение диаграммы. В диалоговом окне Мастер диаграмм (Шаг 4 из 4): размещение диаграммы задается расположение диаграммы:

- на рабочем листе рядом с исходными данными;

- на отдельном листе диаграмм.

Настройка диаграммы. В Excel имеется широкий ассортимент команд, с помощью которых можно изменить внешний вид диаграммы.

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

изменение размеров диаграммы и размеров ее элементов (для изменения размеров объекта следует переместить маркеры изменения размеров с помощью мыши);

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

удаление диаграммы и элементов диаграммы (выделить объект и нажать клавишу );

добавление новых элементов (контекстное меню или команда Диаграмма – Параметры диаграммы);

изменение типа диаграммы (контекстное меню или команда Диаграмма – Тип диаграммы);

Использование Мастера диаграмм является самым простым и надежным способом создания диаграммы.

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

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

Список рассматривают как табличную базу данных.

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

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

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

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

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

В поле списка Сортировать по выбирается поле, по которому будут отсортированы данные, и тип сортировки: по возрастанию – цифры сортируются по возрастанию, текст – в алфавитном порядке, логические выражения – ЛОЖЬ предшествует ИСТИНА; по убыванию – сортировка в обратном порядке.

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

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

Гедранович Б.А., Гедранович В.В.

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

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

Поиск и фильтрация данных позволяет выводить на экран и просматривать данные списка, которые удовлетворяют определенным условиям (критериям).

Excel поддерживает несколько типов критериев поиска:

Поиск точного соответствия. Позволяет находить точные соответствия данных. В критериях на основе