Excel为文本分配自动编号 [英] Excel assign auto number for text

问题描述

我有一个电子表格，我需要一个公式来创建一个类似于自动号码的行为，如果值是另一列中的Countif公式是重复的。我想为具有多个订单＃的每个名称设置一个auto＃。所以在这个例子中，Joe的ID为1，因为他是第一个是Sheet，Bunny得到2，当Joe的名字重复时，它仍然是1.这个名字可能是几十个人，每个文件都会有所不同。感谢您的帮助。

 订单＃LN数量项目名称点数自动编号
 OD10560 1 24部分＃12位置1 Joe John 1 1 
 OD10559 1 1部分＃114位置2 Bunny Johnson 2 2 
 OD10569 1 3部分＃444位置1 Joe John 1 1 
 OD10567 1 3部分＃777位置2 Archy Bunker 0 
 OD10567 2 4部分＃123位置2 Archy Bunker 0 
 OD10562 1 24部分＃458位置1 Kay Harrison 0 
 OD10577 1 2部分＃471位置2 Bunny Johnson 1 2 
 OD10577 2 1部分＃736位置2 Bunny Johnson 1 2 
 OD10563 1 2部分＃778位置1标记谜语0 
 OD10579 1 2部分＃3347位置1 Terry Clark 0 
  

电子表格现在用于公式参考，
链接

解决方案

假设countifs列是列H ，并且名称列是列G，将其放在I2中并向下拖动：

  = if（H2> 0，iferror指数（I $ 1：I1，match（G2，G $ 1：G1,0）），max（I $ 1：I1）+1），）
  

这首先检查是否有该名称的重复值，如countifs列中定义的那样。如果有的话，它会检查这个特定的名称是否已经在此选项卡上方的一行上显示，使用MATCH功能。如果有匹配，则会给出匹配的行号。请注意，引用开始于对行1的绝对引用，以及对行1的相对引用。这将在您将公式向下拖动时拖动测试区域的最下一行。公式将始终在公式单元格上方的单元格结束。

如果MATCH数字提供行号，它将赋值给INDEX函数，已经在第一列上面找到了已经分配的号码。如果有错误，那么还没有匹配，所以在上面列出了最大值，并添加了一个。如果THAT提供了错误，请参见下文。 / p>

根据I1的值，您可能需要将第一个匹配的值硬编码为1，否则将公式更改为以下内容：

  = if（H2> 0，iferror（index（I $ 1：I1，match（G2，G $ 1：G1,0）），iferror （max（I $ 1：I1）+1,1）），）
  

唯一的区别这里是，如果MAX函数返回一个错误，那么上面没有数字值，因此它被认为是第一个命中，默认为1。

I have a spreadsheet that I need a formula to create what would act similar to an auto number if a value is a duplicate based on a Countif formula in another column. I want to make an auto # for every name that has multiple order#. So in this example Joe is getting ID of 1 as he was first is sheet and Bunny gets 2 and when Joe's name repeats it is still 1. This names are could be dozens of people and will be different each file. Thanks for help.

ORDER#  LN  QTY ITEM        LOC         NAME          Countifs  Auto Number
OD10560 1   24  part#12     Location 1  Joe John       1            1
OD10559 1   1   part#114    Location 2  Bunny Johnson  2            2
OD10569 1   3   part#444    Location 1  Joe John       1            1
OD10567 1   3   part#777    Location 2  Archy Bunker   0    
OD10567 2   4   part#123    Location 2  Archy Bunker   0    
OD10562 1   24  part#458    Location 1  Kay Harrison   0    
OD10577 1   2   part#471    Location 2  Bunny Johnson  1            2
OD10577 2   1   part#736    Location 2  Bunny Johnson  1            2
OD10563 1   2   part#778    Location 1  Mark Riddle    0    
OD10579 1   2   part#3347   Location 1  Terry Clark    0    

Spreadsheet is here for formula reference, Link

解决方案

Assuming the countifs column is column H, and the name column is column G, put this in I2 and drag down:

=if(H2>0,iferror(index(I$1:I1,match(G2,G$1:G1,0)),max(I$1:I1)+1),"")

This first checks to see if there are duplicate values for that name, as defined in the countifs column. If there are, it checks to see if that particular name has been shown on a row above on this tab yet, using the MATCH function. If there is a match, it gives the row number of that match. Note that the references start with an absolute reference to row 1, and a relative reference to row 1. This will drag the bottom row of the testing area as you drag the formula down. The formula will always end at the cell above the formula cell.

If the MATCH number provides the row number, it gives that value to the INDEX function, which pulls in the already-assigned number found above on column I. If there is an error, there is no match yet, so it takes the MAX value found in the column thus far above, and adds 1. If THAT provides an error, see below.

Depending on the value of I1, you may need to hardcode the value of the first match as '1', or else change the formula to the following:

=if(H2>0,iferror(index(I$1:I1,match(G2,G$1:G1,0)),iferror(max(I$1:I1)+1,1)),"")

The only difference here is that if the MAX function returns an error, then there are no number values above yet, and therefore it is presumed to be the first hit, which defaults to 1.

这篇关于Excel为文本分配自动编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！