نرم‌افزار مایکروسافت اکسل ابزاری قدرتمند است که می‌تواند اکثر کارهایی را که یک روزنامه‌نگار برای تحلیل داده نیاز دارد تا الگو‌های جذاب را کشف کند، انجام دهد. این کارها شامل چهار مورد زیر می‌شوند:
- مرتب‌سازی
- جدا کردن(فیلتر کردن)
- استفاده از توابع ریاضی و متنی
- استفاده از جداول محوری
معرفی اکسل
اکسل می‌تواند عملیات متعددی را روی مقادیر زیادی داده که در قالب یک جدول(با ستون و ردیف) سازمان یافته‌اند اجرا کند. در ستون‌ها(که با حروف …,A,B,C نمایش داده می‌شوند) معمولا متغیرها(مانند اسم، سن، تعداد جنایت و …) قرار می‌گیرند و در ردیف اول نام متغیرها نوشته می‌شود. ردیف‌های دیگر برای اطلاعات ثبت شده‌ی(record) افراد یا مواردی است که تحلیل می‌شوند. در هر سلول بخشی از مجموعه داده ثبت می‌شود.
نسخه‌های جدید اکسل می‌توانند ۱۰۴۸۵۷۶ رکورد همراه با ۱۶۳۸۴ متغیر را در خود جای دهند. صفحه گسترده‌ی(spreadsheet) اکسل همچنین می‌تواند چندین جدول را در صفحه‌های جداگانه ذخیره کند. این صفحه‌ها در پایین صفحه به صورت تب(tab) قابل دسترسی هستند.
مرتب‌سازی(Sorting)
یکی از پرکاربردترین قابلیت‌های اکسل، مرتب کردن داده‌ها به صورتی است که اطلاعات بیشتری از درون آن نمایان شود. اغلب اوقات، ما فهرست‌هایی را دریافت می‌کنیم که براساس ترتیب حروف الفبایی مرتب شده‌اند. ترتیب الفبایی تنها برای پیدا کردن یک رکورد مشخص در یک فهرست طولانی کاربرد دارند. در روزنامه‌نگاری، ما معمولا به اکسترمم‌ها علاقه‌مندیم: بزرگترین، کوچکترین، بیشترین، کمترین، بهترین، بدترین.
در مجموعه داده‌ای که در این مطلب از آن استفاده می‌شود، فهرستی از استان‌های ایتالیا وجود دارد که در آن تعداد انواع جرم‌های رخ داده شده در هر کدام از آنها در سال ۲۰۱۴ به نمایش گذاشته شده است. در اینجا می‌بینید که این فهرست چطور به ترتیب حروف الفبا مرتب شده است.
این فهرست وقتی جذاب‌تر می‌شود که بر اساس تعداد قتل‌های رخ داده، از زیاد به کم مرتب شود و خشن‌ترین شهر در بالای فهرست قرار گیرد.
برای مرتب‌سازی دو روش وجود دارد. از اولین روش که سریع هم است می‌توان برای مرتب‌سازی داده‌های یک متغیره استفاده کرد. یکی از سلول‌های ستونی که می‌خواهید مرتب شود را انتخاب کنید(در این مثال “Murders”(قتل‌ها)) سپس روی آیکون Sort & Filter کلیک کنید و به هر صورت که علاقه‌مندید آن را مرتب کنید(از کوچک به بزرگ، از بزرگ به کوچک و …).
حواستان باشد که یکی از سلول‌ها را انتخاب کنید و نه حرف ستون(در این مورد D) را. به مثال زیر توجه کنید:
اگر حرف ستون را انتخاب کنید با یک پیام هشدار مواجه می‌شوید. در این حالت گزینه‌ی Continue with the current selection را انتخاب نکنید، چرا که فقط مقادیر همان ستون انتخابی مرتب می‌شود و در نتیجه، داده‌های شما به هم می‌ریزد. روش دیگر مرتب‌سازی زمانی به کار می‌آید که شما می‌خواهید داده‌هایتان را بر اساس بیشتر از یک متغیر مرتب کنید. به عنوان مثال فرض کنید که می‌خواهیم اطلاعات جرائم(crime) را ابتدا بر اساس ترتیب الفبایی مناطق(territory) انجام دهیم و سپس بر اساس تعداد قتل‌ها(murders) و از زیاد به کم. ابتدا در نوار ابزار روی تب data و بعد از آن روی آیکون sort کلیک می‌کنیم. بعد از آن هر کدام از متغیرها را که می‌خواهیم مرتب شود را انتخاب می‌کنیم. با کلیک بر روی add level، سطح(متغیر) جدیدی برای مرتب‌سازی اضافه خواهد شد.
و در نهایت این نتیجه حاصل می‌شود:
جدا کردن(Filtering)
بعضی اوقات شما می‌خواهید بخش بخصوصی از رکوردهای یک مجموعه‌ی بزرگ داده را بررسی کنید. برای این کار می‌توانید از ابزار فیلتر اکسل استفاده کنید. در toolbar روی تب Data بروید و سپس روی آیکون Filter کلیک کنید. دکمه‌های کوچکی بالای هر ستون ظاهر می‌شوند.
فرض کنید می‌خواهیم فقط اطلاعات مربوط به منطقه‌ی «لاتزیو»(Lazio) را مشاهده کنیم. روی دکمه‌ی مربوط به ستون منطقه(territory) کلیک می‌کنیم. نشان «انتخاب همه‌ی گزینه‌ها»(Select All) را بر می‌داریم و سپس Lazio را از لیست انتخاب می‌کنیم، مانند تصویر زیر:
حاصل کار اینگونه می‌شود:
ملاحظه کنید که شما در حال حاضر فقط ردیف‌های ۳۶، ۴۴، ۷۸، ۸۰ و ۱۰۴ را می‌بینید. ردیف‌های دیگر هم همچنان وجود دارند ولی دیده نمی‌شوند. امکان اعمال فیلترهای پیچیده‌تری هم وجود دارد. به طور مثال فرض کنید می‌خواهید رکوردهایی را ببینید که در آنها تعداد «دزدی از خانه»(burglaries) بزرگتر یا مساوی ۵۰۰۰ فقره و تعداد «سرقت اتومبیل»(car thefts) کمتر از ۲۰۰۰ فقره باشد. ابتدا با فیلتر کردن «دزدی از خانه» شروع می‌کنیم:
سپس…
همین کار را برای «سرقت اتومبیل» انجام دهید، در نهایت به اطلاعات مورد نظرمان دست پیدا می‌کنیم.
استفاده از توابع ریاضی و متنی(math and text functions)
اکسل تعداد زیادی تابع آماده و کاربردی برای اجرای عملیات ریاضی و کار با تاریخ‌ها(ِdate) و متون(text) دارد. برای مثال فرض کنید می‌خواهیم مجموع تعداد قتل‌ها در هر استان را محاسبه کنیم. برای انجام آن باید به پایین ستون D برویم، یک ردیف را خالی بگذاریم و در سلول D106 فرمول (SUM(D2:D104= را بنویسیم. قرار دادن علامت مساوی (=) برای همه‌ی توابع ضروری است. علامت دو نقطه (:) به معنی تمام عددهای بین D2 تا D114 است. بعد از نوشتن فرمول، کلید Enter را فشار دهید، نتیجه‌ی کار به این صورت می‌شود:
دلیل اینکه یک ردیف را خالی می‌گذاریم اینست که عدد مجموع از جدول اصلی جدا باشد تا در موقع مرتب‌سازی، داخل جدول به حساب نیاید و عملیات به درستی انجام شود. اغلب اوقات شما می‌خواهید که محاسبه را برای همه‌ی ردیف‌های داده‌ی خود انجام دهید. برای مثال شاید بخواهید برای مقایسه‌ی معضل سرقت اتومبیل در شهرهایی با وسعت مختلف، نرخ سرقت اتومبیل(تعداد اتومبیل‌هایی که به ازای هر ۱۰۰هزار نفر جمعیت دزدیده شده‌اند) را محاسبه کنید. برای این کار باید در ستون J (اولین ستون خالی) یک متغیر جدید به نام «نرخ سرعت اتومبیل به ازای ۱۰۰هزار نفر»(Car Theft Rate per 100k) بسازیم. بنابراین در سلول J2 این فرمول را وارد می‌کنیم: G2/C2)*100000)=
فرمول مورد نظر تعداد اتومبیل‌های سرقت شده را بر جمعیت شهر تقسیم می‌کند، سپس حاصل را در ۱۰۰۰۰۰ ضرب می‌کند. نتیجه اینگونه خواهد شد:
همچنین روی تب Home می‌توانید نوع اعداد(Numbers Format) را تعیین کنید:
نوشتن این فرمول برای همه‌ی ۱۰۳ ردیف داده کاری خسته‌کننده است. خوشبختانه اکسل برای کپی کردن سریع فرمول راه میان‌بری را در اختیار کاربران قرار داده است. برای این کار باید نشانگر(cursor) ماوس را روی نقطه‌‌ای که در گوشه‌ی پایین سمت راست سلولی که فرمول در آن نوشته شده قرار دهید. وقتی که نشانگر در جای درستی قرار گرفت به یک علامت مثبت کوچک سیاه‌رنگ(+) تغییر شکل می‌دهد. در همین نقطه با دبل کلیک کردن، فرمول برای همه‌ی سلول‌های پایینی تا اولین سلول خالی ستون سمت چپ اعمال می‌شود. اگر قصد داشتید فرمول فقط تا ردیفی خاص تکرار شود علامت + را تا ردیف مورد نظرتان بکشید. نتیجه‌ی کار در تصویر زیر به نمایش در آمده است:
دقت کنید که فرمول برای هر ردیف فرق می‌کند یعنی در ردیف ۵ فرمول به این صورت نوشته می‌شود: G5/C5*100000= و به همین ترتیب برای سایر ردیف‌ها، بنابراین این راه جایگزین کار را بسیار ساده می‌کند. حالا اگر «نرخ سرقت اتومبیل» را بصورت نزولی(از زیاد به کم) مرتب کنیم شهرهایی که بیشترین مشکل «سرقت اتومبیل» را دارند مشاهده خواهیم کرد.
با مرتب کردن به صورت صعودی(از کم به زیاد)، شهرهایی که کمترین جرایم در آنها رخ می‌دهد را خواهیم دید:
در این جا چند مورد از تابع‌های کاربردی دیگر را که به شکل‌های مشابه می‌توان از آنها استفاده کرد معرفی می‌کنیم.
(شما می‌توانید عملیات جمع، تفریق، ضرب و تقسیم را با استفاده از علامت‌های +، –،* و / انجام دهید.)
- AVERAGE=: محاسبه‌ی میانگین اعداد یک ردیف یا ستون یا مجموعه‌ای از اعداد
- MEDIAN=: پیدا کردن میانه در یک ردیف یا ستون یا مجموعه‌ای از اعداد
- COUNT=: شمارش تعداد آیتم‌های موجود در یک ردیف یا ستون یا مجموعه‌ای از اعداد
- MAX=: پیدا کردن بزرگترین عدد در یک ردیف یا ستون یا مجموعه‌ای از اعداد
- MIN=: پیدا کردن کوچکترین عدد در یک ردیف یا ستون یا مجموعه‌ای از اعداد
همچنین در اکسل توابع متنی متنوعی هم وجود دارند که می‌توانند رشته‌متن‌‌ها(text strings) را به هم وصل کند و یا از هم جدا کند، به طور مثال:
اگر “Steve” در سلول B2 و “Doig” در سلول C2 قرار داشته باشند، فرمول B2&” “&C2= نتیجه‌اش می‌شود: “Steve Doig” و C2&”, “&B2= برابر می‌شود با “Doig, Steve”. سایر توابع متنی عبارتند از:
- SEARCH=: پیدا کردن یک رشته متن در یک رشته‌ی بزرگتر و تشخیص موقعیت قرارگیری آن
- LEN=: شمارش تعداد کاراکترهای موجود در یک رشته متن
- LEFT=: جدا کردن تعداد کاراکترهای معین از سمت چپ رشته متن
- RIGHT=: جدا کردن تعداد کاراکترهای معین از سمت راست رشته متن
- MID=: جدا کردن تعداد کاراکترهای معین از قسمت مورد دلخواه شما از یک رشته متن
همچنین می‌توانید روی تاریخ هم محاسباتی را انجام دهید، مانند محاسبه‌ی تعداد روزها یا سال‌های بین دو تاریخ یا ساعت‌ها، دقیقه‌ها و/یا ثانیه‌های بین دو زمان مختلف. به عنوان نمونه اگر تاریخ امروز را ۲۴ آوریل ۲۰۱۹ در نظر بگیریم، برای محاسبه‌ی سن(بر اساس تعداد سال) کسی که تاریخ تولدش در سلول B2 ثبت شده است می‌توان از این فرمول استفاده کرد:
(DATE(2019,4,24)-B2)/365.25=
اولین قسمت فرمول تعداد روزهای بین دو تاریخ را محاسبه می‌کند و سپس حاصل آن بر ۳۶۵/۲۵ تقسیم می‌شود(۰/۲۵ برای محاسبه سال‌های کبیسه در نظر گرفته شده) تا تعداد سال بدست بیاید.
یک تابع مفید دیگر WEEKDAY= است که به شما می‌گوید تاریخ مورد نظر شما چه روزی از هفته(از ۱:یکشنبه تا ۷:شنبه) است. برای مثال اگر تاریخ تولد یک نفر بیست و یکم آوریل ۱۹۴۸ میلادی باشد با استفاده از این تابع ((WEEKDAY(DATE(1948,4,21= که خروجی آن در این مورد عدد ۴ می‌شود می‌توان فهمید که او در روز چهارشنبه به دنیا آمده است.
جداول محوری(Pivot Table)
یکی از هنرهای اکسل قابلیت خلاصه کردن اطلاعات دسته‌بندی شده است. ابزاری که این کار را انجام می‌دهد جدول محوری(Pivot Table) نام دارد که می‌تواند جداول متقاطع تعاملی، داده‌ها را بر اساس دسته‌بندی آنها بسازد. برای ساختن جداول محوری، هر ستون از داده‌ی شما باید عنوان داشته باشد. در حقیقت بهتر است همیشه بلافاصله بعد از اینکه یک ستون جدید ساختید عنوان متعیر را هم به آن اضافه کنید.
ابتدا مطمئن شوید که نشانگر ماوس روی یکی از سلول‌های جدول قرار گرفته باشد. به tool bar بروید و اول روی تب Insert و بعد از آن روی آیکون Pivot Table کلیک کنید. پنجره‌ای شبیه تصویر زیر باز می‌شود:
روی OK کلیک کنید تا یک صفحه‌ی جدید شبیه تصویر زیر باز شود.
در داده‌ی مثال ما، اطلاعات ۱۰ استان(Province) در ۲۰ منطقه‌ی(Territory) ایتالیا دیده می‌شود. فرض کنید که می‌خواهیم مجموع تعداد قتل‌ها(murders) در هر منطقه(Territory) را بدست بیاوریم. برای این کار باید فهرستی درست کنیم که در آن مجموع تعداد قتل‌ها کنار هر منطقه نوشته شده باشد. برای ساختن این جدول محوری(pivot table)، با استفاده از ماوس، Territory را از لیست متغیرها بر می‌داریم و آن را در بخش Row Labels(عنوان ردیف‌ها) قرار می‌دهیم. پس از آن متغیر Murders را به بخش Values(متغیرها) منتقل می‌کنیم. حاصل کار این گونه می‌شود:
اگر ستون Sum of murders(مجموع قتل‌ها) را به صورت نزولی مرتب کنید این نتایج به دست می‌آید:
امکان ساختن جداول محوری خیلی پیچیده‌تری هم وجود دارد، اما بهتر است برای بدست آوردن پاسخ هر پرسش یک جدول محوری جدید بسازید. تعداد زیادی جدول ساده قابل قهم‌تر از یک جدول خیلی پیچیده است که می‌خواهد جواب چندین سوال را یک جا به شما ارائه بدهد.
دکمه‌ی فلش‌مانند کوچک سیاه روی بخش Values(مقادیر) پنجره‌ای را به نمایش می‌گذارد که به شما اجازه می‌دهد برای خلاصه کردن و نمایش نتایج، انتخاب‌های متنوعی داشته باشید. روی Value Field Settings(تنظیمات بخش مقادیر) کلیک کنید تا پنجره‌ی زیر باز شود.
چند نکته‌ی دیگر
اکسل به جز فرمت‌های اصلی خودش(xls. یا xlsx.) توانایی خواندن فرمت‌های متنوع دیگری را هم دارد. برای مثال می‌توانید فایل‌های متنی را که در آنها ستون‌های داده به وسیله ویرگول(،)، تب(tab) و سایر کاراکترها از یکدیگر جدا شده‌اند را وارد(import) اکسل کنید، مانند این:
اگر شما یک صفحه‌ی وب پیدا کردید که درون آن اطلاعاتی به شکل جدول(ستون‌ها و ردیف‌ها) وجود داشت، اکسل قادر است آن را در قالب یک صفحه گسترده باز کند. جدول را copy و سپس در اکسل paste کنید، بیشتر وقت‌ها اطلاعات در ستون‌های درست جایگذاری می‌شوند.
پیدا کردن داده
چند سالی است در بسیاری از کشورهای جهان، سازمان‌های دولتی دسترسی عمومی به داده‌های خود را در قالب فایل اکسل و سایر فرمت‌ها امکان‌پذیر کرده‌اند. این حرکت در ایران هم آغاز شده است و به طور مثال شهرداری تهران در وب‌سایت داده‌نمای تهران، اطلاعات آماری مرتبط با پایتخت را به صورت فایل‌های اکسل در اختیار مخاطبان خود قرار داده است.
یک ترفند برای پیدا کردن داده‌های جذاب در سازمان‌های دولتی و وب‌سایت‌های ایرانی، استفاده از گوگل و اضافه کردن عبارت (gov.ir. یا org.ir. یا ir. یا) site:.gov به همراه filetype:xls است.
منبع: schoolofdata