Excel – 채랑이네

Excel – VBA : 가계부 만들기1

취미생활 / 프리아 / Excel, VBA, 가계부, 매크로, 엑셀, 자동수식걸기

안녕하세요 채랑이 아빠 프리아입니다. 오늘은 엑셀을 사용해서 가계부를 만들어 볼겁니다.그냥 엑셀을 사용해서 만드는 거라면 누구라도 만들수 있겠죠 ^^저희는 VBA가 추가된 가계부를 만들겁니다. VBA를 사용할 경우 간단하게는 자동으로 수식을 넣게 만들 수 도 있구요,가계부 입력할 내용을 팝업으로 띄워서 입력하게 만들수도 있습니다.두가지 모두 VBA를 사용하는 방법이라고 할 수 있습니다. 먼저 VBA를 사용하여 자동으로 수식을 넣는 방법을 알려드리겠습니다. 우선, 가계부 틀을 잡아야겠죠.틀은 다음과 같이 잡아보도록 하겠습니다. 심플하죠? 일반적으로 많이 볼 수 있는 형태로 잡았어요.자 이중에서 자동 수식을 걸 수 있을 만한 곳은 총 2곳입니다.No.와 잔액입니다.나머지는 다 입력해야지만 되는 항목들이죠. VBA를 걸 내용은 다음과 같습니다. – 일자, 입금, 출금, 비고 중에 값이 입력되면 수식을 자동 입력한다. 일단 우리가 알아야 하는 데이터는 2가지입니다.셀을 이동했을 때, 이전의 셀의 Row값과 Column값이죠. 일단 이전 셀의 Row값과 Column값을 입력할 전역 변수를 지정할 것입니다. 일단 Alt+F11을 눌러서 VBA 창을 띄우세요. 이렇게 보이면서 Sheet1이 선택되어 진것이 보입니다. 옆에 칸에다가 다음과 같이 입력합니다. Dim r, c As Integer Dim은 변수를 선언하는 구절입니다.변수의 이름은 r과 c이며, 타입은 정수입니다. r은 Rows의 약자이며, c는 Column의 약자입니다.이렇게 변수명은 자신이 알아보기 쉬운 것으로 하는 것이 좋습니다.다만, 너무 변수명을 디테일하게 잡으시면 에러가 발생할 수 있습니다.쉽게 말해 변수를 rows로 잡아버리면, VBA 명령어 중에 있는 Rows라는 명령어와 중첩이 되어버리기 때문에 구절 오류가 발생합니다.그래서 row_check나, r 등 단어+추가문구 또는 완전 약자의 형태로 잡으시는게 좋습니다.전 길게 쓰는게 싫어서 그냥 1글자로 퉁쳐요 ㅎㅎㅎㅎ. public나 private 문구 안이 아닌 밖에 Dim으로 선언한 변수의 경우 모든 하위 집단에서 사용할 수 있다는 장점이 있으며,하나의 명령어 집단이 완료되어도 해당 변수는 유지된다는 장점이 있습니다.(public나 private 구절안의 변수는 해당 명령어가 종료되면 변수가 사라짐)다만, 너무 많은 전역 변수를 사용할 경우 용량이 커지고 느려진다는 단점이 있습니다. 자, 이제 셀이 바뀌는 부분을 지정해 보겠습니다.명령어 입력창 위에 (일반)이라고 되어있는 부분의 선택창을 눌러서 Worksheet를 선택합니다.그러면 옆에 (선언)이라고 되어 있는 선택창이 자동으로 SelectionChange로 바뀌면서 밑에 Private 명령어 구문이 생기는 것을 볼 수 있습니다.(만약 (선언)이 그대로 있거나, 다른 명령어가 나온다면, SeletionChange를 선택해 주세요.) 위와 같은 창이 나온다면 일단 제대로 되고 있는 것입니다. 일단 셀이 바뀌면, 현재 셀을 어떻게 표현하는지 임시로 다음과 같이 문구 입력해서 테스트 해보시길 바랍니다. MsgBox “Rows : ” & Target.Row & ” / Columns : ” & Target.Column 입력하셨으며, 엑셀 시트로 가셔서 마우스로 여기저기 눌러 보시면 다음과 같은 창이 뜨는 것을 볼 수 있습니다. C5가 선택되었죠. 행은 5이며, 열은 C이니까 3입니다.정확하게 지금 선택한 셀의 값이 나오게 됩니다. 다만, 저희는 이전에 선택한 셀에 값이 바뀌었는지 확인해야 하므로, 이전 셀을 나타낼 수 있도록 변수를 이용해야 합니다.즉, 행과 열의 번호를 변수로 지정하여, 이전셀을 나타낼 것입니다.다음과 같이 입력하겠습니다. MsgBox “Privious – Rows : ” & r & ” / Columns : ” & c & Chr(13) & Chr(10) & “current – Rows : ” & Target.Row & ” / Columns : ” & Target.Column 중간에 chr(13) & chr(10)은 줄을 바꾸는 명령어 입니다.다음과 같이 된 것을 확인하셨으면, 또다시 엑셀을 이동해 보세요. 처음 이동시에는 이전 셀의 위치가 제대로 나오지 않습니다.당연히 값을 지정하지 않았으니까입니다. 하지만 2번째부터는 이전셀과 현재 셀을 정확하게 나타내는 것을 볼 수 있습니다.메세지창을 띄우고 r과 c의 값을 바꾸기 때문입니다. 즉 이전셀을 확인하기 위해서는 r과 c의 값을 지정하기 이전에 모든 명령어가 종료되어야 한다는 뜻입니다. 자 이제 필요한 내용을 채워보겠습니다.필요한 조건은 2가지입니다.첫번째는 이전 셀이 최상단의 제목이 아니어야 하며, 두번째는 A열과 E열이 아니어야 한다는 것입니다. 제목셀에 내용이 있다고, No.와 잔액에 다른 글자를 넣어버리면 큰일 나겠죠.그리고 A열과 E열은 수식이 들어가야 하는데 여기에 다른 값을 넣어도 안되겠죠.그런 것입니다. ㅎㅎㅎ 다음과 같이 내용 입력하겠습니다. 맨 위와 두번째 IF문은 첫번째 실행할 때, 행과 열값이 없으면 오류가 나기 때문에 기본값으로 1을 넣어 주었습니다.세번째의 IF문은 이전 셀의 행 값이 1보다 커야 하고, 열 값이 1(A열)과 5(E열)이 아니면서 이전 셀 값이 공백이 아닐 경우 실행하라는 내용입니다.즉, 1행이거나, A열이거나, E열이면 실행을 하지 않고, 또한 셀 값이 변경되지 않았으면 실행하지 않도록 설계한 것입니다.네번재 IF문은 r이 2일 경우인데요, 즉 이전 합계액이 없을 경우에는 입금값은 더하고 출급값은 빼고, 2이상일 경우 합계액에서 입금값은 더하고 출금값은 빼라는 명령어입니다. 이렇게 하면 간단한 가계부의 수식 자동 입력은 끝났습니다.테스트 해보도록 할께요. No.와 잔액에 자동으로 수식이 입력되었나요?생각보다 어렵진 않죠?일단 이 정도에서 중간 저장을 하겠습니다.여기서 주의할 점이 하나가 더 있습니다.바로…. 저장 파일형식을 매크로타입(.xlsm)으로 해야 된다는 것입니다. 매크로 사용 통합문서로 저장하지 않으면, 다음에 열때 매크로가 사라져 있는 기적을 보시게 될 것입니다. ㅎㅎㅎㅎ여기까지 작업한 파일은 아래와 같이 올려두었으니 참조하시기 바랍니다. 자 오늘은 여기까지 입니다.간단한 VBA인데도, 처음 설명하니까 시간이 길어지네요. 오늘도 좋은 하루 보내세요.

