Рассчитать графу стоимость р используя курс доллара как абсолютный адрес excel
Тема. Абсолютный адрес в MS Excel.
Цель. Приобрести и закрепить практические навыки по применению абсолютной адресации при расчёте электронной таблицы.
Задание 1. Создать и заполнить таблицу расчёта доходов, показанную на рисунке.
| A | B | C | D | E |
| Распределение доходов в зависимости от КТУ | ||||
| Общий доход | ||||
| Фамилия | Время, ч | Квалификационнй разряд | КТУ | Сумма к выдаче |
| Сотрудник 1 | ||||
| Итого |
Алгоритм выполнения задания.
Выделить ячейку Е4.
В строке формул отображается формула из этой ячейки, щёлкнуть по адресу В2 в этой формуле, нажать клавишу F4, у обозначения адреса появятся значки $B$4, щёлкнуть по обозначению адреса D14, нажать клавишу F4, у обозначения адреса появятся значки $D$14.
Выполнить заново операцию Автозаполнение для графы Сумма к выдаче (вместе с ячейкой Итого).
В ячейке Итого должна получиться сумма, равная Общему доходу.
Присвоить денежным величинам обозначение в рублях, для этого выделить ячейку В2, щёлкнуть кнопку Денежный формат на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
- Денежный, установить в поле Обозначение тип р.
- Для проверки возможности автоматического перерасчёта таблицы заменить значения Квалификацилннлгл разряда, Времени, затраченного некоторыми сотрудниками, а также величины Общего дохода, например на 25000 р.
- Установить для графы Сумма к выдаче отображение с двумя десятичными разрядами, для этого выделить диапазон ячеек Е4:Е14, щёлкнуть на кнопке Увеличить разрядность на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
/Денежный, установить в поле Число десятичных знаковчисло 2.
Задание 2. Создать и заполнить таблицу расчёта стоимости, показанную на рисунке.
| A | B | C | D | E |
| Стоимость программного обеспечения | ||||
| Наименование | Стоимость, $ | Стоимость, р. | Стоимость, Евро | Доля в общей стоимости, % |
| OC Windows | ||||
| Пакет MS Office | ||||
| Редактор Corel Draw | ||||
| Графический ускоритель 3D | ||||
| Бухгалтерия 1С | ||||
| Антивирус DR Web | ||||
| Итого | ||||
| Курс валюты (к рублю) |
Алгоритм выполнения задания.
- Записать исходные текстовые и числовые данные.
- Рассчитать графу Стоимость, р., используя курс доллара как абсолютный адрес.
- Рассчитать графу Стоимость, Евро,используя курс доллара и курс Евро как абсолютные адреса.
- Рассчитать графу Доля в общей стоимости, используя итоговую Стоимость, р. как абсолютный адрес.
- Преобразовать числовые значения в графе Доля в общей стоимостив процентные значения:
Выделить числовые значения этой графы.
Щёлкнуть по кнопке Процентный формат.
Установить отображение процентов с одним десятичным знаком, используя кнопки Увеличить или Уменьшить разрядность.
Приобрести и закрепить практические навыки по применению абсолютной адресации при расчёте электронной таблицы.
Задание 1. Создать и заполнить таблицу расчёта доходов, показанную на рисунке.
Алгоритм выполнения задания
1. Записать исходные значения таблицы, указанные на рисунке.
2. Заполнить графу Фамилия значениями Сотрудник 1÷10, используя операцию Автозаполнение.
3. Рассчитать графу КТУ как произведение времени, затраченного сотрудником, на его квалификационный разряд (формула =В4*С4).
4. Подсчитать значение Итого с помощью операции Автосумма.
5. Графа Сумма к выдаче рассчитывается как произведение общего дохода на отношение КТУ данного сотрудника к итоговому КТУ (формула =В2*D4/D14).
7. Для правильного расчёта необходимо зафиксировать адреса В2 и D14, для этого:
Выделить ячейку Е4.
В строке формул отображается формула из этой ячейки, щёлкнуть по адресу В2 в этой формуле, нажать клавишу Shift-F4, у обозначения адреса появятся значки $B$4, щёлкнуть по обозначению адреса D14, нажать клавишу Shift-F4, у обозначения адреса появятся значки $D$14.
Выполнить заново операцию Автозаполнение для графы Сумма к выдаче (вместе с ячейкой Итого).
В ячейке Итого должна получиться сумма, равная Общему доходу.
8. Присвоить денежным величинам обозначение в рублях, для этого выделить ячейку В2, щёлкнуть кнопку Денежный формат на панели инструментов Форматирование или выполнить команду Формат/Ячейки/Число/Денежный, установить в поле Обозначение тип руб.
9. Для проверки возможности автоматического перерасчёта таблицы заменить значения Квалификационного разряда, Времени, затраченного некоторыми сотрудниками, а также величины Общего дохода, например на 25000 р.
10. Установить для графы Сумма к выдаче отображение с двумя десятичными разрядами, для этого выделить диапазон ячеек Е4:Е14, щёлкнуть на кнопке Увеличить разрядность на панели инструментов Форматирование или выполнить команду Формат/Ячейки/Число/Денежный, установить в поле Число десятичных знаков число 2.
Задание 2. Создать и заполнить таблицу расчёта стоимости, показанную на рисунке.
Алгоритм выполнения задания
1. Записать исходные текстовые и числовые данные.
2. Рассчитать графу Стоимость, р., используя курс доллара как абсолютный адрес.
3. Рассчитать графу Стоимость, Евро, используя курс доллара и курс Евро как абсолютные адреса.
4. Рассчитать графу Доля в общей стоимости, используя итоговую Стоимость, р. как абсолютный адрес.
5. Преобразовать числовые значения в графе Доля в общей стоимости в процентные значения:
6. Выделить числовые значения этой графы.
7. Щёлкнуть по кнопке Процентный формат.
Установить отображение процентов с одним десятичным знаком, используя кнопки Увеличить или Уменьшить разрядность.
1. Для чего используются абсолютные и относительные адреса ячеек?
2. В чём смысл правил автоматической настройки формул при выполнении операций копирования и перемещения?
Цель. Приобрести и закрепить практические навыки по применению абсолютной адресации при расчёте электронной таблицы.
Задание 1. Создать и заполнить таблицу расчёта доходов, показанную на рисунке.
Теоретические сведения
Ссылка в редакторе Excel однозначно определяет ячейку таблицы или группу ячеек рабочего листа. Ссылки указывают на то, в каких ячейках находятся значения, которые нужно применить в качестве операндов формулы. В формуле при помощи ссылок можно использовать данные, находящиеся в различных местах рабочего листа. Кроме того, можно использовать значение одной и той же ячейки в нескольких формулах.
При помощи ссылок можно также ссылаться на ячейки, находящиеся на других листах рабочей книги или в другой рабочей книге, либо даже на данные другого приложения.
После того как формула введена в ячейку, эту формулу можно перенести, скопировать или распространить на блок ячеек. Копирование и перемещение ячеек с формулами выполняется так же, как и копирование и перемещение ячеек с данными.
При перемещении формулы из одной ячейки в другую ссылки не изменяются, в то время как при копировании они автоматически изменяются.
В случае, если ссылки автоматически корректируются при копировании формулы из одной ячейки в другую, они называются относительными. По умолчанию в формулах используются именно они.
Например, если в ячейке А3 была записана формула =А1*А2, то при копировании содержимого АЗ в ячейки ВЗ и СЗ новые формулы с обновленными ссылками примут следующий вид: = В1*В2, =С1*С2 (рис. а).
Кроме относительных ссылок, в редакторе Excel часто используются абсолютные ссылки, где кроме названия столбца и номера строки используется специальный символ «$», который фиксирует часть ссылки (столбец, строку) и оставляет ее неизменной при копировании формулы с такой ссылкой в другую ячейку. Обычно абсолютные ссылки указывают на ячейки, в которых содержатся константы, используемые при вычислениях.
 |  |
