2.2. Теоретическое введение.

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

Рабочий лист в табличном процессоре, как правило, имеет матричную структуру – состоит из ячеек, расположенных на пересечении строк и столбцов. Ячейки рабочего листа имеют заданный формат, который устанавливается командой ФОРМАТ => Ячейки.

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

Константы делятся на числовые, текстовые, логические, типа дата/время. Числа включают цифры и символы-разделители: десятичная точка, процент, знак числа, денежное обозначение (например, р. или $).

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

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

Формула всегда начинается с символа равно (=). Операнды в выражении соединяются с помощью символов арифметических операций (+, -, /, *, ^) или операций отношения (>, >, =, <, <=, <>).

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

Рассмотрим указанные способы адресации ячеек подробнее.

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

При ее использовании в формулах табличный процессор запоминает расположение относительно текущей ячейки. При копировании формулы относительные ссылки, входящие в нее, изменяются таким образом, чтобы отображалось правило их вхождения в формулу относительно ее нового местоположения. Так при копировании формулы =А1+А2 из ячейки А4 в ячейку В4 формула примет вид =В1+В2. Таким образом, при копировании сохранилось правило “прибавить содержимое ячейки, расположенной тремя рядами левее, к содержимому ячейки, расположенной двумя рядами левее”.

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

Для ее задания перед именем столбца и номером строки следует ввести символ $, например: $В$4, $С$8, $J$9 и т. д.

При использовании смешанной адресации символ $ ставится перед тем параметром адреса, который при копировании должен оставаться неизменным. Например, С$8 или $Н9.

Часто в операциях обработки используются не отдельные ячейки, а блок ячеек, т. е. прямоугольная область смежных (например, А1:C10) или несмежных ячеек (например, А1:A10; C1:С10). Для выделенного блока ячеек можно создать имя с помощью команды ВСТАВКА => Имя => Создать, а затем использовать это имя в качестве одного из операндов, составляющих формулу. При копировании формулы, имена блоков остаются неизменными.

Табличные процессоры предоставляют большое число встроенных функций для различных вычислений (математические, статистические, логические, текстовые, финансовые, дата и время и другие). Любая функция имеет имя, один или несколько аргументов и начинается со знака равно (=). Функции вводятся с помощью диалогового окна “ Мастер функций”, которое вызывается командой ВСТАВКА => Функция или соответствующей кнопкой на панели рументов .

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

При построении формул следует придерживаться двух правил:

1. для формирования аргумента как результата промежуточного вычисления вызывается вложенный экран “Мастера функций” и после завершения определения вложенной функции осуществляется возврат на предыдущий экран;

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

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

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

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

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

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

Форматирование типов диаграмм осуществляется при активизации построенной диаграммы с помощью команды ДИАГРАММА => Тип диаграммы.

Параметрическая настройка диаграммы осуществляется при активизации построенной диаграммы командой ДИАГРАММА => Параметры диаграммы. После активизации диаграммы можно выделить любой из ее компонентов для корректировки, которая заключается в удалении или изменении свойств компонента путем форматирования, которое осуществляется с помощью команды ФОРМАТ, соответствующей элементу пункта меню (например,

ФОРМАТ => Выделенная область диаграммы; ФОРМАТ => Выделенная область построения; ФОРМАТ => Выделенный ряд и другие), либо с помощью аналогичных команд контекстного меню.

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

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

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

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

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

числа, текст, логические значения, значения ошибок, пустые ячейки.

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

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

Фильтрацию записей списка можно выполнить с помощью команды ДАННЫЕ => Фильтр. Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр.

Команда ДАННЫЕ => Фильтр => Автофильтр для каждого столбца позволяет задать:

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

• все – выбираются все записи без ограничений;

• первые 10 - выбираются первые 10 записей (с помощью контекстного меню эту цифру можно изменить).

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

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

Чтобы выключить автофильтр, достаточно выбрать еще раз команду ДАННЫЕ => Фильтр => Автофильтр.

Команда ДАННЫЕ => Фильтр => Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей списка:

1. критерий сравнения;

2. вычисляемый критерий.

Если критерий фильтрации формируется в нескольких столбцах, то его называют множественным критерием.

Технология использования расширенного фильтра включает два этапа:

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

2. фильтрация записей списка.

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

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

1. состав столбцов определяется составом столбцов извлекаемой информации;

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

3. ниже меток располагаются критерии сравнения типа:

4. точного значения;

5. значения, формируемого с помощью операторов отношения;

6. шаблона значения, включающего символы * и (или) ?.

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

Например, допустим, что нам нужно выбрать из таблицы, содержащей сведения о результатах экзаменационной сессии, записи о сдаче студентами группы Э‑11‑1Д экзамена по информатике на 4 или 5. Указанное условие можно сформировать следующими способами:

1)

Группа	Предмет	Оценка
Э-11-1Д	Информатика	>3

2)

Группа	Предмет	Оценка
Э-11-1Д	Информатика	4
Э-11-1Д	Информатика	5

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

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

=G2>СРЗНАЧ($E$5:$E$14)

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

При использовании заголовка столбца в формуле условия вместо ссылки или имени диапазона, в ячейке будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.

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

Для снятия действия условий фильтрации выполняется команда ДАННЫЕ => Фильтр => Показать все.

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

Выделение рабочих листов.

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

Вставка, удаление и переименование рабочих листов.

Чтобы добавить один лист, выберите команду Лист в меню ВСТАВКА или используйте аналогичную команду контекстного меню, которое вызывается щелчком правой кнопки по ярлыку рабочего листа. Новый рабочий лист вставляется перед выделенным рабочим листом. Чтобы вставить несколько листов, выберите необходимое количество листов, удерживая нажатой клавишу Shift, затем выполните команду Лист из меню ВСТАВКА.

Удаление выделенных рабочих листов осуществляется с помощью команды Удалить лист меню ПРАВКА или аналогичной команды контекстного меню. При этом выдается сообщение, что выделенные листы будут удалены навсегда.

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

Перемещение/Копирование листов.

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

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

Отображение скрытого листа

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

Отображение скрытых строк или столбцов

Для отображения на экран скрытых строк, выделите ячейки в строках над и под скрытыми строками, а затем в меню ФОРМАТ установите указатель на пункт Строка и выберите команду Показать. Для отображения на экран скрытых столбцов выделите ячейки в столбцах слева и справа от скрытых столбцов, а затем в меню ФОРМАТ установите указатель на пункт Столбец и выберите команду Показать.

Связывание рабочих листов можно осуществлять несколькими способами:

1. с помощью формул, содержащих адресные ссылки, прямо осуществляя их написание;

2. с помощью копирования и Специальной вставки с использованием кнопки Вставить ссылку;

3. с помощью консолидации рабочих листов.

Рассмотрим первый из трех перечисленных способов, разобрав примеры приведенные ниже.

=D7+'Итого'!B15

=('Экзамен1'!В11+'Экзамен2'!B11+'Экзамен3'!B11)/3

=СРЗНАЧ('Экзамен1'!В11;'Экзамен2'!B11;'Экзамен3'!B11)

С помощью первой формулы вычисляется сумма чисел, содержащихся в ячейке D7 текущего рабочего листа и ячейке В15 листа Итого. Во второй и третьей формулах вычисляется среднее арифметическое чисел, находящихся в ячейках В11 на рабочих листах Экзамен1, Экзамен2, Экзамен3.

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