Установлення інтервалу критеріїв

Критерії бувають двох типів.

1. Критерії обчислення – це критерії, що є результатом обчислення формули. Наприклад, інтервал критеріїв =F7>СРЗНАЧ($F$7:$F$21) виводить на екран рядки, що мають у стовпці F значення більше, ніж середнє значення розмірів у вічках F7:F21. Формула повинна повертати логічне значення ЛОЖЬ абоИСТИНА. При фільтрації будуть доступні тільки ті рядки, значення яких будуть додавати формулі значення ИСТИНА.

2. Критерії порівняння – це набір умов для пошуку, використовуваний для витягу даних при запитах за прикладом. Критерій порівняння може бути послідовністю символів (константою) або вираженням (наприклад, Ціна > 700).

Для пошуку за допомогою форми даних записів, що відповідають критерію, необхідно:

­ виділити вічко в таблиці;

­ у меню Данные вибрати команду Форма;

­ натиснути кнопку Критерии;

­ у полях редагування увести критерії для пошуку даних;

­ для виводу на екран першого запису, що відповідає критерію, натиснути кнопку Далее;

­ для виводу на екран попереднього запису, що відповідає критерію, натиснути кнопку Назад;

­ для пошуку записів у переліку по іншому критерії натиснути кнопку Критерии і увести новий критерій;

­ по закінченні натиснути кнопку Закрыть.

Щоб знову одержати доступ до усіх записів переліку необхідно натиснути кнопку Критерии, а потім натиснути кнопку Правка.

Команда Фильтр меню Данные дозволяє відшукувати і використовувати потрібну підмножину даних у переліку. У відфільтрованому переліку виводяться на екран тільки ті рядки, що містять визначене значення або відповідають визначеним критеріям, при цьому інші рядки виявляються приховані. Для фільтрації даних використовуються команди Автофильтр і Расширенный фильтр з пункту Фильтр меню Данные.

Автофильтр

Команда Автофильтр установлює кнопки прихованих переліків (кнопки зі стрілкою) безпосередньо в рядок із іменами стовпців (мал.35). З їхньою допомогою можна вибрати записи бази даних, що слід вивести на екран. Після виділення елементу в переліку, що розкривається, рядки, що не містять даний елемент, будуть приховані. Наприклад, якщо у прихованому переліку поля Цена вибрати 99грн., то будуть виведені тільки записи, в яких у полі Цена міститься значення 99грн.

 

Мал. 35

 

Мал. 36

Якщо у прихованому переліку вибрати пункт Условие …, то з’явиться вікно Пользовательский автофильтр (мал.36). У верхньому правому переліку слід вибрати один з операторів (равно, больше, меньше та ін.), а у полі праворуч вибрати одне зі значень. У нижньому правому переліку можна вибрати іншій оператор, і у полі ліворуч – значення. Коли увімкнений перемикач И, то будуть виводитися тільки записи, які задовольняють обидві умови. При увімкненому перемикачу ИЛИ будуть виводитися записи, які задовольняють одну з умов. Наприклад, у вікні на мал.36 введені умови для виведення записів по виробам з ціною більше 99грн і менше 187грн.

Для виведення декількох записів з найбільшими або найменшими значенням по будь-якому полю слід у прихованому переліку поля вибрати пункт Первые 10. У діалоговому вікні Наложение условия по списку у першому полі з лічильником необхідно вибрати кількість записів, а у полі праворуч вибрати наибольших або наименьших.

Щоб вивести усі дані переліку, необхідно викликати команду Отобразить все або скасувати команду Автофильтр меню Данные, підміню Фильтр.

Расширенный фильтр

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

 

Мал. 37

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

скопировать результат в другое место – копіює відфільтровані дані на інший робочий аркуш, або на інше місце на тому ж робочому аркуші;

Исходный диапазон – поле, що визначає інтервал, якій містить перелік, що підлягає фільтрації;

Диапазон условий – поле, що визначає інтервал вічок на робочому аркуші, якій містить необхідні умови;

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

