Как вычислить функцию в excel: Вычисление вложенной формулы по шагам

Содержание

Операции с условием в Excel

«Операции с условием в Excel»

Цель работы: Освоить работу с условными операторами в Excel. Научиться использовать имеющиеся функции с условием для конкретных задач.

Рекомендуемая литература: [1–9].

Задание:

1.Открыть Excel и созданный ранее документ. Создать новый лист и назвать его if(x).

2.Вычислить значение заданной функции одной переменной f1 с условием.

3.Вычислить количество точек функции, попадающих в заданный интервал.

4.Вычислить значения заданной функции одной переменной f2.

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

6.Вычислить значение функции двух переменных.

7.Вычислить максимальное и минимальное значение функции.

8.Вычислить количество положительных и сумму отрицательных элементов функции.

9.Посчитать произведение тех значений функции, которые меньше 2.

10.Сохранить документ.

Пример выполнения задания:

Задание:

1.	f 1(x)	x 1,			x 0			,
					x	0
		x 1,
где х меняется от –2 до 2 с шагом 0,2. Интервал: [–0,2; 0,2].
		x2	1,			x 2
2.		1,			2 x 2 ,
	f 2(x)
		x 3,					x 2


где х меняется от –4 до 4 с шагом 0,5. Интервал [–1; 1]
3.			2	y		2	если1, тт..		((x,yy))лежитв кругев кругес радиусомрадиусом3,	3
	f (x, y) x
		x 2		y 2			1в,противном случае, в противном случае

где х меняется от –4 до 4 с шагом 0,5, а y от –2 до 2 с шагом 0,25.

1. Откроем документ (\Файл\Открыть…) и создадим новый лист (\Вставка\Лист). Переименуем лист при помощи контекстного меню в if(x).

2. Рассмотрим алгоритм вычисления данной функции. Для этого построим блок-схему:

Истина

Ложь

x 0

y=x+1		y=x – 1

Для записи таких выражений в Excel есть встроенные логические функции. В данном случае нам необходима функция ЕСЛИ( ; ; ), ее окно ввода выглядит следующим образом:

В первое поле ввода «Лог_выражение» вводится условие, записанное в нашей блоксхеме в ромбе. Во второе поле ввода «Значение_если_истина» вводится, то что присваивается в случае истинности условия. В третье поле ввода записывается оставшееся выражение, которое присваивается в случае невыполнения условия.

Данную функцию можно записать и без вызова ее окна, для этого записываем: =ЕСЛИ(условие;выр_истина;выр_ложь)

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

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

В ячейках А1 и В1 запишем заголовок таблицы: «х» и «f1(x)» соответственно. В ячейку А2 введем –2, в А3 –1,8, теперь выделим ячейки А2 и А3 и растянем до ячейки

А22. Теперь установим курсор ячейки на В2 и на строке формул нажмем кнопку	,
появится окно:

Развернем поле выбора «Категория» и выберем «Логические», получим:

В поле выбора «Выберите функцию:» выберем «ЕСЛИ» и нажмем «ОК». Получим окно функции «ЕСЛИ». Заполним первое поле этого окна:

Обращаем ваше внимание на то, что вместо х набрано А2, так как ячейке В2, содержащей значения функции, соответствует значение аргумента из ячейки А2. Далее, аналогично заполняя поля «Значение_если_истина» и «Значение_если_ложь», получим:

Теперь, нажав «ОК», мы получим следующий результат:

Обратите внимание, как в строке формул заполнилась сама функция ЕСЛИ(). Осталось только растянуть формулу до ячейки В22 и получить окончательный

результат.

3. Нам осталось вычислить количество значений функции, попадающих в заданный интервал.

Для этого предназначена функция СЧЁТЕСЛИ().

В ячейке D2 произведем вычисления, для чего установим курсор ячейки на D2 и

нажмем кнопку

, выберем категорию «Статистические» и найдем функцию СЧЁТ-

ЕСЛИ(), нажмем «ОК» и получим окно вида:

В поле «Диапазон» установим курсор и произведем выделение ячеек с В2 до В22, а в поле «Критерий» запишем «>=-0,2», получим:

Теперь, нажав «ОК», мы получим количество значений функции, которые больше или равны –0,2, так как данная функция Excel позволяет находить количество не в интервале, а на заданной полуоси. Но нам необходимо найти количество значений функции, попадающих в заданный интервал. Как известно, вычисление количества в любом интервале можно представить как разность количеств на двух полуосях. Например, если нам необходимо вычислить количество элементов, попадающих в интервал [a,b], представим его разностью между количеством на полуоси «>=a» и количеством с полуоси «>b», в результате мы получим количество элементов, попадающих в заданный интервал. Применим это к нашей задаче. В ячейке D2 мы уже вычислили количество элементов «>=- 0,2», теперь в ячейку D3 вычислим количество элементов «>0,2» и в ячейку D4 запишем разность:

=D2-D3

Получим результат:

4. Теперь наберем более сложную функцию, но прежде нарисуем ее блок-схему:

	ДА		НЕТ
		x -2
		ДА		НЕТ
		ДА		НЕТ
			x 2

y=x2+1		y=x-3		y=1

Обратите внимание, что на одной оси условного оператора вложен еще один

условный оператор. Теперь реализуем эту схему.									x
В	ячейки	F1	и	G1	наберем	заголовок	для	таблицы:	x

и f2(x) соответственно. В ячейки F2–F18 занесем значения аргумента. В ячейку G2 запишем:

=ЕСЛИ(F2<=-2;F2*F2+1;ЕСЛИ(F2>=2;F2-3;1)) Теперь, нажав кнопку на строке формул, получим:

Остается только растянуть формулу до ячейки G18.

5. Вычислим сумму тех значений функции, аргументы которых лежат в заданном интервале. Для этого есть функция СУММЕСЛИ().

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

кнопку

, в математических функций найдем нужную нам и нажмем «ОК». Получаем

окно следующего вида:

В первое поле ввода – «Диапазон» – вводятся адреса тех ячеек, по которым будет происходить проверка условия, во второе поле – «Критерий» – вводится условие, накладываемое на заданный «Диапазон». В третье поле – «Диапазон_суммирования» – вводятся адреса ячеек, данные в которых и будут суммироваться (заметим, что они зависят от ячеек, заданных в поле «Диапазон»). Напомним, что критерий суммирования ограничивает полупрямую, а не отрезок, поэтому поступим так же, как и в случае с вычислением количества. Для нашего интервала [-1; 1], сначала вычислим сумму с критерием «>=-1», а затем «>1», а искомый результат получим вычитая из первой суммы вторую.

Для первой суммы получим формулу:

Обратите внимание, как при этом выглядит запись в строке формул.

Теперь, нажав кнопку «ОК», получим результат. Аналогично вычислим вторую сумму:

Теперь для ячейки I4 запишем формулу: =I2-I3

нажмем Enter и получим окончательный результат.

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

Для этого вставим новый лист и назовем его if(x,y). Ячейки А2-А18 заполним значениями х. Ячейки B1-R1 заполним значениями y. Теперь установим курсор в ячейку В2 и запишем туда формулу:

ЕСЛИ(A2*A2+B1*B1<=9;A2*A2+B1*B1+1;A2*A2+B1*B1-1)

Но растягивать такую формулу пока нельзя, так как будет происходить смещение ссылок на значения для x и y. Закрепим для значений x имя столбца – А, а для значений y номер строки – 1. Получим формулу вида:

ЕСЛИ($A2*$A2+B$1*B$1<=9;$A2*$A2+B$1*B$1+1;$A2*$A2+B$1*B$1-1) Ее можно растянуть до R1, получим:

7. Вычислим максимальное и минимальное значения этой функции.

Для этого установим курсор ячейки на В20 и в «Статистических» функциях выберем МАКС(), а в первое поле ввода запишем: «B2:R18». Аналогично в ячейку В21 вычислим минимум при помощи МИН(). Получим:

8. Теперь вычислим количество положительных и сумму отрицательных элементов

функции.

Для этого установим курсор ячейки на С20 и в строку формул запишем: =СЧЁТЕСЛИ(B2:R18;”>0”)

а в ячейку С21: =СУММЕСЛИ(B2:R18;”<0”)

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

Получаем:

9. Теперь вычислим произведение тех значений функции, которые меньше 2. Для этого установим курсор в ячейку Е21 и в строке формул запишем:

=ПРОИЗВЕД(ЕСЛИ(B2:R18<2;B2:R18)) нажмем Ctrl+Shift+Enter, получим результат:

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

Функции в Excel. — it-black.ru

Функции в Excel. — it-black.ru
Перейти к содержимому

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

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

Синтаксис функций в Excel

Для корректной работы, функция должна быть написана в определенной последовательности, которая называется синтаксис. К базовому синтаксису функции относятся знак равенства (=), имя функции (например, СУММ) и один или более аргументов. Аргументы содержат информацию, которую необходимо вычислить. В следующем примере функция суммирует значения в диапазоне A1:A20.

Вставка функций в Excel

Давайте познакомимся с некоторыми наиболее распространенными и часто используемыми функциями:

СУММ – эта функция суммирует значения всех аргументов.
СРЗНАЧ – определяет среднее арифметическое величин, содержащихся в аргументах. Функция вычисляет сумму значений ячеек, а затем делит результат на их количество.
СЧЁТ – подсчитывает количество чисел в списке аргументов. Функция полезна для быстрого подсчета числа элементов в диапазоне.
МАКС – определяет максимальное значение из списка аргументов.
МИН – определяет минимальное значение из списка аргументов.

В следующем примере мы создадим простую формулу для расчета средней цены за единицу заказанных товаров, используя функцию СРЗНАЧ.

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

2. Вводим знак равенства (=) и нужное имя функции. В нашем случае вводим =СРЗНАЧ.

3. Вводим диапазон ячеек в качестве аргумента в круглых скобках. В нашем примере мы вводим (C3:C10). Эта формула суммирует значения в диапазоне С3:С10, а затем делит результат на количество ячеек в этом диапазоне, тем самым определяя среднее значение.

4. Нажимаем Enter на клавиатуре. Функция будет вычислена, и можно увидеть результат. В данном примере средняя цена за единицу заказанных товаров составляет $15,93.

Вставка функций с помощью команды Автосумма

Команда Автосумма позволяет автоматически вставлять наиболее распространенные функции в формулы Excel. В следующем примере мы создадим формулу для расчета полной стоимости недавно заказанных товаров, используя функцию СУММ.

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

2. В группе команд Редактирование на вкладке Главная находим и нажимаем стрелку рядом с командой Автосумма, а затем выбераем нужную функцию в раскрывающемся меню. В нашем случае мы выберем Сумма.

3. Выбранная функция появится в ячейке. Команда Автосумма автоматически определяет диапазон ячеек для аргумента. В данном примере диапазон D3:D11 был выбран автоматически, а значения просуммированы, чтобы вычислить полную стоимость. Также можно вручную ввести нужный диапазон.

4. Нажимаем Enter на клавиатуре. Функция будет вычислена, и можно увидеть результат. В нашем примере сумма значений диапазона D3:D11 составляет $606,05.

Facebook

Twitter

Виктор Черемных
17 октября, 2017
No Comments

Группа в VK

Обнаружили опечатку?

Сообщите нам об этом, выделите текст с ошибкой и нажмите Ctrl+Enter, будем очень признательны!

Свежие статьи

Облако меток

Instagram

Youtube

Odnoklassniki

Полезно знать

Авторы

Функция Excel не вычисляется

Главная » Ошибки Excel » Функция не вычисляется

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

Мы рассмотрим следующие два случая:

Случай 1: В ячейке отображается функция вместо его рассчитанного значения

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

Case Number 1: The Cell Displays the Function Instead of its Calculated Value

	A	B
1	TEXT1	=LEFT( A1, 1 )
2	ТЕКСТ2	=ЛЕВО( A2, 1 )

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

Возможная причина 1:

Эта проблема часто возникает из-за того, что ячейки, содержащие формулу, форматируются как «текст», а не как «Общий».

Это могло произойти из-за:

Текстовый формат был ранее применен к ячейке

или

Вы вставили новый столбец рядом со столбцом, отформатированным как текст, и новый столбец «унаследовал» форматирование соседнего столбца.

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

Решение 1:

Чтобы исправить это, необходимо преобразовать тип данных ячейки в тип данных «Общий». Это можно сделать с помощью инструмента Excel Text To Columns следующим образом:

