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

فهرست مطالب (7 بخش)

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

در این آموزش، SUMIFS را با یک مثال کوچک بررسی می‌کنیم. این تابع مجموع یک محدوده را برای ردیف‌هایی محاسبه می‌کند که همه شرط‌های تعیین‌شده را دارند. نمونه، داده مالی واقعی نیست و هیچ نرخ یا قیمت روزی را نشان نمی‌دهد؛ هدف، آزمودن منطق فرمول است.

داده نمونه را پیش از پرامپت بسازید

فرض کنید ستون A شناسه، B شهر، C مبلغ فرضی و D وضعیت است. فقط ردیف‌های شهر تهران با وضعیت «تأیید» باید جمع شوند. پنج ردیف زیر را روی یک کپی آزمایشی وارد کنید؛ فایل اصلی کسب‌وکار را برای امتحان فرمول تغییر ندهید.

شناسه شهر مبلغ فرضی وضعیت
A01 تهران ۱۰۰ تأیید
A02 شیراز ۲۰۰ تأیید
A03 تهران ۳۰۰ رد
A04 تهران ۱۵۰ تأیید
A05 تهران منفی ۲۰ تأیید

نتیجه موردانتظار ۲۳۰ است: ۱۰۰ به‌اضافه ۱۵۰ منهای ۲۰. این جواب را قبل از دیدن خروجی مدل بنویسید. در اکسل، مبلغ منفی را به‌صورت عدد منفی واقعی وارد کنید، نه متن «منفی ۲۰»؛ نمایش واژه‌ای در جدول بالا فقط برای روشن‌بودن مثال است.

دستور را با ساختار ستون‌ها بدهید

می‌توانید بنویسید: «در شیت نمونه، C2:C6 مبلغ عددی، B2:B6 شهر و D2:D6 وضعیت است. فرمولی بده که فقط مبلغ ردیف‌های تهران و تأیید را جمع کند. دو شرط باید هم‌زمان برقرار باشند. توضیح بده هر محدوده چه نقشی دارد و جواب موردانتظار این داده را جدا حساب کن.»

فرمول متناسب با این ساختار چنین است:

=SUMIFS(C2:C6,B2:B6,"تهران",D2:D6,"تأیید")

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

فقط عدد نهایی را کنترل نکنید

یک ردیف تهرانیِ تأییدشده را به شهر دیگر تغییر دهید و ببینید جمع به اندازه همان مبلغ کم می‌شود یا نه. سپس یک وضعیت رد را به تأیید تغییر دهید و اثرش را بررسی کنید. ردیف منفی نیز باید طبق تعریف شما در جمع وارد شود؛ اگر هدف حذف برگشت‌هاست، این شرط باید صریح و جدا تعریف شود.

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

خطاهای فارسی و عددی را پیدا کنید

فاصله اضافی، تفاوت «ی» و «ي» یا «ک» و «ك» و املای متفاوت «تأیید» می‌توانند مقایسه متن را تغییر دهند. در ستون مبلغ نیز عدد ذخیره‌شده به‌صورت متن با عدد واقعی تفاوت دارد. قبل از دست‌کاری فرمول، نوع و شکل داده را بررسی کنید؛ بزرگ‌کردن پرامپت، داده ناسازگار را خودکار اصلاح نمی‌کند.

برای پاک‌سازی، یک ستون کمکی روی کپی بسازید و روش تبدیل را ثبت کنید. اگر مقادیر، شناسه‌اند نه مبلغ، تبدیل خودکار به عدد می‌تواند صفر اول را حذف کند. همه ستون‌ها را با یک دستور عمومی «عددی کن» تغییر ندهید؛ نوع داده باید با معنای ستون هماهنگ باشد.

انتقال به فایل اصلی

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

چرا SUMIF را جای SUMIFS نگذاریم؟

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

آیا جواب مدل را می‌توان مستقیم روی فایل حساس اجرا کرد؟

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

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

برای ادامه یادگیری

منابع اصلی

حامد لطف

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

پروژه دارم ↖
SPONSORED · تبلیغ

آموزش رایگان + سرمایه ترید

آموزش کاملا رایگان سیستم محور فارکس , کریپتو و بورس ایران همراه با سرمایه ترید

شروع رایگان ↖

دیدگاه‌ها

۰ دیدگاه

هنوز دیدگاهی ثبت نشده؛ اولین نفر باشید.

دیدگاه خود را بنویسید

نظرات پس از بررسی منتشر می‌شوند؛ ایمیل شما هیچ‌جایی نمایش داده نمی‌شود.
ادامهٔ مسیر

بیشتر بخوانید.

همهٔ «ابزارها» ↖
ابزارها

هر آنچه باید دربارهٔ فیگما بدانید (پرسش‌های پرتکرار)

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

۲۳ شهریور۲ دقیقه مطالعه