13435

УПРАВЛІННЯ ДАННИМИ В MICROSOFT EXCEL

Лабораторная работа

Информатика, кибернетика и программирование

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОЇ РОБОТИ № 5 УПРАВЛІННЯ ДАННИМИ В MICROSOFT EXCEL 1. Мета заняття Оволодіти навичками роботи з базами даних в Microsoft Excel. 2. Завдання роботи Навчитись: 2.1. Створювати бази даних. 2.2. Створювати форми даних і працювати в них із запи...

Украинкский

2013-05-11

130.5 KB

8 чел.

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОЇ РОБОТИ № 5

УПРАВЛІННЯ ДАННИМИ В MICROSOFT EXCEL

 

1. Мета заняття

Оволодіти навичками роботи з  базами даних в Microsoft Excel.

2. Завдання роботи

Навчитись:

2.1. Створювати бази даних.

2.2. Створювати форми даних і працювати в них із записами.

2.3. Сортувати записи в базах даних.

2.4. Вибирати записи по різноманітним критеріям.

3. Зміст роботи

3.1. Створити новий аркуш робочої книги „База даних” і розмістити на ньому довільні дані (не менше 20 записів), або дані вказані викладачем, у вигляді бази даних. В першому рядку таблиця повинна містити імена полів. З другого рядка повинні розміщуватись записи.

3.2. Викликати форму даних для вашої таблиці і відредагувати записи в ній згідно п. 6.

3.3. Вибрати записи по наступним умовам :

по єдиному в своєму роді критерію в текстовому полі;

більше одного зі значень в числовому полі;

по першій літері текстового поля;

по двом критеріям в числових полях.

3.4. Відсортувати базу даних по убуванню третього стовпця.

Однакові значення третього стовпця відсортувати по зростанню другого.

3.5. Включити „автофільтр” і відфільтрувати записи по наступнім параметрам:

однаковість в текстовому полі;

неоднаковість в числовому полі;

 И інтервал;

 ИЛИ інтервал.

3.6. Оформити звіт.

 

4. Вимоги до звіту

Звіт повинен включати:

назву і номер роботи, відомості про виконавця;

постановку задачі і відомості про послідовність її  виконання;

відповіді на контрольні питання п.7 вказівок;

таблиці з розрахунками.

До звіту додається дискета з виконаним завданням.

5. Загальні положення

5.1. Загальні поняття про бази даних

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

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

Microsoft Excel має певний набір команд, які дозволяють легко працювати з базами даних. Щоб скористатися можливостями Microsoft Excel для обробки даних, потрібно створити базу даних на робочому аркуші. Базу даних, як і будь-яку книгу Microsoft Excel, створюють командою „ФайлСтворити”.

5.2. Команда Форма

„Форма” – один з об’єктів Microsoft Excel, призначений для комфортної роботи користувачів при уведенні, відображенні та зміні даних при роботі з базою даних.

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

В цьому діалоговому вікні, назви стовпців таблиці стають іменами полів у формі даних. Microsoft Excel автоматично присвоює „гарячі клавіші” кожному імені поля. Для того, щоб переміститись в певне поле, необхідно одночасно натиснути клавішу <ALT> з літерою, яка підкреслена в імені поля. Поля відповідають кожному стовпцю у списку. Всі поля, які доступні для редагування, з’являються у вікні редагування.

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

В діалоговому вікні форми даних можна користуватись наступними кнопками:

 

Кнопка

Призначення

„Создать”

Дозволяє ввести новий запис в базу даних. Дані що вводяться, будуть додані як новий запис в кінець бази

„Удалить”

Видаляє виведений запис; Інші записи бази зсуваються. Видалені записи не можуть бути відновлені

„Восстановить”

Поновлює відредаговані поля у виведеному запису, видаляє зроблені зміни. Якщо потрібно відновити запис, то це необхідно зробити перед натисненням клавіші <ENTER> або перед переходом до іншого запису.

„Предыдущая”

Виводить попередній запис в базі

„Следующая”

Виводить наступний запис в базі

„Критерии”

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

„Закрыть”

Закриває форму даних

„Очистить”

Видаляє існуючий критерій з вікна діалогу. Доступна тільки тоді, коли натиснута кнопка „Критерії”.

„Форма”

Повертає до типу форми даних по умовчанню. Доступна тільки тоді, коли натиснута кнопка „Критерії”

 

Якщо натиснута кнопка „Критерії”, то кнопки „Попередня” і „Наступна” дозволяють переміщуватись тільки по відібраним записам.

 

5.3. Сортування записів

Реляційні бази даних (списки) сортують аналогічно даним таблиць-документів:

за зростанням або спаданням значень вибраних полів;

за алфавітом і за послідовністю, зворотною йому;

за одним, двома або трьома ключами;

за рядками або стовпцями;

за днями, місяцями та іншими ключами.

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

Щоб відсортувати базу даних цілком, потрібно лише виділити одну комірку зі списку і вибрати команду „Дані  Сортування”. Microsoft Excel автоматично вибере увесь список для сортування. Microsoft Excel визначає розташування міток стовпців, навіть якщо вони займають два рядки, і виключає їх з сортування. Можна використати мітки стовпців для того, щоб вказати стовпці, по яким потрібно відсортувати список. Microsoft Excel дозволяє також сортувати тільки виділені рядки або стовпці, або дані тільки в одному рядку або стовпці.

Після завдання даних для сортування і вибору команди на екрані з’явиться вікно діалогу, у якому потрібно задати наступні поля:

Сортувати - Дозволяє виділити потрібний стовпець для сортування і вибрати порядок сортування по зростанню або по убуванню. Потрібно натиснути кнопку По зростанню” для сортування від меншого до більшого, по алфавіту або в хронологічному порядку дат, або кнопку „По убуванню” для сортуванню від більшого до меншого, в зворотному алфавітному або в зворотному хронологічному порядку. Вибраний стовпець називається ключовим.

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

Рядок міток стовпців - Визначає, чи є  в базі, що підлягає сортуванню, рядок заголовків, котрий потрібно виключити із сортування. Якщо база містить мітки стовпців, то виберіть перемикач „Є”, а якщо міток немає, то виберіть перемикач „Ні”.

Параметри - Виводить вікно діалогу „Параметри сортування”, в якому Ви можете:

визначити користувальницький порядок сортування для стовпців, зазначених у вікні „Сортувати”;

визначити сортування з урахуванням регістру;

змінити орієнтацію сортування, тобто, замість сортування зверху вниз встановити сортування зліва направо. Після встановлення усіх необхідних полів і параметрів потрібно натиснути на кнопку „ОК”.

Microsoft Excel використовує наступні основні принципи при сортуванні:

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

рядки з пустими комірками в стовпці, по яким ведеться сортування, розташовуються у кінці списку що сортується;

параметри сортування - вибраний стовпець, порядок (зростаючий чи убутний) і напрямок сортування (зверху вниз чи зліва направо);

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

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

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

5.4. Фільтрація або вибірка даних

За допомогою команди Дані  Фільтр можна відшукати і використати потрібну підмножину даних в базі. У відфільтрованій базі виводяться на екран тільки ті рядки, які містять певні значення або відповідають певним критеріям вибірки. При цьому інші рядки будуть тимчасово сховані. Підменю „Фільтр в Microsoft Excel надає можливість використовувати як команду „Автофільтр”, так і команду „Розширений Фільтр” якщо потрібно використовувати складний критерій для вибірки даних.

5.4.1. Застосування Автофільтру

Команда „Автофільтр поміщає кнопки зі списків що розкриваються (кнопки зі стрілкою) безпосередньо в рядок з мітками стовпців. За їх допомогою можна вибрати елементи бази, які потрібно вивести на екран, наприклад, всі рядки, що містять певні значення в стовпці, скажемо, усі рядки, котрі містять дату замовлення 11.02.07 в стовпці „Дата Замовлення”.

Можна також застосовувати користувальницькі критерії порівняння для фільтрації даних в базі. Для цього у списку потрібно вибрати пункт „Настройка”. На екран виведеться діалогове вікно „Користувальницький Автофільтр”. 

У цьому вікні можна визначити до двох критеріїв порівняння для одного і того ж стовпця при фільтрації даних. Microsoft Excel порівняє елементи списку з уведеними раніше значеннями і виведе лише ті рядки, які задовольняють критерію. Можна також використовувати умовні оператори „И / ИЛИ” для об’єднання або порівняння критеріїв в одному і тому ж стовпці.

Для визначення двох критеріїв виберіть один із наступних варіантів:

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

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

5.4.2. Застосування „Розширеного фільтру”

