Transpose Range

Введение

Транспонирование диапазона означает его поворот таким образом, чтобы то, что было строкой, стало столбцом, а то, что было столбцом, стало строкой, фактически отражая данные относительно их главной диагонали. В Microsoft Excel функция рабочего листа TRANSPOSE выполняет эту операцию, а концептуальная справка задокументирована по адресу https://support.microsoft.com/en-us/excel/functions/transpose-function. Ту же идею можно применить программно к диапазону ячеек, что полезно во многих бизнес-сценариях и сценариях отчётности. Ниже приведены типичные случаи, когда транспонирование полезно.

  • Переориентация квартальных или годовых отчётов о продажах, в которых кварталы обычно идут поперёк страницы, а регионы — вдоль страницы, или наоборот.
  • Изменение ориентации осей в информационных панелях или диаграммах так, чтобы временной ряд шёл вдоль страницы, а не поперёк.
  • Изменение формы данных, импортированных из внешних систем, чтобы они соответствовали компоновке, ожидаемой шаблонами последующего анализа или отчётности. Чтобы сделать дальнейшее изложение статьи конкретным, во всех примерах используется следующая небольшая таблица продаж по регионам и кварталам. В демонстрационной рабочей книге эта таблица занимает диапазон A1:D5, при этом ячейка A1 оставлена пустой как левый верхний угол, в ячейках B1:D1 находятся заголовки регионов, а в ячейках A2:A5 — заголовки кварталов.
    Регион Европа Азия Северная Америка
    Кв. 1 21704714 8774099 12094215
    Кв. 2 17987034 12214447 10873099
    Кв. 3 19485029 14356879 15689543
    Кв. 4 22567894 15763492 17456723
    Далее в статье представлены три различных способа транспонирования этих данных с помощью Aspose.Cells for Node.js via C++, каждый из которых подходит для определённой версии Excel и случая использования.

Подход 1 — Транспонирование диапазона на месте (range.transpose)

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

Используемый API

range.transpose() — это метод экземпляра класса Aspose.Cells.Range. При его вызове диапазон переворачивается на месте путём замены его строк и столбцов, так что то, что было строкой, становится столбцом, а то, что было столбцом, становится строкой. Метод изменяет базовые ячейки напрямую, не записывая формулу.

Шаги

  1. Откройте исходную рабочую книгу с помощью LoadOptions, установленного в формат .xlsx, вызвав new Workbook(srcFile, new LoadOptions(LoadFormat.Xlsx)).
  2. Получите первый рабочий лист из рабочей книги с помощью workbook.getWorksheets().get(0).
  3. Получите доступ к коллекции ячеек рабочего листа через worksheet.getCells().
  4. Создайте исходный диапазон, охватывающий A1:D5, вызвав cells.createRange("A1:D5").
  5. Вызовите source.transpose(), чтобы повернуть диапазон на месте, поменяв местами строки и столбцы.
  6. Сохраните рабочую книгу с помощью workbook.save(outputFile). После транспонирования тот же самый опорный диапазон содержит повёрнутые данные. Первая строка читается как (пусто, Европа, Азия, Северная Америка), а первый столбец — как (пусто, Кв. 1, Кв. 2, Кв. 3, Кв. 4). Каждый исходный столбец продаж становится строкой в транспонированном диапазоне.
var srcFile = "source.xlsx";
var outputFile = "transposed.xlsx";
var workbook = new AsposeCells.Workbook(srcFile, new AsposeCells.LoadOptions(AsposeCells.LoadFormat.Xlsx));
var worksheet = workbook.getWorksheets().get(0);
var cells = worksheet.getCells();
var source = cells.createRange("A1:D5");
source.transpose();
workbook.save(outputFile);

Подход 2 — Транспонирование с помощью формулы динамического массива (Excel 365 / 2021)

Используйте этот подход, когда требуется сохранить формулу =TRANSPOSE(A1:D5) как действующую формулу в выходной рабочей книге, чтобы результат автоматически обновлялся при изменении исходных данных, и при этом целевой файл Excel будет открываться в Excel 365 / Excel 2021 или более поздней версии, где поддерживаются динамические массивы и оператор распределения (spill).

Используемый API

cell.setDynamicArrayFormula(string formula, FormulaParseOptions options, bool calculateValue) — это метод класса Aspose.Cells.Cell, который задаёт формулу ячейки как формулу динамического массива. Excel вычисляет формулу один раз и автоматически распределяет результат в окружающие ячейки. Третий параметр, если он установлен в true, указывает Aspose.Cells также вычислить результирующие значения во время записи.

Шаги

  1. Загрузите исходную рабочую книгу с помощью new Workbook(srcFile, new LoadOptions(LoadFormat.Xlsx)).
  2. Получите первый рабочий лист и доступ к его коллекции Cells.
  3. Поместите формулу динамического массива в ячейку A6, прямо под исходным диапазоном, вызвав cells.get("A6").setDynamicArrayFormula("=TRANSPOSE(A1:D5)", null, true).
  4. Аргумент null передаёт параметры FormulaParseOptions по умолчанию, а третий аргумент true указывает Aspose.Cells обрабатывать формулу как динамический массив и вычислить её так, чтобы распределённые значения были записаны в рабочую книгу.
  5. Сохраните рабочую книгу с помощью workbook.save(outputFile). Ячейка A6 содержит формулу =TRANSPOSE(A1:D5), и Excel автоматически распределяет результат в область A6:D10, блок размером 5 строк на 4 столбца, равный транспонированным данным.
