Робота з електронними таблицями як базами даних. План лекції: 1. Основні поняття та обмеження. Типові операції обробки баз даних. 2. Упорядкування баз даних за простим та складеним ключем. 3. Використання фільтрів для аналізу даних бази. 4. Використання форм для введення, перегляду, редагування і пошуку даних у базі. 5. Використання проміжних підсумків для аналізу даних у базі. 6. Функції обробки баз даних.

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

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

Упорядкування рядків та стовпчиків (за простим ключем) Щоб упорядкувати рядки у відповідності зі значеннями окремого стовпчика: 1.Виділіть в списку довільну комірку або весь діапазон. 2.За допомогою команди Данные-Сортировка... відкрийте вікно діалогу "Сортировка диапазона". Якщо список містить рядок заголовку, то у вікні діалогу автоматично вмикається перемикач Идентифицировать поля по подписям (первая строка диапазона). 3.В полі Сортировать по з випадаючого списку виберіть заголовок стовпчика, за яким упорядковуватимуться дані, і ввімкніть один з перемикачів по возрастанию або по убыванию. 4.Натисніть кнопку ОК.

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

Для упорядкування стовпчиків у відповідності зі значеннями окремого рядка: 1.Виділіть в списку довільну комірку або весь діапазон і виконайте команду Данные-Сортировка... 2.У вікні діалогу "Сортировка диапазона" натисніть кнопку Параметры, встановіть перемикач Столбцы диапазона і натисніть кнопку ОК. 3.В полі Сортировать по з випадаючого списку виберіть рядок, за яким виконуватиметься упорядкування, і натисніть кнопку ОК. 4.За допомогою перемикачів по возрастанию або по убыванию визначте порядок сортування і натисніть кнопку ОК. 5.Упорядкування за складеним ключем (за порядком, який визначається користувачем)

Excel не обмежує користувача використанням тільки стандартного порядку упорядкування за зростанням чи спаданням. Порядок упорядкування може визначатись окремим списком автозаповнення, який виступає в ролі складеного ключа. Список автозаповнення можна створити за допомогою команди Сервис Параметры - вкл. Списки. Щоб скористатись створеним списком: 1.Виконайте команду Данные-Сортировка... 2.У вікні діалогу "Сортировка диапазона" натисніть кнопку Параметры. 3.Розкрийте список Порядок сортировки по первому ключу і виберіть потрібний список. 4.Двічі скористайтесь кнопкою ОК.

Как с помощью фильтров выделить из списка нужные значения

Використання фільтрів для аналізу даних бази. Фільтри дозволяють приховати всі рядки списку за виключенням тих, які задовольняють вказаним умовам відбору. В Excel існують два типи фільтрів: n Автофільтр - при використанні простих умов відбору. n Розширений фільтр - для використання більш складних критеріїв.

Автофільтр 1. Виділіть довільну комірку або весь рядок заголовків списку. 2. Виконайте команду Данные-Фильтр- Автофильтр. Після цього біля кожного заголовка стовпчика з'являться кнопки зі стрілками. 3. Клацніть по кнопці зі стрілкою для стовпчика, на який накладаються обмеження. В результаті розкриється список значень, які можна використовувати в умовах відбору. 4. Задайте умови відбору для даного стовпчика. 5. Повторіть пп.3-4

Розширений фільтр Розширений фільтр по відношенню до автофільтру має ряд додаткових можливостей. Він дозволяє: n задавати умови, які об'єднуються оператором ИЛИ для декількох стовпчиків; n задавати три і більше умов для одного стовпчика; n задавати обчислювальні критерії.

Використання розширеного фільтру вимагає визначення умов відбору в окремому діапазоні робочого листа. Діапазон умов містить по меншій мірі два рядки - рядок заголовків (верхній рядок) та рядки з умовами відбору (другий і наступні рядки). За виключенням обчислювальних умов, заголовки в діапазоні умов повинні точно збігатися з заголовками списку. В діапазон умов достатньо включити заголовки тільки тих стовпчиків, які використовуються в умовах відбору. Умови, зазначені в одному рядку діапазону умов, об'єднуються між собою за допомогою логічного оператора И, а в різних - за допомогою логічного оператора ИЛИ.

