سازنده فرمول اکسل: یک محاسبه به زبان ساده را به فرمولی کاربردی تبدیل کنید
چرا این پرامپت اهمیت دارد
Hand-built spreadsheet formulas for board and finance decks fail quietly far more often than people expect. A SUM that drops a text-formatted cell, an AVERAGE that includes a header row, or a blank cell silently treated as zero can shift a reported KPI enough to trigger a strategic conversation based on a wrong number. Once a bad figure has been presented to finance or the board, correcting it afterward costs far more in credibility than the two minutes it takes to get the formula right and understand exactly why it works before you present it.
ما از آن برای چه استفاده میکنیم
You're a sales operations analyst at a mid-size SaaS company, working in a Google Sheets pipeline tracker with roughly 3,000 rows. Finance needs the weighted average discount percentage across all deals marked "Closed Won" in Q3, weighted by deal value, and a few rows have a blank discount field because a rep forgot to fill it in. The number is due in the quarterly business review deck in two hours, and you need a formula that handles the blanks correctly instead of silently including them as zero and skewing the average down.
پرامپت
Act as a senior data analyst who has spent years cleaning up broken finance and sales spreadsheets and knows exactly how formulas silently fail. Context: I need a formula for the following calculation, described in plain English: [DESCRIBE THE CALCULATION YOU NEED, e.g. "weighted average discount percentage for all deals marked Closed Won, weighted by deal value"] Tool: [SPECIFY Excel (and version, e.g. Excel 365) OR Google Sheets] Relevant data: [DESCRIBE YOUR COLUMNS/RANGES OR PASTE A SAMPLE ROW, e.g. "Column G = deal stage, Column H = close date, Column I = discount %, Column J = deal value, data starts row 2, ends row 3000"] Edge cases to handle: [LIST ANYTHING THAT COULD BREAK A NAIVE FORMULA, e.g. "some rows in the discount column are blank," "some deal values are stored as text," "I only want deals closed within a specific date range"] Task: 1. Write the working formula, ready to copy and paste directly into the cell. 2. Break down what each part of the formula does, in plain English, step by step. 3. Flag anything in my data description that is likely to break this formula (mismatched data types, absolute vs. relative references, off-by-one range errors). 4. If the tool I specified has a simpler function available in a recent version that could replace an older-style formula, mention it as an alternative. 5. If there's a meaningful syntax difference between Excel and Google Sheets for this formula, note it even if I only asked about one. Constraints: Avoid volatile functions (OFFSET, INDIRECT, NOW, TODAY) unless the calculation specifically requires them. The formula must be copy-paste-ready with no placeholder text left inside it — resolve every range and condition using the data description I gave you. Assume I am an intermediate spreadsheet user: I understand basic functions like SUM and IF but don't regularly write nested array formulas. Output Format: - The Formula (in a code block, copy-paste ready) - Step-by-Step Breakdown (numbered, plain English) - What Could Break It (bulleted) - Excel vs. Google Sheets Note (only if relevant) - Simpler Alternative (only if one exists)
نتیجه
The Formula (Google Sheets, weighted average discount for Closed Won deals in Q3, ignoring blank discount rows):
=SUMPRODUCT((G2:G3000="Closed Won")*(H2:H3000>=DATE(2026,7,1))*(H2:H3000<=DATE(2026,9,30))*(I2:I3000<>"")*I2:I3000*J2:J3000)/SUMPRODUCT((G2:G3000="Closed Won")*(H2:H3000>=DATE(2026,7,1))*(H2:H3000<=DATE(2026,9,30))*(I2:I3000<>"")*J2:J3000)Step-by-Step Breakdown
- The formula is built from two SUMPRODUCT calls: the top one calculates the sum of (discount × deal value) for qualifying rows, and the bottom one calculates the sum of deal value alone for the same rows. Dividing the first by the second gives a value-weighted average rather than a simple average.
- Each condition inside the parentheses — (G2:G3000="Closed Won"), the date range check, and (I2:I3000<>"") — evaluates to TRUE (1) or FALSE (0) for every row. Multiplying these conditions together means a row only contributes to the total if ALL conditions are true simultaneously.
- The (I2:I3000<>"") condition is what solves your blank-discount problem: any row where the discount cell is empty evaluates to FALSE, zeroing out that row's contribution entirely instead of treating a blank as a 0% discount, which would have dragged your average down incorrectly.
- The date range conditions (H2:H3000>=DATE(2026,7,1)) and (H2:H3000<=DATE(2026,9,30)) restrict the calculation to close dates within Q3 2026 specifically.
What Could Break It
- If the discount column (I) is formatted as text instead of a number (common when values are pasted from another system with a % sign as text), SUMPRODUCT will treat those cells as zero rather than throwing an error — check for left-aligned numbers in that column, which usually signals text formatting.
- If deal value (Column J) contains any blank or text cells within qualifying rows, the multiplication will silently zero out that row's contribution to both sums, which is usually fine but worth confirming isn't hiding missing data.
- The range G2:G3000 assumes your data never exceeds row 3000 — if new deals get added below that row, this formula will silently exclude them. Consider converting your range to a Table (Ctrl+T in Excel, or an equivalent structured range in Sheets) so the formula auto-expands.
Excel vs. Google Sheets Note: This exact formula works identically in Excel 365 and Google Sheets — SUMPRODUCT syntax is consistent across both. In older Excel versions (pre-2007), you may need to enter it as an array formula with Ctrl+Shift+Enter, but modern Excel and Sheets both evaluate it as a normal formula.
Simpler Alternative: If you only needed a straight sum-based condition without the weighting, SUMIFS would be simpler and faster to read — but because this calculation requires weighting by deal value, SUMPRODUCT is the correct tool here rather than a simplification.
بیشتر افرادی که به یک فرمول صفحهگسترده نیاز دارند در واقع نمیخواهند نحو صفحهگسترده را یاد بگیرند — آنها عدد درست را در سلول میخواهند، توضیحی که بتوانند به آن اعتماد کنند، و اطمینان از اینکه دفعه بعد که کسی یک ردیف اضافه کند بهطور بیصدا خراب نمیشود. این پرامپت دقیقاً برای ارائه همین موضوع طراحی شده، و ساختار آن عمداً حول نقاطی طراحی شده که درخواستهای فرمول معمولاً در آنها با شکست مواجه میشوند.
چرا این پرامپت به این شکل ساختاریافته است
چارچوببندی نقش — یک تحلیلگر ارشد داده که سالها صرف تعمیر صفحاتگسترده مالی و فروش خراب کرده — اهمیت دارد چون مدل را به سمت نوشتن فرمول تدافعی به جای کوتاهترین پاسخ فنی صحیح هدایت میکند. مدلی که به سادگی از او خواسته میشود یک فرمول برای X بنویس اغلب چیزی برمیگرداند که روی دادههای تمیز کار میکند و به محض ظاهر شدن یک سلول خالی یا عددی با قالب متنی خراب میشود. چارچوببندی مدل به عنوان کسی که شخصاً این خرابیها را رفع اشکال کرده باعث میشود آنها را فعالانه علامتگذاری کند به جای اینکه منتظر پرسیده شدن بماند.
بخش زمینه بهطور صریح توضیح محاسبه، ابزار (Excel یا Sheets)، چیدمان داده و موارد حاشیهای را به چهار ورودی مجزا به جای یک پاراگراف تقسیم میکند. این مهم است چون درخواستهای فرمول اغلب زمانی شکست میخورند که یکی از این موارد وجود نداشته باشد — کسی محاسبه را به وضوح توصیف میکند اما فراموش میکند یک مورد حاشیهای سلول خالی را ذکر کند، و فرمولی دریافت میکند که از نظر تئوری درست اما روی دادههای واقعی او اشتباه است.
فهرست پنجبخشی وظایف عمداً با درخواست یک جایگزین سادهتر و یک یادداشت نحوی بینابزاری پایان مییابد. تحلیلگران اغلب نمیدانند که تابعی که استفاده میکنند در یک نسخه اخیر با چیزی تمیزتر جایگزین شده، یا اینکه فرمولی که قرار است در Google Sheets پیست کنند از نحو اختصاصی Excel استفاده میکند. درخواست هر دو از قبل این را قبل از تبدیل شدن به یک خطای کپی-پیست که وسط ارائه کشف میشود، شناسایی میکند.
چگونه آن را تطبیق دهیم
برای یک محاسبه بسیار سادهتر — یک SUM مستقیم یا COUNTIF بدون پیچیدگی وزندهی یا مدیریت خالی — مدل بهدرستی یک تابع کوتاه برمیگرداند به جای ساختن پیچیدگی غیرضروری با SUMPRODUCT یا فرمولهای آرایهای. این ساختار هم به سمت پایین و هم به سمت بالا مقیاسپذیر است.
برای محاسبات تکراری که تیم شما در هر چرخه گزارشدهی انجام میدهد، ارزش دارد فرمول نهایی را همراه با توضیح گامبهگام آن جایی ذخیره کنید که کل تیم شما بتواند به آن مراجعه کند — این کار یک پرامپت یکباره را به دانش سازمانی تبدیل میکند که حتی وقتی تحلیلگر اصلی به پروژه دیگری منتقل میشود باقی میماند.