Задание 2. Работа с данными: фильтрация, сортировка, консолидация

1. Откройте файл Обработка таблиц. xls. Открыть лист Таблица 1.

2. Перейти на лист 2 и переименовать его в Таблица 2. Оформите Таблицу 2, согласно своему варианту (для вариантов 4-5 вместо графы Зарплата введите Стипендия и вместо ячейки Ставка руб./нормо-часы введите Норматив стипендии, остальное – как показано на рис. 1). Предварительно установить в ячейках всех столбцов, кроме столбца А, формат: Денежный с числом знаков 0.

3. Скопировать в столбец А Таблицы 2 соответствующие данные из столбца А Таблицы 1.

4. Установить курсор в ячейку В5 и ввести формулу:=Таблица1!B4*$B$1 (для этого нужно перейти на Таблицу 1 и там щелкнуть по ячейке В4, нажать Enter, а затем вернуться в Таблицу 2 и произвести ней оставшиеся действия), после чего произвести мультиплицирование формулы на ячейки В6-В14. В столбце будет рассчитана норма зарплаты (стипендии).

5. В ячейку D5 ввести и мультиплицировать формулу =B5*0,13 (ставка налога на доход).

6. В ячейку С5 необходимо вставить формулу, рассчитывающую сумму премии: =ЕСЛИ(Таблица1!B4>Таблица1!$B$1;B5*0,1;0). С этой целью используйте функцию ЕСЛИ.

В диалоговом окне в поле Лог_выражение вставьте: Таблица1!B4>Таблица1!$B$1 (обе ячейки – из таблицы 1!), в поле Значение_если_истина - B5*0,1, а в поле Значение_если_ложь – 0. Мультиплицируйте функцию в ячейки С6-С14.

7. В ячейку Е5 таким же образом вставьте функцию для подсчета суммы штрафа:

=ЕСЛИ(Таблица1!B4<0,9*Таблица1!$B$1;0,5*(Таблица1!$B$1-Таблица1!B4);0).При этом в первое поле вставляется выражение: Таблица1!B4<0,9*Таблица1!$B$1;

во второе - 0,5*(Таблица1!$B$1-Таблица1!B4); в последнее поле вставьте 0. Мультиплицируйте функцию в ячейки Е6-Е14.

8. Наконец, в ячейку F5 вставьте формулу: =B5+C5-D5-E5 и мультиплицируйте её в ячейки F6-F14.

9. В заключение рассчитайте итоговые суммы во всех столбцах B – F.

10. Теперь произведем фильтрацию данных. Для этого щелкните по любой ячейке Таблицы 2 и исполните команду: Данные – Фильтр – Автофильтр., Щелкните по кнопке Список в столбце С и выберите вкладку Условие…. В диалоговом окне выберите (из списка) опцию больше (>) и в соседнем окне установите условие 0. В таблице 2 останутся только те данные, которые будут удовлетворять этим условиям, в данном случае – это люди, получившие премии. Скопируйте эту таблицу в буфер. Теперь отмените фильтрацию: щелкните по той же кнопке в том же столбце и выберите: (Все):. таблица восстановиться в исходном виде. Ниже нее вставьте из буфера фильтрованную таблицу с премиями.

11. Аналогичным образом произведите фильтрацию по признаку Штраф, т.е., выберите людей, получивших штрафы, и сохраните таблицу на том же листе.

12. Перейдите на Лист3 и переименуйте его в Таблица 3. Занесите в него данные согласно вашему варианту. Для заполнения столбца А используйте инструмент Автозаполнение. Для этого внесите в ячейку А5 первое значение 1. Выделите ячейки А4-А13 и выполните команду: Правка – Заполнить – Прогрессия … В диалоговом окне установите параметры: по столбцам, арифметическая, шаг 1.

13. При изменениях данных в Таблице 2 синхронно должны меняться и данные в Таблице 3 – выдачи зарплаты (стипендии). В таких ситуациях необходимо установить связи между ячейками соответствующих таблиц. Сделаем это: выделите несмежные диапазоны ячеек А5:А15 и F5:F15 таблицы 2 и скопируйте их в буфер. Перейдите на лист Таблица 3 и в ячейке В5 выполните команду: Правка – Специальная вставка и в появившемся окне нажмите кнопку Вставить связь. Теперь попробуйте изменить какой-либо параметр в Таблице 2 (например, зарплату (стипендию) одному из работников) и проверьте, что такие же изменения синхронно произошли и в Таблице 3. Попробуйте поменять значение параметра в ячейке В1 Таблицы 1 и убедитесь, что синхронные изменения происходят и в других таблицах: процессор Excel автоматически пересчитывает значения всех формул.

14. По результатам таблицы 3 постройте объемную, круговую диаграмму и поместите ее на том же листе. В качестве легенды вставьте Выдача, подпишите сегменты диаграммы: Ряд и в поле подписи катего_ введите столбец В с фамилиями работников (студентов).Диаграмму поместите на том же листе Таблица 3.

15. Отсортируйте данные в таблице 3 по убыванию. Для этого выделите таблицу (без ячейки ИТОГО), выполните команду: Данные – Сортировка, выберите столбец К выдаче и опцию по убыванию.

Таблица 2 (варианты 1-3)

Рис.1