ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 17.04.2021
Просмотров: 1374
Скачиваний: 2
36
Рисунок
37
Тестирование
таблицы
При
вложении
одной
формулы
в
другую
легко
допустить
ошибку
.
Для
того
чтобы
избежать
этого
возможно
использование
средства
Excel,
позволяющего
проследить
зависимость
значений
в
одних
ячейках
от
фор
-
мул
и
значений
,
находящихся
в
других
ячейках
.
Для
определения
зависимостей
поместите
табличный
курсор
в
рас
-
сматриваемую
ячейку
и
вызовите
команду
Сер
-
вис
→
Зависимости
→
Зависимые
ячейки
или
Влияющие
ячейки
.
После
этого
между
зависимыми
ячейками
появляются
стрелки
.
Они
показывают
непо
-
средственное
влияние
содержимого
одних
ячеек
на
формирование
резуль
-
тата
в
других
ячейках
.
При
выборе
команды
Влияющие
ячейки
стрелки
зависимостей
пока
-
зывают
на
ячейки
,
значения
которых
влияют
на
данную
ячейку
.
При
выборе
команды
Зависимые
ячейки
стрелки
будут
указывать
на
ячейки
,
значения
которых
зависят
от
данной
ячейки
.
В
случае
,
когда
нужно
проследить
большое
число
зависимостей
,
удобно
применять
панель
Зависимости
.
Использование
зависимостей
при
вложении
формул
1.
Поместите
табличный
курсор
в
ячейку
А
3
и
нажмите
кнопку
Зависимые
ячейки
.
2.
Скопируйте
в
строке
формул
формулу
из
ячейки
А
3
без
знака
равенст
-
ва
.
3.
В
ячейках
,
на
которые
указывают
стрелки
(
А
4
и
В
4),
произведите
заме
-
ну
адреса
ячейки
скопированной
формулой
.
После
выхода
из
режима
ре
-
дактирования
содержимого
ячейки
стрелка
зависимости
должна
исчезнуть
.
4.
Проделайте
эту
же
процедуру
для
диапазона
ячеек
В
3:D3.
37
5.
Установите
стрелки
зависимостей
для
ячейки
А
4
и
произведите
в
фор
-
мулах
зависимых
ячеек
аналогичную
замену
адресов
ячеек
содержащими
-
ся
в
них
формулами
.
6.
Еще
раз
установите
табличный
курсор
в
ячейку
А
4
и
проверьте
,
оста
-
лись
ли
еще
зависимые
ячейки
.
Если
нет
,
то
содержимое
ячейки
А
4
можно
удалить
.
7.
Проделайте
аналогичную
операцию
с
диапазоном
В
4:D4.
Вложение
формул
с
логическими
функциями
ЕСЛИ
лучше
начинать
с
самой
внут
-
ренней
.
Но
следует
помнить
,
что
для
функции
ЕСЛИ
допускается
не
более
7
уровней
вложения
.
Таким
образом
,
на
определенном
этапе
ячейку
,
кото
-
рая
влияет
на
другие
ячейки
и
в
которой
находится
сложная
формула
,
нужно
оставить
и
выполнить
вложение
формул
в
следующих
зависимых
от
нее
ячейках
.
Фрагмент
рабочего
листа
со
стрелками
,
показывающими
зависи
-
мость
одних
ячеек
от
других
представлен
на
рисунке
38:
Рисунок
38
Тема
6.
Электронный
табель
учета
рабочего
времени
.
На
основе
содержащихся
в
табеле
данных
производится
расчет
зара
-
ботной
платы
,
табель
обычно
связывают
с
базой
данных
сотрудников
и
с
ведомостью
расчета
заработной
платы
.
Табель
представляет
собой
именной
список
сотрудников
подразде
-
ления
,
например
,
цеха
,
отдела
и
т
.
д
.,
в
котором
учитывается
отработанное
каждым
сотрудником
время
.
В
табель
заносятся
данные
о
каждом
дне
,
а
в
качестве
итога
подсчитывается
время
за
месяц
.
На
основе
табеля
произво
-
дится
расчет
заработной
платы
.
Учет
использования
рабочего
времени
в
табеле
осуществляется
либо
методом
сплошной
регистрации
(
для
каждого
лица
фиксируется
время
прибытия
и
т
.
д
.),
либо
путем
регистрации
отклонений
(
опозданий
,
неявок
и
т
.
д
.).
Различают
:
двухстрочный
и
однострочный
табели
.
38
Двухстрочный
табель
Рассчитаны
на
предприятия
,
график
которых
предусматривает
ноч
-
ные
смены
,
сверхурочные
часы
и
т
.
п
.
Для
каждого
сотрудника
отводится
две
строки
:
в
нижней
указывает
-
ся
количество
часов
,
отработанных
в
ночное
время
,
а
верхняя
,
предназна
-
чена
для
ввода
остальных
данных
.
Функции
двухстрочного
табеля
1.
Автоматический
расчет
отработанного
времени
в
часах
,
в
том
числе
:
−
всего
отработанного
времени
;
−
времени
,
отработанного
в
выходные
и
праздничные
дни
;
−
времени
,
отработанного
ночью
.
2.
Учет
времени
в
днях
,
включая
:
−
отработанные
дни
;
−
дни
,
которые
сотрудник
провел
в
командировке
;
−
дни
,
когда
сотрудник
был
в
отпуске
;
−
дни
,
когда
сотрудник
был
в
учебном
отпуске
;
−
дни
,
пропущенные
из
-
за
болезни
;
−
дни
неявки
на
работу
по
неуважительной
причине
;
−
выходные
дни
.
Однострочный
табель
Предназначен
для
использования
на
предприятиях
,
где
не
ведутся
работы
в
ночное
время
,
праздничные
и
выходные
дни
.
Однострочный
табель
позволяет
:
−
автоматически
определять
нормативное
количество
рабочих
часов
;
−
определять
количество
календарных
дней
в
месяце
;
−
определять
коэффициент
для
начисления
заработной
платы
в
зависи
-
мости
от
отработанного
времени
;
−
выводить
сообщения
в
случае
возникновения
ошибок
при
вводе
.
Структура
однострочного
табеля
показана
на
рисунке
39:
39
Левая
часть
однострочного
табеля
.
Правая
часть
однострочного
табеля
.
Рисунок
39
Заполнение
области
ввода
1.
Вставьте
новый
лист
:
Однострочный
табель
.
2.
Начиная
с
ячейки
А
8
введите
названия
столбцов
№
п
/
п
,
Фамилия
,
Та
-
бельный
номер
,
Должность
.
3.
Пронумеруйте
столбец
№п
/
п
.
4.
Выполните
связывание
книг
:
−
откройте
две
книги
,
между
которыми
будет
установлена
связь
;
−
расположите
их
в
одном
окне
:
Окно
→
Расположить
.
−
перейдите
в
окно
Однострочный
табель
,
выделите
диапазон
ячеек
С
9:
С
18,
поставьте
знак
=.
−
перейдите
в
окно
файла
База
данных
,
установите
курсор
на
первый
табельный
номер
.
Ссылка
автоматически
создается
абсолютной
,
поменяй
-
те
ее
на
относительную
.
Нажмите
Ctrl+Enter.
−
выполните
аналогичные
действия
для
столбца
Должность
,
в
резуль
-
тате
на
листе
Однострочный
табель
появятся
формулы
вида
=[
База
дан
-
ных
.xls]
Сотрудники
!B2
.
5.
Выполните
автоматический
ввод
Ф
.
И
.
О
.
Используйте
функции
СЦЕПИТЬ
и
ЛЕВСИМВ
(
для
написания
инициа
-
лов
).
Формула
в
ячейке
В
9
возвращает
фамилию
,
которая
находится
в
ячейке
С
2
рабочего
листа
Сотрудники
и
инициалы
,
которые
берутся
из
ячеек
D2
и
Е
2.
Также
формула
обеспечивает
расстановку
между
ними
то
-
чек
и
пробелов
.
=
СЦЕПИТЬ
([
База
.xls]
Сотрудники
!C2;" ";
ЛЕВСИМВ
([
База
.xls]
Сотрудники
!D2;1); ". ";
ЛЕВСИМВ
([
База
.xls]
Сотрудники
!E2;1);".")
Не
забыть
:
Поменять
ссылки
с
абсолютных
на
относительные
,
после
окончания
ввода
формулы
нажать
Ctrl+Enter.
40
Действие
формулы
сводится
к
следующему
:
из
базы
данных
извлекается
полная
фамилия
,
а
от
имени
и
отчества
отсекаются
первые
буквы
,
после
которых
ставятся
точки
и
добавляются
пробелы
.
После
закрытия
книги
,
с
которой
установлена
связь
,
ссылка
изменится
:
в
ней
будет
указан
полный
путь
,
по
которому
находится
исходная
информа
-
ция
.
6.
Определите
нормативное
количество
рабочих
часов
.
Нормативное
количество
рабочих
часов
для
конкретного
месяца
будет
ука
-
зано
в
ячейке
D5,
а
количество
календарных
дней
в
этом
месяце
–
в
ячейке
D6.
Эти
данные
находятся
на
листе
Праздники
.
Используем
функцию
ВПР
,
которая
ищет
значение
в
крайнем
левом
столбце
таблицы
и
возвращает
значение
в
той
же
строке
из
указанного
столбца
таблицы
.
Буква
«
В
»
в
имени
функции
ВПР
означает
«
вертикальный
».
В
итоге
формула
в
ячейке
D5
будет
иметь
вид
:
=
ВПР
(J4;
Праздники
!B30:C41;2;
ЛОЖЬ
)
J4 –
ячейка
,
содержащая
название
месяца
;
Праздники
!B30:C41 –
диапазон
,
находящийся
на
листе
Празднике
и
содержащий
соотношение
названий
месяцев
и
количество
рабочих
часов
в
них
.
ЛОЖЬ
–
так
как
диапазон
не
отсортирован
.
Для
ячейки
D6
создайте
аналогичную
формулу
.
7.
Выполните
расчет
данных
в
диапазоне
ячеек
А
J9:
АР
19:
−
столбец
Итого
рабочих
дней
содержит
формулу
:
=
СЧЁТЕСЛИ
(E9:AI9;">0")
–
подсчитываются
ячейки
,
в
которых
приведены
цифры
.
−
столбец
Выходные
дни
:
=
СЧЁТЕСЛИ
(E9:AI9;"
в
")
−
столбец
Больничные
дни
:
=
СЧЁТЕСЛИ
(E9:AI9;"
б
")
−
столбец
Отпуск
:
=
СЧЁТЕСЛИ
(E9:AI9;"
от
")
−
столбец
Всего
дней
:
=
ЕСЛИ
(
СУММ
(AJ9:AM9)=$D$6;
СУММ
(AJ9:AM9);"
Ошибка
")
-
сравнивается
количество
дней
,
полученных
в
области
AJ9:AM9,
с
количеством
кален
-
дарных
дней
в
данном
месяце
,
указанном
в
ячейке
D6.
Если
условие
вы
-
полняется
,
выдается
общее
количество
дней
,
в
противном
случае
текст
–
«
Ошибка
!».
Ошибка
может
быть
связана
с
некорректным
вводом
данных
.
−
столбец
Итого
часов
:
=
СУММ
(E9:AI9)
−
столбец
Коэффициент
:
=
ОТБР
(AO9/$D$5;2)
−
в
созданном
электронном
табеле
нельзя
автоматически
определить
количество
рабочих
дней
для
сотрудников
,
отработавших
неполный
месяц
по
той
причине
,
что
они
в
этом
месяце
уволены
или
только
что
приняты
на
работу
.
Это
можно
исправить
модифицировав
формулу
в
ячейке
AN9:
=
ЕСЛИ
(
СУММ
(AJ9:AM9)+
СЧЁТЕСЛИ
(E9:AI9;"
ув
")=$D$6;
СУММ
(AJ9:AM9);"
Ошибка
!")
8.
Выполните
расчет
начисленной
суммы
за
месяц
.
Добавьте
столбец
Оклад
,
с
помощью
связывания
таблиц
.
Рассчитайте
,
сколько
начислено
каждому
сотруднику
по
формуле
=
Оклад
*
Коэффициент
.