Етапи розробки проекта інформаційно-аналітичної податкової системи

46.Табличний процесор MS Excel. Застосування статистичних функцій для розв’язання економічних задач. Табличний процесор-це універсальний засіб для автоматизації розрахунків під час роботи з табличними даними.Microsoft Excel-це засбі для роботи з електронними таблицями,що містить зручний апарат для обробки даних у вигляді великого набору ф-цій,аналізу даних інструменти для опрацювання тексту,створення ділової графіки,роботи з базами даних та інше. Під час складання звітних відомостей використовуються ста­тистичні функції, такі як РАНГ і ЧАСТОТА. Функція РАНГ (Rank) по­вертає ранг числа в списку чисел. Ранг числа – це положення його величини у впорядкованому списку. Якщо список відсор­тувати, то ранг числа буде його позицією в списку. Синтаксис Рангу (число; посилання; порядок):число – число (або адреса комірки, що містить число), для якого визначається ранг; посилання – комірки або посилання на список чисел. Нечислові значення у посиланні ігноруються; порядок – число, що визначає спосіб впорядкування. Якщо значення параметра Порядок дорівнює 0 або його немає, то MS Excel визначає ранг числа так, ніби посилання було списком, відсортованим за спаданням. Якщо значення пара­метра Порядок є будь-яким ненульовим числом, то MS Excel визначає ранг числа таким чином, якби посилання було спис­ком, відсортованим за зростанням.За допомогою функції РАНГ однаковим числам надається однаковий ранг. За допомогою функції ЧАСТОТА (Frequency) повертається розподіл частот у вигляді вертикального масиву. Для такої кількості значень і кількості інтервалів частотний розподіл підраховує, скільки початкових значень потрапляє в кожен інтервал.Синтаксис ЧАСТОТА (масив даних; двійковий масив): - масив_даних – масив або посилання на дані, для яких обчислюються частоти; якщо масив_даних не містить значень, то функція ЧАСТОТА повертає масив нулів;- двійковий _ масив – масив або посилання на безліч ін­тервалів, в які групуються значення аргументу масив _ да­них. Якщо у двійковому_масиві немає значень, то функція ЧАС­ТОТА повертає кількість елементів в аргументі двійкового ма­сиву.

47.Табличний процесор Microsoft Excel. Аналіз даних: підбір параметра, таблиці підстановки, консолідація даних. Функція "Подбор параметра" Підбір параметра - спосіб пошуку певного значення комірки шляхом зміни значення в іншій комірці. При підборі параметра значення в комірці змінюється до тих пір, поки формула, залежна від цієї комірки, не поверне необхідний результат. Microsoft Excel змінює значення в одній конкретній комірці доти, поки формула, залежна від цієї комірки, не повертає потрібний результат. Функція "Таблица подстановки" Ця функція використовується для дослідження впливу одного чи двох параметрів на значення певної величини, яка задана формулою, яка містить ці параметри. Якщо параметр один, то список його значень і список значень досліджуваної величини утворюють таблицю, яка називається таблицею підстановки з однією змінною, що відображає функціональну залежність S=f(P). Функція Консолідація даних При консолідації даних об'єднуються значення з декількох діапазонів даних. Наприклад, якщо є аркуш витрат для кожного регіонального представництва, консолідацію можна використовувати для перетворення цих даних в аркуш корпоративних витрат. Консолідувати дані в Microsoft Excel можна декількома способами. Найзручніший метод полягає в створенні формул, що містять посилання на комірки в кожному діапазоні об'єднаних даних. Формули, що містять посилання на декілька робочих аркушів, називаються тривимірними формулами. Консолідацію по розташуванню слід використовувати у випадку, якщо дані всіх початкових областей знаходяться в одному місці і розміщені в однаковому порядку; наприклад, якщо є дані з декількох аркушів, створених на основі одного шаблону. Якщо встановлено автоматичне оновлення консолідації при зміні початкових даних, змінити набір комірок і діапазонів, що входять в консолідацію, неможливо. Дана функція доступна тільки при оновленні консолідації вручну. Консолідацію по категорії слід використовувати у випадку, якщо вимагається узагальнити набір робочих аркушів, що мають однакові заголовки рядів і стовпців, але різну організацію даних. Цей спосіб дозволяє консолідувати дані з однаковими заголовками зі всіх аркушів.

