- •Розділ 3. Інформаційні системи
- •Тема 8. Системи табличної обробки даних
- •8.1. Теоретичні відомості та методичні поради до вивчення теми
- •8.1.1. Поняття табличного процесора
- •8.1.2. Особливості Microsoft Excel
- •Ключові переваги табличного редактора ms Excel
- •8.1.2.1. Ефективний аналіз і обробка даних
- •Багаті засоби форматування та відображення даних.
- •Наочний друк.
- •Спільне використання даних і робота над документами.
- •Обмін даними та робота в Інтернеті.
- •Можливості
- •8.1.2.2. Інтеграція в Microsoft Office
- •8.1.2.3. Майстри
- •8.1.2.4. Спеціальні можливості
- •8.1.2.5. Програмування в Exсel
- •8.1.2.6. Сфери застосування
- •8.1.3. Основи роботи з табличним процесором
- •8.1.3.1. Вигляд і основні поняття Робоча книга і робочий аркуш
- •Рядок стану
- •Основні правила
- •Структура комірки Excel:
- •Блоки комірок
- •8.1.3.2. Введення та редагування даних
- •8.1.3.3. Меню і піктограми
- •Панелі інструментів
- •Розміщення панелей на екрані.
- •Настроювання панелі інструментів.
- •Панель інструментів Стандартна.
- •Панель інструментів Форматування
- •8.1.3.4. Формат даних
- •Стиль подання даних
- •8.1.4. Обчислення в Excel. Формули та функції
- •8.1.4.1. Формули
- •8.1.4.2. Використання посилань і імен
- •8.1.4.3. Переміщення та копіювання формул. Відносні й абсолютні посилання
- •8.1.4.4. Функції Поняття функції
- •Типи функцій
- •8.1.4.5. Майстер функцій
- •8.1.5. Серії та діаграми
- •Прогресія
- •Створення серій
- •Перший спосіб
- •8.1.5.2. Діаграми та графіки
- •Типи діаграм
- •Побудова діаграм
- •8.1.6. Робота зі списками та бази даних1 у Excel
- •8.1.6.1. Формування списку
- •8.1.6.2. Робота з командою Форма
- •8.1.6.3. Пошук у базі даних
- •8.1.6.4. Сортування бази даних
- •8.1.6.5. Фільтрація даних у списку
- •8.1.6.6. Підбиття підсумків у базі даних
- •Вставка проміжних підсумків
- •Створення діаграми закупівельних цін
- •8.1.7. Деякі спеціальні можливості
- •8.1.7.1. Автоформат
- •8.1.7.2. Розв’язання рівнянь
- •8.1.7.3. Створення сценаріїв
- •8.1.7.4. Звіт за сценаріями
- •8.1.7.5. Створення макросу
- •8.1.7.6. Створення активної кнопки
- •8.1.8. Деякі особливості (для поглибленого вивчення)
- •8.1.8.1. Корисні поради для роботи з Excel 97 і Excel 2000 Створення Web-сторінки
- •Використання Web-запитів
- •Зв’язок напису з коміркою електронної таблиці
- •Блокування доступу до особистих комірок електронної таблиці
- •«Підвищення» точності обчислень формул
- •Переклад рядка в комірці таблиці
- •Локальне ім’я (використання того самого імені на різних аркушах)
- •«Гарячі» клавіші
- •Функції сумм() і если() можуть оперувати з масивами.
- •Як до дати додавати місяці
- •8.2. Контрольні запитання та теми для обговорення
- •8 .3. Завдання для самостійної роботи
- •8.4. Завдання для перевірки знань
- •8.5. Основні терміни та визначення
8.1.7. Деякі спеціальні можливості
В Excel існують деякі цікаві можливості, що дають можливість спростити роботу і трохи автоматизувати її. Нижче наведені деякі з таких можливостей.
8.1.7.1. Автоформат
Команда Формат/Автоформат... дає можливість швидко оформити готову таблицю, скориставшись готовим Списком форматов.
Рис. 8.34. Автоформат
8.1.7.2. Розв’язання рівнянь
Однією з найцікавіших особливостей Excel є можливість віднаходження коренів рівнянь типу f(x) = a. Для цього необхідно в якусь із комірок увести передбачуване (початкове) значення кореня рівняння, в іншу комірку записати саме рівняння f(x), виконати команду Сервис/Поиск решения.... У вікні Поиск решения, у поле Установить целевую ячейку записати адресу комірки, в якій записане рівняння f(x), у поле Равной:/Значению ввести значення a, у поле Изменяя ячейки ввести адресу комірки, в якій міститься передбачуване (початкове) значення кореня рівняння, і натиснути кнопку Выполнить. Після цього в комірці, де містилося передбачуване (початкове) значення кореня рівняння, з’явиться обчислене, значенню кореня рівняння з точністю, заданою в розділі Параметры....
Якщо рівняння має не один, а безліч коренів, то інші ко- рені можна одержати, змінюючи початкові значення кореня рівнянь.
Зауваження. Початкове значення необхідно підбирати з певною обережністю, якщо функція, у якій шукаються корені, має різні особливості, тому що в процесі пошуку рішення початкове значення може перебувати в ділянці негативних градієнтів, у результаті чого «спіраль» пошуку може бути не такою, що сходиться, а такою, що розходиться.
Рис. 8.35. Розв’язання рівняння в Excel
8.1.7.3. Створення сценаріїв
Часто виникає необхідність тим самим коміркам присвоїти різні значення, причому так, щоб не втратити попередні. В Excel це виконується за рахунок механізму сценаріїв, виклик якого здійснюється через меню Сервис/Сценарии... Відкри- ється вікно, в якому буде список усіх наявних сценаріїв (спо- чатку цей список порожній). Натисніть кнопку Добавить..., з’явиться вікно.
Рис. 8.36. Вікно створення сценарію
У цьому вікні потрібно:
1. Дати ім’я сценарію.
2. Позначити ті комірки, що будуть змінюватися даним сце- нарієм.
3. Написати коментарі до даного сценарію в поле Примечание.
Натиснути кнопку ОК.
Рис. 8.37. Диспетчер сценаріїв
Після цього з’явиться нове вікно, в яке потрібно ввести значення для позначених комірок. Описану операцію потрібно повторити кілька разів для створення декількох сценаріїв.
Для того, щоб заповнити комірку значеннями з конкретного сценарію, потрібно вибрати його в списку і натиснути кнопку Вывести.
8.1.7.4. Звіт за сценаріями
Після того, як сценарії створені, можна створити звіт за цими сценаріями. Для цього використовується кнопка Отчет...
У вікні Отчет по сценариям вкажіть: Тип отчет/Структура, а в полі Ячейка результата — вкажіть адреси комірок (розділені знаком «крапка з комою»), у яких містяться підсумки (наприклад: I13;I19;I25;I31;I38).
Після цього буде автоматично створена нова сторінка зі звітом за вихідними даними сценаріїв і комірок результатів.
8.1.7.5. Створення макросу
Макрос — це запис певної послідовності дій в Excel. Такий запис виконується мовою Visual Basic (чи мовою макро- команд для ранніх версій) на спеціальних аркушах робочої кни- ги «Модуль#», що є редакторами цієї мови. Кожен такий запис повинен мати власне ім’я. З погляду програмування — це під- програма.
Sub сценарії()
‘ сценарії Макрос
‘ Макрос записаний 15.08.99 (bm) Створення звіту за сценаріями
Sheets(«Структура сценарію»).Select
ActiveWindow.SelectedSheets.Delete
Range(«A1»).Select ActiveSheet.Scenarios.CreateSummary
ReportType:=xlStandardSummary,_
ResultCells:=Range(«I13,I19,I25,I31,I38»)
End Sub
Створювати макроси можна «вручну» чи автоматично — змусити Excel відстежувати всі дії та записувати їх у відповідний модуль.
Для цього:
1. Спершу перейдіть на ту комірку, з якою почнеться запис.
2. Потім потрібно ввійти в меню Сервис/Макрос/Начать запись...
3. У вікні Запись макроса дати йому ім’я й у поле Опи- сание записати короткий коментар: для чого цей макрос, кнопка ОК. З цього моменту всі дії записуються у вигляді інструкцій Visual Basic. На екрані з’явиться додаткова панель інструментів із кнопкою Остановить запись. Ні в якому разі не закривайте цю панель за допомогою кнопки закриття вікна!
4. Виконати послідовність дій, що визначають макрос.
5. Натиснути Остановить запись. Запис дій у модуль припиниться, і додаткова панель зникне з екрана.
Виконати макрос можна через меню Макрос/Макросы... — вибрати в списку ім’я макросу — кнопкою Исполнить. Макросу також можна призначити гарячу клавішу, чи кнопку на панелі інструментів (це робиться через діалогове вікно Настройка, виклик: меню Вид/Панели инструментов/Настройка...).