Копирование формул в excel информатика

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

• Перемещение формулы При этом ссылки на ячейки в формуле не изменяются независимо от типа ссылки на ячейку.

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

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

1. Вы выберите ячейку с формулой, которую вы хотите переместить.

2. Щелкните Главная > вырезать (или нажмите CTRL+X).

   

3. Выйдите из ячейки, в которая должна вться формула, и нажмите кнопку Вировать (или нажмите CTRL+V).

   

4. Убедитесь, что ссылки на ячейки остаются нужными.

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

ЛАБОРАТОРНАЯ
РАБОТА № 2

Ссылки.
Копирование формул

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

Задание 1 

На листе «Премия» рабочей книги Excel
создайте таблицу по образцу. Вычислите по формулам графы Премия, НДФЛ, Сумма к
выдаче, Итого. 

Задание 2 

На листе «Коэффициент» создайте
таблицу по образцу. Вычислите алгебраические выражения, используя относительные
и абсолютные ссылки. Округлите полученные результаты до 3-х знаков после
запятой.

Задание 3 

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

1.     
Выпуск продукции = Кол-во изделий · Цена изделий,

2.     
Себестоимость выпускаемой продукции = Количество выпущенных
изделий · Себестоимость изделий, 

3.     
Прибыль = Выпуск продукции – Себестоимость выпускаемой продукции,

4.     
Рентабельность = Прибыль / Себестоимость выпускаемой продукции.

Задание 4 

На листе «Товары» создайте таблицы.
Рассчитайте стоимость товаров в рублях, долларах, евро, зная курс валют
(обновите курс на сегодняшнюю дату).   

                                               Курс валют                       10.10.2018

Задание 5 

На листе «Скидки» создать таблицу посчитать
цену товаров, учитывая скидку.

Задание 6 

Выполните задание на листе «Функция».
Вычислить значения функции y=k·(x²-1)/(х²+1) для всех х
на интервале [-2:2] с шагом 0,2 при k = 10. Решение должно быть получено в виде
таблицы:

Сохраните файл
под именем Лаб_раб_2.xlsх в личном каталоге. Предъявите работу преподавателю
для проверки.

Копирование формул

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

Пример. В
таблице, приведенной на рис. 18, рассчитать
сумму проданных товаров (Сумма =
Цена*Количество).

+

Рисунок
18 – Использование маркера заполнения
для
ввода формулы в ячейки столбца

Формула
для расчета суммы товаров набирается
в ячейке D2 (=В2*С2), затем с помощью маркера
заполнения она вносится в ячейки D3 и
D4.

Вычисление части формулы

При
поиске ошибок в составленной формуле
бывает удобно посмотреть результат
вычислений какой-то части формулы. Для
этого:

1. Сделайте активной ячейку, содержащую
   формулу.

2. В
   строке формул выделите часть формулы,
   которую нужно вычислить (рис. 19):

Рисунок 19 –
Вычисление части формулы

1. Поочередно
   нажмите кнопки F9
   (вычисление), Enter
   (результат
   вычисления вставить в формулу) и Esc
   (возврат формулы в исходное состояние).

Ввод ссылок на ячейки

Указать
положение ячейки можно с помощью
относительной
или
абсолютной
адресации.
Когда вы используете относительную
адресацию,
ссылки в формулах автоматически
изменяются при копировании формулы в
другое место. Например, если в ячейке
С7 содержится формула =С4*В7, то при
копировании этой формулы в ячейку С8
она преобразуется в =С5*В8.

Чтобы
ссылки в формуле не изменялись при
копировании формулы в другую ячейку,
надо использовать абсолютные
ссылки. Они обозначаются
знаком «$». Например, $С$4 – зафиксирована
ячейка С4.

Если
при копировании формулы нужно сохранить
фиксированным только один параметр,
т.е. номер строки или номер столбца,
используются смешанные
ссылки. Например, $СЗ
– номер столбца не меняется, номер
строки будет подстраиваться под новое
расположение формулы при копировании.
Для С$3 – фиксированный номер строки.

Пример
ввода ссылок на ячейки:

1. Выделить ячейку, в которую нужно ввести
   формулу.

