Работа с ссылками в excel практическая работа

Практическая
работа по теме: «Абсолютные и относительные ссылки в MS Excel»

Цели выполнения практической работы:

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

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

Задание:

1.   
Прочитайте представленный теоретический
материал для выполнения практической работы.

2.   
Создать предложенную таблицу по
образцу (рисунок 21.3).

3.   
Произвести расчёты начисления заработной
платы за февраль по формулам.

4.   
Ответьте на контрольные вопросы в конце работы с целью самопроверки.

Теоретические основы для
выполнения практической работы

Электронные таблицы
– это программа для автоматической обработки данных, представленных в виде таблицы.

Основные  типы  данных.

1
тип —         
ЧИСЛА – записываются
в обычном числовом или экспоненциальном формате (обычный – 195,2; экспоненциальный
– 1,952Е+02). По умолчанию числа выравниваются по правому краю.

2
тип —         
ТЕКСТ – последовательность
символов, состоящая из букв, цифр, пробелов и других знаков. По умолчанию выравнивается
по левому краю.

3
тип —         
ФОРМУЛА – начинается
со знака равенства и может включать в себя числа, имена ячеек, функции и знаки математических
операций. Формула вводится только на английском языке. В  ячейке отображается результат
вычислений, а формула – в строке формул. При изменении исходных данных, результат
пересчитывается автоматически.

Автозаполнение ячеек

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

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

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

Алгоритм работы с границами ячеек

Выделить диапазон ячеек, которые должны быть обрамлены линиями. Открыть
окно «Формат ячеек» на вкладке «Граница». Указать тип линий и сами линии все сразу
или по отдельности. Закрыть окно щелчком по кнопке «ОК». Установить границы можно
с помощью инструмента «Границы», который расположен на панели инструментов «Главная».

Рисунок 21.1 – Инструмент Границы

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

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

Быстрая сортировка

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

2.        
Нажмите кнопку , чтобы выполнить сортировку по возрастанию (от А
до Я или от наименьшего числа до наибольшего).

3.        
Нажмите кнопку , чтобы выполнить сортировку по убыванию (от Я до
А или от наибольшего числа до наименьшего).

Алгоритм выполнения сортировки по указанным условиям

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

2.        
На вкладке Данные в группе Сортировка
и фильтр выберите команду Сортировать (рисунок 21.2)

Рисунок 21.2 – Команда Сортировка
и Фильтр

Порядок выполнения работы:

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

Рисунок
21.3 – Ведомость начисления заработной платы

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

Премия = Оклад * %Премии

Всего начислено = Оклад + Премия

Удержания = Всего начислено * %Удержания

К выдаче = Всего начислено – Удержания

3.        
В
строку Всего (по окладу) ввести формулу (21.1)

=СУММ(C5:C18)
(21.1)

4.        
Выполнить
автозаполнение формулой в соответствующие столбцы.

5.        
Переименовать
ярлык Листа 1, присвоив ему имя «Зарплата за февраль», вызвать на ярлыке
листа контекстное меню, выбрать команду Переименовать,

6.        
Скопировать
содержимое листа «Зарплата за февраль» на новый лист, присвоить скопированному листу
название «Зарплата за январь», изменить порядок листов по месяцам. Исправить название
месяца в названии таблицы.

7.        
Изменить
значение премии на 32%. Убедится, что был произведен перерасчет формул.

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

9.        
Рассчитать
значение доплаты по формуле (Значение доплаты принять равным 5%)

Доплата = Оклад * %Доплаты

10.     Изменить формулу
для расчета значений колонки «Всего начислено»:

Всего начислено = Оклад + Премия + Доплата

11.     Провести сортировку
по фамилиям в алфавитном порядке по возрастанию, используя инструмент «Сортировать
по».

12.     Сохранить электронную
книгу под именем Практическая работа № 21. 

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

1.        
Михеева Е.В., Титова О.И.: Информатика: учебник.- М.: Академия, 2016

2.        
Михеева Е.В.: Практикум по информатике: уч.пособие.– М.: Академия,
2012

Контрольные
вопросы с целью самопроверки:

1.        
Какие типы ссылок применяются для
расчета премии?

2.        
Для какой цели применяется функция
автозаполнения данными (формулой)?

3.        
Какой способ для изменения процентного
формата числа?

4.        
С помощью какого инструмента выполняется
сортировка по алфавиту?

Примерный
вариант оформления результатов работы

Скопируем формулу, вычисляющую цену в рублях, в нижележащие ячейки с

использованием операции заполнения диапазона.

4. Подсчитаем в ячейке С12 общую цену компьютера с помощью встроенной

функции СУММ().

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

формате.

6. Выделить диапазон ячеек С2:С12 и ввести команду Главная —Формат.

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

Повторить процедуру для ячейки Е2.

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

изменяются, а абсолютные не изменяются.

Занятие 3 (на оценку 5). Создайте ЭТ «Доставка груза» по образцу и

вычислить стоимость доставки и всего:

ПРИМЕЧАНИЕ:

Формула в ячейке F4 вводится с учетом того, что стоимость доставки груза

складывается из следующих трех величин (наценок):

А) 5% от стоимости груза,

Б) от расстояния (3р./км).

В) номера этажа (2,5р./этаж)

Практическая работа.

«Microsoft Excel 2007. Абсолютная и относительная
адресация»

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

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

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

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

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

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

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

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

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

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

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

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

Индивидуальное задание

Задание 1

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

Задание 2

Создайте следующую таблицу. Заполните нужные ячейки
формулами, воспользуйтесь относительными, абсолютными или смешанными ссылками
при автозаполнении формул. Для товаров, стоимость которых с учетом их
количества превышает 500$, установите скидку в 1%, используя функцию «ЕСЛИ»
(информацию о данной функции найдите в справке).

Расчет приобретенных компанией канцелярских средств
оргтехники

Курс $ = 26,89 руб.

Задание 3

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

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

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

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

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

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

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

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

Задание
№4.

 Переименуйте новый лист в книге 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. Вы
увидите, что результат будет достигнут: таблица умножения будет сделана
правильно.

Скачано
с www.znanio.ru

1)
Создайте электронную таблицу учета
платы за квартиру согласно образцу
(рис.1):

Рис.
1.
Исходная таблица данных.

2)
Выберите для столбцов нужную ширину
(учтите стоимость в $ и длину полосы).

3)
Заполните таблицу формулами:

а)
стоимость 1/2 полосы = 0,5 цены за полосу;

б)
стоимость 1/4 полосы = 0,25 цены за полосу;

в)
стоимость заказа зависти от длины
полосы;

г)
НДС равно 20% стоимости заказа;

д)
надбавки вычисляются с учетом номера
полосы:

1
полоса – 100% стоимости заказа;

2
полоса – 50% стоимости заказа;

3
полоса – 25% стоимости заказа;

е)
итого в $ вычисляется по формуле: Стоимость
заказа + НДС + Надбавки;

5)
Оформите таблицу по окончательному
образцу, приведенному в конце на рис.
2.

Для
этого:

1)
Для заполнения таблицы формулами в
ячейку G8
введите формулу: =0,5*F8;
скопируйте эту формулу в ячейки G9:G24.
В ячейку H8
введите формулу: =
0,25*F8.
Скопируйте ее в ячейки H9:H24.
Для вычисления стоимости заказа, в
зависимости от длины полосы в ячейку
L8
введите формулу:
=ЕСЛИ(J8=0,25;0,25*F8;ЕСЛИ(J8=0,5;0,5*F8;
F8)).

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

ЕСЛИ(J8=0,25;0,25*F8;ЕСЛИ(J8=0,5;0,5*F8;ЕСЛИ(J8=1;
F8; «скидок нет»)))
изменится ли результат вычислений?
Какие отличия в этой формуле вы можете
указать по отношению к указанной в п.1)
задания.

2)
В ячейку M8
введите формулу: =0,2*L8.
Скопируйте ее в ячейки M9:M24.

