thumbnail [엑셀] 배열수식에서의 OR옵션 배열수식 - 조건 AND 옵션배열수식은 특정조건들을 만족시키는 값을 찾을 때 주로 사용한다. 그러므로 각 조건을 모두 만족하는 AND 옵션을 채택하는 경우가 많다. 아래와 같이 *를 통해 조건 값을 연결하게 된다.{=sum( if( (조건1)*(조건2)*(조건3), 대상영역) ) }  배열수식 - 조건 OR 옵션거의없겠지만, 각 조건을 AND 말고 OR로 연결 시키고 싶을 때가 있다. 그럴 경우, 아래와 같이 + 값으로 연결해주면 된다.{=sum( if( (조건1)*( (조건2)+(조건3) ), 대상영역) ) }   꽤 오래동안 몰랐던 사실이다. 2022. 5. 23.
thumbnail [엑셀] 조건에 맞는 값이 여러개일때 n번째 값 찾기 꽤 오래전에 배열 관련된 내용을 기재했었지만, 자주 안쓰니 까먹게 된다. 다시한번 정리 해둔다.  [엑셀] 배열수식 한방에 정리하기엑셀 배열수식 한방에 정리하기 VBA를 쓰지 않는다면, 배열수식은 끝판왕같은 느낌이다. 나도 한동안 그랬었는데, 내부에서 어떤일이 벌어지고 있는지 이해하지 못했기 때문이라 생각된다. 알고sunnybong.tistory.com SMALL 함수SMALL 함수를 처음 봤을 때, 사용하는 사람이 있었을까 싶었지만, 배열 순서를 확인할 때, 사용하는 함수라고 봐도 무방할 것 같다. 예를 들어, 조건의 결과가 `{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}` 로 뱉어졌다면, 이를 숫자로 바꿔주면 `{1;FALSE;1;FALSE;1;FALSE}` 이 될 것이고, 여기에 순.. 2022. 5. 20.
thumbnail [엑셀] Office 추가 기능 오류 관련 Office 프로그램의 아쉬운 점은 그 역사에 비해, Extension들이 공식적으로 공유되지 않고, 그들만의 리그로 계승된다는 점이다. 최근 365의 등장으로 좀 해결되는 듯 하여, 스토어에서 필요한 기능을 몇가지 찾아보았다. 찾아낸 추가 기능은 아래 두개로, formula Editor는 함수를 예쁘게 정리해주는 것이고, Sheet Explorer는 하루나 카페에서 추천을 해준 시트 리스트를 보여주는 것이다. 설명을 읽어보면, formula Editor는 2019버전에서는 작동하지 않고, 온라인 또는 365에서만 작동한다. Sheet Explorer는 2019에서 작동이 가능하다 했다.   해서, 기대를 품고 실행을 눌렀더만, 두개 추가 기능 모두 아래와 같은 에러를 보여준다. 하루나 카페지기가 올린 .. 2022. 5. 19.
thumbnail [엑셀] 다중 조건에서 INDEX/MATCH함수로 값 찾기 조건에 맞는 문자값 찾기값을 찾을 때, 찾고자 하는 값이 문자값이고, 행과 열 조건이 하나씩 있을 때,INDEX 함수와 MATCH 함수를 사용할 수 있다.   MATCH 함수를 통해 찾고자 하는 값이 해당 배열(행/열)에서 몇번째 순서에 위치하는지 찾는다.   INDEX함수에 MATCH함수로 찾은 값을 변수로 사용해서, 최종적으로 찾을 값을 반환받는다.   다중 조건에 맞는 문자값 찾기그럼, 행과 열의 조건이 하나 이상이라면 어떻게 처리할 것인가?우선 행 조건이 두개인 경우이다. 아래 그림과 같이 A열에 중복되는 값들이 있고, 행 조건이 A열에서는 3, B열에서는 F로 주어졌다. 답은 "타"로, 기존의 "하"와 달라졌음을 알 수 있다.   추가된 행 조건을 처리하는 방법은 아래와 같다. 행 조건을 처리하.. 2022. 5. 12.
thumbnail [엑셀] 날짜 평일만 표시하여 연속 데이터 채우기 연속채우기로 날짜를 채우면, 당연히 주말(토/일)이 포함된다. WBS를 짜려면 주말날짜는 빼고 생각하는 것이 인지상정이기 때문에, 이를 일일이 걷어내야 하거나 회색으로 처리해줘야할 때가 있는데, 이를 처리 하는 방법이다. 아래 처럼 연속 채우기가 있다.   홈 > 편집 메뉴에 보면 채우기 기능을 활용할 예정이다. 물론 함수를 사용해되 되긴한다.   기준이 되는 날짜를 적고, 채우고 싶은 만큼 선택을 한 다음, 채우기를 클릭하면, 다음과 같은 창을 확인할 수 있다. 행방향이고, 날짜이며, 평일만 표기한다는 조건을 선택한다.    아주 간단하게 토요일과 일요일을 제외한 날짜 배열이 완성됐다.   알고는 있지만, 잘 안써먹기에, 생각난 김에 적어봤다. 끝. 2022. 4. 26.
thumbnail [엑셀] 등급 찾아서 넣기 : 이름정의에서 배열 사용#2(VLOOKUP) 이름정의에서 배열상수를 넣어서, 별도의 참조테이블을 사용하지 않는 방법을 얼마전 글에서 적은 적이 있다. 이를 사용하여 점수별 등급을 손쉽게 넣을 수 있다. 이는 VLOOKUP의 마지막 상수(RANGE_LOOKUP)값을 TRUE 또는 FALSE로 설정하는 것으로 가능한데, 이에 대한 이해가 필요하다.   [엑셀] 이름정의에서 배열 사용(VLOOKUP)유효성 검사로 드랍다운 메뉴를 사용하는 때를 생각해 보자. 다른 값이 들어오는 것을 막을 때 주로 사용하지만, 특정값이 들어왔을때, VLOOKUP 함수를 사용하여, 다른 테이블을 참조한 값을 넣어sunnybong.tistory.com VLOOKUP 함수의 RANGE_LOOKUP 값을 FALSE로 사용하는 경우, 완전히 값이 동일할 때 값을 반환한다. TRUE.. 2022. 4. 25.
thumbnail [엑셀] 시트이름을 수식에서 사용하기(날짜 계산) 회사에서 일을하다보면, 시트이름을 월로 해두고 동일한 양식을 복사해서 사용하는 경우가 왕왕 있다.이때, 시트이름에서 월데이터 정보를 얻어서, 시트 내에서 사용한다면, 유용할 수 있겠다는 생각을 해봤다.  시트이름 호출아래 수식을 복사한다면, 당신은 시트이름을 반환받을 수 있다.CELL 함수로 워크북 저장경로, 파일이름, 시트이름을 불러오고, FIND 함수로 시트이름의 시작을 찾아서,MID 함수로 발라내는 방식이다.아래 함수보다 복잡하지 않은 범용 사용법을 안다면 꼭 댓글을 부탁한다.=MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,LEN(CELL("filename",A1)))   여기서 사용된 B1셀 참조값은 시트 내 아무 값이어도 무방하다. 바뀌는.. 2022. 4. 22.
thumbnail [엑셀] 이름정의에서 배열 사용(VLOOKUP) 유효성 검사로 드랍다운 메뉴를 사용하는 때를 생각해 보자. 다른 값이 들어오는 것을 막을 때 주로 사용하지만, 특정값이 들어왔을때, VLOOKUP 함수를 사용하여, 다른 테이블을 참조한 값을 넣어줄 때, 많이 사용한다. 주로 참조를 해야하는 값의 수가 적고, 자주 변하지 않는 경우에 사용하게 되는데, 이 과정이 귀찮으므로, 대안을 검토할 가치가 있다. 아래 예시를 보자.학생 이름이 있고, 학점이 있다. 학점은 그 수가 많지 않으므로, 드랍다운 메뉴든 뭐든 처리를 한다고 치자. 옆 열에 기재된 영문 학점에 대해 4점 환산을 하고 싶다면, IF 함수를 쓰는 방법도 있고, 간이 참조 테이블을 만들어서, VLOOKUP처리를 하는 방법도 있다.  간이테이블을 참조하는 VLOOKUP은 그 어딘가에서 간이테이블을 유.. 2022. 4. 14.
thumbnail [엑셀] 같은 파일에 있는 시트 동시에 창으로 띄우기 같은 파일(워크북)에 워크시트가 여러개라면 시트를 이동하면서, 데이터를 비교거나 수정사항이 반영되었는지 확인하기 귀찮을 때가 있는데, 이를 해결할 수 있는 방법이다.첫번째 시트가 데이터를 참조하는 요약, 두번째 시트가 데이터 원본이라고 하자.   CASE1. 요약 테이블이 복잡한 경우요약 페이지에 있는 테이블이 요약이 아니라, 메인 데이터에 준하게 복잡하다면, 둘 다 적당히 큰 창이 필요할 것이므로, 보기 옵션을 활용하는 것이 좋다.보기 메뉴의 창 탭에 새창 버튼을 눌러주면, 새로운 창이 뜨는데, 파일명:1, 파일명:2로 같은 파일에서 파생된 창임을 확인 할 수 있다.   모두 정렬 기능으로 창을 세로로 배치할 경우, 보기 편하게 작업을 할 수 있다.   2개 이상의 창을 띄우는 것도 가능하니, 적당한 .. 2022. 4. 14.
thumbnail [엑셀] 행 삽입 할때, 함수(영역참조)에 주는 영향 함수의 변수값이 영역을 넣는다고 했을 때, - 아래 예시처럼 vlookup 함수의 table_array 변수를 잡았을 때,원본 테이블에 행 추가를 하면, 아주 가끔이지만, 영역이 틀어질때가 있으므로 주의하는 것이 좋다.어떤 때 틀어지게 되는지 살펴보자.   참조영역의 첫 행에 추가영역참조의 첫 행 위에 행을 추가하게 되면, 해당 행은 영역에서 제외된다.   영역을 다시 확인해보면, 포함되지 않은 것을 확인할 수 있다.   참조 영역의 중간에 추가영역참조 중간에 행을 추가하게 되면, 해당 행은 영역에 포함된다.   참조 영역의 마지막에 추가영역참조 마지막에 행을 추가하게 되면, 해당 행은 영역에서 제외된다.   정리행 추가시 기본적으로 선택한 행의 위쪽에 행이 삽입되게 되는데, 시작행과 마지막행일 경우, .. 2022. 4. 13.
thumbnail [엑셀] 비어있는 셀 찾아서 채우기 비어있는 셀을 찾아서, 값을 채울 일이 있을지 모른다.우선 찾기를 할 영역을 선택한다. 같은 값을 넣어줄 영역을 선택해주는 것이 좋다.   F5키를 눌러 이동창을 꺼내고, 좌하단에 있는 옵션버튼을 눌러준다.   빈셀을 선택해준다.   선택이 잘 되었는지 확인한다.   F2키를 눌러 첫번째 셀에 값을 입력하고, CTRL+ENTER키를 눌러주면 동일한 값이 입력된다.   아주 가끔이지만 매우 요긴하게 쓸수 있다.   끝. 2022. 4. 8.
thumbnail [엑셀] 배열수식(ARRAY_FORMULA)의 이해 배열수식을 간단하게 정리 해보자. 단계1. 사용이유아래 과 같은 데이터 테이블이 있다고 하자. 의 데이터를 로 요약해서, 각 과일의 판매량 합계를 기재할 예정이다. 과일별로 소팅을 해서 SUM함수를 걸거나, 피벗테이블을 만드는 방법 등이 있다. 배열수식은 어디까지나, 원본 데이터를 건드리지 않고, 요약표를 별도로 만들 때 사용하기 용이한 방법이다.*피벗테이블 대비 유연한 요약표 작성이 가능하고, 새로고침 과정이 필요없는 것도 장점이라 하겠다.   에 아래와 같이 IF수식을 사용하였다. F3의 값이 C3와 같다면, 판매량을 반환하는 방식이다. 단일 셀값을 비교한다는 뜻이 되겠다.    그럼, 의 사과를 과일열 전체와 비교하고, 과일명이 같다면, 각 판매량을 얻어내는 방법은 아래처럼 영역을 찍어주면 될까? .. 2022. 4. 8.

티스토리툴바