Vlookup

Excel – 수식 : Vlookup, Match, Index, 다중조건 데이터 찾기

안녕하세요 채랑이 아빠 프리아입니다. 오늘도 시간을 잠시 내서 글 쓰러 왔습니다 ^^. 계속해서 엑셀 관련 글을 쓰게 되네요 ^^. 간단하게 글쓰기엔 엑셀이 참 좋아요.오늘도 데이터 검색에 대한 팁을 하나 더 드리려고 합니다. Vlookup 명령어를 쓰다보면, 가끔 여러가지 조건을 넣은 검색을 해야 하는 경우가 있습니다.여러가지 조건을 넣은 합계나 평균의 경우 DSUM이나, DAVERAGE 명령으를 쓰면 되지만, 데이터를 찾아서 가져오는 것은 별도의 얘기죠.그래서 다중 조건 검색하는 방법을 설명 드리겠습니다. 오늘도 샘플 데이터를 가지고 설명 드릴께요.데이터는 랜덤으로 만들어져 있습니다. 가격차가 심하다구요? 네 랜덤으로 그냥 3만원 미만의 단가를 집어 넣었습니다. ^^아 랜덤 데이터 생성하는 것도 나중에 한번 다뤄봐야겠네요 ^^ 자 이번에는 문제를 내야 되겠죠? 가장 많은 부품명이 음…. HHHH 인것 같네요. 그럼 HHHH 부품 중 2022년 11월 11일에 들어온 자재의 단가를 확인해 보겠습니다. 2가지 조건이 들어간 문제로 테스트를 해보겠습니다. 자 Vlookup을 사용하여 데이터를 찾으려고 한다면, 보이는 테이블에서 바로 수행은 어렵습니다.네 하나의 열을 추가하여 검색을 하셔야 합니다. Vlookup 명령어의 기본은 찾는 데이터가 있는 열이 맨 앞에 있어야 합니다.즉, 단가의 데이터를 찾으시려면 A열의 앞과 뒤에 열을 추가해야 합니다. 열을 추가하셨으면, 질의 에 대한 서치 데이터를 만드셔야 겠죠?저는 A열 뒤에 열을 추가하고, B열에 다음의 수식을 반영하였습니다. =A2 & F2 그리고 복사해서 다 채웠어요그리고 아래와 같이 Vlookup 명령어로 데이터를 찾았습니다. =VLOOKUP(L4&L5,B2:C23,2,FALSE) 결과 화면은 다음과 같습니다. 결과는 보시다 시피 잘 나왔습니다.다만, B열에 신규 열이 추가되었습니다. 간단하게 데이터만 사용하는 경우에는 이렇게 작업해도 아무런 문제가 없겠지만, 여러사람이 봐야하는 데이터나 열을 추가할 수 없는 상황에서라면 이렇게 작업할 수 없게 됩니다.이럴때 사용하는 수식이 Match와 Index을 복합적으로 사용하는 것입니다. Index 수식은 배열에서 원하는 좌표를 반영하여 데이터를 가져오는 수식입니다.수식의 사용방법은 다음과 같습니다. =INDEX(array, row_num, [column_num]) 여러번 설명 드렸지만, array는 원하는 범위입니다. 그리고 row_num은 행의 번호이며, column_num은 열의 번호입니다.즉, 원하는 범위 내에서 행 번호, 열 번호에 해당되는 데이터를 출력하는 것입니다.위에 표에서 예를 들어 드릴께요. =INDEX(A2:G23,3,1) 범위 A2:23에서 3번째 행의 첫번째 열은 JJJJ값이 됩니다. 수학적인 표를 보는 것과 동일한 것입니다. 이번에는 Match 수식에 대해 설명드리겠습니다. Match는 요청하는 데이터가 몇번째에 있는지 찾아내는 수식입니다.수식의 사용방법은 다음과 같습니다. =MATCH(lookup_value, lookup_array, [match type]) 첫번재는 lookup_value입니다. 찾는 값이죠. 두번째는 lookup_array입니다. 찾는 데이터를 검색할 범위 입니다. 단, 이 범위는 행이면 1개의 행에서 진행되어야 하며, 열로 찾을 것이면 1개의 열에서 진행되어야 합니다. 여러개의 열과 행을 동시에 적용할 수 없습니다. 마지막으로 match type인데요, 이것은 숫자를 검색할 때 많이 사용하는 기능입니다. match type는 0, -1, 1이며, 0은 일치하는 값, -1은 작은 값, 1은 큰 값을 찾는 역활을 합니다.문자를 찾을 경우에는 반드시 0으로 검색해야 합니다. 이것도 예를 들어보겠습니다. =MATCH(“BBBB”,A2:A23,0) 자 A2에서 A23 범위 안에서 “BBBB”라는 부품이 몇번째 있냐는 질문입니다. 위에서부터 4번재에 있죠. 이것도 Vlookup과 마찬가지로 위에서부터 검색하므로, 여러개가 있을 경우 맨 위의 위치 값을 리턴합니다. 여기까지 듣고 나서 Match와 Index를 어떻게 써야할지 감이 잡히시는 분들도 계실 겁니다. 일단은 다중 조건 검색 이전에 Vlookup 명령어와 동일하게 사용되는 화면을 먼저 보여드리겠습니다.간단하게 질문 내용은 CCCC 부품의 단가로 찾아 보도록 하겠습니다. 자 결론부터 말씀드리면, 동일한 데이터가 출력된 것이 보이시죠? 맞습니다. Vlookup 명령어는 이렇게 Match와 인덱스 명령어를 섞어서 만들어진 수식입니다.그래서 동일한 결과가 나오게 되는 것입니다. Vlookup 명령어는 다시 설명 드리지 않겠습니다.Match와 Index를 사용한 수식을 설명드리겠습니다. 먼저 Index 명령어를 분석하면 밑에처럼 A1:G23 범위 내에서 행번호 열번호를 입력하도록 하는 것입니다.INDEX(A1:G23,행번호,열번호) 행번호는 MATCH(K4,A1:A23,0)입니다. 즉 찾는 데이터는 K4 즉 “CCCC”라는 부품명이죠, 범위는 A열이구요. match type는 당연히 일치(0)입니다. 그러면 CCCC가 있는 7이라는 값이 리턴되겠습니다. 마지막 열 번호는 단가가 있는 2열을 직접 입력하셔도 되고, MATCH(“단가”,A1:G1,0)으로 찾으셔도 됩니다. 1번행에서 “단가”와 일치하는 열 위치를 찾으니, 당연히 2라는 값이 리턴되겠죠. 그래서 인덱스에서 11,2의 데이터인 24,200의 값이 출력되게 됩니다. 자 이제 Match, Index 명령어에 대해서 감을 잡으셨지요?그럼 이번 글의 주요 핵심인 다중조건 검색을 진행해 보도록 하겠습니다. 수식은 다음과 같습니다. =INDEX(array, MATCH(lookup_value1&lookup_value2,lookup_array1&lookup_array2,0),column_num)=INDEX(데이터 범위, MATCH(조건1&조건2, 조건1의범위&조건2의범위,0),열번호) 원래는 엑셀에서 주는 조건을 입력하는데 이해하기 힘드실까봐 한글로도 내용을 써 드렸습니다.우선 Index 명령어 사용은 위의 1가지 조건으로 검색하던 것과 동일합니다. 대산 Match명령어는 조금 변경되었습니다. 우선 찾는 데이터가 조건1 & 조건2로 바뀌었습니다. 조건들 사이에 &가 들어가는 겁니다.또한 찾는 데이터를 검색할 범위 또한 범위1 & 범위2로 바뀌었습니다. 당연히 순서는 바뀌면 안되겠습니다. 그외에 match type은 동일하게 0(일치)으로 적용하면 됩니다. 여기서 아셔야 할 점은 2가지 조건이 아니라 조건이 추가되는 경우에도 위와 동일하게 적용할 수 있습니다. 즉, 조건1&조건2&조건3&……으로 하고, 범위1&범위2&범위3&….으로 하셔도 결과가 나온다는 겁니다. 이렇게 사용하면 열을 추가하지 않고도 다중 조건의 데이터를 검색할 수 있습니다. 다만, 이 경우는 최신 엑셀에서 적용되는 수식이라는 점입니다. 저의 경우 Office 2022를 사용하고 있어 데이터가 잘 나오는 것입니다.그렇다고 이전 버젼에서는 실행되지 않느냐 하면 그렇지는 않습니다. 다만, 식을 입력하고 ctrl+shift+enter를 눌러야만 적용된다는 점만 다릅니다. ctrl+shift+enter을 누르면 수식의 양 옆에 “{ }”가 추가된 것을 볼 수 있습니다. 예전에는 저렇게 하지않으면 데이터가 출력되지 않았으나, 최신버젼에서는 일반 수식처럼 입력해도 동작을 하도록 바뀌었습니다.참고로 말씀드리면, ctrl+shift+enter을 사용할 경우 파일의 용량이 커지며, 여러개를 사용할 경우 속도가 느려질 수 있다는 점 유의하여 주시기 바랍니다.아 물론 최신 버젼의 경우 다중조건 수식이 포함되는 것만으로도 용량이 커집니다. ㅎㅎㅎ. 자 이렇게 오늘은 다중조건의 데이터 검색을 알아보았습니다. 좋은 팁이 되었기를 바랍니다. 오늘도 좋은 하루 되세요 ^^

