72704

Технологія створення та редагування таблиць

Лабораторная работа

Информатика, кибернетика и программирование

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

Украинкский

2014-11-27

334 KB

0 чел.

Лабораторна робота 1 (EXCEL).

(2г.)

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

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

Література:

  1.  Шафрин Ю. Основы компьютерной технологии. Учебное пособие.- М.,1996.
  2.  Ефимова О., Моисеева М., Шафрин Ю. Практикум по компьютерной технологии.-М.:ABF,1997.
  3.  Руденко В.Д., Макарчук О.М., Патланжоглу М.О. Практичний курс інформатики.-К.: Фенікс, 1997.
  4.  Конспект лекцій.

Теоретичні відомості.

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

Для запуску програми Excel слід виконати команду Пуск/Программы/Microsoft Excel, після чого на екрані з’явиться вікно табличного процесора. Вікно містить ряд типових елементів: рядок заголовка, рядок меню, яке включає такі пункти:

Файл - робота з файлами книг (створення, збереження, відкриття файлів, друкування файлів);

Правка - редагування книг;

Вид - перегляд книг;

Вставка - вставка у аркуші книг малюнків, діаграм та інших типів даних;

Формат - форматування книг (встановлення параметрів, форматів таблиць);

Сервис - сервісні функції (встановлення параметрів настройки Excel);

Данные - робота з базами даних;

Окно - робота з вікнами книг;

? - виклик довідкової інформації.

Наступні типові елементи:

  •  Панелі інструментів. Для вибору потрібної панелі слід скористатися командою Вид/Панель инструментов. За замовчуванням Excel виводить на екран панелі інструментів Стандартная і Форматирование.
  •  Рядок формул. Для введення та редагування формул, у першому вікні рядка виводиться адреса поточної чарунки.
  •  Рядок стану. У цей рядок виводиться інформація про хід виконання деяких операцій.

Основні елементи Excel. Книга – це документ, який створюється у табличному процесорі Excel. Кожна книга складається з листів таких типів: робочі листи, листи діаграм, листи макросів, модулів, листи діалогу. Робочі листи -  це електроні таблиці, що складаються з колонок і рядків. Максимальне число  колонок таблиці –  255, рядків –  16384.  Колонки  позначаються  зліва  направо  літерами - A…Z, AA-AZ…. Рядки позначаються зверху вниз цифрами від 1 до 16384. На перетині колонки і рядка розміщується чарунка. Позначення (адреса)

чарунок складається з позначення колонки та рядка. В Excel може одночасно існувати кілька вікон книг. Для маніпуляцій з вікнами використовують меню Окно. Табличний курсор переміщується по таблиці за допомогою клавіш керування курсором або за допомогою миші.

Excel має могутні засоби адаптації до вимог конкретного користувача. За їх допомогою можна змінити зовнішній вигляд екрану програми, параметри редагування, перегляду, збереження, друкування таблиць. Ці засоби реалізуються командою меню Сервис/Параметры. Вікно має ряд вкладок, розгорнувши вкладку, можна встановити відповідні параметри.

  •  Вкладка Вид дозволяє встановити або відмінити індикацію рядка формул і рядка стану, показ смуг прокрутки і ярликів листів та ін.
  •  Вкладка Общие дозволяє встановлювати різні режими для позначки адреси чарунок та ін.

Створення та збереження файлів книг. Робота з файлами книг виконується за допомогою пункту горизонтального меню Файл.

Створення таблиці. При створенні нового документа слід натиснути кнопку на панелі інструментів, або вибрати команду Файл/Создать. На екрані з’явиться нове вікно документа з іменем Книга1 або Книга 2, або…) для вводу даних таблиці.

Завантаження таблиці. Будь-яка таблиця, яка була збережена під певним іменем, може бути завантажена до процесору Excel для подальшого редагування або перегляду. Для цього слід скористатися командою Файл/Открыть або натиснути кнопку на панелі інструментів. На екрані з’явиться вікно діалогу, робота з яким відбувається за стандартними правилами, тобто треба вказати дисковод, папку, ім’я файлу.

Збереження книги. Для збереження таблиці слід виконати команду Файл/Сохранить або натиснути на панелі інструментів кнопку . Якщо книга нова і ця команда виконується для неї вперше, то відкривається вікно діалогу Сохранение документа, де можна вказати дисковод, папку, ввести ім’я, під яким файл буде збережений. За замовчуванням книгам Excel присвоює розширення xls, яке вказує на тип інформації файла та на програму-додаток, засобами якої цей файл був створений.

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

В разі потреби збереження файлу під новим іменем чи в іншій папці або з іншим форматом,  потрібно виконати команду Файл/Сохранить как…, на екрані з’являється вікно діалогу, в якому слід виконати дії для зміни папки розташування файлу чи змінити ім’я документа, чи вказати новий формат для збереження таблиці.

 Закриття файла. Після завершення роботи над таблицею можна закрити цей файл командою Файл/Закрыть або кнопкою у рядку заголовка.

Введення і редагування даних, форматування чарунок. Для введення або редагування даних у яку-небудь чарунку таблиці слід зробити цю чарунку активною. Перед введенням можна вибрати кнопками панелі інструментів Форматирование шрифт у списку Шрифт, його розмір, а також тип шрифту. Вводити дані в активну чарунку можна безпосередньо у полі самої чарунки чи в полі рядка формул. Натискування клавіші [Enter] завершує введення даних у чарунці, а клавіші [Esc] – відмінює його. Щоб відредагувати дані у поточній чарунці слід натиснути клавішу [F2].

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

За допомогою команди Формат/Ячейки можна змінювати числові формати даних поточної чарунки, включати різні режими розташування даних, переносу тексту по словам у межах однієї чарунки, змінювати шрифт, границі чарунок.

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


Арифметичні операції:

додавання – +

віднімання – -

множення – *

ділення – /

піднесення до степені – ^.

Операції відношень:

більше -  <

менше -  >

дорівнює –  =

менше або дорівнює - <=

більше або дорівнює -  >=

не дорівнює - <>.


Формула електронної таблиці починається зі знака рівності, а далі записуються числа чи адреси тих чарунок, в яких знаходяться потрібні числа, та знаки математичних операцій ( +, -, *, / ) .

Наприклад: = 25+B1*C3  - число, яке знаходиться у чарунці В1 помножиться на число, яке знаходиться у С3, а потім їх сума збільшиться на число 25.

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

Хід роботи.

  1.  Перед початком роботи з редактором ознайомтесь з довідковою інформацією (4-5 хвилин).
  2.  Познайомитися з зовнішнім виглядом вікна редактора Excel (рядком меню, панелями інструментів, рядками заголовків програми та документа, рядком формул).
  3.  Ввести таблицю1. Оформити бюджет сім’ї за квартал.

Бюджет сім’ї за квартал

Січень

Лютий

Березень

Сума за квартал

Дохід сім’ї

700

Комунальні послуги

100

Витрати на їжу

300

Оплата за діт. садок

25

Покупки

50

Подарунки

50

Сума витрат

Залишок

  1.  Відформатувати текст, чарунки та зовнішній вигляд таблиці як показано на прикладі.
  2.  Провести розрахунки у таблиці за наступними правилами:
  •  Дохід сім’ї однаковий кожний місяць.
  •  Витрати сім’ї складаються з наступних пунктів: комунальні послуги, оплата за дитячий садок, витрати на їжу, покупки різного роду та подарунки.
  •  Комунальні послуги - кожний місяць однакові.
  •  Оплата за дитячий садок - кожний місяць однакова.
  •  Витрати на їжу - кожний місяць збільшуються на 20гр.
  •  Покупки - кожний місяць збільшуються на 10%.
  •  Подарунки - кожний місяць збільшуються на 5 %.
  •  Підрахувати суму витрат по кожному місяцю.
  •  Підрахувати суму витрат по кожному пункту за квартал.
  •  Підрахувати загальну суму витрат.
  •  Підрахувати залишок по кожному місяцю та за квартал.
  1.  Зберегти файл у папці Мои документы.

 

Контрольні питання і завдання.

  1.  Що являє собою електрона таблиця?
  2.  Які призначення мають основні елементи вікон книг?
  3.  Які типи даних може містити чарунка?
  4.  Яким чином можна ввести та відредагувати дані в чарунках?
  5.  Які параметри включає поняття формат чарунок?
  6.  Як ввести формулу до чарунки таблиці?


Лабораторна робота 2 (EXCEL).

(2г.)

Тема: Технологія розрахунків у таблицях.

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

Література:

  1.  Шафрин Ю. Основы компьютерной технологии. Учебное пособие.- М.,1996.
  2.  Ефимова Б. Моисеев М., Шафрин Ю. Практикум по компьютерной технологии.-М.:ABF,1997.
  3.  Руденко В.Д., Макарчук О.М., Патланжоглу М.О. Практичний курс інформатики.-К.: Фенікс, 1997.
  4.  Конспект лекцій.

Теоретичні відомості.

 Виділення діапазону чарунок. Більшість команд Excel оперують з активною чарункою або діапазоном чарунок. Для виділення суміжного діапазону чарунок слід за допомогою миші (утримуючи ліву клавішу миші натиснуто) підсвітити потрібний прямокутник чарунок. Для виділення колонки або рядка слід клацнути мишею по заголовку колонки або номеру рядка. Для виділення несуміжного діапазону чарунок слід, утримуючи натиснуто клавішу [Ctrl], пересувати курсор миші по чарунках, які потрібно виділити.

Виділити всі чарунки можна за допомогою команди Правка/Выделить все. Чарунки виділеного діапазону відображаються інверсним кольором, за винятком активної чарунки, яка зберігає звичайний колір. Для зняття виділення слід клацнути мишею поза виділеним діапазоном.

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

Робота команд Правка/Копировать, Правка/Вырезать, Правка/Вставить нічим не відрізняється від аналогічних команд буфера обміну середовища Windows. Перемістити або скопіювати діапазон чарунок швидше і зручніше за допомогою миші. Переміщення – встановити курсор миші на межі виділеного діапазону (курсор набуває форми стрілки), натиснути ліву кнопку миші і відбуксувати чарунки у потрібне місце. Копіювання відбувається аналогічно, але при цьому слід тримати натиснуто клавішу [Ctrl] (курсор набуває форми стрілки з хрестиком).

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

Очистка діапазону чарунок відбувається за допомогою команди Правка/Очистить. На екрані з’являється діалогове вікно, в якому слід вказати що потрібно очистити: формат, значення, примітку.

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

Наприклад:

Адреса виділеного діапазону чарунок: В2:В5.

За замовчуванням встановлюється режим автоматичного обчислення, тобто зміна вмісту чарунок веде до перерахунку формул, які використовують ці чарунки. Для встановлення ручного режиму обчислень слід активізувати команду Сервис/Параметры і у вкладці Вычисления встановити Ручной режим.

Посилання на чарунки бувають відносні, абсолютні та змішані.

Посилання, яке включає назву колонки і номер рядка, є відносним. При копіюванні формули таке посилання модифікується. При копіюванні формули вліво чи вправо – змінюється назва колонки.

Наприклад: формулу “=С3+25” скопіювали вліво на 1 колонку, формула змінилась на “=В3+25”, в разі копіювання вправо формула стала виглядати, як “=D3+25”.

При копіюванні формули вниз або вверх – змінюються номери рядків. Якщо скопіювати формулу на 2 рядка вверх, то номер рядка зменшиться на 2 одиниці, якщо донизу – збільшиться.

В абсолютних посиланнях перед назвою колонки або номером рядка стоїть спеціальний символ - $. Такі посилання не модифікуються, тобто при копіюванні, переміщенні такі адреси залишаються без змін. Наприклад: адреса -  $F$6.

У змішаних посиланнях абсолютною є назва колонки або номер рядка. У них модифікується тільки відносна частина посилання. Наприклад: адреси - $G3; B$3.

Якщо у формулі є посилання на чарунки, які знаходяться на іншому листі, то посилання повинно містити ім’я листа, знак оклику та адресу чарунки. Наприклад: Лист3!В2.

Аналогічно додається назва файлу, якщо у формулі є посилання на чарунки іншого файлу. Наприклад: Книга2!Лист3!С5.

Excel дає можливість назначати імена чарункам, робиться це за допомогою команди Вставка/Имя/Присвоить… У формулі можуть використовуватися посилання на чарунки, які містять не адреси, а їх імена.

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

Наприклад: =СРЗНАЧ(A2:А10), значенням цієї функції буде середнє арифметичне чисел, які знаходяться у діапазоні чарунок від А2 до А10.

Функція суми використовується у електронних таблицях часто, - тому для неї в панелі інструментів Стандартная знаходиться спеціальна кнопка (Автосуммирование). Якщо активізувати чарунку та натиснути кнопку , то з’явиться формула суми, яку можна модифікувати. При подвійному натискуванні на , результат відразу фіксується у чарунці, автоматично обчислюється сума чисел, які знаходяться над поточною чарункою або зліва від неї.

Помилки у формулах. Якщо при обчисленні формули сталася помилка, то в чарунку виводиться повідомлення про помилку, яке починається із символу «#». Excel виводить такі повідомлення про помилки:

#ДЕЛ/0! – спроба поділити на нуль або на порожню чарунку.

#ИМЯ? – формула використовує не існуюче ім’я (іноді адресу чарунки вводять російськими буквами).

#ЗНАЧ! – помилка при обчисленні функція (наприклад, замість посилання на число у арифметичній формулі використовується посилання на текст).

#ССЫЛ! – формула посилається на неіснуючу чарунку.

#Н/Д! – формула посилається на чарунку з невизначеними даними.

#ЧИСЛО! – помилка у числі, число неможливо подати в Excel.

####### - число не вміщується у чарунку даного розміру, треба збільшити ширину чарунки.

Хід роботи.

  1.  Оформити звіт про товарообіг за поточний день по даному зразку.  
  2.  За допомогою команди Формат/Ячейки/Выравнивание розвернути текст у деяких заголовках на 90º (дивіться зразок).
  3.  За допомогою команди Формат/Ячейки/Выравнивание у деяких заголовках включити перемикач для переносу тексту по словам (дивіться зразок).
  4.  Для інших чарунок, де знаходяться заголовки, вибрати вирівнювання по центру.
  5.  За допомогою кнопки Объединить и поместить в центре з’єднати потрібні чарунки у заголовках таблиці (дивіться зразок).
  6.  За допомогою команди Формат/Ячейки/Число у всіх колонках, де буде писатися ціна товару та сума грошей перейти до грошевого формату.

Прихід

Витрати

Залишок

№

Відділ

Назва товару

Одиниці вимірювання

ціна

кількість

ціна

кількість

сума

кількість

сума

1

Кондитерській

Зефір

кг

4,60р.

15

8

2

Кондитерський

Шоколад

шт.

1,30р.

25

6

3

Кондитерський

Рулет

упак.

4,20р.

10

4

4

Кондитерський

Торт

шт.

8,00р.

8

3

5

Канцтовари

Зошит

шт.

0,65р.

30

22

6

Канцтовари

Олівці

упак.

3,40р.

14

11

7

Канцтовари

Лінійка

шт.

0,50р.

25

16

8

Канцтовари

Ручка

шт.

0,85р.

45

30

Ітого:

Мінімум

Максимум

  1.  Провести обчислення у таблиці згідно з наступними положеннями:
  •  Ціна витрат збільшується відносно  ціні приходу на 15%.
  •  Сума грошей, отриманих за продаж кожного виду товару дорівнює:

«=Ціна витрат * Кількість витрат».

  •  Кількість товару, який залишився у магазині дорівнює

«=Кількість приходу – Кількість витрат».

  •  Сума грошей за товар, який залишився, дорівнює: «=Ціна витрат * Кількість залишку».
  1.  Підрахувати за допомогою команди Автосуммирование загальну суму грошей, отриманих після продажу товару за поточний день.  
  2.  За допомогою майстра функцій знайти за продаж якого товару було вилучено найменш грошей, а за який товар – найбільш. Функції МИН та МАКС знаходяться у категорії функцій Статистические.
  3.  За допомогою команди Формат/Ячейки розфарбувати рядки заголовків, провести границі між чарунками, для цього слід активізувати вкладки Граница та Вид.
  4.  Зберегти Книгу.

