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

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

Головне управління освіти і науки

Виконавчого органу Київради (КМДА)

 

 

 

 

 

 

КВАЛІФІКАЦІЙНА  ПРОБНА РОБОТА

 

За професією «Оператор комп’ютерного набору»

ІІ  категорія

 

Тема: Сортування, пошук, фільтрація та захист даних

в електронних таблицях Excel

 

 

 

 

 

 

 

учениці групи №21

Князєвої Вероніки Русланівни

Майстер виробничого навчання

 

 

 

 

 

 

 

 

 

Київ 2011

Зміст

1. Вступ……………………………………………………………………………3

1.1.Запуск програми Excel…………………………………………………4

2. Сортування……………………………………………………………………..6

3. Пошук та заміна………………………………………………………………..9

4. Фільтрація……………………………………………………………………..13

4.1. Автофільтр…………………………………………………………….13

4.2. Розширений фільтр…………………………………………………...17

5. Захист даних…………………………………………………………………..21

5.1. Захист аркуша…………………………………………………………22

5.2. Захист книги…………………………………………………………...23

6. Висновок………………………………………………………………………24

7.Викоростана  література……………………………………………………….25

 

 

 

1. Вступ

 

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

 

Деякі види інформації необхідно відображати у вигляді таблиць. Особливо широка така структура даних застосовується у роботі з економічною інформацією. Для оброблення табличної інформації розроблені спеціальні програмні системи – табличні процеси. Складовою MS Office є табличний процесор  MS Excel – пакет програм, призначений для обробки інформації у вигляді таблиці.

У кожній комірці  таблиці Excel можна відобразити певний обсяг інформації, зокрема ввести до 32 767 символів тексту, що відповідає 10 сторінкам звичайного тексту або число в межах ( -2,2250738585072 * 10 ^-308 ; 1, 79769313486231 * 10³⁰⁸ ).

 

Табличний процесор MS Excel дає змогу:

• Здійснювати оброблення табличних даних;
• Розв’язувати науково-технічні задачі за допомогою системи програмування VBA та вбудованих функцій;
• Відображати дані у графічному вигляді (як графіки та діаграми)
• Працювати з базами даних, використовуючи сортування інформації, групування даних, що відповідають певними критеріям та ін.;
• Здійснювати імпорт та експорт в інші програмні системи та мережі.

 

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

 

 

 

 

 

 

 

 

 

 

 

 

1.1. Запуск програми Excel

 

Існує декілька способів запуску програми Microsoft Excel.

 

1. Спосіб через меню Пуск:

 

² ² ²

 

2. Спосіб через Робочий стіл:

На Робочому столі знайти ярлик Microsoft Excel ¢ клік правою кнопкою миші ¢ У підменю, що з’явиться обрати Открыть

 

 

 

Після виконання одного із способів запуску програми, з’явиться таке вікно:

 

 

Мал. 1. Загальне вікно Excel

 

 

2.Сортування

 

Сортування  – це впорядкування записів в таблиці, в алфавітно - цифровому порядку за зростанням або зменшенням.

Для того щоб розпочати Сортування потрібно обрати:

 

Данные_Сортировка

 

 

Та  виконати наступні дії у такій послідовності:

1. Обрати будь-яку комірку яка містить данні;
2. Данные _{_} Сортировка;
3. У діалоговуму вікні обрати потрібні параметри:

ñСортировать по

-по возрастанию – від А до Я.

-по убыванию- Від Я до А.

ñЗатем по

ñВ последнюю очередь по

ñИдентифицировать поля по

-Подписям - первая строка диапазона – означає , що перший рядок не приймає участі в сортируванні.

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

 

ñПараметры – дають змогу встановити послідовність нестандартного сортування.

 

Сортування з використанням  панелі інструментів

• З допомогою піктограми можливо виконати сортування по зростанню чи по зменшенню. Сортування проводиться від стовпця з поточної комірки.

 

 

Приклад №1

 

 Створити таблицю «Відділ кадрів» та відсортувати її по прізвищам, а потім за віком.

 

Таблиця «Відділ кадрів»:

 

Мал. 3 Початкова База даних

 

 

Порядок виконуваних дій:

1. Обрати будь-яку комірку таблиці.
2. Данные_Сортировка
3. Встановити поля для сортування і порядок сортування

 

Мал. 4 параметри сортування

 

В результаті отримаємо:

 

Мал. 5

 

 

 

3.Пошук  та заміна

 

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

Для того щоб розпочати пошук потрібно:

1. Обрати Правка _ Найти, або Ctrl + F, після чого відкриється діалогове вікно;

 

Мал. 6

 

2. Задати потрібні параметри;

 

  В полі Найти можна ввести текст, символ або поєднання символів;

  Формат – дозволяє шукати данні за форматом комірки, та обрати формат  з комірки;

Мал. 7 Формат ячеек

 

  Искать – дозволяе обрати де шукати: на листе або в книге;

 

 

  Просматривать – дозволяє задати напрямок пошуку : по строкам; по столбцам;

 

 

 

 Область поиска – доволяє визначити зону пошуку: формулы; значения; примечания;

 

 

         

3. Після того як були встановлені потрібні параметри натиснути на:

• Найти все – виконує пошук по всій книзі;

• Найти далее – виконує пошук дали по коміркам.

 

 

 

 

Для того щоб виконати заміну потрібно:    

1. Обрати Правка _ Заменить;

Мал. 8 Найти и Заменить

2. Задати потрібні параметри;

  В полі Найти, вводимо символи які потрібно знайти;

  В полі Заменить на, вводимо данні які треба замінити;

 

 

Приклад №2

У таблиці «Шуточка про Шурочку» знайти слово «Шурочка», а потім замінити його на «Мурочку»

Початкова БД:

Послідовність дій:

1. ПравкаcНайтиcЗаменить;
2. Задати критерії вказані у завданні;
3. Лівий клік миші на Заменить все;

Мал. 10

 В результаті отримуємо:

 

 

4.Фільтрація

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

 

Фільтри  поділяють на: Автофільтр та Розширений фільтр.

 

4.1 Автофільтр

 

Для того, щоб  встановити для таблиці автофільтр за певними критеріями, потрібно встановити курсор в будь-якій комірці таблиці. Після чого виконати такі команди: Данные _ Фильтр _ Автофильтр.

 

² ²

 

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

 

Мал. 12

 

Список фільтрів включає в себе наступні пункти:

ê Перелік всіх значень даного поля в таблиці;

ê Все – всі рядки таблиці;

ê Первые 10 – вибір декількох найбільших  чи найменших значень;

ê Условие – особиста умова користувача;

êПустые – коміркі які не мають данних;

êНе пустые – комірки які мають данні.

 

Можливо задати до двох критеріїв одного й того ж стовпця, зв’язавши їх логічними операторами И чи Или.

 

 

 

 

 

 

 

 

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

В умовах пошуку можна використовувати шаблони, в якості яких виступають символи-замінники ? чи * (? – заміна одного символа, * -  заміна будь-якої кількості будь-яких символів в рядку).

 

êДля відміни фільтрації в полі потрібно в списку фільтра обрати Все.

êДля відміни фільтрів у всіх полях: ДанныеCФильтрCПоказать все.

êВихід з режиму авто фільтра: ДанныеCФильтрCАвтофильтр.

 

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

 

Початкова БД:

 

 

 

 

 

 

 

 

 

1. Відібрати 3-х самих молодших працівників:

 

 

 

 

 

 

 

 

В результаті:

Мал. 16

 

2. Відібрати тих, хто має телефон та оклад  ≤ 300000, або ≥ 600000.

Порядок дій:

В полі Телефон відібрати записи по принципу Не пустые.

В полі Оклад скористатися відбором Условие.

 

В результаті будуть відібрані такі записи:

Мал. 18

3. Відібрати тих, чиє прізвище починається на букву Б.

Результат відбору:

 

 

 

 

 

 

4.2 Розширений  фільтр 

 

Розширений фільтр дозволяє використовувати для пошуку більш складніші критерії, чим в автофільтрах, і об’єднувати їх в  довільні поєднання як по И ,так і по ИЛИ.

Для того щоб  скористатися Розширеним фільтром потрібно виконати таку послідовність дій: Данные c Фильтрc Расширенный фильтр.

 

Під час роботи розширений фільтр спирається на три  області:

êобласть даних;

êобласть критеріїв пошуку. Ця область формується із рядка заголовків полів, які будуть ключовими при відборі записів, і рядка або рядків критеріїв. Якщо критерії знаходяться в одному рядку, то вони працюють по принципу И. Якщо  в різних – по принципу ИЛИ. В критеріях можуть використовуватися шаблони ? та *.

êцільова область. Її завдання необов’язкове, так як існує функція «оставить результаты отбора на месте».

 

Області можуть бути розташовані на одній сторінці, на різних сторінках, та навіть в різних файлах.

 

Порядок дій:

 

 

1. В вільне місце на сторінці скопіювати заголовки критеріїв пошуку.(Копіювання виконується для того, щоб не допустити неточності в назвах полів. Наприклад: замість української С не набрати латинську С.)

2. Заповнити рядки критеріїв. Причому, сполучені по «И» в одному рядку, сполучені по «ИЛИ» в різних рядках.

3. Скопіювати у вільне місце на сторінці заголовки, що цікавлять в результаті відбору полів.(Якщо відібрані записи знаходитимуться в окремому місці.)

4. Данные c Фильтрc Расширенный фильтр.

5. Діалогове вікно Расширенный фильтр.

 

Обработка – куди помістити результат пошуку по критерію.

ê Фильтровать результат на месте – залишити там же;

ê Скопировать результат в другое место - помістити у сформульовану цільову область.

 

Исходный диапазон – База даних

Диапазон  критериев – містить сформульовані в пунктах 1 і 2 критеріїв відбору.

Поместить результат в диапазон – цільова область сформована у пункті 3. Доступна лише при виборі прапорця Скопировать результат в другое место.

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

 

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

 

Відібрати записи, що містять прізвище, телефон та оклад тих, хто має прізвище, яке починається на букву Б та оклад більше 500000.

 

 

 

 

 

Початкова БД:

Мал. 22

 

Прядок  дій:

1. Для завдання області критеріїв скопіювати назви полів

2. Записати в наступному рядку 
3. критерії: Б* та >500000
4. Для завдання полів області скопіювати назви полів

5. Вставити в будь-яку клітинку БД.
6. Данные c Фильтрc Расширенный фильтр.
7. Заповнити діалогове вікно:

Мал. 23

 

В результат отримаємо:

Мал. 24

 

 

 

 

5.Захист  даних

Excel дозволяє уникнути небажаної зміни даних, а також приховати частину інформації установкою захисту комірок, листів і робочих книг. Наприклад, можна приховати формули, аби вони не з'являлися в рядку формул.

Для того щоб  виконати захист комірки потрібно виконати наступні дії:

1. ¢2. _3. ¢4.

 У діалоговому вікні що  з’явиться обираємо потрібні  параметри.

 

! Захист комірки не діє , якщо не включений захист аркуша.

 

5.1 Захист аркуша

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

Для захисту аркуша потрібно виконати наступні дії: Сервис † Защита † Защитить лист.

_ c

Після виконання цих дій з’явиться таке діалогове вікно:

У полі Пароль для відключення захисту аркуша введіть пароль, який може містити до 255 символів. При введенні пароля розрізняються рядкові і прописні символи.

У списку  Разрешить всем пользователям этого листа - можно встановити прапорці, що дозволяють користувачам форматування вічок, стовпців, рядків, вставку стовпців, рядків, видалення стовпців, рядків і так далі.

 

 

 

 

5.2 Захист книги

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

Для захисту книги потрібно виконати наступні дії: Сервис † Защита † Защитить лист.

² ²

У діалоговому вікні, що з’явиться обрати потрібні параметри:

 

 

 

 

ê Структуру — забезпечує захист структури книги, що запобігає видаленню, перенесенню, відкриттю, перейменуванню і вставці нових листів;

ê Окна — запобігає переміщенню, зміні розмірів, показу і закриттю вікон.

 

Відключення захисту Аркуша або Книги

Для відключення захисту аркуша виконайте послідовність дій:  СервисCЗащитаCСнять защиту листа або Снять защиту книги .

 

 

 

6.Висновок

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

 

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

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

 

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

 

Всі ці дії дозволяють розширити можливості  електронних  таблиць  Excel.

 

 

7.Використана література

1. Столяров А., Столярова Е. «Шпаргалка по Excel 7.0»
2. Глинський Я.М.  «Практикум з Інформатики : навч. Посіб. Самоучитель – ІІ-те вид. – Львів: СПД Глинський, 2008.
3. Шпак Ю.Н. «Microsoft Office 2003». Руская версія/Под ред.. Ю.С. Ковтанюка – К.: Издательство «Юниор», 2004
4. Дилженко В.А., Колесніков Ю.В. «Microsoft Excel 2003». – СПБ. : БХВ – Петербург 2006 г.
5. Спиридонов О.В. «Раширенные возможности Microsoft Excel 2003».