Как свести учет рабочего времени, если данные из Ардуино?

Question

[Nikomas]

Друзья, созрел вопрос: Ардуинка по считыванию RFID метки шлет данные с датой, временем и UID записи в таблицу:

Date	Time	uidDec
7/1/2024	17:46:58 	863705125

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

=MAXIFS(Sheet1!$B$2:$B, Sheet1!$C$2:$C, "=863705125", Sheet1!$A$2:$A, "=7/1/2024") - MINIFS(Sheet1!$B$2:$B, Sheet1!$C$2:$C, "=863705125", Sheet1!$A$2:$A, "=7/1/2024")

Но вся соль происходит когда новые данные из Ардуинки вставляются над строкой с данными - вид формулы меняется на считывание данных не со второй строки Sheet1!$B$2:$B, а с предыдущей Sheet1!$B$3:$B. И соотв. новое время сработки RFID метки формула не видит.

Как можно зафиксировать формулу от сдвига? Как видите, символы $ не помогают.

Answer 1

[Alexander Ivanov]

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

=MAXIFS(
  INDIRECT("Sheet1!$B$2:$B"),
  INDIRECT("Sheet1!$C$2:$C"), "=863705125",
  INDIRECT("Sheet1!$A$2:$A"), "=7/1/2024"
)

Comments

[Nikomas]

Действительно работает. Премного благодарен. Но есть еще небольшой вопрос: например, сотрудник не закрыл сессию в день ее открытия

Date	Time	uidDec
7/1/2024	07:46:58 	863705125
7/2/2024	16:46:58 	863705125

Тогда не выполняется условие MAXIFS - MINFS за одну и ту же дату и в продолжительности имеем 0ч:00мин. Можно как-то помечать такие дефектные ячейки ошибкой?

Answer 2

[Nikomas]

Исправил немного скрипт:

sheet.insertRows(3); 
inserting an entire row
         sheet.getRange('A3').setValue(date_now); // publish current date to cell A3
         sheet.getRange('B3').setValue(time_now); // publish current time to cell B3
         sheet.getRange('C3').setValue(uidDec);   // publish uidDec from Arduino code to cell C3

А вторую строчку заполнил пустышкой с датой и временем.

Пока работает.

Comments

[Alexander Ivanov]

Отметил решением. Но без примера очень сложно судить о том, что у вас там есть и как оно работает.