Загальне уявлення про функції MS Excel 


Мы поможем в написании ваших работ!



ЗНАЕТЕ ЛИ ВЫ?

Загальне уявлення про функції MS Excel



 

Функції в Excel використовуються для виконання стандартних обчислень в робочих книгах. Значення, що використовуються для обчислення функцій, називаються аргументами. Значення, що повертаються функціями як відповідь, називаються результатами. Крім вбудованих функцій ви можете використовувати в обчисленнях користувальницькі функції, які створюються за допомогою засобів Excel. Щоб використовувати функцію, потрібно ввести її як частину формули в комірку робочого аркуша. Послідовність, в якій повинні розташовуватися використовувані у формулі символи, називається синтаксисом функції. Всі функції використовують однакові основні правила синтаксису. Якщо ви порушите правила синтаксису, Excel видасть повідомлення про те, що у формулі є помилка. Якщо функція з'являється на самому початку формули, їй повинен передувати знак рівності, як і у всякій іншій формулі.

Аргументи функції записуються в круглих дужках відразу за назвою функції і відокремлюються один від одного символом крапка з комою ";". Дужки дозволяють Excel визначити, де починається і де закінчується список аргументів. Усередині дужок повинні розташовуватися аргументи. Пам'ятайте про те, що при записі функції повинні бути присутніми відкриває і закриває дужки, при цьому не слід вставляти пробіли між назвою функції і дужками.

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

Запитання й вхідні параметри повинні мати допустимі для даного аргументу значення. Деякі функції можуть мати необов'язкові аргументи, які можуть бути відсутні при обчисленні значення функції.

Всі функції в Excel характеризуються:

· назвою;

· призначенням (що, власне, вона робить);

· кількістю аргументів (параметрів);

· типом аргументів (параметрів);

· типом значення, що повертається.

Для зручності роботи функції в Excel розбиті по категоріях: функції управління базами даних і списками, функції дати і часу, DDE / Зовнішні функції, інженерні функції, фінансові, інформаційні, логічні, функції перегляду і посилань. Крім того, присутні такі категорії функцій: статистичні, текстові та математичні.

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

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

У Excel широко представлені математичні функції. Наприклад, можна виконувати різні операції з матрицями: множити, знаходити зворотну, транспонувати.

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

Як ми бачимо, щоб Excel не видав помилку, функція повинна відповідати певному набору правил. Цей набір правил називається синтаксис запису функції.

Загальний синтаксис запису будь-якої функції в Excel:

імя_функциі ([аргумент_1; аргумент_2;...; аргумент_N])

Список аргументів укладений у квадратні дужки, що говорить про те, що це необов'язкова частина. Деякі функції взагалі не приймають аргументів. Однак, навіть якщо функція не приймає аргументів, порожні круглі дужки писати обов'язково, інакше Excel видасть помилку! Деякі функції приймають РІВНЕ ОДИН аргумент. Наприклад функції sin (число), cos (число) і т. п. Деякі функції приймають більше, ніж один аргумент. У такому випадку аргументи розділяються між собою крапкою з комою «;».

У загальному випадку, аргументами функції можуть бути константи (числа, введені вручну), посилання на клітинки, посилання на діапазон комірок, іменовані посилання та інші функції (вкладені функції).

Існує кілька способів введення функції:

· введення функцій вручну;

· введення функції за допомогою кнопки "сигма";

· робота з майстром функцій;

Для набору найпростіших формул, що містять функції, можна не користуватися спеціальними засобами, а просто писати їх. Однак цей спосіб погано підходить для набору довгих формул.

Занадто велика ймовірність допустити помилку, набираючи вручну складні і довгі формули, і на це йде багато часу.

Один із засобів полегшити і прискорити роботи з функціями - кнопка на панелі інструментів «Стандартна». У ній розробники Microsoft «сховали» п'ять часто використовуваних функцій:

СУМ (мінімум один, максимум 30 аргументів). Підсумовує свої аргументи.

СРЗНАЧ (мінімум один, максимум 30 аргументів). Знаходить середнє арифметичне аргументів;

