در این آموزش چند روش سریع برای انتخاب تصادفی نام‌ها، اعداد یا داده‌های دیگر در اکسل به شما یاد می‌دهیم. همچنین یاد می‌گیرید که چگونه یک نمونه‌ی تصادفی غیر تکراری بگیرید و چگونه به طور تصادفی تعداد یا درصد مشخصی از سلول‌ها، ردیف‌ها یا ستون‌ها را با کلیک ماوس انتخاب کنید.
این که آیا شما برای راه اندازی محصول جدید تحقیق می‌کنید یا نتایج کمپین بازاریابی خود را ارزیابی می‌کنید، مهم است که از یک نمونه‌ی بی‌طرف از داده‌ها برای تحلیل خود استفاده کنید؛ و ساده‌ترین راه برای دستیابی به این هدف، انتخاب تصادفی در اکسل است.
- نمونه‌ی تصادفی چیست؟
-
نحوه‌ی انتخاب تصادفی در اکسل با فرمول
- انتخاب یک عدد یا مقدار متنی تصادفی از یک لیست
- انتخاب تصادفی بدون تکراری
- انتخاب تصادفی سطرها در اکسل
نمونه‌ی تصادفی چیست؟
قبل از بحث در مورد تکنیک‌های نمونه‌گیری، بگذارید کمی اطلاعات پیش‌زمینه در مورد انتخاب تصادفی و چه زمانی ممکن است بخواهید از آن استفاده کنید داشته باشیم.
در آمار و احتمالات، یک نمونه‌ی تصادفی زیرمجموعه‌ای از داده‌های انتخاب‌شده از یک مجموعه داده‌ی بزرگ‌تر، یعنی جمعیت است. هر عنصر از نمونه‌ی تصادفی کاملاً تصادفی انتخاب میشود و احتمال انتخاب برابر دارد. چرا به یکی نیاز دارید؟ در اصل، برای به دست آوردن نمایندگی غیر مغرضانه از کل جمعیت.
به عنوان مثال، شما می‌خواهید یک بررسی کمی در بین مشتریان خود انجام دهید. بدیهی است، عاقلانه نیست که یک پرسشنامه برای هر فرد در بانک اطلاعاتی چند هزار نفری خود ارسال کنید؛ بنابراین، نظرسنجی شما با چه کسانی است؟ آیا این 100 مشتری جدید خواهد بود، یا 100 مشتری اول که به ترتیب حروف الفبا ذکر شده‌اند، یا 100 نفر با کوتاه‌ترین نام؟ هیچکدام از این رویکردها متناسب با نیازهای شما نیستند زیرا آنها ذاتاً مغرضانه هستند. برای به دست آوردن یک نمونه‌ی بی‌طرف در جایی که هر کس فرصت انتخاب برابر خود را دارد، با استفاده از یکی از روش‌های گفته شده در زیر، یک انتخاب تصادفی انجام دهید..
انتخاب تصادفی در اکسل با فرمول
هیچ تابع داخلی برای انتخاب تصادفی سلول در اکسل وجود ندارد، اما می‌توانید از یکی از توابع RAND و RANDBETWEEN برای تولید اعداد تصادفی به عنوان یک راه حل استفاده کنید. احتمالاً این‌ها را نمی‌توان فرمولهای شهودی ساده نامید، اما کارایی دارند.
نحوه‌ی انتخاب یک مقدار تصادفی از یک لیست
به فرض شما لیستی از نام‌ها در سلول‌های A2:A10 دارید و می‌خواهید به طور تصادفی یک نام را از لیست انتخاب کنید. این کار با استفاده از یکی از فرمولهای زیر قابل انجام است:
=INDEX($A$2:$A$10,RANDBETWEEN(1,COUNTA($A$2:$A$10)),1)
یا
=INDEX($A$2:$A$10,RANDBETWEEN(1,ROWS($A$2:$A$10)),1)
تمام! انتخابگر نام تصادفی شما برای اکسل کاملاً تنظیم و آماده‌ی خدمت است:
توجه: لطفاً توجه داشته باشید که RANDBETWEEN یک تابع متغیر است، به این معنی که با هر تغییری که در صفحه‌ی کار ایجاد می‌کنید، دوباره محاسبه میشود. در نتیجه، انتخاب تصادفی شما نیز تغییر خواهد کرد. برای جلوگیری از این اتفاق، می‌توانید نام استخراج شده را کپی کرده و آن را به صورت مقدار (Value) در سلول دیگری بچسبانید (Paste Special > Values).
طبیعتاً، این فرمول‌ها نه تنها می‌توانند اسامی تصادفی را انتخاب کنند، بلکه اعداد تصادفی، تاریخ یا هر سلول تصادفی دیگری را نیز انتخاب میکنند.
نحوه‌ی کار این فرمول‌ها
به طور خلاصه، شما از تابع INDEX برای استخراج مقداری از لیست بر اساس شماره ردیف تصادفی که توسط RANDBETWEEN برگردانده شده‌است، استفاده می‌کنید.
به طور خاص، تابع RANDBETWEEN یک عدد صحیح تصادفی بین دو مقداری که شما تعیین می‌کنید ایجاد می‌کند. برای مقدار کمتر، عدد 1 را ارائه می‌کنید. برای مقدار بالاتر، از COUNTA یا ROWS استفاده می‌کنید تا تعداد ردیف کل را بدست آورید. در نتیجه، RANDBETWEEN یک عدد تصادفی را بین 1 و تعداد کل ردیف‌های موجود در مجموعه داده‌ی شما برمی‌گرداند. این عدد به آرگومان row_num از تابع INDEX می‌رود و می‌گوید کدام سطر انتخاب شود. برای argument_num، از 1 استفاده می‌کنیم زیرا می‌خواهیم مقداری را از ستون اول استخراج کنیم.
توجه: این روش برای انتخاب یک سلول تصادفی از یک لیست به خوبی کار می‌کند. اگر فرضاً نمونه‌ی شما شامل چندین سلول باشد، فرمول فوق ممکن است چندین پیشامد مشابه را برگرداند زیرا تابع RANDBETWEEN عاری از کپی نیست؛ مخصوصاً برای انتخاب نمونه‌ای نسبتاً بزرگی از لیست نسبتاً کوچک. مثال بعدی نحوه‌ی انجام انتخاب تصادفی غیر تکراری در اکسل را نشان می‌دهد.
نحوه‌ی انتخاب به طور تصادفی در اکسل بدون تکراری
چند روش برای انتخاب داده‌های تصادفی بدون تکراری در اکسل وجود دارد. به طور کلی، می‌توانید از تابع RAND برای اختصاص یک عدد تصادفی به هر سلول استفاده کنید، و سپس با استفاده از یک فرمول Index Rank چند سلول را انتخاب کنید.
با لیستی از نام‌ها در سلول‌های A2:A16، لطفاً این مراحل را برای استخراج چند نام تصادفی دنبال کنید:
-
فرمول Rand را در B2 وارد کرده و آن را تا آخر ستون کپی کنید:
=RAND()
-
فرمول زیر را در C2 قرار دهید تا مقداری تصادفی از ستون A استخراج شود:
=INDEX($Aز2:$A$16, RANK(B2,$B$2:$B$16), 1)
- فرمول فوق را به همان تعداد سلول‌هایی که می‌خواهید انتخاب کنید، کپی کنید. در مثال ما، فرمول را در چهار سلول دیگر کپی می‌کنیم (C2:C6).
تمام! پنج نام تصادفی بدون تکراری استخراج شده است.
نحوه‌ی کار این فرمول‌ها
مانند مثال قبلی، شما از تابع INDEX برای استخراج مقداری از ستون A بر اساس مختصات ردیف تصادفی استفاده می‌کنید. در این حالت، دو تابع مختلف برای به دست آوردن آن لازم است:
- فرمول RAND ستون B را با اعداد تصادفی جمع می‌کند.
- تابع RANK رتبه‌ی عدد تصادفی را در همان ردیف برمی‌گرداند. به عنوان مثال، RANK(B2,$B$2:$B$16) در سلول C2 رتبه‌ی عدد موجود در سلول B2 را می‌گیرد. هنگام کپی کردن در C3، ارجاع نسبی B2 به B3 تغییر می‌کند و رتبه‌ی عدد موجود در سلول B3 را برمی‌گرداند و غیره.
عدد برگشتی توسط RANK به آرگومان row_num از تابع INDEX داده میشود. در آرگومان argument_num، ستون 1 را داده‌اید زیرا می‌خواهید مقدار را از ستون اول استخراج کنید.
احتیاط! همانطور که در تصویر بالا نشان داده شده‌است، انتخاب تصادفی اکسل ما فقط دارای مقادیر منحصر به فرد است. اما از لحاظ تئوری، احتمال بسیار کمی برای بودن نسخه‌های تکراری در نمونه‌ی شما وجود دارد. به این دلیل است: در یک مجموعه داده‌ی بسیار بزرگ، RAND ممکن است اعداد تصادفی تکراری تولید کند، و RANK همان رتبه را برای آن عدد باز می‌گرداند. به شخصه، هرگز در طول آزمونهای خود با مقدار تکراری برخورد نکرده‌ایم، اما در تئوری، چنین احتمالی وجود دارد.
اگر به دنبال یک فرمول همه فن حریف برای انتخاب تصادفی فقط با مقادیر منحصر به فرد هستید، به جای RANK از RANK.EQ + COUNTIF استفاده کنید. فرمول کامل کمی بدقلق است، اما 100٪ عاری از تکراری است.:
=INDEX($A$2:$A$16, RANK.EQ(B2, $B$2:$B$16) + COUNTIF($B$2:B2, B2) - 1, 1)
توجه:
- مانند RANDBETWEEN، تابع RAND نیز با هر محاسبه‌ی مجدد در شیت شما، اعداد تصادفی جدید ایجاد می‌کند و باعث میشود انتخاب تصادفی تغییر کند. برای اینکه نمونه‌ی خود را بدون تغییر نگه دارید، آن را کپی کرده و در جای دیگری به صورت مقادیر جای گذاری کنید (Paste Special > Values)
- اگر یک نام (عدد، تاریخ یا هر مقدار دیگر) بیش از یک بار در مجموعه داده‌های اصلی شما ظاهر شود، نمونه‌ی تصادفی ممکن است حاوی چندین مورد با همان مقدار باشد.
نحوه‌ی انتخاب ردیف‌های تصادفی در اکسل
در صورتی که ورک‌شیت شما دارای بیش از یک ستون از داده‌ها باشد، می‌توانید از این طریق یک نمونه‌ی تصادفی را انتخاب کنید: یک عدد تصادفی را به هر سطر اختصاص دهید، آن اعداد را مرتب کنید و تعداد مورد نیاز از ردیف‌ها را انتخاب کنید. مراحل مفصل در زیر آمده‌است:
- ستون جدیدی را در سمت راست یا سمت چپ جدول خود قرار دهیددرج کنید (ستون D در این مثال).
- در سلول اول ستون درج‌شده، به استثنای عنوان ستون‌ها، فرمول RAND را وارد کنید:
=RAND()
- روی دسته‌ی پر کردن دوبار کلیک کنید تا فرمول تا پایین ستون کپی شود. در نتیجه، شما یک عدد تصادفی به هر سطر اختصاص خواهید داد.
-
اعداد تصادفی را از بزرگ به کوچک (largest to smallest) مرتب کنید (مرتب سازی بر اساس ترتیب صعودی می‌تواند عنوان ستون‌ها را در انتهای جدول منتقل کند، بنابراین حتماً به صورت نزولی مرتب کنید). برای این کار، به تب Data > گروه Sort & Filter بروید و بر روی دکمه‌ی ZA کلیک کنید. اکسل به طور خودکار انتخاب را گسترش می‌دهد و کل ردیف‌ها را به ترتیب تصادفی مرتب می‌کند. اگر از نحوه‌ی انتخاب تصادفی جدول خود کاملاً راضی نیستید، دوباره دکمه‌ی مرتب‌سازی را بزنید تا به آن دوباره مرتب شود.
-
در آخر، تعداد مورد نیازی از سطرها را برای نمونه‌ی خود انتخاب کنید، آنها را کپی کرده و در هر کجا که دوست دارید پیست کنید.
برای مشاهده‌ی دقیق‌تر فرمولهای مورد بحث در این آموزش، می‌توانید ورک‌بوک نمونه‌ی ما را با عنوان Excel Random Selection دانلود کنید.
تابع CONCAT یکی از توابع متنی (Text) اکسل می باشد. در این آموزش پیشرفته اکسل با قسمت های تشکیل دهنده و کاربرد این تابع آشنا می شویم.
پیش نیازهای آموزشی این بخش
چگونه در یک سلول اکسل فرمول بنویسم؟
تکمیل فرمول ها و آرگومان توابع با استفاده از ماوس
ورودی های تابع CONCAT اکسل
آرگومان تابع CONCAT اکسل در پنجره Function Arguments این تابع در تصویر زیر نمایش داده شده است.
همانطور که در تصویر مشخص است اکسل برای تابع CONCAT وقتی برای اولین بار تابع را درج می نمایید دو آرگومان Text1 و Text2 را نمایش می دهد.
با این وجود اگر در توضیحات تابع دقت نمایید تابع CONCAT اکسل می تواند 254 آرگومان متفاوت داشته باشد که این به این معناست که تابع CONCAT می تواند 254 متن (Text) را با همدیگر جمع نماید.
شکل فرمولی تابع CONCAT اکسل
=CONCAT(“Text1“؛”Text2“؛…؛”Text254“)
می توانید محدوده ها را نیز در شکل فرمولی تابع CONCAT بکار ببرید.
=CONCAT(range)
برای آشنایی بیشتر با محدوده ها در اکسل و آموزش نحوه ارجاع یک تابع به یک محدوده یا ترکیبی از محدوده ها این صفحه را مشاهده نمایید.
کارکرد تابع CONCAT اکسل
تابع CONCAT کار جمع زدن متن ها در اکسل را انجام می دهد. این می تواند 254 متن متفاوت را با یکدیگر جمع نماید (بدنبال هم بیاورد).
آرگومان های تابع می تواند محتوای یک سلول، نتیجه یک فرمول یا تابع، کلمات و اعداد ثابت و یا کاراکترهای خاص و بطور کلی هر چیزی که جایگزین یک رشته متنی است باشد.
رشته متنی یک دنباله ادامه دار از کاراکترها می باشد. توجه داشته باشید که فاصله (space) و یک رشته خالی (“”) نیز نمونه هایی از یک رشته متنی می باشند.
تکمیل آرگومان تابع CONCAT از طریق پنجرهFunction Arguments
همه آرگومان های تابع CONCAT از یک نوع (رشته متنی) می باشند و بنابراین تکمیل همه شبیه هم می باشد.
بطور کلی سه راه برای تکمیل آرگومان های تابع CONCAT از طریق پنجرهFunction Arguments در اکسل وجود دارد که عبارتند از:
۱- سلول یا یک محدوده بعنوان آرگومان تابع
برای ارجاع به سلول کافیست نام سلول در جعبه نام (Name Box) اکسل را بدون هیچ کاراکتر اضافه ای (مانند =) در جعبه مقابل هر آرگومان تایپ یا هم بجای تایپ با استفاده از ماوس سلول موردنظر را انتخاب نمایید.
برای ارجاع به یک محدوده باید محدوده موردنظر را در جعبه مقابل هر آرگومان تایپ نمایید و یا بجای تایپ با استفاده از کشیدن و رها کردن ماوس (drag and drop) محدوده موردنظر را انتخاب نمایید.
اگر نحوه کار کردن با ماوس در اکسل را نمی دانید این صفحه را مشاهده نمایید.
۲- عدد یا متن ثابت بعنوان آرگومان تابع
می توان بجای هر یک از آرگومان های تابع از عدد یا متن ثابت استفاده کرد. برای اینکار کافیست عدد یا متن را در کادر روبروی آرگومان وارد نماید.
نکته
اگر یک متن ثابت مثل My name را بعنوان آرگومان تابع وارد نمایید اکسل بصورت خودکار متن را در یک جفت ” (double quotation) بصورت "My name" قرار می دهد.
توجه داشته باشید که اگر از صورت فرمولی تابع استفاده می نمایید خودتان باید متن های ثابت را بین یک جفت ” (double quotation) قرار دهید.
۳- تابع یا فرمول بعنوان آرگومان تابع
می توانید یک تابع یا فرمول را نیز بعنوان آرگومان تابع وارد نمایید. برای اینکار کافیست فرمول یا تابع را بدون نوشتن علامت مساوی در ابتدای آن در کادر روبروی آرگومان تایپ نمایید.
مثال تابع CONCAT
سلول های اکسل را مانند تصویر زیر تکمیل نمایید.
می خواهیم با استفاده از تابع CONCAT در ستون D مقابل هر شخص متنی را ایجاد نماییم که هر شخص چقدر حقوق دریافت می نماید.
برای اینکار سلول D2 را انتخاب نمایید و روی عنوان CONCAT از دسته بندی Text در برگه Formulas کلیک نمایید.
حالا در پنجره Function Arguments آرگومان های تابع را مانند تصویر زیر تکمیل نمایید.
توضیح هر کدام از آرگومان ها
Text1: ارجاع به سلول A2 شده است و مقدار آن به مقدار سلول A2 بستگی دارد.
Text2: یک فاصله خالی (space) است که برای اینکه بین نام و نام خانوادگی فاصله قرار بگیرد و به هم متصل نشوند استفاده کرده ایم.
Text3: ارجاع به سلول B2 شده است و مقدار آن به مقدار سلول B2 بستگی دارد.
Text4: در این آرگومان از عملگر & استفاده کرده ایم. عملگر & مثل تابع CONCAT کار می کند و برای اتصال متن بکار می رود. در اینجا یک فاصله خالی را به سلول C2 متصل کرده ایم و بعنوان آرگومان تابع بکار برده ایم.
Text5: یک متن ثابت است و جمله ما را تکمیل می نماید. توجه داشته باشید که در ابتدای این متن ثابت یه فاصله خالی (space) قرار داده ایم تا فاصله های جمله ما درست باشد.
حالا روی OK کلیک نماید تا نتیجه را در سلول D2 مشاهده نمایید.
حالا ماوس را به گوشه پایین سمت چپ سلول D2 ببرید تا به شکل + در بیاید. در همین حالت دو بار کلیک نمایید تا فرمول بصورت خودکار در سلول D3 کپی گردد.
مثال: قد: 1.79 (متر) وزن 77 کیلوگرم)، شاخص وزنی فرد عبارتست از: 24