Только уникальные записи – перемикач, що виводить тільки рядки, що задовольняють критерію і не містять неповторюваних елементів. Якщо інтервал критеріїв не визначений, то в цьому випадку усі рядки переліку, що містять дублікати, будуть приховані.

Для установлення складних критеріїв необхідно:

1. уставити декілька рядків у верхній частині робочого аркушу;

2. в одному із уставлених порожніх рядків увести імена стовпців, по яких слід відфільтрувати перелік;

3. при використанні критеріїв порівняння, імена критеріїв повинні бути ідентичні іменам стовпців, що перевіряються;

4. у рядках, розташованих під рядком із іменами стовпців, що перевіряються, увести критерії, яким повинні відповідати вічка стовпців, що перевіряються;

5. вибрати в меню Данные підміню Фильтр, а потім команду Расширенный фильтр, і в діалоговому вікні увести умови фільтрації.

 

Мал. 38

Для об'єднання критеріїв за допомогою умовного оператораИ потрібно зазначити критерії в одному і тому ж рядку, а для об'єднання критеріїв за допомогою умовного оператора ИЛИ слід подати критерії в різних рядках. Наприклад, інтервал критеріїв на мал.38 виводить на екран усі записи, що мають у стовпці Цена значення більше 50 і менше 100.

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

Щоб знову вивести усі записи слід у меню Данные вибрати пункт Фильтр і потім пункт Отобразить все.

 

 

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

 

1. Для чого призначена програма Excel?

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

3. Що необхідно зробити для встановлення програми Excel?

4. Як завантажити програму Excel?

5. Як називається документ в табличному процесорі?

6. Скільки стовпців в електронній таблиці?

7. Як позначаються назви рядків в Excel?

8. Що необхідно виконати для збереження книги?

9. Яких типів можуть бути листи книги?

10. Де вказується кількість листів у книзі?

 

 

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

Тема

Excel. Структура таблиці і основні типи даних.Створення таблиць і введення даних.

Мета

Уміти налаштовувати параметри робочої сторінки, вводити текстові, числові дані і формули в таблицю, редагувати дані, форматувати дані і таблицю, копіювати формули з відносними адресами.

Задача

Підготувати таблицю, де зафіксована купівля декількох найменувань (шести-восьми) товарів. Вхідні дані: тип (авто­мобілі, книги, телевізори, косметика, комп'ютери, касети, одяг, літаки, іграшки, ліки, запчастини, продукти, овочі тощо), назву, ціну, кількість товарів задайте самостійно.

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

Електронна таблиця (ЕТ) — це програма, призначена для опрацювання даних, наведених у вигляді таблиці бухгалтерського, економічного чи статистичного характеру, а також для автомати­зації математичних обчислень.

ЕТ складається з клітинок (комірок, чарунок), що утворюють рядки і стовпці. Стовпці таблиці позначені буквами (А, В, С,..., Z, АА, АВ... AZ, ВА...), а рядки цифрами (1, 2...). Кожна клітинка має адресу, наприклад, А1— адреса лівої верхньої клітинки. Стовпців може бути до 256, а рядків до 65536.

Заповнені клітинки утворюють робочу таблицю. Робоча таблиця міститься на робочій сторінці (аркуші). Сторінка має назву, наприклад Лист 1, яка відображена на бірці внизу екрана). Декілька робочих сторінок утворюють робочу книжку. Книжка зберігається у файлі з розширенням xls (у випадку ви­користання програми MS Excel).

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

Щоб виконати якусь дію над клітинкою чи її даним, клітинку потрібно виокремити (вибрати, активізувати). Це роблять за допомогою клавіш зі стрілками або миші. Активна (виокремлена) клітинка має рамку з маркером, який є у правому нижньому куті. З нею можна виконувати дії, визначені в основному чи контекст­ному меню: ввести чи вилучити дане, скопіювати чи перемістити дане в буфер обміну, очистити клітинку, відформатувати дане чи клітинку, вставити примітку.

Виокремлювати можна не лише одну, але й декілька клітинок (рядків чи стовпців).

