1. Вирівнювання в клітинні, орієнтація тексту.

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

Інші закладки пункту Формат ячеек дозволяють змінювати шрифти, межі та заливку комірок чи їх діапазонів.

2. Зміна ширини стовпчика

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

Змінити ширину стовпчика можна і використовуючи діалогове вікно Ширина столбца, що відкривається після виклику команди Столбеи/Ширина меню Формат.

3. Зміна висоти рядка

Висота рядків залежить від розміру игрифту, що використовується. Зміна

висоти рядка виконується так само, як і зміна ширини стовпця. Крім того, висота рядка змінюється і при встановленні іншого розміру шрифта.

Точне значення висоти рядка встановлюється в діалоговому вікні Висота строки, що відкривається після виклику команди Строки / Вмсота меню Формат.

4. Використання автоформатуваини

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

1. Виділити таблицю.

2. Акгивізувати команду Автоформат з меню Формат, внаслідок чого відкриється однойменне діалогове вікно.

3. Вибрати формат у полі Список форматов. При виділенні назви формату в полі Образец з'явиться зразок таблиці, оформленої з використанням вибраного формату.

Автоформатування можна скасувати. Для цього в меню Формат виберіть команду Автоформат далі Список форматов і виберіть елемент Нет.

5. Використання посилань у формулах

Посилання - ідентифікатор комірки чи групи комірок у книзі. Створюючи формули, що містять посилання на комірки, Ви зв'язуєте формулу з комірками книги. Значення формули залежить від вмісту комірок, на які вказують посилання, і воно змінюється при зміні вмісту цих комірок. Відносне посилання - вказує на комірку, ґрунтуючись на її положенні щодо комірки, у якій знаходиться формула, наприклад на «два рядки вище». Відносне посилання на комірку: "у42". При копіюванні формул відносні посилання змінються. Абсолютне посилання використовує для звернення до комірки її фіксоване положення на аркуші, наприклад, «комірка знаходиться в стовпці А рядка 2». Абсолютне посилання на комірку: "$А$2При копіюванні не змінюються.

виводиться вікно діалогу, за допомогою якого користувач може створити новий аркуш зі звітом. Цей звіт показує значення, які кожний сценарій призначає коміркам, що змінюються. Крім того, звіт може також показати вплив кожного сценарію на одну чи кілька комірок результату. На рисунку 27 зображено вікно діалогу, яке відкривається при натисканні кнопки Отчет. У цьому вікні виберіть тип звіту і вкажіть комірки результат)', які ви хочете включити до звіту.

Етан 4.1 Побудова звіту Структура сценария

Для виведешія цього звіту встановіть перемикач Структура в групі Тип отчета вікна діалогу Отчет по сценарию. Звіт буде вставлено перед активним аркушем робочої книги на окремому аркуші з іменем Структура сценария. Звіт за нашим прикладом наведено на рисутгку 28. Ширина деяких колонок змінена для компактного і ясного надання інформації. У звіті виводяться імена комірок, що змінюються і комірок результату. Імена сценаріїв виводяться як заголовки колонок, а колонки з'являються в порядку визначення сценаріїв. Значення комірок, що змінюються, виводяться в сценарії еірим кольором.

Отчет по сценарию КЗІЗ

тт'отіті ■ їС

Д-шТіоір«іул»Т*т«:• -У ^•>' , •; _ч. -і |С6;С16;СІ8|

Рис. 27 - Діалог овс вікно Отчет по сценарию Е гап 4.2 Побудова звіту Сводная таблица по сценарию Для виведення цього звіту встановіть перемикач Сводная таблица в групі Тип отчета вікна діалогу Отчет по сценарию. Зведена таблиця є інструментом, який дозволяє за допомогою миші виконувати прямі дії з комірками для змішування і порівняння різноманітних сценаріїв у звіті й сиостерії-ати отримувані при цьому значення в комірках резульгагу. На рисунку

