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?


 

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

19790. Створення меню. Компоненти MainMenu та PopupMenu 28 KB
  1.Створення меню. Компоненти MainMenu та PopupMenu. MainMenu невізуальний на відміну від візуальних компонентів TEdit і TLabel в точності відповідних своєму зовнішньому вигляду в працюючій програмі. Це означає що хоча його видно на формі як невеликий квадрат у вікні створеної програми ...
19792. Текстові компоненти Label, Edit, Memo 26.5 KB
  Текстові компоненти Label Edit Memo Компоненти керування стандартні для Windows інтерфейсні елементи такі як головне і спливаюче меню кнопка однорядковий і багаторядковий редактори перемикачі мітки списки і деякі інші компоненти що застосовуються найчастіше. Компонен
19793. Файловий ввід/вивід. Поняття потока. Класс TFileStream 27 KB
  Файловий ввід/вивід. Поняття потока. Класс TFileStream. Файловий вводвивод реалізується по поточним стандартам.Наприклад Мова Сі абстрагує всі файлові операції перетворюючи їх на операції з потоками байтів які можуть бути як потоками введення так і потоками виведе
19794. Требования, предъявляемые к современным вычислительным сетям 17.73 KB
  Требования предъявляемые к современным вычислительным сетям Главным требование обеспечение пользователям потенциальной возможности доступа к разделяемым ресурсам всех компьютеров объединенных в сеть. Все остальные требования производительность надежность сов
19795. Основні методи комутацій. Методи передачі даних 18.59 KB
  Існують три принципово різні схеми комутації абонентів у мережах: комутація каналів circuit switching комутація пакетів packet switching і комутація повідомлень message switching. Мережа з комутацією каналів вид телекомунікаційної мережі у якій між двома вузлами мережі повинне б...
19796. Моделі OSI. Рівень моделі OSI, їх призначення 19.93 KB
  Модель OSI англ. Open Systems Interconnection Reference Model модель взаємодії відкритих систем абстрактна модель для мережних комунікацій і розробки мережевих протоколів. Представляє рівневий підхід до мережі. Кожен рівень обслуговує свою частину процесу взаємодії. Завдяки такій структ
19797. Апаратні та програмні компоненти ЛОМ 15.56 KB
  Весь комплекс програмноапаратних засобів мережі може бути описаний багатошаровою моделлю що складається з шарів:комп'ютери чи комп'ютерні платформи;комунікаційне обладнання;операційні системи;мережеві додатки.Комп'ютериВ основі будьякої мережі лежить апаратний шар
19798. Архітектура, основні поняття і визначення глобальних мереж 19.54 KB
  Комунікаційна мережа система що складається з об'єктів що здійснюють функції генерації перетворення зберігання і споживання продукту званих пунктами вузлами мережі і ліній передачі зв'язків комунікацій з'єднань що здійснюють передачу продукту між пунктами.Відм...