Списком (базою даних) називають набір даних, що містить інформацію про певні об'єкти. У Microsoft Excel такою базою даних є таблиця, рядки в якій, починаючи з другого, називають записами, стовпчики — полями. Перший рядок списку містить назви полів (стовпчиків).
Під час створення списку — бази даних у середовищі Microsoft Excel слід дотримуватися певних правил:
• на одному робочому аркуші не можна розміщувати більше одного списку;
• список має бути відокремлений від інших даних робочого аркуша щонайменше одним порожнім стовпчиком і одним порожнім рядком;
• список не може містити порожніх рядків (стовпчиків);
• заголовки полів мають бути унікальними, тобто не може бути повторів назв заголовків полів;
• бажано, щоб формати заголовків полів відрізнялися від форматів записів;
• не може бути порожнього рядка або навіть порожньої комірки між заголовками полів і записами;
• в усіх рядках списку в однакових стовпчиках мають розташовуватися однотипні дані.
Список (база даних) Microsoft Excel подано такими параметрами:
• діапазоном бази даних або іменем діапазону та
• адресами назв полів або їх порядковими номерами у базі даних.
Наприклад, діапазоном бази даних таблиці замовлень є комірки A11:L24 (або ім'я діапазону — Базаі, яке було надане вказаному діапазону), а адресами назв полів є All — поле № п/п (порядковий номер 1), ВИ — поле Прізвище клієнта (порядковий номер 2) і так далі до адреси L11 — поле Сплачено (порядковий номер 12).
37. Аналіз даних за допомогою проміжних підсумків (Итоги) та зведених таблиць у табличному процесорі MS EXCEL.
Проміжні підсумки використовуються для розрах. Значень, які повторюються. Такими підсумками можуть бути ф-ції: Сума, К-сть зн-нь, Мін, Макс. Перед викон. Такої ф-ції БД має бути відсортована по значенням поля, які повторюються. Знайти б-я ф-цію для певного поля. Курсор у БД; Ком. Данные → Сортировка. Ком. Данные → Итоги; В полі “При каждом изменении в” вказують те поле, в якому відбувається сортування, яке має повторююче зн-ня. У полі “Операція” актив. відповідну ф-цію. У полі “Добавить итоги по” перемикачі (зліва) тих полів, зн-ня яких потрібно розрахувати. Перемикач “Заменить текущие итоги” активний, коли треба розрахувати лише 1 ф-цію. Коли декілька підсумків - він неактивний. Після ОК з’являється БД, в якій після кожної групи однакових значень виводиться підсумок. У лівій частині екрану з’являється структура вкладеності рівнів зн-нь. Кн. 1, 2, 3 … дозволяють згортати (розгортати) перегляд зн-нь. Для вилучення Ф-ції (не значень) – курсор у БД → Данные → Итоги → кн. Убрать все.
38. Використання автофільтру для аналізу списку в табличному процесорі MS EXCEL.
Для допомогою фільтрів можна вибрати записи, що містять потрібні для пошуку дані. Фільтри бувають двох типів: Авто-фільтр і Розширений фільтр.
Автофільтр може працювати з простими критеріями (одна умова), складними критеріями (максимум дві умови за одним полем) і складеними критеріями, які можуть містити стільки умов, скільки полів у списку, але при цьому використовують максимум дві умови за одним полем. Критерії заносяться під час роботи Автофільтра, а результати запиту для подальшого збереження користувач може самостійно скопіювати в інше місце поточного робочого аркуша або на новий аркуш.
Данные → Фильтр → Автофильтр.
39. Використання розширеного фільтру для аналізу списку в табличному процесорі MS EXCEL. Навести приклади з використанням простого та складеного критеріїв.
На відміну від Автофільтра, де критерії заносяться під час роботи фільтра, Розширений фільтр може працювати тільки тоді, коли критерії для пошуку даних попередньо створені користувачем і занесені у визначений діапазон комірок таблиці. Цей діапазон бажано має міститися над списком і має бути відокремленим від списку щонайменше одним порожнім рядком.
Простий критерій складається з двох комірок, розміщених у таблиці вертикально: верхня комірка містить точну копію заголовка поля списку, в якому перевіряється умова пошуку даних, а нижня комірка містить безпосередньо саму умову пошуку.
1. Виорати зі списку всі записи про клієнта Васильєва.
За правилами створення критеріїв діапазон для них оуде розміщуватися у робочому аркуші по рядках з першого по дев'ятий.
Даний запит включає одну умову (прізвище клієнта — Васильєв), тому створюється простий критерій для пошуку в полі Прізвище клієнта прізвища Васильєв.
У комірку В1 копіюється заголовок поля Прізвище клієнта, а в комірку В2 — умова для пошуку — Васильєв:
Таким чином, діапазон створеного простого критерію розміщується у комірках В1 :В2.
Складений критерій — це сукупність декількох складних критеріїв — містить по декілька умов на декілька полів.
4. Вибрати зі списку всі записи про клієнта Васильєва, що замовляв телефони, клієнта Петрова, що замовляв принтери, та всі замовлення клієнта Іванова.
У даному випадку створюється складений критерій, що містить по три умови на два поля. Під час виконання запиту в полі Прізвище клієнта має міститися або прізвище Васильєв і одночасно в полі Назва виробу — Телефон, або прізвище Петров і одночасно Принтер, або прізвище Іванов і будь-яка назва виробу.
У комірку СЗ копіюється назва виробу Принтер, а в комірку С4 заноситься знак * (який означає будь-яке текстове значення):
Діапазон створеного критерію — комірки В1 :С4.
40. Використання розширеного фільтру для аналізу списку в табличному процесорі MS EXCEL. Навести приклади з використанням складного та обчислювального критеріїв.
На відміну від Автофільтра, де критерії заносяться під час роботи фільтра, Розширений фільтр може працювати тільки тоді, коли критерії для пошуку даних попередньо створені користувачем і занесені у визначений діапазон комірок таблиці. Цей діапазон бажано має міститися над списком і має бути відокремленим від списку щонайменше одним порожнім рядком.
Складний критерій містить у собі декілька простих критеріїв, сполучених або по вертикалі — декілька умов на одне поле, або по горизонталі — по одній умові на декілька полів.ты в диапазон” зазначають адресу вільної комірки. ОК. З’являється нова БД.
2. Вибрати зі списку всі записи, що стосуються або клієнтів Васильєва, або Петрова, або Іванова.
Даний запит включає три умови, що накладаються на поле Прізвище клієнта (прізвище — або Васильєв, або Петров, або Іванов), тому створюється складний критерій із трьох простих, які поєднані зв'язкою ИЛИ.
У комірку ВЗ копіюється умова для пошуку — Петров, а в комірку В4 — Іванов:
Діапазоном створеного складного критерію будуть комірки В1:В4.
Обчислювальний критерій може бути різновидом простого або складного критерію, де як умова виступає функція або формула. Записи за таким критерієм вибираються зі списку за значенням, отриманим у результаті обчислення цієї формули або функції.
8. Вибрати зі списку всі записи про максимальну і мінімальну суму замовлення.
У комірку Е6 копіюється заголовок поля Сума замовлення, у комірку Е7 заноситься функція
= МАКС(абсолютний діапазон числових значень цього поля), а у комірку Е8 — функція
=МИН(абсолютний діапазон числових значень цього поля).
Замість тексту функцій у критерію будуть показані обчислені за цими функціями значення.
41. Технологія використання функцій ВПР та ГПР у табличному процесорі MS EXCEL. Навести приклади.
=ВПР(пошукове значення; масив комірок; номер поля;[діапа-зон перегляду])
Функція ВПР провадить пошук вказаного значення у першому стовпчику масиву комірок і, якщо таке значення є у масиві, видає з поля значення, відповідне знайденому в тому ж рядку. Діапазон перегляду — необов'язковий логічний параметр, який може набувати значення ИСТИНА або ЛОЖЬ. Якщо він не вказується (ИСТИНА), то провадиться точний пошук, якщо ж вказується (ЛОЖЬ), то провадиться приблизний пошук. Таблиця (масив комірок) обов'язково має бути відсортована за зростанням по першому
Функція ГПР аналогічна ВПР, але замість полів(стовпчиків), вона аналізує рядки.
Функція ГПР має такий вигляд
=ГПР(пошукове значення; масив комірок; номер рядка;[діапа-зон перегляду])
Приклад:
Треба за введеним кодом виробу визначити:
• Назву виробу (вибирається з поля 2 — Назва виробу), Використовується функція ВПР з такими параметрами: Заданий код виробу — посилання на адресу комірки, де міститься значення коду або сам код,
Масив комірок — A3:F7,
Номер поля — 2 (стовпчик Назва виробу).
• замовлену кількість (вибирається з поля 3 — Замовлена к-сть),
Використовується функція ВПР з такими параметрами: Заданий код виробу — посилання на адресу комірки, де міститься значення коду, або сам код, Масив комірок — A3:F7, Номер поля — 3 (стовпчик Замовлена к-сть).
42. Технологія використання функцій баз даних для аналізу списку в табличному процесорі MS EXCEL. Навести приклади використання функцій БСЧЕТ ТА БИЗВЛЕЧЬ.
За допомогою функцій баз даних можна виконувати обчислення у списках — базах даних Microsoft Excel.
Загальний вигляд функції баз даних:
=ім'я функції(діапазон бази даних або його ім'я; поле;критерій)
Діапазон бази даних — це область робочого аркуша, в якій розташований список даних, поле — це або адреса заголовка стовпчика в списку даних, або його порядковий номер, критерій — це одна або декілька умов для обчислення (як у розширеному фільтрі).
=БСЧЕТ(діапазон бази даних або його ім'я; поле;критерій) — визначає кількість значень у вказаному числовому полі, якщо задовольняється критерій,
Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)
5. Скільки разів сплачений податок перевищував середнє зна-чення. Використовується функція БСЧЕТ, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — II1, або 9 (назва числового стовчика Кількість міститься у комірці 111, порядковий номер стовпчика= 9), Критерій — міститься у комірках F6:F7.
=БИЗВЛЕЧЬ(діапазон бази даних або його ім'я; поле;крите-рій) — визначає потрібне значення в указаному полі, якщо задовольняється критерій.
Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)
7. Прізвище клієнта з максимальною сумою замовлення. Використовується функція БИЗВЛЕЧЬ, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — ВИ, або 2 (назва стовпчика Прізвище клієнта міститься у комірці В11, порядковий номер стовпчика= 2), Критерій — міститься у комірках Е6:Е7.
43. Технологія використання функцій баз даних для аналізу списку в табличному процесорі MS EXCEL. Навести приклади використання функцій БДСУММ та ДСРЗНАЧ.
За допомогою функцій баз даних можна виконувати обчислення у списках — базах даних Microsoft Excel.
Загальний вигляд функції баз даних:
=ім'я функції(діапазон бази даних або його ім'я; поле;кри-терій)
Діапазон бази даних — це область робочого аркуша, в якій розташований список даних, поле — це або адреса заголовка стовпчика в списку даних, або його порядковий номер, критерій — це одна або декілька умов для обчислення (як у розширеному фільтрі).
=БДСУММ(діапазон бази даних або його ім'я; поле;крите-рій) — визначає суму значень у вказаному полі, якщо задовольняється критерій,
Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)
Наприклад, за даними таблиці замовлень визначити: 1. Загальну суму податку за вироби, замовлені клієнтом Васильєвим.
Використовується функція БДСУММ, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — II1, або 9 (назва стовпчика Податок міститься у комірці II1, порядковий номер стовчика= 9), Критерій — міститься у комірках В1 :В2.
=ДСРЗНАЧ(діапазон бази даних або його ім'я; поле;крите-рій) — визначає середнє значення у вказаному полі, якщо задовольняється критерій,
Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)
2. Середню кількість телефонів, замовлених клієнтом Васильєвим.
Використовується функція ДСРЗНАЧ, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — F11, або 6 (назва стовчика Кількість міститься у комірці F11, порядковий номер стовпчика= 6), Критерій — міститься у комірках В1 :С2.
44. Технологія використання функцій баз даних для аналізу списку в табличному процесорі MS EXCEL. Навести приклади використання функцій ДМИН та БСЧЕТА.
За допомогою функцій баз даних можна виконувати обчислення у списках — базах даних Microsoft Excel.
Загальний вигляд функції баз даних:
=ім'я функції(діапазон бази даних або його ім'я; поле;кри-терій)
Діапазон бази даних — це область робочого аркуша, в якій розташований список даних, поле — це або адреса заголовка стовпчика в списку даних, або його порядковий номер, критерій — це одна або декілька умов для обчислення (як у розширеному фільтрі).
=ДМИН(діапазон бази даних або його ім'я; поле;критерій) — визначає мінімальне значення в указаному полі, якщо задовольняється критерій,
Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)
4. Мінімальну кількість замовлених телефонів. Використовується функція ДМИН, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — F11, або 6 (назва стовчика Кількість міститься у ко-мірці F11, порядковий номер стовпчика= 6), Критерій — міститься у комірках Н6:Н7.
=БСЧЕТА(діапазон бази даних або його ім'я; поле;критерій) — визначає кількість значень у вказаному текстовому полі, якщо задовольняється критерій,
Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)
6. Скільки разів клієнт Васильєв замовляв телефони за готівку. Використовується функція БСЧЕТА, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — В11, або 2 (назва текстового стовпчика Прізвище клієнта міститься у комірці ВИ, порядковий номер стовпчика= 2), Критерій — міститься у комірках В1 :D2.
45. Технологія використання функцій баз даних для аналізу списку в табличному процесорі MS EXCEL. Навести приклади використання функцій Б ИЗВЛЕЧЬ та Д МАКС.
За допомогою функцій баз даних можна виконувати обчислення у списках — базах даних Microsoft Excel.
Загальний вигляд функції баз даних:
=ім'я функції(діапазон бази даних або його ім'я; поле;кри-терій)
Діапазон бази даних — це область робочого аркуша, в якій розташований список даних, поле — це або адреса заголовка стовпчика в списку даних, або його порядковий номер, критерій — це одна або декілька умов для обчислення (як у розширеному фільтрі).
=БИЗВЛЕЧЬ(діапазон бази даних або його ім'я; поле;крите-рій) — визначає потрібне значення в указаному полі, якщо задовольняється критерій.
Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)
7. Прізвище клієнта з максимальною сумою замовлення. Використовується функція БИЗВЛЕЧЬ, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — В11, або 2 (назва стовпчика Прізвище клієнта міститься у комірці В11, порядковий номер стовпчика= 2), Критерій — міститься у комірках Е6:Е7.
=ДМАКС(діапазон бази даних або його ім'я; поле;критерій) — визначає максимальне значення у вказаному полі, якщо задовольняється критерій,
Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)
3. Максимальну загальну вартість замовлення за готівку у 1997 році.
Використовується функція ДМАКС, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — НІ 1, або 8 (назва стовпчика Загальна вартість міститься у комірці НІ 1, порядковий номер стовчика= 8), Критерій — міститься у комірках D1:F2.
