Методика выполнения работы

Методика выполнения работы

1. Создать представленные таблицы на одном рабочем листе Excel.

2. Присвоить диапазонам ячеек имена:

- выделить блок ячеек, содержащий значения начальной стоимости и выполнить команду Формулы Þ Присвоить имя. Задать имя Первоначальная_стоимость (обратите внимание, что в именах не должно быть пробелов);

- выделите блок ячеек, содержащих значения года создания и выполните команду Формулы Þ Присвоить имя. Задать имя Год_создания;

- таким же образом присвоить имя диапазону Год эксплуатации

3. Выделите блок ячеек, содержащий таблицу 5.11, и присвойте ему имя Справочная_таблица.

Далее воспользуемся функцией ГПР. Функция ГПР берет значение из первого диапазона, сравнивает со значениями первой строки второго диапазона, находит равное ему или ближайшее наибольшее, а затем в качестве результата берет значение из указанной строки второго диапазона, в нашем примере - строка 2. Подобным образом работает функция ВПР, но она работает с диапазонами ячеек, расположенными вертикально.

4. В первую ячейку, находящуюся на пересечении строки с годом 2000 и столбца – год 2005 введите формулу =Первоначальная_стоимость*(1-ГПР(Год_эксплуатации-Год_создания;Справочная_таблица;2)). скопируйте формулу с помощью автозаполнения в другие вычисляемые ячейки

Задание 2

Необходимо создать две связанные таблицы для учета отдыхающих в Доме отдыха. В зависимости от категории комнаты и количества дней проживания рассчитать сумму оплаты. Выполнить поиск, и подстановку значений в таблицу используя функцию ПРОСМОТР. При этом количество дней проживания определяется как разница между днем отъезда и днем заезда.

1. Создайте новую рабочую книгу под именем «Дом отдыха».

2. Переименуйте первый лист в «Сведения» и заполните лист (рис.5.71).

3. Присвойте имена диапазонам командой Формулы Þ Присвоить имя.

a) А2:А31 - комнаты

б) В2:В31 - категория

в) D2:D7 – цена_категория

г) Е2:Е7 – цена

д) G2 – курс

е) G5 – сегодня

Рисунок 5.71 – Таблица для заполнения

4. Перейдите на лист 2, переименуйте его в «Заезды».

5. Подготовить таблицу следующего вида (рис.5.72):

Рисунок 5.72 – Таблица для заполнения

6. Заполните столбец D (номер комнаты) данными по своему усмотрению, на основании данных листа «Сведения».

7. В ячейку Е2 введите формулу =ПРОСМОТР(D2;комнаты;категория) и скопировать ее вниз до 14 строки.

8. В ячейку F2 для определения этажа введите формулу =ЛЕВСИМВ(D2;1) и скопируйте ее вниз.

9. В ячейку G2 для определения количества дней проживания вводим функцию, =ЕСЛИ(C2=0;сегодня-B2;C2-B2)

10. В ячейку H2 для определения суммы оплаты в $ введем формулу =ПРОСМОТР(E2;цен_категория;цена)

11. В ячейку I2 для определения суммы оплаты в рублях введем формулу =H2*Курс

12. Создать сводную таблицу, позволяющую определить общую сумму оплаты по месяцам заезда. Для этого установите курсор внутри таблицы «Заезды» и выполните команду Вставка Þ Таблица Þ Сводная таблица. Откроется макет для размещения полей. На ось строк перетащите поле Дата прибытия, а в область данных поля: Сумма в рублях и Сумма в $ (рис. 5.73).

Рисунок 5.73 – Макет сводной таблицы

13. Установите активную ячейку в поле даты на начальное значение и выполните команду Данные Þ Структура ÞГруппировать. Заполните диалоговое окно (рис. 5.74) так, чтобы выполнялась группировка по месяцам и нажать ОК.

Рисунок 5.74

Сохраните файл.

Самостоятельное задание

1. В таблице приведена выработка рабочих за 3 дня. Создать на рабочем листе таблицу. На новом листе определить выработку каждого работника по дням с помощью функции СУММЕСЛИ и оформив в виде таблицы.

Ф.И.О.	№ уч-ка	23.05.2009	24.05.2009	25.05.2009
Иванов
Петров
Сидоров
Петров
Петров
Иванов
Иванов
Петров
Иванов
Петров
Сидоров
Иванов
Петров
Иванов
Сидоров
Сидоров
Иванов
Сидоров
Сидоров
Иванов
Сидоров
Петров
Иванов
Петров
Петров
Сидоров
Сидоров

2. Найти в Excel решение уравнения АХ=В, если все его аргументы – массивы.

Массив А		Массив В

Лабораторная работа № 7 Работа со сводными таблицами

Цель работы: освоить навыки создания, редактирования и анализа данных на основе сводных таблиц.

Задание.

Построить сводную таблицу для расчета месячной заработной платы рабочих при повременной форме оплаты труда, начисления премии и учета удержаний. Премия дифференцирована по разрядам: 2 разряд 25%, 3 разряд 32%, 4 разряд 50% к тарифу, 5 разряд – 50%. Удержания берутся со всех видов начислений (зарплата, премия) и составляют 13% от суммы начислений.

Дата добавления: 2017-02-01