Сдам Сам

ПОЛЕЗНОЕ


КАТЕГОРИИ







МЕТОДИЧНІ МАТЕРІАЛИ З ВИКОРИСТАННЯ MS EXCEL





МЕТОДИЧНІ МАТЕРІАЛИ З ВИКОРИСТАННЯ MS EXCEL

ДЛЯ РОЗВ’ЯЗУВАННЯ ЗАДАЧ ЕКОНОМІЧНОГО ХАРАКТЕРУ

 

Викладач: Білоока Т.П.

 

2015 рік

ЗМІСТ

ВСТУП
   
Практична робота№1 Розв’язування задач економічного характеру за допомогою електронних таблиць Exсel.    
   
Практична робота№2 Використання логічних функцій при маркетингових дослідженнях. Розрахунок вартості послуг по перевезенню.    
   
Практична робота№3 Розрахунок податкових платежів та чистої заробітної плати з використанням логічних функцій та майстра збору даних „Консолідація”  
   
Практична робота№4 Microsoft Excel – створення та редагування зведених таблиць.  
   
Практична робота№5 Розрахунок вартості замовлення з використанням елементів управління  
   
Практична робота№6 Сервіс Microsoft Excel “Пошук рішення.  
   
Практична робота№7 Складання оптимального графіку роботи персоналу підприємства з використанням процедури “Пошук розв’язання”    
   
РЕКОМЕНДОВАНА ЛІТЕРАТУРА

Вступ.

Одним із головних напрямків розвитку країн світу є створення інформаційного суспільства.

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

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

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



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

· визначити тип і формати полів таблиці, що створюється;

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

· створити формули для полів таблиці, що обчислюються. Такими є поля, які позначені символом *. Використати при створенні формул логічні та статистичні функції (там де це потрібно). Використати копіювання формул;

· відформатувати таблицю, використовуючи різні варіанти обрамлення, вирівнювання, колірного оформлення і інші можливості. Роздрукувати готову таблицю;

· побудувати діаграми за допомогою Майстра діаграм. Тип діаграми повинен відповідати меті аналізу даних. Отформатувати діаграму. Роздрукувати діаграму.

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

· вміти використовувати консолідацію, елементи управління, пошук рішення, тощо.

Далі наведено рекомендації щодо застосування інтегрованого пакету прикладних програм табличного процесора Microsoft Excel для вирішення економічних задач на сучасних персональних комп’ютерах. Ці рекомендації будуть корисні студентам у процесі самостійного вивчення розділу “Електронні таблиці” та у виконанні практичних і лабораторних робіт.


Практична робота№1

Тема: Розв’язування задач економічного характеру за допомогою електронних таблиць Exсel.

Мета:Вдосконаленняроботи з електронними таблицями Exсel.

Хід роботи:

Завдання 1.

1. Створити електронну таблицю.

Найменування тканини Поступило (м.) Собівартість (грн./м.) Продано (м.) Відпускна ціна (грн./м.)
Льон - меланж 120,00 24,00 грн. 87,00 28,00 грн.
Кристалон 170,00 28,00 грн. 93,00 33,00 грн.
Льон "Веселка" 130,00 26,00 грн. 98,00 31,00 грн.
Болонья 130,00 24,00 грн. 57,00 28,00 грн.
Поплин 190,00 13,00 грн. 142,00 15,00 грн.
Пальтова 120,00 89,00 грн. 68,00 105,00 грн.
Титаник - стрейч 210,00 37,00 грн. 180,00 43,00 грн.
Штапель 210,00 10,00 грн. 160,00 12,00 грн.
Кристалон 170,00 28,00 грн. 93,00 33,00 грн.
Букле 190,00 30,00 грн. 97,00 35,00 грн.

Установити наступний формат чарунок для стовпців:

“№ п/п ” – Числовой ;"Дата" – Дата;

"Найменування тканини" – Текстовый; "Поступило" – Числовой;

"Собівартість" – Денежный; “Продано” – Числовой;

"Відпускна ціна" – Денежный

Перейменувати робочий лист Лист1 на Ваше прізвище.

2. Додати перший стовпець, і ввести заголовок "№ п/п" ( розташування тексту – вертикальне (ориентация +90о), вирівнювання по вертикалі - по центру).

Після стовпця № п/п добавте стовпець Дата надходження (дати надходження введете за своїм розсудом).

Додати вгору шість рядки. У 3 та 4 рядках уведіть назву таблиці (назву вибрати самостійно), наприклад, у першому рядку “Рахунок - фактура”, у другому – “на реалізацію товарів магазина “Флекс”. Колір шрифту для тексту установити - червоний, фон – голубий.

Об'єднати чарунки з назвою таблиці спочатку першого, потім другого рядків окремо.

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

3. Помістити в комірки другого та третього стовпців над заголовком таблиці поточну дату й час за допомогою функції СЕГОДНЯ() і ТДАТА().

4. Зробити форматування таблиці.

Встановити ширину стовпців таблиці відповідно кількості символів.

5. Додати в кінець таблиці нове поле Залишки, де визначити залишки кожного товару , як різницю полів Поступило та Продано.

6. Додати в кінець таблиці ще два поля Ціна1 та Ціна2. Значення поля Ціна1 визначити як добуток між Собівартість та Продано, значення поля Ціна2 визначити як добуток між Відпускна ціна та Продано .

7. Додати в кінець таблиці поле Податок. Підрахувати податок як різницю від значень полів Ціна1 та Ціна2 поділену на шість. (Ціна2-Ціна1)/6

8. Додати в кінець таблиці поле Прибуток. Визначити отриманий прибуток від реалізації по кожному найменуванню товару, як різницю між значеннями полів Ціна2 та Ціна1 та Податок.

9. Додати в кінець таблиці поле ЗАКАЗ. Якщо значення залишків по кожному найменуванню товару у полі Залишки менше п’ятдесяти метрів, то напроти цього товару написати слово “Заказати”, а якщо більше - написати слово “Ні”. Це здійснити за допомогою функції ЕСЛИ. У вікні функції ЕСЛИ правильно записати потрібну умову та значення істини та неправди. Рядок формул при цьому має набути, наприклад, такий вигляд: =ЕСЛИ(H7<=50;”Заказать”;”Нет”). Це у тому випадку, коли поле Остатки знаходиться у стовпці H, а перше значення цього поля у 7-му рядку.

10. Визначити товар, від реалізації якого отриманий максимальний прибуток, використовуючи умовне форматування. Для цього попередньо виділити потрібний діапазон (тобто діапазон значень поля Прибуток) і вибрати в меню Формат – Условное форматирование. У вікні Условное форматирование з першого списку вибрати значение, з другого - равно, а у третьому полі ввести =МАКС(діапазон) і виділити необхідний діапазон комірок, який після виділення автоматично запишеться в дужках. Потім клацнути кнопку Формат…і у вікні Формат Ячеек вибрати вкладку Вид, де задати колір заливання комірки з максимальним значенням прибутку.

11. Визначити сумарні витрати на виробництво всієї продукції, що надійшла, використовуючи функцію СУММПРОИЗВ. Значення помістити над назвою таблиці над полем Собівартість й додати відповідну примітку. Для проведення обчислень у діалоговому вікні Майстер функций у полі Категория вибрати Математические, у полі Функция - СУММПРОИЗВ і правильно заповнити діапазони комірок Массив1 (поле Поступило) і Массив2 ( поле Собівартість).

Додати примітку для комірки “Сумарні витрати”.

Аналогічно визначити сумарні витрати на виробництво продукції, що продалася, використовуючи функцію СУММПРОИЗВ для полів Продано й Собівартість. Значення помістити над назвою таблиці над полем Продано, додати відповідну примітку.

Визначити суму, що надійшла від реалізації продукції, використовуючи функцію СУММПРОИЗВ для полів Продано й Відпускна ціна. Значення помістити над назвою таблиці над полем Відпускна ціна, додати відповідну примітку.

12. Додати в кінець таблиці новий рядок, де визначити підсумкові значення полів (Поступило, Собівартість, Продано, Відпускна ціна і далі).

13. Порівняти сумарне значення поля Ціна1 з сумарними витратами на виробництво продукції, що продалася, які ви підрахували раніше, використовуючи функцію СУММПРОИЗВ для полів Продано й Собівартість. Також порівняти сумарне значення поля Ціна2 зі значенням суми, що надійшла від реалізації продукції, яке ви отримали раніше, використовуючи функцію СУММПРОИЗВ для полів Продано й Відпускна ціна. (значення повинні бути однакові). Зробити заливання комірок з однаковими значеннями одним і тим кольором.

14. Визначити середні значення полів Собівартість і Відпускна ціна. Використати функцію СРЗНАЧ. Значення помістити в таблиці під полями Собівартість і Відпускна ціна, додавши ще один рядок у кінець таблиці.

