5213

Робота з формулами в MS Excel

Реферат

Информатика, кибернетика и программирование

Робота з формулами Будова формули Будь-яка формула обов'язково починається зі знака Якщо про цей знак забути, то введене буде сприйнято як звичайний текст. Зрозуміло, що така формула працювати не буде. У загальному випадку ф...

Украинкский

2012-12-04

151.21 KB

68 чел.


Робота з формулами

1. Будова формули

Будь-яка формула обов’язково починається зі знака “=”. Якщо про цей знак забути, то введене буде сприйнято як звичайний текст. Зрозуміло, що така формула працювати не буде.

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

Основні правила для побудови формул такі:

  1.  формула записується тільки в рядок;
  2.  аргументи функцій обов’язково беруться в дужки;
  3.  дужки у формулах можуть бути тільки круглими;
  4.  аргументи функції відокремлюються один від другого комою (якщо було виконано відповідну настройку);
  5.  знак множення у формулах пропускати не можна;
  6.  якщо у формулі використовується текст, то він має бути взятий у подвійні лапки.

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

Приорітет

Знак операції

Найменування операції

1 (найвищий)

«-»

унарний мінус

2

«^»

піднесення до степеня

3

«*», «/»

множення і ділення

4

«+», «-»

додавання і віднімання

Основні повідомлення про помилки у формулах такі:

  1.  #ДЕЛ/0! – ділення на нуль. Таке повідомлення виникає в тих випадках, коли значення дільника операції ділення виявляється рівним нулю.
  2. «Обнаружены ошибки во введённой формуле». Таке повідомлення бачимо, наприклад, при введенні помилкової формули «=10+*2», в якій указано дві операції підряд. При цьому пропонується варіант виправлення формули. Якщо ви погоджуєтесь із запропонованим, то натискуєте кнопку Да. Якщо ви натискуєте кнопку Нет, то це означає, що виправлення ви збираєтесь внести самостійно.
  3. «Несоответствие скобок». Таке повідомлення виникає в тих випадках, коли у введеній формулі порушено баланс між відкриваючими та закриваючими дужками. Наприклад: «=(2+3*4».
  4. #ЗНАЧ! – помилка значення, що входить до складу формули. Виникає, наприклад, у випадку «=SIN("кут")», де для функції указано аргумент не числового, а текстового типу.
  5. #ИМЯ? – помилка в імені. Виникає, наприклад, при обчисленні виразу , якщо застосувати неправильне найменування функції «=КОРЕНЬ(tg(1)+1)». Правильне рішення: «=КОРЕНЬ(TAN(1)+1)».
  6. #ЧИСЛО! – помилка в аргументі функції. Наприклад, аргумент функції у формулі  «=КОРЕНЬ(-5)» – від’ємний.
  7. #Н/Д – стандартне помилкове значення, яке означає НеДоступно. Правило тут таке: якщо хоча б один аргумент будь-якої функції дорівнює #Н/Д, то і значення цієї функції теж дорівнює #Н/Д. Наприклад, «=COS(#Н/Д)».

2. Використання функцій 

До складу MS Excel входить надзвичайно велика кількість різноманітних стандартних функцій. Для ознайомлення з ними можна скористуватись спеціальним засобом, який називається Мастер функций. Основні стандартні елементарні математичні функції представлено в таблиці.

Математичний запис

Запис в Excel

Sin x

SIN(x)

Cos x

COS(x)

Tg x

TAN(x)

Arcsin x

ASIN(x)

Arccos x

ACOS(x)

Arctg x

ATAN(x)

Ln x

LN(x)

Lg x

LOG10(x)

Logax

LOG(x, a)

ex

EXP(x)

|x|

ABS(x)

КОРЕНЬ(x)

ПИ()

x^3

x^(1/3)

Заокруглення числа x до найближчого меншого цілого

ЦЕЛОЕ(x)

Обчислення остачі від ділення націло числа N на число D

ОСТАТ(N, D)

Заокруглення числа x до заданого числа розрядів k 

ОКРУГЛ(x, k)

Зауваження 1. В наведених тригонометричних функціях використовується радіан як одиниця вимірювання величин кутів.

Зауваження 2. Функції ЦЕЛОЕ(x) і ОСТАТ(N,D) утворюють загальне правило обчислення частки і остачі від ділення націло числа N на число D: N=D*ЦЕЛОЕ(N/D)+ОСТАТ(N,D).

Зауваження 3. Функція ОКРУГЛ(x, k) виконує заокруглення за звичайним арифметичним правилом заокруглення (якщо цифра, яка відкидається, менша 5, то попередня цифра залишається без змін, у протилежному випадку попередня цифра збільшується на одиницю). Якщо kдодатне, то число x заокруглюється до указаної кількості десяткових розрядів справа від десяткової крапки. Якщо k=0, то число x заокруглюється до найближчого цілого. Якщо kвід’ємне, то число x заокруглюється до указаної кількості десяткових розрядів зліва від десяткової крапки.

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

Приклади використання деяких функцій:

  1.  Формула «=ЦЕЛОЕ(5.7)» дає результат 5, а формула «=ЦЕЛОЕ(-5.7)» дає результат 6.
  2.  Формула «=ЦЕЛОЕ(23/3)» дає частку від ділення націло числа 23 на число 3, тобто 7.
  3.  Формула «=ОСТАТ(23,3)» дає результат 2.
  4.  Якщо x=143.3184, то формула «=ОКРУГЛ(x,2)» дає результат 143.32.
  5.  Якщо x=143.3184, то формула «=ОКРУГЛ(x,0)» дає результат 143.
  6.  Якщо x=143.3184, то формула «=ОКРУГЛ(x,-1)» дає результат 140.

Тут розглянуто приклади використання лише деяких функцій. Описи решти функцій слід шукати в області задач Справка Excel, яка з’являється після натискування клавіши <F1>. В цій області задач треба натиснути на пункт Оглавление і після його появи знайти в ньому розділ Справка по функциям.

Крім арифметичних виразів, важливим компонентом формул є логічні вирази, зокрема, логічні функції. Логічний вираз це є спільна назва для висловлювання та предиката. Висловлюванням називається твердження, відносно якого відразу можна зробити висновок, вірне воно чи ні. Наприклад, значенням висловлювання «7>5» буде ИСТИНА. Значенням висловлювання «3>5» буде ЛОЖЬ. Висловлювання, яке містить змінні величина, називається предикатом. В залежності від значень змінних предикат може набувати значення ИСТИНА або ЛОЖЬ. Наприклад, результатом порівняння «x>3» буде ЛОЖЬ при x=2 і ИСТИНА  при x=6. Предикат утворюється внаслідок порівняння двох арифметичних виразів, з яких хоча б один містить змінні.

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

У логічних виразах можуть використовуватись також логічні операції, реалізовані у вигляді логічних функцій: НЕ(x) заперечення, И(x,y) логічне множення, ИЛИ(x,y) логічне додавання.

В арифметичних виразах логічне значення ИСТИНА поводить себе як число 1, а ЛОЖЬ – як число 0. І навпаки – в логічних виразах число 1 поводить себе як ИСТИНА, а число 0 – як ЛОЖЬ. Більше того, замість ИСТИНА можна указувати будь-яке число, відмінне від нуля.

Логічні вирази найчастіше застосовуються як перший аргумент логічної функції ЕСЛИ:

ЕСЛИ(лог_выражение, значение_если_истина, значение_если_ложь).

Ця функція має три аргументи, зміст яких такий: якщо лог_выражение дорівнює ИСТИНА, то значення функції обчислюється як значення другого аргументу значение_если_истина, а якщо лог_выражение дорівнює ЛОЖЬ, то значення функції обчислюється як значення третього аргументу значение_если_ложь. Особливість функції ЕСЛИ полягає в тому, що її тип наперед не визначений і співпадає з типом або другого, або третього свого аргумента.

Приклад 1. Задано число z. Побудувати формулу, яка дає результат z+1, якщо z>1, і дає результат z-1 у протилежному випадку.

Розв’язок: «=ЕСЛИ(z>1,z+1,z-1)».

Приклад 2. Задано число z. Побудувати формулу, результатом якої є повідомлення «Перевищено порогове значення», якщо z>100, і яка дає результат z у протилежному випадку.

Розв’язок: «=ЕСЛИ(z>100, "Перевищено порогове значення",z)».

Приклад 3. Задано число z. Побудувати формулу, яка дає результат z/2, якщо , дає результат 10, якщо z<10, і дає результат 25, якщо z>25.

Розв’язок: «=ЕСЛИ(z<10,10,ЕСЛИ(z>25,25,z/2))».

3. Введення та копіювання формул 

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

При використанні рядка формул відомі наступні способи введення формул:

  1. безпосередній,
  2. за схемою автоматичного отримання адрес,
  3. за допомогою майстра функцій.

В процесі введення формули є доступними всі клавіши редагування для виправлення допущених помилок. Якщо надруковане не влаштовує повністю, то можна натиснути кнопку Отмена (червоний хрестик у рядку формул) або клавішу <Esc>. Якщо процес друкування закінчено, то слід натиснути кнопку Ввод (зелена позначка у рядку формул) або клавішу <Enter>. 

При безпосередньому введенні формули всі її знаки послідовно набираються вручну. При введенні формули за схемою автоматичного отримання адрес клацаємо лівою кнопкою мишки на відповідній комірці, після чого її адреса автоматично потрапляє до складу формули. Якщо введена адреса має бути абсолютною, то починаємо послідовно натискувати клавішу <F4>, спостерігаємо за варіантами абсолютних адрес і зупиняємось на потрібному.

При введення формули за допомогою Мастера функций роботу починаємо з натискування кнопки Вставка функции рядка формул. Внаслідок цього отримуємо в рядку формул знак “=” і текстовий курсор, а також діалогове вікно Мастер функций – шаг 1 из 2. Указане вікно містить повний перелік функцій, із яких слід вибрати потрібну. Тепер виникає діалогове вікно Аргументы функции, в якому поле Число призначене для вказівки аргументів вибраної функції. 

Відзначимо, що діалогове вікно Мастер функций – шаг 1 из 2 містить функції, перелік яких розбито на категорії. Перелік категорій міститься у підвікні Категория:. Перелік функцій, що входять до складу відміченої категорії, представлений у другому підвікні Выберите функцию:. Відмітивши потрібну функцію у цьому підвікні, нижче у вікні можна побачити стислий опис цієї функціїї. Для отримання детальної довідки (повний опис, математичне обгрунтування, приклад застосування) треба натиснути пункт Справка по этой функции в нижній частині вікна. Внаслідок цього буде отримано область задач Справка Excel з детальною інформацією.  

Виконуючи введення аргументів функцій за допомогою діалогового вікна Аргументы функции, слід звернути увагу на кнопку з червоною стрілкою в кінці поля Число для введення аргументів. Ця кнопка дозволяє тимчасово згорнути діалогове вікно до вузенької смужки, щоб воно не заважало вибрати і відмітити аргумент в області робочого листа. Після вибору аргумента смужку можна знову розгорнути до нормального розміру за допомогою подібної ж кнопки на ній.    

Як і будь-які інші дані, формули в комірках ЕТ можна копіювати. При цьому вони автоматично настроюються на нове місце свого розташування. В багатьох випадках копіювання формули можна зробити шляхом автозаповнення. Для цього треба виділити комірку з формулою і сумістити вказівник мишки з правим нижнім кутком селектора. При цьому вказівник мишки має набути форми маленького чорного хрестика. Утримуючи ліву кнопку мишки у натиснутому стані, протягуємо вказівник мишки у потрібному напрямі на потрібну кількість комірок. Після цього кнопку мишки відпускаємо.  

4. Умовне форматування

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

Для виконання умовного форматування заданої комірки використовують команду Формат-Условное форматирование.... Внаслідок цього з’являється діалогове вікно Условное форматирование, яке містить три поля введення, що відповідають певній умові Условие 1.

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

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

Третє поле введення. Складається з однієї або з двох ділянок. В цьому полі указуються конкретні значення для порівняння у відповідності з вибраною умовою.

Тепер у діалоговому вікні Условное форматирование треба, натиснувши кнопку Формат..., отримати на екрані вікно Формат ячеек і вибрати в ньому специфічний формат виділення. При цьому є можливість указати накреслення і/або колір шрифта (вкладинка Шрифт), певний вигляд і/або колір зовнішньої границі комірки (вкладинка Граница), а також колір і/або візерунок заливки комірки (вкладинка Вид).

Натискуючи далі кнопку А также>>, ми отримуємо можливість указати до трьох умов форматування. Введення умов форматування завершується натискуванням кнопки OK.


 

А также другие работы, которые могут Вас заинтересовать

72738. Английский язык – визитная карточка современного человека 152 KB
  Значение английского языка как языка межкультурной коммуникации и международного языка имеет в настоящее время неоспоримую значимость. Вся современная мировая общественность общается на английском языке, поскольку наибольшая часть коммуникаций самого различного характера...
72740. Возможности исследовательской работы в детском объединении художественно-эстетического направления (на примере детского театра моды «Виктория») 89 KB
  Детский театр моды - форма работы с детьми в дополнительном образовании предполагающая интеграцию декоративно-прикладной художественно-эстетической и театральной деятельности детей. Главным результатом работы в объединении конечным продуктом совместной деятельности является создание коллективной коллекции.
72743. Исторический вечер «России верные сыны» 127.5 KB
  Сценарий составлен таким образом, что его можно проводить как в классе, так и в актовом зале школы как внеклассное или школьное мероприятие, посвященное 200-летию Отечественной войны 1812 года. Форма музыкально-поэтической композиции с элементами театрализации способна увлечь и заинтересовать учеников, проявить свои актёрские и поэтические таланты.
72744. Исследование жизни божьих коровок 40 KB
  1 слайд «Божья коровка, улети на небо, принеси нам хлеба!» - кто из нас не приговаривал так, посадив на палец этого красивого жучка? И крошка, добравшись до кончика пальца, улетала. Куда? Чем она занимается? Сейчас мы постараемся ответить, на эти и другие вопросы о божьей коровке.
72745. Отражение национального характера немцев в немецкой фразеологии 100 KB
  Особенности национального характера немцев. Фразеологизмы отражающие национальные особенности немцев. В связи с этим я выбрала фразеологизмы отражающие особенности национального характера немцев.
72746. Правдивая история о соли 96.5 KB
  Если предположить, что значение соли в жизни человека очень велико и значимо, её исчезновение несёт неминуемую гибель человечеству, то становится понятным, почему соль ценили с древних времен и дорожат ею в наше время, остаются актуальными используемые в речи народные пословицы, поговорки и приметы.