Щоб увести в клітинку дане, її виокремлюють, набираю дане на клавіатурі і натискають на клавішу вводу або на клавішу Tab. Під час введення дане можна редагувати. Уведений у клітинку текст (до 255 символів) автоматично вирівнюється до лівого краю, а числа — до правого.

Якщо почати вводити нове дане у клітинку, то старе пропадає.

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

1) двічі клацають мишею;

2) натискають на клавішу F2.

Вилучити з клітинки дане, примітку, формат даного можна командами з меню: Редагувати → Очистити →Все.

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

Відображення даного залежить від формату його зображення. Формати чисел у вибраних клітинках задають командою Формат → КЛІТИНКИ → Вибирають закладну Число.Для роботи з числами корисним є формат Числовий, де можна задати кількість десят­кових знаків після коми. Є й інші формати: загальний, грошо­вий, фінансовий, дата, час, процентний, дробовий, експоненціальний, текстовий, додатковий (поштовий індекс, номер телефону, табельний номер), усі формати користувача. Наприклад, число 1230,5 у форматі користувача # ##0,00грн.;[Червоний]-# -#0,00грн. буде зображене на екрані у грошовому форматі 1 230,50грн., а від'ємне таке ж число буде зображене червоним кольором. Символ 0 у форматі - це вказівка відображати у відповідній позиції конкретну цифру або нуль, символ # - лише значущі цифри, пропуск забезпечує відокремлення груп цифр.

Розділювачем цілої і дробової частини в числах може бути крапка або кома залежно від налаштування Windows. Розділювач можна поміняти на закладці Числа у вікні програми Мова і стандарти панелі керування (Пуск → Налаштовування → Панель керування → Мова і стандарти).

Над таблицею є рядок формул (якщо він увімкнений). У ньому висвітлюється дане чи формула, які вводять або які вже є в клітинці.

Формули призначені для виконання дій над змістом клітинок (над даними) згідно з умовою конкретної задачі. Вони мають символ = на початку, наприклад, =В2*С2. Після введення форму­ли у клітинці відображається результат обчислень, а формулу можна побачити лише у рядку формул.

Щоб побачити всі формули у таблиці, треба задати режим відо­браження формул у клітинках. Це роблять у діалоговому вікні Пара­метри так: Сервіс → Параметри → Закладка Вигляд → формули

Щоб знову побачити результати обчислень, потрібно зняти режим відображення формул.

Якщо замість результатів ви отримали ######, то це озна­чає, що велике число в клітинці не поміщається, отже, стовпець треба зробити ширшим, перетягнувши межу в заголовку стовпця.

Обчислення в таблиці ведуться автоматично. Це означає, що зміна будь-якого вхідного даного одразу ж веде до переобчислень всієї таблиці (якщо задано режим 0 Автоматично на закладці Обчислення діалогового вікна Параметри). Режим Автоматично можна вимкнути і скористатися ручним переобчисленням за допомогою клавіші F9.

Адреси клітинок вигляду ВЗ чи СЗ називаються відносними.

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

Під час копіювання формули відбуваються такі дії:

§ формула вводиться в інші клітинки автоматично;

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

Наприклад, під час копіювання формули = ВЗ*СЗ з третього рядка у четвертий формула в четвертому рядку набуде вигляду = В4*С4.

Копіювання виконують методом перетягування маркера клі­тинки у потрібному напрямку. Це інакше називають автозаповненням таблиці.

Копіювати можна не тільки формули, а й текст і числа.

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

Копіювання формул і автоматичне переобчислення у таблиці — це два основні засоби автоматизації обчислень в ЕТ.

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

Зазвичай таблиця на екрані має сітку, якщо увімкнуто режим відображення сітки, однак під час друкування на папері вона не відображається. Щоб таблиця була відповідним чином розграфлена на папері, треба задати параметри на закладці Межі діалогового вікна Формат клітинок: Формат → Клітинки → межі або, що зручніше, скористатися відповідними кнопками на панелі форматування.

Розглянемо інші закладки вікна Формат клітинок.

На закладці Вирівнювання задають спосіб написання тексту в клітинці: горизонтально, вертикально, під кутом і режим пере­несення тексту по словах (коли потрібно, щоб великий текст був у вузькій клітинці).

