-ee -er
前言 (Preface)
This Article is a replacement of http:/A_1788-Getting-your-EE-Ranking-statistics-in-Excel.html. Changes in the way Experts Exchange delivers point statistics, implemented in July 2010, made this complete overhaul necessary, and I decided to spawn a new Article for the sake of clarity and reference.
本文替代了http：/A_1788-Getting-your-EE-Ranking-statistics-in-Excel.html 。 自2010年7月起，由于Experts Exchange提供点统计信息的方式发生了变化，因此有必要进行全面的检查，为了清晰和参考起见，我决定生成新的文章。
I'm trying to (re-)cover all areas of the predecessor first, for all those of you who did not use the Excel sheet before. So that sections can be read diagonally for the "experienced users" (I tried to keep it as close as possible to the predecessor to make that easy).
对于所有以前没有使用过Excel工作表的人，我想先（重新）介绍其前身的所有方面。 这样一来，对“经验丰富的用户”就可以对角线阅读各节（为了使这一点变得容易，我尝试使其尽可能靠近前辈）。
Then I'll show the differences, and how to incorporate the valuable historical data obtained previously and no longer available yet.
然后，我将展示差异，以及如何合并以前获得的宝贵历史数据，并将不再提供这些数据。
Since VBA is required, the method described will not work for Office 2008 for Mac, which has no VBA. The only way to have a similar result is to create the required Web Queries yourself, and replace some of the code with Excel formulas - doable, but very time consuming to set up.
由于需要VBA，因此所描述的方法不适用于没有VBA的Office 2008 for Mac 。 产生类似结果的唯一方法是自己创建所需的Web查询，并用Excel公式替换一些代码-可行，但设置起来非常耗时。
目标 (Target)
For all Experts out there who keep a record of the points they get, and do that manually, here is a way how to obtain the data automatically - as far as possible.
对于在那里记录获得的积分并手动进行的所有专家，这是一种尽可能自动地获取数据的方法。
Because of the nature of the new ranking statistic pages, you need to get them in time. You cannot go back one week or month or year anymore, as it was possible before the major change in July 2010. Hence it is important to collect data regularly, else you get inaccurate statistics. If that happens, just adjust the previously retrieved data as far as you can and need. E.g. if you have all statistics, and missed points of the last days of the month, you can obtain the corrections by substracting the recent month values from the monthly or yearly ones to get the previous.
由于新的排名统计页面的性质，您需要及时获取它们。 您再也不能回溯一周，一个月或一年，因为在2010年7月进行重大更改之前，这是有可能的。因此，定期收集数据非常重要，否则您将获得不准确的统计信息。 如果发生这种情况，则可以根据需要尽可能地调整以前检索的数据。 例如，如果您拥有所有统计信息，并且错过了该月最后几天的得分，则可以通过从月度或年度值中减去最近一个月的值以获取前一个值来获得更正。
The technique used here is based on Web Queries, which pull table-like data from Web pages.
此处使用的技术基于Web查询，Web查询从Web页中提取表式数据。
The framework I provide here as an Excel Worksheet will allow for filling in as much data as possible. To keep track of overall points, which are not the total of all zone points as you certainly know, you have still to query and enter it manually for past periods. There is no magic wand used, and hence nothing you can't do on your own - with one exception: the JavaScript methods used do not allow for using Web Queries. The Web Query compatible URL used instead is not supported by EE, but works great.
我在此处作为Excel工作表提供的框架将允许填写尽可能多的数据。 要跟踪总点（ 不是您肯定知道的所有区域点的总和），您仍必须查询并在过去的一段时间内手动输入。 没有使用魔术棒，因此您无法自己做任何事情-除了一个例外：使用JavaScript方法不允许使用Web查询。 EE不支持使用Web Query兼容的URL，但是效果很好。
You should need only basic Excel skills to use this spreadsheet (though understanding will need some more skills, but it is not really difficult).
您只需要具备基本的Excel技能即可使用此电子表格（尽管理解将需要更多的技能，但这并不是很难）。
Important:
重要：
Remember to have the "Remember Me" setting enabled in your web login to Experts Exchange via Internet Explorer. Otherwise, you will get no data at all (only members are allowed to see the ranking tables).
切记在通过Internet Explorer到Experts Exchange的Web登录中启用“记住我”设置。 否则，您将一无所获（仅允许成员查看排名表）。
工作表 (The Worksheets)
The Workbook as provided here contains no data. Some of the screenshots shown below contain a small subset of real-life data (of me), but are for visualization purpose only.
此处提供的工作簿不包含任何数据。 下面显示的某些屏幕快照包含一小部分现实生活数据（属于我），但仅用于可视化目的。
The following worksheets are the "important" ones:
以下工作表是“重要”的工作表：
Setup contains some setup data, like your Expert name, number of entries to read, and some more we will cover later. The buttons you should use to auto-fill of the other worksheets are located here, too.
设置包含一些设置数据，例如您的专家名称，要读取的条目数，以及我们稍后将介绍的更多数据。 用于自动填充其他工作表的按钮也位于此处。 总体将填充每个区域中达到的所有时间点的数据。
排名需要进一步的解释... (Ranking needs some further explanation...)
Besides the data that is filled out semi-automatically based on the formulas referring to months' worksheets (shown for an example matrix of 3 zones), it allows for manual record keeping of overall statistics not retrievable automatically. That is, only data for the current month and year can be queried using the Web interface, and if you haven't been quick enough and waiting too long, last month's data is unreachable. I will describe in detail what is done automatically, what you have to do first, and what to do for the unknown values of the past. In short, columns B to R of the current month, year and total are kept up-to-date by code.
除了基于引用月份工作表的公式（以3个区域的示例矩阵所示）为基础的半自动填充数据外，它还允许手动记录无法自动检索的总体统计信息。 也就是说，可以使用Web界面查询当前月份和年份的数据，如果您不够快且等待时间太长，则无法访问上个月的数据。 我将详细描述自动完成的操作，首先要执行的操作以及如何处理过去的未知值。 简而言之，当前月份，年份和总数的B到R列通过代码保持最新。
The % columns stand for "Success rate". 95% would mean you are a top expert, with 95% of all contributing (!) members ranked worse than you. In other words, you are one of the 5% of top experts.
％列代表“成功率”。 95％的人表示您是顶尖专家，所有（！）会员中有95％的排名比您差。 换句话说，您是5％的顶级专家之一。
The Points column in Overall area allows for Overall milestones. As an example I have added conditional formatting, which colours the cell green on each 500 000 points boundary. The conditional formatting here should show just the idea how to use formulas to colour up your spreadsheet.
“ 总体”区域中的“ 积分”列可用于“总体”里程碑。 作为示例，我添加了条件格式，该格式在每个500 000点边界上将单元格显示为绿色。 这里的条件格式应该只显示如何使用公式为电子表格着色的想法。
Those example columns for zones, starting at column T, you can see in the "Ranking Example Zones" screenshot have to be adapted to your needs. The first three are referring to the corresponding month's worksheet, looking up for the zone name noted in row 2 (exact match needed). Row 3 is a short name you can choose arbitrary; I usually hide row 2 and display only the short name.
您可以在“排名示例区域”屏幕快照中看到从T 列开始的区域示例列 。 前三个参考相应月份的工作表，查找第2行中注明的区域名称（需要完全匹配）。 第3行是一个短名称，您可以选择任意名称； 我通常隐藏第2行，只显示短名称。
You will also see some numbers in row 1 of each individual zone. That is the One-Year-Average of that zone, used in last row to get the approximated days for getting the next rank in that zone. Just a teaser ;-). The area OneYear is adapted automatically to the previous 12 months when you retrieve monthly statistics. At the beginning this area will be less than 12 months for the lack of data.
您还将在每个单独区域的第1行看到一些数字。 那是该区域的一年平均，在最后一行中用于获取获得该区域下一个排名的大概天数。 只是一个预告片;-)。 当您检索每月统计信息时， OneYear区域会自动适应之前的12个月。 最初，由于缺乏数据，该区域将少于12个月。
The "Rank of next rank" numbers below show in which probable order you will receive the next certificate, based on the one-year average in the respective zone. It will only display the Top Ten (the remaining ranks are made white on white to hide them).
下面的“下一等级的排名”数字显示了根据相应区域的一年平均值，您将以哪种可能的顺序获得下一证书。 它将仅显示前十名（其余的等级以白底白字隐藏）。
The last 3 columns are for zone point aggregation. That helps for calculating the point in time you got a new rank, a million, or similar, in a zone. They are referring to the values in the corresponding columns of this worksheet by simple cell value accumulation.
最后三列用于区域点聚合。 这有助于计算您在区域中获得新排名，一百万或类似等级的时间点。 他们通过简单的单元格值累加来引用此工作表相应列中的值。
You usually will put your own zones of interest in both the month and total area, which should be easy as you can just copy and insert the example columns, and change the zone names in rows 2 and 3.
通常，您会在月份和总面积中都设置自己感兴趣的区域，这很容易，因为您只需复制并插入示例列，然后更改第2行和第3行中的区域名称即可。
The "previous" row is the subtotal of all values which are not retrieved. The white area needs to be filled out manually to make the statistics correct. This is most important for the last three example columns. The grey areas are not filled out usually.
“上一个”行是未检索的所有值的小计。 需要手动填写白色区域以使统计信息正确。 对于最后三个示例列，这是最重要的。 通常不填写灰色区域。
The Zones column is just to keep track of how broad your pointing is spread over different zones. It counts values in the corresponding monthly worksheet - very simple, if you know how.
“ 区域”列只是为了跟踪您的指针在不同区域的分布范围。 它会在相应的每月工作表中计数值-如果您知道怎么做的话，非常简单。
如何自动填充 (How to Auto-Fill)
First, you will have to enter the Expert name. Open the Setup worksheet, and type the name in the empty green cell. If you forget this step - never mind, the VBA code checks for it, and will make it really obvious that you should enter something.
首先，您必须输入专家名称。 打开安装程序工作表，然后在空白的绿色单元格中键入名称。 如果您忘记了此步骤-没关系，VBA代码会对其进行检查，并且很明显您应该输入一些内容。
If you like, you can adjust the two green "Max Zones" cell values below the Expert Name, which limit the number of Overall Zone Ranking and Monthly Zone Ranking zones retrieved. Usually I set this to something small for quick updates, and at the beginning of a new month to a high value to get the final monthly stats.
如果愿意，可以在“专家名称”下方调整两个绿色的“最大区域”单元格值，以限制检索到的“总体区域排名”和“每月区域排名”区域的数量。 通常，我将其设置为较小的值以进行快速更新，并在新月初将其设置为较高的值以获取最终的每月统计信息。
The other two settings are discussed later.
其他两个设置将在后面讨论。
Well, the time has come to let the "magic" work. Press the topmost button ("All in One"), and after a short time you should see the worksheets coming into live. Each worksheet is shown while the code works on it as visual feedback.
好吧，是时候让“魔术”发挥作用了。 按下最上面的按钮（“多合一”），片刻之后，您应该会看到工作表生效。 显示每个工作表，同时代码作为视觉反馈在其上运行。
The "All in One" button retrieves all numbers of the current month and year, including the Member Ranking statistics available. This will be the button you use most often. A generated worksheet will look like this:
“所有合一”按钮可检索当前月份和年份的所有数字，包括可用的会员排名统计信息。 这将是您最常使用的按钮。 生成的工作表将如下所示： 其他按钮用于启动单个操作，例如，如果您只想更新总体工作表或会员排名。 右侧的按钮可让您在最近一个月或总体贡献的每个区域中的最后排名。 因为如果您在100的5或1000的10中排名，会有所不同；-)。
Instead of using the buttons, you are free to call the corresponding macros, or use the VBA Immediate Pane to start actions:
您可以调用相应的宏，也可以使用VBA即时窗格开始操作，而无需使用按钮：
- open VBA Editor ([Alt F11]), 打开VBA编辑器（ [Alt F11] ），
- open Immediate Pane ([Ctrl G]), 打开立即窗格（ [Ctrl G] ），
- and type 和类型
call getPointsOverall
This will be the same as pressing the "All in one" button.
这与按下“多合一”按钮相同。
I recommend fixing the values in the Ranking worksheet from time to time. The formulas used there have no meaning for static data, and the monthly sheets are static for the past - they cannot change once the month is gone, with exception of you adjusting them manually to apply e.g. revoked points when a question is reopened.
我建议您不时修改排名工作表中的值。 此处使用的公式对于静态数据没有意义，并且月度表在过去是静态的-一旦月度过去，它们便无法更改，除非您手动调整它们以应用（例如，当问题重新打开时被撤销的点）。
To remove the formulas, select the range you want to have the formulas removed, and call the Formula2Value macro (or press Ctrl-Shift-X). This will speed up Excel when changes are made, and reduce the file size.
若要删除公式，请选择要删除公式的范围，然后调用Formula2Value宏（或按Ctrl-Shift-X键）。 进行更改时，这将加快Excel的速度，并减小文件大小。
Advanced users might want to integrate this step into the automation.
高级用户可能需要将此步骤集成到自动化中。
You might wonder about the TopicIDs sheet. It is needed for determining the TAID (zone ID) for Member and Zone Ranking calls. While they result in zone names they expect TAIDs for filtering. As noted in that sheet, the list of zones and their IDs is retrieved from http://www.ee-stuff.com/zonelist.php. If there should be a general zone revision just retrieve that list again, e.g. by using a WebQuery - you should now know how to do that ;-).
您可能想知道TopicIDs表。 确定成员和区域等级呼叫的TAID（区域ID）是必需的。 尽管它们产生区域名称，但它们期望TAID进行过滤。 如该工作表中所述，可以从http://www.ee-stuff.com/zonelist.php检索区域及其ID的列表。 如果应该进行常规区域修订，则只需再次检索该列表即可，例如使用WebQuery-您现在应该知道该怎么做了；-)。
幕后守则 (The Code behind the scenes)
This section is for the more advanced users, and not necessary for utilizing the workbook. If you want to understand how all the stuff is done, read this section.
本部分适用于高级用户，而对于使用工作簿则不是必需的。 如果您想了解所有工作的完成方式，请阅读本节。
Option Explicit
Sub getPointsOverall()
If Not WhoAmIFilled Then Exit Sub
shOverall.Activate
SetCalc False
GetWebData shOverall.Cells(2, 1), [maxzones].Value
ReSortZoneRankingData shOverall, [maxzones].Value
SetCalc True
End Sub
Sub getPointsMonth()
If Not WhoAmIFilled() Then Exit Sub
Dim pot As Date
pot = Now() - 2 - [TZOffset] / 24
SetCalc False
getMonthZoneRanks month(pot), year(pot)
ReSortZoneRankingData ActiveSheet, [maxMonthZones].Value
shRanking.Activate
Dim oystart, oyend, offs As Integer
offs = DateDiff("m", Range("A5").Value, pot) - 1
oystart = Range("A5").row + WorksheetFunction.Max(0, offs - 12)
oyend = Range("A5").row + offs
Range(Cells(oystart, 1), Cells(oyend, 1)).EntireRow.Name = "OneYear"
SetCalc True
End Sub
Sub getMemberRanking()
Dim URL As String
Dim qt As QueryTable
URL = "http://e-e.com/shared/async/expertsZone/memberRank.jsp?expertName=" & [WhoAmI] & "&m_typeID=" & [StatsType]
On Error Resume Next
If [ScratchArea].Name = "" Then [Setup!G1].Name = "ScratchArea"
On Error GoTo 0
If [ScratchArea].Count = 1 Then
Range([ScratchArea].Address, [ScratchArea].Offset(10, 4).Address).Name = "ScratchArea"
End If
[ScratchArea].Parent.Activate
Set qt = [ScratchArea].Parent.QueryTables.Add(Connection:="URL;" & URL & "&m_periodID=0", Destination:=[ScratchArea])
With qt
.WebTables = 1
.FillAdjacentFormulas = False
.PreserveFormatting = True
.AdjustColumnWidth = False
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = False
.SaveData = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.Refresh
End With
' Some preparation for further processing
With Application
.DecimalSeparator = "."
.ThousandsSeparator = "·"
.UseSystemSeparators = False
End With
With [ScratchArea]
.Offset(0, 1).Name = "Lookup"
' Copy rank to the end, else we cannot use VLookup
.Range("E1:E11").Formula = "=" & [ScratchArea].Columns(1).Address
' Filter Expert in result rows
.Range("A13:A15").Formula = "= IF(ISNA(VLOOKUP(WhoAmI, Lookup, 4, FALSE)), 0, VLOOKUP(WhoAmI, Lookup, 4, FALSE))"
.Range("B13:B15").Formula = "= IF(ISNA(VLOOKUP(WhoAmI, Lookup, 1, FALSE)),"""", VLOOKUP(WhoAmI, Lookup, 1, FALSE))"
.Range("C13:C15").Formula = "= IF(ISNA(VLOOKUP(WhoAmI, Lookup, 2, FALSE)),"""", VLOOKUP(WhoAmI, Lookup, 2, FALSE))"
.Range("D13:D15").Formula = "= IF(ISNA(VLOOKUP(WhoAmI, Lookup, 3, FALSE)), 0, VLOOKUP(WhoAmI, Lookup, 3, FALSE))"
.Range("E13").Value = "Overall"
.Range("E14").Value = "Yearly"
.Range("E15").Value = "Monthly"
End With
' Now retrieve Overall, Year, Month
[ScratchArea].Range("A13:E13").Select
qt.Refresh
Call Formula2Value
[ScratchArea].Range("A14:E14").Select
qt.Connection = "URL;" & URL & "&m_periodID=1"
qt.Refresh
Call Formula2Value
[ScratchArea].Range("A15:E15").Select
qt.Connection = "URL;" & URL & "&m_periodID=2"
qt.Refresh
Call Formula2Value
' Now tricky bit - get last rank by using very high starting rank
' URL = "http://www.experts-exchange.com/experts.jsp?etIndex=4"
URL = "http://e-e.com/shared/async/expertsZone/memberRank.jsp?m_typeID=" & [StatsType] ' 0: All, 10: Question, 30: Article
With [ScratchArea].Range("F13")
.Select
qt.Connection = "URL;" & URL & "&m_periodID=0&hofStart=1000000"
qt.Refresh
.Value = [ScratchArea].Range("A2")
End With
With [ScratchArea].Range("F14")
.Select
qt.Connection = "URL;" & URL & "&m_periodID=1&hofStart=1000000"
qt.Refresh
.Value = [ScratchArea].Range("A2")
End With
With [ScratchArea].Range("F15")
.Select
qt.Connection = "URL;" & URL & "&m_periodID=2&hofStart=1000000"
qt.Refresh
.Value = [ScratchArea].Range("A2")
End With
' Cleanup
[Lookup].Name.Delete
[ScratchArea].ClearContents
qt.Delete
' Now we enter this into the Ranking worksheet
With [Ranking!A5].Offset(DateDiff("m", [Ranking!A5], Now - 2 - [TZOffset] / 24))
.Range("B1").Value = Replace([ScratchArea].Range("D15").Value, ",", "")
.Range("C1").Value = [ScratchArea].Range("A15").Value
.Range("D1").Value = Replace([ScratchArea].Range("F15").Value, ",", "")
.Range("K1").Value = Replace([ScratchArea].Range("D14").Value, ",", "")
.Range("L1").Value = [ScratchArea].Range("A14").Value
.Range("M1").Value = Replace([ScratchArea].Range("F14").Value, ",", "")
.Range("O1") = Replace([ScratchArea].Range("D13").Value, ",", "")
.Range("P1") = [ScratchArea].Range("A13").Value
.Range("Q1") = Replace([ScratchArea].Range("F13").Value, ",", "")
End With
Application.UseSystemSeparators = True
shRanking.Select
End Sub
Sub getOverallZoneRanking()
Call getZoneRanking("Overall", 0)
End Sub
Sub getMonthZoneRanking()
Call getZoneRanking(year(Now() - 2 - [TZOffset] / 24) & " " & month(Now() - 2 - [TZOffset] / 24), 2)
End Sub
' --- Helper routines ---
Sub Formula2Value()
Dim c As Range
SetCalc False
ActiveSheet.EnableCalculation = False
For Each c In Selection
c.Formula = c.Value
Next c
SetCalc True
End Sub
Sub SetCalc(calc As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
ws.EnableCalculation = calc
Next ws
End Sub
Function WhoAmIFilled() As Boolean
Dim ws As Worksheet
With [WhoAmI]
If .Value = "" Then
shSetup.Activate
.Activate
.Interior.Color = RGB(255, 0, 0)
.Offset(, 1).Value = "<--- Please enter your EE Member name!"
Else
.Interior.Color = .Offset(1, 0).Interior.Color
.Offset(, 1).Clear
End If
End With
WhoAmIFilled = [WhoAmI].Value <> ""
End Function
Sub GetWebData(rng As Range, zones As Long, Optional month As Integer = 0, Optional year As Integer)
Dim URL As String
URL = "http://e-e.com/shared/async/expertsZone/zoneRank.jsp?etIndex=3&expertName=" & [WhoAmI].Value _
& "&typeID=" & [StatsType] & "&zoneID=0&subZoneID=0&zrOrderBy=-1&zrSort=-1"
' All: typeID=0 Q's only: typeID=10 Articles: typeID=30
If month <> 0 Then URL = URL & "&periodID=2" Else URL = URL & "&periodID=0" ' no specific month or year supported anymore
zones = Int(zones / 10 + 1) * 10
With Application
.DecimalSeparator = "."
.ThousandsSeparator = "·"
.UseSystemSeparators = False
End With
Dim i As Long
For i = 1 To zones Step 10
With rng.Parent.QueryTables.Add(Connection:="URL;" & URL & "&zrStart=" & i, Destination:=rng.Cells(1.1 * i, 1))
.Name = "zoneRank"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = 1
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh
End With
If i > 1 Then
If rng.Cells(i * 1.1 + 1, [Zone].Column).Value = rng.Cells(i * 1.1 - 1, [Zone].Column).Value Then
rng.Range(Cells(i * 1.1 + 1, 1), Cells(i * 1.1 + 1, 5)).Value = ""
Exit For
End If
End If
If rng.Cells((i + 10 - 1) * 1.1, 2).Value = "" Then Exit For
Next
zones = i + 9
For i = zones * 1.1 To 2 Step -1
With rng.Cells(i, 4)
If .Value = "No Certificates" Then .Value = " "
If .Value = "Overall Certification Level" Then
If i > 2 Then
rng.Rows(i).Hidden = True
End If
.Offset(0, 1) = ""
.Offset(0, 5) = ""
Else
' This is for all those countries where a comma is not a thousands separator ...
.Offset(0, 1).Value = Replace(.Offset(0, 1).Value, ",", "")
.Offset(0, -3).Value = Replace(Replace(.Offset(0, -3).Value, ",", ""), ".", "")
rng.Cells(i, [Rank].Column).Value = rng.Cells(i, 1).Value
End If
End With
Next
Dim qt As QueryTable
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Application.UseSystemSeparators = True
End Sub
Sub ReSortZoneRankingData(ws As Worksheet, zones As Long)
Dim row, c As Range
Dim tmp2 As Integer
Set row = [3:3]
While row.Cells(, 2) <> "" And row.row < zones * 1.1 + 2
row.Select
If row.Cells(, 5) <> "" Then
Set c = [ZoneRK].Find(row.Cells(, ws.Range("Zone").Column), , xlValues, xlWhole, xlByRows, xlNext)
If c Is Nothing Then
' Zone rank info gets lost now for simplicity
' since we can't do an exchange of row data ...
row.Cells(, [Ranks].Column).ClearContents
row.Cells(, [ZoneRK].Column).ClearContents
ElseIf c.row > row.row Then
tmp2 = row.Cells(, [Ranks].Column)
row.Cells(, [Ranks].Column) = c.Offset(, -1).Value
c.Offset(, -1) = tmp2
c = row.Cells(, [ZoneRK].Column)
End If
End If
row.Cells(, [ZoneRK].Column) = row.Cells(, [Zone].Column)
Set row = row.Offset(1)
Wend
[A1].Select
End Sub
Sub getMonthZoneRanks(month As Integer, year As Integer)
If Not WhoAmIFilled() Then Exit Sub
Dim ws As Worksheet, r As Long
On Error Resume Next
Set ws = Worksheets(year & " " & month)
On Error GoTo 0
If ws Is Nothing Then
Call shMonth.Copy(After:=shOverall)
Set ws = Worksheets("Month (2)")
ws.Name = year & " " & month
ws.Tab.Color = RGB(255, 0, 0)
End If
With ws
.Activate
GetWebData .Cells(2, 1), [maxMonthZones].Value, month, year
For r = 3 To [maxMonthZones].Value * 1.1
With .Cells(r, 4)
If .Value <> "Points" Then .NumberFormatLocal = "###·###"
End With
Next
End With
End Sub
Sub getZoneRanking(wsname As String, period As Integer)
Dim URL As String
Dim qt As QueryTable
Dim ws As Worksheet
Dim row As Range
Dim c, r As Range
URL = "http://e-e.com/shared/async/expertsZone/memberRank.jsp?etIndex=4&m_typeID=" & [StatsType] & "&m_periodID=" & period & "&hofStart=100000&m_subZoneID="
' All: typeID=0 Q's only: typeID=10 Articles: typeID=30
On Error Resume Next
If [ScratchArea].Name = "" Then [Setup!G1].Name = "ScratchArea"
On Error GoTo 0
If [ScratchArea].Count = 1 Then
Range([ScratchArea].Address, [ScratchArea].Offset(10, 4).Address).Name = "ScratchArea"
End If
With Worksheets(wsname)
.Activate
Set row = [3:3]
' The 2 below is just a dummy Subzone (TA) ID, to make a valid URL
Set qt = [ScratchArea].Parent.QueryTables.Add(Connection:="URL;" & URL & "2", Destination:=[ScratchArea])
With qt
.WebTables = 1 '[WebTables] '*** "11", sometimes "9"!
.FillAdjacentFormulas = False
.PreserveFormatting = True
.AdjustColumnWidth = False
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = False
.SaveData = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
End With
While row.Cells(, 1) <> "" And row.row < [maxzones] * 1.1 + 2
row.Cells(, [Ranks].Column).Select
If row.Cells(, 5) <> "" Then
Set c = [TopicIDs!B:B].Find(row.Cells(, 3), , xlValues, xlWhole, xlByRows, xlNext)
qt.Connection = "URL;" & URL & c.Offset(0, -1).Value
qt.Refresh
row.Cells(, [Ranks].Column) = Replace([ScratchArea].Cells(2, 1), ",", "")
' Duplicate zone name, for ReSort...
row.Cells(, [ZoneRK].Column) = row.Cells(, [Zone].Column)
End If
Set row = row.Offset(1)
Wend
qt.Delete
End With
[ScratchArea].ClearContents
[A1].Select
End Sub
Almost all actions performed are based on Web Queries, issued by GetWebData. Web Queries call a Web page, then analyse the contents, and extract data structured as a table. You can play with that by going into the Data menu, Import External Data, New Web Query. Provide a valid Web link, and you will see what you can get. Or comment the line with qt.Delete in GetWebData, and start a month or overall read; then open context menu on cell C2, and "Edit Query". You should see the Web page used, with some yellow square symbols and a single green one for the member/zone ranking table. The URL you see determines the Web page, but the table reference is stored as a number in the query object (in .WebTables) and not directly visible to you.
几乎所有执行的操作都基于GetWebData发出的Web查询。 Web查询调用Web页面，然后分析内容，并提取构造为表的数据。 您可以通过进入“数据”菜单，“导入外部数据”，“新建Web查询”来进行操作。 提供有效的Web链接，您将看到可以获得的信息。 或者在GetWebData中用qt.Delete注释该行，并开始一个月或整体阅读； 然后打开单元格C2上的上下文菜单，然后打开“编辑查询”。 您应该看到所使用的网页，其中的黄色正方形符号和一个绿色的用于成员/区域排名表。 您看到的URL决定了Web页面，但是表引用以数字形式存储在查询对象中（ .WebTables中 ），并且您不能直接看到。
The Web pages shown are not formatted using your country specific delimiters for numbers. Hence I had to change them or Excel won't be able to use the numbers because the comma is not always a valid thousands delimiter.
所显示的网页未使用您所在国家/地区的数字分隔符格式化。 因此，我不得不更改它们，否则Excel将无法使用数字，因为逗号并不总是有效的千位分隔符。
Since the Web Queries are doing nothing more than calling Web pages and extracting data from them, you can't get more than 10 zones per page. So we have to ask the Web pages repeatedly with a different starting page. Each query will again retrieve the column headers (Zone, Rank, Points), and we have to account for that. Because pretty printing matters, the code empties some of the cells, like removing the "No Certificates" rank, and hides the "superfluous" column titles.
由于Web查询只需要调用网页并从中提取数据，所以每页最多只能有10个区域。 因此，我们必须使用不同的起始页面反复询问网页。 每个查询将再次检索列标题（区域，等级，点），我们必须对此加以考虑。 因为漂亮的打印很重要，所以该代码清空了某些单元格，例如删除了“无证书”等级，并隐藏了“多余的”列标题。
As a last step, all internal linking is removed for the Web queries. Since we will not use the Refresh feature of those queries, the original linkage is useless, wasting storage memory.
最后一步，将删除Web查询的所有内部链接。 由于我们将不使用这些查询的“刷新”功能，因此原始链接是无用的，浪费了存储内存。
GetMonthZoneRanks fills a single worksheet with zone ranking data of one month (month and year are provided as arguments). If the referenced worksheet does not exist, it will be created using the Month worksheet as template. New worksheets are inserted directly after the "Overall" worksheet, so the months will be ordered from right to left.
GetMonthZoneRanks用一个月的区域排名数据填充单个工作表（提供月和年作为参数）。 如果引用的工作表不存在，将使用“ 月”工作表作为模板来创建它。 新工作表将直接插入“总体”工作表的后面，因此月份将从右到左排序。
In most cases you will not need to call that procedure on your own, since there is getMonthZoneRanking. It will get the data for the current month. It applies a skew of two days back, as that works best; the stats are calculated only once a day, as you certainly know already. It is important to adjust this to what works best for your purpose - if the stats are getting into the wrong row in Ranking, this is why. (See the last chapter for details.)
在大多数情况下，由于存在getMonthZoneRanking ，因此您无需自己调用该过程。 它将获取当月的数据。 它适用于两天前的歪斜，因为效果最好； 如您所知，统计数据每天仅计算一次。 重要的是将其调整为最适合您的目的-如果统计信息进入“排名”的错误行，这就是原因。 （有关详细信息，请参阅上一章。）
GetMemberRanking has gotten quite long. I had to use some tricks to get the corresponding values, you can't retrieve them directly. That is what happens:
GetMemberRanking已经相当长了。 我不得不使用一些技巧来获取相应的值，您无法直接检索它们。 那就是发生的事情：
The Overall Member Ranking screen is called with your member name. As you know, you get a list of 10 members, and one of them is you. Normally, you are positioned at row 6, but that can change under rare circumstances (wouldn't work for the "toppest artists" like angelIII, for example). That is why I decided to use VLookup again to get the correct row. The values found are copied and the formulas removed - we are using the "ScratchArea" as a temporary database soon.
将使用您的会员名称调用“总体会员排名”屏幕。 如您所知，您将获得10个成员的列表，其中一个就是您。 通常，您位于第6行，但在极少数情况下可能会发生变化（例如，对于像angelIII这样的“顶尖艺术家”不起作用）。 这就是为什么我决定再次使用VLookup来获取正确的行的原因。 复制找到的值并删除公式-我们很快将“ ScratchArea”用作临时数据库。
Now, we do the same with Yearly and Monthly Member Ranking.
现在，我们对“年度和月度会员排名”执行相同的操作。
After that, we can try to estimate the number of ranked experts in each period. Again, we cannot get that directly, as there is no table to read which contains that value. But if we retrieve the very last ranked member, let's say at rank 1 000 000, we will always get a single row, containing the last rank given.
之后，我们可以尝试估算每个时期的专家排名。 同样，我们无法直接获得该值，因为没有要读取的表包含该值。 但是，如果我们检索到最后一个排名较高的成员，比如说排名1 000 000，我们将总是得到一行，其中包含给出的最后一个排名。
Again, we perform this for Overall, Yearly and Monthly, and fix the values each time.
同样，我们针对“总体”，“年度”和“每月”执行此操作，并每次都固定值。
Now all we have to do is to find the corresponding cells in the Ranking worksheet. It helps we know each month builds a new row, the rest is simply math, and copying values from one cell to another via VBA code.
现在，我们要做的就是在“ 排名”工作表中找到相应的单元格。 这有助于我们知道每个月都会建立一个新行，其余的只是数学运算，并通过VBA代码将值从一个单元格复制到另一个单元格。
Hidden in the Ranking worksheet is another piece of "code", this time as Excel formula. Since the zone ranking columns there refer to worksheets named according to the value in column A (month and year), we have a lot of indirection. That is, the cell addresses we refer to are calculated - combined with the Lookup function, this allows for easy cut & paste without having to change anything in the cells. With the worksheets, you do not have any automation that relocates addresses with new worksheets, as it is done with cells on the same worksheet if you copy a formula in a cell. For example, = A3+B3 in cell C3 will be changed to = A4+B4 if copied to cell C4. But '2010 08'!A3 will only change to '2010 08'!A4.
在排名工作表中隐藏的是另一段“代码”，这次是Excel公式。 由于那里的区域排名列引用了根据A列中的值（月和年）命名的工作表，因此我们有很多间接方法。 也就是说，我们引用的单元格地址是经过计算的 -与Lookup函数结合使用，可以轻松剪切和粘贴，而无需更改单元格中的任何内容。 使用工作表，您将没有任何自动化来使用新工作表重新定位地址，因为如果您在单元格中复制公式，则对同一工作表中的单元格就可以完成。 例如，如果复制到单元格C4 ，则单元格C3中的 = A3 + B3将更改为= A4 + B4 。 但是'2010 08'！A3只会更改为'2010 08'！A4 。
Hence, the address is built with = Indirect("'" & year(A3) & " " & month(A3) & "'!A3"), and you can copy the formula to above or below. This so-called indirect addressing is used to build a cell address with other formulas resulting in a string, and refer to this dynamically addressed cell (or range).
因此，该地址是使用= Indirect（“'”＆year（A3）＆“”＆month（A3）＆“'！A3”）构建的，您可以将公式复制到上方或下方。 所谓的间接寻址用于通过其他公式生成一个单元格来生成一个单元地址，并生成一个字符串，并引用该动态寻址的单元（或范围）。
However, indirect addressing implies that Excel refreshes and recalculates contents on each change of cells. Speed can decrease drastically if overused (just as a little warning for other projects). And the usage here makes it much more complex, as indirect addressing is combined with the Lookup function.
但是，间接寻址意味着Excel会在每次单元格更改时刷新并重新计算内容。 如果使用过度，速度可能会急剧下降（这只是对其他项目的一点警告）。 由于间接寻址与Lookup函数结合使用，因此这里的用法变得更加复杂。
To avoid unnecessary recalculations on cell changes the VBA code switches calculations off while filling worksheets. That is always a good idea if you fill in much data by VBA code.
为避免对单元格进行不必要的重新计算，VBA代码在填充工作表时会关闭计算。 如果您通过VBA代码填写大量数据，那总是一个好主意。
一些想法 (Some Thoughts)
Be aware that there might be a difference between the data retrieved for current or past months if there is some point discussion or Moderator operation that results in removing and reassigning points. Usually this will only apply to the previous or current months, so if you find differences between the sum of your zone points and the total retrieved from the Web, you should adjust the last month (remember: you can't re-retrieve prior months anymore), until the result fits. The same applies to Member Ranking.
请注意，如果某些讨论或主持人操作导致删除和重新分配点，则当前或过去几个月检索到的数据可能会有所不同。 通常这仅适用于前一个月或当前月，因此，如果您发现区域点总数与从Web检索到的总数之间存在差异，则应调整上个月（请记住：您无法重新检索前几个月） ），直到结果合适为止。 会员排名也是如此。
Note that with the recent changes to the point system there is no distinction in ranking between Article and Question points anymore. If you are active in Articles, it does make sense to use two separate workbooks, one for all points and another one for Articles only. And that is what the "Statistics Type" setting in Setup is for - it determines which one you want to see. Of course it does not make sense to change that often.
请注意，随着积分系统的最新变化，Article和Question积分之间的排名不再存在区别。 如果您活跃于Articles，那么使用两个单独的工作簿是有意义的，一个工作簿适用于所有要点，而另一工作簿仅适用于Articles。 这就是安装程序中 “统计信息类型”设置的作用-它确定您要查看的哪一个。 当然，经常进行更改是没有意义的。
Compared to the previous release, the code has gone thru a complete reconditioning to account for the sheet variables and the site changes, and received some improvements. I have tried to remove all parts relying on a particular column layout, but there are still some.
与以前的版本相比，该代码经过了彻底的重新调整以解决工作表变量和站点更改，并获得了一些改进。 我试图删除所有依赖于特定列布局的部分，但仍然有一些。
One improvement is the replacement of references to existing worksheets via the Worksheets collection by variables. That requires that each worksheet gets a variable name. This is performed best in VBA Editor, in the properties of each sheet (press F4 for the property sheet), for the very first property called "(Name)". As soon as you enter something different here, you can use that name you entered directly as object reference. As an example see that I reference to the Overall sheet by using shOverall instead of Worksheets("Overall").
一种改进是通过Worksheets集合将对现有工作表的引用替换为变量。 这就要求每个工作表都必须有一个变量名。 在VBA编辑器中，最好在每个工作表的属性中执行此操作（对于属性表，请按F4键），对于第一个称为“（Name）”的属性，该操作最好执行。 在此处输入不同的内容后，您可以将直接输入的名称用作对象引用。 例如，我使用shOverall而不是Worksheets（“ Overall”）来引用“总体”表。
与第一版相比的重要变化 (Important Changes Compared to First Release)
If you have used the XLS version described in the preceding Article, you will just need to move or copy the monthly sheets into the new XLS. I recommend removing the formulas included before copying/moving, as they are no longer relevant, by calling the Formula2Value macro on the respective range. I have changed some of the formulas, including error checking, which makes them different too much from before to have an advantage from keeping them.
如果您使用了上一篇文章中描述的XLS版本，则只需将月度表移动或复制到新的XLS中。 我建议通过在相应范围内调用Formula2Value宏，删除复制/移动之前包含的公式，因为它们不再相关。 我更改了一些公式，包括错误检查，这使它们与以前相差太大，因此无法保留它们。
While you could also copy the Ranking sheet from the previous version, I advise to copy the rows manually and insert them in the existing one; then copy and insert the zone stat columns starting at column T.
虽然您也可以从以前的版本复制排名表，但我建议手动复制行并将其插入现有的行中。 然后复制并插入从T列开始的区域状态列。
Overall will be filled out completely again, so no need to take that one over.
总体将再次完整填写，因此无需接管。
Previously you used the RankDef sheet to set variables and start actions. That has now (more consistently) been moved into an own worksheet Setup.
以前，您使用RankDef表设置变量和启动操作。 现在（更一致地）将其移至自己的工作表Setup中 。
EE在2010年7月的排名变化 (EE Changes in the Ranking in July 2010)
As already mentioned, the ranking system has been changed in several ways:
如前所述，排名系统已通过多种方式进行了更改：
- Article and Question points can be retrieved separately or as overall points. Ranks are based on overall points only.
Previously you could get a Master in Articles or in Questions, but if you had 25000 in both, you have not been assigned a "degree". What you retrieve is determined by the "Statistics Type" setting in Setup worksheet, as already said.
以前，您可以在文章或问题上获得硕士学位，但是如果您同时拥有25000和25,000，则您尚未获得“学位”。 如前所述，您检索的内容取决于安装程序工作表中的“统计信息类型”设置。
- No historical data can be retrieved anymore. The only choices are (recent) year, month, week and day.
That means you have to get your stats for the last month in time - one or two days into the new month, else they are gone.
这意味着您必须及时获取上个月的统计信息-新月开始的一两天，否则它们就消失了。
- Statistics are no longer calculated in (approximately) real-time, instead they are collected once a day at 0:00 GMT (for the previous day). 不再（实时）实时计算统计信息，而是每天在格林尼治标准时间0:00（前一天）收集一次统计信息。
Both the "once a day" and "start at GMT" issues require us to have a more accurate time base, so we hit the right month no matter at which time and in which time zone we call for numbers. Below the "Statistics Type" you will find a time zone offset from GMT for exact that reason. Please be aware that it is still not accurate because of delays at EE. You should wait at least one hour after 0:00 GMT ;-).
“一天一次”和“格林尼治标准时间开始”这两个问题都要求我们有一个更准确的时基，因此无论我们在哪个时间和哪个时区要求输入数字，我们都选择了正确的月份。 正是由于这个原因，您可以在“统计信息类型”下方找到与格林尼治标准时间的时区偏移量。 请注意，由于EE的延迟，它仍然不准确。 您应该在格林尼治标准时间0:00后至少等待一小时;-)。
The new retrieval of data is much faster now than before because of the way EE changed the calculation of statistics, which can be cached now. Thus the "Max zones" settings done in Setup worksheet are not that important anymore, and you can set them to 100 or more without any issues. With the exception of the "Zone Ranking" buttons: Since the number of ranks for each zone needs to be retrieved one by one, it does matter when you want to update those.
现在，新的数据检索比以前快得多，这是因为EE更改了可以立即缓存的统计信息的计算方式。 因此，在安装程序工作表中完成的“最大区域”设置不再那么重要，您可以将它们设置为100或更多而没有任何问题。 除了“区域排名”按钮外：由于每个区域的排名数量需要一个一个地获取，因此何时更新它们就很重要。
Please do not forget to press the "Thumbs up" button if this article was helpful.
如果本文对您有所帮助，请不要忘记按下“竖起大拇指”按钮。
-ee -er
扫一扫
5448
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
