🚀 مقدمهای بر پردازش معکوس دادهها در اکسل
اکسل به طور پیشفرض، دادههای متنی و عددی را از چپ به راست پردازش میکند. توابعی مانند LEFT یا حتی ورودیهای پیشفرض MID همگی بر اساس اولین کاراکتر سمت چپ عمل میکنند. اما در دنیای واقعی، بسیاری از محاسبات و تحلیلها نیازمند رویکردی متفاوت هستند؛ یعنی شروع از انتهای رشته (سمت راست).
زمانی که نیاز دارید «ارزش مکانی» اعداد (یکان، دهگان، صدگان) را تحلیل کنید، یا الگوریتمهای اعتبارسنجی بانکی (که معمولاً روی رقم آخر حساس هستند) را پیادهسازی کنید، توابع استاندارد به تنهایی کافی نیستند. اینجاست که باید با ترکیب هوشمندانهی توابع ریاضی و متنی، جهت حرکت اکسل را معکوس کنیم.
ما در این مسئله با ترکیب سه تابع MID، LEN و ROW، یک موتور پردازشگر میسازیم که به جای خواندن از “شروع به پایان”، از “پایان به شروع” حرکت میکند.
🛠 معرفی و کاربرد ابزارها (توابع)
برای حل این معما، هر تابع نقش خاصی را در یک “خط تولید” ایفا میکند:
1. تابع MID (استخراجگر)
- نقش: قیچی!
- کاربرد: این تابع مسئول برش دادن و بیرون کشیدن کاراکترهاست. برخلاف
LEFTیاRIGHTکه فقط ابتدا یا انتها را میبینند،MIDمیتواند به هر نقطهای در وسط متن برود و برش را انجام دهد. - در این مسئله: ما به
MIDمیگوییم که به سراغ کاراکترهای انتهایی برود، اما برای یافتن “جای دقیق” برش، به کمک دو تابع دیگر نیاز دارد.
2. تابع LEN (متر نواری)
- نقش: تعیین مرزها.
- کاربرد: این تابع طول کل رشته یا عدد را میشمارد. بدون دانستن طول کل، ما نمیدانیم “آخرین” کاراکتر کجاست.
- در این مسئله: اگر عدد ما ۵ رقمی باشد،
LENعدد ۵ را برمیگرداند. این عدد نقطه شروع محاسبات معکوس ماست.
3. تابع ROW (شمارنده خودکار)
- نقش: موتور حرکت.
- کاربرد: این تابع شماره ردیف یک سلول را برمیگرداند. قدرت اصلی آن زمانی است که فرمول را به پایین میکشید (Drag)؛ در این حالت خروجی آن به صورت خودکار افزایش مییابد (۱، ۲، ۳ و…).
- در این مسئله: این تابع نقش یک متغیر را بازی میکند که در هر سطر یک واحد از طول کل (
LEN) کم میکند تا ما بتوانیم قدمبهقدم از انتهای عدد به سمت ابتدای آن عقبگرد کنیم.
💡 چرا این ترکیب مهم است؟
این فرمول فراتر از یک تفکیک ساده است؛ این الگوی پایه برای حل مسائل پیچیدهتر است، از جمله:
- الگوریتمهای Checksum: بررسی صحت کد ملی یا شماره کارت بانکی (الگوریتم Luhn).
- رمزنگاری ساده: معکوس کردن رشتههای متنی برای کدگذاری.
- فرمتدهی سفارشی: جدا کردن سه رقم سه رقم اعداد به روشهای غیر استاندارد.
حالا با این دیدگاه، به سراغ حل مسئله میرویم…
این سناریو برای مواقعی کاربرد دارد که نیاز دارید ارزش مکانی اعداد را بررسی کنید (یکان، دهگان، صدگان و…) یا میخواهید الگوریتمهایی مثل “Luhn” (بررسی صحت کارت بانکی یا کد ملی) را پیادهسازی کنید که محاسباتشان از سمت راست شروع میشود.
در این مسئله، ما یک عدد در یک سلول داریم و میخواهیم ارقام آن را به صورت عمودی و معکوس (از آخرین رقم به اولین رقم) زیر هم لیست کنیم.
📝 سناریو: تحلیل ارزش مکانی ارقام (معکوس)
فرض کنید یک عدد طولانی (مثل شماره پیگیری یا مبلغ) در سلول A2 دارید. میخواهیم این عدد را طوری تفکیک کنیم که “یکان” در اولین سطر، “دهگان” در سطر دوم و… قرار گیرد.
جدول دادهها
| ردیف | A (عدد ورودی) | B (ارقام تفکیک شده از راست) | توضیح (ارزش مکانی) |
|---|---|---|---|
| 2 | 98765 | 5 | (یکان) |
| 3 | 6 | (دهگان) | |
| 4 | 7 | (صدگان) | |
| 5 | 8 | (هزارگان) | |
| 6 | 9 | (ده هزارگان) |
💡 راه حل و فرمول
در سلول B2 فرمول زیر را بنویسید و آن را به سمت پایین (Drag) بکشید:
=MID($A$2, LEN($A$2) - ROW(A1) + 1, 1)
🔍 تحلیل ریاضی فرمول
بیایید ببینیم چطور ترکیب LEN و ROW باعث حرکت معکوس (راست به چپ) میشود. فرض کنید عدد ما 98765 است (طول آن 5 رقم است).
-
LEN($A$2):-
طول کل رشته را محاسبه میکند.
-
مقدار ثابت: 5.
-
-
ROW(A1):-
این تابع نقش شمارنده را بازی میکند.
-
در سلول اول (B2)،
ROW(A1)برابر 1 است. -
وقتی فرمول را پایین میکشید (B3)، تبدیل به
ROW(A2)شده و برابر 2 میشود.
-
-
هسته مرکزی:
LEN - ROW + 1:-
این فرمول ریاضی موقعیت برش را معکوس میکند:
-
در سطر اول: $5 – 1 + 1 = 5$ (کاراکتر پنجم را بده -> 5)
-
در سطر دوم: $5 – 2 + 1 = 4$ (کاراکتر چهارم را بده -> 6)
-
در سطر سوم: $5 – 3 + 1 = 3$ (کاراکتر سوم را بده -> 7)
-
-
MID(..., ..., 1):-
حالا تابع MID بر اساس موقعیتی که در مرحله قبل محاسبه شد، دقیقاً یک کاراکتر را استخراج میکند.
-
⚠️ نکته برای جلوگیری از خطا
اگر فرمول را بیش از حد پایین بکشید (بیشتر از تعداد ارقام عدد)، با خطا مواجه میشوید. برای حرفهایتر شدن، میتوانید از تابع IFERROR یا یک شرط استفاده کنید که اگر شمارنده بیشتر از طول عدد شد، سلول را خالی بگذارد:
=IF(ROW(A1)>LEN($A$2), "", MID($A$2, LEN($A$2)-ROW(A1)+1, 1))
- سطح / ناشرEXCEL، مقاله تخصصی
- بازدید602