На закладці ВИГЛЯД можна задати колір клітинок і узор. На закладці Захист можна зняти чи задати режими захисту клітинок від несанкціонованих змін і ховання формул. Щоб унеможливити внесення змін до захищених клітинок, захист з зазначенням паролю або без нього треба задати ще командоюСервіс → Захист → Захистити лист.

Хід роботи

1. Запустіть програму Excel.

Відкриється порожня книжка (в іншому випадку створіть нову книжку).

2. Перевірте, які задано параметри для першої сторінки.

Сервіс → Параметри → Закладка Вигляд. Задайте відображення рядків формул і стану, приміток з індика­торами, об'єктів, сітки, заголовків, смуг прокручування, бірок сторінок, відмініть режим відображення формул (зверніть лише увагу на ці параметри, оскільки більшість з них уже задана). Задайте режим 0 Автоматично на закладці Обчислення і відмі­ніть П Ітерації, якщо вони були задані. На закладці Загальні задайте стандартний кирилізований шрифт.

3. Закрийте діалогове вікно Параметри (ОК).

4. Розгляньте панель форматування.

Панелі Стандартна і Форматування мають бути ввімкнені, всі інші викнені командою ВИГЛЯД => Панелі Інструментів. Пере­сувайте повільно курсор над кнопками панелі форматування до появи назв і переписуйте їх у звіт.

Товарний чек
Номер	Назва	Ціна	Кількість	Сума
	Книга	35,0,00 грн.

5. Введіть дані для розв'язування задачі 1 так:

 

 

6. Перемкніть клавіатуру на англійський (En) алфавіт.

7. Уведіть формули так:

Адреса Формула

ЕЗ =C3*D3

E4 =C4*D4 (Скористайтеся методом копіювання формул)

Е5 =C5*D5

Е6 =C6*D6

Е7 =C7*D7

E8 =C8*D8

E9 =C9*D9

Ell =E3+E4+E5+E6+E7+E8+E9 або =СУММ(ЕЗ:Е9)

Яке значення Всього у клітинці Е11? Перепишіть відповідь у звіт.

8. Сформатуйте числа в стовпцях С і Е.

Виокремте лише числові дані у стовпці С. Щоб виокремити діапазон діапа­зон даних, виберіть клітинку СЗ, клацніть мишею і перетягніть білий хрестоподібний курсор у цьому випадку вниз. Натисніть на кнопки Збільшити чи Зменшити розрядність. Обмежтеся двома цифрами після десяткової крапки. Повторіть усе для стовпця Е.

9. Скопіюйте робочу таблицю на сторінку 2.

Виокремте усю таблицю з заголовком. Скопіюйте її в буфер обміну (Ctrl+C). Перейдіть на сторінку 2, клацнувши на її бірці (вставте сторінку 2 у книжку, якщо її нехає., командами вставити → Сторінку). Виокремте клітинку А1 і вставте вміст буфера обміну (Ctrl+V).

10. Поверніться на сторінку 1 для ручного форматування таблиці.

Зліквідуйте виокремлення таблиці, натиснувши на Esc і клацнув­ши за її межами.

11. Відцентруйте усі значення в стовпцях А і D.

Виберіть стовпець А, клацнувши на його назві (А) мишею, і натисніть на кнопку вирівнювання До центру на панелі форма­тування. Повторіть це для стовпця D.

12. Розграфіть таблицю.

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

13. Виокремте і замалюйте клітинки з числами жовтим кольором.

14. Виокремте заголовки стовпців і замалюйте їх червоним кольором.

15. Заголовок таблиці "Товарний чек" виконайте великим шрифтом синього кольору.

16. Перейдіть на сторінку 2 для автоформатування таблиці.

17. Виокремте таблицю і виконайте її автоформатування.

Формат →Автоформатування → Виберіть якийсьформат із запропонованого списку форматів → ОК. Виберіть ізастосуйте ще 2-3 інші формати зі списку автоформатів. Які автоформати ви застосували і який вам найбільше до вподоби?

