واردکردن داده ها از فایل متنی یا یک سند
24 خرداد 1401
دقیقه
در این فصل به شما نشان میدهیم که چگونه میتوانید یک فایل متنی را برای استفاده آن در تحلیل دادهها به برنامه مایکروسافت اکسل وارد نمایید.
آخرین بهروزرسانی: 27 دی 1401
در سری مقاله های آموزش اکسل، در فصل گذشته به جریمه و Solver تکاملی پرداختیم، در این مقاله به بررسی واردکردن دادهها از فایل متنی یا یک سند میپردازیم.
جف ساگرین خالق بخش رتبهبندی تیمهای بسکتبال در مجله USA Today و من سیستمی برای رتبهبندی بازیکنان NBA طراحی کردهایم که در بسیاری از تیمهای NBA مثل تیمهای دالاس ماوریک و نیویورک نیکز مورداستفاده قرار گرفته بود. برنامه فورترن جف هر روز در طول فصل بازی اطلاعات فراوانی شامل رتبهبندی هر ترکیب تیم دالاس ماوریک در هر بازی را به شکل یک فایل متنی به دست میآورد. در این فصل به شما نشان میدهیم که چگونه میتوانید یک فایل متنی را برای استفاده آن در تحلیل دادهها به برنامه مایکروسافت اکسل وارد نمایید.
چگونه میتوان دادهها را از فایل متنی به اکسل وارد کرد تا بتوان آنها را تحلیل نماییم؟
احتمالاً تاکنون دادههایی را به شکل فایلهای متنی مایکروسافت ورد یا به شکل فایل متنی با پسوند .txt دریافت کردهاید که بخواهید آنها را برای تحلیل عددی به درونبرنامه اکسل وارد کنید. برای واردکردن یک سند متنی ورد در اکسل ابتدا میبایست آن را به قالببندی فایل متنی (با پسوند.txt) ذخیره کنید. میتوان از ابزار Text Import Wizard برای واردکردن فایل استفاده کرد. با ابزار Text Import Wizard میتوانید دادههای فایل متنی را با یکی از روشهای زیر در ستونهایی تفکیک کنید:
اگر گزینه Fixed-Width را انتخاب کنید، اکسل خود فرض میکند که دادهها از کجا میبایست به ستونهایی تفکیک شوند. البته میتوانید بهآسانی حدسها و فرضهای اکسل را بهدلخواه خود تغییر دهید.
اگر گزینه Delimited (محدود شده) را انتخاب کنید، در این صورت میبایست یک کاراکتر (انتخاب متداول معمولاً کاما، فضای خالی، یا علامت بعلاوه است) و اکسل دادهها را از هرجایی که با آن کاراکتر انتخابی روبرو شود به ستونهایی تفکیک میکند.
فایل Lineupsch39temp.docx بهعنوان مثالی در پوشه Templates این فصل قرارداد شده است. این فایل حاوی مقدار زمانهایی است که هر ترکیب تیم دالاس در طول فصل بازی 2003-2002 بازی کرده است. این فایل همچنین، حاوی رتبهبندی آن ترکیبهای تیمی هست. مثلاً دو خط اول نشان میدهند که ترکیب بازیکنهای بل، فینلی، لافرنتز، نش و نویتزکی در مقابل تیم ساکرامنتو حدود9.05 دقیقه بازی کردند و امتیازی در سطح 19.79 که بدتر از سطح متوسط امتیاز ترکیبهای تیمی NBA بود کسب کردند. (هر 48 دقیقه) فهرست زیر به شما نمونهای از دادهها را نشان میدهد:
Bell Finley LaFrentz Nash Nowitzki – 19.79 695# 9.05m SAC DAL* Finley Nash Nowitzki
Van Exel Williams – 11.63 695# 8.86m SAC DAL* Finley LaFrentz Nash Nowitzki
Van Exel 102.98 695# 4.44m SAC DAL* Bradley Finley Nash Nowitzki Van Exel – 44.26
695# 4.38m SAC DAL* Bradley Nash Nowitzki Van Exel Williams 9.71 695# 3.05m SAC DAL*
Bell Finley LaFrentz Nowitzki Van Exel – 121.50 695# 2.73m SAC DAL* Bell LaFrentz
Nowitzki Van Exel Williams 39.35 695# 2.70m SAC DAL* Bradley Finley Nowitzki Van Exel
Williams 86.87 695# 2.45m SAC DAL* Bradley Nash Van Exel Williams Rigaudeau – 54.55
695# 2.32m SAC DAL*
فرض کنید میخواهیم این اطلاعات مربوط به ترکیب تیمی را به اکسل وارد کنیم تا برای هر ترکیب اطلاعات زیر در ستونهای متفاوتی درج شود:
- نام بازیکن هر تیم
- دقیقههایی که در ترکیب تیمی بازی کرده
- رتبهبندی آن ترکیب
بازیکنی بنام وان اکسل (نام کاملش نیک وان اکسل است) مشکلساز میشود. اگر گزینه Delimited را انتخاب کرده و از یک کاراکتر فضای خالی برای تفکیک دادهها در ستونهایی استفاده کنید، ون اکسل دو ستون اشغال میکند. دادههای عددی برای ترکیبهایی که شامل نام ون اکسل است در ستونی متفاوت از ستون دادههایی مربوط به ترکیبهایی که دارای نام ون اکسل نیست قرار داده میشود. برای حل این مشکل از فرمان Replace در نرمافزار Word استفاده میکنیم تا هرجایی نام ون اکسل ظاهر شده را به اکسل تغییر دهیم. حالا وقتی اکسل دادهها را از جایی که با فضای خالی روبرو شود تفکیک میکند، ون اکسل تنها یک ستون نیاز خواهد داشت. اولین ردیف دادهها به شکل زیر خواهد بود:
Bell Finley LaFrentz Nash Nowitzki – 19.79 695# 9.05m SAC DAL* Finley Nash
Nowitzki Exel Williams – 11.63 695# 8.86m SAC DAL* Finley LaFrentz Nash Nowitzki
Exel 102.98 69 5# 4.44m SAC DAL* Bradley Finley Nash Nowitzki Exel – 44.26 695#
4.38m SAC DAL* Bradley Nash Nowitzki Exel Williams 9.71 69 5# 3.05m SAC DAL* Bell
Finley LaFrentz Nowitzki Exel – 121.50 695# 2.73m SAC DAL* Bell LaFrentz Nowitzki Exel
Williams 39.35 69 5# 2.70m SAC DAL* Bradley Finley Nowitzki Exel Williams 86.87 69 5#
2.45m SAC DAL* Bradley Nash Exel Williams Rigaudeau – 54.55 695# 2.32m SAC DAL*
ترفند اصلی برای واردکردن دادهها از نرمافزار ورد و یا فایل متنی به نرمافزار اکسل استفاده از ابزار Excell text Import Wizard است. همانطور که پیشازاین گفتیم، در ابتدا میبایست فایل ورد را (در این مثال فایل Lineupsch39temp.docx) بهعنوان یک فایل متنی ذخیره کنید. برای انجام این کار بهسادگی فایل را در نرمافزار ورد بازکرده روی تب File کلیک کرده و گزینه Save As را انتخاب میکنیم. فایل را پیدا کرده کلمه Temp را از نام آن حذف میکنیم و در فهرست Save As Type گزینه Plain Text با پسوند (*.txt) را انتخاب میکنیم. در کادر محاورهای File Conversation گزینه Windows که گزینه پیشفرض در قسمت Text Encoding است را انتخاب میکنیم و سپس روی Ok کلیک میکنیم. حالا فایل شما به نام Lineupsch39.txt ذخیره میشود. سند ورد را بسته و فایل Lineupsch39.txt را در اکسل باز کنید، روی منوی File کلیک کرده و گزینه Open را انتخاب کنید، گزینه Browse را کلیک کرده و به پوشه فایلهای متنی بروید، در لیست File Types در سمت راست گزینه *.* را انتخاب کنید، فایل موردنظر را انتخاب کرده و روی دکمه Open کلیک کنید. حالا مرحله اول از ابزار Text Import Wizard را که در تصویر 1-39 نشاندادهشده میبینید.
روشن است که در این مورد گزینه Dilimited را انتخاب میکنیم و دادهها را از هر فضای خالی تفکیک مینماییم. هرچند بیایید فرض کنیم این بار گزینه Fixed Width را انتخاب کردهایم. حالا مرحله دوم کادر محاورهای Text Import Wizard ظاهر میشود که در تصویر 2-39 نشاندادهشده است. همانطور که میبینید، در اینجا میتوان یک نقطه تفکیک را ایجاد، تغییر مکان داد و یا حذف کرد. در بسیاری از عملیات واردکردن دادهها، تغییر نقاط تفکیک ستونها میتواند هم به نتیجهای موفق و هم ناموفق منجر شود
چنانچه در مرحله اول گزینه Delimited را انتخاب کنید، مرحله دوم از Text Import Wizard را به صورتی که در تصویر 3-39 نشاندادهشده خواهید دید. در این مثال فضای خالی را بهعنوان جداکننده انتخاب کردم با انتخاب Treat Conscutive Delimiters As one (یکی درنظرگرفتن جداکنندههای متوالی) اطمینان حاصل میکنیم که نتیجه جداکنندههای متوالی تنها در یک ستون تفکیک داده شوند. توصیه میشود که گزینه Tab همواره انتخاب شود چرا که در صورت عدم انتخاب این گزینه بسیاری از افزونههای اکسل (Add Ins) بهدرستی کار نمیکنند.
وقتی روی گزینه Next کلیک کنید، به مرحله سوم منتقل میشوید که در تصویر 4-39 نشاندادهشده. با انتخاب گزینه General برای قالببندی، به اکسل دستور میدهید که دادههای عددی را بهعنوان عدد و سایر مقادیر را بهعنوان مقادیر متنی در نظر بگیرد.
وقتی بر روی دکمه Finish کلیک کنید، این ابزار دادهها را همانطور که در تصویر 5-39 میبینید به نرمافزار اکسل وارد میکند.
اطلاعات هر بازیکن در ستون جداگانهای (ستونهای A الی E) درج شدهاند؛ ستون F حاوی رتبههای هر ترکیببندی است، ستون G شامل تعداد بازیها، ستون H شامل دقیقههای بازی شده توسط هر ترکیب بازیکن و ستونهای I و J دو تیم هر بازی را فهرست کردهاند. پس از ذخیره این فایل بهعنوان یک فایل اکسل (با پسوند xlsx) میتوانید از همه قابلیتهای تحلیل اکسل برای تحلیل نحوه بازی ترکیببندیهای تیم دالاس استفاده کنید. مثلاً میتوانید میانگین کار آیی تیم را وقتی که دیرک نویتزکی در ترکیببندی تیم حضور دارد را محاسبه کنید.
مسئلههای این فصل:
فایلی به نام kingslineups.xlsx حاوی رتبههای بازی برخی از ترکیببندیهای تیم کینگ ساکرامنتو است. این دادهها را در اکسل وارد کنید.
در مثالی که در این فصل مطرح شد، هر زمانی که هر ترکیببندی بازی کرده است (ستون H) با حرف m پایان میگیرد. این فایل را چنان تغییر دهید که زمان بازی شده توسط هر ترکیببندی یک رقم واقعی باشد.