Команда „Розширений фільтр” дозволяє відшукувати рядки за допомогою більш складних критеріїв, наприклад, „продаж 10.12.07 на суму більш, ніж 500000 грн., або продаж 11.12.07 на суму більш, ніж 300000 грн.”

При виборі цієї команди, в діалоговому вікні що з’явиться, потрібно встановити наступні параметри:

Дія” - перемикач „Фільтрувати Список На Місці” приховує рядки, які не задовольняють вказаному критерію. Перемикач „Копіювати На Інше Місце” копіює відфільтровані дані на інший робочий аркуш, або на інше місце на тому ж робочому аркуші.

Інтервал Списку - визначає інтервал вмісту даних, котрі повинні фільтруватись.

Інтервал Критеріїв” - визначає інтервал комірок на робочому аркуші, котрі містять потрібні критерії. Microsoft Excel виводить посилання на цей інтервал.

Копіювати На” - визначає інтервал комірок, в який копіюються рядки і котрі задовольняють певним критеріям. Це поле активно тільки в тому випадку, якщо включений перемикач „Копіювати На Інше Місце”.

Без Повторів” - виводить тільки рядки, що задовольняють критерію і не містять повторюваних елементів.

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

5.4.3. Робота з відфільтрованими даними

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

В цьому режимі більшість команд Microsoft Excel впливають тільки на видимі комірки. Після того, як буде застосований фільтр для виводу тільки потрібних рядків, можна скопіювати отриману підмножину даних в інше місце для подальшого аналізу. Для того що повернутись до первісного виду бази даних, необхідно скористатися командою „Дані  Фільтр  „Показати все”.

 

6. Методичні рекомендації

6.1. Редагування і вибір записів у формі даних

Виділити будь-яку комірку таблиці, викликавши форму даних і виконати наступні вправи:

перейти на третій запис;

перейти на початок та кінець бази даних;

за допомогою кнопки „Створити” увести 2 нових записи; для переходу до наступного поля скористайтесь мишкою, після уведення значення останнього поля, натисніть клавішу <Enter>;

видалити четвертий запис;

змінити значення одного з полів другого запису;

виконайте те ж саме з останнім записом, але потім змінене значення відновіть.

Для того щоб вибрати запис по будь-якій умові, необхідно натиснути кнопку „Критерії і увести наступні параметри:

для вибору по одному критерію в текстовому полі, потрібно увести одне конкретне значення у відповідне поле;

для вибору більше одного значення, потрібно поставити оператор порівняння “>“ у відповідному полі і написати потрібне значення;

для вибору записів по першій літері текстового поля, необхідно увести першу літеру у відповідне перше поле;

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

Після уведення параметрів вибірки, натиснути клавішу <Enter>. Для перегляду вибраних записів скористайтесь кнопками „Попередня” та „Наступна”. Щоб видалити попередній критерій скористайтесь кнопкою „Очистить”. Закрийте форму даних.

Для того щоб відсортувати базу даних по убуванню третього стовпця, необхідно вибрати будь-яку комірку третього поля бази даних і виконати команду „Дані  Сортування”. З’явиться діалогове вікно, в якому в полі „Сортувати повинно автоматично з’явитись ім’я третього стовпця. Вказати порядок сортування по убуванню.

В першому полі потім потрібно визначити порядок вторинного сортування для рядків третього поля, в яких є співпадаючі елементи. Потрібно вибрати ім’я другого поля і встановити прапорець „По Зростанню”. Після встановлення усіх параметрів натисніть на клавішу „ОК”.

 

6.2. Фільтрація даних

Виділити будь-яку комірку бази даних і виконати команду Дані  Фільтр  Автофільтр”. В рядку з іменами стовпців з’являться кнопки списків що розкриваються (кнопки із стрілками). За їх допомогою можна вибрати елементи бази, які потрібно вивести на екран. Для того щоб відфільтрувати однакові записи в текстовому полі, потрібно вибрати зі списків що розкриваються, відповідне значення цього поля. Для виконання інших завдань потрібно у відповідних полях вибрати значення зі списку „Настройка”.

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

7. Контрольні питання

7.1. Що розуміють під терміном „база даних”?

7.2. Як потрібно змінити таблицю щоб вона мала вигляд бази даних ?

7.3. Які можливості по редагуванню баз даних надає команда „Форма”?

7.4. Які критерії можна задавати в команді „Форма”?

7.5. Для чого потрібне сортування записів і як його здійснити ?

7.6. Як задати порядок вторинного сортування ?

7.7. Як відсортувати тільки один стовпець в таблиці ?

7.8. Як здійснити сортування по рядкам ?

7.9. Чи розрізняється верхній та нижній регістр при сортуванні ?

7.10. Що можна зробити за допомогою команди „Автофільтр ?

7.11. Які параметри потрібно встановити щоб дані вибирались при одночасному виконанні двох умов?

7.12. Які параметри потрібно встановити щоб дані вибирались при виконанні хоча б однієї з двох умов?

7.10. Що можна зробити за допомогою команди „Розширений фільтр”?

 

8. Рекомендована література

8.1. Зацерковний В.І. Конспект лекцій по дисципліні „Інформатика і програмування” Чернігів ЧДЕІУ -2007, ч.III Microsoft Excel;

8.2 Наймершайм Джек „Exсel 5.0 for Windows”. -М.: Международные отношения 1995, -240 c.

8.3 Харвей Грег „Exсel 5.0 для „чайников””, -Киев: Диалектика, 1995. - 288 с.

8.4. Блаттнер П. „Использование Microsoft Excel 2002”.М.: Изд. Дом. «Вильямс» 2002 – 860 с.

8.5. Ларсен Р.У. „Инженерные расчеты в Excel” М. Изд. Дом. «Вильямс» 2002 –544 с.

8.6. Долдж М., Стинсон К. „Эффективная работа с Microsoft Excel 2000”. М.:«Питер» 2000 – 1052 с.

PAGE  41


 

А также другие работы, которые могут Вас заинтересовать

51034. Изучение зависимости температуры кипения воды от внешнего давления 58.5 KB
  Цель работы: Изучение зависимости температуры кипения воды от внешнего давления экспериментальное определение теплоты парообразования воды. Выждали пока показания температуры на табло электронного измерителярегулятора перестали измеряться. Сняли показания температуры и давления на табло измерителярегулятора и манометра: Включили переключатель нагрев 2ую ступень.
51035. Разработка тестов в программеPower Point 37.5 KB
  Индивидуальные данные для выполнения работы: 2 вариант Результаты выполнения работы Открыла программу Power Point; выбрала шаблон оформления слайдов для теста. Оформила титульный слайд. Как рассчитать количество нужных слайдов при оформлении теста в Power Point Количество слайдов =: количество вопросов 3сам вопрос правильно неправильно два титульных листа 1 заключительный; 4. Что необходимо выбрать в настройках слайда с результатом неправильного ответа чтобы вернуться на слайд с вопросом Добавить управляющую...
51036. Разработка тестов в программе Excel 37 KB
  Разработка тестов в программе Microsoft Excel на основе индивидуальных данных минимум 6 тестовых заданий. Индивидуальные данные для выполнения работы: 2 вариант Результаты выполнения работы Создала тест в Microsoft Excel 2010 по образцу данному в задании лабораторной работы Контрольные вопросы 1. Какие этапы создания тестовых заданий выделяют в технологии составления компьютерных тестов средствами Excel Можно выделить следующие этапы создание теста.
51038. Автоматизація та компютерно-інтегровані технології. Методичні вказівки 44.28 MB
  По статичним характеристикам перетворення визначити абсолютну відносну та приведену похибки по діапазону вимірювання для обох приладів та побудувати графіки для обох приладів: а реальної статичної характеристики перетворення; б залежності приладів похибок по діапазону вимірювання.
51039. Налоговая декларация. Налоговый контроль 72.01 KB
  Налоговая декларация представляет собой письменное заявление налогоплательщика о полученных доходах и произведенных расходах, источниках доходов, налоговых льготах и исчисленной сумме налога и (или) другие данные, связанные с исчислением и уплатой налога.
51040. Спектры видеоимпульсов 1.48 MB
  ОСНОВНЫЕ ХАРАКТЕРИСТИКИ ИМПУЛЬСОВ Различают импульсы высокочастотных колебаний называемые радиоимпульсами и видеоимпульсы не связанные с высокочастотными колебаниями. В дальнейшем при отсутствии оговорок под импульсами следует понимать видеоимпульсы положительной или отрицательной полярности. Резкий подъем импульса называется фронтом резкий спад срезом а верхняя часть вершиной. Иногда после среза импульса наблюдается выброс противоположной полярности за которым может следовать медленно меняющаяся часть называемая хвостом...