Пример 2. Определение размера премии

- Введите исходные данные в рабочий лист электронной таблицы, как показано ниже.

- Для расчета премии использовалась формула: =F2*$J$2,автозаполнение которой в столбце G позволило определить премии для каждого сотрудника. Адрес ячейки J2, в которой указан размер премии в %, оформлен как абсолютный.

- В ячейке G13 вычислите суммарный фонд премии. Рабочий лист электронной таблицы будет выглядеть, как показано ниже.

- Определим размер премии в % так, чтобы суммарный фонд премии был равен заданному: 100000 руб.. Для этого активизируйте команду Данные / Анализ «что-если» / Подбор параметра.В диалоговом окнеПодбор параметра:

a) в поле Установить в ячейке: введите ссылку на ячейку G13, содержащую необходимую формулу;

b) введите искомый результат в поле Значение: 100000;

c) в поле Изменяя значение ячейки: введите ссылку на ячейку, значение которой нужно подобрать: J2. Формула в ячейке, указанной в поле Установить в ячейке: должна ссылаться на эту ячейку. ОК.

После нажатия ОК, Excel выдает окно Результат подбора параметра, где отображаются результаты операции.

В данном случае системе удалось подобрать аргумент, при котором результат равен 100000 (в клетке J2 -число 36%).

Задания для самостоятельной работы

1. Определите максимальную сумму кредита при условии, что кредит берется на 15 лет с процентной ставкой 5,75% при условии, что сумма ежемесячных платежей не должна превышать 11000 рублей.

Ежемесячный платеж рассчитывается с помощью функции =ПЛТ(Ставка;Кпер;Пс), где Ставка задает ежемесячную процентную ставку по кредиту, Кпер – количество периодов погашения кредита, Пс– сумма кредита.

2. Определите срок погашения ссуды, если сумма ссуды равна 180000 руб., процентная ставка составляет 1,7 % годовых, а ежемесячные платежи равны 3250 руб. (Для того, чтобы можно было использовать Подбор параметра, задайте предварительное значения срока погашения ссуды (месяцы), равным, например, 1).

3. Депозит открывается на 3 года с процентной ставкой 12,75% годовых. Какова должна быть начальная сумма депозита, если конечная сумма должна быть порядка 150000 руб.?

Конечная сумма депозита рассчитывается с помощью функции =БС(Ставка;Кпер;;-Пс), где Ставка задает ежемесячную процентную ставку депозита, Кпер – срок хранения депозита, Пс– начальная сумма депозита.

4. Используя режим подбора параметра, определить штатное расписание фирмы.

Известно, что в штате фирмы состоит: 1 директор, 1 секретарь, 2 юриста 1 категории, 2 юриста 2 категории, 2 адвоката, 1 электрик и 1 уборщица. Общий месячный фонд зарплаты составляет 150000 руб. Необходимо определить, какими должны быть оклады сотрудников фирмы.

Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата =A_i×х+В_i, где х - оклад уборщицы; А_i и В_i - коэффициенты, показывающие: А_i - во сколько раз превышается значение х; В_i - на сколько превышается значение х.

Фильтрация

Краткая справка

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

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

1. Использование только одного списка на листе. Избегайте создания более чем одного списка на листе. Некоторые возможности управления списком, например фильтрация, могут быть использованы только для одного списка.

2. Помещайте подобные объекты в один столбец. Спроектируйте список таким образом, чтобы все строки содержали подобные объекты в одном столбце.

3. Держите список отдельно. Между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец. Это позволяет Excel быстрее обнаружить и выделить список при выполнении сортировки, наложении фильтра или вставке вычисляемых автоматически итоговых значений.

4. Располагайте важные данные сверху или снизу от списка. Избегайте размещения важных данных слева или справа от списка; данные могут быть скрыты при фильтрации списка.

5. Отображение строк и столбцов. Перед внесением изменений в список убедитесь в том, что все скрытые строки и столбцы отображены. Если строки или столбцы списка скрыты, данные могут быть непреднамеренно удалены.

6. Использование форматированных подписей столбцов. Создайте подписи столбцов в первой строке списка. Excel использует подписи при создании отчетов, поиске и оформлении данных. Шрифт, выравнивание, формат, шаблон, граница и формат прописных и строчных букв, присвоенные заголовкам столбцов списка, должны отличаться от формата, присвоенного строкам данных. Перед вводом подписей столбцов ячейкам должен быть присвоен текстовый формат.

7. Использование границ ячеек. Для отделения подписей от данных следует использовать границы ячеек, а не пустые строки или прерывистые линии.

8. Отсутствие пустых строк и столбцов. В самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.

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

10. Расширенный список форматов и формул. При добавлении новых строк в конец списка Excel использует согласованные форматы и формулы. Чтобы это произошло, три из пяти предыдущих ячеек должны иметь тот же формат или содержать ту же формулу.

Пример 3. Премия

Чтобы отфильтровать список с помощью расширенного фильтра, столбцы списка должны иметь заголовки.

1. Скопируйте из списка заголовки фильтруемых столбцов. Вставьте скопированные заголовки столбцов в первой пустой строке диапазона условий отбора.

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

3. Предположим, необходимо составить список сотрудников - женщин.

Укажите ячейку в списке.

Выберите на вкладке Данные в группе Сортировка и фильтр команду Дополнительно. Откроется диалоговое окно Расширенный фильтр.

Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель Обработка в положение фильтровать список на месте.

Чтобы скопировать отфильтрованные строки в другую область листа, установите переключатель Обработка в положение скопировать результаты в другое место, перейдите в поле Поместить результат в диапазон:, а затем укажите верхнюю левую ячейку области вставки (в нашем случае - A20).

Введите в поле Диапазон условий: (F16:G17) ссылку на диапазон условий отбора, включающий заголовки столбцов. ОК.

В результате получим.

4. Если необходимо выбрать мужчин с окладом больше 20000 руб., то формируем диапазон условий следующего вида:

В результате использования расширенного фильтра получим.

Краткая справка