Часть 1. Отбор по критерию сравнения

Этап 1. Формирование области критериев отбора

3. Для получения данных о произведенных расходах на первой копии скопируйте имена полей списка (ячейки A1:H1, рис. 8) и поместите их на свободной области листа (например, в ячейки K1:R1). Это имена полей области критерия.

4. Под именами на следующей строке сформируйте область критериев отбора:

под столбцом Расход введите >0

Этап 2. Фильтрация данных

5. Щелкните в области исходного списка.

6. Выполните команду вкладка Данные/группа Сортировка и фильтр/кнопка Дополнительно.

7. В диалоговом окне задайте параметры:

переключатель скопировать результат в другое место;

Исходный диапазон –по умолчанию уже указан;

Диапазон условий – $K$1:$R$2 (выделите мышью);

Поместить результат – $K$4 (щелкните на ячейку)

8. Проанализируйте результаты отбора и убедитесь, что они удовлетворяют заданному критерию.

Самостоятельно

9. На этом же листе в свободной области сформируйте область второго критерия отбора (данные о сборах членских взносов по улицам Центральная и Солнечная) и проведите фильтрацию.

10. Придумайте и примените собственные критерии отбора по критерию сравнения и поместите их на том же листе в свободных областях листа.

 

Часть 2. Отбор по вычисляемому критерию

11.  Для получения данных о сборах за последние две недели создайте копию листа Касcа и переименуйте ее на Касcа-расшир фильтр2.

12. Скопируйте имена полей списка (ячейки A1:H1, рис. 8) и поместите их на свободной области листа (например, в ячейки K1:R1).

13. Переименуйте в области критерия столбец Дата оплаты на Дата оплаты1.

14. Под именем Дата оплаты 1 задайте формулу отбора (см. в параграфе).

15. Выполните фильтрацию. Убедитесь, что данные отобраны правильно.

16. Для отбора сумм оплаты за электроэнергию выше среднего значения вновь скопируйте имена полей списка (ячейки A1:H1, рис. 8) и поместите их на свободной области листа.

17. Отберите путем критерия сравнения только сборы за электроэнергию.

18. Для полученной таблицы сформируйте вычисляемый критерий. Для этого вновь скопируйте заголовки таблицы и вставьте их в свободной области листа.

19. Переименуйте столбец Приход в Приход1.

20. Под именами на следующей строке сформируйте следующий критерий отбора:

Номер корреспондирующего счета – 76;

Приход1 – формула = Яч1>СРЗНАЧ(Диапазон), где

Яч1 – адрес первой ячейки в столбце Приход в исходном списке,

СРЗНАЧ – функция вычисления среднего значения,

Диапазон – все значения столбца Приход.

Примечание.

В диапазоне надо указывать абсолютные адреса ячеек, чтобы при просмотре следующих оценок списка диапазон вычисления среднего не смещался.

21. Выполните фильтрацию. Убедитесь, что данные отобраны правильно. Для контроля правильности отбора данных с помощью функции СРЗНАЧ() вычислите средний показатель сборов за электроэнергию. Сравните результаты отбора с вычисленным значением и убедитесь, что данные отобраны правильно.

22. Придумайте собственные критерии отбора по типу «вычисляемый критерий» и поместите результаты фильтрации на этом же листе.

Задание 4. Структурирование таблицы ручным способом

Задание выполняется на основе листа Касса в файле Заготовки Excel.

Создать структуру таблицы (рис. 8), которая позволяет скрывать

· столбцы с данными о приходе и расходе,

· строки с записями, относящиеся к одной улице.

Технология выполнения задания

1. Прочитайте параграф «Ручной способ структурирования» темы 4.5.

2. В файле Заготовки Excel создайте копию листа Касcа и переименуйте ее на Касcа-структура.

3. Отсортируйте строки списка по названию улицы.

4. Вставьте пустые строки между записями с разными названиями улиц, отделив таким образом группы с повторяющимися значениями.

5. Выделите в списке строки, относящиеся к одной улице.

6. Выполните команду вкладка Данные/группа Структура/кнопка Группировать.

7. После операции слева от таблицы на листе появился уровень группировки. С помощью кнопки «минус/плюс» скройте и вновь раскройте уровень структуры.

