Робота з функціями і формулами

IF(A; Sl; S2) - умовний оператор. Якщо логічний вираз А приймає значення TRUE, тоді функція прийме значення Sl, якщо А приймає значення FALSE - то вираз S2. Наприклад,    1) IF( A4=50; SUM(B2:D2); " "). Якщо чарунка А4 має значення 50, тоді в активну чарунку буде записана сума значень чарунок В2, С2, D2, в іншому випадку ця чарунка буде пустою;

2) IF( D2>E2; "рентабельно"; "не рентабельно"). Якщо вміст  чарунки D2 більше ніж Е2, то  в активну чарунку буде записано слово рентабельно в іншому випадку - не рентабельно.

3) IF (SUM(A1 :A10)>0; SUM(A1:A10);0). Якщо SUM(A1:A10) більше нуля, то в активну  чарунку буде занесена ця сума, в іншому випадку - нуль.

Статистичні     функції.     Функція AVERAGE(X1;X2;...;XN) - обчислює середнє значення величин, які є аргументом цієї функції. Аргументом можуть бути числа, адреси чарунок, діапазон чарунок. Функція С0UNT(значення1;значення2;...) - обчислює число чарунок, які містять числові значення. Функція МАХ (число1; число2; ...) - обчислює найбільше значення із вказаного списку, MIN (число1; число2;...) - обчислює найменше значення із списку, VAR (число1;-число2;...) - обчислює дисперсію, MODE( число1;число2; ...) - знаходить значення, які найчастіше зустрічаються. Аргументами цих функцій можуть бути числа, адреси чарунок, діапазон чарунок. Наприклад, результатом =MODE (1;4;4;7) буде значення 4.

Приклади використання деяких функцій статистичного аналізу. Excel пропонує широкий діапазон засобів для аналізу статистичних даних. Такі вбудовані функції, як AVERAGE (СРЗНАЧ), MEDIAN (МЕДИАНА), MODE (МОДА), можуть бути корисні для проведення нескладного аналізу даних. Якщо вбудованих статистичних функцій недостатньо, то можна звернутися до пакету aнaлiзy. Пакет аналізу можна використовувати для створення піктограм, утворення випадкових або періодичних вибірок з набору даних, проведення регресійного аналізу, одержання основних статистичних характеристик для вибірки, генерації випадкових чисел з різним розподілом, а також застосувати до cвoїx даних перетворення Фур’є та інші перетворення.

Щоб одержати доступ до пакету аналізу потрібно виконати команду TOOLS (cepвic) => DATA ANALYSIS (аналіз даних). Якщо команда аналіз даних відсутній, то виконуємо команду TOOLS (cepвic) => ADD-INS (налаштування) i aктивізуємо опцію ANASLYSIS TOOLPAK-VBA (пакет аналізу -VBA). Якщо ця опція відсутня, то її потрібно доінсталювати, використовуючи, MICROSOFT EXCEL SETUP.

Генерація випадкових чисел. Вбудована функція RAND (СЛЧИС) генерує випадкові числа, які мають рівномірний розподіл в інтервалі між O i l. іншими словами всі значения між 0 i 1 мають однакову ймовірність появи. Пакет аналізу дозволяє генерувати випадкові числа з розподілами відмінними від piвномipнoro. Такі множини випадкових чисел використовуються в методі Монте - Карло i при інших типах моделювання. Є шість типів розподілу UNIFORM (р;вномірний), NORMAL (нормальний), BERNOULLI (Бернуллі), BINOMIAL (біномінальний), POISSON (Пуассона), DISCRETE (дискретний, який визначається користувачем). Крім того, можна використати PATTERNED (модельний), розподіл для одержання невипадкових чисел у заданих інтервалах. Модельний розподіл може служити в якості альтернативи команді EDIT (редагування) => FILL (заповнення) => SERIES (прогресія). Для генерації випадкових чисел виконуємо команду TOOLS (cepвic) => DATA ANALYSIS (аналіз даних) i вибираємо команду RANDOM NUMBER GENERATION (reнерація випадкових чисел) i клацаємо на ОК. З’явиться вікно, вид якого залежить від вибору типу розподілу, вибраного у рядку DISTRIBUTION (розподіл).

