Контрольно-екзаменацiйна робота

НА ТЕМУ: АВТОМАТИЗАЦIЯ КОНТРОЛЮ ВIДВIДУВАННЯ СТУДЕНТАМИ ЗАНЯТЬ

Вступ

Опис програмних засобів, використаних в екзаменаційній роботі.

Для виконання даної екзаменаційної роботи був використаний програмний
засіб Microsoft Excel з пакету офісних програм Microsoft Office.

Загальний вигляд програмного засобу “Microsoft Excel“

Як і будь-яка Windows – програма, Microsoft Excel містить ряд типових
елементів. Рядок заголовка (верхній рядок вікна) містить назву програми
(“Microsoft Excel”), а також назву книги, з якою працюють в даний
момент. Крайня ліва кнопка є кнопкою виклику системного меню, за
допомогою якого можна керувати розмірами і положенням програми, а також
закривати її. Праворуч розміщені відповідно кнопка згортання,
відновлення та закриття програми.

Рядок меню

Рядок меню звичайно розміщується під вікном заголовку і включає такі
пункти:

”Файл” — робота з файлами книг (створення, збереження, відкриття файлів,
друкування файлів книг);

“Правка” – вміщує команди роботи з книгою, пошуку та заміни;

”Вид” – дозволяє змінити спосіб відображення книги на екрані
(звичайний/розмітка сторінки), а також задати панелі інструментів, що
будуть відображені, та змінити масштаб;

“Вставка” — вставка в книги нових листів, малюнків, діаграм та інших
типів даних;

“Формат” – дозволяє змінити формат комірки, рядка / колонки чи цілого
листа, управління стилями;

“Сервіс” – сервісні функції (перевірка орфографії, настройка автозаміни,
встановлення захисту на лист чи цілу книгу, робота з макросами та
настройка параметрів Excel);

“Дані” – робота з базами даних (сортування, фільтр та ін.);

“Вікно” – робота з вікнами книг ;

“Справка” – виклик довідкової інформації.

Панель інструментів «Стандартнаная»

