تکرار سلول در اکسل — راهنمای کاربردی

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

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

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

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

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

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

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

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

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

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

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

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

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

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

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

به کمک فرمول نویسی در اکسل می‌خواهیم کاری کنیم که تا زمانی که مقدار سطر کمکی کوچکتر یا مساوی فراوانی مقدار مورد نظر بود، همان مقدار ثبت شود در غیر اینصورت، مقدار خالی یا " " درون سلول جای گیرد. واضح است که به علت شرطی بودن نوع محاسبه باید از تابع 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 استفاده کنید.

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

کلیک کنید

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

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

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

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

 A3: = A2 + C2

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

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

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

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

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

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

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

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

کلیک کنید

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

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

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

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

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

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

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