Я пытаюсь, чтобы ячейка возвращала значение самой старой даты, учитывая, что она совпадает с именем магазина (A5 указано в формуле), а также проверяет, не просрочен ли первый столбец. Дата хранится в столбце E на листе открытых заказов. Любой совет?
1 ответ
1
AGGREGATE может быть хорошим вариантом. Это может быть формула, которая будет выполнять вычисления, подобные массиву, не будучи фактическим массивом. Причина, по которой я могу сказать, заключается в том, что только определенные функции AGGREGATE выполняют вычисление массива ... 14 и 15 - две из них. И еще одна причина, по которой я говорю, может быть хорошим вариантом, заключается в том, что при выполнении вычисления массива вы хотите избегать использования полных ссылок на столбцы, таких как F:F в массиве. Это может привести к большому количеству лишних вычислений и может привести к сбою в работе вашей системы. Расчет массива должен быть разумно ограничен данными, с которыми вы имеете дело.
Давайте посмотрим на функцию AGGREGATE и каковы ее части
AGGREGATE(function #, What to include #, Calculation, parameter)
- Функции 14 и 15 будут сортировать результаты от наибольшего к наименьшему, а другие - к другому направлению.
- Что включать #, имеет несколько вариантов игнорирования скрытых строк или результатов, которые дают ошибки. Я склонен использовать 6, который игнорирует ошибки, но включает в себя все остальное. Возможно, вам придется настроить в соответствии с вашими потребностями.
- Расчет - это область, в которую вы хотите поместить информацию, которую вы ищете, и ваши проверки состояния. Ваши проверки состояния - это действительно булевы функции, которые возвращают TRUE или FALSE. Если мы отправим их с помощью математической операции, Excel преобразует TRUE = 1 и FALSE = 0. Поэтому, если агрегат настроен на игнорирование ошибки и вас интересует только информация, соответствующая вашим условиям, разделите вашу информацию на ваши условия. Ложные условия будут генерировать ошибку деления на 0, и агрегатная функция будет игнорировать эту соответствующую информацию.
- Параметр является необязательным значением для некоторых функций, но для 14 и 15 он сообщает AGGREGATE, какая позиция в отсортированных результатах значений, которые вас интересуют. Размещение 1 здесь вернет либо наименьшее, либо наибольшее число в зависимости от того, используете ли вы функцию 14 или 15
Так что для вашей формулы мы бы сделали что-то вроде:
=AGGREGATE(14,6,'Open POs'!E2:E100/(('Open POs'!A2:A100="Overdue")*('Open POs'!F2:F100=$A5),1)
Если это дает вам самую раннюю дату, то измените 14 на 15. Настройте диапазоны в соответствии с вашими данными вместо использования полных ссылок на столбцы.
Предостережение: Ваши даты должны храниться в формате Excel, а не в виде строк. Вы можете проверить это ISNUMBER(F4), где F4 - одна из ваших ячеек даты. Если вы получите ложный результат, ваши даты должны быть преобразованы.