Умови відбору, як правило, задаються за допомогою логічних операторів >, =, <=, =; тому їх називають порівняльними. При цьому дозволяється використовувати символи шаблонів * (зірочка) і ? (знак питання).

Крім цього, при визначенні умов відбору для текстових значень можна використовувати такі правила: 1.Якщо в умові відбору вказати єдину букву, то будуть відібрані всі значення, які починаються з цієї букви. 2.Символи (більше) дозволяють знайти всі значення, що знаходяться за алфавітом до або після введеного текстового значення. 3.Формула ="текст" дозволяє знайти значення, які точно співпадають з рядком символів текст.

Вибір даних за допомогою розширеного фільтра з використанням оператора ИЛИ На комірки одного стовпчика накладуються три або більше умов відбору Щоб задати для окремого стовпчика три або більше умов відбору, введіть умову в комірки, які розташовані в суміжних рядках. NRUN Бесарабськи й Сінний

Вибір даних за допомогою розширеного фільтра з використанням оператора И Умови відбору накладуються на комірки двох або більше стовпців Щоб накласти умови відбору на декілька стовпчиків одночасно необхідно ввести умови в комірки, які розташовані в одному рядку діапазона умов. KODT >=102<=302

Вибір даних за допомогою розширеного фільтра з використанням операторів И ЛИ та И Для вибору рядків, які задовольняють одній із декількох умов, накладених на різні стовпці, введіть умови в комірки, які розташовані в різних рядках діапазона умов. Наприклад, для слідуючого діапазону умови будуть відібрані рядки, які містять або «Продукты» в стовпчику «Товар», або «Белов» в стовпчику «Продавец», або мають суму реалізації більше 1000.

Щоб накласти складну умову відбору, уведіть його складові частини в окремі рядки діапазону умов. Наприклад, для наступного діапазону умов будуть відібрані рядки, що містять «Белов» у стовпці «Продавець» і реалізації, що мають суму, більше 3000 або рядка, що містять «Батурин» у стовпці «Продавець» і реалізації, що мають суму, більше 1500.

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

Якщо діапазон умов підготовлений, можна переходити до використання розширеного фільтру. Для цього: Виконайте команду Данные-Фильтр-Расширенный фильтр. 1. У вікні діалогу "Расширенный фильтр" в полі Исходный диапазон задайте діапазон комірок списку, а в полі Диапазон условий - діапазон комірок з умовами відбору. 2. Залишіть ввімкненим перемикач Фильтровать на месте, якщо бажаєте замінити список відібраними даними. 3. Якщо відібрані дані треба скопіювати в іншу частину листа, ввімкніть перемикач Скопировать результат в другое место. Тоді стає доступним поле Поместить результат в диапазон, в якому достатньо вказати першу комірку, починаючи з якої будуть вставлятися відібрані дані. Щоб скопіювати тільки конкретні стовпчики списку, створіть копії їх заголовків і задайте їх в якості діапазону для розміщення результату. 4. Натисніть кнопку ОК

Функції обробки баз даних. Excel пропонує ряд функцій (їх чотирнадцять), які спеціально призначені для роботи зі списками. Кожна з них повертає інформацію про елементи списку, які задовольняють певним умовам. Вони мають спільну назву Д-функції або функції баз даних. До них відносяться функції ДСРЗНАЧ, БСЧЕТ, ДМАКС, ДМИН та інші. Кожна з цих функцій є аналогом "звичайної" статистичної функції. Різниця між функціями баз даних і їх аналогами полягає в тому, що Д-функції оперують тільки з елементами діапазону, які задовольняють визначеним умовам.

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

При роботі зі списками крім Д-функцій часто використовуються ще дві -СЧЕТЕСЛИ й СУММЕСЛИ. Вони простіші у використанні, тому що дозволяють задавати умови безпосередньо у формулі, але при цьому допускаються лише прості умови порівняння.

Функція СЧЕТЕСЛИ має такий синтаксис: = СЧЕТЕСЛИ (база_даних; критерій), де аргумент база_даних задає діапазон, в якому необхідно визначити кількість значень, а аргумент критерій - це текстове значення, що задає умову. Наприклад, СЧЕТЕСЛИ (А1:А20; "Б"), СЧЕТЕСЛИ(В1:В10; ">=30").