. MS Excel візьме формулу в рядку формул у фігурні дужки і зробить необхідні обчислення з елементами масивів
(=МУМНОЖ(МУМНОЖ(ТРАНСП((D2:D3);А2:ВЗ); D2:D3)}
В комірці F2 буде знайдене шукане значення 196. 62. Розв’язування економічних задач за допомогою формул і вбудованих функцій Excel.
Формула — це запис дій, які треба провести з вхідними даними для отримання потрібного результату. Для внесення формули треба активізувати комірку, де має розміщуватися формула, натиснути = (Дорівнює) або на клавіатурі, або мишкою ліворуч
у рядку формул і заносити текст. Текст формули складається з адрес комірок із вхідними значеннями, поєднаних математичними, логічними або пунктуаційними знаками.
Якщо, наприклад необхідно скласти значення клітин А1 та В1 і вивести їх суму у клітині С1 встановіть рамку виділлення на клітину С1 і введіть формулу =В1+В2.
Зведення у ступінь ^.
Арифметичні формули можуть також містити оператори порівняння: дорівнює (=), не рівно (< >), більше (>), менше (<), не більше (<=), не менше (>=). Результатом обчислення арифметичної формули є число.
Логічні формули можуть містити вказані оператори порівняння, а також спеціальні логічні оператори:
#NOT# - логічне заперечування “НІ”
#AND# - логічне “І”
#OR# - “АБО”
Логічні формули визначають, вираз “істина” чи “помилка”. Виразу “істина” привласнюється числова величина 1, виразу “помилка” - 0.
Форматування даних – вибір форми представлення числових або символьних даних в комірці.
Змінювати формат можна у одній комірці або у цілому діапазоні. Виділяється комірка або діапазон комірок,
• вибирається пункт меню Формат/Ячейки або
• права кнопка на виділеній комірці або діапазоні і пункт з динамічного меню Формат ячеек...
Відкриється вікно форматів з декількома вкладниками: Вкладника Число використовується для надання даним форматів, що перелічені у списку Числовые форматы. Тут можна вибрати основний формат і його додаткові параметри.
Вкладника Выравнивание використовується для вирівнювання даних по горизонталі та вертикалі (зона ліворуч угорі), для вибору кута розташування даних у комірці (зона праворуч), а покажчики, розташовані ліворуч внизу, дозволяють поєднувати комірки у суміжному діапазоні, розташовувати текст у комірках в декілька рядків та автоматично змінювати ширину стовпчиків, якщо дані не зміщуються у ньому.
Вкладника Шрифт дозволяє вибирати параметри шрифта для виділеного діапазону.
Вкладинка Граница пропонує оформлення виділеного діапазону рамками різного типу.
Вкладинка Вид опрацьовує оформлення тла діапазону різним
кольором.
Вкладинка Защита використовується для захисту змісту комірок у діапазоні (спрацьовує тільки після захисту аркуша).
Під функцією розуміють залежність однієї змінної (у) від іншої (х) або кілька змінних (х1, х2, ..., хn). Причому кожному набору значень змінних х1, х2, ..., хn буде відповідати єдине значення певного типу залежностей змінної у. Функції вводять у таблицю у склад формул або окремо.
Деякі дії з даними у таблиці виконуються за допомогою функцій, наприклад, обчислення середнього, мінімального, максимального значення, суми
значень із заданого діапазону значень
тощо.
Загальний вигляд функції:
=ім'я функції(параметр/и)
У електронних таблицях можуть бути представлені такі види функцій:
- математичні функції виконують різні математичні операції, наприклад, обчислення логарифмів, тригонометричних функцій та ін.;
- статистичні функції виконують операції по обчисленню параметрів випадкових величин або їх розподілень, представлених множиною чисел, наприклад, стандартного відхилення, середнього значення, медіани і т.п.
- текстові функції виконують операції над текстовими рядками або послідовністю символів, обчислюючи довжину рядка, перетворюючи прописні літери у рядкові і т.п.
- логічні функції використовуються для побудови логічних виразів, результат яких залежить від того, чи є істиною умова, що перевіряється.
- фінансові функції використовуються у складних фінансових розрахунках, наприклад, визначення форми дисконту, розміру щомісячних виплат для погашення кредиту, обчислення амортизаційних відрахувань та ін..
- функції дати і часу та ін.
Всі функції мають однаковий формат запису і включають ім'я функції і список розділених комою аргументів, що знаходяться у круглих дужках.
Будь-яка функція виконується або внесенням її тексту з клавіатури у активну комірку, або її викликом з активної комірки за допомогою Майстра функцій.
Майстер функцій — це спеціальна програма, за допомогою якої можна вибрати потрібну функцію і виконати її, вказавши всі потрібні параметри.
Майстер функцій можна завантажити через пункт меню
Вставка/Функція..., або за допомогою fx кнопки на панелі інструментів.
Приклади функцій:
SUM (список) – статистична функція визначення суми всіх числових значень у списку, який може складатися з адрес комірок і блоків, а також числових значень.
AVERAGE (список) – статистична функція визначення середнього арифметичного значення всіх перерахованих у списку величин.
МАХ (список) – статистична функція, результатом якої є максимальне значення у вказаному списку.
IF (умова, істина, помилка) – логічна функція, що перевіряє на істинність задану логічну умову.
63. Використання апроксимації залежності за допомогою лінійної регресії для прогнозування і статистичного моделювання.
64.Поняття нарощування і дисконтування
65.Прогнозування в таблицях даних з використанням сценаріїв. Робота зі сценаріями. Прогнозування в таблицях даних з використанням таблиці підстановки.
66. Пошук даних за критеріями.
критерії бувають двох типів.
1. Критерії обчислення – це критерії, що є результатом обчислення формули. Наприклад, інтервал критеріїв =F7>СРЗНАЧ(7:21 ) виводить на екран рядки, що мають у стовпці F значення більше, ніж середнє значення розмірів у вічках F7:F21 . Формула повинна повертати логічне значення ЛОЖЬ або ИСТИНА . При фільтрації будуть доступні тільки ті рядки, значення яких будуть додавати формулі значення ИСТИНА .
2. Критерії порівняння – це набір умов для пошуку, використовуваний для витягу даних при запитах за прикладом. Критерій порівняння може бути послідовністю символів (константою) або вираженням (наприклад, Ціна > 700 ).
Для пошуку за допомогою форми даних записів, що відповідають критерію, необхідно:
виділити вічко в таблиці;
у меню Данные вибрати команду Форма ;
натиснути кнопку Критерии ;
у полях редагування увести критерії для пошуку даних;
для виводу на екран першого запису, що відповідає критерію, натиснути кнопку Далее ;
для виводу на екран попереднього запису, що відповідає критерію, натиснути кнопку Назад ;
для пошуку записів у переліку по іншому критерії натиснути кнопку Критерии і увести новий критерій;
по закінченні натиснути кнопку Закрыть .
Щоб знову одержати доступ до усіх записів переліку необхідно натиснути кнопку Критерии , а потім натиснути кнопку Правка.
Команда Фильтр меню Данные дозволяє відшукувати і використовувати потрібну підмножину даних у переліку. У відфільтрованому переліку виводяться на екран тільки ті рядки, що містять визначене значення або відповідають визначеним
критеріям, при цьому інші рядки виявляються приховані. Для фільтрації даних використовуються команди Автофильтр і Расширенный фильтр з пункту Фильтр меню Данные
67.Використання засобу „АВТОФИЛЬТР”. Використання засобу „РАСШИРЕННЫЙ ФИЛЬТР” для пошуку інформації за складними критеріями
Для допомогою фільтрів можна вибрати записи, що містять потрібні для пошуку дані. Фільтри бувають двох типів: Авто-фільтр і Розширений фільтр.
Автофільтр може працювати з простими критеріями (одна умова), складними критеріями (максимум дві умови за одним полем) і складеними критеріями, які можуть містити стільки умов, скільки полів у списку, але при цьому використовують максимум дві умови за одним полем. Критерії заносяться під час роботи Автофільтра, а результати запиту для подальшого збереження користувач може самостійно скопіювати в інше місце поточного робочого аркуша або на новий аркуш.
Данные → Фильтр → Автофильтр.
На відміну від Автофільтра, де критерії заносяться під час роботи фільтра, Розширений фільтр може працювати тільки тоді, коли критерії для пошуку даних попередньо створені користувачем і занесені у визначений діапазон комірок таблиці. Цей діапазон бажано має міститися над списком і має бути відокремленим від списку щонайменше одним порожнім рядком.
Простий критерій складається з двох комірок, розміщених у таблиці вертикально: верхня комірка містить точну копію заголовка поля списку, в якому перевіряється умова пошуку даних, а нижня комірка містить безпосередньо саму умову пошуку.
1. Виорати зі списку всі записи про клієнта Васильєва.
За правилами створення критеріїв діапазон для них оуде розміщуватися у робочому аркуші по рядках з першого по дев'ятий.
Даний запит включає одну умову (прізвище клієнта — Васильєв), тому створюється простий критерій для пошуку в полі Прізвище клієнта прізвища Васильєв.
У комірку В1 копіюється заголовок поля Прізвище клієнта, а в комірку В2 — умова для пошуку — Васильєв:
Таким чином, діапазон створеного простого критерію розміщується у комірках В1 :В2.
Складений критерій — це сукупність декількох складних критеріїв — містить по декілька умов на декілька полів.
4. Вибрати зі списку всі записи про клієнта Васильєва, що замовляв телефони, клієнта Петрова, що замовляв принтери, та всі замовлення клієнта Іванова.
У даному випадку створюється складений критерій, що містить по три умови на два поля. Під час виконання запиту в полі Прізвище клієнта має міститися або прізвище Васильєв і одночасно в полі Назва виробу — Телефон, або прізвище Петров і одночасно Принтер, або прізвище Іванов і будь-яка назва виробу.
У комірку СЗ копіюється назва виробу Принтер, а в комірку С4 заноситься знак * (який означає будь-яке текстове значення):
Діапазон створеного критерію — комірки В1 :С4.
На відміну від Автофільтра, де критерії заносяться під час роботи фільтра, Розширений фільтр може працювати тільки тоді, коли критерії для пошуку даних попередньо створені користувачем і занесені у визначений діапазон комірок таблиці. Цей діапазон бажано має міститися над списком і має бути відокремленим від списку щонайменше одним порожнім рядком.
Складний критерій містить у собі декілька простих критеріїв, сполучених або по вертикалі — декілька умов на одне поле, або по горизонталі — по одній умові на декілька полів.ты в диапазон” зазначають адресу вільної комірки. ОК. З’являється нова БД.
2. Вибрати зі списку всі записи, що стосуються або клієнтів Васильєва, або Петрова, або Іванова.
Даний запит включає три умови, що накладаються на поле Прізвище клієнта (прізвище — або Васильєв, або Петров, або Іванов), тому створюється складний критерій із трьох простих, які поєднані зв'язкою ИЛИ.
У комірку ВЗ копіюється умова для пошуку — Петров, а в комірку В4 — Іванов:
Діапазоном створеного складного критерію будуть комірки В1:В4.
Обчислювальний критерій може бути різновидом простого або складного критерію, де як умова виступає функція або формула. Записи за таким критерієм вибираються зі списку за значенням, отриманим у результаті обчислення цієї формули або функції.
8. Вибрати зі списку всі записи про максимальну і мінімальну суму замовлення.
У комірку Е6 копіюється заголовок поля Сума замовлення, у комірку Е7 заноситься функція
= МАКС(абсолютний діапазон числових значень цього поля), а у комірку Е8 — функція
=МИН(абсолютний діапазон числових значень цього поля).
Замість тексту функцій у критерію будуть показані обчислені за цими функціями значення.
68.Використання функцій баз даних (БДСУММ, ДСРЗНАЧ, БСЧЁТ, ДМИН, ДМАКС, БИЗВЛЕЧЬ) для пошуку інформації та підрахунків
СРЗНАЧ(параметр) - Середнє значення чисел
=СЧЕТЕСЛИ(діапазон для перевірки умови; «умова») - Кількість разів виконання умови
У процесі роботи цієї функції проглядається діапазон для
перевірки умови і підраховується кількість виконань умови. Наприклад,
• визначити, скільки разів робив замовлення клієнт Іванов: Діапазон для перевірки — В12:В24, умова — «Іванов». Функція має такий вигляд: =СЧЕТЕСЛИ(В12:В24;«Іванов»)
• визначити, скільки разів робились замовлення до 1998 року: Діапазон для перевірки — С12:С24; умова — «<01.01.98». Функція має такий вигляд:
=СЧЕТЕСЛИ(С 12:С24;«<01.01.98»)
=НАИМЕНЬШИЙ(параметр;N) - N-e мінімальне з чисел
=МАКС(параметр) - Максимальне з чисел
=СЧЕТ(параметр 1 ;параметр2;... ;параметр30) - Кількість чисел серед параметрів
Для підрахунку суми числових значень у певному діапазоні використовується функція СУММ (діапазон комірок), проте вона підраховує суму із усіх значень, розташованих у
вказаному діапазоні. Коли ж потрібно підрахувати суму числових значень, які відповідають певній умові у таблиці, використовується функція СУММЕСЛИ, яка має такий вигляд: =СУММЕСЛИ(діапазон для перевірки умови; «умова»; діапазон для підрахунку суми).- Сума чисел за умовою
Наприклад, за даними попередньої таблиці треба підрахувати суму податку, що сплатив клієнт Іванов.
Діапазоном для перевірки умови є стовпчик з прізвищами клієнтів — В12:В24, серед яких зустрічається (або ні) потрібне прізвище.