29 зображено звіт Сводная таблиця по сценарию, створений на основі раніше створених сценаріїв. При виведенні на екран звіту Сводная таблица по сценарию з'являється панель інструментів Сводньїе таблицьі, яка дозволяє змінювати зведену таблицю.

; АгіаІ Суг

<Є|>«Дньо4 3<к«уітм

юлмсосгі середньої

покупців нл $•. хількосй покупив

і м»*иш«ния на 114 мм<йи<ж

ДОХОДУ ви

односо покуту вмірді иа рскялиу И4 $4 НЛ 11%

32.11грн. Щ ,

;

? 115 $44"грн. І Ш 1 НІ 755.50ср«- І 341 111.25грн. V.** 213 Н4.*гри. 2** ІИ 14411грн. 4Н 1 НІ 211.71грн. 1 Ш

доходу ви одного покупця

Н4 11%,

иЯмшсіми іиш»о віпрл НІ 1Г. І

СМІ!Ір«КІШМ

на 11%

шт

121133,14грн. 17? 111,47грн. і т 1040г рн. 1321 йіізгри. 274II»5.15грн. 2 121 247.11ірн.

14$ 973.17грн. 231 571.31грн. 1 34$ ИМїгрн. 1 417 711.11грн. 1 244 731.11грн. $ 751 11$.71>рн.

: сголбвц'Тачувие імаченм*" грвАСта&плет значення измемаемьа: ^еек • Г соєвим» ИТОГО*<ХО ОТ^Тб ПО Сі£НфИ0. И)МЄИ5ІЄМЬІд Л^АіИ ДПЙ КАЖДОТО

Рис. 28 - Звіт Структура сценария

Зміст звіту

• Титульна сторінка

• Назва і мста роботи.

• Результати виконаного завдання згідно з вибраним варіантом.

•» ,

• Висновки.

Контрольні питання

1. Як створюваги сценарії для розв'язання економічних задач з кількома змінними?

2. Як додавати нові сценарії?

3. Як редагувати сцснарії?

4. Як видаляти сценарії?

5. Як будувати звіти за сценаріями?

> Т*пв* Мв* Когп*і < • і? * * X «і

Г«?"1>2189 48>45

07 ^#{172189.46745

а . • • ^ • в г... с. ! ~

®і»ХМО &3&ЩЩІ

всього чнпрат к» р/« Чистий іунцтинн.

Яупики С*0_УЛЬ

за—г.

Уібупу*

61216125.

У.шпьнии гум

.1 ^ІЛШЇ'КИД доходу від одного !п<л>тця № 10%. і^пшнои і < таї гі інші із; витрат ю І статті р*кжлм* на 15^#о

г+родаьої кіикості покупці® на 15% вкк лдок

витрат ю рекххму

2355^0.302

649^789.911

5-51865.-93

1459-3.916

6345905.03

1244-38.602

6 10® •

'Шяьшеїтл С*р*ДИЬОІ КІЛЬКОСТІ

покупців яі » шеню^юм

доходу від одеого иок>иии на

5Н

1*213? Дб^

6325604.63

ї^Ш^ХШЩ К ТїхШЗй * Ш.

іЛ

:. ,)•-■- '""•' . -ІІ.І ■ ■. ■ . І.І .11 .

Рис. 29 - Звіт Сводная таблица

Література: [1; 2; 4; 5; 6; 7; 8; 9; 10; 12; 13].

Лабораторна робота № 10 Тема.

Застосування формул .масиву для ведення обліку витрат у дрібнотоварному виробництві

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

Короткі теорет ичні відомості

У більшості випадків для ведення обліку грошових і товарних иогоків у малому бізнесі нсмас необхідності придбати спеціальні бухгалтерські або управлінські програми. Можна скористатися стандартним набором засобів, які надає проірама Ехсеї, та розробити свою систему обліку на базі електронних таблиць.

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

допомогою електронних таблиць

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

