Копирование формул. Относительные и абсолютные ссылки

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

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

Пример. Пусть необходимо вычислить функцию у = k*sin (x) для

x = 0; 0.1; 0.2; 0.3...1.0 и k=3 (k -константа). Постройте на листе 1 следующую таблицу:

А теперь введите формулу в ячейку В18

=$D$18*sin(A18)

Заметьте, при ссылке на ячейку, содержащую значение k, использован знак $. Такая ссылка и означает абсолютную, т.е. такую, которая не должна меняться при копировании формул. В ссылке на ячейку А18 значок $ не фигурирует, поэтому данная ссылка является относительной. Теперь формулу из ячейки В18 скопируйте в ячейки В19:В27. Просмотрите получившиеся формулы в данном столбце и убедитесь в том, что ссылки на ячейку D18 не меняются.

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

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

Графическое представление данных с помощью диаграмм

Программа предоставляет пользователям различные функции для работы с диаграммами. Эти функции вызываются командой меню Вставка / Диаграмма либо с помощью кнопки Мастер диаграмм (на этой кнопке изображена картинка столбиковой диаграммы) на панели инструментов. Диаграмма может быть создана на отдельном листе рабочей книги (листе диаграмм) либо ее можно разместить на рабочем листе (листе с вашими таблицами).

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

· гистограмма (столбиковая диаграмма);

· точечная;

· линейная;

· кусочно-линейная;

· круговая;

· кольцевая

и т. д.

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

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

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

Мастер диаграмм

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

Выделите в вашей таблице доходов следующие диапазоны ячеек:

· строку, содержащую названия месяцев (ячейки A3:G3);

· строку затрат (общих) (ячейки A9:G9);

· строку чистого дохода (ячейки A18:G18).

Для выделения отдельных строк воспользуйтесь следующим приемом: сначала выделите ячейки A3:G3, нажав клавишу{Shift} и используя курсорную клавишу “стрелка вправо”, а затем отпустите клавишу {Shift}. Нажмите клавишу {Ctrl} и, не отпуская ее, выделите курсором мыши ячейки A9:G9, потом ячейки A18:G18, а затем отпустите клавишу {Ctrl}.

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

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

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

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

!!! Если вам необходимо вернуться в одно из предыдущих окон Мастера, следует использовать кнопку Назад.

Во 2-м диалоговом окне задается тип диаграммы. Этот выбор не является окончательным, тип диаграммы впоследствии можно изменить. Выберите тип “Гистограмма” и перейдите в следующее окно.

В 3-м окне задается вид диаграммы выбранного типа. Выберите вид номер 6 и перейдите в следующее окно.

В 4-м окне указывается:

1) где находятся данные – в строках или столбцах таблицы – используйте установку по умолчанию: «в строках»;

2) где находятся метки оси Х и метки легенды – установите значения равными единице.

Нажмите кнопку Далее и перейдите в последнее окно Мастера.

В 5-м окне указываются название диаграммы и подписи по осям: введите название “Отчет о доходах и расходах предприятия”; для оси Х укажите обозначение категорий “Месяцы”, а для значений оси Y - “Показатели, тысяч рублей”. Затем нажмите кнопку Готово. У вас должна получиться диаграмма, представленная на рис.14.

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

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

Рис.14.

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

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

2.20. Управление данными в таблицах

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

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

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

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

!!! Список характеризует не содержимое таблицы, а способ ее организации. Отдельные записи таблицы должны быть однородны по столбцам.

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

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

Сортировка данных

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

!!! Возможно задание трех уровней сортировки: за одну операцию выполняется сортировка сначала по 1-у уровню, затем по 2-у и 3-у.

Для сортировки списка достаточно поместить указатель ячейки внутрь таблицы либо выделить таблицу, включая заголовки столбцов, а затем ввести команду меню Данные / Сортировка. В результате ввода команды откроется окно “Сортировка диапазона”. В данном окне следует установить необходимые для сортировки параметры (опции): в полях “Сортировать по ”, “Затем по ”, “В последнюю очередь по ”. Используя кнопки прокрутки, следует вы-

аблица 7

Профессиональные кадры компании “Россия-Урал-Возрождение”

брать заголовок (заголовки) столбцов, по которому вы хотите отсортировать записи, установить опцию “по убыванию” или “по возрастанию” и затем нажать кнопку ОК.

Отсортируйте вашу таблицу сначала по столбцу “Отдел” по алфавиту, затем задайте сортировку по полю “ФИО” по алфавиту.

Использование фильтров

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

Применение функции автофильтра

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

Найдите все записи вашей таблицы, содержащие в поле “Отдел” значение “Администр.”, иначе говоря – всех сотрудников административного отдела.

При использовании функции автофильтра пользователь может задавать свои критерии для фильтрации данных. Для этого необходимо активизировать нужное поле списка и в представленном списке выбрать элемент “Условие”. В открывшемся окне можно задавать комплексные критерии, включающие операции отношения (<, >, > =, < =, =, < >) и логические операции “И” и “ИЛИ”.

Щелкните по полю “Оклад”, выберите в списке элементов элемент “Условие” и в открывшемся окне (рис.15) задайте параметры для поиска сотрудников с окладом от 2000 до 5000 рублей.

Обратите внимание на пояснения в нижней части окна – символы “*” и “?” позволяют задавать критерии поиска для текстовых полей. Например, для поиска всех фамилий на букву Е необходимо задать следующий критерий: = Е* .

Рис.15

Найдите всех сотрудников, проживающих на ул.А.Валека и проработавших более 4 лет.

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

Отключение функции автофильтра производится повторным вводом команды Данные / Фильтр / Автофильтр.