Контрольні питання і завдання.

  1.  Які формати чисел підтримує Excel?
  2.  Як вибирається обрамлення для заголовків колонок і рядків створеної таблиці?
  3.  Що таке діапазон чарунок? Якими способами можна виділити діапазон чарунок?
  4.  Які операції редагування доступні для виділеного діапазону?
  5.  Які типи посилань на чарунки використовують у формулах?
  6.  Якими способами можна вставити функцію у формулу?
  7.  Як за допомогою миші спростити ручний ввід формули?
  8.  Описати два засоби використання функції додавання.
  9.  Які типи помилок можливі при обчисленні формул?


Лабораторна робота 3-4 (EXCEL).

(4г.)

Тема: Технологія застосування функцій у таблицях.

Операції редагування таблиць. Фільтрація даних.

Мета: Познайомитися з загальними правилами редагування таблиць. Отримати навички роботи з логічною функцією ЕСЛИ та з функцією ПРОСМОТР за допомогою майстра функцій. Використання складних функцій. Навчитися використовувати автофільтр та розширений фільтр.

Література:

  1.  Шафрин Ю. Основы компьютерной технологии. Учебное пособие.- М.,1996.
  2.  Ефимова О., Моисеева М., Шафрин Ю. Практикум по компьютерной технологии.-М.:ABF,1997.
  3.  Руденко В.Д., Макарчук О.М., Патланжоглу М.О. Практичний курс інформатики.-К.: Фенікс, 1997.
  4.  Конспект лекцій.

Теоретичні відомості.

Розглянемо деякі приклади використання складних функцій. При розв’язанні багатьох задач значення чарунки необхідно обчислювати однім з декількох засобів, в залежності від виконання або невиконання умови. Для розв’язання таких задач застосовують умовну функцію ЕСЛИ. Ця функція має формат:

ЕСЛИ (<логічне вираження>, < значення 1>, < значення 2>).

Перший аргумент – логічне вираження приймає одне з двох значень: «Істина» або «Неправда». В першому випадку (якщо значення істинно) функція ЕСЛИ приймає значення1, а в другому (значення неправдиво) – значення2. В якості значення може бути число, формула  або інша функцію. Наприклад: Маємо дві функції ЕСЛИ, одна вкладена в другу:

ЕСЛИ(<логічне вираження1>; < значення1_1>; ЕСЛИ(<логічне вираження2>;< значення2_1>; < значення2_2>)).

Функція ПРОСМОТР шукає значення в векторі або масиві даних. Ця функція має різні списки аргументів, розглянемо один з списків аргументів:

ПРОСМОТР (розшукуване значення; масив).

Розшукуване значення – це значення, яке функція ПРОСМОТР шукає в означеному масиві даних, значення може бути числом, рядком, ім'ям або посиланням на значення. Масив – це діапазон чарунок, де міститься текст, числа або логічні значення, серед яких потрібно знайти розшукуване значення.

Фільтр являє собою конструкцію, призначену для відбору тих рядків таблиці, що задовольняють заданій умові, і тимчасового приховання інших. Основою фільтру є список, що містить умови відбору рядків. В першу чергу необхідно виділити діапазон, для якого буде створений фільтр. Вибрати команду Данные/Фильтр/Автофильтр. Після цього автоматично створюється в чарунках верхнього рядка виділеного діапазону спеціальна кнопка ▼, що розкриває список фільтру (мал.1).

Натиснувши на кнопку ▼, можна вибрати один із варіантів відбору даних: перші десять рядків списку, задати умову фільтрації та ін. Умова користувача може складатися з одного або двох логічних виразів. В останньому випадку вираження з'єднуються логічними операндами  И або ИЛИ (мал.2).

Щоб зняти фільтр необхідно повторно вибрати команду Данные/Фильтр/Автофильтр. Режим фільтрації можна відмінити командою Данные/Фильтр/Показать все.

