18.1 Теоретичні відомості
Одним із основних призначень Microsoft Excelє виконання різних обчислень за допомогою формул та вбудованих функцій. Формулапочинається зі знака рівності і є виразом, який може складатися з чисел, посилань (адрес осередків) або функцій, об'єднаних знаками арифметичних дій. У формулах Excel застосовуються такі арифметичні дії: зведення у ступінь (^); множення (*); розподіл (/); додавання (+); віднімання (-).
Функція- це готова формула, яка складається з імені функції та аргументу або кількох аргументів, наприклад, СУМ(A3; C8). Ім'я функції визначає дії, а аргументи задають значення або комірки та вказуються у круглих дужках. Причому між ім'ям функції та круглими дужками прогалини відсутні. Для обчислень за допомогою функцій використовується Майстер функцій – крок 1 із 2, який викликається клацанням на піктограмі fx у рядку введення формул або виконанням команди Вставка/Формула. Перед викликом функції необхідно встановити курсор у той осередок, в який необхідно ввести функцію.
Для наочного уявлення залежностей між величинами електронних таблиць Excel використовуються діаграми.
З допомогою Excelможна створювати складні діаграми даних електронних таблиць. Діаграмибудуються за допомогою майстра діаграм, який можна викликати, натиснувши кнопку Майстер діаграм на стандартній панелі інструментів, або виконавши команду Вставка/Діаграма. Діаграму можна створити за один крок, клацнувши на клавіші F11. Перед викликом майстра діаграм необхідно виділити електронну таблицю або частину таблиці, для якої потрібно створити діаграму.
Наприклад, для побудови графіка функції Y = 2sin 3 (5пx) + 7cos(3 пx) 2
у Microsoft Excel, її необхідно представити (за допомогою арифметичних операцій, що використовуються в Excel) у вигляді зручному для виконання обчислень. Після перетворення функції, вона матиме вигляд: Y = 2*(sin(5*ПІ()*x)^3 + 7*cos(3*ПІ()*x)^2.
Потім у комірки, які визначені для аргументу "x", треба ввести числа з певним кроком (наприклад, від -2 до +2 з кроком 0.1), а у комірки, призначені для розміщення функції Y, необхідно ввести формулу = 2*(sin (5*ПІ()*x)^3 + 7*cos(3*ПІ()*x)^2 При цьому в формулу замість аргументу "x" треба ввести посилання на комірки, в яких розміщені їх значення. обчислень необхідно виділити результати обчислень і викликати майстер побудови діаграм одним із способів, а потім побудувати графік функції за чотири кроки, використовуючи майстер діаграм.
Для виконання цієї лабораторної роботи необхідно ознайомитись з функціями, формулами, діаграмами Microsoft Excel, які викладені у лекційному курсі або у рекомендованих джерелах інформації.
18.2 Мета роботи
Набуття практичних навичок роботи з формулами, функціями та діаграмами в Microsoft Excel.
18.3 Постановка задачі
Обчисліть функції:
- Y=2sin(пx)cos 5 (2пx) + sin(5 пx) 2 де п - число ПІ = 3, 14;
- Z=cos(2пx) sin 3 (пx) - 3cos(4пx);
- діапазон зміни аргументу X нач =-3, X кон =3, крок-0,1.
За результатами виконаних обчислень (функцій Y та Z) збудуйте в єдиній системі координат графіки функцій. Графіки функцій розташуйте на окремому аркуші та введіть Підписи осі Х.
18.4 Покрокове виконання роботи
18.4.1 Увімкніть ПК
Натисніть кнопку Powerна системному блоціПК.
18.4.2 Запустіть Microsoft Excel
18.4.2.1 Запустіть Microsoft Excel, використовуючи команду Головного меню.
Після повного завантаження ОС запустіть Microsoft Excel, клацнувши кнопку Пуск і вибравши в головному меню команду Програми/Microsoft Office, Microsoft Office Excel 2003. В результаті відкриється вікно програми Microsoft Excel, в якому відображається порожня книга "Книга 1" з трьома робочими листами.
18.4.2.2 Збережіть робочу книгу Excel.
Щоб зберегти робочу книгу в Excel, виконайте команду Файл/Зберегти, у вікні діалогу Збереження документа введіть ім'я файлу: Графіки функцій. Клацніть на кнопці ОК, зберігши робочу книгу Excel у папці Мої документи.
18.4.3 Обчислення функцій
18.4.3.1 Призначте стовпці A, B, C імена (Аргумент X, Функція Y, Функція Z).
Виконайте таке:
- у комірку A1 введіть ім'я Аргумент X;
- у комірку B1 введіть ім'я Функція Y;
- у комірку C1 введіть ім'я Функція Z.
18.4.3.2 Заповнення шпальти A значеннями аргументу X.
Виконайте таке:
- у комірку A2 введіть початкове значення (рівне -3);
- виділіть комірку A2 та виконайте команду Правка/Заповнити/Прогресія... ;
- заповніть поля: Розташування – по стовпцях; Крок – 0,1; Тип - арифметична граничне значення - кінцеве значення 3;
18.4.3.3 Введення в стовпець формули B для розрахунку функції Y.
Введіть у стовпець B формулу для розрахунку функції Y, використовуючи Майстер функцій або вводячи її з клавіатури:
18.4.3.4 Введення в стовпець формули C для розрахунку функції Z.
Введіть формулу C для розрахунку функції Z, використовуючи Майстер функцій або вводячи її з клавіатури. Введення формули для розрахунку функції Z аналогічне введенню формули для розрахунку функції Y, описаному в п. 18.4.3.3. Аргументом x для формули, поміщеної в комірку C2 є адреса комірки A2.
18.4.3.5 Заповнення формулами інших осередків шпальт B і C.
Для заповнення формулами осередків стовпців B і C доцільно використовувати спосіб автозаповнення:
- виділіть одночасно осередки B2 та C2;
- встановіть вказівник миші на маркер заповнення і, утримуючи ліву кнопку миші, протягніть виділення вниз інші осередки. Осередки будуть заповнені формулами.
18.4.4 Побудова графіків
18.4.4.1 За результатами обчислень побудуйте графік функції Y.
Для побудови графіка функції Y на окремому аркуші виконайте таке:
- виділіть результати обчислень разом із заголовком Функція Y;
- виконайте команду Вставка/Діаграма;
- вибираючи на кожному з чотирьох кроків необхідні установки, побудуйте графік на окремому аркуші.
18.4.4.2 За результатами обчислень побудуйте графік функції Z на тій самій діаграмі, що і графік функції Y.
Для побудови графіка функції Z на тій же діаграмі, що і графік функції Y виконайте:
- виділіть результати обчислень разом із заголовком Функція Z;
- виконайте команду Правка/Копіювати;
- відкрийте аркуш із графіком функції Y;
- виділіть діаграму, клацнувши на ній лівою кнопкою миші;
- вставте дані з буфера обміну, виконавши команду Правка/Вставити.
18.4.5 Введіть Підписи осі X на діаграмі
Для введення підпису осі X на діаграмі виконайте таке:
- виділіть діаграму;
- виконайте команду меню Діаграма/Вихідні дані...;
- відкрийте вкладку Ряд;
- клацніть на кнопці згорнути, розташовану праворуч текстового вікна Підписи осі X;
- перейдіть на аркуш із функціями, виділіть значення аргументу X, крім заголовка та натисніть клавішу Enter;
- Щоб закрити вікно Вихідні дані, натисніть кнопку ОК.
Збережіть зміни у файлі.
18.4.6 Завершення роботи
Повідомте викладача про виконану роботу. Після дозволу на завершення роботи закрийте прикладну програму Microsoft Excel, Після чого можете приступити до складання тестів з виконаної роботи.
Метою лабораторної роботи є вивчення та закріплення навичок роботи із введення даних та використання формул у Microsoft Excel 2007 .
Введення даних до електронної таблиці
У осередках електронної таблиці можуть бути дані трьох типів: числові значення (включаючи час і дату), текст, формули. На робочому аркуші, але в «графічному шарі» поверх аркуша, можуть також бути малюнки, діаграми, зображення, кнопки та інші об'єкти.
Введення чисел
Числа вводяться за допомогою верхнього ряду або числової клавіатури. Як десятковий роздільник застосовується кома або точка, можна вводити знаки грошових одиниць. Якщо перед числом ввести "мінус" або дужки, воно вважається негативним. Нулі, набрані перед числом, ігноруються програмою. Якщо необхідно отримати значення з нулями попереду, його потрібно інтерпретувати як текстове.
Для представлення чисел в Excel використовується 15 цифр, при введенні числа із 16 цифр воно автоматично збережеться з точністю до 15 цифр. Числові значення автоматично вирівнюються по правій межі комірки.
Введення значень дат та часу
Excel подання дат використовує внутрішню систему порядкової нумерації дат. (Так, рання дата, яку може розпізнати програма, – 1 січня 1900 року, цій даті присвоєно порядковий номер 1, наступної дати – порядковий номер 2 тощо.). Дати вводяться у звичному для користувача форматі та розпізнаються автоматично. Тимчасові значення також вводяться в одному з форматів часу, що розпізнається. Подання дати та часу безпосередньо на аркуші регулюється завданням формату відображення комірки.
Введення тексту
Як текстові значення сприймаються всі введені дані, які не розпізнаються як числа або формули. Текстові значення вирівнюються по лівій межі таблиці. Якщо текст не вміщається в одному осередку, він розташовується поверх сусідніх осередків, якщо вони вільні. Параметри розташування тексту в осередку задаються через осередок.
Введення формули
Формулою вважається будь-який математичний вираз. Формула завжди починається зі знака «=», може включати, крім операторів і посилань на осередки, вбудовані функції Excel.
Формати даних
Після введення в комірку даних Excel автоматично намагається визначити їх тип і привласнити комірці відповідний формат - форму представлення даних. Важливо призначити правильний формат осередку, щоб, наприклад, осередок міг брати участь у обчисленнях (не текстової).
В Excel є набір стандартних форматів осередків, які можуть бути використані у всіх книгах (рисунок 2.2.17). Активізувати його можна, вибравши Головна – Число – Числовий формат, або по контекстному менюдля виділеної комірки на вкладці Число вікна Формат осередків.
Малюнок 2.2.17. Стандартні формати
Спочатку всі осередки таблиці мають формат Загальний. Використання форматів впливає на те, як відображатиметься вміст у осередках: загальний – числа відображаються у вигляді цілих чисел, десяткових дробів, якщо число занадто велике, то у вигляді експоненціального; числовий – стандартний числовий формат; фінансовий та грошовий – число округляється до 2 знаків після коми, після числа ставиться знак грошової одиниці, грошовий формат дозволяє відображати негативні суми без знака «мінус» та іншим кольором; коротка дата та довгий формат дати – дозволяє вибрати один із форматів дат; час – надає на вибір кілька форматів часу; - відсотковий – число (від 0 до 1) у осередку множиться на 100, округляється до цілого та записується зі знаком %; дробовий - використовується для відображення чисел у вигляді не десяткового, а звичайного дробу; експоненціальний – призначений для відображення чисел у вигляді добутку двох складових: числа від 0 до 10 та ступеня числа 10 (позитивної чи негативної); текстовий – при встановленні цього формату будь-яке введене значення сприйматиметься як текстове; додатковий – включає формати Поштовий індекс, Індекс+4, Номер телефону, Табельний номер; всі формати – дозволяє створювати нові формати у вигляді шаблону користувача.
Використання засобів, що прискорюють введення даних
При введенні даних на листи таблиці можуть бути використані деякі прийоми, що дозволяють прискорити їхнє введення.
1) Автозаповнення під час введення. При введенні однакових значень у кілька осередків за допомогою маркера автозаповнення (хрестика в нижньому правому куті активного осередку) можна скопіювати значення суміжні осередки. За допомогою контекстного меню, що відкривається, натискання правої кнопки миші після перетягування, можна задати Додаткові параметриавтозаповнення (наприклад, ввівши в комірки числа 1 та 3, можна отримати послідовність чисел з кроком 2 для виділеного діапазону осередків).
2) Використання прогресії. Якщо осередок містить число, дату або період часу, який може бути частиною ряду, то при копіюванні відбувається збільшення її значення (виходить арифметична або геометрична прогресія, список дат). Щоб задати прогресію, потрібно вибрати кнопку Заповнити панелі Редагування вкладки Головна і в діалоговому вікні Прогресія встановити параметри для арифметичної або геометричної прогресії.
3) Автозавершення під час введення. За допомогою цієї функції можна виконувати автоматичне введення текстових даних, що повторюються. Після введення в комірку тексту Excel запам'ятовує його і при наступному вступі після набору перших літер слова пропонує варіант для завершення введення. Для завершення введення необхідно натиснути Enter. Доступ до цієї команди можна також отримати вибравши по контекстному меню з правої кнопки миші пункт Вибрати зі списку, що розкривається. Функція автозавершення працює лише з безперервною послідовністю осередків.
4) Використання автозаміни під час введення. Автозаміна призначена для автоматичної заміни одних заданих сполучень символів на інші під час введення. Наприклад, можна ввести один символ замість введення кількох слів. Команда доступна на кнопці Office – Параметри Excel. У пункті Правопис - Параметри автозаміни потрібно задати текст та його скорочення.
5) Використання сполучення клавіш Сtrl+Enter для введення повторюваних значень. Для введення тих самих значень у кілька осередків можна виділити їх, ввести значення в одну комірку і натиснути Сtrl+Enter. В результаті ті самі дані будуть введені у всі виділені осередки.
Перевірка даних під час введення
Якщо необхідно бути впевненим у тому, що на аркуш введені правильні дані, можна вказати критерії, які є допустимими для окремих осередків чи діапазонів осередків. Щоб перевірити, виконайте команду Дані – Робота з даними – Перевірка даних. У вікні (малюнок 2.2.18) задайте критерії перевірки на вкладці Параметри, текст повідомлення-підказки користувачеві для введення на вкладці Повідомлення для введення, текст повідомлення про помилку на вкладці Повідомлення про помилку.
Після застосування команди Дані – Робота з даними – Обвести неправильні дані всі неправильні дані будуть обведені червоними кружками.
Малюнок 2.2.18. Вікно завдання параметрів перевірки даних
Використання формул
Під формулою в Excel розуміється математичне вираз, виходячи з якого обчислюється значення деякої осередки. У формулах можуть використовуватись: числові значення; адреси осередків (відносні, абсолютні та змішані посилання); оператори: математичні (+, -, *, /, %, ^), порівняння (=,<, >, >=, <=, < >), текстовий оператор & (для об'єднання кількох текстових рядків в одну), оператори відношення діапазонів (двокрапка (:) – діапазон, кома (,) – для об'єднання діапазонів, пробіл – перетин діапазонів); функції.
Введення формули завжди починається зі знака "=". Результат формули відображається у осередку, а сама формула – у рядку формул. Адреси осередків у формулі можуть вводитися вручну, а можуть просто за допомогою клацання миші за потрібними осередками.
Після обчислення в осередку відображається отриманий результат, а рядок формул у вікні введення – створена формула.
Способи адресації осередків
Адреса осередку складається з імені стовпця та номера рядка робочого листа (наприклад А1, BM55). У формулах адреси зазначаються за допомогою посилань – відносних, абсолютних чи змішаних. Завдяки посиланням дані, що знаходяться в різних частинах аркуша, можуть використовуватись у кількох формулах одночасно.
Відносне посилання вказує розташування потрібного осередку щодо активного (тобто поточного). При копіюванні формул ці посилання автоматично змінюються відповідно до нового положення формули (Приклад запису посилання: A2, С10).
Абсолютне посилання вказує на точне розташування осередку, що входить у формулу. При копіюванні формул ці посилання не змінюються. Для створення абсолютного посилання на комірку, поставте знак долара ($) перед позначенням стовпця та рядка (Приклад запису посилання: $A$2, $С$10). Щоб зафіксувати частину адреси осередку від змін (по стовпцю або рядку) при копіюванні формул, використовується змішане посилання з фіксацією потрібного параметра. (Приклад запису посилання: $A2, $10).
Зауваження
· Щоб вручну не набирати знаки долара під час запису посилань, можна скористатися клавішею F4, яка дозволяє перебрати всі види посилань для комірки.
Вбудовані функції Excel
Кожна функція має свій синтаксис і порядок дії, який потрібно дотримуватись, щоб обчислення були правильними. Аргументи функції записуються в круглих дужках, причому функції можуть мати або не мати аргументів, при їх використанні необхідно враховувати відповідність типів аргументів. Функція може бути аргументом для іншої функції, в цьому випадку вона називається вкладеною функцією. При цьому у формулах можна використовувати кілька рівнів вкладення функцій.
У Excel 2007 є математичні, логічні, фінансові, статистичні, текстові та інші функції. Ім'я функції у формулі можна вводити вручну з клавіатури (при цьому активується засіб Автозаповнення формул, що дозволяє за першими введеними літерами вибрати потрібну функцію(малюнок 2.2.19)), а можна вибирати у вікні Майстер функцій, що активується кнопкою на панелі Бібліотека функцій вкладки Формули або груп функцій на цій же панелі, або за допомогою кнопки панелі Редагування вкладки Головна.
Малюнок 2.2.19. Автозаповнення формул
Формули можна відредагувати так само, як і вміст будь-якого іншого осередку. Щоб відредагувати вміст формули: двічі клацніть на клітинці з формулою, або натисніть F2, або відредагуйте вміст у рядку введення формул.
Присвоєння та використання імен осередків
У Excel 2007 є корисна можливість присвоєння імен осередкам або діапазонам. Це особливо зручно при складанні формул. Наприклад, задавши для будь-якої комірки ім'я Ітого_за_рік, можна у всіх формулах замість адреси комірки вказувати це ім'я.
Ім'я осередку може діяти в межах одного аркуша або однієї книги, воно має бути унікальним та не дублювати назви осередків. Щоб призначити ім'я осередкам, потрібно виділити осередок або діапазон і ввести у рядок назви нове ім'я. Або скористатися кнопкою Присвоїти ім'я панелі Певні імена вкладки Формули та викликати діалогове вікно (малюнок 2.2.20), щоб задати потрібні параметри.
Малюнок 2.2.20. Вікно створення імені
Щоб переглянути всі імена, використовуйте команду Менеджер імен. Також на аркуші можна отримати список усіх імен з адресами комірок за командою Використати у формулі – Вставити імена панелі Певні імена.
Для вставлення імені у формулу можна застосувати команду Використати у формулі та вибрати зі списку потрібне ім'я осередків.
Зауваження. Ім'я може бути присвоєне як діапазонам осередків, а й формулі. Це зручно при використанні вкладених формул.
Відображення залежностей у формулах
Щоб виявити помилки під час створення формул, можна відобразити залежності осередків. Залежності використовуються для перегляду на табличному полі зв'язків між осередками з формулами та осередками зі значеннями, які були задіяні в даних формулах. Залежно відображаються лише в межах однієї відкритої книги. При створенні залежності використовуються впливові осередки та залежні осередки.
Впливаючий осередок – це осередок, який посилається на формулу в іншому осередку.
Залежна комірка – це комірка, що містить формулу.
Щоб відобразити зв'язки комірок, потрібно вибрати команди Впливні комірки або Залежні комірки панелі Залежності формул вкладки Формули. Щоб не відображати залежність, застосовуйте команду Прибрати стрілки тієї ж панелі.
Малюнок 2.2.21. Відображення осередків, що впливають
Режими роботи з формулами
В Excel встановлено режим автоматичних обчислень, завдяки якому формули на аркушах миттєво перераховуються. При розміщенні на аркуші дуже великої кількості (до кілька тисяч) складних формул, швидкість роботи може помітно знизитися через перерахунок усіх формул на аркуші. Щоб керувати процесом обчислення за формулами, необхідно встановити ручний режимобчислень, застосувавши команду Формули – Обчислення – Параметри обчислень – Вручну. Після внесення змін потрібно викликати команду Провести обчислення (для перерахунку даних на аркуші книги) або Перерахунок (для перерахунку всієї книги) на панелі Обчислення.
Корисною можливістю для роботи з формулами є відображення всіх формул на аркуші. Це можна зробити за допомогою команди Формули – Залежності формул – Показати формули. Після цього в осередках замість обчислених значень буде показано записані формули. Для повернення до звичайного режиму потрібно ще раз натиснути кнопку Показати формули.
Якщо формула повертає помилкове значення, Excel може допомогти визначити комірку, яка викликає помилку. Для цього необхідно активізувати команду Формули – Залежності формул – Перевірка наявності помилок – Джерело помилок. Команда Перевірка помилок допомагає виявити всі помилкові записи формул.
Для налагодження формул існує засіб обчислення формул, що викликається командою Формули – Залежності формул – Обчислити формулу, що показує покрокове обчислення у складних формулах
Практикум:
1. Сформувати таблицю розрахунку суми ряду (варіанти завдань із розрахунку суми ряду – див. нижче). При формуванні таблиці використовувати вбудовані функції, абсолютну та відносну адресацію, автозаповнення осередків.
2. Залежно від числа доданків n оформити таблицю так:
Таблиця 19.
| x i | 1 | 2 | … | n | S | Y |
| 0,1 | ||||||
| 0,2 | ||||||
| . | ||||||
| . | ||||||
| 1 |
Таблиця 20
| i x | 0,1 | 0,2 | … | 1 |
| 1 | ||||
| 2 | ||||
| . | ||||
| . | ||||
| n | ||||
| S | ||||
| Y |
3. Використовуючи умовне форматування, виділити негативні числа синім кольором, числа більше 1,5 – червоним.
4. Оформити таблицю. Зразок оформлення – нижче. Крок зміни x в залежності від варіанта завдання дорівнює 0,1 (або Pi/*).
5. Побудувати в одній координатній сітці (на одній діаграмі) графіки s=f(x) та y=f(x).
6. Вивчити можливості застосування функцій (список функцій див. у варіанті завдання), навести приклад роботи кожної функції.
Таблиця 21. Варіанти завдань
Лабораторна робота №1. Формули та функції MS Excel
Підготувати відповідні відомості, скориставшись наведеними рекомендаціями.
Завдання 1. Сформувати оголошення про продаж квартир за зразком (рис. 1).
Рис. 1. Оголошення про продаж квартир
Згрупувати наявні дані щодо квартир у вигляді списку (рис. 2).
Рис. 2. Дані про квартири, виставлені на продаж
У осередок G2ввести формулу:
А2&" кв., по "&В2&", площа: "SD2&", "&Е2&"поверх, "&ТЕКСТ(С2;"# ##0р.")&", "&ECJIІ(F2="+"; "телефон"; "телефону немає")
Поясніть цю формулу, скориставшись довідкою.
Для діапазону G3:G5користуватися маркером автозаповнення чи скопіювати цю формулу.
При необхідності відформатувати отримані оголошення за допомогою команди Формат | Автоформат.
Завдання 2.Сформувати та заповнити відомість переоцінки основних засобів виробництва за формою, наведеною на рис. 3.
У осередок А1ввести назву відомості.
У комірки A4:F4ввести назви полів відомості: Найменування об'єкта, Балансова вартість (БС), Зношування об'єкта (ІВ), Залишкова вартість (ОС), Відновна повна вартість (ВПС), Відновна залишкова вартість (воє). ПолеНайменування об'єкта включає наступні рядки:Відділ менеджменту та маркетингу, Відділ транспортувань, Складальний цех, Оздоблювальний цех, Склад № 1, Склад № 2, Склад № 3, Разом.
Формули для розрахунків:
ОС = БС – ІО
ВВС = БС * К
ВОС = ОС * К
де до - коефіцієнт, рівний:
3,3 - якщо БС менше або дорівнює 650 млн руб.;
4,2 - якщо БС більше 650 млн руб., Але менше 1000 млн руб.;
5,1 - якщо БС дорівнює 1000 млн. руб. або більше.
Для формування автоматичних розрахунків використовуйте такі формули:
для осередку D5: =В5-С5
для осередку Е5: =В5*ЯКІ(В5<=650;3,3;ЕСЛИ(И(В5>б50; В5<1000);4,2;5,1))
для осередку F5: =D5*ЯКІ(В5<=650;3.3;ЕСЛИ(И(В5>650; В5<1000);4,2;5.1))
Результативний рядок можна отримати використанням, наприклад, для осередку О 12формули:
СУМ(В5: В11), або слід виділити діапазон осередків B12: F12 і скористатися можливістю підсумовування (натиснути кнопку Автосума на панелі інструментів).
Відформатувати результати, отримані в таблиці, а також назву відомості.
Рис. 3. Відомість переоцінки основних засобів виробництва
Завдання 3. Сформувати та заповнити звітну відомість роботи мережі комп'ютерних клубів за формою, наведеною на рис. 4.
Рис. 4. Відомість роботи мережі комп'ютерних клубів
У осередок А1ввести назву відомості.
У комірки АЗ:НЗввести назви полів відомості: клуб, Січень, Лютий, Березень, Сумарний виторг, Місце, Середня виручка, відсоток. Поле Клуб включає наступні рядки : Альтаїр, Грувіт, Полігон, Гелакс, Зірка, Хексен, Антей, Арсенал, Арена, Бліндаж, Разом.
Основні формули для обчислень, що копіюються для аналогічних обчислень за рядками, наведено в таблиці 1.
Формули для розрахунку
Відформатувати отриману відомість.
Завдання 4.Сформувати на робочому аркуші відомості "Розрахунок заробітної плати працівників науково-проектного відділу "Альфа"(рис. 5).
Рис. 5 Відомість із розрахунку заробітної плати
У осередок А2 помістити назву відомості- розрахунок заробітної плати працівників науково-проектного відділу "Альфа", відцентрувати по лівому краю (наприклад, командою Формат | Осередки| вкладка Вирівнюванняабо відповідною кнопкою По правому краю (на панелі інструментів).
У комірки АЗ:КЗввести назви полів відомості: № пп, Прізвище І.О., Посада, Тарифна ставка, Стаж, к, Надбавка за стаж, Разом, Відсоток податку, Утримати, Виплата.
шапці відомості - до кожного стовпця - створити приховані примітки. Примітки створюються командою Вставлення | Примітка(також можна використовувати панель інструментів Рецензування,яка додає командою Вид| Панелі інструментів| Рецензування):
№ пп – номер працівника відділу;
Прізвище І.Б. - заносяться всі прізвища працюючих у науково-проектному відділі;
Посада - посада на момент заповнення відомості;
тарифна ставка-грошовий еквівалент займаної посади;
Стаж- вноситься ціла кількість відпрацьованих років на момент заповнення відомості;
к-коефіцієнт за стаж роботи;
надбавка за стаж – грошовий еквівалент за стаж роботи;
підсумко- нарахування заробленої плати з урахуванням тарифної ставки та стажу роботи;
відсоток податку - визначає відсоток відрахувань до бюджету;
Утримати - грошовий еквівалент відрахувань до бюджету;
Виплата – сума, призначена до видачі.
При розрахунках у відомості враховувати таке:
до, Надбавка за стаж, Разом, Відсоток податку, Утримати, виплата-обчислюються за допомогою відповідних формул, з використанням автозаповнення або копіювання формули.
Коефіцієнт присвоюється з наступного розрахунку:
0,1 – відпрацьовано до 5 років включно, 0,2 – від 5 до 10 років включно, 0,25 – від 10 до 15 років включно, 0,3 – понад 15 років. Формула для комірки F4:
ЯКЩО(Е4<=5;0,1;ЕСЛИ(И(Е4>5; Е4<=10);0,2;ЕСЛИ(И(Е4>10; Е4<=15);0,25;0,3)))
надбавка за стаж – грошовий еквівалент за стаж роботи. Формула для комірки G4:
G4:
(запроваджується командою Формат | Осередки| вкладка Число зі списку Числові формативибрати Усі форматиі в полі Типввести вказаний формат).
разом - тарифна ставка з урахуванням стажу. Формула для комірки Н4:
Користувальницький формат числа для комірки Н4:
Відсоток податку - враховує, що: 2% - нарахування (за підсумком) становить до 7000 грн. включно, 10% - понад 7000 грн. до 10 000 грн. включно, 20% - понад 10 000 грн. до 25 000 грн. включно, 35% - перевищують 25 000 р. Формула для комірки I4:
ЯКЩО(Н4<=7000;0,02;ЕСЛИ(И(Н4>7000; Н4<=10000);0,1;ЕСЛИ(И(Н4>10000; Н4<=25000);0,2;0,35)))
Формат числа для комірки I4- відсотковий.
Утримати – грошовий еквівалент податків. Формула для комірки J4:
Користувальницький формат числа для комірки J4:
Виплата - сума До видачі:Разом без Утримати.
Вимоги до стовпця стаж:
Створити формат даних, що враховує стаж роботи: до 5 років - дані представлені жовтим кольором, від 5 до 10 - синім, від 10 до 15 - зеленим, понад 15 - червоним.
Скористайтеся командою Формат | Осередкита ввести власний формат для комірки Е4:
[Червоний]# ##0;
а також використовувати команду Формат | Умовне форматування.
У разі введення негативної кількості років має з'являтися відповідне вікно. Для перевірки
введення чисел використовувати команду Дані Перевірка |вкладка Повідомлення про помилку.
Для поля Тарифна ставка – вивести постійне повідомлення: Тарифна ставка. Будьте уважні під час введення тарифної ставкидля отримання якого використовувати команду Дані Перевірка| вкладка Повідомлення для введення.
У разі введення негативних значень у стовпчику тарифна ставка з'являється відповідне попередження: Тарифна ставка не може бути негативною, яке формується через формат користувача:
###0,00р.;[Червоний]"Тарифна ставка не може бути
негативною!"
Рішення
Для основних платежів за позикою, що погашається рівними платежами наприкінці або на початку кожного розрахункового періоду, в MS Excel XP використовується функція:
ОСПЛТ (Ставка, Період, Кпер, Пс, Бс)
(У ранніх версіях MS Excel ця функція називалася ОСНПЛАТ) У нашому випадку функція СППЛТ має вигляд: ОСПЛТ(12%, 4, 5, 1000000000)
Введення даних та розрахунки проводяться відповідно до рис. 4.4.
Рис. 11. Розрахунок основних платежів із позики
У осередок В 8вводиться формула:
ОСПЛТ(В5; В6; В4; ВЗ)
Рішення
Для обчислення величини постійної періодичної виплати ренти (наприклад, регулярних платежів за позикою) за постійної процентної ставки використовується функція ПЛТ (у ранніх версіях - функція ППЛАТ):
ПЛТ(Ставка; Кпер; Бс; Пс; Тип)
У нашому випадку функція ПЛТ має вигляд:
ПЛТ(10%/12; 20*12; -(350000* (1-25%)))- щомісячні виплати;
ПЛТ (10%; 20; - (350000 * (1-25%))) - щорічні виплати.
Розв'язання задачі наведено на рис. 12 та 13.
Рис. 12. Розрахунок іпотечної позички
Рис. 13. Формули для розрахунку іпотечної суди
Завдання 3.Визначити, яка сума виявиться на рахунку, якщо 52000 руб. покладено на 20 років під 11% річних. Відсотки нараховуються щомісяця.
Рішення
Для розрахунку майбутньої вартості єдиної суми вкладу використовуються складні відсотки, а розрахункова формула заснована на функції БС (у ранніх версіях – функція БЗ):
БС(Ставка; Кпер; Плт; Пс; Тип)
Для нашого завдання функція БС набуде вигляду:
БС(11%/12; 20 * 12; ,-52000)
Розв'язання задачі наведено на рис. 14, а формула для комірки В26:
БС(B22/B23;B24*B23;;-B21)
Рис. 14. Розрахунок майбутньої вартості вкладу
Завдання 4. Облігація номіналом 200 000 руб. випущено на 7 років. Передбачається наступний порядок нарахування відсотків: у перший рік-11%, наступні три роки-по 16%, у решту
Рішення
Для розрахунку нарощеної вартості облігації за складною відсотковою ставкою використовується функція:
БРАСПИС(Первинне; План)
Для нашого завдання функція набуває вигляду:
БРАСПИС(200000; (11%; 16%; 16%; 16%; 20%; 20%; 20%))
Рішення наведено на рис. 15, а формула для розрахунку в осередку В42:
БРАСПИС(В30; В34: В40)
Аналогічним чином можна використовувати вбудовані функції MS Excel та інших фінансових розрахунків.
Рис. 15 Розрахунок нарощеної вартості облігації за складною процентною ставкою
Рішення
Розрахунок внутрішньої швидкості обороту інвестицій проводиться за допомогою функції ВСД (у ранніх версіях - ВНДОХ): ВСД (Значення; Припущення)
Рис. 16 Робочий лист визначення початкових витрат за проектом
Введення вихідних даних здійснюється відповідно до рис. 16. Спочатку для розрахунку величина витрат на проект вибирається довільно (комірку для цієї суми можна залишити навіть порожньою) та проводяться обчислення.
У осередок О 12вводиться формула = ВСД (В4: В9).
приклад представлений на рис. 18.
Рис. 17 Вікно Вибір параметра
Рис. 18 Розрахована величина початкових витрат за проектом
Приклад розрахунку ефективності нерівномірних капіталовкладень
Завдання 2.Вас просять дати у борг 15 000 руб. і обіцяють повернути через рік 3000 руб., Через два - 5000 руб., Через три - 9000 руб. За якою відсотковою ставкою ця угода вигідна?
Рішення
При вирішенні цього завдання слід використовувати функцію ЧПС та засіб Підбір параметра:
ЧПС(Ставка; значення 1; значення 2; . . .)
Введення вихідних даних здійснюється відповідно до рис. 19. Спочатку для розрахунку вибирається довільний відсоток річної облікової ставки (комірку з цією величиною можна залишити навіть порожньою) та проводяться обчислення. У осередок О 9вводиться формула: = ЧПС (В6; В2: В4)
Рис. 19. Робочий лист для вирішення задачі з нерівномірними капіталовкладеннями
У осередок С7можна ввести таку формулу:
ЯКЩО(B7=1;"рік";ЯКІ(І(B7>=2;B7)<=4);"года";"лет"))
У полі Встановити в осередкувведіть О 9, тобто адреса осередку, в якій необхідно отримати шукане значення суми угоди (15000);
У полі Значеннявведіть 15000, тобто саме шукане значення суми угоди;
У полі Змінюючи значення осередкувведіть адресу осередку - В8, в якій за допомогою засобу Вибір параметрабуде отримано необхідну відсоткову ставку для розглянутого завдання (у разі, якщо така існує).
Остаточне вирішення задачі наведено на рис. 20.
Рис. 19. Вікно Вибір параметрадля завдання про нерівномірні капіталовкладення
Рис. 20 Оптимальна процентна ставка
Лабораторна робота №6. Таблиця підстановки
Таблиця підстановкидозволяє проводити аналіз зміни результату при довільному діапазоні вихідних даних. На одному робочому аркуші можна розташувати кілька таблиць підстановок. Це дає можливість одночасно аналізувати різні формули та статистичні дані.
Таблицю підстановки можна використовувати для:
Зміни одного вихідного значення, переглядаючи у своїй результати однієї чи кількох формул;
- зміни двох вихідних значень, переглядаючи результати лише однієї формули.
Використання таблиці підстановки з однією змінною змінною і декількома формулами
Розглянемо цю методику на прикладі розрахунку щомісячних виплат за позикою (розрахунок відбувається за допомогою функції ПЛТ) та платежів за відсотками (функція ПРОЦПЛАТ):
Вирішення такого завдання передбачає наступні кроки:
1. Створити або перейти на робочий лист, де вирішуватиметься аналізоване завдання.
2. Організувати інтерфейс таким чином, щоб всі дані, що вводяться, були зрозумілі користувачеві:
У відповідні осередки робочого листа вводяться необхідні підписи та дані (рис. 21).
У осередок В 5- Формула: = ПЛТ ($ $ 4/12; $ $ 3 * 12; $ $ 2)
У осередок D6- Формула: =ПРОЦПЛАТ($D$4;$D$5;$D$3;$D$2)
Рис. 21 Підготовка вихідних даних
Порада: При вирішенні завдань, пов'язаних з використанням таблиці підстановки, рекомендується застосовувати у формулах абсолютну адресацію осередків. Це сприяє правильному виконанню обчислень у осередках робочого листа.
3. Після підготовки вихідних даних перейти до того місця робочого аркуша, де будуть розташовуватися значення залежно від зміни однієї змінної і від різних формул, що розраховуються (рис. 22).
У верхньому рядку майбутньої таблиці над місцем розташування результатів вказати адресу кожної формули, на яку потрібно отримати список результатів. Замість адреси можна помістити в комірку саму формулу (комірки B10і C10містять формули).
Зліва розташувати різні значення вихідних даних, які необхідно протестувати.
Рис. 22 Підготовка діапазону, що змінюється, і розрахункових формул для використання одномірної таблиці підстановки
4. Виділити осередки, які будуть містити таблицю. При цьому найлівішим стовпцем цієї таблиці повинен бути стовпець вихідних значень, а найвищим рядком має бути рядок аналізованих формул. Результат буде поміщений у порожні осередки.
5. Скористатися командою Дані Таблиця підстановкита у діалоговому вікні Таблиця підстановки(рис. 23) вказати, куди та які значення необхідно підставляти.
У прикладі - підстановка значень відсоткової ставки (стовпець вихідних значень А10: А19)відбувається в осередок В 4, т. к. у цьому осередку спочатку вказується величина відсоткової ставки, що входить у формули, що розраховуються. Результати обчислень будуть поміщені в незаповнені осередки виділеного діапазону (рис. 24).
Рис. 23 Використання таблиці підстановки
Рис. 24 Розраховані значення для одномірної таблиці підстановки
Використання таблиці підстановки з двома змінними змінними і однією формулою
Розглянемо цю методику на прикладі розрахунку щомісячних виплат за позикою залежно від різних термінів погашення та різних процентних ставок. Вирішення завдання передбачає
наступні кроки:
1. Організувати на робочому аркуші відповідний інтерфейс користувача для деякого набору вхідних даних (мал. 25):
Конкретна процентна ставка - 3% (осередок В 4);
Конкретний термін погашення - 3 роки (осередок ВЗ);
Формула для комірки В 5:
ПЛТ($В$4/12;$В$3*12;$В$2)
Рис. 25. Підготовка даних задачі
2. Підготувати таку таблицю (рис. 26):
Дані, що змінюються, помістити в лівий стовпець і верхній рядок - у нашому випадку значення процентної ставки (осередок В 4)розташовуються в діапазоні В10: В14,а значення терміну погашення (осередок ВЗ) - в діапазоні C9: F9;
На перетині рядка та стовпця у верхньому лівому кутку розташувати необхідну формулу або посилання на неї (осередок О 9містить формулу осередку В 5, у якій обов'язкова абсолютна адресація осередків).
Рис. 26. Підготовка діапазону для використання двовимірної таблиці підстановки
3. Виділити діапазон осередків, що є прямокутною областю і містить формулу для розрахунку, змінювані дані для двох змінних і осередки для результатів обчислень.
4. Виконати команду Дані Таблиця підстановкиі в вікні (мал. 27) вказати, куди і які значення необхідно підставляти.
Рис. 27 Використання таблиці підстановки під час розрахунків за двома параметрами
У цьому прикладі підстановка значень відсоткової ставки (стовпець вихідних значень В10: В14) відбувається в осередок В 4, т. к. у цьому осередку спочатку вказується величина відсоткової ставки, що входить у формулу, що розраховується, а підстановка значень термінів погашення (рядок значень C9: F9) - в осередок ВЗ. Результати обчислень будуть поміщені в незаповнені осередки виділеного діапазону (28).
Рис. 28. Розраховані дані з використанням двовимірної таблиці підстановки
Зауваження:Після побудови таблиці підстановки не можна редагувати окрему формулу всередині таблиці. Значення даних усередині таблиці можна змінити, змінюючи значення вихідних даних у лівому стовпці та верхньому рядку.
Вихідні дані: витрати за проектом становитимуть 700 млн руб. Очікувані доходи протягом наступних 5 років становитимуть, відповідно, 70, 90,300,250, 300 млн руб. Оцінити економічну
доцільність проекту за швидкістю обороту інвестиції, якщо ринкова норма доходу 12%. Розглянути також такі варіанти (витрати на проект представлені числом зі знаком мінус):
600; 50;100; 200; 200; 300;
650; 90;120;200;250; 250;
500, 100,100, 200, 250, 250.
Рис. 29 Вікно Диспетчер сценаріїв
Рішення
Для обчислення внутрішньої швидкості обороту інвестиції (внутрішньої норми доходності) використовується функція ВСД.
ВСД (Значення; Припущення)
У нашому випадку функція для вирішення задачі використовує лише аргумент Значення, один з яких обов'язково негативний (витрати на проект). Якщо внутрішня швидкість обороту інвестицій виявиться більшою за ринкову норму прибутковості, то проект вважається економічно доцільним. Інакше проект має бути відкинуто.
1. Рішення наведено на рис. 30. Формули для розрахунку:
в осередку ОБ 11:= ВСД (В75: В80)
в осередку С11:=якщо(В84>В82; "Проект економічно доцільний"; "Проект необхідно відкинути")
Рис. 30. Розрахунок внутрішньої швидкості обороту інвестицій
2. Розглянемо цей приклад всім комбінацій вихідних даних. Для створення сценарію слід використати команду Сервіс | Сценарії| кнопка Додати(Рис. 31).
Рис. 31. Додавання сценарію для першої комбінації вихідних даних
Після натискання на кнопку ОК з'являється можливість внесення нових значень для осередків, що змінюються (рис. 32).
Рис. 32. Вікно для зміни значень осередків сценарію
Для збереження результатів за першим сценарієм немає необхідності редагувати значення осередків-досить натиснути кнопку ОК для підтвердження значень, що з'явилися за замовчуванням, та виходу у вікно Диспетчер сценаріїв(Рис . 33).
Рис. 33 Вікно Диспетчер сценаріївз першим збереженим сценарієм
3. Для додавання до розглянутої задачі нових сценаріїв достатньо натиснути кнопку Додатиу вікні Диспетчер сценаріїві повторити вищеописані дії, змінивши значення у осередках вихідних даних (рис. 34).
На рис. 34 сценарій Швидкість_обороту_1 відповідає даними (-700; 70; 90; 300; 250; 300), сценарій Швидкість_обороту_2 - даними (-600; 50; 100; 200; 200; 300), сценарій Швидкість_0; ; 200; 250; 250), сценарій швидкість_оборота_4-даним (-500, 100, 100, 200,
Натиснувши кнопку Вивести,можна переглянути на робочому аркуші результати розрахунку відповідної комбінації вихідних значень.
Рис. 34. Вікно Диспетчер сценаріївз доданими сценаріями
з розрахунку швидкості обороту інвестицій
Рис. 4.31. Додавання осередків результату у вікно Звіт за сценарієм
4. Для отримання підсумкового звіту за всіма доданими сценаріями слід натиснути кнопку Звіту вікні диспетчер сценаріїв. У вікні, що з'явилося Звіт за сценарієм(рис. 35) вибрати необхідний тип звіту та дати посилання на осередки, в яких обчислюються результуючі функції. При натисканні на кнопку ОК на відповідний аркуш робочої книги виводиться звіт за сценаріями (рис. 36 та рис. 37)
Рис. 36 Звіт типу Структура
Рис. 37 Звіт типу Зведена таблицяза сценаріями розрахунку швидкості обороту інвестицій
Рішення
Для виконання завдання:
1. Введіть дані на робочий лист відповідно до рис. 38.
Рис.38 Підготовка даних для побудови діаграми
2. Виділіть мишею діапазон А5: В12та виконайте команду Вставлення | Діаграмаабо натисніть кнопку майстра діаграм на панелі інструментів Стандартна.
Вибір виду діаграми - з огляду на те, що у прикладі запропоновані дискретні статистичні дані, відповідний вид діаграми - гісторамного типу.
Який ряд вибраний як значення даних (заповніть поля Ряді значення).У нашому випадку у полі Рядвносимо обсяг та у поле Значення- діапазон $В$5: $В$12;
D який ряд буде служити підписами по осі X- у полі Підписи по осі Xвносимо рік і вказуємо діапазон $А$5:$А$12.
Рис. 39. Визначення рядів даних
4. Відформатуйте отриману діаграму, використовуючи контекстне меню кожного її елемента (рис. 40).
Рис. 40. Побудована діаграма Обсяг продажів
Завдання 2.Побудувати графік функції: у= cos 3 (πx).
Рішення
Результат цього прикладу представлений на рис. 41.
Для виконання завдання:
1. Вкажіть область визначення Xвведенням початкових даних: 0 і 0,1, а потім маркером автозаповнення підготуйте весь діапазон А7: А27.
2. У осередок О 7введіть формулу:
=(СОЗ(ПІ()*А7))^3та скопіюйте її на діапазон В7:В27.
3. Побудуйте графік функції за допомогою майстра діаграм.
4. Відформатуйте отриманий графік.
Рис. 41 Приклад побудови графіка функції
Рішення
А1: J35, A1:J1)
2. Сформуйте діапазон критеріїв для розширеного фільтра відповідно до рис. 43.
Рис. 43. Діапазон критеріїв для розширеного фільтра до задачі про білі та чорні машини
3. Виконайте команду
4. Відфільтровані дані наведено на рис. 44.
Рис. 44. Дані до завдання про білі та чорні машини, відібрані розширеним фільтром
Завдання 2.Визначити, чи є у списку (див. рис. 42) машини, рік випуску яких більше 2000 і пробіг яких понад 100 00 км, але менше 100 000 км, або чорні Мерседеси, ціна яких понад 20 000 у. е., але менше 30 000 у. тобто.
Рішення
1. Відкрийте список, що підлягає фільтрації (список розташований у діапазоні А1:JЗ5,рядок заголовка-в діапазоні A1:J1)
МОЗ: М4. У осередок МОЗвведіть Умову. У осередок М4введіть формулу:
АБО(І(G2>10000;G2)<100000;D2>1990); І (C2 = "Мерседес"; F2 = "Чорний"; H2> 20000; H2<30000))
3. Виконайте команду Дані Фільтр | Розширений фільтр.
4. Відфільтровані дані представлені на рис. 45.
Рис. 45. Дані до завдання про пробіг, відібрані розширеним фільтром
Завдання 3.Визначити автомобілі білого або червоного кольору, ціна яких менша за середню ціну для всіх автомобілів і пробіг яких більший або дорівнює середньому пробігу для всіх автомобілів (див. рис. 42).
Рішення
1. Відкрийте список, що підлягає фільтрації (список розташований у діапазоні А1:JЗ5, рядок заголовка-в діапазоні A1:J1)
2. Сформуйте обчислюваний критерій для розширеного фільтра в діапазоні L1: L2. У осередок L1введіть Умову. У осередок L2введіть формулу:
І(АБО(Г2="білий";Г2="червоний");Н2<СРЗНАЧ($Н$2:$Н$133); G2 >=СРЗНАЧ($G$ 2:$G$13 3))
3. Виконайте команду Дані Фільтр | Розширений фільтр.
Рішення
1. Виділіть список (або - встановіть у список вказівник осередку) та проведіть сортування (команда Дані Сортування)спочатку - по полю Продавець,потім - по полю дата продажу(Рис. 47).
Рис. 46. Список продажів
2. Використовуйте команду Дані Підсумки.У вікні Проміжні висновкивстановіть параметри відповідно до мал. 48: для отримання верхнього (першого) рівня підсумків – загальна кількість товарів, проданих конкретним продавцем.
Рис. 47. Сортування списку
Рис. 48. Вікно Проміжні висновкидля отримання підсумків по полю Продавець
3. Для отримання другого рівня підсумків помістіть покажчик осередку до списку з отриманими підсумками, потім виконайте команду Дані Підсумки,встановивши у вікні Проміжні висновкипараметри відповідно до рис. 49.
Рис.49. Вікно Проміжні висновкидля отримання підсумків по полю дата продажу
4. Отримані проміжні результати представлені на рис. 50.
Рис. 50.Вкладені проміжні підсумки
Консолідація даних
Консолідація призначається узагальнення однорідних даних. Її здійснення передбачає використання наступної методики:
1. Вказати розташування майбутніх консолідованих даних.
2. Вибрати команду Дані Консолідація.
3. У вікні вказати діапазони даних, що підлягають консолідації.
4. Вказати спосіб консолідації:
Відповідно до розташування в діапазоні - знято всі прапорці області Використовувати як імена;
Відповідно до заголовків рядків та стовпців-встановлені
прапорці підписи верхнього рядкаі значення лівого стовпця.
5. Вибрати тип консолідації, тобто вказати, яка операція буде проводитися з консолідованими даними.
6. За необхідності вказати додавання структури - встановити прапорець Створювати зв'язки із вихідними даними.
Завдання 1.Об'єднати дані про кількість та вартість проданих товарів у мережі магазинів, які представлені у вигляді списку з наступними полями (рис. 51): Товар, Вартість, Кількість,розташовані на аркуші 2, аркуші 4 і аркуші 5.
Рис. 51.Дані про реалізовані товари
Консолідаціявідповідно до даних рис. 52. Об'єднані дані подано на рис. 53.
Зведені таблиці
Зведені таблиці є засіб для групування, узагальнення та аналізу даних, що у списках MS Excel чи таблицях, створених інших додатках. Зовні зведені
Рис. 52 . Введення даних у вікно Консолідація
Рис. 53. Подання консолідованих даних
Зведені таблиці
Зведені таблиці є засіб для групування, узагальнення та аналізу даних, що у списках MS Excel або таблицях, що у інших додатках. Зовнішні зведені таблиці є структурою, що дозволяє розміщувати дані тривимірному вигляді. Зведені таблиці можуть використовуватись:
Для узагальнення великої кількості однотипних даних;
Для реорганізації даних (за допомогою перетягування);
Для відбору та угруповання даних;
Для побудови діаграм.
Зведені таблиці створюються за допомогою майстра зведених таблиць (команда Дані Зведена таблиця)за наступною методикою:
1. Вибрати місце для зведеної таблиці, тобто встановити покажчик осередку в потрібне місце на робочому аркуші.
2. Виконати команду Дані| Зведена таблиця.
3. Задати вихідний діапазон даних, виконавши кроки 1 та 2 майстри (рис. 54 та 55). Після натискання кнопки Далі у вікні майстра, наведеному на рис. 55 відкриється вікно 3-го кроку майстра (рис. 56).
4. Перш ніж вказати розташування майбутньої таблиці (рис. 56), необхідно натиснути кнопку Макеті у вікні (рис. 57) сформувати макет зведеної таблиці (тобто задати сторінку, рядки, стовпці, підсумкові та обчислювані поля зведеної таблиці).
Рис. 54Визначення розташування даних для зведеної таблиці
Рис. 55.Діапазон даних для зведеної таблиці
Рис. 56.Вказівка розташування майбутньої зведеної таблиці
5. Для визначення необхідної операції для полів, розміщених в області Даніабо завдання поля, що обчислюється, двічі клацнути лівою кнопкою миші на полі, поміщеному в область Дані(рис. 57), та вибрати необхідні дії у вікні Обчислення поля зведеної таблиці(Рис. 58).
6. Натиснути кнопку Параметри(рис. 56) і у вікні (мал. 59) встановити необхідні параметри зведеної таблиці.
Рис. 57. Формування макета зведеної таблиці
Рис. 58. Вікно Обчислення поля зведеної таблиці
Рис. 59 . Встановлення параметрів зведеної таблиці
7. Після всіх підготовчих операцій натиснути кнопку Готово(Рис. 56).
Під час створення, редагування та роботи зі зведеними таблицями необхідно враховувати наступне:
Розташування зведеної таблиці - бажано розташовувати її на окремому аркуші, т. к. при оновленні, угрупованнях зведеної таблиці інформація, що полягає на робочих аркушах поруч зі зведеною таблицею, може бути прихованою;
Місцезнаходження вихідних даних - список MS Excel, зовнішнє джерело, діапазони консолідації, що знаходяться в іншій зведеній таблиці;
Необхідність при створенні структури зведеної таблиці визначити:
1) поля, що знаходяться в рядках та стовпцях таблиці;
2) поля, за якими підбиваються підсумки (з вибором необхідної операції);
3) поля для сторінок, що дозволяє подати інформацію у тривимірному вигляді.
Зведена таблиця – це лише відображення даних. Тож у самій таблиці дані редагувати не можна. Для зміни даних у зведеній таблиці необхідно внести зміни до джерела даних, а потім оновити зведену (кнопкою Оновити даніна панелі інструментів Зведені таблиці(Рис. 60);
Рис. 60. Панель інструментів Зведені таблиці
У зведених таблицях можна змінювати назви полів, що не тягне змін у полях вихідних даних. Маніпулювання елементами зведеної таблиці можна здійснювати мишею.
Шаталова Л.М.
Лабораторна робота №8
«Обчислення вEXCEL. Використання функцій»
Мета роботи -вивчити можливості стандартних функцій програми Excel та набути практичних навичок використання механізму введення аргументів функцій на прикладі логічних функцій, функцій посилань та масивів, а також деяких фінансових функцій.
Програма Excel має понад 300 вбудованих функцій, які можна використовувати як окремо, так і у складі формули. Будь-яка функція – це заздалегідь створена формула, яка виконує певні операції. Для введення функцій використовуються елементи керування розділу бібліотеки функційвкладки ФормулиСТРІЧКИ. При виборі пункту "вставити функцію" (Shift + F3) з'являється вікно "Майстра функцій" крок 1, в якому виконується вибір конкретної функції (тут є можливість отримати довідку вибраної функції). Після натискання кнопки Ok з'являється вікно кроку 2, у якому заповнюються рядки з аргументами. Справа при цьому відображаються введені дані, а наприкінці – результат. На цьому етапі можна виконати роботу над помилками введення. Функція має такий формат:
Ім'я функції
> (Аргумент 1
;
Аргумент 2
;…;
Аргумент
N
)
,
де “;” -Розділювач. Розділювач"точка з комою" визначається Windows.
Аргумент– це: 1) Число або текст, 2) посилання на адресу осередку, 3) Діапазон осередків, 4) Арифметичний вираз (наприклад, А7/А10*35), 5) Інша функція
Програма Excel містить такі категорії стандартних функцій:
Фінансові, Дата та час, Математичні, Статистичні, Посилання та масиви, Робота з базою даних, Текстові, Логічні, Перевірка властивостей та значень, Інженерні, Аналітичні.
ВСТУП
1.ТЕХНІЧНЕ ОПІВ ЗАДАЧІ
1.1 Переваги та недоліки програмного продукту
1.3 Алгоритм установки Excel
1.4 Актуальність теми
1.ТЕХНОЛОГІЧНИЙ ОПИС
2.1 Формули
2.2 Порядок уведення формул
2.3 Відносні, абсолютні та змішані посилання
2.5 Копіювання формул
2.7 Перегляд залежностей
2.8 Редагування формул
2.9 Функції Excel
2.10 Автообчислення підсумкових функцій
2.12 Вибір функцій, що недавно використовувалися
3. ТЕХНІКА БЕЗПЕКИ
3.1 Вимоги до приміщення для експлуатації комп'ютера
3.2 Вимоги до організації та обладнання робочих місць
3.3 Санітарно-гігієнічні норми роботи на ПЕОМ
ВИСНОВОК
ПЕРЕЛІК СКОРОЧЕНЬ
Список літератури
ДОДАТКИ
ВСТУП
Що можна сказати про сучасні програми обробки електронних таблиць? Сьогодні це засоби, що дозволяють не тільки виконувати обчислення, а й керувати списками та створювати діаграми. Якщо ж говорити про EXCEL, яка є однією з найвідоміших програм обробки електронних таблиць, то без перебільшення можна стверджувати, що її можливості практично невичерпні. Особисто я вважаю, що такі програми на сьогоднішній день є одним з найбільш потужних і гнучких інструментів, створених для комп'ютера.
1.1 Переваги та недоліки програмного продукту
Для того, щоб краще розуміти причини, з яких варто займатися модифікацією електронних таблиць (на зразок Excel), я наводжу огляд основних переваг та недоліків.
Переваги
· Реалізація алгоритмів у табличному процесорі не вимагає спеціальних знань у галузі програмування.
· Програма в табличному процесорі оперує поняттями простору та відносин, оскільки створюється шляхом завдання взаємозв'язку осередків, розташованих у просторі листа.
· На відміну від звичайного програмування, що вимагає суворої послідовності команд для роботи програми, табличні процесори "прощають" помилки та незакінченість структури.
· Обчислення результату в табличному процесорі може бути розбите на необхідну кількість кроків, кожен з яких може бути визначений через формулу у своєму осередку.
· Осередки таблиці можуть містити як формули, а й простий текст, що дозволять описувати і коментувати логіку роботи програми, розташовуючи на аркуші текстові коментарі.
· Весь процес обчислення здійснюється у вигляді таблиць,
· Сучасні табличні процесори дозволяють використовувати засоби оформлення, такі як, наприклад, колір та шрифти, які полегшують розуміння програми та несуть додаткове смислове навантаження.
Недоліки
· Формули, виражені в термінах адрес осередків, при їх великій кількості становлять велику проблему, так як адреси осередків самі по собі не несуть жодного смислового навантаження.
· Реалізація складної структури в рамках електронної таблиці вимагає величезної уваги до деталей, так як автор програми з деякого моменту стає не в змозі запам'ятати сенс безлічі адрес, що зустрічаються у сотнях формул.
· Програми в табличних процесорах сильно залежні від розмірності і жорстко прив'язані до сітки.
· Темпи розробки табличних програм значно знижуються рахунок того, що розробнику доводиться працювати на рівні осередків.
· Користувач, який має доступ до таблиці, може випадково або навмисно внести зміни, які можуть порушити роботу програми.
· Нестача контролю за виправленнями підвищує ризик помилок, що виникають через неможливість відстежити, протестувати та ізолювати зміни.
1.2 Вимоги до апаратних та програмних засобів
· Персональний комп'ютер із процесором Pentium 100 МГц або потужнішим.
· Операційна система Microsoft Windows 95 або пізнішої версії або Microsoft Windows NTWorkstation версії 4.0 з пакетом оновлень 3 або пізнішим.
· Оперативна пам'ять:
· 16 Мбайт пам'яті – для операційної системи Windows 95 або Windows 98 (Windows 2000). 32 Мбайт пам'яті – для операційної системи WindowsNTWorkstation версії 4.0 або пізнішої.
1.3 Алгоритм установки Excel
Exсel – досить популярна програма, що полегшує роботу з цифрами та таблицями, а також дозволяє проводити аналіз досить великих обсягів інформації. Програма входить до пакета Microsoft Office. Її можна придбати на диску або завантажити з офіційного веб-сайту компанії Microsoft.
Щоб встановити Excel правильно, потрібно скористатися автоматичною установкою всього пакета Microsoft Office. Якщо його компоненти вже встановлені (наприклад, при вибірковій установці інших програм), або ж користувач через різні причини встановлює лише програму Exсel, тоді потрібно в процесі установки прибрати всі галочки навпроти встановлених раніше програм, залишивши її лише напроти установки Exсel (рисунок 1.1. )
Потім з'явиться діалогове вікно, яке повідомить Вам, що встановлення успішно завершено (рис. 1.3)
|
Ймовірно, Excel - це другий за запитом компонент Microsoft Office після програми Word.
У міру інформатизації суспільства табличні процесори перестають використовуватися тільки для бухгалтерських та фінансових завдань і стають інструментами аналізу та візуалізації найрізноманітніших даних. Будучи найпоширенішим табличним процесором, Excel має величезну аудиторію користувачів, дуже неоднорідну за складом. Тому якщо досвідченим спеціалістам не вистачає існуючих можливостей, то новоосвоюючим продукт не вистачає функцій, що спрощують оволодіння елементарними операціями.
У зв'язку з цим розвиток продукту ставить перед розробниками досить складне завдання: "ускладнення" для однієї категорії користувачів та "спрощення" - для іншої. За свідченням розробників, основна увага в Excel 2003 якраз і була спрямована на спрощення роботи з програмою, на оптимізацію доступу до різноманітних інформації та одночасно на розвиток ефективності роботи та додавання нових можливостей.
Додалося безліч нових функцій, у той же час деякі зайві попередження були видалені. Завдяки новим функціям спростилося вирішення низки завдань. На думку ряду фахівців, з усіх програм Office XP найбільше аргументів на користь оновлення дає саме Excel 2003.
Формули - це вираз, що починається зі знака рівності і складається з числових величин, адрес осередків, функцій, імен, які з'єднані знаками арифметичних операцій. До знаків арифметичних операцій, що використовуються в Excel, належать: додавання; віднімання; множення; розподіл; зведення в ступінь.
Деякі операції у формулі мають вищий пріоритет і виконуються в такій послідовності:
зведення у ступінь та вираження у дужках;
множення та розподіл;
складання та віднімання.
Результатом виконання формули є значення, яке виводиться у осередку, а сама формула відображається у рядку формул. Якщо значення у комірках, на які є посилання у формулах, змінюються, то результат зміниться автоматично.
2.2 Порядок уведення формул
Вводити формулу треба зі знаку рівності. Це треба для того, щоб Excel зрозумів, що в комірку вводиться саме формула, а не дані.
Виділимо довільний осередок, наприклад А1. У рядку формул введемо =2+3 та натиснемо Enter. У осередку з'явиться результат (5). На рядку формул залишиться сама формула (рисунок 2.1.)
Малюнок 2.1. Результат формули
Доцільно користуватися дужками під час написання формули. У цьому випадку ви убезпечите себе від випадкової помилки у обчисленнях з одного боку, а з іншого – дужки значно полегшують читання та аналіз формул. Якщо у формулі кількість дужок, що закривають і відкривають, не збігається, Excel видасть повідомлення про помилку і запропонує варіант її виправлення. Відразу після введення дужки Excel відображає жирним шрифтом (або іншим кольором) останню пару дужок, що дуже зручно за наявності у формулі великої кількості дужок.
Введіть у комірку А1 число 10, а в комірку А2 - число 15. У комірці А3 введіть формулу =А1+А2. У осередку А3 з'явиться сума осередків А1 та А2 - 25. Поміняйте значення осередків А1 та А2 (але не А3!). Після зміни значень у осередках А1 і А2 автоматично перераховується значення осередку А3 (згідно з формулою) (рисунок 2.2.)
2.4 Використання тексту у формулах
З текстовими значеннями можна виконувати математичні операції, якщо текстові значення містять лише такі символи:
Цифри від 0 до 9, + - е Е/
Ще можна використовувати п'ять символів числового форматування:
$ % () пробіл
При цьому текст повинен бути укладений у подвійні лапки.
Неправильно : =$55+$33
Правильно : ="$55"+$«33»
При виконанні обчислень Excel перетворює числовий текст на числові значення, так результатом вищезазначеної формули буде значення 88.
Для об'єднання текстових значень є текстовий оператор & (амперсанд). Наприклад, якщо осередок А1 містить текстове значення «Юрій», а осередок А2 - «Кордик», то ввівши в осередок А3 наступну формулу =А1&А2, отримаємо «ЮрійКордик». Для вставки пробілу між ім'ям та прізвищем треба написати так =А1&" "&А2. Амперсанд можна використовувати для об'єднання осередків із різними типами даних. Так, якщо в осередку А1 знаходиться число 10, а в осередку А2 - текст «мішків», то в результаті дії формули = А1 & А2 ми отримаємо «10 мішків». Причому, результатом такого об'єднання буде текстове значення.
Копіювання формули в суміжні осередки проводиться шляхом автозаповнення, тобто. протягуванням маркера заповнення осередку з формулою на сусідні осередки (стовпцем або рядком). Це найзручніший і найшвидший спосіб копіювання.
Інші способи копіювання формул:
1. виділити діапазон для заповнення (включаючи комірку з введеною формулою) та виконати команду меню ПравкаЗаповнитиВниз(якщо копіювання виконується по стовпцю).
1. протягнути маркер заповнення осередку з формулою правою кнопкою миші, у контекстному меню, що з'явилося, вибрати потрібну команду
1.1. копіювати осередки;
2.6 Імена осередків для абсолютної адресації
Будь-якому осередку (діапазону) можна привласнити ім'яі надалі використовувати його у формулах замість адреси комірки. Іменовані осередки завжди мають абсолютну адресацію.
Ім'я осередку не повинне починатися з цифри; не можна використовувати в імені пробіли, знаки пунктуації та арифметичні знаки. Не можна також давати ім'я, схоже на адресу осередку.
Присвоєння імені поточному осередку (діапазону):
Перший спосіб:
1. клацнути на полі адреси рядки формул, ввести ім'я;
2. натиснути клавішу
Другий спосіб:
1. виконати команду Вставка Ім'яПрисвоїти ;
2. у діалоговому вікні ввести ім'я.
Це ж діалогове вікно можна використовувати для видалення імені, однак слід мати на увазі, що якщо ім'я вже використовувалося у формулах, його видалення викличе помилку (повідомлення – «# ім'я?»)
програмний формула електронна таблиця
Команда меню Сервіс Залежність формулдозволяє побачити на екрані зв'язок між осередками.
Для перегляду осередків, що впливають, потрібно зробити поточний осередок з формулою і виконати команду Сервіс Залежності формулВпливають осередки .
Якщо потрібно побачити, в якій формулі є посилання на поточний осередок, слід виконати команду Сервіс Залежності формулЗалежні осередки .
Усі залежності у таблиці зображаються стрілками. Для видалення стрілок служить команда Сервіс Залежності формулПрибрати всі стрілки .
За необхідності перегляду багатьох залежностей зручно відобразити панель інструментів Залежності командою Сервіс Залежності формулПанель залежностей .
2.8 Редагування формул
Для редагування формули потрібно виконати клацання у рядку формул або двічі клацнути в осередку, що містить формулу. Під час редагування можна змінити адресу комірки, на яку є посилання, тип посилання та ін.
· Виділити в рядку формул адресу осередку подвійним клацанням;
· Відокремити в таблиці осередок, на яку має бути посилання.
Зміна типу адресації:
· Виділити адресу осередку подвійним клацанням;
· натиснути клавішу
Для підтвердження внесених змін використовувати клавішу
2.9 Функції Excel
Функції Excel використовуються для виконання стандартних обчислень у робочих книгах. Значення, що використовуються для обчислення функцій, називають аргументами. Значення, що повертаються функціями як відповідь, називаються результатами.
Крім вбудованих функцій ви можете використовувати в обчисленнях функції, які створюються за допомогою засобів Excel.
Щоб використовувати функцію, потрібно ввести її як частину формули в комірку робочого листа. Послідовність, в якій повинні розташовуватися символи, що використовуються у формулі, називається синтаксисом функції. Усі функції використовують однакові основні правила синтаксису. Якщо ви порушите правила синтаксису, Excel видасть повідомлення про те, що у формулі є помилка.
Якщо функція з'являється на початку формули, їй повинен передувати знак рівності, як і в будь-якій іншій формулі.
Аргументи функції записуються в круглих дужках відразу за назвою функції та відокремлюються один від одного символом крапка з комою “ ; ”. Дужки дозволяють Excel визначити, де починається і закінчується список аргументів. Усередині дужок повинні бути аргументи. Пам'ятайте про те, що при записі функції повинні бути відкриті і закриває дужки, при цьому не слід вставляти пробіли між назвою функції і дужками.
Як аргументи можна використовувати числа, текст, логічні значення, масиви, значення помилок або посилання. Аргументи може бути як константами, і формулами. У свою чергу, ці формули можуть містити інші функції. Функції, що є аргументом іншої функції, називають вкладеними. У формулах Excel можна використовувати до семи рівнів вкладеності функцій.
2.10 Автообчислення підсумкових функцій
У Excel автоматизований виклик функцій, що обчислюють підсумкові значення в діапазонах осередків з числовими даними, такі функції вибирають зі списку, що розкривається, кнопки Автосуммав панелі інструментів (таблиця 2.2.)
Таблиця 2.2. Підсумкові функції
2.11 Використання Майстра функцій
Для виклику Майстра функцій призначено кнопку Вставлення функціїу рядку формул або панелі інструментів.
Примітка. Майстер функцій можна також викликати:
· у списку кнопки Автосума(Пункт Інші функції ...);
· командою меню ВставкаФункція ;
· Комбінацією клавіш<Shift > + <F3 >.
Діалогове вікно Майстри функцій(рисунок 2.4.) містить два списки: список, що розкривається Категоріяі список функцій. Якщо вибрано категорію, відображається відповідний список функцій.
При виборі функції у нижній частині вікна з'являється її короткий опис. Після клацання на кнопці Ok(або натискання клавіші<Enter>) ім'я обраної функції заноситься в рядок формул разом із дужками, що обмежують список аргументів, і одночасно відкривається вікно Аргументи функції .
Приклад такого вікна функції показано малюнку 2.5.
3.1 Вимоги до приміщення для експлуатації комп'ютера
1. Приміщення повинно мати штучне та природне освітлення.
2. У приміщенні мають бути вбудовані або пристінні шафи (полиці) для зберігання сумок, портфелів учнів.
3. Приміщення не повинно межувати з приміщеннями, у яких рівні шуму та вібрації перевищують нормовані значення.
4. Приміщення має обладнатися системами опалення, кондиціонерами та вентиляційними отворами.
5. Для внутрішньої обробки інтер'єру приміщень, у приміщенні повинні використовуватися дифузно-відбивні матеріали з коефіцієнтом відбиття від стелі – 0,7-0,8; для стін – 0,5-0,6; для підлоги – 0,3-0,5.
1. Площа на одне робоче місце у всіх навчальних закладах повинна становити не менше ніж 6,0 квадратних метрів, а обсяг не менше ніж 20,0 кубічних метрів.
2. Перед тим, як сісти за робоче місце, необхідно провести зовнішній огляд, переконатися у використанні з'єднувальних кабелів.
3. Не слід піддавати поштовхам та вібраціям системний блок та інші частини комп'ютера.
4. Екран повинен знаходитись на відстані від очей – 40-50 см.
5. Не торкайтеся струмовідних частин комп'ютера.
6. Необхідно дотримуватися режиму роботи за комп'ютером, 40-50 хвилин безперервної роботи та 5-10 хвилин перерви. Якщо під час роботи сильно втомлюються очі, необхідно періодично відводити погляд від екрану на будь-яку дальню точку приміщення.
1. Періодично перед роботою протирати монітор спеціальною тканиною.
2. Не допускайте потрапляння пилу та рідини на клавіатуру комп'ютера та дискету та інші частини комп'ютера.
3. На робочому місці (за комп'ютером) не слід вживати їжу та воду.
4. У приміщенні має щодня проводитися вологе прибирання та по можливості провітрювання.
5. Учні обов'язково повинні мати при собі змінне взуття та білі халати
Знання формул та функцій та вміння працювати з ними є основним аспектом успішної та приємної роботи в Excel, інакше прості обчислення у програмі будуть витрачати багато часу та сил.
В даний час, табличний процесор MSExcel, є найзручнішим і найпоширенішим серед аналогічних програм, і я з цим повністю згодна.
АС – автоматизована система
КС – комп'ютерна система
ОС – операційна система
ЕОМ - електронно-обчислювальна машина
ПК – персональний комп'ютер
1. Біліг В.А., Дехтяр М.І. VBA та Office ХР. Офісне програмування. -М.: Російська редакція, 2004. -693 с.
2. Гарнаєв А. Використання MS Excel та VBA в економіці та фінансах. -СПб.: БХВ-Петербург, 2002. -420 с.
6. Інформатика: підручник. Курносов А.П., Кулев С.А., Улезько А.В., Камалян А.К., Чернігін А.С., Ломакін С.В.: за ред. А.П. Курносова Воронеж, ВДАУ, 1997. -238 з.
7. Інформатика: Підручник. / За ред. Н.В. Макарової - М.: Фінанси та статистика, 2002. -768 с.
8. Пакети прикладних програм: Навч. посібник для середовищ, проф. освіти / Е. В. Фуфаєв, Л. І. Фуфаєва. -М: Видавничий центр «Академія», 2004. -352 с.
9. Колесніков Р. Excel 97 (русифікована версія). – Київ: Видавнича група BHV, 1997
Додаток 1
Введення чисел
Додаток 2
Використання формули "Автосума"
