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

[µo°Ý] ¨ç¼Æ¤½¦¡¡J³Ì«áªº¦PÄæ¹ïÀ³­È¦³¥X²{¦b«ü©wªº¤G¦C

[µo°Ý] ¨ç¼Æ¤½¦¡¡J³Ì«áªº¦PÄæ¹ïÀ³­È¦³¥X²{¦b«ü©wªº¤G¦C

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2018-2-12 22:35 ½s¿è

TEST-1.rar (3.32 KB)

¥Ø«eªºT7¤½¦¡
=·íIÄæ=R7´Á¼Æªº$R$5­È¡A¨ä¦bT1´Á¼Æªº¦PÄæ¹ïÀ³­È¡A¦³¦A¥X²{¦bT2´Á¼Æ¡F¥B¸Ó¦³¥X²{¦bT2´Á¼Æªº­È¡A¨ä¦bT3´Á¼Æªº¦PÄæ¹ïÀ³­È¡A¥ç¦³¥X²{¦bT4´Á¼Æ¡C

·Q¶i¶¥¬°T7¤½¦¡
=·íIÄæ=R7´Á¼Æªº$R$5­È¡A¨ä¦bT1´Á¼Æªº¦PÄæ¹ïÀ³­È¡A¦³¦A¥X²{¦bT2´Á¼Æ¡F¥B¸Ó¦³¥X²{¦bT2´Á¼Æªº­È¡A¨ä¦bT3´Á¼Æªº¦PÄæ¹ïÀ³­È¡A¥ç¦³¥X²{¦bT4´Á¼Æ©MT5´Á¼Æ¡C

½Ð¦U¦ì°ª¤â¤j¤jÀ°¦£~ÁÂÁÂ!

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2018-2-13 22:44 ½s¿è

­ü!¯u¬O¨S¤H½t
¥i§_½Ðª©¥D¤j¤j­ÌÀ°À°¦£?
¸U¤À·PÁÂ¥ý~

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2018-2-18 09:56 ½s¿è

¦^´_ 2# ziv976688
=IF(OR($R$5="",$R7=""),"",IF(OR(SUMIF(OFFSET($I$6,T$2,1,,7),SUMIF(OFFSET($I$6,$R7,1,,7),$R$5,OFFSET($I$6,T$1,1,,7)),OFFSET($I$6,T$3,1,,7))=OFFSET($I$6,T$4,1,,7))*OR(SUMIF(OFFSET($I$6,T$2,1,,7),SUMIF(OFFSET($I$6,$R7,1,,7),$R$5,OFFSET($I$6,T$1,1,,7)),OFFSET($I$6,T$3,1,,7))=OFFSET($I$6,T$5,1,,7)),$R$6,""))
=IF(OR($R$5="",$R7=""),"",IF·íIÄæ=R7´Á¼Æªº$R$5­È¡A¨ä¦bT1´Á¼Æªº¦PÄæ¹ïÀ³­È¡A¦³¦A¥X²{¦bT2´Á¼Æ¡F¥B¸Ó¦³¥X²{¦bT2´Á¼Æªº­È¡A¨ä¦bT3´Á¼Æªº¦PÄæ¹ïÀ³­È¡A¥ç¦³¥X²{¦bT4´Á¼Æ*·íIÄæ=R7´Á¼Æªº$R$5­È¡A¨ä¦bT1´Á¼Æªº¦PÄæ¹ïÀ³­È¡A¦³¦A¥X²{¦bT2´Á¼Æ¡F¥B¸Ó¦³¥X²{¦bT2´Á¼Æªº­È¡A¨ä¦bT3´Á¼Æªº¦PÄæ¹ïÀ³­È¡A¥ç¦³¥X²{¦bT5´Á¼Æ¡C

½Ð°Ý¡JT7¤½¦¡­n¦p¦ó²¤Æ¡HÁÂÁÂ¥ý!

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2018-2-24 16:53 ½s¿è

¼h¦¸¤Ó¦h ¤À¶}³B²z  ¥ý§ä¥X¼Æ¦r T8=SUMIF(OFFSET(I6,T2,1,,7),SUMIF(OFFSET(I6,R7,1,,7),R5,OFFSET(I6,T1,1,,7)),OFFSET(I6,T3,1,,7))
§PÂ_T4 T5¬O§_¦³¦¹¼Æ¦rT7=IF(COUNT(MATCH(T4:T5,IF(MMULT(N(J7:P22=T8),ROW(1:7)^0),I7:I22),))=2,R6,"µL")
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 4# hcm19522
hcm19522¤j¤j:
·PÁ±zªº¦^À³©M«ü¾É^^

´ú¸Õªºµ²ªG:
T7=16
T8=34
¨Ì·Ó¶Q¸Ñªº®æ¦¡¬OOKªº~
¦ý¦bT8ªº¤½¦¡µLªk®M¶iT7¤½¦¡¤¤¨Ï¥Î
=IF(COUNT(MATCH(T4:T5,IF(MMULT(N(J7:P22=(SUMIF(OFFSET(I6,T2,1,,7),SUMIF(OFFSET(I6,R7,1,,7),R5,OFFSET(I6,T1,1,,7)),OFFSET(I6,T3,1,,7)))),ROW(1:7)^0),I7:I22),))=2,R6,"µL")
OFFSET¨ç¼Æ¨Ï¥Î¦³°ÝÃD
¦Ó¤S¤£¯à¦³»²§UÄ檺±¡ªp¤U~¤p§Ì­n¦p¦ó±NT7¤½¦¡¤U©Ô»PRÄæ¹ï»ô^^"

©|½Ð»Õ¤U¦A¦¸«ü¾É!·P®¦^^

TOP

¦^´_ 4# hcm19522
hcm19522¤j¤j:
MMULT¨ç¼Æ~ÁÙ¤£¤Ó¼ô½m¤£¤Ó·|À³¥Î
¥u¦n¨Ì¾Ú¶Q¸Ñªº¼Ò¦¡¡A¥H¨ä¥Lªº¨ç¼Æ­×§ï®M¥Î
=IF(OR($R$5="",$R7=""),"",IF((COUNT(RANK(SUMIF(OFFSET($I$6,T$2,1,,7),SUMIF(OFFSET($I$6,$R7,1,,7),$R$5,OFFSET($I$6,T$1,1,,7)),OFFSET($I$6,T$3,1,,7)),OFFSET($I$6,CHOOSE({1,2},T$4,T$5),1,,7)))=2),$R$6,""))
°}¦C¤½¦¡

¦A¦¸·PÁ±zªº«ü¾É^^

TOP

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 7# hcm19522
ÁÂÁ±z±NMMULT¨ç¼Æ§@¸Ô²Óªº»¡©ú~·P®¦^^

TOP

        ÀR«ä¦Û¦b : °ß¨ä´L­«¦Û¤vªº¤H¡A¤~§ó«i©óÁY¤p¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD