Решения Excel для разных версий

Excel значительно эволюционировал за последнее десятилетие, и каждая версия содержит разные решения множества проблем.

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

За последние десять лет Excel кардинально изменился. Теперь, как никогда ранее, версия вашего программного обеспечения определяет, что вы можете делать и насколько легко решить эту проблему.

График, с которым я работаю, показан в Рисунок 01.

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

Мой отчет отображается в Рисунок 02. Столбцы имеют четыре разных решения. В строке 1 указаны версии, с которыми он работает. Оценка 2 описывает решение.

Все формулы взяты из 4 класса в Рисунок 02 появляется в Рисунок 03.

Все формулы строки 4 скопированы. Вам нужно будет обратиться к Рисунок 03 Я тоже объясняю эти формулы. Формулы достаточно сложные, и я предоставлю более подробную информацию в сопроводительном видео к этой статье.

Формула из ячейки K9 еще не отображалась и будет завершена.

Чтобы определить или вернуть пустую ячейку, вы можете использовать двойные кавычки вместе, например: «».

Все версии — массив — столбец H.

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

Удерживая клавиши Ctrl и Shift, нажмите клавишу Enter. Таким образом формула будет заключена в квадратные скобки (фигурные скобки). Вы можете увидеть это в первой версии, указанной в Рисунок 03.

Эта формула массива сначала вычисляет первую функцию ЕСЛИ. Это сравнивает каждую ячейку в диапазоне A2: A13 с кодом проекта в ячейке G4. Если ячейка соответствует коду проекта, она переходит к следующей функции ЕСЛИ и просматривает соответствующую строку в диапазоне E2: 13 пустой ячейки.

Если он находит пустую ячейку, он возвращает соответствующую строку из D2: D13. Если любой из тестов имеет значение ЛОЖЬ, возвращается пустая ячейка.

Вместе функции ЕСЛИ предоставляют диапазон дат и пустые ячейки для функции МИН (которая возвращает минимальное значение) для вашего обзора. Функция MIN игнорирует пустые ячейки.

Остальные даты сравниваются, чтобы определить самую раннюю (самую низкую) дату. Этот тип вычислений с использованием обычных формул требует выполнения нескольких ячеек. Формула массива может выполнять все вычисления в одной ячейке.

Excel 2010 и более поздние версии — группировка — первый столбец

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

Функция СОВМЕСТНЫЙ ИТОГ аналогична функции ПРОМЕЖУТОЧНЫЙ ИТОГ. Вы указываете расчет, который нужно выполнить. Число «15» в начале определяет функцию МАЛЕНЬКАЯ. Функция МАЛЕНЬКИЙ — это гибкая версия функции МИН.

Функция MIN возвращает минимальное значение в диапазоне. Функция МАЛЕНЬКИЙ может найти наименьшее или второе наименьшее, указав номер позиции: 1 находит наименьшее, а 2 находит второе наименьшее.

Второй аргумент AGGREGATE имеет 6, и это указывает ему игнорировать ошибки.

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

Каждая ячейка в диапазоне может возвращать ИСТИНА или ЛОЖЬ. Когда мы умножаем два условия вместе, они преобразуют ИСТИНА в 1 и ЛОЖЬ в 0. И результат из A2 будет умножен на результат из E2 и так далее ниже диапазонов.

При двух условиях есть четыре возможности и два возможных результата, как показано на Рисунок 04.

Когда вы умножаете что-либо на ноль (FALSE), возвращается ноль, как вы можете видеть в Рисунок 04. Единственный раз, когда возвращается 1, — это когда оба условия ИСТИНА.

Мы разделили даты по результатам по этому делу. Любое несоответствующее условие будет нулевым. Деление на ноль возвращает ошибку.

Мы указали функции AGGREGATE игнорировать ошибки. Когда оба условия совпадают, дата будет разделена на 1, оставив ее без изменений. Остальные даты будут соответствовать обоим критериям. Он анализируется функцией МАЛЕНЬКИЙ, чтобы определить минимальную дату.

Число 1 в конце функции AGGREGATE предписывает функции SMALL возвращать наименьшее (самое старое) значение дат.

Функция ЕСЛИОШИБКА была добавлена ​​для обработки ошибки, возникающей при отсутствии условий.

Это происходит, когда для определенного кода проекта нет пустой ячейки.

Excel 2019 и более поздние версии — MINIFS — Предпочтительное решение — Столбец J.

Подобно тому, как СУММЕСЛИМН допускает условное группирование, МИНИМЕСЛИМН допускает вычисление условного минимума.

Первый диапазон, D2: D13, — это диапазон, в котором находится нижняя граница.

Затем вы связываете диапазон условий A2: A13 с условием G4. Вы можете добавить условия, добавив еще один диапазон условий, E2: E13, и еще одно условие, «». Все они разделены запятыми.

Поскольку эта функция была создана для удовлетворения наших требований, это рекомендуемое решение. К сожалению, он доступен только в версии Excel 2019 и подписки. Обратите внимание, что также есть функция MAXIFS.

Версия только для подписки — динамические массивы — столбец K.

Версия Excel по подписке доступна через Microsoft 365 (ранее Office 365). Эта версия регулярно обновляется. В 2020 году он получил обновление динамических массивов, о котором я рассказывал в трех отдельных статьях в мае, июне и июле 2020 года.

Динамические матрицы меняют способ вычисления формул. Для формул массива вам больше не нужно использовать Ctrl + Shift + Enter для их ввода. Формулы в ячейках H4 и K4 идентичны, за исключением скобок на обоих концах.

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

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

Предположим, что вместо поиска первой даты на основе кода проекта мы хотим найти первую дату на основе первой буквы кода учетной записи в столбце C. Различные префиксы могут быть связаны с разными категориями учетных записей. Рисунок 05 У него есть отчет. Формула входит в K9 в Рисунок 03. MINIFS не может справиться с подобными ситуациями.

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

Мы можем заменить условие кода проекта условием, которое извлекает первую букву из каждой ячейки в диапазоне C2: C13. Затем он сравнивается с буквой в ячейке J9, чтобы вернуть ИСТИНА или ЛОЖЬ для каждой ячейки в диапазоне.

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

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

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

В сопроводительном видео и файле Excel будут более подробно описаны эти методы.

Нил Блэквуд CPA ведет бухгалтерский учет A4, проводит обучение по Excel, вебинары и консультационные услуги. Вопросы можно отправлять на [email protected]