3)
В ячейку N8
введите формулу:
=ЕСЛИ(I8=1;L8;ЕСЛИ(I8=2;0,5*L8;ЕСЛИ(I8=3;0,25*L8;0))).
Скопируйте ее в ячейки N9:N24.

4)
Чтобы рассчитать итоговую стоимость в
$
в ячейку P8
введите формулу: =L8+M8+N8.
Скопируйте ее в ячейки P9:P24.

5)
Чтобы рассчитать итоговую стоимость в
рублях в ячейку O8
введите формулу: =30,5*P8.
Скопируйте эту формулу в ячейки O9:O24.
Окончательные результаты вычислений
см. на рис. 2.

6)
Оформите таблицу по образцу, не забывая
о соответствующих форматах столбцов
(стоимость в $ и в рублях). Стоимость в $
устанавливается с помощью выбора:
Главная
→ Число → Числовые форматы → Финансовый
→ Обозначение
→
$ английский (США).
Число десятичных знаков выберите 0
(ноль). Рублевый формат выбирается
аналогично или используйте: Главная
→ Число → Числовые форматы → Денежный.

В
колонке «Длина
в полосах»
ячейкам J8:J24
присвойте дробный формат: Главная
→ Число
→ Числовые
форматы
→ Дробный
→ Тип → Простыми дробями 1/4.

Примечание:
в ячейке J8,
J11, J12
и т.д. где длина в полосах указана одной
цифрой, для выравнивания по центру по
горизонтали используйте вкладку
Выравнивание
по горизонтали → по значению.
В ячейках J9,
J10
и т.д., где длина в полосах указана в виде
обыкновенной дроби (1/4, 1/2) установите
Выравнивание
по горизонтали →
по
центру.

Рис.
2.
Образец таблицы «Расценки
на размещение рекламы в центральной
прессе»
с окончательными расчетами и оформлением.

Практическая работа №6. Тема: Работа с датами, объединение текста с данными ячеек, создание собственных форматов.

Немного
теории:

Excel
изображает даты с помощью натуральных
чисел от 1
до 65380.
Летоисчисление начинается с 1
января 1900
года, которое соответствует 1,
и кончается 31
декабря 2078
года (эта дата соответствует числу
65380).

Для
преобразования количества часов, минут
и секунд в значение времени следует
воспользоваться приведенными ниже
формулами. Чтобы преобразовать количество
часов в значение времени следует
разделить это количество на 24.
Например, если ячейка А1
содержит значение 9,25
(часов), то следующая формула вернет
время 9:15:00
(9
часов и 15 минут):
=А1/24.

Чтобы
преобразовать количество минут в
значение времени, это количество следует
разделить на 1440
(число минут в сутках). Если ячейка А1
содержит значение 500
(минут) , то следующая формула вернет
время 8:20:00
(8
часов и 20 минут):
=А1/1440.

Для
преобразования количества секунд в
значение времени, необходимо это
количество разделить на число 86 400
(количество секунд в сутках). Если ячейка
А1
содержит значение 65 000
(секунд), то следующая формула вернет
время 18:03:20
(18
часов, 3 минуты и 20 секунд):
=А1/86400.

Поскольку
время представляется в виде последовательных
чисел, то вычисление разности значений
времени не вызывает затруднений.
Например, если в ячейке А2
хранится время 5:30:00,
а в ячейке B2
– 14:00:00
(разность между ними составляет 8
часов и 30 минут),
то следующая формула вернет значение:
=B2-A2.
Однако, Excel
не может отображать отрицательные
значения времени, и если в результате
вычислений в ячейке получится ######,
то это именно тот случай. Тогда, если
знак разности времен не имеет для вас
значения, то для отображения отрицательных
значений времени можно использовать
функцию ABS
(абсолютная величина), которая отобразит
это значение в виде положительного
времени:
=
ABS(B2-A2).
Проблема «отрицательного времени»
часто возникает при вычислении разности
времен, относящихся к разным суткам.
Например, надо вычислить длительность
рабочего времени ночной смены, которая
начинается в 22:00
и заканчивается в 6:00
на следующий день. Использование
предыдущей формулы вернет значение 16
часов,
а это не верно (длительность смены 8
часов).
В этом случае поможет формула:
=B2+(B2<A2)-A2.
Проверьте правильность последней
формулы самостоятельно.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

