실용적인 엑셀 스킬 공유 : 출석 데이터를 빠르게 정리해보세요!

이전 글 "엑셀 차트 학습: 목표값으로 다중 계열 및 다중 조건 히스토그램 만들기"에서 엑셀 차트 튜토리얼에 대해 배웠고, 오늘은 출석 데이터 정리에 대한 튜토리얼을 공유합니다. 공식을 배우면 회사에 사람이 아무리 많아도 두려워하지 않을 것입니다.

출석기는 거의 모든 회사에서 사용하고 있습니다. 매월 초와 말에는 출석기에서 내보낸 출석 데이터를 정리하는 데 시간이 많이 걸립니다. 출석 데이터를 1분 안에 정리할 수 있습니다.

우리가 일반적으로 출석 기계에서 내보내는 출석 데이터는 기본적으로 Excel을 통해 열 때 다음 형식입니다.

출석 데이터를 추가로 수집하기 위해 위 형식을 다음 효과로 구성하기를 바랍니다.

모든 사람의 일일 펀치인은 동일한 라인에 기록되며, 출퇴근 시간을 기준으로 비교 후 이상 징후가 표시됩니다.

출퇴근 시간은 다음과 같습니다.

오전 근무 시간은 8시, 오전 근무 시간 12시, 오후 근무 시간 13시 30분, 오후 근무 시간입니다. 오후는 17:30

이런 변화를 이루려면 기분이 매우 좋지요... 사실 두 가지 공식과 몇 가지 기본 조작 기술을 익히면 1분 안에 완료할 수 있다는 것이 문제입니다. 그것을 달성하는 방법.

1. 기본 데이터 정리

기본 데이터 오른쪽에 몇 개의 열을 추가하고, 시간을 오전 출근, 오전 퇴근, 오후 퇴근, 오후 퇴근의 4개 열로 나누고 표시를 합니다. 해당 시간:

카드번호, 사람, 날짜 3열을 오른쪽 해당 위치에 복사한 후 "중복 삭제" 기능을 사용하세요.

확인을 클릭한 후 중복된 내용이 1인당 하루에 한 행만 남기고 삭제됩니다:

다음 작업은 해당 위치에 해당 펀치 인 시간을 입력하고, 비정상적인 데이터에 대해서는 특정 시간을 표시하지 않고, 비정상이라는 단어. 이 목적을 달성하려면 먼저 첫 번째 공식을 살펴보겠습니다.

2. 출근 시간을 기록하는 공식을 이용하세요

출근 시간 통계를 용이하게 하기 위해 먼저 통근 시간을 기준으로 메모를 작성해야 아래 그림과 같은 효과를 얻을 수 있습니다. :

출퇴근 시간에 따라 구분합니다.

1. 8시 이전 체크인은 오전 근무로 간주됩니다.

2. 오전에 일한다는 점을 고려하면 인위적인 시간 제한은 오전 12시에서 12시 30분 사이입니다. 13시에서 13시 30분 사이에 출근하면 오후에 퇴근한다는 의미로 인위적으로 규정됩니다.

4. 17시 30분 이후에 출근하면 오후에 퇴근한다는 의미입니다.

5. 체크인이 유효하지 않으며 디스플레이가 비어 있습니다.

E2 셀 수식은 다음과 같습니다.

=IF(D2=$L$1,D2=$P$1,D2=$N$1,$N$2,"") code > 그림에 효과를 적용하려면 채우기를 두 번 클릭하세요.

=IF(D2=$L,D2=$P,D2=$N,$N,"")，双击填充可实现图中的效果。

第一个IF为：=IF(D2

当d2（打卡时间）小于等于k1（上午上班时间）时，if函数的结果为k2（上午上班这四个字），否则返回空值；

第二个if为：IF(AND(D2>=$L,D2

当d2（打卡时间）大于等于L2（上午下班时间）同时小于等于o1（人为规定下班打卡截止时间）时，if函数的结果为L2（上午下班这四个字），否则返回空值。

第三个if为：IF(AND(D2>=$P,D2첫 번째 IF는

입니다: =IF(D2<p style="text-align: center;"><img src="https://img.php.cn/upload/image/295/444/628/1658832663513695.jpg" title="1658832663513695.jpg" alt="실용적인 엑셀 스킬 공유 : 출석 데이터를 빠르게 정리해보세요!">d2(시계 시간)가 k1(오전)보다 작거나 같은 경우 근무 시간) if 함수의 결과는 k2("아침에 출근하다"라는 네 단어)이고, 그렇지 않으면 null 값을 반환합니다. </p>🎜🎜두 번째 if는 🎜: <code>IF(AND( D2>=$L$1,D2🎜🎜<img src="https://img.php.cn/upload/image/114/658%20/359/1658832579533626.jpg" title="1658832579533626 .jpg" alt="실용적인 엑셀 스킬 공유 : 출석 데이터를 빠르게 정리해보세요!">🎜🎜d2(시간 기준)가 L2(아침 기준 시간)보다 크거나 같고 작거나 같은 경우 o1(인위적으로 지정된 출근 시간)과 같으면 if 함수의 결과는 L2(아침 시간)입니다. 이 네 단어는 "퇴근 시간"입니다. 그렇지 않으면 null 값이 반환됩니다. 🎜🎜🎜세 번째 if는 🎜입니다: <code>IF(AND(D2>=$P$1,D2🎜🎜🎜🎜<p>d2(펀칭 시간)가 p2(인위적으로 지정된 출근 시작 시간)보다 크거나 같고 M1(오후 근무 시간)보다 작거나 같은 경우 if 함수의 결과는 M2(네 단어 "off")입니다. 오후에 일하세요"), 그렇지 않으면 null 값이 반환됩니다. </p> <p><strong>네 번째 if는 </strong>: <code>IF(D2>=$N$1,$N$2,"")IF(D2>=$N,$N,"")

当d2（打卡时间）大于等于N1（下午下班时间）时，if函数的结果为N2（下午下班这四个字），否则返回空值。

完成了备注信息之后，就该把对应的时间填入对应的区域内，这时候可以用一个公式右拉下拉就能完成时间的填充，一起来看看是哪个神奇的公式吧。

三、填充时间

在K2单元格输入公式：

=TEXT(SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K),"hh:mm:ss;;异常;")

右拉下拉即可完成时间的填充。

这个公式用到了两个函数，text和sumifs，来看看公式的原理吧。

sumifs函数的结构为sumifs（要求和的数据区域，条件区域1，条件1，条件区域2，条件2……），在今天的例子里我们用了三个条件，实际求和的是D列，三个条件分别是日期、卡号和备注信息，符合三个条件的数字都是唯一的，所以求和结果和引用结果是一致的。

因此公式为：

SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K)

d2(체크인 시간)가 N1보다 크거나 같은 경우( pm get off work time), if 함수의 결과는 N2("get off work in the pm"이라는 네 단어)이고, 그렇지 않으면 null 값을 반환합니다.

비고사항 작성을 완료한 후, 해당 영역에 해당 시간을 입력하는 시간입니다. 이때, 수식을 이용하여 마우스 오른쪽 버튼을 클릭하고 아래로 끌어내리면 시간 입력이 완료됩니다. 마법의 공식.

3. 채우기 시간

셀 K2에 수식을 입력하세요.

=TEXT(SUMIFS($D:$D,$C:$C,$J3,$A:$A, $H3,$E:$E,K$2),"hh:mm:ss;;Exception;")

오른쪽으로 끌어내리면 시간 채우기가 완료됩니다.

이것 수식은 text와 sumifs라는 두 가지 함수를 사용합니다. 수식의 원리를 살펴보겠습니다.

sumifs 함수의 구조는 sumifs(합산할 데이터 영역, 조건 영역 1, 조건 1, 조건 영역 2, 조건 2...)입니다. 오늘의 예에서는 세 가지 조건을 사용했습니다. 실제 합계는 D 열입니다. 세 가지 조건은 날짜, 카드 번호, 비고 정보입니다. 세 가지 조건을 충족하는 숫자는 모두 고유하므로 합산 결과와 참조 결과가 일치합니다. 공식은 다음과 같습니다: SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K$2)

수식을 사용하여 결과를 이와 같은 숫자로 얻으세요. Excel에서는 날짜와 시간의 본질이 숫자이고, 정수는 날짜를 나타내고, 소수는 시간을 나타내기 때문입니다. 위 영역의 셀 형식을 변경하세요. to time and see again 효과를 보면 아래와 같이 숫자가 특정 시간으로 바뀌었습니다.

🎜실제로 셀 형식을 설정한 후에는 기본적으로 표시 효과를 향상시키고 목적이 달성되었습니다. 강제로 시간 형식으로 표시하게 되면 sumifs 외부에 텍스트 함수가 추가됩니다. 일반 형식에서도 시간 단위로 표시되며, 예외로 위치가 0으로 표시됩니다. 🎜🎜🎜🎜🎜 텍스트, 텍스트(데이터, 지정된 형식)의 사용법을 간략하게 설명합니다. 이 예에서는 두 번째 매개변수 형식이 시, 분, 초의 표시 모드로 정의됩니다. , 분, 초는 각각 2자리 표시입니다. 🎜🎜형식 코드의 세미콜론은 데이터 유형에 따라 표시 모드를 별도로 설정할 수 있습니다. 텍스트는 양수, 음수, 텍스트의 네 가지 유형으로 구분됩니다. 이 예에서는 양수는 시간 형식에 따라 표시되고, 음수와 텍스트는 지정된 형식 없이 표시되지 않으며, 0은 "비정상"이라는 단어로 표시됩니다. 🎜🎜텍스트 기능에 대해 오늘은 이 예에서 사용법을 이해하면 됩니다. 🎜🎜🎜🎜요약: 🎜🎜🎜🎜1. 보조 열의 올바른 사용: 출석 데이터를 정리하는 것은 항상 번거로운 문제였습니다. 이때, 보조 열의 합리적인 사용은 종종 매우 어렵습니다. 문제의 난이도를 훨씬 낮출 수 있습니다. 🎜🎜2. 공식은 전능하지 않습니다. 출석의 기본 데이터는 시간이며 관련 규정이 함께 데이터 간의 논리적 관계를 형성합니다. 이 예는 여러 교대가 공존하는 경우 이를 달성할 수 없습니다. 이 방법을 통해 각 사람의 근무 시간에 해당하는 통근 시간도 고려해야 하며, 데이터의 통계 요약을 완성하려면 출석 기계의 지원 소프트웨어가 필요합니다. 🎜🎜관련 학습 권장 사항: 🎜excel 튜토리얼🎜🎜

위 내용은 실용적인 엑셀 스킬 공유 : 출석 데이터를 빠르게 정리해보세요!의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!