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

[µo°Ý] ½Ð°Ý¦p¦ó²Î­p¯Zªíªº¸ê®Æ(¥¿±`¤é¡B¥[¯Z¡B¬P´Á¤é)?

[µo°Ý] ½Ð°Ý¦p¦ó²Î­p¯Zªíªº¸ê®Æ(¥¿±`¤é¡B¥[¯Z¡B¬P´Á¤é)?

¥»©«³Ì«á¥Ñ gaishutsusuru ©ó 2021-10-22 23:01 ½s¿è

¤j®a¦n¡A
§Ú¦Û»s¤@±iªí¡A¦ý¦b­pºâ¤½¦¡¤W¤@ª½¸Õ¤£¥X¨Ó¡A¦]¦¹·Q½Ð±Ð¤j®a¦p¦ó­pºâ¯Zªíªº¸ê®Æ©O?

¥H¤U¬O¬ÛÃöªº²Ó¸`¡G

(1) ³o±i¹Ï¬°¤F»¡©ú¡A¦]¦¹¥uºI¹Ï1~10¤é (§¹¾ã31¤Ñªº¤é´Á¸ê®Æ¡A¦A·Ð½Ð¨£ªþÀɾާ@)
(2) ²Å¸¹»¡©ú¡G
¦­¯Z¡G¡uS¡v
¤¤¯Z¡G¡uC¡v
©]¯Z¡G¡uD¡v
¡uS+2¡v¡G¦­¯Z¡A¦³¥[¯Z2¤p®É
¡uC+1¡v¡G¤¤¯Z¡A¦³¥[¯Z1¤p®É
±Æ¥ð¡G¡u¥ð¡v
¡K¨ä¾l¥H¦¹Ãþ±À


§Æ±æ¯à§â¬õ¦r³o¨Ç¼Æ¦r¡A¥Î¤½¦¡­pºâ¥X¨Ó¡C
(1) ¥X¶Ô¦³¤À¦¨¡G¦­¯Z¤¤¯Z±ß¯Z (µù¡G¥X¶Ô¤£­p¤J¬P´Á¤é)
(2) ¥[¯Z¤p®É¡A¦³¤À¦¨¡G¥­±`¤é¥[¯Z¡B¬P´Á¤é¥[¯Z
(3) ¬P´Á¤é¦³¤À¦¨¡G¦­¯Z¤¤¯Z±ß¯Z

³Ì«á¡Aªþ¤WªþÀÉ¡G ·s¼W Microsoft Excel Worksheet.zip (7.9 KB)

§Æ±æ¤j®a¯à©âªÅÀ°¦£¨ó§U´£¨Ñ·Qªk¡A«D±`·PÁ¤j®a¡C(¦p¹ï°ÝÃD´y­z¦³¥ô¦ó¤£²M·¡¡AÅwªï¯d¨¥µ¹§Ú¡AÁÂÁÂ)

¤W¤U¯Z¤Î¥[¯Z²Î­p.rar (12.56 KB)

{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 2# ML089


­ì¨Ó¦p¦¹¡A¯uªº«D±`ÁÂÁÂML089¤j¤jªºÀ°¦£¡A¤½¦¡¥i¥H¥Î®@¡C:)

TOP

¦^´_ 3# gaishutsusuru
¸É¥R¤@¤U¡AÀx¦s®æÃC¦â³]©w¤è¦¡

¿ï¾Ü B2:AF3 ³]©w®æ¦¡¤Æ¡A¬P´Á¤é¤£¦PÃC¦â                       
        B2:AF3 ª½±µ³]©³¦â               
        B2:AF8 ³]©w®æ¦¡¤Æ¡A¤½¦¡ =AND(WEEKDAY(B$2,2)=7,COUNTA($B2:$AF2)>0) ¡A¶ñÃC¦â
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 4# ML089

ÁÂÁ±zªºÀ°¦£¡C

¦ý¦b¹ê°µ¤W¤S¹J¨ì¥t¤@­Ó°ÝÃD¡A¦]¦¹·Q¦A½Ð±Ð±z¡G

