Приклади сортування списку

Первинна таблиця приведена на Рис. 2. Результати сортування по статі представлені на Рис. 3. Результати сортування по статі й окладу представлені на Рис. 4. Результати сортування по статі, прізвищу і посаді представлені на Рис. 5.

Рис. 5.2. Первинна таблиця.

Рис. 5.3. Результати сортування по полю "Стать".

Рис. 5.4. Результати сортування по полям "Стать" (перше поле) та "Оклад" (друге поле).

Рис. 5.5. Результати сортування по полям "Стать", "Прізвище" та "Посада".

5.1.4. Використання проміжних результатів для аналізу списку.

У пункті меню Даннные є підменю Итогивикористання якогоможливо тільки післяроботи команди "Данные"-"Сортировка", коли список вже відсортований за заданими критеріями. Команда "Данные"-"Итоги" додає рядок проміжних результатів для кожної групи елементів списку, а також створює загальні підсумки. При цьому можна використовувати різні функції для обчислення результатів (наприклад, СУММ чи СРЗНАЧ). При виводі проміжних результатів Excel створює структуру списку. Щоб вивести необхідний рівень деталізації даних, потрібно клацнути мишею на відповідному символі структури.

Діалогове вікно "Промежуточные итоги" представлене на Рис. 6. Заповнення діалогового вікна для списку (Рис. 2) так, як показано на Рис. 6, дає сумарні оклади жінок і чоловіків (список був попередньо відсортований по статі), а також загальну суму їхніх окладів. Результат роботи команди представлений на Рис. 7. При виконанні цієї команди список був структурований. Щиглик миші на символі рядка рівня 1 дає можливість переглянути тільки загальні підсумки, на символі рядка рівня 2 - вивести тільки проміжні і загальні підсумки, сховавши сам список, і побачити всі рівні деталізації - на символі рядка рівня 3.

Рис. 5.6. Діалогове вікно "Промежуточные итоги"

Рис. 5.7. Результат роботи команди "Промежуточные итоги".

Щоб видалити проміжні результати і структуру, потрібно використовувати кнопку "Убрать все" діалогового вікна "Промежуточные итоги". Для застосування декількох підсумкових функцій досить повторити команду "Итоги" для нової підсумкової функції і зняти прапорець "Заменить текущие итоги" діалогового вікна "Промежуточные итоги".

5.1.5. ОБРОБКА СПИСКІВ ЗА ДОПОМОГОЮ ФОРМИ

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

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

Рис. 5.8. Форма для роботи зі списком

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

Форма дозволяє виконувати наступні дії з записами:

• перегляд;
• видалення;
• додавання;
• редагування;
• пошук за критерієм.

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

Пошук записів за критерієм здійснюється за допомогою кнопки "Критерий". В обраному полі критерій може бути записаний різними способами. Наприклад, для вибору зі списку (Рис. 2) усіх співробітників, прізвища яких починаються з букви "І", у поле "Прізвище" потрібно вписати І* і, користаючись кнопками "Назад" і "Далее", переглянути записи, що задовольняють цьому критерію. Критерії можуть містити умовні вираження. Так, для пошуку співробітників, оклад яких більше 700, досить у поле "Оклад" як критерій записати: >700. Excel надає можливість задати одночасно кілька критеріїв у різних полях. Ці критерії будуть зв'язані логічною функцією И.

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

5.1.6. ЗАСТОСУВАННЯ ФІЛЬТРІВ ДЛЯ АНАЛІЗУ СПИСКІВ

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

5.1.6.1. Використання автофільтру

Перед використанням автофільтру потрібно помістити курсор усередину списку. Команда "Данные"-"Фильтр"-"Автофильтр" активізує автофільтр. У кожного заголовка стовпця Excel установить автофільтр у виді кнопки зі стрілкою (Рис. 9). У результаті роботи автофільтру Excel виводить відфільтровані рядки і відображає їхні номера синім кольором. При цьому відповідне повідомлення виводиться в рядку стану (наприклад, "Знайдено записів: 10 з 22").

Рис. 5.9. Використання автофільтру

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

1. Все(відновлює вихідний список).

2. Первые 10 (приводить до появи діалогового вікна, показаного на Рис. 10).

Рис. 5.10. Діалогове вікно "Наложение условия по списку"

Вікно має три елементи керування. У першому можна вказати число від 1 до 500, у другому - "наибольших" або "наименьших", а в третьому - "элементов списка" чи "процент от количества элементов". Наприклад, для пошуку трьох найбільш високооплачуваних співробітників у поле "Оклад" потрібно вибрати критерій "Первые 10", у першому списку, що розкривається, установити 3, а в другому - "наибольших".

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

Рис. 5.11. Діалогове вікно "Пользовательский автофильтр"

Наприклад, для відбору співробітників, оклад яких знаходиться в діапазоні від 500 до 1200 діалогове вікно критерію "Условие" повинне бути заповнено, як показано на Рис. 11.

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

Після одержання результатів роботи автофільтру вони можуть бути оброблені за допомогою функції ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Функція ПРОМЕЖУТОЧНЫЕ.ИТОГИ може бути ініційована натисканням кнопки “Автосумма” Σі має наступний синтаксис:

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(N; диапазон),

де N може приймати наступні значення:

	обчислення середнього значення (СРЗНАЧ);			обчислення добутку (ПРОИЗВЕД);
	кількість чисел(СЧЕТ);	7, 8	обчислення стандартних відхилень (СТАНДОТКЛОН, СТАНДОТКЛОНП);
	кількість значень та кількість пустих клітинок (СЧЕТЗ);		обчислення суми (СУММ);
	обчислення максимального значення (МАКС);	10, 11	обчислення дисперсії (ДИСП, ДИСПР).
	обчислення мінімального значення (МИН);

Діапазон задає область застосування функції. Наприклад, для обчислення середнього значення окладів жінок необхідно спочатку відфільтрувати співробітників зі значенням поля "Стать" = ж , зробити активною клітинку D8, а потім використовувати функцію ПРОМЕЖУТОЧНЫЕ.ИТОГИ для отриманого діапазону значень поля "Оклад" і використати значення N=1 (варіант для обчислення середнього значення відповідно таблиці). Результат роботи такої фільтрації приведений на мал. 13.

Рис. 5.12. Використання функції "Промежуточные итоги"

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

5.1.6.2. Використання розширеного фільтра.

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

На відміну від автофільтра, розширений фільтр дозволяє:

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

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

· показувати у відфільтрованих записах не всі стовпці, а тільки зазначені;

· поєднувати оператором ИЛИ умови різних стовпців;

· для одного стовпця поєднувати операторами И, ИЛИ більше двох умов;

· створювати обчислюють критерії, що обчислюються;

· виводити тільки унікальні значення.

Рис. 5.13. Діалогове вікно для створення розширеного фільтру.

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

Увага! Заголовки в діапазоні критеріїв повинні точно збігатися з заголовками стовпців, і тому їх краще створювати копіюванням відповідних заголовків стовпців.

Для реалізації функції ИЛИ умови відбору записуються в різних рядках, а функції И - в одній.

Діапазон даних списку - це таблична область, що включає заголовки стовпців.

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

Приклад 1. Варіанти формування критеріїв розширеного фільтру для списку, який був представлений на Рис. 5.2:

1.Витягти зі списку записи, що містять дані про співробітниць з окладом більше 500 (Рис. 14а). Розміщені в одному рядку критерії реалізують логічну функцію И.

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

3. Витягти зі списку дані про співробітників, чиї прізвища починаються з літер З, Ж и И. У цьому випадку повинний бути реалізований критерій, що використовує ИЛИ функцію для даних одного стовпця (Рис. 14в).

