컴퓨터활용능력 1급으로 만드는 유통기한 관리표, 엑셀로 임박 상품을 자동 표시하는 방법
상품의 날짜를 눈으로 하나씩 확인하면 품목이 늘어날수록 누락이 생기기 쉽다. 엑셀에서는 표시된 소비기한을 날짜 데이터로 입력하고 TODAY 함수로 오늘과의 차이를 계산한 뒤 IF 함수와 조건부서식으로 경과·임박·정상 상태를 자동 구분할 수 있다. 이 글은 가상 상품 8개와 2026년 10월 30일이라는 예시 기준일을 사용해 셀 배치부터 수식, 색상 규칙까지 순서대로 설명한다.
관리표를 만들기 전에 날짜의 뜻부터 구분한다
유통기한과 소비기한을 같은 값으로 다루면 안 되는 이유
유통기한은 제품의 제조일부터 소비자에게 유통·판매가 허용되는 기한을 뜻하고, 소비기한은 식품에 표시된 보관방법을 지켰을 때 안전하게 섭취할 수 있는 기한을 뜻한다. 두 용어는 모두 날짜처럼 보이지만 판단 목적이 다르다. 식품의약품안전처가 안내한 소비기한 표시제도 이러한 차이를 소비자가 알기 쉽게 전달하기 위해 도입됐다. 따라서 재고표를 만들 때 열 제목을 막연히 ‘기한’이라고 적기보다 실제 포장에 적힌 표시가 유통기한인지 소비기한인지 구분해야 한다. 서로 다른 성격의 날짜를 한 열에 섞으면 같은 수식으로 상태를 계산하더라도 결과를 해석하는 단계에서 혼선이 생긴다.
이 예제에서는 제품 포장에 표시된 소비기한을 C열에 그대로 입력한다고 가정한다. 입고일 B열은 매장이나 창고에 들어온 날짜를 기록할 뿐 소비기한을 대신하지 않는다. 예를 들어 입고가 늦었다고 해서 포장에 표시된 소비기한이 자동으로 연장되는 것은 아니며, 입고일에 임의의 보관 일수를 더해 소비기한을 새로 만들어서도 안 된다. 소비기한은 제품 특성과 제조방법, 포장, 보관조건 등을 고려해 설정되는 정보이므로 관리자는 표시값을 정확히 옮기는 역할을 해야 한다. 엑셀은 이미 결정된 날짜를 비교하고 우선순위를 보여주는 도구이지 식품의 안전기한을 새로 판정하는 도구가 아니다.
또한 소비기한은 표시된 보관방법을 준수했다는 전제와 연결된다. 냉장 제품이 장시간 상온에 방치됐거나 포장이 손상되고 개봉된 경우에는 셀의 날짜가 남아 있어도 정상 재고라고 단정할 수 없다. 관리표에는 필요에 따라 보관구분, 개봉 여부, 손상 여부, 로트번호 같은 열을 추가할 수 있지만 이번 예제는 TODAY와 IF의 원리를 선명하게 보기 위해 제품명·입고일·소비기한·남은 일수·상태만 사용한다. 실제 업무에서는 회사의 식품 안전 절차와 제조사의 보관 지침을 함께 적용하고, 날짜 자동 표시만으로 폐기나 판매 여부를 독단적으로 결정하지 않아야 한다.
가상 상품 8개를 A열부터 E열까지 배치한다
셀 주소가 바뀌지 않도록 표의 뼈대를 먼저 만드는 순서
새 워크시트의 1행에 제목을 입력한다. A1은 제품명, B1은 입고일, C1은 표시된 소비기한, D1은 남은 일수, E1은 상태로 정한다. 데이터는 2행부터 9행까지 총 8개를 넣는다. A2부터 A9에는 가상우유, 가상샐러드, 가상두부, 가상요거트, 가상햄, 가상주스, 가상소스, 가상냉동만두를 순서대로 입력한다. 브랜드나 실제 판매상품을 분석한 것이 아니라 수식 연습을 위한 가상 자료다. B열과 C열은 텍스트가 아니라 엑셀이 인식하는 날짜값으로 입력하고 표시 형식은 yyyy-mm-dd로 맞추면 연도와 월, 일을 한눈에 비교하기 쉽다.
- 2행: 가상우유 / 입고일 2026-10-26 / 소비기한 2026-10-29
- 3행: 가상샐러드 / 입고일 2026-10-28 / 소비기한 2026-10-30
- 4행: 가상두부 / 입고일 2026-10-27 / 소비기한 2026-11-02
- 5행: 가상요거트 / 입고일 2026-10-25 / 소비기한 2026-11-06
- 6행: 가상햄 / 입고일 2026-10-20 / 소비기한 2026-11-07
- 7행: 가상주스 / 입고일 2026-10-18 / 소비기한 2026-11-15
- 8행: 가상소스 / 입고일 2026-10-10 / 소비기한 2026-12-01
- 9행: 가상냉동만두 / 입고일 2026-10-05 / 소비기한 2027-01-15
날짜가 올바르게 들어갔는지 확인하려면 C2를 선택하고 표시 형식을 일반 또는 숫자로 잠시 바꿔 볼 수 있다. 날짜가 엑셀의 일련번호로 나타나면 계산 가능한 날짜값일 가능성이 높고, 입력한 문자열이 그대로 보인다면 텍스트로 저장됐을 수 있다. 가장 안전한 방법은 셀에 2026-10-29처럼 입력한 뒤 날짜 형식을 적용하는 것이다. 외부 시스템에서 가져온 자료는 앞뒤 공백이나 지역별 날짜 순서 때문에 텍스트로 들어오는 경우가 있으므로 바로 수식을 복사하기 전에 몇 개 셀을 시험 계산한다. C2-C3 같은 의미 없는 비교보다 C2-TODAY()처럼 날짜와 오늘의 차이를 계산했을 때 숫자가 나오는지를 보는 편이 실용적이다.
표 범위 A1:E9를 엑셀 표로 변환하면 필터와 줄무늬, 열 제목 관리가 편해지지만 필수 단계는 아니다. 컴퓨터활용능력 1급 연습에서는 일반 셀 범위와 절대·혼합 참조를 이해하는 것이 우선이므로 이 글은 A1:E9의 일반 범위를 기준으로 설명한다. 이후 행이 늘어날 가능성이 크다면 표 기능을 적용하거나 충분한 예상 범위에 수식과 조건부서식을 미리 복사할 수 있다. 다만 빈 행에 상태 수식을 무작정 넣으면 소비기한이 없는 셀이 경과로 표시될 수 있으므로, 실제 확장판에서는 C열이 비었을 때 빈 문자열을 반환하는 예외 처리도 고려해야 한다.
TODAY 함수로 소비기한까지 남은 일수를 계산한다
D2 수식을 아래 행까지 복사하면 날짜 차이가 갱신되는 원리
D2에 =C2-TODAY()를 입력한다. TODAY 함수는 현재 날짜의 일련번호를 반환하고 C2도 날짜 일련번호이므로, 두 값을 빼면 소비기한까지 남은 일수가 정수로 계산된다. 결과가 양수면 그만큼 날짜가 남았고, 0이면 소비기한이 오늘이며, 음수면 이미 날짜가 지났다는 뜻이다. D2의 채우기 핸들을 D9까지 끌어내리면 상대참조 C2가 C3, C4처럼 행에 맞게 바뀌면서 각 상품의 결과가 계산된다. TODAY에는 인수가 없으므로 괄호 안을 비워 =TODAY()로 써야 하며, 기준일을 직접 입력한 것처럼 따옴표로 감싸지 않는다.
이 글의 화면 예시는 TODAY()가 2026-10-30을 반환한 가상 시점의 결과다. 따라서 D2는 2026-10-29에서 2026-10-30을 뺀 -1, D3은 같은 날짜이므로 0, D4는 3, D5는 7이 된다. 이어서 D6은 8, D7은 16, D8은 32, D9는 77이다. 같은 파일을 다른 날 열면 TODAY의 값이 달라져 남은 일수도 함께 바뀐다. 이 자동 갱신이 날짜 관리표의 장점이지만, 교육용 정답을 비교하거나 특정 시점의 보고서를 보존해야 할 때는 기준일을 별도 셀에 입력하고 그 셀을 참조하는 방식이 더 적합할 수 있다.
TODAY 결과는 컴퓨터의 시스템 날짜와 통합문서의 계산 상태에 영향을 받는다. 시스템 날짜가 잘못돼 있으면 표도 잘못 분류되고, 계산 옵션이 수동이면 최신 결과가 즉시 반영되지 않을 수 있다. 파일을 열었는데 날짜가 예상과 다르면 먼저 운영체제 날짜, 수식 표시 여부, 계산 옵션을 확인한다. D열이 45200처럼 큰 숫자로 보이면 수식이 틀린 것이 아니라 셀 서식이 날짜로 지정됐을 가능성이 있으므로 D열 표시 형식을 일반 또는 숫자로 바꾼다. 남은 일수는 날짜 모양보다 정수로 보여야 사용자가 우선 처리 순서를 빠르게 판단할 수 있다.
IF 함수로 경과·임박·정상 상태를 자동 분류한다
E2에서 먼저 경과 여부를 검사해야 하는 이유
E2에는 =IF(C2<TODAY(),"경과",IF(C2<=TODAY()+7,"임박","정상"))를 입력한다. 첫 번째 IF는 C2의 소비기한이 오늘보다 이전인지 검사한다. 참이면 곧바로 ‘경과’를 반환하고, 거짓이면 두 번째 IF로 넘어간다. 두 번째 IF는 소비기한이 오늘부터 7일 뒤까지의 범위에 들어오는지를 확인해 ‘임박’을 반환하며, 이 조건도 거짓이면 ‘정상’을 반환한다. 오늘 날짜와 같은 상품은 첫 조건 C2<TODAY()에는 해당하지 않지만 두 번째 조건에는 해당하므로 임박으로 분류된다. 7일 뒤 날짜도 <= 연산자를 사용했으므로 임박에 포함된다.
조건의 순서는 결과를 바꿀 수 있다. 만약 ‘C2<=TODAY()+7이면 임박’이라는 검사부터 실행하면 이미 지난 날짜도 7일 뒤보다 작기 때문에 임박으로 처리되고, 경과 상태를 따로 구분할 수 없다. 그래서 가장 제한적이고 우선순위가 높은 경과 조건을 먼저 검사한 뒤 임박 범위를 검사해야 한다. E2 수식을 E9까지 복사하면 C2 참조가 행별로 변경된다. 가상 기준일 2026-10-30에서 E2는 경과, E3부터 E5까지는 임박, E6부터 E9까지는 정상이다. D열의 숫자 결과와 E열의 문구가 이 대응과 일치하는지 대조하면 수식 입력 오류를 빠르게 찾을 수 있다.
실제 표에서는 소비기한이 비어 있는 새 행을 고려할 수 있다. 빈 C열이 날짜 0처럼 평가되는 상황을 막으려면 =IF(C2="","",IF(C2<TODAY(),"경과",IF(C2<=TODAY()+7,"임박","정상")))처럼 가장 바깥쪽에 빈 셀 검사를 추가한다. 다만 시험 연습이나 처음 배우는 단계에서는 기본 수식의 논리 순서를 먼저 이해한 뒤 예외 처리를 덧붙이는 편이 좋다. ‘7일’을 회사 정책에 따라 바꾸려면 수식의 숫자를 직접 여러 곳에서 고치기보다 별도 기준 셀에 임박 일수를 입력하고 절대참조로 연결하면 관리가 쉬워진다. 기준 변경은 식품의 안전기한을 바꾸는 것이 아니라 업무상 알림 시점을 조정하는 것임도 구분해야 한다.
조건부서식으로 임박 상품의 행 전체를 강조한다
혼합참조를 사용해 A열부터 E열까지 같은 색을 적용하는 방법
색상은 E열 한 칸만 바꾸는 것보다 A2:E9 전체 행에 적용하면 제품명과 날짜를 함께 확인하기 쉽다. 먼저 A2:E9 범위를 선택하고 홈 탭의 조건부서식에서 새 규칙을 연 뒤 ‘수식을 사용하여 서식을 지정할 셀 결정’ 유형을 선택한다. 경과 규칙에는 =$C2<TODAY()를 입력하고 연한 빨강 채우기를 지정한다. 수식의 $는 C열을 고정하고 행 번호 2는 상대참조로 둔다. 따라서 규칙이 아래로 내려갈 때 C2, C3, C4를 차례로 검사하지만 색은 선택한 행의 A열부터 E열까지 적용된다. $C$2처럼 행까지 고정하면 모든 행이 C2 하나만 검사하므로 원하는 결과가 나오지 않는다.
임박 규칙은 =AND($C2>=TODAY(),$C2<=TODAY()+7)로 만들고 연한 노랑을 지정한다. 시작 조건을 $C2>=TODAY()로 함께 넣어야 지난 날짜가 임박 색으로 겹치지 않는다. 정상 규칙은 =$C2>TODAY()+7로 만들고 필요하면 연한 초록을 적용한다. 세 규칙은 서로 겹치지 않도록 작성됐기 때문에 결과가 명확하지만, 실제 파일에서 다른 규칙을 추가했다면 조건부서식 규칙 관리자에서 적용 범위와 우선순위를 확인한다. 적용 대상은 세 규칙 모두 =$A$2:$E$9가 되어야 하며, 새 행을 추가할 계획이라면 범위를 넓히거나 표 기능을 사용해 확장성을 확보한다.
조건부서식은 값을 바꾸는 기능이 아니라 화면 표시를 바꾸는 기능이다. 행이 빨갛게 보여도 C열의 실제 날짜와 E열의 상태값은 그대로이며, 필터나 다른 수식은 셀의 원래 값을 사용한다. 색에만 의존하면 흑백 인쇄나 색각 차이에서 정보가 약해질 수 있으므로 E열의 경과·임박·정상 문구를 함께 유지하는 편이 낫다. 규칙이 작동하지 않으면 C열 날짜가 텍스트인지, 적용 범위 첫 행이 수식의 행 번호와 맞는지, TODAY 뒤에 괄호가 있는지, 따옴표가 스마트 따옴표로 바뀌지 않았는지를 확인한다. 이 검수 순서는 컴퓨터활용능력 실기에서 수식 오류를 찾을 때도 유용하다.
버전과 입력 환경이 달라질 때 생기는 오류를 점검한다
Excel 데스크톱·웹과 지역 설정 차이를 확인하는 방법
TODAY와 IF, AND 및 수식 기반 조건부서식은 널리 사용되는 기능이지만 메뉴 위치와 화면 모양은 Excel 버전, 운영체제, 데스크톱과 웹 환경에 따라 다를 수 있다. 대한상공회의소의 현재 안내에서는 2024년부터 2026년까지 컴퓨터활용능력 시험 프로그램으로 Microsoft Office LTSC Professional Plus 2021을 사용한다. 시험을 준비한다면 자신이 연습하는 버전과 시험장 버전의 리본 메뉴, 함수 입력 방식, 조건부서식 규칙 관리 화면을 비교해야 한다. 이후 시험 프로그램은 변경될 수 있으므로 응시 직전에는 공식 시험 안내를 다시 확인하는 편이 안전하다.
지역 설정에 따라 함수 인수를 구분하는 문자가 쉼표가 아니라 세미콜론으로 표시되는 환경도 있다. 이 글의 수식은 쉼표를 사용하는 한국어 Excel의 일반적인 입력 예를 기준으로 작성했다. 날짜 표기도 2026-10-30, 2026/10/30처럼 입력 방식이 달라질 수 있으며, 10/11처럼 연도가 없거나 월과 일이 모호한 값은 환경에 따라 다르게 해석될 수 있다. 여러 사람이 파일을 공유한다면 날짜는 yyyy-mm-dd처럼 연도까지 포함해 입력하고, 셀 표시 형식과 실제 값이 같은지를 확인한다. CSV에서 가져온 날짜는 텍스트로 들어오기 쉬우므로 가져오기 단계에서 열 데이터 형식을 지정하는 것이 좋다.
또 하나의 흔한 오류는 표시된 날짜를 수식으로 계산할 수 있다고 오해하는 것이다. 입고일과 소비기한은 성격이 다르므로 B열에 일정 일수를 더해 C열을 자동 생성해서는 안 된다. 포장에 적힌 소비기한을 정확히 입력하거나 신뢰할 수 있는 내부 시스템에서 가져와야 한다. 같은 제품이라도 제조 로트에 따라 표시일이 다를 수 있어 제품명만 기준으로 일괄 덮어쓰는 것도 위험하다. 버전 차이는 메뉴와 기능 접근 방식의 문제이고, 데이터 정확성은 원자료와 입력 절차의 문제다. 두 문제를 분리해서 점검하면 수식은 맞는데 결과가 이상한 상황을 훨씬 빨리 진단할 수 있다.
컴퓨터활용능력 1급 지식을 실제 관리 흐름으로 확장한다
수식 작성에서 검수·정렬·업무 인계까지 연결하는 연습
컴퓨터활용능력 1급 실기에는 스프레드시트 실무가 포함되며, 함수와 데이터 관리 기능을 제한된 시간 안에 정확하게 다루는 능력이 요구된다. 소비기한 관리표는 TODAY와 중첩 IF, AND, 혼합참조, 조건부서식을 한 흐름으로 묶어 연습하기 좋은 사례다. 수식을 입력하는 데서 끝내지 말고 E열에서 ‘경과’만 필터링하고, D열을 오름차순으로 정렬해 처리 우선순위를 확인하며, C열의 빈 값이나 중복 입력을 점검하는 단계까지 이어가면 실제 업무 감각을 키울 수 있다. 원본 순서가 필요한 경우 정렬 전에 일련번호 열을 추가하거나 원본 시트를 보존해야 한다.
유통·물류·매장 운영이나 사무관리 업무에서는 입출고 자료를 정리하고 예외 항목을 찾는 작업이 반복된다. 자격증은 엑셀 기능의 기본 숙련도를 보여주는 한 요소가 될 수 있지만, 자격증만으로 특정 직무의 취업이나 업무 능력이 보장되는 것은 아니다. 실제 현장에서는 회사의 재고 시스템, 상품코드, 로트 추적, 식품 안전 절차를 이해하고 다른 사람이 검토할 수 있도록 수식과 기준을 문서화하는 능력이 함께 필요하다. 예제 파일을 포트폴리오처럼 활용하려면 가상 데이터임을 표시하고, 임박 기준 7일의 설정 근거와 변경 방법, 오류 처리 규칙을 설명할 수 있어야 한다.
완성 후에는 네 가지를 마지막으로 확인한다. 첫째, C열이 실제 날짜값인지 확인한다. 둘째, D열의 남은 일수와 E열 상태가 기준일에 맞는지 대조한다. 셋째, 조건부서식의 적용 범위가 A2:E9이고 $C2 혼합참조가 유지되는지 본다. 넷째, 소비기한 표시와 보관 상태를 함께 관리한다는 주의사항을 업무 인계서에 남긴다. 이 과정을 거치면 관리표는 단순히 색이 바뀌는 예제가 아니라 데이터 입력, 계산, 시각적 경고, 검수와 인계가 연결된 실무형 문서가 된다. 날짜나 기준이 바뀌더라도 수식 구조를 이해하고 있으면 안전하게 수정할 수 있다.