Упражнение 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), то функция ВПР ищет точное соответствие.