文章 · 存档
公式总写不出来,卡住你的是业务逻辑那一层
做财务久了都变成了excel高手,可是Excel明明学了很多年,SUMIFS、XLOOKUP、IF、INDEX+MATCH、OFFSET、EOMONTH这些函数,单个拿出来都认识,真轮到自己面对一张复杂表格时,手还是会停在键盘上想了半天,函数名在脑子里打转,可公式就是写不顺,越写越乱,嵌套两层还行,
最典型的场景就是条件汇总。比如你要做销售提成,规则看上去像一句话,真落进表里就会变成很多层:地区不同,品类不同,回款状态不同,是否跨期不同,折让单要不要扣,退货要不要冲回,月底前到账和月底后到账口径还不一样。很多人一上来就急着写公式,于是脑子里同时塞进业务规则、字段位置、函数结构和括号层级,越写越堵。
更高效的做法是先让AI把业务规则拆开,比如先要求它把这套制度翻译成“如果客户地区为华东且品类为A且回款状态为已到账,则提成基数取含税收入减退货;如果跨期到账,则进入次月口径;如果有折让,则按折让后金额计算”这样的步骤树,再让它判断哪些条件适合用SUMIFS,哪些地方需要IF包裹,哪些口径要单独设辅助列。一旦逻辑被拆成顺序明确的判断链,公式反而没有那么吓人。先把人的思路说清,再把公式交给机器协助生成。
另一个更让人头大的场景是多表联动校验加动态期间计算。财务人都知道这类活最耗人。销售台账一张表,回款台账一张表,开票记录一张表,预算执行又是一张表,口径还要按“本月”“本季度”“本年截至”“滚动十二个月”来切。人一旦直接跳进公式层,脑子会很快被表名、字段名、期间边界和匹配条件搅成一锅粥。这种时候AI最有价值的地方不是替你凭空写一个万能公式,而是先帮你把任务拆成三个动作:先定义主键和匹配关系,再定义校验规则,最后定义时间边界。比如它可以先帮你写出这样一层业务语言:如果订单号与客户编码同时匹配,则去回款表取金额;如果匹配到多条,则按最晚回款日取值;如果统计口径为本季度,则起始日期取本季度第一天,结束日期取本月月末;如果统计口径为滚动十二个月,则以当前月份向前回溯十二个完整月。到了这一步公式生成已经不再像“猜答案”,更像是在把已经说清楚的决策树翻译成Excel语法。对财务人来说,这至少说明一件事:复杂问题的关键关口,正在从手搓公式往拆清规则移动。
所以真正有用的AI使用法从来都很像一个安静的三步走。先拆逻辑,把一句模糊的业务要求,拆成能落在表格字段上的判断顺序;再生成公式,让模型按拆好的逻辑写出第一版;最后做校验,让它反过来检查边界条件、空值、重复值、跨期问题和异常样本。这个闭环很重要,因为公式写出来只是开始,校验才决定它能不能进真实工作。行业里真正成熟的方向已经不是把AI当成一个背函数的高手,而是把它放进业务逻辑翻译、公式草拟和结果复核这整条链路里。
很多财务人之所以长期卡在公式上,心里背着一种误会,总觉得自己再多背几个函数,再多记几个套路,问题总会过去。可现实里真正让人卡壳的常常是那句业务话没有拆明白,那几个条件的先后顺序没有排出来,那张表到底谁依赖谁没有理顺。函数当然重要,熟练当然有价值,可它更像词汇量,真正决定一句话能不能说通的,还是语法和结构。AI把这件事照亮了,它让越来越多人第一次意识到,公式能力深处藏着的是逻辑能力,表格问题深处藏着的是业务翻译能力。
我写这本新书的时候一直不想把它写成一本只教人抄提示词的工具书。我更想做的是把很多人每天在表格里吃过的亏、熬过的夜、卡过的壳,拆成真正可复制的方法。公式写不出来并不意味着你学不会。很多时候只是那层逻辑还没有被人帮你拆开。我的这本新书把逻辑拆解当成AI使用的底层能力,想解决的正是这个问题:让财务人从此少在公式前发怵,少在复杂业务里打转,把原本堵在脑子里的那团东西,一步一步拆开,一步一步落地,最后再把时间和精力,留给更值得自己出手的分析和判断。
What is really stopping you is the business logic part
You still can't write the formula. What is really stopping you is the business logic part
After doing finance work for a long time, people all become Excel experts. But even after learning Excel for many years, and knowing functions like SUMIFS, XLOOKUP, IF, INDEX+MATCH, OFFSET, and EOMONTH one by one, when it is really time to face a complex table by yourself, your hands still stop on the keyboard and you think for a long time. The function names spin around in your head, but the formula just does not come out smoothly. The more you write, the more messy it gets. Two levels of nesting may still be okay, but by the third or fourth level, you start to lose confidence. In fact, no matter how strong your Excel formula skills are, the most important thing is still breaking down the business logic. As long as you really understand the business logic, you can tell AI in plain language, and even if you do not understand one single function, you can still make a very good formula.
The most typical situation is conditional summary. For example, if you need to calculate sales commission, the rule may look like just one sentence, but when it goes into a table, it turns into many layers: different regions, different product types, different payment status, whether it crosses into another period, whether discount notes should be deducted, whether returned goods should be reversed, and even the rule is different for money received before month-end and after month-end. Many people rush to write the formula right away, so their heads are filled at the same time with business rules, field positions, function structure, and bracket levels, and the more they write, the more stuck they get.
A more efficient way is to first let AI break down the business rules. For example, first ask it to turn the rules into a step tree like this: “If the customer region is East China and the product type is A and the payment status is received, then the commission base is taxed revenue minus returns; if the payment comes in a later period, then move it into next month's rule; if there is a discount, then calculate based on the amount after discount.” Then let it decide which conditions should use SUMIFS, which places need IF around them, and which rules should use helper columns. Once the logic is broken into a clear order of decisions, the formula is not so scary anymore. First make the human thinking clear, then let the machine help create the formula.
Another even more stressful situation is checking across many tables plus calculating changing time periods. Finance people all know this kind of work takes the most energy. One table for sales records, one table for payment records, one table for invoice records, and another table for budget use, and the rules also need to switch between “this month,” “this quarter,” “year to date,” and “rolling twelve months.” Once a person jumps straight into the formula part, the mind quickly turns into a big mess of table names, field names, time limits, and matching conditions. At this time, the most valuable thing AI can do is not to magically write one perfect formula for you, but to first break the task into three actions: first define the main key and matching relationship, then define the checking rules, and last define the time limits. For example, it can first help you write a business rule like this: if the order number and customer code both match, then go to the payment table and get the amount; if there are many matches, then use the latest payment date; if the report rule is this quarter, then the start date is the first day of this quarter and the end date is the last day of this month; if the report rule is rolling twelve months, then go back twelve full months from the current month. By this step, making the formula no longer feels like “guessing the answer.” It feels more like turning a clear decision tree into Excel language. For finance people, this shows at least one thing: the key point in solving hard problems is moving from hand-writing formulas to clearly breaking down the rules.
So the truly useful way to use AI is always like a quiet three-step path. First break down the logic, turning one unclear business request into a decision order that can fit into table fields; then create the formula, letting the model write the first version based on the broken-down logic; and last do checking, letting it look back and check edge cases, blank values, repeated values, cross-period problems, and unusual samples. This full loop is very important, because writing the formula is only the start, and checking decides whether it can really be used in real work. The truly mature direction in the industry is no longer to treat AI as a smart person who remembers functions, but to put it into the whole chain of translating business logic, drafting formulas, and checking results.
Many finance people stay stuck on formulas for a long time because they carry a misunderstanding in their hearts. They always feel that if they memorize a few more functions and remember a few more tricks, the problem will go away. But in real life, what really makes people get stuck is often that the business sentence was not broken down clearly, the order of the conditions was not arranged clearly, and the relationship between the tables was not sorted out. Of course functions are important, and being skilled is valuable, but they are more like vocabulary. What really decides whether a sentence can be said clearly is still grammar and structure. AI has helped shine light on this. It has made more and more people realize for the first time that under formula ability there is logic ability, and under table problems there is the ability to translate business rules.
When I wrote this new book, I never wanted it to become just a tool book that only teaches people to copy prompts. What I really want to do is turn the losses, late nights, and stuck moments that so many people have had in spreadsheets every day into methods that can truly be copied and used again. Not being able to write a formula does not mean you cannot learn it. Many times, it only means that logic layer has not yet been opened up for you. My new book treats logic breakdown as the basic ability for using AI, and this is exactly the problem it wants to solve: helping finance people feel less scared in front of formulas, spend less time going in circles inside complex business work, slowly break apart the big messy thing stuck in their minds, step by step put it into real work, and in the end leave their time and energy for the analysis and judgment that are more worth doing by themselves.