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

lichang

·s¼W Microsoft Excel ¤u§@ªí.rar (8.12 KB)

¤U¸ü¦¸¼Æ: 7

2016-6-1 20:39

¦U¦ì¡A¤p§Ì²{¦b·Q¼g¤@­Ó§PÂ_¦¡
A~E°Ï³£¬O¿é¤J°Ï¡A§PÂ_¤è¦¡¬°¾î¦V§PÂ_
¨Ò¦p¡GA2­n§PÂ_H¦C¬O§_¦³²Å¦Xªº¡A
            B2­n§PÂ_I¦C¬O§_¤]²Å¦X
           ¥H¦¹Ãþ±À

§PÂ_§¹«á¡Aµ²ªGÅã¥Ü¦bF°Ï
§PÂ_§¹¤@¦æ«á¡A¦A§PÂ_3¦æ¬O§_¦³²Å¦Xªº

©Ò¥Hµ²ªG¤¤¥u¦³"¿é¤J°Ï²Ä11¦æ"¬O§¹¥þ»P"°Ñ·Ó°Ï8¦æ"§¹¥þ¤@¼Ë¡A©Ò¥HÅã¥Ü¦bF°Ï¡AÅã¥ÜOK

¦]¬°¥¨¶°¤w¸g¦b¶]¨C¬í°O¿ý¥\¯à¡A©Ò¥H¸ê·½³Q¥e¥Î«Ü¦h¡A©Ò¥H¬O§_¦³¤½¦¡ªº¤è¦¡´N¯à§âF°Ïª½±µÅã¥Ü¥X¨Ó(¤£¨Ï¥Î¥¨¶°ªº¤è¦¡)

ÁÂÁÂ

¤U¸ü (132.93 KB)

2016-6-1 20:48

p212

¦^´_ 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

lichang

¦^´_ 2# p212

ÁÂÁ¤j¤jªº´£¨Ñ¡A½Ð°Ý¤@¤U¬°¤°»ò§Ú³o­Óª©¥»¦b2003¤£ªk¨Ï¥Î!!???
¦³»yªk¤£¤ä´©¶Ü???

p212

¦^´_ 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

hcm19522

http://blog.xuite.net/hcm19522/twblog/420545808

­ã´£³¡ªL

F2:
=IF(OR(MMULT(N(A2:E2=H$2:L$14),{1;1;1;1;1})=5),"OK","NG")

p212

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

hcm19522

"­ã¤j" ¤Ó¦£ ¥N¸Ñ»¡ ­Y¦³¤£¨¬ ½Ð½ç±Ð
¨Ò :MMULT(A1:E9,{1;1;1;1;1})
«e­±¯x§Î½d³ò ,«á­±¤W¤U®æ ,¹q¸£¥Î ";" °Ï¹j  ,«e«á¹ï½Õ¬O¿ù»~
«e­±¥ª¥k®æ¼Æ»Ý = «á­±¤W¤U®æ¼Æ   ;«á­±¤W¤U®æ¼Æ­Y¤Ó¦h ,¥i¥Î¦pROW($1:$99)^0
¥»¨Ò¤À§O¬°A1:E1*{1;1;1;1;1}«á¥[Á`  ;A2:E2*{1;1;1;1;1}«á¥[Á`  ;¡K  ;A9:E9*{1;1;1;1;1}«á¥[Á` ,­p¦¨9®æ¼Æ¦r
A1:E9¥i­pºâ¦¡

lichang

¦^´_ 4# p212


¤j¤j¡A§Ú­×§ï«á¥i¥H¨Ï¥Î¡A¦ý§Ú¤S¹J¨ì¤U­Ó°ÝÃD
¦pªG§Ú¬O­n°Ñ¦Ò©ñ¦b¥t¤@­Ó¤À­¶¡A¬°¤°»ò´NµL®Ä¤F????
¦pªþ¥ó¡AÁÂÁÂ!!!

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

¤U¸ü¦¸¼Æ: 5

2016-6-7 17:41

lichang

¦^´_ 8# hcm19522

¤£¦n·N«ä¡A¤p²Ä¤£¤~¡A¥i§_¼g­Ó½d¨Ò¡A¦p¦ó¸Ñ¤Æ¤½¦¡¡A¦]¬°§Úªº¤ñ¸û¦¡ªº½T«Ü¦h(¦h¹F20­Ó)
©Ò¥H¦³¤ñ¸û¦nªº¤è¦¡¥i¥H´£¨Ñ¶Ü???

ÁÙ¦³¡A¦pªG§Ú­n¤ñ¸ûªº°Ñ¦Ò°Ï¬O¸õ®æ¤è¦¡(¨Ò¦p¡G­ì¥»IÁÙ¬OI,¦ýJ¶]¨ìK¡AK¶]¨ìM¥H¦¹Ãþ±À¡AÀ³¸Ó­n«ç»ò§ï???