4.3. Лабораторная работа e2 Расчет по формулам с использованием абсолютных и относительных ссылок

Дан месячный фонд зарплаты 60000 руб. Для работы отдела нужны: один уборщик, один вахтер, четыре контролера, два кассира, два старших кассира, два старших контроллера и один заведующий отделом. Зарплата сотрудника равняется зарплате уборщика, умноженной на коэффициент К, плюс доплата Д. Надо ввести таблицу, задаться зарплатой уборщика, рассчитать по формуле зарплату остальных сотрудников, определить суммарную зарплату и, изменить зарплату уборщика так, чтобы получить фонд месячной зарплаты ≈ 60000 руб.

№	Должность	К	Д	Зарплата сотрудника	Количество сотрудников	Суммарная зарплата
1	Уборщик	1	0		1
2	Вахтер	1,5	0		1
3	Кассир	4	0		2
4	Контролер	5	0		4
5	Ст. кассир	4	40		2
6	Ст. контролер	5	50		2
7	Зав. отделом	7	0		1
	Итого

4.4. Лабораторная работа e3 Расчет значений функции, построение диаграммы в виде поверхности

1. Подготовить таблицу значений функции Z=(X-4)²+(Y-6)² .

В первую строку, применяя автозаполнение, ввести значения X, а в первый столбец - значения Y. В ячейку, находящуюся на пересечении столбца X=0 и строки Y=1, ввести формулу для вычисления Z. При вводе формулы необходимо там, где это требуется, использовать абсолютную адресацию строк и столбцов. Далее, применяя автозаполнение, формулу необходимо скопировать в остальные ячейки таблицы.

2. Построить диаграмму в виде поверхности

Рис. 4.2. Построение поверхности

Z=(X-4)²+(Y-6)²

4.5. Лабораторная работа e4 Консолидация данных. Построение сводной таблицы

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

Последовательность работы:

1. Выделить ячейку, которая будет соответствовать левому верхнему углу новой таблицы консолидированных данных. В нашем случае пусть это будет ячейка A20.

2. Вызвать команду Данные/Консолидация.

3. В окне Консолидация (Рис. 4.3) в списке Функция укажите Сумма. В этом же окне установите флажок Использовать в качестве имен значения левого столбца.

4. Установите курсор в строкуСсылка. Выделите первую исходную область. В нашем примере это A4:D7 и нажмите (щелчок мышью) кнопку Добавить.

5. Повторите эти действия для диапазоновA9:D12 и A14:D16, затем нажмите кнопку ОК - будет построена таблица консолидированных данных (Рис 4.4).

Рис. 4.3. Окно диалогаКонсолидация

Рис. 4.4. Таблица консолидированных данных

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

Порядок работы:

1) Выполните командуДанные/Сводная таблица и выберите режим Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel (Рис. 4.5). Перейдите к следующему шагу - кнопка Далее.

Рис. 4.5. Мастер сводных таблиц, шаг первый

2. Укажите диапазон данных. В нашем случае это A2:E10 (Рис. 4.6).

Рис. 4.6. Мастер сводных таблиц, шаг второй

3) На третьем шаге нужно создать макет сводной таблицы. Для этого переместите мышкой кнопкуКафедра на поле Страница, кнопку Наименование – на поле Строка, кнопку Цена – на поле Столбец, кнопку Сумма – на поле Данные (Рис. 4.7).

Рис. 4.7. Мастер сводных таблиц, шаг третий

4. После нажатия кнопки Далее будет создана следующая сводная таблица:

Таблица показывает для одной (выбранной - в поле B3) кафедры или для всех цены (в строке 6) и стоимость каждого вида приобретенного оборудования.