Próbuję utworzyć formułę w arkuszu kalkulacyjnym Google, który wygląda następująco:

if (x < 0, x + 1, x)

Oznacza to, że jeśli xjest mniejsze niż 0, wróć x + 1, w przeciwnym razie wróć x.

Jednak xsamo to wyrażenie, np A1 + B1. Więc skończyłem z:

if ((A1 + B1) < 0, (A1 + B1) + 1, (A1 + B1))

Jak zapisać wyrażenie (A1 + B1)w zmiennej tymczasowej x, aby to zrobić? :

x = (A1 + B1);
if (x < 0, x + 1, x);

Obecne wyrażenie w moim arkuszu kalkulacyjnym wygląda następująco:

if(
    timevalue(Sheet1!$D10)-timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10))))
    < 0,
    1 +
    timevalue(Sheet1!$D10)-timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10))))
    ,
    timevalue(Sheet1!$D10)-timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10))))
)

Staram się, aby wyglądał na krótszy i łatwiejszy w zarządzaniu:

x = timevalue(Sheet1!$D10) - timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10))));
if(
    x
    < 0,
    1 +
    x
    ,
    x
)

Często używam komórek jako zmiennych do często używanych obliczeń i faktycznie nazywam je za pomocą „nazwanych zakresów”. Łatwiej jest pomyśleć o formule, którą próbujesz opracować. Możesz ukryć te komórki, jeśli nie chcesz, aby były widoczne.

Krótka odpowiedź

W tej chwili Arkusze Google nie mają funkcji przypisywania nazwy do zmiennej zdefiniowanej przez formułę zamiast odwołania do komórki lub zakresu. Aby użyć formuły z tego rodzaju zmiennymi, alternatywą jest użycie funkcji niestandardowych.

Funkcje niestandardowe w Arkuszach Google

Funkcje niestandardowe są definiowane w projektach powiązanych z Google Apps Scripts lub dodatkach do Arkuszy Google. Można ich używać tylko do zwracania wartości, a nie do automatyzacji zadań takich jak wysyłanie wiadomości e-mail.

Funkcja niestandardowa jest jak funkcja JavaScript i może używać JSDOC do dodawania funkcji wbudowanych, takich jak autouzupełnianie i do wyświetlania pomocnika formuły wyskakującej. Można również dołączyć niestandardowy komunikat o błędzie.

W przypadku przedstawionym w pytaniu pożądana struktura wzoru do wyświetlenia w komórce powinna mieć następującą strukturę

if(x < 0, 1 + x, x)

gdzie xmoże być funkcja niestandardowa.

Poniżej znajduje się prosty przykład niestandardowej funkcji używającej JSDOC .

/**
* Returns the cell value of the specified row in column A of Sheet1.
* @param {number}   row_number   Input the row number.
* @return The cell value of the specified row in column A of Sheet1.       
* @customfunction
*/
function z(row_number) {
  if(typeof(row_number) != 'number') 
    throw new Error("A row number is required");
  return SpreadsheetApp
           .getActiveSheet()
           .getRange("Sheet1!A"+row_number)
           .getValue();
}

Formuła wykorzystująca powyższą funkcję niestandardową ze strukturą określoną w pytaniu będzie wyglądać w następujący sposób

=if(z(10) < 0, 1 + z(10), z(10))

row() można użyć zamiast stałej, aby mieć jako dane wejściowe wiersz, w którym formuła jest umieszczona.

Aby „emulować” jawną formułę przedstawioną w pytaniu, wbudowane formuły należy zastąpić funkcjami JavaScript i skryptów Google Apps.

Bibliografia

• Czy wiedziałeś? (Funkcje niestandardowe w skrypcie Google Apps)
• Przewodnik po stylu JavaScript w Google

Próbuję tego dokonać (zgadzam się, że byłaby to bardzo przydatna funkcja, szczególnie w przypadku długich formuł. Oto, co napisałem do tej pory:

//array to store variables
var variables = [];

//Wrap a formula with "D_VAR0()" for later use
function D_VAR0(formula) {
  variables.push(formula);
}

//Write "VAR0" to access the 0th element of variables[] (which could be a long formula)
function VAR0() {
  return variables[0];
}

Teoretycznie byłoby to przydatne do uproszczenia złożonej formuły komórkowej, takiej jak:

=IF((A2+B2)/B3<16, (A2+B2)/B3, ((A2+B2)/B3)*45)

zaangażowany w to:

=IF(D_VAR0((A2+B2)/B3)<16, VAR0, VAR0*45)

Dodatkowe D_VAR#'si VAR#'smogą być zapisane w skrypcie, aby umożliwić wiele deklaracji zmiennych i wywołań w razie potrzeby.

Wydaje się jednak, że D_VAR0nie przechowuje poprawnie zawiniętej zmiennej w tablicy zmiennych [].

Jeśli ręcznie wprowadzę element do tablicy, VAR0 będzie w stanie uzyskać do niego dostęp i zwrócić go do aktywnej komórki. Na przykład:

var variables = ["test"];

//results in the active cell being set to "test"
function VAR0() {
     return variables[0];
}

To pozostawia mi dwa pytania. Jeśli możliwe są następujące, to myślę, że można zadeklarować i wywołać zmienne tymczasowe za pomocą funkcji niestandardowej:

1. Jeśli funkcje niestandardowe mogą uzyskać dostęp do elementów tablicy, czy mogą również przechowywać elementy w tablicy? Jeśli tak to jak?

2. Jeśli funkcje niestandardowe mogą przechowywać elementy w tablicy, czy są one dostępne tylko w tej samej ciągłej formule w tej samej komórce? Czy jest to możliwe, aby uzyskać dostęp do tej samej przechowywanej zmiennej w innej komórce? Przykład:

Cell A1 = D_VAR0(sum(3,5))

Cell D4 = VAR0() //would this return 8 in cell D4? It hasn't worked in my testing.