안녕하세요. 행아아빠입니다.
이번에 다뤄볼 엑셀 내용은 차량 배차와 관련한 자동 서식을 만는데 필요한 수식을 작성해 보도록하겠습니다.
차량 배차와 관련한 업무 내용은 굉장히 다양하게 있을 수 있기 때문에 여러분들의 환경에 맞는 배차 서식에 따라서 작성해야 하는 수식도 달라질 수 있으니 참고 하시길 바라겠습니다.
■ 행아아빠의 Youtube 채널.
● 주요 컨텐츠 주제 - 내일은로또왕 / 내일은엑셀왕
● Youtube 채널 주소 - https://www.youtube.com/@economic-freedom
■ 행아아빠 후원 안내.
● Youtube 채널 맴버십 후원 - https://www.youtube.com/channel/UCPmC4OZu99Rqguwz5QEINjA/join
● Youtube 댓글 후원 - Youtube 영상 댓글에서 "Super Thanks" 구매 후 댓글 작성.
● 내일은엑셀왕 대표 Youtube 영상 - https://youtu.be/oYVZHgv_sKM (2023년 급여 자동 계산 서식)
그림과 같이 차량, 이름, 장소, 요일, 승하차, 시간 데이터를 입력하고 조건을 "대형1호차", "월요일"로 입력하면
월요일 대형1호차의 배치시간, 장소, 승하차, 이름데이터값을 수식을 작성해서 자동으로 가져올 수 있습니다.
1. 조건을 만족하는 "시간"값 중복없이 가져오는 수식.
대형1호차, 월요일의 조건을 만족하는 시간값을 중복없이 가져오는 수식은 다음과 같이 작성할 수 있습니다.
| =IFERROR(INDEX($L$3:$L$21,MATCH(1,INDEX((COUNTIF($O$4:O4,$L$3:$L$21)=0)* (OFFSET($C$2,1,MATCH($P$3,$D$2:$J$2,0),19,1)=1)*($A$3:$A$21=$O$3)* ($L$3:$L$21<>""),),0)),"") |
위 수식을 작성하고 O8셀까지 수식을 복사 하면 대형1호차, 월요일 배차 시간을 중복없이 수식의 결과값으로 자동으로 입력시킬 수 있습니다.
2. 조건을 만족하는 "장소"값 중복없이 가져오는 수식.
조건을 만족하는 장소값을 중복없이 가져오는 수식을 작성하기 위해 참조해야할 조건은 대형1호차, 월요일, 시간은 O열 5행에 입력된 시간을 참조해서 P5셀의 장소값을 가져올 수 있습니다.
수식을 작성하면 다음과 같이 작성할 수 있습니다.
| =IFERROR(LOOKUP(1,1/(($L$3:$L$21=$O5)*($A$3:$A$21=$O$3)* (OFFSET($C$2,1,MATCH($P$3,$D$2:$J$2,0),19,1)=1)),$C$3:$C$21),"") |
장소값을 결과값으로 가져오는 주요 함수는 Lookup함수와 Offset함수로 수식을 작성할 수 있습니다.
Lookup함수로 다중조건 Vlookup함수의 결과값을 가져오면서, 월요일부터 일요일까지 해당요일에 배차가 배정되어 있을 경우 입력된 숫자1의 값을 요일 조건에 일치하는 범위를 자동으로 설정하기 위해서 Offset함수로 참조범위 수식을 작성해 줘서 결과값을 가져올 수 있습니다.
3. 조건을 만족하는 "승●하차"값 가져오는 수식.
조건을 만족하는 "승차" 또는 "하차"로 결과값을 가져오기위한 조건으로는 데이터에서 대형1호차, 월요일, O5셀의 시간, P5셀의 데이터값과 동일한 값일때 K열의 승차 또는 하차의 데이터값을 결과값으로 가져오는 수식을 작성할 수 있습니다.
이때 요일을 참조하는 범위는 Offset함수 수식으로 동적범위로 설정해서 작성하게 되면 요일별로 참조범위를 따로 지정해 주지 않고도 간단하게 수식을 작성할 수 있습니다.
Q5셀에 수식을 작성하면 다음과 같이 수식을 작성할 수 있습니다.
| =IFERROR(LOOKUP(1,1/(($L$3:$L$21=$O5)*($A$3:$A$21=$O$3)* (OFFSET($C$2,1,MATCH($P$3,$D$2:$J$2,0),19,1)=1) ),$K$3:$K$21),"") |
위 수식을 작성하고 Q8셀까지 수식을 복사하면 결과값을 자동으로 입력시킬 수 있습니다.
4. 중복값없이 조건을 만족하는 이름 데이터 나열하는 수식.
중복값 없이 조건을 만족하는 이름을 나열하는 수식을 작성하기 위한 조건은 대형1호차, 월요일, O5셀 시간값, P4셀 장소, Q5셀 승●하차 구분값과 일치하는 데이터의 이름을 중복없이 결과값으로 나열하는 수식을 작성합니다.
수식은 아래 수식을 R5셀에 작성하고 R5셀에 작성한 수식은 AA5셀까지 수식을 복사해주면 조건과 일치하는 이름을 중복값없이 결과값으로 가져올수가 있습니다.
| =IFERROR(INDEX($B$3:$B$21,MATCH(1,INDEX((COUNTIF($Q5:Q5,$B$3:$B$21)=0)* (OFFSET($C$2,1,MATCH($P$3,$D$2:$J$2,0),19,1)=1)*($A$3:$A$21=$O$3)* ($L$3:$L$21=$O5)*($C$3:$C$21=$P5)*($K$3:$K$21=$Q5)* ($B$3:$B$21<>""),),0)),"") |
위 수식을 작성했다면 AA8셀까지 수식을 복사해 주면 나머지셀도 조건과 일치하는 데이터값을 중복없이 이름을 데이터값으로 채워줄수가 있습니다.
오늘은 Vlookup 다중조건의 결과값을 가져올 수 있는 Lookup함수, 동적범위 함수 Offset, 중복값없이 데이터값을 가져오는 index, match함수까지 여러 함수들로 수식을 작성해 봤습니다.
차량배차업무가 사용자마다 다른환경, 다른규칙에 따라서 적용되는 서식으로 작성할 수 있기 때문에 오늘 작성한 서식을 그대로 활용하기에는 무리가 있을수도 있습니다.
다만, 여러 수식을 다양한 형태로 작성한것들을 참고 하셔서 여러분들이 실제로 활용하고자 하는 서식에서 활용해 볼 수 있기를 바라겠습니다.
※ 서식 다운로드
'엑셀' 카테고리의 다른 글
| 엑셀 자주 사용하는 5~9개 함수. (0) | 2023.04.19 |
|---|---|
| 엑셀 연차관리대장 자동 계산 서식 만들기. (0) | 2023.03.06 |
| 엑셀 날짜와 시간으로 입력된 데이터를 날짜와 시간으로 분리 시키는 수식 작성 방법. (0) | 2023.03.02 |
| 엑셀 날짜와 시간대별 예약 관리 현황 서식과 수식 작성 방법. (1) | 2023.03.02 |
| 엑셀 급여 계산 사용자 조건에 맞는 급여 계산 서식 작성 방법. (0) | 2023.02.27 |
댓글