План заняття. 2. Використання функцій

План

1. Створення формул

2. Використання функцій

3. Копіювання клітинок і формул

4. Арифметичні операції та операції з текстом

5. Логічні функції

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

Формули та функції Excel

Цифри і текст вводяться в клітину безпосередньо, без попере­дньої підготовки, а введення формули починається знаком дорів­нює.

Приклад: У клітинки А1, А2, A3, А4, А5 напишемо будь-які цифри, а в клітину Аб — формулу = А1+А2+АЗ+А4+А5 і натисне­мо Enter. Excel підрахує суму і запише у клітину А6 (рис. 1).

Рис. 1

Між іншим, суму можна одержати, використавши стандартну функцію Excel.

Виділимо клітини з А1 по А5 і натиснемо кнопку Автосумма на панелі інструментів і відразу дістанемо суму чисел.

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

Формула автоматично відображується у рядку формул. Якщо ми використаємо кнопку Автосумма на панелі інструментів, то в клітині А6 буде функція =СУММ(А1:А5).

Excel може визначати суму по всьому стовпчику, тоді слід запи­сати =СУММ(А: А); по рядку =СУММ(1:1). Проте зверніть увагу, що формули потрібно ставити в інший стовпчик і в інший рядок, щоб не було циклічних додавань.

Розглянемо приклад, коли потрібно додати стовпчики А, С, а В пропустити. Тоді формула матиме вигляд =СУММ(А1:А6; С1:С6) (рис. 2).

Іноді виникає по­треба у формулі ви­користати дані з ін­шої таблиці на іншо­му аркуші. Напри­клад, знайдемо суму, що складається з вмі­сту клітин А1 на першому та другому аркушах і розмістимо цю формулу в клітині А1 на третьому аркуші, формула матиме вигляд =СУММ(Лист1!А1;Лист2!А1).

Рис. 2

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

Рис. 3

Якщо таким самим спосо­бом потрібно одержати суму по рядках, то слід виділити діа­пазон А1:С5 і захопити пустий стовпчик D, де і розмістяться суми, обчислені по рядках (рис. 4.

Рис.4

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

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

Рис5

Копіювання клітинок і формул.

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

Рис.6

основний вигляд курсору Excel;

курсор копіювання (заповнення)

Цей чорний хрестик застосовують для копіювання інформації, що є в клітинах, у тому числі формул. При цьому формули копію­ються таким чином, що аргументи змінюють свої адреси в напрям­ку руху курсору. Якщо формула була =А1+В1, то при переміщенні вниз вона змінює свій вигляд на =А2+В2 і т.д. Аналогічно веде себе Excel і при копіюванні формул по рядку. Наприклад, якщо в А6 є функція = СУММ(А1:А5), то, копіюючи формулу по рядку, одержимо =СУММ(В1:В5) і т.д. Це значно економить час при мо­делюванні розрахунків, особливо зі складними, повторюваними формулами. Можна також скопіювати формулу з однієї клітини в іншу, якщо вони розташовані не поряд. Для цього потрібно зроби­ти активною клітину з формулою, поставити курсор на її горизон тальну або вертикальну межу (курсор перетвориться на об'ємну білу стрілку), натиснути Ctrl, тоді біля стрілки з'явиться значок «плюс», який означатиме, що ми робимо копіювання. Тепер фор­мулу можна перетягти в потрібне місце. Якщо не натискати Ctrl, то формула переміститься на нове місце. Таким чином можна пе­реміщувати і копіювати стовпчики, рядки, діапазони клітин.

Розглянемо, як можна за­блокувати зміну адреси кліти­ни при копіюванні формул. Наприклад, гривні потрібно перевести в долари по курсу (рис. 7). Суму в гривнях помістимо в колонку А, а курс у клітину С1, суму в доларах в колонку С. Як же заблокувати зміну адреси клітини С1 при копіюванні? Формула в клітині С4 матиме вигляд =$С$1*А4, скопіюємо її в клітини С5:С7, і побачимо, що адреса клітини, яка міс­тить курс, не змінилась. Знак $ блокує зміну адреси клітини, тобто ми задали для клітини С1 у формулі абсолютну адресу.

Рис. 7

Клітина блокується натисканням клавіші F4 або введенням знака $ з клавіатури.

За потреби можна задати абсолютну адресу стовпчика або ряд­ка. Застосовують ще інший спосіб адресації — присвоєнням імені клітини комбінацією клавіш Ctrl+F4. Потім можна звертатися до потрібної клітини з будь-якого аркуша книги.

Арифметичні операції та операції з текстом. Excel викори­стовує звичайні знаки арифметичних операцій +, -,*, /, ^. Ариф метичні операції записують за загальноприйнятими правилами. Можна використовувати багаторівневі дужки, наприклад: =((F4+C5)/E6)*(B2-D2)+C4^2. Щоб об'єднувати або приєднувати текст до формул, використовують знак & (амперсанд). Так, якщо обчислена сума виражається в гривнях, то формула набирає ви­гляду =(F4+C5)/E6)*(B2-D2)+C4^2&«rpн». Зверніть увагу на те, що приєднаний текст береться в лапки. Пробіли також мають бути в лапках. Дуже важливо знати, що цифра з назвою не вважається числом і не може використовуватися як аргумент в інших форму­лах. В Excel можна об'єднувати текст. Наприклад, у клітину А1 введемо текст Наш головний партнер, в А2 — масло, в A3 — завод. В А4 напишемо формулу = «В 2001 р.»&А1«є»& А2&АЗ, одержимо «В 2001 р. наш головний партнер є маслозавод».

Логічні функції значно розширюють можливості Excel:

> команда ЕСЛИ дає змогу організовувати різного виду розга­луження. Формат команди: = ЕСЛИ (Логічна умова; коли прави­льно; коли неправильно). Логічна умова може бути виражена зна­ками >, <, =, >=, <=, < >. Наприклад: = Если (Cl>Dl*B5; «УРА!»; «НА ЖАЛЬ...». Вираз означає, якщо в клітині СІ число більше, ніж добуток Dl*B5, то в нашій клітині відобразиться текст УРА!, інакше в клітині буде текст НА ЖАЛЬ...;

> команда И дає можливість задати кілька умов, які можна використовувати в команді ЕСЛИ. Всі умови в команді И мають бути виконані, тільки тоді функція набуває значення ИСТИНА. Якщо хоч одна умова не виконана, то її значення ЛОЖЬ. Формат команди: = И (логічна умова 1; логічна умова 2). Всього логічних умов може бути до 30. Так, разом можуть виконуватися ЕСЛИ і И. =Если (И (Е1>1; С2 = «УРА!»); «Угадав»; «Не вгадав») — якщо в клітині Е1 є число >1, а в С2 слово «УРА!», то в нашій клітині ви­світиться «Угадав» (ИСТИНА); якщо ж будь-яка з логічних умов не виконана (ЛОЖЬ), в нашій клітині одержимо «Не вгадав»;

> команда ИЛИ також може задати кілька умов, але іншим способом. Якщо хоча б одна з умов виконується, то функція наби­рає значення ИСТИНА; коли всі значення умови не виконані, одержимо значення ЛОЖЬ. Формат команди: =ИЛИ (логічна умова 1, логічна умова 2), як і формат И, можна використовувати з ЕСЛИ. Наприклад: = ЕСЛИ (ИЛИ В1>=1; D2=«TAK») «ПРЕМІЯ»; «НЕМАЄ ПРЕМІЇ») — якщо в клітині Ві коефіцієнт >= 1 або в клітині є текст «ТАК», у нашій клітині висвітиться «ПРЕМІЯ», якщо жодну з умов не виконано, в клітині висвітиться «ПРЕМІЇ НЕМАЄ».

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

# ДЕЛ/0! — означає ділення на нуль. Як правило, за дільник взято пусту клітину;

# ИМЯ? — неправильно набрано ім'я клітини у формулі. На-приклад, замість латинських літер використано українські;

# ЗНАЧ! — замість числа у формулі використано текст;

# ССЫЛКА! — клітинку, на яку робилося посилання, видалено командою Правка/Удалить, коли відбулося зміщення клітинок по рядку і стовпчику;

#ЧИСЛО! — спроба добути квадратний корінь з від'ємного чис­ла, результат обчислень надто великий або малий, щоб його мож­на відобразити в Excel.

Практичні завдання

Моделювання облікових і економічних табличних розрахунків за допомогою Microsoft Excel 2003:

1. Відкрити створену книгу.

2. Обробити таблицю иа аркуші Структура:

>- за допомогою кнопки Автосумма визначити загальну площу; >- провести розрахунок урожайності по культурах (В3/площа*10); >- розрахувати структуру по культурах (індивідуальну площу/сумарну площу), встановити формат числа — Процентный;

3. Обробити таблицю на аркуші Прибуток:

> визначити прибуток ((к-ть реалізованої продукції*ціну/1000 (без ПДВ)) - за­гальні затрати);

> застосувати до стовпчика Прибуток (Збиток) умовне форматування (збиток виділити червоним кольором, напівжирним);

4. Створити на Листе З таблицю Рентабельність фермерського господар­
ства Поліське, 2005 p.:

> ввести заголовок таблиці;

> за допомогою функції перетягування перенести колонки № пп, культу­ра, загальні витрати — всього, прибуток (збиток);

> додати колонку Рентабельність, провести розрахунок (прибуток (зби­ток/загальні витрати — всього), встановити формат числа — Процентный;

> застосувати до стовпчика Рентабельність умовне форматування (збит­ковість виділити червоним кольором, напівжирним);

> таблицю відформатувати за правилами оформлення попередніх таб­лиць.

5. Перейменувати Лист З на Рентабельність.

6. Зберегти зміни, внесені в електронну таблицю.

7. Закрити програму Excel.

Лекція № 9

Тема: Поняття бази даних. Визначення файлу даних: запис, структура запису, поле запису та його реквізити. Типи даних. СУБД Access. Створення та корегування таблиць

Мета:

Сформувати поняття бази даних, дати означення запису, поля та його реквізитів. Охарактеризувати типи даних СУБД Access. Розглянути методику створення та корегування таблиць

Розвивати логічне мислення, вміння аналізувати та робити висновки.

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

1. Поняття бази даних та СУБД. Загальна характеристика субд.

2. Субд Access

3. Структура бази даних.

4. Властивості полів.

5. Типи полів.

6. Режими роботи з Access.

1. Поняття бази даних та СУД. Загальна характеристика субд.

Дані — це факти та ідеї, подані у формалізованому вигляді для оброблення за допомогою певного процесу (алгоритму) або для передачі.

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

База даних — сукупність взаємозв'язаних даних (файлів), призначених для спільного застосування. Поняття бази даних пов'язане в основному з комп'ютерною технікою. Фактично база даних — це система збереження та оброблення реляційних таблиць.

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

За допомогою СУБД забезпечується вирішення таких основних завдань:

• створення бази даних;

• занесення, коректування і вилучення даних;

• упорядкування даних;

• вибір сукупності даних, що відповідають заданим критеріям;

• оформлення вихідних даних і т. д.

Сукупність СУБД і бази даних — це банк даних. До достоїнств підходу, який ґрунтується на концепції банку даних, належать:

• задоволення інформаційних потреб різних типів користувачів;

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

• санкціонований доступ до даних;

• адаптація інформаційної моделі до змін предметної області;

• видача інформації у формі, встановленій користувачем;

• одноразове введення даних і багаторазове їх використання;

• можливість виключення надмірності даних, що зберігаються, і т. д.

Недоліком цього підходу є велика ємність необхідної пам'яті ПК.

Найпопулярнішими серед настільних систем, що функціонують у середовищі MS DOS, є СУБД, які підтримують реляційну модель даних. З них найпоширенішими є такі:

• dBase компанії Ashton-Tate, поглиненої згодом компанією Borland;

• Paradox компанії Borland;

• Fox Pro компанії Fox Software;

• Clipper та ін.

Кожна з цих СУБД має кілька версій. Найвідомішими серед них є СУБД:

• Access (фірма Microsoft);

• Paradox 7 for Windows 95 (фірма Borland); «Approach for Windows 95 (фірма Lotus).

Названі вище СУБД мають такі загальні властивості:

• графічний багатовіконний інтерфейс, що дає змогу користувачеві в діалоговому режимі створювати таблиці, форми, запити, звіти, макроси;

• спеціальні засоби, які автоматизують роботу під час створення і ведення бази даних (до них належать численні Майстри та Конструктори в СУБД Access, Асистенти в Approach або Експерти в Paradox);

• здатність працювати в локальному режимі або в режимі клієнта на робочій станції в комп'ютерній мережі;

• можливість використовувати об'єктну технологію для впровадження в базу даних об'єктів різної природи (текстів, ЕТ, діаграм, рисунків тощо);

• власна мова програмування, що розширює можливості використання даних, та ін.

Кожна база даних містить інформацію про певний клас об'єктів. Сукупність таких об'єктів називається предметною областю бази даних (наприклад, сукупність даних про працівників фірми, зведення про вироби на складі, про транспортні засоби автопідріємства тощо).

Інформація про об'єкти предметної області, подана в різних документах, утворює модель предметної області (картки обліку кадрів, картки складського обліку, накладні, журнали обліку транспортних засобів та ін.).

Опис цих документів (назви документів; колонки, в яких вони знаходяться; зв'язки між ними) називається схемою відношень.

Дата добавления: 2014-01-06