18. Для таблиці на сторінці 2 задайте режим відображення формул і переконайтеся у їх правильності.

Звузьте ширину стовпців для перегляду таблиці. Виявивши помилку, усуньте її на обох сторінках.

19. Поверніться на сторінку 1 для виконання Обчислень.

20. Змініть вхідні дані (кількість куплених товарів у клітинках D3 і D4 збільшіть удвічі) і простежте, як зміняться результати.

Яке тепер значення Всього? Перепишіть результат у звіт.

21. Ознайомтеся з числовими форматами виведення чисел.

Виберіть клітинку з числовим значенням Всього і виконайте команди Формат → Клітинки → Число. Вибирайте по черзі всі назви форматів і переписуйте вигляд числа у звіт з поля Зразок. Застосуйте формат, який на Вашу думку найбільше підходить до змісту задачі.

22. Збережіть книжку на диску з назвою Прізвище.

23. Закінчіть роботу. Здайте звіти.

 

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

1. Що таке електронна таблиця і яке її призначення?

2. Як позначаються стовпці і рядки в ЕТ?

3. Як змінити ширину стовпця в ЕТ?

4. Які типи даних опрацьовують ЕТ?

7. Яке розширення має файл книги електронної таблиці?

8. Як змінити висоту рядка?

9. Як вибрати (виокремити) клітинку?

10. З чого складається робоча книжка?

12. Наведіть приклади даних числового і текстового типу?

13. Як виокремити в ЕТ потрібну клітинку?

15. Від чого залежить відображення чисел в ЕТ?

18. Для чого використовують текстові дані в ЕТ?

19. Як відобразити числа з символом грошової одиниці?

20. Як побачити формулу у одній клітинці, формули у всій ЕТ?

21. Які засоби автозаповнення таблиці ви знаєте?

22. Яке призначення клавіші F2 в ЕТ?

23. Як виконати копіювання формули?

24. Як задати чи змінити деякий формат відображення чисел?

25. Які ви знаєте формати числових даних?

 

 

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

Тема

Електронна таблиця, як база даних. Підведення підсумків.

Мета

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

Задача

Протабулювати функцію у — n(sinx²+1), де п — номер варіан­та, та обчислити площу під кривою методом лівих прямокут­ників. Відрізок, на якому розглядати функцію, і крок табулювання h задати самостійно (у таблиці повинно бути 10-12 рядків).

 

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

Декілька клітинок робочої таблиці, які мають суміжні сторо­ни, утворюють діапазон клітинок.

Діапазони мають прямокутну форму і описуються адресами двох діагонально-протилежних клітинок. Наприклад:

§ А1:СЗ — прямокутний діапазон;

§ А1:А9 — діапазон-стовбець;

§ А1:Е1 — діапазон-рядок.

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

Щоб виокремити несуміжні діапазони треба користуватися клавішею Ctrl. Наприклад, щоб виокремити два несуміжні стовпці-діапазони, потрібно клацнути на їхніх назвах в режимі натиснутої клавіші Ctrl.

Діапазонам можна надавати назви і ці назви замість виразів типу А1:А9. Програма сама дає назви діапазонам, якщо вона може їх одночаснорозпізнати.

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

Стандартних функцій є декілька категорій:

§ математичні — sin, cos, exp, In, abs, atan, sqrt тощо, а також функції для роботи з матрицями;

§ статистичні — СРЗНАЧ, МИН, МАКС, СУММ тощо (розглядаємо російськомовну версію програми);

§ логічні;

§ фінансові;

§ для роботи з датами, текстами та інші.

Функції можуть бути визначені над числами, адресами клітинок, адресами (назвами) діапазонів і їхніми списками. Елементи списку записують через розділювач, який визначається операцій­ною системою: кому, якщо в числах використовується десяткова крапка, або крапку з комою,

Оскільки суми обчислюють найчастіше, на панелі керування є кнопка Автосума ∑. Нею користуються так: виокремлюють клітинку під стовпцем чи праворуч від рядка з даними і клацають на кнопці Автосума - отримують потрібну суму (числових даних з відповід­ного стовпця чи рядка).