С помощью мыши выберите ячейку (ячейки), содержащие формулу (это необходимо делать по одному столбцу за раз).
На вкладке Данные на ленте Excel выберите параметр Текст в столбцы .
Откроется диалоговое окно «Текст в столбцы».
В диалоговом окне «Текст в столбцы» убедитесь, что выбран параметр D elimited , и нажмите N доб .
Убедитесь, что все параметры разделителя не выбраны, а затем нажмите N доб еще раз.
Теперь вам должен быть предложен выбор форматов данных столбцов. Выберите G общий и нажмите кнопку F закончить .

Возможная причина 2:

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

CTRL-` для отображения/скрытия формул

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

Этот параметр находится на панели параметров в разделе «Дополнительно». Однако намного быстрее включить/отключить эту опцию, используя сочетание клавиш Ctrl + ` в качестве переключателя (т.е. нажмите клавишу Ctrl и, удерживая ее, нажмите клавишу ` (большое ударение) клавиша).

(Обратите внимание, что на многих клавиатурах клавиша ` находится вверху слева, рядом с цифрой 1).

Решение 2:

Попробуйте нажать сочетание клавиш Ctrl + ` , чтобы увидеть, приводит ли формула к отображению вычисленного значения.

Если это не решит вашу проблему, снова нажмите Ctrl + ` , чтобы вернуть предыдущую настройку параметра «Показывать формулы в ячейках вместо результатов их вычислений».

Случай номер 2: Формула не обновляется после внесения изменений

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

Вероятная причина:

Эта проблема обычно вызвана настройкой параметра Расчет для электронной таблицы Excel.

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

Решение:

Чтобы исправить это, необходимо установить для параметра Расчет значение Автоматически . Для этого:

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

Вернуться к Ошибки Excel Страница
Возврат на домашнюю страницу ExcelFunctions. net

Как подогнать уравнение к данным в Excel

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

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

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

После того, как вы определили форму уравнения, следующим шагом будет определение параметров уравнения. Предполагая, что точка пересечения с осью y равна 0, квадратное уравнение имеет вид:

Итак, наши параметры a и b.

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

Далее мы вычисляем новый ряд в Excel, используя приведенное выше уравнение. Ряд будет функцией параметров a и b и независимой переменной x.

Отображение исходных данных y и рассчитанного результата «ycalc» на одном графике говорит нам о том, что параметры функции еще не верны. Но мы исправим это в ближайшее время, настроив их, чтобы найти наилучшее соответствие.

Хотя это было бы утомительно, мы могли бы вручную отрегулировать два параметра и «на глазок» подгонять кривую до тех пор, пока она не выглядела бы хорошо. Но мы умнее этого, поэтому мы будем использовать метод наименьших квадратов вместе с Solver, чтобы автоматически находить параметры, которые намного эффективнее определяют кривую наилучшего соответствия.

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

Остатки — это разница между значением, предоставленным функцией, и значением данных при заданном значении x.

Итак, давайте создадим еще один столбец для остатков:

Затем, чтобы вычислить сумму квадратов остатков, используйте функцию СУММСК: лучше всего подходит функция к данным. Чтобы минимизировать этот член, мы будем использовать Solver.

Если вы еще не активировали надстройку Solver в своей копии Excel, вы можете найти инструкции по этому прямо здесь.

После установки вы можете открыть его с правой стороны вкладки «Данные»:

При открытом Решателе выберите ячейку, содержащую формулу СУММСК, в качестве цели, и ячейки, содержащие значения для «а». и «b» в качестве переменных ячеек.

Конечно, цель состоит в том, чтобы минимизировать сумму квадратов остатков, поэтому нажмите кнопку рядом с «Мин» в окне Решателя.

Наконец, снимите флажок «Сделать переменные без ограничений неотрицательными». Мы не знаем заранее, что наилучшие значения a и b обязательно положительны, поэтому это ограничение недействительно.

Для моего рабочего листа окно Решателя имеет следующую настройку:

Для такой простой модели нет необходимости изменять какие-либо параметры решателя. Однако для более сложных уравнений это может понадобиться, и я объяснил, как это сделать в этом посте.

Осталось только нажать «Решить» и позволить Солверу найти оптимальные параметры для функции.

Решатель корректирует значения констант до тех пор, пока сумма квадратов невязок не станет минимальной. Эти значения показаны ниже:

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

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

Как вычислить функцию в excel: Вычисление вложенной формулы по шагам

Операции с условием в Excel