2. Введите
   знак равенства «=». Затем введите адрес
   ячейки, которая должна быть в формуле,
   или щелкните по ней. Полученная ссылка
   будет относительной.

3. Чтобы
   из относительной ссылки получить
   абсолютную или смешанную ссылку, нажмите
   необходимое число раз клавишу F4,
   пока ссылка не отобразится в нужном
   виде.

4. Продолжайте
   ввод других значений, ссылок и операторов.

5. Если
   формула завершена, нажмите Enter.

8 Функции ms Excel

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

Вызов функций, задание аргументов

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

1 Выделить
ячейку, в которую надо поместить формулу,
и выполнить одно из следующих действий:

– из
основного меню вызовите команду Вставка
/ Функция;

– в
строке формул нажмите кнопку

(вставка функции). Откроется окно первого
шага Мастера функций
(рис. 20).

Рисунок 20 –
Мастер функций – шаг 1

2
Двигаясь по категориям и по названиям
функций, выберите необходимую функцию
(например, категория Статистические,
функция СРЗНАЧ).
Нажмите ОК.

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

Рисунок
21 – Мастер функций – шаг 2

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

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

Была ли эта статья полезной?

Microsoft Excel

Использование встроенных функций.

Формулы, их копирование, расчеты.

Все о формулах

• Формула выполняет вычисления соответствующих заданий и отображает на листе окончательный результат;
• В формулах Excel можно использовать числа, знаки арифметических действий и ссылки на ячейки;
• Формула ВСЕГДА начинается со знака равенства(=);
• По умолчанию формулы на экране не отображаются, но можно изменить режим работы программ, чтобы увидеть их;
• Формулы могут включать обращение к одной или нескольким функциям;
• В формулах недопустимы пробелы;
• Длина формулы не должна превышать 1024 элементов;
• Нельзя вводить числа в форматах даты и времени дня непосредственно в формулы. В формулах они могут быть введены только в виде текста, заключенного в двойные кавычки. Excel преобразует их в соответствующие числа при вычислении формулы.

Ввод формулы

• Щелкните ячейку, в которую нужно ввести формулу;
• Введите знак равенства — обязательное начало формулы.
• Введите первый аргумент — число или ссылку на ячейку. Адрес можно ввести вручную или вставить автоматически, щелкнув нужную ячейку;
• Введите знак арифметического действия;
• Введите следующий аргумент;
• Повторяя пункты 4 и 5, закончите ввод формулы;
• Нажмите Enter . Обратите внимание, что в ячейке отображается результат вычислений, а в строке формул — сама формула.

Арифметические операторы

= E3+F3

Вывод формул на экран

 Выберите из меню Сервис команду Параметры ;

 Щелкните вкладку Вид ;

 Установите флажок Формулы ;

 Щелкните OK .

Вычисление части формулы

При поиске ошибок в составленной формуле бывает удобно посмотреть результат вычисления какой-то части формулы. Для этого:

 Встать на ячейку, содержащую формулу;

 В строке формул выделить часть формулы, которую нужно вычислить;

 F9 — вычисление

Enter – результат вычисления выводит на экран. Esc — возврат формулы в исходное состояние.

Совет

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

 возведение в степень;

 умножение и деление;

 сложение и вычитание.

Например: D1 равно 28

Виды ссылок на ячейки

В формулах Excel обычно использует относительные ссылки. Отсюда следует — ссылки в формулах автоматически изменяются при копировании формул в другое место. Например, если в ячейке B10 содержится формула = СУММ (В3:В9), то при копировании этой формулы в ячейку С10 она преобразуется в =СУММ (С3:С9).

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

Абсолютная строка выглядит как D$7.

Абсолютный столбец выглядит как $D7 .

Перемещение и копирование формул

Формула введена в ячейку. Её можно перенести или скопировать.

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

При копировании формула перемещается в новое место таблицы:

а) ссылки перенастраиваются при использовании относительных ячеек;

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

Формулы: Замена значениями

Формулу на рабочем листе можно заменить её значением, если в дальнейшем понадобится только результат а, а не сама формула. Для этого:

 Выделите ячейку с формулой, которую нужно преобразо- вать в значение;

 Щелкните на кнопке Копировать на стандартной панели инструментов;

 Выберите Правка — Специальная вставка и щелкните на строке Значения . Щелкните на ОК ;

 Нажмите Enter , чтобы убрать «муравьиную дорожку» вокруг ячейки.