Excel – VBA : 가계부 만들기1 더 읽기"

Excel – 수식 : Indirect, Address

취미생활 / 프리아 / Address, Excel, index, Indirect, match, 수식, 엑셀, 주소참조, 함수

안녕하세요, 채랑이 아빠 프리아입니다. 오늘은 조금 기분이 별로인 하루입니다….. 고객사로부터 오늘가지 자료 제출해 달라고 해서 아침부터 급하게 자료를 만들기 시작해서 점심때쯤 마무리 단계에 다달았는데 갑자기 연락이 와서 필요없다네요….. 이게 몬가요 ㅠ.ㅠ 급하게 하느라고 다른일도 못하고 있었는데… 에혀 여담은 여기까지만 하고, 오늘은 Indirect 함수와 Address 함수에 대해서 설명드릴께요.어떻게 보면 많이 쓸일이 없을지도 몰라요.하지만 잘 사용하기만 한다면 무지 좋은 기능을 가지고 있기도 합니다.(제 생각입니다 ㅋ) Indirect 함수는 임의의 글자를 셀이름으로 변환해 주는 명령어 입니다.무슨 뜻인지 조금 애매하지요? ㅎㅎㅎ기본적으로 엑셀에서 다른 셀을 참조하려면 다음과 같이 함수를 사용합니다. =A1 그러면 A1의 데이터를 가져오는 형식이죠.그런데, C1셀에는 A가 D1셀에는 1이 들어가 있는 상태에서, 다음과 같은 식을 써 넣으면 어떻게 될까요? =C1&D1 정답은 “A1″이라는 글자가 당겨오게 되어 있습니다.만약 저렇게 조합해서 A1셀의 데이터를 가져오고 싶다면 어떻게 해야 할까요?네 맞습니다. 이럴때 사용하는 함수가 Indirect 입니다. =INDIRECT(C1&D1,TRUE) 이렇게 입력을 하면 C1&D1의 조합이 A1이기 때문에 A1에 있는 데이터를 가져오는 것이죠.저기 보이는 마지막의 TRUE는 무엇인가 하면, A1스타일이라는 것입니다. ㅎㅎㅎ즉 우리가 부르는 셀의 이름 스타일을 True로 정의하고 있습니다.반대로 False는 R1C1 스타일이라고 하는데요, 행번호-열번호 스타일입니다.즉 R1C1은 1번행의 1번열이므로 A1이 됩니다. 즉, 각 셀을 번호로 호출하게 하는 것입니다. 위의 내용을 정리하면 다음과 같이 됩니다. 어떻게 이용할지는 아직 잘 모르시겠죠? 이용방법은 Address 명령어를 설명드리고, 응용하는 방법을 설명드리겠습니다. Address 함수는 Indirect의 R1C1 스타일과 같다고 생각하시면 됩니다.다만, R과 C라는 글자를 넣지 않고 행번호, 열번호로 이루어진다는 것이 차이점입니다.다양한 사용법이 있지만 Address를 많이 쓰지 않기 때문에 단순 사용법만 알려드릴께요.(사실 Address 함수는 Indirect명령어와 같이 쓰지 않으면 큰 의미는 없는데, 다른 사용법을 쓰실거면 그냥 Indirect명령어만 쓰는것이 더 낫습니다.) =ADDRESS(row_num, column_num) 상기와 같이 입력하면 되며, Row1, Column 1을 입력하면 $A$1 이라는 값을 리턴합니다.즉, 주소의 절대참조가 지정되는 것이지요.그리고 말 그대로 주소가 리턴되기 때문에 주소에 대한 결과가 리턴되는 것은 아닙니다.그래서 제대로 사용하려면 Indirect 명령과 혼합해서 사용해야 합니다. =INDIRECT(ADDRESS(1,1),TRUE) 이렇게 말입니다.예제는 다음과 같습니다. 자 이제 이걸 어디에 쓸것이냐 하면, 바로 Match, Index 명령어에 사용할 예정입니다.궁금하신가요? ㅎㅎㅎ일단 도서용 검색을 위한 프로그램이라고 가정하고 다음과 같이 사용할 수 있습니다. 별건 아니고, 일단 검색 기준과 출력 내용을 정하여 결과물을 출력하는 내용입니다.보시면 책 제목 “고양이 해결사 깜냥 2″의 출판사를 물어본 결과입니다.(고양이 해결사 깜냥은 우리 채랑이가 좋아하는 책입니다. ㅎㅎ TMI) 보다시피 Match, Index, Indirect, Address 4개의 명령어를 잘 사용하면 위와 같은 검색 프로그램을 만들 수가 있습니다. 정말 다양한 검색을 하기위해서는 추가로, 수식이 필요하겠지만 단순한 검색용도로는 충분합니다.대충 감이 잡히시나요? 한번씩 해보시면 쉽게 이해하실 수 있으실 거에요.어쩌면 많이 사용할 일은 없겠지만, 그래도 하나씩 알아두면 나쁘지 않을 것 같습니다. 자 오늘도 여기까지 쓰겠습니다. 좋은 하루 되세요 ^^

