연말정산관련 엑셀수식

<a href="javascript:checkVirus('grpid%3DrtTh%26fldid%3DQ2Ux%26dataid%3D171%26fileid%3D1%26regdt%3D20180222135716&url=http%3A%2F%2Fcfile283.uf.daum.net%2Fattach%2F99D7FD495A8E5242179F9F')"><img src="https://t1.daumcdn.net/daumtop_deco/icon/icon.hanmail.net/editor/p_etc_s.gif" border="0" alt="첨부파일" class="vam"/> 예제 - 2017년귀속 연말정산 - ○○○○(주)-1.xlsx</a> <a href="javascript:checkVirus('grpid%3DrtTh%26fldid%3DQ2Ux%26dataid%3D171%26fileid%3D2%26regdt%3D20180222135716&url=http%3A%2F%2Fcfile273.uf.daum.net%2Fattach%2F993D6A365A8E8E1819930A')"><img src="https://t1.daumcdn.net/daumtop_deco/icon/icon.hanmail.net/editor/p_etc_s.gif" border="0" alt="첨부파일" class="vam"/> 기부금 - 소득세법.xlsx</a> 근속월수 (J23 기산일(입사일) , M23 퇴사일)=IF(J23="",0,DATEDIF(J23,M23,"M")+1) 근속연수 (T23 근속월수 W23 제외월수 Z23 가산월수 AC23 중복월수) =IF(T23="",0,IF(((T23-W23+Z23-AC23)/12)<=INT((T23-W23+Z23-AC23)/12),INT((T23-W23+Z23-AC23)/12),INT((T23-W23+Z23-AC23)/12)+1)) 근속기간 ( D4 입사일 E4 퇴사일)=IF(E4="","",DATEDIF(D4,E4+1,"Y") & "년 " & DATEDIF(D4,E4+1,"YM") & "개월 "& IF(OR(VALUE(TEXT(D4,"dd"))-1=VALUE(TEXT(E4,"dd")),TEXT(D4,"dd")="01"),DATEDIF(D4,E4,"MD")+1 & "일",DATEDIF(D4,E4,"MD")+1 & "일")) 근속일수=IF(E4="","",E4-D4+1) 생년월일            (주민등록번호(셀서식 주민등록번호일경우) 생년월일 추출 (M4 주민등록번호) )=IF(OR(MID(M4,LEN(CLEAN(M4))-6,1)<="2",MID(M4,LEN(CLEAN(M4))-6,1)="5",MID(M4,LEN(CLEAN(M4))-6,1)="6"),DATE(MID(M4,1,2),MID(M4,3,2),MID(M4,5,2)),CHOOSE(14-LEN(CLEAN(M4)),DATE(MID(M4,1,2)+100,MID(M4,3,2),MID(M4,5,2)),DATE(MID(M4,1,1)+100,MID(M4,2,2),MID(M4,4,2)),DATE(2000,MID(M4,1,2),MID(M4,3,2)),DATE(2000,MID(M4,1,1),MID(M4,2,2)))) 입사일 현재 나이 (D4 입사일 N4 생년월일) =IF(D4="","",DATEDIF(N4,D4+1,"Y") & "년 " & DATEDIF(N4,D4+1,"YM") & "개월 "& IF(OR(VALUE(TEXT(N4,"dd"))-1=VALUE(TEXT(D4,"dd")),TEXT(N4,"dd")="01"),DATEDIF(N4,D4,"MD")+1 & "일",DATEDIF(N4,D4,"MD")+1 & "일")) 성별 (번호)   (M4 주민등록번호)=IF(13=LEN(M4),MID(M4,7,1),IF(12=LEN(M4),MID(M4,6,1),IF(11=LEN(M4),MID(M4,5,1),IF(10=LEN(M4),MID(M4,4,1),IF(9=LEN(M4),MID(M4,3,1),0))))) 성별 (남여) O4 성별(번호)=CHOOSE(O4,"남","여","남","여","남","여","남","여") 차가감징수세액 환급액 (-만 더하기)=SUMIF(AJ$4:AJ$48,"<0") 차가감징수세액 추징액 (+만 더하기)=SUMIF(AJ$4:AJ$48,">0") 차감징수세액 소득세=TRUNC(Z4-AC4-AG4,-1) 차감징수세액 주민세=TRUNC(AA4-AD4-AH4,-1) <table border="0" cellpadding="0" cellspacing="0" width="657" style="border-collapse: collapse;width:494pt"> <colgroup><col width="72" span="2" style="width:54pt"> <col width="98" style="mso-width-source:userset;mso-width-alt:3136;width:74pt"> <col width="78" span="2" style="mso-width-source:userset;mso-width-alt:2496; width:59pt"> <col width="108" style="mso-width-source:userset;mso-width-alt:3456;width:81pt"> <col width="43" style="mso-width-source:userset;mso-width-alt:1376;width:32pt"> <col width="108" style="mso-width-source:userset;mso-width-alt:3456;width:81pt"> </colgroup><tbody><tr height="22" style="height:16.5pt"> <td height="22" class="xl92" colspan="2" width="144" style="height:16.5pt;mso-ignore: colspan;width:108pt">○○○테크㈜</td> <td width="98" style="width:74pt"></td> <td width="78" style="width:59pt"></td> <td width="78" style="width:59pt"></td> <td width="108" style="width:81pt"></td> <td width="43" style="width:32pt"></td> <td class="xl89" width="108" style="width:81pt">입사일</td> </tr> <tr height="46" style="mso-height-source:userset;height:34.5pt"> <td height="46" class="xl90" style="height:34.5pt">NO</td> <td class="xl90" style="border-left:none">사원코드</td> <td class="xl90" style="border-left:none">사원명</td> <td class="xl90" style="border-left:none">입사일</td> <td class="xl90" style="border-left:none">퇴사일</td> <td class="xl90" style="border-left:none">근속기간</td> <td class="xl90" style="border-left:none">일수</td> <td class="xl90" style="border-left:none">나이</td> </tr></tbody></table> <table border="0" cellpadding="0" cellspacing="0" width="655" style="border-collapse: collapse;width:493pt"> <colgroup><col width="78" style="mso-width-source:userset;mso-width-alt:2496;width:59pt"> <col width="76" style="mso-width-source:userset;mso-width-alt:2432;width:57pt"> <col width="79" style="mso-width-source:userset;mso-width-alt:2528;width:59pt"> <col width="67" style="mso-width-source:userset;mso-width-alt:2144;width:50pt"> <col width="139" style="mso-width-source:userset;mso-width-alt:4448;width:104pt"> <col width="78" style="mso-width-source:userset;mso-width-alt:2496;width:59pt"> <col width="38" span="2" style="mso-width-source:userset;mso-width-alt:1216; width:29pt"> <col width="62" style="mso-width-source:userset;mso-width-alt:1984;width:47pt"> </colgroup><tbody><tr height="22" style="height:16.5pt"> <td colspan="3" height="22" class="xl94" width="233" style="height:16.5pt;width:175pt">취업감면</td> <td width="67" style="width:50pt"></td> <td width="139" style="width:104pt"></td> <td width="78" style="width:59pt"></td> <td width="38" style="width:29pt"></td> <td width="38" style="width:29pt"></td> <td width="62" style="width:47pt"></td> </tr> <tr height="46" style="mso-height-source:userset;height:34.5pt"> <td height="46" class="xl91" style="height:34.5pt;border-top:none">기간1</td> <td class="xl91" style="border-top:none;border-left:none">기간2</td> <td class="xl91" style="border-top:none;border-left:none">한도</td> <td class="xl90" style="border-left:none">내·외국인</td> <td class="xl90" style="border-left:none">주민(외국인)등록번호</td> <td class="xl91" style="border-left:none">생년월일</td> <td class="xl91" style="border-left:none">성별</td> <td class="xl91" style="border-left:none">성별</td> <td class="xl92" style="border-left:none">부서</td> </tr></tbody></table> <table border="0" cellpadding="0" cellspacing="0" width="2187" style="border-collapse: collapse;width:1645pt"> <colgroup><col width="125" style="mso-width-source:userset;mso-width-alt:4000;width:94pt"> <col width="96" style="mso-width-source:userset;mso-width-alt:3072;width:72pt"> <col width="117" span="2" style="mso-width-source:userset;mso-width-alt:3744; width:88pt"> <col width="100" style="mso-width-source:userset;mso-width-alt:3200;width:75pt"> <col width="97" span="2" style="mso-width-source:userset;mso-width-alt:3104; width:73pt"> <col width="117" span="4" style="mso-width-source:userset;mso-width-alt:3744; width:88pt"> <col width="97" span="10" style="mso-width-source:userset;mso-width-alt:3104; width:73pt"> </colgroup><tbody><tr height="22" style="height:16.5pt"> <td colspan="2" height="22" class="xl106" width="221" style="border-right:.5pt solid black; height:16.5pt;width:166pt">○○○테크-당사</td> <td rowspan="2" class="xl111" width="117" style="border-bottom:.5pt solid black; width:88pt">총급여 소계 (ⓐ) (원천징수이행상황신고서)</td> <td rowspan="2" class="xl107" width="117" style="border-bottom:.5pt solid black; width:88pt">감면율</td> <td class="xl104" width="100" style="width:75pt">제출</td> <td class="xl100" width="97" style="border-left:none;width:73pt">미제출</td> <td class="xl101" width="97" style="border-left:none;width:73pt">비과세</td> <td rowspan="2" class="xl113" width="117" style="border-bottom:.5pt solid black; width:88pt">급여 계 (손익,제조)</td> <td colspan="3" class="xl90" width="351" style="border-left:none;width:264pt">72. 결정세액</td> <td colspan="3" class="xl90" width="291" style="border-left:none;width:219pt">기납부세액 74.주(현) 근무지</td> <td colspan="4" class="xl106" width="388" style="border-right:.5pt solid black; border-left:none;width:292pt">기납부세액 73.종전근무지</td> <td colspan="3" class="xl90" width="291" style="border-left:none;width:219pt">76. 차감징수세액</td> </tr> <tr height="46" style="mso-height-source:userset;height:34.5pt"> <td height="46" class="xl90" style="height:34.5pt;border-top:none">급여액</td> <td class="xl90" style="border-top:none;border-left:none">상여액</td> <td class="xl105" style="border-top:none">비과세</td> <td class="xl102" style="border-top:none;border-left:none">비과세</td> <td class="xl103" style="border-top:none;border-left:none">합계</td> <td class="xl94" style="border-top:none;border-left:none">소득세</td> <td class="xl95" style="border-top:none;border-left:none">지방소득세</td> <td class="xl96" style="border-top:none;border-left:none">소계</td> <td class="xl94" style="border-top:none;border-left:none">소득세</td> <td class="xl95" style="border-top:none;border-left:none">지방소득세</td> <td class="xl96" style="border-top:none;border-left:none">소계</td> <td class="xl93" width="97" style="border-top:none;border-left:none;width:73pt">총급여 (ⓑ)</td> <td class="xl97" width="97" style="border-top:none;border-left:none;width:73pt">(72.결정세액) 소득세</td> <td class="xl98" style="border-top:none;border-left:none">지방소득세</td> <td class="xl99" style="border-top:none;border-left:none">소계</td> <td class="xl94" style="border-top:none;border-left:none">소득세</td> <td class="xl95" style="border-top:none;border-left:none">지방소득세</td> <td class="xl96" style="border-top:none;border-left:none">소계</td> </tr></tbody></table> <table border="0" cellpadding="0" cellspacing="0" width="820" style="border-collapse: collapse;width:615pt"> <colgroup><col width="117" style="mso-width-source:userset;mso-width-alt:3744;width:88pt"> <col width="87" span="3" style="mso-width-source:userset;mso-width-alt:2784; width:65pt"> <col width="97" style="mso-width-source:userset;mso-width-alt:3104;width:73pt"> <col width="97" style="mso-width-source:userset;mso-width-alt:3104;width:73pt"> <col width="124" span="2" style="mso-width-source:userset;mso-width-alt:3968; width:93pt"> </colgroup><tbody><tr height="22" style="height:16.5pt"> <td height="22" class="xl89" width="117" style="height:16.5pt;width:88pt"></td> <td width="87" style="width:65pt"></td> <td width="87" style="width:65pt"></td> <td width="87" style="width:65pt"></td> <td width="97" style="width:73pt"></td> <td width="97" style="width:73pt"></td> <td colspan="2" class="xl95" width="248" style="width:186pt">주민세 (매달 원천세신고시)기납부세액</td> </tr> <tr height="46" style="mso-height-source:userset;height:34.5pt"> <td height="46" class="xl93" width="117" style="height:34.5pt;width:88pt">총급여 (ⓐ+ⓑ)</td> <td class="xl90" style="border-left:none">연금</td> <td class="xl90" style="border-left:none">건강</td> <td class="xl90" style="border-left:none">요양</td> <td class="xl90" style="border-left:none">고용</td> <td class="xl89"></td> <td class="xl90" style="border-top:none">아산시</td> <td class="xl90" style="border-top:none;border-left:none">안성시</td> </tr></tbody></table>