Збірник_Excel

yзастосувати функцію СУММЕСЛИ().

6.Надрукувати створену таблицю з обчисленими значеннями та використаними формулами.

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

1.Способи виклику майстра функцій.

2.Параметри майстра функцій.

3.Функції категорії Математические, їх призначення.

4.Функції ОКРВВЕРХ() та ОКРВНИЗ(). Призначення і аргументи. Різниця між вказаними функціями.

5.Функції ОТБР() та ЦЕЛОЕ(). Призначення і аргументи. Різниця між вказаними функціями.

Лабораторна робота №8.

Тема: Використання функцій MS Excel.

Мета: Набути та закріпити навички використання функцій MS Excel категорії Ссылки и массивы.

Хід роботи:

1.Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).

2.Зберегти створений документ під іменем ЛР8_Прізвище.xls.

3.Збільшити кількість робочих листів до 5.

4.Сформувати на робочому листі Лист1 вид платіжного доручення, який наведений на рис. 8.1. Змінити назву Лист1 на Бланк.

5.На робочому листі Лист2 створити таблицю "Реквізити платника" (рис. 8.2.), в якій містяться всі необхідні реквізити для заповнення розділу Платник платіжного доручення.

6.Назвати робочий лист Лист2 – Платник.

7.На робочому листі Лист3 створити таблицю "Реквізити одержувача" (рис. 8.3.), в якій містяться всі необхідні реквізити для заповнення розділу Одержувач платіжного доручення. Назвати лист, на якому створена таблиця, Одержувач.

8.На робочому листі Лист4 створити таблицю "Дані платежу" (рис. 8.4.), яка містить дані необхідні для заповнення платіжного доручення. Назвати робочий лист Лист4 Дані.

21

Рис. 8.1. Вид платіжного доручення.

Рис. 8.2. Таблиця "Реквізити платника".

Рис. 8.3. Таблиця "Реквізити одержувача".

22

Рис. 8.4. Таблиця "Дані платежу".

9.На робочому листі Бланк заповнити розділ Платник платіжного доручення використовуючи дані робочого листа Платник.

Вказівки до виконання:

yдля кожної комірки вказаного розділу створити посилання на відповідну комірку робочого листа Платник.

10.На робочому листі Лист5 побудувати таблицю "Допоміжна інформація" (рис. 8.5.).

Рис. 8.5. Таблиця "Допоміжна інформація".

Вказівки до виконання:

yзначення комірки Номер платіжки, що формується потрібно вводити вручну;

yдля визначення значення комірок Номер рядка даних платіжки та Номер рядка реквізитів одержувача використати функцію ПОИСКПОЗ();

yдля визначення значення комірки Одержувач використати функцію

ВПР() категорії Ссылки и массивы.

11.Заповнити три платіжних доручення використовуючи дані таблиці

Допоміжна інформація і функцію ИНДЕКС() категорії Ссылки и массивы.

Вказівки до виконання:

yдля заповнення комірок розділу Одержувач використовувати дані комірок Одержувач і Номер рядка реквізитів одержувача таблиці

Допоміжна інформація та дані з робочого листа Одержувач;

23

yдля заповнення інших комірок платіжного доручення використовувати дані комірок Номер платіжки, що формується і Номер рядка даних платіжки та дані з робочого листа Дані.

12.Роздрукувати заповнені платіжні доручення.

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

1.Способи визначення границь комірок.

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

3.Способи виділення цілих рядків і стовпців робочого листа.

4.Способи копіювання даних в межах однієї робочої книги.

5.Функції категорії Ссылки и масивы, їх призначення.

Лабораторна робота №9.

Тема: Використання функцій MS Excel.

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

Хід роботи:

1.Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).

2.Зберегти створений документ під іменем ЛР9_Прізвище.xls.

3.На робочому листі Лист1 створити таблицю "Діючі тарифи на комунальні послуги в жовтні 2005р. (грн.)", наведену на рис. 9.1.

Змінити назву робочого листа Лист1 на Тарифи.

Рис. 9.1. Таблиця "Діючі тарифи на комунальні послуги в жовтні 2005 р. (грн.)".

4.На робочому листі Лист2 створити таблицю "Розрахунок нарахувань за комунальні послуги у жовтні 2005 р. (грн.)", наведену на рис. 9.2.

Змінити назву Лист2 на Нарахування.

24

Рис. 9.2. Таблиця "Розрахунок нарахувань за комунальні послуги в жовтні

2005 р. (грн.)".

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

Пояснення: При виконанні розрахунків використати діючі тарифи та правила:

9 квартплата обчислюється як добуток двох величин: тарифу

(квартплата) і загальної площі;

9нарахування плати за водопостачання:

за наявності гарячої води дорівнює добутку кількості зареєстрованих та суми тарифу холодної води і тарифу гарячої води;

без гарячої води дорівнює добутку тарифу холодної води і кількості зареєстрованих;

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

9нарахування плати за газопостачання визначається як добуток відповідного тарифу і кількості зареєстрованих;

9нарахування плати за опалення дорівнює добутку відповідного тарифу і загальної площі квартири (м2).

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

6.На робочому листі Нарахування нижче створеної таблиці знайти:

6.1.Третій по порядку найбільший борг,

Вказівки до виконання:

y використати функцію НАИБОЛЬШИЙ() категорії Статестичиские.

6.2.Прізвище квартиронаймача з третім по порядку найбільшим

боргом;

Вказівки до виконання:

25

yвикористати функції ПОИСКПОЗ(), ИНДЕКС() категории Ссылки и массивы.

6.3.Другу по порядку найменшу суму нарахувань з урахуванням боргу.

Вказівки до виконання:

y використати функцію НАИМЕНЬШИЙ() категорії Статестичиские.

6.4.Прізвище квартиронаймача з другою по порядку найменшою

сумою нарахувань з урахуванням боргу.

6.5.Ранг загальної суми нарахувань з урахуванням боргу і без врахування боргу Іванової Лілії;

Вказівки до виконання:

yвикористати функцію РАНГ() категорії Статестичиские.

7.На робочому листі Лист3 створити таблицю "Графік оплати за комунальні послуги" (рис. 9.3.). Заповнити таблицю згідно зразка. Змінити назву Лист3 на Графік.

Рис. 9.3. Таблиця "Графік оплати за комунальні послуги".

Вказівки до виконання:

yвиділити відповідні діапазони комірок;

yвиконати команду Правка→Копировать або натиснути комбінацію клавіш Ctrl+C;

yвиконати команду Правка→Специальная вставка;

yу діалоговому вікні Специальная вставка натиснути кнопку Вставить связь.

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

Вказівки до виконання:

yвикористати функцію ДЕНЬНЕД() категорії Дата/время.

9.Визначити в якому місяці була здійснена остання оплата.

Вказівки до виконання:

26

yвикористати функцію Месяц() категорії Дата/время.

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

1.Копіювання, переміщення і видалення даних на робочому листі.

2.Способи копіювання, переміщення і видалення комірок на робочому листі.

3.Використання параметрів діалогового вікна Специальная вставка при копіюванні і переміщенні даних робочого листа.

4.Функції категорій Дата/Время, синтаксис і призначення.

Лабораторна робота №10.

Тема: Графічне представлення даних з допомогою діаграм MS Excel.

Мета: Набути та закріпити навички створення діаграм різного типу. Закріпити навички роботи з майстром діаграм MS Excel.

Хід роботи:

1.Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).

2.Зберегти створений документ під іменем ЛР10_Прізвище.xls.

3.На робочому листі Лист1 створити таблицю "Обсяги товарообігу за асортиментною структурою НКВФ "Колос", наведену на рис. 10.1.

Змінити назву робочого листа Лист1 на Дані.

Рис. 10.1. Таблиця "Обсяги товарообігу за асортиментною структурою НКВФ "Колос".