Excel – 수식 : Vlookup, Match, Index, 다중조건 데이터 찾기 더 읽기"

Excel – 수식 : Vlookup, Lookup 마지막 값 찾기

안녕하세요, 채랑이 아빠 프리아 입니다. 어제보단 조금 시간적 여유가 있네요 ㅎㅎㅎ. 어제 고객사 손님이 왔다가 갔지만, 특별한 이슈사항은 없었네요. 참 다행입니다 ㅎㅎㅎ. 오늘도 엑셀에 대한 팁을 하나 더 드릴껀데요.Vlookup함수의 단점이 무엇인지 알고 계신가요? 아시는 분들은 다 아실텐데요… 그것은 바로 위에서부터 내용을 검색하기 때문에 동일한 데이터가 2개 이상인 경우 맨먼저 나오는 답만을 리턴한다는 것입니다.아래와 같이 나오게 되겠죠 보시면 aaa라는 부품이 3번 반복해서 나오지만, Vlookup 명령어를 쓸경우 맨 위에 aaa에 대한 데이터를 리턴하게 됩니다. 일단 오늘 하고자 하는 것은 맨위가 아닌 맨 아래 데이터를 리턴하는 방법을 써보려고 합니다.물론 이 경우에는 Vlookup 명령어가 아닌 Lookup 명령어를 써야 합니다. Lookup 명령의 배치는 다음과 같습니다.Lookup(lookup_value, lookup_vector, [result_vector]) 또는 Lookup(lookup_value, array) 이 두가지 방식입니다.그런데 이중 가장 이해가 안될 부분은 vector이라는 문구이겠죠?이과나 공대를 나오신 분들은 벡터라는 말을 한번씩은 다 들어보셨을 겁니다. 쉽게 말해 절대치의 값 정도로 이해하시면 될텐데요, 음 이것도 어렵낭….조금 설명하기 애매한 개념이라서 설명이 좀 어렵긴 하네요.일단 다르게 말씀드릴께요. 동일한 데이터가 있을 경우 맨위에꺼 하나 말고는 다 무시한다입니다.이것도 어렵죠 ^^;;; 다르게 말하면 문자를 그대로 보지 않고 독특한 순위와 글자로 분석하여 별도의 언어로 구분한다입니다.숫자는 쉽게 인식하지만 문자는 별도로 분석해 버린다는 거죠. 어렵죠? 네 압니다. ㅎㅎㅎㅎ 그래서 예를 들어 드릴께요. 자 Lookup 명령어를 써봤습니다. 그냥 보면 Vlookup과 동일하죠? 무엇이 문제인지 전혀 모르시겠죠? ^^그럼 맨위의 데이터의 aaa를 삭제해 보겠습니다. 보이시죠? Vlookup 명령어는 바로 다음의 aaa 값을 찾아서 리턴하지만, Lookup 명령어는 답이 없다고 나와 버립니다. 즉 문자를 벡터화 시키면서 해당 내용을 찾지 못하는 현상이 발생하는 것입니다. 이번에는 aaa를 숫자로 변환하고 찾는 부품도 숫자로 변환해 보겠습니다. 자 aaa를 숫자로만 변환했을 뿐인데 Lookup 함수가 작동하죠? 숫자는 이미 벡터화 되어 있기 때문에 문자의 벡터화를 하더라도 그대로이기 때문입니다.이것이 벡터입니다. 이래도 모르신다면 그냥 포기해야죠 모 ㅋㅋㅋ. 쉽게 말해 Lookup 명령어를 쓰려면 숫자로 치환해야 된다라고 생각하시면 됩니다. 자 자꾸 이상한데로 가는데요.. 오늘은 목표는 마지막 값을 찾는 것입니다.명령어는 다음과 같이 사용하시면 됩니다. =LOOKUP(2, 1/(lookup_array=lookup_value),output_array) 이것도 어려우시죠? 엑셀을 누가 만들었는지 몰라도 참 어렵게 만들어놨습니다.맨 앞의 2는 벡터 2 이하의 숫자를 찾으라는 뜻이라고 보면 됩니다. 여기서 아실 부분은 2 이하의 숫자입니다. 즉, 자연수로 치면 1이나 2입니다.여기서 왜 0이나 음수가 없냐고 물으신다면… 모 다음 구절에서 답을 찾아야죠. “1/” 보이시죠? 1로 나누었을때 0이 나오는 숫자는 없습니다. 아시죠? lookup_array=lookup_value를 쓰시면 리턴되는 것이 true 또는 false이며 벡터화 하면 1 또는 0입니다.즉, 1 아니면 #DIV/0 라는 말이죠. 그러니 음수나 0은 절대 나타나지 않으니 신경쓰지 않으시면 됩니다. 앞의 수를 3이나 4로 바꾸어도 1, 2, 3, 4의 숫자를 찾기에 동일한 결과가 나옵니다. 즉 1보다 큰 아무숫자나 넣으셔도 된다는 뜻입니다. 원활한 설명을 위해 실행한 결과를 볼께요. 자 보시면, Lookup의 명령어는 맨 마지막 값을 찾아온 것이 보이시나요?명령어를 그대로 풀이해서 나타내면 다음과 같아 집니다. =LOOKUP(2,{1,#DIV/0,#DIV/0,#DIV/0,1,#DIV/0,#DIV/0,1},{3000,4000,3500,8000,3200,5000,7000,3800}) 이렇게 해석하게 됩니다. 그중 가장 특이한 점은 중간의 1, #DIV/0 ….. 1 의 데이터를 앞에서부터 찾는 것이 아니라, 뒤에서부터 찾아서 올라간다는 점입니다.이 특이점으로 인하여 맨 마지막 값을 불러올 수 있게 되는 것입니다. 좀 특이하죠? 어려울 수 있습니다. 그래서 그냥 쉽게 가장 마지막 값을 찾고 싶으시다면 =LOOKUP(2, 1/(검색범위=검색값),출력범위) 이렇게 명령어를 써야 한다고 이해하시면 되겠습니다.위에 설명드린 것은 저처럼 특이하신 분들이 왜 저렇게 되는 거지? 3이나 4를 쓰면 다른 데이터가 나오나? 이런 궁금증을 가지실까봐 설명드린 것입니다. 그냥 하나의 팁을 가지고 간다고 생각하시면 되겠습니다. 자 오늘도 여기까지 하겠습니다. 재미있으셨기를 바래요 ^^ 그럼 이만 물러납니당.

Excel – 수식 : Vlookup, Lookup 마지막 값 찾기 더 읽기"

Excel 수식 – Data 찾기 : Vlookup 포함하는 문자 찾기

안녕하세요 채랑이 아빠 프리아 입니다. 어제에 이어 오늘도 무지 바쁩니다. 그런데 자꾸 그런 핑계로 글을 안쓰는것 같아서 짧게라도 글을 쓰려고 잠시 들어왔네요.오늘은 진짜 시간이 너무 없어서 정말 짧은 팁 하나만 던져두고 사라지려고 합니다. ㅎㅎㅎ 이전 글에서 Vlookup을 사용하는 방법을 말씀드렸어요. 그러면서 유사한 데이터 찾기는 잘 안된다고도 말씀드렸죠 ^^(맨뒤에 True나 1을 넣으면 이상한 결과가 나온다는…) 자 그럼 lookup_value에 정확한 내용을 모르고 일부분만 아는 경우 어떻게 데이터를 찾아야 할까요?애매하시죠? 엑셀도 마이크로소프트에서 만들다보니 윈도우랑 비슷하게 해결할 수 있습니다. 윈도우에서 포함하는 문자열을 표현하는 방식은 ‘*’입니다. 아시죠? 예를 들면 모든 데이터를 찾으려고 하면 *.*입니다. 아시죠? 또한 파일명 상관없이 확장자가 같은걸 찾을 때는 *.xlsx로 검색을 해야 하죠. 이것과 마찬가지로 엑셀에서도 동일하게 적용할 수 있습니다. 예를 들어 찾고자 하는 것이 사과를 포함하는 데이터이지만 정확하게 생각이 나지 않는다고 하면 lookup_value에 다음과 같이 입력하시면됩니다. “*사과” 그렇게 하면 빨간 사과, 파란 사과 이런 것들을 인식하게 되는 거죠. 마찬가지로 사과가 중간에 있는 문자열이라고 하면 lookup_value에 다음과 같이 입력하시면 됩니다. “*사과*” 이러면 빨간 사과 상자, 파란 사과 박스 이런 것들을 인식하게 됩니다. 또한 이렇게 글자만 찾는게 아니라 셀에 있는 데이터를 찾는다고 한다면 셀 값에다가 추가로 *을 넣으면 됩니다.즉, A1열의 글자를 찾고자 하나, 정확하지 않다 싶으면 다음과 같이 입력하시면 됩니다. “*” & A1 그러면 앞에 어떤 내용이 추가된 A1 셀을 찾게 되는 것이죠 위의 *사과와 동일한 결과가 됩니다. 마찬가지로 앞 뒤에 추가한다면 다음과 같이 됩니다. “*”&A1&”*” 그러면 위의 *사과*와 동일한 효과를 보게 됩니다. 간단하죠? 그래서 간단한 팁이라고 말씀드린 거죠 ^^ 정말 다른일 하러 가야 해서 짧게 올리고 사라집니다. 다들 행복하세요 ^^