48.Табличний процесор Microsoft Excel. Функція Поиск решения. Функція Поиск решения є частиною блоку задач, який називають аналізом "що-якщо". Процедура пошуку рішення дозволяє знайти оптимальне значення формули, яка міститься в комірці, яка називається цільовою коміркою.Ця процедура працює з групою комірок, прямо або опосередовано пов’язаних з формулою в цільовій комірці.Щоб одержати за формулою, що міститься в цільовій комірці,заданий результат, процедура змінює значення у впливаючих на це значення комірках. Щоб звузити безліч значень, що використовуються в моделі, застосовуються обмеження.Ці обмеження можуть посилатися на інші впливаючі комірки.Процедуру пошуку рішення можна використовувати для визначення значення впливаючої комірки, яке відповідає екстремуму залежної комірки — наприклад, можна змінити об’єм планованого бюджету реклами і побачити, як це вплине на проектовану суму витрат. Задач такого змісту існує дуже багато у фінансово-економічній сфері, сфері маркетингу та менеджменту.Ці задачі дуже важливі і тому важливо вміти їх розв’язувати.Середовище MS Excel для розв’язання таких задач надає до послуг користувачів функцію-надбудову Поиск решения. Щоб викликати цю функцію-надбудову, потрібно відкрити меню Сервис і вибрати пункт Поиск решения,за умови, що при інсталяції програмного засобу MS Excel надбудови встановлені на вашому комп’ютері.Засіб пошуку рішення Microsoft Excel використовує алгоритм нелінійної оптимізації Generalized Reduced Gradient (GRG2), розроблений Леоном Ласдоном (Leon Lasdon, University Texas at Austin)і Аланом Уореном (Allan Waren, Cleveland State University).Алгоритми сімплексного методу і методу «branch-and-bound» для вирішення лінійних і цілочисельних задач з обмеженнями розроблені Джоном Уотсоном (John Watson) і Деном Філстра (Dan Fylstra) з Frontline Systems, Inc.

49.Використання табличного процесора MS Excel для для пошуку найкращого рішення розв’язання оптимізаційних задач. Електронні таблиці дають можливість користувачу вирішувати оптимизаційні задачі різного типу без витрат часу на опис і програмування. Якщо користувач добре розуміє сутність задачі, що стоїть перед ним, її тип і метод рішення, а також володіє спеціальним інструментарієм електронної таблиці, то рішення являє собою зручну й ефективну процедуру. На етапах ознайомлення з умовою задачі і планування рішення, після введення вихідних даних, у діалозі з Excel користувач вказує комірку цільової функції і її екстремум, задає комірку для видачі рішення, уводить нескладні арифметичні формули і встановлює обмеження. Цей процес у сутності і є визначенням (описом) задачі, у результаті якого в Excel як би за замовчуванням створюється математична модель конкретної оптимизаційної проблеми, автоматично підтримувана наявними програмними засобами.Задачі, розв'язувані за допомогою оптимізатора, мають три характерних ознаки: наявність (1) цільової комірки; (2) змінюваних комірок; (3) обмежуючих комірок.1.Є єдина цільова комірка. У неї користувач повинний увести формулу, указавши пізніше в програмному діалозі який екстремум необхідний (максимум чи мінімум). Після завершення побудови моделі й ініціалізації розрахунку програма автоматично повинна домогтися для цієї комірки екстремального результату. Формула буде обчислювати цільовий показник, наприклад, чистий прибуток чи витрати, при автоматичному варіюванні значень інших (змінюваних) комірок. Для цільової комірки в програмному діалозі (а не в самій комірці) можна установити і конкретне цільове значення, якщо для його досягнення необхідно буде підбирати значення взаємозалежних з нею комірок. Задачі такого типу відносять до так званого аналізу – яким чином? (how саn-аналізу).2.У формулі цільової комірки повинні бути зроблені посилання на одну чи більш змінюваних комірок, від значень яких залежить результат. Вони можуть бути названі також невідомими чи змінними для рішення. Функція (Поиск решения) установлює значення змінюваних комірок так, щоб знайти для формули цільової комірки оптимальне рішення.3.Обмежуючих комірок може бути не менш однієї на кожну змінювану комірку. Може існувати і деяка кількість додаткових комірок обмежень, наприклад, обмеження по обсязі ресурсу й обмеження по попиті (мінімальний попит, максимальний попит). Типи задач для оптимізатора: Задачі про перевезення: наприклад, мінімізація витрат по доставці товарів з декількох фабрик у кілька магазинів з урахуванням попиту.Задачі розподілу робочих місць: наприклад, мінімізація витрат на утримання штату з дотриманням вимог, визначених законодавством.Задачі управління асортиментом товарів: витяг максимального прибутку за допомогою варіювання асортиментним набором товарів (при дотриманні вимог клієнтів). Аналогічна задача виникає при продажі товарів з різною структурою витрат, рентабельністю і показниками попиту.Задачі заміни чи змішування матеріалів: наприклад, маніпуляція матеріалами з метою зниження собівартості, підтримки необхідного рівня якості і дотримання вимог споживачів.Задачі лінійної алгебри: рішення лінійних рівнянь.У технологічному процесі рішення лінійної оптимізаційної задачі за допомогою Excel виділяються три типових етапи: Підготовчий (постановки задачі, підготовка табличної моделі до звертання до діалогового вікна оптимізатора, уведення даних і формул);Основний (діалог з оптимізатором для визначення цільової комірки, екстремума, змінюваних комірок, а також обмежень); Заключний (збереження результатів поточного рішення і збереження створеної моделі для можливих майбутніх рішень).

