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

Например, предположим, что Sheet1 — это ваш сводный рабочий лист, и у вас есть подробная информация о банковских счетах на Sheet2. При просмотре подробной информации у вас есть даты в столбце A и остатки для разных счетов в столбцах B, C и D. Таким образом, подробная информация представляет собой таблицу, которая показывает текущую прогрессию банковских остатков на разные даты.

Собирая сводную информацию на Листе 1, вы понимаете, что вам нужно сослаться на последние цифры в столбцах B, C и D. Эти цифры представляют собой последние балансы и, таким образом, идеально подходят для вашего сводного отчета. Как ты делаешь это? В частности, когда вы продолжаете добавлять информацию в подробный рабочий лист с течением времени?

Собственно, есть несколько способов подойти к проблеме. (Обычно есть несколько способов решить любую проблему Excel.) Один из способов — использовать функцию ВПР. В том месте сводки, где вы хотите получить последний баланс из столбца B детали (Sheet2), вы должны поместить следующую формулу:

=VLOOKUP(MAX(Sheet2!$A:$A),Sheet2!$A:$D,2)

Чтобы изменить ссылки для двух других остатков на счетах, вы просто измените последнее число (2) на 3 (для счета в столбце C) или 4 (для счета в столбце D). Функция работает, потому что она ищет максимальное значение в столбце A, который содержит даты. Затем он просматривает таблицу данных (Sheet2! $ A: $ D) и находит соответствующее смещение для нужного столбца.

Этот подход работает нормально, если в столбце A нет дат после последних введенных балансов. Если да, то возвращаемые значения всегда будут неверными.

Другой способ решить проблему — использовать функцию ИНДЕКС вместе с СЧЁТЧИКОМ или СЧЁТЧИКОМ. Если столбцы сведений не содержат текста (даже в заголовках столбцов), вы должны использовать функцию COUNT. Если есть текст, предпочтительнее COUNTA. В точке, где вы хотите включить последний баланс из столбца B детали, вы должны использовать следующую формулу:

=INDEX(Sheet2!B:B,COUNTA(Sheet2!B:B))

Он просматривает таблицу, определяет количество непустых ячеек в столбце B, а затем извлекает число из этой последней непустой ячейки. Чтобы адаптировать формулу для столбцов C и D, просто замените ссылки B на соответствующие C или D.

Еще один способ справиться с проблемой — использовать функцию СМЕЩЕНИЕ, как показано ниже:

=OFFSET(Sheet2!B1,COUNTA(Sheet2!B:B)-1,0)

Эта функция возвращает значение ячейки смещения от базовой опорной ячейки. В этом случае базовой ячейкой является Sheet2! B1. Функция COUNTA используется для определения количества строк для смещения от базы, а 0 указывает, что смещение должно быть в том же столбце, что и базовая ссылка. Чтобы изменить формулу для столбцов C и D, просто замените все ссылки на B на C или D.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (12470) применим к Microsoft Excel 2007, 2010, 2013 и 2016.

Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Referring_to_the_Last_Cell [Ссылка на последнюю ячейку].