Дії над елементами ЕТ (виокремленими клітинками, стовп­цями, рядками, діапазонами, усією таблицею) виконують команда­ми контекстного чи основного меню або за допомогою кнопок панелі інструментів.

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

У виокремлену клітинку можна вставити примітку, яка по­яснює її призначення, командою Вставити → Примітка, а вилу­чити командою Редагувати → ОЧИСТИТИ (або засобами контекст­ного меню).

Розглянемо ще один спосіб швидкого введення текстових даних у таблицю. Він полягає у використанні списків користу­вача. Списки можуть містити назви товарів, міст, фірм, прізвища тощо. Список користувач спочатку створює командами Сервіс → Параметри → Закладка СПИСКИ → НОВИЙ СПИСОК → Вводить елементи списку через кому або натискаючи на клавішу вводу → Додати → ОК. Список використовують так: перший елемент списку вводять у деяку клітинку, перетягують її маркер копіюван­ня - відбувається автозаповнення таблиці елементами списку.

ЕТ можна використовувати як базу даних. Розглянемо типові дії, які можна виконувати з даними: 1) впорядковувати рядки за зростанням чи спаданням значень у деякому стовпці; 2) шукати дані за деяким критерієм. Стовпець з даними тут називають полем.

Впорядкування. Спочатку вибирають частину таблиці з дани­ми і назвами полів або всю таблицю (без заголовка таблиці і рядків з підсумками). Сортування виконують командою Дані → Сорту­вати, отримують список назв полів, де вибирають потрібну назву, наприклад Місто, і задають порядок сортування: за зростанням чи спаданням — отримують таблицю, де рядки будуть впорядко­вані в алфавітному чи зворотному порядку назв міст.

Пошук даних називають інакше фільтруванням даних. Спо­чатку вибирають рядок, що містить назви стовпців, і виконують команду Дані → Фільтр → Автофільтр. Клітинки з назвами стовпців стають списками з кнопками розгортання. Розгортають потрібний список, наприклад Січень, вибирають у списку значен­ня Умова — відкриється вікно конструктора умов, у якому є зручні засоби для формулювання критерію пошуку по стовпцю Січень, наприклад такого: більше 500000 і менше 2000000, натискають на ОК і на екрані отримують результати пошуку — рядки таблиці з містами, де показник діяльності фірми у січні задовольняв даному критерію. Щоб відновити на екрані таблицю, виконують команду Дані → Фільтр → Показати все.

Якщо треба побудувати складний критерій на базі назв де­кількох стовпців, то використовують команду Дані → Фільтр → Розширений фільтр, яку в цій роботі не використовуватимемо, оскільки їїможна замінити виконанням команди Автофільтр декілька разів.

Підсумки в таблицях.Підсумки підводять з метою визначення кращих, гірших, сумарних, середніх показників діяльності фірми в деяких країнах, містах, підрозділах тощо. Для цього спочатку рядки в таблиці сортують з метою групування (розташування поруч) даних, щостосуються кожної країни, міста чи підрозділу. Для отримання підсумків до впорядкованої таблиці, застосовують команду Дані→ Підсумки, де задають:1) назву поля, що містить об'єкти, для яких створюють підсумки, наприклад Країна; 2) операцію підсумовування і 3) назву поля, що містить дані, які підлягають підсумовуванню (наприклад,Всього або/і Березень). Операції підсумовування є різні:сума, максиму, мінімум, середнє значення, відхилення від норми тощо.

 

Хід роботи

1. Уведіть вхідні дані розв'язування задачі так:

Адреси Дані

А1 Табулювання функції та обчислення

А2 площі під кривою

А3 <тут вкажіть свою функцію, межі, крок>

А5 х

В5 у

С5 Площа

2. Задайте режим "Допускати назви діапазонів".

Сервіс →Параметри → Закладка Обчислення. Якщо задано цей режим, то програма автоматично надаватиме діапазонам-стовпцям назви полів - стовпців. Ці назви можна використовувати у формулах. Якщо діапазону треба надати іншу назву, то див. пункт 3.

