스프레드시트에서 Google 양식 응답과 함께 수식을 사용하는 방법

게시 됨: 2022-06-16

사람들이 Google 양식을 제출하면 양식 응답을 저장하는 Google 시트에 새 행이 삽입됩니다. 이 스프레드시트 행에는 양식이 제출된 실제 날짜인 타임스탬프 열이 포함되어 있으며 시트의 다른 열에는 열당 하나씩 모든 사용자의 답변이 포함되어 있습니다.

Google Forms 시트를 확장하여 수식 필드도 포함할 수 있으며 Google Form에 의해 시트에 새 행이 추가될 때마다 셀 값이 자동으로 계산됩니다. 예를 들어:

모든 양식 응답에 자동 증가하지만 순차적인 ID를 할당하는 자동 번호 수식을 가질 수 있습니다. 인보이스 발행을 위해 Google Forms를 사용할 때 유용할 수 있습니다.
고객 주문 양식의 경우 Google 스프레드시트에 공식을 작성하여 항목 선택, 국가(세율이 다름) 및 양식에서 선택한 수량을 기반으로 총액을 계산할 수 있습니다.
호텔 예약 양식의 경우 공식은 Google 양식에 고객이 입력한 체크인 및 체크아웃 날짜를 기반으로 객실 임대료를 자동으로 계산할 수 있습니다.
퀴즈의 경우 교사가 양식에 입력한 값을 실제 답변과 일치시키고 점수를 할당하여 학생의 최종 점수를 자동으로 계산할 수 있습니다.
사용자가 여러 양식을 제출한 경우 수식은 사용자가 양식을 제출하는 즉시 사용자가 작성한 총 항목 수를 결정하는 데 도움이 될 수 있습니다.

Autofill Google Sheets Formulas

이 단계별 가이드에서는 Google 설문지와 연결된 Google 스프레드시트에 수식을 추가하는 방법을 배웁니다. 응답 행의 해당 셀 값은 새 응답이 제출될 때 자동으로 계산됩니다.

우리가 달성하려는 것을 더 잘 이해하려면 이 Google 양식을 열고 응답을 제출하세요. 그런 다음 이 Google 시트를 열면 새 행에서 응답을 찾을 수 있습니다. 열 FK는 수식을 사용하여 자동으로 채워집니다.

아래의 모든 예제는 Google Sheets의 ArrayFormula 함수를 사용하지만 이러한 예제 중 일부는 FILTER 함수를 사용하여 작성할 수도 있습니다.

양식 응답이 저장된 Google Sheet를 열고 첫 번째 빈 열로 이동하여 빈 열의 #1 행에 다음 수식을 복사하여 붙여넣습니다.

 =ArrayFormula( IFS( ROW(A:A)=1, "Invoice ID", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, LEFT(CONCAT(REPT("0",5), ROW(A:A) -1),6) ) )

ROW() 함수는 현재 응답 행의 행 번호를 반환합니다. 송장 열의 첫 번째 행에 대해 1 을 반환하므로 첫 번째 행에 열 제목을 설정합니다. 후속 행의 경우 행의 첫 번째 열(일반적으로 Timestamp)이 비어 있지 않으면 송장 ID가 자동으로 생성됩니다.

ID는 00001 , 00002 등입니다. 수식을 열의 첫 번째 행에 배치하기만 하면 열의 다른 모든 행이 자동으로 채워집니다.

IFERROR 함수는 오류 값이 아니면 첫 번째 인수를 반환하고, 그렇지 않으면 두 번째 인수가 있으면 두 번째 인수를 반환하고, 두 번째 인수가 없으면 공백을 반환합니다. 따라서 이 경우 1/0 은 오류이므로 항상 빈 값을 반환합니다.

Google 양식에는 체크인 날짜와 체크아웃 날짜의 두 가지 날짜 필드가 있습니다. 호텔 요금은 계절마다 다를 수 있으므로 Google 시트에 월 임대료를 유지하는 별도의 테이블이 있습니다.

Google Sheets Date Formula

Google 시트의 C 열에는 체크인 날짜에 대한 응답이 저장되고 D 열에는 체크아웃 날짜가 저장됩니다.

 =ArrayFormula( IF(ROW(A:A) = 1, "Room Rent", IF(NOT(ISBLANK(A:A)), (D:D - C:C) * VLOOKUP(MONTH(D:D), 'Room Rates'!$B$2:$C$13,2, TRUE), "" ) ) )