Excel 수식 – Data 찾기 : Vlookup 포함하는 문자 찾기 더 읽기"

Excel 수식 – Data 찾기 : Vlookup, Hlookup

안녕하세요, 채랑이 아빠 프리아 입니다. 어떻게 하다보니 글을 못올린지가 한달이 다 되었네요.. 갑자기 일이 몰려들어서 출장 가고, 자료 모으고, 데이터 뽑고 정신이 없이 한달을 보냈네요.원래는 복귀하면 홈페이지부터 올리려고 했는데, 아직도 일이 끝나지 않았어요 ㅠ.ㅠ 내일 또 고객사에서 손님이 온데서 자료 만들고 있음… 그래서 오늘은 쉬운거 하나만 올리려고 합니다(일하기 싫으면 몇개 더 올릴수도 있어요… 땡땡이는 바쁠때 쳐야 보람이 있으니까요 ㅎㅎㅎ) 아! 그리고, 카테고리가 취미인 점은… 주업무가 아니라 그냥 재밌어서 하는거라 취미로 구분했어요 ㅋㅋㅋ 기분나빠 하진 마시공. 원래는 많이 알려지지 않은 팁을 알려드릴려고 했는데, 그 팁을 사용하지 않을 때의 기본적인 내용을 먼저 설명드려야 할 것 같아서.. 일단 기본적으로 회사에서 가장 많이 쓰는 명령어 중 하나인 Vlookup과 Hlookup을 들고 왔습니다. 우선 아래와 같이 임의의 데이터를 만들었습니다. 우선 Vlookup에 대해서 설명드리겠습니다.Vlookup 데이터가 행으로 나열되어 있는 데이터를 찾는 명령어입니다.명령어의 수식은 =Vlookup(lookup_Value,table_array,col_index_num,[range_lookup])으로 사용합니다.설명을 드리면, – lookup_value : 찾고자 하는 데이터의 구분하는 문자 – table_array : 찾고자 하는 데이터가 포함된 선택 영역 – col_index_num : 찾고자 하는 데이터가 있는 열의 번호 – range_lookup : 데이터의 정확도?? 정도로 이해하시면 됩니다. 무슨 말인지 헷갈리시죠? ㅎㅎㅎㅎ 예를 들어 설명 드릴께요 공급부품 A-Part의 입고된 수량은 몇개 인가요? 라는 질문이 있다고 가정하면 위에서 설명한 내용으로 수식 전개하면 다음과 같습니다. =VLOOKUP(“A-Part”,B1:F7,3,FALSE) 이해되시나요?일단 lookup_value는 “A-Part”입니다. 찾고자 하는 데이터의 구분하는 문자라고 쓴 이유 아시겠죠?tabel_array 는 B1:F7입니다. 여기서 주의할 점은 lookup_value가 반드시 첫번째 열에 있어야 한다는 것입니다. A1부터 찾으면 당연히 A열에는 lookup_value가 없기 때문에 데이터가 없음(#N/A)가 표시될 수 밖에 없습니다. 반드시 첫번째 열에 lookup_value가 존재해야 합니다.col_index_num은 3입니다. table_array에서 1열은 공급부품이며, 2열은 단가, 3열은 수량, 4열은 공급금액이 되겠습니다. 우리는 수량을 찾으라고 했으니 수량을 나타내는 3열의 데이터를 가져와야 하는 것이지요. 그래서 3입니다.마지막으로 range_lookup은 정확하게 일치하느냐(False 또는 0), 대충 비슷하냐(True 또는 1)를 구분해야 합니다. 그러나 사실상false 말고는 쓸일이 없습니다. True로 하면 비슷한 값을 찾아오거나 정확한 데이터를 찾아올 것이라고 생각하기 쉽지만, 사실은 엉뚱한 데이터를 가져오는 경우가 더 많습니다. 비슷한의 의미가 애매하기 때문입니다. 관련되어 이 내용은 확인할 수가 없습니다. 참 쉽습니다. ㅎㅎㅎ 욕먹을지 몰라도… 일단 vlookup은 회사에서 가장 많이 사용하는 명령어 중에 하나입니다. 왜냐구요? 회사다녀보면 아세요 ㅋㅋㅋㅋㅋ수많은 데이터 중에 필요한걸 찾아내는 명령어이기 때문이죠 ㅎㅎㅎㅎ 자 다음으로 Hlookup에 대해서 설명드릴께요. 길게 설명드리지 않습니다. ㅎㅎㅎ 내용은 Vlookup가 동일합니다. 다만, 행과 열이 반대로 되어 있을 경우 Hlookup을 사용하게 됩니다. 즉 위의 테이블이 아래와 같이 바뀌게 되면 사용한다는 말입니다. 보이시죠? Vlookup과 행과 열이 바뀌었습니다. 이 경우 Hlookup 명령어를 쓰게 됩니다. 명령어의 수식은 =Hlookup(lookup_Value,table_array,row_index_num,[range_lookup])으로 사용합니다.설명을 드리면, – lookup_value : 찾고자 하는 데이터의 구분하는 문자 – table_array : 찾고자 하는 데이터가 포함된 선택 영역 – row_index_num : 찾고자 하는 데이터가 있는 행의 번호 – range_lookup : 데이터의 정확도??입니다. 아까 Vlookup과 동일한 문제를 푼다고 생각하면 다음과 같이 입력하게 됩니다. =HLOOKUP(“A-Part”,A2:G6,3,TRUE) Vlookup과 거의 똑같죠? 따로 설명은 안드리겠습니다. Vlookup에 비하면 사용빈도가 조금 낮은 편입니다. 왜냐하면, 대부분의 회사에서 데이터를 행으로 관리하기 때문이죠. 그 이유는 간단합니다. 옆으로는 마우스 휠이 잘 안 먹으니까요 ^^ 심플하죠? 어쨋든 가장 단순한 자료찾기 명령어에 대해서 알아보았습니다. 또 시간이 되면 다음글 쓰도록 하겠습니다. 다시 올때까지 잘 지내세요 ㅠ.ㅜ

Excel 수식 – Data 찾기 : Vlookup, Hlookup 더 읽기"

error: Content is protected !!