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

[µo°Ý] excelªº¤½¦¡+¨ç¼Æ­t²ü­«¡A¬O§_¦³¿ìªkºë²©O¡H

[µo°Ý] excelªº¤½¦¡+¨ç¼Æ­t²ü­«¡A¬O§_¦³¿ìªkºë²©O¡H

½Ð°Ý¦U¦ì¤j¤j¡G
¥Ø«eG2¬O¥Îexcelªº¤½¦¡©M¨ç¼Æ¼g¥X¨Óªº¡AÁöµM¥i¥H¨Ï¥Î¡A¦ýµ{¦¡ªº­t²ü«Ü­«¡A
¬O§_¦³¿ìªkºë²¡A¥B¡u¥uÅã¥ÜG2¡v©O¡H

G2¡÷·í¤U®É¶¡­Y²Å¦XC1®É¶¡¡A´N¥Î $A$2 x DÄæ ©Òºâ¥X¨Óªº¼Æ¦r¡C

Àµ½Ð¦U¦ì°ª¤â¤j¤j«ü¾ÉÀ°¦£¡I¡IÁÂÁ¡I¡I


°ÝÃD20190409.rar (8.84 KB)

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2019-5-9 09:54 ½s¿è

=LOOKUP(1,0/(MOD(B2,1)<=C:C),A2*D:D)
E F ¤½¦¡¥i¤£¥Î
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 2# hcm19522


    ·PÁÂhcm19522¤j¤jªº«ü¾É¡I

¡@¦A¦¸½Ð±Ðhcm19522¤j¤j¡G
¡@¦]¬°§Ú­ì¥»®É¶¡§Ç¦C¬O»¼´îªº¡A¦pªG­n§ï¦¨»¼¼Wªº
    C3  9:05
    C4  9:10
    C5 9:15
    ....¥H¦¹Ãþ±À

   °ÝÃD¤@¡G®É¶¡§Ç¦C(CÄæ)·Q§ï¦¨»¼¼W¡A¤½¦¡­n¦p¦ó­×§ï©O¡H
                   ­ì¤½¦¡ =LOOKUP(1,0/(MOD(B2,1)<=C:C),A2*D:D)

   °ÝÃD¤G¡G 0/MOD(B2,1)  ¬O¤°»ò·N«ä©O¡H¬°¤°»ò¬O¥Î¡u0/¡v©O?

TOP

¦^´_ 3# cslu37


    =OFFSET(D1,MATCH(MOD(B2,1),C:C),)*A2
0/±ø¥ó¦¨¥ß-->0    0/±ø¥ó¤£¦¨¥ß-->0/0-->¿ù»~   LOOKUP-->¨ú³Ì«á±ø¥ó¦¨¥ß­È
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2. Dim a, t
  3. a = Range("c1:d" & [c1048576].End(3).Row).Value
  4. t = [b2].Value
  5. For i = UBound(a) To 3 Step -1
  6.     If t < a(i, 1) Then Exit For
  7. Next
  8. [g2] = [a2] * a(i, 2)
  9. End Sub
½Æ»s¥N½X

zz.zip (13.93 KB)

TOP

¦^´_ 4# hcm19522


    ·PÁÂhcm19522¤j¤j­@¤ß¸Ô²Óªº»¡©ú¡A­t²ü§¹¥þ´î»´¤F¡A¯uªº¤ÓÁÂÁ±z¤F¡I¡I

TOP

¦^´_ 5# ikboy


·PÁÂikboyªºÀ°¦£~
¦ý¬O§Ú¶}±Ò±zªºzzÀɮסAG2Äæ¤@ª½³£Åã¥Ü0¡A¬O§Ú¬Y­Ó¨BÆJ¤Ö°µ¤F¶Ü><?

TOP

¦^´_ 7# cslu37


    ª½±µ±N²Ä4¦æ§ï¬°
  1. t = TimeValue(Time)
½Æ»s¥N½X

TOP

¦^´_ 8# ikboy


   ·PÁÂikboyªº«ü¾É¡IÁÂÁ±z¡I
   ·Q½Ð±Ðikboy¤j¤j¡A­ì¥»Àɮתº®É¶¡¬O"­Ë§Ç"¡A­n§ï¦¨"¥¿§Ç"¡A
   §Ú±N²Ä¤­¦æ³Ì«á¡u-1¡v§ï¦¨¡u1¡v¡A²Ä¤»¦æªº¡u<¡v§ï¦¨¡u>¡v¡AµM«á...´N¤£·|°Ê¤F...
   Àµ½Ð¤j¤j«ü¾É¡A¥¿§Çªº¥¿½T­×§ï¤è¦¡¡AÁÂÁ±z¡I

   zz-1.rar (15.3 KB)

TOP

  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2. Dim a, t
  3. a = Range("C1:D" & [C1048576].End(3).Row).Value
  4. t = TimeValue(Time)
  5. For i = UBound(a) To 3 Step -1
  6.     If t > a(i, 1) Then Exit For
  7. Next
  8. [G2] = [A2] * a(i, 2)
  9. End Sub
½Æ»s¥N½X

TOP

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