Относительная адресация в электронных таблицах

Вид работы: практическая работа 

Автор: Шевченко Е.А.

Предмет: информатика

аудитория: 9 класс

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

Цель: приобрести и закрепить практические навыки по использованию относительной адресации при расчетах в Excel;  отработать применение операции Автозаполнения; систематизировать знания, относящиеся к работе в табличном процессоре Excel; развить алгоритмическое мышление; приобрести опыт работы в Excel

Задачи: освоить, закрепить и систематизировать практические приемы при работе в табличном процессоре (использовать относительную адресацию при расчётах в Excel, операцию Автозаполнение)

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

продолжить формирование умения работать с информацией (сбор, систематизация, хранение, использование)

 Ожидаемые результаты:

формирование устойчивых практических умений и навыков по использованию относительной адресации при выполнении расчетов в Excel, применению операции Автозаполнение; ответственного отношение к обучению;

умение анализировать и делать выводы; действовать по заданному алгоритму, оценивать правильность выполнения задания; 

Задание 1 Создать и заполнить таблицу расчета стоимости 

                                Алгоритм выполнения задания

1. Запишите исходные текстовые и числовые данные

2. Рассчитайте графу Стоимость как произведение количества банок на цену одной банки (формула =B3*C3)

3 Используя операцию Автозаполнение скопируйте формулу в диапазон ячеек (D4:D6)

4 Подсчитайте Суммарный расход и значение Итого используя операцию Автосумма

Задание2 Создать и заполнить таблицу итоги тестирования.

Алгоритм выполнения задания

1. Создайте таблицу по образцу

2 Запишите исходные текстовые и числовые данные

3. Заполните графу № п/п,  используя операцию Автозаполнение

4 Рассчитайте графу Общий балл как сумму баллов за каждое задание (формула =C3+D3+E3+F3)

5 Используя операцию Автозаполнение скопируйте формулу в диапазон ячеек (G4:G10)

Задание3 Создать и заполнить таблицу расчета заработной платы сотрудников

Алгоритм выполнения задания

1. Создайте таблицу по образцу

2 Запишите исходные текстовые и числовые данные

3. Заполните графу № п/п,  используя операцию Автозаполнение

4 Рассчитайте графу Почасовая з/плата как произведение почасовой ставки на количество отработанных часов(формула =C3*D3)

5 Используя операцию Автозаполнение скопируйте формулу в диапазон ячеек (E4:E14)

6 Графа Итого начислено рассчитывается как сумма Почасовой з/платы и % премии от заработной платы (формула =E3+E3*F3)

7 Используя операцию Автозаполнение скопируйте формулу в диапазон ячеек (G4:G14)

Задание4 Создать и заполнить таблицу итогов экзаменов

                                                 Алгоритм выполнения задания

1. Переименуйте Лист2 в Итого экзаменов

2 На листе Итого экзаменов создайте и оформите таблицу по образцу

3 Запишите исходные текстовые и числовые данные

4. Заполните графу № п/п,  используя операцию Автозаполнение

5 Рассчитайте графу Средний балл как среднее арифметическое баллов, полученных за четыре экзамена(формула =(C3+D3+E3+F3)/4)

6 Используя операцию Автозаполнение скопируйте формулу в диапазон ячеек (G4:G8)

Задание5 Создать и заполнить таблицу подсчета количества дней до Нового года

                                                   Алгоритм выполнения задания

1. На  Листе3  создайте таблицу по образцу

2  Запишите исходные текстовые и числовые данные

4. Заполните графу Текущая дата,  датой выполнения практической работы

5 Для ячейки результата установите формат «Общий». 

6 Рассчитайте количество дней до Нового года  разность между 31.12. и текущей датой(формула =B2-B1)

Покажите выполненную практическую работу учителю