РАХУНОК (мінімум один, максимум 30 аргументів). Підраховує кількість чисел у списку аргументів (використовується для підрахунку кількості комірок з числами, порожні клітинки і текст ігноруються);

МАКС (мінімум один, максимум 30 аргументів). Повертає максимальний аргумент;

МИН (мінімум один, максимум 30 аргументів). Повертає мінімальний аргумент.

Принцип роботи: активізуємо осередок, де має бути результат (просто клацаємо); натискаємо на стрілочку праворуч від кнопки "сигма"; вибираємо потрібну функцію. Після вибору Excel сам вставить знак "=", ім'я функції, круглі дужки, і навіть спробує вгадати діапазон, який ми ходимо виділити (хоча він рідко вгадує); виділяємо зв'язний діапазон комірок. Excel вставить в круглі дужки адресу виділеного діапазону; якщо потрібно, наприклад, підсумувати числа з незв'язних діапазонів, затискаємо Ctrl, і виділяємо потрібну кількість діапазонів. Excel сам поставить крапку з комою, і вставить посилання на інший діапазон; коли виділили всі потрібні діапазони, для завершення натискаємо Enter; в процесі виділення можна натискати F4 для зміни типу посилання і F3 для вставки іменованих посилань.

При написанні складних формул, особливо використовують вкладені функції, використання майстра функцій - найкраще рішення. Він дуже полегшує і прискорює введення формул, і робить багато речей за нас: автоматично вставляє знак "дорівнює", ім'я функції, круглі дужки, розставляє крапки з комою. Дозволяє переглядати значення посилань і результати проміжних обчислень.

Існує 3 способи запуску майстра функцій:

· за допомогою кнопки в рядку формул;

· за допомогою команди "Інші функції..." кнопки;

· за допомогою пункту меню "Вставка" "Функція";

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

В одну функцію можна вставити іншу функцію. Звичайно, функцію можна записати вручну (писати назву вкладеної функції, відкривати дужки, ставити крапки з комою). Однак це суперечить самій ідеології майстра функцій, який повинен полегшувати написання формул, захищати користувача від помилок і звести до мінімуму ручну роботу. Існує більш зручний спосіб вкласти функцію - спеціальна кнопка на панелі "Рядок формул".

 

Категорій функцій MS Excel

 

У Microsoft Excel використовується більше 100 функцій, об'єднаних за категоріями:

Функції роботи з базами даних можна використовувати, якщо необхідно переконатися в тому, що значення списку задовольняють умові. З їх допомогою, наприклад, можна визначити кількість записів у таблиці про продажі або витягти ті записи, в яких значення поля «Сума» більше 1000, але менше 2500.

Функції роботи з датою і часом дозволяють аналізувати і працювати зі значеннями дати і часу у формулах. Наприклад, якщо потрібно використовувати у формулі поточну дату, скористайтесь функцією СЬОГОДНІ, що повертає поточну дату з системних годинах.

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

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

Інформаційні функції призначені для визначення типу даних, що зберігаються в комірці. Вони перевіряють виконання якогось умови і повертають в залежності від результату значення ИСТИНА або ЛОЖЬ. Так, якщо клітинка містить парне значення, функція ЕЧЁТН повертає значення ИСТИНА. Якщо в діапазоні функцій є порожня клітинка, можна скористатися функцією СЧИТАТЬПУСТОТЫ.

Логічні функції призначені для перевірки виконання умови або для перевірки декількох умов. Так, функція ЕСЛИ дозволяє визначити, чи виконується зазначена умова, і повертає одне значення, якщо умова істинно, і інше, - якщо воно помилкове.

Функції посилання і автопідстановки здійснюють пошук в списках або таблицях. Наприклад, для пошуку значення в таблиці використовуйте функцію ВПР, а для пошуку положення значення у списку - функцію ПОИСКПОЗ.

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

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

Функції обробки тексту дозволяють проводити дії над рядками тексту, наприклад змінити регістр або визначити довжину рядка. Можна також об'єднати кілька рядків в одну. Наприклад, за допомогою функцій СЕГОДНЯ і ТЕКСТ можна створити повідомлення, що містить поточну дату і привести його до вигляду «дд-ммм-рр»: = «Балансовий звіт від» & ТЕКСТ (СЕГОДНЯ), «дд-мм-рр»)

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

Розглянемо основні функції.

· МИН (number1; чісло2;...; чісло30) МАКС (число1; число2;...; чісло30) Функції МИН і МАКС приймають від 1 до 30 аргументів (у Office 2007 - до 255) і повертає мінімальний / максимальний з них. Якщо в якості аргументу передати діапазон комірок, з діапазону буде вибрано мінімальне / максимальне значення. Ці функції також можуть бути вставлені за допомогою кнопки "сигма".

· СРЗНАЧ (number1; чісло2;...; чісло30) Функція СРЗНАЧ (середнє значення) приймає від 1 до 30 аргументів (у Office 2007 - до 255) і повертає їх середнє арифметичне (сума чисел, поділена на кількість чисел). Цю функцію також можна вставити за допомогою кнопки "сигма"

· СТЕПЕНЬ (число; ступінь) Функція СТЕПЕНЬ повертає результат зведення першого аргументу ("число"), в ступінь, зазначену в другому аргументі ("степень").

· СУМ (арг1; АРГ2;...; арг30) Функція СУММ приймає від 1 до 30 аргументів (у Office 2007 - до 255) і повертає їх суму. В якості аргументів можна передавати адреси діапазонів (що найчастіше і робиться), в цьому випадку підсумуйте всі числа в діапазоні.

· СЧЁТ (арг1; АРГ2;...; арг30) Функція СЧЁТ приймає від 1 до 30 аргументів (у Office 2007 - до 255) і повертає кількість аргументів, які є числами. Найчастіше функції просто передають адресу діапазону, а вона підраховує кількість осередків з числами.

· ПИ () Повертає значення тригонометричної константи pi = 3,1415...

· Вироблено ( арг1; АРГ2;...; арг30) Функція заброньований приймає від 1 до 30 аргументів (у Office 2007 - до 255) і повертає їх твір. В якості аргументів можна передавати адреси діапазонів, в цьому випадку перемножуються всі числа в діапазоні.

· СУММЕСЛИ Часто необхідно не просто просуміровать всі значення в діапазоні, а включити в суму тільки ті, які задовольняють певній умові. Функція СУММ підсумовує все, що їй дають, функція СУММЕСЛІ дозволяє підсумувати лише значення, відповідні певній умові

· СУММЕСЛИ (діапазон; критерій; [діапазон_суммірованія]) Перевіряється діапазон, кожна клітинка з якого перевіряється на відповідність умові, вказаному у другому аргументі. Умова для підсумовування, на відповідність яким перевіряється кожна клітинка з перевіряється діапазону. Якщо необхідно використовувати операцію порівняння, то "логічне вираження" вказується без лівого операнта і полягає в подвійні лапки (наприклад, "> = 100" - підсумовувати всі числа, більші 100). Також можна використовувати текстові значення (наприклад, "яблука" - підсумовувати всі значення, що знаходяться навпроти тексту "яблука") і числові (наприклад, 300 - підсумовувати значення в осередках, значення в яких 300).

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

СЧЁТЕСЛИ Працює дуже схоже на функцію СУММЕСЛИ. На відміну від СУММЕСЛИ, яка підсумовує значення з комірок, СЧЕТЕСЛИ підраховує кількість осередків, що задовольняють певній умові. Якщо написати формулу СУММЕСЛИ ("> 10", A1: A10), буде підрахована сума значень з комірок, значення в яких більше 10. Якщо ж написати СЧЕТЕСЛИ ("> 10", A1: A10), буде підраховано кількість осередків, значення в яких більше 10.

СЧЕТЕСЛИ (діапазон; критерій) Перевіряється діапазон, кожна клітинка з якого перевіряється на відповідність умові, вказаному у другому аргументі. З цього ж діапазону відбувається підрахунок кількості осередків.