8. Повторите аналогичные действия для каждой улицы.

9. Выделите столбцы Номер корреспондирующего счета, Приход, Расход и Расшифровка.

10. Выполните группировку столбцов. Скройте и раскройте получившийся уровень группировки.

11. Выделите столбцы Приход и Расход.

12. Выполните группировку столбцов. Получилась группировка 2-го уровня. Скройте и раскройте получившийся уровень группировки.

13. На еще одной копии листа Касса проделайте самостоятельно другие виды структурирования таблицы.

Задание 5. Автоструктурирование.
Создание иерархического уровня структуры

Задание выполняется на основе листа Зарплата в файле Заготовки Excel.

Создать таблицу расчета заработной платы.

Исходными данными являются столбцы Фамилия, Зар.плата (оклад без вычетов и надбавок), Надбавка.

Столбцы Начислено, Подоходный налог, Пенсионный фонд, Общий налог, Сумма к выдаче являются расчетными.

Провести автоструктурирование таблицы.

Создать дополнительные иерархические уровни структурирования вручную (рис. 15).

Рис. 15. Автоструктурирование и дополнительное структурирование по строкам

Технология выполнения задания

1. Прочитайте параграф «Автоструктурирование» темы 4.5.

2. В файле Заготовки Excel перейдите на лист Зарплата.

3. Отформатируйте ячейки заголовков, указав следующие параметры:

· выравнивание по горизонтали – по значению;

· выравнивание по вертикали – по верхнему краю;

· флажок – переносить по словам;

· шрифт – полужирный.

4. Создайте таблицу согласно по образцу (рис. 11). Для этого введите формулы для Скворцова (1-я строка в таблице) по правилам (таблица 2). При составлении формул используйте ссылки на ячейки с процентами отчислений.

Таблица 2. Правила расчета вычисляемых значений

Имя столбца	Правило вычисления
Начислено	Зар.плата + Надбавка
Подоходный налог	%подоходного налога * Начислено
Пенсионный фонд	%пенсионного фонда * Начислено
Общий налог	Подоходный налог + Пенсионный фонд
Сумма к выдаче	Начислено – Общий налог

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

5. Рассчитайте значения строки Итого для столбца Зар.плата, используя функцию СУММ.

6. Скопируйте формулу во все остальные ячейки строки Итого.

7. Проведите сортировку в списке по столбцу Фамилия.

8. Установите курсор в любую ячейку области данных.

9. Выполните команду Создать структуру (вкладка Данные/группа Структура/в меню кнопки Группировать). Получилась таблица с автоструктурой, как на рис. 12.

10. Пользуясь кнопками «плюс» и «минус», скройте и раскройте элементы структуры.

11. Создайте дополнительные иерархические уровни структурирования по строкам, например, выделив группы по две фамилии. Для этого:

· вставьте пустую строку после первых двух фамилий;

· вставьте пустую строку перед строкой Итого;

· выделите строки с первыми двумя фамилиями;

· сгруппируйте первые две строки;

· выделите строки другими фамилиями;

· сгруппируйте строки;

· сравните результат с образцом (рис. 15).

Задание 6. Структурирование таблицы
с автоматическим подведением итогов по группам

Задание выполняется на основе листа Касса в файле Заготовки Excel.

На основе таблицы с исходными данными (рис. 8) создать различные варианты группировки с подведением итогов:

1-й уровень итогов – сумма собранных взносов по улицам.

2-й уровень итогов – сумма собранных взносов по каждому участку.

3-й уровень итогов – сумма собранных взносов по отдельным статьям.

Проанализировать полученный список (рис. 16).

Рис. 16. Таблица с итогами

Технология выполнения задания

1. Скопируйте лист Касса и переименуйте его на Касса-Итоги1.

2. Отсортируйте список записей с помощью команды Данные/Сортировка по следующим уровням:

1-й уровень – улица в садоводстве;

2-й уровень –№ участка;

3-й уровень – номер корреспондирующего счета;

3. Установите курсор в произвольную ячейку списка записей и выполните команду вкладка Данные/группа Структура/кнопка Промежуточные итоги.

4. В диалоговом окне установите параметры подведения итогов:

При каждом изменении в – Улица в садоводстве;

Операция – Сумма;

Добавить итоги по – Приход;

Заменять текущие итоги – нет;

Конец страницы между группами – нет;

Итоги под данными – да.

5. Просмотрите 1-й уровень итогов – Сумма сборов по каждой улице.

6. Для создания 2-го уровня итогов установите курсор в произвольную ячейку списка и опять нажмите кнопку Промежуточные итоги.

7. В диалоговом окне установите параметры:

При каждом изменении в –№ участка;

Операция – Сумма;

Добавить итоги по – Приход;

Заменять текущие итоги – нет;

Конец страницы между группами – нет;

Итоги под данными – да.

8. Для создания 3-го уровня итогов установите курсор в произвольную ячейку списка и опять нажмите кнопку Промежуточные итоги.

9. В диалоговом окне установите параметры:

При каждом изменении в – Номер корреспондирующего счета;

Операция – Сумма;

Добавить итоги по – Приход;

Заменять текущие итоги – нет;

Конец страницы между группами – нет;

Итоги под данными – да.

10. В результате выполненных операций автоматически создалась 7-уровневая структура списка.

11. Скройте 6-й уровень иерархии (рис. 16). Останутся только итоговые значения по 3-м полям, по которым подводились итоги. Просмотрите, как выглядят автоматически создаваемые подписи к итогам.

Обратите внимание. По тем участкам, которые еще не произвели ни одной оплаты, итоги не подводятся.

 

Задание 7. Комплексное задание по изученным технологиям

Задание выполняется на основе листа Касса в файле Заготовки Excel.

Создать список должников по оплате членских взносов, если членский взнос за 2010 г. 2000 руб.

Технология выполнения задания

1. Для формирования списка должников по оплате членских взносов скопируйте лист Касса и переименуйте его на Касса-Итоги2. Список должников состоит из тех участков, которые вообще не платили членские взносы, и тех участков, которые выплатили членские взносы не полностью.

2. С помощью расширенного фильтра отберите все записи с оплатой членских взносов (либо по полю Номер корреспондирующего счета = 86, либо по полю Расшифровка). Скопируйте полученный список на новый лист. Назовите лист Должники.

3. Для формирования списка участков, которые вообще не платили членские взносы, проведите сортировку, например по дате, и в конце списка окажутся нужные нам записи, у которых дата оплаты не указана. Скопируйте эту часть списка на лист Должники и присоедините к уже существующему списку.

4. На листе Должники отсортируйте полученный список по улице и по номеру участка.

5. Подведите итоги по номеру участка с подсчетом суммы по столбцу Приход. Скройте исходные данные и оставьте только итоги. Получится таблица как на рис. 17. Как видно, таблица стала малоинформативная. Поскольку на разных улицах есть участки с одинаковыми номерами, из таблицы не ясно, какой участок к какой улице относится.

Рис. 17

6. Отмените все итоги.

7. Чтобы улучшить вид таблицы, и вставьте новый столбец после № участка. Назовите столбец Поступило от.

8. Создайте в столбце Поступило от формулу, которая будет связывать название улицы и номер участка в одну строку примерно такого вида:

=B2&", "&C2

B2 – адрес первой ячейки столбца Улица в садоводстве,

C2 – адрес первой ячейки столбца № участка,

", " – текстовая строка, состоящая из 2-х символов «запятая» и «пробел», заключенных в кавычки,

& – знак операции склеивания данных в одну строку.

Результат представлен на рис. 18.

Рис. 18

9. Подведите итоги по столбцу Поступило от с подсчетом суммы по столбцу Приход.

10. Скройте исходные данные и оставьте только итоги.

11. Сгруппируйте столбцы Дата оплаты, Улица в садоводстве, № участка, № документа и скройте их.

12. Удалите столбцы Номер корреспондирующего счета и Расшифровка.

13. Переименуйте столбец Расход в Долг.

14. В столбце Долг создайте формулу, которая вычисляет разность между размером членского взноса (2000 руб) и суммарной оплатой в столбце Приход.

15. Отсортируйте все записи по столбцу Долг и найдите в отсортированной таблице записи, в которых долг больше нуля. Это и есть должники (Рис. 19).

Рис. 19. Должники по оплате членских взносов

[1] Размещение команды сортировки на вкладке Главная говорит о том, что это одна из часто используемых операций.