⚙️پردازش دادههای ناخالص (Dirty Data)
در دنیای واقعی، دادههایی که از نرمافزارهای حسابداری یا سیستمهای تحت وب خروجی گرفته میشوند، همیشه تمیز و آمادهی محاسبه نیستند. یکی از مشکلات رایج، «اعداد ذخیره شده با فرمت متنی» است که با کاراکترهای اضافه همراه شدهاند.
تابع MAX به تنهایی نمیتواند روی متن کار کند. اگر سلولی حاوی عبارت "14029999-OK" باشد، تابع MAX آن را نادیده میگیرد.
این فرمول سه مرحله عملیاتی را در کسری از ثانیه روی مجموعهای از سلولها انجام میدهد:
-
برش (Slicing): قسمت مورد نظر (عدد) را از متن جدا میکند (
LEFT). -
تغییر ماهیت (Casting): متن جدا شده را که هنوز ماهیت نوشتاری دارد، به ماهیت عددی و قابل محاسبه تبدیل میکند (
VALUE). -
تجمیع (Aggregation): حالا که دادهها عدد خالص شدند، بزرگترین آنها را پیدا میکند (
MAX).
این فرمول در اکسلهای قدیمی (قبل از 2021) یک فرمول آرایهای محسوب میشود (Array Formula).
📝 صورت مسئله: پیدا کردن آخرین شماره فاکتور از متن ترکیبی
فرض کنید سیستم فروش شرکت شما خروجی اکسلی میدهد که در آن شماره فاکتور (۱۰ رقمی) و نام دپارتمان در یک سلول چسبیده به هم نوشته شدهاند. شما میخواهید بدانید آخرین (بزرگترین) شماره فاکتور صادر شده چند است تا فاکتور بعدی را صادر کنید.
چالش
دادهها به صورت متن هستند و نمیتوانید مستقیماً آنها را سورت یا مکس بگیرید.
جدول دادهها (محدوده B3 تا B9)
| ردیف | B (کد تراکنش سیستم) | توضیحات (آنچه در ذهن ماست) |
|---|---|---|
| 3 | 1402010050-Sales | فاکتور شماره 1402010050 |
| 4 | 1402010051-HR | فاکتور شماره 1402010051 |
| 5 | 1402010049-IT | فاکتور شماره 1402010049 |
| 6 | 1402010055-MKT | فاکتور شماره 1402010055 |
| 7 | 1402010052-Sales | فاکتور شماره 1402010052 |
| 8 | 1402010053-Admin | فاکتور شماره 1402010053 |
| 9 | 1402010054-Sales | فاکتور شماره 1402010054 |
💡 راه حل و تحلیل فرمول
شما فرمول زیر را در سلول نتیجه وارد میکنید:
=MAX(VALUE(LEFT(B3:B9, 10)))
نتیجه خروجی: 1402010055
تحلیل گامبهگام عملیات:
-
LEFT(B3:B9, 10):
اکسل ابتدا به سراغ تکتک سلولهای بازه میرود و ۱۰ کاراکتر اول سمت چپ را جدا میکند.-
نتیجه موقت (آرایه متنی):
{"1402010050", "1402010051", ..., "1402010055", ...} -
نکته: اینها هنوز متن هستند (داخل کوتیشن) و
MAXروی اینها کار نمیکند.
-
-
VALUE(...):
این تابع آرایه متنی بالا را میگیرد و به اعداد واقعی ریاضی تبدیل میکند.-
نتیجه موقت (آرایه عددی):
{1402010050, 1402010051, ..., 1402010055, ...}
-
-
MAX(...):
حالا تابع MAX بین این اعداد میگردد و بزرگترین مقدار را برمیگرداند که همان 1402010055 (مربوط به ردیف 6) است.
⚠️ نکته مهم اجرایی
اگر از اکسل نسخه 2019 یا قدیمیتر استفاده میکنید، برای اینکه این فرمول کار کند باید بعد از نوشتن آن، به جای Enter، کلیدهای Ctrl + Shift + Enter را بزنید تا اکسل بفهمد با یک آرایه طرف است (فرمول داخل { } قرار میگیرد). در اکسل 2021 و 365 زدن Enter کافی است.
- سطح / ناشرEXCEL، مقاله تخصصی
- بازدید852