| a | б |
Рис.Использование ссылок а– относительных; б – абсолютных
Например, если необходимо зафиксировать в формуле =А1*В1 значение ячейки А1 (рис. б), которое не должно изменяться в случае копирования данной формулы, то абсолютная ссылка на эту ячейку будет иметь следующий вид: $А$1. Таким образом, при копировании формулы из ячейки В2 в ячейку С2 формула примет вид =$А$1*С1.
Если требуется зафиксировать в ссылке только строку или только столбец, в котором находится используемая ячейка, в частности столбец А или строку 1, то ссылка примет вид $А1 или А$1 соответственно.
Изменить тип ссылки можно следующим образом:
1. Выделить ячейку с формулой.
2. В строке формул выделить ссылку, которую нужно изменить.
3. Нажатием клавиши F4выбрать требуемый тип ссылки.
Последовательность изменения типов ссылок для ячейки А1 при использовании клавиши F4такая:
· $А$1 - абсолютная ссылка (фиксированная ячейка);
· А$ 1 - изменяемый столбец и неизменяемая строка;
· $А1 - неизменяемый столбец и изменяемая строка;
· А1 - относительная ссылка.
В формулах можно использовать ссылки на ячейки как текущего листа, так и других листов рабочей книги. Например, ссылка на ячейку А1, расположенную на листе с именем Лист1, будет выглядеть так: Лист1!А1.
Формулы со ссылками могут быть получены двумя способами:
· путем непосредственного ввода ссылок с клавиатуры (вводятся латинскими буквами), что часто используется при редактировании формул;
· щелчком мыши по ячейкам, значения которых принимают участие в вычислениях.
Второй способ включает в себя следующие действия:
1. В режиме редактирования установить курсор в ту часть формулы, куда необходимо вставить ссылку, но обязательно после математического оператора или скобки.
2. Чтобы при построении формулы создать ссылку на данные текущего листа, необходимо выполнить щелчок мышью по ячейке с ними или выделить требуемый диапазон ячеек. Если данные расположены на другом рабочем листе, сначала выполнить переход на нужный лист щелчком мыши по его ярлыку внизу экрана, а затем указать ячейки с данными. Аналогично можно сослаться и на данные, содержащиеся в другой книге.
3. Нажать клавишу Enter.
Порядок выполнения работы
| A | B | C | D | E |
| Распределение доходов в зависимости от КТУ | ||||
| Общий доход | ||||
| Фамилия | Время, ч | Квалификационнй разряд | КТУ | Сумма к выдаче |
| Сотрудник 1 | ||||
| Итого |
Алгоритм выполнения задания.
1. Записать исходные значения таблицы, указанные на рисунке.
2. Заполнить графу Фамилия значениями Сотрудник 1÷10, используя операцию Автозаполнение.
3. Рассчитать графу КТУ как произведение времени, затраченного сотрудником, на его квалификационный разряд (формула =В4*С4).
4. Подсчитать значение Итого с помощью операции Автосумма.
5. Графа Сумма к выдаче рассчитывается как произведение общего дохода на отношение КТУ данного сотрудника к итоговому КТУ (формула =В2*D4/D14).
7. Для правильного расчёта необходимо зафиксировать адреса В2 и D14, для этого:
Выделить ячейку Е4.
В строке формул отображается формула из этой ячейки, щёлкнуть по адресу В2 в этой формуле, нажать клавишу F4, у обозначения адреса появятся значки $B$4, щёлкнуть по обозначению адреса D14, нажать клавишу F4, у обозначения адреса появятся значки $D$14.
Выполнить заново операцию Автозаполнение для графы Сумма к выдаче (вместе с ячейкой Итого).
В ячейке Итого должна получиться сумма, равная Общему доходу.
Присвоить денежным величинам обозначение в рублях, для этого выделить ячейку В2, щёлкнуть кнопку Денежный формат на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
8. Денежный, установить в поле Обозначение тип р.
9. Для проверки возможности автоматического перерасчёта таблицы заменить значения Квалификационного разряда, Времени, затраченного некоторыми сотрудниками, а также величины Общего дохода, например на 25000 р.
10. Установить для графы Сумма к выдаче отображение с двумя десятичными разрядами, для этого выделить диапазон ячеек Е4:Е14, щёлкнуть на кнопке Увеличить разрядность на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
/Денежный, установить в поле Число десятичных знаковчисло 2.
Задание 2. Создать и заполнить таблицу расчёта стоимости, показанную на рисунке.
| A | B | C | D | E |
| Стоимость программного обеспечения | ||||
| Наименование | Стоимость, $ | Стоимость, р. | Стоимость, Евро | Доля в общей стоимости, % |
| OC Windows | ||||
| Пакет MS Office | ||||
| Редактор Corel Draw | ||||
| Графический ускоритель 3D | ||||
| Бухгалтерия 1С | ||||
| Антивирус DR Web | ||||
| Итого | ||||
| Курс валюты (к рублю) |
Алгоритм выполнения задания.
1. Записать исходные текстовые и числовые данные.
2. Рассчитать графу Стоимость, р., используя курс доллара как абсолютный адрес.
3. Рассчитать графу Стоимость, Евро,используя курс доллара и курс Евро как абсолютные адреса.
4. Рассчитать графу Доля в общей стоимости, используя итоговую Стоимость, р. как абсолютный адрес.
5. Преобразовать числовые значения в графе Доля в общей стоимостив процентные значения (Правой кнопкой мыши/Формат Ячеек/Число …):
Выделить числовые значения этой графы.
Щёлкнуть по кнопке Процентный формат.
Установить отображение процентов с одним десятичным знаком, используя кнопки Увеличить или Уменьшить разрядность.
Контрольные вопросы
1. Для чего используются абсолютные и относительные адреса ячеек?
2. В чём смысл правил автоматической настройки формул при выполнении операций копирования и перемещения?
Применение абсолютной адресации
Абсолютная адресация нам может понадобиться, например, в том случае, когда мы копируем формулу, одна часть которой состоит из переменной, отображаемой в ряду чисел, а вторая имеет постоянное значение. То есть, данное число играет роль неизменного коэффициента, с которым нужно провести определенную операцию (умножение, деление и т.д.) всему ряду переменных чисел.
В Excel существует два способа задать фиксированную адресацию: путем формирования абсолютной ссылки и с помощью функции ДВССЫЛ. Давайте рассмотрим каждый из указанных способов подробно.
Способ 1: абсолютная ссылка
Безусловно, самым известным и часто применяемым способом создать абсолютную адресацию является применение абсолютных ссылок. Абсолютные ссылки имеют отличие не только функциональное, но и синтаксическое. Относительный адрес имеет такой синтаксис:
У фиксированного адреса перед значением координат устанавливается знак доллара:
Существует и более быстрый способ. Нужно установить курсор в ячейку, в которой находится адрес, и щелкнуть по функциональной клавише F4. После этого знак доллара моментально появится одновременно перед координатами по горизонтали и вертикали данного адреса.
Теперь давайте рассмотрим, как применяется на практике абсолютная адресация путем использования абсолютных ссылок.
Возьмем таблицу, в которой рассчитывается заработная плата работников. Расчет производится путем умножения величины их личного оклада на фиксированный коэффициент, который одинаков для всех сотрудников. Сам коэффициент расположен в отдельной ячейке листа. Перед нами стоит задача рассчитать заработную плату всех работников максимально быстрым способом.
-
Итак, в первую ячейку столбца «Заработная плата» вводим формулу умножения ставки соответствующего работника на коэффициент. В нашем случае эта формула имеет такой вид:
В Экселе также применяется, так называемая смешанная адресация. В этом случае в адресе элемента фиксируется либо столбец, либо строка. Достигается это таким образом, что знак доллара ставится только перед одним из координат адреса. Вот пример типичной смешанной ссылки:
Этот адрес тоже считается смешанным:
То есть, абсолютная адресация в смешанной ссылке используется только для одного из значений координат из двух.
Посмотрим, как такую смешанную ссылку можно применить на практике на примере все той же таблицы заработной платы сотрудников предприятия.
-
Как видим, ранее мы сделали так, что все координаты второго множителя имеют абсолютную адресацию. Но давайте разберемся, обязательно ли в этом случае оба значения должны быть фиксированными? Как видим, при копировании происходит смещение по вертикали, а по горизонтали координаты остаются неизменными. Поэтому вполне можно применить абсолютную адресацию только к координатам строки, а координаты столбца оставить такими, каковыми они являются по умолчанию – относительными.
Выделяем первый элемент столбца «Заработная плата» и в строке формул выполняем вышеуказанную манипуляцию. Получаем формулу следующего вида:
Способ 2: функция ДВССЫЛ
Вторым способом организовать абсолютную адресацию в таблице Excel является применение оператора ДВССЫЛ. Указанная функция относится к группе встроенных операторов «Ссылки и массивы». Её задачей является формирование ссылки на указанную ячейку с выводом результата в тот элемент листа, в котором находится сам оператор. При этом ссылка прикрепляется к координатам ещё крепче, чем при использовании знака доллара. Поэтому иногда принято называть ссылки с использованием ДВССЫЛ «суперабсолютными». Этот оператор имеет следующий синтаксис:
Функция имеет в наличии два аргумента, первый из которых имеет обязательный статус, а второй – нет.
Аргумент «Ссылка на ячейку» является ссылкой на элемент листа Excel в текстовом виде. То есть, это обычная ссылка, но заключенная в кавычки. Именно это и позволяет обеспечить свойства абсолютной адресации.
Взглянем, как будет работать абсолютная адресация, организованная при помощи функции ДВССЫЛ, на примере нашей таблицы заработной платы.
-
Производим выделение первого элемента столбца «Заработная плата». Ставим знак «=». Как помним, первый множитель в указанной формуле вычисления зарплаты должен быть представлен относительным адресом. Поэтому просто кликаем на ячейку, содержащую соответствующее значение оклада (C4). Вслед за тем, как её адрес отобразился в элементе для вывода результата, жмем на кнопку «умножить» (*) на клавиатуре. Затем нам нужно перейти к использованию оператора ДВССЫЛ. Выполняем щелчок по иконке «Вставить функцию».
Ставим курсор в поле «Ссылка на ячейку». Просто кликаем по тому элементу листа, в котором находится коэффициент для расчета зарплаты (G3). Адрес тут же появится в поле окна аргументов. Если бы мы имели дело с обычной функцией, то на этом введение адреса можно было бы считать завершенным, но мы используем функцию ДВССЫЛ. Как мы помним, адреса в ней должны иметь вид текста. Поэтому оборачиваем координаты, которые расположись в поле окна, кавычками.
Абсолютную адресацию в таблицах Excel можно обеспечить двумя способами: использование функции ДВССЫЛ и применение абсолютных ссылок. При этом функция обеспечивает более жесткую привязку к адресу. Частично абсолютную адресацию можно также применять при использовании смешанных ссылок.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Читайте также: