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?


 

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

66438. Стан та перспективи розвитку управлінського консультування на підприємствах України 747.5 KB
  У практичній частині проведений аналіз тенденцій консалтингового забезпечення підпрємств а також аналіз перешкод та приоритетів розвитку менеджмент-консалтингових компаній. Особливості взаємодії консалтингових компаній та підприємств замовників в контексті світових економічних тенденцій...
66439. Анализ особенностей фразеологического оборота в романе М.А. Шолохова «Поднятая целина» 126 KB
  Это произошло без участия сознательной авторской воли просто за прошедшие годы самостоятельность и зрелость были искоренены в крестьянстве приученном к тому что за самостоятельность наказывают вплоть до физического уничтожения.
66443. Програма підготовки майбутніх соціальних педагогів до зміцнення здоров’я дітей у сім’ї 141.62 KB
  В Україні формування відповідального ставлення до здоров’я в дитячому та молодіжному оточенні стає важливим соціально-економічним та гуманітарним складником державної політики. Особливе занепокоєння викликає те, що за даними міністерства охорони здоров’я за останнє...
66444. Российское экологическое законодательство: современное состояние и перспективы развития 170.5 KB
  Целями данного исследования являются: вопросы, связанные с определением общих и специальных факторов, воздействующих на экологическое законодательство; выделение тенденций развития экологического законодательства в субъектах РФ. В ходе работы над дипломным проектом использовались следующие методы: диалектический, логический, сравнительно-правовой, исторический, социологический и др.
66445. Разработка прибора РЭМИ – 4 (регистратор электромагнитного излучения) 4.49 MB
  Согласовываться две платы должны по гибкому шлейфу что облегчит сборку и возможные дальнейшие доработки устройства. Печатная плата индикации и управления крепится к крышке корпуса шестью винтами что обеспечивает надежное крепление печатной платы.
66446. Вексель - вид ценной бумаги, как объект гражданских прав 336 KB
  Когда впервые появился вексель достоверно установить невозможно. Ученые высказывают различные точки зрения о времени и месте появления первых векселей. Одни предполагают, что вексельные операции были известны древним римлянам и древним грекам, другие отстаивают точку зрения, что вексель появился в Италии во второй половине 12 века.