Excel – 수식 : Indirect, Address 더 읽기"

Excel – VBA : 매크로(Macro)와 VBA의 기초

취미생활 / 프리아 / .Value, Cells, Excel, Macro, Msgbox, Range, VBA, 내용입력, 매크로, 엑셀

안녕하십니까 채랑이 아빠 프리아입니다. 오늘도 취미활동을 엑셀을 올릴 예정입니다. 다만, 너무 수식만 하니까 제가 재미가 없어지는 듯하여 VBA를 섞어서 올리도록 하겠습니다. ㅎㅎㅎ원래 제 전문은 엑셀 수식보다는 VBA 쪽이라고 할까요 ^^;;솔직히 전문까지는 아니구요… 다만 비주얼베이직을 많이 공부했던바 있어서, 다른 분들보다 조금 더 깊게 알고 있을 정도입니다.(그래도 정보처리기사 자격증은 있다구요… 하긴 공과대학교 나온 사람 중에 그거 없는 사람이 몇이나 된다고 ㅠ.ㅠ) 그래서 오늘은 VBA 입문을 위한 매크로 연결 기초를 설명 드리겠습니다. 일단 VBA를 설명드리려면 매크로를 먼저 설명드려야 합니다.엑셀에서 매크로 기능이라고 하면, 일련의 행동을 그대로 기록해 두었다가,나중에 실행하면 매크로의 기록된 작업이 그대로 반복하는 행위를 말합니다.게임에서도 매크로라는 말을 쓰는데요, 동일하고 같은 작업을 반복하는 행위를 뜻하죠. 매크로 기능은 보기 > 매크로 > 매크로 기록을 누르시고 일련의 행동을 만들면 됩니다.일단 간단하게 A1에는 “매크로 테스트”, B1에는 “2022-11-14″을 기록해 보도록 하겠습니다.간단하게 보기 > 매크로 > 매크로 기록을 누르시고, 매크로 이름은 매크로1로 그냥 두시고 확인을 누르세요.그 다음에 A1과 B1에 지정한 문구를 넣으시고, 보기 > 매크로 > 기록중지를 누르세요.그러면 하나의 매크로가 만들어집니다. 자 그럼 이 매크로를 어떻게 사용하느냐… 바로 버튼을 만들어서 사용하시면 됩니다.버튼이라고해서 거창한 것은 아니구요.삽입 > 도형에 들어가셔서 아무모양이나 넣으시면됩니다. 저는 사각형 : 빗면(버튼 모양)을 넣을 겁니다.그리고 해당 도형을 마우스 우측 클릭하신 후 매크로 지정을 누릅니다.그리고 좀전에 기록한 매크로1(이름을 바꾸셨다면 바꾸신 이름을 선택하시면 됩니다.)을 누르시고 확인 버튼을 눌러줍니다. 자, 그럼 아까 넣었던 A1셀과 B1 셀의 내용을 지우시고, 매크로 버튼을 눌러보세요.참고로 첫번째 값은 현재 지정되어 있는 셀에 입력될 확률이 큽니다. 왜냐하면, 매크로 기록은 입력칸을 기준으로 하는 것이 아니라, 매크로를 시작할 때의 셀(현재 셀)이 기준이기 때문입니다.그래서 만약 C1에서 매크로를 시작한다면, C1에 “매크로 테스트”, B1에 “2022-11-14″라고 입력될 가능성이 큽니다.다만, 매크로 시작할대 A1이 아닌 다른 셀에서 시작하고 A1으로 옮겨서 “매크로 테스트”라고 쓰셨으면, A1셀에 데이터가 입력될 것입니다. 저는 어디에 나오냐구요? 안 가르쳐주기 위해서 A1셀을 누르고 버튼을 누를꺼에요 ㅋㅋㅋㅋ 이런게 매크로 기능입니다. 다만, 매크로 기능의 경우 한계가 명확합니다. 딱 지정된 행동만 하기 때문에 조건에 따른 내용이라던지, 지정된 사유에 대한 입력 등을 할수 없기 때문입니다.그래서 매크로의 상위 기능으로 VBA를 사용하는 것입니다. VBA는 Visual Basic For Applications의 약자로 비주얼베이직 언어를 엑셀에다가 융합한 기능입니다. 일종의 프로그래밍 언어입니다.다만, 비주얼베이직 언어는 계속 변경되는데 반해 엑셀에서 사용하는 VBA는 비주얼베이직 6.0 버젼 수준에서 멈추어 있습니다.그 점은 참조하여 주시기 바랍니다. 일단, 오늘은 VBA의 기초만 알려드릴 것이기 때문에 기능의 실행만 알려드리고 끝내겠습니다.틈틈이 사용할만한 수식들 알려드릴께요. ^^아니면 저한테 개인적으로 질문해 주시면 알려드리겠습니다.(홈페이지 오른쪽 아래에 보시면 관리자 문의라고 게시판이 연결되어 있어요. 엑셀문의라고 써 주시면 제가 알고 있다면 알려드리겠습니다.) 자 오늘은 A1셀에 “VBA 테스트”, B1셀에 “2022-11-14″을 입력하고, 메세지 박스를 띄워 “Welcome to VBA World”를 써 보겠습니다.웰컴 문구는 모든 프로그래밍 언어를 배울때 최초로 배우는 문구 식이에요 ㅎㅎㅎ. 그래서 저도 그래요(공돌이의 본능입니다.) 일단 엑셀에서 Alt + F11을 눌러주시면 Microsoft Visual Basic for Applications – 통합 문서1이라는 창이 열리는 걸 볼 수 있습니다. 왼쪽에 프로젝트라는 창에는 현재 열려있는 모든 엑셀 문서들이 다 나타나요. 그중 VBA를 적용할 문서를 선택하시면 됩니다.(자동으로 선택되어 있어요.)저기서 모듈이라는 폴더 옆에 +를 누르시면 Module 1이라는 게 보일것입니다. 그걸 더블클릭하시면, 아까 실행한 매크로1의 수식이 나타날 거에요. 저 수식이 매크로1에 저장된 수식입니다.설명해 드릴 수는 있으나, 나중에 하나씩 알려드리다 보면 설명드려야 하는 내용이라서, 오늘은 그냥 패스하겠습니다. 자 End Sub 밑에 엔터키를 눌러 공백을 늘려주시구요.Sub VBA_Test()라고 입력하고 엔터키를 눌러주세요. 그러면 이전 End Sub 밑에 줄이 나타나고, 새로운 End Sub가 나타나는 것을 볼 수 있습니다.Sub는 하위 명령어를 시작한다는 명령이며, End Sub는 해당 명령이 끝났음을 알리는 명령어입니다.즉 Sub와 End Sub 사이의 내용이 VBA 명령어를 실행하는 구문이 되겠습니다. A1셀과 B1셀에 내용을 입력하는 명령어는 다음과 같습니다. Range(“A1”).Value = “VBA 테스트”Range(“B1”).Value = “2022-11-14” Range()는 범위를 선택하는 명령어입니다.“A1” 처럼 하나의 셀을 지정할수도 있지만, “A1:A10″처럼 범위를 지정할 수도 있습니다.그리고 뒤에 있는 .Value는 입력된 값을 의미합니다.Range()뒤에 점을 찍으면 사용할 수 있는 명령어가 여러개 나오게됩니다.이중에서 .Value는 해당 범이에 들어가는 내용을 의미합니다.(나중에 설명하겠지만, 복사도 할 수 있고, 셀 서식을 바꿀 수도 있고, 범위 지정에서 할 수 있는 여러가지 기능을 수행할 수 있습니다.) 또한, Cells 명령어를 사용할 수도 있습니다.Cells명령어를 사용할 때는 다음과 같이 입력해야 합니다. Cells(1,1).Value = “VBA 테스트”Cells(1,2).Value = “2022-11-14” Cells의 명령어 뒤에 숫자는 반드시 행이 먼저이고, 열이 나중이어야 합니다.즉, A1셀은 (1,1)이지만, B1은 (1,2)입니다. – 1행 1열(A열), 1행 2열(B열)임 결과는 동일합니다. 그리고 마지막으로 메세지 박스를 띄우는 명령어는 다음과 같습니다. msgbox “Welcome to VBA World” msgbox 뒤에 따옴표를 넣어서 글자를 입력하는 것이 가장 기본적인 형태입니다.이 외에도 위에서 사용한 Range()나, Cells()를 사용하여 메세지 박스를 띄울수도 있습니다.예를 들면 다음과 같습니다. msgbox Range(“A1”).Value 참 쉽죠? ㅎㅎㅎㅎ 이거 해보고 싶었어요. 예전에 밥로스 아저씨 보면서 ㅋㅋㅋㅋ일단 위에 내용을 모두 포함하여 제가 만든 VBA 식은 다음과 같습니다. 보시면 Sub 밑에 시작할때 탭으로 한칸 안으로 들어간 것을 보실 수 있습니다.Sub 안에 있는 명령어를 구분하기 위하여 많이 사용하는 방식입니다.오늘 하는 것은 간단하기에 굳이 구분할 필요는 없지만, 이러한 방식은 꾸준히 적용하도록 습관화 하는것이 좋습니다.그래야 나중에 좀 복잡한 내용을 하더라도, 쉽게 구분할 수 있으니까요. 자 이제, 엑셀과 연결하겠습니다.아까 만들어 놓은 버튼을 마우스 우클릭하고 매크로지정을 누릅니다. VBA_Test를 선택하고 확인을 누릅니다. 그후 버튼을 좌클릭하면, 지정된 명령이 입력되는 것을 볼 수 있습니다. 위와 같이 입력되고 메세지 창이 뜨는 것을 볼 수 있습니다.(메세지 창은 한꺼번에 2개가 뜨지 않고, 확인 버튼을 눌러야 두번재 창이 뜹니다.) 흥미로우신가요? ^^오늘은 간단하게 입력하는 것을 알려드렸습니다.다음에는 실 사무에 적용할 수 있는 기능을 알려드리도록 하겠습니다. 오늘은 여기서 줄일께요. 즐거운 하루 되시기 바랍니다.

Excel – VBA : 매크로(Macro)와 VBA의 기초 더 읽기"

Excel – 수식 : Rand 랜덤 데이터의 생성

취미생활 / 프리아 / Excel, rand, 랜덤, 수식, 엑셀, 주사위, 함수

안녕하세요, 채랑이 아빠 프리아입니다. 그냥 이전 글 쓸대 생각 난 김에 랜덤 수치를 만드는 것을 설명 드리겠습니다. 많은 분들이 알고 계시겠지만, 모르시는 분들도 있으니 그냥 참고만 하시면 되겠습니다. 일단 수식은 Rand이며, 사용하는 방법은 다음과 같습니다. =RAND() 무언가 허무합니다. ㅎㅎㅎ아무런 인수도 없이 그냥 RAND()가 명령어의 전부입니다. 저렇게 입력하시면 0~1 사이의 임의의 숫자가 적용됩니다.그리고 다른 데이터를 손댈때마다 숫자가 변경됩니다. 자 그럼 설명 끝내겠습니다…. 라고 하면 웃기겠지요? ㅎㅎㅎ 그러고 싶은데….. 일단은 확장하여 사용하는 방법을 더 설명드리도록 하겠습니다. Rand 명령어는 반드시 0~1사이의 숫자가 적용되게 되어 있습니다. 여기에는 0고 1은 포함되지 않습니다. 즉 0.00000000……1에서 0.999999999….. 의 데이터가 생성되는 것이지요.여기서 문제는 흔히 우리는 1이하의 숫자를 랜덤으로 사용하는 경우는 극히 드뭅니다. 자연수 이상의 수치를 많이 사용하게 되죠.그래서 여기에 일부 변형이 필요하게 됩니다. 방법은 그렇게 어렵지 않습니다. 만약 0~100 사이 숫자를 랜덤하게 만들고자 한다면 다음과 같이 사용합면 됩니다. =RAND()*100 즉, 가장 큰 숫자를 곱하면 되는 것이죠. 다만 여기에 함정이 있다면, 절대 0과 100은 나타나지 않는 다는 것입니다. 네, 0과 1이 안나타나니 100을 곱해도 0과 100은 없습니다. 그리고 나타나는 결과는 소숫점 이하 무한으로 나오게 됩니다.(정말 무한은 아니에요 해보시면 아시겠지만 소숫점 이하 15자리 정도까지 나옵니다.) 만약 0에서 100사이의 자연수가 나오게 하고 싶다면 여기에 추가로 다른 수식을 사용해야 합니다.가장 많이 사용하는 함수는 반올림을 나타내는 Round 함수 입니다.Round함수의 사용 방법은 다음과 같습니다. =ROUND(number, num_digits) number은 숫자이며, num_digits는 소숫점 아래 몇자리까지 표시할지를 정하는 숫자입니다.자연수를 끊는다면, num_digits는 0이 되며, 소숫점 아래 한자리는 1, 두자리는 2…. 이런식입니다.물론 10의 자리이 이후를 끊고 싶다면 -1을 100의 자리에서 끊고 싶다면 -2….. 이런게 사용할 수도 있습니다. 위와 같이 결과가 나오게 됩니다. 자 그러면 0에서 100까지 랜덤 숫자를 만든다면 다음과 같이 사용하면 됩니다. =ROUND(RAND()*100,0) 이렇게 입력을 하면 0과 100의 숫자도 나타나게 됩니다.왜냐하면 0.1~0.4까지는 반올림하면 0이되고, 99.5~99.9까지는 반올림하면 100이 되니까요. 이렇게 응용을 하면 원하는 랜덤숫자를 생성할 수 있습니다. 다만 여기서 유의할 점이 하나 있습니다.위와 같이 수식을 사용할 경우는 그냥 랜덤수치를 나타내기 위한 용도로만 적용가능하다는 겁니다.무슨 말이냐… 동일한 확율을 나타내는 용도로는 사용할 수 없다는 말입니다. 예를 들면 주사위 게임의 수치를 랜덤화 한다고 해 보겠습니다.주사위의 숫자는 1~6의 숫자를 사용하게 되겠죠.정말 쉰게 생각한다면 다음과 같은 수식을 사용할 수 있습니다. =ROUND(RAND()*5,0)+1 여기서 RAND()가 왜 5이고, 마지막에 1을 왜 더하냐고 물으시는 분이 있을 수 있겠죠.만약 6을 곱한다면 0~6의 숫자가 발생하게 됩니다. 0, 1, 2, 3, 4, 5, 6 이 발생하게 되겠죠.하지만 주사위에는 0이 없습니다. 따라서, 랜듬 수치는 0, 1, 2, 3, 4, 5를 만든다음, 1을 더해서 1, 2, 3, 4, 5, 6이 나오게 만들어야 하는 것입니다. 자 여기서 문제점이 무엇인지 아시는 분들도 있겠죠.(물론 모를 수도 있습니다.. 이과가 아니시라면 모를 수 있어요 ^^)확률상으로 1, 6이 나올 확율이 다른 2~5가 나올 확률보다 낮다는 것입니다.차근히 설명해 드리자면 다음과 같습니다.(설명을 위해서 소숫점 2자리까지만 설명드리겠습니다.)1 : 1.00~1.492 : 1.50~2.493 : 2.50~3.494 : 3.50~4.495 : 4.50~5.496 : 5.50~5.99 즉, 소숫점 2자리 기준으로 했을 때, 2~5의 숫자는 나올확률이 100개라고 한다면, 1과 6의 경우에는 나올 확률이 50개 밖에 되지 않습니다. 다른 숫자에 비해서 나올 확율이 낮을 수 밖에 없는 거죠. 여기서 도박 사기가 발생합니다. ㅎㅎㅎㅎ 그래서, 이렇게 동일한 확률이 나와야 하는 경우에는 반올림 함수를 사용하시면 안됩니다.이럴 경우에는 내림(Rounddown), 올림(Roundup), 정수화(Int), 버림(Trunc)의 수식을 사용해야 합니다. Round, Rounddown, Roundup, Trunc, Int의 차이점이 궁금하신 분들은 아래에 연결된 글을 참조해 주세요. ^^ 주사위를 생성하기 위해서는 다음 중 하나를 선택하시면 됩니다. =ROUNDUP(RAND()*6,0)=ROUNDDOWN(RAND()*6,0)+1=TRUNC(RAND()*6,0)+1=INT(RAND()*6)+1 Roundup의 경우 0보다 크고 1보다 작거나 같은 수는 1로 올려버립니다. 그래서 자연스럽게 동일한 비율을 가진 주사위가 됩니다. 마찬가지로, Rounddown, Trunc, Int의 경우 소수점 이하를 버리기 때문에 0~5의 숫자가 만들어지므로 +1을 해줘야 주사위를 완성할 수 있습니다. 그래서 랜덤화 수치를 구성할 때 동일한 발생확율을 만들고자 한다면 roundup, rounddown, trunc의 명령어를 쓰시고 간단하게 하려면 round의 명령어를 쓰시기 바랍니다. Int는 정수화된 랜덤수치를 만들때만 사용하셔야 합니다. 자 이번엔 간단한 글을 완료하였습니다.(솔직히 간단하지 않음… 이글을 보시면 모르시겠지만, 저기 연결된 수식의 차이점 글을 여기에 다 쓰다가 글의 정체성이 흐트러져서 차이점 글을 중간에 하나 다시 쓰고 옮 ㅋㅋ) 오늘도 좋은 밤 되세요 ^^ 그럼 이만…

Excel – 수식 : Rand 랜덤 데이터의 생성 더 읽기"

Excel – 수식 : Round, Roundup, Rounddown, Trunc, Int의 차이점

취미생활 / 프리아 / Excel, int, round, rounddown, roundup, trunc, 구분, 내림, 반올림, 버림, 수식, 엑셀, 올림, 정수화, 차이점, 함수

안녕하세요. 채랑이 아빠 프리아입니다. 음 어쩌다 보니 글을 하나 더 쓰게 되었습니다.랜덤화 데이터 생성을 설명하다가 갑자기 얘기가 너무 이상한 데로 빠지고 있어서 글을 보는 사람들이 헷갈릴 것 같아 중간에 함수의 차이점을 설명하던 내용을 별도의 글로 구분하여 작성해야겠더라구요.그래서 하나의 글이 추가로 생성되었습니다. ㅎㅎㅎ Round, Rounddown, Roundup는 형제 함수라고 할 수 있을 정도로 사용방법이 동일합니다. 그리고 이 Round 3형제는 수학적인 요소의 수식입니다. 반올림, 올림, 내림의 의미입니다. 즉 지정한 자릿수 이하의 숫자를 반올림 할것이냐, 올림할것이냐, 내림할 것이냐를 결정하는 것이죠. 다만, 수학과 다른 것이 있다면 음수의 처리 방법입니다. -1.6을 반올림하면 어떤 숫자가 되어야 할까요? -1일까요, -2일까요? 답은 -2입니다. 수학이나 엑셀의 Round나 동일한 결과가 나옵니다.그럼 -1.6을 올림하면 어떤 숫자가 될까요? 수학적으로는 -1이라는 답을 내게 되나, 엑셀에서 Roundup을 걸면 -2가 되어 버립니다.또, -1.6을 내림할 경우 수학적으로는 -2가 되나, 엑셀의 Rounddown을 걸면 -1이 되어 버립니다.일반 수학과는 의미가 달라져 버리는 거죠. 이건, 사실상 엑셀에서의 오류라고 밖에 볼 수 없습니다.참 황당한 일인 거죠. 혹시나 모르셨다면 참고하시기 바랍니다.(제가 말씀드리는 것은 엑셀 2022버젼입니다. 이전버전에서는 다를 수 있어요..) 자 여기서 버림(Trunc)의 요소를 보겠습니다. Trunc도 Round 3형제와 수식 구조는 동일합니다. 사용한 결과는 Rounddown과 동일합니다.그렇다면 왜 버림이 있는지 이해가 되지 않을 수 있습니다. 왜 똑같은 결과가 나오는데 2개가 구분되어 있는것인가 하는 문제죠.이유는 아주 단순합니다. 위에서 설명한 바와 같이, 올림은 요구하는 요구자리 이하의 숫자가 0보다 크면 큰 숫자를 향해 나아가고, 내림은 요구자리 이하의 숫자를 작은 숫자를 향해 나아가는 형식입니다. 즉 양수의 경우 올림은 0에서 멀어지는 쪽으로 움직이고, 내림의 경우 0에서 가까워는 쪽으로 움직입니다. 반대로 음수에서는 올림의 경우 0에서 가까워지는 방향으로 움직이고, 내림은 0에서 멀어지는 방향으로 움직입니다.예를 들면 위에서 설명한 것처럼, 1.6을 올림하면 2가 되고, 1.6을 내림하면 1이 됩니다. 올림은 0에서 멀어지는 방향으로 갔고, 내림은 0에서 가까워지는 방향으로 갔죠.음수에서는 -1.6을 올림하면 -1이 되고, -1.6을 내림하면 -2가 되어야 합니다. 올림은 0에서 가까워지는 방향으로, 내림은 0에서 멀어지는 방향으로 이동하죠. 그런데 버림의 경우에는 지정된 이하의 자릿수를 그냥 0으로 치환해 버리게 됩니다. 즉 1.6을 버리면 1이 되고, -1.6을 버리면 -1이 되는 것입니다. 쉽게 말하면 양수에서의 버림은 내림과 동일하게 취급되고, 음수에서의 버림은 올림과 동일하게 취급되는 것이죠. 그런데 아까 말한바와 같이 엑셀에서는 Rounddown은 무조건 0으로 가까워지도록 되어 있고, Roundup은 0에서 멀어지도록 설계가 되어 수학적인 올림, 내림이 달라져 버린 것입니다. 그래서 Rounddown과 Trunc 명령어가 똑같아져 버린 것이죠.제대로 되어 있었다면 양수에서는 Rounddown과 Trunc가 동일한 결과를 내고, 음수에서는 Roundup과 Trunc가 동일한 결과를 내야 하는데, 엑셀에서는 양수와 음수 상관없이 Rounddown과 Trunc 가 동일해져 버린 것입니다. 마지막으로 Int의 경우 정수화입니다. 간단하게 소숫점 이하를 버리는 형태입니다.그런데 여기서 황당한 것이 하나가 발생하게 됩니다. 이론적으로 Int는 소숫점 이하를 버리는 것이기 때문에 Trunc와 동일한 결과가 나와야 하지만, 결과는 생각과는 다릅니다.결과론적으로 Int 함수를 쓰게 되면 수학적인 내림의 결과가 나오는 것을 볼 수 있습니다.즉, 1.6을 Int를 걸면 1이 되고, -1.6을 Int를 걸면 -2가 되어 버립니다.황당할 수 밖에 없습니다. 개념적으로 정수화는 버림의 의미를 가지거든요.(뭐 물론 수학에서는 정수화란 단어 자체가 없으니 제가 잘 못 알고 있을 수는 있어요.)어쨋든 Int 함수를 쓰면 해당 숫자보다 작은 수의 정수로 치환된다는 것으로 이해하시길 바랍니다. 자, 여기서는 엑셀 수식을 설명하는 것이므로 수학적 의미는 제외하고, 아래와 같이 정리하고 마무리 하도록 하겠습니다. 자 오늘도 재밌으셨기를 바랍니다. 이만 물러갈께요 ^^