15. Задати границі таблиці.

Після виконаних дій та розрахунків ваша таблиця має набути наступного вигляду:

Завдання 2.

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

Перейменувати другий лист, давши ім'я –“Таблиця”.

2. Виконати сортування записів у таблиці одним зі способів.

Сортування даних у списках

1 спосіб

Для проведення простого сортування по одному стовпці в списку потрібно:

· Виділити будь-яку комірку у стовпці, по якому варто провести сортування

· Вибрати кнопку Сортировка по возрастанию чи Сортировка по убыванию на панелі інструментів Стандартна

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

Спосіб

Щоб записи відсортувати по декількох стовпцях, виконаєте наступні дії:

· Виберіть будь-яку комірку у списку

· Виберіть Данные-Сортировка. З'явиться діалогове вікно Сортировка диапазона.

· Із списку Сортировать по виберіть заголовок стовпця, за яким буде виконуватися перший етап сортування. Виберіть перемикач по возрастанию чи по убыванию

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

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

 

 

3. Виконайте фільтрацію даних у таблиці.

Практична робота№2

Тема: Використання логічних функцій при маркетингових дослід-женнях. Розрахунок вартості послуг по перевезенню.

Мета: Навчитись використовувати логічні функції.

Хід роботи:

Практична робота№3

Тема: Розрахунок податкових платежів та чистої заробітної плати з використанням логічних функцій та майстра збору даних „Консолідація”

Мета: Навчитись використовувати логічні функції та консолідацію.

Хід роботи:

Умова роботи: Встановлено відповідні оклади співробітникам підрозділу. Необхідно виконати розрахунки загального податку і суму до видачі кожному співробітнику за три місяці і зробити підсумок розрахунків за квартал. Розрахунок податкових платежів базується на системі оподаткування працівників за станом на 01.01.2015 і виконується за допомогою вбудованих у відповідні комірки формул. Формула обчислення податкових платежів будується на основі логічної функції „ЕСЛИ()”.

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

1. Завантажити Excel. Встановити вказівник миші на ярлик аркуша “Лист1” і через контекстне меню перейменувати на “Січень”. Інші аркуші вилучити через контекстне меню „Вилучити”. Зберегти у власній папці книгу з ім’ям “ЗП_Прізвище”.

2. На аркуші „Січень” підготувати бланк нарахування заробітної плати

Для цього потрібно виконати наступні дії:

- об’єднати діапазон клітинок А1:F1 за допомогою кнопки „Об’єднати та розмістити по центру” панелі „Форматування”. Встановити текст напівжирним за допомогою кнопки „Ж”на панелі „Форматування” та ввести текст “Нарахування заробітної плати за січень”.

- змінити ширину стовпців;

- виділити вказівником миші діапазон клітинок A2:F6 і за допомогою кнопки „Межі” на панелі „Форматування” вибрати «Все границы». Аналогічним чином встановити межі для діапазону клітинок D7:F7;

- виділити вказівником миші діапазон клітинок A2:F2 та вибрати меню „Формат” – „Ячейки…” – вкладника „Выравнивание” – „по горизонтали” – „по центру” – „по вертикали” – „по центру” –поставити ü „переносить по словам”;

- в рядок 2 ввести заголовки стовпчиків: в клітинку А2 –„№п/п”, В2 –„ПІБ”, С2 – „Посада”, D2 – „Оклад”, Е2 –„Податкові платежі”, F2 – „До видачі”. В клітинку С7 ввести: „Разом” та вибрати вирівнювання „по правому краю” на панелі „Форматування”;

- ввести в діапазон клітинок А3:А6 порядкові номери. Відцентрувати ці числа;

- ввести інформацію щодо співробітників в діапазон клітинок В3:С6;

- виділити діапазон клітинок D3: F7 та вибрати меню „Формат” – „Ячейки…” – вкладника „Число” – „числовой” – „число десятичных знаков” встановити 2;

- ввести з клавіатури оклади співробітникам (оклад більше та менше 1710 грн.)

- встановити курсор в клітинку Е3 і ввести за допомогою функції ЕСЛИ або використовуючи клавіатуру створити логічну формулу обчислення загального податку:

=ЕСЛИ(D3<=1710;D3*3,6%+(D3-D3*3,6%-609)*15%;D3*3,6%+(D3-D3*3,6%)*15%)

Примітка: Податкові платежі – ЕСВ (3,6%) та ПДФО (15%).

- вибрати клітинку Е3, в рядку формул поставити курсор після = та набрати ОКРУГЛ(поставити курсор в кінці формули, набрати ;2) натиснути на клавіатурі Enter;

- скопіювати дану формулу в діапазон клітинок Е4:Е6;

- встановити курсор у клітинку F3 і ввести формулу =D3-E3. Скопіювати дану формулу в діапазон клітинок F4:F6;

- виділити D3:D7, кнопка „Автосума” з’явиться формула підсумку =СУММ(D3:D6).

Скопіювати дану формулу в діапазон клітинок Е7:F7.

3. Зробити три копії аркушу „Січень”. Для цього потрібно встановити вказівник на ярлик аркуша “Січень” і через контекстне меню виконати команду „Перемистить/Скопировать”. Встановити прапорець „Создавать копию” та натиснути кнопку „ОК”. З’явиться аркуш першої копії.

Аналогічним чином створити ще дві копії аркушу „Січень”. Встановити вказівник на ярлик аркуша „Січень” і, утримуючи ліву кнопку миші, перетягнути його ліворуч на перше місце. Інші ярлики перейменувати (контекстне меню „Переименовать”) відповідно на “Лютий”, “Березень”, “1 квартал”. Послідовно активізувати останні три аркуші та відредагувати текст заголовку бланків відповідно „Нарахування заробітної плати за лютий”, „Нарахування заробітної плати за березень”, „Нарахування заробітної плати за 1квартал” та змінити оклади на нові (за потребою). Автоматично відбудеться перерахунок.

4. Активізувати аркуш „1 квартал”.

- виділити мишею діапазон клітинок D3:F7. Натиснути клавішу „DEL” для процедури очищення даного діапазону клітинок від інформації;.

- виконати команди меню „Дані/Консолідація” та такі дії. Натиснути кнопку „Посилання”, активізувати мишею аркуш “Січень” і виділити діапазон клітинок D3:F7. Натиснути знову кнопку „Посилання”. На екрані з’явиться діалогове вікно „Консолідація”, де потрібно натиснути кнопку „Додати”. Ще двічі повторити дану процедуру з активізацією аркушу „Лютий” та „Березень”. При цьому у вікні „Список діапазонів” повинні бути посилання на три діапазони для підсумку;

- натиснути кнопку „ОК” на панелі „Консолідація”.

5. Зберегти файл з розрахунками (“ЗП_Прізвище”) та перенести в папку Вашої групи, яка знаходиться в папці Студенти на ПК Glav8.


Практична робота№4

Тема: Microsoft Excel – створення та редагування зведених таблиць.

Мета: Навчитись групувати дані, обробляти економічну інформацію з використанням зведених таблиць.

Хід роботи:

Рисунок 2 - Визначення макету зведеної таблиці

Щоб створити зведену таблицю, в якій групування здійснюється по товарах і датах реалізації (рис. 3) треба:

Перейти на лист робочої книги, де міститься таблиця “Відомість надходження товарів”. Виконати команду меню ДанныеСводная таблица.

На першому кроці роботи з майстром зведених таблиць обрати перемикачі в списке или базу данных Microsoft Excel і сводная таблица. Натиснути на кнопці Далее.

Виділити усю таблицю без заголовка та підсумкового рідка - чарунки А2:F11, натиснути на кнопці Далее. Натиснути на кнопці Нет для створення незалежної від попередньої зведеної таблиці.

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

Обрати місце розташування зведеної таблиці – новый лист. Натиснути Готово.

Рисунок 3 - Зведена таблиця по товарах і датах поставок

 


Практична робота№5

Тема: Розрахунок вартості замовлення з використанням елементів управління

Мета: Навчитись складати кошторис використовуючи елементи управління.

Хід роботи:

Потрібно розрахувати вартість замовлення на придбання комп’ютера визначеної конфігурації. Для спрощення прикладу, у якості змінюваних параметрів замовлення будемо розглядати тільки тип процесора, оперативної пам’яті, вінчестера та монітора. При цьому також врахуємо вартість гарантії і доставки. Всі дії і розрахунки будуть виконуватися на робочому листі „Кошторис”. На таких додаткових робочих листах, як: „Процесори”, „Пам’ять”, „Вінчестери” та „Монітори” будуть розміщені відповідні прайс - листи для підбору конфігурації ПК. Для вибору комплектуючих із прайс - листів і для проведення розрахунків будуть використані відповідно елементи управління та функції табличного процесора Excel.

1. Завантажити Excel.

2. Перейменувати робочі аркуші книги, щоб назви відбивали зміст розміщених на аркушах даних. Для цього потрібно клацнути правою кнопкою миші на ярличку робочого аркуша „Лист1”. На екрані з’явиться контекстне меню. Виконати команду „Перейменувати” та ввести нову назву аркуша „Кошторис”. Натиснути клавішу „Enter”. Аналогічним чином перейменувати інші робочі аркуші, що будуть використовуватися: „Лист2” на „Процесори”, „Лист3” на „Пам’ять”, „Лист4” на „Вінчестери”, „Лист5” на „Монітори”.

3. Підготувати робочий аркуш „Кошторис”. Для цього потрібно активізувати курсором даний аркуш і ввести текст у відповідні клітинки А1 Вартість замовлення на придбання комп'ютера; А3 Дата:; С3 Курс доллара; А5 Процесор; А7 Пам'ять; А9 Вінчестер; А11 Монітор; А13 Сума; А15 Гарантія; А17 Доставка; А19 Разом

4. Виділити курсором миші діапазон А1:D2 і натиснути кнопку „Об’єднати та розмістити по центру”. Виконати команди меню „Формат/Клітинки”, закладка „Вирівнювання”. У списку „по горизонталі” та „по вертикалі” вибрати „по центру”. Далі виконати команди „Межі/ Зовнішні межі” на панелі „Форматування”.

5. Встановити ширину стовпчиків А, В, С, D відповідно 10, 17, 30, 4. Для цього необхідно встановити курсор миші на заголовок стовпчика і через контекстне меню виконати команду „Ширина стовпчика” та з клавіатури ввести необхідне число.

6. Виділити діапазон А3:А19 і виконати команди „Межі/ Зовнішні межі” на панелі „Форматування”. Аналогічно встановити зовнішні межі для діапазонів клітинок В3:В19 і С3:D19.

7. Виділити діапазон В5:В19 і виконати команди меню „Формат/Клітинки”, закладка „Число”. Вибрати формат „Фінансовий”. Відкрити поле зі списком „Значення”, встановити: „грн” і натиснути „ОК”.

8. Зробити так, щоб на бланку „Вартість замовлення на придбання комп’ютера” автоматично з’являлася поточна дата. Для цього вибрати В3 і виконати меню „Вставка/Функція” (або натиснути на кнопку ¦х). На екрані з’явиться діалог майстра функцій. Вибрати зі списку Категорія – „Дата і час” Вибрати зі списку „Функція” – „СЕГОДНЯ()”. Натиснути двічі кнопку „ОК”. У клітинці В3 з’явиться поточна дата.

9. Ввести в клітинку D3 поточний курс долара.

10. Підготувати прайс - листи на окремих робочіх аркушах „Процесори”, „Пам’ять”, „Вінчестери”,„Монітори”.

11. На робочому аркуші „Кошторис” встановити елемент управління „Поле зі списком”, через який можна переглядати список процесорів і вибирати потрібне найменування.

Для цього необхідно активізувати аркуш „Кошторис”. Виконати команду меню „Вид/ Панелі інструментів/Форми”. На екрані з’явиться панель з елементами управління. Натиснути кнопку „Поле зі списком” на панелі інструментів „Форми”. Встановити курсор миші на верхній лівий кут клітинки С5, натиснути кнопку миші і, не відпускаючи її, акуратно провести курсор-хрестик по лінії, що розділяє рядки 4 і 5 так, щоб хрестик був у клітинці D5, десь на дві третини ширини клітинки від лівого її краю. Відпустити кнопку миші. Елемент управління „Поле зі списком” буде встановлено (для редагування встановленого елемента управління необхідно на ньому клацнути правою, а потім лівою кнопкою миші. З’являться маркери навколо елемента управління. Тепер можна курсором миші змінити положення та розмір елемента управління).

12. Щоб призначити елементу управління „Поле зі списком” список типів процесорів необхідно клацнути правою кнопкою миші на створеному елементі управління. В контекстному меню вибрати команду „Формат об’єкта”. На екрані з’явиться діалог „Формат елемента управління”. По умовчанню в діалозі обрана закладка „Елемент управління”. Клацнути в полі „Формировать список по диапазону”. Активізувати курсором миші закладку „Процесори”. Виділити курсором діапазон А2:А6 із назвами типів процесорів. Рядок у полі „Формувати список по діапазону” буде мати вигляд: „Процесори!$A$2:$A$6”. Клацнути в полі „Связь с ячейкой”, при цьому в робочій книзі відчиниться аркуш „Кошторис”. Клацнути мишею на клітинці Е5. Межі клітинки виділяться пунктирною лінією, а в полі ведення діалогу „Форматування об’єкта” з’явиться „$E$5”. В цій клітинці на робочому аркуші „Кошторис” буде розміщено порядковий номер обраного елемента. У полі „Количество строк списка” ввести з клавіатури – 5. Натиснути „ОК”. Тепер через встановлене поле зі списком можна не тільки переглядати список процесорів, але і вибирати елементи цього списку. При натисканні кнопки списку з’явиться перелік типів процесорів. Оберіть перший.

13. Розмістити ціну процесора в клітинку В5 на основному робочому аркуші „Кошторис”. Для цього потрібно встановити курсор миші в клітинку В5 і натиснути кнопку „Вставка функції”. Вибрати „Посилання та масиви” зі списку „Категорія”. У списку „Функція” вибрати „ИНДЕКС”. Натиснути „ОК”. На екрані з’явиться діалог з виділеним рядком „массив; номер_строки; номер_столбца”. Натиснути знову кнопку „ОК”. На екрані з’явиться діалог „Аргументи функції „ИНДЕКС”. Клацнути в полі „Масив”. Активізувати курсором миші закладку „Процесори”. Виділити діапазон В2:В6 з цінами процесорів. Після цього рядок у полі „Масив” буде мати вигляд: „Процесори!В2:В6”. Клацнути в полі „Номер рядка”. При цьому активізується аркуш „Кошторис”. Клацнути на клітинці Е5. Межі клітинки будуть виділені пунктиром, а в полі введення параметрів з’явиться рядок „Е5”, натиснути кнопку „ОК”. Діалог введення параметрів зникне з екрана. В клітинці В5 з’явиться ціна обраного процесора.

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

15. Для вибору конфігурації пам’яті необхідно встановити на робочому аркуші „Кошторис” елемент управління „Лічильник”, за допомогою якого можна переглядати список типів пам’яті. Для цього натиснути кнопку „Лічильник” на панелі інструментів „Форми”. Встановити „Лічильник” в клітинці D7. Встановлення елемента управління „Лічильник” виконується аналогічно елементу „Поле зі списком”, що описано раніше. Розмір поля елемента управління варто встановити приблизно на дві третини ширини клітинки D7.

16. Задати параметри „Лічильника”. Для цього потрібно клацнути правою кнопкою миші на елементі „Лічильник” вибрати „Формат об’єкта”. На екрані з’явиться діалог „Формат елемента управління”. Ввести значення полів із клавіатури: „Поточне значення”: 1; „Мінімальне значення”: 1; „Максимальне значення”: 5; „Крок зміни”: 1. Значення в поле „Зв’язок з клітинкою” заноситься, як описано раніше при використання елемента управління „Поле зі списком”. Для цього вибрати клітинку Е7 на основному робочому аркуші „Кошторис”.

17. Тепер необхідно розмістити ціну і тип обраної конфігурації пам’яті відповідно в клітинки В7 і С7. Для цього потрібно вибрати С7 викликати майстр функцій. Вибрати функцію „ИНДЕКС()”, вказати робочий аркуш – „Пам’ять” і діапазон клітинок відповідного списку, тобто стовпчик типів (А2:А6). Вказати клітинку, в якій буде знаходитися порядковий номер обраного в списку рядка, це клітинка Е7. Натисніть кнопку „ОК”. Аналогічно зробити для В7 тільки вибрати з аркуша „Пам’ять” стовпчик цін (В2:В6). У В7 і С7 з’явиться інформація щодо ціни і типу обраної конфігурації пам’яті. Встановити для клітинки Е7 білий колір шрифту.

18. У подальшому для розрахунку вартості замовлення на придбання ПК вінчестер будемо вибирати за допомогою „Лічильника”, а монітор - за допомогою елемента управління „Поле зі списком”. Виконайте це самостійно аналогічно тому, як описано раніше.

19. Розрахунок вартості гарантії потребує використання такого елемента управління як „Перемикач”, який застосовується в ситуації, коли необхідно зробити вибір одного з декількох взаємовиключних варіантів. Кошторис на основному аркуші передбачає два види гарантії по бажанню замовника: безкоштовна - терміном на півроку, або вартістю 10% від ціни комп’ютера - терміном на рік. Спочатку потрібно встановити перемикач для першого виду гарантії. Для цього необхідно натиснути кнопку „Перемикач” на панелі інструментів „Форми”. Перемістити курсор миші до верхнього лівого кута клітинки С15 і натиснути ліву кнопку миші.. На робочому аркуші з’явиться поле „Перемикач” в режимі редагування. Вилучити стандартний заголовок поля і ввести новий заголовок: „6 міс.”. Для встановлення параметрів елемента управління клацнути правою кнопкою миші на полі „Перемикач” і через контекстне меню виконати команду „Формат об’єкта”. На екрані з’явиться діалог „Формат елемента управління”. Ввести значення полів: „встановлений”, „зв’язок з клітинкою” - Е15. Натиснути кнопку „ОК”. Перемикач виділиться (кружок стане темним), а в клітинці Е15 з’явиться значення 1 - номер активного перемикача на робочому аркуші.

20. Встановити другий перемикач (для гарантії на рік) в клітинці С15 зліва від першого. Встановлення виконується аналогічно п.19. Вилучити стандартний заголовок перемикача і ввести новий: „1 рік” Активізація діалогу „Формат елемента управління” в даному випадку зайва. Параметри другого перемикача налаштовуються автоматично.

21. Перевірити дію перемикачів. Для цього потрібно клацнути мишею на перемикачі „1 рік”. Виділення переміститися з першого на другий перемикач. У клітинці Е15 з’явиться значення „2” - номер активного (другого) перемикача. Клацнути мишею на перемикачі „6 міс.”. Виділення переміститися на перший перемикач. У клітинці Е15 відновиться значення „1”.

22. Оскільки вартість гарантії залежить від вартості комп’ютера, необхідно обчислити спочатку вартість комп’ютера в клітинці В13 за допомогою кнопки „Автосума” як суму цін комплектуючих елементів в діапазоні клітинок В5:В11.

23. Ввести в клітинку В15 формулу розрахунку вартості гарантії з використанням в цій формулі значення активного перемикача: =В13*10%*(Е15-1).

24. Встановити (активізувати) перемикач „1 рік”. У клітинці В15 з’явиться значення, яке дорівнює 10% від вартості комп’ютера. У формулі „В13*10%” – це 10% від суми комплектуючих „(Е15-1)” дає нам 0, якщо в Е15 - значення „1” (встановлено перемикач „6 міс.”). Якщо ж встановлено перемикач „1 рік”, тоді в Е15 буде значення „2”, „(Е15-1)” дорівнюватиме „1”, а загальне значення формули буде дорівнювати „В13*0,1”. Встановити для клітинки Е15 білий колір шрифту.

25. Для обліку вартості доставки доцільно використати такий елемент управління, як „Прапорець”. Прапорець застосовується в ситуації, коли потрібно вирішити: потрібна або не потрібна доставка комп’ютера. При цьому для опрацювання результатів рішення використовується логічна функція „ЕСЛИ()”. Якщо доставка потрібна, то у вартість комп’ютера потрібно включити вартість доставки. Для встановлення на основному робочому аркуші „Кошторис” елемента управління натиснути кнопку „Прапорець” на панелі інструментів „Форми”. Перемістити курсор миші до верхнього лівого кута клітинки С17 і натиснути ліву кнопку миші. На робочому аркуші з’явиться поле „Прапорець” в режимі редагування. Вилучити стандартний заголовок поля і ввести новий заголовок: „Потрібна”. Знову клацнути правою кнопкою миші на поле „Прапорець” і через контекстне меню вибрати команду „Формат об’єкта”. На екрані з’явиться діалог елемента управління. Ввести значення полів: „встановлений”, „зв’язок з клітинкою” - Е17. Натиснути кнопку „ОК”. Діалог зникне з екрана. При встановленні курсором миші прапорця всередині елемента управління з’являється значок-позначка, а в клітинці Е17 – значення „ИСТИНА”.

26. Ввести логічну формулу розрахунку вартості доставки в клітинку В17. Для цього потрібно встановити курсор миші в клітинку В17 і натиснути кнопку „Вставка функції” (fx). З’явиться діалог майстра функцій. Вибрати „Логічні” в списку „Категорія”. У списку „Функція” вибрати функцію „ЕСЛИ()”. Натиснути кнопку „ОК”. Ввести значення параметрів: „лог_выражение” - Е17, „значение_если_истина” - 50, „значение_если_ложь” - 0. Натиснути кнопку „ОК”. В результаті вартість доставки в клітинці В17 буде формуватися прапорцем в клітинці С17. Клацнути мишею на прапорці (або на його заголовку). Позначка з прапорця зніметься і в клітинці В17 з’явиться значення „0” або „-„. Клацнути мишею на пра порці ще раз. У клітинці В17 з’явиться значення „50,00 грн”.

Встановити для клітинки Е17 білий колір шрифту.

27. Для підрахунку суми вартості комплектуючих зібраного комп’ютера, гарантії і доставки ввести в клітинку В19 формулу: =В13+В15+В17.

28. Увести в клітинку С19 формулу перерахування вартості комп’ютера в долари: =В19/D3. В клітинці D3 зберігається поточний курс долара. Встановити для клітинки С19 доларовий формат. Для цього необхідно виконати команду головного меню „Формат/Клітинки”, закладка „Число”. Вибрати курсором числовий формат „Усі формати”. Далі вибрати у вікні маску формату # ##0. Встановити курсор у полі „Тип” перед встановленою маскою і ввести з клавіатури знак долара: $. Маска формату буде мати вигляд $# ##0. Вона означає, що головні нулі в сумі відображатися не будуть, а перед першою значущою цифрою буде виводитися символ долара. Натиснути кнопку „ОК”. У клітинці С19 вартість замовлення буде показана в доларах. Основний робочий аркуш „Кошторис” буде мати свій остаточний вигляд. Закрити панель інструментів „Форми”.

29. Розрахувати вартість замовлення на придбання комп’ютера визначеної конфігурації.

32. Зберегти файл у власній папці з іменем „Вартість замовлення_Прізвище”.

33. Закінчити роботу з табличним процесором.

 

 


Практична робота№6

Тема: Сервіс Microsoft Excel “Пошук рішення.

Мета: Навчитись використовувати пошук рішення при розв’язанні системи рівнянь.

Хід роботи:

1. Сервіс Microsoft Excel “Пошук рішення”

Команда меню СервисПошук рішення Microsoft Excel дає змогу розв’язувати системи рівнянь, задачі лінійної оптимізації.

Щоб скористатися даним сервісом Microsoft Excel слід спочатку підготувати дані на листі робочої таблиці:

1. Зарезервувати для кожної змінної чарунку електронної таблиці.

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

3. Ввести в вигляді формул ліві частини рівнянь, підставляючи адреси чарунок у позиції змінних.

Далі можна починати розв’язання задачі. Для цього слід подати команду меню СервисПошук рішення. У вікні діалогу команди слід визначити:

1. адресу чарунки, у якій міститься цільова функція, якщо розв’язується задача лінійної оптимізації, при розв’язанні системи рівнянь поле повинно бути порожнім, а також визначити якого значення повинна досягти цільова функція – мінімального, максимального або певного;

2. адреси чарунок, що змінюються, ці чарунки, які було зарезервовано для змінних;

3. в області Ограничения добавити обмеження, що визначають яке значення повинно прийняти кожне рівняння;

4. натиснути на кнопку Выполнить.

 

 

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

1. Створити новий документ Microsoft Excel.

2. У вікні редагування ввести тему роботи, прізвище, групу, дату роботі.

3. Ввести данні для розв’язання системи рівнянь за індивідуальним варіантом, розв’язати систему.

4. Надрукувати таблицю з виведенням результатів розрахунків і з виводом формул у чарунках таблиці, звіт по результатах.









Что будет с Землей, если ось ее сместится на 6666 км? Что будет с Землей? - задался я вопросом...

ЧТО ТАКОЕ УВЕРЕННОЕ ПОВЕДЕНИЕ В МЕЖЛИЧНОСТНЫХ ОТНОШЕНИЯХ? Исторически существует три основных модели различий, существующих между...

Система охраняемых территорий в США Изучение особо охраняемых природных территорий(ООПТ) США представляет особый интерес по многим причинам...

ЧТО ПРОИСХОДИТ ВО ВЗРОСЛОЙ ЖИЗНИ? Если вы все еще «неправильно» связаны с матерью, вы избегаете отделения и независимого взрослого существования...





Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте:


©2015- 2021 zdamsam.ru Размещенные материалы защищены законодательством РФ.