Реферат: Віти І науки україни відкритий міжнародний університет                  розвитку людини «Україна» Р. М. Літнарович практика по excel     Рівне,2008

МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ ВІДКРИТИЙ МІЖНАРОДНИЙ УНІВЕРСИТЕТ                  РОЗВИТКУ ЛЮДИНИ «Україна»


Р.М.ЛІТНАРОВИЧ


ПРАКТИКА ПО EXCEL





   


Рівне,2008


УДК 378.14

Літнарович Р.М. Практика по Excel. ВМУРоЛ, Рівне, 2008,-116с.


Рецензенти : В.О.Боровий,доктор технічних наук,професор

В.Г.Бурачек,доктор технічних наук, професор

Є.С.Парняков,доктор технічних наук,професор


Відповідальний за випуск: Й.В.Джунь,доктор фізико- математичних наук,професор


© Літнарович Р.М.


ЗМІСТ

Передмова ……………………………………………4

Завдання 1.Робота з електронними таблицями типу

Super Calc………………………………………………5

Завдання 2. Основні методи роботи з Excel…………15

Завдання 3. Основні методи роботи з Excel

(продовження)…………………………………………17

Завдання 4.Використання формул в Excel…………..20

Завдання 5. Використання функцій в Excel…………26

Завдання 6. Використання статистичних та

фінансових функцій в Excel//………………………...30

Завдання 7. Використання діаграм в Excel………….36

Завдання 8. Організація баз даних в Excel………….38

Завдання 9. Сортування і вибірка з баз даних в Excel40

Завдання 10. Робота з великими списками.

Підсумки і зведені таблиці вExcel …………………..44

Завдання 11. Зв'язування робочих книг і обмін

даними між програмами……………………………..48

Завдання 12. Робота з картами і шаблонами………..53

Завдання 13. Макроси. Використання Макросів……55

Звіт з практики по Excel………………………………59

Література…………………………………………..…88

Додатки………………………………………………..88


ПЕРЕДМОВА

    Все більше слово “керівник” в наш час замінює слово “менеджер”. Це пов’язано зокрема з теперішнім світовим досвідом управління, який широко використовує сучасну інформаційну комп’ютерну технологію,що значно підвищує професійність менеджера.
    Щоб ефективно організувати робочий час спеціаліста в наш час, широкого використання набули персональні комп’ютери. Через швидкий ріст кількості комп’ютерів в нашій країні ними можуть користуватися не тільки професіонали в області обчислювальної техніки, а й менеджери, котрим необхідно мати оперативний доступ до інформації, уміти оброблювати і аналізувати цю інформацію.
    Широке використання персонального комп’ютера приводить до підвищення продуктивності праці, заохочення організації виробництва.Тому серед потреб, які висувають в практичній роботі, все частіше називаються вміння і навички використання сучасної комп’ютерної технології.
    Перехід країни до ринкових відносин завжди спонукає вчених і практиків до найретельнішого вивчення досвіду управління фірмами і підприємствами та до нових пошуків шляхів і засобів, які забезпечили б стабільно високий рівень добробуту нашої країни.
    Як показує досвід економічно розвинених країн, успіх якого вони досягли, є найкращим показником системи управління виробництвом та обміном матеріальних благ.
    Під впливом еволюції ринкової системи господарювання менеджмент став її невід’ємним складовим елементом. Слід також зазначити, що розпорядником соціального прошарку економічного життя суспільства на Заході є менеджери.
    Саме ознайомлююча практика проводиться з метою поглибленого вивчення основ обчислювальної техніки і програмування, поглибленого ознайомлення з майбутньою професійною діяльністю, адже специфіка роботи економіста полягає в постійному використанні формул і різних таблиць. При чому одні і ті ж самі обрахунки робляться по декілька разів. Для розширення роботи склали спеціальну програму, в якій всі підрахунки робить сама програма.

Загальне знайомство з електронними таблицями

Завдання 1. Робота з електронними таблицями типу SuperCalc

 

Обробка даних на ПЕОМ часто проводиться у вигляді таблиць. Особливо цінною така обробка є для числових величин. Надзвичайно вимогливим користувачам – бізнесменам, фінансистам і комерсантам сподобалася програма двох шістнадцятирічних школярів з США у вигляді електронної таблиці – VISICALC. Успіх цієї програми привів до створення цілого сімейства програм для електронної обробки даних у таблицях – табличних процесорів. Особливо популярними була програма SuperCalc фірми Computer Associates (США), варіанти її: АБАК,ТАГРА, ВАРИТАБ. Фірма Lotus аналогічним продуктом Lotus 1-2-3, захопивши лідерство, 70% електронних таблиць світового ринку постачає і на сьогодні для ПЕОМ, що працюють в DOS.

Прогрес у інформаційних технологіях та мікрокомп’ютерній техніці трансформував програмні продукти типу табличні процесори у програми – електронні таблиці. Робота у Windows значно покращила інтерфейс таблиць, збільшивши їх можливості тощо. Детальне вивчення цих програм буде проведено пізніше, а перше знайомство з електронними таблицями проведемо на прикладі SuperCalc.

Стандартним методом запустіть програму SC4.COM. На екрані отримуємо заставку і можливість відразу ознайомитись з допомогою, натиснувши F1. Екрани допомоги, а їх більше 600, у будь – який момент роботи можуть надати контексно –залежну допомогу. Перелік тем усіх екранів допомоги можна викликати клавішами F1 і потім – F3.



Рис 1.1.

Робочий екран ( рис1.1) складається з дисплейного вікна і діалогової панелі. Дисплейне вікно, це частина електронної таблиці, що видна на екрані. Стандартно працюють з 20 рядками і 7 колонками (при їх ширині 9 символів). Сама ж велика електронна таблиця має 9999 рядків при 255 колонках. Колонки позначають латинськими літерами (A,B, ... , AA, AB, AC, ...,IU), а рядки цифрами від 1 до 9999. Табличний курсор (на рис.1.1 у положенні А1) переміщують клавішами управління. Положення курсору фіксується біжучими координатами, як перетин відповідної колонки і рядка – B7, AA42 чи SI104. Блок комірок – це прямокутна область, яку позначають координатами лівої верхньої і правої нижньої комірки. Наприклад: А1:С12, S6:W23, А3.D20. Розділовий знак для блоку ( двокрапки ‘:’ чи крапка ‘.’) вирішує користувач довільно. Блоку комірок можна присвоїти ім’я і потім викликати його по імені.

^ Діалогова панель – це чотири нижні рядки екрану: рядок стану (Status line), рядок запиту (Prompt line), рядок введення (Entry line), рядок допомоги (Help line). Користуючись допомогою, літературою і практичною роботою вивчіть і встановіть призначення рядків діалогової панелі. Переміщуючи курсор по електронній таблиці, спостерігайте за змінами у рядках діалогової панелі. У рядку допомоги розміщено і індикатор режиму. Використовуються такі режими роботи: READY, MENU, ENTRY, POINT, EDIT, FILE, NAME. Дайте письмову відповідь на призначення перерахованих режимів і як в них перейти.

В клітини таблиці можна вводити текст, повторювальний текст і формули (рядок стану міститиме Text, або Rtxt, або Form). Ознакою тексту є наявність символу (“) , повторювального тексту символ (‘). Під формульними даними розуміють календарні дати, адреси комірок, математичні формули і числа.

Важливо: 1. Щоб проводити редагування у клітинах таблиці потрібно увійти в режим Edit клавішею F2.

2. Усі основні операції в SuperCalc виконуються через слеш-команди. Меню роботи викликається вводом правого слеша (/). Пам’ятайте, що і вихід з програми здійснюється вводом: /, Quit, Yes.

3. Щоб очистити вміст клітини потрібно набрати /Blank і Enter.

