Generate an Excel formula from a requirement

작성자: AILesson4 분 소요테스트::ChatGPT검토일: 2026-08-28

빠른 답변

Turn a calculation need and real column layout into a formula you can verify. 제공할 내용: Calculation requirement, Columns or cells, Example rows and expected results. 예상 결과: A copy-ready formula, explanation, assumptions, and test cases.

맥락 추가

텍스트는 이 브라우저에 유지됩니다. AILesson Prompts는 이를 모델이나 서버로 보내지 않습니다.

Calculation requirementColumns or cellsExample rows and expected results

프롬프트

채워지지 않은 필드는 플레이스홀더로 표시되므로 프롬프트를 복사하고 편집할 수 있습니다

Create an Excel formula for this requirement:
[requirement]

Workbook layout:
[layout]

Example rows and expected results:
[examples]

Use the stated headers or cell references exactly. Do not invent missing columns. If the requirement is ambiguous, list the ambiguity before proposing a formula. Return:
1. Formula for the first data row
2. Plain-language explanation of each part
3. Assumptions about blanks, errors, dates, text, and regional separators
4. Three test cases with expected results
5. A simpler alternative if one exists

Prefer standard Excel functions and do not use volatile functions unless they are necessary.

Playground에서 사용해 보기

기본적으로 비공개프롬프트 구성은 브라우저에서 로컬로 이루어집니다. 조직에서 허용하지 않는 한 기밀 정보를 AI 서비스에 입력하지 마세요.

입력에서 결과까지

적용 예시

구체적인 맥락이 이 레시피를 바로 사용할 수 있는 결과로 바꾸는 방법을 확인하세요

실제 입력

Calculation requirement: In column E, calculate the number of calendar days an order was open. Use the closed date when present; otherwise use 30 June 2026. Leave the result blank when the created date is missing.
Columns or cells: A: Order ID (text); C: Created Date (Excel date); D: Closed Date (Excel date or blank); E: Days Open. Data starts on row 2.
Example rows and expected results: C2=1 Jun 2026, D2=5 Jun 2026 => 4; C3=20 Jun 2026, D3 blank => 10; C4 blank, D4 blank => blank.

예시 출력

Formula for E2 =IF(C2="","",IF(D2="",DATE(2026,6,30)-C2,D2-C2))
How it works The outer IF returns an empty string when C2 has no created date. The inner IF uses 30 June 2026 when D2 is blank; otherwise it subtracts the created date from the closed date. Excel stores dates as serial numbers, so subtraction returns calendar days.
Assumptions C and D contain real Excel dates, not text. A same-day close returns 0. A closed date before creation produces a negative number and should be investigated rather than hidden. The formula uses commas; installations using semicolons must replace the separators.
Tests 1 Jun to 5 Jun → 4; 20 Jun with no close → 10; missing created date → blank. Add a same-day case to confirm that 0 is intended.
Alternative There is no meaningfully simpler standard formula that preserves all three rules.

효과가 있는 이유

1
Real headers and expected results constrain the formula to the actual workbook.
2
Explicit edge cases expose blank and type-handling mistakes before the formula is filled down.

결과 확인

Does the formula reference only supplied cells or headers?
Do the test cases cover blanks and a boundary value?
Does the result match a manual calculation for at least two rows?

안심하고 사용하세요

자주 묻는 질문

이 레시피를 언제 사용해야 하는지, 무엇을 제공해야 하는지, 그리고 어떤 부분에서 사람의 검토가 여전히 중요한지에 대한 실용적인 답변

What should I prepare before using “Generate an Excel formula from a requirement”?

For “Generate an Excel formula from a requirement,” prepare Calculation requirement, Columns or cells, and Example rows and expected results. Replace placeholders only with information you can verify. If a detail is unknown, preserve that uncertainty explicitly instead of asking the model to infer it.

When is the “Generate an Excel formula from a requirement” result not ready to use?

The result is not ready if it does not yet deliver the stated outcome—A copy-ready formula, explanation, assumptions, and test cases—from the supplied evidence, or if it relies on unresolved assumptions, missing approvals, or invented details. Use the checks as release gates: revise the source inputs or assign a named, authorized reviewer instead of polishing an unsupported output.

Which AI tools have recorded tests for “Generate an Excel formula from a requirement”?

The published test record for “Generate an Excel formula from a requirement” lists ChatGPT as of 2026-08-28. This confirms recorded runs, not guaranteed compatibility or identical results in later product versions. For another tool or version, keep every constraint visible and repeat the result checks before use.

더 많은 탐색 방법