Лабораторная работа №4. СПИСКИ EXCEL(продолжение)

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

Задания:

1. Скопируйте файл sрisоk1000.Xls из папкиHCommonArm_managerв свою фамильную папку на жестком диске пк. Откройте его.

2. Перейдите в конец таблицы (Ctrl-End), убедитесь, что в ней 1000 записей, не считая строки заголовка. Вернитесь к ячейке А1 (Ctrl-Home).

3. Разделите рабочую область приложения на два окна (вкладка ВидгруппаОкнокнопкаРазделитьили маленькие кнопки выше вертикальной и правее горизонтальной линеек прокрутки). Просмотрите независимо разные фрагменты таблицы. Вернитесь к одному окну.

4. Закрепите строку заголовка, чтобы при прокрутке таблицы она оставалась на месте (ВидЗакрепить области,Закрепить верхнюю строку).

5. Закрепите произвольный фрагмент таблицы. Снимите закрепление областей.

6. Обеспечьте наличие заголовка таблицы на каждой странице при печати (вкладка Разметка страницыгруппаПараметры страницыкнопкаПечатать заголовки, выводимый диапазон A1:I1001, сквозная строка – 1, нажмите кнопкуПросмотр, чтобы убедиться в результате). Нажмите кнопкуОтмена.

7. Добавьте в таблицу поле Выручка, подсчитайте значения в этом поле.

8. Заблокируйте столбец Jот изменения формулы (значения при этом будут пересчитываться в случае изменения цены или количества товара). Для этого:

9. Выделите лист Список сделокцеликом (нажмите кнопку между именем столбца А и номером строки 1). На вкладкеГлавнаяв группеШрифтнажмите кнопку вызова диалогового окнаФормат шрифта ячейки. На вкладкеЗащитаснимите флажокЗащищаемая ячейка, а затем нажмите ОК.

10. Выделите столбец J. Для выделенных ячеекустановитефлажокЗащищаемая ячейка(см. предыдущий пункт).

11. На вкладке Рецензированиев группеИзменениянажмите кнопкуЗащитить лист. В спискеРазрешить всем пользователям этого листавыберите элементы форматирования ячеек листа, которые остаются доступными пользователям (нужно поставить флажки во всем списке). Можно ввести пароль для отключения защиты листа (в данном случае лучше этого не делать).

12. Если установленная защита листа будет мешать выполнять последующие действия, то можно ее отключить (в этом случае не будет действовать и защита ячеек).

13. С помощью фильтра определите список фирм, в которых можно купить писчую бумагу дешевле 100 руб. за пачку. Скопируйте полученные данные на пустой рабочий лист (назовите его Результаты). В исходной таблице снимите фильтр.

14. Создайте расширенный фильтр (ДанныеСортировка и фильтрДополнительно) для выбора организаций-покупателей, купивших в фирме Лето записные книжки в количестве более 40 штук. Сохраните выборку на листеРезультаты, там же должен быть диапазон условий. Снимите фильтр с исходной таблицы.

15. С помощью фильтра выведите данные по продаже калькуляторов. Проведите сортировку фильтра по покупателям. Скопируйте полученную укороченную таблицу на лист Результаты. Подсчитайте в ней промежуточные итоги по максимальному количеству калькуляторов, купленных разными организациями, (менюДанныеПромежуточный итог, в диалоговом окне: при каждом изменении в поле Покупатель, операция Максимум, итоги по полю Количество). В исходной таблице снимите фильтр.

16. Создайте копию листа Список сделок, назовите егоСортировка.

17. На этом листе выполните тройную сортировку списка по товарам, фирмам и ценам (вкладка ДанныегруппаСортировка и фильтркнопкаСортировка).

18. На базе исходной таблицы создайте сводную таблицу, показывающую суммарное количество всех товаров, купленных разными покупателями во всех фирмах (вкладка ВставкагруппаТаблицыкнопкаСводная таблица). Сводную таблицу поместите на новый лист, после формирования таблицы дайте ему имяСводная. Для составления макета сводной таблицы перетащите из списка полей таблицы: поле Количество в область значений (убедитесь, что операция Сумма), поле Фирма в область фильтра отчета, поле Товар в область строк, поле Покупатель в область столбцов.

19. На основе сводной таблицы на этом же листе постройте для фирмы «Зима» сравнительную диаграмму приобретения всех видов бумаги двумя покупателями (например, Ангарскнефтеоргсинтезом и Аэрофлотом). Используйте фильтры в полях сводной таблицы.

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

21. Сохраните преобразованную рабочую книгу под именем Фамилия_список1000.