Визначення та створення формули

У цьому уроці ми познайомимо вас з основними правилами для створення формул і використання функцій. Ми вважаємо, що одним з найкращих способів навчання є практика, тому ми надаємо кілька прикладів і детально пояснюємо їх. Теми, які ми розглянемо, включають:

ШКОЛА НАВІГАЦІЇ

Навіщо потрібні формули і функції?
Визначення та створення формули
Відносна і абсолютна посилання на клітинку і форматування
Корисні функції, які ви повинні знати
Пошук, діаграми, статистика та зведені таблиці

рядків і стовпців
приклад математичної функції: SUM ()
операторів
пріоритет оператора
Приклад фінансової функції: PMT (), оплата кредиту
використовуючи функцію “string” (“string” є скороченням “string of text”) всередині формули і функцій вкладеності

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

Рядки та стовпці

Щоб зрозуміти, як писати формули і функції, потрібно знати про рядки і стовпці.

Рядки виконуються горизонтально, а стовпці - вертикально. Пам'ятати, що таке, що думають про колону, що тримає дах - стовпці йдуть вгору-вниз і таким чином ряди йдуть ліво-право.

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

Приклад: сума функцій ()

Тепер давайте продемонструємо, як використовувати функцію.

Функції можна використовувати, ввівши їх безпосередньо в майстер функцій або за допомогою майстра функцій. Майстер функцій відкривається, коли ви обираєте функцію з меню "Формули" з "Бібліотека функцій". В іншому випадку ви можете набрати = в комірці, а зручне випадаюче меню дозволить вам вибрати функцію.

Майстер повідомляє вам, які аргументи потрібно надати для кожної функції. Вона також надає посилання на онлайн-інструкції, якщо вам потрібна допомога у розумінні того, що функція виконує, і як її використовувати. Наприклад, якщо ви введете = sum в комірку, вбудований майстер покаже вам, які аргументи потрібні для функції SUM.

Коли ви вводите функцію, майстер знаходиться вбудовано або прямо на ваших пальцях. Коли ви вибираєте функцію з меню «Формули», майстер є спливаючим вікном. Ось спливаючий майстер для функції SUM ().

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

Припустімо, у нас є ця таблиця, яка містить плани щодо бюджетного відпустки вашої родини:

Щоб розрахувати загальні витрати, ви можете написати = b2 + b3 + b4 + b5, але легше використовувати функцію SUM ().

У Excel знайдіть символ Σ у верхньому лівому куті екрана Excel, щоб знайти кнопку AutoSum (математики використовують грецьку літеру Σ для додавання серії чисел).

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

Натисніть клавішу "enter", щоб прийняти діапазон, вибраний Excel, або скористайтеся курсором, щоб змінити обрані клітинки.

Якщо ви подивитеся на те, що Excel помістили в таблицю, ви побачите, що вона написала цю функцію:

У цій формулі Excel підсумовує числа від B2 до B9. Зауважте, що ми залишили номер нижче рядка 5, щоб ви могли додати до бюджету сімейного відпочинку - вартість, безумовно, збільшиться, оскільки список дітей, що вони хочуть зробити, і де вони хочуть піти, росте довше!

Функції математики не працюють з літерами, тому якщо ви покладете букви в стовпець, результат відображається як "#NAME?", Як показано нижче.

#NAME? вказує на наявність певної помилки. Це може бути будь-яка кількість речей, включаючи:

погана посилання на клітинку
за допомогою букв у математичних функціях
пропущення необхідних аргументів
неправильна назва функції написання
незаконні математичні операції подібні до поділу на 0

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

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

Натисніть “enter” для підтвердження результатів.

Оператори розрахунку

Існує два типи операторів: математика і порівняння.

Математичний оператор	Визначення
+	Крім того
-	віднімання або заперечення, наприклад, 6 * -1 = -6
*	множення
/	поділ
%	відсотків
^	показник, напр. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Існують інші оператори, які не пов'язані з математикою, наприклад "&", що означає конкатенацію (приєднання кінця до кінця) двох рядків. Наприклад, "Excel" і "це Fun" дорівнює "Excel - Fun".

Тепер розглянемо операторів порівняння.

Оператор порівняння	Визначення
=	дорівнює, наприклад, 2 = 4 або "b" = "b"
>	більше, ніж, наприклад, 4> 2 або "b"> "a"
<	менше, ніж, наприклад, 2 < 4 or “a” < “b”
> =	більше або дорівнює - інший спосіб подумати про це> = означає або > або =.
<=	менше або дорівнює.
	не дорівнює, наприклад, 46

Як ви можете бачити вище, оператори порівняння працюють з числами і текстом.

Зверніть увагу, що якщо ви введете = "a"> "b" у клітинку, то вам буде сказано "FALSE", оскільки "a" не перевищує "b". "B" з'являється після "a" в алфавіті, так що "a" > "Б" або "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)

Давайте розглянемо приклад для розрахунку виплати кредиту.

Почніть з створення нового аркуша.

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

Аналогічно відформатуйте інші клітинки для “валюти” замість відсотка і виберіть “номер” для терміну кредиту.

Тепер у нас є:

Додайте функцію SUM () до "загальних" щомісячних витрат.

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

Помістіть курсор у клітинку платежів (B4).

У меню Формули виберіть випадаюче меню «Фінансовий», а потім виберіть функцію PMT. Майстер відкриється:

За допомогою курсору виберіть "швидкість", "nper" (термін кредиту), "Pv" ("поточна вартість" або сума кредиту). Зверніть увагу, що процентну ставку потрібно розділити на 12, оскільки відсотки розраховуються щомісяця. Також необхідно помножити термін кредиту на роки на 12, щоб отримати термін кредиту в місяцях. Натисніть "OK", щоб зберегти результат у електронній таблиці.

Зверніть увагу, що платіж відображається як від'ємне число: -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 і відняти одну, щоб опустити кому і пробіл.

Давайте зробимо це крок за кроком.

Згори це = FIND (“,”, A3 ”)
Довжина рядка = LEN (A3)
Вам знадобиться використовувати математику, щоб знайти кількість символів: = LEN (A3) - FIND (“,”, A3) - 1
Права частина рядка, яку ми хочемо, є = RIGHT (A3, LEN (A3) - FIND (",", A3) - 1)

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

Це було трохи важко, але вам потрібно лише написати ці формули один раз.

Далі йде далі…

Це завершує наш урок на сьогоднішній день. Ви повинні мати досить чітке розуміння формул і функцій, рядків і стовпців, і те, як все це можна використовувати через кілька певних прикладів.

Далі в Урок 3 ми розглянемо посилання на клітинку та форматування, а також переміщуємо та копіюємо формули, щоб вам не потрібно було переписувати кожну формулу знову і знову!