912 подписчиков

Контроль сроков действия права самостоятельного производства экспертиз (способы адресации ячеек)

26 июня 202326 июн 2023

6 мин

(Применение табличных процессоров в практической деятельности судебных экспертов; Глава 2. Другие примеры использования электронных таблиц)

Применение табличных процессоров в организационной работе не ограничивается рассмотренным в Главе 1 созданием индивидуального журнала учёта экспертиз. Ещё один вариант подобного использования электронных таблиц будет описан ниже, но сначала придётся сделать отступление, чтобы рассказать об одной важной особенности, касающейся способов указания ссылок на ячейки.

Создайте новую книгу и в ячейки “А1” и “А2” одного из листов введите число 1. После этого в ячейку “А3” введите следующую формулу:

=А2+А1

Выделите ячейку “А3” и при помощи маркера заполнения откопируйте находящуюся в ней формулу до ячейки “А12”. Получившийся при этом ряд чисел – известная в математике последовательность Фибоначчи, два первых члена которой равны единице, а все последующие – сумме двух предыдущих:

F₁ = 1, F₂ = 1, Fₙ₊₁ = Fₙ + Fₙ₋₁ (n ≥ 2)

А теперь давайте посмотрим, как будет себя вести отношение двух соседних членов последовательности, если из него при этом вычитать некую наперёд заданную величину, которая будет указываться в ячейке “D1”. Введите туда число 10, а в “В3” – формулу:

=A3/A2-D1

Если теперь попытаться маркером заполнения откопировать её на остальные ячейки в столбце “В”, то окажется, что в них отображается только отношение членов последовательности Фибоначчи, а число 10, находящееся в ячейке “D1”, не вычитается, точнее откопированные формулы пытаются что-то вычитать, но при этом «цепляют» не те ячейки в столбце “D”:

Формулы в показанных на рисунке ячейках “В4” и “В5” ссылаются на “D2” и “D3” соответственно, но так как те пусты, то в расчётах их численное значение принимается равным нулю. Можно ли при копировании формул маркером заполнения заставить программу брать число именно из ячейки “D1”? Да, можно, и сделать это очень легко – нужно лишь сначала немного поправить исходную формулу в ячейке “В3”:

=A3/A2-$D$1

Она в таком изменённом виде, будучи размноженной маркером заполнения, приведёт к результатам, схематически показанным ниже:

Результаты эти отличаются от тех, что были получены до корректировки формулы. Указанный способ «жёсткой» привязки к значению какой-либо конкретной ячейки при помощи знаков доллара $ перед обозначением столбца и строки носит название абсолютной адресации в отличие от использовавшейся до этого адресации относительной.

Введите в ячейку “D1” вот такую формулу:

=(КОРЕНЬ(5)+1)/2

=(SQRT(5)+1)/2

В ней отобразится число 1,61803... , известное под названием «золотого сечения». Обозначается оно греческой буквой «фи»:

Нетрудно видеть, что в этом случае вычисляемая в столбце “В” разница

Fₙ₊₁ / Fₙ – φ при увеличении n стремится к нулю:

Приведённый пример наглядно демонстрирует следующую связь между золотым сечением и последовательностью Фибоначчи:

Обратите также внимание на ячейку “В12”: её содержимое отображается как "-5,6461E-005" – это принятая в табличных процессорах экспоненциальная форма записи чисел: мантисса и порядок отделяются друг от друга заглавной латинской буквой "E". В более привычном виде приведённое число записывается как –5,6461·10⁻⁵ или –0,000056461. Таким образом, величины, подобные числу Авогадро Nₐ = 6,022·10²³ моль⁻¹ и постоянной Планка h= 6,626·10⁻³⁴ Дж·с, в формулах будут выглядеть соответственно как 6,022E+23 и 6,626E–34 .

Следует отметить, что абсолютная адресация может применяться не только к ячейке, но и к столбцу или к строке. В этом случае символ $ нужно ставить лишь перед соответствующей частью адреса. Так, в приведённом выше примере можно было бы обойтись обозначением "D$1" (вместо "$D$1") – подобная ссылка означает, что для столбца “D” используется относительная адресация, а к первой строке применена абсолютная адресация.

Набор текста формулы для вычисления Fₙ₊₁ / Fₙ – φ можно осуществлять и так:

выделите “B3”,
введите "=",
щёлкните по “А3”,
нажмите "/",
щёлкните по “А2”,
нажмите "-",
щёлкните по “D1”,

нажмите [F4]

воспользуйтесь комбинацией [Shift] + [F4]

Примечание: В Calc из LibreOffice 7.5 достаточно нажать [F4] (без [Shift]).

и вы увидите, что в набираемой формуле запись "D1" заменится на "$D$1" (повторение последнего действия будет приводить к циклическому изменению написания "D$1" – "$D1" – "D1" – "$D$1" – выберите подходящий вариант адресации), после этого останется закончить ввод формулы нажатием [Enter]. Сходным образом можно редактировать ссылки на ячейки и в уже введённых формулах.

Ну а теперь пора от отступления перейти к основной текущей теме. Поскольку судебным экспертам по каждой имеющейся у них экспертной специальности необходимо каждые пять лет проходить методическое рецензирование для продления права самостоятельного производства экспертиз, само собой разумеется, что в судебно-экспертном учреждении должна быть соответствующая информация о всех сотрудниках. При помощи электронных таблиц к подобным сведениям можно добавить некоторой дополнительной функциональности. Взгляните на таблицу на рисунке ниже (приводится по состоянию на дату 17.04.2015):

Думается, что с содержимым столбцов А-F всё и так понятно. Легко также видеть, что для удобства здесь добавлен автофильтр и включена фиксация первых четырёх строк. В столбце “G” рассчитывается стаж по экспертной специальности сотрудника по формуле (показано на примере ячейки “G5”):

=ЕСЛИ(E5<>"";ОКРУГЛВНИЗ((СЕГОДНЯ()-E5)/365,25;0);"")

=IF(E5<>"";ROUNDDOWN((TODAY()-E5)/365,25;0);"")

Как эта формула работает? Сначала она проверяет, указана ли дата присвоения какой-либо экспертной специальности – это сделано для новых сотрудников, ещё не успевших аттестоваться на право самостоятельного производства экспертиз. При отсутствии таких данных формула оставляет ячейку со стажем пустой, в противном случае производится расчёт: сначала из текущей даты вычитается дата присвоения эксперту специальности и полученное значение делится на среднее число суток в году. Частное от деления округляется в меньшую сторону до целого числа при помощи функции:

ОКРУГЛВНИЗ( ; )

ROUNDDOWN( ; )

Первым аргументом у неё служит округляемое число, а вторым – число десятичных знаков, до которого надо осуществлять округление (0 означает округление до целого).

Формула в столбце “Н” следующая (показана на примере “Н5”):

=ЕСЛИ(ИЛИ(E5=""; F5=""); ""; ЕСЛИ(СЕГОДНЯ()-F5>0; "Просрочено !"; ЕСЛИ(F5-СЕГОДНЯ()<$K$3;"* * *"; "")))

=IF(OR(E5="";F5="");"";IF(TODAY()-F5>0;"Просрочено !"; IF(F5-TODAY()<$K$3;"* * *";"")))

Как видно, формулы могут быть весьма громоздкими из-за нескольких вложенных друг в друга функций, что затрудняет восприятие и бывает сложно разобраться, что именно они делают. Из функций, которые ещё не рассматривались ранее, в приведённой формуле присутствует такая:

ИЛИ( ; ; ... )

OR( ; ; ... )

Эта логическая функция выполняет проверку ряда указанных ей нескольких условий (в нашем случае – двух) и возвращает значение «Истина», если хотя бы одно из условий выполняется, и значение «Ложь» – если ни одно условие не выполнено.

Обратите внимание, что в формуле ячейка “K3” имеет абсолютную адресацию – в ней пользователем указывается число дней для начала выдачи оповещения (в виде знаков « * * * ») о необходимости продления права самостоятельного производства экспертиз перед окончанием его действия. Алгоритм работы рассматриваемой формулы удобней представить в виде следующей блок-схемы:

Условия:

у1 – Обе ли ячейки – «Дата присвоения» и «Дата окончания» – содержат данные?

у2 – Текущая дата превышает дату окончания действия права самостоятельного производства экспертиз?

у3 – На текущую дату до окончания срока действия права самостоятельного производства экспертиз осталось меньше дней, указанных в ячейке оповещения (“K3”)?

Действия:

д1 – Оставить ячейку пустой

д2 – Выдать сообщение «Просрочено!»

д3 – Выдать сообщение « * * * »

д4 – Оставить ячейку пустой

Непосредственный результат работы формулы выглядит так (по состоянию на дату 17.04.2015):