Нажать кнопку Дополнительно на закладкеДанные

4. Заполнить появившееся окно Расширенный фильтр.

Исходный диапазон - часть таблицы, которая будет фильтроваться. Если до выполнения команды фильтрации была помечена часть таблицы или вся таблица, то в этом окне автоматически появиться абсолютный адрес фильтруемой части таблицы. Диапазонусловий - задается адрес блока, в котором описаны критерии фильтрации. Поместить результат в диапазон - указать начальный адрес таблицы, которая будет получена после фильтрации. Это окно доступно для заполнения, если указана опция - скопировать результат в другое место. ОпцияТолько уникальные записи позволяет устранять повторяющиеся записи в результирующей таблице.

При выполнении команды расширенной фильтрации, если в окне фильтрации была задана опция - фильтровать список на месте, доступ к исходной таблице невозможен.

ЗАДАНИЕ

1. Скопируйте с листа «Прибыль» таблицу прибыли предприятий на три новых листа. Операцию копирования нужно осуществлять с использованием склеивания листов. Склеивание листов применяется для записи “под копирку”, т.е. при заполнении одного листа точно такая же информация появляется на всех склеенных с ним листах. Для этого выполните следующие действия:

· Перейдите на новые листы и назовите их соответственно «Сортировка», «Автофильтр», «Фильтр».

· Перейдите на лист «Прибыль», выделите таблицу прибыли предприятий вместе с заголовком и выполните команду копирования.

· Склейте листы «Сортировка», «Автофильтр», «Фильтр»: щелкнуть мышью на листе «Сортировка», нажать клавишу Shift и щелкнуть на листе «Фильтр».

· Перейдите на лист «Сортировка», встаньте на ячейку А1 и выполните команду Вставить.

2. Расклейте листы, для этого перейдите на лист «Прибыль».

3. На листе «Сортировка» отсортируйте таблицу по возрастанию прибыли первой фирмы и по убыванию прибыли третьей фирмы.

· Встать на любую ячейку таблицы.

· Выполнить команду Сортировка.

· В появившимся окне установить опции сортировки: Сортироватьпо: Фирма 1 (заголовок поля, в котором перечислена прибыль первой фирмы), рядом с окном выбрать опцию по возрастанию.

· Для добавления сортировки по Фирма 3, нажмите кнопку Добавить уровень, установить сортировку Фирма 3по убыванию.

· Нажать кнопку ОК

4. На листе «Автофильтр» выберите из таблицы данные с прибылью всех предприятий не меньше 50 единиц, используя автофильтр. Для этого нужно выполнить следующие действия.

· Встать на любую клетку таблицы

· Выполнить команду Автофильтр.

· Раскрыть список фильтрации на имени Фирма 1. В появившимся окне задать Числовые фильтры.

· В окне Пользовательский фильтр задать операцию – больше или равно, и число 50.

· Для полей Фирма 2 и Фирма 3 сделатьфильтрацию данных - оставить значение больше или равно 50 (аналогично тому как делали для фирмыФирма 1)

5. На листе «Фильтр» необходимо получить данные о месяцах, в которых прибыль Фирмы 1 была больше 60 единиц и прибыль Фирмы 3 была больше 70 единиц. Для выполнения данного запроса необходимо сделать следующие действия:

• В ячейку F2 скопировать имя Фирма 1 (имя поля, соответствующего Фирме 1), в ячейку G2 скопировать имя Фирма 3.
• В ячейке F3 записать >60.
• В ячейке G3 записать >70.
• Пометить всю таблицу, кроме последней строки - итоговой прибыли, вместе с заголовками полей.
• Выполнить команду для Расширенного фильтра.
• Заполнить появившееся диалоговое окно
• задать опцию копировать результат в другое место
• в окне исходный диапазон автоматически появиться абсолютный адрес блока, соответствующий фильтруемой таблице.
• в окне диапазон критериев задать F2:G3
• в окне поместить результат в диапазон написать A20 (результирующая таблица будет находиться ниже исходной.
• Нажать кнопку ОК.

ЛАБОРАТОРНАЯ РАБОТА № 9
«Подбор параметров. Создание сценариев»

Подбор параметровпозволяет подобратьпараметр для данного целевого значения. Окно Подбор параметра вызывается: закладка Данные, Анализ «что если»,Подбор параметра и содержит три опции:

· опция Установить в ячейке указывает целевую ячейку (адрес ячейки),

· опция Значение задает значение целевой ячейки,

· опция Изменяя значение ячейки задает варьируемый параметр (адрес ячейки).

Результат выполнения операции подбора параметра появится в окне Результат подбора параметра. Если нажать кнопку ОК значения ячеек будут изменены в соответствии с найденным решением, при нажатии кнопки Отмена ячейки не изменяться. Длительный поиск можно приостановить кнопкой Пауза. В этом случае станет доступной кнопка Шаг – пошаговое выполнение, а вместо кнопки Пауза появиться кнопкаПродолжить.

Для работысо сценариями нужно открыть закладку Данные, Анализ «что если»,Диспетчер сценариев. Окно Диспетчера сценариев содержит следующие опции: Показать – демонстрирует работу текущего сценария, Удалить – удаляет сценарий из списка, Добавить – добавление нового сценария, Редактировать – внесение изменений в сценарий, Объединить – дает возможность распространить созданные сценарии на другие листы текущей рабочей книги, Отчет– дает возможность посмотреть итоги работы сценария либо в виде отчета, либо в виде сводной таблицы. Нажатие последней кнопки выводит на экран диалоговое окно Отчет по сценарию, в котором можно выбрать опции: Ячейки результата– указываетсякакие ячейки нужно включить в отчет, (при выборе этой опции и закрытии диалога в книгу будет добавлен новый лист с итогами работы сценария). Структура и Сводная таблица – на новом листе создается отчет по сценариям в виде структуры и сводной таблицы.

Для создания нового сценария нужно выполнить следующие действия.

1. Вызвать Диспетчер сценариев.

2. В окне Диспетчер сценариев нажать кнопкуДобавить.

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

4. В появившемся окне Значения ячеек сценария следует записать адрес изменяемой ячейки. Нажать кнопку ОК.

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

Для сохранения результатов работы сценария нужно выполнить следующие действия.

1. В окне Диспетчер сценариев выделить нужный сценарий и нажать кнопку Итоги.

2. В появившемся окне Отчет по сценарию выбрать Структур или водная таблица, задать Ячейки результата и нажать кнопку ОК. Ячейками результата будут ячейки, значения которых нужно включить в результат работы сценария. Если была выбрана опция Структура, то появиться новый лист Структура сценария; если Сводная таблица – лист Сводная таблица по сценарию.

Задание 1.

Создайте новый лист «Расчет». Скопируйте на этот лист данные с листа «Налог». Используя Подбор параметров рассчитайте за сколько нужно продать квартиру первому человеку, что бы его доход составил ровно 3000000 руб.

Задание 2.

Перейдите на лист «Налог». Создайте сценарии, показывающие какой доход получит второй человек, если будет продавать квартиру за 2700000 руб., 2900000 руб., 3200000 руб. В отчете использовать Структуру.