4. Витягти зі списку дані про співробітників, прізвище яких починаються з З, Ж и Н і оклад яких більше 700 (Рис. 14г). У цьому випадку одночасно використовуються ИЛИ функції і И.

5.Витягти зі списку дані про співробітників, чий оклад знаходиться в діапазоні від 450 до 1200 (Рис. 14д). У цьому випадку реалізується функція И для даних одного стовпця.

А б в г д

Рис. 5.14. Приклади формування критеріїв

Умови можна задавати не тільки числові, а також текстові. При завданні текстових умов треба пам'ятати наступні правила:

• одна буква означає, що потрібно знайти всі значення, що починаються з цієї букви;
• знак " >" або " <" означає, що потрібно знайти всі значення, що знаходяться за алфавітом після або перед уведеною літерою.

Крім того можна задавати умови для часу, дати а також логічні умови.

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

Приклад 2 .Для таблиці (Рис. 5.2) витягти прізвища і телефони менеджерів. При цьому вихідний діапазон визначиться як a1:f6.

Рис. 5.15. Використання розширеного фільтру.

Критерій заданий діапазоном h1:h2, діапазон заголовків результату - b10:c10, результатом фільтрації є дані в осередках b11:c12 (Рис. 15).

До результатів розширеної фільтрації може бути застосовна функція
ПРОМЕЖУТОЧНЫЕ.ИТОГИ так само, як і для автофільтру. При використанні розширеного фільтра можна використовувати умови, що обчислюються, тобто значення, що повертаються формулою.

Створення цих умов вимагає виконання наступних правил:

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

Наприклад, для списку (Рис. 2) необхідно знайти всіх співробітників, у яких заробітна плата більше середнього окладу. Нехай у клітинку H1 записана формула =СРЗНАЧ(D2:D7), а в осередках Н2:Н3 введена умова, що обчислюється (Рис.16).

Рис. 5.16. Підготовка даних використання функції ПРОМЕЖУТОЧНЫЕ.ИТОГИ до результатів розширеної фільтрації.

Результат такої фільтрації на місці вихідних даних представлений на Рис. 17.

Рис. 5.17. Результат використання функції ПРОМЕЖУТОЧНЫЕ.ИТОГИ до результатів розширеної фільтрації.

5.1.6.3. Використання функцій СУММЕСЛИ і СЧЁТЕСЛИ

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

Функція СЧЁТЕСЛИ має наступний синтаксис:

=СЧЁТЕСЛИ(діапазон;критерій)

, де діапазон - це діапазон, у якому потрібно підрахувати кількість значень, а критерій- це текстове значення, що задає умова.
Так, для підрахунку числа співробітниць у списку (Рис. 2) можна використовувати функцію =СЧЁТЕСЛИ(E2:E7;"ж") . Функція поверне результат 3.

Функція СУММЕСЛИ має синтаксис:

=СУММЕСЛИ(диапазон; критерий; диапазон_суммирования)

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

Так, для підрахунку сумарного окладу, одержуваного чоловіками, для списку (Рис. 2) можна використовувати функцію =СУММЕСЛИ(E2:E7;"м";D2:D7). Функція поверне результат 1550.

КОНТРОЛЬНІ ПИТАННЯ.

1. Що таке список у Excel?

2. Які можливості по редагуванню баз даних надає команда ФОРМА ?

3. Які критерії можна задавати в команді ФОРМА ?

4. Для чого потрібна сортування записів і як її здійснити ?

5. Як задати порядок вторинного сортування ?

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

7. Як здійснити сортування по рядках ?

8. Чи розрізняються верхній і нижній регістр при сортуванні ?

9. Що можна зробити за допомогою команди АВТОФИЛЬТР ?

10. Які параметри потрібно установити щоб дані вибиралися при одночасному виконанні двох умов?

11. Які параметри потрібно установити щоб дані вибиралися при виконанні хоча б одного з двох умов?

12. Що можна зробити за допомогою команди РАСШИРЕННЫЙ ФИЛЬТР ?