Фильтрация сводных таблиц по меткам или значениям

Then the rest…

Введение

Сводные таблицы являются мощными аналитическими инструментами, однако необработанные сводки часто содержат значительно больше информации, чем требуется представить. Фильтрация — это основной механизм сужения сводной таблицы до строк, столбцов или значений, которые важны для конкретного отчёта. Aspose.Cells for Node.js via C++ воспроизводит возможности фильтрации, доступные в Microsoft Excel, предоставляя их программно, чтобы формирование отчётов могло быть полностью автоматизировано.

В данной статье рассмотрены следующие стратегии фильтрации:

  1. Фильтр по меткам — фильтрует элементы полей строк или столбцов на основе их текстовых меток.
  2. Фильтр по датам — фильтрует поля строк или столбцов, содержащие только значения даты-времени (или пустые значения).
  3. Фильтр по значениям — фильтрует элементы на основе агрегированных значений поля данных.
  4. Фильтр топ-10 — показывает только N верхних или нижних элементов, ранжированных по полю значений.
  5. Скрытие / отображение элементов сводной таблицы — ручное управление видимостью каждого отдельного элемента в поле.

Каждый подход использует отдельный метод класса PivotField или свойство класса PivotItem. После применения любого фильтра необходимо вызвать refreshData() и calculateData() на сводной таблице, чтобы кэшированные данные и вычисленные значения отражали новое состояние фильтра.

Фильтр по меткам

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

Aspose.Cells предоставляет фильтрацию по меткам через метод PivotField.filterByLabel(PivotFilterType, string). Перечисление PivotFilterType включает такие значения, как CaptionBeginsWith, CaptionContains, CaptionEndsWith, CaptionDoesNotContain, CaptionIsNotBlank, CaptionIsBlank и другие. Второй аргумент задаёт строку метки, используемую для сравнения.

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

let fileName = "sample.xlsx";
let prefix = "B";

// Загрузить существующую книгу, содержащую сводную таблицу
let workbook = new AsposeCells.Workbook(fileName);

// Получить доступ к рабочему листу по индексу (первый рабочий лист)
let worksheet = workbook.getWorksheets().get(0);

// Получить доступ к сводной таблице по индексу
let pivotTable = worksheet.getPivotTables().get(0);

// Извлечь первое строковое поле PivotField
let rowField = pivotTable.getRowFields().get(0);

// Применить фильтр по подписи — отображать только элементы строк, подписи которых начинаются с указанного префикса
rowField.filterByLabel(AsposeCells.PivotFilterType.CaptionBeginsWith, prefix, "");

// Обновить и пересчитать данные сводной таблицы, чтобы фильтр вступил в силу
pivotTable.getPivotCache().refresh();

// Сохранить книгу обратно на диск
workbook.save(fileName);

Фильтр по датам

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

Aspose.Cells предоставляет фильтрацию по датам через метод PivotField.filterByDate(PivotFilterType, params DateTime[] values). Перечисление PivotFilterType содержит специализированные значения для дат, такие как Today, Yesterday, LastWeek, ThisWeek, NextWeek, LastMonth, ThisMonth, NextMonth, LastQuarter, ThisQuarter, NextQuarter, LastYear, ThisYear, NextYear и Between. В зависимости от выбранного типа фильтра передаётся одно или два значения DateTime (для Between передаются начальная и конечная даты).

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

const AsposeCells = require("aspose.cells");
const fs = require("fs");

const inputPath = "sample.xlsx";
const outputPath = "output_filtered.xlsx";

if (!fs.existsSync(inputPath))
{
    throw new Error("Source workbook not found: " + inputPath);
}

// Загрузить существующую книгу, содержащую сводную таблицу
const workbook = new AsposeCells.Workbook(inputPath);

// Получить доступ к листу, содержащему сводную таблицу (по индексу)
const worksheet = workbook.getWorksheets().get(0);

// Получить доступ к сводной таблице по индексу
const pivotTable = worksheet.getPivotTables().get(0);

// Получить поле даты PivotField из области строк
// (Фильтр по дате работает только когда область строк/столбцов содержит только ячейки с датой-временем или пустые)
const dateField = pivotTable.getRowFields().get(0);

// Определить критерий даты для фильтра Between
const startDate = new Date(2020, 0, 1);
const endDate = new Date(2020, 11, 31);

// Применить фильтр по дате к полю сводной таблицы
dateField.filterByDate(AsposeCells.PivotFilterType.DateBetween, startDate, endDate);

// Обновить и пересчитать сводную таблицу, чтобы фильтр вступил в силу
pivotTable.getPivotCache().refresh();

// Сохранить книгу
workbook.save(outputPath);

Фильтр по значениям

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

Aspose.Cells предоставляет фильтрацию по значениям через метод PivotField.filterByValue(PivotField valueField, PivotFilterType filterType, params object[] values). Параметр filterType использует значения, такие как ValueGreaterThan, ValueLessThan, ValueBetween, ValueEqual, ValueNotEqual, ValueGreaterThanOrEqual и ValueLessThanOrEqual. Параметр valueField указывает, какое поле данных должно оцениваться, а последний аргумент (или аргументы) задаёт пороговое значение (или значения).

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

let dataFieldIndex = -1;
for (let i = 0; i < pivotTable.getDataFields().getCount(); i++) {
    if (pivotTable.getDataFields().get(i) === dataField) {
        dataFieldIndex = i;
        break;
    }
}

if (dataFieldIndex >= 0) {
    rowField.filterByValue(dataFieldIndex, AsposeCells.PivotFilterType.ValueGreaterThan, 5000, Number.MAX_VALUE);
}

pivotTable.getPivotCache().refresh();

workbook.save("output.xlsx");

Фильтр топ-10

Фильтр топ-10 представляет собой специализированную форму фильтра по значениям, которая оставляет только N элементов с наибольшими или наименьшими значениями, исходя из выбранного поля значений. Он обычно используется для отчётов с ранжированием, таких как «10 лучших продуктов по выручке» или «5 худших регионов по количеству продаж».

Aspose.Cells предоставляет фильтрацию топ-10 через метод PivotField.filterTop10(int itemCount, bool isTop, PivotField valueField, PivotFilterType filterType). Параметр itemCount определяет, сколько элементов нужно оставить, isTop указывает, следует ли сохранить верхние элементы (true) или нижние (false), valueField ссылается на поле данных, используемое для ранжирования, а filterType управляет тем, как вычисляется значение (как правило, Sum, но также Count и Percent).

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

const AsposeCells = require("aspose.cells");

// Загружаем существующую книгу, содержащую сводную таблицу
const inputPath = "input.xlsx";
const outputPath = "output.xlsx";
const workbook = new AsposeCells.Workbook(inputPath);

// Получаем доступ к листу, содержащему сводную таблицу (индекс 0)
const worksheet = workbook.getWorksheets().get(0);

// Получаем доступ к сводной таблице по индексу
const pivotTable = worksheet.getPivotTables().get(0);

// Проверяем, что в области данных есть хотя бы одно поле значений (PivotField)
if (pivotTable.getDataFields().getCount() === 0) {
    throw new Error("Pivot table has no value (data) PivotField.");
}
const valueField = pivotTable.getDataFields().get(0);

// Получаем целевое поле строки (PivotField), к которому применяем фильтр Top 10
const rowField = pivotTable.getRowFields().get(0);

// Первое (и единственное) поле данных находится по индексу 0; Top 10 ранжирует по нему.
const valueFieldIndex = 0;

// Применяем фильтр Top 10 к полю строки:
//   - itemCount   = 10
//   - filterType  = PivotFilterType.Sum
//   - isTop       = true (верхние N; false — нижние N)
//   - valueFieldIndex = индекс поля данных, используемого для ранжирования элементов
rowField.filterTop10(10, AsposeCells.PivotFilterType.Sum, true, valueFieldIndex);

// Обновляем данные сводной таблицы и пересчитываем её, чтобы фильтр вступил в силу
pivotTable.getPivotTableCache().refresh();

// Сохраняем книгу
workbook.save(outputPath);

Фильтрация путём скрытия или отображения элементов сводной таблицы

В дополнение к структурированным API-интерфейсам фильтрации Aspose.Cells позволяет напрямую управлять видимостью каждого отдельного элемента сводной таблицы. Перебирая коллекцию PivotItems поля PivotField и переключая свойство IsHidden, можно выборочно скрывать определённые элементы без применения фильтра на основе формулы. Установка IsHidden = true скрывает элемент из сводной таблицы; установка IsHidden = false снова делает его видимым.

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

AsposeCells = require("aspose.cells");

// Загружаем существующую рабочую книгу, содержащую сводную таблицу
const workbook = new AsposeCells.Workbook("pivot_table_sample.xlsx");

// Получаем доступ к первому листу, который содержит сводную таблицу
const sheet = workbook.getWorksheets().get(0);

// Получаем доступ к сводной таблице по индексу (первая сводная таблица на листе)
const pivotTable = sheet.getPivotTables().get(0);

// Получаем целевое поле сводной таблицы (первое поле метки строки, в котором мы будем скрывать/отображать элементы)
const pivotField = pivotTable.getRowFields().get(0);

// Перебираем коллекцию PivotItems выбранного PivotField
const itemCount = pivotField.getPivotItems().getCount();
for (let i = 0; i < itemCount; i++)
{
    const item = pivotField.getPivotItems().get(i);

    // Скрываем элементы сводной таблицы, соответствующие определённому имени/критерию
    if (item.getName() == "Item1" || item.getName() == "Item2")
    {
        item.setIsHidden(true);
    }

    // Демонстрируем отображение: повторно показываем ранее скрытый элемент сводной таблицы
    if (item.getName() == "Item3")
    {
        item.setIsHidden(false);
    }
}

// Обновляем и пересчитываем сводную таблицу, чтобы изменения вступили в силу
pivotTable.getPivotCache().refreshData();

// Сохраняем рабочую книгу — скрытые элементы остаются в исходных данных,
// но исключаются из отображаемого вывода сводной таблицы
workbook.save("output_pivot_filtered.xlsx");

Резюме

Aspose.Cells for Node.js via C++ предоставляет полный набор возможностей фильтрации сводных таблиц, соответствующий тем, что доступны в Microsoft Excel. Фильтры по меткам, датам и значениям охватывают наиболее распространённые аналитические сценарии, тогда как фильтр топ-10 обрабатывает отчёты с ранжированием. Когда правило фильтрации нерегулярно, свойство PivotItem.IsHidden предлагает гибкий резервный вариант на уровне элементов. Комбинирование этих стратегий, например применение фильтра по меткам с последующим скрытием определённых элементов, позволяет формировать точно нацеленные отчёты сводных таблиц полностью из кода.

Указание абсолютного положения элемента сводной таблицы