А б в г д

Рис. 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. Що можна зробити за допомогою команди РАСШИРЕННЫЙ ФИЛЬТР?

Дата добавления: 2014-11-29