50.Табличний процесор MS Excel. Робота з базою даних: сортування, фільтрація даних, проміжні підсумки. Зведені таблиці та зведені діаграми. Функції для роботи з базою даних.Сортуванням називають перевпорядковування даних, при якому значення обраних вами полів (їх називають ключовими полями або ключами) розташовуються: числа – в порядку зростання або зменшення їх величин, тексти – в алфавітному порядку (прямому чи зворотному), дати і час - у хронологічному порядку (прямому або зворотному). Підведення підсумків можна виконувати тільки після виконання відповідного сортування даних. Якщо, наприклад, вам потрібно підвести підсумки по датах із проміжними підсумками по товарах, то і сортування повинне виконуватися в першу чергу по датах, у другу - по товарах. Сортування необхідне тому, що рядки з підсумками додаваються там, де відбувається зміна значення даних. Наприклад, підсумок по даті додавається там, де відбувається зміна дати. Отже значення, по яких підводиться підсумок, повинні групуватися разом, а не бути розкиданими по таблиці.При вирішенні багатьох задач виникає потреба здійснювати фільтрацію даних, тобто відображати й обробляти не всі рядки, наявні в таблиці, а тільки ті, що задовольняють визначеним умовам.
Excel має два засоби для фільтрації даних - автофільтр і розширений фільтр. Щоб установити автофільтр, треба помістити курсор усередину таблиці і вибрати в меню Данные опції Фильтр і Автофильтр. В результаті біля заголовків стовпців з'являться кнопки списків, що розгортаються, з яких можна вибрати наступні опції:- (Все) - знімає фільтр із даного стовпця і забезпечує вивід рядків із будь-якими значеннями в даному стовпці;- (Первые 10...) - дозволяє відфільтрувати задану кількість або заданий відсоток найбільших або найменших елементів даного стовпця;- (Условие...) дозволяє задати одну або дві умови фільтрації у формі рівності або нерівності.Якщо умов дві, то їх можна зв'язати логічною операцією І чи АБО. У першому випадку будуть відфільтровані рядки, для яких одночасно виконуються обидві умови, у другому - хоча б одна з них.У списку містяться також усі значення даних, присутніх в даному стовпці. Вибір такої опції приводить до того, що через фільтр пройдуть тільки рядки з обраним вами значенням.Для зняття автофільтра потрібно виконати ті ж дії, що і при його встановленні.Щоб використати розширений фільтр, потрібно створити допоміжну таблицю, що має таку ж шапку, як і шапка вхідної таблиці, що підлягає фільтрації. Для цього найзручніше просто скопіювати шапку вхідної таблиці.Якщо помістити в допоміжну таблицю деяке значення, то у відфільтрованій таблиці будуть показані тільки ті рядки, що містять зазначене вами значення в одноіменному стовпці.

