Установите флажок в окне Поиск решения и нажмите кнопку ОК.

Лабораторная работа 1

Тема: Создание электронной таблицы MS Excel 2007.

«Расчет квартплаты»

Задание 1.1. Выполнить расчет оплаты за квартиру в ТСЖ, включающую в себя плату за коммунальные услуги, исходя из тарифов (вода, газ, тепло). Расчетная схема приведена на рисунке

Рис.1.1. Расчет квартплаты

Порядок выполнения

1. Заполнитетаблицу «Расчет квартплаты» (рис.1.1) по мере вселения жильцов.

2. Установите в столбце Площадь пользовательский формат «кв.м» (см. Введение, Форматы данных, п. 2.2.1.), а в тарифах - «р/чел.», «р/кв.м.»

3. Вычислите квартирную плату отдельно для каждой квартиры по формуле D8 = B8*$В$4+C8*($В$2+$В$3) в рублях.

4. Вычислите суммы в строке Всего.

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

6. Проведите сортировку по номеру квартиры, используя команду Вкладка Данные/Сортировка и фильтр/Сортировка. Перед сортировкой выделите всю таблицу.

Задание 1.2. Подготовьте круговую диаграмму на основании таблицы «Расчет квартплаты»

Порядок выполнения

1. Выделите столбцы №кв. (Категория) и Кв.плата (Ряд)и создайте диаграммму. Для этого: выберите вкладку Вставка/Диаграмма/Круговая (см.рис. 1.2)

Рис.1.2. Выбор диаграммы

2. Проверьте данные на диаграмме. Для этого установите мышь на диаграмму, щелкните правой кнопкой, выберите Выбрать данные, появится окно «Выбор источника данных» (рис.1.3) и, если необходимо, сделайте изменения (в окошечках Изменить).

Рис.1.3. Выбор источника данных

3. Подпишите диаграмму и данные. Проставьте на диаграмме доли (проценты - %) квартплат каждого квартиросъемщика в общей стоимости услуг (см. Введение, тему 3. «Диаграммы»)

Задание 1.3. Определите тарифы оплаты для получения общей квартплаты заданной величины, используя надстройку Поиск решения.

При анализе данных может возникнуть необходимость определить, как повлияет на результат формулы изменение значений одной или нескольких переменных. Например, вам может потребоваться найти, как следует изменить значения тарифов оплаты, чтобы суммарная квартирная плата ТСЖ имела определенное значение.

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

Порядок установки надстройки Поиск решения

Чтобы использовать эту надстройку в Excel, необходимо сначала загрузить ее.

Выберите команду Office\Параметры Excel\Надстройки, нажмите кнопку Перейти.

Установите флажок в окне Поиск решения и нажмите кнопку ОК.

Совет: Если Поиск решения отсутствует в списке поля Доступные надстройки, чтобы найти надстройку, нажмите кнопку Обзор. В случае появления сообщения о том, что надстройка для поиска решения не установлена на компьютере, нажмите кнопку Да, чтобы установить ее.

После загрузки надстройки для поиска решения во вкладке Данные/Анализ становится доступна команда Поиск решения.

Выберите команду Поиск решения и в появившемся окне сделайте следующие установки:

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

· включите флажок Равной значению ивведите нужное вам значение суммарной кварплаты (например, 10 000),

· в строке Изменяя ячейки укажите ячейки B2:В4 (тарифы оплаты, рис.1.1),

· щелкните на кнопке Выполнить.

Проанализируйте полученный результат.

Если формула содержит единственный изменяемый параметр, то удобнее воспользоваться надстройкой Подбор параметра. Данные\Работа с данными\Анализ «что-если» и выбрать Подбор параметра.

Задание 1.4. Подготовьтев текстовом редакторе Word отчет о квартплате в ТСЖ, используя возможности редактора. Подготовленную в MS Excel таблицу «Расчет квартплаты» внедрите в подготовленный в Word документ в режиме Связать, как показано на рис.1.4.

	Отчетная ведомость ТСЖ для расчёта квартплаты за январь 2013г.
Председатель ТСЖ Иванова А.А.

Рис. 1.4. Отчетная ведомость ТСЖ

 

Порядок выполнения

1. Выделите блок ячеек на рабочем листе Excel, содержащий таблицы «Расчет квартплаты», «Тарифы оплаты», диаграмму и отправьте его в буфер обмена.