عمومی, کاربردی 530 بازدید

یک «جدول فراوانی» (Frequency Table) برای شمارش تعداد تکرار یک یا چند مقدار در قالب یک جدول، مورد استفاده آمارشناسان قرار می‌گیرد. به این ترتیب متوجه می‌شویم که بیشترین فراوانی مربوط به کدام دسته یا مقدار است. ولی گاهی لازم است براساس یک جدول فراوانی، مشاهدات را بازسازی کنیم. به این ترتیب باید هر مقدار از ستون مربوط به مقادیر جدول فراوانی را به تعداد مشخصی تکرار کنیم و داده‌های اصلی را بسازیم. در این نوشتار می‌خواهیم عمل تکرار سلول در اکسل را به منظور استخراج مشاهدات از جدول فراوانی انجام دهیم. شاید در نگاه اول این کار ساده به نظر برسد ولی اگر تعداد فراوانی‌ها زیاد باشند یا گروه‌ها یا مقادیر متعددی در جدول فراوانی وجود داشته باشد، تقریبا این کار با مشکل همراه خواهد شد. به همین دلیل از اکسل برای خودکار سازی این عملیات استفاده خواهیم کرد.

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

تکرار سلول در اکسل

یک جدول اطلاعاتی را در کاربرگ اکسل در نظر بگیرید. ستون اول با عنوان دسته، نشانگر مقدارهای مختلف متغیر و ستون دوم یا فراوانی، تکرار آن‌ها را نشان می‌دهد. در حقیقت چنین جدول را یک «جدول فراوانی» (Frequency Table) می‌نامیم. جداول فراوانی برای خلاصه‌سازی داده‌ها و اطلاعات بسیار مناسب هستند. ولی هدف از این نوشتار، انجام عمل عکس است. یعنی می‌خواهیم براساس یک جدول فراوانی، مشاهدات اصلی این جدول را بازسازی کنیم. به این ترتیب عمل تکرار سلول در اکسل با توجه به تعداد فراوانی صورت خواهد گرفت.

اگر به تصویر ۱ نگاهی بیندازید، این تبدیل را به خوبی درک خواهید کرد. می‌خواهیم در این متن، به کمک بعضی از ابزارها و توابع اکسل چنین عمل را انجام دهیم.

worksheet for frequency table to cases
تصویر ۱: تبدیل جدول فراوانی به مشاهدات سطری

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

columnar repeated contents in excel
تصویر ۲: تبدیل جدول فراوانی به مشاهدات به صورت ستونی

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

جداسازی متن و عدد از رشته ترکیبی

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

removenum and removetext functions
تصویر ۳: استفاده از دو تابع دست‌ساز RemoveNum و RemoveText برای جدا سازی متن و عدد

نکته: در تصویر ۳، از تابع RemoveNum برای جداسازی متن و از تابع RemoveText برای شناسایی تعداد فراوانی و استخراج عدد از رشته متنی استفاده کرده‌ایم. ولی اگر اقلام و محتویات سلول‌ها کم باشد، به شیوه دستی نیز می‌توانیم فراوانی‌ها را از متن تفکیک کرده و در سلول‌های جداگانه ثبت کنیم. کدهای این «توابع دست‌ساز» (User Define Function) در فایل نمونه اکسل که در انتهای متن معرفی شده، وجود دارد.

تکرار سلول در اکسل به صورت افقی

همانطور که مشخص است، در این بخش، قرار است با کپی کردن یک فرمول بتوانیم به تعداد مشخصی، محتویات یک سلول را در سلول‌های دیگر ایجاد کنیم. فرض کنید عبارت مورد نظر در سلول A2 و تعداد تکرار آن در سلول B2 نوشته شده است. برای آنکه تعداد تکرارها را کنترل کنیم از یک سطر کمکی نیز استفاده کرده‌ایم. مقدارهای از ۱ تا مثلا 20 را هم در سطر اول کاربرگ و از ستون C به بعد وارد کرده‌ایم. سطر دوم از کاربرگ (از ستون C به بعد) محلی است که باید عبارت‌های متنی در آن‌ها تکرار شوند.

به تصویر ۴، توجه کنید. این بخش‌ها را در این تصویر مشاهده می‌کنید.

auxiliary row
تصویر ۴: سطر کمکی برای تکرار سلول در اکسل

به کمک فرمول نویسی در اکسل می‌خواهیم کاری کنیم که تا زمانی که مقدار سطر کمکی کوچکتر یا مساوی فراوانی مقدار مورد نظر بود، همان مقدار ثبت شود در غیر اینصورت، مقدار خالی یا ” ” درون سلول جای گیرد. واضح است که به علت شرطی بودن نوع محاسبه باید از تابع IF در اکسل کمک بگیریم. با توجه به فرضیات گفته شده، فرمول را برای سلول C2 به صورت زیر خواهیم نوشت.

C2: = IF(B2 >= C1  ,A2,” “)

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

C2: = IF($B2 >= C1  ,$A2,” “)

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

C2: = IF($B2 >= C$1, $A2,” “)

به این ترتیب با کپی کردن سلول C2 در سلول‌های دیگر مانند C2 تا V25 نتیجه‌ای مانند تصویر ۱ حاصل خواهد شد.

توجه داشته باشید که در نوشتن فرمول‌های بالا این قاعده را به کار برده‌ایم که عبارت «:C2» به این معنی است که سلول C2 را انتخاب کرده و فرمول بعد از علامت «:» را در آن ثبت کنید. به این ترتیب مشخص است که سلول وابسته و پیش‌نیازهای آن چه هستند.

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

C2: = IF($B2 >= C$1, “▓”,” “)

با این کار، محتویات سلول‌ها، علامت خواهد بود. برای درج این علامت درون سلول، از کلید‌های ترکیبی Alt+178 استفاده کنید. اگر هم می‌خواهید این علامت تمامی عرض سلول را پر کند، از پنجره قالب‌بندی Format Cell و برگه Alignment گزینه Fill‌ را در قسمت Horizontal انتخاب کنید. در تصویر 5، پنجره مربوط به قالب‌بندی و تراز متن را مشاهده می‌کنید.

fill format cells
تصویر ۵: پنجره قالب‌بندی با تنظیم گزینه Fill

تکرار سلول در اکسل به صورت عمودی

در این بخش می‌خواهیم جدول فراوانی را به صورتی در بیاوریم که مقادیر یا مشاهدات به صورت پشت ‌سرهم قرار گرفته و مطابق با تصویر ۲، در یک ستون از کاربرگ ظاهر شوند. به این ترتیب تکرار سلول در اکسل صورت خواهد گرفت. همانطور که در تصویر ۶ مشاهده می‌کنید، در اینجا احتیاج به دو ستون کمکی داریم. در قسمت اول که در ستون A قرار گرفته است، مکان اولین مقدار یا رده را از جدول فراوانی استخراج می‌کنیم. این ستون با توجه جمع فراوانی هر رده با مقدار «محاسبه کمکی» رده قبلی حاصل می‌شود.

به این منظور کافی است ابتدا مقدار اولین سلول در ستون محاسبه کمکی را برابر با ۱ وارد کرده و برای سلول‌های بعدی این ستون، از فرمول زیر کمک بگیرید.

 A3: = A2 + C2

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

نکته: محاسبه کمکی را برای یک سطر بعد از کل جدول اطلاعاتی نیز به کار برده‌ایم. دلیل این امر را بعدا توضیح خواهیم داد.

برای تکرار سلول در اکسل یک ستون کمکی را براساس آخرین مقدار محاسبه شده در قسمت قبل، ایجاد می‌کنیم. در نظر داشته باشید که آخرین مقدار در مرتبه هجدهم (و البته قبل از رده ۱۹) خواهد بود. به این ترتیب لیست یا دنباله‌ای از اعداد ۱ تا ۱۹ را در ستون کمکی ثبت می‌کنیم. برای آشنایی با نحوه ایجاد چنین دنباله عددی بهتر است مطلب دنباله در اکسل — از صفر تا صد را مطالعه کنید.

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

auxiliary columns
تصویر 6: شیوه بدست آوردن محاسبه کمکی و ستون کمکی

فرض کنید بخواهیم برای سلول F2، فرمول را مشخص کنیم. کافی است به کمک تابع Vlookup، موقعیت آن سطر را در جدول فراوانی مشخص کرده و مقدار مربوط به ستون دوم جدول اطلاعاتی را نمایش دهیم. توجه داشته باشید که مقادیر مربوط به ستون A، از کم به زیاد مرتب شده‌اند و تابع Vlookup نیز باید نزدیک‌ترین مقدار را مورد جستجو قرار دهد. پس این تابع را به صورت زیر به کار می‌بریم.

F2: = VLOOKUP( E2 , A2 : B6 , 2 )

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

برای مثال فرض کنید که این فرمول در ردیف ۱۸ام کپی شده. تابع Vlookup به دنبال مقدار ۱۸ در لیست مرتب شده در ستون A می‌گردد ولی مقدار دقیق آن را پیدا نمی‌کند. وقتی به مقدار ۱۹ در این لیست می‌رسد متوجه می‌شود که مقدار مورد جستجو (۱۸) از مقدار ۱۹ کوچکتر است ولی هنوز به ۱۸ نرسیده، پس باید پاسخ نزدیک‌تر یعنی مقدار ردیف قبل از ۱۹ را به عنوان نتیجه نشان دهد. در نهایت فرمول به صورت زیر در خواهد آمد.

F2: = VLOOKUP( E2 , $A$2 : $B$6 , 2 , TRUE )

به این ترتیب با کپی کردن سلول F2 در سلول‌های ردیف‌های بعدی این ستون، نتیجه‌ای به مانند تصویر ۲ ایجاد خواهد شد.

نکته: پس از اینکه این لیست ایجاد شد، می‌توانید به کمک اجرای دستور Copy و Paste Special به همراه گزینه Values، مقادیر آن را به هر کجای کاربرگ جاری یا کاربرگ‌های دیگر منتقل کنید.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل

آموزش فرمول نویسی و توابع در اکسل training course

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

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

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

خلاصه و جمع‌بندی

در این نوشتار با استفاده از چند ترفند ساده، یک مشکل به ظاهر پیچیده را حل کردیم. در حقیقت با در نظر گرفتن یک جدول فراوانی، مقادیر را توسط تکرار سلول در اکسل بازیابی کرده و مشاهدات اولیه برای تولید جدول فراوانی را ساختیم. در این بین ممکن است شیوه یا رویکردهای دیگری نیز برای انجام این کار در نظر گرفته شود ولی استفاده از فرمول نویسی و به کارگیری توابع اکسل راه‌کاری است که علاوه بر سادگی، قدرت و سرعت انجام محاسبات را برای تعداد زیادی از سلول‌ها، میسر می‌سازد. در این نوشتار از توابع IF ،Vlookup و چند «تابع دست‌ساز» (User Define) استفاده کرده و فرمولی تهیه کردیم که به تعداد مشخص شده، محتویات یک سلول را در سلول‌های مجاور کپی کرده یا تکرار می‌کند. عمل تکرار سلول در اکسل را هم به صورت افقی و هم به صورت عمودی در این متن مورد بررسی قرار دادیم.

اگر می‌خواهید توابع و نحوه استفاده از کدهای برنامه نویسی شده در این متن را به منظور تکرار سلول در اکسل دریافت کنید، بهتر است کارپوشه مربوطه را به قالب فشرده از اینجا دریافت کنید. پس از آنکه این فایل را از فشردگی خارج کردید، فایل اکسل Book1.xlam را باز کرده و می‌توانید به فرمول و توابع آن دسترسی داشته باشید. به منظور دسترسی به کد ویژوال بیسیک مربوط به توابع دست‌ساز RemoveText و RemoveNum که در این متن از آن‌ها استفاده کردیم، از کلید‌های ترکیبی Alt+F11 استفاده کنید. البته پس از باز کردن این فایل، باید روی گزینه Enable Content در پایین نوار فرمول کلیک کنید تا دسترسی به ماژول‌های ویژوال بیسیک برایتان فراهم شود.

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

آرمان ری بد (+)

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

آیا این مطلب برای شما مفید بود؟

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *