Поделиться через

Работа с формулами

  • Статья

В этом разделе рассматривается класс Open XML SDK CellFormula и его связь со схемой SpreadsheetML формата файла Open XML. Дополнительные сведения об общей структуре частей и элементов, образующих документ SpreadsheetML, см. в статье Структура документа SpreadsheetML (Open XML SDK).

Формулы в SpreadsheetML

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

Формулы хранятся внутри ячеек, в которых они используются, в XML-файле листа. CellFormula Используйте элемент (<f/>>) для определения текста формулы. Формулы могут содержать математические выражения, включая широкий спектр встроенных функций.

Элемент CellValue (<v/>>) сохраняет кэшированное значение формулы на основе времени последнего вычисления формулы. Это позволяет пользователю отложить вычисление значений формул при открытии электронной таблицы, что ведет к экономии времени при открытии листа. Указывать значение необязательно, и если его пропустить, то при открытии листа вычислять значение на основании определения формулы будет модуль чтения Open XML. Дополнительные сведения о классе см. в CellValue разделе CellValue.

В следующей информации из спецификации ISO/IEC 29500 представлен cellFormula элемент (<f/>>).

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

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

Example: рассмотрим формулу PI()*(A2^2). В данном случае

  • PI() является вызовом функции PI, которая возвращает значение числа π. — Ссылка на ячейку A2 возвращает значение в этой ячейке. — 2 — числовая константа. — Оператор курсора (^) поднимает левый операнд во власть правого операнда. — для группировки используются круглые скобки ( и ). — Оператор звездочки (*) выполняет умножение двух операндов.

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

Каждый набор ячеек таблицы по горизонтали является строкой, а каждый набор ячеек таблицы по вертикали является столбцом. Комбинация строки и столбца ячейки определяет расположение этой ячейки.

Ссылка на ячейку определяет одну или несколько ячеек на одном листе. Используя ссылки, можно: — использовать данные, содержащиеся в разных частях одного листа, в одной формуле. — Используйте значение из одной ячейки в нескольких формулах. — Ссылаться на ячейки на других листах той же книги и даже на другие книги. (Такие действия по отношению к ячейкам в других книгах называются ссылками.)

Функция — это именованная формула с нулевым или большим числом аргументов, которая выполняет операцию и (необязательно) возвращает результат. Примеры вызова функций: PI(), СТЕПЕНЬ(A1,B3) и СУММ(C6:C10).

В данном стандарте Office Open XML определяется более 300 встроенных функций. Также можно использовать функции, определяемые пользователями.

Имя — это псевдоним для константы, ссылки на ячейку или формулы. Имя в формуле может упростить понимание ее назначения. Например, формула СУММ(ПродажиПервКварт) более понятна, чем СУММ(C20:C30).

У каждого выражения есть тип. Формулы SpreadsheetML поддерживают следующие типы: массив, ошибка, логический, числовой и текстовый.

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

© ISO/IEC 29500: 2016

Дополнительные сведения о синтаксисе формул см. в стандарте ISO/IEC 29500.

Пример SpreadsheetML

В этом примере представлен XML-код для файла с одной формулой — функцией СУММ в ячейке A6 на Листе1. Приведенный ниже XML-код определяет лист и хранится в файле "sheet1.xml".

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="https://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="https://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
        <dimension ref="A1:A6"/>
        <sheetViews>
            <sheetView tabSelected="1" workbookViewId="0">
                <selection activeCell="A7" sqref="A7"/>
            </sheetView>
        </sheetViews>
        <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
        <sheetData>
            <row r="1" spans="1:1" x14ac:dyDescent="0.25">
                <c r="A1">
                    <v>1</v>
                </c>
            </row>
            <row r="2" spans="1:1" x14ac:dyDescent="0.25">
                <c r="A2">
                    <v>2</v>
                </c>
            </row>
            <row r="3" spans="1:1" x14ac:dyDescent="0.25">
                <c r="A3">
                    <v>3</v>
                </c>
            </row>
            <row r="4" spans="1:1" x14ac:dyDescent="0.25">
                <c r="A4">
                    <v>4</v>
                </c>
            </row>
            <row r="5" spans="1:1" x14ac:dyDescent="0.25">
                <c r="A5">
                    <v>5</v>
                </c>
            </row>
            <row r="6" spans="1:1" x14ac:dyDescent="0.25">
                <c r="A6">
                    <f>SUM(A1:A5)</f>
                    <v>15</v>
                </c>
            </row>
        </sheetData>
        <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
    </worksheet>