Як створити поле, що обчислюється, в excel. Стовпці, що обчислюються в Transact-SQL Як створити обчислюване

У цій статті говоримо про поля, що обчислюються в запити Access. У запиті, як і в таблиці, для кожного запису можуть проводитись обчислення з числовими, рядковими значеннями або значеннями дат з використанням даних з одного або кількох полів. Результат обчислення утворює в таблиці запиту нове поле, що обчислюється. На відміну від обчислюваних полів таблиці, поля, що обчислюються, у вихідних таблицях бази даних нових полів не створюють. При кожному виконанні запиту виробляються обчислення з урахуванням поточних значень полів.

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

Завдання 1.У таблиці ТОВАР є поля ЦІНА та СТАВКА_ПДВ, обчисліть ціну з урахуванням ПДВ та порівняйте її з отриманою в обчислюваному полі таблиці Ціна з ПДВ.

1. Створіть у режимі конструктора запит на вибірку таблиці ТОВАР. Перетягніть у бланк запиту поля НАІМ_ТОВ, ЦІНА, СТАВКА_ПДВ та Ціна з ПДВ (рис. 4.6).
2. Для підрахунку ціни з урахуванням ПДВ створіть поле, що обчислюється, записавши в порожньому осередку рядка Поле (Field) вираз [ЦІНА]+[ЦІНА]*[СТАВКА_ПДВ].
3. Для відбору записів зі значенням вище 5000 у полі, що обчислюється, у рядок Умова відбору (Criteria) введіть > 5000
4. Після введення виразу система за умовчанням формує ім'я поля, що обчислюється Вираз 1, яке стає заголовком стовпця в таблиці з результатами виконання запиту. Це ім'я вставиться перед виразом [ЦІНА]+[ЦІНА]*[СТАВКА_ПДВ]. Для кожного нового обчислюваного поля у запиті номер виразу збільшується на одиницю. Ім'я поля, що обчислюється, відокремлюється від виразу двокрапкою. Для зміни імені встановіть курсор миші в полі бланка запиту, що обчислюється, і натисніть праву кнопку миші. У контекстно-залежному меню виберіть Властивості(Properties) поля та в рядок Підпис (Caption) введіть нове ім'я поля ― Ціна з ПДВ1. Тепер у таблиці з результатами виконання запиту в заголовку стовпця, що обчислюється, відобразиться це ім'я. Ім'я поля може бути виправлено безпосередньо у бланку запиту.
5. Для відображення результату виконання запиту натисніть кнопку Виконати(Run) у групі Результати(Results). Обчислюване поле таблиці та запиту мають однакові значення.
6. Змініть в одному із записів запиту ціну товару. Значення в обох полях, що обчислюються, будуть моментально перераховані.
7. Для формування складного вираження у полі, що обчислюється, або умови відбору доцільно використовувати будівельник виразів. Побудовник дозволяє вибрати необхідні у виразі імена полів із таблиць, запитів, знаки операцій, функції. Видаліть вираз у полі, що обчислюється, і використовуйте будівельник для його формування.
8. Викличте будівельник виразів (Expression Builder), натиснувши кнопку Будівник(Builder) у групі Налаштування запиту(Query Setup) стрічки Конструктор (Design), або вибравши Побудувати(Build) у контекстно-залежному меню. Курсор миші повинен бути встановлений попередньо в комірці введення виразу.
9. У лівій частині вікна Побудовник виразів(Expression Builder) (рис. 4.7) виберіть таблицю ТОВАР, на якій побудовано запит. Праворуч відобразиться список її полів. Послідовно вибирайте потрібні поля та оператори, подвійним клацанням вставляючи у вираз. Вираз сформується у верхній частині вікна. Зверніть увагу, що будівельник перед ім'ям поля вказав ім'я таблиці, якій воно належить, і відокремив його від імені поля знаком оклику.
10. Завершіть процес побудови виразу в полі, що обчислюється, клацнувши на кнопці ОК.
11. Збережіть запит під іменем ― Ціна з ПДВ та закрийте його.
12. Виконайте збережений , виділивши його в області навігації та вибравши в контекстному меню команду Відкрити (Open).

Завдання 2.У обчислюваних полях та умовах відбору можна використовувати вбудовані функції. У Access визначено понад 150 функцій.
Нехай необхідно вибрати всі накладні, за якими здійснювалося відвантаження у заданому місяці. В НАКЛАДНА дата відвантаження зберігається в полі ДАТА_ОТГ з типом даних Дата/час (Date/Time).

1. Створіть у режимі конструктора запит на вибірку таблиці НАКЛАДНА. Перетягніть у бланк поля НОМ_НАКЛ і КОД_СК (рис. 4.8).
2. Створіть обчислюване поле в порожньому осередку рядка Поле(Field), записавши туди один із виразів: Format([НАКЛАДНА]![ДАТА_ВІДГР];»mmmm») ― ця функція поверне повну назву місяця
   або Format([НАКЛАДНА]![ДАТА_ВІДГР];»mm») ― ця функція поверне номер місяця.
3. Для відбору накладних, виписаних у заданому місяці, в полі, що обчислюється, в рядок Умова відбору (Criteria) введіть назву місяця, наприклад березень (мал. 4.8), або номер місяця, наприклад 3 відповідно до параметра у функції Format.
4. Виконайте запит, натиснувши кнопку Виконати(Run) у групі Результати(Results) на вкладці стрічки Робота із запитами | Конструктор(Query Tools | Design).
5. Запишіть у обчислюваному полі функцію Month (НАКЛАДНА! ДАТА_ОТГ), і переконайтеся, що ця функція повертає виділений з дати номер місяця.
6. Для вибору всіх рядків, що належать до другого кварталу, в рядок Умова відбору (Criteria) введіть оператор Between 4 And 6, який визначає, чи попадає значення виразу у вказаний інтервал.
7. Запишіть у обчислюваному полі вираз MonthName (Month (НАКЛАДНА! ДАТА_ОТГ)) і переконайтеся, що функція MonthName перетворює номер місяця в його повну назву.

