Теория

Робота зi списками в Excel

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

Ι. Створення спискiв.

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

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

Ознакою кiнця списку є пустий рядок. Функцiї списку не можна використовувати якщо видiленi несумiжнi комірки.

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

A).Функцiя автозаповнення

Для активiзацiї даної функцiї необхiдно встановити опцiю Авто­заполнение значений ячеек на вкладцi вiкна Параметры. В ре­зультатi при вводi тексту в комірку Excel перевiрить значення iнших комірок.I якщо у них знаходиться текст, першi символи якого спiвпадають з символами введеними вами, то ввiд даних завершиться автоматично.

ΙΙ. Опрацювання спискiв за допомогою форми даних.

Функцiї списку надають додаткову можливiсть опрацювання даних. Так, зi створеним нами списком можна провести такi опе­рацiї:внести або видалити данi,знайти iнформацiю,вiдповiдну зада­ним критерiям, тощо.

A).Вiкно форми даних

У вiкнi форми даних можна проводити пошук iнформацiї,вводити до списку новi записи та видаляти непотрiбнi,а також редагувати список.

Активiзуйте будь-яку комірку списку i вiдкрийте дiалогове вiкно форми даних за допомогою команди Форма з меню Данные.

У вiкнi форми даних програма вказує загальну кiлькiсть записiв у списку та позицiю даного запису.

Б).Редагування записiв

Редагування даних виконується так,як і редагування тексту. Клавiша [Tab] використовується для переходу до наступного поля,а ком­бiнацiя клавiш [Shift+Tab] – для повернення назад. Клавiша Вернуть i [Esc] використовуються для вiдмiнення внесених змiн.

Клавiша Удалить дає можливiсть видалити поточний запис.Якщо необхiдно вставити новий запис,клацнiть на кнопцi Добавить.

В). Пошук записiв

При пошуку записiв за допомогою форми даних використовуються критерiї пошуку. В результатi натиснення кнопки Критерий на ек­ранi з’явиться форма даних без записiв. У критерiях можна викори­стовувати символи пiдстановки: * — для замiни деякого числа сим­волiв та ?-для замiни одного невiдомого символа.Так, наприклад, щоб знайти данi про людей фамiлiя яких починається на лiтеру К у якостi критерiю пошуку в поле Фамилия потрiбно ввести наступне: К*.

При пошуку числових значень використовують оператори по­рiвняння. Наприклад, у якостi критерiю можна задати дiапазон зна­чень, вказавши при цьому максимальне та мiнiмальне значення.

ΙΙΙ. Сортировка спискiв.

Необхiднiсть сортировки даних у списках може виникнути при ви­конаннi найрiзноманiтнiших задач. Сортирувати можна як числовi так i текстовi данi. При цьому текстовi данi сортирують в алфавітному або протиалфавiтному порядку, а числовi – чи в по­рядку спадання,чи в порядку нарощення.

А). Сортировка рядкiв

При використаннi функцiй списку видiлення списку вiдбувається ав­томатично.Та користувач може наперед видiлити комірки, що пiдлягають сортировцi. Діалогове вiкно Сортировка диапазона вiдкривається за допомогою команди Сортировка з меню Данные.

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

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

Якщо при сортировцi по алфавiту слiд розпiзнавати великi та малi лiтери, необхiдно натиснути кнопку Параметры. У резуль­татi відкриється дiалогове вiкно Параметры сортировки, у якому слiд активiзувати опцiю Учитывать регистр.

Б).Сортировка по стовпчикам

Для того щоб задати сортировку по стовпчикам потрiбно видiлити комірки, якi пiдлягають сортировцi i вiдкрити дiалогове вiкно Сорти­ровка діапазона. Клацнувши на кнопцi Параметры, вiдкрийте дiалогове вiкно Параметры сортировки i активiзуйте в областi Сортировать опцiю столбцы диапазона.

В). Порядок сортировки користувача

Вiдкрийте дiалогове вiкно Параметры сортировки.

У полi Сортировка по первому ключу, котре мiстить список розроблених користувачем способiв сортировки, спочатку зав­жди видiлений елемент Не производится.

Якщо жоден з запропонованих порядкiв вас не задовольняє, задайте свiй. Для цього виберiть у меню Сервис команду Параметры i у дiалоговому вiкнi, що з’явилося перейдiть на вкладку Списки. Ви­берiть елемент Новий список, у наслiдок цього у полi Элементы списка з’явиться курсор вводу. Введiть елементи списку у потрiбнiй послiдовностi, завершуючи ввiд кожного з них клавішею [Enter].

Пiсля завершення вводу елементiв списка слiд натиснути кнопку Добавить для включення зіставленого списку у перелiк iснуючих.

Поле Импорт списка из ячеек дозволяє створювати список, ви­користовуючи данi iз таблицi. Помiстiть курсор вводу у це поле та видiлiть комірки, що мiстять данi для нового спи­ску.Завершiть процес видiлення комiрок натисненням клавiши Импорт.

ΙV. Фiльтрацiя даних.

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

А).Функцiя автофiльтру

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

Пiсля встановлення автофiльтру можна використовувати у якостi критерiю вмiст будь-якої комірки, крiм заголовкiв стовпцiв. Для цього необхiдно клацнути на кнопцi зi стрiлкою у рядку заголовка i вибрати потрiбний елемент зi списку. При цьому у рядку стану з’явиться повiдомлення про те,скiльки записiв з, вiдповiдних зада­ному критерiю, знайдено у списку.

Вiдбiр записiв можна продовжувати, задаючи критерiї у iнших сто­впчиках. Кожний новий критерiй зв’язується з попереднiм умовним оператором I.

За допомогою елементу (Первые 10…) користувач може створити но­вий список, що складється з 10-ти найбiльших або найменших чисел попереднього списку.Пiсля вибору елементу (Первые 10…) вiдкривається дiалогове вiкно Наложение условия по списку з трьома полями для вводу. Праве поле призначене для вказання кiлькостi елементів списку. Значення элэмэнтов списка та % от ко­личества элэмэнтов дозволяють встановити числове чи процентне обмеження на кiлькiсть виводимих на екран елементів списку.

При використаннi функцiї автофiльтру можна задавати критерiї ко­ристувача для фiльтрацiї даних. Вiдкрийте поле списку стовпця, данi котрого ви хочете вiдфiльтрувати, та виберiть елемент Условие. Дiалогове вiкно Пользовательский автофильтр надає можливiсть задати комплекснi критерiї об’єднанi за допомогою умовних опера­торiв И та ИЛИ. У цьому вiкнi можна вказати, наприклад, границi областi, котра буде використана у якостi критерiю фiльтру.

Б). Розширений фiльтр

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

Тепер у комірку пiд вiдповiдним заголовком можна вставити критерiй. Критерiї,введенi в одному рядку, об’єднуються умовним оператором I, а критерiї у рiзних рядках оператором АБО. У випадку необхiдностi разом з критерiєм у комірку вводять оператор порiвняння.

При заданнi критерiїв знак (=) не використовується, оскiльки у цьому випадку програма iнтерпретує критерiй як формулу i видає значення помилки.

Для активiзацiї функцiї розширеного фiльтру виберiть команду Фильтр/Автофильтр, в меню Данные. Пiсля цього на екранi з’явиться дiалогове вiкно Расширенный фильтр, у якому слiд вказати дiапазон критерiїв та позицію списку, створеного за допо­могою фiльтру.

Спочатку вкажiть нову позицiю вибраних записiв. Якщо акти­вiзована опцiя Скопировать результат в другое место, у полi Поместить результат в диапазон слiд вказати адресу комірки, починаючи з якої будуть розмiщуватися вiдповiднi критерiям записи. Якщо вказiвник комірки знаходиться у списку, поле Исходный диапазон мiстить адреси першої та останньої комірок списку. Список, що фiльтрується завжди можна визначити заново, установивши курсор вводу у поле Исходный диапазон i ви­дiливши комірки на робочому листi за допомогою мишi. Щоб задати діапазон комірок, шт мiстять критерiї, помiстiть курсор вводу у поле Диапазон условий i вiдмiтьте потрiбнi комірки, включаючи заго­ловки стовпцiв. У результатi встановлення опцiї Только уникальные записи iз отриманого списку будуть виключенi записи з загальними елементами. (Честно говоря, это все можно было не читать. Но если ты это прочитал(а), оставь комментарий ниже, на этой странице - будет выше оценка за практическое задание. Кто оставит первым комментарий - практическое задание можно не выполнять вообще. Иди и займись чем нибудь полезным. Об этом содержании всем говорить не нужно. Кто его прочитает, тому отдельная благодарность.) 

При вказувані дiапазону критерiїв слiд видiляти тiльки запов­ненi рядки, так як порожнiй рядок iнтерпретується програмою як критерiй зв’язаний з iншими критерiями умовним оператором ИЛИ. Якщо діапазон критерiїв мiстить порожнiй рядок (порожній рядок вiдповiдає будь-яким даним), то у пiдсумковому списку будуть представленi усi записи вихiдного (початкового) списку.