blocks|key|3097300|text|我们中那些从事专业开发工作表解决方案的人，有一些我们遵循的基本规则，并鼓励其他人不要破坏。其中之一是“永远不要将计算或比较将执行或评估的任何地方合并。”|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3097301|虽然你看到的可能是个小故障，但我不会这么说。合并后的单元格并不能真正摆脱你看不到的单元格。例如，您的单元格R27不“摆脱”单元格S27、T27和U27。它只是把它们保存在记忆中，大概是空的。但是请记住，在床单的“记忆”中，它不会“看到”一个网格。只有1和0。当网格是新的，没有单元格被合并时，工作表会“找到”每个单元格(用我所能给出的最好的类比)，按1和0计数，这些单元格非常小，而且非常紧凑。保持这种想法..。|3097302|还要注意的是，当许多人正在进行多个复杂的计算，并得到一个返回的答案时，他们会感到惊讶，这与他们在计算链更远的单元格中所期望的结果是不同的。这是因为工作表使用浮点小数点。例如，“一除以三”大约是0.333。但在现实中，这是远远不够的:+0.33333333333333333333。但这不可能永远持续下去，否则床单就跑不了了。所以在某个时候它会被截断。最终，这些额外的十进制加成将开始加起来，并将数字向上(或向下)小幅度增加。所有这些都是由运行其他一切的相同的1和0处理的。|3097303|回到合并的单元格。发现并准确地“读取”合并后的单元格越不准确，因为该过程相对于内存中的其他单元格位置。过了一段时间，工作表的“浮动小数点”特性开始失去跟踪，特别是(我已经观察到)，如果您使用的是与您使用的相同类型的相对公式引用(例如，您的许多叠加合并单元格引用了另一个合并单元格:+BB2+)，则尤其如此。因此(再一次类比)，Sheet试图“通过使用线索来猜测它在哪里。”也许它在合并的细胞Z-AA-AB-AB+23中找到了一些参考合并细胞BB2-BB3-BC2-BC3的正确发现；但在那次，你要求它添加合并的细胞R-S-T-U23和V-W-X-Y23，减去合并的细胞V-W-X-Y24+.在下面一组合并的细胞，Z-AA-AB-AA+24中，你仍然要求BB2-BB3-BC2-BC3参考，而不是V-W-X-Y24减法。等等..。|3097304|更重要的是，你已经改变了整个地方的行高。有些被设置为特定高度，而另一些则适合于数据。|3097305|你能拍到照片。|3097306|如果床单试图通过从单元格A1来回移动来寻找东西，但没有规律，有时，它只是把手举起来，然后说：“h-双曲棍球棒在哪里？”当您重新加载工作表或删除并替换公式时，它再次从A1开始，并在破碎的板子上播放滑道和梯子，并可能将一个“浮动的”微小的1或0移动到与上次不同的方式.再找到你想要的东西。|3097307|此外，还会得到一张56列的工作表，而您可能只需要12或15列。同样，您已经将行数扩展到了所需的范围之外。所以你的处理速度慢了很多。|3097308|短版：|offset|length|style|BOLD|3097309|虽然合并的单元可能看起来很好，但它们是计算的噩梦。自从电子表格被发明以来，我就一直在使用它们，实际上我从来不需要合并的单元格(虽然我偶尔会在纯粹美观的领域中使用它们)。|3097310|的解决方案：|3097311|1.)在没有合并单元格的情况下重新制作工作表。|3097312|-或者-|3097313|2.)尝试在计算中包含所有合并但隐藏的单元格。这至少给了床单一个更大的网可投。例如，在上面的示例中，您引用了以下公式：|3097314|=IF($BB$2<>"Rental","",R27-V27)|code-block|syntax|javascript|3097315|尝试(在可能的情况下)包括您合并的整个范围：|3097316|=IF($BB$2<>"Rental",+"",+SUM(R27:U27)+-+SUM(V27:Y27)+)|3097317|从技术上讲，您还可以在BB2-BB3-BC2-BC3合并中包括所有单元格：|3097318|=IF(AND($BB$2<>"Rental",+$BB$3<>"Rental",+$BC$2<>"Rental",+$BC$3<>"Rental"),+"",+SUM(R27:U27)+-+SUM(V27:Y27)+)|3097319|但是，尝试使用仅使用SUMmed合并范围的较短的路由，并查看它是否有效。如果是这样的话，就坚持下去。如果不是，请引用合并范围内的每个单元格。|3097320|很明显，你在这张纸上做了很多工作。所以我相信这不是你希望听到的简单答案。但我希望它能给你提供一些下一步的方向。|3097321|entityMap^0|0|0|0|0|0|0|0|0|0|3|0|0|0|6|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|1R|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1S|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1T|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1U|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1V|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1W|8|@$R|1X|S|1Y|T|U]]|9|@]|A|$]]|$1|V|3|W|5|6|7|1Z|8|@]|9|@]|A|$]]|$1|X|3|Y|5|6|7|20|8|@$R|21|S|22|T|U]]|9|@]|A|$]]|$1|Z|3|10|5|6|7|23|8|@]|9|@]|A|$]]|$1|11|3|12|5|6|7|24|8|@]|9|@]|A|$]]|$1|13|3|14|5|6|7|25|8|@]|9|@]|A|$]]|$1|15|3|16|5|17|7|26|8|@]|9|@]|A|$18|19]]|$1|1A|3|1B|5|6|7|27|8|@]|9|@]|A|$]]|$1|1C|3|1D|5|17|7|28|8|@]|9|@]|A|$18|19]]|$1|1E|3|1F|5|6|7|29|8|@]|9|@]|A|$]]|$1|1G|3|1H|5|17|7|2A|8|@]|9|@]|A|$18|19]]|$1|1I|3|1J|5|6|7|2B|8|@]|9|@]|A|$]]|$1|1K|3|1L|5|6|7|2C|8|@]|9|@]|A|$]]|$1|1M|3|-4|5|6|7|2D|8|@]|9|@]|A|$]]]|1N|$]]

Those of us who work professionally developing Sheets solutions have a few basic rules that we follow and encourage others not to break. Among them is &quot;Never merge cells anywhere that calculations or comparisons will be (or ever might be) performed or assessed.&quot;
While what you're seeing might seem to be a glitch, I wouldn't call it that. Merged cells don't really get rid of the cells you don't see. For instance, your cell R27 doesn't &quot;get rid of&quot; cells S27, T27 and U27. It just holds them in memory, presumably empty. But remember that, inside Sheets' &quot;memory,&quot; it doesn't &quot;see&quot; a grid. It's just 1's and 0's. When a grid is new and no cells are merged, Sheets &quot;finds&quot; each cell (using the best analogy I can give) counting over and down by 1's and 0's that are very small and tightly packed. Hold that thought...
Also note that many people are surprised when they have multiple complex calculations going on and get a returned answer that is off from what they expect in a cell further down the calculation chain. This is because Sheets uses floating decimal points. For instance, &quot;one divided by three&quot; is about 0.333. But in reality, that extends very far out: 0.33333333333333333333. But that can't go on forever or Sheets wouldn't be able to run. So at some point it gets truncated. Eventually, those extra decimal-extras will start to add up and bump numbers up (or down) by small degrees. All of this is handled by the same 1's and 0's that run everything else.
Back to merged cells. Finding and accurately &quot;reading&quot; merged cells gets less accurate the more of them you merge, because the process is relative to other cell locations in memory. After a while, the &quot;floating decimal point&quot; nature of Sheets starts to lose track, especially (I've observed) if you've got merged cells using the same sort of relative formula reference that you're using (e.g., a lot of your stacked merged cells are referencing yet another merged cell: BB2. So (again by analogy), Sheets is trying to &quot;guess where it is by using clues.&quot; Perhaps it found something correctly in merged cells Z-AA-AB-AC23 that referenced merged cells BB2-BB3-BC2-BC3; but that time, you asked it to add merged cells R-S-T-U23 and V-W-X-Y23 and subtract merged cells V-W-X-Y24 ... where in the following set of merged cells, Z-AA-AB-AC24, you still asked for the BB2-BB3-BC2-BC3 reference but not the V-W-X-Y24 subtraction. And so on...
To add to this, you've got row heights changed all over the place. Some are set to specific heights, while others are fit to data.
You get the picture.
If Sheets is trying to find things by moving over and down from cell A1, but there is no regularity, sometimes, it just throws it's hands up and says, &quot;Where the h-double-hockey-sticks am I anymore?&quot; When you then reload the sheet or delete and replace the formula, it starts out at A1 again and plays Chutes and Ladders on the broken board and might shift one &quot;floating&quot; teeny-weeny 1 or 0 a different way from last time... and find what you wanted it to find again.
You also-also wind up with a sheet that is 56 columns across, when you probably only needed it to be 12 or 15. Likewise, you've extended the number of rows beyond what it needed to be. So you're slowing down your processing by a lot.
THE SHORT VERSION:
As nice as merged cells might look, they are a computing nightmare. I've been working with spreadsheets since they were invented, and I've literally never had a need for merged cells (though I've occasionally used them in areas that are purely aesthetic).
THE SOLUTIONS:
1.) Remake the sheet without merged cells.
-or-
2.) Try encompassing all of the merged-but-hidden cells in your calculations. This at least gives Sheets a wider net to cast. For instance, in your example above, you reference this formula:
<pre><code>=IF($BB$2&lt;&gt;&quot;Rental&quot;,&quot;&quot;,R27-V27)
</code></pre>
Try (where possible) to include the whole range you merged:
<pre><code>=IF($BB$2&lt;&gt;&quot;Rental&quot;, &quot;&quot;, SUM(R27:U27) - SUM(V27:Y27) )
</code></pre>
Technically, you could also include all cells in the BB2-BB3-BC2-BC3 merge:
<pre><code>=IF(AND($BB$2&lt;&gt;&quot;Rental&quot;, $BB$3&lt;&gt;&quot;Rental&quot;, $BC$2&lt;&gt;&quot;Rental&quot;, $BC$3&lt;&gt;&quot;Rental&quot;), &quot;&quot;, SUM(R27:U27) - SUM(V27:Y27) )
</code></pre>
However, try the shorter route with just the SUMmed merged ranges and see if it holds up. If so, stick with that. If not, go with referencing every cell in ranges you merged.
You've clearly put a lot of work into setting up this sheet. So I'm sure this isn't the easy answer you were hoping to hear. But I hope it does give you some direction for next steps.

blocks|key|64623|text|对我来说，最简单的解决方案是锁定-引用合并单元格中最上面的单元格。不要将顶部公式中的引用引用为B2，引用为$B$2。这样，您在其他单元格中的公式将引用所需的相同值。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|64624|使用锁引用：|64625|64626|+$B$2|blockquote|64627|64628|entityMap^0|1B|2|1H|4|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|U|8|@]|D|@]|E|$]]|$1|I|3|J|5|K|7|V|8|@]|D|@]|E|$]]|$1|L|3|-4|5|6|7|W|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|N|$]]

For me, the simplest solution is to lock-reference the uppermost cell of the merged cells.
Instead of referencing in the top formula as <code>B2</code>, reference as <code>$B$2</code>. This way, your formulas in other cells will reference the same value as required.
USE LOCK-REFERENCING:
<blockquote>
$B$2
</blockquote>

So I have created an invoice spreadsheet in Google Sheets and have used merged cells extensively in order to create a cleaner look for the invoice.
Here is the problem that I am having: sometimes, my simple subtraction formula does not give me a correct result. The formula is supposed to subtract the Unsold balance (cell V27) from the Beginning Balance (cell R27).
Again, there is nothing wrong with the formula itself, as it works correctly 99% of the time, but every now and then, it just gives me an answer matching the Beginning Balance.
<a href="https://i.stack.imgur.com/aYxtn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/aYxtn.png" alt="enter image description here" /></a>
Also here is a link to a copy of the spreadsheet for anyone willing to take a look. <a href="https://docs.google.com/spreadsheets/d/1bf_QE-u36mo4AKyqg7Dk11gwoR0p5g0qveIazR83Xbk/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1bf_QE-u36mo4AKyqg7Dk11gwoR0p5g0qveIazR83Xbk/edit?usp=sharing</a>
Has anyone else ever had this situation happen to them? What could possibly be causing it, and more importantly, how can it be fixed to be more reliable?

Google Sheets Formula Sometimes does not work with Merged Cells

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

因此，我已经在Google中创建了一个发票电子表格，并广泛地使用了合并的单元格，以便为发票创建一个更干净的外观。这是我遇到的问题:有时，我的简单减法公式不能给我一个正确的结果。该公式应从初始余额(单元格V27)中减去未售出余额(单元R27)。同样，公式本身没有什么问题，因为它在99%的时间里正确工作，但它不时地给我一个...

问Google公式有时不适用于合并的单元格
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Google公式有时不适用于合并的单元格EN