데이터 정리, 정리, 분석과 관련하여 Excel과 Google Sheets는 오랜 시간 동안 검증을 거쳐 왔습니다. 생성형 AI의 인기 속에서 스프레드시트는 여전히 마케팅 분석에 필수적인 도구입니다. 스프레드시트 기술을 향상시키는 5가지 팁을 소개합니다.
※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: 5 Excel and Google Sheets tricks every SEO should know
- URL: https://searchengineland.com/excel-google-sheets-tricks-seo-435310
개요
모든 유명한 SEO(Search Engine Optimization: 검색 엔진 최적화) 도구는 대부분의 데이터를 CSV 형식으로 내보냅니다. 데이터 분석을 위해 생성형 AI를 사용할 수 있지만 시작하려면 여전히 오래된 스프레드시트가 필요한 경우가 많습니다. Excel 및 Google 스프레드시트 기술을 향상시키는 5가지 팁을 통해 SEO 및 디지털 마케팅 담당자로서의 삶을 더 쉽게 만들 수 있는 방법을 살펴보겠습니다.
1. 여러 엑셀 파일 병합
이것은 기본적인 기능이지만, 의외로 많은 사람들이 이를 모르고 있습니다. 이런 상황을 생각해 보세요. 여러분은 키워드 조사를 통해 건강 회사를 돕고 있으며 그 회사는 경쟁이 치열한 틈새 시장에 있습니다. 처음에는 더 자세히 알아볼 수 있는 10가지 질병 목록이 제공됩니다.
SEO에서는 각각 약어가 있는 10개의 롱테일 문구에 해당합니다. 최종 결과는 40개 파일입니다. 이러한 파일을 하나의 큰 파일로 결합하려는 경우 대부분의 사용자는 각 파일을 연 다음 복사하여 붙여넣는 작업을 40번 수행하게 됩니다. 파일이 크고 여는 데 시간이 걸리면 분석을 시작하지도 못한 채 2시간 이상 걸릴 수도 있습니다. 시간이 많이 걸리는 접근 방식 대신 이렇게 해보세요.
- 새 폴더를 만들고 그 안에 모든 파일을 넣습니다.
- 새 Excel 파일을 열고 [데이터] 탭으로 이동합니다.
- [데이터 가져오기] - [파일에서] - [폴더에서]를 선택합니다.
Excel은 컬럼(열)이 동일한 모든 파일을 하나로 자동 병합합니다. 좋은 점은 Excel에서 데이터를 결합하기 전에 인코딩 유형을 묻는 메시지를 표시한다는 것입니다. 이렇게 하면 약 5분 안에 예를 들어 50,000개의 행이 있는 하나의 큰 파일이 생성됩니다.
5,000개의 행은 키워드 조사에 많은 양이며, 특히 이해관계자가 일반적으로 50~100개 이하의 구체적인 아이디어를 기대하는 경우에는 더욱 그렇습니다. 스프레드시트에서 검색량 데이터가 없는 키워드를 모두 제거한 후 철자가 틀린 키워드를 모두 제거할 수 있습니다.
2. 클릭 한 번으로 수천 단어의 맞춤법 검사
대용량 Excel 파일의 철자를 검사하는 방법이 있으며 이 방법은 영어와 기타 언어 모두에서 작동합니다. 매크로를 사용하면 가능합니다. 다음 매크로를 사용하면 현재 워크시트에서 철자가 틀린 모든 단어를 빨간색으로 강조 일괄 표시해 주므로 빠르게 바로잡을 수 있습니다.
''' 굳이 시트를 보호해야 할 필요는 없을 것 같아서 코드 일부 내용을 수정하였습니다(주석 처리).
Sub HighlightMisspelledCells()
Dim ws As Worksheet
Dim cell As Range
Set ws = ActiveSheet
Application.ScreenUpdating = False
' ws.Unprotect
For Each cell In ws.UsedRange
If Not Application.CheckSpelling(word:=cell.Text) Then
cell.Interior.Color = RGB(255, 0, 0)
End If
Next
' ws.Protect
Application.ScreenUpdating = True
End Sub
파일이 큰 경우 실행하는 데 약간의 시간이 필요합니다. 코드가 완벽하지 않다는 점을 명심하세요. 약어나 이름(브랜드, 국가, 사람)이 있는 경우 올바른 대문자로 작성되지 않으면 이러한 모든 항목이 철자가 틀린 것으로 강조 표시됩니다. 이러한 모든 항목을 다시 확인하고 강조 표시를 해제하려면 한 단계 더 필요합니다. 이 경우에는 간단한 IF 수식이 도움이 될 수 있습니다. 매크로나 VBA에 대한 지식이 있다면 코드를 업무 특성에 맞게 수정할 수 있습니다.
3. 소스 언어 감지
목록에 있는 모든 키워드의 언어를 정의할 수도 있습니다. 특정 언어가 아닌 키워드를 제외하려면 이 단계를 수행하세요. 불가리아에서 웹사이트를 개선하는 작업을 하고 있다고 가정해 보겠습니다. 그렇다면 영어가 아닌 불가리아어로 새로운 콘텐츠에 대한 아이디어를 생각해내야 합니다. 일부 불가리아 사용자는 검색할 때 영어를 사용할 수도 있지만 모국어로 된 콘텐츠에 초점을 맞추는 것이 중요합니다. 이를 위해서는 Google 스프레드시트에서 다음 수식을 사용합니다.
DETECTLANGUAGE(텍스트_또는_범위)
이것은 여러분에게 좋은 시작을 제공하지만 이 수식의 결과가 완벽하지 않기 때문에 추가로 직접 검토하는 것이 좋습니다. 라틴 알파벳을 사용하는 언어에 대한 결과는 더 정확하고 신뢰할 수 있습니다. 데이터에 정보 계층을 하나 더 추가한 후 분석을 시작할 수 있습니다.
4. 색상이 있는 셀 카운팅하기
데이터를 색상별로 필터링하고 정렬할 수 있는 편리한 기능이 있어 하나의 열에 색상 서식을 지정하는 것이 쉽습니다. 그러나 30개 또는 50개의 열이 있고 모든 열에서 구체적인 색상으로 모든 셀을 계산하는 방법을 알고 싶다면 어려운 일이 됩니다. 예를 들어, 필자는 Screaming Frog를 사용해야 하는 내부 연결 프로젝트를 진행하고 있었습니다. 이 특정한 경우에 우리는 20개의 다른 페이지에 내부 링크를 추가하기 위해 40개의 다른 용어를 찾고 있었습니다. 프로젝트를 완료한 후 우리 팀이 실제로 얼마나 많은 내부 링크를 구현했는지 알고 싶었습니다.
이 때 40개의 열을 모두 수동으로 하나씩 필터링하고 싶은 사람은 없습니다. 내부 링크를 배치할 때마다 색상 코딩을 사용했기 때문에 특히 그렇습니다. 이 작업을 처리하기 위해 매크로를 사용할 수 있습니다. 셀 중 하나를 사용하여 셀 색상을 정의할 수 있습니다.
Function CountCellsByColor(data_range As Range, cell_color As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Application.Volatile
cntRes = 0
indRefColor = cell_color.Cells(1, 1).Interior.Color
For Each cellCurrent In data_range
If indRefColor = cellCurrent.Interior.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByColor = cntRes
End Function
Google Sheets를 좋아하는 분들에게 좋은 소식은 Excel을 사용하지 않고도 이 작업을 수행할 수 있는 방법이 있다는 것입니다. Google Apps 스크립트만 있으면 됩니다. 이 도구에 익숙하지 않다면 확장 메뉴 아래에 있는 다채로운 아이콘입니다. 필자는 Excel의 팬이지만 이 옵션이 훨씬 쉽습니다.
5. 구글 시트의 ArrayFormula를 사용하여 수식 단계 줄이기
필자가 유용하다고 생각하는 또 다른 Google Sheets 트릭은 ArrayFormula입니다. 이 수식을 사용하면 다른 수식을 동시에 작성할 수 있으며 구체적인 배열에 대해 자동으로 확장됩니다. 필자가 이 공식을 사용하는 실제 사례는 SEO 노력의 ROI를 계산하는 것입니다. 여러 통화로 송장을 받았지만 회사의 예산은 하나로 정의되어 있습니다.
(1) 모든 송장 금액을 하나의 구체적인 통화로 변환합니다.
=GOOGLEFINANCE(“Currency:<Currency Code 1><Currency Code2>”)
(2) 특정 기간(예를 들어 한 달) 동안 이 모든 것을 합산합니다.
(3) 생성된 값을 기반으로 ROI를 계산합니다.
최종 결과는 다음과 같습니다.
D열: =ArrayFormula(IF(NOT(ISBLANK(A2:A)), A2:A * GOOGLEFINANCE(“통화:” & B2:B & “USD”), “”))
다른 셀: =( E1 – SUMIFS( D2:D, C2:C, “>=” & DATE(2023,1,1), C2:C, “<=” & DATE(2023,1,31) ) / SUMIFS( D2 :D, C2:C, “>=” & DATE(2023,1,1), C2:C, “<=” & DATE(2023,1,31))
- 송장 금액은 A열에 있습니다.
- 변환용 통화 코드는 B열에 있습니다.
- 송장 날짜는 C열에 있습니다.
- 위 수식에서는 송장 금액을 미국 달러로 변환합니다.
- 유기 채널에 의해 생성된 값은 E1이라는 특정 셀에 있습니다.
- 특정 달(예: 2023년 1월)의 합계를 계산합니다.
수식이 길어 보이지만 한 번만 입력하면 된다는 점을 기억하세요. 또한 ChatGPT에 대신 작성해 달라고 요청할 수도 있습니다. 여러분이 해야 할 일은 원하는 결과와 여러분이 가지고 있는 입력 정보를 설명하는 것뿐입니다.
스프레드시트와 챗GPT를 사용하여 데이터 분석하기
ChatGPT는 데이터 분석을 통해 빠른 통찰력을 제공할 수 있습니다. 먼저 데이터가 깨끗하고 체계적으로 구성되도록 준비해야 합니다. 그런 다음 원하는 분석을 단계별로 설명하는 자세한 프롬프트를 작성하고 구체적인 결과를 요청하세요.
ChatGPT의 고급 데이터 분석 기능에 액세스하려면 ChatGPT Plus가 필요합니다. 이를 통해 데이터를 기반으로 한 정량적 분석이 가능하지만, 정성적 결과가 완전히 정확하지 않을 수 있으므로 늘 사실 확인과 검증이 필요합니다.
ChatGPT를 사용하면 데이터 시각화를 쉽고 빠르게 처리할 수 있습니다. 보고서를 작성할 때 회귀 분석, 가설 테스트 등과 같은 고급 작업에 챗GPT를 사용할 수 있습니다. Python을 사용하거나 Excel + Python을 사용하여 이러한 작업 대부분을 수행할 수도 있지만, Python 전문가가 아닌 경우 ChatGPT를 사용하는 것이 좋습니다.
'VBA' 카테고리의 다른 글
| 챗GPT로 Excel 매크로를 만드는 방법 (2) | 2023.12.30 |
|---|---|
| Excel에서 매크로를 활성화하는 방법 (0) | 2023.12.29 |
| Excel에서 매크로를 기록하는 방법 (2) | 2023.11.21 |
| Excel에서 각종 Mac 프로그램을 여는 VBA 코드 (0) | 2023.11.18 |
| VBA를 사용하여 Excel에 도형 삽입하기 (0) | 2023.11.11 |
