Use Excel to become a true "full war" engineer

Because of their work, most programmers have little contact with Excel. Once they encounter problems related to Excel, their first reaction may be to use code to solve them. In fact, if the data is already in Excel, it can be easily solved with the functions of Excel itself. In addition, if you are familiar with the formula structure of Excel, you can also use Excel to create program codes in batches in some scenarios. This article uses a few cases to show you the skills in this regard.

The main user groups of Excel, the spreadsheet software, are finance, HR, and information workers who need to process and analyze data in various departments. Because of my work, I have a lot of dealings with my cousins, cousins, and cousins. So I often hear their complaints:

"Our company's IT doesn't even know VLOOKUP"
"I just wanted to convert a two-dimensional table to a one-dimensional table, but my BF actually said that I have to wait until he is free to type the code."

In their minds, programmers with professional CS background should take all software problems, including but not limited to Excel.

But this is obviously a misunderstanding, because most programmers rarely interact with Excel in their work, they don't know VLOOKUP, and it's normal to want to code everything. Programmers are the kings of the information age. There is nothing that can’t be done with a single line of code. If it doesn’t work, then write a few more lines.

I also have a lot of programmers and siege lion friends, and occasionally help them solve small problems in Excel. I have always had an opinion that anyone will encounter Excel in their lifetime, and it is very valuable to know a little bit in advance and light up this skill tree. Moreover, for programmers with deep knowledge, the speed of learning Excel must be 10 times faster than that of ordinary people. Below, I will share with a few examples, even programmers can use Excel to improve the speed of drawing swords!

splicing SQL conditional statement

For example, the business department suddenly sends you a form to let you process the data in the background.

This is a temporary task. You need to write a SQL statement. Whether it is UPDATE or DELETE, you need a conditional statement. How can these dozens of data be quickly written into a conditional statement?

Let's talk about the main point first: if the data is already in Excel, you must firmly believe that Excel is the most efficient and labor-saving tool.

We can write the formula in B2:

=B1&","&"'"&A2&"'"
Double-click the fill handle in the lower right corner of B2 to automatically copy the formula to the entire column.

Finally, copy the B25 cell directly, paste it into your SQL editor, edit it a little, and you're done.
picture

'&' is used to concatenate strings, numbers or cells in Excel formulas. In the concatenation process in this example, single quotes are used. If you need to splice out the effect of double quotes in some programming languages, you can use the following formula:

=B1&","&""""&A2&""""

SQL Continuously process multiple data tables

Because some databases use the table-partitioning technology, the same type of data is stored in a certain data table according to the established rules. If you occasionally need to process a data according to conditions, you must traverse all the tables.

For example, device_list_0 to device_list_9 are now used to store device-related information. If you want to temporarily delete a record with device_no of 78262170fa33, you don't know which table the record is in. A simple way is to delete each table directly. At this point, you can use Excel to construct SQL statements in batches.

Open Excel and write the formula in cell A1:

= "'device_list_"&ROW()-1&"'"

Write the formula in cell B1:

="delete from "&A1&" where 'device_no' = '78262170fa33';"
This completes the first statement.

Select A1:B1, copy the formula down to A10:B10, and get all the required SQL statements!

Using this splicing idea, it is convenient to complete the "duplicate code" in various programming languages, and you can flexibly handle it according to the actual situation.

Handling duplicate records

In the process of development and operation and maintenance, it is inevitable to face duplicate data. Although the initiator is not yourself, most of the people who need to work overtime are yourself.

For example, in the data table provided by the business department, there may be duplicates in device_no. These duplications cannot be easily dealt with. They must be marked first and then checked.

To repeat the important words: If the data is already in Excel, you have to believe that Excel is the most efficient and labor-saving tool.

Faced with this situation, you only need to select any cell of the data table first, and then click [Conditional Formatting] → [Highlight Cell Rules] → [Duplicate Values] under the [Home] tab to solve the problem.

Well, then all duplicate values are flagged.

Now right-click B2, which is the first marked cell, and click [Filter] → [Filter by Color of Selected Cell] in the pop-up shortcut menu.

This filters out all duplicate records.

In addition, Excel also provides the "Remove Duplicates" function, which can directly delete duplicate records. You can also use the COUNTIF function to exclude duplicates after the first or before the last, which is very convenient.

Analysis and Recombination Data

In Excel, use the "Split Columns" function or the Text function to split the contents of a string into multiple cells. Starting with Excel 2013, the smarter "Quick Fill" feature appeared.

The following table is an example.

If you want to split the main model and release year from columns B and C into columns D and E, you can do this:

Enter FSA in cell D2, select cell D3, and press <Ctrl+E>;

Enter 2016 in the E3 cell, select the E3 cell, and press the <Ctrl+E> key combination;

The required information is split in an instant.

Assuming that the naming rule of the new model is city code-main model-year, you can enter 025-FSA-2016 in the F2 cell, then select the F3 cell, and press the <Ctrl+E> key combination.

Note that the split operation just now is not necessary to calculate the new model.

Summary

This article mainly introduces how to use Excel to quickly handle some data problems and help programmers improve Excel data capabilities. In the words of programmers, you can use an existing wheel if you can, there is no need to rebuild the wheel. Since the data is in Excel, and Excel already has countless wheels, why don't we use it?

Everyone encounters a problem and prioritizes using the tools they are most familiar with, because this is the least risky and most efficient. But in fact, each tool has its own advantages and applicable scenarios, so we should not have limitations, or even "disdain" plots. Knowing more about some tools can improve our comprehensive ability to deal with various problems.

The above content is based on my personal experience, and it can be regarded as a guide for everyone. I believe that there are many programmers whose Excel level is much better than mine. I hope you will share some secret skills too!

Microsoft Most Valuable Professional (MVP)

The Microsoft Most Valuable Professional is a global award given to third-party technology professionals by Microsoft Corporation. For 28 years, technology community leaders around the world have received this award for sharing their expertise and experience in technology communities both online and offline.

MVPs are a carefully selected team of experts who represent the most skilled and intelligent minds, passionate and helpful experts who are deeply invested in the community. MVP is committed to helping others and maximizing the use of Microsoft technologies by Microsoft technical community users by speaking, forum Q&A, creating websites, writing blogs, sharing videos, open source projects, organizing conferences, etc.

For more details, please visit the official website, Microsoft MVP, look forward to your joining