ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 17.04.2021
Просмотров: 1388
Скачиваний: 2
26
7.
В
ячейках
Н
5
и
Н
14
просуммируйте
количество
сотрудников
по
отде
-
лам
и
по
должностям
.
8.
Выполните
проверку
рассчитываемых
значений
:
Результаты
сложных
и
наиболее
важных
расчетов
всегда
нужно
прове
-
рять
на
правильность
.
Важным
средством
контроля
могут
служить
допол
-
нительные
ячейки
,
в
которых
производятся
те
же
расчеты
,
но
другим
ме
-
тодом
,
или
расчеты
,
позволяющие
проверить
основной
результат
.
Для
нашей
задачи
возможен
следующий
метод
контроля
:
если
в
списке
работников
нет
ошибки
,
то
значения
в
столбце
оклады
должны
быть
боль
-
ше
нуля
,
для
этого
в
ячейку
Н
16
занесем
формулу
=
СЧЕТЕСЛИ
($
Е
$2:$
Е
$11;”>0”)
Если
расчеты
производились
правильно
,
то
численность
сотрудников
по
должностям
и
отделам
должна
совпадать
.
Изменение
должностных
окладов
.
Предположим
,
финансовые
возможности
предприятия
позволяют
увеличить
штатные
оклады
сотрудников
на
7,7%.
Рассчитаем
новые
став
-
ки
,
воспользовавшись
несколькими
методами
.
При
этом
необходимо
учи
-
тывать
,
что
размер
оклада
должен
выражаться
целым
числом
,
то
есть
не
содержать
копеек
.
Скопируйте
лист
Количество
сотрудников
и
переименуйте
его
в
Ок
-
лады
.
Использование
диалогового
окна
Специальная
вставка
1.
Скопируйте
лист
Количество
сотрудников
и
переименуйте
его
в
Окла
-
ды
.
2.
В
ячейку
F1
введите
заголовок
Новый
оклад
(
Специальная
вставка
)
,
в
диапазон
F2:F11
скопируйте
значения
старых
окладов
.
3.
В
ячейку
С
14
введите
значение
индекса
увеличения
оклада
(1,077).
4.
Скопируйте
содержимое
данной
ячейки
.
5.
Выделите
диапазон
F2:F11,
содержащий
оклады
.
6.
Из
контекстного
меню
выберите
Специальная
вставка
.
7.
В
области
Вставить
появившегося
окна
активизируйте
переключатель
Значения
,
в
области
Операция
-
переключатель
Умножить
,
Ок
.
В
результате
все
числа
,
указанные
в
ячейках
F2:F11,
будут
умноже
-
ны
на
значение
1,077,
введенное
в
ячейку
С
14.
Но
при
использовании
дан
-
ного
метода
значения
увеличенных
окладов
выражены
в
рублях
с
копей
-
ками
.
Применение
формул
1.
В
ячейку
G1
введите
заголовок
Новый
оклад
(
формула
)
.
2.
Выделите
диапазон
G2:G11,
введите
формулу
=
ОКРУГЛ
(
старый
оклад
*
значение
индекса
увеличения
оклада
;0)
Нажмите
Ctrl+Enter.
Использование
коэффициентов
Размер
оклада
каждого
сотрудника
с
помощью
определенного
коэф
-
фициента
«
привязывается
»
к
окладу
ведущего
специалиста
(
например
,
ди
-
27
ректора
или
начальника
отдела
).
Допустим
,
оклад
начальника
отдела
Реа
-
лизации
составляет
1400
руб
.
Новая
зарплата
других
сотрудников
опреде
-
ляется
умножением
оклада
начальника
на
заранее
установленный
коэффи
-
циент
.
1.
В
ячейку
Н
1
введите
заголовок
Новый
оклад
(
коэффициенты
)
,
в
ячейку
I1 –
Оклад
(
расчетный
)
,
в
ячейку
–
Коэффициент
.
2.
В
ячейку
I2
занесите
старый
оклад
начальника
отдела
Реализации
–
1400,00
руб
.,
в
ячейку
I3 –
индекс
увеличения
оклада
,
в
ячейку
I4
формулу
расчета
нового
оклада
начальника
=
ОКРУГЛ
(I2*(1+I3);0).
3.
Заполните
диапазон
J2:J11
коэффициентами
,
используемыми
при
пере
-
расчете
окладов
,
а
в
диапазон
Н
2:
Н
11
формулами
расчета
нового
оклада
сотрудников
,
например
для
ячейки
Н
2
=
ОКРУГЛ
($I$4*J2;0).
Расчет
окладов
всеми
рассмотренными
способами
с
числовыми
дан
-
ными
приведен
на
рисунке
:
Рисунок
31
Проверка
данных
Обратите
внимание
на
лист
Сотрудники
:
в
строке
10
указан
сотруд
-
ник
,
который
уже
уволился
,
но
ему
начисляется
заработная
плата
.
Автома
-
тизируем
процессы
поиска
и
исправления
ошибок
.
1.
Вставьте
новый
лист
,
который
назовите
Проверка
данных
.
На
новом
листе
разместите
:
•
столбцы
с
листа
Количество
сотрудников
:
Отдел
,
Должность
,
Фами
-
лия
,
Табельный
номер
,
Оклад
;
•
столбцы
с
листа
Сотрудники
:
Табельный
номер
,
Фамилия
,
Отдел
,
Дата
приема
на
работу
,
Дата
увольнения
.
2.
Внесите
ошибки
в
табельные
номера
.
Если
работник
уволен
Формула
,
с
помощью
которой
можно
определить
,
числился
ли
со
-
трудник
в
списке
работников
на
момент
расчета
премии
,
основана
на
функции
ЕПУСТО
,
относящийся
к
категории
Проверки
свойств
и
значений
.
28
ЕПУСТО
(
значение
) –
функция
проверяет
содержимое
ячейки
и
,
если
ячейка
ни
-
чего
не
содержит
,
возвращает
логическое
значение
ИСТИНА
,
если
в
ячейке
на
-
ходится
какая
-
либо
информация
,
функция
возвращает
значение
ЛОЖЬ
.
Т
.
е
.
с
помощью
этой
функции
можно
выяснить
занесено
какое
-
либо
значение
в
ячейки
столбца
Дата
увольнения
.
Если
ячейка
пуста
,
то
сотруд
-
ник
еще
работает
.
Сравнение
табельных
номеров
.
Воспользуемся
функцией
ЕСЛИ
:
=
ЕСЛИ
(D2=G2;
ИСТИНА
;
ЛОЖЬ
)
Сравнение
фамилий
У
нас
в
одном
столбце
указана
лишь
фамилия
,
а
в
другом
фамилия
и
инициалы
.
Поэтому
воспользуемся
текстовыми
функциями
:
•
сосчитаем
количество
символов
в
ячейке
С
2 (
фамилия
и
инициалы
)
до
первого
пробела
;
•
извлечь
из
ячейки
С
2
количество
символов
,
расположенных
слева
от
первого
пробела
.
Для
определения
символов
,
предшествующих
первому
пробелу
,
вос
-
пользуемся
функцией
НАЙТИ
.
=
НАЙТИ
(" ";
С
2) –
в
ячейке
С
2
занесена
фамилия
с
инициалами
.
Далее
применим
функцию
ЛЕВСИМВ
:
ЛЕВСИМВ
(C2;
НАЙТИ
(" ";C2)-1)
–
получим
фамилию
без
инициалов
.
Отни
-
мается
1,
т
.
к
.
функция
НАЙТИ
определяет
положение
пробела
,
следующего
после
фамилии
.
Осталось
сравнить
фамилии
,
в
итоге
получится
формула
:
=
ЕСЛИ
(H2=
ЛЕВСИМВ
(C2;
НАЙТИ
(" ";C2)-1);
ИСТИНА
;
ЛОЖЬ
)
Соответствие
всем
условиям
Для
проверки
выполнения
всех
трех
условий
:
сотрудник
не
уволен
,
совпадения
табельных
номеров
и
совпадения
фамилий
,
воспользуемся
функцией
И
,
которая
возвращает
значение
ИСТИНА
,
если
все
аргументы
имеют
значение
ИСТИНА
;
возвращает
значение
ЛОЖЬ
,
если
хотя
бы
один
аргумент
имеет
значение
ЛОЖЬ
,
получим
формулу
:
=
И
(L2;M2;N2)
Результаты
представлены
на
рисунке
32:
29
Рисунок
32
Составление
сложной
формулы
методом
вложения
Будем
заменять
ссылки
на
ячейку
содержимым
этой
ячейки
,
т
.
е
.
если
формула
включает
адрес
ячейки
,
которая
,
в
свою
очередь
,
содержит
фор
-
мулу
,
необходимо
вместо
адреса
вставить
саму
формулу
,
находящуюся
по
этому
адресу
.
Для
этого
выделяется
первая
формула
без
знака
=
и
копируется
,
за
-
тем
курсор
устанавливается
на
ячейку
,
ссылающуюся
на
эту
формулу
,
и
вместо
адреса
ячейки
вставляется
сама
формула
с
помощью
Ctrl+Insert
и
т
.
д
.
В
результате
получим
итоговую
формулу
:
=
И
(
ЕПУСТО
(
К
2);
ЕСЛИ
(D2=J2;
ИСТИНА
;
ЛОЖЬ
);
ЕСЛИ
(H2=
ЛЕВСИМВ
(C2;
НАЙТИ
(" ";C2)-1);
ИСТИНА
;
ЛОЖЬ
))
Промежуточные
столбцы
L, M
и
N
можно
удалить
,
а
можно
скрыть
.,
для
этого
выделите
скрываемые
столбцы
и
выполните
Фор
-
мат
→
Столбцы
→
Скрыть
или
из
контекстного
меню
Скрыть
.
Расчет
премии
за
выслугу
лет
Премия
за
выслугу
лет
зависит
от
стажа
работника
,
ее
величина
определя
-
ется
на
основании
данных
таблицы
:
Стаж
,
годы
Премия
, %
Менее
1
Не
начисляется
От
1
до
3 (3
не
входит
) 10
От
3
до
5 (5
не
входит
) 20
От
5
до
10 (10
не
входит
) 30
Свыше
10
40
Алгоритм
вычисления
премии
.
1.
Определить
общее
количество
проработанных
на
предприятии
дней
(
из
даты
начисления
премии
необходимо
вычесть
дату
приема
на
работу
).
2.
Определить
число
отработанных
сотрудником
лет
,
разделив
полученное
на
предыдущем
этапе
число
дней
на
365,25 –
среднее
число
дней
в
году
с
учетом
високосных
лет
.
30
3.
Отбросить
от
полученного
значения
дробную
часть
.
4.
Произвести
начисление
премии
согласно
таблице
.
5.
Если
проверка
,
выполненная
выше
,
не
показала
ошибку
,
зачесть
полу
-
ченную
сумму
премии
,
в
противном
случае
выдать
сообщение
об
ошибке
.
1.
Определение
полного
количества
лет
работы
на
предприятии
:
Для
отбрасывания
дробной
части
используем
математическую
функцию
ОТБР
,
которая
усекает
число
до
целого
,
отбрасывая
дробную
часть
числа
,
так
что
остается
целое
число
.
В
итоге
для
первого
сотрудника
имеем
формулу
:
=
ОТБР
(($O$2-
J2)/365,25)
,
где
$
О
$2 –
ячейка
,
содержащая
дату
расчета
премии
, J2 –
дата
приема
на
работу
1
сотрудника
.
2.
Расчет
суммы
премии
.
Расчет
производится
с
использованием
логических
функций
ЕСЛИ
.
Первая
формула
создается
по
принципу
:
если
служащий
проработал
менее
года
(
значение
ячейки
Q2
сравнивается
со
значением
ячейки
N4),
то
пре
-
мия
равна
произведению
значения
оклада
,
указанного
в
ячейке
Е
2,
на
ко
-
эффициент
,
внесенный
в
ячейку
О
4.
В
противном
случае
рассматривается
стаж
от
1
года
до
3
лет
и
т
.
д
.
В
итоге
для
первого
сотрудника
формула
для
расчета
премии
будет
иметь
вид
:
=
ЕСЛИ
(Q2<1;O4;
ЕСЛИ
(Q2<$N$5;E2*$O$5;
ЕСЛИ
(Q2<$N$6;E2*$O$6;
ЕСЛИ
(Q2<$N$7;E2*$O$7;E2*$O$8))))
3.
Учет
проверки
условий
(
если
сотрудник
не
уволен
,
табельные
номера
и
фамилии
совпадают
,
то
начисляется
премия
,
в
противном
случае
выводит
-
ся
-
Ошибка
!)
=
ЕСЛИ
(L2;S2;"
Ошибка
!")
В
результате
должна
получиться
следующая
таблица
:
Рисунок
33
4.
Формирование
приказа
о
премии
за
выслугу
лет
•
создайте
типовой
бланк
приказа
в
Word,
оставив
место
для
вставки
таблицы
,
сформированной
в
Excel;