4111

Создание формул в Excel

Контрольная

Информатика, кибернетика и программирование

Создание формул в Excel При вводе значений (не формул), которые начинаются с минуса или знака равенства, перед ними нужно поставить одинарную кавычку (апостроф) Чтобы значения типа 2/2 не превращались в даты, формат ячейки нужно установить текстовый...

Русский

2012-11-13

83 KB

66 чел.

Создание формул в Excel

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

Чтобы значения типа 2/2 не превращались в даты, формат ячейки нужно установить текстовый

Если значение ячейки начинается с одинарной кавычки (апострофа), при расчете формул эта кавычка отбрасывается

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

Для ввода формулы вручную ее нужно начинать со знака равно

Мастер функций можно вызвать из меню Вставка - Функция

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

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

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

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

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

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

Условие задачи

Дана таблица с колонками количество и цена. Нужно подсчитать стоимость каждого из товаров и общую стоимость всех товаров.

Пример решения задачи

Пример подсчета стоимости каждого из товаров

Пусть числовые данные начинаются со второй строки. Тогда для подсчета стоимости первого товара нужно ввести в ячейку С2 формулу =А2*В2. После нажатия клавиши Enter ввод формулы завершен, а на экране должно появиться вычисленное по этой формуле значение.

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

Если выделить одну из этих ячеек, в строке формул можно увидеть, что программа сама заменила содержание формул. Например, в ячейке С4 будет формула =А4*В4.

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

Ввод данных нужно завершать нажатием клавиши Enter.

Пример подсчета общей стоимости

Нужно ввести формулу суммы, но она может быть внесена автоматически. Выделяем ячейку С5, на панели инструментов нажимаем кнопку "автосумма" (на ней изображен математический знак суммы). После нажатия клавиши Enter ввод формулы завершен, а на экране должно появиться вычисленное по этой формуле значение.

В строке формул для ячейки С5 можно увидеть формулу суммы =СУММ(C2:C4)

Результат решения задачи

 

A

B

C

1

количество

цена

стоимость

2

1

2

2

3

2

3

6

4

3

4

12

5

всего

 

20

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

  1.  Чем отличается формула от другого текста?
  2.  Можно ли в этой задаче написать формулу для стоимости третьего товара и скопировать ее для первых двух товаров?
  3.  Можно ли формулу суммы вводить вручную?

Нужно протабулировать функцию sin(8x) на отрезке от 0 до 1 с шагом 0,1. По полученным значениям построить график функции.

Пример решения задачи

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

Сначала заполним колонку значений аргумента. Первые два значения вводим вручную: 0 в ячейку А2 и 0,1 у А3. Потом выделяем эти две ячейки и тянем вниз до ячейки А12 маркер заполнения (маленький черный квадрат в нижнем правом углу выделенного диапазона).

У ячейку В2 вводим формулу =SIN(8*A2) И также копируем ее в расположенные ниже ячейки (до В12 включительно).

Теперь функция sin(8x) протабулирована на отрезке от 0 до 1 с шагом 0,1.

Вторая часть задачи - построение графика

Выделяем диапазон А2:В12. На панели инструментов нажимаем кнопку мастера диаграмм. Или в меню Вставка команду диаграмма... открывается окно мастера диаграмм.

На первом шаге нужно выбрать тип диаграммы Точечная, а вид Точечная диаграмма с точками, соединенными сглаживающими линиями.

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

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

Результат решения задачи

 

A

B

1

x

y

2

0

0

3

0,1

0,717

4

0,2

1

5

0,3

0,675

6

0,4

-0,058

7

0,5

-0,757

8

0,6

-0,996

9

0,7

-0,631

10

0,8

0,117

11

0,9

0,794

12

1

0,989

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

  1.  Какие изменения нужно внести в готовое решение задачи, чтобы протабулировать эту же функцию на этом же отрезке с шагом 0,05?
  2.  Как внести изменения в график, если вычислено еще несколько точек протабулированной функции, например 1,1 и 1,2?
  3.  Что нужно сделать, чтобы график был создан на отдельном листе?

Задача: создание математических формул в excel

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

Примеры формул, которые имеют арифметические операции:

  •  сложение =А1+А2
  •  вычитание =А1-А2
  •  умножение =А1*А2
  •  деление =А1/А2
  •  возведение в степень =А1^А2
  •  И их произвольная комбинация, например =В5-А1^А2+С3*А1/А2

Кроме того, excel имеет много функций, которые можно включать в формулы.

Некоторые часто используемые математические функции

Модуль

Функция ABS получает один аргумент и возвращает его модуль, например =ABS(A1)

Логарифм натуральный

Функция LN получает один аргумент и возвращает логарифм натуральный аргумента, например =LN(A3)

Логарифм с произвольным основанием

Функция LOG получает два аргумента и возвращает логарифм первого аргумента по основанию, указанному во втором аргументе, например =LOG(A2;4)

Округление

Функция ОКРУГЛ получает два аргумента и возвращает результат округления первого аргумента до количества знаков, указанных во втором аргументе. Если второй аргумент 0 возвращается целое. Если второй аргумент отрицательный, результат округляется до десятков (-1), сотен (-2) и т.д. Например =ОКРУГЛ(A3;2) =ОКРУГЛ(A3;0) =ОКРУГЛ(A3;-2)

Корень квадратный

Функция КОРЕНЬ получает один аргумент и возвращает квадратный корень аргумента, например =КОРЕНЬ(A4)

Экспонента в степени

Функция EXP получает один аргумент и возвращает экспоненту в степени, равной аргументу, например =EXP(A5)

Число ПИ

Функция ПИ не имеет аргументов и возвращает число ПИ, например =ПИ()

Функция может быть аргументом другой функции

Математическая функция может иметь аргументы в виде чисел, математических формул, адресов ячеек или других функций, которые возвращают числа. Функция может быть частью математической формулы. Например =ОКРУГЛ(A3/SIN(B3*ПИ()/2);2) =EXP(A5+2*LN(5*A5)) =КОРЕНЬ(ABS(10-A4))

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

  1.  Каким будет результат в ячейке с формулой =КОРЕНЬ(-4)?
  2.  Каким будет результат в ячейке с формулой =EXP(LN(2)) ?
  3.  Каким будет результат в ячейке с формулой =ОКРУГЛ(5;-1)?

Задача: создание формул в excel, используя вложенные проверки условий

Условие задачи

Дана таблица с числовой колонкой. Нужно определить, в какой из трех промежутков попадает каждое число: до 100, от 100 до 200, больше 200.

Пример решения задачи

Пусть числовые данные начинаются с второй строки. Тогда для первого числа в ячейку В2 пишем формулу сначала с одной проверкой условия =ЕСЛИ(A2<100;"до 100";"вложенная проверка условия") или вручную, или при помощи мастера функций. После нажатия клавиши Enter ввод формулы завершен, а на экране должно появиться вычисленное по этой формуле значение.

В ячейку С2 пишем формулу =ЕСЛИ(A2>200;"больше 200";"от 100 до 200") Теперь у нас есть обе необходимые проверки условий. Осталось их объединить в одну.

Копируем текст без знака = формулы ячейки С2 в строке формул (а не саму ячейку) и вставляем вместо слов "вложенная проверка условия" в формулу ячейки В2. Получаем =ЕСЛИ(A2<100;"до 100";ЕСЛИ(A2>200;"больше 200";"от 100 до 200"))

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

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

Другой способ решения задачи

Вместо вложенных проверок условий верный результат можно было бы получить, если ввести в ячейку D3 формулу =ЕСЛИ(A2<100;B2;C2) или =ЕСЛИ(A2<100;"до 100";C2) В этом случае проверки условий не вложенные. Это упрощает формулы, но увеличивает количество используемых ячеек.

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

Результат решения задачи

 

A

B

C

D

1

число

результат

дополнительные вычисления

результат

2

99

до 100

от 100 до 200

до 100

3

150

от 100 до 200

от 100 до 200

от 100 до 200

4

210

больше 200

больше 200

больше 200

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

  1.  Сколько ячеек минимально нужно использовать, чтобы получить результат, который требует 12 уровней вложенных проверок условий?
  2.  Как записать формулу проверки условия, чтобы ячейка заполнялась только в одном случае, а в других оставалась пустой?
  3.  Как нужно изменить формулу =А1/В1, чтобы в случае В1=0 результат равнялся нулю?

Задача: создание формул в excel с абсолютными и относительными ссылками

Условие задачи

Дана таблица с колонками количество детей и зарплата. Нужно подсчитать налог с доходов (15%) с учетом льготы на каждого ребенка по 17 грн. Ставка налога и размер льготы записаны в ячейках B1 и D1 соответственно.

Пример решения задачи

Пусть числовые данные начинаются с третьей строки. Тогда для подсчета налога первого работника в ячейку С3 пишем формулу =(B3-$B$1*A3)*$D$1. После нажатия клавиши Enter ввод формулы завершен, а на экране должно появиться вычисленное по этой формуле значение.

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