const AsposeCells = require("aspose.cells");
const srcFile = "source.xlsx";
const outFile = "output_transpose_dynamic.xlsx";
const opts = new AsposeCells.LoadOptions(AsposeCells.LoadFormat.Xlsx);
const workbook = new AsposeCells.Workbook(srcFile, opts);
const worksheet = workbook.getWorksheets().get(0);
const cells = worksheet.getCells();
cells.get("A6").setDynamicArrayFormula("=TRANSPOSE(A1:D5)", new AsposeCells.FormulaParseOptions(), true);
workbook.save(outFile, AsposeCells.SaveFormat.Xlsx);

Подход 3 — Транспонирование с помощью классической формулы массива (CSE)

Используйте этот подход, когда требуется сохранить формулу TRANSPOSE в рабочей книге, но целевой файл Excel может быть открыт в более старых версиях Excel (до 2021, включая 2019, 2016, 2013 и т. д.), где динамическое распределение массивов не поддерживается. Классическая формула массива CSE (Ctrl+Shift+Enter) — это альтернативный вариант с обратной совместимостью, который могут вычислить все версии Excel.

Используемый API

cell.setArrayFormula(string arrayFormula, int nRows, int nColumns) — это метод класса Aspose.Cells.Cell, который назначает классическую формулу массива (CSE) ячейке-привязке и объявляет размеры результирующего массива. Aspose.Cells записывает маркер многоячеечной формулы массива, чтобы Excel вычислял формулу как единое выражение массива, заполняющее объявленный диапазон.

Шаги

  1. Загрузите исходную рабочую книгу тем же способом, что и в предыдущих подходах.
  2. Получите первый рабочий лист и доступ к его коллекции Cells.
  3. Вызовите cells.get("A6").setArrayFormula("=TRANSPOSE(A1:D5)", 4, 5). Второй аргумент 4 — это количество строк результирующего массива, а третий аргумент 5 — количество столбцов.
  4. Сохраните рабочую книгу с помощью workbook.save(outputFile). Ячейка A6 является ячейкой-привязкой формулы массива, и вычисленный массив охватывает 4 строки на 5 столбцов, начиная с A6, что соответствует транспонированным размерам исходного диапазона A1:D5. Excel записывает единый маркер формулы массива по всему результирующему диапазону, чтобы старые версии Excel корректно его вычисляли.
const AsposeCells = require("aspose.cells");
// Загружаем исходную рабочую книгу с параметрами загрузки xlsx
const srcFile = "source.xlsx";
const workbook = new AsposeCells.Workbook(srcFile, new AsposeCells.LoadOptions(AsposeCells.LoadFormat.Xlsx));
// Получаем доступ к первому листу и его коллекции Cells
const worksheet = workbook.getWorksheets().get(0);
const cells = worksheet.getCells();
// Устанавливаем классическую CSE формулу массива в ячейку A6.
// Формула =ТРАНСП(A1:D5) транспонирует исходный диапазон 5 строк x 4 столбца
// в массив 4 строки x 5 столбцов. Второй аргумент (4) — это количество строк
// а третий аргумент (5) — количество столбцов результирующего массива.
// Aspose.Cells записывает маркер CSE-формулы массива, чтобы Excel вычислял её как
// единую многоячеечную формулу массива, совместимую со старыми версиями Excel
// (2019, 2016, 2013 и т.д.), которые не поддерживают динамическое распыление массивов.
cells.get("A6").setArrayFormula("=TRANSPOSE(A1:D5)", 4, 5);
// Сохраняем рабочую книгу, чтобы маркер формулы массива был сохранён
workbook.save("output.xlsx");

Сравнение — Когда использовать каждый подход

Подход API / Метод Версия Excel Сохраняется ли исходная формула? Выходной диапазон
Подход 1 — Транспонирование на месте range.transpose() Все версии Excel Нет (только значения) Тот же опорный диапазон, 5×4
Подход 2 — Формула динамического массива cell.setDynamicArrayFormula Excel 365 / 2021+ Да (динамическое распределение) Распределяется из ячейки-привязки
Подход 3 — Классическая формула массива (CSE) cell.setArrayFormula Все версии Excel Да (многоячеечная формула массива) Явный размер, 4×5
Используйте Подход 1, когда нужна быстрая кросс-версионная трансформация и требуется лишь записать транспонированные значения в файл. Используйте Подход 2, когда гарантирован современный Excel и нужно, чтобы формула оставалась активной и обновлялась при изменении исходных данных. Используйте Подход 3, когда требуется максимальная совместимость с сохранённой формулой во всех версиях Excel, включая старые выпуски, которые не поддерживают динамические массивы.

Связанные статьи

Создавать рабочие книги и рабочие листы с именами диапазонов с помощью Node.js через C++ Автоматическое заполнение диапазона файла Excel с помощью Node.js через C++