Практическое занятие №16

Практическое занятие №16

Тема: Относительная и абсолютная адресация в табличном процессоре MS Excel. Фильтры

Цель работы: изучение информационной технологии организации расчетов с относительной и абсолютной адресацией данных в таблицах MS Excel.

Время работы: 2 часа.

Оборудование: Компьютер с установленным программным обеспечением и подключенный к Internet

Ход работы

Теоретические сведения:

Относительные ссылки

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

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

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

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

Абсолютные ссылки

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

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

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

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

Смешанные ссылки

Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется.

Задание 1

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

Задание 2

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

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

1. Внести в таблицу начальные значения для параметра С (значение равно 6,5) и цены (значение равно 2,8).

2. Заполнить временной столбец n значениями от 0 до 100.

3. Произвести по формуле расчет величины динамики установления равновесия

4. Рассчитать среднюю цену и дисперсию цены, по соответствующим формулам.

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

6. Изменяя начальные значения параметра С, выявить влияние параметра С на процесс установления равновесной рыночной цены.

Задание 3.

Переименуйте новый лист в книге Excel, например, назовите его Ссылки. Зайдите в него, и в ячейки от A1 до A5, а также от B1 до B5, введите какие-нибудь числа. В ячейке C1 напишите: =A1+B1 Нажмите Enter. Ячейка покажет сумму.

Теперь выделите эту ячейку, наведите курсор на нижний правый угол (там, где стоит точка), нажмите левой клавишей мыши и, не отпуская, протяните вниз до ячейки C5. В ячейках от C1 до C5 появятся суммы, причем в ячейке C2 будет сумма ячеек A2 и B2, в ячейке C3 будет сумма ячеек A3 иB3 и так далее. То же самое произойдет, если Вы скопируете ячейку C1 в ячейку C5, например. Вы видите, что адреса ячеек в формулах изменяются. Это потому, что данные адреса ячеек в формулах являются относительными ссылками Excel.

Теперь представьте себе ситуацию: все ячейки с суммой нужно умножить на содержимое ячейки D2. Введите в ячейку D2 какое-нибудь число, в ячейке C1 вставьте курсор в строку формул Excel, заключите сумму в скобки, и допишите *D2. Должно получиться: =(A1+B1)*D2 Результат в ячейке C1 Вы увидите, но если Вы скопируете ячейку C1 в ячейки ниже, ничего не получится, потому что ссылка на ячейку D2 превратится в ссылку на ячейку D3 и так далее.

Как быть в этой ситуации? Нужно относительную ссылку D2превратить в абсолютную. В абсолютную ссылку Excel она превращается путем добавления знака $ перед D и перед 2, то есть абсолютная ссылка выглядит так: $D$2 То есть в ячейке C1формула должна выглядеть так: =(A1+B1)*$D$2

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

Кроме относительных и абсолютных ссылок в Excel есть еще смешанные ссылки вида: $D2 или D$2 Для иллюстрации работы со смешанными ссылками Excelсделаем таблицу умножения. Создайте новый лист, на нем в ячейку A1 поставьте цифру 1,  в ячейку B1 поставьте цифру 2, выделите обе ячейки, наведите курсор на точку в правом нижнем углу обрамления, и протяните в сторону, до ячейки I1. У Вас получится ряд цифр от 1 до 9. Точно так же поставьте цифры от 1 до 9 в ячейки отA1 до A9. В ячейку B2 поставьте: =B1*A2 и протяните до ячейки I9 (сразу не получится, протяните сначала по горизонтали, потом по вертикали). То, что Вы увидите, явно не будет таблицей умножения, потому что относительные ссылки Excel в формуле каждой ячейки изменяются не так, как нам нужно.

Например, в ячейке C3 будет: =C2*B3 А должно быть: =C1*A3

Заметьте,  при переходе из ячейкиB2 в ячейку C3 в формуле

первый множитель B1 должен был преобразоваться в C1, а

второй множитель A2 должен был преобразоваться в A3.

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

Теперь измените формулу в ячейке B2, чтобы она была такой:

=B$1*$A2 Таким образом, Вы делаете неизменными в первом множителе букву, а во втором множителе — цифру с помощью смешанных ссылок Excel. Протяните теперь ячейку B2 до ячейки I9. Вы увидите, что результат будет достигнут: таблица умножения будет сделана правильно.

Задание 4.

1.Наберите таблицу в соответствии с рисунком 1

2.Выделите его.

3.Нажмите кнопку "Сортировка и фильтр" на панели "Редактирование" ленты "Главная".

Рисунок 1 - Таблица

4.Выберите "Сортировка от А до Я". Наш список будет отсортирован по первому столбцу, т.е. по полю ФИО.

5.Если надо отсортировать список по нескольким полям, то для этого предназначен пункт "Настраиваемая сортировка..". Сложная сортировка подразумевает упорядочение данных по нескольким полям. Добавлять поля можно при помощи кнопки "Добавить уровень".Рис3,4

Рис.3

Рис.4.

6.В итоге список будет отсортирован, согласно установленным параметрам сложной сортировки(рис. 5)

Рис.5.

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

Перемещать уровни сортировки можно при помощи кнопок "Вверх" и "Вниз".

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

Контрольные вопросы:

1.Какие данные можно вводить в ячейку? Как Excel определяет, что введён текст, а не формула?

2.Как ввести и отредактировать формулу?

3.Дайте определения абсолютной и относительной адресаций. Чем отличаются эти способы адресации ячеек?

4.Что такое функция? Какими способами можно вставить функцию в формулу?

5.Как можно просуммировать содержимое ячеек?

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

7.Чем отличается условное форматирование от обычного форматирования? Как производится условное форматирование?

8.Как защитить лист от изменений?