Excel复制时会混淆我的公式，我该如何阻止我改变一个部分而不是另一个？ [英] Excel messes with my formula when copied, how can I stop I stop it changing one part but not the other?

问题描述

所以我正在使用一个正在更新的旧数据库系统，以前它运行在许多不同的文件中，一个是电子邮件，一个是数字，你会得到这个想法。

So I'm working with an old database system that is being updated, previously it ran across many different files, one with emails, one with numbers, you get the idea.

无论如何，在尝试编译到一个文件时，我遇到一个错误，有些人没有数字或电子邮件等，这意味着我不能只复制旧数据（PersonID 5的手机将在PersonID 4的空间）

Anyway, in trying to compile in to one file, I've hit a snag, some people don't have a number or an email etc, this means I can't just copy the old data over (PersonID 5's phone would be in PersonID 4's space)

所以我做的是使用Vlookup公式，有一些麻烦，今天早上我意识到了什么问题。 p>

So what I did was use a Vlookup Formula, had some troubles with it and this morning I realised what the problem was.

=VLOOKUP(A2,'PhoneType - Email'!B1:V29190,3,FALSE)

我会快速分解一下，A是PersonID的列，电子邮件是电子邮件所在的工作表，B1：V29190应该选择整个表，3是PersonID在该工作表中的列。

I'll break this up quick, A is the column with the PersonID in, email is the worksheet the emails are in, B1:V29190 should be selecting the whole table and 3 is the column that PersonID is in in that worksheet.

基本上，我需要A改变（A2，A3，A4）符合这行它在我需要B1：V29190保持常态nt当前Excel尝试聪明，并使用每个副本粘贴进度。

Basically, I need A to change (A2, A3, A4) in line with the row it's in BUT I need B1:V29190 to stay constant as currently Excel tries to be clever and progresses it with each copy paste.

我该怎么做？

非常感谢，

icey

推荐答案

strong>绝对和相对单元格引用。 $ 可以转换绝对的行或列，以便在填写或复制到新位置时不会更改。您正在填充行，因此您只需要将行号锁定为绝对值。如果将光标放在单元格地址中，并点击 F4 ，您将看到第一个组合。继续点击 F4 查看其他组合。

The names are Absolute and Relative cell references. The $ turns either a row or column absolute so they do not change when filled or copied to a new location. You were filling down rows so you only needed to 'lock' the row numbers as absolute. If you put the cursor into the cell address and hit F4, you will see the first of the combinations. Keep tapping F4 to see other combinations.

=VLOOKUP(A2,'PhoneType - Email'!B$1:V$29190,3,FALSE)

这篇关于Excel复制时会混淆我的公式，我该如何阻止我改变一个部分而不是另一个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！