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

[µo°Ý] ¸ê®Æ¬Û¹j¦C¼Æ¤£¤@¼Ëªº¸ê®Æ¾ã²z

[µo°Ý] ¸ê®Æ¬Û¹j¦C¼Æ¤£¤@¼Ëªº¸ê®Æ¾ã²z

¦U¦ì¦­
¥»¤H¦³¤@¦Û¨t²Î¿é¥XªºEXCELÀÉ, ¨C¤@¦ì­û¤u§¡¦³¤£¦P¦C¼Æ ªº¸ê®Æ, ¬G¦¹¤£ª¾«ç¼Ë¨ú±o¸ê®Æ. ½Ð°Ý¥Î¬Æ»ò¨ç¥i¥H¾ã²z¸ê®Æ¦pªþ¥ó.

course.rar (6.49 KB)

¦³³Ò.

¦^´_ 1# missbb
J3°}¦C¤½¦¡
=IFERROR(LOOKUP(2,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0),)=J$2),OFFSET($A$1,MATCH($I3,$B:$B,0),1,MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0),)),"")
K3°}¦C¤½¦¡
=IFERROR(LOOKUP(2,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0),)=J$2),OFFSET($A$1,MATCH($I3,$B:$B,0),4,MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0),)),"")
¦V¥k¦V¤U½Æ»s
course.zip (8.81 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh

¦hÁ¸ѵª.½Ð°Ý    LOOKUP(2,1/..... ¤ÎLOOKUP(1,1/.....ªº¥Îªk¬O¬Æ»ò?

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2014-3-11 14:44 ½s¿è

¤½¦¡¤ÓÃø¤F, ¥u¦n¥ÎVBA ¸Õ¸Õ¬Ý:
  1. Option Explicit
  2. Private Sub CommandButton1_Click()
  3.     Dim start1, end1, ªÅ¥Õ¦C As Long, Rng, findcell As Range
  4.     Dim cour As Integer
  5.     start1 = 1
  6.    
  7.     '[A65536].End(xlUp)¡÷¥Ñ¤U©¹¤W§ä, ª½¨ì§ä¨ì«DªÅ¥Õ®æ¬°¤î
  8.     end1 = [A65536].End(xlUp).Row
  9.     Do
  10.         Set Rng = Cells(start1, 1).Resize(end1 - start1 + 1, 1)
  11.               Set findcell = Rng.Find(What:="name", _
  12.               After:=Cells(start1, 1), _
  13.               LookIn:=xlValues, _
  14.               LookAt:=xlPart).Offset(1, 0)
  15.         
  16.         If Not findcell Is Nothing Then
  17.             ªÅ¥Õ¦C = [i65536].End(xlUp).Row + 1
  18.             Cells(ªÅ¥Õ¦C, 9) = findcell.Offset(-1, 1)
  19.             Do               
  20.                 '°²©w³Ì¦h¥u¦³9¬ì¤~¦¨¥ß
  21.                 cour = Val(Mid(findcell, 7, 1))
  22.                 Cells(ªÅ¥Õ¦C, 9).Offset(0, cour * 2 - 1) = findcell.Offset(0, 1)
  23.                 Cells(ªÅ¥Õ¦C, 9).Offset(0, cour * 2) = findcell.Offset(0, 4)
  24.                 Set findcell = findcell.Offset(1, 0)
  25.             Loop Until findcell = ""
  26.         End If
  27.         start1 = findcell.Row
  28.     Loop Until findcell Is Nothing Or start1 > end1
  29. End Sub
½Æ»s¥N½X

TOP

¦^´_ 3# missbb
OFFSET($A$1,MATCH($I3,$B:$B,0),,MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0),)
­pºâ¥X¨C­Ónameªº½d³ò
¨ä¤¤MATCH($I3,$B:$B,0)§ä¨ìnameªº¦C¦ì¸m
MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0)
±qname©¹¤U1000¦Cªº½d³ò­Yµ¥©óªÅ¥Õ¡A³o¼Ë¶Ç¦^1000­ÓTRUE»PFLASEªºµ²ªG
¥Î1¥h°£¥H³o1000ÅÞ¿è­È¡A«h²£¥Í1000­Ó1»P#DIV/0!
¥ÎMATCH §ä¨ì²Ä1­Ó1¡A´N¬O²Ä¤@­ÓªÅ¥Õ¦ì¸m
1°£¥H³o½d³òµ¥©ó³øªí²Ä¤G¦CªºÅÞ¿è­È¡A¥u·|¶Ç¦^¤@­Ó¹ïÀ³course¦¨¬°1/1
¥ÎLOOKUP´M§ä³o½d³ò¤º³Ì«á¤@­Ó¼Æ­È´N±o¨ì¸Ó¦ì¸m
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# Hsieh
ª©¤j§A¦n:
§Ú¤U¸ü2fªºÀÉ®×, ¥´¶}µ²ªG¦p¤U:

±N "_xlfn." ®ø¥h«á, ¤]¥¼¨£§ïµ½, ¬O¥»ª©ªº°ÝÃD¶Ü? ÁÂÁÂ!!
(§Úªº¬OExcel 2003)

TOP

¦^´_ 1# missbb

J3 =IFERROR(VLOOKUP(J$2,OFFSET($A$1,MATCH($I3,$B:$B,),,IF($I4="",99,MATCH($I4,$B:$B,)-MATCH($I3,$B:$B,)),5),2,),"")
K3 =IFERROR(VLOOKUP(J$2,OFFSET($A$1,MATCH($I3,$B:$B,),,IF($I4="",99,MATCH($I4,$B:$B,)-MATCH($I3,$B:$B,)),5),5,),"")
¦P®É¿ï¨úJ3:K3¡A¥k©Ô¤U©Ô½Æ»s¤½¦¡
   
­YÀ´°}¦C¥i±N¤W2¦¡¦X¨Ö¬°¤@¦¡¸û¬°ºë²
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

course3.rar (20.06 KB) ¦^´_ 5# Hsieh

¤j¤j§A¦n, §Ú±NLOOKUP¯u¥¿©ñ¤J§Ú­n³B²zªºÀÉ®×(¬O¬Û¹ï¸û¶Ãªº), ¦ý«ç¼Ë¤]¤£¯à§¹¦¨. ½Ð«ü¾É.

¦³³Ò.

TOP

¦^´_ 6# yen956

§A¬O2003ª©¥»§a¡H
¦]¬°iferror¬O2007¥H«á¤~¦³ªº·s¨ç¼Æ
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 9# Hsieh
ª©¤j§A¦n, ¬Oªº§Úªº¬O2003, ÁÂÁ¦^ÂÐ!!

TOP

        ÀR«ä¦Û¦b : §g¤l¥ß«í§Ó¡A¤p¤H«í¥ß§Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD