#excel #datatable #excel-formula
#excel #datatable #excel-формула
Вопрос:
Я использую Excel 2010.
У меня есть «ежемесячная» таблица данных, которая выглядит примерно так:
MonthBegin InventoryExpenses Overhead TotalSales TotalSalesIncome TotalProfit
July-11 $1,500 $4,952 89 $7,139 $687
August-11 $2,200 $4,236 105 $8,312 $1,876
September-11 $1,100 $4,429 74 $6,691 $1,162
Следующая формула автоматически распространяется на каждую ячейку в столбце [MonthBegin]:
=DATE( 2011, 7 ( ROW( ) - 2 ), 1 )
У любого другого colmun есть аналогичная формула для столбцов, которая автоматически извлекает соответствующие данные из другого источника, на основе месяца, указанного в столбце [MonthBegin].
При такой конфигурации я могу просто вставить новую строку в любое место таблицы, и следующий месяц автоматически появится внизу в правильном порядке (что я нахожу отличным).
Но мне нужно вывести это на следующий уровень автоматизации, чтобы угодить руководству.
Как я могу сделать так, чтобы электронная таблица автоматически добавляла строку за октябрь по окончании месяца?
Я рассматривал возможность использования динамического диапазона для таблицы:
=OFFSET(A1,0,0,( ( YEAR( TODAY( ) ) - 2011 ) * 12 ) ( MONTH( TODAY( ) ) - 7 ),6)
… но Excel не примет такую формулу для области таблицы, я полагаю, потому что она не статична.
Кто-нибудь может объяснить мне, как получить эту функциональность с моей таблицей данных?
Комментарии:
1. Вы хотите постоянно добавлять месяцы или свертывать месяцы, т. Е. добавлять новый месяц и удалять старый месяц?
2. @Jon49 постоянно добавляет. С течением месяцев таблица должна становиться длиннее.
Ответ №1:
Вы не можете динамически добавлять новую строку только с формулой.
Вот процедура события VBA, которая сделает свое дело. Вам нужно поместить в Workbook module
Option Explicit
Private Sub Workbook_Open()
Dim lo As ListObject
Dim iTot As Long
Set lo = ListObjects("MyTable")
iTot = lo.Range.Rows.Count
'Add this statements before the Range with your worksheet name
'ThisWorkbook.Worksheets("Sheet1")
If Now() > Range("A" amp; iTot).Value Then
Range("A" amp; lo.Range.Rows.Count 1).Formula = "=DATE( 2011, 7 ( ROW( ) - 2 ), 1 )"
End If
End Sub
Не забудьте изменить название вашей таблицы и добавить название вашего рабочего листа (см. Комментарий внутри кода)
Ответ №2:
Я предполагаю, что вы используете встроенную в Excel «таблицу»? Не уверен, как вы заставите ее автоматически расширяться, но вы можете использовать эту формулу, которую я тестировал в 2007 году, она работает.
В течение первого месяца использования:
=DATE(YEAR(E1),MONTH(E1),1)
Где E1 — это первый месяц, с которого вы хотите начать.
Затем используйте:
=IF(TODAY()>=F2,DATE(YEAR(F2),MONTH(F2) 1,1),"")
Где F2 — это просто ячейка над вашей текущей ячейкой. А затем просто перетащите ее вниз так далеко, как вы хотите.
Комментарии:
1. У меня уже есть эта функциональность в виде =DATE(2011, 7 ( СТРОКА ( ) — 2 ), 1 ) в виде формулы столбца. Она автоматически вводится в каждую ячейку для всего столбца и зависит только от общей высоты таблицы. Вот почему я хочу сделать высоту таблицы динамической. 🙂
Ответ №3:
Хорошо, вот что вам, вероятно, нужно было бы сделать.
Инструменты> Дизайн> Инструменты> Преобразовать в диапазон
Используйте динамическое имя для вашего нового диапазона. Вы можете использовать INDEX метод или OFFSET method. Затем используйте условное форматирование, чтобы она выглядела красиво.
Я посмотрел в «Формулах Excel 2010» Джона Уокенбаха, и он не дает никакого решения проблемы с таблицами. Я думаю, это потому, что таблицы должны быть автоматическими, поэтому, к сожалению, вы теряете некоторый контроль, когда делаете это.
Вы также могли бы автоматизировать это с помощью VBA.