Установка динамических массивных формул с помощью JavaScript через C++

Что такое массивная формула Excel

В Excel массивная формула - это специальный тип формулы, который позволяет выполнять вычисления над массивами данных, а не отдельными ячейками. Массивные формулы могут использоваться для выполнения сложных вычислений, манипулирования данными и эффективного решения конкретных проблем. Они вводятся по-другому, чем обычные формулы, и часто требуют использования Ctrl + Shift + Enter.

Вот несколько ключевых моментов о массивных формулах в Excel:

Синтаксис:

Массивные формулы записываются как обычные формулы, но включают операции над массивами значений. Они заключены в фигурные скобки { }, чтобы указать, что это массивные формулы. Однако вы сами не вводите эти фигурные скобки; Excel автоматически добавляет их, когда вы вводите формулу правильно.
Ввод массивных формул:

Чтобы ввести массивную формулу, введите её в строку формулы. Вместо нажатия Enter для завершения, нажмите Ctrl + Shift + Enter. Это сообщает Excel, что это массивная формула. Когда введена правильно, Excel отображает формулу в фигурных скобках в строке формул, указывая, что это массивная формула.
Случаи использования:

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

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

Массивные формулы могут быть сложнее понять и устранить неисправности, чем обычные формулы. Они могут замедлить производительность рабочего листа, особенно если используются обширно или с большими наборами данных.
Примеры:

Суммирование значений в диапазоне: {=SUM(A1:A5*B1:B5)}

Поиск максимального значения в диапазоне: {=MAX(A1:A5+B1:B5)}

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

Что такое динамическая массивная формула Excel

Динамические массивные формулы - новая функция, введенная в Excel 365 и Excel 2021. Они позволяют работать с массивами данных более плавно и эффективно по сравнению с традиционными массивными формулами. Динамические массивные формулы автоматически распространяют результаты на соседние ячейки, устраняя необходимость в Ctrl + Shift + Enter и облегчая манипуляцию данными.

Основные моменты динамических массивных формул в Excel:

Автоматическое распространение:

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

Динамические массивные формулы вводятся в одной ячейке, и Excel автоматически заполняет соседние ячейки результатами. Это упрощает управление и понимание формул, поскольку вам нужно ввести формулу только один раз.
Новые функции:

Динамические массивные формулы представляют новые функции, которые могут обрабатывать массивы нативно, такие как FILTER, SORT, UNIQUE, SEQUENCE, SORTBY и RANDARRAY. Эти функции могут возвращать несколько значений или напрямую манипулировать массивами, упрощая сложные вычисления.
Гибкая обработка диапазонов:

Динамические массивные формулы автоматически изменяют размер затопляемого диапазона динамически в зависимости от размера входных данных или выполняемых вычислений. Эта гибкость позволяет более эффективно использовать рабочее пространство и упрощает создание формул.
Повышенная производительность:

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

Динамические массивные формулы доступны в Excel 365 и Excel 2021. В старых версиях Excel они могут не поддерживаться.
Примеры динамических массивных формул:

FILTER: Возвращает массив значений, которые соответствуют указанным критериям.

SORT: Сортирует значения в диапазоне или массиве.

УНИКАЛЬНЫЙ: Возвращает уникальные значения из списка или диапазона.

ПОСЛЕДОВАТЕЛЬНОСТЬ: Генерирует последовательность чисел или дат.

RANDARRAY: Генерирует массив случайных чисел.

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

В чем разница между массивными формулами и динамическими массивными формулами в Excel

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

Черты массивных формул

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

Черты динамическиx массивных формул

Динамические массивные формулы - новая функция, введенная в Excel 365 (подписка Office 365) и Excel 2021.
Они автоматически выводят результаты в соседние ячейки в зависимости от размера входных данных или вычислений формулы.
Динамические массивные формулы не требуют нажатия Ctrl + Shift + Enter; достаточно ввести формулу в одну ячейку, и Excel автоматически заполнит соседние ячейки результатами.
Эти формулы могут возвращать несколько результатов (диапазон ячеек) напрямую без необходимости использования массивной формулы или Ctrl + Shift + Enter.
У них есть новые функции, такие как FILTER, SORT, UNIQUE и другие, которые могут работать с массивами нативно и возвращать результаты в формате динамического массива.
В заключение, динамические массивные формулы являются более современным и удобным способом работы с массивами в Excel, обеспечивая автоматическое разливание результатов и упрощая процесс работы с массивами по сравнению с традиционными массивными формулами. Однако они доступны только в более новых версиях Excel, поддерживающих динамические массивы.

Как настроить и вычислить динамические массивные формулы в Excel

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

Вот пошаговое руководство по настройке динамических формул массива:

Выберите ячейку:

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

Введите динамическую формулу массива в строке формул выбранной ячейки. Используйте одну из доступных функций динамического массива в Excel 365 и Excel 2021, таких как FILTER, SORT, UNIQUE, SEQUENCE, SORTBY, или RANDARRAY.

Например, вы можете использовать функцию FILTER для фильтрации списка данных на основе конкретных критериев: =FILTER(A2:C15,(A2:A15=F4)*(C2:C15=G4),"").
Нажмите Enter:

После ввода формулы просто нажмите Enter на клавиатуре. В отличие от традиционных массивных формул, вам не нужно нажимать Ctrl + Shift + Enter.
Обратите внимание на расширяемый диапазон:

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

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

Если необходимо изменить динамическую массивную формулу, просто отредактируйте ее в исходной ячейке, где она была введена. После редактирования нажмите Enter снова для подтверждения изменений. Excel автоматически обновит расширяемый диапазон при необходимости.
Очистка расширяемого диапазона:

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

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

Как настроить и обновить динамические формулы массива с помощью Aspose.Cells

Пожалуйста, смотрите следующий пример кода, который загружает образец файла Excel, содержащий некоторые фиктивные данные. После загрузки файла вызовите функцию Cell.dynamicArrayFormula(string, FormulaParseOptions, boolean) для установки динамической массивной формулы и Workbook.refreshDynamicArrayFormulas(boolean) для обновления динамических массивных формул перед вызовом вычисления формул, затем сохраните книгу как выходной файл Excel.

<!DOCTYPE html>
<html>
    <head>
        <title>Aspose.Cells Dynamic Array Formula Example</title>
    </head>
    <body>
        <h1>Dynamic Array Formula Example</h1>
        <input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
        <button id="runExample">Run Example</button>
        <a id="downloadLink" style="display: none;">Download Result</a>
        <div id="result"></div>
    </body>

    <script src="aspose.cells.js.min.js"></script>
    <script type="text/javascript">
        const { Workbook, SaveFormat, FormulaParseOptions } = AsposeCells;

        AsposeCells.onReady({
            license: "/lic/aspose.cells.enc",
            fontPath: "/fonts/",
            fontList: [
                "arial.ttf",
                "NotoSansSC-Regular.ttf"
            ]
        }).then(() => {
            console.log("Aspose.Cells initialized");
        });

        document.getElementById('runExample').addEventListener('click', async () => {
            const fileInput = document.getElementById('fileInput');
            if (!fileInput.files.length) {
                document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
                return;
            }

            const file = fileInput.files[0];
            const arrayBuffer = await file.arrayBuffer();

            // Instantiating a Workbook object from the uploaded file
            const wb = new Workbook(new Uint8Array(arrayBuffer));

            // Get the first worksheet
            const ws = wb.worksheets.get(0);

            // Getting the F16 cell
            const f16 = ws.cells.get("F16");

            // Set dynamic array formula (converted from setDynamicArrayFormula to property assignment)
            // Assigning an array containing the formula string, parse options object, and the boolean flag
            f16.dynamicArrayFormula = ["=FILTER(A2:C15,(A2:A15=F4)*(C2:C15=25),\"\")", new FormulaParseOptions(), false];

            // Refresh the dynamic array formulas
            wb.refreshDynamicArrayFormulas(true);

            // Calculate formulas
            wb.calculateFormula();

            // Saving the modified Excel file and preparing download link
            const outputData = wb.save(SaveFormat.Xlsx);
            const blob = new Blob([outputData]);
            const downloadLink = document.getElementById('downloadLink');
            downloadLink.href = URL.createObjectURL(blob);
            downloadLink.download = 'out.xlsx';
            downloadLink.style.display = 'block';
            downloadLink.textContent = 'Download Modified Excel File';

            document.getElementById('result').innerHTML = '<p style="color: green;">Operation completed successfully! Click the download link to get the modified file.</p>';
        });
    </script>
</html>

Снимок вывода:

Задайте максимальное количество строк для разделяемой формулы с помощью JavaScript через C++ Предпосылки и зависимые ячейки с помощью JavaScript через C++