3. Введіть у клітинку D4 значення кроку і дайте клітинці ім'я h.

Введіть число і виберіть цю клітинку. Застосуйте команду Вставити → Ім'я → Присвоїти → Введіть h → Додати → ОК.

4. Уведіть формули розв'язування задачі так:

АдресиФормулипримітки
А6=Значення лівої межі задайте своє

В6=SIN(x^2)+l - Це формула обчислення значення функції

Sin x ² +l, а ви введіть свою формулу

С6 =h*y

Обчислено площу одного лівого прямокутника А7 =А6 + h

5. Скопіюйте формули з клітинок А7, В6, С7 вниз до кінця робочої таблиці - отримаєте площі всіх лівих прямокутників у стовпці С.

У стовпці С вилучіть останнє значення.

6. Щоб отримати площу під всією кривою, обчисліть суму площ всіх лівих прямокутників.

Клацніть під стовпцем С і застосуйте команду-кнопку Автосума. Яке значення площі отримано?

7. У клітинку зі значенням площі вставте таку примітку: Цей результат отримав <Ваше прізвище>.

8. Обчислити середнє арифметичне всіх значень функції.

9. Назвіть робочу сторінку Площа.

10. Сформатуйте числові значення результатів, щоб кількість цифр була оптимальною.

11. Сформатуйте таблицю, щоб вона мала якнайкращий вигляд.

12. Роздрукуйте таблицю.

13. Розв'яжіть задачу.

Задача. З курсу математики відомо, щодля отримання більшточного значення площі значення кроку h потрібно зменшувати. Зменшіть значення h удвічі і обчисліть площу на тому ж самому проміжку.

14. Збережіть книжку на диску в особистій папці.

15. Закінчіть роботу. Здайте звіти.

 

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

1. Що таке копіювання формул?

2. Які є категорії стандартних функцій?

3. Що таке діапазон клітинок? Наведіть приклади діапазонів.

4. Як скопіювати формулу з деякої клітинки в діапазон-стовпець?

5. Як працює команда Підсумки даних?

6. Як вилучити стовпець чи рядок з таблиці?

7. Як виконати пошук потрібних даних в таблиці?

8. Як очистити клітинку?

9. Як користуватись командою Автофільтр?

10. Наведіть приклади статистичних функцій.

11. Яке призначення кнопки Автосума?

12. Як заповнити стовпець числами, що утворюють арифметичну прогресію?

13. Як зберегти книжку на диску?

14. Як заповнити стовпець чи рядок елементами списку користувача?

15. Яким способом відокремлюють аргументи у функціях?

 

 

Лабораторна робота №12

Тема

Excel. Робота з формулами.

Мета

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

Задача

Нехай ваша фірма має філіали в Києві, Харкові, Львові, Одесі, Донецьку чи в інших містах і є дані про щомісячні _; обсяги продаж у філіалах. За даними про діяльність фірми протягом трьох місяців, наприклад, січня, лютого, березня, створити таблицю для визначення обсягів продаж: максимальних, мінімальних і в цілому в Україні. Крім цього, створити нову таблицю - проект бізнес-плану на наступні два місяці: квітень і травень - з розширенням географії діяльності фірми (назви двох-трьох міст додати самостійно).

 

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

Декілька клітинок робочої таблиці, які мають суміжні сторо­ни, утворюють діапазон клітинок.

Діапазони мають прямокутну форму і описуються адресами двох діагонально-протилежних клітинок. Наприклад:

§ А1:СЗ — прямокутний діапазон;

§ А1:А9 — діапазон-стовбець;

§ А1:Е1 — діапазон-рядок.

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

Щоб виокремити несуміжні діапазони треба користуватися клавішею Ctrl. Наприклад, щоб виокремити два несуміжні стовпці-діапазони, потрібно клацнути на їхніх назвах в режимі натиснутої клавіші Ctrl.

Діапазонам можна надавати назви і ці назви замість виразів типу А1:А9. Програма сама дає назви діапазонам, якщо вона може їх одночаснорозпізнати.

