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

SpreadSheet позволяет выполнять операции с простым содержимым ячейки (numbers, strings) и операторами (арифметика, сравнения), а также со встроенными и пользовательскими функциями. Кроме того, можно объединить простые операнды и функции в более сложные формулы. Компонент обрабатывает их и отображает результат в соответствующих ячейках.

Синтаксис формул

Синтаксис формул в SpreadSheet такой же, как и в Excel.

1. Формула должна начинаться со знака '='.
2. Имена функций и ссылки на ячейки должны начинаться с большой буквы.
3. В формулах могут быть использованы следующие элементы:

• базовые математические операторы: +, -, /, *, ^, % (добавление знака процента)
• операторы сравнения: <, >, <=, >=, <>, =
• оператор & для для конкатенации строк
• числа
• ссылки на ячейки
• Excel-подобные функции

"data": [
    [ 3, 2, "=A1+B2",""],
    [ 4, 2, "=7+8",""],
    [ 5, 2, "=7+B2",""],
    [ 6, 2, "=SUM(A3+D7)",""]
    ...
 ]

• пользовательские функции

4 . Существует два способа записи формулы, в зависимости от выбранного способа вы можете получить разные результаты:

• если вы хотите применить формулу только к определенным двум ячейкам, необходимо указать ссылки на эти ячейки, разделив их запятой, например:

"data": [
 [ 7, 2, "=SUM(A3,D7)",""]
]

• если вы хотите применить формулу к диапазону ячеек, укажите ссылку на первую и последнюю ячейку, разделив их двоеточием, например:

"data": [
 [ 7, 2, "=SUM(A3:D7)",""]
]

Конкатенация строк

Вы можете конкатенировать содержимое ячеек в строках. Для этого существует три способа:

1 . оператор & со строками или ссылки на необходимые ячейки: "=nice"&"trip" или =A1&B2

2 . оператор + с теми же операндами: ="nice"+"trip" или =A1+B2. Оператор "+" ведет себя так же, как обычный оператор сложения JS (например ="nice"+12).

3 . функция CONCATENATE(): =CONCATENATE("nice","trip" или A1,B2). Также можно соединить строки из диапазона ячеек, чтобы получить один непрерывный текст: =CONCATENATE(C2:D4)

Использование именованных диапазонов в формулах

Вы можете задать имя определенному диапазону ячеек и затем использовать его в своих формулах. Диапазоны могут быть глобальными или же пренадлежать определённому листу.

Таким образом, формула становится понятнее и удобнее в работе.

Работа с диапазонами через API

Вы можете добавлять диапазоны и работать с ними через специальный модуль ranges.

add()

Метод используется для добавления диапазона глобально или же к активному/указанному листу. Метод принимает следующие параметры:

• name (string) - имя диапазона
• range (string) - диапазон ячеек
• scope (string, boolean) - область видимости. Возможные значения:
  • имя листа, которому хотите добавить локальный диапазон
  • true, если хотите добавить диапазон локально (будет доступен по всему Spreadsheet).

Чтобы добавить локальный диапазон текущему листу, не передавайте параметр scope.

// добавляем дапазон "MYRANGE" листу "Sheet 1"
$$("ssheet").ranges.add("MYRANGE", "B2:C2", "Sheet 1");

Related sample:  Named ranges

Перекрестные ссылки в листах Spreadsheet

Вы можете слинковать данные нескольких листов и использовать эти перекрестные ссылки в формулах.

Правила создания ссылки на внутренний лист просты:

• Sheet_Name!Cell_Name - чтобы сослаться на ячейку другого листа, например =Countries!A4
• Sheet_Name!Cells_Range - чтобы сослаться на диапазон ячеек другого листа, например =SUM(Countries!B2:B3)
• Sheet_Name!Named_Range - чтобы сослаться на именованный диапазон другого листа, например =SUM(Countries!DATA)

Несколько кейсов, которые продемонстрируют как вы можете использовать перекрестные ссылки:

• предоставить ссылку на ячейку из другого листа, например =Data!B8*D13 (где B8 это ячейка листа "Data")
• предоставить данные для диаграммы из другого листа, например =SPARKLINE(Countries!DATA,"splineArea","#6666FF") (где "DATA" это именованный диапазон листа "Countries")
• предоставить данные для дропдауна из другого листа, например ссылаясь на диапазон в "Add dropdown" как: Countries!NAMES (где "NAMES" это именованный диапазон листа "Countries")

Related sample:  Spreadsheet: Multiple Sheets

Редактор формул

В Spreadsheet есть продвинутый редактор формул. Его основные функции:

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

Чтобы выключить редактор формул, задайте значение false свойству liveEditor в конфигурации Spreadsheet:

webix.ui({
    view: "spreadsheet",
    data: base_data,
    liveEditor: false
});

Related sample:  Editor Bar

Подсказки для параметров формул

В редакторе формул есть встроенные подсказки для параметров вводимой формулы.

Локализация параметров формул

Описания параметров формул хранятся в локали, которая называется webix.i18n.spreadsheet.liveEditor["functions-syntax"]. Вы можете перевести их на нужный язык. Описание формул в локали имеет следующую структуру:

{
    methodName: [
        [parameter, description],
        ...
    ]
}

В случае, если вы добавили свою формулу, вы можете добавить подсказки для ее параметров, обновив локаль.

Например:

const ssheet = webix.ui({
  view:"spreadsheet",
  toolbar:"full"
});
 
ssheet.registerMathMethod("RANDOM", function(value){   value = value || 100;    return Math.round(Math.random()*value); });   
webix.i18n.spreadsheet.liveEditor["functions-syntax"].RANDOM = [      ["Digit", "Optional. The number digit."]  ];   
ssheet.setCellValue(1,1,"=RANDOM(100)")

Управление всплывающим окном с подсказками

Чтобы получить доступ к всплывающему окну с подсказками для формул, используйте следующий синтаксис: ssheet.$$("liveEditor").paramsPopup. Например, если вы хотите скрыть всплывающее окно, можно добавить соответствующий обработчик, и вернуть false из колбэк-функции события onBeforeShow:

ssheet.$$("liveEditor").paramsPopup.attachEvent("onBeforeShow", ()=> false);

или просто удалить все методы из локали следующим образом:

webix.i18n.spreadsheet.liveEditor["functions-syntax"] = {};

Наверх