Board logo

¼ÐÃD: ½Ð°Ý¦U¦ì«e½úºô¸ô§ì¤Uªº¸ê®Æ¦³ªÅ®æ¡A¦p¦ó³B²z¤ñ¹ï [¥´¦L¥»­¶]

§@ªÌ: lifetw    ®É¶¡: 2015-7-16 02:04     ¼ÐÃD: ½Ð°Ý¦U¦ì«e½úºô¸ô§ì¤Uªº¸ê®Æ¦³ªÅ®æ¡A¦p¦ó³B²z¤ñ¹ï

½Ð°Ý¦U¦ì«e½úºô¸ô§ì¤Uªº¸ê®Æ¦s¤JexcelÀÉ®×(¤u§@ªí¦WºÙ02)¡A¦s¤JBÄæ³æ¤@Àx¦s®æ¡A¦ý¼Æ¦r¶¡¦³ªÅ®æ¡A¦p¦ó³B²z¤ñ¹ïºÃ°Ý?
  A          B
0707   20 45 10 15 18 88 65 89 90 05
0708   20 30 21 56 44 88 65 89 90 05
.
.
.
.
¦@¦³¬ù¤Q¸Uµ§¸ê®Æ
¥Î¥t¤@¤u§@ªí(¦WºÙ01)¦UÀx¦s®æ¤ºªº¼Æ¦r¦³4~5½X¦p4, 13, 43, 59©Î10, 43, 59, 69, 72
(¼Æ¦r¥Ñ¤p¦Ü¤j¦³±Æ§Ç¡A¦ý","³r¸¹»P¼Æ¦r¶¡¦³ªÅ®æ¡A¤ñ¦p, 72)¤]¦³¬ù¤Q¸Uµ§¸ê®Æ
¡A¥D­n¬O¥Î¤u§@ªí(¦WºÙ01)¨Ó¤ñ¹ï¤u§@ªí02¤¤BÄæ¦ìªº¼Æ¦r¡A(BÄæ¦ìªº¼Æ¦r»Ý±Æ§Ç©Î¦p¦ó³B²z¶Ü?)
¦p²Å¦X«h­pºâÀx¦s®æ¼Æ¥Ø(¥ÎCOUNTIF?)»P¨C­Ó²Å¦X¤§¶¡ªº¶¡¹j¼Æ¡A
¨Ã§ì¨ì¥t¤@¤u§@ªí¡A¤À§O¼g¤JÀx¦s®æ¡A¥Ø¦a¬O²Î­p¤u§@ªí01¦UÀx¦s®æ¤ºªº¼Æ¦r¥X²{´X¦¸(·|¥ÎFREQUENCY¨ç¼Æø»s¤À¥¬¹Ï)
¡A²Î­p¨C­Ó²Å¦X¤§¶¡¶¡¹j¼Æªº¸Ô²Ó¤À¥¬¹Ï
ªì¾Ç¥E½m¡AÁٽЦU¦ì¥ý¶i«ü±Ð~~ÁÂÁÂ!!
§@ªÌ: ML089    ®É¶¡: 2015-7-16 14:53

¦^´_ 1# lifetw


±N¸ê®Æ¾ã²z½d¨ÒÀÉ¡A­ì©l¸ê®Æ¤Î±ý¾ã²z¦¨ªº¸ê®Æ®æ¦¡¤Î­n¨D

ºÉ¶q§ä¥X¤£¤@¼Ëªº¸ê®Æ®æ¦¡¡A¤~¯à§¹¾ã¦Ò¼{

¤£µM¤Q¸Uµ§§@§¹§A«ç»òª¾¹Dµª®×¬O¹ïªº?
§@ªÌ: lifetw    ®É¶¡: 2015-7-16 18:49

¦^´_ 2# ML089
ª©¤j»¡ªº¬O
{:3_57:} ¡A½Ð¦h¦h¨£½Ì!(¤â©çÃBÀY)
ªþ¤W¥|½Xªº½d¨Ò
ÁÂÁÂ!!
§@ªÌ: lifetw    ®É¶¡: 2015-7-16 18:57

¥»©«³Ì«á¥Ñ lifetw ©ó 2015-7-16 18:58 ½s¿è

¦^´_ 2# ML089
ª©¤j»¡ªº¬O
½Ð¦h¦h¨£½Ì!(¤â©çÃBÀY)
ªþ¤W¥|½Xªº½d¨Ò¡A(¤W­±¸ê®Æ¦³º|)¡A½Ð¥H test1.zip ¬°¥D
ÁÂÁÂ!!
§@ªÌ: ML089    ®É¶¡: 2015-7-16 22:02

¦^´_ 4# lifetw

¨S¦³°µ¥X¨Óªº½d¨Ò¥i¥H°Ñ¦Ò

¥u¯à²q·Q¡A½Ð¬Ý¬Ý¹ï¤£¹ï
[attach]21401[/attach]
§@ªÌ: lifetw    ®É¶¡: 2015-7-17 00:25

¦^´_ 5# ML089
MLª©¤j±z¦n
«D±`·PÁ±zªºÀ°¦£¸Ñ´b
¥u¬O§Ú©|¥¼¯à¤U¸üÀÉ®×
¥u¦nµ¥¤É¯Å¤~¯à¤U¸üÀÉ®×
«D±`ÁÂÁÂÀ°¦£!!
§@ªÌ: ML089    ®É¶¡: 2015-7-18 00:22

¦^´_ 6# lifetw


[attach]21407[/attach]
§@ªÌ: lifetw    ®É¶¡: 2015-7-18 16:59

¦^´_ 7# ML089
MLª©¥D¤j¤j±z¦n
·PÁÂÀ°¦£¡A
¦b­ì½d¨ÒÀÉok¡A¥u¬O§Úµ¹ªº½d¨ÒÀɸò­ìÀɦn¹³¤£¤Ó¦X
¦b¦hÄæ¦ì¸ê®Æ¿é¤J(=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",99)),COLUMN(A1)*99-98,99))¨S¤ÏÀ³¡A(¦pªþ¹Ï)
¤£ª¾¬O§_¨ºùØ­n§ï°Ê¡Aªþ¤W½d¨ÒÀÉtest2

¥t¥~±q³Ì·s¸ê®Æ©¹«á¤ñ¹ï³£²Å¦Xªº¥|­Ó¸¹½X¦ì¸m¡A¯à±N¶¡¹j¼ÆÅã¥Ü¥X¨Ó¶Ü?
¤ñ¦p104010754¥|­Ó¸¹½X²Å¦X¡A©¹«á¤ñ¹ï104010740¤S²Å¦X
«h±o¥X¶¡¹j¼Æ14

«D±`ÁÂÁÂÀ°¦£¡A·PÁÂ!!
§@ªÌ: lifetw    ®É¶¡: 2015-7-18 17:17

¦^´_ 8# lifetw
°Ú!!¤£¬OÄF¿n¤À¡A§O§O½Ð»~·|
><||
¯uªº§Ñ¤FªþÀÉ
§@ªÌ: ML089    ®É¶¡: 2015-7-18 22:54

¦^´_ 9# lifetw

G1 =TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",99)),COLUMN(F1)*99-98,99))
­Y­n¤À¸Ñªº¬OFÄæ
­×§ï¬°
G1 =TRIM(MID(SUBSTITUTE($F1,",",REPT(" ",99)),COLUMN(A1)*99-98,99))

COLUMN(A1) ªí¥Ü¨ú²Ä¤@­Ó¼Æ¦r
·í¤½¦¡¥k©Ô®É¤½¦¡¤¤ªºCOLUMN(A1)·|Åܦ¨COLUMN(B1)¡Aªí¥Ü¨ú²Ä¤G­Ó¼Æ¦r¡A¦A¤S©Ô·|Åܦ¨COLUMN(C1)¡Aªí¥Ü¨ú²Ä°Ñ­Ó¼Æ¦r
§@ªÌ: lifetw    ®É¶¡: 2015-7-19 11:12

¥»©«³Ì«á¥Ñ lifetw ©ó 2015-7-19 11:14 ½s¿è

¦^´_ 10# ML089
MLª©¥D¤j¤j±z¦n
·PÁÂÀ°¦£¡A
­ì¥»¸ê®Æ¤À´²¦hÄæ¡A©Ò¥H¦pªGcopy ¦Ü¦P¤@Äæ¡A´N4Äæ«Ý³B²z¡AÁÂÁÂ!!

=COUNT(0/(MMULT(--ISNUMBER(FIND(B1:E1,Sheet2!B$1:B$50)),{1;1;1;1})=4))
³o³¡¤À¯à½ÐMLª©¥D¤j¤j¶}¥Ü¤@¤U¶Ü?  COUNT­pºâ½d³ò¤¤§t¦³¼Æ¦rªºÀx¦s®æÁ`¼Æ¡ACOUNT¤§«á¬O¦p¦ó¤ñ¹ï¬Û¦P©O?

¬Ý¤F¦U¨ç¼Æ»¡©úÁÙ¬O«Ü¼Ò½k...¯S§O¬O{1;1;1;1}=4¡A¤£ª¾¦p¦ó¬d°_!!

³sµ²¬O·L³n©xºô»¡©ú¡AÀ³¸Ó¦w¥þ
FIND ¨ç¼Æ
ISNUMBER ¨ç¼Æ
MMULT ¨ç¼Æ
COUNT ¨ç¼Æ

«D±`ÁÂÁÂÀ°¦£¡A·PÁÂ!!
§@ªÌ: ML089    ®É¶¡: 2015-7-19 20:09

¦^´_ 11# lifetw

=COUNT(0/(MMULT(--ISNUMBER(FIND(B1:E1,Sheet2!B$1:B$50)),{1;1;1;1})=4))

¤½¦¡­pºâ¨BÆJ
¨BÆJ1¡A¥HB1:E1¬d¸ßSheet2!B$1:B$50¬O§_¦s¦b¡A²£¥Í4¦æ*50¦C°}¦C¸ê®Æ
FIND(B1:E1,Sheet2!B$1:B$50)

¨BÆJ2¡A¦³¬d¨ìªº¸ê®ÆÂର1¡A¨S¦³¬d¨ìªº¸ê®ÆÂର0
--ISNUMBER(FIND(B1:E1,Sheet2!B$1:B$50))

¨BÆJ3¡A±N4¦æ*50¦C°}¦C¸ê®Æ¨Ï¥ÎMMULTÂର1¦æ*50¦C¸ê®Æ
MMULT(--ISNUMBER(FIND(B1:E1,Sheet2!B$1:B$50)),{1;1;1;1})

¨BÆJ4¡A1¦æ*50¦C¸ê®Æ­Y¼Æ­È¬°4®Éªí¥Ü4­Ó¥þ³¡¦³¬d¨ì¡C
MMULT(--ISNUMBER(FIND(B1:E1,Sheet2!B$1:B$50)),{1;1;1;1})=4

¨BÆJ5¡A0/TRUE=0¡A 0/FALSE=#DIV/0!
0/(MMULT(--ISNUMBER(FIND(B1:E1,Sheet2!B$1:B$50)),{1;1;1;1})=4)

¨BÆJ6¡A COUNT §Q¥Î0/(...)­pºâ¥X¦³¦h¤ÖTRUEªº¼Æ¶q
=COUNT(0/(MMULT(--ISNUMBER(FIND(B1:E1,Sheet2!B$1:B$50)),{1;1;1;1})=4))

¤j­P¸ÑÄÀ¡A¤½¦¡»¡©ú¦Û¦æ¬d¸ßHELP
§@ªÌ: lifetw    ®É¶¡: 2015-7-19 23:59

¦^´_ 12# ML089
MLª©¥D¤j¤j±z¦n
¸Ñ»¡¤w¸g«D±`¸Ô²Ó¡A§Ú·|¥J²Ó¸ÑŪ¸Õ§@

«D±`ÁÂÁ¼·¤¾À°¦£¡A·PÁÂ!!!




Åwªï¥úÁ{ ³Â»¶®a±Ú°Q½×ª©ª© (http://forum.twbts.com/)