본문 바로가기

Google Spreadsheet

 (12)
[Tip] 특정 패턴 텍스트 추출하기 split() 함수 개념 잡기 split() 함수는 (나눌 대상, 구분자, 개별분할옵션, 공백제거옵션)로 구성되어 있습니다. 'I know who you are'이라는 문장을 공백(" ")을 구분자로 나눈 결과입니다. B1:F1까지 나눈 결과값들이 한번에 출력되는 것을 확인하실 수 있습니다. 옵션값들은 True로 입력되어서 조정이 필요 없을 경우 딱히 입력 안해주셔도 됩니다. 하지만 꼭 필요한 경우가 아니라면 대부분 상황에선 개별분할 옵션을 꺼주세요. 기본적으로 True로 되어있습니다. 위 예시처럼 아무 옵션을 추가로 입력하지 않았지만, True가 기본으로 되어있어 공백과 같은 단일 텍스트로 나눠집니다. 여기서 위 문장을 " who " 를 구분자로 해서 "I know"와 "you are"로 나누고 싶을 ..
[Tip] vlookup함수 다중 조건으로 검색하기 vlookup()함수 다중 조건으로 검색하기 vlookup()함수는 단일값으로 테이블에서 값을 찾는데 최적화된 테이블입니다. '=vlookup(찾을값, 테이블범위, 열순서, 옵션값)'으로 이루어져 있죠. 하지만 간혹 조회할 열이 유니크한 값들로 이루어져 있지 않고 다른 열과 조합했을 때 유니크해지는 경우가 있습니다. 이 때는 기존 vlookup함수만으론 검색이 불가능합니다. 추가 보조 열을 생성해서 해결해야 합니다. 위 예시에서 code가 b고 number가 3인 value를 조회하고자 합니다. vlookup() 함수의 찾을값에 b를 넣자니 b에는 각 number마다 다른 value들이 들어가 있어 원하는 값 검색이 불가능합니다. 이럴 때 함수만 써서는 불가능합니다. 유니크한 값들만 남길 수 있는 보조열..
[Tip] 알파벳 ABC를 숫자로 변환하기 ABC 알파벳을 123으로 변환하기 칼럼명이 몇번째인지, 알파벳마다 숫자로 된 코드를 달고 싶을 때 알파벳마다 숫자를 부여하고 싶을 때가 있습니다. 이 때 유용한 함수가 있습니다. 바로 code()함수죠. code()함수는 각 문자에 대해 정해진 유니코드를 계산해줍니다. 유니코드는 모든 문자들에 대해 U+16진수 숫자로 된 값들이 정해진 표준화 된 코드입니다. 여기서 code()함수는 첫번째 문자에 대해 유니코드 16진수 숫자만 10진수 숫자로 변환해서 계산해주죠. 이 때, 대문자 A의 유니코드는 65, B는 66입니다. 이걸 활용해서 각 영문자들의 순서인 숫자값을 계산하는 함수를 만들었습니다. =code(upper('알파벳 셀 주소 또는 값'))-code("A")+1 예로 알파벳 C 유니코드는 67입니..
[Tip] 테이블 랜덤으로 섞기 RAND() 함수를 이용한 테이블 섞기 구글 스프레드시 시트 내 [범위 임의로 섞기] 방법을 설명드리기 전에, 전통적(?)으로 진행해왔던 데이터 섞는 방법을 알려드립니다. 첫번째로, 새로운 열을 테이블 맨 우측에 만듭니다. 이후 첫번째 행에 =RAND() 함수를 작성해줍니다. 그리고 드래그 하여 모든 행에 입력합니다. 두번째로, 첫번째 머리행 클릭 후 필터를 걸으신 후 RAND() 함수가 작성된 열을 내림차순 또는 오름차순으로 정렬합니다. 랜덤으로 입력된 수에 의해 정렬되니까 어떠한 정렬기준을 선택하시던간에 랜덤으로 정렬된다는 결과는 똑같습니다. 완료 후 RAND() 함수가 작성된 열을 제거하여 테이블을 정리합니다. 구글 스프레드시트 자체 기능을 통해 데이터를 랜덤으로 섞어보자 위 설명이 무색할정도로 간..
[Tip] 구글 스프레드시트 속도 향상을 위한 최적화 방법들 느려터진 구글 스프레드 시트 구글 시트에 수많은 데이터를 넣고 복잡한 함수를 넣다보면 어느 순간 굉장히 버벅거리고 무거운 것을 느끼게 될 겁니다. 데이터를 줄이고, 함수를 날려봐도 어느순간 시트에 접속조차 어려워지는 상황에 처하게 되죠. 저같은 경우는 회사에서 데이터가 수천개 쌓여 있고 복잡한 함수가 걸린 시트에 다수 유저가 데이터 입력하는 상황에서 최적화를 고려해야 하다보니 시트 속도와 최적화에 대해 고민이 많았습니다. 요번 포스트에서는 제가 겪으면서 익혔던 구글 시트를 최적화 할 수 있는 몇가지 팁들을 알려드립니다. 쓰지 않는 셀들은 삭제합니다. 행이 늘어날수록 쓰지 않는 열들의 행도 같이 늘어납니다. 전체적으로 시트 크기가 커지게되죠. 구글 시트는 크키가 클수록 계산이 느려집니다. 빈 셀이 많을 수..
[Tip] 대소문자 구분해서 카운트하기 countif()함수의 한계 위 이미지를 살펴보면 A열에 'abc'가 2개, 'ABC'가 1개가 있습니다. 소문자 'abc'만 카운트 하고 싶은데 대문자 'ABC'까지 같이 카운트 되는 상황이죠. 이는countif()함수에서는 대소문자를 구분해서 카운트하는 기능이 없기 때문입니다. 당연히 같은 기능을 하면서 조건만 추가된 countifs()문 역시 제대로 대소문자를 구분해서 계산하지 못합니다. sumproduct(), exact() 함수를 이용한 대소문자 구분하여 카운트하는 함수 =sumproduct(exact("abc", a:a)) 함수 설명 exact() 함수와 sumproduct() 함수를 이용해서 대소문자를 구분한 검색이 가능합니다. sumproduct()도 익숙한 함수는 아닌데 exact()함수..
[기초] [자료형] 참/거짓형(불리언형) 참/거짓형(불리언 Boolean 형) 불리언, 참/거짓형은 불(Bool) 대수라고 불리며 참(True) 또는 거짓(False), 단 두가지의 값만 존재하는 자료형입니다. 간단히 예를들면 다음과 같죠. =3>1 : 3이 1보다 큰 것이 맞으므로 참(True)을 출력합니다. =3>6 : 3이 6보다 큰 것은 틀리므로 거짓(False)을 출력합니다. 불리언형은 부등호를 비롯한 비교 연산자, 참/거짓 값을 반환하는 함수를 통해 계산됩니다. 구글 스프레드시트에서 사용되는 비교 연산자는 아래와 같습니다. > : 왼쪽이 클 때 True = : 왼쪽이 크거나 같을 때 True 3, "맞아", "틀려") 함수를 살펴봅시다. 가장 먼저 맨 처음 인자(단순 연산자(2>3))가 참인지 거짓인..
[함수] Sum()과 관련된 함수들 Sum() 함수 sum()은 숫자나 참조할 범위의 숫자들의 함계를 계산해주는 함수입니다. 주로 총계를 계산할 때 주로 사용하죠. 구글 스프레드시트에서는 Sum에 다양한 옵션들을 추가해준 함수들을 제공합니다. 실무에서 주로 사용하는 함수는 다음과 같습니다. sum() sumif() sumifs() sumproduct() 상세 내용에 대해선 각 항목의 우측 링크를 클릭해서 조회해주세요.
[기초] [자료형] 문자형 문자형 문자형 데이터는 "가나다"같은 한글과 ABC같은 영어 그리고 @#$&, 天地玄黃과 같은 특수문자나 한자가 해당됩니다. 문자형 데이터는 글자를 나누는 split()함수, 글자를 합치는 textjoin(), join() 함수, 글자를 찾는 find() 함수 등에 사용되며, 고급함수인 query()함수에서 쿼리문 작성시에도 이용됩니다. 문자형 자료는 숫자 연산 함수에서 사용될 수 없습니다. 위에서 "=sum("구글", "스프레드시트")에서 에러나는 것을 확인하실 수 있습니다. 문자형 데이터를 입력하는 방법 문자형 데이터 입력은 데이터 앞뒤에 큰따옴표(")를 붙이는 것입니다. "구글", "스프레드시트"처럼요. 그러면 이 때 숫자 123 앞뒤에 큰따옴표를 붙이면 어떻게 될까요? 숫자일까요? 문자일까요? =..
[기초] [자료형] 숫자형 숫자형 숫자형은 1, 2, 3과 같은 정수, -1, -2, -3과 같은 음수, 0.1, 0.3, 0.5같은 소수를 포함한 모든 숫자형 자료를 말합니다. 그 외에도 날짜나 시간, 기간 등도 숫자형 데이터입니다. 숫자형 데이터는 사칙연산이나 평균이나 최대/최소값 계산 등에 활용됩니다. 날짜와 시간의 숫자형 변환 및 계산 구글 스프레드시트에서는 날짜, 시간, 기간도 숫자형 데이터로 계산됩니다. 예를 들어 날짜 '2020년 1월 1일'은 43,831입니다. '1899년 12월 31일'을 1로 기준으로 하고 해당 일자로부터 43,830일이 더 지난 날이 2020년 1월 1일이기 때문입니다. 시간도 마찬가지입니다. 오전 12:00:00는 0.0입니다. 하루의 절반이 지난 오후 12:00:00는 0.5입니다. 즉 날..

티스토리툴바