Для закріплення дивимось відеоурок.

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

Мал. 1. Необхідно створити в наявній зведеній таблиці поле, що обчислюється Середня виручка за годину, яке надасть нового змісту аналізованим даним

Завантажити нотатку у форматі або , приклади у форматі

Обчислюване поле – це поле даних, створюване в результаті обчислень, що ґрунтуються на існуючих полях зведеної таблиці. Обчислюване поле додається до набору даних як віртуальний стовпець. Цей стовпець не входить у вихідні дані, містить значення, що визначаються за допомогою формули, і взаємодіє з даними зведеної таблиці так само, як і з іншими полями зведеної таблиці.

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

Тепер подивіться на рис. 5.1 і запитайте себе: «Навіщо знадобилося додавати поля, що обчислюються? Чому б не використовувати звичайні формули осередків або не провести необхідні обчислення безпосередньо у вихідній таблиці для отримання інформації?» Щоб отримати відповіді на ці питання, розглянемо різні методи, які можна використовувати при створенні поля, що обчислюється, показаного на рис.1.

Спосіб 1. Додавання обчислюваного поля джерело даних

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

Я намагаюся перетворити вихідні дані на Таблицю (що зроблено на рис. 2 і в Excel-файлі, що додається). При цьому, по-перше, виділяються заголовки, по-друге, з'являються кнопки з фільтрами, що часто корисно для дослідження даних, по-третє, рядки забарвлюються через одну, і, нарешті, найважливіше – при додаванні рядків і стовпців не потрібно міняти область даних для зведеної таблиці. Як така область вказується не прямокутний діапазон, а ім'я Таблиці. У нашому випадку – Джерело(Рис. 3).

Мал. 3. Формування зведеної на основі спеціального інструменту Excel - Таблиця

Але такий спосіб має й недоліки: по-перше, не завжди зручно додати стовпець у вихідні дані; по-друге, обмежуються можливості у разі зміни структури вихідних даних (наприклад, ви експортуєте дані з 1С, і в новому експорті на один стовпець більше; цей новий стовпець затрем ваш обчислювальний стовпець).

Спосіб 2. Використання формули поза зведеною таблицею для створення обчислюваного поля

Можна додати обчислюване поле поряд зі зведеною таблицею. На рис. 4 кожен осередок у стовпці Середня виручка за годинузабезпечена формулою, що посилається на зведену таблицю. До речі, якщо при спробі послатися на комірку зведеної таблиці у вас у формулі «вилазить» функція ОТРИМАТИ.ДАНІ.ЗВЕДЕНОЇ.ТАБЛИЦІ, то ви не зможете «протягнути» формулу. Щоб подолати цю скруту ознайомтеся із заміткою.

Мал. 4. При введенні формули після створення зведеної таблиці ви по суті додаєте обчислюване поле, яке змінюється при оновленні самої зведеної таблиці

Незважаючи на те, що цей метод забезпечує додавання обчислюваного поля, яке оновлюється при оновленні зведеної таблиці, будь-які зміни в структурі зведеної таблиці можуть призвести до того, що формула стане марною. Наприклад, в результаті перетягування поля Ринок збуту в область ФІЛЬТРИ списку полів зведеної таблиці структура звіту змінюється, внаслідок чого в полі, що обчислюється, з'являється повідомлення про помилку (рис. 5). Якщо ж додати ще одне поле в область зведеної таблиці, стовпець Середня виручка за годинубуде затерто.

Спосіб 3. Безпосередня вставка поля, що обчислюється, в зведену таблицю

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

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

Звіт зведеної таблиці, показаний на рис. 6 представляє зведену таблицю, яка показана на рис. 1, за винятком того, що вона реконструйована таким чином, щоб виводити середню виручку за годину для кожної послуги та кожного ринку збуту.

Мал. 6. Створене раніше обчислюване поле залишається «дійсним» навіть за зміни структури зведеної таблиці, що призводить до відображення середньої виручки за годину для кожної послуги та кожного ринку збуту

Створення обчислюваного поля

Перед створенням поля, що обчислюється, необхідно відкрити або згенерувати зведену таблицю. У прикладі використовується зведена таблиця, показана на рис. 1, але без стовпця D. Щоб створити поле, активізуйте діалогове вікно Вставка поля, що обчислюється. Для цього натисніть на зведеній таблиці, щоб активізувати групу контекстних вкладок Робота зі зведеними таблицями. Перейдіть на вкладку Аналіздо групи Обчислення, клацніть на кнопці Поля, елементи та наборита виберіть у меню команду Обчислюване поле (мал. 7).

На екрані з'явиться діалогове вікно Вставка поля, що обчислюється(Рис. 8). У верхній частині діалогового вікна є два текстові поля: Ім'яі Формула. У цих полях слід задати ім'я поля і створити формулу, вказавши необхідні поля даних і математичні оператори. Полю, що обчислюється, присвоєно описове ім'я Середня виручка за годину. Це ім'я слід вибирати таким чином, щоб точно охарактеризувати тип виконуваної математичної операції. За промовчанням текстове поле Формуладіалогового вікна Вставка поля, що обчислюєтьсямістить вираз = 0. Перед введенням формули слід видалити нуль. Виберіть одне з полів в області Поля; у нашому випадку – Обсяг продажіві клацніть Додати поле. Назва поле з'явиться у рядку Формула. Введіть розділовий знак /, а потім виберіть друге поле – Період продажу (у годинах). Клацніть Додати, а потім ОКдля активізації нового поля, що обчислюється.

У зведеній таблиці з'явиться нове поле, що обчислюється Середня виручка за годину(Див. рис. 1). Щойно до зведеної таблиці було успішно додано нове поле. Тепер можна змінювати налаштування цього нового поля так само, як і параметри будь-якого іншого поля (наприклад, ім'я поля, числовий формат або колір).

Чи означає це, що ви додали стовпець у джерело даних? Ні. Обчислювані поля схожі на елементи Проміжна сумаі Загальна сумазведеної таблиці, що задаються за умовчанням, оскільки вони є математичними функціями, які виконують перерахунок даних при зміні чи оновленні зведеної таблиці. Полі, що обчислюються, просто імітують явно задані поля в джерелі даних. Можете перетягувати їх, змінювати настройки полів, а також використовувати разом з іншими полями, що обчислюються.

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

Створення обчислюваних елементів

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

У зведеній таблиці, показаній на рис. 9, вказується загальний обсяг продажів для певних звітних періодів (поле Звітний період). Уявіть собі, що потрібно порівняти середній обсяг продажів останніх шести періодів із середнім обсягом продажів попередніх семи періодів. Якщо бути точнішим, то нам потрібно визначити середнє для періодів Р01–Р07 та порівняти його із середнім для періодів Р08–Р13.

Помістіть покажчик у будь-яку комірку поля Звітний період,перейдіть на контекстну вкладку стрічки Аналізу групу Обчислення,та клацніть на кнопці Поля, елементи та набори. У меню виберіть команду Об'єкт, що обчислюється(Рис. 10).

Відкриється діалогове вікно (Рис. 11). Зверніть увагу, що у верхній частині діалогового вікна вказано, з яким полем ви працюєте. В даному випадку це поле Звітний період. Крім того, список містить усі елементи поля Звітний період. Надайте обчислюваному елементу ім'я, а потім створіть формулу, вказавши необхідну комбінацію елементів даних та операторів, які забезпечать правильний результат. Формула вводиться у поле Формула, а необхідні елементи даних, що застосовуються в ній, вибираються у списку Елементи. Клацніть на кнопці ОКі обчислюваний елемент додасться в зведену таблицю (рис. 12).

Мал. 11. Діалогове вікно Вставка обчислюваного елемента

Аналогічно створіть обчислюваний елемент, який представлятиме середній обсяг продажів для періодів Р08-Р13. Після цього приховайте окремі звітні періоди, залишивши лише два обчислювані елементи. Після невеликого форматування наші обчислювані елементи будуть застосовуватись для порівняння середніх обсягів продажу за попередні та наступні шість місяців (рис. 13).

Якщо приховати елементи даних, використовувані під час розрахунку обчислюваних елементів, то проміжні і загальні результати може бути неправильними.

Переваги та недоліки обчислень у зведених таблицях

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

Пріоритет виконання операцій.Як і в електронних таблицях, у формулах елементів і полів, що обчислюються, можна використовувати будь-який оператор: +, –, *, /, %, ^ і т.п. Більше того, як і в електронних таблицях, обчислення у зведених таблицях виконуються відповідно до пріоритету операторів. При виконанні обчислення, в якому комбінуються кілька операторів, наприклад (2+3) * 4/50%, Excel оцінює вираз та проводить розрахунок у визначеному порядку. Знання цього порядку вбереже вас від багатьох помилок.

Отже, порядок виконання операцій на Excel наступний:

• обробка виразів у круглих дужках;
• обробка діапазонів (:);
• обробка перетинів (областей);
• обробка об'єднань (;);
• виконання операції заперечення;
• перетворення відсоткових значень (наприклад, 50% перетворюється на 0,50);
• зведення у ступінь (^);
• множення (*) та розподіл (/); ці операції мають рівний пріоритет;
• додавання (+) та віднімання (–); ці операції мають рівний пріоритет;
• об'єднання текстових даних (&) ця операція називається також конкатенацією;
• виконання операцій порівняння (=,<>, <=, >=).

Рівнозначні оператори в одному виразі завжди виконуються в порядку прямування (зліва направо).

Розглянемо найпростіший приклад. Як відомо, вираз (2+3)*4 повертає результат 20. Якщо ж ви видалите дужки та залишите вираз 2+3*4, то Excel обчислить: 3*4 = 12 + 2 = 14.

Розглянемо інший приклад. Якщо ви введете як формулу вираз 10^2, тобто. вкажете звести число 10 у квадрат, то програма поверне як відповіді значення 100. Якщо ви введете вираз –10^2, можете очікувати повернення значення –100. Проте Excel знову поверне значення 100. Причина у цьому, що програма виконує операцію заперечення перед зведенням у ступінь, тобто. значення 10 спочатку перетворюється на –10, а результат виразу –10*–10 дійсно дорівнює 100. Використання круглих дужок у формулі –(10^2) гарантує, що програма спочатку виконає зведення ступінь і лише потім операцію заперечення, повернувши очікуваний результат – 100.

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

Функції робочих аркушів.Можна використовувати будь-яку функцію робочого аркуша, яка не використовує як аргументи посилання на комірки або іменовані об'єкти. Насправді можна використовувати будь-яку функцію робочого аркуша, яка не вимагає обов'язкового застосування посилань на комірки або на іменовані об'єкти. У цю категорію потрапляють такі функції, як РАХУНОК, РОЗЗНАЧ, ЯКЩО, І, НІ та АБО.

Константи.У обчисленнях, що виконуються у зведених таблицях, можна використовувати будь-які константи. Константи є статичні значення, які змінюються у часі. Наприклад, у наступній формулі число 5 є константою: [Продано одиниць] *5. Незважаючи на те, що значення Продано одиницьможе змінюватися на основі доступних даних, число 5 завжди буде одним і тим самим.

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

Спеціальні правила для полів, що обчислюються.Розрахунки в полях, що обчислюються, завжди виконуються по відношенню до підсумкових сум, а не до окремих елементів даних. Простіше кажучи, Excel завжди обчислює поля даних, проміжні суми та підсумкові суми перед оцінкою поля, що обчислюється. Це означає, що ваше поле, що обчислюється, завжди застосовується до підсумків для вихідних даних. Приклад, показаний на рис. 14 демонструє, яким чином це може вплинути на аналіз даних.

Мал. 14. Незважаючи на те, що обчислюване поле справедливе для окремих елементів даних, для проміжних сум його застосовувати математично некоректно

У кожному кварталі вам потрібно отримати підсумкові обсяги продажів для кожного товару, навіщо кількість проданих одиниць товару множиться на ціну одиниці цього товару. Якщо ви спочатку перегляньте дані за перший квартал, то одразу визначте проблему. Замість розрахунку суми 220+150+220+594, що дорівнює 1184, сума кількості одиниць товару множиться у сумі ціни товари, у результаті повертається неправильне значення. На жаль, цю проблему вирішити неможливо, але її можна оминути. Виключіть проміжні та підсумкові суми зі зведеної таблиці, а потім обчислити нову підсумкову суму внизу за межами зведеної.

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

Управління обчисленнями та їх підтримка у зведених таблицях

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

Зміна та видалення обчислень у зведених таблицях.При зміні параметрів обчислень або відсутності необхідності в обчислюваному полі або обчислюваному елементі можна відкрити діалогове вікно, щоб змінити або видалити обчислення. Активізуйте діалогове вікно Вставка поля, що обчислюєтьсяабо Вставка обчислюваного елемента(див. опис до рис. 7 і 10) і виберіть в ньому список, що розкривається. Ім'я(Рис. 15).

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

Мал. 16. Відкрийте діалогове вікно Порядок виконання обчислень

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

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

Мал. 18. Команда Вивести формулидозволяє легко і швидко документувати обчислення, що є в зведеній таблиці.

Нотатка написана на основі книги Джелен, Александер. . Розділ 5.

Слово Таблиця написано з великої літери, оскільки це не просто таблиця, а окремий інструмент Excel.

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

У другий випадок форма будується з урахуванням таблиці. У формі створюється нове поле, в якому як джерело даних вказується вираз, наприклад, = [Ціна за одиницю]*Кількість. При посиланні на поля у виразах обов'язковим є використання квадратних дужок, якщо назва поля складається більш ніж з одного слова. Введення виразу допускається прямо в елемент управління (поле), якщо воно коротке і довжина поля допускає. В іншому випадку вираз вводиться в комірку властивості Данівікна Властивості.При цьому можна скористатися виразником. Якщо вираз не міститься в осередку властивості, потрібно натиснути клавіші + та відкрити вікно Область введення. За потреби слід задати формат поля під час виведення на екран.

6. Створення кнопкової форми

Виберіть команду меню Сервіс/Службові програми/Диспетчер кнопкових форм.

У діалоговому вікні, де запитується, чи потрібно створювати форму кнопки натисніть ТАК.

На екрані з'явиться вікно Диспетчер кнопкових форм. У цьому вікні наведено список сторінок кнопкової форми. На початку роботи у вікні одна сторінка - Головна кнопкова форма. У вікна натисніть кнопку Змінити.

Відкривається діалогове вікно Зміна сторінки кнопкової форми. Відкоригуйте назву форми.

Натисніть кнопку Створити. З'являється вікно Зміна елемента кнопкової форми.

У полі Текствведіть найменування кнопки, що створюється. Ім'я кнопки має асоціюватися у користувача з об'єктами, з якими він має намір працювати. Друге поле Командадозволяє визначити дію, яка виконуватиметься після натискання цієї кнопки. Виберіть із розкривного списку необхідну для вашої програми команду.

Додайте кнопки для всіх форм та звітів, структурувавши їх за типом.

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

Налаштуйте програму, щоб Головна кнопкова формавідкривалася під час відкриття БД. Для цього скористайтесь командою меню Сервіс/Параметри запуску.

ПРИКЛАД:

Мал. 28. Кнопкова форма верхнього рівня

Лабораторна робота 3 створення запитів

Мета роботи -отримати практичні навички розробки запитів до БД.

Завдання:

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

Послідовність виконання роботи

1.Створення запиту на вибірку по одній таблиці за допомогою Конструктора запитів

У вікні БДклацніть на закладці Запити.

Клацніть на кнопку Створити.

У вікні Новий запитвиберіть Конструкторта натисніть кнопку OK.

У вікні Додавання таблицівиберіть із вікна списку таблицю (або запит), за якою будуватиметься запит і натисніть кнопку Додати. Закрийте поточне вікно. Далі на екрані з'явиться вікно Конструктори запитів.

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

Додати поле в запит можна так: натиснути ліву кнопкумиші на виділеному полі та, не відпускаючи її, перетягнути поле у ​​потрібне місце бланка запиту; клацнути на полі комірки в бланку запиту для відображення списку полів, що розкривається, і вибрати з нього необхідне поле; клацнути на полі комірки у бланку запиту та ввести ім'я поля.

Щоб додати у запит всіх полів таблиці, необхідно виділити поле, позначене зірочкою (*). Зірочка полегшує роботу з конструювання запиту, але призводить до ускладнення сортування та введення умов для відбору полів.

За промовчанням у всіх полях запиту сортування відсутнє. За потреби можна вказати у рядку Сортуваннябланка запиту спосіб виведення значень стовпця: за зростанням або за спаданням. При визначенні порядку сортування в кількох полях спочатку сортуються записи по крайньому лівому стовпцю, а потім по кожному наступному стовпцю зліва направо.

У рядку Виведення на екранпроставте прапорці в полях, які потрібно побачити в наборі записів після виконання запиту. За промовчанням виводяться всі поля, включені до бланку запиту.

У бланку запиту у рядку Умова відборута у рядку Абовкажіть умови вибірки із бази даних. Умови є логічні висловлювання.

Над умовами відбору, що розташовані в одному рядку, виконується логічна операція AND. Декілька умов відбору по одному полю можна задати одним із двох способів: ввести всі умови в один осередок рядка Умова відбору, з'єднавши їх логічним оператором OR, або ввести кожну умову в окремий осередок рядка Або. Перехід на наступний рядок області осередків АБОпо клавіші<>. Вирази в комірку бланка запиту вводяться з клавіатури або для їх створення використовується Побудовник виразів, який запускається командою Побудуватиз контекстного меню, пов'язаного з рядком Умова відбору(або можна натиснути кнопку Побудуватина панелі інструментів Access).

Перегляньте результати запиту, натиснувши кнопку Запуск(кнопка із зображенням знака оклику) або кнопку Видна панелі інструментів.

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

Запити, створені у режимах Конструктораі Майстригенерують команди на мові SQL. Можна переглянути ці команди, вибравши меню Видопцію режимSQL.

ПРИКЛАД:

Вибрати відомості про продукти в кількості більшій за 100, але меншій за 300 і вартістю менше 100 рублів.

Мал. 10. Конструктор запиту з умовою

Мал. 11. Результат запиту з умовою

2. Внутрішнє з'єднання по одному полю

Виберіть вкладку Запитивікна БД, натисніть кнопку Створити.Відкриється діалогове вікно Новий запит. Виберіть опцію Конструктор.

У вікні Додавання таблицівиберіть послідовно кілька пов'язаних таблиць, якими будуватиметься запит. У верхній частині Конструктори запитіввідображаються пов'язані таблиці.

Заповніть бланк запиту.

Клацніть на кнопці Запускабо Виддля відображення результатів запиту.

ПРИКЛАД:

Вибрати назви товарів, на постачання яких укладено договір, вказавши назви їх виробників та обсяг постачання.

Мал. 12. Конструктор багатотабличного запиту

Мал. 13. Результат багатотабличного запиту

3. Зовнішні з'єднання

Виконайте пункти 1-3 розділу Внутрішнє з'єднання по одному полю.

Встановіть зв'язок між таблицями, якщо раніше він не був прописаний у Схемі БД.

Клацніть на лінії об'єднання полів, щоб виділити її.

Виберіть із меню команди Видопцію Параметри об'єднання.Відобразиться діалогове вікно Параметри об'єднання.

Виберіть тип об'єднання, натисніть кнопку OK. Після цієї операції на лінії з'єднання з'явиться стрілка, що вказує тип з'єднання.

4. Включення до бланку запиту групової операції

Клацніть по кнопці Групові операції, знак  на панелі інструментів Конструктори запитів. У бланку запиту над рядком Сортуванняз'явиться рядок Групова операція.

Замініть у рядку Групова операціявстановлення Угрупованняна потрібну групову операцію, вибравши її з поля зі списком: Sum, Avg, Count, Мin, Max, StDev, Var, First, Last, Expression(вираз),Where(Умова),Group by(Угруповання).

Вираздозволяє ввести замість назви поля в комірку якийсь вираз , наприклад, для обчислення розмаху значень по деякому стовпцю Х:Max([ X]) – Min([ X]).

Умовапоказує, що це поле використовуватиметься для завдання умови відбору записів. Сама умова вибору записується в комірку рядка Умова відбору.

3. Виконайте запит.

ПРИКЛАД:

Вибрати сумарну кількість продуктів кожного виробника, вказавши його назву.

Мал. 14. Конструктор запиту із групуванням записів

Мал. 15. Результат запиту з групуванням записів

5. Включення до бланку запиту обчислюваних полів

Конструктори запитів .

Клацніть у рядку Полеза порожнім стовпцем бланка запиту. Введіть ім'я стовпця, потім двокрапку, за ним – вираз. Для введення виразу натисніть кнопку Побудувати, щоб скористатися Побудовником виразів.

У верхній частині Побудовника виразівв області введення створіть вираз.

У стовпці поля, що обчислюється, перемістіть курсор у рядок Групова операціята виберіть у розкривному списку Вираз(якщо не вибрати опцію Вираз, при виконанні запит відкриває діалогове вікно Введіть значення параметраабо повертає повідомлення про помилку).

Запустіть запит.

ПРИКЛАД:

Обчислити загальну суму замовлення за кожним контрактом (визначається як добуток кількості товару та ціни плюс витрати на транспорт).

Мал. 16. Конструктор запиту з полем, що обчислюється.

Мал. 17. Результат запиту з обчислюваним полем

6.Створення параметричного запиту

Створіть запит на вибірку у режимі Конструктори запитів.

Введіть в один із осередків рядка Умова відборупараметр, а чи не значення. Наприклад, якщо вибір записів виконується на прізвище, то в параметричний запитпо полю Прізвище в умовах відбору квадратних дужок може бути записана фраза [введіть прізвище].

Виберіть із меню Запитопцію Параметри. У діалоговому вікні Параметри запитуу лівому стовпці введіть у квадратних дужках параметр, що збігається з параметром бланку запиту Конструктори запитів(параметр можна скопіювати із бланка запиту). У правому стовпці вкажіть тип даних: натисніть клавішу , у списку виберіть тип даних.

Запустіть запит на виконання. Access відобразить вікно Введіть значення параметравведіть потрібне значення параметра і натисніть кнопку OK.

5. Запустіть запит на виконання.

ПРИКЛАД:

Вибрати список продуктів, термін яких перевищує задане значення.

Мал. 18. Конструктор параметричного запиту

Мал. 19. Введення значення терміну придатності товару

Мал. 20. Результат параметричного запиту

7. Створення перехресних запитів

1. Створіть за допомогою Конструктора новий запитта увімкніть у нього необхідні таблиці.

2. Виберіть поля, значення яких будуть використані для створення рядків та стовпців динамічної таблиці, та перетягніть їх у бланк запиту.

3. Виберіть з меню команду Запит/Перехресний.Заголовок Конструкторазміниться з Запит 1: на вибірку на Запит 1: перехресний запит . У бланку запиту з'явиться рядок Перехресна таблиця .

4. Для полів, які будуть рядками перехресної таблиці, відкрийте список рядка, що розкривається. Перехресна таблицявиберіть опцію Заголовки рядків.Кожне поле, що є заголовком рядка, повинно мати в рядку Групова операція установку Угруповання.

5. Для полів, які будуть шпальтами перехресної таблиці, залежно від запиту, можливі такі дії:

у рядку Перехресна таблицявибрати опцію Заголовки стовпціві в рядку Групова операціявстановлення Угруповання,або у рядку Групова операція – Умови,у рядках Умова відборуі АБОзаписати логічний вираз.

6. Задати поле, на основі якого створюватиметься підсумкове значення. У рядку Перехресна таблицявибрати значення.У рядку Групова операціявибрати підсумкову функцію або Вираз.В останньому випадку в осередку Полезаписати вираз.

7. Запустіть запит на виконання.

ПРИКЛАД:

Вибрати сумарні кількості замовлених продуктів кожного виду, що випускаються кожним виробником. Подати результат як таблиці, де рядками служать назви видів продукції, стовпцями – назви виробників.

Мал. 21. Конструктор перехресного запиту

Мал. 22. Результат перехресного запиту

8.Запит на створення таблиці

Щоб створити таблицю з результатів запиту, який використовується для відбору записів у пов'язаних таблицях, виконайте такі дії:

Зробіть копію запиту на вибір записів з таблиць (або створіть новий).

Відкрийте створений запит у режимі Конструктори,виділивши його ім'я у списку запитів у вікні БД та натиснувши кнопку Конструктор.

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

Натисніть кнопку ОК. Запит на вибірку перетворюється на запит створення таблиці.

Запустіть запит подвійним клацанням миші на ім'я запиту у вікні БД. Перед виконанням запиту з'являється повідомлення, яке попереджає, що до нової таблиці буде внесено зміни (незважаючи на те, що вона ще не створена).

Натисніть кнопку Так. З'явиться друге попереджувальне повідомлення про кількість записів, які будуть розміщені в новій таблиці в результаті виконання запиту. Натисніть кнопку Так.

Розкрийте вкладку Таблиці у вікні БД; у списку має з'явитися нова таблиця.

9.Запит на оновлення записів

Створіть новий запит і увімкніть до нього створену копію таблиці.

Виберіть у меню команду Запит/Оновлення. У бланку запиту рядка Сортуванняі Виведення на екранбудуть замінені рядком Оновлення.

У рядку Оновленнязадайте вираз, який є новим значенням для поточного поля.

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

Виконайте запит. Перевірте правильність оновлення записів.

ПРИКЛАД:

Оновити вартість продуктів, кількість якого перевищує 500 одиниць, встановивши її на рівні 90% від первісної вартості.

Мал. 23. Конструктор запиту оновлення

10.Запит на видалення записів

Створіть копію таблиці, яка передбачає оновлення записів.

Створіть новий запит і увімкніть відповідну таблицю.

У бланк запиту перетягніть поля, за значеннями яких відбиратимуться поля. У рядку Умови відборувкажіть критерії відбору.

Запустіть запит на вибірку, щоб відобразити записи, що підлягають видаленню.

Відкрийте вікно бази даних. Виберіть вкладку Таблиці.

Активізуйте створений запит на вибірку та перейдіть в режим Конструктора запиту.

Виберіть у меню команду Запит/Видалення. У бланку запиту рядка Сортуванняі Виведення на екранбудуть замінені рядком Вилучення.

Клацніть на кнопці Запускпанелі інструментів. З'явиться вікно повідомлень, яке вимагатиме підтвердження видалення записів.

Видалення записів у таблиці “ один”, котрим у таблиці “ багато”існують пов'язані записи, що порушує правило цілісності даних. Тому опція цілісності даних, встановлена ​​зв'язку між таблицями, може перешкоджати видалення записів. Потрібно застосувати каскадне видалення.

ПРИКЛАД:

Мал. 24. Конструктор запиту видалення

11. Запит на додавання записів

Запит на додавання записів часто використовується для скопіювання даних з однієї таблиці в іншу.

Виберіть вкладку Запитивікна БД, натисніть кнопку Створити.Відкриється діалогове вікно Новий запит. Виберіть опцію Конструктор.

У вікні Додавання таблиціВиберіть таблицю, з якої будуть братися дані.

Перенесіть у рядок Полебланка запиту поля, значення яких включатимуться до іншої таблиці.

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

У бланку запиту після рядка Сортуваннябуде включено рядок Додавання,в якій потрібно вказати поля таблиці, куди додаються дані. Якщо імена полів збігаються Aзcessавтоматично вставить у рядок Додаванняімена полів.

Запустіть запит на виконання. Подивіться, як змінилася таблиця, в яку внесено нові рядки.

Якщо у запиті на Додаванняє поля типу Лічильник, то працювати з такими полями можна двома способами:

не включати поля типу Лічильнику поля, які мають бути додані; тоді таблиці, куди додаються дані, продовжиться нумерація послідовності даних, тобто. якщо є таблиця службовців із номерами від 1 до 100 та додати до неї нові записи, то їх нумерація розпочнеться зі 101 номера;

включати поля типу Лічильникдо списку запиту в якості полів, що додаються, то значення, які вони мали в початковій таблиці будуть збережені в полях доданих записів; якщо такі значення вже існують у полях таблиці, до якої додаються записи, це може спричинити помилку. Повідомлення про помилку з'явиться на екрані до виконання запиту. За наявності помилки вибрати кнопку Скасувати та виправити помилку.

2-й спосіб: клацнути правою кнопкою миші за значеннями потрібного поля в області ΣЗначення [Σ Values] і вибрати:

