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

Если ссылка состоит из обозначения столбца и строки (например,С5). То она является относительной. При копировании или протаскивании маркера автозаполнения относительные ссылки изменяются. Например, создадим в Excel следующую таблицу:

 *в ячейку С1 введена формула =А1+В1

Протягиваем с помощью маркера автозаполнения формулу ячейки С1 вниз, и в ячейке С2 получим формулу =А2+В2 (результат 110. то есть при протягивании вниз в относительных ссылках изменились номера строчек.

Протянет формулу ячейки С1 вправо, в ячейке D1 получим формулу =В1+С1 (результат 11), то есть при протягивании вправо в относительных ссылках изменились обозначения столбцов.

Если же в ссылке перед обозначением столбца и перед обозначением строки стоят знаки $ (например, $C$5). То такая ссылка называется абсолютной и при протягивании (или при копировании) не меняется.

Например, в той же таблице изменим формулу в ячейке С1 как =$A$1+$B$1. При протягивании вниз и вправо она меняться не будет, везде результатом будет число 7.

Если знак доллара стоит только перед номером столбца или только перед номером строки. То ссылка называется мешенной. При этом тот элемент. перед которым стоит знак доллара, при протягивании ли копировании изменяться не будет, а тот элемент, перед которым знака доллара нет, будет изменяться. Например, изменим формулу в ячейке С1 так: =A$1+$B1. При протягивании вниз в ячейке С2 получим =A$1+$B2 (в ссылке A$1 номер строки является абсолютным, поэтому он не поменялся, а в ссылке $B2 номер строки является относительным, перед ним знака доллара нет, поэтому он поменялся). При протягивании вправо получим в ячейке D1 формулу =B$1+$B1 (в первой ссылке столбец относительный, без доллара, он поменялся, а во второй ссылке столбец абсолютный, он не поменялся).

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

Но иногда требуется сослаться на ячейки других листов. В этом случае адрес ячейки содержит еще и обозначение листа, и называется объемным. Например, Лист1!С5. В объемном адресе, так же как и в плоском, можно использовать абсолютные и относительные ссылки.

Задание

· Составьте таблицу для перевода цены товара в долларах в рубли. Для этого в столбце А (начиная с А1) введите названии товаров, в столбце В – их цену в долларах, в ячейке F1 – курс доллара (например, 26). В ячейке С1 введите формулу для пересчета цены из долларов в рубли (=А1*$F$1). Протяните эту формулу вниз. Объясните, почему ссылка на ячейку F1 - абсолютная

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

· Составьте таблицу для перевода градусов в радианы без использования функции (в одном градусе π/180 радиан, число π=3,1415, коэффициент для перевода надо записать в какую-то ячейку). Значения градусов должны изменяться от 0 до 90 с шагом 5 градусов (используйте автозаполнение). В таблице будет две колонки – в одной градусы, в другой формула для пересчета в радианы

· Составьте таблицу для перевода скорости из узлов в м/с (1 узел – это одна морская миля в час, то есть 1852 м/час). Коэффициент для пересчета запишите в какую-нибудь ячейку таблицы. Скорость в узлах должна задаваться от 0 до 20 узлов с шагом 1 (используйте атозаполнение)

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

· Составьте таблицу квадратов натуральных чисел от 1 до 55. Выглядеть она должна следующим образом:

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

Сортировка и фильтрация.

Excel позволяет отсортировать данные в зависимости от значений в каком-либо столбце. Для этого надо выделить диапазон ячеек, подлежащих сортировке, и выбрать пункт меню «Данные/Сортировка». В появившемся диалоговом окне надо выбрать столбец, по которому осуществляется сортировка, и порядок сортировки (по возрастанию или убыванию), после чего нажать ОК. Данные будут отсортированы. Можно задать несколько столбцов, по которым будет осуществляться сортировка. В этом случае те строки, для которых значения в первом столбце одинаковы, будут отсортированы по значениям второго столбца.

Например, данная таблица отсортирована по фамилиям, а если фамилии одинаковы, то по именам:

Excel может также отображать на экране только те данные, которые удовлетворяют определенным условиям. Для этого надо выделить диапазон ячеек и выбрать пункт меню «Данные/Фильтрация/Автофильтр». Рядом с заголовками столбцов появятся кнопки со стрелками. При нажатии на кнопку появится список, содержащий все значения столбца, а также пункты «все» и «условие». При выборе какого-то значения из списка на экране остаются только те строчки, которые содержат выбранное значение в столбце. Например, в этой таблице осталась только информация о студентах с фамилией «Сергеев»

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

то в таблице будут отображаться строки о студентах с фамилией «Сергеев» или «Сергеева»:

Это же условие можно задать проще, если для фамилии выбрать условие «Начинается с…», а в качестве значения задать «Сергеев»

При выборе пункта «все» будут показываться все строчки.

Можно задавать условия фильтрации для разных столбцов.

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

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

• Создайте следующую таблицу:

Отсортируйте ее по фамилиям в алфавитном порядке. Должно получиться так:

• Отсортируйте ее по возрасту (то есть по дате рождения в обратном порядке). Должно получиться так:

• Отобразите информацию только о сотрудниках, зарплата которых менее 12000 руб.
• Установите для отображенных строк синюю заливку.
• Отобразите информацию только о сотрудниках, зарплата которых более 25000 руб.
• Установите для отображенных строк красную заливку.
• Отобразите все записи. Должно получиться так:

• Очистите формат ячеек
• Отобразите только информацию о водителях, зарплата которых более 13000 руб., и установите для этих строчек синюю заливку
• Отобразите только информацию о менеджерах с зарплатой от 10 до 11 тыс руб и установите для этих строчек красную заливку
• Отобразите все записи

«Логические функции MS Excel 2003»

Выполнив задания этой темы, вы научитесь:

Технологии создания и форматирования табличного документа;

Использовать встроенные логические функции Excel 2003 для расчетов.

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

Технология работы:

Запустить приложение Excel 2003.

В столбец A ввести фамилии продавцов в соответствии с образцом (см. рис.). В 1 строку введите текст шапки таблицы. В столбец B введите значения объема продаж.

• Правило 1. Если объем продаж меньше 20000, то комиссионные составляют 10% от его объема, а если не меньше 20000, то 20%.

• Правило 2. Если объем продаж меньше 20000, то комиссионные составляют 10% от его объема, если больше 20000, но меньше 30000, то 20%, а если больше 30000, то 30%.

Для расчетов комиссионных по первому правилу в ячейку С2 введите формулу =ЕСЛИ(В2<20000;В2*0,1;B2*0,2). Для этого: Щелкните по кнопке Вставка функции .В окне мастера функций выберите Категория – Логические. Выберите функцию ЕСЛИ – Ok. Появится окно Аргументы функции, в окне Логическое выражение введите выражение из скобок. Затем скопируйте эту формулу в диапазон С2:С6 или распространите ее на столбец C.

Для расчетов комиссионных по второму правилу в ячейку D2 введите формулу
=ЕСЛИ(В2<20000;В2*0,1;ЕСЛИ(И(В2>=20000;В2<30000);В2*0,2;
ЕСЛИ(В2>=30000;В2*0,3))). Скопируйте эту формулу в ячейки D2:D6.

Формула для расчетов комиссионных по второму правилу довольно сложна и состоит из нескольких вложенных функций ЕСЛИ. Того же результата можно достичь не за счет не суперпозиции этих функций, а их сложения. В ячейку Е2 введите следующую формулу: =ЕСЛИ(В2<20000;В2*0,1;0)+ЕСЛИ(И(В2>=20000;В2<30000);В2*0,2;0)+
ЕСЛИ(В2>30000;В2*0,3;0). Она будет давать тот же результат, что и формула в столбце D. Так же скопируйте эту формулу в ячейки Е2:Е6.

В заключение отберем тех менеджеров, которые по результатам продаж добились лучших результатов. С этой целью в ячейку F2 введите формулу =ЕСЛИ(В2=МАКС ($2:$6);"Лучший";""), а затем скопируйте ее в диапазон F2:F6.

Оформим таблицу в соответствии с образцом. Выделите всю таблицу Формат – Ячейки – Граница. Щелкните Внешние и Внутренние. Тип линии – Двойная. Вновь щелкните по кнопке Внешние границы.

Выделите 1 столбец. Формат – Ячейки – Вид – Заливка ячеек. Выберите цвет заливки, но не слишком насыщенный, иначе не будет видно текст, он будет сливаться с заливкой. Аналогично измените заливку 1 строки таблицы.

Измените начертание текста на полужирный в 1 столбце и 1 строке.

Удалим сетку с листа. Сервис – Параметры – Вид - Параметры окна. Снимите флажок – Сетка.

Разобьем лист на страницы. Сервис – Параметры – Вид – Авторазбиение на страницы.

Excel 2003 предоставляет возможность оформить фон всего листа. Формат – Лист – Подложка. Выберите рисунок, который будет являться фоном таблицы.

Задания для контрольной работы

Задание 1

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