☰

Лист 1# --- Сводная ведомость на конец предыдущего месяца

tag-icon • tag-icon microsoft-excel worksheet-function logging tracking
Лист 1# --- Сводная ведомость на конец предыдущего месяца

Моя цель — отслеживать часы работы оборудования и составлять ежедневный отчет, отражающий общее количество часов и время работы.

Я хочу создать файл с рабочими листами по 31 дню в рабочей тетради за весь месяц, чтобы можно было организовать файлы по дате.

Порядок рабочих листов в файле следующий:

Первый рабочий лист будет представлять собой начальный обзор часов за предыдущий месяц.

Следующие 31 рабочий лист — это листы с 1 по 31 для каждого дня месяца.

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

Я хотел бы, чтобы рабочая книга была настроена и готова к использованию без манипуляций пользователя с задействованными формулами. Все, что нужно будет сделать пользователю, это ввести начальные часы на первом листе, а затем каждый день вводить итоговую сумму на конец дня для различного оборудования.

Пример одного столбца:

Лист 1# --- Сводная ведомость на конец предыдущего месяца

Расположение ячейки:(Лист 1~~~~Ячейка A1)

Формула ячейки: нет формулы - Пользователь ввел число часов на конец месяца100

Окончательный вид:

100

Лист 2#--- День 1

Расположение ячейки:(Лист 2 ~~~~Ячейка A1)

Формула ячейки: нет формулы - Пользователь ввел число для часов конца дня

Использование: Общее количество часов на сегодня — значение, введенное пользователем в конце дня.125

Расположение ячейки:(Лист 2 ~~~~Ячейка A2)

Формула ячейки: =+'лист 1'!A1

Использование: Часы с конца предыдущего дня - (значение взято из Листа 1 ~~~~Ячейка A1-100

Расположение ячейки:(Лист 2 ~~~~Ячейка А3)

Формула ячейки: =A1-A2

Использование: Сегодняшнее время выполнения - простой расчет значений из ячеек Листа 2 ~A1, вычитаемых из ячеек Листа 2 ~A2 "вышеуказанных ячеек"-24

Окончательный вид:

124

100

24

Следующий день будет организован следующим образом:

Лист 3#--- День 2

Расположение ячейки:(Лист 3, Ячейка А1)

Формула ячейки: Формула отсутствует — пользователь ввел число для часов конца дня

Использование: Общее количество часов за сегодня (значение, введенное пользователем в конце дня)148

Расположение ячейки:(Лист 3, Ячейка А2)

Формула ячейки: =+'лист 2'!A1

Использование: Часы с конца предыдущего дня - (значение взято из Листа 2 - Ячейка A1 -124

Расположение ячейки:(Лист 3 - Ячейка А3)

Формула ячейки: =A1-A2

Использование: Today's Runtime - простой расчет значений из ячеек Листа 3 - A1 минус Листы 3~A2 "вышеуказанные ячейки на том же листе"-24

Окончательный вид:

148

124

24

Это была самая легкая часть...

Теперь можно легко получить значение 31 дня с помощью нескольких столбцов чисел.

Лист 4#,

Я нажимаю правой кнопкой мыши на вкладку, выбираю перемещение или копирование, создаю копию. Переименовываю новую вкладку как "лист 4"

При использовании описанного выше метода создания нового рабочего листа формулы будут такими же, как и на предыдущем листе, и будут извлекать информацию из того же места (лист 2).

Ячейка А1введенный пользователем номер, и этот номер переносится так же, как и на скопированном листе.

Ячейка А2теперь копируется как: =+'sheet 2'!A1

Ячейка А3копируется как обычный расчет "=A1-A2", который вычисляется из ячеек в пределах одного листа. Никаких проблем.

Я хотел бы получить формулу вячейка А2автоматически обновиться до:

=+'лист 3'!A1при изготовлении следующего листа.

Как мне сделать так, чтобы новый лист подтянул исправленные формулы ячеек, не исправляя их вручную?

Учитывая, что с одного листа нужно извлечь более 40 ячеек информации и перенести их на следующий день, вручную вносить изменения и изменять их 1240 раз для настройки одного месяца было бы слишком много.

решение1

Меню «Найти и заменить».http://office.microsoft.com/en-us/excel-help/find-or-replace-text-and-numbers-on-a-worksheet-HP001216390.aspx

по сути, вы хотите найти в формулах '=+'лист 3'!A1' и заменить на '=+'лист 4'!A1'. Мне приходилось делать много таких таблиц Excel, 2 в течение целого года, и это было лучшее решение, с которым я мог работать.

решение2

Вот метод, который сделает то, что вам нужно, при условии, что имена ваших вкладок контролируются и заканчиваются возрастающими числами, как вы описываете.

Я вам объясню:

  1. Нужно знать, на каком листе я нахожусь, чтобы знать, как "вычислить" название листа предыдущего дня. ДопустимХХ= =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256). На Листе 1 возвращается строка «Лист 1», на Листе 2 возвращается «Лист 2».

  2. Теперь мне нужно проанализировать эту строку, чтобы выяснить число, и вычесть из него 1, чтобы получить вчерашнее число, но только если мой день не 1.ГГ"="=IF(MID(XX,7,2)>1,MID(XX,7,2)-1,1)

  3. И наконец, вот в чем магия: используйте функцию INDIRECT, чтобы вычислить мою «цель» на лету. =INDIRECT("'Sheet "& YY &"'!A1")

Соберите все вместе, выделив жирным/курсивом те части, которые нужно настроить (я не использовал разметку кода, так как подсветка, похоже, не работает внутри КОДА):

=КОСВЕННЫЙ("'Лист"& ЕСЛИ(СРЕДН(СРЕДН(ЯЧЕЙКА("имя_файла",A1),НАЙТИ("]",ЯЧЕЙКА("имя_файла",A1))+1,256),7,2)>1,СРЕДН(СРЕДН(ЯЧЕЙКА("имя_файла",A1),НАЙТИ("]",ЯЧЕЙКА("имя_файла",A1))+1,256),7,2)-1,1) &"'!А1")

Что необходимо обновить:

  • «Лист» — замените на префикс вашего листа (часть перед номером)
  • '7' -- замените на длину указанного выше префикса +1 (или замените на функцию)
  • «A1» — замените на адрес нужной ячейки с предыдущего листа.
  • Не меняйте «имя файла» — оно должно остаться прежним (специальное зарезервированное имя)
  • Не нужно менять A1 внутри функции CELL. Это произвольно и не нужно для этого зарезервированного имени, и его не нужно менять.

Связанный контент