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

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

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

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

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

базовые математические операторы: +, -, /, *, ^, % (добавление знака процента)
операторы сравнения: <, >, <=, >=, <>, =
оператор & для для конкатенации строк
числа
ссылки на ячейки
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).

Вы также можете включить настройку strict: true для оператора "+" с помощью свойства strict, тогда математические вычисления будут такими, как в Excel:

// обычный оператор "+"
"1"+0 = 10
"a"+"b" = "ab"
// если включена настройка `strict: true` 
"1"+0 = 1
"a"+"b" = #ERROR!

Related sample: Strict mode

Вы также можете включить настройку strict: true для оператора "+" с помощью свойства strict, тогда математические вычисления будут такими, как в Excel:

// обычный оператор "+"
"1"+0 = 10
"a"+"b" = "ab"
// если включена настройка `strict: true` 
"1"+0 = 1
"a"+"b" = #ERROR!

Related sample: Strict mode

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")

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

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

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

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

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

Related sample: Editor Bar

Доступ к формулам через UI

Формулы разделены на категории: Common, Date, Text, Number, Financial. Формулы категории Common доступны по умолчанию.

Для того, чтобы сделать формулы доступными через пользовательский интерфейс необходимо использовать параметр methods и определить в нем категорию. Параметр methods может быть строкой, например, methods: "financial" или массивом, например, methods:["number", "text"].

webix.ui({
    view:"spreadsheet",
    methods:["date", "text"],
    toolbar:"full",
    data:spreadsheet_data
});

Для того, чтобы подключить все категории, используйте methods: "all". Если параметр methods не определен, в SpreadSheet добавляются только формулы категории Common.

Вы можете осуществлять поиск по категориям (обратите внимание, что поле Category будет скрыто, если параметр methods не определен):

Вы также можете указать собственную категорию при добавлении новых формул:

webix.ui({
  view:"spreadsheet",
  id: "sheets",
  methods:"all",
  toolbar:"full",
}); 
 
webix.i18n.spreadsheet.liveEditor.custom = "Custom";
$$("sheets").registerMathMethod("returnone", () => 1, "custom");
$$("sheets").setCellValue(1, 1, "=RETURNONE()")

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

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

Тултип с подсказками доступен при включенном свойстве liveEditor.

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

Описания параметров формул хранятся в локали, которая называется 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"] = {};

Наверх

If you have not checked yet, be sure to visit site of our main product Webix javascript ui components library and page of javascript spreadsheet library product.