3-58. 스프레드시트 정보 함수 활용

혹시 이런 경험 없으신가요? 친구들과 함께 설문조사를 했는데, 숫자를 적을 칸에 누군가 "모름"이라고 글자를 적거나, 중요한 칸을 그냥 비워둬서 통계가 엉망이 되었던 경험 말이에요. 데이터가 많아질수록 이런 '숨은 범인(오류)'을 찾는 건 정말 어려운 일이죠.

하지만 걱정 마세요! 우리에게는 데이터 속 숨은 오류를 귀신같이 찾아내는 비밀 탐정 도구, 정보 함수가 있으니까요! 오늘은 스프레드시트의 돋보기 같은 이 '정보 함수'들을 활용해 데이터 탐정이 되는 법을 쉽고 재미있게 알려드릴게요.

 

🔗 지금 바로 사용해보세요!

📥 정보 함수 활용 스프레드시트 바로가기

클릭 한 번으로 바로 접속해서 복사하여 사용하실 수 있습니다

 

1. 정보 함수, 왜 필요할까요? (개념과 필요성)

 

**정보 함수(Information Functions)**는 셀에 담긴 데이터가 어떤 종류인지, 혹은 어떤 상태인지를 알려주는 함수입니다. 계산을 하는 대신, "이 칸은 비어있니?", "이 칸에 있는 게 숫자니?", "혹시 오류가 발생했니?" 같은 질문에 TRUE(참) 또는 **FALSE(거짓)**로 대답해 주죠.

정보 함수가 꼭 필요한 이유

• 데이터의 건강검진: 우리가 입력한 데이터가 깨끗하고 정확한지 진단해 줍니다. 잘못된 데이터가 섞여 있으면 SUM이나 AVERAGE 같은 다른 함수의 계산 결과 전체가 엉뚱하게 나올 수 있기 때문이죠.
• 오류 사전 방지: 계산 과정에서 발생할 수 있는 #DIV/0! (0으로 나눔) 같은 오류를 미리 찾아내고, IF 함수와 함께 사용하여 오류 대신 "값을 입력하세요" 같은 안내 문구를 보여줄 수도 있습니다.
• 시간 절약: 수백, 수천 개의 데이터 속에서 잘못된 부분을 눈으로 하나하나 찾는 것은 불가능에 가깝습니다. 정보 함수와 '조건부 서식'을 함께 사용하면 1초 만에 문제 있는 셀들을 다른 색으로 표시할 수 있습니다.

오늘은 데이터 탐정의 기본 장비와도 같은 ISBLANK, ISNUMBER, ISTEXT, ISERROR 함수에 대해 집중적으로 배워보겠습니다.

 

2. 실습 방법: 단계별 적용법

 

스프레드시트(엑셀, 구글 시트 등)를 켜고, 데이터 탐정의 도구를 하나씩 사용해 봅시다!

 

1) ISBLANK 함수: 빈칸을 찾아내는 추적 전문가

 

ISBLANK 함수는 선택한 셀이 비어있는지 아닌지를 검사합니다. 비어있으면 TRUE, 무엇이든 들어있으면 FALSE를 보여줍니다. 과제 제출 여부 확인에 딱이죠!

• 상황: 친구들의 과제 제출 여부 목록에서, 아직 내지 않은 친구(빈칸)를 찾아내고 싶어요.
• 수식: =ISBLANK(value)
• 적용:

1. 결과를 표시할 셀에 =ISBLANK( 라고 입력합니다.
2. 검사하고 싶은 셀(예: 과제 제출란인 B2셀)을 클릭합니다.
3. 괄호를 닫고 엔터를 칩니다. =ISBLANK(B2)
4. 결과: B2셀이 비어있다면 TRUE, 무엇이든 적혀 있다면 FALSE가 나타납니다.

 

2) ISNUMBER 함수: 숫자만 골라내는 감식 전문가

 

ISNUMBER 함수는 셀 안의 데이터가 숫자인지 아닌지를 판별합니다. 숫자이면 TRUE, 글자나 다른 것이면 FALSE를 보여줍니다. 설문조사에서 나이나 키처럼 숫자만 입력받아야 할 때 유용해요.

• 상황: 반티 사이즈 조사를 '95', '100'처럼 숫자로 받았는데, 몇몇 친구가 'L', 'M'처럼 문자로 입력했어요. 숫자만 제대로 입력됐는지 검사하고 싶어요.
• 수식: =ISNUMBER(value)
• 적용:

1. 결과를 표시할 셀에 =ISNUMBER( 라고 입력합니다.
2. 사이즈가 입력된 B2셀을 클릭하고 수식을 완성합니다. =ISNUMBER(B2)
3. 결과: B2셀에 '100'이 있다면 TRUE, 'L'이 있다면 FALSE가 나타납니다.

 

3) ISTEXT 함수: 글자만 쏙쏙 찾아내는 분석 전문가

 

ISTEXT 함수는 ISNUMBER와 반대로 셀 안의 데이터가 텍스트(글자)인지를 검사합니다.

• 상황: 위 반티 사이즈 조사 예시에서, 잘못 입력된 '텍스트' 값들만 따로 찾아내고 싶어요.
• 수식: =ISTEXT(value)
• 적용:

1. 결과를 표시할 셀에 =ISTEXT( 라고 입력합니다.
2. 사이즈가 입력된 B2셀을 클릭하고 수식을 완성합니다. =ISTEXT(B2)
3. 결과: B2셀에 '100'이 있다면 FALSE, 'L'이 있다면 TRUE가 나타납니다.

 

4) ISERROR 함수: 오류의 흔적을 놓치지 않는 해결사

 

ISERROR 함수는 셀에 #N/A, #VALUE!, #DIV/0! 등 계산 오류가 발생했는지를 확인합니다. 오류가 맞다면 TRUE, 정상이면 FALSE를 보여줍니다.

• 상황: 시험 점수 평균을 계산하는데, 누군가 점수 칸에 '결시'라고 적어두어 평균 계산 셀에 #VALUE! 오류가 발생했습니다. 이 오류를 감지하고 싶어요.
• 수식: =ISERROR(value)
• 적용:

1. 결과를 표시할 셀에 =ISERROR( 라고 입력합니다.
2. 오류가 발생한 평균 점수 셀(B2)을 클릭하고 수식을 완성합니다. =ISERROR(B2)
3. 결과: B2셀에 #VALUE! 오류가 있다면 TRUE, 정상적인 숫자라면 FALSE가 나타납니다.

---

3. CSV 예시 데이터

 

아래는 학생들이 제출한 과제 데이터를 검증하는 가상 시나리오입니다. B열(예시데이터1)에는 숫자, 텍스트, 빈칸, 오류 등 다양한 형태의 데이터가 섞여 있습니다. 이 데이터를 정보 함수로 어떻게 판별하는지 확인해 보세요.

 

코드 스니펫

번호,예시데이터1,예시데이터2,결과값
1,100,ISNUMBER,"'=ISNUMBER(B2)"
2,"완료",ISTEXT,"'=ISTEXT(B3)"
3,,ISBLANK,"'=ISBLANK(B4)"
4,#DIV/0!,ISERROR,"'=ISERROR(B5)"
CSV 파일 직접 만들기: 아래 50개 샘플 데이터 전체를 복사하여 메모장에 붙여넣고, 파일을 저장할 때 data_validation_list.csv 라고 이름을 지정한 뒤, 인코딩(파일 형식)을 UTF-8로 선택하여 저장하면 바로 사용할 수 있는 CSV 파일이 만들어집니다.

 

코드 스니펫

번호,예시데이터1,예시데이터2,결과값
1,100,ISNUMBER,"'=ISNUMBER(B2)"
2,제출완료,ISTEXT,"'=ISTEXT(B3)"
3,,ISBLANK,"'=ISBLANK(B4)"
4,95,ISNUMBER,"'=ISNUMBER(B5)"
5,88,ISNUMBER,"'=ISNUMBER(B6)"
6,미제출,ISTEXT,"'=ISTEXT(B7)"
7,100,ISNUMBER,"'=ISNUMBER(B8)"
8,76,ISNUMBER,"'=ISNUMBER(B9)"
9,,ISBLANK,"'=ISBLANK(B10)"
10,65,ISNUMBER,"'=ISNUMBER(B11)"
11,오류,ISTEXT,"'=ISTEXT(B12)"
12,100,ISNUMBER,"'=ISNUMBER(B13)"
13,92,ISNUMBER,"'=ISNUMBER(B14)"
14,85,ISNUMBER,"'=ISNUMBER(B15)"
15,,ISBLANK,"'=ISBLANK(B16)"
16,100,ISNUMBER,"'=ISNUMBER(B17)"
17,확인필요,ISTEXT,"'=ISTEXT(B18)"
18,80,ISNUMBER,"'=ISNUMBER(B19)"
19,77,ISNUMBER,"'=ISNUMBER(B20)"
20,95,ISNUMBER,"'=ISNUMBER(B21)"
21,,ISBLANK,"'=ISBLANK(B22)"
22,100,ISNUMBER,"'=ISNUMBER(B23)"
23,100,ISNUMBER,"'=ISNUMBER(B24)"
24,보류,ISTEXT,"'=ISTEXT(B25)"
25,90,ISNUMBER,"'=ISNUMBER(B26)"
26,,ISBLANK,"'=ISBLANK(B27)"
27,85,ISNUMBER,"'=ISNUMBER(B28)"
28,70,ISNUMBER,"'=ISNUMBER(B29)"
29,66,ISNUMBER,"'=ISNUMBER(B30)"
30,94,ISNUMBER,"'=ISNUMBER(B31)"
31,88,ISNUMBER,"'=ISNUMBER(B32)"
32,통과,ISTEXT,"'=ISTEXT(B33)"
33,,ISBLANK,"'=ISBLANK(B34)"
34,91,ISNUMBER,"'=ISNUMBER(B35)"
35,83,ISNUMBER,"'=ISNUMBER(B36)"
36,79,ISNUMBER,"'=ISNUMBER(B37)"
37,99,ISNUMBER,"'=ISNUMBER(B38)"
38,,ISBLANK,"'=ISBLANK(B39)"
39,100,ISNUMBER,"'=ISNUMBER(B40)"
40,재확인,ISTEXT,"'=ISTEXT(B41)"
41,95,ISNUMBER,"'=ISNUMBER(B42)"
42,82,ISNUMBER,"'=ISNUMBER(B43)"
43,76,ISNUMBER,"'=ISNUMBER(B44)"
44,,ISBLANK,"'=ISBLANK(B45)"
45,98,ISNUMBER,"'=ISNUMBER(B46)"
46,97,ISNUMBER,"'=ISNUMBER(B47)"
47,보충필요,ISTEXT,"'=ISTEXT(B48)"
48,89,ISNUMBER,"'=ISNUMBER(B49)"
49,93,ISNUMBER,"'=ISNUMBER(B50)"
50,#DIV/0!,ISERROR,"'=ISERROR(B51)"

 

4. XLSX 예시 템플릿 갤러리

 

진정한 데이터 탐정은 도구를 잘 활용해야죠! 여러분을 위해 데이터 검증부터 시각적인 대시보드 분석까지 한 번에 할 수 있는 '데이터 탐정 사무소' 템플릿을 준비했습니다. 아래 코드를 이용해 직접 파일을 생성하고 데이터 탐정 놀이를 시작해 보세요!

• XLSX 템플릿 파일 직접 생성하기:

1. 라이브러리 설치: 컴퓨터의 터미널(또는 명령 프롬프트)에 pip install openpyxl 을 입력해 필요한 프로그램을 설치합니다. (이전에 설치했다면 생략)
2. 코드 실행: 아래 파이썬 코드를 create_detective_template.py 라는 이름의 파일로 저장한 뒤 실행하세요. 코드와 같은 폴더에 data_detective_template.xlsx 파일이 마법처럼 생성될 거예요!

 

Python

# create_detective_template.py

import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.formatting.rule import FormulaRule
from openpyxl.chart import PieChart, Reference

# 1. 새 워크북 생성
wb = openpyxl.Workbook()

# --- 시트 1: 데이터_검증 시트 ---
ws1 = wb.active
ws1.title = "데이터_검증"

# 헤더
headers = ["번호", "제출 데이터", "빈칸 확인", "숫자 확인", "텍스트 확인", "오류 확인"]
ws1.append(headers)

# 샘플 데이터 (CSV 내용과 유사하게 생성)
raw_data = [
    100, "제출완료", None, 95, 88, "미제출", 100, 76, None, 65, "오류", 100, 92, 85, None, 100,
    "확인필요", 80, 77, 95, None, 100, 100, "보류", 90, None, 85, 70, 66, 94, 88, "통과",
    None, 91, 83, 79, 99, None, 100, "재확인", 95, 82, 76, None, 98, 97, "보충필요",
    89, 93
]
# 마지막에 오류 데이터 추가
ws1.append([50, "=1/0", "", "", "", ""])

# 데이터 및 수식 입력
for i, data in enumerate(raw_data, start=1):
    row_num = i + 1
    # 수식 적용
    formulas = [
        f"=ISBLANK(B{row_num})",
        f"=ISNUMBER(B{row_num})",
        f"=ISTEXT(B{row_num})",
        f"=ISERROR(B{row_num})"
    ]
    ws1.append([i, data] + formulas)

# 조건부 서식 적용 (데이터 탐정의 하이라이트!)
red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
yellow_fill = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")

# 규칙1: B열이 빈칸이면 노란색으로 (C열이 TRUE일 때)
ws1.conditional_formatting.add(f"B2:B{ws1.max_row}", FormulaRule(formula=[f"C2=TRUE"], fill=yellow_fill))
# 규칙2: B열이 숫자가 아니면 (숫자여야 할 데이터인데) 빨간색으로 (D열이 FALSE일 때)
ws1.conditional_formatting.add(f"B2:B{ws1.max_row}", FormulaRule(formula=[f"AND(D2=FALSE, C2=FALSE, E2=TRUE)"], fill=red_fill)) # 텍스트인 경우
ws1.conditional_formatting.add(f"B2:B{ws1.max_row}", FormulaRule(formula=[f"F2=TRUE"], fill=red_fill)) # 오류인 경우


# --- 시트 2: 실시간 데이터 검사기 ---
ws2 = wb.create_sheet("실시간 데이터 검사기")

ws2['B3'] = "데이터를 입력하면 종류를 판별해 드립니다!"
ws2['B5'] = "▶︎ 데이터 입력"

ws2['F4'] = "판별 결과"
ws2['F5'] = "ISBLANK (빈칸인가?)"
ws2['F6'] = "ISNUMBER (숫자인가?)"
ws2['F7'] = "ISTEXT (텍스트인가?)"
ws2['F8'] = "ISERROR (오류인가?)"
ws2['F9'] = "TYPE (데이터 종류는?)"

# 수식 입력
ws2['G5'] = "=ISBLANK(C5)"
ws2['G6'] = "=ISNUMBER(C5)"
ws2['G7'] = "=ISTEXT(C5)"
ws2['G8'] = "=ISERROR(C5)"
ws2['G9'] = "=TYPE(C5)"

ws2['F11'] = "* TYPE 결과: 1(숫자), 2(텍스트), 4(논리값), 16(오류)"


# --- 시트 3: 대시보드 ---
ws3 = wb.create_sheet("대시보드")
ws3['B2'] = "데이터 품질 분석 보고서"

# 요약 데이터 테이블
ws3['B5'] = "항목"
ws3['C5'] = "건수"
ws3['B6'] = "정상 데이터 (숫자)"
ws3['B7'] = "텍스트 데이터 (확인필요)"
ws3['B8'] = "누락 데이터 (빈칸)"
ws3['B9'] = "오류 데이터"

# COUNTIF로 데이터_검증 시트의 결과 요약
ws3['C6'] = "=COUNTIF('데이터_검증'!D:D, TRUE)"
ws3['C7'] = "=COUNTIF('데이터_검증'!E:E, TRUE)"
ws3['C8'] = "=COUNTIF('데이터_검증'!C:C, TRUE)"
ws3['C9'] = "=COUNTIF('데이터_검증'!F:F, TRUE)"

# 파이 차트 생성
pie = PieChart()
labels = Reference(ws3, min_col=2, min_row=6, max_row=9)
data = Reference(ws3, min_col=3, min_row=5, max_row=9)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "데이터 유형 분포"

ws3.add_chart(pie, "E5")

# --- 전체 시트 스타일링 ---
# (이전 답변의 스타일링 코드와 유사하게 적용 가능, 여기서는 생략)
# ... 스타일링 코드 ... (가독성을 위해 생략되었지만, 실제 생성시에는 포함되어야 함)


# 파일 저장
wb.save("data_detective_template.xlsx")
print("파일 생성 완료: data_detective_template.xlsx")

 

5. 실무 및 교육 현장 활용 사례 (중학생 탐정단 버전)

 

정보 함수는 우리 생활 속 다양한 문제를 해결하는 데 쓰일 수 있어요.

• 사례 1: 반티 사이즈 취합 대작전! (ISNUMBER & 조건부 서식)

• 사건: 2학년 5반 반장인 나희는 구글 시트로 반티 사이즈를 취합하고 있습니다. 사이즈는 90, 95, 100 같은 숫자로만 받아야 주문이 가능한데, 몇몇 친구들이 'M', 'L' 또는 '크게' 라고 적어냈습니다.
• 탐정의 해결: 나희는 사이즈가 적힌 열 옆에 =ISNUMBER(B2) 함수를 적용하여 숫자 형태가 아닌 셀들을 FALSE로 가려냈습니다. 그리고 한 단계 더 나아가, 조건부 서식 기능을 활용하여 FALSE가 나온 셀, 즉 사이즈가 숫자로 입력되지 않은 셀들을 모두 빨간색으로 칠해버렸죠. 덕분에 어떤 친구에게 다시 물어봐야 하는지 한눈에 파악하고 정확하게 주문을 마칠 수 있었습니다.

• 사례 2: 과학 실험 데이터의 함정을 피하라! (ISTEXT)

• 사건: 모둠원들과 용액의 온도를 1분마다 측정하는 과학 실험을 진행한 민준이네 모둠. 결과를 스프레드시트에 정리하고 평균 온도를 계산하려는데 자꾸 오류가 발생했습니다. 범인은 한 친구가 '25.3도' 라고 숫자 뒤에 '도'라는 단위를 함께 적어 넣었기 때문입니다. 스프레시트는 '25.3도'를 숫자가 아닌 텍스트로 인식하여 계산을 못 한 것이죠.
• 탐정의 해결: 민준이는 ISTEXT 함수로 온도 데이터 열을 검사했습니다. TRUE가 나온 셀을 찾아가 보니, 범인인 '25.3도'를 발견! '도'를 지우고 숫자 25.3만 남기자마자 평균 온도가 정상적으로 계산되었습니다.

• 사례 3: 사라진 설문지 응답자를 찾아라! (ISBLANK)

• 사건: 학교 축제 부스 만족도에 대한 온라인 설문조사를 실시한 학생회. 총 150명이 응답했지만, 중간중간 필수 항목인 '학년/반' 정보를 입력하지 않은 유령 응답자들이 있었습니다.
• 탐정의 해결: 학생회 담당자는 '학년/반' 정보가 담긴 열에 ISBLANK 함수 필터를 걸었습니다. 결과가 TRUE인 행들만 골라보니, 총 12명의 학생이 정보를 누락한 것을 바로 찾아낼 수 있었습니다. 이처럼 ISBLANK는 데이터의 완전성을 확인하는 데 아주 중요한 역할을 합니다.

---

데이터 탐정 훈련, 어떠셨나요? 오늘 배운 정보 함수들은 화려하진 않지만, 모든 데이터 분석의 가장 첫 단추인 '데이터 정제(Cleaning)' 과정에서 핵심적인 역할을 하는 아주 중요한 도구랍니다. 여러분도 다음번 학교 과제나 프로젝트를 할 때 이 탐정 도구들을 꼭 활용해서 데이터의 품질을 높여보세요. 정확한 데이터에서 정확한 결과가 나온다는 사실, 잊지 마세요!