Усі дані щодо витрат та придбання інструментів та матеріалів заносяться до Журналу операцій, який зображений на рис. ЗО.

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

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

• ознакою формули масиву є фігурні дужки "{ } " на початку та в кінці

формули;

• фігурні дужки вводяться натисканням комбінації клавіш [СігІ+8кі/і+Епіег] - або після завершення вводу формули, або в процесі її

редагування;

• при редагуванні формул фігурні дужки зникають;

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

Хід роботи

Розрахунок втрат матеріалів необхідно проводити наступним чином:

1. ГІо-псрше необхідно набрати журнал операцій (рис. ЗО) і присвоїти імена стовпцям журналу. Для цього необхідно виділиги діапазон комірок Л 1:038 (або А1:СЗІ00 чи більше, якщо планується продовжувати записи в •журналі) та натиснути комбінацію клавіш [Сіг1+5ЬіЙ+РЗ]. У вікні Создать имена необхідно вибрати В строке вмше та натиснути кнопку ОК.

2. На аркуші з назвою "Витрати!** (рис. 31) підраховується загальна вартість придбаних матеріалів, вартість використаних матеріалів та тих, що

Коміркам з датами необхідно присвоїти імена. Для комірки В1 задаємо ім'я Періоде, а для комірки В2 - ім'я ПеріодПо.

Формула в комірці В4 здійснює обчислення всієї суми придбаних за вказаний період матеріалів: =СУММ(В6:В10)

У комірці В6 повинна міститися формула, призначена для розрахунку суми, що втрачається за певний період часу на придбання матеріалу, вказаного

Щоб ввести в комірку В6 цю формулу, необхідно набрати в рядку <|юрмул:

^УММ(ЕСЛИ(Дата>=ПеріодС;ЕСЛИ(Дата<=ПсріодПо;Е(:ЛИ(ІІай >існуваннн=А6;1Ірил6аііо;0);0);0))

а потім натиснути комбінацію клавіш [Сіг1+8ЬШ+ЕпІег].

Далі необхідно скопіювати В6 у комірки В7-В10.

У комірці В14 знаходиться формула, що розраховує вартість витраченого за даний період матеріалу, вказаного у комірці А14:

{^У^(ЕСЛИ(Дата>=ПеріодС^ саме=А14;Витрачено;0);0);0))}

У комірці В22 міститься формула, що визначає різницю між придбаними га витраченими матеріалами за вказаний період часу:

=В6-В14

За результатами розрахунків вартості використаних матеріалів за допомогою Мастера Диаграмм будується діаграма.

3. У таблиці на аркуші Внграти2 відображені витрати матеріалів на виготовлення виробів усіх найменувань. За допомогою її можна отримати інформацію про вартість матеріалів, витрачених на виробництво певних виробів. Для розрахунку вартості витраченої сосни на виготовлешія дубових маленьких столів за даний період, комірка В7 повинна містити таку формулу:

{=СУММ(ЕСЛИ(Дата>=ІТеріодС;ЕСЛИ(;ита<=ПеріодІ]о;ЕСЛИ(Із_ч ого=$В$4;ЕСЛИ(Найменування=$В$5;ЕОІИ(Розмір=$В$6;ЕСІШ(Що_саме =А7;Витрачено;0);0);0);0);0);0);0)}

При копіюванні даної формули в комірки ВЯ-В11 буде змінюватися тільки найменування мзісріалу (комірка А7).

При копіюванні в комірку 07 необхідно буде скоригувати адресу комірки, що вказує найменування виробу. Змініть адресу на $0$5. Формула в комірці 1)7 буде мати вигляд:

{=СУММ(ЕСЛИ(Дата>=ІІеріодС;ЕСЛИ(Дата<=11еріодІ1о;ЕСЛИ(1з_ч ого=$В$4;ЕСЛИ(ІІайменувая»ія=$П$5;ЕСЛИ(Розмір=$І)$6;ЕСЛИ(ІЦо_сам е=А7;Витрачено;0);0);0);0);0);0);0)}

