Excel 在复制时弄乱了我的公式，我怎样才能停止它改变一个部分而不是另一个? [英] Excel messes with my formula when copied, how can I stop I stop it changing one part but not the other?

问题描述

所以我正在使用一个正在更新的旧数据库系统，以前它运行过许多不同的文件，一个是电子邮件，一个是数字，你懂的.

无论如何，在尝试编译成一个文件时，我遇到了一个障碍，有些人没有号码或电子邮件等，这意味着我不能只是复制旧数据(PersonID 5 的电话)将在 PersonID 4 的空间中)

所以我所做的是使用 Vlookup 公式，但遇到了一些问题，今天早上我意识到了问题所在.

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

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

基本上，我需要根据它所在的行更改(A2、A3、A4)，但我需要 B1:V29190 保持不变，因为当前 Excel 试图变得聪明并在每次复制粘贴时进行处理.

我该怎么做?

非常感谢，

冰冷

解决方案

名称是绝对和相对单元格引用.$ 将一行或一列绝对，因此它们在填充或复制到新位置时不会改变.您正在填充行，因此您只需要将行号锁定"为绝对值.如果您将光标放入单元格地址并点击 F4，您将看到第一个组合.继续点按 F4 以查看其他组合.

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

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.

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)

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)

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.

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.

How can I do this?

Many thanks,

icey

解决方案

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屋！