من ابتدا در دوران نوجوانی از مایکروسافت اکسل استفاده کردم، در حالی که سعی کردم اعداد را در یک صفحه گسترده و بعداً در QuickBooks سازماندهی کنم، رسیدها را زیر و رو می کردم. من به دوست پدرم کمک می‌کردم تا برای اظهارنامه مالیاتی‌اش آماده شود و چند دلار از این معامله گرفتم. من مطمئن نیستم که چرا او به یک نوجوان 16 ساله (با تجربه صفحه گسترده صفر) اعتماد کرد تا بتواند چنین چیزی را مدیریت کند. نمی‌توانستم فکر نکنم، “او فقط این کار را به من داد تا خوب باشم”، در حالی که سر کلیپی، شخصیت گیره کاغذ، فریاد زدم که پس از اینکه نتوانست آنطور که ادعا می‌کرد به من کمک کند، از سر راه خود خارج نشدم.
مطالب مرتبط:
اکنون، سال‌ها پس از آن، به گذشته نگاه می‌کنم و از انجام آن وظیفه خسته‌کننده آن زمان سپاسگزارم. این پایه و اساس حرفه ای بود که در XLSX. شروع شد، و من روزانه از این نوع فایل در Go Fish Digital استفاده می کنم. در تیم بازاریابی محتوا، ما اغلب از داده ها برای کمپین های مشتریان خود استفاده می کنیم. این به ما امکان می‌دهد مطالب قانع‌کننده‌ای را برای روزنامه‌نگاران تولید کنیم تا در داستان‌های خود از آنها استفاده کنند و در نتیجه بک لینک و پوشش خبری برای مشتریان خود به دست آوریم.
حتی اگر در زمینه‌های پر داده مانند بازاریابی یا SEO نیستید، درک کلی از صفحات گسترده برای هر حرفه‌ای ضروری است. Microsoft Excel و Google Sheets دو ابزار اصلی هستند که با آنها روبرو خواهید شد و ساختار بسیار مشابهی دارند. در زیر راهنمای پنج عملکرد هر یک از ابزارهایی است که می توانید برای مدیریت موفقیت آمیز داده ها از آنها استفاده کنید.
5 عملکرد اصلی اکسل چیست؟
این 5 تابع اصلی اکسل است که همه باید بدانند:
- تابع VLookup
- تابع الحاق
- متن به ستون
- موارد تکراری را حذف کنید
- جداول محوری
فرمول VLookup
مثال:
شما صفحه‌گسترده‌ای از همبرگرفروشی‌ها دارید، و هر کدام جداگانه در ستون A فهرست شده‌اند، با آدرس خیابان، شهر، ایالت، نام مالک، فروش سالانه و سایر اطلاعات در ستون‌های مجاور.
در میان لیست طولانی فروشگاه های همبرگر شما، تنها تعداد انگشت شماری وجود دارد که در مورد آنها به اطلاعات نیاز دارید. از نظر تئوری می‌توانید در صفحه‌گسترده پیمایش کنید یا سعی کنید فهرست را مرتب کنید/فیلتر کنید، یا می‌توانید از یک تابع Vlookup برای بیرون کشیدن اطلاعات دقیق مورد نیاز خود استفاده کنید. موارد استفاده زیادی برای این فرمول وجود دارد، و هر چه بیشتر با آن آشنا شوید، بیشتر اوقات راه هایی برای استفاده از آن برای صرفه جویی در وقت خود پیدا خواهید کرد.
نحوه تنظیم آن:
- اگر قبلاً جدولی ایجاد نکرده اید، یک جدول جدید در برگه دیگری ایجاد کنید.
- در ستون اول، “مقدار جستجو” را خواهید داشت، که بخشی از اطلاعاتی است که به فرمول می گویید که در صفحه گسترده اصلی خود به دنبال آن بگردید. این اطلاعات باید در سمت چپ ترین ستون جدولی باشد که فرمول باید به آن نگاه کند، همانطور که در اولین تصویر بالا آمده است.
- سپس فرمول را به صورت زیر به سلول مجاور اضافه کنید:
=VLOOKUP(مقدار_جستجو، آرایه_جدول، عدد_شاخص_رنگ،[range_lookup])
lookup_value: سلولی که در آن فرمول می تواند اطلاعاتی را که باید در جدول اصلی شما جستجو کند، پیدا کند. در این حالت، هنگام قرار دادن فرمول در سلول B2، این مقدار خواهد بود A2.
آرایه جدولی: جدولی که فرمول باید جستجو کند و اطلاعات را از آن استخراج کند. از آنجایی که جدول فروشگاه های همبرگر من در برگه ای به نام «برگرفروشی» است و من داده هایی را در ستون های تا ستون H دارم، این مقدار خواهد بود “برگرفروشی”! A:H.
col_index_num: این روشی است که می‌توانید به فرمول بگویید چه اطلاعاتی را می‌خواهید برای شما بازگرداند. در این مثال، من به دنبال شماره تلفن هستم که در ستون F در جدول اصلی من قرار دارد. F 6 استهفتم حرف در حروف الفبا، و بنابراین این مقدار در فرمول خواهد بود 6.
[range_lookup]: این یک بخش منطقی از فرمول است که گزینه ها در آن قرار دارند درست است، واقعی یا نادرست. اگر قرار دهید درست است، واقعی، فرمول مقداری را بر اساس نزدیکترین تطابق با مقدار جستجوی شما برمی گرداند، حتی اگر دقیق نباشد. اگر قرار دهید نادرست، فقط در صورتی یک مقدار را برمی گرداند که دقیقاً مقدار جستجوی مورد اشاره شما را پیدا کند. در بیشتر موارد، و در این مثال فروشگاه همبرگر، ما می خواهیم قرار دهیم نادرست
فرمول نهایی:
=VLOOKUP(A2،’برگرفروشی’! A:H,3,FALSE)
فرمول الحاق
مثال:
در ادامه مثال فروشگاه برگر، فرض کنید می خواهید شهر و ایالت را در یک ستون داشته باشید. شاید بخواهید داده ها را بر اساس شهر تجزیه و تحلیل کنید، اما شهرهایی با همین نام در ایالت های مختلف وجود دارند، بنابراین به ترکیب آنها نیاز دارید. تابع الحاق یک فرمول فوق العاده آسان است که می توانید از آن برای ترکیب محتویات سلول های مختلف استفاده کنید.
نحوه تنظیم آن:
- ابتدا باید یک ستون جدید به جدول خود اضافه کنید، جایی که داده های ترکیبی جدید در آن قرار می گیرند
- سپس فرمول را به صورت زیر اضافه می کنید:
=CONCATENATE(متن1، متن2،…)
شما می توانید متن و/یا سلول های مرجع را در این فرمول تایپ کنید، که آن را بسیار متنوع می کند. برای افزودن متن، فقط آنچه را که می‌خواهید تایپ کنید و اطراف آن علامت نقل قول اضافه کنید. در این مثال، ما می خواهیم یک کاما و فاصله بین شهر و ایالت اضافه کنیم.
فرمول نهایی:
=CONCATENATE(C2،”، “،D2)
متن به ستون
مثال:
فرض کنید مشکلی برخلاف مثال قبلی دارید – شهر و ایالت را برای هر فروشگاه برگر ترکیبی دارید، اما می‌خواهید آنها را در ستون‌های جداگانه قرار دهید. تابع “متن به ستون” روشی ساده برای شکستن داده ها به چندین ستون بر اساس معیارهای خاص است. این می تواند متن را بر اساس یک فاصله یا یک جداکننده خاص، مانند خط تیره یا کاما، جدا کند.
نحوه تنظیم آن:
- ستونی را در سمت راست ستونی که جدا می‌کنید (یا اگر داده‌ها را به بیش از دو بخش جدا می‌کنید چندین ستون) اضافه کنید، مانند تصویر زیر
- کل ستون را با متنی که باید جدا شود برجسته کنید (در این مورد ستون C است)
- روی برگه «داده» در نوار ابزار کلیک کنید
- روی «متن به ستون» کلیک کنید
- تأیید کنید که «محدود شده» انتخاب شده است
- روی “بعدی” کلیک کنید
- سپس جداکننده مورد نظر را انتخاب کنید
در مثال فروشگاه همبرگر، جداکننده اعمال شده یک کاما است، بنابراین باید مطمئن شوم که فقط کادر “کاما” انتخاب شده باشد. در این مرحله با یک عملیات Text to Columns ساده، می‌توانید روی «پایان» کلیک کنید و داده‌های خود را تقسیم خواهید کرد. ممکن است مجبور شوید عناوین ستون ها را به روز کنید.
موارد تکراری را حذف کنید
مثال:
در این صفحه گسترده فروشگاه همبرگر، ممکن است این اتفاق بیفتد که همان فروشگاه همبرگر چندین بار فهرست شده باشد. اگر قصد دارید این داده ها را در یک جدول محوری به صورت تعداد یا میانگین تجزیه و تحلیل کنید، قطعاً هیچ مقدار تکراری در مجموعه داده نمی خواهید.
در این مثال، هر فروشگاه همبرگر دارای یک نام منحصر به فرد است، بنابراین هر دو ردیف با نام مغازه مشابه، نشان دهنده تکراری است که باید حذف شود. اگر از صفحه‌گسترده‌ای استفاده می‌کنید که در آن چندین آیتم با عنوان یکسان در یک سلول خاص وجود دارد، اگرچه آنها بر اساس آدرس خیابان یا شهر یا موارد دیگر متفاوت هستند، می‌توانید از یک فرمول Concatenate ساده (به بالا مراجعه کنید) برای ایجاد یک صفحه استفاده کنید. “نام” منحصر به فرد برای هر مورد در جدول شما، سپس بر اساس آن موارد تکراری را حذف کنید.
نحوه تنظیم آن:
- ابتدا، با کلیک بر روی اولین ستون (ستون A) و کشیدن موس خود به آخرین ستون مربوطه، تمام ستون های جدول خود را برجسته می کنید.
- در مرحله بعد، روی «داده» از نوار ابزار و سپس «حذف موارد تکراری» کلیک کنید. پنجره ای ظاهر می شود و تیک هر ستون را به جز ستونی که دارای مقادیری است که باید منحصر به فرد باشد، بردارید. در این مثال، ستون A است زیرا نام های فروشگاه همبرگر را دارد.
- در نهایت، روی «OK» کلیک کنید و اکسل به شما اطلاع می‌دهد که در صورت وجود تعداد دفعات تکراری حذف شده است. این بدان معنی است که اکسل تمام داده های ردیفی را که در آن یک نسخه تکراری پیدا شده بود حذف کرد.
جداول محوری
مثال:
فرض کنید می خواهید ببینید فروش سالانه چگونه در بین ایالت ها مقایسه می شود. جداول محوری به شما این امکان را می دهد که یک مجموعه داده را بگیرید و آن را به روش های مختلف خلاصه کنید. این یکی از کاربردی ترین توابع Excel یا Sheets است.
نحوه تنظیم آن:
- به جدول داده های خود بروید و در هر نقطه از آن کلیک کنید
- نیازی به برجسته کردن دستی همه سلول‌های مربوطه خود نیست، مگر اینکه بخواهید ردیف‌ها یا ستون‌های خاصی را از جدول محوری خود حذف کنید – تابع جدول محوری به طور خودکار تمام داده‌ها را در یک جدول کامل که دارای هر گونه شکستی است، می‌کشد.
- در بخش «درج» در اکسل یا «داده» در برگه‌ها، روی «PivotTable» کلیک کنید.
- در اکسل، پنجره ای ظاهر می شود و روی «OK» کلیک می کنید. اکنون می توانید جدول محوری خود را تنظیم کنید تا داده های خود را به صورتی که می خواهید خلاصه کنید
- برای به دست آوردن میانگین فروش سالانه بر اساس مثال ایالت، “State” را از لیست نام فیلد به بخش با عنوان “ردیف” بکشید.
- «فروش سالانه» را به قسمتی با عنوان «ارزش‌ها» بکشید
- برای این مثال، ما می خواهیم میانگین این مقادیر را ببینیم، نه مجموع. برای تغییر آن، روی نماد “i” در کنار “SUM of Annual Sales” در بخش “Values” کلیک کنید، سپس “Average” را از لیست ظاهر شده انتخاب کنید.
- سپس میانگین فروش سالانه را بر اساس ایالت خواهید داشت
موارد استفاده دیگر:
راه های بی پایانی برای استفاده از جداول محوری وجود دارد – این فقط به مجموعه داده ای که در حال تجزیه و تحلیل هستید بستگی دارد. در صفحه‌گسترده خانه‌های برای فروش، می‌توانید تعداد خانه‌های فروش را بر اساس شهر یا ایالت بیابید. در جدول درآمد ماهانه در چهار سال گذشته، می توانید میانگین درآمد را بر اساس ماه یا سه ماهه سال بیابید. همچنین می توانید جداول محوری پیچیده تری را تنظیم کنید که شامل فیلدهای محاسبه شده است.
افکار بسته
مسلماً، اولین باری که از یکی از این توابع استفاده می‌کنید، ممکن است تجربه ناامیدکننده‌ای از تلاش برای کارکرد آن باشد. من از شما می خواهم که اگر این اتفاق افتاد به آن پایبند باشید. درک کامل این توابع ضروری در هر صنعتی که نیاز به استفاده از داده ها دارد – که تقریباً همه آنها هستند – ارزشمند است. هرچه بیشتر تمرین کنید، راحت‌تر و سریع‌تر از صفحه‌گسترده استفاده می‌کنید و می‌توانید در زمان جمع‌آوری تحلیل‌ها در آینده در زمان بیشتری صرفه‌جویی کنید.
اخبار را مستقیماً در صندوق ورودی خود جستجو کنید
*ضروری