본문 바로가기
728x90

IT/엑셀, 구글스프레드시트28

[엑셀] 컬럼명에 필터 걸기 단축키 엑셀에서 항목명 (컬럼명, 컬럼 제목)에 필터 걸고 싶을 때 단축키로 걸면 매우 편리하다 필터 걸 항목들을 마우스 드래그 해서 선택되게 한 다음에 필터 생성 단축키 누르기 Ctrl + shift + L 이렇게 필터가 걸렸다 항목이 너무 많아서 마우스 드래그로 지정하기 불편할 때는 컬럼제목이 써져있는 행의 행번호 써져있는 칸을 클릭하면 해당 한 줄이 전체 선택 됨 그 상태에서 Ctrl + shift + L 하면 전체 항목 모두 필터 걸림 2023. 3. 16.
엑셀 피벗(pivot) 테이블 사용 방법 엑셀에서 피벗(pivot) 테이블 사용하는 방법 용어설명 사용법 용어설명 피벗 ? 어떤 한 축을 중심으로 회전 시킨다는 건데 엑셀에서의 피벗은 정제되지 않은 여러 컬럼의 raw 데이터가 있는 상태에서 날짜기준으로 집계해서 볼 수 있게 한다던지 광고 계정 기준으로 클릭수, 노출수를 볼 수 있게 한다던지 등 날 것 그대로의 데이터를 -> 보고 싶은 내용만 한 눈에 보기 쉽게 집계 및 정리 해서 볼 수 있게 해주는 기능이다. 사용법 예를 들어 아래와 같은 raw 데이터가 있다. (정제 되지 않은 날 것의 데이터) 이 데이터를 계정이름별, 월별로 노출, 클릭을 집계하고 싶다면 ? 우선 테이블이 전체 선택되게 A부터 M까지 쭈욱 마우스로 클릭해서 선택되게 한 다음에 메뉴에 삽입 -> 피벗 테이블 클릭 이렇게 새로.. 2022. 10. 21.
엑셀에서 JSON 파일 불러오기 (DB에서 추출한 CSV 마지막 숫자 0으로 바뀌는 문제) DB 에서 데이터를 추출하려고 할 때 다운 받을 수 있는 형식이 JSON or CSV 만 지원해 주는 경우 , CSV 다운받았을때 주문번호 같은 경우는 맨 뒤에 숫자가 0으로 전부 바뀌어서 다운로드 된다 CSV 를 다운받은 경우 아래 화면 처럼 주문번호 끝이 0이 아닌데 전부 0으로 바뀐것을 볼 수 있다. 어떻게 해야 원 주문번호 그대로 파일을 다운 받을 수 있을까? 일단 JSON 파일로 다운 받으면 모든 주문번호가 제대로 잘 살아있다. DB 에서 JSON 파일을 다운 받은 후 엑셀에서 열면 주문 번호 살아있는 채로 파일 형식으로 볼 수 있다. 엑셀 -> 데이터 -> 데이터 가져오기 -> 파일에서 -> JSON에서 미리 준비해 둔 json 파일 선택해서 가져오기 아래와 같은 화면이 뜨면 "테이블로" 클릭.. 2022. 6. 20.
[엑셀, 구글 스프레드시트] 표에서 가로 세로 교차하는 지점 값 반환하기 (vlookup + match) 엑셀, 구글 스프레드시트에서 함수를 사용하여, 표에서 가로 세로 모두 맞는 값 찾는 방법 (vlookup + match) VLOOKUP 과 MATCH 함수를 중첩하면 표에서 가로 세로가 교차하는 지점의 값을 반환 할 수 있다. 배송비 테이블이 아래와 같이 있다. 오른쪽 표에 1지역, 무게 0.5kg 이면 배송비가 얼마인지 반환해주고 싶다면 ? =VLOOKUP (H4, A3:D11, match(G4,B3:D3,0)+1, 0) =VLOOKUP ( 0.5kg무게를 , 배송비테이블 A3:D11에서 검색, 반환해줄 열은 몇번째 열인지 , 일치검색 ) match(G4, B3:D3, 0 )+1 를 들여다 보면, match ( 검색할 지역, B3:D3 범위에서 몇번째 칸에 있는 단어인지, 일치검색 ) + 1 i4 셀을.. 2022. 4. 5.
[구글 스프레드시트] 날짜 차이 구하기 DATEDIF 날짜 차이 DATE DIFFERENCE 의 약자 DATE DIF DATEDIF 함수 사용법 DATEDIF ( 시작일, 종료일, 차이를 계산할 단위 ) 차이를 계산할 단위는 Y, M, D, MD, YM, YD 중에 택 1 "Y": 시작일과 종료일 사이 전체 연도의 수 "M": 시작일과 종료일 사이 전체 개월의 수 "D": 시작일과 종료일 사이의 전체 일 수 "MD": 시작일과 종료일 사이의 기간에서 전체 개월 수를 무시한 일 수 "YM": 시작일과 종료일 사이의 기간에서 전체 연도 수를 무시한 개월 수 "YD": 시작일과 종료일 사이의 간격이 1년 이하라는 가정 하에 계산한 시작일과 종료일 사이의 일 수 EX ) '내 나이에서 만이 되는 연 수와 월 수를 빼면 며칠이 남는지' 확인하려면 단위 "MD"를 사.. 2022. 3. 31.
[구글 스프레드시트] ? 이스케이프 하기 / 특수문자 검색하기 (feat. isnumber, search) 본론부터 얘기하자면 ? 를 이스케이프 하기 위해서 앞에 붙이면 되는 기호는 ~ 물결 기호이다. 아래 캡쳐본을 보면 A2 셀에 문자에 물음표 기호가 포함 되어 있는지, 없는지 여부에 따라서 TRUE, FALSE 를 출력하고자 함수를 작성했는데 물음표가 있으나 없으나 TRUE 를 출력한다. =isnumber(search("?",A2)) 물음표 기호를 그냥 일반 문자로 인식 되도록 ? 앞에 ~ 물결 기호를 붙여서 ? 를 이스케이프 해줘야 된다. =isnumber(search("~?",A2)) ~ 붙여서 ? 를 일반 문자로 인식하게끔 해주니 결과가 잘 나온다. 참고 ) ISNUMBER , SEARCH SEARCH 함수는 찾고자 하는 단어가 있으면 발견된 위치의 숫자를 반환하고 찾고자 하는 단어가 없으면 #VAL.. 2022. 3. 4.
[구글 스프레드시트] 특정 단어 포함된 행들 출력하기 (feat. Filter 함수) 구글 스프레드시트에서 특정 단어가 포함되는 행들만 출력하고 싶을 땐 FILTER 함수 + REGEXMATCH 함수 두가지를 중첩해서 쓰면 된다 FILTER 함수 사용법 REGEXMATCH 함수는 텍스트 일부가 정규표현식과 일치하는지 TRUE, FALSE 로 여부 알려주는 함수다 바로 활용해 보자 아래 캡쳐본에 B2 셀에 입력한 함수다. =FILTER(A:A,REGEXMATCH(A:A,"acne|Acne|ACNE")) A:A 범위에 있는 모든 셀들 중에 acne or Acne or ACNE 를 포함한 단어들만 출력 하라는 의미이다. FILTER 함수가, REGEXMATCH 결과가 TRUE 인 행들만 출력해 준것이다. acne 이 단어만 포함된 행들만 출력하고 싶다면 =FILTER(A:A,REGEXMATCH.. 2022. 3. 4.
[구글 스프레드시트] Filter 함수로 조건 걸어 출력하기 (Feat. 드롭다운메뉴) Filter 함수는 offset 함수 + if 함수 .. 이런 느낌.. 조건에 맞는 데이터들만 촤르르 출력해주는 기능이다. Query 함수도 조건에 충족하는 데이터들을 촤르르 뿌려주는 함수인데 Query 함수 보다 훨 간편히 쓸 수 있고 비슷한 기능을 가진 함수가 Filter 함수다. Fiter 함수를 아래와 같이 쓸 수 있다 선택한 월에 따라 해당되는 외화 값 출력 어떻게 했는지 하나씩 뜯어보자 일단 필터함수는 아래와 같이 셋팅 해서 쓸 수 있다. = FILTER ( 범위, 조건1, [조건2, …] ) = FILTER ( 데이터 테이블에서 데이터 뿌려줄 범위 설정, 데이터 뿌려줄 조건1 , 데이터 뿌려줄 조건2 ..... ) 위 그림에서 F1 : H 가 데이터 테이블이다. 여기서 F 칼럼인 '월' 로 .. 2022. 1. 24.
[구글 스프레드시트, 엑셀] 배송비 테이블에서 해당 되는 무게구간 구하기 실 무게 -> 해당되는 무게구간 매칭시켜주기 실무게 4.2kg 은 0.5kg 단위의 배송비 요율표에선 4.5kg 에 해당된다 2.4kg 는 2.5kg 에 해당됨 택배를 여러개 보낸다면, 위의 표 처럼 박스마다 실 중량이 측정되어 나올텐데 (3.521kg, 2.33kg...) 이 박스는 어떤 무게 구간에 해당하는지 엑셀에서 함수로 찾아내는 방법 반올림함수, 내림함수, if 함수 사용하면 가능하다 IF, ROUNDDOWN, ROUND (복잡한 것 싫거나, 급한 분은 위 엑셀 다운로드 받아서 함수 복사해서 사용하세요 ㅎㅎ) 실습 ! 배송비 테이블이 아래와 같이 있다. 박스가 0.32kg 이면 배송비 5천원 1.88kg 이면 12,500원 이다. 양이 적으면 눈대중으로 보면 되지만, 택배를 1,000건 보냈다면.. 2022. 1. 14.
[구글 스프레드시트] 이전 버전으로 복원하기 구글 스프레드시트 이전 버전으로 복원하는 방법 어제로 복원하든, 편집했던 어떤 한 시점으로 복원하든 모두 가능! 메뉴 툴 바에서 파일 -> 버전기록 -> 버전 기록 보기 또는 단축키 Ctrl + Alt + Shift + H 화면 오른쪽에 '버전 기록' 중 복원 시킬 버전을 선택하고 화면 왼쪽 상단에 초록 버튼 '이 버전 복원하기' 클릭 이렇게 해당 버전으로 복원 완료! 실수로 상당량의 데이터를 날렸을 때, 수기로 수정 불가능 할 만큼 이전 버전 복원이 절실할 때 당황하지 말고 이렇게 하시면 됩니다 :) 2021. 11. 24.
업무 할 때 자주 쓰는 단축키 모음 (엑셀, 구글스프레드시트 편) 업무할 때 엑셀, 구글 스프레드시트에서 자주 쓰는 단축키 모음! 1. 복사 + 붙여넣기 한번에 하기 즉 ctrl + c 와 ctrl + v 를 한번에 해버리는 단축키 아래로 복붙 Ctrl + D 오른쪽으로 복붙 Ctrl + R 2. 셀 선택 전체선택 Ctrl + A 행 선택 Shift + Space bar 필터 걸기 Ctrl + Shift + L 행 or 열 삭제 : 행 or 열 우클릭 후 D 행 or 열 삽입 : 행 or 열 우클릭 후 i 값만 붙여넣기 : Ctrl + Shift + V 서식만 붙여넣기 : Ctrl + Alt + V 행 or 열 삭제 : 행 or 열 선택후 Ctrl + Alt+ '-' (마이너스) 행 or 열 삽입 : 행 or 열 선택후 Ctrl + Alt + '+' (플러스) 👇업무 .. 2021. 11. 19.
[구글 스프레드시트] 다른 시트 불러오기 IMPORTRANGE 구글 스프레드시트에서 IMPORTRANGE 함수를 써서 다른 시트 내용을 그대로 불러올 수 있다 불러올 시트의 URL을 입력하고, 셀 범위만 선택하면 된다 =IMPORTRANGE(스프레드시트_URL, 범위_문자열) =IMPORTRANGE(내용가지고 올 스프레드시트_URL, "시트이름!셀범위") 예) =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Hh2grfB6rp9OQ2yAIu3S5YF_CCFJGwyqPGveABlOZKg/edit", "World Cup!A1:D21") 아래 이미지는 Importrange 함수를 사용해서 왼쪽 시트에 있는 일정 범위 데이터를 오른쪽 시트에 불러온 캡쳐본이다 위 이미지에서 사용한 IMPORTRANGE 함수를 살펴보자 오른쪽 .. 2021. 11. 10.
[구글 스프레드시트, 엑셀] 소수점 뒷자리 없애기 - TRUNC 숫자의 소수점 뒷 부분을 정리해주는 함수로 TRUNC를 사용할 수 있다. TRUNC 사용 법 =TRUNC (값, 소수점이하 몇자리까지만 남길건지) ex1) =TRUNC (181.6666667, 2) =181.66 반올림 하는게 아니고 이렇게 아예 잘라버린다 ex2) 다른 함수와 중첩해서 사용해보자 =TRUNC( AVERAGE(B2:D2) , 0) =181 소수점을 모두 없애고 싶을땐 이렇게 하면된다 =TRUNC( 값, 0 ) 여기서 0은 생략해도 결과가 동일하다 2021. 9. 11.
[구글 스프레드시트] 문자랑 함수 한 칸에 같이 표기하는 방법 (feat. &) 구글 스프레드시트에서 한 칸에 (한 셀에) 문자와 함수를 같이 쓰고 싶을 때 & 앰퍼샌드를 이용해서 맘껏 같이 쓸 수 있다. 예시1) A1 칸에 글자 "금액" 과 Sum 함수를 한번에 표기하고 싶을때, 아래와 같이 ="금액 " & sum(A2:A5) 이렇게 앰퍼샌드로 문자와 함수를 이어주면 위 이미지에 A1 같이 표기된다 함수 쓸때 쓰는 등호(=) 기호를 먼저 써주고 큰 따옴표(" ") 안에 문자를 써주고 앰퍼샌드(&)로 문자와 함수를 이어준다 "금액 " 이렇게 '액' 뒤에 한칸 띄우면 띄어쓰기가 된다 문자와 함수 순서는 상관없다. ex) ="문자" & 함수 & "문자" 도 OK =함수 & "문자" & 함수 도 OK 위 예시 A1 sum 값 뒤에 문자 "원"을 추가해 보자 예시2) countif(A2:A.. 2021. 9. 10.
[엑셀, 구글 스프레드시트] 주민등록번호 뒷자리 가리는 방법 (left, rept, concat) 엑셀 or 구글 스프레드시트에서 함수를 사용해서 주민등록번호 뒷자리를 가리는 방법을 알아보자 사용할 함수는 3가지다. LEFT, REPT, CONCAT LEFT 함수는 선택한 문자열에서 왼쪽에서 몇자리까지 추출할 건지 선택해서 추출해주는 함수 REPT 함수는 원하는 문자를 몇번 반복할건지 정해서 반복시켜주는 함수 CONCAT 함수는 문자열들을 연결시켜주는 함수 사용된 함수의 의미들을 살펴보자 =LEFT(B2,8) =B2셀에 있는 문자열에서 왼쪽에서부터 8자리까지만 반환해줘 =결과는 950130-1 =REPT("*",6) =문자 * 를 6번 반복해줘 =****** =CONCAT(C2,D2) =C2와 D2를 연결해줘 =결과는 950130-1****** 2021. 8. 31.
[구글 스프레드시트] 두 데이터 상관 분석 PEARSON, CORREL 어떤 두 데이터 집단간에 상관 관계를 알고 싶을때 통계학에서 주로 사용하는 분석은 상관관계와 회귀분석이다. 이 글에서 다룰 내용은 상관관계! 상관관계는 일반적으로 피어슨 상관관계(r)를 의미하는 상관계수이다. 피어슨 상관 계수(Pearson Correlation Coefficient ,PCC)란 두 변수 X 와 Y 간의 "선형 상관 관계"를 계량화한 수치다 . 피어슨 상관 계수는 코시-슈바르츠 부등식에 의해 +1과 -1 사이의 값을 가지는데, +1은 완벽한 양의 선형 상관 관계, 0은 선형 상관 관계 없음, -1은 완벽한 음의 선형 상관 관계를 의미한다. 상관 계수에 따른 분산도 (-1 ~ +1) 통계학 상관 계수인 피어슨 상관 계수를 구글 스프레드 시트에서 함수로 제공한다. =PEARSON(종속 데이터.. 2021. 8. 30.
[구글 스프레드시트] SQL Query문을 구글스프레드 시트에서~ 구글 스프레드시트에서 를 통해 SQL 쿼리문과 같은 기능을 사용할 수 있다. query 함수는 아래와 같이 사용. = query(사용할 데이터 범위 설정, 쿼리문) 필자는 SQL문을 어디에 사용했느냐, 매주 상품별로 발주를 넣는데, 100개가 넘는 브랜드 중 팀원마다 맡은 브랜드가 있다. 각 팀원별로 맡은 브랜드만 확인하면 되니까 Query 함수를 써서 해당 브랜드만 각 담당자별 시트탭에 데이터를 뿌려줬다. ↓실제 시트 화면↓ 우선 상품별로 발주가 필요한지 여부를 체크해 주는 '상태부여' 탭을 만들고 (한달 동안 판매된 수량과 재고 수량 비교하여 발주할 수량 정해줌) 각 담당자들 탭에 query 문으로 데이터 뿌려주기 =QUERY('상태 부여'!A2:I,"select * where B='에뛰드 하우스' .. 2021. 5. 14.
[구글 스프레드시트] 중복 제거 함수 unique 구글 스프레드시트에 아주 유용한 함수 중 하나인 unique 중복 제거 함수이며 엑셀에서 중복된 항목 제거라는 기능과 동일. 에서 중복제거 하는 방법 중복 제거 할 열을 선택한 후 "데이터 -> 중복된 항목 제거" 에서 중복 제거 하는 방법 * unique 함수 사용 =unique(A2:A) ㄴ A2:A 범위에서 중복된 값은 제거하고 unique하게 딱 하나씩만 반환해! 라는 의미 *물론 구글스프레드 시트에서도 unique 함수를 사용하지 않고 중복 제거 하는 방법 있음 데이터 -> 중복 항목 삭제 1. 행 데이터도 중복 제거 가능 unique 함수를 사용하면 "열" 데이터만 중복 제거 가능한 것이 아니고 "행" 데이터도 중복 제거 가능하다는 사실~ =unique(A1:J1, "true") =unique.. 2021. 4. 29.
[구글 스프레드시트] if 함수 사용법 / if 중첩 정말 많이 쓰이는 함수 if if 함수는 조건에 맞는지 안 맞는지 따져본 후 맞으면 A를 반환, 안 맞으면 B를 반환 해주는 함수입니다. = if (조건문, 조건이 맞으면 반환할 A, 조건 안맞으면 반환할 B) = if (조건문, 조건이 true 일 때 값, 조건이 False 일 때 값) ex ) =if (A1>40, "통과", "탈락") = A1이 40보다 크면, "통과" 출력 A1이 40보다 크지 않으면, "탈락" 출력 * if 응용편 -> if 중첩 =if(A1>80,"A",if(A1>=60,"B","C")) = if (조건문, 조건이 true 일 때 값, 조건이 False 일 때 값) =if (A1>80, "A" , if(A1>=60,"B","C")) -> A1 이 80보다 작을 때 if(A1>=.. 2021. 2. 26.
[구글 스프레드시트] 공백 제거 세가지 방법 (trim, mid, 값바꾸기) 구글 스프레드 시트에서 상황별로 공백을 제거하는 세가지 방법! 첫 번째. trim 함수 이용하기 trim 함수는 문자열의 맨 앞과 맨 뒤에 있는 공백을 제거해 줍니다 이 방법은 도구 모음에 있는 데이터 -> 공백제거 기능과 동일한 기능입니다. 두 번째. ctrl + h 찾기 및 바꾸기 위의 trim 이나 도구모음의 공백제거 기능은 문자열의 맨앞, 맨뒤의 공백만 제거가 가능합니다. 그럼 문자열의 중간에 있는 공백을 없애고 싶을 땐 ? 단축키 ctrl + h 를 누르면 찾기 및 바꾸기가 나오는데 이걸 활용! "현영아 안녕" 이걸 "현영아안녕" 이렇게 바꾸고 싶을 땐 ctrl + h 찾기 및 바꾸기에서 찾기 칸에는 "아 " 완료 버튼을 누르면 "현영아 안녕" -> "현영아안녕" 이렇게 변경됩니다. 숫자 중간에.. 2021. 2. 26.
[구글 스프레드시트] 이거 은근 많이 쓰여 - countif 응용편 countif 응용편 입니다. 직딩 실무 필수 함수3편 - countif , countifs 을 보고 오시면 더 이해가 잘 될거에요 :) [구글 스프레드시트] 직딩 실무 필수 함수3 - countif , countifs 직딩 실무 필수 함수 3탄!! - countif , countifs 편입니다 :) countif 는 count + if 로써 말그대로 조건에 부합하는게 몇개인지 카운트 해달라는 의미 입니다. . . . . =countif(범위지정. devfairy.tistory.com 자 그럼 다짜고짜 퀴즈를 내보겠습니다 왼쪽 범위에 여러 값들이 존재하네요. 오른쪽엔 내가 찾고싶은 값들이 있어요. 내가 찾을 값이 왼쪽 범위에 존재하는지, 존재하지 않는지 어떻게 알 수 있을까요? 지금은 값이 몇개 없어서 .. 2020. 10. 30.
[구글 스프레드시트] vlookup 마스터하기 실무에 빠질 수 없는 구글 스프레드시트 함수 vlookup! 이 함수는 엑셀에서 동일하게 적용됩니다 :) vlookup 은 어떨때 사용하느냐? -> 단어를 검색해서 해당 단어의 오른쪽에 있는 값(데이터)을 가져오고 싶을 때! . . 예시를 들어드릴게요 . . ex1) 아래 화면을 봅시다. B9 셀에 3월 식비 지출이 얼마인지 vlookup 을 사용해서 바로 찾아오고 싶을때? A9셀에 적혀있는 "식비" 라는 단어가 가계부 테이블에도 존재하기 때문에 이 단어를 가계부 테이블에서 검색해서 찾은 후 오른쪽에 있는 값을 출력 시킬 수 있습니다 =VLOOKUP(A9,A2:D5,4,0) -> 의미해석 : VLOOKUP("식비"라는 단어 검색할거야, 어디서? 가계부테이블에서 , "식비" 단어 찾았어? 그 셀부터 세어서.. 2020. 10. 24.
[구글 스프레드시트] 조건 성립하면 카운트 해 - countif , countifs countif , countifs countif 는 count + if 로써 말그대로 조건에 부합하는게 몇개인지 카운트 해달라는 의미 입니다. . . . . =countif(범위지정, 조건) =countif(지정한 범위에서, 이 조건에 해당되는것 몇개인지 카운트해줘) =countifs(범위지정1, 조건1,범위지정2, 조건2, .... ) -> 지정한 여러가지 조건을 모두 부합하는 것만 카운트! . . . . countif 사용 예시 A1:B22 테이블에 날짜별 방문자 이름이 정리되어 있습니다.D열에 있는 방문자들은 각각 총 몇번 방문했는지 countif 를 사용해서 구해봅시다 E2 셀에 이렇게 적혀있네요 =countif(B2:B22,D2) =countif(지정한 범위에서, 이 조건에 해당되는것 몇개인지 .. 2020. 10. 24.
[구글 스프레드시트] 조건 성립하면 sum해 - sumif , sumifs 직딩 실무 필수 함수 ! sumif , sumifs 편입니다 :) 이 함수는 엑셀에서 동일하게 적용됩니다 SUMIF 와 SUMIFS 는 내가 설정한 조건에 해당되는 값들만 골라서 SUM 해주는 함수입니다. =SUMIF(조건을 찾을 범위 , 조건, 조건이 성립할 때 SUM할 범위) =SUMIFS(조건이 성립할 때 SUM할 범위, 조건을 찾을 범위1 , 조건1, 조건을 찾을 범위2 , 조건2, .......) SUMIFS 는 조건을 여러개 넣어주는 것이기때문에 맨뒤에 S가 붙었습니다 SUMIFS 에서는 여러가지로 넣어준 조건들을 전부 성립하는 값들만 더해줍니다. SUMIF 예시 A1:B8 테이블에 여러 카테고리가 섞여있는데요, D1:E6 테이블에 정리해줘볼까요? 우선 D열에 카테고리를 정리해서 적어주고, E.. 2020. 10. 24.
728x90

티스토리툴바