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

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

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

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

строки и столбцы
пример математической функции: 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, чтобы найти кнопку «Автосумма» (математики используют греческую букву Σ для добавления серии чисел).

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

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

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

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

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

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

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

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

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

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

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

Есть два типа операторов: математика и сравнение.

Математический оператор	Определение
+	прибавление
-	вычитание или отрицание, например, 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 or “a” < “b”
> =	больше или равно - другой способ думать об этом> означает = или > или же знак равно.
<=	меньше или равно.
	не равно, например, 46

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

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

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

Допустим, ваш босс решает, что вы отлично справляетесь, и он или она собирается повысить вас на 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. Мастер появляется:

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

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

Теперь электронная таблица выглядит так:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сверху это = НАЙТИ («,», А3 »)
Длина строки = LEN (A3)
Вам нужно будет использовать некоторую математику, чтобы найти количество символов: = LEN (A3) - НАЙТИ («,», A3) - 1
Правая часть нужной нам строки: = ВПРАВО (A3, LEN (A3) - НАЙТИ («,», A3) - 1)

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

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

Далее…

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

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