داده های جغرافیایی و سهام

آخرین به‌روزرسانی: 27 دی 1401

در سری مقاله های آموزش اکسل، در فصل گذشته به ویژگی Get & Transform پرداختیم، در این مقاله به بررسی داده های جغرافیایی و سهام می‌پردازیم.

سؤالات پاسخ داده شده در این فصل:

• کدام‌یک از مشخصه‌های موقعیت‌های جغرافیایی را می‌توان در فرمول‌های اکسل مورداستفاده قرارداد؟
• کدام یک از مشخصه‌های شرکت‌ها را می‌توان در فرمول‌های اکسل مورداستفاده قرارداد؟

نوع داده‌های (Data Type) جدید برنامه Office 365 به آن معناست که اکنون سلول‌های حاوی موقعیت جغرافیایی و یا اطلاعات شرکتی دارای قطعات فراوانی از داده‌های جاری مرتبط با موقعیت جغرافیایی (داده های جغرافیایی) و یا شرکتی است. چنانچه از Office 365 استفاده می‌کنید، اکنون می‌توانید فرمول‌هایی بنویسید که مشخصه‌های مکان جغرافیایی متنوعی را برگردانند. مثلاً اگر سلول E4 حاوی عبارت Paris باشد، در آن صورت فرمول E4.Population به سراغ ویکی‌پدیا رفته و جمعیت امروز شهر پاریس را به شما نشان می‌دهد. شما می‌توانید فرمول‌هایی بنویسید که مشخصه‌هایی شرکتی را برگرداند. مثلاً اگر سلول E8 حاوی عبارت Microsoft باشد، فرمول E8.Price قیمت سهام جاری شرکت مایکروسافت را برمی‌گرداند.

داده های جغرافیایی

کدام‌یک از مشخصه‌های موقعیت‌های جغرافیایی را می‌توان در فرمول‌های اکسل مورداستفاده قرارداد؟

برای نمایش نحوه استفاده از نوع داده های جغرافیایی برخی از نام‌های ایالات را در سلول‌های E3:E7 کاربرگی به نام States در فایلی به نام Newdatatypestemp.xlsx وارد کرده‌ایم. پس از انتخاب سلول‌های E3:E7 و انتخاب گزینه Geography در تب Data (همان‌طور که در تصویر 1-41 نشان‌داده‌شده) می‌بینید که آیکون‌های ” کارت” همان‌طور که در تصویر 2-41 نشان‌داده‌شده ظاهر می‌شوند. کلیک کردن روی آیکون کارت موقعیت اطلاعات موجود در سلول را نشان می‌دهد. برخی از این داده‌های مربوط به کارت ایالت ایندیانا در تصویر 2-41 نشان‌داده‌شده‌اند.

داده‌های اطلاعاتی موجود برای ایالت ایندیانا در کارت ایندیانا نمایش‌داده‌شده است. برای واردکردن جمعیت هریک از ایالات در ستون F3 می‌توانید به‌سادگی به سلول F3 رفته و بعد از تایپ E3 فهرستی از مشخصه‌های ایالت ایندیانا را مشاهده خواهید کرد (تصویر 3-41 را ببینید) که می‌توانند در سلول F3 وارد شوند. پس از کلیک کردن بر عبارت Population یا جمعیت، فرمول =E3.Population را خواهید دید. همان‌طور که در تصویر 4-41 نمایش‌داده‌شده، کپی‌کردن این فرمول در محدوده E4:E7 جمعیت هر ایالت را به ما می‌دهد.

به‌جای تایپ مشخصه‌های موردنظر ایالتی در سلول، می‌توانیم از تابع جدید FIELDVALUE استفاده کرده و فرمول = FIELDVALUE(E3,$G$2) را از سلول G3 به محدوده سلول‌های G4:G7 کپی کنید تا محدوده پنج سلول فهرست شده را به ما برگرداند. توجه کنید که اگر محتوی سلول G2 را به مثلاً Capital یا پایتخت تغییر دهید، اکنون ستون G مراکز هر ایالت را فهرست می‌کند.

تصویر 5-41 نشان می‌دهد که چگونه انواع داده جدید می‌توانند ایالت و منطقه مربوط به یک شهر ایالات متحده را برگردانند درحالی‌که تصویر 6-41 نشان می‌دهد که چگونه داده‌های جدید می‌توانند کد تماس تلفنی و میزان امید به زندگی کشورهای انتخاب شده را برگردانند.

ملاحظات

• اگر اکسل درباره موقعیت مکانی که در ذهن دارید تردید داشته باشد، علامت سؤالی مشاهده خواهید کرد. پس از کلیک کردن بر روی علامت سؤال می‌توانید موقعیت مکانی خاصی را انتخاب کنید. مثلاً همان‌طور که در فایلی به نام Xlsx در تصویر 7-41 نشان‌داده‌شده، چهار نمونه از شهر بلومینگتون را وارد کرده و ایالات ایندیانا، مینه‌سوتا، ایلی نویز و کالیفرنیا را به ترتیب انتخاب کرده‌ایم. پس از آن ایالت هر شهر و جمعیت آن را استخراج نموده‌ایم.
• همان‌طور که در کاربرگی به نام Xlsx از فایلی به نام Newdatatypes.xlsx و تصویر 8-41 نشان‌داده‌شده، اگر موقعیت جغرافیایی خود را به جدولی در اکسل مبدل کنید و پس از آن در سرستونی عنوان Population تایپ کنید، جمعیت هر موقعیت مکانی فوراً استخراج و نمایش داده می‌شود، اگر موقعیت مکانی جدید اضافه کنید، جمعیت این موقعیت جدید فوراً وارد می‌شود.

کدام یک از مشخصه‌های شرکت‌ها را می‌توان در فرمول‌های اکسل مورداستفاده قرارداد؟

می‌توانید فرمول‌هایی بنویسید که با واردکردن نام و نماد سهام یک شرکت، اطلاعات اساسی شرکتی انتخاب شده را استخراج نمایید. برای انجام این کار سلول موردنظر را انتخاب کرده و از تب Data نوع Stock Data را انتخاب کنید. مثلاً در کاربرگی به نام Stock.xlsx ترکیبی از نام شرکت‌ها و نماد سهامشان را وارد کرده‌ایم. پس از انتخاب محدوده F4:F8 از تب Data قالب Stock را انتخاب می‌کنیم.

سپس برای هریک از شرکت‌های کارتی را مشاهده خواهیم کرد. کلیک کردن بر هر کارت داده‌های موجود آن شرکت را نمایش خواهد داد. اگر در سلول G4 فرمول =F4 را تایپ کنیم مشخصه‌های مربوط به شرکت مایکروسافت (که در تصویر 9-41 نشان‌داده‌شده) را می‌بینیم که می‌توانند استخراج شوند.

تصویر 10-41 قیمت نهایی هریک از سهام در پایان مبادلات تاریخ 12 نوامبر سال 2018 را نشان می‌دهد. البته هنگامی که فایل را باز کنید داده‌های به‌روزتری خواهید دید.

مسئله‌های این فصل:

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

از ابزار Power Query استفاده کنید تا فهرستی از تمامی کشورها را دانلود کنید و سپس جمعیت و مساحت هر کشور را استخراج نمایید.

از سهام موجود در کاربرگی به نام Stock.xlsx نسبت قیمت به درآمد هر شرکت و تعداد کارمندان را استخراج کنید. این داده‌ها را به شکلی تنظیم کنید که اگر شرکت جدیدی به فهرست اضافه شد اطلاعات به شکل خودکار استخراج شوند.