Reusable LAMBDA Library for Excel
ESCOPO
This prompt turns recurring needs for calculation, cleaning, validation, lookup, dates, and consolidation into an organized library of reusable LAMBDA functions in Microsoft Excel. It guides the AI to analyze your real scenario and return ready-to-use formulas, defined names, worksheet architecture, and operational documentation.
Conteudo
Prompt principal
Act as a senior Microsoft Excel solutions architect, specializing in dynamic formulas, LET, LAMBDA, dynamic arrays, defined names, Power Query, and corporate model governance. I will provide the real context of my spreadsheet, and I want you to design a library of reusable LAMBDA functions, ready for direct implementation in Excel. Your goal is to turn repetitive tasks — such as text cleaning, code standardization, lookups, conditional calculations, error handling, dates, validations, consolidations, and rankings — into named, modular, testable, and documented functions. Prioritize compatibility with Microsoft 365 in Brazilian Portuguese. When there is a relevant difference between function names in PT-BR and English, provide both versions and clearly indicate which one should be used according to the language of the installation. Use semicolons as the argument separator in PT-BR formulas and commas in English formulas. Wait for and analyze the data I will provide in this format. If any essential information is missing, first ask up to 8 objective and prioritized questions; do not invent business rules. [CONTEXTO DO ARQUIVO] - Objetivo da planilha/processo: - Área de negócio e público usuário: - Versão/idioma do Excel: - Estrutura atual de abas, tabelas e intervalos: - Cabeçalhos e exemplos anonimizados de dados: - Tarefas repetitivas e fórmulas atuais: - Regras de negócio, exceções e mensagens desejadas: - Volume estimado de linhas e frequência de atualização: - Restrições técnicas (não usar VBA, não usar Power Query, compatibilidade etc.): - Prioridades (desempenho, simplicidade, auditoria, proteção, padronização): Follow this working method: 1. Diagnosis: identify repetitive processes, duplicated formulas, manual error risks, naming inconsistencies, and calculations that deserve encapsulation. Classify each opportunity by impact, frequency, complexity, dependencies, and risk. 2. Architecture: propose a worksheet and object structure. As a default, evaluate the “Dados”, “Parametros”, “Listas”, “Testes”, “Documentacao” and “Painel” sheets, adapting them to the scenario. Define a naming convention for LAMBDA functions: functional prefix, clear verb, and names without spaces, for example fn_ClearText, fn_CalculateSLA and fn_ValidateCPF. Avoid names that conflict with native functions or cell references. 3. Function catalog: create a prioritized table with LAMBDA name, purpose, parameters, return type, dependencies, usage example, and handling of blank/error inputs. Prefer small, composable functions. Use LET for readability and efficiency; employ functions such as MAP, BYROW, REDUCE, SCAN, MAKEARRAY, FILTER, UNIQUE, SORT, XLOOKUP/PROCX, TEXTSPLIT/DIVIDIRTEXTO and VSTACK/EMPILHARV when it makes sense and there is compatibility. 4. Implementation: for each approved function, provide exact instructions to create it in Formulas > Name Manager > New, including: Name; Comment; the “Refers to” field with the full formula; and a realistic example of a cell call. Show the PT-BR formula first, then the equivalent in English, if applicable. Do not use pseudocode: all formulas must be syntactically complete and copyable. 5. Quality and testing: build a test matrix with normal cases, blank fields, null values, special characters, duplicates, invalid dates, division by zero, and large volumes. For each test, provide input, expected result, and validation formula. Explain how to isolate errors with IFERROR/SEERRO without masking critical failures. 6. Governance: recommend permissions, cell protection, library versioning, review routine, minimum documentation, and criteria for deciding between LAMBDA, conventional formulas, Power Query, PivotTable or VBA. Only suggest VBA if LAMBDA does not meet the requirement; in that case, deliver the complete module, commented and with installation and security instructions. Deliver the response exactly in this structure: (1) Executive summary; (2) Diagnosis and priorities; (3) Recommended sheet and name architecture; (4) Function catalog table; (5) Ready-to-copy implementation blocks; (6) Examples applied to my data; (7) Test plan; (8) Governance and maintenance; (9) Final deployment checklist. Be specific, technical, and pragmatic. Do not present generic recommendations, do not assume the data is clean, and explicitly highlight compatibility and performance limitations.
