[Excel] 빅테크 주식 데이터 분석

🛠️ 사전 준비: 데이터 가져오기

실습을 시작하기 전, CSV 파일들을 엑셀로 불러와야 합니다.

1. 새 엑셀 파일 열기
2. 데이터 가져오기:
   • 메뉴: 데이터(Data) 탭 > 텍스트/CSV(From Text/CSV) 클릭.
   • Amazon.csv 파일 선택 > 가져오기(Import) > 로드(Load) 클릭.
3. 시트 이름 변경: 아래 시트 탭을 더블 클릭하여 이름을 Amazon으로 변경.
4. 반복: 위 과정을 Apple, Facebook, Google, Netflix에 대해 반복하고 각각 시트 이름을 지정.
5. 저장: 파일명을 Tech_Stocks_Analysis.xlsx로 저장.

📉 주식 데이터 분석 (Step-by-Step)

1. 날짜 및 변동폭 데이터 가공 (각 시트에서 수행)

Amazon 시트로 이동하여 데이터 오른쪽에 새로운 열(Header)을 만들고 수식을 입력합니다. (데이터가 2행부터 시작한다고 가정)

• J1 (연도): Year 입력 → J2 셀에 =YEAR(A2)
• K1 (월): Month 입력 → K2 셀에 =MONTH(A2)
• L1 (일일변동$): Change($)입력 → L2 셀에=E2-B2\ (Close - Open)
• M1 (일일변동%): Change(%) 입력 → M2 셀에 =(E2-B2)/B2 (서식: (홈)표시 형식에서 백분율 -> 소수점 둘째자리 까지)
• N1 (고저차): Range 입력 → N2 셀에 =C2-D2 (High - Low)
• 채우기 핸들: 작성한 수식들을 더블 클릭하여 맨 아래 행까지 복사합니다. (모든 회사 시트에 반복)

2. Summary 시트 제작 & 데이터 유효성 검사

맨 앞에 새 시트를 만들고 Summary로 이름 짓습니다.

• A열 (회사명): Amazon, Apple, Facebook, Google, Netflix 입력.
• B열 (월 선택 - 드롭다운 만들기):
  1. B2 셀 클릭.
  2. 메뉴: 데이터 > 데이터 유효성 검사(Data Validation).
  3. 제한 대상: 목록(List).
  4. 원본: 1,2,3,4,5,6,7,8,9,10,11,12 입력.
• C열 (연도 선택): 위와 동일한 방법으로 데이터에 있는 연도(예: 2015, 2016...)를 목록으로 만듭니다.

3. 핵심 수식 구현 (Summary 시트)

이제 AVERAGEIFS와 INDEX/MATCH를 사용합니다.

---

엑셀의 INDIRECT 함수를 사용하면 시트 이름을 직접 고치지 않고, 셀에 적힌 텍스트(회사명)를 이용해 자동으로 시트를 찾아가게 만들 수 있습니다.

1. COUNTIF 수식 변경 (E열)

기존 수식 대신 아래 수식을 E2 셀에 입력하고 아래로 드래그해 보세요. (A2 셀에 'Amazon'이라는 글자가 있다고 가정합니다.)

=COUNTIF(INDIRECT($A2 & "!L:L"), ">0")

• 설명: INDIRECT($A2 & "!L:L")은 A2 셀의 값("Amazon")과 "!L:L"을 합쳐서 실제 주소인 Amazon!L:L로 변환해줍니다.

---

D열: 월평균 종가 (AVERAGEIFS) Amazon 행(2행)에 입력할 수식입니다. (다른 회사는 시트 이름만 변경)

=AVERAGEIFS(Amazon!E:E, Amazon!K:K, $B2, Amazon!J:J, $C2)

• 설명: Amazon 시트 E열(종가) 평균, 조건1: K열(월)이 B2와 같음, 조건2: J열(연도)이 C2와 같음.

E열: 주가 상승 횟수 (COUNTIF) Change($) 열(L열)이 0보다 큰 경우를 셉니다.

=COUNTIF(Amazon!L:L, ">0")

F열: 최고 실적일 날짜 찾기 (INDEX + MATCH) 가장 높은 변동률(%)을 기록한 날짜를 가져옵니다.

=INDEX(Amazon!A:A, MATCH(MAX(Amazon!M:M), Amazon!M:M, 0))

• 설명: M열(변동률)에서 최대값(MAX)의 위치(MATCH)를 찾아, A열(날짜)의 값을 가져옴(INDEX).