Розширений фільтр використовується для фільтрації списків тоді, коли звичайний фільтр пасує. Термін список в Excel вживають для позначки самої звичайної таблиці. Таблиця-список повинна мати колонки з заголовками, в колонках повинні знаходитися дані одного типу. А в кожному рядку списку – дані про один об'єкт.

Звертання до розширеного фільтра виконується за допомогою команди Данные/Фильтр/Розширенный фильтр. На екрані з’являється діалогове вікно, де треба вказати діапазон чарунок, в яких знаходяться данні, які треба фільтрувати, та вказати діапазон умов або критеріїв по яким будується фільтр (мал.3). Критерій в даній операції створюється як таблиця (блок умов для даних), що може зберігатися в будь-якому місці робітничої книги або навіть в іншому файлі. Результат фільтрації можна показати в поточній таблиці, а можна вивести в інше місце, так звану цільову область, яка теж оформлюється користувачем як самостійна таблиця. Знімається фільтр командою Данные/Фильтр/Показать все.

Хід роботи.

  1.  Оформити Довідник посад, що містить оклади. Загальна кількість робітничих днів є константою для кожного місяця.

Довідник посад.

Довідник посад

Бухгалтер

Інженер

Начальник

Програміст

Кіл-ть роб. днів

Оклад

180

140

240

220

19

  1.  На тому ж самому листі оформити Відомість заробітної плати по відділу за січень.

 

Відомість заробітної плати

Січень

Прізвища

Посада

Відпрац.

дні

Начислено

Премія

Сума

Прибутковий

податок

Пенсійний

фонд

Сума до видання

Іващенко

Начальник

19

Сидорук

Бухгалтер

18

Коваленко

Програміст

17

Гаврилов

Програміст

19

Денисенко

Інженер

16

Петренко

Інженер

10

Давидов

Інженер

19

Карпенко

Інженер

10

Симоненко

Інженер

18

Ітого:

Підрахувати суму налічених грошей за відпрацьовану кількість днів по формулі:

«Налічене =(Оклад/Загальна кіл-ть днів)*Відпрац. дні»

  •  Оклад слідує одержувати з таблиці Довідник посад, застосувавши функцію ПРОСМОТР, де розшукуване значення – це посада кожного з робітників, а масив – це діапазон чарунок, в яких розміщена таблиця Довідник посад (слідіть за однаковістю в назві текстових полів).
  •  Премія дорівнює 20% від налічених грошей.
  •  Загальна сума грошей обчислюється по формулі: «Сума=Налічене+Премія»
  1.  Підрахувати Прибутковий податок, який розраховується з наступних умов:

Якщо Сума < 100, то прибутковий податок дорівнює 0.

Якщо Сума < 200, то прибутковий податок дорівнює 10% від Суми.

Якщо Сума >=200, то прибутковий податок дорівнює 20% від Суми.

(Використати  вкладені функції ЕСЛИ).

  1.  Підрахувати Пенсійний фонд, який дорівнює 2% від Суми.
  2.  Підрахувати Суму до видавання, яка розраховується по формулі:

«Сума до видавання=Сума – Прибутковий податок – Пенсійний фонд»

  1.  Підрахувати загальну суму по полю  Сума до видавання.
  2.  Скласти аналогічну відомість для лютого. Для цього на Лист2 скопіювати таблицю  Довідник посад, змінити в ній дані по полю Кількість відпрацьованих днів: для лютого – 17, та скопіювати таблицю Відомість заробітної плати, де в полі Відпрацьовані дні внести зміни згідно табеля. Простежити за змінами, які відбуваються автоматично при переобчисленні формул.

Табель Відпрацьованих днів за місяць лютий:

Прізвища

Відпрац. дні

Іващенко

15

Сидорук

16

Коваленко

17

Гаврилов

17

Денисенко

16

Петренко

14

Давидов

17

Карпенко

10

Симоненко

12

  1.  Скласти на Листі3 аналогічну відомість для березня. Кількість відпрацьованих днів: для березня – 21. Табель Відпрацьованих днів за місяць березень:

Прізвища

Відпрац. дні

Іващенко

20

Сидорук

16

Коваленко

21

Гаврилов

17

Денисенко

19

Петренко

18

Давидов

17

Карпенко

21

Симоненко

20

  1.  Оформити на Листі4 загальну відомість за квартал.

Загальна відомість за квартал

Прізвища

Посада

Сума

Іващенко

Начальник

Сидорук

Бухгалтер

Коваленко

Програміст

Гаврилов

Програміст

Денисенко

Інженер

Петренко

Інженер

Давидов

Інженер

Карпенко

Інженер

Симоненко

Інженер

  1.  Підрахувати суму грошей, отриманих кожним робітником за квартал. Сума дорівнює сумі грошей, отриманих за кожний місяць ( у формулі для обчислення суми повинні бути посилання на чарунки Листа1, Листа2, Листа3, де знаходяться суми, які були отримані за кожен місяць).
  2.  Зробити Автофильтр (автоматичний фільтр) в таблиці Відомість заробітної плати за березень. Показати всіх робітників, які отримали суму грошей > 150 (пункт меню Данные/Фильтр/Автофильтр).
  3.  Зняти автофільтр.
  4.  Побудувати Розширений фільтр (пункт меню Данные/Фильтр/Розширенный фильтр). Основною базою є таблиця Відомість заробітної плати за березень. Спочатку результат фільтрації слід розмістити на місці базової таблиці (у діалоговому вікні Розширеного фільтру включити перемикач Фильтровать список на месте). У вікні Диапазон условий  вказати діапазон чарунок, де знаходиться наступна таблиця:

Діапазон умов -

Посада

Відпрац. дні

Інженер

>=18

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

Цільова область -

Прізвища

Начислено

Сума до видання

  1.  Зберегти Книгу.

Контрольні питання і завдання.

  1.  В якому форматі записується функція ЕСЛИ?
  2.  В якому форматі записується функція ПРОСМОТР?
  3.  Чим відрізняється функція ЕСЛИ від інших функцій? Складіть приклади випадків, в яких необхідно використати функцію ЕСЛИ?
  4.  Що таке Майстер функцій? Як з ним працювати?
  5.  Формулу перенесли на інший лист робочої Книги. Які зміни потрібне зробити в формулі, щоб не порушити обчислення?
  6.  Чи існують функції без аргументів?
  7.  Для чого необхідна фільтрація даних?
  8.  Які операнди використовуються в умові відбору при автоматичному фільтрі?
  9.  Чим відрізняється автоматичний фільтр від розширеного?
  10.  Як відмінити фільтрацію даних?


Лабораторна робота 5 (EXCEL).

(2г.)

Тема: Технологія побудови діаграм. Сортування та фільтрація даних.

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

Література:

  1.  Шафрин Ю. Основы компьютерной технологии. Учебное пособие.- М.,1996.
  2.  Ефимова О., Моисеева М., Шафрин Ю. Практикум по компьютерной технологии.-М.:ABF,1997.
  3.  Руденко В.Д., Макарчук О.М., Патланжоглу М.О. Практичний курс інформатики.-К.: Фенікс, 1997.
  4.  Конспект лекцій.

Теоретичні відомості.

Excel дозволяє вставити в робочий лист об’єкти, створені іншими програми, наприклад, текстові документи Word, малюнки графічного редактора Paint. Найпопулярнішим майстром в Excel є Майстер діаграм, який дозволяє будувати діаграми 15 різних типів. Кожен тип діаграми має декілька видів. Початковий етап в розробці діаграми полягає в підготовці блоку даних для відображення на діаграмі. Щоб розпочати конструювання діаграми, необхідно виділити блок даних і натиснути кнопку майстра діаграм або вибрати пункт меню Вставка/Диаграмма. Щоб виділити для блоку даних стовпці, які не знаходяться один біля одного, скористуйтесь клавішею [Ctrl].

На екрані з’являється діалогове вікно майстра діаграм. Спочатку слід вибирати тип діаграми, а потім вказати конкретний її вид. За допомогою кнопки Просмотр результата  ви маєте змогу передивитися, як будуть виглядати вибрані вами дані у вигляді діаграми (мал.1).

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

