Teach you how to get started with zero-based Excel table editing and quickly master Excel

time:2022-12-08 05:56:15 source:scripttoolbox.com author:Camera
Teach you how to get started with zero-based Excel table editing and quickly master Excel

When it comes to Excel, everyone is familiar with it. No matter what industry you are in, you must use it to make a statistical or explanatory table more or less. Excel tables can not only be used at work, but also help others edit tables to earn extra money in their spare time. So if you are just starting to learn Excel, or you want to know more about Excel, you can actually read my article, which can add more Excel knowledge to everyone and bring you various conveniences. So let me teach you step by step:

Step 1: Familiarize yourself with the architecture

First, I took the 2016 version of OFFICE Excel As an example, as soon as you open Excel, it is best to first click on the toolbars such as File, Start, Insert, Page Layout, Formula, Data, Review and View, etc. to familiarize yourself with the structure and know where each tool is. When we are familiar with the tools, we can also call up their shortcut keys. How to adjust them? In fact, it is very simple. We only need to press the Alt key in the normal interface to pop up the shortcut key of the toolbar. It will be marked in gray font. Later, we can press and hold Alt + any gray shortcut key to jump to the desired item. Jump to the required item menu bar, such as the picture below, after jumping to the start item bar, the tool about editing class will appear and its shortcut key will be displayed, remember that we can directly use Ctrl+Shortcut after we are familiar with it. key to use this tool directly. Provide effective support for our tabulation efficiency.

Step 2: Use of shortcut keys

We talked about being familiar with the architecture above, and most of the shortcut keys are on it. There are some easier to use but not marked out, I will add to you. Among them, ctrl+e is a shortcut key for quick filling. This key is very useful. Since the Office 2013 version, the function key combination of Ctrl+E has made our daily work a lot easier, which is equivalent to the function of format brush. 1. Batch merge/extract content and add symbols When processing table files, it is usually necessary to process the contents of cells, such as merging data and adding symbols in batches. Let's take the following figure as an example. Names and groups exist independently of each other. You just need to enter the target value you want in the first cell and press Ctrl+E to do it easily. 2. Batch replacement of characters In the past, when we encountered character replacement, we always used Ctrl+H to do data replacement, or used REPLACE and SUBSTITUTE functions to complete. But now using the efficient Ctrl+E, just enter the target value in the first cell, press Ctrl+E, and the text and symbols can be replaced with one click.

Step 3: Application of functions

Next, I will start to learn some basic functions, such as sum , sumif , mid , left , right, vlookup after learning these functions You can organize data sources, then learn pivot tables, make a simple table, and then draw a graph or something, which can reach the primary level. 1. Digital processing 1. Absolute value = ABS (number) 2, rounding = INT (number) 3, rounding = ROUND (number, decimal places) 2. Judgment formula 1, display the error value generated by the formula as empty Formula: C2=IFERROR(A2/B2,"") Description: If it is an error value, it will be displayed as empty, otherwise it will be displayed normally. 2. IF multi-condition judgment return value formula: C2=IF(AND(A2<500,B2="Not yet due"), "Repayment", "") Description: Use AND if two conditions are established at the same time, either one is established Use the OR function. 3. Statistical formula 1. Count the duplicated content of two tables. 2. Count the total number of non-repeated people Formula: C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) Description: Use COUNTIF to count the number of occurrences of each person, and divide the number of occurrences into the denominator by 1 , then add up. 4. Summation formula 1. The summation formula for every column: H3=SUMIF($A$2:$G$2,H$2,A3:G3) or =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0 )*B3:G3) Note: If there is no rule in the header row, use the second formula Conditional Fuzzy Summation Formula: See the following figure for details: If you need to perform fuzzy summation, you need to master the use of wildcards, in which an asterisk represents any number of characters, such as "*A*", which means that there are arbitrary characters before and after a Multiple characters, i.e. including A. 4. Multi-condition fuzzy summation formula: C11=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) Description: Wildcard *5 can be used in sumifs, multi-table summation formula at the same position : b2=SUM(Sheet1:Sheet19!B2) Description: After deleting or adding a table in the middle of the table, the formula result will be updated automatically. 6. Sum formula by date and product: F2=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25 ) Description: SUMPRODUCT can complete multi-condition summation 5. Search and reference formula 1, single-condition search formula Formula 1: C11=VLOOKUP(B11,B3:F7,4,FALSE) Description: Search is the best at VLOOKUP, basic usage 2. Two-way search formula Formula: =INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0)) Description: Use the MATCH function to find the position, and use the INDEX function to take the value 3 , to find the last record that matches the condition. Formula: See the following figure for details: 0/(condition) can turn the unqualified into an error value, and lookup can ignore the error value

Step 4: Learn to use Pivot table

1. Preliminary data collation Before creating a pivot table, you must first do a good job in preliminary data collation. Although the power of the pivot table is extremely powerful, the premise of using it is that the data source must be standardized, otherwise it will bring layers of obstacles to the later creation and use of the pivot table, or even impossible to create the pivot table. The data specification is to check to avoid the following problems in the data source: 1. It cannot contain empty fields (including empty fields, and cannot make a pivot table) 2. It does not contain the same fields (the same field name, the serial number will be automatically added to show the Difference) 3. There cannot be merged cells (the row where the field is located has merged cells, which is equivalent to an empty field, and a pivot table cannot be created) 4. There cannot be an empty row (if there is an empty row, it will be treated as a null value) 5. It does not contain text-type numbers (text-type numbers will cause errors in the summary and summation results), so we have a problem when creating a pivot table. In most cases, we need to check the data source to rule out whether the above problems exist. 2. Create a PivotTable After finishing the data in the early stage, you can start to create a PivotTable. Step 1: Select any cell in the data source area Step 2: Click [Insert] in the toolbar Step 3: Select [PivotTable] under Insert Step 4: Create a PivotTable window in the pop-up window , click [OK] Step 5: Select the column, row, and value fields and drag them to the corresponding areas below 3. Understand the pivot table interface The structure of the pivot table includes report filter area, column area, row area, and value area. There are many small partners who are new to Pivot Tables who do not know much about these areas of Pivot Tables. We use a picture to tell you: drag and drop fields in different areas, and the summary results will be displayed in different display ways. Moreover, the order in the same area is different, and the level of summary in the pivot table will also be different.

Step 5: Methodology

Finally, make a summary, how should we learn when we are learning Excel? In addition to the above-mentioned knowledge points we need to master, we also need: 1. Problem-oriented, if you encounter problems at work, you can find shortcuts yourself, and solve one problem when you encounter it. 2. There must be a batch method for repeated things. If it is repeated more than three times, then you have to think about whether there is an easy way. 3. Systematic learning. Many people have mastered a lot of small skills in the process of watching Xiao E's official account, but they are still confused when they encounter problems. This is where the problem lies.

(Responsible editor:CPU)

Related content