Як відомо, для виконання обчислень використовують формули. Формула має вигляд =вираз. Розглянемо правила утворення виразів. Пріоритети виконання операцій у виразах такі як в елементарній математиці. Наведемо їх у спадному порядку:

ПріоритетОпераціїПояснення

1 () операції в дужках, аргументи функцій:

2 sin, cos тощо математичні та інші функції;

3 - унарний мінус;

4 % відсотки;

5 ^піднесення до степеня (-5^2=25);

6 * або / множення або ділення;

7 + або - додавання або віднімання;

8 & об'єднання текстів;

9 =,<, >, >= операції порівняння.

Стандартних функцій є декілька категорій:

§ математичні — sin, cos, exp, In, abs, atan, sqrt тощо, а також функції для роботи з матрицями;

§ статистичні — СРЗНАЧ, МИН, МАКС, СУММ тощо (розглядаємо російськомовну версію програми);

§ логічні;

§ фінансові;

§ для роботи з датами, текстами та інші.

Функції можуть бути визначені над числами, адресами клітинок, адресами (назвами) діапазонів і їхніми списками. Елементи списку записують через розділювач, який визначається операцій­ною системою: кому, якщо в числах використовується десяткова крапка, або крапку з комою, наприклад так,

=СУММ (А1; В6:С8; 20).

Оскільки суми обчислюють найчастіше, на панелі керування є кнопка Автосума ∑. Нею користуються так: виокремлюють клітинку під стовпцем чи праворуч від рядка з даними і клацають на кнопці Автосума - отримують потрібну суму (числових даних з відповід­ного стовпця чи рядка).

Дії над елементами ЕТ (виокремленими клітинками, стовп­цями, рядками, діапазонами, усією таблицею) виконують команда­ми контекстного чи основного меню або за допомогою кнопок панелі інструментів.

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

У виокремлену клітинку можна вставити примітку, яка по­яснює її призначення, командою Вставити → Примітка, а вилу­чити командою Редагувати → ОЧИСТИТИ (або засобами контекст­ного меню).

Розглянемо ще один спосіб швидкого введення текстових даних у таблицю. Він полягає у використанні списків користу­вача. Списки можуть містити назви товарів, міст, фірм, прізвища тощо. Список користувач спочатку створює командами Сервіс → Параметри → Закладка СПИСКИ → НОВИЙ СПИСОК → Вводить елементи списку через кому або натискаючи на клавішу вводу → Додати → ОК. Список використовують так: перший елемент списку вводять у деяку клітинку, перетягують її маркер копіюван­ня - відбувається автозаповнення таблиці елементами списку.

ЕТ можна використовувати як базу даних. Розглянемо типові дії, які можна виконувати з даними: 1) впорядковувати рядки за зростанням чи спаданням значень у деякому стовпці; 2) шукати дані за деяким критерієм. Стовпець з даними тут називають полем.

Впорядкування. Спочатку вибирають частину таблиці з дани­ми і назвами полів або всю таблицю (без заголовка таблиці і рядків з підсумками). Сортування виконують командою Дані → Сорту­вати, отримують список назв полів, де вибирають потрібну назву, наприклад Місто, і задають порядок сортування: за зростанням чи спаданням — отримують таблицю, де рядки будуть впорядко­вані в алфавітному чи зворотному порядку назв міст.

Пошук даних називають інакше фільтруванням даних. Спо­чатку вибирають рядок, що містить назви стовпців, і виконують команду Дані → Фільтр → Автофільтр. Клітинки з назвами стовпців стають списками з кнопками розгортання. Розгортають потрібний список, наприклад Січень, вибирають у списку значен­ня Умова — відкриється вікно конструктора умов, у якому є зручні засоби для формулювання критерію пошуку по стовпцю Січень, наприклад такого: більше 500000 і менше 2000000, натискають на ОК і на екрані отримують результати пошуку — рядки таблиці з містами, де показник діяльності фірми у січні задовольняв даному критерію. Щоб відновити на екрані таблицю, виконують команду Дані → Фільтр → Показати все.