수식은 VLOOKUP 을 사용하여 양식 응답에 지정된 여행 날짜의 객실 요금을 가져온 다음 객실 임대료에 숙박 기간을 곱하여 객실 임대료를 계산합니다.

VLOOKUP 대신 IFS 를 사용하여 동일한 수식을 작성할 수도 있습니다.

 =ArrayFormula( IF(ROW(A:A) = 1, "Room Rent", IFS(ISBLANK(C:C), "", MONTH(C:C) < 2, 299, MONTH(C:C) < 5, 499, MONTH(C:C) < 9, 699, TRUE, 199 ) ) )

이 접근 방식에서는 FILTER 함수를 사용하므로 IF 함수를 사용하는 것보다 수식이 덜 복잡할 수 있습니다. 단점은 행 #1에 열 제목을 쓰고 행 #2에 수식을 붙여넣어야 한다는 것입니다(따라서 수식이 작동하려면 하나의 양식 응답이 있어야 함).

 =ArrayFormula(FILTER(E2:E, E2:E<>"")*1.35)

여기에서 인보이스 값에 35% 세금을 적용하고 이 공식은 스크린샷과 같이 "세금 금액" 열의 2행에 추가해야 합니다.

큰 사과로 알려진 도시는 어디입니까? 이것은 Google Forms의 단답형 질문이므로 학생들은 New York, New York City, NYC와 같은 응답을 제공할 수 있으며 여전히 정답입니다. 교사는 정답에 10점을 할당해야 합니다.

 =ArrayFormula( IF(ROW(A:A) = 1, "Quiz Score", IFS( ISBLANK(A:A), "", REGEXMATCH(LOWER({B:B}), "new\s?york"), 10, {B:B} = "NYC", 10, TRUE, 0 ) ) )

이 공식에서 우리는 프로그래밍에서 IF THEN 문과 같은 IFS 함수를 사용하고 있습니다. 정규 표현식을 사용하여 New York, New York, newyork 와 같은 값을 한 번에 일치시키기 위해 REGEXMATCH 를 사용하고 있습니다.

IFS 함수는 조건 중 어느 것도 참이 아닌 경우 NA 를 반환하므로 이전 조건 중 어느 것도 일치하지 않고 0 을 반환하는 경우 항상 true 로 평가되는 마지막에 TRUE 검사를 추가합니다.

사용자에게 전체 이름을 입력하도록 요청하는 양식 필드가 있는 경우 Google 스프레드시트 기능을 사용하여 전체 이름에서 이름을 추출하고 해당 필드를 사용하여 개인화된 이메일을 보낼 수 있습니다.

 =ArrayFormula( IFS( ROW(A:A)=1, "First Name", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, PROPER(REGEXEXTRACT(B:B, "^[^\s+]+")) ) )

여기서 RegexExtract 메서드를 사용하여 이름 필드의 첫 번째 공백 앞에 있는 문자열을 가져왔습니다. PROPER 함수는 사용자가 이름을 소문자로 입력한 경우 이름의 첫 글자를 대문자로 표시합니다.

Google 양식이 수집 이메일 주소인 경우 해당 필드를 사용하여 동일한 사용자가 여러 번 제출한 응답을 빠르게 감지할 수 있습니다.

 =ArrayFormula( IFS( ROW(A:A)=1, "Is Duplicate Entry?", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, IF(COUNTIF(B:B, B:B) > 1, "YES", "") ) )

B열에 응답자의 이메일 주소가 저장되어 있다고 가정하면 COUNTIF 함수를 사용하여 응답 스프레드시트에서 중복 항목을 빠르게 표시할 수 있습니다. 스프레드시트에서 조건부 서식을 사용하여 중복 항목일 가능성이 있는 행을 강조표시할 수도 있습니다.

Document Studio를 사용하여 양식 응답자에게 자동으로 이메일을 보낼 수 있습니다. Google 스프레드시트에서 공식 값이 자동으로 채워진 후 이메일이 전송됩니다. 원본 양식 응답 및 계산된 값은 생성된 PDF 문서에도 포함될 수 있습니다.