실용적인 엑셀 스킬 공유 : 스마트한 출석부 만들기

이전 글에서 "엑셀 실무 스킬 공유 : 막대 차트를 더욱 생생하게 만드는 방법! 》이 글에서는 엑셀 막대 차트를 더욱 생생하고 눈에 띄게 만드는 방법에 대해 알아봤습니다. 오늘은 스마트 출석부 만드는 방법에 대해 알아보겠습니다!

출석기가 그다지 똑똑하지 않고 수동 보조 통계가 필요한 경우, 회사에서 아직 출석기를 사용하지 않은 경우 당월 출석표를 만드는 것은 복잡하고 긴 과정입니다. 따라서 제한된 시간 내에 어떻게 업무 효율을 높이고 월별 출석 통계를 완성할 수 있는지가 인사부 MM에게는 큰 문제입니다.

효율성을 높이려면 더 좋은 출석부가 있어야 합니다. 이 출석부는 최소한 다음 사항을 갖추어야 합니다.

• 출석 항목 입력을 단순화합니다.

• 주말 데이터를 눈에 띄게 표시합니다.

• 데이터를 자동으로 요약할 수 있습니다.

오늘은 제가 가르쳐 드리겠습니다. 위 3가지를 동시에 갖춘 스마트 출석부 만드는 방법, 구경오세요!

만들기를 시작하기 전에 최종 효과 표시를 살펴보겠습니다.

시연에서 다음 사항을 확인할 수 있습니다.

• 제목 입력은 매우 간단합니다. 월 번호

• 월의 일수가 자동으로 변경되고, 날짜가 월에 따라 자동으로 조정되어 표시되며, 토요일과 일요일은 자동으로 색상이 변경됩니다.

• 출석 데이터는 드롭을 사용하여 입력됩니다. -다운 메뉴를 클릭하면 프롬프트 정보가 표시됩니다.

• 양식 오른쪽에 출석이 있습니다. 결과 통계 영역은 입력된 내용을 기반으로 자동으로 통계 데이터를 생성합니다.

이러한 형태를 만드는 방법을 가르쳐 드리겠습니다.

1. 출석부 본체 제작

먼저 그림과 같이 가장 기본적인 형태를 만듭니다.

이 형태의 준비는 매우 간단합니다. 완료하시면 됩니다. 다음은 조금 세세한 작업이므로 꼭 잘 살펴보시기 바랍니다.

첫 번째 행과 두 번째 행의 마지막 5개 셀을 각각 병합하고, 완성된 효과는 아래와 같습니다.

첫 번째 행에서 병합된 셀을 클릭하고 마우스 오른쪽 버튼을 클릭하고 "를 선택합니다. 셀 서식 지정"을 참조하세요. 팝업 대화 상자에서 사용자 정의된 형식은 다음과 같습니다: 10월 Xinsheng 회사 출석 통계표.

여기에는 문자 O가 아니라 숫자 0이 있습니다.

설정 후 확인을 클릭한 다음 첫 번째 줄에 숫자를 입력하여 효과를 확인하세요.

사실 이게 커스텀포맷의 특성을 잘 활용한거 아닌가요?

두 번째 줄에 "월의 일수"를 직접 입력하고 다음 공식을 입력하세요: =DAY(EDATE(DATE(2018,A1,1),1)-1)

이 공식에 대한 간략한 소개 의 의미는 세 가지 날짜 함수가 사용됩니다:

• DATE 함수의 형식은 DATE(년, 월, 일)입니다. 즉, 지정된 연도를 기준으로 날짜를 얻습니다. 월, 일입니다. 이 예에서 연도는 2018입니다(다음 연도인 경우 2019로 변경하면 됩니다). 월은 첫 번째 줄에 입력된 숫자이고 일은 1입니다. 매월 1일 출석부에 기재됩니다.

• EDATE 함수의 형식은 EDATE(시작 날짜, 월 간격)입니다. 즉, 지정된 날짜와 간격 월 수를 기준으로 새 날짜를 가져옵니다. 이 예에서는 시작 날짜입니다. 는 그 달의 1일이고, 개월 간격이 1이면 다음 달 1일의 날짜를 나타냅니다.

• DAY 함수의 형식은 DAY(날짜)입니다. 이는 지정된 날짜의 일자를 가져옵니다. 이 예에서 지정된 날짜는 다음 달 1일의 하루 전입니다. EDATE 함수 다음에 1을 뺀 날) 즉, 해당 월의 마지막 날이 됩니다. 그런 다음 DAY 함수를 사용하여 해당 월의 총 일 수를 가져옵니다.

참고: 날짜 함수는 여러 함수와 함께 사용되는 경우가 많으며 동일한 문제에 대해 여러 가지 아이디어가 있습니다. 일부 수식에는 매우 기발한 아이디어가 있는데, 이는 함수 학습의 재미 중 하나이기도 합니다.

다음으로 설정할 것은 요일입니다. C4 셀에 =TEXT(DATE(2018,$A1,C3),"aaa") 수식을 입력하고 오른쪽으로 드래그합니다.

공식 =TEXT(DATE(2018,$A$1,C$3),"aaa")는 TEXT와 DATE라는 두 가지 함수를 각각 사용합니다. =TEXT(DATE(2018,$A,C),"aaa")中用到了两个函数TEXT和DATE，分别解释一下这两个函数的作用：

DATE函数刚才已经讲过了，在这个公式中，年还是2018，月用的A1，因为公式要向右拉，为了防止右拉的时候A1发生变化，所以在列号前面加了$锁定，日就用第三行对应的数字表示，这样就得到了当月所有的日期。

TEXT函数的格式为：TEXT（要指定格式的数据，格式代码）。这个函数算是一个比较高级的函数了，虽然结构比较简单，但是格式代码非常多，所以也是一个多功能函数。本例中的格式代码为“aaa”，就是用一个字来显示星期，有兴趣的读者可以自己试试代码“aaaa”“ddd”和“dddd”分别是什么效果吧。

注：在使用TEXT函数的时候，格式代码必须加引号（引号在英文状态输入）。

至此，表格大体上已经完成了，有些单位可能不区分上午和下午，但有些单位是区分的，如何将每个人的一行变成两行，难道一行一行插入么？肯定不是，这里用到了一个非常简单的小技巧，一起通过动画演示来看看吧：

在这个操作中，涉及到几个技巧：批量插入空行的技巧、使用格式刷的技巧、快速填充重复内容的技巧，希望大家能够多练习，熟练掌握这些技巧。

至此，这个考勤表的主题已经做好了，效果是这样的：

二、使用条件格式和数据有效性加工表格

接下来需要完成两项工作：

• 让表格中的日期根据实际情况显示，同时周六周日会自动变色；

• 考勤数据使用下拉菜单完成输入，同时有提示信息。

来看看如何实现这些效果。对于第一项内容，要用到条件格式这个功能，步骤虽然不多，但是很多细节地方，大家慢慢跟着来：

（1）选择数据区域，点击【条件格式】-【新建规则】：

进一步选择【使用公式确定要设置格式的单元格】，输入公式：=C>$AF DATE 함수가 방금 언급되었습니다. 이 수식에서 연도는 여전히 2018이고 월은 A1입니다. 수식을 오른쪽으로 당겨야 하기 때문에 오른쪽으로 당길 때 A1이 변경되는 것을 방지하려면 $ 잠금이 필요합니다. 열 번호 앞에 추가하고 날짜는 3번째 줄의 해당 숫자를 이용하여 표현하면 해당 월의 날짜를 모두 알 수 있습니다.

TEXT 함수의 형식은 TEXT(형식을 지정하는 데이터, 형식 코드)입니다. 이 기능은 비교적 고급 기능으로 간주됩니다. 구조는 비교적 간단하지만 형식 코드가 많기 때문에 다기능 기능이기도 합니다. 이 예의 형식 코드는 "aaa"로, 한 단어를 사용하여 요일을 표시합니다. 관심 있는 독자는 각각 "aaaa", "ddd" 및 "dddd" 코드의 효과를 시도해 볼 수 있습니다.

참고: TEXT 기능을 사용할 때 형식 코드는 따옴표로 묶어야 합니다(따옴표는 영어 모드에서 입력됩니다).

이쯤 되면 표는 기본적으로 완성된 상태인데, 어떤 유닛은 오전과 오후 구분이 안 될 수도 있지만, 어떤 유닛은 한 사람당 한 줄씩 두 줄로 바꾸는 것도 가능한가요? ? 확실히 그렇지 않습니다. 여기서는 매우 간단한 트릭을 사용하여 애니메이션 데모를 살펴보겠습니다.

이 작업에는 빈 줄을 일괄 삽입하는 기술, 형식 페인터를 사용하는 기술, 빠르게 삽입하는 기술 등 여러 기술이 사용됩니다. 중복된 콘텐츠 기술을 작성하는 데 더 많은 연습을 하여 이러한 기술에 능숙해지기를 바랍니다. 이제 이 출석부 테마는 완성되었으며, 효과는 다음과 같습니다.

2. 조건부 서식 및 데이터 유효성을 사용하여 테이블 처리

다음 두 가지 작업을 완료해야 합니다:

• 표의 날짜를 실제 상황에 따라 표시하고 토요일과 일요일을 표시하도록 합니다. li>

• 드롭다운 메뉴를 사용하여 출석 데이터 입력을 완료하면 동시에 프롬프트 정보가 표시됩니다.

이러한 효과를 얻는 방법을 살펴보겠습니다. 첫 번째 항목의 경우 조건부 서식 기능을 사용해야 합니다. 단계는 많지 않지만 세부 사항은 많습니다.

(1) 데이터 영역을 선택하고 [조건부 서식] - [새 규칙]을 클릭합니다. ]:

더 자세히 [수식을 사용하여 서식을 지정할 셀 결정]을 선택하고 수식:

=C$3>$AF$2을 입력한 다음 [서식] 버튼을 클릭합니다:

🎜🎜공식 분석: 매우 간단합니다. 세 번째 줄의 각 날짜가 해당 월의 일 수보다 큰지 비교하는 것입니다. 이 단계에서는 설정된 형식에 따라 실행합니다. $의 위치. 🎜🎜[Custom]을 선택하고 유형에 세미콜론 3개를 입력합니다: ;;, 영어에서는 세미콜론이라는 점에 유의하고 완료 후 확인을 클릭합니다. 🎜🎜🎜🎜🎜형식 코드 설명: 구체적인 의미는 더 복잡합니다. 기억하세요. 🎜 세미콜론을 세 개 입력하면 셀의 내용을 표시하지 않는다는 의미입니다. 🎜🎜🎜확인을 다시 클릭하면 이번 달 날짜에 속하지 않는 데이터가 더 이상 표시되지 않는 것을 확인할 수 있습니다. 🎜🎜🎜🎜🎜토요일과 일요일에 자동 색상 변경을 추가로 설정하려면, 여전히 데이터 영역을 선택하고 새 규칙을 만들고 수식 =OR( C$4="six", C$4="day")를 입력한 다음 형식을 설정할 수 있습니다. 🎜🎜🎜🎜🎜이 수식도 쉽습니다. OR은 두 가지 조건 중 하나라도 만족하면 설정된 형식에 따라 실행되며, 동작이 "6" 또는 "일"인 경우 지정된 색상이 표시됩니다. 🎜🎜셀 서식 버튼을 클릭한 후 [채우기]를 선택하고 색상을 선택한 후 확인을 클릭하세요. 🎜🎜🎜🎜🎜확인을 다시 클릭하면 효과가 나타납니다. 🎜🎜🎜🎜🎜야, 왜 뒤쪽의 빈 공간도 색칠되어 있습니까? 🎜

주의 깊게 보니 이곳이 우연히 토요일이었는데 이전에 설정한 형식으로 숨겨져 있었는데 색칠할 때 여전히 적용되는 문제를 어떻게 해결해야 할까요? 매우 간단합니다. 데이터 영역을 선택한 후 조건부 서식 관리 규칙을 엽니다.

다음 규칙 뒤에 "참이면 중지"를 선택한 다음 위로 이동 버튼을 클릭하고 확인을 클릭합니다.

효과를 보세요.

다음 단계는 출석 데이터의 유효성을 설정하는 것입니다. 이 단계를 진행하기 전에 먼저 통계 영역 생성을 완료하겠습니다.

3. 통계 영역 생성

이 영역의 생성은 매우 간단합니다. 실제 필요에 따라 비고란을 추가하고 출석 항목 및 범례를 입력하고 각 범례 아래에 COUNTIF 기능을 사용합니다. 통계를 완성하기 위한 공식은 =COUNTIF($C5:$AG6,AI)/2:

COUNTIF 함수의 형식은 다음과 같습니다. COUNTIF(통계 영역, 계산할 콘텐츠), 일반인의 용어로, 이는 계산할 각 출석 항목입니다. 예시에서는 일자별 데이터가 2행이므로 통계 결과를 2로 나누어야 합니다.

병합된 셀을 처리하는 방법은 매우 간단합니다. 하나를 병합한 다음 형식 브러시를 사용하여 브러싱하면 됩니다.

이제 다시 돌아가서 출석 데이터 드롭다운 메뉴를 설정하세요. 유효성을 설정하기 전에 먼저 몇 가지 준비 작업을 수행하고 나중에 사용할 수 있도록 범례와 설명을 복사한 다음 유효성을 설정하십시오. 이 작업 부분에 대한 애니메이션 데모를 살펴보겠습니다.

이 시점에서 출석 시트가 완료됩니다. 오늘 내용을 통해 많은 것을 얻으실 수 있으실 거라 믿습니다!

관련 학습 권장 사항: excel 튜토리얼

위 내용은 실용적인 엑셀 스킬 공유 : 스마트한 출석부 만들기의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!