Додатково, щоб вибрати іншу функцію з запропонованого списку, налаштувати формат поля та змінити ім'я поля.

Щоб користуватися ефектами. Сьогодні ми вас навчимо. Позначте комірки, які ви хочете намалювати. Урок 3: Таблиці та зведені діаграми. Припустимо, ми маємо набір для аналізу. Щоб використати зведену таблицю, виберіть будь-яку комірку на аркуші та в меню «Вставка» виберіть «Зведена таблиця».

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

 Додаткові обчислення, Далі вибрати потрібний варіант розрахунку.

Повторне використання вихідного поля у звіті зведеної таблиці

По тому самому вихідному полю можна робити кілька обчислень. Для цього необхідно вихідного поля зі списку полів зведеної таблиці додати область значень Σ Значення [Σ

Values] кілька разів, а потім налаштувати параметри поля. Це дозволить одночасно бачити як саме обчислення, і обчислення, виконане з його основі.

Додавання обчислюваних полів у зведену таблицю

До звіту зведеної таблиці не можна додати нові рядки або стовпці для вставки формул або додаткових розрахунків, але можна додати поле, за допомогою яких можна вводити формули для розрахунку нових даних на підставі існуючих.

Бази даних Робоча карта 1 Бази даних Робоча карта 1 Створіть папку «Бази даних», щоб зберегти всі дані курсу. Створює запит, який відображає назви та описи. З'явиться діалогове вікно керування базою даних. Використовуючи це вікно, користувач керує базою даних та всіма її елементами.

Що таке електронна таблиця? Електронна таблиця – це програма для виконання математичних обчислень. З його допомогою ми також можемо розбірливо провести розрахунки у формі. Щоб користуватися перевагами конкретних действий. Найважче - підготовка даних. Завдання 1 Математична група 2 Дотримуйтесь цих кроків у свою чергу. Запустіть таблицю.

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

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

Західнопомеранський технологічний університет у Щецині Факультет морських технологій та транспорту Кафедра структур, механіки та технології Корабель в оперативній дослідницькій лабораторії. Зведені таблиці використовуються швидкого аналізу даних. Щоб використовувати зведені таблиці, ми повинні мати правильну підготовку даних із заголовками.

Щоб уникнути таких ситуацій, ви повинні відформатувати дані у вигляді таблиці. Щоб форматувати дані у вигляді таблиці. Це вибере усі заголовки. . Наступним вибором є розташування зведеної таблиці, а для збереження зручності читання даних ви повинні залишити опцію «Новий лист», яка буде вставляти новий листу таблицю.

Microsoft Excel 2010. Рівень 2. Розширені можливості

Таким чином, при додаванні власних полів, що обчислюються, в зведену таблицю очевидні переваги:

 Виключення потенційних помилок у формулах та посиланнях на комірки.

 Можливість додавання та видалення даних із зведеної таблиці без зміни вихідних обчислень.

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

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

 Можливість автоматичного перерахунку даних при зміні або оновленні зведеної таблиці.

 Забезпечує гнучкість зміни обчислень при зміні визначень елементів.

 Можливість ефективного керування обчисленнями.

Створення обчислюваного поля:

1. Виділити комірку зведеної таблиці;

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

Щоб швидко змінити звіт та подивитися, як він продав Добровальського в інші країни, просто змініть порядок умов на полях, захопивши Продавця та перетягнувши його через державу. Крім Ірландії та Німеччини, ясно, що результати її продажів не найкращі. Коли ви натиснете на продаж в Ірландії, ви побачите новий аркуш зі столом, який містить усі транзакції Добровальського в Ірландії.

2. На вкладці Параметри, у групі Обчислення, розкрити список Поля,

елементи та набори та вибрати Обчислюване поле.

3. У вікні Вставка поля, що обчислюється:

 У полі Ім'я ввести ім'я нового поля.

 У полі Формула скласти формулу, починаючи зі знака= та вибираючи

доступні поля у списку Поля (для вставки можна використовувати подвійне клацання по полю або виділити поле, потім натиснути кнопку Додати поле).

Якщо ми хочемо роздрукувати наші дані, ми візьмемо поле Продавця з області Лінії і перемістимо його в область Колонка. Це змусить таблицю поширитись по всьому аркушу. У цьому вікні ми також можемо вказати числовий формат, що відображається у всіх осередках поля. Просто використовуйте кнопку «Формат номера» та виберіть відповідну валюту, наприклад.

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

 Натиснути Додати.

Налаштування зовнішнього вигляду поля, що обчислюється, як і будь-якого іншого поля, відбувається у вікні

Параметри полів значень.

Редагування поля, що обчислюється

1. На вкладці Параметри, у групі Обчислення, розкрити список Поля,

елементи та набори, вибрати Обчислюване поле.

Усі ми вміємо користуватися зведеними таблицями. У них є купа переваг, але є і купа недоліків. Багато хто вважає, що основна проблема зі зведеними таблицями - це те, що в ній немає можливості робити обчислення - це помилка. Навіщо це може бути потрібне. Припустимо, ви складаєте бюджет за допомогою зведених таблиць. Дані у вихідній таблиці представлені в поквартальній розбивці, для цілей бюджету буде необхідно скласти ці дані на річну суму.

Ми відзначаємо місяці, і ми приймаємо їх. Ми отримаємо звіт, відсортований за місяцями. Ви також можете поділити дані на кілька категорій. Повертаючись до угруповання конфігураторів та вибравши «Квартал і місяць», ми отримаємо зовнішній вигляд звіту. Угруповання також може використовуватись для чисел.

Коли ви відкриваєте контекстне меню, поле "Рядки" та параметр "Група" показують інше вікно, ніж якщо ви групували дати. Числа згруповані за діапазонами, які ми визначаємо на початку, кінці та обсягу інтервалу. У цьому випадку ми встановлюємо його на 500, тож у першій групі будуть усі транзакції вартістю від 0 до 500 злотих.