При копіюванні формули в комірку Р7 необхідно скоригувати адресу комірок, що вказують найменування виробу та матеріалу, з якого вони виготовлені. Змініть адреси на $Р$5 та $Р$4 відповідно:

(<:УММ(ЕСЛИ(Дата>-І1еріодС;ЕСЛИ(Дата<=иеріодио;ЕСІШ(1з_ч оіо=5Е$4;ЕОІИ(Наймснування=$^5;ЕСЛИ(Розмір=5Е$6;ЕСЛИ(ІЦо_саме =А7;Витрачено;0);0);0);0);0);0);0)}

Гістограма, що побудована за результатами розрахунків, відображає витрати матеріалу в грошовому вираженні по кожному виробу.

Таким самим чином, використовуючи формули масивів, можна розрахувати суми виторгу та прибутку з кожного виробу.

Зміст звіту

- Титульна сторінка.

• Назва і мета роботи.

• і- • ул

• Результати виконаного завдання згідно з вибраним варіантом.

- Висновки.

Контрольні питання

1. Як вводити та редагувати формули масиву?

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

• .

формул масивів?

3. Як будувати за результатами розрахунків графіки? Література: [1; 2; 4; 5; 6; 7; 8; 9; 10; 12, ІЗ].

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

Мета: створити програму для визначення періодичних платежів з кредиту.

Короткі теоретичні відомості

У середовищі Місгозой Ехсеї можна створити програму для визначення періодичних платежів з кредиту на купівлю побутової техніки з використанням системи знижок залежно від порядкового номера покупки. Передбачити можливості друку умов кредитування для клієнта і відображення процесу погашення кредиту на діаграмі. Програма повинна бути створена з використанням макросів.

Програма може бути використана при роботі з клієнтами в торговельній

•

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

ХІД роботи

Створюємо робочий аркуш, подібний до наведеного на рисунку 35. Для цього у відповідні комірки вводимо цифрові дані й формули, як це показано на рисунку 33.

"Ціна товару" вводиться з клавіатури залежно від "вітринної" ціни товару.

>

Графи "Номер покупки" і "Знижка, %" не заповнюємо.

Для швидкого і зрозумілого відображення умов покупки з наданням знижки і кредитування доцільно використати макроси - набори інструкцій, які комп'ютер запам'ятовує і повторює на вимогу користувача. Щоб створиш макрос для розрахунку умов першої покупки в кредит без надання клієнтові знижки, виконаємо наступні дії:

1 Вибираємо в меню Вид пункт Попели инструментов.

2. У пункті Панели инструментов ставимо позначку навпроти панелі Форми.

3. На панелі інструментів Формьі, наведеній на рисунку 34, і яка з'явилася на робочому полі екрана, натискаємо на піктограму Кнопка і на робочому аркуші справа від таблиці рисуємо першу кнопку, якій комп'ютер призначить макрос після натискання у діалоговому вікні Назначить макрос обьекту (рисунок 34) кнопки Записать.

4. У вікні Запись макроса слід вказати найменування макросу

■ ' *

"Перша покупка" (обов'язково через знак підкреслення між словами, інакше комп'ютер не сприйме таке ім'я), його автора та коменгарі стосовно його роботи (за бажанням)і натиснути ОК.

1. Далі з'явиться панель інструментів Остановка записи з відповідною кнопкою, яку слід натиснути, коли треба припинити запис макросу.

2. У комірку Об слід ввести порядковий номер покупки, що її здійснив даний клієнт, - 1 і натиснути клавішу введення. У комірку 07 слід ввести відсоток знижки для цієї покупки - 0%; і натиснути клавішу введення.

3. На панелі інструментів Остановка записи натиснути кнопку з такою самою назвою для припинення 'запису макросу.

4. Для призначення щойно створеній кнопці зрозумілого імені треба натиснути на ній правою кнопкою миші, вибрати в контекстному меню пункт Изменить текст і призначити кнопці нове ім'я, наприклад, "Перша покупка. Знижка 0%".

5. Для перевірки роботи макросу треба стерти дані з комірок Об і 07 і проставити в них, скажімо, номер покупки "2" і відсоток знижки "З". Далі слід натиснути кнопку макросу "Перша покупка. Знижка 0%", який перерахує все відповідно до вказаних при його створенні інструкцій.

Аналогічно слід створити макроси для розрахунку суми кредиту при другій покупці зі знижкою 3% і при третій покупці зі знижкою 7%.

Для створення макросу для перегляду щомісячних виплат спочатку слід створити таблицю, наведену на рисунку 36, внісши туди відповідні формули для зв'язку з початковими даними (сумою кредиту), що будуть вво;щтися на початку робочого аркуша, а також формульної обробки цих даних (визначення помісячних виплат за кредитом, суми боргу за місяцями), як це показано на рисунку 37. Після створення таблиці слід перейти на початок робочого аркуша і почати запис нового макросу з кнопкою, який би здійснював перехід на частину сторінки з таблицею помісячних витрат - почати запис макросу, поіменувати його, перейти на потрібну частину сторінки, зупинити запис макросу. Аналогічний макрос для переходу на початок сторінки створюється і для кнопки "Назад".

Для створення макросу для кнопки "Друкувати умови" слід розпочати запис макросу, вибрати в меню Файл пункт Печать і, вибравши параметри

іруку, відправити документ па принтер, потім зупиниш запис макросу.

Для створення макросу для кнопки "Графік помісячних виплат" слід, |Н)іиочавши запис макросу, побудуваги діаграму, подібну до наведеної на рисунку 38, за даними таблиці щомісячних виплат, потім зутшнити запис макросу. Макрос для кнопки "Назад" на діаграмі повинен здійснювати перехід між аркушами робочої книги. Дії з його створення описані вище.

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

Таблиця 5 - Умови для отримання знижок при покупці побугової техніки

№	Номер покупки	Знижка, %
1	1	.. 0
2	2	3
3	3	7
4	4	9

Зміст звпу

• Титульна сторінка.

• Назва і мета роботи.

• Результати виконаного завдання згідно з вибраним варіантом.

• Висновки.

Контрольні питання

1. Як створювати міні-інформаційиу підсистему зі зрозумілим

• • '

інтерфейсом для проведення взаємопов'язаних розрахунків і графічного зображення їх результатів у середовищі МісгозоЙ Ехсеї? Література: [1; 2; 4; 5; 6; 7; 8; 9; 10; 12; 13].

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

Функція - це заздалегідь визначена формула, що оперус з одним чи декількома значеннями і повертає певне значення. Багато з функцій Ехсеї € короткими варіантами формул, що часто використовуються. Наприклад: ряд значень можна підсумувати так: *^вАІ+А2+АЗ+А4+А5+А6 або гак -СУММ(А1:А6). Очевидно, що функція СУММ робить формулу значно коротшою і зручнішою для використання.

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

• Присвоєння імен коміркам і діапазонам

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

Призначення імен. Команда Имя з меню Вставка дозволяє призначаги імена коміркам і їх діапазонам. Для присвоєїпія імені комірці виконайте наступні дії:

Виділіть комірку чи кілька комірок.

і

У меню Вставка виберіть команду Имя, а потім - Создать, і в діалоговому вікні Создать имена виберіть де знаходяться імена для комірок.

Нагисніть ОК.

Тепер у формулах можна використовувати імена комірок, для вставлення

%

їх до формули виконайте наступні дії:

Почніть набирати формулу.

У місці, де потрібно до формули як аргумент вставити ім'я комірки, виберіть пункт Имя в меню Вставка.

Виберіть пункт Вставить і виберіть потрібне ім'я із запропонованого

Натисніть ОК.

Хід роботи