[µo°Ý] Excel ¸ê®Æ¤ñ¹ï°ÝÃD

missbox

¦U¦ìª©¤Wªº«e½ú­Ì~
¤p§Ì¦³¦³­ÓExcel¸ê®Æ¤ñ¹ï°ÝÃD·Q½Ð°Ý¤j®a

ªþ¥ó¦³ "²M³æ" ¤Î "®w¦s" ¨â­Ó¤u§@ªí
(¨C­Ó®Æ¸¹³£¬O³æ¤@¼Æ¶q)
¥H²M³æ¬°°ò·Ç¦³¿ìªk¤ñ¹ï¥X

1. ®w¦s¯Ê¤Öªº²M³æ®Æ¸¹»P¼Æ¶q
2. ²M³æ¤º¦³ªº®Æ¸¹®w¦s«o§ä¤£¨ìªº®Æ¸¹»P¼Æ¶q

ª¦¤åª¦¤F¤@¬q®É¶¡ÁÙ¬O§ä¤£¨ì¸Ñ¨M¤èªk
·Ð½Ð¦U¬°ª©¤j°ª¤âÀ°§Ú¬Ý¬Ý~·PÁ¦U¦ì

book1.zip (8.14 KB)

¤U¸ü¦¸¼Æ: 12

2012-5-8 10:59

missbox

¦^´_ 4# Hsieh

·PÁÂHsieh¤j
¬Ý°_¨Ó¦³ÂIªø»Ý­nÂI®É¶¡®ø¤Æ¤@¤U
¤£¹L¸Õ¥Î¹L«áªº½T¬O§Ú·Q­nªºµ²ªG
¦A¦¸·PÁÂ~~¥ý¨Ó¥h®ø¤Æ¤@¤Uµ²ªG¬O¦p¦ó²£¥Íªº!!:)

Hsieh

¦^´_ 3# missbox
²M³æ!C2=OFFSET(®w¦s!$B$1,IF(MOD(ROW(A1),16),MOD(ROW(A1),16),16),INT((ROW(A1)-1)/16)*3)&""
²M³æ!D2=IF(C2="","",INDIRECT("®w¦s!R"&SUMPRODUCT((®w¦s!$A$1:$T$17=--²M³æ!C2)*(ROW(®w¦s!$A$1:$T$17)))&"C"&SUMPRODUCT((®w¦s!$A$1:$T$17=--²M³æ!C2)*COLUMN(®w¦s!$A$1:$T$17))-1,0))
A»PCÄæ¥Î®æ¦¡¤Æ±ø¥óÅܦⴣ¿ô

¤U¸ü (211.85 KB)

2012-5-8 21:50

missbox

¦^´_ 2# Hsieh

·PÁÂHsieh¤jªº¦^ÂÐ~
¤£¹L»Ý­n¤ñ¹ï¨âÃ䪺¸ê®Æ
©Ò¥H°£¤FHsieh¤j©Ò»¡ªº³o¨Ç¥~
ÁÙ±o¤Ï¹L¨Ó¤ñ¹ï®w¦sªº¸ê®Æ
­ì¥ýªº°µªk¬O§â®w¦s¸ê®Æ«ö¶¶§Ç½Æ»s¨ì¥t¤@¤u§@ªí®æ¦¡»P²M³æ¤@¼Ë¦A°µ¤@¦¸¤ñ¹ï
¤ñ¹ïµ²ªG!B2=IF($A2="","",COUNTIF($D$2:$D$9,A2)=1)

¤U¸ü (5.94 KB)

2012-5-8 21:11

½Ð°ÝHsieh¤j~¯à¦³¤èªk§â®w¦s­¶­±ªº®æ¦¡¦Û°Ê±Æ§Ç¦p²M³æªº®æ¦¡¤@¼Ë¶Ü?

Hsieh

¦^´_ 1# missbox

¤£ª¾¹D¬O¤£¬O§A­nªº
²M³æ!B2=IF(COUNTIF(®w¦s!$A$2:$T$17,²M³æ!$A2)>0,"","µL")
   

¤U¸ü (20.4 KB)

2012-5-8 20:40