Критерій - умова, на відповідність яким перевіряється кожна клітинка з першого аргументу. Умова записується аналогічно СУММЕСЛИ.

У прикладі вище фактично підраховується кількість клітинок, які містять текст "Яблука".

Призначення: функція ЕСЛИ виконує те ("Значення якщо ИСТИНА") чи інше ("Значення якщо ЛОЖЬ") дію залежно від того, виконується (так само ИСТИНА) умова чи ні (так само ЛОЖЬ).

Аргумент1. Логічний вираз: Все, що дає в результаті логічні значення ЛОЖЬ або ИСТИНА. Зазвичай або вираження ставлення (A1> = 12) або функції, які повертають логічні значення (И, ИЛИ).

Аргумент2. Значення якщо ИСТИНА: будь-яке допустиме в Excel вираз.

Аргумент3. Значення якщо ЛОЖЬ: будь-яке допустиме в Excel вираз.

повертається значення: може повертати значення будь-яких типів, залежно від аргументів 2 і 3.

Логіч_знач І (логіч_знач1; логіч_знач2;...; логіч_знач30)

Призначення: функція И використовується тоді, коли потрібно перевірити, чи виконуються декілька умов ОДНОЧАСНО. Одне з найбільш часто використовуваних застосувань функції И - перевірка, чи потрапляє число x в діапазон від x1 до x2.

Аргументи: Функція И приймає від 1 до 30 аргументів (у Office 2007 - до 256), кожен з яких є логічним значенням ЛОЖЬ або ИСТИНА, або будь-яким виразом або функцією, яке в результаті дає ЛОЖЬ або ИСТИНА., Що повертається: Функція І повертає логічне значення. Якщо ВСЕ аргументи функції И рівні ИСТИНА, повертає ИСТИНА.

Логіч_знач ИЛИ (логіч_знач1; логіч_знач2;...; логіч_знач30)

Призначення: Функція ИЛИ використовується тоді, коли потрібно перевірити, чи виконується ХОЧА-БИ ОДНЕ з багатьох умов.

Аргументи: Функція ИЛИ приймає від 1 до 30 аргументів (у Office 2007 - до 256), кожен з яких є логічним значенням ЛОЖЬ або ИСТИНА, або будь-яким виразом або функцією, яке в результаті дає ЛОЖЬ або ИСТИНА., Що повертається: функція ИЛИ повертає логічне значення. Якщо ХОЧА Б ОДИН аргумент має значення ИСТИНА, повертає ИСТИНА.

Функція ВПР (Вертикальне Перше Рівність) Для функції необхідно внести наступні дані: яке значення шукати (вказується осередок містить значення), масив в якому відбувається пошук і номер стовпчика в якому знаходиться значення, номер повторного значення в масиві, значення якого і буде використано, яке треба повернути. Функція значно розширює свої можливості при використанні її в парі з логічною функцією ЕСЛИ ().!!

ВПР (що шукаємо, таблиця де шукаємо, з якого стовпця взяти значення, булевська мінлива актуальна для дуже великих відсортованих таблиць, для малих завжди ставимо 0 - повний перебір таблиці). Приміром = ВПР (A1, B: D, 2, 0) Ця функція потрібна наприклад коли вам потрібно копіювати дані з однієї таблиці в іншу. Припустимо, в одній таблиці у вас виробництво за моделями, а в іншій-продажу за моделями. У цих таблицях моделі не по порядку. Ви можете додати колонку "продажу" в таблицю "виробництво" і за допомогою функції ВПР знайти відповідні значення продажів з іншої таблиці. Використання функції автоматично знаходить в зазначеній таблиці потрібне значення і позбавляє від ручних копіювань. Примітка: ВПР вибирає тільки одне значення. Якщо у вас в таблицях моделі повторюються, необхідно використовувати функцію СУММЕСЛИ.

 

 

 



Поделиться:


Последнее изменение этой страницы: 2016-08-01; просмотров: 281; Нарушение авторского права страницы; Мы поможем в написании вашей работы!

infopedia.su Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. Обратная связь - 54.198.37.250 (0.051 с.)