Если выделить одну из этих ячеек, например, С4, в строке формул можно увидеть, что программа сама изменила содержание формул. Но изменились только относительные адреса ячеек, т.е. без знака доллара. Абсолютные адреса ячеек, т.е. со знаком доллара, остались без изменений. Например, в ячейке С4 будет формула =(B4-$B$1*A4)*$D$1.

Второй способ решения задачи

Абсолютные ссылки для колонки и строки указываются отдельно. В данном примере все формулы находятся в колонке С, поэтому верный результат можно было бы получить, если ввести в ячейку С3 формулу =(B3-B$1*A3)*D$1. В этой формуле абсолютная ссылка только на строку 1.

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

Результат решения задачи

 

A

B

C

D

1

льгота

17

ставка налога

0,15

2

количество детей

зарплата

налог

 

3

1

100

12,45

 

4

0

200

30

 

5

2

300

39,9

 

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

  1.  Чем отличаются абсолютные ссылки от относительных?
  2.  Изменится ли значение в четвертой строке, если вместо 0 ячейка А4 будет пустой?
  3.  Что нужно изменить в решении данной задачи, если изменится только размер льготы, например, с 17 на 34?


 

А также другие работы, которые могут Вас заинтересовать

65109. Кыпчаки и Кимакский каганат. Йемеки 47 KB
  Легендарные сведения подтверждают указанное направление движения кыпчаков: согласно легенде о предке уйгуров Огузкагане последний послал кыпчаков чтобы они поселились между страной итбараков предположительно киргизов. Повидимому более раннее или параллельное название кыпчаков сиры.
65110. КНЯЗЬЯ КАЗАНСКИЕ, КНЯЗЬЯ БОЛГАРСКИЕ 103 KB
  Осенью этого года великий князь суздальский и нижегородский Дмитрий Константинович послал рать на Болгарского князя Асана вариант написания: Блъгарского князя Осана 4. высказана следующим образом: на Болгарского князя Асана еже ныне глаголются Казанцы выделено нами.
65111. Модель Татарстана: «За» И «Против» 134.5 KB
  Обычно Модель Татарстана противопоставляется опыту других регионов суверенизация которых была сопряжена с острыми кризисами Абхазия Чеченская республика Ичкерия и др. Но сам опыт Татарстана при этом рассматривается весьма однобоко в основном с точки зрения мирного характера...
65112. Об основных этапах становления татарской нации 248.5 KB
  Проблему формирования татарской нации на сегодня трудно считать изученной сколько-нибудь исчерпывающе. Ее автор выделил три основных этапа становления татарской буржуазной нации: первый с конца ХVII до конца ХVIII вв.
65113. Проблема этнического единства татар 73.5 KB
  Самый свежий пример подобного рода относится к крымским татарам среди которых недавно происходила большая дискуссия по вопросу об идентичности быть ли им крымскими татарами къырым татарлары къырым татар халкы или принять название крымчан къырымлы къырым халкы.
65114. ТАТАРЫ (ПОПУЛЯРНЫЙ ОЧЕРК ЭТНИЧЕСКОЙ ИСТОРИИ И ДЕМОГРАФИИ) 92.5 KB
  Татары являются одним из крупных тюркоязычных этносов. Татарский народ прошел длительный путь исторического развития и имел в прошлом свою государственность древнетюркские каганаты Причерноморская Булгария Хазарский каганат Волжская Булгария Золотая Орда Казанское Крымское...
65115. Образование Золотой Орды и формирование средневекового татарского этноса (XIII – первая четверть XV века) 299.5 KB
  Золотая Орда как великая евразийская империя вторая после Тюркского каганата существовал сравнительно недолгий период а на фоне истории мировой цивилизации и достаточно незначительный. Это государство современники называли поразному...
65116. ЮГО-ВОСТОК ТАТАРСТАНА: ПРОБЛЕМА ИЗУЧЕНИЯ ЭТНИЧЕСКОЙ ИСТОРИИ РЕГИОНА XIV-XVII ВЕКОВ 123.5 KB
  В связи с состоянием источников ранняя история татарского населения юго-восточных районов республики остается исследованной недостаточно. Дискуссионным остается вопрос о существовании в этой зоне татарского населения до начала XVIII века.
65117. Из истории изучения формирования тюркоязычного населения Пермского края 63.5 KB
  Тюркоязычное население Пермского края татары и башкиры длительное время поддерживали интенсивные этнические контакты. Именно поэтому история изучения формирования пермских татар и башкир рассматривается вместе.