­Y¦Ò¼{¨ì¦­°hªº±¡ªp¡A¸Ó¦p¦ó³B²z©O?


1­Ó¯Z³£¬O8¤p®É¡A±¡ªpÁ|¨Ò¡G(+¥Nªí¥[¯Z¡A-¥Nªí¦­°h)
(1) S-1/8¡G¤W¦­¯Z¡A¦ý¦­°h1¤p®É
(2) S+2-2/8¡G¤W¦­¯Z¡A¦ý¦­°h2¤p®É¡A¦ý·í¤Ñ¤S¶]¨Ó¥[¯Z2¤p®É  (¹ê°È¤W¤Ö¨£¡A¦ý¤´¦³¥i¯à¥X²{)
¦]¦¹¡A§Æ±æ¯à§â¤½¦¡§ï¦¨¡A¥X²{¹³¬O¡u¶À¦â¿Ã¥úµ§¡vªº¼Æ¦r¡C
(§Ú¹Á¸Õ¥Î¤Fsearch¨Ó§PÂ_¡A§ï¼g¤½¦¡¡A¦ý¤@ª½¸Õ¤£¥X¨Ó)

§Æ±æ±z¯à©âªÅ´£¨ÑÀ°¦£¡AÁÂÁ±z¡C


µù¡G§Ú¬O¥Î±zªþªºÀɮ׶i¦æ½s¿èªº¡A¦]¦¹´N¥¼¦Aªþ¤WexcelÀɤF¡C

TOP

¦^´_ 5# gaishutsusuru

AH4 =SUMPRODUCT((LEFT($B4:$AF4,1)=MID(AH$3,4,1))*($B$3:$AF$3<>"¤é")) - SUMPRODUCT((LEFT($B4:$AF4,1)=MID(AH$3,4,1))*($B$3:$AF$3<>"¤é")*TEXT(MID($B4:$AF4,{2;4},2),"!0;0;;!0"))/8               
AK4 =SUMPRODUCT((LEFT($B4:$AF4,1)=MID(AH$3,4,1))*($B$3:$AF$3="¤é")) - SUMPRODUCT((LEFT($B4:$AF4,1)=MID(AH$3,4,1))*($B$3:$AF$3="¤é")*TEXT(MID($B4:$AF4,{2;4},2),"!0;0;;!0"))/8               
AN4 =SUMPRODUCT(TEXT(MID($B4:$AF4,{2;4},2),"0;!0;;!0")*($B$3:$AF$3<>"¤é"))               
Ao4 =SUMPRODUCT(TEXT(MID($B4:$AF4,{2;4},2),"0;!0;;!0")*($B$3:$AF$3="¤é"))               
AH4:AM5 Àx¦s®æ®æ¦¡ "# #/8"
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# ML089


¯uªº«D±`ÁÂÁÂML089¤j¤j¡A¤½¦¡¥i¥H¥Î¡A¯uªºÀ°§U§Ú«Ü¤j¡C

¥t¥~¡A·Q½Ð±Ð±z¡uTEXT(MID($B4:$AF4,{2;4},2),"0;!0;;!0"¡v³o­Ó¤½¦¡¸Ì­±ªº¡u{2;4}¡v¡B¡u"0;!0;;!0"¡vªº¥Î·N¬O¤°»ò©O? ³o­Ó¦b®Ñ­±¦n¹³´X¥G¨S¦³´£¨ì¡A¤£ª¾¹D¥i§_½Ð±z©âªÅ¸Ñ´b©O?


ÁÂÁ±z :)

TOP

¦^´_ 7# gaishutsusuru


    ¡uTEXT(MID($B4:$AF4,{2;4},2),"0;!0;;!0"¡v³o­Ó¤½¦¡¸Ì­±ªº¡u{2;4}¡v¡B¡u"0;!0;;!0"¡vªº¥Î·N¬O¤°»ò©O?
text( ... , "0;!0;;!0")   ªí¥Ü TEXT ªº®æ¦¡  "¥¿¼Æ; ­t¼Æ; 0; ¤å¦r"  ³B²z¤è¦¡¡A  !0 ªí¥Ü ¥ô¤@¼Æ³£¥H 0 Åã¥Ü¡A©Ò¥H "0;!0;;!0" ·N«ä±N¥¿¼Æ¯d¤U¡A¨ä¥L³£³]¬° 0

MID(... , {2;4},2) ³sÄò¨ú2­Ó¼Æ¦r¡A¦C
S+2-2/8 ·|¨ú¥X {"+2" ; "-2")  ---> "+2" µø¬° ¥¿¼Æ2 ; "-2"µø¬°­t¼Æ2
S-1/8 ·|¨ú¥X {"-1" ; "/8") ---> "-1" µø¬° ¥¿¼Æ1 ; "/8"µø¬°¤å¦r
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 8# ML089


­ì¨Ó¦p¦¹¡AÁÂÁÂML089¤j¤jªº¸Ñ´b¡AÁÂÁ±zªº©âªÅ¨ó§U¡C

TOP

        ÀR«ä¦Û¦b : µÊ®ð¼L¤Ú¤£¦n¡A¤ß¦a¦A¦n¤]¤£¯àºâ¬O¦n¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD