태그 보관물: google-apps-script

google-apps-script

Google 스프레드 시트 수식에 임시 변수를 저장하는 방법은 무엇입니까? ((A1 + B1) < 0, (A1

Google 스프레드 시트에서 다음과 같은 수식을 만들려고합니다.

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

이는 x보다 작 으면 0return x + 1, 그렇지 않으면 return을 의미 x합니다.

그러나 x그 자체는 표현입니다 (예 🙂 A1 + B1. 그래서 나는 결국 :

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

표현식 (A1 + B1)을 임시 변수에 저장하여 어떻게 할 수 x있습니까? :

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

내 스프레드 시트의 현재 표현식은 다음과 같습니다.

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

다음과 같이 더 짧고 관리하기 쉽도록하려고합니다.

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
)

답변

나는 종종 자주 사용되는 계산을 위해 변수로 셀을 사용하고 실제로는 “명명 된 범위”를 사용하여 이름을 지정합니다. 개발하려는 공식에 대해 쉽게 생각할 수 있습니다. 원하지 않는 경우 해당 셀을 숨길 수 있습니다.

답변

짧은 답변

현재 Google 스프레드 시트에는 셀 또는 범위 참조 대신 수식으로 정의 된 변수에 이름을 지정하는 기능이 없습니다. 이러한 종류의 변수에 수식을 사용하려면 사용자 지정 함수를 사용하는 것이 좋습니다.

Google 스프레드 시트의 맞춤 기능

맞춤 기능은 Google Apps Scripts 바운드 프로젝트 또는 Google 스프레드 시트 애드온에서 정의됩니다. 전자 메일 보내기와 같은 작업을 자동화하지 않고 값을 반환하는 데만 사용할 수 있습니다.

사용자 정의 함수는 JavaScript 함수와 유사하며 JSDOC 를 사용 하여 자동 완성과 같은 내장 함수 기능을 추가하고 팝업 수식 도우미를 표시 할 수 있습니다. 또한 사용자 정의 오류 메시지가 포함될 수 있습니다.

질문에 제시된 경우, 셀에 표시되는 원하는 공식 구조는 다음 구조를 가져야합니다.

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

여기서 x사용자 정의 함수가 될 수 있습니다.

다음은 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();
}

질문에 지정된 구조로 위의 사용자 정의 함수를 사용하는 공식은 다음과 같습니다.

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

row() 상수 대신에 수식을 배치 할 행을 입력으로 사용할 수 있습니다.

질문에 제시된 명시 적 수식을 “모방”하려면 내장 수식을 JavasScript / Google Apps Script 함수로 바꿔야합니다.

참고 문헌

답변

나는 이것에 대해 몇 가지 시도를하고 있습니다 (특히 긴 수식의 경우 매우 유용한 기능이라는 데 동의합니다. 지금까지 작성한 내용은 다음과 같습니다.

//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];
}

이론적으로 이것은 다음과 같은 복잡한 셀 수식을 단순화하는 데 유용합니다.

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

이것으로 :

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

추가 D_VAR#'s및 VAR#'s필요에 따라 여러 변수 선언 및 호출 할 수 있도록 스크립트에 기록 될 수 있습니다.

그러나 D_VAR0랩핑 된 변수를 variables [] 배열에 제대로 저장하지 않는 것 같습니다 .

배열에 요소를 수동으로 입력하면 VAR0이 해당 요소에 액세스하여 활성 셀로 되돌릴 수 있습니다. 예를 들면 다음과 같습니다.

var variables = ["test"];

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

이것은 나에게 두 가지 질문을 남긴다. 다음이 가능하다면 사용자 정의 함수를 통해 임시 변수를 선언하고 호출하는 것이 가능하다고 생각합니다.

  1. 사용자 정의 함수가 배열 요소에 액세스 할 수있는 경우 요소를 배열에 저장할 수 있습니까? 그렇다면 어떻게?

  2. 사용자 정의 함수가 요소를 배열에 저장할 수있는 경우 동일한 셀의 동일한 연속 수식 내에서만 액세스 할 수 있습니까? 또는 다른 셀에서 동일한 저장된 변수에 액세스 할 수 있습니까? 예:

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

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

답변