2012-1-14 its panther article source uses Excel VBA to automatically generate personalized charts in educational statistical analysis.
Author:
Source: China Education Informatization Basic Education 20 12 9.
Font size: large, medium and small
Display Chinese Pinyin
Abstract: The advantages of images are vivid and informative, and they have obvious advantages in reflecting the changing trend of data. At present, Excel has been widely used in education management and education research, and mastering the method of making charts has become a compulsory content. However, in the management of educational statistics, sometimes we want to interpret the data in a unique way so that personalized charts can highlight the important information in the data. Therefore, this paper first introduces how to make Excel charts by manual operation, then generates fixed format charts in the system through VBA programming, and finally focuses on how to automatically generate personalized charts through Excel VBA to serve education management and research.
Keywords: statistical analysis; Automatically generated; Personalized chart
China Library Classification Number: TP3 19 Document Identification Number: A Document Number:1673—8454 (2012)18—0068—05.
Due to the development of science and technology and the accelerated pace of life, we have entered the era of reading pictures. We have the experience that we may not remember a series of numbers and their relationships and trends, but we can easily remember a picture or a curve. In the research of educational statistics, it is necessary to find new phenomena or laws from a large number of data as soon as possible in order to optimize management and guidance. In the analysis of "Jiangsu province primary and secondary school students' academic quality test", it is a good example to reflect the dispersion of test results concisely through "box diagram" and provide an intuitive method for educational evaluation.
At present, Excel has been widely used in education management and education research, and mastering the method of chart making has become a compulsory content. However, in the management of educational statistics, sometimes we want to interpret the data in our own unique way, so that the personalized charts show a changing trend, thus highlighting the important information in the data. This paper first introduces how to make Excel charts by manual operation, and then generates fixed format charts in the system by VBA programming. Finally, it focuses on how to use Excel VBA to automatically generate personalized charts to serve education management and research.
A, manual operation chart.
Charts can be used to represent some correspondence between data. We generally use column chart to compare the relationship between data, line chart to reflect the trend relationship between data, pie chart to show the proportional distribution relationship between data and so on. Taking making "column chart" as an example, this paper briefly introduces the method of making charts by hand with Excel.
(2) Click the Insert menu, select the column chart on the tab, and select the 2D column chart in the column chart series. The chart appears, and the menu of design, layout and format appears in the menu bar.
(3) Under the "Design" menu, click "Switch Row/Column", select a series, and generate rows or columns as needed. Chart Style allows you to choose the style of the column chart (in this case, style 26 is selected).
(4) Click the layout menu, and through the tab, you can perform operations such as title, legend and axis. For example, in this example, select "Picture Title", select "Above the Picture" in the drop-down option, and enter "Comparison of the Second Survey Results".
Third, use Excel VBA to automatically generate personalized charts.
Although Excel can generate a variety of charts, the formats of these charts are fixed in Excel system. In education management and research, sometimes we want to interpret the data in our own unique way, so that personalized charts can highlight the important information in the data. So, how can we achieve such a function?
Taking making "box diagram" as an example, this paper introduces how to make personalized diagrams.
In educational measurement, we often use box chart to concisely reflect the dispersion degree of grades. Rank the total scores of all students from low to high. The lower short line indicates that about 5% of the students' scores are lower than the corresponding scores, and the upper short line indicates that about 5% of the students' scores are higher than the corresponding scores. The scores corresponding to the top line, middle line and bottom line of the box show that about 25%, 50% and 75% of the students' scores are higher than those corresponding to this line. The center line of the box chart describes the average grade of students, and the height of the rectangular box in the box chart intuitively describes the dispersion degree of students' grades to some extent, which provides a convenient means for education management.
2. How to realize the "basic elements" when generating personalized charts?
(1) Draw a line segment.
Through VBA program, draw a dotted line, two short lines up and down, a vertical line segment, etc.
It can be achieved by shape. AddLine method and use it to draw a line.
Grammar: expression. AddLine(BeginX，BeginY，EndX，EndY)
Parameters represent the coordinates of the starting point and the ending point of the line respectively.
The relationship between line spacing and fractional value is that 1 fractional value corresponds to n times of points.
(2) draw a text box
Draw a "box" representing hierarchical dispersion, set the height and color of the box and draw a rectangular background.
You can create a text box with these shapes. AddTextbox method.
Grammar: expression. AddTextbox (direction, left, top, width, height)
These parameters indicate the direction of the text frame, the position of the upper left corner of the text frame relative to the upper left corner and top of the document, and the width and height, respectively.
(3) Draw an ellipse
You can draw an "ellipse" through these shapes. AddShape method, which means the scores are equal.
Grammar: expression. AddShape (type, left, top, width, height)
These parameters represent the shape of the autoshape (ellipse), the position of the upper left corner relative to the upper left corner and top of the file, and the width and height.
What kind of investment should be made in the informatization of cadre education? Wenbotong (Beijing) Technology Co., Ltd. can help you.
I. Cost input
The cost of informatization construction includes software cost, hardware cost and operation cost. Hardware costs, including computer equipment and network equipment, can be estimated according to market prices. Software cost is usually determined by development cost. At present, the calculation method of enough software package modules is generally adopted. Software development costs can be calculated according to modules, functions and secondary development, and secondary development costs can also be included in the purchase cost of system software. Then there is the cost of system support and upgrade.
Two. Choose mature products
At present, the price of hardware and equipment in the domestic market is declining, and most cadre training institutions also have a certain hardware and equipment foundation. Hardware infrastructure construction has less pressure on informatization cost, and the core of informatization cost is the continuous innovation of software solutions. The informatization construction of cadre training institutions should pay more attention to the perfection and sustainable development of software systems.
Choose mature information products. According to the long-term development direction, the system of contact transaction processing, online analytical processing, data analysis and integration will be implemented purposefully and step by step. It can not only give full play to the functions of existing equipment, but also have the ability to continue to provide services for subsequent information systems, thus maximizing the scope of the previous information functions.
Secondly, we need to pay attention to the maturity, integrity, compatibility and expansibility of the software, and whether it can meet the needs of long-term planning of cadre training institutions. Therefore, choosing a mature and extensible information system is also the key to saving costs.
Three. Choose high-performance products
At present, domestic software companies have no "price advantage" but only "price disadvantage". Ignoring the professionalism of the industry makes many organizations not consider the cost when choosing information products, but try to reduce the investment in the later implementation, maintenance and service stages, which eventually leads to the embarrassing situation of the project and increases the software cost and operating cost of the information system.
Nowadays, information technology is changing with each passing day. Information management and its application have gone through three stages of development, from file system to traditional database management system, and then to data management application system. At present, it has entered the era of diversified data structure, heterogeneous storage and intelligent decision-making. Therefore, the choice of information products depends on whether its manufacturers have the ability to develop for a long time and absorb new and innovative information technologies. Only the information system with sustainable expansion package can realize the maximum benefit of information construction.
This paper discusses how to use SWOT analysis method to analyze information. SWOT analysis is an internal analysis method of enterprises, that is, according to the established internal conditions of enterprises, find out the strengths, weaknesses and core competitiveness of enterprises. Where S stands for strength, W stands for weakness, O stands for opportunity and T stands for threat, where S and W are internal causes and O and T are external causes. According to the complete concept of enterprise competitive strategy, strategy should be an organic combination of what an enterprise can do (that is, the advantages and disadvantages of the organization) and what it can do (that is, the opportunities and threats of the environment). The competition theory put forward by Michael Porter, a famous competitive strategy expert, thoroughly analyzes and explains the "possibility" of an enterprise from the perspective of industrial structure, while competency-based management scientists use the value chain to deconstruct the value creation process of an enterprise and pay attention to the analysis of the company's resources and capabilities. On the basis of synthesizing the former two, SWOT analysis combines the internal analysis of the company (that is, the research orientation concerned by the management authorities in the mid-1980s, represented by the ability school) with the external analysis of the industry competitive environment (that is, the central theme concerned by the early strategic research, represented by Andrews and Michael Porter) to form its own structured balanced system analysis system.
Compared with other analysis methods, SWOT analysis has obvious structural and systematic characteristics from the beginning. As far as structure is concerned, first of all, in form, SWOT analysis is to construct a SWOT structure matrix and give different analytical significance to different areas of the matrix; Secondly, the main theoretical basis of SWOT analysis also emphasizes the analysis of the external environment and internal resources of enterprises from the perspective of structural analysis. In addition, as early as the 1960s before the birth of SWOT, some people put forward the internal strengths, weaknesses, external opportunities and threats involved in SWOT analysis, but only analyzed them in isolation. The important contribution of SWOT method lies in matching these seemingly independent factors with systematic ideas for comprehensive analysis, which makes the formulation of enterprise strategic plan more scientific and comprehensive.
Since its formation, SWOT method has been widely used in enterprise strategic research and competitive analysis, and has become an important analytical tool for strategic management and competitive intelligence. Intuitive analysis and simple use are its important advantages. Even without accurate data support and more professional analysis tools, we can draw convincing conclusions. However, it is this intuition and simplicity that makes SWOT inevitably have the defect of insufficient accuracy. For example, SWOT analysis adopts qualitative methods, and forms a fuzzy description of the competitive position of enterprises by enumerating various manifestations of S, W, O and T, and the judgment made on this basis is bound to be subjective. Therefore, when using SWOT method, we should pay attention to the limitations of this method. When listing facts as the basis of judgment, we should try our best to be true, objective and accurate, provide some quantitative data, make up for the deficiency of SWOT qualitative analysis, and build the foundation of high-level qualitative analysis.
In recent years, SWOT analysis has been widely used in many fields, such as school self-analysis and personal ability self-analysis.
For example, when analyzing your career development with SWOT, you can follow the following five steps:
The first step is to evaluate your strengths and weaknesses.
Everyone has his own unique skills, talents and abilities. In today's very fine division of labor environment, everyone is good at a certain field, not proficient in everything. Unless, of course, you are a genius. For example, some people don't like sitting in the office all day, while others feel numb and nervous at the thought of having to deal with strangers. Please list the things you like to do and your advantages. Similarly, through the list, you can find out what you don't like to do and your weaknesses. Finding your own shortcomings is as important as finding your own advantages, because you can make two choices according to your own advantages and disadvantages; Or try to correct normal mistakes, improve their skills, or give up those departments that need skills that they are not good at. List the strengths and weaknesses that you think you have that have an impact on your study choice, and then mark those that you think are important to you.
Step two, find out your career opportunities and threats.
We know that different industries (including different companies in these industries) face different external opportunities and threats, so finding out these external factors will help you successfully find a job that suits you, which is very important for your job search, because these opportunities and threats will affect your first job and future career development. If the company is in an industry that is often affected by external unfavorable factors, naturally, the company can provide fewer career opportunities, and there is no chance of career promotion. On the contrary, industries full of many positive external factors will provide job seekers with broad career prospects. Please list one or two industries you are interested in, and then carefully evaluate the opportunities and threats faced by these industries.
The third step is to outline your career goals in the next 3-5 years.
Carefully make a SWOT analysis and evaluation of yourself, and list four or five career goals that you want to achieve most in five years. These goals can include: what kind of career you want to pursue, how many people you will manage, or what level of salary you want to get. Please always remember: you must try your best to give full play to your advantages and match them perfectly with the job opportunities provided by the industry.
The fourth step is to outline the career action plan for the next 3-5 years.
This step mainly involves some specific contents. Please draw up an action plan to achieve each of the goals listed in the third step above, and explain in detail everything you have to do to achieve each goal, and when to finish these things. If you think you need some outside help, please explain what kind of help you need and how to get it. For example, your personal SWOT analysis may show that in order to achieve your ideal career goals, you need to take more management courses. Then, your career action plan should be clear about which courses to take, what level of courses to take and when to take these courses. Your detailed action plan will help you make a decision, just as a plan made in advance before traveling will be your action guide.
Step 5: Seek professional help.
It is not difficult to analyze a person's shortcomings in career development and behavior habits, but it is difficult to change them in an appropriate way. I believe that your friends, superiors and career counseling experts can give you some help, especially with the help of professional consulting power, you will take shortcuts. With the help and supervision of external forces, you will get better results.
Can patent information analysis system be used for patent retrieval? Yes, the landlord can prove how much he knows about this idea, except what the landlord has said so far.
There are three kinds of patent retrieval: paper retrieval, software retrieval and intellectual property retrieval.
Search before R&D: Before R&D, we can determine whether the technological innovation has been patented or patented by others through IncoPat's scientific and technological innovation information platform;
Novelty search before patent application: it can be determined whether the technical scheme is novel before application, so as to determine whether the technical scheme can apply for a patent;
Search to prevent infringement: through search, the possibility that products manufactured or sold fall into the scope of patent protection of others can be ruled out;
Evidence collection in invalid procedure: the retrieved previously disclosed technology can be used as evidence to question the novelty and creativity of the other party's patent right in invalid procedure.
How to educate the dog? . . Be sure to fight. . . When I was a child, I had to beat it. . .
If it doesn't listen now.
It will be amazing when you grow up. . .
Try harder. Let it know the pain. It works well when it squeaks.
If you hit it gently. If you make a mistake this time, you will be beaten like this. Dare to do it again next time. . .
Let it know. If you do something wrong, you will be beaten.
How teachers capture and effectively use educational information can be considered from the following aspects:
1, cultivate sensitivity to educational information
2. Seize the opportunity of flashing educational information.
3. Grasp the current situation of educational information.
4. Analyze and judge the value of educational information.
5. Ingeniously transformed into dynamic research resources.
However, without a fixed teaching method, the classroom changes rapidly, which requires teachers to have strong classroom control ability and respond to various changes that may occur in the classroom in time. On the one hand, they need to have strong basic teaching skills, on the other hand, they need to actively sum up their usual experience, and on the other hand, they need to consult experienced teachers humbly.
How to write educational information? From the perspective of others, educational information plays a learning opportunity and an educational opportunity. 3. As far as schools are concerned, educational information plays a communication role and promotes exchanges between schools. At the same time, it has improved the school's popularity and school-running taste. 4. Administratively, educational information provides necessary information for the work deployment of the Education Bureau.
Second, the framework of educational information 1, segmented educational information is generally dominated by two or three paragraphs. The first paragraph briefly describes the time, content and place (school) of the information. The middle paragraph gives a concrete (not detailed) description (narrative+explanation) of the information to be written, and names the educational function of the information. 2, the number of words is about 250 words. Of course, as far as specific educational information is concerned, if the content is rich, you can write three or four hundred words.
Iii. the theme of educational information 1. Within a certain range, it is not limited by time. The range can be one year or two years. For example, small class teaching has its own characteristics. Middle school can be a semester or a month, which is generally a continuous activity. Primary and secondary schools train teachers in information technology. 2. Limited by time. Generally, it is to write about the trend of the school within a week. 3. The writing of educational information should take teaching as the main theme and choose multiple topics in school teaching activities.
Fourth, the content of educational information 1 is orderly. The amount of educational information is small, and it must be concise, orderly and complete, so that people can easily read the full text in a short time. 2, there is a problem with words. It is necessary to make clear the purpose of writing educational information, stick to the theme and expand the expression of a complete information. 3. That makes sense. Activities arranged by the school always have a certain purpose. When writing educational information, we should also make clear the educational purpose contained in the information. For example, in order to improve students' legal awareness, a report meeting on legal education was held. 4, the words are intentional. As an information officer, we should also see the profound significance implied in educational activities. For example, by watching safety education films, students can understand more: obeying traffic rules means cherishing life.
How the reporting tool FineReport uses charts to analyze data charts is a report form that is used more and more now. Using charts has obvious advantages over general data display. The data is also analyzing the chart, which is a two-way interpretation.
At present, FineReport has realized column chart (7 kinds), line chart (1 kind), bar chart (7 kinds), pie chart (2 kinds), area chart (4 kinds), scatter chart (1 kind), bubble chart (1 kind) and radar chart (/. There are 16 styles and 37 charts, including combination chart (1), map (4), Gantt chart (1), gis map (2) and doughnut chart (2).
In the chart style of 16, the four basic chart types are histogram, bar chart, line chart and pie chart. More special charts, such as maps, often have eye-catching effects if used well.
Histogram is a statistical graph with the length of a rectangle as a variable. Vertical bars represent the distribution of data and are used to compare two or more values (at different times or under different conditions). There is only one variable, which is usually used for the analysis of smaller data sets. It has a good effect on comparing the specific changes and development trends of two or more materials with the same properties.
Line charts can display data that changes with time, so line charts are suitable for analyzing data changes at the same time interval. In general, the category data in a line chart are evenly distributed along the horizontal axis, and the specific data values of all information are evenly distributed along the vertical axis. Usually, it is better to analyze the data together with line charts and column charts, which not only compare the specific values, but also compare the overall development trend, taking into account the trivial and overall situation.
Bar chart can be understood as horizontal bar chart, which is mainly used to analyze and compare the contrast between various projects. Bar charts are needed when there are few horizontal items and a large vertical numerical span of data that need chart analysis.
The rest of the chart analysis can focus on Fan Ruan. /related documents.
Use the network system to test the qualification certificate of educational function lectures, and you can train online according to your own time. As long as you go in, the effect is the same.
On the positive side:
1 Online learning can be decided according to your own time, and the length of learning time can also be controlled to ensure your own efficiency.
If you don't understand, you can listen again and again, and it's easy to take notes.
The price of online training courses is cheaper than that of applying for classes, and it is much more convenient to study there.
Disadvantages are:
Can't communicate with the teacher in real time. When there is a problem, I can only calm down and really learn to solve the problem independently.
How to judge which charts to use when analyzing data visually with charts? EXCEL is mainly a line chart, radar chart, histogram, pie chart and other common statistical charts. There is no such chart as word Cloud, but EXCEL data is powerful, and many data analysis can be completed by functions such as functions and pivot tables. In addition, EXCEL has some fixed analysis templates that users can use directly and conveniently. BDP Personal Edition has many analysis functions, such as high-order calculation of ratio, retention rate and repetition rate, as well as various charts, such as line chart, bar chart, biaxial chart, pie chart, GIS chart, provincial chart, word cloud, funnel chart, Zanguitu and so on.