Формулы: защита и скрытие

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

 Чтобы снять защиту ячейки (диапазона ячеек) или запретить отображение её содержимого в строке формул, сначала выделите нужную ячейку или диапазон;

 Выберите Формат — Ячейки и щелкните на вкладке Защита ;

 Снимите флажок Защищаемая ячейка , чтобы снять защиту ячейки. Установите флажок Скрыть формулы , чтобы они не отображались в строке формул при выборе ячейки. Щелкните на ОК ;

 Установите флажок Сервис – Защита – Защитить лист.

Результат – весь лист, кроме изменяемых ячеек, защищен

Формулы: создание текстовой строки

Иногда бывает нужно объединить содержимое двух ячеек. В Excel такая операция называется конкатенация. Для этого:

 Выделите ячейку, в которую нужно поместить формулу и введите знак равенства (=), чтобы начать ввод;

 Введите адрес или имя или щелкните на ячейке на рабочем листе;

 Введите оператор конкатенации (&), затем введите следующую ссылку;

 Если необходимо, повторите шаг 3. Не забывайте о том, что в формулу необходимо вставить кавычки с пробелом между ними, чтобы Excel вставила пробел между двумя текстовыми фрагментами.

 Чтобы закончить ввод формулы, нажмите Enter .

Пример: формула =С3 &” “D3 , где С3 — « Парафеева », а D3 — «Таня»

Даст результат : «Парафеева Таня» — в одной ячейке

Формулы: ссылки на ячейки из других рабочих листов

При организации формул возможно ссылаться на ячейки других рабочих листов. Для этого:

 Выделите ячейку, в которую нужно поместить формулу, и введите знак равенства (=);

 Щелкните на ярлычке листа, содержащего нужную ячейку;

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

 Завершите ввод формулы, затем нажмите Enter .

Пример: формула = Лист1!В6+Лист2!D9

Все о функциях

• Функциями называют встроенные в Excel формулы;
• Функций в Excel сотни : инженерные, информационные, логические, арифметические и тригонометрические, статистические, функции обработки текста, функции работы с датой и временем, функции работы с базами данных и многие-многие другие ;
• Функции можно использовать как по отдельности, так и в сочетании с другими функциями и формулами;
• После имени каждой функции в ( ) задаются аргументы. Если функция не использует аргументы, то за её именем следуют пустые ( ) без пробела между ними;
• Аргументы перечисляются через запятую;
• Функция может иметь не более 30 аргументов.

Ввод функции

 Щелкните ячейку, в которую хотите ввести функцию;

 Введите знак равенства, название функции (или выберите из Мастера функций) и открывающуюся круглую скобку;

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

 Щелкните кнопку ввода в строке формул или нажмите Enter . Закрывающуюся скобку Excel вставит самостоятельно.

Самые популярные функции Excel

C УММ

Функции: мастер функций

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

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

на стандартной панели инструментов;

 Укажите нужный тип функции в списке Категории . Если вы не знаете, к какой категории принадлежит функция, выберите

10 недавно использовавшихся или Полный алфавитный перечень ;

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

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

 Щелкните на ОК , чтобы завершить ввод функции и поместите её в ячейку.

Спасибо за внимание!

Автор материалов — Лада Борисовна Есакова.

Microsoft Excel (в дальнейшем просто — Excel) — это программа выполнения расчетов и управления так называемыми электронными таблицами.

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

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

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

Для написания формул используют адреса ячеек и знаки арифметических операций (+, -, *, /, ^). Формула начинается знаком =.

В Excel предусмотрены стандартные функции, которые могут быть использованы в формулах. Это математические, логические, текстовые, финансовые и другие функции. Однако, на экзамене Вам могут встретиться только самые простые функции: СЧЕТ (количество непустых ячеек), СУММ (сумма), СРЗНАЧ (среднее значение), МИН (минимальное значение), МАКС (максимальное значение).

