Використання формул і функцій для обчислень в MS Excel
Обробка даних, що зберігаються в осередках робочих аркушів Excel, здійснюється за формулами, певним користувачем. Для переходу в режим створення формул необхідно виділити осередок і ввести знак =. У формулах можуть використовуватися як стандартні арифметичні оператори, так і вбудовані функції Excel. Приклад визначення найпростішої формули: = (7 - 4) * 2.
При обчисленні математичних виразів за формулою Excel керуються такими традиційними правилами. визначальними пріоритет виконання операцій:
· В першу чергу обчислюються вирази всередині круглих дужок;
· Визначаються значення, які повертаються вбудованими функціями;
· Виконуються операції піднесення до степеня (^), потім множення (*) і ділення (/), а після - складання (+) і віднімання (-).
Зауваження. Необхідно відзначити, що операції з однаковим пріоритетом виконуються зліва направо.
Функції MS Excel. Арифметичні та тригонометричні функції.
В Excel є цілий ряд вбудованих математичних функцій, що істотно полегшують рішення завдань. Синтаксис функцій:
<Имя Функции><Аргумент 1; …; Аргумент N> .
Тут в якості аргументу можуть використовуватися як безпосереднє значення, так і ідентифікатор осередки.
1) При використанні в функції декількох аргументів вони відокремлюються один від одного крапкою з комою. = Вироблено (A1; B2; C4). Ця формула вказує Excel, що необхідно перемножити числа в осередках A1, B2 і C4.
2) Будь-який аргумент може бути діапазоном, що містить довільне число осередків листа. Наприклад, функція = вироблено (A1: A3; B2: B4) має два аргументи, але перемножує вміст шести осередків.
3) Аргументи не обов'язково повинні утворювати безперервні діапазони осередків. = Вироблено (A1: A3; B2; B4: B7).
4) Деякі функції. наприклад ПІ (). не мають аргументів. Комбінацію функцій можна використовувати для створення вираження, наприклад: СУМ (КОРІНЬ (16); COS (A1 * ПІ ())).
Таблиця 1. Основні математичні функції
Відкидає всі цифри праворуч від десяткового дробу. Аргумент n визначає позицію в дробової частини числа, після якого проводиться усічення.
Функції можуть вводитися в робочий лист декількома способами. Після введення знака = функція або може бути введена безпосередньо з клавіатури, або вибиратися в поле імені (див. Рис.1), яке в цьому випадку стає полем функції.
Рис.1. Вибір функції через поле функції
Існує два способи, рівноцінних останньому, але не вимагають попереднього введення знака рівності:
§ за допомогою кнопки Вставка функції;
Посилання в межах робочого листа
В Excel розрізняють посилання трьох типів. відносні. абсолютні. змішані. Існують два стилю оформлення посилань: стиль A1. або основний, і стиль R1C1.
Може відобразитися значення відносної посилання являє собою комбінацію з імені стовпця і номера осередку, відповідну її поточним координатам. Може відобразитися значення відносної посилання автоматично змінюється при копіюванні формули в новий осередок.
Наприклад, в формулах = A1 + B1 і = A5 + B5, що знаходяться в осередках B3 і B7 (рис.2, а), що відображається значенням A1 і A5 відповідають однакові збережені значення: <текущий столбец – 1> <текущая строка – 2>.
Якщо до моменту фіксації введення формули натискати на клавішу F4. можна змінити посилання або на абсолютну, або на змішану.
§ при запису знака $ перед ім'ям стовпця і номером рядка (рис. 2, б);
§ при використанні імені осередку.
Змішані посилання являють собою комбінацію з відносних і абсолютних посилань. Можна визначити два типи змішаних посилань.
Приклади змішаних посилань: = $ B1 + $ D7, = B $ 1 + D $ 7.
Рис.3. змішані посилання
Посилання в стилі R1C1
Наприклад, формула в комірці R3C2 (рис.4) читається так: скласти вміст комірки, розташованої на два рядки нижче і на стовпець лівіше вічка з формулою, з осередком, що знаходиться на два рядки нижче в тому ж стовпці. Формула в комірці R3C1 використовує абсолютні посилання на комірки, що знаходяться на перетині першого рядка з першим і другим стовпцями.
Приклади: 1. Абсолютні посилання. R2C6 - осередок, розташована у другому рядку і шостому стовпці; R5C3 - осередок, розташована в п'ятому рядку і третьому стовпці.
2. Відносні посилання. RC - осередок введення; RC [1] - осередок праворуч від осередку введення; RC [-2] - осередок друга зліва від осередку введення; R [3] C - осередок третя знизу від осередку введення; R [-1] C - осередок зверху від осередку введення; R [1] C [-1] - осередок, розташована на один рядок нижче і на один стовпець лівіше осередку введення.
Рис.4. Посилання в стилі R1C1
До складу Excel включена сукупність засобів, що забезпечують синтаксичний контроль за введенням формул, контроль за правильністю посилань і типами даними, оброблюваних за допомогою формул.
Для зручностей сприйняття формули Excel дозволяє розмістити її на декількох рядках в рядку формул. Включення в формулу розриву рядка забезпечується натисканням комбінації клавіш Alt + Enter.
В Excel передбачено контроль за введенням формул: при невідповідності кількості відкривають і закривають дужок виводиться повідомлення: Помилка у введеному вираженні. Прийняти запропоновані виправлення? і пропонується свій варіант формули.
Режим відображення формул
Трасування посилань і залежностей
Для контролю за правильністю потоків даних і джерелами помилок в Excel використовується трассировщик осередків. При трасуванні відзначаються впливають і залежні осередки.
Впливають - це осередки, значення яких використовуються формулою в виділеної осередку. Осередок, для якої визначено впливають осередки, завжди містить формулу.
Зовсім - це осередки, які використовують значення виділеної комірки. Осередок, для якої визначено залежні осередки, може містити формулу або константу.
Для трасування необхідно вибрати пункт Сервіс / Залежності і потім або Залежні клітинки, або Що впливають осередки. Так на рис.2 і рис.2б проведена трасування впливають осередків A1 і B1.
Неодноразовий послідовний вибір пунктів Зовсім осередку або Впливають осередку дозволяє простежити побічно залежні і побічно впливають осередки.
Помилковою значення - це результат формули, яку Excel не може обчислити. В Excel визначені сім помилкових значень (табл.6).
Таблиця 6.Ошібочние значення Excel
Видаляється все стовпці, що містять виділені осередки
Для швидкого видалення рядків і стовпців цілком необхідно виділити весь рядок (рядки) або стовпець (стовпці) і скористатися будь-яким із способів, наведених у пункті 2 послідовності дій з видалення осередків, рядків, стовпців. При цьому діалогове вікно Видалення осередків відкриватися не буде.
Слід розрізняти команди очищення осередків і видалення осередків. Перша очищає вміст комірки, формат чи примітка, але залишає осередок незайманої, а друга повністю видаляє осередку, рядки або стовпці, а не тільки їх вміст.
Вставка комірок, рядків і стовпців
Excel дозволяє вставляти рядки і стовпці, а також додавати осередку, не роблячи змін до цілих рядках і шпальтах. Вставляти комірки, рядки і стовпці так само просто, як і видаляти їх.
Операція вставки комірки, рядка або стовпця складається з наступних кроків:
1) виділити клітинку або діапазон комірок там, куди потрібно вставити клітинку або діапазон комірок, або виділити по одній клітинці в суміжних рядках або суміжних стовпчиках, перед якими будуть вставлені рядки або стовпці;
2) скористатися одним з наведених нижче способів для відкриття діалогового вікна Додавання осередків:
- командою контекстного меню Додати осередку;
3) вибрати в діалоговому вікні Додавання осередків вказати потрібний параметр (див. Табл.10) і натиснути кнопку ОК.
Таблиця 10.Параметри діалогового вікна Додавання осередків