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

[µo°Ý] ½Ð°ÝÃö©ó²Î­p¤£­«½Æ¶µ¥Øªº­Ó¼Æ"«ü©w±ø¥ó"

¤£¦n·N«ä~¦pªG¦AA12Äæ¿é¤J¼Æ¾Ú¤]¬O·|¥X²{¿ù»~,
§Ú¦A±N(MMULT(ISNUMBER(FIND(MID(D$2,{1,2},1),$A$3A$9999))*1,{1;1})=2)
§ï¬°§ó«e¤è½d³ò¤@¼Ë¤j¤p¤]¬O·|¥X¿ù©O,
¦pªG»¡¨Ï¥ÎCOUNTIF«Ü¯Ó¸ê·½ªº¸Ü,ÁÙ¬O»¡¦³¹J¨ìªÅ¥Õ´N¦Û°Ê°±¤îÄ~Äò­pºâ¤U¥h©O,
¦]§Ú¸ê®Æ¦pªG¦³1000µ§´N1~1000³£¦³¸ê®Æ,1000´Nµ¥©óµ²§ô¤£·|¦A¦³¸ê®Æ¥i¥H­pºâ,¦³¿ìªk°»´úªÅ¥Õ´N°±¤î¶Ü

TOP

(MMULT(ISNUMBER(FIND(MID(D$2,{1,2},1),$A$3A$5000))*1,{1;1})=2))-E3
ÁÙ¬O·|¾É­P¦³ªÅ¥Õ´N­pºâ¿ù»~©O,
§Ú¦A±N¬õ¦r½d³ò§ó§ï¬°¸û¤j¤]¬O·|¥X¿ù

¦pªG»¡COUNTIF«Ü¯Ó¸ê·½,¦³¿ìªk°»´ú¨ìªÅ¥Õ§Y°±¤î­pºâ¶Ü?

TOP

§Ú¦³§ä¨ìª©¥D¤§«e¦^ÂЪº¤å³¹
¡iSUMPRODUCT ¹J¨ìªÅ¥ÕÀx¦s®æÅã¥Ü¿ù»~¸Ñ¨M¤èªk¡j
§Ú±N½s¸¹©w¸q¬°¦WºÙ«á,
=SUMPRODUCT(1/COUNTIF(OFFSET($A$3,,,COUNTA(¼Æ¶q)),OFFSET($A$3,,,COUNTA(¼Æ¶q)))*(MMULT(ISNUMBER(FIND(MID(D$2,{1,2},1),$A$3:$A$5000))*1,{1;1})=2))-E3
=SUMPRODUCT(1/COUNTIF(OFFSET($A$3,,,COUNTA(AV)),OFFSET($A$3,,,COUNTA(AV)))*(MMULT(ISNUMBER(FIND(MID(E$2,{1,2},1),$A$3:$A$5000))*1,{1;1})=2))

¥i¦Û°Ê­pºâ¤F,·Q½Ð°Ý³o¼Ëªº¼gªk¦³¤°»ò°ÝÃD¶Ü|?©Î¬O¦³¸û¦nªº«Øij

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2015-4-27 14:17 ½s¿è

¦^´_ 23# starry1314

§Ú¦³§ä¨ìª©¥D¤§«e¦^ÂЪº¤å³¹
¡iSUMPRODUCT ¹J¨ìªÅ¥ÕÀx¦s®æÅã¥Ü¿ù»~¸Ñ¨M¤èªk¡j
§Ú±N½s¸¹©w¸q¬°¦WºÙ«á,
=SUMPRODUCT(1/COUNTIF(OFFSET($A$3,,,COUNTA(¼Æ¶q)),OFFSET($A$3,,,COUNTA(¼Æ¶q)))*(MMULT(ISNUMBER(FIND(MID(D$2,{1,2},1),$A$3:$A$5000))*1,{1;1})=2))-E3
=SUMPRODUCT(1/COUNTIF(OFFSET($A$3,,,COUNTA(AV)),OFFSET($A$3,,,COUNTA(AV)))*(MMULT(ISNUMBER(FIND(MID(E$2,{1,2},1),$A$3:$A$5000))*1,{1;1})=2))

¥i¦Û°Ê­pºâ¤F,·Q½Ð°Ý³o¼Ëªº¼gªk¦³¤°»ò°ÝÃD¶Ü|?©Î¬O¦³¸û¦nªº«Øij


¥i¥Î¦WºÙ©w¸q Rng ¬° OFFSET($A$3,,,COUNTA($A$3:$A$9999))

D3 =SUMPRODUCT(1/COUNTIF(Rng,Rng)*(MMULT(ISNUMBER(FIND(MID(D$2,{1,2},1),Rng))*1,{1;1})=2))-E3
E3 =SUMPRODUCT(1/COUNTIF(Rng,Rng)*(MMULT(ISNUMBER(FIND(MID(E$2,{1,2},1),Rng))*1,{1;1})=2))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 24# ML089


   ·PÁ«ü¾É³o¼Ë«ü¥O¤SÁYµu¤F¡I³o¼ËÀ³¸Ó¤£·|µo¥Í¤§«e»¡ªº«ö¤U«ü¥O«á¥h¶]ªM©@°Ø§a¡I¡I¡I¡@¡@:D

TOP

¦^´_ 25# starry1314

COUNTIF(Rng,Rng)

Rng ¸ê®Æ½d³ò¶W¹L3000µ§´N·|«ÜºC
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 26# ML089


   ¤F¸Ñ¡ã¥Ø«e¬ù¦b¢°¢¯¢¯¢¯¡ã¢°¢´¢¯¢¯¤§¶¡¡I­n¨ì¢²¢¯¢¯¢¯ÁÙ­n¦³ÂI®É¶¡¡ã¦ý¨ì¨º®É¸ê®Æ®wÀ³¸Ó¤w¸g«Ø¸m§¹¦¨¡A´N¨SÃö«Y¤F!·PÁÂÀ°¦£Åo

TOP

¦^´_ 26# ML089


    ¤£¦n·N«ä~¹J¨ì­Ó°ÝÃD¡I¡I¡@¦pªG¡@¢³¢Þ¢´¢Ð¢ä¡]§t¦³¢Þ¢Ð¢ä¡^¡@¡@¡@¢³¢Ð¢´¢ä¡]§t¦³¢Ð¢ä¡^¡@·|¾É­P­«½Æ­pºâ¡@½Ð°Ý¥i«ü©w³Ì¥kÃä¼Æ¨Ó¬O¢ä¥B¦b²Ä¢±Äæ¦ì¬O¢Ðªº¶Ü
«ü©w±ø¥ó ¤£­«½Æ-2.png
2015-4-28 13:41


«ü©w±ø¥ó ¤£­«½Æ.rar (7.7 KB)

TOP

¦^´_  ML089


    ¤£¦n·N«ä~¹J¨ì­Ó°ÝÃD¡I¡I¡@¦pªG¡@¢³¢Þ¢´¢Ð¢ä¡]§t¦³¢Þ¢Ð¢ä¡^¡@¡@¡@¢³¢Ð¢´¢ä¡]§t¦³¢Ð¢ä ...
starry1314 µoªí©ó 2015-4-28 14:53


½s½X³W«h¤Î¨ú½X³W«h¤£¬OÁ¿±o«Ü²M·¡¡A
­Y¨S¦³¿ìªkÁ¿¤£¬O«Ü²M·¡®É¡A½Ð§@¥Ü½d¨Ò¤ñ¸ûª¾¹D§A­n¦p¦ó¨ú?

½s½X³W«h¥Ø«e¬O«e4½X©T©w¦³¡A²Ä5½X V ¤£¤@©w¦³
¢³¢Þ¢´¢Ð¢ä §A­n¤ñ¸ûªº½X¦³ ¦r¤¸²Ä2¡B4¡B5¦r¡A³o»P¤W¤èªº BV ¡BPV­n¦p¦ó¹ïÀ³? ¦³®É¹ï²Ä2½X¡B¦³®É¹ï²Ä4½X¡A³o¼Ë¬O¬Ý¤£¥X³W«h
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 29# ML089
¤£¦n·N«ä~¤wªþ¤WÀɮרå[»¡©ú,

«ü©w±ø¥ó ¤£­«½Æ-2.png
2015-4-28 18:10

    «ü©w±ø¥ó ¤£­«½Æ.rar (8.08 KB)

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤Hªº§Ö¼Ö¡D¤£¬O¦]¬°¥L¾Ö¦³±o¦h¡A¦Ó¬O¦]¬°¥L­p¸û±o¤Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD