스프레드시트 활용/41~60. 데이터 관리 및 처리

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

JS's Spreadsheets 2025. 9. 16. 12:53

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

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

 

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

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

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

 

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)' 과정에서 핵심적인 역할을 하는 아주 중요한 도구랍니다. 여러분도 다음번 학교 과제나 프로젝트를 할 때 이 탐정 도구들을 꼭 활용해서 데이터의 품질을 높여보세요. 정확한 데이터에서 정확한 결과가 나온다는 사실, 잊지 마세요!