Інструмент DESCRIPTIVE STATISTIES (описова статистика). Інструмент описова статистика створює таблицю основних статистичних характеристик для однієї або декількох множин вхідних значень. Результатом роботи цього інструменту є таблиця, в якій обчислені MEAN - середнє арифметичне, яке відповідає функції AVERAGE(NUMBER1;...; NUMBERN) (СРЗНАЧ(ЧИСЛ01;...;ЧИСЛОN), STANDARD ERROR -стандартна помилка, аналогічно функції STEYX(KNOWN_Y’S; KNOWN_X’S) (CTOШУХ(відоме_значення_У; відоме_значення_У)) - обчислює середнє арифметичне, але без врахування нормальності початкових даних; MEDIAN медіана, аналогічна функцї MEDIAN (NUMBER1;...; NUMBERN) (МЕДИАНА-(ЧИСЛ01;...;ЧИСЛОN) - обчислює медіану чисел; MODE мода, аналогічна функції MODE(NUMBER1;...;NUMBERN) MOДА(ЧИСЛ01;...;ЧИСЛОN) - обчислює значення, яке частіше інших зустрічається в множині чисел; STANDARD DEVTVATION - стандартне відхилення, аналогічна функції STDEV(NUMBER1;...; NUMBERN) (СТАНДОТК-ЛОН(ЧИСЛ01 ; ...;ЧИСЛОN)) - оцінює розсіювання випадкової величини; SAMPLE VARIANCE - дисперсія вибірки, аналогічна функції VAR(NUMBER1;...; NUMBERN)ДИCП(ЧИCЛ01;...;ЧИCЛON)),KURTOSIS - ексцес, аналогічна функції KURT(NUMBER1;...; NUMBERN) (ЭКСЦЕСС-(ЧИСЛ01;...;ЧИСЛON)) - обчислює ексцес множини даних; SKEWNESS - коефіцієнт асиметрії, аналогічна функції SKEV(NUMBER1;...; NUM-BERN)CKOC (ЧИСЛ01;...;ЧИСЛОN) - обчислює асиметрію розподілу; RANGE - інтервал, обчислює різницю між. максимальним i мінімальним значеннями; MINIMUM- мінімум, аналогічна функції MIN(NUMBER1;...;NUMBERN)(MИH(ЧИСЛ01;...;ЧИСЛON)) - обчислює мінімальне значення, MAXIMUM - максимум, аналогічна функції MAX(NUMBER1;...; NUMBERN) МАКС(ЧИСЛ01;...;ЧИСЛОN) - обчислює максимальне значення, SUM - обчислює суму, аналогічна функції SUM(NUMBER1;...;NUMBERN)(CУMM(ЧИСЛ01;...;ЧИСЛON)) - обчислює суму значень; COUNT - рахунок, аналогічна функції COUNT(NUMBER1;...; NUMBERN)СЧЕТ(ЧИСЛ01;...;ЧИСЛОN) -обчислює кількість чисел у списку аргументів; LARGEST - найбільший, аналогічна функції LARGE (АРРАУ;К)(НАИБОЛЬШИЙ(МАССИВ;К)) - обчислює К-те найбільше значення з масиву даних; SMALLEST найменший , аналогічна функції SMALL (ARRAY;K) (НАИМЕНЬШИЙ(МАССИВ;К)) - обчислює найменше К-те значення з масиву даних. Для використання інструменту описова статистика виконуємо команду TOOLS (cepвic) =>DATA ANALYSIS (аналіз даних), a пoтім вибираємо команду DESCRIPTIVE STATISTIES (описова статистика). Клацаємо на ОК. з’явиться вiкно DESCRIPTIVE STATISTIES (описова статистика).

У рядку INPUT RANGE (вхідний інтервал) вказуємо інтервал, у якому розташовані вхідні дані. з допомогою. опції GROUPED BY (групувати по) вказуємо, де розташовані дані в COLUMNS (стовпець) або ROWS (рядок). Активізуємо опцію LABELS IN FIRST ROW (позначка в першому рядку), якщо перший рядок у вхідному діапазоні містить назви стовпців i EXCEL використовує ix для створення заголовків у вихідній таблиці. Поле OUTPUT RANGE (вихідний інтервал) використовується для того, щоб вказати місце розташування випадкових чисел. Якщо активізувати опцію NEW WORKSHEET PLY (новий аркуш), то числа розташовуються на новому аркуші. А якщо активізована опція NEW WOORKBOOK (нова робоча книга), то результати виведуться в іншому файлі. Якщо активна опція SUMMARY STATISTICS (підсумкова статистика), то висвітиться повний список характеристик. 3 допомогою опцій К-ТН LANGEST (К-тий найбільший) i K-TH SMALLEST (К-тий найменший) вибираємо найбільший елемент, на к-тій позиції, при умові що масив у пам’яті посортований у порядку спадання i найменший елемент на к-тій позиції, якщо масив у пам’яті посортований по зростанню. Якщо К = 1, то її значення співпадають з відповідними значеннями наибільшого i найменшого елементів позначеного діапазону. Клацаємо на ОК. інструмент описова статистика створює таблицю констант.

Інструмент  Гістограми. Гістограма - це діаграма, де для початкової множини значень визначається i висвітлюється число значень (частот (FREQUENCY)), які попадають в інтервали розбиття (кишені (BINS)). Числа в діапазоні чарунок повинні бути посортовані по зростанню. Числа можна отримати, виконавши команду EDIT (редагування) => FILL (заповнити) => SERIES (прогресія) або виконавши команду TOOLS (cepвic) DATA ANALYSIS (аналіз даних) i вибираємо команду RANDOM NUMBER GENERATION (генерування випадкових чисел). Тоді виконуємо команду ТООLS (сервіс) => DATA ANALYSIS (аналіз даних) i вибираємо команду HISTOGRAM (гістограма). 3’являється діалогове вікно вигляду:

У рядку INPUT RANGE (вхідний інтервал) задаємо діапазон чарунок числами, рядок BIN RANGE (інтервал кишень) можна залишити пустим. У рядку OUTPUT RANGE (вихідний діапазон) задаємо чарунку з якої починаємо побудову гістограми. Активізуємо опцію CHART OUTPUT (виведення графіка) i клацаємо на ОК. Якщо активна опція PARETO (SORTED HISTOGRAM) (Парето (посортована гістограма)), то сортуються вихідні дані в порядку спадання. Активна опція CUMULATIVE PERCENTAGE (інтегральний відсоток) дає можливість включити в таблицю частоти в відсотках для кожного інтервалу гістограми.

Piвномірний  розподіл. Виконуємо команду TOOLS (cepвic) => DATA ANALYSIS (аналіз даних) i вибираємо команду RANDOM NUMBER GENERATION (генерація випадкових чисел). У рядку DISTRIBUTION (розподіл) вибираємо UNIFORM (рівномірний) i тим самим генеруємо множину дійсних чисел, piвномірно розподілених у вказаному інтервалі, який задається початковим i кінцевим значенням. Цей інструмент можна використовувати як більш зручну альтернативу функції RAND (СЛЧИС), якщо потрібний інтервал відмінний від [0,1]. А потім з допомогою інструменту Гістограма можна побудувати графік цього розподілу.

Функції дати i часу. Функція TODAY() (СЕГОДНЯ()) результатом, якoї буде десяткове значення поточної дати. Наприклад, у А1 встановимо формат dd.mm.yy i введемо дату у вигляді 05.12.93. У чарунку В1 введемо формулу = TODAY()-А1. Результатом буде число днів ніж поточною датою i датою вказаною в чарунці А1. Для зручності можемо в чарунці В1 встановити формат YY, який дасть можливість висвітлити різницю в роках.

Контрольні  питання

1. Що таке формула?
2. Які типи операндів і які операції допустимі у формулі?
3. Які типи посилань на комірки допустимі у формулах?
4. Які переваги дає використання імен діапазонів у формулах?
5. Яким чином можна присвоїти ім’я діапазону комірок?
6. Якими способами можна вставити функцію у формулу?
7. Яким чином здійснюються обчислення формул?
8. Які типи помилок можливі в процесі обчислення формул?