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

FREQUENCY°ÝÃD

FREQUENCY°ÝÃD

¦]¬°¤u§@Ãö«Y¡A¨C¤Ñ³£­n¨Ï¥ÎExcelµe¹Ï¡A¬°¤F¼W¥[¤u§@®Ä²v½Ð±Ð¦U¦ì¡A°ÝÃD¦p¤U

Range("a:b").Select
Selection.FormulaArray = _
"=FREQUENCY(x1:x2,y1:y2)"

½Ð°Ý­Y§Ú­n±NFREQUENCY¨ç¦¡¤¤x1:x2¦ì§}³]¬°ÅܼơA¸Ó¦p¦ó°µ?
§Úªº¥Øªº¬O­nµeª½¤è¹Ï¡A¦ýFREQUENCY(data_array,bins_array)¤¤ªºdata_array¬O·|Åܰʪº¡A
x1¬°©T©w,x2ªº¼Æ­È·|ÀHªÌ¸ê®Æ¦h¹è¦ÓÅÜ°Ê¡C

¥»©«³Ì«á¥Ñ luhpro ©ó 2013-9-10 20:44 ½s¿è
¦]¬°¤u§@Ãö«Y¡A¨C¤Ñ³£­n¨Ï¥ÎExcelµe¹Ï¡A¬°¤F¼W¥[¤u§@®Ä²v½Ð±Ð¦U¦ì¡A°ÝÃD¦p¤U

Range("a:b").Select
Sele ...
wes µoªí©ó 2013-9-10 00:00
  1. Range("a:b").Select
  2. Selection.FormulaArray = _
  3. "=FREQUENCY(INDIRECT(ADDRESS(1,24,4,1)):INDIRECT(ADDRESS(2,24,4,1))," & _
  4.            "INDIRECT(ADDRESS(1,25,4,1)):INDIRECT(ADDRESS(2,25,4,1)))"
  5. ' ADDRESS(¦C¸¹, Ä渹, 1=$X$1 2=X$1 3=$X1 4=X1, 0=R1C1 1=A1 ªí¥Ü¤è¦¡) -> ¨ú±o¦ì§}ªí¥Ü¤å¦r
  6. ' INDIRECT -> ¨ú±oªí¥Ü¦ì§}ªº¤å¦r©Ò¹ïÀ³ªºÀx¦s®æ°Ñ·Ó
½Æ»s¥N½X

TOP

¦^´_ 2# luhpro


·PÁ±zªº¦^ÂСA!¦ý¤S¸I¨ì¤F°ÝÃD¡A¦A¦¸½Ð±Ð±z,
Selection.FormulaArray = _
    "=FREQUENCY(INDIRECT(ADDRESS(2,9,1,1)):INDIRECT(ADDRESS(X,9,1,1))," & _
           "INDIRECT(ADDRESS(15,18,1,1)):INDIRECT(ADDRESS(85,18,1,1)))"

¦]¬°¨C¦¸¸ê®Æªºªø«×¬O·|Åܰʪº¡A¨Ò¦p»¡§Ú±NX­È©ñ¦bExcelªº"T4"¦ì¸m¡A§Ú¦p¦ó±NX­È±a¤J©O?

TOP

¦^´_ 1# wes
  1. Option Explicit
  2. Sub Ex()
  3.     Dim data_array As Range, Bins_array As Range
  4.     With Sheets("Sheet1")
  5.         Set data_array = .Range("X1", .Range("X1").End(xlDown))        'data_array¬O·|Åܰʪº¡Ax1¬°©T©w,x2ªº¼Æ­È·|ÀHªÌ¸ê®Æ¦h¹è¦ÓÅÜ°Ê¡C
  6.        .Range("a:b").FormulaArray = "=FREQUENCY(" & data_array.Address & ",y1:y2)"
  7.         '*************************************************************************
  8.         'Set Bins_array = .Range("y1", .Range("y1").End(xlDown))       '¦pBins_array¬O·|Åܰʪº¡Ay1¬°©T©w,y2ªº¼Æ­È·|ÀHªÌ¸ê®Æ¦h¹è¦ÓÅÜ°Ê¡C
  9.         '.Range("a:b").FormulaArray = "=FREQUENCY(" & data_array.Address & "," & Bins_array.Address & ")"
  10.         '***************************************************************************
  11.     End With
  12. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2013-9-11 21:00 ½s¿è
¦^´_  luhpro
·PÁ±zªº¦^ÂСA!¦ý¤S¸I¨ì¤F°ÝÃD¡A¦A¦¸½Ð±Ð±z,
Selection.FormulaArray = _
    "=FREQUENCY(INDIRECT(ADDRESS(2,9,1,1)):INDIRECT(ADDRESS(X,9,1,1))," & _
           "INDIRECT(ADDRESS(15,18,1,1)):INDIRECT(ADDRESS(85,18,1,1)))"
wes µoªí©ó 2013-9-10 22:17

¹ï©óÀx¦s®æªº FormulaArray ¨ç¼Æ¨Ó»¡,
Excel VBA ¥u­nµ¹¥¦¤@­Ó»P "Àx¦s®æ¤½¦¡" §¹¥þ¬Û¦Pªº¦r¦ê§Y¥i.

§A§â¦C¸¹©ñ¦b T4 ¤W, §Y¬O­n¥H T4 Àx¦s®æªº¤º®e°µ¬°¨ä¦C¸¹,
¬G¦Ó¦¹¨Ò³]©wÀx¦s®æ¤½¦¡À³§ï¬° :
  1. =FREQUENCY(INDIRECT(ADDRESS(2,9,1,1)):INDIRECT(ADDRESS(T4,9,1,1)),INDIRECT(ADDRESS(15,18,1,1)):INDIRECT(ADDRESS(85,18,1,1)))
½Æ»s¥N½X
¥ç§Y¥u­n§â X ¥Î T4 ¨Ó¨ú¥N´N¥i¥H¤F.

¥t¦b Excel VBA ¤¤,
¨â­Ó " ¤§¶¡§Y¬°¤@­Ó¦r¦ê,(¦Û«ü¥Oªº¶}ÀYºâ°_)
¦Ó¨â­Ó¦r¦ê¶¡«hÀ³¥Î & ¨Ó³sµ²¨Ï¦¨ ¦r¦êA & ¦r¦êB ªº§Î¦¡,
­Y·Q¦b¸Ó¦r¦ê¤¤´¡¤JÅܼƥi¥H¦b±ý´¡¤Jªº¦ì¸m¤W¥[¤W  " & ÅܼƦWºÙ & "
¨Ï¨äÅܦ¨ "¦r¦êÀY" & ÅܼƦWºÙ & "¦r¦ê§À" ªº§Î¦¡§Y¥i.

TOP

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