求助:如何禁止在同一列中输入相同的数据

momocd

<p>我用的方法是:数据--有效性--自定义--公式:=countif(a:a,a1)=1</p><p>这样设置好之后,不可以输入相同的数据.但是我按CTRL+D可以复制上面的数据,并且Ctrl+V也可以复制相同的数据在那一列,并且不会出错.为什么????</p><p>有什么更好的公式可以保证不能输入相同的数据吗??????</p><p></p>

[此贴子已经被作者于2008-7-21 19:49:38编辑过]

长安布衣

建议用条件格式，出现重复，单元格格式变化提醒

abcd321abcd

数据有效性

Sampson

<p>可以利用辅助列进行判断，最终结果会显示在C列。</p><p><table cellspacing="0" cellpadding="0" width="713" border="0" style="WIDTH: 535pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="26" style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 950;"></col><col></col><col></col><col></col><col width="52" style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901;"></col><col></col><col></col><col></col><col width="379" style="WIDTH: 284pt; mso-width-source: userset; mso-width-alt: 13860;"></col><col></col><col></col><col></col><col width="256" style="WIDTH: 192pt; mso-width-source: userset; mso-width-alt: 9362;"></col><col></col><col></col><col></col></colgroup><tbody><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl24" width="26" height="17" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 20pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver;"><font face="Arial" size="2">&nbsp;</font></td><td class="xl25" width="52" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver;"><font face="Arial" size="2"><strong>&nbsp;&nbsp;&nbsp;&nbsp; A</strong></font></td><td class="xl25" width="379" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 284pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver;"><font face="Arial" size="2"><strong>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B</strong></font></td><td class="xl25" width="256" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 192pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver;"><font face="Arial" size="2"><strong>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; C</strong></font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl25" height="17" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver;"><font face="Arial" size="2"><strong>1</strong></font></td><td class="xl26" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2"><strong>&nbsp;输入行</strong></font></td><td class="xl26" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2"><strong>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 判断行</strong></font></td><td class="xl26" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2"><strong>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 显示行</strong></font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl25" height="17" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver;"><strong><font face="Arial" size="2">2</font></strong></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">&nbsp;</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">=IF(COUNTIF(A:A,A3)=1,"A",IF(COUNTIF(A:A,A3)=0,"B","C"))</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">=IF(B3="A",A3,IF(B3="B","","重复输入"))</font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl25" height="17" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver;"><strong><font face="Arial" size="2">3</font></strong></td><td class="xl28" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">1</font></td><td class="xl28" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">C</font></td><td class="xl28" xfmla="=IF(C4=&quot;A&quot;,B4,IF(C4=&quot;B&quot;,&quot;&quot;,&quot;重复输入&quot;))" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">重复输入</font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl25" height="17" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver;"><strong><font face="Arial" size="2">4</font></strong></td><td class="xl28" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">2</font></td><td class="xl28" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">A</font></td><td class="xl28" xfmla="=IF(C5=&quot;A&quot;,B5,IF(C5=&quot;B&quot;,&quot;&quot;,&quot;重复输入&quot;))" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">2</font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl25" height="17" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver;"><strong><font face="Arial" size="2">5</font></strong></td><td class="xl28" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">1</font></td><td class="xl28" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">C</font></td><td class="xl28" xfmla="=IF(C6=&quot;A&quot;,B6,IF(C6=&quot;B&quot;,&quot;&quot;,&quot;重复输入&quot;))" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">重复输入</font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl25" height="17" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver;"><strong><font face="Arial" size="2">6</font></strong></td><td class="xl28" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">&nbsp;</font></td><td class="xl28" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">B</font></td><td class="xl28" xfmla="=IF(C7=&quot;A&quot;,B7,IF(C7=&quot;B&quot;,&quot;&quot;,&quot;重复输入&quot;))" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">&nbsp;</font></td></tr></tbody></table></p><p></p><p>第2行显示的为公式，第3~6行显示的是实例</p>

[此贴子已经被作者于2008-7-23 0:45:31编辑过]