Насправді така можливість є і реалізується одним із трьох способів.

Освіжити свої знання про основи зведених таблиць можна.

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

Другий спосіб.Побудувати розрахунки поруч із зведеною таблицею – знову ж таки, спосіб не з найкращих. Якщо зведена таблиця змінюватиметься (а користуватися зведеними таблицями варто саме тоді, коли ми очікуємо зміни у таблиці).

Третій спосіб.Вбудувати обчислювані поля в саму зведену таблицю. А ось це хороший спосіб. Робиться це так. Коли зведена таблиця вже побудована, ставимо курсор на зведену таблицю (при цьому на стрічці з'являються дві закладки, що відносяться до даної зведеної таблиці) і натискаємо на закладку, що з'явилася Options, потім Fields,Items , &Setsз блоку Calculations , у меню вибираємо Calculatedfield. У 2007 офісі ця кнопка була на тій же вкладці Options, тільки кнопка була окрема.

У вікні, в полі Nameвведіть бажану назву поля. У полі Formulaвведіть необхідну вам формулу, вставляючи поля зі списку внизу та натискаючи кнопку Insertfield.

Вийде таблиця з новим розрахунковим полем. Таблиця готова!

Екселевський файл можна завантажити звідси.

Цікавий факт.При застосуванні третього способу програма робить обчислення для кожного рядка і після цього застосовує необхідний параметр: підсумовування, середнє значення і т.д.

Останні новини

• Просунутий Excel: Чому я перестав користуватися функцією V…

  Я вже писав про те, що функція VLOOKUP (ВПР), напевно, найкорисніша функція після простих арифметичних операцій.

• Як Excel перераховує книгу і чому треба уникати волатіл...

  Якщо ви часто працюєте з великими файлами, які виробляють велику кількість обчислень, що залежать один від одного, про…

• Як швидко побудувати графік Waterfall (водоспад)

  В Excel немає стандартних діаграм типу Waterfall. Тому для створення діаграм цього типу зазвичай використовують гіс...

• Вікно контрольного значення для відстеження результатів

  Часто буває, що ми працюємо з великими таблицями, які розраховують зрештою 1-2 показники, але залежать від …

• Bullet chart для порівняння запланованих показників і фактично…

  Часто виникає необхідність порівняти основні фінансово-економічні показники з запланованими, наприклад, для цілей…

• Тацуо Хоріучі - 73-річний художник, який малює в Excel

  "Я ніколи не користувався Excel на роботі, але я бачив, як інші люди роблять у ньому досить гарні діаграми та графік…"

• Як звести число в ступінь і отримати корінь

  Чи знаєте ви, що для того, щоб звести в ступінь числа в Excel є спеціальний символ^ (На шістці в англійський ...

Обчислювані поля призначені для відображення у звітах значень виразів на основі вихідних даних. Обчислювані поля у звітах можна використовуватиме отримання підсумкових значень чи проведення спеціальних обчислень, наприклад, щоб відобразити загальну ціну замовлення, обчисливши добуток кількості товару ціну одного виробу. Розглянемо процедуру створення обчислюваних полів на прикладі звіту "Товари на складі", створеного раніше.

Щоб додати до звіту поле "Сума":

1. Відкрийте звіт "Товари на складі" у режимі Конструктора.
2. На панелі елементів натисніть кнопку Напис(Label) і помістіть новий напис праворуч від напису "На складі" (Units in Stock) у розділі верхнього колонтитула. Введіть слово "Сума" (Sum) як текст напису.
3. Щоб створити поле "Сума" (Sum), натисніть кнопку Поле(Text Box) на панелі елементів та додайте текстове поле праворуч від текстового поля "На Складі" (UnitsInStock) у розділі даних. Видаліть позначку нового текстового поля.
4. Клацніть всередині текстового поля і введіть як його значення вираз:

   = [На Складі]* [Ціна] (= * ).

   Відкрийте вікно властивостейполя, двічі клацнувши по ньому, і на вкладці Макет(Format) у полі зі списком властивості Формат поля(Format) виберіть значення Грошовий(Currency).

Зауваження

Ввести вираз у текстове поле можна кількома способами: виділіть його та натисніть комбінацію клавіш +, щоб увійти в режим редагування вмісту поля, або зробіть подвійне клацання по полю для відображення діалогового вікна Властивості(Properties), а потім введіть вираз як значення властивості Дані(Source).

Мал. 6.27.

1. Повторіть кроки 3 і 4 для того, щоб створити текстове поле в розділі приміток групи "Категорія" (Category), але як вираз для обчислення проміжного значення слід ввести: =Sum([НаСкладе] * [Ціна]) (=Sum( *) )). Натисніть кнопку Напівжирний(Bold) на панелі форматування, щоб встановити жирний шрифт для тексту в новому полі. Відкрийте діалогове вікно Властивості(Properties) та розкрийте в ньому вкладку Інші(Other), у полі Ім'я(Name) Введіть ім'я текстового поля "Сума" (Sum). Потім розкрийте вкладку Макет(Format) та у полі зі списком властивості Формат поля(Format) виберіть значення Грошовий(Currency).
2. Оскільки ширина області даних нашого звіту збільшилася, зменшіть ширину полів сторінки, щоб усі поля розмістилися на одній сторінці. Для цього виберіть команду Файл, параметри сторінки(File, Page Setup) і задайте замість ширини лівого та правого полів, заданих за замовчуванням, значення 10 мм (див. Розд. "Перегляд та друк звітів" у цьому розділі).
3. Натисніть кнопку Вид(View) на панелі інструментів Конструктор звітів(Report Design), щоб оцінити результати своєї роботи. Звіт виглядатиме так, як показано на рис. 6.27. До попередніх даних додався крайній правий стовпець зі значеннями вартості кожного товару в наявній кількості на складі та проміжних підсумкових сум за вартістю товарів для кожної категорії.