۱۴ راه فرمول نویسی در گوگل شیت برای سئوکارها

ابزار Google Sheets یک ابزار قدرتمند (و رایگان) مبتنی بر فضای ابری است که به ما امکان می‌دهد داده‌ها را به طور موثر و کارآمد سازماندهی کنیم. راز استفاده کامل از همه مزایایی که Google Sheets ارائه می‌دهد‌، استفاده از انواع فرمول نویسی در گوگل شیت است (این ابزار شباهت‌های زیادی با نرم افزار اکسل دارد اما امکانات آن کمتر است).

بدون مجموعه ای از فرمول‌های آماده‌ و کاربردی گوگل شیت، باید ساعت‌ها وقت خود را صرف مرتب سازی‌، تقسیم‌، ادغام‌، افزودن‌، حذف و جستجوی داده‌ها کنید. از سوی دیگر‌، اگر دقیقاً بدانید از چه فرمول گوگل شیت برای هر کار خاص استفاده کنید‌، در وقت خود و همچنین از انرژی ذهنی خودتان بسیار صرفه جویی می‌کنید. همچنین می‌توانید صفحه‌های قابل اشتراک گذاری خود را برای خود و دیگران که نیاز به استفاده از آنها دارند بسیار تمیزتر و قابل کنترل تر کنید.

آنچه فعالان عرصه سئو از گوگل شیت نیاز دارند:

به عنوان متخصص سئو، ما نمی‌خواهیم تمام وقت خود را صرف ساماندهی صفحات گسترده (اسپرد شیت‌ها) کنیم. ما می‌خواهیم صفحات گسترده ما منابع مفیدی باشند که به ما در ساده سازی وظایف و ارائه داده‌های ضروری و واضح کمک کنند. امروز‌، می‌خواهم لیستی از فرمول‌های اساسی را با شما به اشتراک بگذارم که می‌دانم به شما کمک می‌کند سریع‌تر و هوشمندانه‌تر کار کنید‌، بنابراین زمان بیشتری برای صرف سایر کارهای مورد علاقه خود خواهید داشت.

شاید وقتی صحبت از Google Sheets به میان می‌آید‌، تازه کار هستید و نمی‌دانید از کجا باید شروع کنید. یا شاید از قبل علاقمند به استفاده از این ابزار حرفه‌ای گوگل بوده اید و می‌خواهید از همه امکاناتی که ارائه می‌دهد نهایت استفاده را ببرید. در هر صورت‌، لیست فرمول گوگل شیت زیر مجموعه ای عالی از ابزارهایی است که باید بلد باشید:

چند اصل درباره فرمول‌های Google Sheet  قبل از شروع

قبل از اینکه به خود فرمول‌ها بپردازیم‌، می‌خواهیم چند اصل اساسی را برای مبتدیان ارائه دهیم. دانستن این تعاریف اساسی‌، نحوه استفاده از فرمول‌هایی را که امروز به اشتراک می‌گذاریم و سایر مواردی که در آینده می‌آموزید بسیار آسان تر می‌کند.

• سلول یک نقطه داده واحد در یک برگه است.
• یک ردیف مجموعه ای از سلول‌ها است که به صورت افقی گسترش می‌یابد.
• ستون مجموعه ای از سلول‌ها است که به صورت عمودی گسترش می‌یابد.
• یک تابع عملیاتی (function) است که برای ایجاد نتیجه دلخواه استفاده می‌شود.
• فرمول ترکیبی از سلول‌ها‌، ستون‌ها‌، سطرها‌، توابع و محدوده است که برای ایجاد معادله استفاده می‌شود.
• یک محدوده یا بازه با انتخاب بخشی از سلول‌ها در یک ستون یا سطر (یا هر دو) ایجاد می‌شود.
• همه فرمولها باید با علامت مساوی (=) شروع شوند.

1- فرمول VLOOKUP برای وارد کردن داده‌ها

اکثر فعالان SEO‌ معتقدند که عملکرد VLOOKUP برای یکی از بهترین ابزارهایی است که در بخش ابزار وجود دارد. با استفاده از آن‌، می‌توانید داده‌ها را از یک صفحه گسترده به صفحه گسترده دیگر بکشید.  به شرطی که این دو صفحه یک ارزش مشترک داشته باشند.

این امر هنگامی‌مفید است که مجموعه‌های بزرگی از داده‌ها را داشته باشید که می‌خواهید بدون نیاز به کپی و پیست کردن داده در هر سلول واحد آن را به صورت دسته جمعی وارد شیت جدید کنید. این امر به ویژه هنگامی‌که با تعداد زیادی صفحات گسترده کار می‌کنید بسیار مفید است.

فرض کنید من یک صفحه گسترده با دو زبانه دارم (تصویر صفحه زیر را ببینید). یک برگه لیستی از کلمات کلیدی و حجم جستجوی ماهانه هر کلمه کلیدی را نشان می‌دهد. برگه دوم دارای همان لیست کلمات کلیدی با نمرات دشواری SEO آنها است. هدف من این است که نمرات دشواری SEO را با استفاده از فرمول VLOOKUP به برگه اول برسانم:

=VLOOKUP(search_key, range, index, is_sorted)

توجه به این نکته ضروری است که مکان سلول (در این مورد “کلمه کلیدی keyword”) که می‌خواهم از یک برگه به ​​برگه دیگر برسانم باید در هر دو صفحه یکسان باشد. این نقطه داده همچنین باید در ستون اول تب دوم – ستون A قرار گیرد.

من می‌خواهم داده‌های ستون دو (DifficultyScore) را به صفحه برگه اول خود بکشم. بنابراین‌، فرمول من به این شکل است:

=VLOOKUP(A2,DifficultyScore!A:B,2,0)

بیایید هر قسمت از این معادله را تجزیه کنیم:

• Search_key : ارزش مشترک هر دو صفحه است. ما با اولین کلمه کلیدی در لیست‌، که در سلول A2 قرار دارد‌، شروع می‌کنیم.
• محدوده یا رنج: جایی است که شما نام برگه ای را که از آن داده‌ها را می‌خواهید بردارید را وارد می‌کنید، به همراه تعداد سلول‌هایی که میخواهید فرمول آن‌ها را از ستون بخواند. ستون‌هایی که می‌خواهید فرمول شما بخواند‌، وارد می‌کنید. من می‌خواهم ستون A و B (کلمه کلیدی و دشواری SEO )را به صفحه اول بکشم.
• ایندکس: جایی است که شماره ستونی را که می‌خواهید داده‌ها له آن وارد کنید، اضافه می‌کنید. A = 1‌، B = 2‌، C = 3 و غیره.

آخرین و مهمترین نکته این است که من is_sorted   را روی صفر یا “FALSE” قرار می‌دهم‌، که معمولاً در این فرمول گوگل شیت برای مطابقت دقیق استفاده از آن توصیه می‌شود.

برای وارد کردن فرمول‌، روی سلول C2 کلیک می‌کنم‌، فرمول را در آن تایپ می‌کنم و enter را فشار می‌دهم. مقدار صحیح 31 در آن سلول نمایش داده می‌شود. سپس می‌توانم گوشه سلول C2 را کلیک کرده و به سمت پایین بکشید تا بقیه مقادیر را در سراسر ستون پر شوند.

2- اگر شرایط صحیح یا غلط باشد (IF):

فرمول IF به شما امکان می‌دهد بررسی کنید که آیا یک شرط خاص برای لیست داده‌های صفحه شما درست یا نادرست است.

فرمول گوگل شیت آن به صورت زیر است:

=IF(condition, value_if_true, value_if_false)

در واقع در این فرمول شرایط چیزی است که شما برای صحت یا نادرست بودن آن آزمایش می‌کنید. اگر شرط درست باشد‌، “value_if_true” نمایش داده می‌شود. اگر نادرست باشد‌، “value_if_false” به جای آن نمایش داده می‌شود.

بیایید به یک مثال بپردازیم تا ببینیم چگونه این کار انجام می‌شود.

این بار‌، من می‌خواهم لیستی از کلمات کلیدی را بررسی کنم تا ببینم کدام‌ها بیش از 5000 بازدیدکننده در ماه دارند. من با حجم بازدید کلمه کلیدی اول در سلول B2 شروع می‌کنم. فرمولش هم این است:

=IF(B2>5000,”YES”,”NO”)

هنگامی‌که فرمول اولین کلمه کلیدی را وارد می‌کنم و enter را فشار می‌دهم‌، می‌توانم از اولین سلول (D2) به پایین ستون بکشم و پاسخ‌ها به صورت خودکار پر می‌شوند.

همه کلمات کلیدی با ترافیک بیش از 5000 بازدیدکننده در ماه به عنوان “بله”‌، و آنهایی که دارای ترافیک کمتر از 5000 هستند به عنوان “نه” نشان داده می‌شوند.

همچنین می‌توانید از عملیات‌های زیر در این معادله استفاده کنید:

• مساوی (=)
• نامعادله(<>)
• بزرگتر از (>)
• کمتر از (<)
• بزرگتر یا مساوی (> =)
• کمتر یا مساوی (<=)

3- تنظیم پیش فرض خطا با IFERROR

فرض کنید یکی از سلول‌های شما هنگامی‌که سعی می‌کنید یک فرمول گوگل شیت را اجرا کنید، پیغام خطا نشان می‌دهد؛ که می‌تواند به صورت “#ERROR!” یا “#DIV/0!” یا “#Value!” باشد.

در صورت تقسیم بر صفر یا استفاده از محدوده ناشناخته‌، چنین خطاهایی ممکن است رخ دهد. وقتی می‌خواهید محاسباتی را انجام دهید که این سلول‌ها را شامل می‌شود‌، این مشکل ایجاد می‌شود. همچنین می‌تواند صفحه گسترده شما را نامرتب نشان دهد و هنگام ارائه آن به دیگران گیج کننده باشد.

فرمول IFERROR به شما کمک می‌کند تا با جایگزینی مقادیر خطا با مقادیر پیش فرض که خودتان تعیین کرده اید‌، از این مسئله مصون بمانید.

به سادگی فرمول اصلی خود را همراه با هر مقدار که می‌خواهید مقدار پیش فرض شما باشد وارد کنید. حتی می‌توانید آن را خالی بگذارید و فقط “value_if_error” را تایپ کنید.

=IFERROR (original_formula, value_if_error)

در این مثال‌، می‌خواهم بفهمم که کدام کلمات کلیدی می‌توانند بیش از 500 بازدیدکننده را به وب سایت من بیاورند. با ضرب حجم جستجو در نرخ کلیک (CTR) این کار را انجام می‌دهم اما عبارت کلیدی “digital marketing agency near me” حجم جستجوی صفر دارد و نرخ کلیک آن صفر بوده و به جای آن یک خط تیره گذاشته شده است که در محاسبات خط تیره معنایی ندارد لذا با قرار گیری در فرمول خطا می‌دهد.

من می‌خواهم به جای ارور، آن سلول خالی بماند لذا از فرمول زیر استفاده می‌کنم:

=IFERROR(IF(C3*E3>500,”YES”,”NO”),””)

4- فرمول SPLIT برای تقسیم کردن داده‌های دو دسته سلول

فرمول بعدی SPLIT است:

=SPLIT(Text, Delimiter)

این فرمول به شما امکان می‌دهد داده‌ها را از یک سلول به چندین سلول تقسیم کنید. فرض کنید شما یک صفحه گسترده از مخاطبین با نام کامل آنها در یک ستون دارید. برای وارد کردن صحیح داده‌های خود به پایگاه داده‌، باید نام و نام خانوادگی را به دو ستون جداگانه تقسیم کنید.

اگر بخواهم نامهای ستون A را به نام و نام خانوادگی در ستون B و C تفکیک کنم فرمول گوگل شیت به این صورت خواهد بود:

=SPLIT(A2, ” “)

سلول A2 همانی است که قصد تفکیک نام‌های موجود در آن را دارم و این نام ها با یک فاصله  space از هم جدا شده اند که فاصله را داخل دبل کوتیشن داخل فرمول گذاشته ایم. به همین سادگی !

من روی سلول B2 کلیک می‌کنم و وقتی فرمول را وارد کنم‌، B2 و C2 باید همزمان پر شوند. مرحله بعدی این است که از اولین سلولی که گوشه اولین سلول را تا آخرین سلول (B12)  بکشید.