4.На робочому листі Дані виконати необхідні розрахунки.

5. Побудувати звичайну гістограму, на якій відобразити структуру

27

фактичної закупівлі товарів у 2003 та 2004 роках (рис. 10.2.). Додати до діаграми заголовок "Порівняльний аналіз обсягів закупівлі товарів у

2003-2004 рр". Розмістити діаграму на окремому листі діаграм, назвати лист зі створеною діаграмою Факт 2003-2004.

5.1.Змінити формат області побудови діаграми.

5.2.Змінити формат ряду даних Факт закупівлі 2003 р. (колір заливки).

5.3.Встановити розмір шрифта заголовка – 12 пт.

5.4.Змінити вирівнювання підписів по осі категорій.

Рис. 10.2. Діаграма "Порівняльний аналіз обсягів закупівлі товарів у 20032004 рр.".

6.Побудувати об’ємну звичайну лінійчату діаграму, на якій відобразити виконання плану продажу товарів у 2003 році (рис. 10.3.). Додати до діаграми заголовок "Аналіз виконання плану продажу товарів у 2003

році". По осі значень вивести основні і проміжні лінії. Розмістити діаграму на робочому листі Лист2, назвати робочий лист Лист2 –

Діаграми.

6.1.Змінити колір стінок діаграми.

6.2.Змінити кольори, якими відображаються ряди даних.

6.3.Встановити ціну проміжних поділів ліній сітки – 10.

Рис. 10.3. Діаграма "Аналіз виконання плану продажу товарів у 2003 році".

28

7.Побудувати об’ємну гістограму з стовпцями у вигляді конусів і циліндрів, на якій відобразити виконання плану продажу товарів у 2004 році (рис. 10.4.). Додати до діаграми заголовок "Аналіз виконання плану продажу товарів у 2004 році". Розмістити діаграму на робочому листі Діаграми. Відформатувати діаграму згідно поданого зразка.

Рис. 10.4. Діаграма "Аналіз виконання плану продажу товарів у 2004 році".

8.Побудувати об’ємну розрізану кругову діаграму, на якій відобразити асортименту структуру обсягів фактичного продажу товарів у 2003 році (рис. 10.5.). Додати до діаграми заголовок "Асортиментна структура обсягів продажу товарів у 2003 році". Відобразити на діаграмі підписи даних – долі. Розмістити діаграму на робочому листі Діаграми. Відформатувати діаграму згідно поданого зразка.

8.1.Здійснити поворот діаграми таким чином, щоб найменший сектор був на першому плані.

8.2.Змінити колір найтемнішого сектора.

Рис. 10.5. Діаграма "Асортиментна структура обсягів продажу товарів у

2003 році".

29

9.Побудувати кільцеву діаграму, на якій відобразити асортименту структуру обсягів фактичного продажу товарів у 2004 році (рис. 10.6.).

Додати до діаграми заголовок "Асортиментна структура обсягів продажу товарів у 2004 році". Відобразити на діаграмі підписи даних – долі. Розмістити діаграму на робочому листі Діаграми. Відформатувати діаграму згідно поданого зразка.

Рис. 10.6. Діаграма "Асортиментна структура обсягів продажу товарів у

2004 році".

10. Роздрукувати таблицю та створені діаграми.

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

1.Об’єкти діаграми Excel.

2.Способи створення діаграм в середовищі MS Excel.

3.Зв’язок діаграми з робочим листом.

4.Друк діаграм.

5.Типи діаграм.

6.Майстер діаграм. Параметри діаграми, які встановлюються з допомогою діалогових вікон майстру діаграм.

Лабораторна робота №11.

Тема: Форматування діаграм MS Excel.

Мета: Набути та закріпити навички роботи з елементами діаграм різного типу. Закріпити навички форматування та редагування об’єктів діаграм MS Excel.

Хід роботи:

1.Відкрити табличний процесор MS Excel.

2.Відкрити документ ЛР10_Прізвище.xls, створений у лабораторній роботі

30