Формулы рабочего листа диаграммы
О формуле рабочего листа диаграммы в презентации
Рабочий лист диаграммы в презентации является источником данных для диаграммы. Рабочий лист диаграммы содержит данные, которые представлены на диаграмме в графическом виде. Когда вы создаете диаграмму в PowerPoint, связанный с этой диаграммой рабочий лист автоматически создается. Рабочий лист диаграммы создается для всех типов диаграмм: линейная диаграмма, столбчатая диаграмма, диаграмма солнечного всплеска, круговая диаграмма и т.д. Чтобы увидеть рабочий лист диаграммы в PowerPoint, вы должны дважды щелкнуть на диаграмме:
Рабочий лист диаграммы содержит названия элементов диаграммы (Название категории: Категория1, Название серии) и таблицу с числовыми данными, соответствующими этим категориям и сериям. По умолчанию, когда вы создаете новую диаграмму, данные рабочего листа диаграммы устанавливаются с помощью данных по умолчанию. Затем вы можете вручную изменить данные рабочего листа в таблице.
Обычно диаграмма представляет собой сложные данные (например, финансовые аналитики, научные аналитики), имеющие ячейки, которые рассчитываются на основе значений в других ячейках или из других динамических данных. Ввод значения ячейки вручную и жесткое кодирование его в ячейку усложняет его изменение в будущем. Если вы измените значение определенной ячейки, все ячейки, зависимые от нее, также потребуют обновления. Более того, данные таблицы могут зависеть от данных из других таблиц, создавая сложную структуру данных презентации с необходимостью обновления в легкой и гибкой форме.
Формула рабочего листа диаграммы в презентации представляет собой выражение для автоматического вычисления и обновления данных рабочего листа диаграммы. Формула рабочего листа определяет логику вычисления данных для определенной ячейки или набора ячеек. Формула рабочего листа – это математическая формула или логическая формула, которая использует: ссылки на ячейки, математические функции, логические операторы, арифметические операторы, функции преобразования, строковые константы и т.д. Определение формулы записывается в ячейку, и эта ячейка не содержит простое значение. Формула рабочего листа вычисляет значение и возвращает его, после чего это значение присваивается ячейке. Формулы рабочего листа диаграмм в презентациях на самом деле аналогичны формулам Excel, и для их реализации поддерживаются те же функции, операторы и константы по умолчанию.
В Aspose.Slides рабочий лист диаграммы представлен с помощью метода Chart.getChartData.getChartDataWorkbook типа IChartDataWorkbook. Формула рабочего листа может быть присвоена и изменена с помощью метода IChartDataCell.setFormula. Для формул в Aspose.Slides поддерживаются следующие функции:
- Логические константы
- Числовые константы
- Строковые константы
- Константы ошибок
- Арифметические операторы
- Операторы сравнения
- Ссылки на ячейки в стиле A1
- Ссылки на ячейки в стиле R1C1
- Предопределенные функции
Обычно электронные таблицы хранят последние рассчитанные значения формул. Если после загрузки презентации данные диаграммы не изменились, метод IChartDataCell.getValue вернет эти значения при чтении. Но если данные рабочего листа были изменены, при чтении свойства ChartDataCell.Value будет выброшено исключение CellUnsupportedDataException для неподдерживаемых формул. Это происходит потому, что при успешном анализе формул определяются зависимости ячеек и корректность последних значений. Но если формулу невозможно разобрать, корректность значения ячейки не может быть гарантирована.
Добавление формулы рабочего листа диаграммы в презентацию
Сначала добавьте диаграмму на первый слайд новой презентации с помощью IShapeCollection.getShapes.addChart. Рабочий лист диаграммы автоматически создается и может быть доступен с помощью метода Chart.getChartData.getChartDataWorkbook:
Presentation pres = new Presentation();
try {
IChart chart = pres.getSlides().get_Item(0).getShapes().addChart(ChartType.ClusteredColumn, 150, 150, 500, 300);
IChartDataWorkbook workbook = chart.getChartData().getChartDataWorkbook();
// ...
} finally {
if (pres != null) pres.dispose();
}
Давайте запишем некоторые значения в ячейки с помощью свойства IChartDataCell.setValue, которое имеет тип Object, что означает, что вы можете установить любое значение для этого свойства:
workbook.getCell(0, "F2").setValue(-2.5);
workbook.getCell(0, "G3").setValue(6.3);
workbook.getCell(0, "H4").setValue(3);
Теперь, чтобы записать формулу в ячейку, вы можете использовать метод IChartDataCell.setFormula:
Примечание: метод IChartDataCell.setFormula используется для установки ссылок на ячейки в стиле A1.
Чтобы установить ссылку ячейки R1C1Formula, вы можете использовать метод IChartDataCell.setR1C1Formula:
Затем, если вы попытаетесь прочитать значения из ячеек B2 и C2, они будут вычислены:
Object value1 = cell1.getValue(); // 7.8
Object value2 = cell2.getValue(); // 2.1
Логические Константы
Вы можете использовать логические константы, такие как ЛОЖЬ и ИСТИНА в формулах ячеек:
workbook.getCell(0, "A2").setValue(false);
IChartDataCell cell = workbook.getCell(0, "B2");
cell.setFormula("A2 = TRUE");
Object value = cell.getValue(); // значение содержит логическое "false"
Числовые Константы
Числа могут использоваться в обычной или научной нотации для создания формулы рабочего листа диаграммы:
workbook.getCell(0, "A2").setFormula("1 + 0.5");
workbook.getCell(0, "B2").setFormula(".3 * 1E-2");
Строковые Константы
Строковая (или буквальная) константа - это определенное значение, которое используется как есть и не меняется. Строковые константы могут быть: даты, тексты, числа и т.д.:
workbook.getCell(0, "A2").setFormula("\"abc\"");
workbook.getCell(0, "B2").setFormula("\"2/3/2020 12:00\"");
Константы Ошибок
Иногда невозможно рассчитать результат по формуле. В этом случае в ячейке отображается код ошибки вместо ее значения. Каждый тип ошибки имеет конкретный код:
- #DIV/0! - формула пытается делить на ноль.
- #GETTING_DATA - может отображаться в ячейке, пока ее значение все еще вычисляется.
- #N/A - информация отсутствует или недоступна. Некоторые причины могут быть: ячейки, используемые в формуле, пусты, лишний пробел, ошибка в написании и т.д.
- #NAME? - определенная ячейка или другие объекты формулы не могут быть найдены по своему имени.
- #NULL! - может появиться, когда есть ошибка в формуле, такая как: (,) или пробел вместо двоеточия (:).
- #NUM! - численное значение в формуле может быть недопустимым, слишком длинным или слишком малым и т.д.
- #REF! - недопустимая ссылка на ячейку.
- #VALUE! - неожиданный тип значения. Например, строковое значение, установленное в числовую ячейку.
IChartDataCell cell = workbook.getCell(0, "A2");
cell.setFormula("2 / 0");
Object value = cell.getValue(); // значение содержит строку "#DIV/0!"
Арифметические Операторы
Вы можете использовать все арифметические операторы в формулах рабочего листа диаграммы:
| Оператор | Значение | Пример |
|---|---|---|
| + (плюс) | Сложение или унарный плюс | 2 + 3 |
| - (минус) | Вычитание или отрицание | 2 - 3 -3 |
| * (звездочка) | Умножение | 2 * 3 |
| / (косая черта) | Деление | 2 / 3 |
| % (процент) | Процент | 30% |
| ^ (карыч) | Возведение в степень | 2 ^ 3 |
Примечание: Чтобы изменить порядок вычислений, заключите в скобки часть формулы, которую нужно вычислить сначала.
Операторы Сравнения
Вы можете сравнивать значения ячеек с помощью операторов сравнения. Когда два значения сравниваются с использованием этих операторов, результатом является логическое значение, либо ИСТИНА, либо ЛОЖЬ:
| Оператор | Значение | Значение |
|---|---|---|
| = (знак равно) | Равно | A2 = 3 |
| <> (знак не равно) | Не равно | A2 <> 3 |
| > (знак больше) | Больше | A2 > 3 |
| >= (знак больше или равно) | Больше или равно | A2 >= 3 |
| < (знак меньше) | Меньше | A2 < 3 |
| <= (знак меньше или равно) | Меньше или равно | A2 <= 3 |
Ссылки на Ячейки в Стиле A1
Ссылки на ячейки в стиле A1 используются для рабочих листов, где колонка имеет буквенный идентификатор (например, “A"), а строка имеет числовой идентификатор (например, “1"). Ссылки на ячейки в стиле A1 можно использовать следующим образом:
| Ссылка на ячейку | Пример | ||
|---|---|---|---|
| Абсолютная | Относительная | Смешанная | |
| Ячейка | $A$2 | A2 | A$2 $A2 |
| Строка | $2:$2 | 2:2 | - |
| Колонка | $A:$A | A:A | - |
| Диапазон | $A$2:$C$4 | A2:C4 | $A$2:C4 A$2:$C4 |
Вот пример, как использовать ссылку на ячейку в стиле A1 в формуле:
workbook.getCell(0, "A2").setFormula("C3 + SUM(F2:H5)");
Ссылки на Ячейки в Стиле R1C1
Ссылки на ячейки в стиле R1C1 используются для рабочих листов, где и строка, и колонка имеют числовой идентификатор. Ссылки на ячейки в стиле R1C1 можно использовать следующим образом:
| Ссылка на ячейку | Пример | ||
|---|---|---|---|
| Абсолютная | Относительная | Смешанная | |
| Ячейка | R2C3 | R[2]C[3] | R2C[3] R[2]C3 |
| Строка | R2 | R[2] | - |
| Колонка | C3 | C[3] | - |
| Диапазон | R2C3:R5C7 | R[2]C[3]:R[5]C[7] | R2C3:R[5]C[7] R[2]C3:R5C[7] |
Вот пример, как использовать ссылку на ячейку в стиле R1C1 в формуле:
workbook.getCell(0, "A2").setR1C1Formula("R2C4 + SUM(R5C6:R7C9)");
Предопределенные Функции
Существуют предопределенные функции, которые можно использовать в формулах для упрощения их реализации. Эти функции инкапсулируют наиболее часто используемые операции, такие как:
- ABS
- AVERAGE
- CEILING
- CHOOSE
- CONCAT
- CONCATENATE
- DATE (1900 годовая система)
- DAYS
- FIND
- FINDB
- IF
- INDEX (формат ссылки)
- LOOKUP (векторный формат)
- MATCH (векторный формат)
- MAX
- SUM
- VLOOKUP