Диапазон ячеек обозначается следующим образом: A1:D4 (все ячейки прямоугольника от A1 до D4.

Адреса ячеек бывают относительными, абсолютными и смешанными.

Они по-разному ведут себя при копировании формулы из ячейки в ячейку.

Относительная адресация:

Если в ячейке B2 мы напишем формулу =D1+3, то таблица воспримет это как «взять значение ячейки на две правее и на одну выше текущей, и прибавить к нему 3».

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

Абсолютная адресация:

Если нам не нужно, чтобы адрес пересчитывался при копировании формулы, мы можем его «закрепить» в формуле — поставить знак $ перед буквой и индексом ячейки: =$D$1+3. Такой адрес называется абсолютным. Такая формула не будет изменяться при копировании:

Смешанная адресация:

Если же мы хотим, чтобы при копировании формулы автоматически пересчитывался, к примеру, только индекс ячейки, а буква оставалась неизменной, мы можем «закрепить» в формуле только букву (или наоборот):  =$D1+3. Такой адрес называется смешанным. При копировании формулы будет меняться только индекс в адресе ячейки:

Электронные таблицы. Копирование формул.

Пример 1.

В ячейке C2 записана формула =$E$3+D2. Какой вид приобретет формула, после того как ячейку C2 скопируют в ячейку B1?

1) =$E$3+C1   2) =$D$3+D2      3) =$E$3+E3      4) =$F$4+D2

Решение:

Место расположения формулы меняется с C2 на B1, т.е. формула сдвигается на одну ячейку влево и на одну ячейку вверх (буква «уменьшается» на единицу и индекс уменьшается на единицу). Значит, так же изменятся все относительные адреса, а абсолютные (закрепленные знаком $) останутся неизменными:

=$E$3+С1.

Ответ: 1

Пример 2.

В ячейке В11 электронной таблицы записана формула. Эту формулу скопировали в ячейку А10. В результате значение в ячейке А10 вычисляется по формуле х—Зу, где х — значение в ячейке С22, а у — значение в ячейке D22. Укажите, какая формула могла быть написана в ячейке В11.

1) =C22-3*D22          2) =D$22-3*$D23      3) =C$22-3*D$22      4) =$C22-3*$D22

Решение:

Проанализируем поочередно каждую формулу:

Место расположения формулы меняется с B11 на A10, т.е. буква «уменьшается» на 1 и индекс уменьшается на 1.

Тогда при копировании формулы изменятся следующим образом:

1)      =B21-3*C21

2)      =C$22-3*$D22

3)      =B$22-3*C$22

4)      =$C21-3*$D21

Условию задачи соответствует формула 2).

Ответ: 2

Электронные таблицы. Определение значения формулы.

Пример 3.

Дан фрагмент электронной таблицы:

В ячейку D1 введена формула =$А$1*В1+С2, а затем скопирована в ячейку D2. Какое значение в результате появится в ячейке D2?

1) 10   2) 14    3) 16    4) 24

Решение:

Место расположения формулы меняется с D1 на D2, т.е. буква не меняется, а индекс увеличивается на 1.

Значит, формула примет вид: =$А$1*В2+С3. Подставим в формулу числовые значения ячеек:1*5+9=14. Правильный ответ указан под номером 2.

Ответ: 2

Пример 4.

В электронной таблице значение формулы =СРЗНАЧ(A6:C6) равно (-2). Чему равно значение формулы =СУММ(A6:D6), если значение ячейки D6 равно 5?

1) 1                    2) -1                     3) -3                     4) 7

Решение:

По определению среднего значения:

СРЗНАЧ(A6:C6) = СУММ(A6:С6)/3 = -2

Значит, СУММ(A6:С6) = -6

СУММ(A6:D6) = СУММ(A6:С6)+D6 = -6+5 = -1

Ответ: 2

Электронные таблицы и диаграммы.

Пример 5.

Дан фрагмент электронной таблицы в режиме отображения формул.

После выполнения вычислений построили диаграмму по значениям диапазона  A1:D1. Укажите полученную диаграмму:

Решение:

Вычислим по формулам значения ячеек A1:D1.

B1 = 3-2 =1

A1 = 2-1 =1

C1 = 1+2 =3

D1 = 1*3 =3

Этим данным соответствует диаграмма 3.

Ответ:3