Легенда – це інформація, яка пояснює дані, по яким будується діаграма. На останньому крокі вказується місце для розміщення діаграми. Діаграма може будуватись на активному листі або на новому. Зміна діалогових вікон відбувається за допомогою кнопки Далее. На кожному кроку роботи майстра можна повернутися до попередньої інформації за допомогою кнопки Назад або припинити побудову діаграми кнопкою Отмена. Після натискання  кнопки Готово на екрані з’являється діаграма.

Редагування діаграми. Перед редагуванням діаграму треба обов’язково виділити, це робиться клацанням миші. Всі операції редагування можна знайти у контекстному меню, або користуватися мишею. Діаграму можна переміщувати, міняти розміри, вилучати. Після побудови діаграми виводиться панель інструментів, за допомогою якої ви можете відредагувати вже створену або побудувати нову діаграму. Якщо панелі інструментів Диаграммы не має на екрані, її можна визвати за допомогою команди Вид/Панели инструментов/Диаграммы (мал.2).

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

Іноді для аналізу чи для роботи з даними їх зручніше розташовувати у певному порядку, тобто відсортовувати.  Упорядкувати дані можна за будь-яким полем (стовпцем) у алфавітному, числовому або хронологічному порядку. Сортування рядків таблиці здійснюється командою Данные/Сортировка. Але перед цім слід виділити таблицю, де розташовані дані. На екрані з’являється діалогове вікно, в якому треба вказати стовпець, де знаходяться дані, які будуть відсортовані та спосіб сортування: по спаданню або зростанню. За  допомогою кнопки Параметры можна вказати деяку додаткову інформацію для сортування. Після натискання на кнопку ОК рядки таблиці переміщуються згідно умові сортування (мал.3).

Хід роботи.

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

Ріст населення.

1970р.

1980р.

1990р.

2000р.

Приріст населення

Європа

738 412

794 420

913 917

Африка

362 788

481 034

713 351

Америка

511 607

613 564

814 077

  1.  Підрахувати чисельність населення у 1990р. по наступним даним:

У 1990р. населення збільшилось:

Європа – на 6%.

Африка – 35%.

Америка – 18%.

  1.  Підрахувати на скільки відсотків збільшилось населення за останні 10 років, тобто між 2000р. та 1990р., по кожному пункту.
  2.  Отриману таблицю Ріст населення відсортувати по полю 2000р. по зростанню (пункт меню Данные/Сортировка). Прослідкувати за переміщенням рядків таблиці.
  3.  Побудувати гістограму по таблиці «Ріст населення» за 1970 - 2000р.р. Дати назву гістограмі та висвітити легенду.
  4.  Побудувати кругову діаграму по одному з пунктів даної таблиці, наприклад, по рядку «Європа». Перенести цю діаграму на інший лист, збільшити розміри діаграми.
  5.  За допомогою контекстного меню внести зміни у надписи до діаграми: надписати долі даних чи їх значення.
  6.  Вставити колонтитули: у верхньому колонтитулі написати назву таблиці, у нижньому – поточну дату.
  7.  Зберегти Книгу.
  8.  У текстовому редакторі Word написати за допомогою фігурного тексту назву статті та 3-4 речення, які були б присвячені темі: Ріст населення.
  9.  Перенести за допомогою буфера обміну таблицю Ріст населення та діаграму з процесору Excel до набраного тексту.
  10.  Вставити готовий малюнок до тексту по цій же тематиці або намалювати його у графічному редакторі. Розташувати всі ці об’єкти так, щоб вийшла невелика замітка.
  11.  Роботу показати викладачу.

Контрольні питання і завдання.

  1.  Перерахувати засоби запуску Майстра Діаграм.
  2.  Що таке Майстер Діаграм? Опишіть етапи побудови діаграми.
  3.  Що відображається у Легенді діаграми?
  4.  Які основні елементи містить діаграма?
  5.  Яким чином можна відредагувати елементи діаграми?
  6.  Як виконати сортування даних через горизонтальне меню?
  7.  Описати процес з’єднання у одному документі об’єктів різного типу.

PAGE  15