카메라트랩 조사 집계, RAI 계산과 요약 보고서를 엑셀로 잇는 구조

대시보드 왼쪽 조사 구역 슬라이서에서 '대조구'를 누릅니다.

RAI 분모 칸이 980에서 363으로 바뀝니다. 종별 막대도 전부 다시 그려집니다.

발전단지 주변 카메라트랩 조사를 옮겨 만든 엑셀 데모의 한 장면입니다. 카메라 다섯 대와 관찰 기록 62행은 모두 가짜 값입니다.

손으로 세는 집계표라면 이 순간이 제일 번거롭습니다. 카메라 한 대의 운영 일수가 뒤늦게 바뀌면 그 구역 지수를 처음부터 다시 내야 하기 때문입니다.

이 글은 생태 조사 실무에서 쓰던 엑셀 두 개를 데모로 옮기며 남긴 구조 기록입니다. 시트 흐름과 열 순서·조건부 서식은 원본을 따랐고, 집계는 수식이 보이도록 바꿨으며 값은 가짜입니다.

관찰수만 세면 틀리는 곳

데모의 너구리는 조사구와 대조구에서 각각 2번 찍혔습니다. 관찰수만 보면 두 구역이 같습니다.

그런데 조사구 카메라는 세 대가 617일을 돌았고, 대조구는 두 대가 363일을 돌았습니다. 노력량이 1.7배쯤 차이 납니다.

RAI(상대풍부도지수)는 이 차이를 걷어 내는 지표입니다. 관찰수를 유효 카메라일로 나누고 100을 곱합니다.

RAI = 관찰수 ÷ 유효 카메라일 × 100

이렇게 내면 너구리는 조사구 0.32, 대조구 0.55가 됩니다. 고라니는 관찰수가 11 대 2였는데 RAI로는 1.78 대 0.55입니다.

종	조사구 (617일)	대조구 (363일)
너구리	2회, RAI 0.32	2회, RAI 0.55
고라니	11회, RAI 1.78	2회, RAI 0.55
삵	3회, RAI 0.49	2회, RAI 0.55

모두 데모 데이터의 RAW와 카메라 대장에서 계산한 값입니다. 데모 화면에서는 대시보드 슬라이서로 구역을 고르면 이 RAI가 막대로 나옵니다. 관찰수는 촬영 기록 건수이고, 한 장에 찍힌 개체수는 따로 합산해 옆 열에 둡니다.

분모는 운영 대장 한 장에

노력량으로 정규화한다는 말은 분모가 관찰 기록 바깥에 있다는 뜻입니다. 그래서 데모에는 '카메라' 시트를 따로 뒀습니다.

카메라 번호와 구역, 유효 카메라일만 적는 표입니다. 맨 아래 합계 셀이 RAI의 분모가 됩니다.

RAI피벗 시트의 분모 열에는 이 합계 980이 들어갑니다. 종별 RAI는 모두 같은 분모 하나로 나뉩니다.

카메라가 한 대 늘거나 운영 일수가 줄면 대장에서 그 행만 고칩니다. 합계가 바뀌고 종별 RAI가 모두 따라옵니다.

구역별 지수도 같은 원리입니다. 슬라이서에서 구역을 고르면 분모가 그 구역 카메라일의 합으로 바뀝니다. 첫 장면에서 980이 363이 된 이유입니다.

관찰 RAW에 카메라일 열을 붙이는 방식도 가능은 합니다. 다만 같은 카메라의 운영 일수가 행마다 반복되면, 한 곳만 고쳤을 때 값이 둘로 갈라집니다.

원본 시트 순서를 그대로 둔 이유

발전단지 원본은 모니터링 RAW에서 Data, Pivot, 대시보드를 거쳐 국가생물종목록으로 시트가 흘렀습니다. 데모는 RAW→집계→대시보드→종목록 흐름을 따랐습니다.

관찰 기록을 적는 곳은 RAW 하나입니다. 구역·카메라 번호·날짜·시각·국명·개체수를 적습니다.

시간대와 주야는 사람이 고르지 않습니다. 촬영 시각의 시(hour)에서 새벽·오전·오후·야간 4구간과 주간·야간을 뽑아냅니다.

데모 RAW 62행 가운데 39행이 야간으로 분류됩니다. 주야 축이 피벗에 없으면 이 비중은 보고서에 드러나지 않습니다.

정규화는 파워쿼리가 맡습니다. 원본 기록을 RAW 시트의 열 순서에 맞춰 한 표로 모읍니다.

집계는 데모 안에서 수식이 보이도록 COUNTIF·SUMIF·COUNTIFS로 피벗을 재현했습니다. 실제 파일에서는 상황에 맞춰 고르면 됩니다.

방식	잘 맞는 상황	걸리는 점
피벗테이블	축을 바꿔 가며 볼 때	새로고침을 잊으면 옛 값이 남음
COUNTIFS 수식	셀마다 계산 근거를 보여야 할 때	축이 늘면 수식 열도 늘어남
파워쿼리	여러 카메라 기록을 한 표로 모을 때	처음 설정에 시간이 듦

데모에서 구역은 대시보드 슬라이서로 나누고, 시간대 4구간과 주야는 시간대피벗으로 나눕니다. 개발 영향 밖의 대조구와 비교하는 것이 이 조사의 목적이라 구역 슬라이서는 대시보드 맨 앞에 뒀습니다.

등급을 끌어오는 종 마스터

RAW에는 국명만 적습니다. 학명·목·과·멸종위기 등급은 국가생물종목록 시트에서 끌어옵니다.

발전단지 원본의 종 목록은 130종이었습니다. 데모에는 이번 조사에 실제로 찍힌 16종만 추려 넣었습니다.

등급은 조건부 서식으로 칠합니다. Ⅰ급은 빨강, Ⅱ급은 주황이라 보고서를 넘기다가도 해당 행이 먼저 눈에 걸립니다.

발전단지 데모에서는 산양이 Ⅰ급, 삵과 담비와 하늘다람쥐와 무산쇠족제비가 Ⅱ급으로 켜집니다. 산양은 대조구 카메라에만 두 번 찍혔습니다.

멸종위기 등급은 고시가 바뀔 때마다 달라질 수 있습니다. 파일마다 종 목록을 따로 들고 있으면 같은 종의 등급이 파일끼리 갈라집니다.

등급은 최신 고시를 확인한 뒤 종 마스터 한 곳에서만 고치도록 두세요.

국립공원 조사는 구역이 셋

두 번째 데모는 국립공원 조사입니다. 핵심구역·완충구역·대조구 3구역에 분류군 4개(우제목·식육목·설치목·고슴도치목)의 5~10월 기록을 담았습니다.

여기서는 지수보다 요약 보고서가 목적입니다. 종별·구역별로 개체수를 모아 조사종수·총 개체수·멸종위기종 현황을 첫 장에 올립니다.

데모 값으로는 기록 62건에서 15종과 개체 96마리, 멸종위기 5종이 나옵니다. 분류군 슬라이서에서 우제목만 고르면 이 숫자들이 다시 계산됩니다.

구역과 분류군을 교차한 표는 SUMIFS로 냅니다. 어떤 종이 어느 분류군인지는 이쪽도 종 마스터에서 가져옵니다.

두 데모는 결과물로 나뉩니다. 발전단지 쪽은 관찰 기록을 지수로 바꾸는 계산 구조가 중심입니다.

국립공원 쪽은 정해진 입력 구조로 받은 기록을 요약 보고서까지 잇는 파일입니다.

지금 쓰는 파일을 점검하는 질문

조사 파일을 열어 놓고 아래 표를 위에서부터 짚어 보세요.

점검 질문	걸리면 생기는 일
관찰 RAW와 카메라 운영 대장을 분리했는가	카메라일이 바뀔 때마다 지수 칸을 손으로 고침
종 마스터에서 학명과 등급을 끌어오는가	같은 종의 등급이 파일마다 달라짐
조사구·대조구를 슬라이서나 피벗으로 나눠 볼 수 있는가	구역 비교표를 매번 필터로 따로 뽑음
RAW만 붙이면 보고서가 갱신되는가	보고서 숫자를 옮겨 적다 어긋남

둘 이상 걸리면 수식을 손대기 전에 시트 구조부터 다시 잡기를 권합니다. RAW와 운영 대장, 종 마스터가 제자리에 있으면 나머지는 참조로 이어집니다.

팀 안에서 직접 고쳐 나가려면 엑셀 양식 설계 교육이 맞습니다. 이 두 데모를 입력 구조와 계산 구조의 예제로 씁니다.

지금 파일을 이 구조로 옮기는 일을 맡기려면 대시보드 제작 문의를 이용하세요. 값을 지운 빈 양식이나 열 이름만 보내 주셔도 됩니다.

어디까지 자동으로 이을 수 있는지는 그 구조로 가늠합니다.

데모 둘러보기 순서

발전단지 생태 모니터링 데모를 열면 대시보드 시트가 먼저 뜹니다. 탭을 아래 순서로 옮겨 가며 보세요.

1. 카메라 시트에서 카메라별 유효 카메라일과 합계 셀을 봅니다. 조사구 617일, 대조구 363일입니다.
2. RAI피벗 시트에서 RAI 셀을 눌러 보세요. 수식바에 관찰수 열을 카메라일 열로 나누는 식이 뜹니다.
3. 모니터링RAW 시트를 내려 보면 멸종위기종 행에 색이 들어가 있습니다. 국가생물종목록 시트를 같이 열면 등급이 어디서 오는지 보입니다.
4. 대시보드로 돌아와 조사 구역 슬라이서를 누릅니다. 분모와 RAI 막대가 함께 바뀝니다.

요약 보고서는 국립공원 카메라트랩 조사 데모의 DashBoard 시트에서 확인합니다. 대조구만 눌렀을 때 조사종수와 총 개체수가 어떻게 줄어드는지 보세요.

발전단지 파일에서 숫자를 적는 곳은 RAW와 카메라 운영 대장 두 곳뿐입니다. 내 파일에서 RAW·운영 대장 밖에 손으로 적는 숫자 칸이 몇 개인지 세어 보는 것으로 점검을 시작하면 됩니다.