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

Введение

Сводные таблицы — это мощный аналитический инструмент, однако необработанные сводки зачастую содержат значительно больше информации, чем требуется представить. Фильтрация является основным механизмом сужения сводной таблицы до строк, столбцов или значений, важных для конкретного отчёта. Aspose.Cells for Node.js via Java дублирует возможности фильтрации, доступные в 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 передаются начальная и конечная даты). В следующем примере загружается рабочая книга со сводной таблицей, в области строк которой находится поле даты, применяется фильтр по дате, ограничивающий видимые элементы определённым диапазоном дат, сводная таблица обновляется, и рабочая книга сохраняется.

let inputPath = "sample.xlsx";
let outputPath = "output_filtered.xlsx";
if (!fs.existsSync(inputPath))
{
    throw new Error("Source workbook not found. Path: " + inputPath);
}
// Загружаем существующую книгу, содержащую сводную таблицу
var workbook = new AsposeCells.Workbook(inputPath);
// Получаем доступ к листу, содержащему сводную таблицу (по индексу)
var worksheet = workbook.getWorksheets().get(0);
// Получаем доступ к сводной таблице по индексу
var pivotTable = worksheet.getPivotTables().get(0);
// Извлекаем поле даты из области строк
// (Фильтр по дате работает только тогда, когда область строк/столбцов содержит только ячейки с датой/временем или пустые ячейки)
let dateField = pivotTable.getRowFields().get(0);
// Определяем критерий даты для фильтра "Между"
let startDate = new Date(2020, 0, 1);
let 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 задаёт, какое поле данных должно оцениваться, а последний аргумент (или аргументы) указывает пороговое значение (или значения). В следующем примере загружается рабочая книга со сводной таблицей, применяется фильтр по значениям, оставляющий только те элементы, агрегированные продажи которых превышают числовой порог, сводная таблица обновляется, и рабочая книга сохраняется.

var workbook = new AsposeCells.Workbook("sample.xlsx");
var worksheet = workbook.getWorksheets().get(0);
var pivotTable = worksheet.getPivotTables().get(0);
var rowField = pivotTable.getRowFields().get(0);
var dataField = pivotTable.getDataFields().get(0);
// Находим индекс поля данных вручную, так как PivotFieldCollection не имеет IndexOf
var dataFieldIndex = -1;
for (var i = 0; i < pivotTable.getDataFields().getCount(); i++)
{
    if (pivotTable.getDataFields().get(i) == dataField)
    {
        dataFieldIndex = i;
        break;
    }
}
if (dataFieldIndex >= 0)
{
    rowField.filterByValue(dataFieldIndex, AsposeCells.Pivot.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 наибольших элементов по сумме продаж, сводная таблица обновляется, и рабочая книга сохраняется.

let inputPath = "input.xlsx";
let outputPath = "output.xlsx";
let workbook = new AsposeCells.Workbook(inputPath);
// Доступ к рабочему листу, содержащему сводную таблицу (индекс 0)
let worksheet = workbook.getWorksheets().get(0);
// Доступ к сводной таблице по индексу
let pivotTable = worksheet.getPivotTables().get(0);
// Проверяем, что в области данных есть хотя бы одно поле значений (PivotField)
if (pivotTable.getDataFields().getCount() == 0)
{
    throw new Error("Сводная таблица не содержит поле значений (данных).");
}
let valueField = pivotTable.getDataFields().get(0);
// Получаем целевое поле строки (поле, к которому хотим применить фильтр Топ-10)
let rowField = pivotTable.getRowFields().get(0);
// Первое (и единственное) поле данных находится по индексу 0; Топ-10 ранжирует по нему.
let valueFieldIndex = 0;
// Применяем фильтр Топ-10 к полю строки:
//   - itemCount   = 10
//   - filterType  = PivotFilterType.Sum
//   - isTop       = true (верхние N; false означало бы нижние N)
//   - valueFieldIndex = индекс поля данных, используемого для ранжирования элементов
rowField.filterTop10(10, AsposeCells.PivotFilterType.Sum, true, valueFieldIndex);
// Обновляем данные сводной таблицы и пересчитываем её, чтобы фильтр вступил в силу
pivotTable.getPivotCache().refresh();
// Сохраняем книгу
workbook.save(outputPath);

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

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

let workbook = new AsposeCells.Workbook("pivot_table_sample.xlsx");
// Получаем доступ к первому листу, содержащему сводную таблицу
let sheet = workbook.getWorksheets().get(0);
// Получаем доступ к сводной таблице по индексу (первая сводная таблица на листе)
let pivotTable = sheet.getPivotTables().get(0);
// Извлекаем целевое поле сводной таблицы (первое поле метки строки, в котором будем скрывать/отображать элементы)
let pivotField = pivotTable.getRowFields().get(0);
// Перебираем коллекцию PivotItems выбранного PivotField
let itemCount = pivotField.getPivotItems().getCount();
for (let i = 0; i < itemCount; i++) {
    let 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 Java предоставляет полный набор возможностей фильтрации сводных таблиц, аналогичный тем, что есть в Microsoft Excel. Фильтры по меткам, датам и значениям охватывают наиболее распространённые аналитические сценарии, а топ-10 фильтр обрабатывает отчёты ранжирования. Когда правило фильтрации нерегулярно, свойство PivotItem.IsHidden предлагает гибкий резервный вариант на уровне элементов. Комбинирование этих стратегий — например, применение фильтра по меткам с последующим сокрытием определённых элементов — позволяет создавать точные целевые отчёты сводных таблиц полностью из кода.