ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¦p¦ó§PÂ_¥þ³¡²Å¦Xªº±ø¥ó®æ

¥»©«³Ì«á¥Ñ p212 ©ó 2016-6-2 09:14 ½s¿è

¦^´_ 1# lichang
¤@¡B©w¸q¦WºÙ
1¡B¡u¦WºÙ¡v¿é¤J rng¡A¡u°Ñ·Ó¨ì¡v¿é¤J=OFFSET(¤u§@ªí1!$H$2,,,COUNTA(¤u§@ªí1!$H:$H)-1)
2¡B¡u¦WºÙ¡v¿é¤J x¡A¡u°Ñ·Ó¨ì¡v¿é¤J=MATCH(¤u§@ªí1!$A2,rng,0)
3¡B¡u¦WºÙ¡v¿é¤J I¡A¡u°Ñ·Ó¨ì¡v¿é¤J=INDIRECT(ADDRESS(x+1,COLUMN(¤u§@ªí1!$I$1)))
4¡B¡u¦WºÙ¡v¿é¤J J¡A¡u°Ñ·Ó¨ì¡v¿é¤J=INDIRECT(ADDRESS(x+1,COLUMN(¤u§@ªí1!$J$1)))
5¡B¡u¦WºÙ¡v¿é¤J K¡A¡u°Ñ·Ó¨ì¡v¿é¤J=INDIRECT(ADDRESS(x+1,COLUMN(¤u§@ªí1!$K$1)))
6¡B¡u¦WºÙ¡v¿é¤J L¡A¡u°Ñ·Ó¨ì¡v¿é¤J=INDIRECT(ADDRESS(x+1,COLUMN(¤u§@ªí1!$L$1)))
¤G¡BÀx¦s®æF2¿é¤J¤½¦¡
=IFERROR(IF(SUMPRODUCT((B2=I)*(C2=J)*(D2=K)*(E2=L))<>1,"NG","OK"),"NG")
¦V¤U½Æ»s¤½¦¡
¥H¤W©å¨£¡A½Ð°Ñ¦Ò¡I

·s¼W Microsoft Excel ¤u§@ªí_ref.zip (13.13 KB)

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2016-6-7 10:28 ½s¿è

¦^´_ 3# lichang
Excel 2003µLªk¤ä´©IFERROR
½Ð­×§ï2#¤§
¤G¡BÀx¦s®æF2¿é¤J¤½¦¡
=IF(ISNA(B2=I),"NG",IF(SUMPRODUCT((B2=I)*(C2=J)*(D2=K)*(E2=L))<>1,"NG","OK"))
¦V¤U½Æ»s¤½¦¡
½Ð°Ñ¦Ò¡I

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2016-6-7 15:03 ½s¿è

¦^´_ 6# ­ã´£³¡ªL
¤@¨B¨ì¦ìªºµu¤½¦¡¹ê¦b¤Ó¼F®`¡I
¥i§_½Ðª©¥D´N6#ªº¤½¦¡(MMULT¨ç¼Æ)
«ü¾É¤@¤U¯x°}­¼ªkªº¶ø¸q¡H
ÁÂÁ¡I
ps. Excelªº»¡©ú¦³¨ÇÃøÀ´¡A¹ï¦~ªñ50ªº¤H¦Ó¨¥ ^_^

TOP

        ÀR«ä¦Û¦b : ¦¨¥\¬OÀuÂIªºµo´§¡A¥¢±Ñ¬O¯ÊÂIªº²Ö¿n¡C
ªð¦^¦Cªí ¤W¤@¥DÃD