5- با فرمول COUNTIF سلول‌ها را سریع بشمارید

با شمارش سلولها به صورت دستی خداحافظی کنید. با استفاده از فرمول COUNTIF‌، می‌توانید سلولهایی را که دارای معیارهای خاصی هستند شمارش کنید.

=COUNTIF(range, criteria)

این فرمول نه تنها در وقت شما صرفه جویی می‌کند  بلکه از صحت اعداد نیز اطمینان حاصل می‌کند.

در یک مثال بسیار ساده می‌خواهم همه سلول‌های صفحه گسترده خود را که در آن کلمه “Betty” ذکر شده است‌، شمارش کنم. در اینجا فرمول را به این صورت خواهم نوشت:

=COUNTIF(B2:B15, “Betty”)

اگر این فرمول را در یک سلول خالی بنویسم و “Enter” را فشار دهم‌، پاسخ برایم نشان داده خواهد شد.

6- یک فرمول گوگل شیت را به تعداد زیادی از سلول ها با فرمول ARRAYFORMULA تعمیم دهید

احتمالا شما از این فرمول بسیار خوشتان خواهد آمد. عبارت ARRAYFORMULA به شما امکان می‌دهد فرمول خود را یکبار بنویسید و آن را در یک ردیف یا ستون کامل اعمال کنید. کافیست فرمول خود را در ARRAYFORMULA وارد کنید.

=ARRAYFORMULA(array_formula)

فرض کنید من یک فرمول IF دارم که می‌خواهم از آن در ستون D استفاده کنم تا مشخص شود کدام کلمات کلیدی بیش از 5000 بازدید در ماه دارند. من اولین سلول خود (D2) را انتخاب می‌کنم و فرمول خود را در آن می‌نویسم.

من فقط باید مطمئن باشم که اولین و آخرین سلول‌هایم را در فرمول خود‌، یعنی B2 و B24 قرار می‌دهم. سپس enter را فشار می‌دهم و ستون به صورت خودکار پر می‌شود.

=ARRAYFORMULA(IF(B2:B24>5000, “Yes”,”No”))

7- برای شمارش کاراکترها از فرمول LEN استفاده کنید

فرمول LEN به شما کمک می‌کند تا تعداد کاراکترهای یک سلول را بشمارید.

=LEN(insertcell)

فرض کنید شما در حال مرور توضیحات متا و عناوین صفحات برای همه صفحات وب سایت خود در یک صفحه گسترده هستید و می‌خواهید کاراکترها را بشمارید تا از طول مناسب آنها مطمئن شوید. روی یک سلول خالی کلیک کنید و فرمول گوگل شیت را وارد کنید و “insertcell” را با سلولی که شامل کاراکترهایی است که می‌خواهید شمارش کنید جایگزین کنید.

مثلا A2 اولین سلولی است که می‌خواهم کاراکترش را شمارش کنم‌، بنابراین فرمول من به سادگی = LEN (A2) خواهد بود. پس از آن enter را فشار می‌دهم و تعداد کاراکترها ظاهر می‌شود.

سپس می‌توانم از این فرمول برای بقیه ستون خود با کشیدن از اولین سلول به پایین تا آخرین سلول استفاده کنم.

8- به راحتی سلول‌های خود را مرتب (SORT) کنید.

آیا می‌خواهید سلول‌های خود را مرتب کنید؟ شما درست حدس زده اید؛ ما یک فرمول برای آن داریم. با فرمول SORT‌، می‌توانید سطرها را بر اساس مقدار در یک یا چند ستون مرتب کنید. این فرمول به این شکل است:

=SORT(range, sort_column, is_ascending)

رنج گروهی از سلول‌ها است که می‌خواهید آن‌ها را مرتب کنید.

Sort_cumn  شماره ستونی هست که می‌خواهید آن را مرتب کنید مثل A=1، B=2 و … .

اگر می‌خواهید مقادیر به ترتیب صعودی مرتب شوند‌، عبارت TRUE را برای is_ascending علامت گذاری کنید. اگر می‌خواهید مقادیر به ترتیب نزولی مرتب شوند‌، به جای آن “FALSE” را علامت بزنید.

بیایید به یک مثال ساده نگاه کنیم.

می‌خواهم ببینم کدام کلمه کلیدی بیشترین حجم جستجو را دارد. فرمول گوگل شیت آن را می‌نویسم:

=SORT(A2:B11,2,FALSE)

وقتی می‌خواهید مقادیر را در چند ستون مرتب کنید‌، این فرمول به کار می‌آید. در مثال بعدی‌، من می‌خواهم ایمیل‌های برتر خود را تعیین کنم. باید فرمول خود را تنظیم کنم‌، بنابراین ایمیل‌هایی با بیشترین نرخ بازدید و کمترین لغو اشتراک ابتدا ظاهر می‌شوند. فرمول من به این شکل خواهد بود:

=SORT(A2:C9,2,FALSE,3,TRUE)

9- سایر اطلاعات را با فرمول IMPORTRANGE وارد کنید.

فرمول IMPORTRANGE  مشابه VLOOKUP است زیرا به شما امکان می‌دهد داده‌ها را از صفحه گسترده دیگر به صفحه مدنظر خودتان وارد کنید.

=IMPORTRANGE(spreadsheet_url, range_string)

نشانی وب spreadsheet_url را با نشانی اینترنتی صفحه گسترده دیگر جایگزین کنید و سپس محدوده سلول‌هایی را که می‌خواهید وارد کنید را مشخص کنید. اگر چندین برگه (tab) در صفحه گسترده دارید که می‌خواهید از آنها استفاده کنید‌، باید آن‌ها را مشخص کنید.

فرض کنید شما از برگه ای به نام “Keywords” داده‌های سلول‌های A2 تا H150 را می‌خواهید. فرمول شما به این شکل خواهد بود:

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1XkS8qQsCA1bnCFqqrbkZCIiIKQp2gmYL/edit#gid=1658834149”, “Keywords!A1:H150”)

10- فقط داده‌های خاصی را نگه دارید

ما با داده‌های زیادی کار می‌کنیم و به خوبی می‌دانیم که اطلاعات تکراری هر چند وقت یکبار ظاهر می‌شوند. فرمول UNIQUE می‌تواند در اینجا به ما کمک کند.

داده‌ها ممکن است به انواع روش‌ها تکرار و کپی شوند و  این مشکل می‌تواند صفحات گسترده و سیستم‌های مدیریت محتوا را به سرعت آشفته و بهم ریخته کند. در اینجا این فرمول می‌تواند به شما کمک کند.

=UNIQUE(range)

برای مثال، روی یک سلول خالی کلیک کردم و فرمول = UNIQUE (A2: C24) را وارد کردم تا هر سه ستون داده را شامل شود. سپس اینتر را زدم. همه فیلدهای تکراری حذف شدند و فیلدهای منحصر به فرد باقی ماندند.

11-جستجو برای یک مقدار خاص

از فرمول SEARCH می‌توان برای بررسی مجموعه ای از داده‌ها استفاده کرد.

به عنوان مثال‌، فرض کنید شما همه آدرس‌های وب سایت خود را در یک صفحه گسترده لیست کرده اید و می‌خواهید همه مقالات وبلاگ خود را شناسایی کنید.

به جای مرور دستی و انتخاب پست‌های وبلاگ‌، می‌توانید از این فرمول گوگل شیت استفاده کنید:

=SEARCH(search_query, text_to_search)

برای نشانه گذاری پست‌های خود به عنوان “وبلاگ”‌، باید فرمول بالا را با فرمول IF ترکیب کنید‌، که در بالا به آن پرداختیم. می‌تواند فرمولی مانند این باشد:

=IF(SEARCH(“/blog/”,A2),”Blog”,””)

این فرمول همه  URL‌های حاوی / blog / را با شروع از سلول A2 جستجو می‌کند.

آدرس‌هایی که حاوی blog نیستند‌، پیام خطا (#VALUE!) را نشان می‌دهند. برای جلوگیری از این امر‌، می‌توانید این فرمول را در یک فرمول IFERROR بپیچید و نشانی‌های اینترنتی غیر وبلاگ را به عنوان “سایر” برچسب گذاری کنید (یا آن را خالی بگذارید) و داده‌های خود را تمیز و بدون خطا نگه دارید.

=IFERROR (IF(SEARCH(“/blog/”,A2),”Blog”,””), “Other”)

12- با استفاده از TODAY تاریخ بزنید

وارد کردن تاریخ در صفحات گسترده Google Sheets دیگر راحت شده است. این فرمول به شما کمک می‌کند تا محدوده تاریخ گزارش‌ها را سریعتر از قبل درج کنید.

اگر می‌خواهید تاریخ امروز را اضافه کنید‌، تنها کاری که باید انجام دهید این است که فرمول زیر را به سلول خود اضافه کنید:

=TODAY()

شما حتی نیازی به دانستن تاریخ ندارید ؛ به طور خودکار تاریخ روز برای شما در نظر گرفته خواهد شد.

اگر می‌خواهید بدون نیاز به تقویم یک تاریخ را از دو یا سه هفته قبل مشخص کنید‌، فقط کافی است تعداد روزهای مورد نظر را از فرمول خود کم کنید.

=TODAY() – 14

این تاریخ را دقیقاً 14 روز قبل به شما نشان می‌دهد.

برعکس‌، اگر می‌خواهید در آینده یک تاریخ را انتخاب کنید‌، به جای کم کردن‌، تعداد روزها را اضافه کنید.

=TODAY() + 7

در مورد بالا‌، تاریخ 7 روز بعد را خواهید دید.

13- ادغام داده سلول‌ها با CONCATENATE

ما قبلاً نحوه تقسیم داده‌ها به سلول‌ها را آموخته ایم‌، اما ادغام داده‌های سلولی چطور؟ این فرمول گوگل شیت برای کار با URL‌ها مفید است.

گاهی اوقات‌، هنگامی‌که لیستی از آدرس‌های اینترنتی را بارگیری می‌کنید‌، آدرس وب سایت آنطور که می‌خواهید قالب بندی نمی‌شود.

شاید شما فهرستی را از Google Analytics بارگیری کرده اید و دامنه وجود ندارد. یا شاید  URL‌ها دیگر فرم پروتکل اصلی را ندارند.

به جای بروزرسانی دستی هر URL به صورت جداگانه‌، می‌توانید از فرمول CONCATENATE استفاده کنید.

هنگامی‌که اولین مورد را انجام می‌دهید‌، به سادگی گوشه اولین سلول را پایین بیاورید و خواهید دید که تغییر مورد نظر در سلول‌های باقی مانده به طور خودکار ظاهر می‌شود.

=CONCATENATE(range)

اگر یک پیشوند یا پسوند واحد در برگه وجود ندارد‌، می‌توانید آن را به صورت دستی نیز بدون نیاز به ایجاد ستون اضافی سلول برای آن وارد کنید. مثلا برای اضافه کردن https داریم:

CONCATENATE(“https://”,A2)

14- مجموع داده‌ها را با SUMIF پیدا کنید

فرمول SUMIF می‌تواند برای جمع مقادیر بر اساس یک معیار خاص مفید باشد.

=SUMIF(range, criterion, [sum_range])

در مثال زیر‌، من می‌خواهم میزان بازدید صفحه پست‌های وبلاگ خود را با عملکرد بالا جمع کنم تا تعیین کنم کدام دسته وبلاگ محبوب ترین هستند. من می‌توانم با استفاده از این فرمول برای هر دسته به سرعت میزان بازدیدها را جمع کنم.

برای محاسبه نمایش صفحات در دسته SEO:

range شامل ستون سلول‌های مورد نظر است.

SEO به جای criterion قرار می‌گیرد.

فرمول، تعداد کل بازدید از صفحات در دسته SEO را جمع می‌زند؛ 36،834

بدون نیاز به مرتب سازی یا محاسبه اضافی‌، من می‌توانم مجموع هر دسته را مشاهده کنم و به راحتی تعیین کنم که کدام دسته از پست‌های وبلاگ بیشترین بازدید صفحات را دارد.

این فرمول‌های قدرتمند را در ذهن داشته باشید. اکنون فرمول گوگل شیت هر چه که هست را دانسته و عملکردهای مفید آنها را می‌دانید که می‌توانید در google sheet از آن‌ها استفاده کنید تا صفحات خود را بسیار راحت تر به عنوان یک سئوکار حرفه ای مدیریت کنید.