상대 참조와 절대 참조 이해하기
셀에 있는 함수의 값을 복사해서 다른 셀에 복사해도 함수가 그대로 복사됨
그리고 D2에서 D3도 바뀜. 참조 대상이 바뀌는데 이것을 상대 참조라고 함
=OR(G2="Unit1", I2="Cari Bauch")여기서 Cari Bauch 라는 이름을 쓰지 않고 셀을 쓰고 싶다면, 그냥 클릭을 하면 됨
=OR(G2="Unit1", I2=I29)이렇게 바뀌는데,
원래 함수에서 절대 참조하고 싶은 영역을 잡고 쉬프트 누르고 선택한 뒤 F4를 누르면 $표시가 생김
=OR(G2="Unit1", I2=$I$28)F4를 계속 누르면 $의 위치가 변함
이 셀을 복사해서 다른 셀에 붙이면 셀의 위치를 표현해주는 상대적인 위치는 바뀌지만 $가 붙은 숫자는 바뀌지 않음
이게 절대참조
이렇게 옆으로 빼서 노란 칸에 해당하게 두고,
=OR(G2=$O$1, I2=$O$2)F4를 눌러서 입력한다. 이걸 복사하면, O열에 해당하는 셀과 비교하며 값이 생성된다.
절대 참조하는 것을 바꾸면 참조하는 값도 바로 바뀜
다양한 방법으로 데이터 복사와 붙여넣기
다른 값으로 붙여넣기 하면 노란색이 없어짐
이럴 때는 값으로 붙여넣기 기능 활용
노란색 없어지지 않고 값만 바꾸려면 오른쪽 클릭 후 값만 붙여넣기
셀 클릭 후 컨트롤 알트 사이의(텐키리스의 경우 오른쪽 컨트롤의 왼쪽)누르고 선택하여 붙여넣기(S) -> 값만(V)
수식만 붙여넣는 경우
함수 붙여넣기 시 배경 노란색 건드리고 싶지 않은 경우
복사 후 붙여넣기 할 셀에 수식만 붙여넣기!!!
서식(배경)만 복붙 가능
서식 붙여넣기 있음
행/열 바꿔서 붙여넣기하면 행과 열이 바뀌어서 붙여넣어짐
함수를 행열 바꿔서 붙여넣으면 함수가 바뀌어 제대로 작용하지 않음
그래서 행/열이 전환되면서도 제대로 작동하게 하려면 붙여넣을 때 선택하여 붙여넣기(S) -> 값(V) -> 행/열 바꿈
VLOOKUP 함수 - 범위에서 값 찾기
다른 두 개의 데이터(거래, 고객사)가 있을 때 이 둘을 합칠 때 사용하는 함수
고객사가 어떤 기업이고 어떤 대륙에서 활동하는 등 추가적 정보를 알 수 있음
상세정보(어떤 대륙 등)를 거래 데이터에 적지 않는 이유는 한 회사가 여러 번 나타나기 때문이다.
-> 데이터 중복 일어나서 수정 시 적힌 곳마다 찾아가서 수정해야함
하나의 데이터는 한 곳에만!
또 해야한다면 참조에서만.
고객사 시트에는 고객사 내용만 나옴
이걸 합치는게 VLOOKUP
VLOOKUP(lookup: 무엇을 찾아올지, 어디에서 찾아올지(다른 시트, 드래그해서 잡음): table arry, col_inde_num : 몇 번째걸 가져올지)
=VLOOKUP(B3, 고객사!A:C,2,0)VLOOKUP 아래 찾을 내용을 고르고 반드시 그 시트에서 쉼표, 그 다음 다른 시트에서 영역을 선택하고 맨 왼쪽 열(1)로부터 몇 번째 열을 가져올지 숫자를 적음, 맨 마지막엔 True도 되지만 그냥 0
찾을 내용이 가장 왼쪽에 있어야 함
만약 고객구분이 왼쪽에 있다면?
=VLOOKUP(B38,고객사!A:C,2,0)lookupvalue가 있는 가장 왼쪽의 위치는 B이므로 함수 안에서 변경해야함
=VLOOKUP(B2,고객사!B:C,2,0)
=VLOOKUP(B3, 고객사!A:C, 3, 0)
VLOOKUP 할 경우 기준이 되는 열은 가장 왼쪽에!
VLOOKUP 함수 - 혼합참조 이해하기(행고정, 열고정)
혼합창조 - 상대참조 + 절대참조를 혼합한 것
상대참조 : =누르고 어떤 셀을 선택했을 때 동작하는 것
하나의 셀만 복사 시 상대참조/절대참조 구분되지 않음
쉬프트 눌러서 붙여넣기 하면 상대참조 동작하는 것 볼 수 있음
복사했던 셀은 B2 참조하고 있었고 아래에는 B3이었고 오른쪽은 C3을 참조하고 있었음
복사되어져서 붙여넣어진 위치에 따라 참조 대상이 바뀌는 상태
절대참조 - = 누르고 참조대상 누른 상태에서 F4 누르면 컬럼이름과 행 이름에 $가 붙는다!
이 상태에서 엔터를 누르면 상대참조와 차이를 모르지만 셀 복사하고 아래, 오른쪽 2칸씩 복사하면
모두 같은 값이 같음
상대참조에서는 column, row가 알파벳과 숫자가 바뀌기 때문에 참조대상이 바뀌지만 절대참조는 바뀌지 않음
혼합참조는 이 둘을 합친 것
Row나 Column 중 하나는 고정하고 나머지 하나만 바뀌는 것
=$B$2
# 열, 행 순여기서 Row를 고정하고 싶다면, 고정하고 싶은 곳의 $는 그대로 두고, 바꾸고 싶은 것의 $만 지워주면 됨
행은 변하지 않을 거고, 열은 변할것이다.
=B$2=B$2 B는 고정하지 않았기 때문에 오른쪽에 복사해서 넣으면 C$2, D$2로 바뀜
Row 고정뿐만 아니라 Column 고정도 있음
=$B2컬럼이 고정됨. K에서 L로 열이 바뀐다고 해서 B가 바뀌는 것이 아님
숫자 앞에는 $가 없으므로 그 아래를 복사한 경우 B3, B4로 바뀜
혼합참조는 row/column을 하나만 바꾸는 것
VLOOKUP 시 Columns을 고정하는 혼합참조를 많이 쓰게 됨
참조 유형을 손쉽게 바꾸는 방법
더블클릭으로 편집모드로 하고 F4를 눌러서 하나씩 변형시키면 됨
특강 - Kaggle에서 데이터분석하기
전통적인 방식과 머신러닝의 차이
사람이 기존의 데이터를 기반으로 규칙을 정하고 이를 프로그래밍하여 작성
입출력 사이의 관계를 사람이 찾는 방식
유지보수의 한계(사람이 직접 해야함)
새로운 데이터가 기존과 다른 패턴을 가질 경우 반영하기 어려움
깔끔한 코드 작성 어려울 수 있음
VLOOKUP 함수 - 다른 시트(Sheet) 값 가져오기
VLOOKUP이 J 칼럼에 적혀있을 때 첫 번째 값은 찾아야 할 값, table array: 어디에서 찾아올 것인가, 왼쪽부터 몇번째꺼, 0)
table array를 전체 영역 말고 특정 영역으로 했을 경우 다른 시트에 있는 순서와 달라서 에러가 날 수 있음
VLOOKUP은 절대참조
절대참조한 셀을 옆 열로 복붙해도 상대참조가 되어 B에서 C로 칼럼 레이블이 변경됨
LOOKUP value는 $로 고정해주는게 좋음
LOOKUP Value는 테이블을 고정하고, table array는 전체 영역을 절대참조하는 방식으로 구성하는 것이 좋음
VLOOKUP의 세번째 값인 col index num 구하는 법!
만약 참조하는 열의 정보가 엄청 많다면?
옆으로 갈수록 2, 3, 4처럼 1씩 증가시키는 법? -> 위에 하나의 줄을 증가시키고 2참조하는 곳 위에 2라고 씀
=L1+1 쓰고 드래그하면 됨
이것도 J1을 절대참조로 해야함
J$1 이런 식으로 혼합참조하면 됨
=VLOOKUP($B$3,고객사!$A$1:$C$539, $J$1, 0)
=VLOOKUP($B3,고객사!$A$1:$C$539, K$1, 0)
=VLOOKUP($B3,고객사!$A$1:$F$539, L$1, 0)열, 행 순서임
보통 참조할 때 열은 고정!
VLOOKUP 활용 - COLUMNS과 ROW 함수
COLUMN(), ROW() 함수는 각각의 열과 행의 위치를 알려줌
="주소"&ROW()&는 텍스트를 붙이는 것
다 주소였는데 주소2로 나옴
=ROW()&"@email.com"
VLOOKUP 활용 - 열번호 동적 적용(With COLUMN)
다른 시트에 있는 여러 개의 Column을 가져와야 하는 경우에, 이름마다 추가적인 정보가 있음
여러 개의 정보를 가져올 때 위에 있는 숫자들을 활용해서(=2, =J1-1 등) 만들 수 있음
=COLUMN()-8로 만들어서 2, 3, 4 등을 만들었고, 이 대신
=VLOOKUP($B4,고객사!$A$1:$C$539,COLUMN()-8, 0)
J1 -> COLUMN()-8로 바꿈
원래는 아래와 같았음
=VLOOKUP($B3,고객사!$A$1:$C$539,J$1, 0)=VLOOKUP($B25,고객사!$A$1:$C$539,K$1, 0)모든 셀에 같은 함수 적용, 맨 위의 줄 지워도 됨
셀을 옮겨도 lookup value만 변경됨
COLUMN 함수 쓰면 같은 수식을 전체에 대해 쓸 수 있다
VLOOKUP 활용 - 특정 데이터 가져오기(With MATCH)
데이터에 해당하는 열이 엄청 많을 때 특정 컬럼 옆에 다른 데이터를 가져오고 싶을 떄
대륙 열과 컬럼M 정보만 가져오고 싶을 때?
MATCH 함수 : 배열에서 지정된 수서상의 지정된 값에 일치하는 항목의 상대 위치 값을 찾는다.
>>> 이름을 찾아라 A~F까지에서 정확히 일치하는 것(0)
=MATCH(C1, A1:F1, 0)
=MATCH("대륙", A1:F1, 0)
# 결과 같음
영역에서 해당하는 값이 몇 번째에 있는지 알려주는 값
lookup value를 table array에서 몇 번째에 있는지 알려줌
찾아오는 영역 정할 때는 절대 참조를 하는 게 좋음
=MATCH(J2,고객사!$A$1:$M$1, 0)
전에 COLUMN()-8을 한 경우 이렇게 데이터를 지우면 6도 바뀌었을 것인데, 이건 그렇지 않음
COLUMN()은 연속적인 경우에만 사용할 수 있음
특정한 원하는 것을 가져오고 싶을 때는 MATCH 사용 가능!
=VLOOKUP($B3,고객사!$A$1:$Z$539,MATCH(J$1,고객사!$A$1:$M$1, 0), 0)중요함..
행이 내려갈 때 열이 바뀌고 행이 고정돼야 하니 행 앞에 $ 붙여야 함
VLOOKUP 활용 - 병합 셀 불러오기
병합 셀 형태로 데이터 표현돼있을 때 VLOOKUP 사용하는 방법
병합하고 가운데 맞춤된 셀들은 해지해보면 어떤 구조인지 알 수 있음
가장 위에 값이 들어가있는 형태
병합된 셀의 첫번째 줄에만 데이터가 있음
C2에 =A2로 놓고 복붙하면 해당 행에만 값이 있는 걸 알 수 있음
isblank 함수로도 확인가능
=IF(C2,D1,A2)C2는 =ISBLANK(A2)라서 A의 2번째줄이 비어있는지 본거임. 비어있으면 그 위의 값 참조
근데 안비어있어서 그 값 참조함
나머지 2~4번째 줄은 다 비어있어서 A2의 값을 가져와서 쓴 것임
의류도 마찬가지
의류의 경우에도 D6가 IF(C6, D5, A6)에서 C6은 A6가 ISBLANK인지 아닌지 확인하고 아니니 의류라는 값을 가져옴
그 아래는 ISBLANK가 TRUE이니 위의 값을 가져옴
VLOOKUP함수에 구매항목 클릭하고, 왼쪽에 만들어 둔 표를 절대참조하고 식품인지 의류인지 찾을건 왼쪽으로부터 3번째니 3, 0 을 누르면 구매항목의 카테고리를 알 수 있다.
VLOOKUP 함수에서 발생하는 오류를 수정하는 방법
찾고자 하는 영역이 테이블 안에 없으면 #N/A 발생 -> 찾고자 하는 값이 없을 때
잡은 영역에서는 A~E까지 5개의 칼럼만 있는데 6번째 값을 갖오라고 하면 참조할 수 없다는 REF 오류 발생 -> 범위를 넘어가는 숫자를 넣었을 때
=VLOOKUP(H3, "a", 6, 0)범위가 들어가야 하는데 값이 들어간 경우 VALUE 에러 -> 뭔가 잘못된 값을 넣었을 때
오류를 넣은 함수는 모두 NA
IFERROR 넣어줌
만약 에러가 나면 뭘로 바꿔주세요 하는 함수
=IFERROR(VLOOKUP(H2, $A$2:$E$20, 2, 0), 0)IFERROR를 썼을 때 그냥 넘어갈 수 있기 때문에 IFNA를 쓰는게 좋다
XLOOKUP 함수 - 원하는 범위에서 값 찾기
VLOOKUP 해야 되는데 기준이 되는 셀의 왼쪽에 데이터가 있을 경우 사용
일치 항목에 대한 범위에서 검색하고 두 번째 범위에서 해당 항목 반환, 기본적으로 정확하게 일치하는 항목이 사용됨
범위가 두 개 주어짐
첫 범위 두 번째 범위
XLOOKUP(look value, lookup array, return array)
lookup array, return array가 두 개의 범위가 되고 lookup value가 찾고 싶은 값임
=XLOOKUP(A2,고객사!$F$1:$F$539,고객사!$A$1:$A$539)VLOOKUP은 범위만 하나만 주고 오른쪽으로 몇 번째 가서 찾아와라 하는 거고 XLOOKUP은 찾을 값을 주고 찾아야 할 범위를 준다. 몇 번째 나오는지 기억하고 오른쪽으로 가는게 아니라 몇 번째 가는지를 기억하고 return array에 해당하는 두 번째 범위에 가서 몇 번째인지 불러옴
다른 칼람을 찾고 싶으면 뒤에 오는 범위의 알파벳만 바꿔주면 됨
=XLOOKUP(A2,$L$2:$O$2, $L$3:$O$3)INDEX와 MATCH 함수 - 다중 조건값 찾기
MATCH : 주어진 목록에서 해당하는 값이 몇 번째에 있는지 알려주는 함수
INDEX : 주어진 배열에서 내가 몇 번째걸 가져오라고 할 때 쓰는 것
=MATCH(A2,고객사!$F$2:$F$539,0)A2 셀에 해당하는 값이 고객사의 F열에서 어디에 있는지 찾아달라는 것
LEFT/RIGHT/MID - 문자열에서 원하는 글자 추출하기
=LEFT(A2,2)
=RIGHT(A2,2)
=MID(A2,3,3)A2 셀에 있는 왼쪽 2글자, 오른쪽 2글자, 3번째 글자로부터 3글자
끝이 lnc라는 회사를 가져오고 싶을때
=RIGHT(A2,4)=" Inc"오른쪽 4글자가 공백+Inc가 맞으면 TRUE 아니면 FALSE
TEXTSPLIT 함수 - 문자열 분할하기(with TRIM)
텍스트를 행또는 열로 분할
=textsplit(분할하고 싶은 값, 무엇으로 분할할지) -> 지원이 안됨
=trim(텍스트 양 옆에 있는 공백을 없애줌)
=INDEX(textsplit(TRIM(A2)," "), 1)공백으로 나눈 것의 첫 번째 단어만 가져옴
trim으로 앞 뒤 공백 제외
단어 사이의 공백으로 나웠을 때 첫 단어만 가져옴
FIND 함수 - 문자의 위치 찾기(with LEN)
함수의 조합을 인덱스와 textsplit, trim을 통해 첫 단어 공백 제거하고 가져왔듯 Left, find 이용하면 동일하게 가져올 수 있음
찾을 문자, 어느 위치의 글자에 있는지 순서로 입력
주어진 글자가 있는 곳을 찾아줌
그걸 A3에서 찾았다면 그 위치를 알려주고 그거 -1만큼 왼쪽에서 가져오면 공백이 있는 단어 기준 왼쪽 단어를 가져올 수 있음
COUNTA 함수 - 비어있지 않은 데이터의 수 구하기
주어진 배열에 몇 개의 데이터가 있는지 셈
=COUNTA(A2:A6)
=COLUMNS(A2:C2)를 하면 주어진 행에 몇 개의 열이 있는지 알려줌
=COLUMNS(A2:C2)=INDEX(textsplit(A4, " "),E2)공백 세고 그 숫자를 가지고 가장 마지막 단어 가져오기