Створення нового файла на основі активного шаблона. (Файл(Создать… /Ctrl
+ N/)

Відкриття чи пошук файл (Файл(Открыть… /Ctrl + O/)

Збереження поточного файла без зміни його назви, формату і місця
розташування (Файл(Сохранить… /Ctrl + S/)

Друк поточного файла чи виділеного елемента (Файл(Печать… /Ctrl+P/)

Попередній перегляд документа в тому виді, в якому він буде надрукований
(Файл(Предварительный просмотр)

Перевірка орфографії в поточному файлі, документі, книзі чи
повідомленні (Сервис(Орфография… /F7/)

Вилучення виділеного фрагмента з поточного документа і вставка його в
буфер обміну (Правка(Вырезать /Ctrl + X/)

Копіювання виділеного фрагмента в буфер обміну (Правка(Копировать /Ctrl
+ С/)

Вставка фрагмента з буферу обміну в поточну позицію з заміною виділеного
фрагмента текста. Команда доступна тільки в тому випадку, коли буфер
обміну містить будь – які дані. (Правка(Вставить /Ctrl + V/)

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

Відміна останньої виконаної команди або вилучення останнього введеного
фрагменту. Для відміни декількох останніх команд треба натиснути стрілку
і вибрати дії, які необхідно відмінити. Якщо відмінити останню дію
неможливо, то назва цієї команди міняється на Нельзя отменить.
(Правка(Отменить /Ctrl+Z/)

Відміна дії останньої дії клавіші Отменить. Як і в попередньому випадку
є можливість вернутись на декілька кроків. (Правка(Вернуть /Ctrl+Y/)

Вставка чи редагування заданої гіперссилки (Вставка(Гиперссылка…
/Ctrl+K/)

Відображає / ховає панель WEB

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

Вивід списку функцій і їх прототипів з можливістю задавати значення
аргументів. (Вставка(Функция…)

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

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

Запуск майстра діаграм, який дозволяє крок за кроком створити нову
діаграму, чи замінити існуючу. (Вставка(Диаграмма…)

Створення карти на основі виділених даних. Дані повинні вміщувати
ссилки на географічні назви, наприклад назви країн чи регіонів.
(Вставка(Карта…)

Виводить панель WordArt для створення текстового ефекту.

Ввід масштабу від 10 до 200% для збільшення чи зменшення зображення
активного документа на екрані. (Вид(Масштаб…)

Клавіша виклику Помощника.

Панель інструментів «Форматирование»

Зміна шрифту виділеного тексту. Шрифт може бути змінений в полі Шрифт.

Зміна розміру шрифту виділеного тексту. В полі Размер шрифта необхідно
ввести нове значення розміру шрифту. Набір доступних розмірів залежить
від вибраного шрифту і поточного принтера.

Оформлення виділеного тексту напівжирним шрифтом. Якщо виділений текст
вже є напівжирний то ця клавіша зніме цей режим.

Оформлення виділеного тексту курсивом. Якщо виділений текст вже є курсив
то ця клавіша зніме цей режим.

Оформлення виділеного тексту підкресленням. Якщо виділений текст вже є
підкреслений то ця клавіша зніме цей режим.

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

Вирівнювання виділеного тексту, чисел і вкладених об’єктів по центру.

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

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

Форматування виділених комірок в Міжнародному грошовому форматі у
відповідності з настройками Windows в Панелі управління.

Установка процентного формату для виділених комірок

Установка формату з розділювачем для виділених комірок

Збільшення числа дробових знаків для виділених комірок

Зменшення числа дробових знаків для виділених комірок

— Додає границі до виділеної комірки чи діапазону

— Зменшення відступу виділеної комірки приблизно на ширину символу
стандартного шрифта

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

Форматування виділеного тексту заданим кольором

“Рядок формул”

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

Наступні 3 клавіші використовуються для вводу формул:

Вийти з режиму редагування формул без запам’ятовування змін

Запам’ятати зміни в формулі і вийти з режиму редагування

Використовується для входу в режим редагування формул

— Власне, рядок формул

Робота з базами даних в Excel

Робочу книгу Microsoft Excel можна розглядати як базу даних. Кожну
колонку тоді розглядають як поле, а кожен рядок – як один запис в базі
даних. Оскільки в базах даних кожне поле має свою назву, тип, ширину та
інші параметри то аналогічно потрібно встановити ці параметри і для
кожної колонки бази даних.

Як видно з наведеної вище бази даних по співробітниках вона має такі
поля: Код сотрудника, Фамилия, Имя, Должность, Дата найма, Адрес, Город,
Домашний телефон. В дану базу даних зараз введено 9 записів.

Загальні рекомендації

по роботі з базами даних в Excel.

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

Організація списку

Використання тільки одного списку на листі.   Уникайте створення більш
ніж одного списку на листі. Деякі можливості управління списком,
наприклад функція фільтрації, можуть бути використані тільки для одного
списку.

Розміщуйте подібні об’єкти в одну колонку. Спроектуйте список таким
чином, щоб всі рядки вміщували схожі об’єкти в одній колонці.

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

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

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

Формат списка

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

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

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

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

Ввід даних в базу даних з допомогою форми

Режим вводу даних в базу даних з допомогою форми включається командою
Данные=> Форма. При успішному виконанні даної команди на екрані
з’явиться вікно, схоже на зображене нижче. В противному випадку
з’явиться вікно з повідомленням про те, що Excel не може знайти дані. В
цьому випадку необхідно помістити табличний курсор на будь-який запис в
базі даних.

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

Клавіші Добавить і Удалить служать для вставки/вилучення записів з бази
даних. При вставці запису він з’явиться в кінці бази даних, а при
вилученні – вилучається поточний запис. При вилученні буде видано вікно
підтвердження дії з повідомленням про те, що відновити даний запис
після вилучення буде неможливо.

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

Клавіша Критерии дозволяє знайти записи по заданих умовах. При
натисненні клавіші Критерии номер запису в правому верхньому куті форми
міняється на напис Критерии. В полях, розміщених в лівій частині форми,
необхідно ввести одне чи кілька значень, які будуть використовуватись в
якості умов, Потім натисніть кнопку Далее або Назад.

Клавіша Закрыть закриває форму. Для запису введених записів необхідно
зберегти книгу.

Сортування бази даних

Для сортування бази даних необхідно вибрати команду Данные=>Сортировка.
Як і в попередньому випадку, табличний курсор необхідно встановити на
будь-який запис в базі даних інакше буде видано повідомлення про те, що
список не знайдено. Якщо перед вибором даної команди був виділений
діапазон то сортуватись буде саме він. В противному випадку Excel сам
виділить цілу базу даних. Не рекомендується сортувати частини полів
(коли виділені не цілі записи а тільки окремі поля з них), тому що це
призведе до того, що будуть просортовані тільки окремі поля бази даних
без корекції решти полів.

На екран буде виведена форма, що зображена нижче:

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

При необхідності можна встановити і сортування по кількох полях.
Наприклад при сортуванні по прізвищу а потім по імені спочатку база
даних буде просортована по полю Прізвище, а потім (в межах однакових
прізвищ) по імені.

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

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

Клавіша Параметры викликає діалогове вікно Параметры сортировки:

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

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

Також в цьому вікні можна змінити об’єкти сортування – рядки чи стовпці.
Для сортування бази даних необхідно вибрати Строки диапазона.

Використання автофільтра

Команда «Автофильтр» є найбільш швидким способом вибрати і вивести на
екран тільки необхідні дані. Для включення режиму Автофильтр необхідно
встановити табличний курсор Excel на будь-який запис в базі даних і
вибрати команду Данные =>Фильтр =>Автофильтр.

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

Якщо перед вибором команди автофільтр не було вибрано запис бази даних
та екран буде видано наступне повідомлення:

У випадку успішного виконання команди Автофільтр справа від назв полів
з’являться стрілки автофільтра.

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

Умови відбору автофільтра

Необхідно: Дії

Всі рядки списку Все

Задане число рядків з максимальними чи мінімальними значеннями комірок
поточної колонки Первые 10

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

Всі рядки, що мають порожні комірки в поточній колонці Пустые

Всі рядки, що мають не порожні комірки в поточній колонці Непустые

Примітка. Умови Пустые і Непустые можна використовувати тільки тоді,
коли в колонці містяться порожні комірки.

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

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

Вилучення фільтра із списка.

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

• Щоб вилучити фільтри для всіх колонок списку потрібно вибрати пункт
Фильтр в меню Данные, а потім — команду Показать все.

• Для вилучення автофільтра із списка, потрібно вибрати пункт Фильтр в
меню Данные, а потім — команду Автофильтр.

Фільтрація списка з

допомогою розширеного фільтра

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

1. Виділіть заголовки колонок, що фільтруються і натисніть кнопку
Копировать.

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

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

4. Вкажіть комірку в списку.

5. В меню Данные виберіть команду Фильтр, а потім — команду Расширенный
фильтр.

6. Щоб показати результат фільтрації, сховавши непотрібні рядки,
встановіть перемикач «Обработка» в положення Фильтровать список на
месте.

Щоб скопіювати відфільтровані рядки в іншу область листа, встановіть
перемикач «Обработка» в положення Скопировать результаты в другое место,
перейдіть в поле Поместить результат в диапазон, а потім вкажіть верхню
ліву комірку області вставки.

7. Введіть в поле Диапазон условий ссилку на діапазон умов відбору, що
включає заголовки колонок.

Щоб сховати діалогове вікно Расширенный фильтр на час виділення
діапазона умов відбору, натисніть кнопку згортання діалогового вікна.

Поради

• Якщо присвоїти діапазонові ім’я Критерии, то ссилка на діапазон буде
автоматично появлятися в полі Диапазон условий. Можна також присвоїти
ім’я База_данных для діапазона фільтруємих даних і ім’я Извлечь для
області вставки результатів, і ссилки на ці діапазони будуть появлятися
автоматично в полях Исходный диапазон і Поместить результат в диапазон
відповідно.

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

Опис необхідного апаратного забезпечення.

Для виконання даної екзаменаційної роботи було використано комп’ютер
наступної конфігурації:

Центральний процесор – AMD K6 з тактовою частотою 233 MHz

Оперативна пам`ять — 32 Мбайт

Жорсткий диск — 2,1 Гбайт

SVGA – відеоадаптер – S3 Trio 64 – 2 Мбайт

Для роботи програмного засобу систематизації даних відвідування
студентами занять достатньо будь-якого комп’ютера, що має встановлений
пакет офісних програм Microsoft Office.

Розробка автоматизованого програмного засобу.

Постановка задачі

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

Загальні відомості

Програма створена в Microsoft Excel і призначена для узагальнення даних
по відвідуванню студентами занять.

Особливості програми:

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

Збереження даних по кожному місяцю і кожній групі на окремому листі;

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

Централізований ввід даних;

Контроль введених даних на наявність помилок;

Поділ на пропуски з поважними причинами і без них;

Створення звіту по відвідуванню по певних групах і місяцях;

Можливість вказати границю допустимої кількості пропусків;

Створення зведеного звіту по групах;

Автоматичне створення діаграми;

Основні прийоми роботи з програмою.

Робоча книга складається таких листів:

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

Лист “Списки” – ввід списків груп і студентів, що в них навчаються;

Лист “Ввід” – призначений для вводу даних відвідування;

Лист “Звіт” – формування звіту а також діаграми відвідування;

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

Ввід списків учнів.

Список учнів вводиться в листі “Списки”.

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

Третій рядок призначений для вводу номерів груп.

Вимоги до номерів груп:

Повинен містити тільки цифри;

Якщо введено більше трьох символів, то до уваги будуть братись тільки
перші три;

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

Рекомендується першою цифрою ставити номер курсу, оскільки при
натисненні клавіші “Ок” групи будуть просортовані в порядку зростання
номерів, що полегшує пошук. Максимальна кількість груп – 254.

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

Зліва на зеленому фоні розміщені номера студентів, що автоматично
проставляються програмою. Для зручності вводу вікно “Списки” закріплено
так, що на екрані постійно відображається номер поточної групи і номер
студента. Максимально допустима кількість студентів у групі – 98 чол.

Після введення всіх даних потрібно натиснути клавішу “Ок” в лівому
верхньому куті. Її дія:

Перегляд і виправлення номерів груп;

Сортування списків по номеру групи;

Підбір ширини комірок таким чином, щоб прізвища повністю вміщувались в
них;

Обновлення інформації при списки груп в елементах вибору на інших
листах.

Не рекомендується змінювати значення комірок, що мають відмінне від
білого забарвлення. Лист має сховану колонку “А”. Її також не
рекомендується модифікувати.

Дані відвідування вводяться в листі “Ввід”.

Порядок вводу даних:

В полі Група вибрати потрібну групу

В полі Місяць вибрати необхідний місяць

Натиснути клавішу Вивести. Після цього поля Група і Місяць та клавіша
Вивести стане недоступною аж до збереження або очистки даних. Якщо лист
із даними відвідування для обраного місяця і групи не існував, то він
створюється і йому присвоюється ім’я що складається з порядкового номера
місяця і номера групи. Наприклад, лист із даними відвідування групи 327
за грудень буде називатись “12-327”. Якщо ж лист існував, то дані з
нього будуть виведені в поточний лист.

Ввести дані в потрібні комірки:

пропуск без поважної причини – символ “б” або “Б”

пропуск по поважній причині – символ “п” або “Б”

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

Зберегти дані не вдасться поки всі помилки не будуть виправлені. Про
помилку сигналізує колонка Контроль. Напроти рядка, що містить невірні
дані в колонці Контроль з’явиться слово Помилка. Помилкою вважається
ввід символів відмінних від “б”, “Б”, “п”, “П”, а також ввід будь — яких
символів в область без прізвища.

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

Створення звітів та діаграм.

За створення звітів і діаграм відповідає лист “Звіт”

Для створення звіту потрібно:

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

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

Задати максимальну кількість дозволених пропусків по поважній причині і
без поважної причини в полі Дозволена кількість пропусків.

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

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

Опис функцій Microsoft Excel,

використаних в роботі.

Функція ЕСЛИ

Повертає одне значення, якщо задана умова при обчисленні дає значення
ИСТИНА, і друге значення, якщо ЛОЖЬ.

Функція ЕСЛИ використовується при перевірці умов для значень і формул.

Синтаксис

ЕСЛИ(логічний_вираз; значення_якщо_ИСТИНА; значення_якщо_ЛОЖЬ)

Логічний_вираз  — це будь-яке значення або вираз, що приймає значення
ИСТИНА чи ЛОЖЬ.

Значення_якщо_ИСТИНА — це значення, яке повертається, якщо
логічний_вираз дорівнює ИСТИНА.

Значення_якщо_ЛОЖЬ — це значення, яке повертається, якщо логічний_вираз
дорівнює ЛОЖЬ.

Функція ПРОСМОТР

Синтаксис

ПРОСМОТР(искомое_значение;просматриваемый_вектор; вектор_результатов)

Искомое_значение   — це значення, яке ПРОСМОТР шукає в першому векторі.
Искомое_значение може бути числом, текстом, логічним значенням, ім’ям
чи ссилкою на значення.

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

Важливо!   Значення в аргументі просматриваемый_вектор повинні бути
розміщені в порядку зростання …, -2, -1, 0, 1, 2, …, A-Z, ЛОЖЬ,
ИСТИНА; в противному випадку функція ПРОСМОТР може повернути невірний
результат. Тексти в нижньому і верхньому регістрі вважаються
еквівалентними.

Вектор_результатов   — це інтервал, що містить тільки один рядок або
одну колонку. Він повинен бути такого самого розміру як і
просматриваемый_вектор.

Якщо ПРОСМОТР не може знайти искомое_значение, то підходящим вважається
найбільше значення в аргументі просматриваемый_вектор, яке менше, ніж
искомое_значение.

Якщо искомое_значение менше, ніж найменше значення в аргументі
просматриваемый_вектор, то функція ПРОСМОТР повертає значення помилки
#Н/Д.

Функція СЧЁТЕСЛИ

Подсчитывает количество ячеек внутри диапазона, удовлетворяющих
заданному критерию.

Синтаксис

СЧЁТЕСЛИ(диапазон;критерий)

Диапазон   — це діапазон , в якому потрібно порахувати комірки.

Критерий   — це критерій в формі числа, виразу чи тексту, який визначає,
які комірки потрібно підрахувати.

Функція СУММ

Додає всі числа в інтервалі комірок.

Синтаксис

СУММ(число1;число2; …)

Число1, число2, …   — це від 1 до 30 аргументів, для яких потрібно
визначити підсумок чи суму.

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

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

Аргументи, які являються значеннями помилок чи текстами що не
перетворюються в числа викликають помилку.

Функція СЧЁТЗ

Підраховує кількість не порожніх значень в списку аргументів. Функція
СЧЁТЗ використовується для підрахунку кількості комірок з даними в
інтервалі чи масиві.

Синтаксис

СЧЁТЗ(значение1; значение2; …)

Значение1, значение2, …   – це від 1 до 30 аргументів, кількість яких
потрібно підрахувати. В даному випадку значеннями вважається значення
любого типу, включаючи порожній рядок («»), але не включаючи порожні
комірки. Якщо аргументом є масив чи ссилка, то порожні комірки в масиві
чи ссилці ігноруються.

Макрос клавіші “Ок” на листі “Списки”

Реакція програми на натиснення клавіші “Ok”:

Перегляд комірок в діапазоні С3:IV3 і виправлення в них номерів груп
(контроль за їх довжиною)

Сортування списків студентів по номеру групи

Підбір ширини колонок із списками груп

Установка списку вибору номерів груп в ComboBox1 на листі «Ввід»

Private Sub CommandButton1_Click()

For Each w In Range(«C3:IV3»)

If w.Value <> «» Then

w.Value = Mid(w.Value, 1, 3)

If Len(w.Value) < 3 Then w.Value = String(3 - Len(w.Value), "0") + Mid(Str(w.Value), 2) End If Next Range("C3:IV100").Sort Key1:=Range("C3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight Range("C3:IV3").Copy Range("A4").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Лист1.ComboBox1.ListFillRange = "Списки!A4:A" + Mid(Str(Range("E1").Value + 3), 2) Columns("C:IV").Columns.AutoFit End Sub Макрос клавіші “Вивести” на листі “Ввід” Реакція програми на натиснення клавіші “Вивести”: Перегляд назв всіх листів у книзі з метою встановлення наявності вибраного листа В випадку відсутності вибраного листа виконується його автоматичне створення Створення заголовка таблиці Вибір кольору Підбір ширини комірок Запис формул в лист Копіювання даних з заданого листа на лист “Ввід” і включення виводу списка студентів на листі Відключення елементів вибору групи і місяця, відключення клавіші “Вивести”, включення клавіш “Очистити” і “Зберегти” Private Sub CommandButton1_Click() Found = False Find = Range("A3").Value2 + "-" + Range("A2").Value2 For Each Wh In Worksheets If Wh.Name = Find Then Found = True Next If Found = False Then ' СТВОРЕННЯ НОВОГО ЛИСТА Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = Find Sheets("Ввід").Activate Sheets(Find).Rows("1:2").Font.Bold = True '№ п/п Sheets(Find).Range("A2").FormulaR1C1 = "№ п/п" Sheets(Find).Columns("A:A").HorizontalAlignment = xlCenter Sheets(Find).Range("A3").FormulaR1C1 = _ "=IF(OR(RC[1]="""",RC[1]=""ВСЬОГО""),"""",IF(R[-1]C=""№ п/п"",1,R[-1]C+1))" Sheets(Find).Range("A3").AutoFill Destination:=Sheets(Find).Range("A3:A50"), Type:=xlFillDefault Sheets(Find).Columns("A:A").ColumnWidth = 6 'прізвище Sheets(Find).Range("B2").FormulaR1C1 = "Прізвище" Sheets(Find).Columns("B:B").ColumnWidth = 15 Sheets(Find).Range("B3").FormulaR1C1 = _ "=IF(LOOKUP(""" + Лист1.Range("A2") + """,Списки!R3C3:R3C256, Списки!R[1]C[1]:R[1]C[254])=0,IF(AND(R[-1]C<>«»ВСЬОГО»»,R[-1]C<>«»»»),»»
ВСЬОГО»»,»»»»),LOOKUP(«»» + Лист1.Range(«A2») +
«»»,Списки!R3C3:R3C256,Списки!R[1]C[1]:R[1]C[254]))»

Sheets(Find).Range(«B3»).AutoFill
Destination:=Sheets(Find).Range(«B3:B50»), Type:=xlFillDefault

‘дні

Sheets(Find).Range(«C2»).Value = «1»

Sheets(Find).Range(«C2»).AutoFill
Destination:=Sheets(Find).Range(«C2:AG2»), Type:= _

xlFillSeries

Sheets(Find).Columns(«C:AG»).ColumnWidth = 2

‘назва місяця

Sheets(Find).Range(«C1:AG1»).Merge

Sheets(Find).Range(«C1:AG1»).HorizontalAlignment = xlCenter

Sheets(Find).Range(«C1»).Value = Лист1.Range(«A4»).Value

‘група

Sheets(Find).Range(«B1»).Value = «Група № » + Range(«A2»).Value

‘Колір

Sheets(Find).Range(«A1:AI2,A:A»).Interior.ColorIndex = 4

Sheets(Find).Range(«A1:AI2,A:A»).Interior.Pattern = xlSolid

‘Всього

Sheets(Find).Range(«AH1:AI2»).HorizontalAlignment = xlCenter

Sheets(Find).Range(«AH1:AI1»).Merge

Sheets(Find).Range(«AH1:AI1»).FormulaR1C1 = «Всього»

Sheets(Find).Range(«AH2»).FormulaR1C1 = «Б»

Sheets(Find).Range(«AI2»).FormulaR1C1 = «П»

Sheets(Find).Range(«AH3»).FormulaR1C1 =
«=IF(RC[-32]=»»ВСЬОГО»»,SUM(R[-1]C34:R3C),IF(RC[-33]=»»»»,»»»»,COUNTIF(R
C[-31]:RC[-1],»»Б»»)))»

Sheets(Find).Range(«AH3»).AutoFill
Destination:=Sheets(Find).Range(«AH3:AH100»), Type:=xlFillDefault

Sheets(Find).Range(«AI3»).FormulaR1C1 =
«=IF(RC[-33]=»»ВСЬОГО»»,SUM(R[-1]C35:R3C),IF(RC[-34]=»»»»,»»»»,COUNTIF(R
C[-32]:RC[-2],»»П»»)))»

Sheets(Find).Range(«AI3»).AutoFill
Destination:=Sheets(Find).Range(«AI3:AI100»), Type:=xlFillDefault

‘ Sheets(Find).Visible = False

GoTo 1

Else

1: ‘Копія даних відвідування

Лист1.Range(«A5»).Value = Find

Sheets(Find).Range(«C3:AG100»).Copy

Лист1.Range(«E7»).PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _

False, Transpose:=False

Лист1.Range(«E5»).Select

Selection.Value = «Дані відвідування за » + Range(«A4″).Value + _

» місяць групи № » + Str(Range(«A2»).Value)

Лист1.ComboBox1.Enabled = False

Лист1.ComboBox2.Enabled = False

Лист1.CommandButton1.Enabled = False

Лист1.CommandButton2.Enabled = True

Лист1.CommandButton3.Enabled = True

Лист1.Range(«A1»).Value = «Yes»

End If

End Sub

Макрос клавіші “Очистити” на листі “Ввід”

Реакція програми на натиснення клавіші “Очистити”:

Вивід попереджувального вікна і отримання відповіді від користувача

В випадку позитивної відповіді:

заборона виводу прізвищ

Відключення клавіш “Очистити” і “Зберегти”

Включення елементів вибору групи і місяця та клавіші “Вивести”

Private Sub CommandButton2_Click()

but = MsgBox(«Ви дійсно хочете очистити таблицю відвідування ?»,
vbQuestion _

+ vbOKCancel)

If but = 1 Then

Range(«E7:AI100»).Cells.Formula = «»

Range(«E5»).Value = «»

Лист1.ComboBox1.Enabled = True

Лист1.ComboBox2.Enabled = True

Лист1.CommandButton2.Enabled = False

Лист1.CommandButton1.Enabled = True

Лист1.CommandButton3.Enabled = False

Range(«A1»).Value = «No»

End If

End Sub

Макрос клавіші “Зберегти” на листі “Ввід”

Реакція програми на натиснення клавіші “Зберегти”:

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

Збереження даних на відповідному листі:

Заборона виводу прізвищ

Відключення клавіш “Очистити” і “Зберегти”

Включення елементів вибору групи і місяця та клавіші “Вивести”

Private Sub CommandButton3_Click()

If Лист1.Range(«A6») <> 0 Then

but = MsgBox(«В даних знадені помилки. Виправте їх перед
збережанням», vbCritical)

Else

but = MsgBox(«Ви дійсно хочете зберегти дані відвідування ?»,
vbQuestion _

+ vbOKCancel)

If but = 1 Then

Лист1.Range(«E5»).Value = «»

Лист1.Range(«E7:AI100»).Copy

Sheets(Лист1.Range(«A5»).Value).Range(«C3»).PasteSpecial
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Лист1.Range(«E7:AI100»).Cells.Formula = «»

Лист1.ComboBox1.Enabled = True

Лист1.ComboBox2.Enabled = True

Лист1.CommandButton2.Enabled = False

Лист1.CommandButton1.Enabled = True

Лист1.CommandButton3.Enabled = False

Лист1.Range(«A1»).Value = «No»

End If

End If

End Sub

Макрос клавіші “Створити” на листі “Звіт”

Реакція програми на натиснення клавіші “Створити”:

Очистка листа від попереднього звіту

Для кожного вибраного в ListBox листа:

Вивід «шапки»

Перегляд даних і перевірка даних по кількості пропусків

Копіювання відібраних даних на лист «Звіт»

Створення узагальненого звіту

Створення діаграми

Private Sub CommandButton1_Click()

RW = 17

GR = 0

Range(«A17:AI5000»).Clear

For I = 0 To ListBox1.ListCount — 1

If ListBox1.Selected(I) Then

‘SH — Назва листа даних з поточною групою і місяцем

SH = Mid(ListBox1.List(I), 38, 6)

‘Друкуємо шапку для групи і місяця

With Range(«A» + Mid(Str(RW), 2) + «:AI» + Mid(Str(RW), 2))

.Merge

.HorizontalAlignment = xlCenter

.Font.Bold = True

.Value = Mid(ListBox1.List(I), 1, 29)

.Interior.ColorIndex = 4

End With

RW = RW + 1

PN = 1

‘Шапка (З днями)

Range(«A16:AI16»).Copy

Range(«A» + Mid(Str(RW), 2)).PasteSpecial

With Range(«A» + Mid(Str(RW), 2) + «:AI» + Mid(Str(RW), 2))

.Interior.ColorIndex = 4

.Font.Bold = True

.Font.Color = 0

End With

RW = RW + 1

‘Переглядаємо діапазон «AH3:AH100»

For Each Cel In Sheets(SH).Range(«AH3:AH100»)

‘Але крім порожніх комірок і крім «ВСЬОГО»

If (Cel.Value <> «») And (Sheets(SH).Range(«B» +
Mid(Str(Cel.Row), 2)).Value <> «ВСЬОГО») Then

‘Для тих комірок, що задовільняють всі вищеперечислені умови
перевіряємо

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

If (Cel.Value > Sheets(«Звіт»).Range(«N3»).Value) Or _

(Sheets(SH).Range(«AI» + Mid(Str(Cel.Row), 2)).Value >
Sheets(«Звіт»).Range(«R3»).Value) Then

‘Номер

Range(«A» + Mid(Str(RW), 2)).Value = PN

PN = PN + 1

‘Копіюємо прізвище і дані

Sheets(SH).Range(«B» + Mid(Str(Cel.Row), 2) + «:AI» +
Mid(Str(Cel.Row), 2)).Copy

Range(«B» + Mid(Str(RW), 2) + «:AI» + Mid(Str(RW),
2)).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False

RW = RW + 1

End If

End If

Next

‘ВСЬОГО

Range(«B» + Mid(Str(RW), 2)).Value = «ВСЬОГО»

Range(«A» + Mid(Str(RW), 2) + «:» + «AI» + Mid(Str(RW),
2)).Font.Bold = True

If PN > 1 Then Range(«AH» + Mid(Str(RW), 2)).FormulaR1C1 =
«=SUM(R[-» + Mid(Str(PN — 1), 2) + «]C:R[-1]C)» _

Else Range(«AH» + Mid(Str(RW), 2)).Value = 0

If PN > 1 Then Range(«AI» + Mid(Str(RW), 2)).FormulaR1C1 =
«=SUM(R[-» + Mid(Str(PN — 1), 2) + «]C:R[-1]C)» _

Else Range(«AI» + Mid(Str(RW), 2)).Value = 0

With Range(«C» + Mid(Str(RW), 2) + «:AG» + Mid(Str(RW), 2))

.Merge

.Value = ListBox1.List(I)

.Font.ColorIndex = 2

End With

RW = RW + 1

GR = GR + 1

End If

Next I

‘ЗВЕДЕНИЙ ЗВІТ

‘(Заголовок)

PN = 0

With Range(«A» + Mid(Str(RW), 2) + «:AI» + Mid(Str(RW), 2))

.Merge

.Interior.ColorIndex = 4

.HorizontalAlignment = xlCenter

.Font.Bold = True

.Font.Size = 18

.Value = «Зведений звіт відвідування»

End With

‘(Дані по групам за місяць)

RW = RW + 1

For Each CL In Range(«B17:B» + Mid(Str(RW), 2))

If CL.Value = «ВСЬОГО» Then

PN = PN + 1

With Range(«B» + Mid(Str(RW), 2) + «:AG» + Mid(Str(RW), 2))

.Merge

.Value = Mid(Range(«C» + Mid(Str(CL.Row), 2)).Value, 1, 29)

End With

Range(«AH» + Mid(Str(RW), 2)).Value = Range(«AH» +
Mid(Str(CL.Row), 2)).Value

Range(«AI» + Mid(Str(RW), 2)).Value = Range(«AI» +
Mid(Str(CL.Row), 2)).Value

RW = RW + 1

End If

Next CL

‘(Всього)

With Range(«B» + Mid(Str(RW), 2) + «:AG» + Mid(Str(RW), 2))

.Merge

.Value = «ВСЬОГО»

.Font.Bold = True

.HorizontalAlignment = xlCenter

End With

Range(«AH» + Mid(Str(RW), 2) + «:AG» + Mid(Str(RW), 2)) _

.FormulaR1C1 = «=SUM(R[-» + Mid(Str(PN), 2) + «]C:R[-1]C)»

Range(«AI» + Mid(Str(RW), 2) + «:AG» + Mid(Str(RW), 2)) _

.FormulaR1C1 = «=SUM(R[-» + Mid(Str(PN), 2) + «]C:R[-1]C)»

If GR > 2 Then

SetSourceData Source:=Sheets(«Звіт»).Range(«AH» + Mid(Str(RW — PN), 2) +
«:AI» + Mid(Str(RW — 1), 2))

ActiveChart.Location Where:=xlLocationAsNewSheet

ActiveChart.Axes(xlValue).MajorGridlines.Select

ActiveChart.ChartArea.Select

ActiveChart.SeriesCollection(1).XValues = «=Звіт!R» + Mid(Str(RW —
PN), 2) + «C2:R» + Mid(Str(RW — 1), 2) + «C33»

ActiveChart.SeriesCollection(1).Name = «=»»Б»»»

ActiveChart.SeriesCollection(2).XValues = «=Звіт!R» + Mid(Str(RW —
PN), 2) + «C2:R» + Mid(Str(RW — 1), 2) + «C33»

ActiveChart.SeriesCollection(2).Name = «=»»П»»»

With ActiveChart

.HasTitle = True

.ChartTitle.Characters.Text = «Діаграма відвідування»

.Axes(xlValue, xlPrimary).HasTitle = True

.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = «Дні»

End With

ActiveChart.HasLegend = True

ActiveChart.Legend.Position = xlTop

ActiveChart.SeriesCollection(1).ApplyDataLabels
Type:=xlDataLabelsShowValue, _

AutoText:=True, LegendKey:=False

ActiveChart.SeriesCollection(2).ApplyDataLabels
Type:=xlDataLabelsShowValue, _

AutoText:=True, LegendKey:=False

End If

End Sub

Текст макроса радіо-клавіші “Сортувати по групах” на листі “Звіт”

Private Sub OptionButton1_Click()

ListBox1.Clear

M = 1

For Each Wh In Worksheets

If Mid(Wh.Name, 3, 1) = «-» Then

A(M) = «Група » + Mid(Wh.Name, 4) + Mid(Wh.Name, 1, 2) +
Sheets(Wh.Name).Range(«C1»)

A(M) = A(M) + Space(30 — Len(A(M))) + «(Лист » + Wh.Name + «)»

M = M + 1

End If

Next

Sort

For I = 1 To M — 1

A(I) = Mid(A(I), 1, 9) + » — » + Mid(A(I), 12)

ListBox1.AddItem (A(I))

Next I

End Sub

Текст макроса радіо-клавіші “Сортувати по місяцях” на листі “Звіт”

Private Sub OptionButton2_Click()

ListBox1.Clear

M = 1

For Each Wh In Worksheets

If Mid(Wh.Name, 3, 1) = «-» Then

A(M) = Wh.Name + Sheets(Wh.Name).Range(«C1»)

A(M) = A(M) + Space(30 — Len(A(M))) + «(Лист » + Wh.Name + «)»

M = M + 1

End If

Next

Sort

For I = 1 To M — 1

d = A(I)

A(I) = Mid(A(I), 7, 10) + » » + «Група » + Mid(A(I), 4, 3)

A(I) = A(I) + Space(30 — Len(A(I))) + Mid(d, 30)

Next I

For I = 1 To M — 1

ListBox1.AddItem (A(I))

Next I

End Sub

Текст макроса Sort на листі “Звіт”

Dim A(500)

Dim M

Sub Sort()

For I = 1 To M — 1

For J = 1 To I — 1

If A(I) < A(J) Then T = A(I) A(I) = A(J) A(J) = T End If Next J Next I End Sub Доцільність автоматизації. Даний програмний продукт значно допомагає систематизувати дані відвідування студентами занять і видати наочні результати у вигляді звітів і діаграм. Лінійка вертикальної прокрутки Панелі інструментів Рядок меню Рядок формул Робоча область Табличний курсор Закладки вибору активного листа Рядок стану Лінійка горизонтальної прокрутки Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата. Арк. Вик Арк. № докум. Підпис Дата.

Похожие записи