51.Табличний процесор MS Excel. Робота з базою даних: зведені таблиці та зведені діаграми. Функції для роботи з базою даних. Технологія роботи. Приклади використання в оподаткуванні. Зведені таблиці використовуються для підсумування, аналізування, переглядання та представлення зведених даних, а зведені діаграми – для візуалізації зведених даних зі звіту зведеної таблиці, що надає змогу порівнювати дані та відстежувати закономірності. Зведені таблиці застосовують для таких цілей:виконання запитів, пов’язаних із великими обсягами даних, різними зручними способами;проміжного підсумування й обчислення сукупного значення числових даних, зведення даних за категоріями та підкатегоріями, виконання додаткових обчислень і створення настроюваних формул;розгортання та згортання рівнів даних для фокусування на результатах, а також детальний перегляд лише потрібної інформації зі зведених даних;переміщення рядків у стовпці або стовпців у рядки («зведення») для перегляду різних зведень даних джерела; фільтрування, сортування, групування й умовного форматування найкорисніших і найцікавіших даних для зосередження уваги на потрібній інформації;представлення стислих і ефективних звітів із примітками в Інтернеті або на папері. Звіт зведеної таблиці часто використовується для аналізування пов’язаних підсумків, особливо коли потрібно підсумувати велику кількість цифр – сукупні дані або проміжні підсумки дають змогу розглянути дані з різних точок зору й порівняти цифри або схожі дані База даних – список зв’язаних даних, в якому рядки даних є записами, а стовпці – полями. Верхній рядок бази даних міс­тить імена полів запису.Для роботи з базами даних використовується категорія фун­кцій Работа с базой данных, що нараховує 12 стандартних функцій. Розглянемо деякі важливі функції цієї категорії. БДПРОИЗВЕД (база_данных; поле; критерий) – перемно­жує значення в стовпці списку або бази даних, які задовольня­ють задані умови. БДСУММ (база_данных; поле; критерий) – підсумовує чис­ла в стовпці списку або бази даних, що задовольняють задані умови. БИЗВЛЕЧЬ (база_данных; поле; критерий) – визначає дані бази даних, які задовольняють задані умови. Зауваження: - якщо жоден із записів не задовольняє критерій, то функ­ція БИЗВЛЕЧЬ повертає значення помилки #ЗНАЧ!.- якщо більше ніж один запис задовольняє критерій, то функція БИЗВЛЕЧЬ повертає значення помилки #ЧИСЛО!. БСЧЕТ (база_данных; поле; критерий) – підраховує кіль­кість комірок у стовпці списку або полі бази даних, в яких міс­тяться числа, що задовольняють задані умови.Аргумент «поле» не є обов’язковим. Якщо аргумент «поле»опущений, то функція БСЧЕТ підраховує кількість за­писів у базі даних, що відповідають критеріям. БСЧЕТА (база_данных; поле; критерий) – підраховує непо­рожні комірки в базі даних, які задовольняють задані умови.Аргумент «поле»не є обов’язковим. Якщо аргумент «поле» опущений, то функція БСЧЕТА підраховує кількість записів у базі даних, що відповідають критеріям. ДМАКС (база_данных; поле; критерий) – повертає найбіль­ше число в стовпці списку або полі бази даних, яке задовольняє задані умови. ДМИН (база_данных; поле; критерий) – повертає якнай­менше число в стовпці списку або полі бази даних, що задо­вольняє задані умови. ДСРЗНАЧ (база_данных; поле; критерий) – обчислює зна­чення в стовпці списку або полі бази даних, яке задовольняє задані умови.З’ясуємо сутність аргументів функцій. База данных – це діапазон комірок, що належать базі даних. Поле – визначає поле, що використовується функцією. Ар­гумент «поле» може задаватися як текст із назвою стовпця в подвійних лапках, наприклад «Назва фірми» в наведеному прикладі бази даних, або як число, що задає положення стовпця в списку: 1 – для першого поля, 2 – для другого поля тощо. Критерий – діапазон комірок, в якому містяться умови, що задаються; у ньому може бути обчислювальне поле. Будь-який діапазон, що містить принаймні одну назву стовпця й одну комірку під назвою стовпця з умовою, можна використо­вувати як аргумент.Крім цих функцій під час роботи з базою даних застосову­ються також функції деяких інших категорій, наприклад фун­кції СУММЕСЛИ та СЧЕТЕСЛИ.

Дата добавления: 2015-05-24