Excel – 수식 : Round, Roundup, Rounddown, Trunc, Int의 차이점 더 읽기"

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

취미생활 / 프리아 / Excel, index, match, match index, Vlookup, 다중조건, 다중조건 데이터, 다중조건 데이터 찾기, 데이터 찾기, 수식, 엑셀

안녕하세요 채랑이 아빠 프리아입니다. 오늘도 시간을 잠시 내서 글 쓰러 왔습니다 ^^. 계속해서 엑셀 관련 글을 쓰게 되네요 ^^. 간단하게 글쓰기엔 엑셀이 참 좋아요.오늘도 데이터 검색에 대한 팁을 하나 더 드리려고 합니다. 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 마지막 값 찾기

취미생활 / 프리아 / Excel, lookup, Vlookup, 마자믹 값, 마지막 값 찾기, 수식, 엑셀

안녕하세요, 채랑이 아빠 프리아 입니다. 어제보단 조금 시간적 여유가 있네요 ㅎㅎㅎ. 어제 고객사 손님이 왔다가 갔지만, 특별한 이슈사항은 없었네요. 참 다행입니다 ㅎㅎㅎ. 오늘도 엑셀에 대한 팁을 하나 더 드릴껀데요.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 포함하는 문자 찾기

취미생활 / 프리아 / Data 찾기, Excel, Vlookup, 문자, 수식, 엑셀, 포함하는

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

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

Excel 수식 – Data 찾기 : Vlookup, Hlookup

취미생활 / 프리아 / data, Excel, Hlookup, Vlookup, 데이터찾기, 엑셀

안녕하세요, 채랑이 아빠 프리아 입니다. 어떻게 하다보니 글을 못올린지가 한달이 다 되었네요.. 갑자기 일이 몰려들어서 출장 가고, 자료 모으고, 데이터 뽑고 정신이 없이 한달을 보냈네요.원래는 복귀하면 홈페이지부터 올리려고 했는데, 아직도 일이 끝나지 않았어요 ㅠ.ㅠ 내일 또 고객사에서 손님이 온데서 자료 만들고 있음… 그래서 오늘은 쉬운거 하나만 올리려고 합니다(일하기 싫으면 몇개 더 올릴수도 있어요… 땡땡이는 바쁠때 쳐야 보람이 있으니까요 ㅎㅎㅎ) 아! 그리고, 카테고리가 취미인 점은… 주업무가 아니라 그냥 재밌어서 하는거라 취미로 구분했어요 ㅋㅋㅋ 기분나빠 하진 마시공. 원래는 많이 알려지지 않은 팁을 알려드릴려고 했는데, 그 팁을 사용하지 않을 때의 기본적인 내용을 먼저 설명드려야 할 것 같아서.. 일단 기본적으로 회사에서 가장 많이 쓰는 명령어 중 하나인 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 더 읽기"