Проведіть ознайомлення з командами програми. Для цього уведіть слеш – команду (/) . Отримуємо меню з 21 командою (опцією), які мають підопції. Ознайомтесь з призначенням команд, використовуючи F1. Перепишіть для звіту призначення команд. Введіть ще раз слеш ( // ). Ознайомтесь з чотирма дубль – слеш командами.

Основні прийоми роботи з електронними таблицями освоїмо “розробкою”

Рис 1.2

таблиці на видачу заробітної плати, як показано на рис 1.2. Потрібно сформувати таблицю з не менше як 10 працівниками, врахувати податок , підрахувати “до виплати”, зробити підсумки (Разом), провести форматування таблиці і зберегти її на дискові.

Допомога. 1. Текст, що вводиться у клітину, у випадку незаповнених наступних, виводиться на екран повністю, хоч розміщений у одній клітині (як заголовок таблиці, який розміщено у клітинці С2, рис.1.2).

2. Ввід у клітину А3 символів (‘_ ) переводить таблицю у режим повторювального тексту. На екрані маємо ланцюжок тире, які відділяють заголовок таблиці від значень. Перервати цей ланцюжок можна вводом у , наприклад, клітину G3 символу (“) – ознака тексту.

3. Щоб таблиця набула зручного для читання вигляду, слід провести форматування таблиці. Для цього використовують слеш – команду форматування наступним чином. Набираємо / , вибираємо Format, потім Column, далі переводимо курсор у колонку А, вибираємо Width і вводимо ширину для колонки, наприклад 11. Форматування колонки А на ширину 11 символів проводиться, таким чином, послідовністю вибраних опцій (команд): /Format, Column, А, Width,11 (коми проставляються автоматично). Процедуру форматування для інших ділянок таблиці проведіть самостійно.

4. В колонку “Податок” у клітину Е6 величину податку слід увести формулою наступним чином: D6*0.15. В колонці “До виплати” у клітину F6 вводимо D6-E6.

5. Якщо ви заповнили даними блок таблиці А6:D15, то тепер формулу з комірки Е6 копіюємо у блок Е7:Е15 набором команд: /Copy,E6,E7:E15 і Enter. Аналогічно поступаємо з формулою комірки F6: /Copy,F6,F7:F15.

6. Суму до виплати рядка “Разом” у клітинці F17 визначимо уведенням формули SUM(F6:F15).

7. Таблицю з введеними формулами можна проглянути командами /Global, Formula. Повторивши вибір повертаємося у таблицю з підрахунками.

8. Щоб зберегти результати у файлі потрібно вибрати: /Save і на запит “Введите имя” ввести Z:\ my_first.cal (або іншу назву і шлях).

Основне призначення електронних таблиць (ЕТ) – проведення розрахунків. Тому ЕТ оперують формулами у яких використовують дані наступних типів: числові, календарні або дата/час, літерні, помилка та недійсні. Формула, це сукупність операторів і операндів, організованих у вигляді алгебраїчного виразу. Оператори- це математичні символи які формують відповідну операцію. Операнди – це ті об’єкти над якими виконується операція. ЕТ використовують стандартні арифметичні оператори і оператори відношень. Крім того у виразах використовуються функції: арифметичні, тригонометричні, логічні, календарні, фінансові, індексні, статистичні, статистичні управління даними, літерні, спеціального призначення.

А) Ознайомлення з основними функціями проведемо наступним чином.

^ 1 .У клітини з А1 по H1 вводимо довільні числа.

2. Починаючи з клітини А2 вводимо статистичні функції: SUM(A1.H1); у клітину B2 – COUNT(A1:H1); у C2 – MAX(A1.H1); D2 – MIN(A1:H1); E2 – AVG(A1:H1); F2 – VAR(A1:H1); G2 – STD(A1:H1); H2 – RAND.

3. У третій рядок введемо арифметичні функції у такому порядку: ABS(A1); EXP(3); INT(PI); LN(B1); LOG(C1); SQRT(144); MOD(144,7); ROUND(PI,0).

4. У четвертий рядок введемо тригонометричні функції: PI; SIN(D1*PI/180); COS(E1*PI/180); TAN(G1*PI/180); ACOS(H1); ASIN(A1+B1); ATAN(F1); ATAN(B1/A1).

Зауваження. 1. Якщо деякі числові значення введені вами не відповідають як аргументи деяким функціям (наприклад, корінь квадратний SQRT приходиться брати з від’ємного числа), то проведіть заміни.

2. Користуючись допомогою (розмістіть курсор у клітину з формулою і виберіть F1) виясніть і опишіть призначення усіх функцій.

3. Збережіть для звіту напрацьовані вами результати на диску.

 

Б) Роботу з календарними функціями розглянемо прикладом, що показаний на рис 1.3. Ознайомившись з допомогою по календарних функціях, розраховуєте за власними даними прожиті дні, години і т.д., керуючись наведеним прикладом. Зверніть увагу на формат вводу дати, перехід до юліанського календаря (функції типу JDATE) , використання системної дати (TODAY) . Можна провести

Рис 1.3

вдосконалення розрахунків, якщо врахувати високосні роки, системний час тощо.

Операції з даними в ЕТ виконуються спеціальною командою //Data. Ця команда має розвинуту систему опцій, які дозволяють:

оголошувати базою даних (БД) групу або блок клітинок таблиці;

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

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

виконувати множинну лінійну регресію;

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

перемножувати і інвертувати матриці, розв’язувати системи лінійних рівнянь;

“розбирати” літерний рядок на визначені поля;

готувати таблиці значень для формул однієї чи двох змінних.

Опції для роботи з БД можна розділити на дві групи: для роботи з БД (Input, Criterion, Output, Find, Extract, Unique, Select, Remain, Delete, Paste), і опції аналізу даних (Analisis, Block, Matrix, Table). Користуючись допомогою випишіть призначення усіх опцій для роботи з БД.

Табличний процесор дозволяє командою //Graphics (або /Global, Graphics в залежності від версіі SC) будувати вісім типів діаграм, а комбінуючи різні типи отримати більше 100 варіантів. Використовуючи довільну залежність даних побудуйте 2 – 4 різних типи діаграм і збережіть на дискові. Опишіть опції, які використовуються при побудові діаграм.

^ Завдання 2 Основні методи роботи з Excel

1. Запустіть програму Excel. Ознайомтеся з екраном, що перед Вами. Excel відкрив робочу книгу під назвою “Книга 1” (назва файла в Excel) та перший робочий лист “Лист 1” (ярлики робочих листів унизу). Робочий лист містить комірки з назвами стовпців (A,B,C,…) та строчок (1,2,3, …). Кожна комірка є елементом робочого листа (таблиці) з назвою A1 – перша, B3, C13, AH40,… , IY597, і т.д. В комірці можна розміщувати числа, дати, текст, формули,…,будь – які інші дані. Розміри комірки можна змінювати. У верхній частині розміщено строчку “Головного меню” з опціями: Файл Правка Вид і т. д. Наступний рядок (рядки) – “Інструментальні панелі”, останній, над таблицею, “Рядок формул”. В цьому рядку вказуються координати активної комірки, редагується вміст комірки чи формули тощо.

Виберіть в головному меню опцію довідки “?”. В підменю вибираєте “Вызов справки  Содержание”. Ознайомтеся з інформацією довідки до пункту “Печать”. Корисно уточнювати інформацію через “Предметный указатель”.

Клацніть мишкою на якійсь з комірок. Вона стає активною. В рядку формул видно її назву. Можна уводити дані. Створимо таблицю з змістом пунктів головного меню та підменю. Виділіть комірку E2, уведіть “Головне меню”. Починаючи з комірки A4 вводите назви опцій з головного меню. Розкриваючи пункти меню, під їх назвами заносимо опції підменю. Отримуємо таблицю з пунктів меню та підменю, що характеризує можливості Excel.

Таблицю оформляємо можливими засобами програми. Міняємо ширину стовпчиків (мишкою або ^ Формат Столбец(Автоформат))  Ширина ). Форматування проводиться для активної комірки, або групи виділених , або виділеного стовпчика (рядка). Змінюючи шрифт, гарнітуру шрифту, фон, рамки, підкреслювання тощо оформіть таблицю. Використайте меню Формат  Формат ячейки , піктограми інструментального меню Заливка , Внешние границы , Шрифт, Размер шрифта тощо.

Для перегляду великих таблиць зручно заголовки (Стовпчики) таблиць при скролінгу залишати на місці, розділювати таблицю тощо. Виділіть строчку нижче заголовків (клацнувши мишкою на номері строчки) або виділіть стовпчик правіше потрібного і використайте опції Окно  Закрепить области , попрацюйте з опціями Окно  Разделить. Лінії розділу можна мишкою переміщувати або зовсім забрати з поля. Мишкою їх можна знову вивести в потрібне місце на робоче поле.

Робочий лист перейменуємо. Клацнувши мишкою два рази на ярлику ^ Лист1 у діалоговому вікні, що відкривається замінюємо назву на ГолМеню, наприклад. Повертаємося на робочий лист. Клацнувши мишкою по ярлику Лист 2 відкриваємо новий чистий лист. Новий лист при необхідності можна і добавити опціями меню Вставка  Лист.

Лист 2 перейменуйте на Заставка. Створіть малюнок – заставку своїх майбутніх робіт. Для цього зробіть ширину стовпчиків рівною їх висоті (квадрати) використовуючи опції Формат  Столбец  Ширина . Якщо змінюємо для всіх стовпчиків, їх виділяємо через Правка  Выделить все або клацнувши мишкою на лівий верхній квадрат листа. Виділіть поле для заставки, використайте Заливка для фону, утримуючи натиснутою клавішу Ctrl виділіть мишкою несуміжні комірки, сформуйте, наприклад, великі літери назви навчального закладу, зафарбуйте їх, запустіть Рисование (піктограма), Цвет шрифта, Цвет линий, створіть художній шедевр.

При роботі можливі втрати результатів. Корисно проводити автозбереження результатів. Для цього через опцію меню ^ Сервис Автосохранение встановити параметри автозбереження. Якщо виникають питання, то використайте довідку і у Предметный указатель уведіть “автосохранение” . Результати роботи потрібно зберегти у робочій директорії (піктограма Сохранить ), а краще, поки книга містить назву Книга 1, зберегти її під, наприклад, назвою MyWork, через меню Файл Сохранить как… Ви маєте можливість вказати параметри збереження і зберегти на власній дискеті

^ Завдання 3 Основні методи роботи з Excel (продовження)

Відкрийте файл з результатами попередньої роботи. Запускаєте Windows , потім Excel і через меню Файл Открыть відкриваєте MyWork.

Використаємо Лист 3 для створення таблиці Список співробітників, перейменувавши новий лист на Список.

Ознайомтеся з можливостями Excel в автозаміні, автоведенні (меню Сервис  Автозамена, Сервис  Параметры), автоформатуванні (Формат Автоформат) тощо. У довідці перечитайте Форматирование листов.

Давши назву таблиці, формуємо назви стовпчиків таблиці : № п/п, Прізвище, Iм’я, По-батькові, Дата народження, Стать, Зарплата, Посада, Дата зарахування, Діти, Сім. стан, Стаж роботи,…всього 15 довільних, але наведені бажані. Число строчок не менше 25!.

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

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

Виділіть мишкою стовпчик прізвищ і клацніть на піктограмі ^ Сортировка по возростанию. Дані будуть відсортовані у алфавітному порядку. Зверніть увагу!!! на те, що тільки прізвища розміщені у алфавітному порядку і тепер “загублені” інші дані до прізвищ. Операцію можна відмінити з допомогою піктограми Отменить. Щоб відсортувати взаємопов'язані дані слід використати меню Данные Сортировка . Перегляньте довідку по вказівнику Сортировка.

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

Добавимо у таблицю строчку і стовпчик ( з назвою К-сть дітей). Виділяємо рядок (13) і в меню через опцію Вставка Строки добавляємо строчку, а стовпчик через опцію Вставка Столбца. Вносимо дані. Поновлюємо нумерацію, яка порушилась.

6. Вид таблиці продовжуємо покращувати. Виділяючи стовпчики і користуючись кнопками ^ По левому краю, По центру, По правому краю вирівнюємо дані. Виділяємо строчку заголовків таблиці. Натискуємо клавішу Полужирный. Через меню Формат Ячейки добавляємо Перенос словами, Выравнивание по вертикали тощо покращуємо вигляд заголовків.

Для завершеної таблиці зробимо копію. Виділяємо ( ^ Правка Выделить все) таблицю. Через Правка Копировать копіюємо виділену діляну у буфер ПЕОМ (або клацнувши правою кнопкою миші вибираємо Копировать). Клацнувши на ярлик Лист 4, відкриваємо його і вставляємо вміст буфера Правка Вставить ( чи з допомогою правої клавіші мишки). Перейменовуємо Лист 4 на Копія. В цьому листі тренуємося з копіюванням стовпчиків, строчок, комірок. Пробуємо видалити строчку, стовпчик, групу комірок. Поновіть видалене з допомогою даних листа Список.

При копіюванні, переміщенні даних тощо можна втратити оформлення комірки(рамки, фон, колір і т.д.). Ви можете поширити “хороший” стиль оформлення (без даних комірки) на потрібні вам області. Для цього виділяєте комірку – зразок, клацаєте по кнопці Формат по образцу і виділяєте ті комірки, куди бажаєте перенести формат. Якщо вас результат роботи не задовольняє, відмінити завжди можна кнопкою Отменить.

Лист Заставка переміщуємо на першу позицію захопивши його мишкою.

Завершуємо роботу і зберігаємо результати у файлі MyWork.



^ Завдання 4 Використання формул в Excel

1.Відкриваємо файл MyWork, створюємо новий лист Прибуток.

2. Формули в Excel містять значення (числа, дати, час, текст - це все може бути значенням). Коли додаємо 5+2, то 5 і 2 – значення. Оператори – це вказівка на те, що потрібно робити із значеннями. До операторів відносять умовні позначення додавання +, віднімання -, множення , ділення /, піднесення в степінь ^, проценти %. Це арифметичні оператори. Значення називають також операндами. Вони можуть бути константами, постійними значеннями. Частіше в Excel у формулах використовують посилання на комірки, подібні С1 або N12. У формулах Excel обов'язковим є її початок із знака рівності “=”. Щоб помістити результат в активну комірку, комбінуючи дужками, записуємо формулу довільної складності у вигляді: =((2+3)*5). Дужки є важливим елементом в формулах, бо 5+2*3 дає 11, а (5+2)*3 дасть 21. Наявність усіх пар дужок в формулі можна перевірити, провівши по виділеній формулі у строчці формул курсором. Відсутність пари буде фіксуватись виділенням непарної дужки.

Використовують також оператори порівняння: рівність=, більше>, менше<, більше або рівне>=, менше або рівне <=, не рівно <>. Результат порівнянь є логічне значення ИСТИНА або ЛОЖЬ.

Важливими для роботи з таблицями є оператори посилань. Дві крапки (:) використовують для визначення діапазону комірок – оператор діапазону. Якщо А3 і H67 – посилання на окремі комірки, то запис D3:F6 означає діапазон усіх комірок від D3 до F6. Крапка з комою (;) це оператор об’єднання. Запис А3;D6 означає, що використовується значення з комірки А3 і комірки D6, а запис A1:D4;F1:H4 означає діапазон A1:D4 і діапазон F1:H4.

Проілюструємо описане на прикладах. В листі Прибуток формуємо таблицю Магазин “ Смачна кава”

Числові дані таблиці форматуємо у форматі фінансовий. Прибуток од. визначаємо у першій комірці так: виділивши комірку записуємо =, клацнувши по Ціна од. автоматично бачимо увід адреси комірки, далі знак – і адреса комірки Собівартість од. Залишається натиснути Enter. В комірці результат 326,00 для прибутку одиниці товару з Суматри. В інші комірки стовпчика формула просто копіюється. Загальний прибуток отримуємо подібним чином: виділивши комірку вносимо знак = , потім клацаємо мишкою по комірці К-сть і уводиться адреса, далі знак множення * і адреса комірки Прибуток од. . В комірки, що залишились формула копіюється. Поклацайте мишкою по двох останніх стовпчиках. Спостерігайте за строчкою формул. Там видно формули, що були використані.

Сформуємо наступну таблицю в тому ж листі.

Розрахунки з клієнтами

Клієнт

Попередній баланс

Останній платіж

Баланс

Виписувати рахунок?

Сидорчук

300000

280000







Бобров

265000

265000







Кисіль

625000

536000







Бідний

422000

421000







Соловей

514000

500000






















Стовпчик Баланс отримуємо як різницю між першим і другим стовпчиками, подібно до попереднього. Результат у Виписувати рахунок? з допомогою логічних операторів: формула = адреса Баланс >= 20000 . Результат роботи подібний до наведеного нижче.

Клієнт

Попередній баланс

Останній

Платіж

Баланс

Виписувати рахунок?

Сидорчук

300000

280000

20000

ИСТИНА

Бобров

265000

265000

0

ЛОЖЬ

Кисіль

625000

536000

89000

ИСТИНА

Бідний

422000

421000

1000

ЛОЖЬ

Соловей

514000

500000

14000

ЛОЖЬ

 

4. При переміщенні формули, посилання на адресу комірки автоматично змінюються. В багатьох випадках це зручно. Таке посилання на адресу називають відносним посиланням. В інших випадках потрібно, щоб адреса комірки не змінювалась. В цьому випадку використовують абсолютне посилання і його формують з допомогою символу $ . Вже адреса $A$7 є абсолютною. А інколи зручно використати і змішане посилання (стовпчик чи рядок не змінюються) у вигляді $A1 або A$1. Розглянемо приклад такого використання посилань.

Будемо працювати з таблицею виду:




A

B

C

D

E

F

G

1

НПЗ можливих прибутків від продажу пива "Світле" і "Темне"




2






















3




% ставка

3,50%













4




Рік1

Рік2

Рік3

Рік4

Рік5

НПЗ

5

"Світле"

-800

150

180

190

210

-128,18

6

"Темне"

-1200

270

290

320

360

-63,84

7






















Відкрийте новий лист, перейменуйте його на Прогноз і уведіть таблицю (крім значень в комірках G5;G6. В С3 знаходиться процентна ставка по Т-векселю. Вона використовується як безризикова процентна ставка в розрахунках чистого приведеного значення (ЧПЗ). ЧПЗ розраховують за формулою:

ЧПЗ=.
Підрахунок в комірці G5 проведемо не за приведеною формулою, а використаємо стандартну функцію ЧПЗ таким чином: уведемо формулу =ЧПС(С3;B5:F5). Отримаємо –128,18р. Якщо формулу перенести в комірку G6, то процентну ставку формула шукатиме в комірці С4 (а тут заголовок Рік2). Результатом підрахунків буде помилка (#ЗНАЧ!). Процентна ставка має мати абсолютне посилання і вірна формула буде =ЧПС($C$3;B5:F5). Після копіювання в комірку G6 формула матиме вигляд =ЧПС($C$3;B6:F6), а підраховане значення -63,84р. Зручно замінювати посилання на абсолютне чи змішане таким чином. В рядку формул встановіть курсор на адресу комірки і натискайте клавішу F4. Автоматично добавляється знак $.

7. Відкриваємо новий лист і копіюємо в нього лист Прогноз. Перейменуємо його на Прогноз1. Дані стовпчика НПЗ можна видалити. Формула виду =D4-H4 дає нам певну інформацію, але було б краще =Реалізація-Витрати. Excel дозволяє адресам комірок чи цілим діапазонам присвоювати імена і використовувати їх у формулах. Це більш інформативне і більш зрозумілими стають записи формул. Виділивши комірку С3 за допомогою меню Вставка Имя Присвоить (або з допомогою “гарячих” клавіш Ctrl+F3) даємо ім’я комірці Т_вексель. Тепер формула підрахунків буде =НПЗ(Т_вексель;B5:F5). Її копіюємо в наступну комірку. Слід пам’ятати, що ім’я формує абсолютне посилання, а використані опції можна використати для автоматичного імені діапазону по назві стовпчика, для видалення імені тощо. Прочитайте додатково у довідці інформацію по “абсолютная”, “ссылки” тощо.

8. Зручним для підрахунків є використання формули масиву. Формула масиву використовує відразу діапазон значень і генерує стільки результатів, скільки значень в діапазоні. Відкриємо лист Копія. В таблиці Магазин “Запашна кава” очистимо значення в колонках Прибуток і Загальний прибуток. Поступаємо таким чином: мишкою виділяємо комірки стовпчика прибуток і уводимо знак =, мишкою виділяємо діапазон Ціна і ставимо знак -, мишкою виділяємо діапазон стовпчика Собівартість і… а тепер важливо – натискаємо одночасно клавіші Shift + Ctrl + Enter. Формула буде взята у фігурні дужки { } , що є ознакою роботи з масивом. Явно уводити фігурні дужки не можна. Отримуємо по усьому діапазону відразу результати. Подібно (тільки через множення, знак * , знаходимо результати у стовпчику Загальний прибуток.

Прочитайте додаткову інформацію у довідці через вибір “массив” тощо.

Формула масиву виду {=E5:E9*F5:F9} означає, що проводиться множення Е5 на F5, E6 на F6 і т.д., а результат вноситься у виділений діапазон. Якщо врахувати, що Excel містить більше 16000 строчок і потрібно проводити підрахунки з великими діапазонами значень, то використання формул масивів єдино можливе.

^ Завдання 5 Використання функцій в Excel

1.І формули і функції в Excel розпочинаються із знака рівності “=”. Функція вказує програмі, що потрібно виконувати. Вона подібна до операторів (+,-, * тощо), але вона може включати в себе багато операторів. Роботу функції можна продублювати формулами, але з готовими функціями працювати краще - швидше і надійніше. Кожна функція оперує з числами, чи датами, чи текстом. Для функцій значення з якими вона працює називають аргументами. Різні функції використовують різні аргументи і різну їх кількість. Перелік функцій, їх використання, синтаксис і т. подібне перегляньте у довіднику через вказівники “функции”, “обзор” та інші.

2. Найчастіше використовується функція автосума (СУММ). Для цієї функції в інструментальному меню створено окрему піктограму (кнопку) із значком суми .Усі інші функції викликаються і опрацьовуються з допомогою клавіші ^ Вставка фукции (розміщена поруч з Автосумма). Excel призначений для проведення всіляких підрахунків і він це робить майстерно, рахуючи швидко, точно і все. При проведені біжучих підрахунків в Excel поступають слідуючим чином. Відкрийте лист Копія. Перемістіть мишку за межи листа униз – направо, у область автопідрахунків і клацніть правою кнопкою миші (на строчку статусу). Отримаєте діалогову панель з можливістю вибору із таких опцій:

^ Нет – підрахунки не проводяться,

Среднее – функція СРЗНАЧ, виводить середнє значення з вибраного діапазону,

Количество значений - функція СЧЕТЗ, виводить кількість не порожніх клітин з вибраного діапазону,

^ Количество чисел - функція СЧЕТ, виводить кількість не порожніх клітин з числами з вибраного діапазону,

Максимум - функція МАКС, виводить максимальне значення числа з вибраного діапазону,

^ Минимум - функція МИН, виводить мінімальне значення числа з вибраного діапазону,

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

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

3. Відкрийте лист Прибуток. Виділіть вільну комірку під стовпчиком Прибуток од. Клацніть в панелі на кнопку Автосумма. Excel виділить самостійно стовпчик угору або область наліво контуром “біжуча змійка”. Якщо виділення Вас влаштовує, ще раз клацніть на Автосумма. Зверніть увагу на строчку формул. Там зафіксовано вірний синтаксис для команди СУММ, а саме =СУММ(F5:F9) (чи щось подібне). Можна було б самостійно набрати команду, але Excel спрощує Вам завдання. Якщо потрібно сумувати не суміжні комірки, то тримайте натиснутою клавішу Ctrl.

4. Зробіть підсумок у стовпчику Загальний прибуток. Уведіть текстове пояснення до результату, типу Разом чи Всього. У різні області листа пробуйте увести підрахунки довільних чисел з таблиці, використайте Ctrl і спостерігайте за синтаксисом команди у рядку формул. Після тренувань очистіть отримані таким шляхом результати. Основна команда СУММ має бути добре Вами засвоєна.

5. Визначимо середнє значення собівартості. Для цього виділяємо комірку під стовпчиком Собівартість і клацаємо по клавіші ^ Вставка функции. Розпочинає роботу Мастер функций. Вже записано знак =. Тепер потрібно вибрати функцію. В Категории вибираємо Статистические, а у полі Функции виділяємо СРЗНАЧ. Зверніть увагу, що Мастер функций постійно дає підказки до Ваших дій, по функціях, по аргументах і додатково можна викликати довідку. Область значень Мастер функций вибирає самостійно і якщо Вас влаштовує – натискаєте ОК чи . Якщо область значень потрібна інша, то вибираєте самостійно, подібно як у попередньому прикладі(панель діалогу Мастера функций легко змістити, щоб не закривала дані, захопивши мишкою і переміщуючи). Мастер функций крок за кроком виконує потрібні дії. Перегляньте функції у Мастер функций, якими оперує Excel, вибираючи їх у полі Категории Мастера функций або вибравши Полный алфавитный перечень.

6. Для поля Ціна підрахуємо серединне значення – МЕДИАНА. Усі операції подібні до попереднього випадку, тільки вибираємо функцію МЕДИАНА.

7. Відкриємо таблицю Розрахунки з клієнтами. Виділимо комірки, вільні біля значень ИСТИНА, ЛОЖЬ(направо). Використаємо функцію ^ ЕСЛИ. Запускаємо Мастер функций, Логические, ЕСЛИ. В строчці логическое виражение вносимо (відмічаючи мишкою) дані стовпчика Баланс і записуємо умову >=20000. Переходимо у наступне поле і уводимо Виписувати рахунок, а у наступне – Не виписувати рахунок. А тепер натискаємо Shift +Ctrl + Enter ( робота з масивом). Прогляньте результат роботи.

8. Відкрийте лист Список. Вставте після стовпчика Дата народження чистий стовпчик. Дайте йому назву Дні. Визначимо для кожного кількість прожитих днів (можна годин, хвилин, секунд). Виділяємо першу комірку і запускаємо Мастер функций. Використовуємо функцію ДНЕЙ360 у Категории Дата и время. В перше поле уводимо (мишкою) адресу Дати народження першого у списку, в друге поле уводимо функцію СЕГОДНЯ() і …ОК. Копіюємо перший результат у наступні комірки. Цю ж операцію повторіть з використанням масиву, потім з використанням комірки з певною датою для абсолютного посилання. Аналогічно підрахуйте кількість пророблених днів (год.) вашими співробітниками зі списку.

9. Ви готові до створення Відомості на нарахування заробітної плати. Створіть нову книгу і дайте їй назву, наприклад, MyFunk. Лист 1 перейменуйте на Зарплата. Оформіть список для не менше 16 співробітників (можна використати лист Список з файла (книги) MyWork. Врахуйте податки, погодинну оплату, доплати, суму до видачі, суму для одержання в банку тощо.

10. Результати роботи збережіть у робочій директорії чи дискеті.


^ Завдання 6. Використання статистичних та фінансових функцій в Excel

Сформуйте таблицю, як показано на рисунку. За 5 доларів придбаний Вами лотерейний квиток виграв 1000000 доларів. Вам їх виплатять протягом десяти

 




А

В

1

Виграш мільйона доларів

2

Період

Виплати

3

Рік 0

($5)

4

Рік 1

$50 000

5

Рік 2

$50 000

6

Рік 3

$50 000

7

Рік 4

$50 000

8

Рік 5

$50 000

9

Рік 6

$150 000

10

Рік 7

$150 000

11

Рік 8

$150 000

12

Рік 9

$150 000

13

Рік 10

$150 000

14

Всього

$1 000 000

15







16

Дисконтна ставка

7%

17

НПЗ




років, як показано в таблиці. Радість отримати аж $1000000 передчасна, бо долар сьогодні не рівний долару (гривні) через рік. Якщо дисконтна ставка 7%, то реально ми отримаємо…і це визначає фінансова функція чистого приведеного значення НПЗ. Запускаємо Мастер функций (попередньо виділивши комірку для результату В17), серед фінансових функцій вибираємо НПЗ і в поле Норма уводимо В16 (дисконтна ставка) , а у поле значення – діапазон В4:B13 . Завершивши роботу, отримаємо результат - $643 517,49. Це далеко не мільйон. Зверніть увагу, що в комірці В3 уведено число в дужках. Ми витратили 5 доларів і вони від’ємні. Круглі дужки, це одна з форм у Excel уведення від’ємних чисел. Функція НПЗ - один з кращих інструментів оцінки інвестицій. Якщо НПЗ повертає додатне число, то буде прибуток.

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

Таблиця ППЛАТ Таблиця ВНДОХ




А

В




А

В

1

Процентна ставка

9%

1

^ Підрахунок ВНДОХ




2

Питома ставка