Есть ли способ оценить формулу, которая хранится в ячейке?
В таблице Google Docs я ищу что-то вроде =EVAL(A1) где A1 установлен на "=1+2",
Я обнаружил, что в MS Excel есть EVALUATE() функция (которая кажется немного сложной для правильного использования). Но я не смог найти ничего похожего в Google Docs.
Я также искал список функций, но не смог найти ничего полезного...
11 ответов
Нет, нет эквивалента Excel EVALUATE() в Google Sheets.
За этим стоит долгая история, например, посмотрите этот старый пост.
Если вы просто интересуетесь простой математикой (как показано в вашем вопросе), это легко сделать с помощью пользовательской функции.
function doMath( formula ) {
// Strip leading "=" if there
if (formula.charAt(0) === '=') formula = formula.substring(1);
return eval(formula)
}
Например, с вашим A1, положить =doMath(A1) в другой камере, и это будет 3,
Если вы хотите оценить простую математику (например, A1: "(1+2)*9/3"), вы можете использовать запрос:
=query(,"Select "&A1&" label "&A1&" ''",0)
Я знаю это старый пост. Мне просто интересно, почему никто не предложил:
myCell.getValue();
Это даст вам результат формулы в myCell (3 в вашем примере).
Если вы хотите записать результат в ячейку (вместо формулы), вы можете использовать:
function fixFormula(myCell) {
myCell.setValue(myCell.getValue());
}
Короткий ответ
Как упоминалось ранее, в Google Sheets нет встроенной функции EVALUATE, но Google Sheets можно расширить, чтобы добавить эту функцию. К счастью, некоторые файлы SocialCalc могут быть использованы для облегчения этого.
скрипт
В таблице Google я делюсь своими успехами. В это время я добавил файлы SocialCalc, которые, на мой взгляд, требуются, и несколько функций, и несколько тестовых случаев.
ЗАМЕЧАНИЯ:
- Специальные функции Google Sheets, такие как FILTER, UNIQUE и другие, недоступны в SocialCalc, а также другие функции, такие как SIGN.
- Я думаю, что файл SocialCalc должен быть заменен файлом на https://github.com/marcelklehr/socialcalc как он должен быть обновлен в последнее время. H/T для eddyparkinson (см. /questions/421143/est-li-sposob-otsenit-formulu-kotoraya-hranitsya-v-yachejke/421164#421164)
Пользы
Функция EVALUATE в связанном файле может использоваться как пользовательская функция.
Пример 1
A1: '=1+2 (обратите внимание на использование апострофа, чтобы Google Sheets рассматривал формулу как строку.
B1 формула:
=EVALUATE(A1)
B1 отображаемое значение:
3
Пример 2
"ОЦЕНИТЬ" формулу типа =VLOOKUP(2,A1:B3,2)В это время нам нужно использовать "продвинутые" параметры. Смотрите следующий пример:
B1: '=VLOOKUP(2,A1:B3,2)
Формула C1:
=EVALUATE(B1,"data","A1:B3")
C1 отображаемое значение:
B
Code.gs
/**
*
* Evaluates a string formula
*
* @param {"=1+1"} formula Formula string
* @param {"Tests"} sheetName Target sheet.
* @param {"A1"} coord Target cell.
*
* @customfunction
*
*/
function EVALUATE(formula,sheetName,coord){
// SocialCalc Sheet object
var scSheet = new SocialCalc.Sheet();
if(sheetName && coord){
// Pass values from a Google sheet to a SocialCalc sheet
GS_TO_SC(scSheet,coord,sheetName);
}
var parseinfo = SocialCalc.Formula.ParseFormulaIntoTokens(formula.substring(1));
var value = SocialCalc.Formula.evaluate_parsed_formula(parseinfo,scSheet,1); // parse formula, allowing range return
if(value.type != 'e'){
return value.value;
} else {
return value.error;
}
}
/**
*
* Pass the Google spreadsheet values of the specified range
* to a SocialCalc sheet
*
* See Cell Class on socialcalc-3 for details
*
*/
function GS_TO_SC(scSheet,coord,sheetName){
var ss = SpreadsheetApp.getActiveSpreadsheet();
if(sheetName){
var sheet = ss.getSheetByName(sheetName);
var range = sheet.getRange(coord);
} else {
var range = ss.getRange(coord);
}
var rows = range.getNumRows();
var columns = range.getNumColumns();
var cell,A1Notation,dtype,value,vtype;
// Double loop to pass cells in range to SocialCalc sheet
for(var row = 1; row <= rows; row++){
for(var column = 1; column <= columns; column++){
cell = range.getCell(row,column);
A1Notation = cell.getA1Notation();
value = cell.getValue();
if(cell.isBlank()){
dtype = 'b';
vtype = 'b';
} else {
switch(typeof value){
case 'string':
dtype = 't';
vtype = 't';
break;
case 'date':
case 'number':
dtype = 'v'
vtype = 'n';
break;
}
}
scSheet.cells[A1Notation] = {
datavalue: value,
datatype: dtype,
valuetype: vtype
}
}
}
}
formula1.gs
https://github.com/DanBricklin/socialcalc/blob/master/formula1.js
socialcalcconstants.gs
https://github.com/DanBricklin/socialcalc/blob/master/socialcalcconstants.js
socialcalc-3.gs
https://github.com/DanBricklin/socialcalc/blob/master/socialcalc-3.js
Скопируйте и вставьте формулы:
Может быть, вы можете скопировать и вставить нужные формулы из "jQuery.sheet". Переехал в:
https://github.com/Spreadsheets/WickedGrid
Похоже, все с открытым исходным кодом
Не смогу решить проблему
Также: проблема "Включить сценарии для использования стандартных функций электронных таблиц" помечена как "Не удается исправить", см. https://code.google.com/p/google-apps-script-issues/issues/detail?id=26
Ethercalc есть электронная таблица с открытым исходным кодом, похожая на Google, которая называется Ethercalc.
Код GUI: https://github.com/audreyt/ethercalc
Формулы: https://github.com/marcelklehr/socialcalc
Демонстрация - на песчаной бури: https://apps.sandstorm.io/app/a0n6hwm32zjsrzes8gnjg734dh6jwt7x83xdgytspe761pe2asw0
Простой способ оценить формулы в электронной таблице Google:
- выделите ячейки или столбцы с формулами
- перейдите Редактировать -> Найти и заменить...
- установите флажок "И искать в формулах"
- заменить "=" на "=="
- заменить обратно "==" на "="
- в том же окне "Найти и заменить" снимите флажок "И искать в формулах"
формулы оценю!:)
Это немного взломать, но это работает
- получить формулу из ячейки;
- снова установите формулу; тогда
- получить значение из ячейки.
var cell = sheet.getRange("A1");
var formula = cell.getFormula();
cell.setFormula(formula);
var fileCell = cell.getValue();
Вот рабочий прием для оценки объединенной строки формулы. Используйте ячейку формулы в качестве источника проверки данных для целевой ячейки. Возможно, это не полностью автоматизированное решение. Но оценка обновленных формул была сокращена до одного щелчка мыши. Вам просто нужно повторно выбрать значение из поля проверки, когда это необходимо. Большое спасибо @Aurielle Perlmann и @user3626588 за идею.
Например, когда вы настроили динамическую множественную конкатенацию такой приведенной ниже формулы на другом листе, это будет хорошо работать с выбором параметра проверки.
В моем случае двойное нажатие Enter неудобно.
=({FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/edit"; "EXPENSES!A2:P"); INDEX(IMPORTRANGE("https://docs.google.com/spreadsheets/d/aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/edit"; "EXPENSES!A2:P"); 0; 1) <> ""); FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb/edit"; "EXPENSES!A2:P"); INDEX(IMPORTRANGE("https://docs.google.com/spreadsheets/d/bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb/edit"; "EXPENSES!A2:P"); 0; 1) <> ""); FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/cccccccccccccccccccccccccccccccccccccccccc/edit"; "EXPENSES!A2:P"); INDEX(IMPORTRANGE("https://docs.google.com/spreadsheets/d/cccccccccccccccccccccccccccccccccccccccccc/edit"; "EXPENSES!A2:P"); 0; 1) <> "")})
[введите описание изображения здесь]
[введите описание изображения здесь]
Вот уловка. Просто получите все значения, вставьте формулу в нужную ячейку, затем снова получите это значение ячейки и замените уже вставленную формулу этим новым значением.
function calculateFormula(){
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName("Sheet Name");
var sheetData = sheet.getDataRange().getValues();
sheet.getRange(row,col).setValue("=sum(D6,C12:C14)");
sheetData = sheet.getDataRange().getValues();
var newValue = sheetData[row-1][col-1];
sheet.getRange(row,col).setValue(newValue);
}
Преобразуйте столбец выражений, которым не предшествует `+.
92/120
67/85
и т.п.
Вот бесплатный инструмент для анализа и оценки формул в Google для распутывания формул:
https://docs.google.com/spreadsheets/d/1wwclmAMXGaFanVLlyvzv63fDx6JkwOcT6Dkisac-sXI/copy
Это бесплатно. Он крошечный. Это просто общий файл таблицы Google (без макросов, надстроек и прочего). Щелкните ссылку, чтобы сделать свою копию. Попробуйте, и, если вам это нравится, добавьте его в закладки для будущего использования, борясь с формулами в Google-таблице.
Я сделал это, чтобы восполнить отсутствие в таблице Google парсера Evaluate Formula (F9), родного для Excel. Он анализирует любую формулу, вставленную в желтую ячейку G1, разделяя и раскрашивая их по глубине или выбранным символам и сравнивая их с доступными формулами, чтобы увидеть, где скобки или запятые, вероятно, были опущены или помещены в неправильное место. Удобный инструмент для простых проверок, а также для создания или анализа сложных или вложенных формул, а также для поиска "ошибок синтаксического анализа" и диагностики #REF!, #NAME?, #N/A, #NUM!, #VALUE! коды ошибок.
Наслаждайтесь!