Упражнение 3. Применение функции ЕСЛИ при проверке условий. Создать таблицу расчета премии за экономию горюче-смазочных материалов (ГСМ)

Ключ к заданию:

Исходные данные:

В столбце F производится расчет премии при условии что План расходования ГСМ > Фактически израсходовано ГСМ.

Для проверки условия используйте функцию ЕСЛИ. Установите курсор в ячейке F4 и выберите командную вкладку Формулы, Логические. Задайте следующие параметры:

Для расчета в остальных строчках используйте маркер заполнения (навести курсор в правый нижний угол ячейки и когда курсор примет вид зачерненного крестика, растянуть в нужном направлении).

После окончания расчета произведите сортировку по столбцу «ФИО». Для этого установить курсор в любую ячейку столбца ФИО и нажать кнопку на командной вкладке Данные.

По данным столбцов «ФИО» и «кВыдаче» постройте диаграмму. Тип диаграммы: гистограмма.

Упражнение 4. Создать ведомость начисления заработной платы.

Ключ к заданию.

§ Профсоюзный и пенсионный налоги примем как 1% от оклада. Удобно ввести формулу в одну ячейку, а затем распространить ее на оба столбца. Поскольку ссылка будет только на оклад, формулы в 4 и 5 столбцах будут иметь ссылки только на столбец «Оклад», поэтому «закрепляем» адрес столбца(нажатием клавиши F4). Формула будет иметь вид: =$С6*0,01. После ввода формулы в ячейку D6 ее нужно растянуть вниз, а затем вправо с помощью маркера заполнения.

§ Подоходный налог подсчитаем по формуле: 12% от оклада за вычетом минимальной заработной платы и пенсионного налога. После ввода формулы копируем ее вниз. Вид формулы: (С6-Е6-$G$2)*0,12

§ После ввода данных выполним их сортировку, если фамилии набраны не по алфавиту.

§ Заполним итоговую строку, подсчитав с помощью автосуммирования сумму окладов, а затем скопировав формулу по нижней строке.

§ Переименуем лист, назвав его «Начисления». Для этого на ярлычке листа вызвать контекстное меню и выбрать команду Переименовать.

§ Построим диаграмму, отражающую начисления каждого сотрудника. Выделим ячейки второго столбца, а затем удерживая нажатой клавишу Ctrl, ячейки 7-го столбца.

 

Пример созданной круговой диаграммы:

§ Создадим ведомость компенсации на детей на новом листе. Лист переименуем в «Детские».

§ В графу «Фамилия, имя, отчество» скопируем список с листа «Начисления». Установим связь между листами. Для этого на листе «Детские» выделим ячейку А2 и введем формулу =Начисления!В6 (после набора знака равенства переходим на лист Начисления и выделяем ячейку В6). После этого копируем формулу вниз.

§ В графе «Сумма» размещаем формулу =Начисления!Н6*Детские!$G$1. Скопируем формулу вниз.

§ Чтобы список состоял только из сотрудников, имеющих детей, отфильтруем его по критерию Сумма >0. Для этого нужно использовать расширенный фильтр. Предварительно создаем диапазон условий (A14:A15) и выбираем командную вкладку Данные, Дополнительно.

Упражнение 5. Создать табель учета рабочего времени.

Ключ к заданию.

§ Сформатируйте заголовок табеля учета рабочего времени за текущий месяц, применив различные способы форматирования.

§ Выделите цветом столбцы, соответствующие нерабочим дням.

§ Для каждого сотрудника проставьте количество часов, отработанных за день, о – если он в отпуске, б – если болеет, п – если прогуливает.

§ для фиксирования столбца «Фамилия», чтобы он оставался на своем месте: выделите столбец справа от столбца «Фамилия» и на командной вкладке Вид выберите команду Разделить.

§ Для подсчета дней явок и неявок используются формулы.

§ Сначала подсчитываем количество ячеек, содержащих числа, не суммируя их. Для этого используется функция СЧЕТ. После вызова функции указываем диапазон значений для первого сотрудника. После этого копируем формулу вниз.

§ Для подсчета количества дней, проведенных в отпуске, вставляем функцию СЧЕТЕСЛИ, в качестве критерия вводим «о».

§ Аналогично считаются дни прогулов и болезни.

§ Сформатируйте готовый табель.

§ Постройте плоскую гистограмму, в которой будут отражены дни явок и число отработанных часов для каждого сотрудника.

Лабораторная работа №7

Организация связи между таблицами на разных листах

Цель занятия: изучение информационной технологии организации расчетов с использованием встроенных функций в таблицах MS Excel.

Теоретические сведения.

Функция ВПР ищет значение в крайнем левом столбце справочной таблицы и возвращает значение в той же строке из указанного столбца таблицы.

Синтаксическая формула функции:

=ВПР (искомое_значение; таблица; номер_столбца; интервальный просмотр)

- искомое_значение – это значение, которое должно быть найдено в первом столбце массива. Искомое_значение может быть значением, ссылкой или текстовой строкой.

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

Если признак имеет значение ИСТИНА, то значения в первом столбце таблицы должны быть расположены в возрастающем порядке, в противном случае функция ВПР может выдать неправильный результат.

Если признак имеет значение ЛОЖЬ, то таблица не обязана быть отсортирована.

Значения в 1 столбце аргумента таблицы могут быть текстами, числами или логическими значениями.

- номер_столбца – это номер_столбца в таблице, в которой должно быть найдено соответствующее значение.

- интервальный просмотр – это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ЛОЖЬ(0), то функция ВПР ищет точное соответствие.