Office software is closely linked to the PDF,the PDF is also must have to control!
Set home Page Add to Favorites
|
three or more formula and the name of the formula, name, such as the PivotTable cell range quoted is too large The second point because of similar reasons, we in the definition of names, to prepare the formula specified PivotTable data source is often convenient moment map, and specify too large a range of cells. For example, in column A, including the title, including 10 data (A1: A10), entitled "Name", we have to define a name, such as "Name", many people will use Insert => Name => designated = > the first line, this is certainly convenient, but this is the case the "name" refers to the name on the A2: A65536, rather than the actual A2: A10. Can you imagine the two of difference? At this time, some friends say: I do so because the data I was growing it, I do not want to change every time when they are to change the name. Of course, you are right, who would willing to do so? When I am convinced that the definition I quoted in the name of the scope can not be fixed, I used a means of "a dynamic named." Sounds familiar right? See this example: For simplicity, we assume that data are continuously added in the A10 behind the start, that is, we hope that when we added to the A15, this "name" on the point as I would like to A2: A15, and All this is done automatically. Then you can "insert" => "name" => "definition" dialog box, find the "name", and then modify the location for the quote = OFFSET ($ A $ 2,0,0, COUNTA ($ A: $ A) -1,1), and then click "Add." Please stay, do not hurry to close this dialog box. You can now click on the "reference position" inside the box, thus to verify the results you want. See it? Worksheet that fleeting flash of the region, said the current "name" quoted in the range of cells. This is just a simple demonstration, the use of such techniques, may enable us to use the most economical manner the results we need. Quoted in the formula, in the specified PivotTable data source, they can use similar techniques. Of course, I would not recommend you to write the formula like this = CountA (OFFSET ($ A $ 2,0,0, COUNTA ($ A: $ A) -1,1)), but recommend that you first define this name, and then to write this formula: = CountA (name) This results in an array formula in a more obvious, unless you have sufficient patience and courage, please do not use references in an array formula is too large range of cells, especially those unnecessary cells. Please note that the above checks should be in the workbook for all staff, including hidden four, VBA code, in particular the impact of UserForm Many of my friends have now learned how to use VBE to build their own projects, how exciting this is the one thing! But today we have to discuss how to deal with VBA project could cause some side effects and how to compress it? There are many tests have proven that users will form a document is to increase the size of one of the reasons for the more prominent. Repeatedly to rewrite the module code works, it will increase more or less the size of the file (we can interpret it this way: the project to rewrite the code again and again, always leave some traces and debris) For this problem, it may be considered for all modules, forms, are derived for the file, then save, and then turn into. On the user form, as a developer should consider is: Is it really necessary to use a form? Here is a simple example, if only to receive user input values can be used on the InputBox method or property, without having to use a special form. At the same time, the work itself can be used to make user interaction interface, should make full use of. |
