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

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

Есть несколько способов решения этой проблемы. Все методы предполагают, что даты в столбце A расположены в возрастающем порядке и что значения в столбце B не находятся в каком-либо заметном порядке. (Другими словами, показания могут колебаться выше и ниже нуля в любую дату.)

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

=IF(B1<0,A1,"")

Эта формула возвращает дату в столбце A, если значение в столбце B меньше 0 (отрицательное), в противном случае она ничего не возвращает. Все, что вам нужно сделать, это найти минимальное значение в столбце C:

=MIN(C:C)

Отформатируйте результат как дату, и он представляет дату, когда показания впервые стали отрицательными.

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

=MIN(IF(B1:B42<0,A1:A42,""))

=OFFSET($A$1,MATCH(TRUE,$B$1:$B$42<0,0)-1,,,)

=INDEX(A:A,MIN(IF(B1:B42<0,ROW(B1:B42))))

=INDEX(A1:A42,MATCH(TRUE,B1:B42<0,0))

=INDIRECT("A"&MIN(IF(B1:B42<0,ROW(B1:B42))),TRUE)

Помните, что все это формулы массива, поэтому вам нужно ввести ту, которую вы выберете, нажав Shift + Ctrl + Enter. Отформатируйте результат как дату, и это будет ответ, который вы ищете.

Если хотите, вы также можете использовать простой макрос для определения даты:

Function GetFirstNegative(rngdata)

Dim c As Variant

For Each c In rngdata         If c < 0 Then             GetFirstNegative = c.Offset(0, -1)

Exit Function         Else             GetFirstNegative = "All Data is Positive"

End If     Next End Function

На рабочем листе вы должны использовать эту пользовательскую функцию следующим образом:

=GetFirstNegative(B1:B42)

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

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

Этот совет (7092) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Finding_the_Date_Associated_with_a_Negative_Value [Поиск даты, связанной с отрицательным значением].