Определение и создание формулы

2024 Автор: Geoffrey Carr | [email protected]. Последнее изменение: 2024-01-31 12:52

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

ШКОЛЬНАЯ НАВИГАЦИЯ

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

строки и столбцы
пример математической функции: SUM ()
операторы
приоритет оператора
пример финансовой функции: PMT (), оплата кредита
используя «строчную» функцию («строка» является сокращением для «строки текста») внутри формулы и функций вложенности

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

Строки и столбцы

Чтобы понять, как писать формулы и функции, вам нужно знать о строках и столбцах.

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

Столбцы обозначаются буквами; строки по номерам. Первая ячейка в электронной таблице равна A1, что означает столбец A, строка 1. Столбцы обозначаются A-Z. Когда алфавит заканчивается, Excel помещает другую букву впереди: AA, AB, AC … AZ, BA, BC, BC и т. Д.

Пример: Функция Sum ()

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

Вы используете функции, введя их непосредственно или используя мастер функций. Мастер функций открывается, когда вы выбираете функцию из меню «Формулы» из «Библиотеки функций». В противном случае вы можете ввести = в ячейку, а удобное раскрывающееся меню позволит вам выбрать функцию.

Мастер сообщает вам, какие аргументы вам необходимо предоставить для каждой функции. Он также предоставляет ссылку на онлайн-инструкции, если вам нужна помощь в понимании того, что делает функция и как ее использовать. Например, если вы введете = sum в ячейку, мастер в строке покажет вам, какие аргументы необходимы для функции SUM.

Когда вы вводите функцию, мастер находится внутри или справа от ваших пальцев. Когда вы выбираете функцию из меню «Формулы», мастер представляет собой всплывающее окно. Вот всплывающий мастер для функции SUM ().

Для нашей первой функции воспользуемся SUM (), которая добавляет список чисел.

Предположим, у нас есть эта таблица, чтобы содержать планы для составления бюджета отпуска вашей семьи:

Чтобы вычислить общие затраты, вы можете написать = b2 + b3 + b4 + b5, но проще использовать функцию SUM ().

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

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

Нажмите «enter», чтобы принять диапазон, выбранный Excel, или используйте курсор для изменения выбранных ячеек.

Если вы посмотрите, что Excel помещает в электронную таблицу, вы можете увидеть, что она написала эту функцию:

В этой формуле Excel суммирует числа от B2 до B9. Обратите внимание: мы оставили несколько комнат ниже строки 5, чтобы вы могли добавить к семейному каникулу бюджет - стоимость, безусловно, возрастет, как список детей, что они хотят делать, и куда они хотят идти, растет дольше!

Математические функции не работают с буквами, поэтому, если вы помещаете буквы в столбец, результат отображается как «#NAME?», Как показано ниже.

#НАЗВАНИЕ? указывает, что существует некоторая ошибка. Это может быть любое количество вещей, включая:

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

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

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

Нажмите «enter», чтобы подтвердить результаты.

Операторы расчета

Существует два типа операторов: математика и сравнение.

Математический оператор	Определение
+	прибавление
–	вычитание или отрицание, например, 6 * -1 = -6
*	умножение
/	деление
%	процентов
^	показателем, например. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 =16

Существуют и другие операторы, не относящиеся к математике, такие как «&», что означает «объединить» (объединить между целыми) две строки. Например, = «Excel» и «is Fun» равно «Excel is Fun».

Теперь посмотрим на операторы сравнения.

Оператор сравнения	Определение
=	равным, например, 2 = 4 или "b" = "b"
>	больше, чем, например, 4> 2 или "b"> "a"
<	менее, чем, например, 2 <4 или "a" <"b"
>=	больше или равно - другой способ думать об этом> = означает или > или же =.
<=	меньше или равно.
	не равным, например, 4 <6

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

Обратите внимание, что если вы введете = «a»> «b» в ячейку, он скажет «FALSE», поскольку «a» не больше, чем «b». «B» появляется после «a» в алфавите, поэтому «a» означает, > "B" или же "B"> "a."

Приоритет заказа оператора

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

Приоритет ордера означает порядок, в котором компьютер вычисляет ответ. Как мы объяснили в уроке 1, площадь круга равна πr², что совпадает с π * r * r. это не (Πr)².

Поэтому вы должны понимать приоритет заказа, когда пишете формулу.

Как правило, вы можете сказать следующее:

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

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

( а также )	Когда используются скобки, они переопределяют обычные правила приоритета. Это означает, что Excel выполнит этот расчет первым. Мы объясняем это ниже.
–	Отрицание, например, -1. Это то же самое, что умножить число на -1. -4 = 4 * (-1)
%	Процент означает умножение на 100. Например, 0,003 = 0,3%.
^	Показатель, например, 10 ^ 2 = 100
* а также /	Умножьте и разделите. Как два оператора имеют одинаковый приоритет? Это просто означает, что если в формуле есть еще два оператора с одинаковым приоритетом, то вычисление выполняется слева направо.
+ и -	Сложение и вычитание.

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

Пример: вычисление площади круга

Площадь круга равна = PI () * радиус ^ 2.

Глядя на таблицу выше, мы видим, что экспоненты появляются до умножения. Поэтому компьютер сначала вычисляет радиус ^ 2, а затем умножает этот результат на Pi.

Пример: расчет повышения зарплаты

Скажем, ваш босс решает, что вы отлично справляетесь, и он или она даст вам 10-процентный рейз! Как бы вы подсчитали свою новую зарплату?

Во-первых, помните, что умножение происходит до добавления.

Это = зарплата + зарплата * 10% или это = зарплата + (зарплата * 10%)?

Предположим, ваша зарплата составляет 100 долларов. С повышением на 10% ваша новая зарплата будет:

= 100 + 100 * 10% = 100 + 10 = 110

Вы также можете написать так:

=100 + (100 * 10%) = 100 + 10 = 110

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

Кстати, более простой способ написать это = зарплата * 110%

Скобки могут быть вложены друг в друга. Итак, когда мы пишем (3 + (4 * 2)), работая изнутри внутрь, сначала вычисляем 4 * 2 = 8, затем добавляем 3 + 8, чтобы получить 11.

Еще несколько примеров

Вот еще один пример: = 4 * 3 / 2. Каков ответ?

Из приведенных выше правил видно, что * и / имеют одинаковый приоритет. Таким образом, Excel работает слева направо, сначала 4 * 3 = 12, затем делится на 2, чтобы получить 6.

Снова вы можете сделать это явным, написав = (4 * 3) / 2

Что насчет = 4 + 3 * 2?

Компьютер видит операторы * и +. Поэтому, следуя правилам приоритета (умножение приходит до добавления), он сначала вычисляет 3 * 2 = 6, затем добавляет 4, чтобы получить 10.

Если вы хотите изменить порядок приоритета, вы должны написать = (4 + 3) * 2 = 14.

Как насчет этого = -1 ^ 3?

Тогда ответ равен -3, потому что вычисляется компьютер = (-1) ^ 3 = -1 * -1 * -1 = -1.

Помните, что отрицательное время отрицательное положительное и отрицательное время положительное отрицательно. Вы можете видеть это следующим образом (-1 * -1) * -1 = 1 * -1 = -1.

Итак, есть несколько примеров математического порядка и приоритета, мы надеемся, что это поможет прояснить некоторые вещи о том, как Excel выполняет вычисления (и, вероятно, для некоторых из них достаточно математики, чтобы продлиться целая жизнь).

Пример: оплата с помощью функции (PMT)

Давайте рассмотрим пример расчета суммы кредита.

Начните с создания нового рабочего листа.

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

Аналогичным образом отформатируйте другие ячейки для «валюты» вместо процента и выберите «число» для срока кредита.

Добавьте функцию SUM () в «итоговые» ежемесячные расходы.

Обратите внимание ипотечный кредит ячейка не включена в общую сумму. Excel не знает, что вы хотите включить это число, поскольку там нет никакой ценности. Поэтому будьте осторожны, чтобы расширить функцию SUM () до вершины либо с помощью курсора, либо путем ввода E2, где говорится, что E3 включает в себя ипотеку в сумме.

Поместите курсор в ячейку платежа (B4).

В меню «Формулы» выберите раскрывающийся «Финансовый», а затем выберите функцию PMT. Мастер появится:

Используйте курсор для выбора «rate.», «Nper» (срок кредита), «Pv» («текущая стоимость» или сумма кредита). Обратите внимание, что вам необходимо разделить процентную ставку на 12, поскольку проценты рассчитываются ежемесячно. Также вам нужно умножить срок кредита в годах на 12, чтобы получить срок кредита в месяцах. Нажмите «ОК», чтобы сохранить результат в электронной таблице.

Обратите внимание, что платеж отображается как отрицательное число: -1013.37062. Чтобы сделать его положительным и добавить его к ежемесячным расходам, укажите на ячейку ипотеки (E2). Введите «= -», затем используйте курсор, чтобы указать поле платежа. Полученная формула = -B4.

Ваши ежемесячные расходы составляют $ 1,863 - Ой!

Пример: текстовая функция

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

Предположим, у вас есть список учеников, как показано ниже. Имя и фамилия находятся в одном поле, разделенном запятой. Мы должны поместить последние и фирменные имена в отдельные ячейки. как нам это сделать?

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

Например, посмотрите на «Вашингтон, Джордж». Процедура разделить это на два слова будет:

Вычислите длину строки.
Найдите позицию запятой (это показывает, где заканчивается одно слово, а другое начинается).
Скопируйте левую часть строки до запятой.
Скопируйте правую часть строки из запятой в конец.

Давайте обсудим, как это сделать с помощью «Джорджа Вашингтона» шаг за шагом в Excel.

Вычислите длину строки с помощью функции = LEN (A3) - результат равен 18.
Теперь найдите позицию запятой, введя эту функцию = FIND (",", A3 ") - результат равен 11.
Теперь возьмите левую часть строки до запятой и создайте эту вложенную формулу, используя результат этапа 1: = LEFT (A3, FIND (",", A3) -1). Обратите внимание, что мы должны вычесть 1 из длины, потому что FIND дает позицию запятой.

Вот что это все выглядит, когда все функции помещаются вместе в формулу. В ячейке B3 вы можете видеть, что эта формула принимает всю информацию из ячейки A3 и вводит в нее «Вашингтон».

Итак, у нас есть «Вашингтон», теперь нам нужно получить «Джордж». Как мы это сделаем?

Обратите внимание, что мы могли бы сохранить результат с шага 1 в ячейке, скажем, B6, а затем написать более простую формулу = LEFT (A3, B6-1). Но это использует одну ячейку для прерывистого шага.

Помните позицию запятой или подсчитайте ее снова.
Вычислите длину строки.
Подсчитайте символы от конца строки до запятой.

Возьмите количество символов из шага 3 и вычтите их, чтобы опустить запятую и пробел.

Давайте сделаем это шаг за шагом.

Сверху это = FIND (",", A3 ")
Длина строки = LEN (A3)
Вам нужно будет использовать некоторую математику, чтобы найти количество символов: = LEN (A3) - FIND (",", A3) - 1
Правая часть нужной строки = RIGHT (A3, LEN (A3) - FIND (",", A3) - 1)

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

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

Далее …

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

Далее, в уроке 3, мы обсудим сотовую ссылку и форматирование, а также переместите и скопируйте формулы, чтобы вам не приходилось переписывать каждую формулу снова и снова!