Coffee germplasm resource seedling comprehensive evaluation method based on Excel-VBA

Abstract

The invention discloses a coffee germplasm resource seedling comprehensive evaluation method based on Excel-VBA, and relates to the technical field of seedling comprehensive evaluation. According to the method, an evaluation system comprising seven core ratio indexes including stem diameter / plant height, main root length / plant height, stem diameter / main root length, leaf dry weight / total dry weight, stem dry weight / total dry weight, main root dry weight / total dry weight and fibrous root dry weight / total dry weight is constructed by collecting original data such as stem diameter, plant height, main root length, leaf dry weight, stem dry weight, main root dry weight and total dry weight; and the growth balance, the stress resistance potential and the resource allocation strategy of the seedlings are quantitatively represented. Based on an Excel VBA platform, the method realizes an automatic comprehensive evaluation process which comprises the steps of dynamically processing a data boundary, processing data by utilizing a range standardization method, calculating a weight by applying an entropy weight method, calculating a sample comprehensive score in combination with the weight and sorting, and automatically dividing the comprehensive score into four resistance grades of'excellent ', 'good', 'medium 'and'poor' by adopting a natural breakpoint method (Jenks). In terms of user interface design, basic data are stored in the 2nd-61th row of Excel, new data are input from the 63rd row, evaluation of the new data can be automatically completed by clicking a'comprehensive evaluation 'button of a macro command, and index weight, comprehensive score, ranking and resistance grade results are output to a designated area. According to the method, the automatic function of Excel-VBA is utilized, so that the evaluation efficiency and objectivity are remarkably improved, and manual weighting deviation is avoided; relying on a universal Excel platform, the technical threshold is reduced; comprehensive evaluation is realized through seven ratio indexes; the modularized VBA structure has good expansibility, and a convenient, scientific and reliable tool is provided for coffee resistant variety breeding, cultivation management and efficient screening of germplasm resources.

Description

Technical Field

[0001] The present invention relates to the technical field of comprehensive seedling evaluation, and in particular to an Excel-VBA-based comprehensive evaluation method for coffee germplasm seedlings. Background Art

[0002] Coffee is one of the world's most important cash crops, with an annual industry valued at hundreds of billions of dollars. It supports the agricultural economies of dozens of tropical countries and is an indispensable "social currency" in global consumer culture. Its supply chain spans cultivation, processing, trade, and end-consumer consumption, directly engaging over 25 million people. Especially in developing countries in Latin America, Africa, and Asia, coffee exports are a crucial pillar of the national economy. In China, the coffee consumption market is growing at the fastest rate globally, and coffee is becoming an integral part of the national lifestyle. Yunnan, the absolute core of China's coffee industry, accounts for over 98% of the country's cultivated area and production, earning it the nickname "the cradle of Chinese coffee." Located in the prime coffee-growing belt between 21° and 25° north latitude, Yunnan's high-altitude mountainous terrain, slightly acidic soil, and significant diurnal temperature swings give its small-grain coffee its unique flavor. In recent years, Yunnan has promoted the transformation of its coffee production to premium quality through its "Six Measures for Coffee," resulting in a substantial increase in the quality of its coffee.

[0003] By systematically evaluating morphological characteristics, physiological indicators, stress resistance, and genetic traits during the seedling stage, we can efficiently screen for promising varieties with superior traits (such as high yield, high quality, pest and disease resistance, and drought and cold tolerance), significantly shortening the breeding cycle and reducing resource investment. The seedling stage is a critical stage in the growth and development of coffee plants. Early performance often predicts the adaptability and productivity of the mature plant. Therefore, comprehensive evaluation can provide an early scientific basis for variety selection and avoid wasting resources later. This evaluation also helps deepen our understanding of the genetic diversity of different coffee varieties, providing data support for the construction and conservation of germplasm resource banks, and promoting coffee variety improvement and innovation. Furthermore, identifying seedlings with strong stress resistance can guide adaptive cultivation, enhance the coffee industry's ability to cope with climate change and pest and disease threats, and ultimately lay a solid foundation for the sustainable development and quality improvement of global coffee production.

[0004] The stem diameter / plant height ratio is a key morphological indicator for evaluating the stress resistance and growth robustness of coffee seedlings. Its value directly reflects the seedling's structural stability and potential for environmental adaptation. A higher stem diameter / plant height ratio generally indicates a thicker stem with more substantial tissue, providing greater mechanical support and nutrient transport capacity, effectively resisting stresses such as lodging, drought, and strong winds. Under deficit irrigation conditions, seedlings with significantly increased stem diameter tend to exhibit higher dry matter accumulation efficiency and water use efficiency, indicating a well-developed root system and efficient resource allocation. This reduces plant height and thickens the stem, thereby reducing transpiration losses and enhancing drought resistance. Conversely, a low ratio (excessively tall plants with thin stems) indicates that the seedlings are susceptible to environmental stresses, such as increased risk of lodging due to thin stems or reduced resistance to cold and disease due to imbalanced photosynthate distribution. Therefore, this indicator can be used as a basis for early screening of stress-resistant varieties.

[0005] The ratio of taproot length to plant height (taproot length / plant height) is a key morphological indicator for evaluating stress tolerance in coffee seedlings. Its value directly reflects a seedling's resource allocation strategy and ability to adapt to stressful environments. A high ratio indicates that the seedling allocates more biomass to the root system rather than to the aboveground parts, a strategy that significantly enhances drought tolerance and nutrient absorption efficiency. Under drought stress, coffee seedlings suppress plant height growth and promote taproot elongation to reach deeper into the soil to acquire water, thereby maintaining turgor pressure and photosynthetic capacity. Under drought conditions, the taproot length of Arabica coffee seedlings increases while plant height decreases significantly. This morphological adjustment reduces transpiration and improves water use efficiency. Furthermore, a well-developed taproot enhances the seedling's ability to mechanically bind soil, reducing the risk of soil erosion and laying the foundation for lodging resistance later in life. Therefore, this indicator serves as an important basis for early screening of stress-tolerant varieties. Seedlings with a high ratio tend to exhibit higher survival rates and growth stability in drought or infertile soils.

[0006] Stem diameter and taproot length are important morphological indicators for evaluating coffee seedling stress resistance, directly reflecting the seedling's physiological potential to cope with environmental stress. Stem diameter reflects the mechanical support capacity and nutrient reserves of the aboveground part of the seedling: a thicker stem indicates a well-developed vascular bundle, which facilitates the longitudinal transport of water and nutrients and accumulates more photosynthetic products, providing an energy base for withstanding stresses such as drought and strong winds. Studies have found that seedlings with a 49.1% increase in stem diameter exhibit significantly improved resistance to lodging and pests and diseases. Taproot length reflects the adaptability of the belowground part: a well-developed taproot can penetrate deep into the soil (up to 100 cm), enhancing water and nutrient absorption efficiency and maintaining plant water balance, especially under drought conditions. Seedlings with longer taproots have stronger root regeneration capacity, recovering quickly within 7–10 days after damage and forming new lateral roots to maintain absorption capacity. These two factors work synergistically to enhance resistance: stem diameter ensures structural stability aboveground, while taproot length optimizes access to belowground resources, thereby enhancing the seedling's overall adaptability to drought, infertility, and extreme climates.

[0007] Leaf dry weight to total dry weight (i.e., leaf mass ratio, LMR) is an important biomass allocation metric for evaluating stress tolerance in coffee seedlings. Changes in LMR directly reflect the seedlings' resource allocation strategies and adaptability to stressful environments. Under drought or low-temperature stress, coffee seedlings optimize resource allocation by reducing LMR (i.e., reducing the proportion of leaf biomass), diverting more assimilated products to root development (e.g., increasing the root mass ratio (RMR) and the root-to-shoot ratio (R / S)). This enhances water and nutrient absorption capacity, thereby maintaining water balance and mitigating stress damage. Increased phosphorus fertilizer application under drought stress significantly promotes root growth and increases the root-to-shoot ratio. A decrease in LMR is positively correlated with increased drought tolerance. Furthermore, lower LMR is often associated with higher leaf dry matter content (LDMC) and leaf mass ratio (LMA). These structural traits reduce water loss and improve cell membrane stability by thickening cell walls and accumulating osmotic regulators (such as soluble sugars), thereby enhancing drought and cold tolerance. Therefore, LMR is not only an intuitive reflection of seedling growth strategy, but also an important basis for early screening of stress-resistant varieties, providing scientific guidance for coffee resistance breeding and cultivation management.

[0008] Stem weight / total dry weight (i.e., stem biomass allocation ratio) is an important morphological indicator for evaluating the stress resistance of coffee seedlings. By quantifying the proportion of stem biomass to total aboveground biomass, it intuitively reflects the seedling's resource allocation strategy and structural resistance foundation. A higher stem weight / total dry weight ratio indicates that the seedling is investing more photosynthetic products in stem construction, forming a more developed lignified structure (such as a thicker base stem and denser cell walls). This not only enhances mechanical support to withstand physical stresses such as wind and rain, but also optimizes drought resistance by increasing the stem's water storage capacity and reducing water transport resistance. Under drought stress, coffee seedlings with well-developed stems can reduce water loss and maintain water transport function, delaying wilting. Furthermore, as the primary storage site for non-structural carbohydrates (such as soluble sugars), an increased proportion of stem biomass helps accumulate more osmotic regulating substances, thereby maintaining cellular osmotic balance and protecting the stability of the membrane system during low temperatures or drought. Therefore, this indicator comprehensively reflects the coffee seedlings' ability to adapt to adverse conditions such as drought and low temperature, and can provide an important basis for the early screening of stress-resistant germplasm resources.

[0009] The ratio of taproot dry weight to total dry weight (i.e., the ratio of root biomass to total biomass) is an important indicator for evaluating coffee seedling stress resistance. It directly reflects the extent to which the plant allocates resources to root development within its biomass allocation. A higher ratio indicates that the seedling allocates more photosynthetic products to the roots, particularly the development of the taproot, thereby forming a stronger deep absorption network. Under drought stress, this allocation strategy significantly enhances the plant's water acquisition capacity. Studies have shown that in drought environments, the root-to-shoot ratio (R / S) of coffee seedlings increases, with the taproot extending deeper into the soil to absorb water from below. Furthermore, a well-developed root system improves nutrient absorption efficiency, supports normal physiological functions of the aboveground part, and enhances antioxidant enzyme activity to mitigate oxidative damage. Furthermore, this indicator is associated with the adaptability of seedlings after transplanting: seedlings with well-developed root systems resume growth more quickly, reducing the impact of environmental stress after transplanting. Therefore, the ratio of main root dry weight to total dry weight is not only an effective predictor of coffee drought resistance, but also provides a scientific basis for the breeding of stress-resistant varieties and cultivation management (such as reasonable fertilization to promote root growth).

[0010] The metric of fibrous root dry weight / total dry weight directly reflects a coffee seedling's resource allocation strategy and stress tolerance by quantifying the proportion of biomass allocated to the absorptive root system. A higher ratio indicates that the seedling preferentially allocates dry matter to the fibrous root system, responsible for water and nutrient absorption, thereby enhancing its adaptability to stressful environments. Well-developed fibrous roots can access water from the soil over a larger area, significantly alleviating drought stress, maintaining leaf moisture content, and reducing oxidative damage. Furthermore, increased fibrous root density expands the root-soil contact area, promoting efficient nutrient absorption and supporting aboveground photosynthetic growth. This metric is also positively correlated with the accumulation of osmotic regulators and antioxidant enzyme activity, which together mitigate physiological damage caused by drought or continuous cropping problems. Conversely, soil factors that inhibit fibrous root development weaken the seedling's stress resistance mechanisms. Therefore, this parameter is a key indicator for evaluating the stress tolerance potential of coffee seedlings and provides theoretical support for the selection of resistant varieties and targeted fertilization management.

[0011] The core significance of using the entropy weighting method for comprehensive evaluation of coffee seedlings lies in scientifically quantifying the true contribution of each seedling growth indicator to overall stress resistance and growth potential through an objective weighting mechanism, thereby avoiding subjective judgment bias and improving the accuracy of breeding selection. The rationale of the entropy weighting method stems from its principle of information entropy: the degree of dispersion of indicator data directly reflects the amount of information contained. If a certain indicator exhibits significant variation across different seedling samples (low entropy values), it indicates that the indicator has high information value in distinguishing seedling stress resistance and should be assigned a higher weight. Conversely, if the indicator data converge (high entropy values), the weighting is reduced. This method not only accounts for the natural variability of seedling growth indicators but also automatically identifies key distinguishing indicators (such as highly variable drought-related indicators), providing a data-driven decision-making basis for resistant variety selection. Furthermore, the entropy weighting method eliminates dimensional differences through standardization and automatically generates weights through an algorithm, significantly simplifying the complexity of integrating multiple indicators. Therefore, the entropy weighting method provides a theoretically rigorous and operationally feasible quantitative tool for early stress resistance assessment and resource optimization in coffee seedlings.

[0012] Comprehensive evaluation of coffee germplasm seedlings systematically analyzes ratios of stem diameter to plant height, taproot length to plant height, stem diameter to taproot length, leaf dry weight to total dry weight, stem dry weight to total dry weight, taproot dry weight to total dry weight, and fibrous root dry weight to total dry weight. These ratios offer significant advantages over traditional single morphological indicators (such as plant height and ground diameter). By quantifying the proportion of biomass allocation and morphological coordination between organs, these indicators more sensitively reveal the growth balance, stress resistance potential, and resource allocation strategies of seedlings. Furthermore, these ratios are standardized to eliminate interference from differences in seedling age, making evaluations of seedlings from different batches or varieties more comparable. They also integrate the synergistic response mechanisms of morphology and physiological function, overcoming the inadequacy of traditional indicators in characterizing stress adaptability. Therefore, the comprehensive evaluation system, through the rational optimization of indicators and the Excel entropy weight method comprehensive evaluation table established based on VBA, can not only provide users with convenient operations, allowing users to obtain comprehensive evaluation results by clicking the run button after entering data, but also provide a more comprehensive and reliable scientific basis for the selection and breeding of coffee-resistant varieties, cultivation regulation and efficient screening of germplasm resources. Summary of the Invention

[0013] The purpose of this invention is to provide a comprehensive evaluation method for coffee germplasm resources seedlings based on Excel-VBA. By constructing an objective and quantitative evaluation model and an automated calculation process, it solves the problems of large subjective weighting deviations, cumbersome multi-index calculations, and low efficiency in traditional evaluation methods, and realizes scientific screening and early accurate evaluation of coffee seedling germplasm resources.

[0014] In order to achieve the above object, the present invention is implemented by the following technical solution, which is characterized by comprising the following steps: S1 acquires data, wherein the data includes stem diameter, plant height, main root length, leaf dry weight, stem dry weight, main root dry weight, and total dry weight; S2. Establish an evaluation index system, which includes seven ratio indicators: stem diameter / plant height, taproot length / plant height, stem diameter / taproot length, leaf dry weight / total dry weight, stem dry weight / total dry weight, taproot dry weight / total dry weight, and fibrous root dry weight / total dry weight, to characterize seedling growth balance, stress resistance, and resource allocation strategy; S3 automatically generates comprehensive evaluation results based on Excel VBA.

[0015] Furthermore, the specific process of automatically generating comprehensive evaluation results based on Excel VBA in step S3 includes: S3.1 Establishing a comprehensive evaluation model based on the Excel VBA platform includes the following sub-steps: S3.1.1 Dynamic data boundary processing: Specifically, use the Excel VBA platform to define the starting and ending rows and columns of the original and new data, and dynamically exclude specific invalid rows (such as row 62) to establish a data boundary processing mechanism; S3.1.2 Data standardization, specifically: using the Excel VBA platform to establish a range standardization method to standardize the data, and storing the standardized data in a temporary array to prevent intermediate values ​​from overwriting the source data; S3.1.3 Calculate weights using the entropy weight method, specifically: Use the Excel VBA platform to calculate the information entropy and variance coefficient of each indicator based on standardized data, then determine the weight value and store it in a temporary array; S3.1.4 Calculate and rank the composite scores, specifically by using the Excel VBA platform to calculate the composite score for each sample based on the standardized data and weight values, sort the scores in descending order, generate ranking information, and output the results to the designated area; S3.1.5 Comprehensive score resistance classification: Specifically, use the Excel VBA platform to establish the natural breakpoint method (Jenks) to classify the comprehensive score into four levels: "excellent", "good", "fair", and "poor", and output the classification results to the designated area; S3.2 Building a visualization interface based on the Excel VBA platform includes the following sub-steps: S3.2.1 Data storage specifications, specifically: Basic data is stored in rows 2-61, with the first row being the header row; S3.2.2 Enter data. Specifically, row 62 is the data entry area title. The data starts at row 63. After entering the data, enter "Indicator Weight" in the first cell of the next row. S3.2.3 Create a "Comprehensive Evaluation" form button. Specifically, insert a form button under "Developer Tools" and name it "Comprehensive Evaluation". After it executes the macro command "Entropy Weight Method For New Data ()," clicking this button will automatically complete the comprehensive evaluation of the input data. S3.2.4 Results are presented as follows: the indicator weight is output in the next row of the "Indicator Weight" cell, and the comprehensive score of the input data is output under the indicator weight. The first column is the corresponding input data number, the second column is the comprehensive score of the input data, and the third column is the comprehensive score ranking. The column after the input data area directly displays the comprehensive score resistance level.

[0016] Compared with the prior art, the present invention has the following beneficial effects: (1) This invention uses the automation function of Excel-VBA to realize the rapid calculation and comprehensive evaluation of multiple index data of coffee seedlings, and automatically generates an objective and quantitative germplasm resource grading report, avoiding the subjective bias and repeated calculation errors caused by manual weighting, greatly improving the evaluation efficiency and scientific accuracy. Compared with traditional manual statistical methods, the efficiency is improved and the results are reproducible; (2) The present invention relies on Excel, a universal tool. Users do not need to master programming or professional statistical software. Complex calculations can be completed through intuitive form buttons, which significantly reduces the technical threshold and learning cost of agricultural researchers. Compared with Python / R language tools that require a code foundation, the present invention is more universal and practical. (3) This invention uses seven core ratio indicators to quantitatively characterize the growth balance and stress resistance of coffee seedlings based on their physiological characteristics, providing customized evaluation solutions for scenarios such as "variety selection, cultivation management, disease resistance screening, and resource conservation." Compared with the screening method based on a single indicator, it more comprehensively supports the precise grading of germplasm resources. (4) The present invention uses a modular VBA programming structure to flexibly adjust the indicator weight algorithm or grading rules to adapt to the iterative upgrade requirements of coffee breeding goals. Compared with software that solidifies the evaluation process, it is more scalable and sustainable. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] Figure 1 The figure is a flow chart of a comprehensive evaluation method for coffee germplasm seedlings based on Excel-VBA in the present invention.

[0018] Figure 2 This is a visual control interface for the comprehensive evaluation method of coffee germplasm resources seedlings based on Excel-VBA in the present invention. DETAILED DESCRIPTION

[0019] The present invention will be further described below with reference to the accompanying drawings and specific embodiments.

[0020] As shown in FIG1 , the present invention discloses a comprehensive evaluation method for coffee germplasm seedlings based on Excel-VBA, which uses Excel-VBA as an operation carrier and is implemented through VBA code. The method is characterized by comprising the following steps: The S1 study selected 60 representative coffee germplasm accessions as the baseline data. Fresh fruit samples from these accessions were collected during the 2024-2025 coffee season, peeled and degummed, and then placed in a sand bed at least 15 cm thick for germination and seedling cultivation. Once the coffee seedlings had successfully germinated and their cotyledons had fully expanded, data collection began: the seedlings were measured for various morphological parameters (stem diameter, plant height, taproot length) and biomass indicators (leaf dry weight, stem dry weight, taproot dry weight, and total dry weight). Before dry weight measurement, the separated root, stem, and leaf tissues were oven-dried at 70°C to a constant weight. To ensure representative and reliable data, 10 replicates were measured for each accession.

[0021] S2 In order to systematically evaluate the growth characteristics of coffee seedlings, this study constructed a comprehensive evaluation index system, which focuses on the coordination of seedling morphological configuration (growth balance), biomass allocation pattern (resource allocation strategy) and potential stress resistance. Specifically, it includes the following 7 key ratio indicators: stem diameter to plant height ratio (stem diameter / plant height), which reflects the robustness of aboveground growth and lodging resistance potential; taproot length to plant height ratio (taproot length / plant height), which characterizes the relative development degree of the aboveground and underground taproot systems, indicating above-ground-underground coordination and anchoring ability; stem diameter to taproot length ratio (stem diameter / taproot length), which comprehensively reflects the synergistic relationship between stem development and taproot extension; leaf dry weight to total dry weight ratio (leaf dry weight / total dry weight), The ratio of stem weight to total dry weight (stem weight / total dry weight) reveals the proportion of investment in photosynthetic organs (leaves) in total biomass; the ratio of stem weight to total dry weight (stem weight / total dry weight) reflects resource investment in supporting and conducting tissues (stem); the ratio of taproot weight to total dry weight (taproot weight / total dry weight) characterizes resource allocation to water and nutrient absorption organs (taproot); and the ratio of fibrous root weight to total dry weight (fibrous root weight / total dry weight) more precisely measures resource investment in the absorptive surface area (fibrous roots), reflecting soil resource acquisition efficiency strategies. These ratio indicators can effectively quantify the resource allocation preferences of coffee seedlings between different organs, the degree of coordination between aboveground and underground growth, and between various organs, and indirectly reflect their physiological basis and adaptive potential for coping with environmental stresses, providing important quantitative evidence for studying seedling phenotypes, screening for superior germplasm, and understanding their adaptive mechanisms.

[0022] S3 automatically generates comprehensive evaluation results based on Excel VBA.

[0023] Furthermore, the specific process of automatically generating comprehensive evaluation results based on Excel VBA in step S3 includes: S3.1 Establishing a comprehensive evaluation model based on the Excel VBA platform includes the following sub-steps: S3.1.1 Set the data range, specifically: Use the Excel VBA platform to define the starting row, ending row, and column ranges for the original and new data, dynamically exclude specific invalid rows (e.g., row 62), and establish a data boundary processing mechanism; S3.1.2 Data standardization, specifically: Use the Excel VBA platform to establish the range standardization method to standardize the data, and store the standardized data in a temporary array. The main formula for data standardization is as follows: Where, For the Indicator No. data The standardized value of For the j The maximum value among the indicators No. The minimum value among the indicators; S3.1.3 Calculate weights using the entropy weight method. Specifically, use the Excel VBA platform to calculate the information entropy and variance coefficient of each indicator based on standardized data, and then determine the weight value and store it in a temporary array. The main formula for calculating weights using the entropy weight method is as follows: In the formula For the The weight of the indicators, ,and ,in , is the number of samples; S3.1.4 Calculate and rank the composite scores, specifically by using the Excel VBA platform to calculate the composite score for each sample based on the standardized data and weight values, sort the scores in descending order, generate ranking information, and output the results to the designated area; S3.1.5 Classify the comprehensive score into four resistance levels: "Excellent," "Good," "Fair," and "Poor," using the Jenks natural breakpoint method established in Excel VBA. Output the classification results to a designated area. S3.2 Building a visualization interface based on the Excel VBA platform includes the following sub-steps: S3.2.1 Basic data rows, specifically: Basic data are stored in rows 2-61, with the first row being the header row; S3.2.2 Enter data. Specifically, row 62 is the data entry area title. The data starts at row 63. After entering the data, enter "Indicator Weight" in the first cell of the next row. S3.2.3 Create a "Comprehensive Evaluation" form button. Specifically, insert a form button under "Developer Tools" and name it "Comprehensive Evaluation." Bind it to the macro command "Entropy Weight Method For New Data()." Clicking this button will automatically complete the comprehensive evaluation of the input data. S3.2.4 The results are presented as follows Figure 2 As shown, specifically: the indicator weight is output in the next row of the "Indicator Weight" cell, and the comprehensive score result of the input data is output under the indicator weight. The first example is the corresponding input data number, the second column is the comprehensive score of the input data, and the third column is the comprehensive score ranking. The column after the input data area directly displays the comprehensive score resistance level.

[0024] The complete VBA code for automatically generating comprehensive evaluation is as follows: Sub EntropyWeightMethodForNewData() Dim lastRow As Long Dim dataStartRow As Integer Dim newDataStartRow As Long Dim colStart As Integer Dim colEnd As Integer Dim resultStartRow As Long Dim i As Long, j As Long Dim totalValue As Double Dim pij As Double Dim entropy As Double Dim sumEntropy As Double Dim weight() As Double Dim result() As Double Dim tempVal As Double Dim errorCount As Integer Dim isDataChanged As Boolean Dim lastResultRow As Long Dim weightRow As Long Dim scoreStartRow As Long Dim scoreEndRow As Long Dim originalDataEndRow As Long ' Set the data range dataStartRow = 2 ' Load data starting from row 2 newDataStartRow = 63 ' New data starts from row 63 originalDataEndRow = 61 ' The original data ends at row 61 (excluding row 62) colStart = 2 colEnd = 8 'Find the row where "Indicator Weight" is located weightRow = 0 For i = newDataStartRow To Cells(Rows.Count, 1).End(xlUp).Row If Cells(i, 1).Value = "Indicator Weight" Then weightRow = i Exit For End If Next i ' If "Indicator Weight" is found, adjust the data range If weightRow>0 Then lastRow = weightRow - 1 'The data ends at the row above the previous row of "Indicator Weight" Else ' Verify data range On Error Resume Next lastRow = Cells(Rows.Count, colStart).End(xlUp).Row On Error GoTo 0 End If ' Ensure that the data range does not include row 62 If lastRow>= 62 Then lastRow = WorksheetFunction.Max(dataStartRow - 1, IIf(lastRow>62,lastRow, originalDataEndRow)) End If ' Check if there are enough rows of data If lastRow <newdatastartrow then msgbox "数据行数不足，请检查数据起始行设置！", vbexclamation exit sub end if ' 检查数据是否有变化 isdatachanged="False" lastresultrow 1).end(xlup).row>lastRow + 5 Then ' Check if there is a previous result For i = newDataStartRow To lastRow For j = colStart To colEnd If Cells(i, j).Value<>Cells(i, j + colEnd - colStart + 2).Value Then isDataChanged = True Exit For End If Next j If isDataChanged Then Exit For Next i Else isDataChanged = True End If ' Add numbers to the data after row 63 in column A For i = newDataStartRow To lastRow If Cells(i, 1).Value = "" Then Cells(i, 1).Value = "ID"&Format(i - newDataStartRow + 1, "000") End If Next i ' Initialize the array ReDim weight(colEnd - colStart) ReDim result(lastRow - newDataStartRow + 1) ' Create a temporary array to store the normalized data Dim stdData() As Double ReDim stdData(dataStartRow To lastRow, colStart To colEnd) ' Reset entropy sum and error counters sumEntropy = 0 errorCount = 0 Dim minVal As Double, maxVal As Double Dim k As Double Dim sampleCount As Long ' Calculate the number of valid samples (excluding 62 rows) sampleCount = 0 For i = dataStartRow To lastRow If i<>62 Then sampleCount = sampleCount + 1 Next i k = 1 / Log(sampleCount) ' Entropy calculation constant ' Calculate the sum and entropy of each indicator For j = colStart To colEnd ' 1. Calculate the minimum and maximum values minVal = 1E+30 maxVal = -1E+30 For i = dataStartRow To lastRow If i = 62 Then GoTo SkipMinMax tempVal = CDbl(Cells(i, j).Value) If tempVal <minval then minval="tempVal" if tempval>maxVal Then maxVal = tempVal SkipMinMax: Next i ' 2. Range normalization [0,1] totalValue = 0 For i = dataStartRow To lastRow If i = 62 Then stdData(i, j) = 0 GoTo SkipNormalize End If tempVal = Cells(i, j).Value If maxVal>minVal Then stdData(i, j) = (tempVal - minVal) / (maxVal - minVal) Else stdData(i, j) = 0.5 ' Process the constant column End If totalValue = totalValue + stdData(i, j) SkipNormalize: Next i ' Calculate entropy entropy = 0 For i = dataStartRow To lastRow If i = 62 Then GoTo SkipEntropy pij = stdData(i, j) / totalValue If pij>0 Then entropy = entropy + pij * Log(pij) End If SkipEntropy: Next i entropy = -k * entropy ' Apply normalization constant 'Store entropy and accumulate total entropy weight(j - colStart) = entropy sumEntropy = sumEntropy + entropy Next j ' Calculate weights (add handling for division by zero errors) Dim diffSum As Double diffSum = 0 For j = 0 To UBound(weight) weight(j) = 1 - weight(j) ' coefficient of variation diffSum = diffSum + weight(j) Next j If diffSum>0 Then For j = 0 To UBound(weight) weight(j) = weight(j) / diffSum Next j Else MsgBox "Division by zero error occurred when calculating weights, please check your data!", vbExclamation Exit Sub End If ' Calculate the composite score (for input data only) For i = newDataStartRow To lastRow result(i - newDataStartRow + 1) = 0 For j = colStart To colEnd On Error Resume Next tempVal = stdData(i, j) ' Get the normalized value from the temporary array On Error GoTo 0 result(i - newDataStartRow + 1) = result(i - newDataStartRow +1) + tempVal * weight(j - colStart) Next j Next i ' Set the starting row of the result If isDataChanged Or lastResultRow<= lastRow + 5 Then ' The data has changed or there is no previous result, generate below the data resultStartRow = lastRow + 2 ' Clear the original result area Range(Cells(resultStartRow, 1), Cells(Rows.Count, colEnd + 2)).ClearContents Else ' Data is unchanged, use the original result position resultStartRow = lastResultRow - (lastRow - newDataStartRow + 1)- 4 ' Clear the original result area (keep the header row) Range(Cells(resultStartRow + 3, 1), Cells(Rows.Count, colEnd +2)).ClearContents End If ' Output indicator weight (get indicator name from line 62) For j = colStart To colEnd Cells(resultStartRow + 1, j).Value = Cells(62, j).Value ' Indicator name Cells(resultStartRow + 2, j).Value = weight(j - colStart) ' Weight value Cells(resultStartRow + 2, j).NumberFormat = "0.00%" ' Set to percentage format Next j ' Output comprehensive score resultStartRow = resultStartRow + 4 Cells(resultStartRow, 1).Value = "Number" Cells(resultStartRow, 2).Value = "Input data comprehensive score" Cells(resultStartRow, 3).Value = "Ranking" ' Save the original data location for restoration after sorting Dim originalOrder() As Long ReDim originalOrder(lastRow - newDataStartRow + 1) For i = newDataStartRow To lastRow Cells(resultStartRow + i - newDataStartRow + 1, 1).Value = Cells(i, 1).Value ' Copy the number Cells(resultStartRow + i - newDataStartRow + 1, 2).Value = result(i - newDataStartRow + 1) originalOrder(i - newDataStartRow + 1) = i - newDataStartRow + 1 Next i 'Record score range scoreStartRow = resultStartRow + 1 scoreEndRow = scoreStartRow + (lastRow - newDataStartRow) ' Sort by comprehensive score (descending order) Application.ScreenUpdating = False With ActiveSheet.Sort .SortFields.Clear .SortFields.Add Key:=Range(Cells(scoreStartRow, 2), Cells(scoreEndRow, 2)), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal .SetRange Range(Cells(scoreStartRow, 1), Cells(scoreEndRow, 2)) .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Application.ScreenUpdating = True ' Add ranking column For i = scoreStartRow To scoreEndRow Cells(i, 3).Value = i - scoreStartRow + 1 Next i ' Copy the sorted data next to the original data area Dim copyStartRow As Long Dim copyEndRow As Long copyStartRow = newDataStartRow copyEndRow = lastRow ' Clear the old data next to the original data area Range(Cells(copyStartRow, colEnd + 2), Cells(copyEndRow, colEnd +4)).ClearContents ' Copy number, score and ranking For i = scoreStartRow To scoreEndRow Cells(copyStartRow + i - scoreStartRow, colEnd + 2).Value = Cells(i, 1).Value ' number Cells(copyStartRow + i - scoreStartRow, colEnd + 3).Value = Cells(i, 2).Value ' score Cells(copyStartRow + i - scoreStartRow, colEnd + 4).Value = Cells(i, 3).Value 'Ranking Next i ' Add ranking column header Cells(newDataStartRow - 1, colEnd + 2).Value = "Number" Cells(newDataStartRow - 1, colEnd + 3).Value = "Comprehensive score" Cells(newDataStartRow - 1, colEnd + 4).Value = "Ranking" ' 1. Collect all comprehensive scoring data (basic data and new input data) Dim allScores() As Double Dim baseScoreCount As Long, newScoreCount As Long baseScoreCount = originalDataEndRow - dataStartRow + 1 ' Number of basic data (rows 2-61) newScoreCount = lastRow - newDataStartRow + 1 ' Number of new input data ReDim allScores(1 To baseScoreCount + newScoreCount) ' Extract the basic data comprehensive score (assuming the basic data comprehensive score is in the "comprehensive score" column, i.e. colEnd + 3) Dim baseScoreStartRow As Long baseScoreStartRow = dataStartRow For i = 0 To baseScoreCount - 1 allScores(i + 1) = Cells(baseScoreStartRow + i, colEnd + 3).Value Next i ' Extract comprehensive score of new input data For i = 0 To newScoreCount - 1 allScores(baseScoreCount + i + 1) = result(i + 1) Next i ' 2. Classification using the natural breakpoint method Dim breakPoints(1 To 4) As Double ' 4 categories require 3 breakpoints Call JenksClassification(allScores, breakPoints) ' 3. Classify new input data Dim classification() As String ReDim classification(newScoreCount) For i = 0 To newScoreCount - 1 classification(i) = ScoreToCategory(result(i + 1), breakPoints) Next i ' 4. Output classification results Cells(newDataStartRow - 1, colEnd + 5).Value = "Resistance Classification" ' Header row For i = 0 To newScoreCount - 1 Cells(copyStartRow + i, colEnd + 5).Value = classification(i) 'Data row Next i MsgBox "Entropy weight calculation completed!"&IIf(isDataChanged, "The result has been updated below the data.", "The result position remains unchanged.")&vbCrLf&_ "The overall scores have been sorted in descending order and ranking information has been added.", vbInformation End Sub ' ====== Natural breakpoint classification function ====== Function JenksClassification(scores() As Double, breakPoints() AsDouble) ' Copy and sort the scores Dim n As Long, i As Long n = UBound(scores) Dim sortedScores() As Double ReDim sortedScores(1 To n) For i = 1 To n sortedScores(i) = scores(i) Next i 'Call the sort subroutine Call SortArray(sortedScores) ' Initialize the breakpoint array breakPoints(1) = sortedScores(1) breakPoints(4) = sortedScores(n) ' Set classification boundaries according to the quartile standard breakPoints(2) = sortedScores(Application.WorksheetFunction.Max(1,Int(n * 0.03))) ' breakPoints(3) = sortedScores(Application.WorksheetFunction.Max(1,Int(n * 0.35))) ' breakPoints(4) = sortedScores(Application.WorksheetFunction.Max(1,Int(n * 0.65))) ' ' Ensure breakpoints are in order (ascending order) If breakPoints(2)<breakPoints(1) Then Call Swap(breakPoints(1),breakPoints(2)) If breakPoints(3)<breakPoints(2) Then Call Swap(breakPoints(2),breakPoints(3)) If breakPoints(4)<breakPoints(3) Then Call Swap(breakPoints(3),breakPoints(4)) End Function Function ScoreToCategory(score As Double, breakPoints() As Double) As String Select Case score Case Is >= breakPoints(4) ' ScoreToCategory = "优" Case breakPoints(3) To breakPoints(4) ScoreToCategory = "良" Case breakPoints(2) To breakPoints(3) ScoreToCategory = "中" Case Else ScoreToCategory = "差" End Select End Function ' Auxiliary function: Array sorting Sub SortArray(arr() As Double) Dim i As Long, j As Long, temp As Double Dim n As Long n = UBound(arr) For i = 1 To n - 1 For j = i + 1 To n If arr(i)>arr(j) Then temp = arr(i) arr(i) = arr(j) arr(j) = temp End If Next j Next i End Sub ' Helper function: swap two values Sub Swap(ByRef a As Double, ByRef b As Double) Dim temp As Double temp = a a = b b = temp End Sub

[0025] The present invention implements the core functions of the comprehensive evaluation model through customized VBA code: first, its entropy weight calculation module ensures the stability of weight assignment; second, the natural breakpoint method (Jenks Natural Breaks) of the FindJenksBreaks() core function adopts dynamic programming for classification optimization.

[0026] Table 1 Evaluation index system for 60 coffee seedling germplasm resources Germplasm number Stem thickness / plant height Taproot length / plant height Stem thickness / taproot length Leaf dry weight / total dry weight Stem dry weight / total dry weight Main root dry weight / total dry weight Root dry weight / total dry weight Dere 401 0.02 1.02 0.02 0.59 0.17 0.08 0.15 Dere 132 0.01 0.76 0.02 0.56 0.19 0.07 0.17 Dere 397 0.02 1.02 0.02 0.55 0.17 0.09 0.19 Dere 296 0.03 1.07 0.03 0.77 0.13 0.05 0.04 Dere 390 0.02 0.96 0.03 0.76 0.15 0.06 0.03 Dere 404 0.03 1.13 0.02 0.75 0.15 0.07 0.04 Dere 401 0.02 0.88 0.02 0.74 0.16 0.06 0.04 Dere 397 0.02 1.03 0.02 0.76 0.14 0.06 0.04 Dere 456 0.03 1.12 0.02 0.74 0.16 0.07 0.02 Dere 402 0.03 1.06 0.02 0.76 0.14 0.05 0.05 Dere 403 0.02 0.99 0.02 0.71 0.19 0.06 0.05 Dere 481 0.02 0.93 0.03 0.67 0.21 0.09 0.04 Dere 772 0.03 1.26 0.02 0.73 0.17 0.08 0.02 Dere 51 0.02 0.96 0.02 0.76 0.16 0.05 0.03 Dere 606 0.03 1.25 0.03 0.75 0.15 0.07 0.03 Dere 715 0.03 1.37 0.02 0.74 0.13 0.07 0.05 Dere 518 0.03 0.90 0.03 0.73 0.17 0.06 0.04 Dere 728 0.02 1.00 0.02 0.71 0.19 0.08 0.02 Dere 412 0.03 0.96 0.03 0.70 0.15 0.12 0.03 Dere 585 0.03 1.09 0.02 0.76 0.14 0.08 0.02 Dere 542 0.03 1.06 0.03 0.60 0.25 0.11 0.04 Dere 684 0.03 1.61 0.02 0.66 0.20 0.11 0.03 Dere 48 0.04 1.66 0.02 0.63 0.21 0.12 0.04 Dere 650 0.04 1.68 0.02 0.49 0.38 0.09 0.04 Dere 114 0.02 1.28 0.01 0.54 0.28 0.16 0.02 Dere 778 0.04 1.80 0.02 0.66 0.19 0.12 0.03 Dere 701 0.04 1.47 0.03 0.62 0.24 0.10 0.04 Dere 393 0.05 1.09 0.04 0.63 0.21 0.09 0.06 Dere 775 0.04 1.61 0.03 0.59 0.23 0.13 0.05 Dere 783 0.06 2.35 0.02 0.63 0.17 0.12 0.07 Dere 692 0.06 1.55 0.04 0.59 0.27 0.09 0.04 Dere 40 0.05 1.07 0.05 0.56 0.30 0.08 0.05 Dere 849 0.03 2.17 0.01 0.62 0.20 0.13 0.05 Dere 656 0.02 1.03 0.02 0.58 0.23 0.11 0.08 Dere 658 0.03 1.92 0.02 0.50 0.21 0.24 0.05 Dere 660 0.03 2.54 0.01 0.59 0.18 0.18 0.04 Dere 663 0.03 1.78 0.02 0.57 0.19 0.17 0.06 Dere 666 0.03 1.22 0.02 0.59 0.22 0.15 0.05 Dere 726 0.03 1.31 0.02 0.62 0.21 0.12 0.05 Dere 671 0.03 1.50 0.02 0.62 0.20 0.12 0.06 Dere 771 0.02 1.07 0.02 0.62 0.21 0.12 0.05 Dere 633 0.03 1.25 0.03 0.61 0.19 0.13 0.07 Dere 773 0.02 1.35 0.02 0.59 0.21 0.15 0.04 Dere 473 0.03 1.14 0.02 0.62 0.14 0.22 0.03 Dere 779 0.03 1.56 0.02 0.63 0.18 0.14 0.05 Dere 760 0.03 0.78 0.04 0.62 0.23 0.10 0.06 Dere 770 0.02 1.05 0.02 0.62 0.22 0.13 0.03 Dere 399 0.03 1.16 0.02 0.67 0.16 0.11 0.06 Dere 396 0.02 0.96 0.02 0.62 0.20 0.13 0.05 Dere 391 0.02 1.50 0.02 0.60 0.22 0.15 0.03 Dere 394 0.03 2.26 0.01 0.64 0.18 0.13 0.06 Dere 716 0.03 1.26 0.02 0.59 0.24 0.14 0.03 Dere627 0.02 1.02 0.02 0.61 0.25 0.12 0.02 Dere 518 0.02 0.89 0.02 0.60 0.25 0.12 0.02 Dere 638 0.02 1.02 0.02 0.49 0.38 0.09 0.04 Dere 628 0.02 1.27 0.02 0.76 0.15 0.08 0.01 Dere 631 0.03 1.25 0.02 0.62 0.23 0.13 0.02 Dere 725 0.02 0.93 0.02 0.60 0.24 0.12 0.04 Dere 63 0.02 1.18 0.02 0.67 0.15 0.10 0.07 Dere 392 0.02 1.09 0.02 0.62 0.22 0.12 0.04 The 60 coffee seedling germplasm resources listed in Table 1 are mainly from Coffea arabica ( Coffea arabica ) as its core, the collection systematically covers Yunnan's main cultivated varieties, introduced varieties, and independently cultivated lines, fully reflecting the current mainstream genetic backgrounds. Key phenotypic indicators show significant diversity: in root development, the taproot length / plant height ratio ranges from 0.76 to 2.54 (with Dere 132 having the lowest value and Dere 660 having the highest value); in biomass allocation, the ratio of leaf dry weight to total dry weight varies from 49% to 77% (with Dere 638 having the lowest value and Dere 296 having the highest value), and the ratio of stem dry weight to total dry weight fluctuates from 13% to 38% (with Dere 296 having the lowest value and Dere 650 having the highest value). Specialized germplasms, such as Dere 783 (root-to-shoot ratio of 2.35) and Dere 658 (taproot dry weight ratio of 24%), exhibit extreme phenotypes and provide key material for the targeted breeding of varieties with deep rooting or high storage roots. The 60 selected germplasm resources demonstrate high variability and broad coverage, accurately analyzing the genetic diversity underlying stress resistance traits in small-grain coffee seedlings.

[0027] Table 2 Weight distribution of each indicator Stem thickness / plant height Taproot length / plant height Stem thickness / taproot length Leaf dry weight / total dry weight Stem dry weight / total dry weight Main root dry weight / total dry weight Root dry weight / total dry weight 8.12% 17.88% 9.59% 9.33% 20.37% 16.35% 18.35% Table 2 shows the weighting of various indicators. In the comprehensive evaluation system for coffee seedlings, root development (taproot length / plant height, taproot dry weight / total dry weight, and fibrous root dry weight / total dry weight) dominates with a combined weight of 52.58%. The fibrous root dry weight ratio (18.35%) and taproot length to plant height ratio (17.88%) are particularly critical. Stem-related indicators (stem dry weight / total dry weight, stem diameter / taproot length, and stem diameter / plant height) are the second most important dimension, with a weight of 38.08%. Stem weight ratio (20.37%) is the highest weighted individual indicator. Biomass allocation (the dry weight ratio of roots, stems, and leaves) has an overall weight of 64.40%, significantly higher than the morphological ratio (35.59%), indicating that the distribution of dry matter among seedling organs is the primary criterion for evaluating seedling quality.

[0028] Table 3 Evaluation results of coffee seedling germplasm resources Germplasm number Comprehensive evaluation value Ranking resistance Dere 783 0.45 1 excellent Dere 658 0.42 2 excellent Dere 660 0.41 3 excellent Dere 692 0.40 4 excellent Dere 394 0.37 5 excellent Dere 775 0.37 6 excellent Dere 663 0.37 7 excellent Dere 40 0.36 8 excellent Dere 849 0.36 9 excellent Dere 650 0.35 10 excellent Dere 778 0.34 11 excellent Dere 393 0.34 12 excellent Dere 48 0.34 13 excellent Dere 701 0.34 14 excellent Dere 633 0.33 15 good Dere 779 0.32 16 good Dere 397 0.32 17 good Dere 671 0.32 18 good Dere 473 0.31 19 good Dere 666 0.30 20 good Dere 684 0.30 21 good Dere 391 0.30 22 good Dere 726 0.29 23 good Dere 760 0.29 24 good Dere 716 0.29 25 good Dere 773 0.29 26 good Dere 542 0.29 27 good Dere 401 0.28 28 good Dere 631 0.27 29 middle Dere 399 0.27 30 middle Dere 656 0.27 31 middle Dere 606 0.26 32 middle Dere 114 0.26 33 middle Dere 412 0.26 34 middle Dere 715 0.26 35 middle Dere 63 0.25 36 middle Dere 771 0.25 37 middle Dere 132 0.25 38 middle Dere 638 0.25 39 middle Dere 392 0.25 40 middle Dere 396 0.24 41 middle Dere 770 0.24 42 middle Dere 481 0.24 43 middle Dere 772 0.24 44 middle Dere 404 0.24 45 middle Dere 725 0.24 46 middle Dere 296 0.23 47 middle Dere 627 0.23 48 middle Dere 518 0.23 49 middle Dere 402 0.22 50 middle Dere 585 0.22 51 middle Dere 518 0.22 52 middle Dere 456 0.22 53 middle Dere 628 0.22 54 middle Dere 403 0.21 55 middle Dere 390 0.21 56 middle Dere 728 0.20 57 middle Dere 397 0.20 58 middle Dere 401 0.19 59 Difference Dere 51 0.18 60 Difference Table 3 shows the significant superiority of a comprehensive evaluation method for coffee germplasm seedlings developed using the entropy weight method and Excel-VBA. Its results cover a score range of 0.45 to 0.18. Using the natural breakpoint method (Jenks), the algorithm objectively identifies the inherent distribution structure of the data and automatically categorizes the results into four gradients: "Excellent," "Good," "Fair," and "Poor." The algorithm accurately captures the key cutoffs: a natural transition between the "Excellent" (top 14) and "Good" (0.28-0.33) grades occurs at Dere 473 (0.31, the first "Good" grade). The algorithm also defines the "Fair" (0.20-0.27) and "Poor" (≤0.19) grades. The automatically numbered cultivar Dere 518 (evaluation values ​​of 0.23 and 0.22) was accurately distinguished, demonstrating the method's ability to accurately process 60 large samples. The evaluation results show that varieties with the same score (e.g., 5th-7th-ranked Dere 394, 775, and 663, all with a score of 0.37) maintain a consistent ranking; varieties with different scores (e.g., 28th-ranked Dere 401 (0.28) and 29th-ranked Dere 631 (0.27)) exhibit a reasonable interval. Crucially, the final output resistance classification (excellent / good / fair / poor) closely aligns with the evaluation value gradient derived from the natural breakpoint method, forming a clear, objective decision boundary based on the inherent structure of the data. This method, which achieves objective weighting through the entropy weight method and combines it with automated scientific grading (based on natural breakpoints) efficiently calculated using VBA, can directly guide breeding decisions. For example, the top 14 "excellent" varieties (Dere 783 to Dere 650) can be selected for core germplasm selection. This fully demonstrates the scientific value and practical advantages of this automated system in leveraging the inherent characteristics of data for objective grading in efficient germplasm screening.< / minval> < / newdatastartrow>

Claims

1. A comprehensive evaluation method for coffee germplasm seedlings based on Excel-VBA, characterized by: The following steps are involved: S1: Obtaining raw data of coffee seedlings, including stem diameter, plant height, taproot length, leaf dry weight, stem dry weight, taproot dry weight, and total dry weight; S2: Establishing an evaluation index system, the evaluation index system includes the following seven ratio indicators: stem diameter / plant height, taproot length / plant height, stem diameter / taproot length, leaf dry weight / total dry weight, stem dry weight / total dry weight, taproot dry weight / total dry weight, and fibrous root dry weight / total dry weight; S3: Automatically generate comprehensive evaluation results based on the Excel VBA platform, including: S3.1: Establish a comprehensive evaluation model, which includes the following sub-steps: S3.1.1 Dynamic processing of data boundaries: Define the starting row, ending row, and column range of original and new data, and dynamically exclude specific invalid rows; S3.1.2 Data Standardization: Process the data using the range standardization method and store the results in a temporary array; S3.1.3 Calculate weights using the entropy weight method: Calculate the information entropy and variance coefficient of each indicator based on standardized data, determine the weight value, and store it in a temporary array; S3.1.4 Calculate and rank the composite scores: Calculate the composite scores of the samples by combining the standardized data and weight values, and generate rankings in descending order; S3.1.5 Comprehensive score resistance classification: The natural breakpoint method (Jenks) is used to classify the comprehensive score into four levels: "excellent", "good", "moderate", and "poor"; S3.2: Construct a visualization interface, which includes the following sub-steps: S3.2.1 Data storage specifications: Basic data is stored in rows 2-61, with row 1 being the header row; S3.2.2 Input data: Line 62 is the header for the input data area. Data starts at line 63, and the first cell of the last line is labeled "Indicator Weight." S3.2.3 Create a "Comprehensive Evaluation" form button: Associate the macro command Entropy Weight Method For NewData () to automatically execute the comprehensive evaluation process when triggered; S3.2.4 Results presentation: Output indicator weights, comprehensive scores, rankings, and resistance levels in designated areas.

2. The method according to claim 1, characterized in that In step S3.1.1, the data range is dynamically identified by VBA, and the invalid row 62 is automatically excluded.

3. The method according to claim 1, characterized in that The data normalization formula in step S3.1.2 is: Where, For the Indicator No. data The standardized value of For the The maximum value among the indicators, For the The minimum value among the indicators.

4. The method according to claim 1, wherein The entropy weight method of step S3.1.3 includes: Calculating information entropy , Calculate the coefficient of variation , Calculating weights .

5. The method according to claim 1, wherein The natural breakpoint method in step S3.1.5 is implemented by VBA using Jenks natural break algorithm.

6. The method according to claim 1, wherein The result presentation of step S3.2.4 includes: (1) The indicator weight is output to the next row of the "Indicator Weight" cell; (2) The comprehensive score is output in three columns: data number, score value, and ranking; (3) The resistance level is displayed in the adjacent right column of the input data area.

Images