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

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

right(G1) ´N¬O  right(G1,1) ªº¬Ù²¤¼gªk

¥x¤¤=A  «Øij¤À¬°¤W¤U2¦C ¥x¤¤ / A¡A¥i¥H²¤Æµ{¦¡¤Î­pºâ®É ...
ML089 µoªí©ó 2015-4-25 18:15


¤F¸Ñ~·PÁ«üÂI

TOP

¦^´_ 10# starry1314


    ´N¬O MID($A$1A$49,2,1)=RIGHT(G1) Åܦ¨ MID($A$1A$49,2,1)=G1¡A¤Ö¤@­Ó¨ç¼Æ¹Bºâ
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

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

¦^´_ 12# ML089


    ¤£¦n·N«ä~¥i¦A½Ð°Ý¤@¤U ¥i¥H°µ¦h±ø¥ó¶Ü?

­ì¥»¥u­nŪ¨ú²Ä¤G­Ó¦r¥Àªº§Y¥i,
¨º¦pªG¹³³o¼Ë
4B2CV
4C20BV
4B3B
4B4C

Vªº¦ì¸m²Î¤@¦b³Ì«á¤@­Ó­È,©MVªº¥N¸¹¬O²Î¤@©T©wªº
¥i¥HŪ¨ú¨ì    ¦³B©MVªº=1
                        ¦³C©MVªº=1
                        ²Ä¤G­Ó¬°Bªº=2

¥i¥Î¸U¥Î¦r¤¸¶Ü?  ¦p>?   >*

TOP

¦^´_  ML089


    ¤£¦n·N«ä~¥i¦A½Ð°Ý¤@¤U ¥i¥H°µ¦h±ø¥ó¶Ü?

­ì¥»¥u­nŪ¨ú²Ä¤G­Ó¦r¥Àªº§Y¥i,
¨º¦pªG ...
starry1314 µoªí©ó 2015-4-26 14:15



       ¤£¦n·N«ä~¥i¦A½Ð°Ý¤@¤U ¥i¥H°µ¦h±ø¥ó¶Ü?

­ì¥»¥u­nŪ¨ú²Ä¤G­Ó¦r¥Àªº§Y¥i,
¨º¦pªG¹³³o¼Ë
4B2CV
4C20BV
4B3B
4B4C

Vªº¦ì¸m²Î¤@¦b³Ì«á¤@­Ó­È,©MVªº¥N¸¹¬O²Î¤@©T©wªº
¥i¥HŪ¨ú¨ì    ¦³B©MVªº=1
                        ¦³C©MVªº=1
                        ²Ä¤G­Ó¬°Bªº=2

¥i¥Î¸U¥Î¦r¤¸¶Ü?  ¦p>?   >*


©T©w¦r¥i¥H¥Î MID¡BRIGHT¡BLEFT¡A¤£©T©w¦r¥i¥H¥ÎFIND
¨Ò¦p
¦³B©MVªº=1¡A (RIGHT(A1:A4)="V")*COUNT(FIND("B",A1:A4))
¦³C©MVªº=1¡A (RIGHT(A1:A4)="V")*COUNT(FIND("C",A1:A4))
²Ä¤G­Ó¬°Bªº=2¡A (MID(A1:A4¡A 4,1)="B")*2
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¤£¦n·N«ä~¥i¦A½Ð°Ý¤@¤U ¥i¥H°µ¦h±ø¥ó¶Ü?

­ì¥»¥u­nŪ¨ú²Ä¤G­Ó¦r¥Àªº§Y¥i,
¨º¦pªG¹³³o¼Ë
4 ...
ML089 µoªí©ó 2015-4-26 18:40


¨º­n«ç»ò¥[¤J¶i¥h¦bSUM(IF(MID($A$1:$A$49,2,1)=RIGHT(G1),1/COUNTIF($A$1:$A$49,$A$1:$A$49)))
³o¸Ì­±©O?

¥Øªº¬O:Åý¦³A¥N¸¹¸Ì­±¤S¦³Vªº¥i¥H²Î­p¦³´X­Ó¦p¤U¹Ï©Ò¥Ü,¤£¦n·N«ä³Â·Ð©p¤F~¦pªG¥i¥Hªº¸Ü¥i¥HÀ°§Úµù¸Ñ¥\¯à¬O¤°»ò¶Ü? ³Â·Ð±z¤F
[attach]20800[/attach]

TOP

¨º­n«ç»ò¥[¤J¶i¥h¦bSUM(IF(MID($A$1A$49,2,1)=RIGHT(G1),1/COUNTIF($A$1A$49,$A$1A$49)))
³o¸Ì­± ...
starry1314 µoªí©ó 2015-4-26 20:17



   ¤§«eªº¤½¦¡¤j­P¬O¬Y«°¥«ªº¤£­«½Æ¼Æ¡A§A¥Ø«e­n§ï¬°¤°»ò? ¨S¦³¥Ø¦a¨S¦³¿ëªkÀ³®M¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ starry1314 ©ó 2015-4-26 23:15 ½s¿è

¦^´_ 16# ML089
¦p¤U¹Ï©Ò¥Ü,­ì¥»¬O­pºâ¥X²Ä2­Ó¦ì¸m±aAªº¤£­«½Æ¼Æ,
²{¦b·Q¦bÄÝ©ó³o­Ó±ø¥ó­pºâ¥Xªº¤£­«½Æ¼Æ¦A±q¤¤§ä¥X±aµÛVªº¼Æ¶q
«ü©w±ø¥ó-¤£­«½Æ-¦h­«±ø¥ó.zip (7.29 KB)

¥¼©R¦W.png (11.97 KB)

¥¼©R¦W.png

TOP

¦^´_  ML089
¦p¤U¹Ï©Ò¥Ü,­ì¥»¬O­pºâ¥X²Ä2­Ó¦ì¸m±aAªº¤£­«½Æ¼Æ,
²{¦b·Q¦bÄÝ©ó³o­Ó±ø¥ó­pºâ¥Xªº¤£­«½Æ¼Æ¦A ...
starry1314 µoªí©ó 2015-4-26 23:13



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

TOP

¦^´_ 18# ML089


    ¯«¤H.....¯uªº¤Ó·PÁ¤F¡I¡I
¦ý¹J¨ìªÅ¥ÕÄæ¦ì,·|¾É­P­pºâ¥¢±Ñ....¦]§Ú¸ê®Æªº¼Æ¶q¨C¤Ñ³£¤£¦P©Ò¥H¦³¹J¨ìªÅ¥Õ¦³¥i²¤¹L¤£­pºâªº¶Ü?
³o¼Ë§Ú¥i±N½d³ò³]¨ìA5000,´N¤£¥Î¨C¦¸§ì¨ú¸ê®Æ«á¨C¦¸³£¤â°Ê¦A§ó§ï

TOP

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

¦^´_ 19# starry1314

¥i¥H±Ä¥Î°ÊºA½d³ò

D3 =SUMPRODUCT(1/COUNTIF(OFFSET($A$3,,,COUNTA($A$3:$A$9999)),OFFSET($A$3,,,COUNTA($A$3:$A$9999)))*(MMULT(ISNUMBER(FIND(MID(D$2,{1,2},1),OFFSET($A$3,,,COUNTA($A$3:$A$9999))))*1,{1;1})=2))-E3
E3 =SUMPRODUCT(1/COUNTIF(OFFSET($A$3,,,COUNTA($A$3:$A$9999)),OFFSET($A$3,,,COUNTA($A$3:$A$9999)))*(MMULT(ISNUMBER(FIND(MID(E$2,{1,2},1),OFFSET($A$3,,,COUNTA($A$3:$A$9999))))*1,{1;1})=2))


ª`·N­Y¦³¿ù»~®É¡AªÅ®æ­n²M°£¤º®e¤£¯à¦³ªÅ¥Õ¦r

¨Ï¥Î COUNTIF «Ü¯Ó¸ê·½¡A­Y¦³ 5000µ§®É·|¶]«Ü¤[¡A«ØijÃö³¬¦Û°Ê­pºâ¡A¶ñ§¹¸ê®Æ«á¦A«öF9±Ò°Ê­pºâ¡AµM«á...¥h³ÜªM©@°Ø...¤W¤W´Z©Ò...¥ð®§¤@¤U¡AÀ³¸Ó·|¶]«Ü¤[¤£­n¥H¬°¬O·í±¼¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD