5213

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

Реферат

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

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

Украинкский

2012-12-04

151.21 KB

67 чел.


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

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.


 

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

40225. Планирование затрат 29.5 KB
  Планирование затрат. В сущности планирование затрат является одним из основных методов управления как непосредственно производством так и общеэкономической деятельностью предприятия. Вопросы планирования затрат на производство и реализацию продукции работ услуг являются непростыми задачами как стратегического так и оперативного экономического анализа. Необходимость предъявления повышенных требований к организации учета затрат на производство и реализацию продукции обусловлена тем что от правильности и своевременности определения...
40226. Прибыль как экономическая категория. Подходы к управлению прибылью 35.5 KB
  На уровне предприятия чистый доход принимает форму прибыли. Наличие прибыли на предприятии означает что его доходы превышают все расходы связанные с его деятельностью. Доля чистой прибыли оставшаяся в распоряжении предприятия после уплаты налогов и других обязательных платежей должна быть достаточной для финансирования расширения производственной деятельности научнотехнического и социального развития предприятия материального поощрения работников. Таким образом планирование прибыли является элементом системы управления прибыли которую...
40227. Управление прибылью на основе анализа безубыточности 36.5 KB
  Управление прибылью на основе анализа безубыточности. Этот метод представляет собой еще один затратный метод ценообразования при котором производится определение точки безубыточности brekeven и целевой прибыли. Анализ безубыточности осуществляется на основе исследования соотношения затраты объем прибыль которое отражает взаимосвязь затрат выручки объема производства и прибыли. По существу этот метод сводится к определению точки безубыточности производства критической точки такого объема продукции при реализации которого...
40228. Использование чистой прибыли 24.5 KB
  Использование чистой прибыли. В начале года следующего за отчетным собственники организации принимают решение о распределении чистой прибыли. Для учета нераспределенной прибыли непокрытого убытка предусмотрен счет 84 Нераспределенная прибыль непокрытый убыток . Для этого в резерв ежегодно отчисляется не менее 5 чистой прибыли.
40229. Подходы к планированию прибыли 25.5 KB
  Подходы к планированию прибыли. Важное место в финансовом планировании занимает этап планирования прибыли. Подходы к планированию прибыли зависят от параметров производственной хозяйственной и финансовой деятельности организации предприятия. Нужно изучить наиболее существенные взаимосвязи в экономике предприятия и понять их влияние на размер прибыли.
40230. Налогообложение организаций 58.5 KB
  Налог обязательный индивидуально безвозмездный платеж взимаемый с организаций и физических лиц в целях финансового обеспечения деятельности государства и или муниципальных образований. Основные функции налогов: фискальная обеспечения государства финансовыми ресурсами и регулирующая нацелена на достижение посредством налоговых механизмов тех или иных задач налоговой политики государства. Налоги оказывают значительное влияние на формирование финансовых результатов предприятия. Если налоговое бремя предприятия чрезмерно велико...
40231. Денежные потоки организации. Методы расчета. Подходы к управлению денежными потоками 27.5 KB
  Анализ денежных средств и управление денежными потоками является одним из главных направлений деятельности компании. Управление денежным потоком включает в себя расчет времени обращения денежных средств финансовый цикл анализ денежного потока его прогнозирование определение оптимального уровня денежных средств составление бюджетов денежных средств и т. Перечислим основные задачи анализа потока денежных средств: Оценка динамики и структуры источников поступления и направления расходования денежных средств. Оценка факторов оказывающих...
40232. Рентабельность как показатель эффективности деятельности организации. Система показателей. Пути повышения рентабельности 41.5 KB
  Пути повышения рентабельности.Коэффициент рентабельности рассчитывается как отношение прибыли к активам ресурсам или потокам её формирующим. Показатели рентабельности часто выражают в процентах. Система показателей Показатели рентабельности характеризуют финансовые результаты и эффективность деятельности предприятия.
40233. Инвестиционный аспект деятельности организации. Инвестиционная политика на предприятии 35.5 KB
  Временный аспект инвестиционной деятельности требует рассмотрения инвестиционного процесса и его составляющих. В первом случае инвестиционный процесс реализуется путем разработки инвестиционной политики организации во втором – путем формирования и реализации конкретных информационных проектов. С одной стороны разработка и принятие к реализации инвестиционных проектов не должны противоречить выработанной инвестиционной политике. Кроме того это выражается в единстве и взаимосвязи целей инвестиционной политики и инвестиционных проектов.