Shulou(Shulou.com)06/02 Report--

This article is about how to execute the SQL query function in Excel, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Data material

The data material is to get the data set about COVID-19 from GitHub. If you need material, please reply "ETSQL" to get the exercise material.

A function ETSQL is used here, which is the built-in function of EFunction.

Data materials include global and domestic data

Case 1: data matching

The "country" Sheet table corresponds to the daily cumulative data and new data of each province. If you need to extract the daily data and information of Shanghai. Then the SQL statement is:

Select * from [country $] as a where a. Province = 'Shanghai'

For those who have learned SQL statements, the query syntax of Excel built-in engine, SQL statements and other relational databases is basically the same. For friends who are not exposed to the SQL statement, it can be understood so simply.

The select keyword is required, indicating that in order to query an information keyword, each statement must have it.

The from keyword is also required, indicating where to query, and the Excel table indicates that it is queried from the Sheet of "country".

The where keyword is also required, indicating that it is the filter condition of the query, which is followed by the filter condition. In the case, the province is equal to "Shanghai" in the Sheet table of "country". If there are multiple conditions, use the keyword and or or to join.

Through the above sentences, we can extract all the daily data from Shanghai.

Extract all the information of Shanghai from the "country" table

If you want to extract all data from Shanghai after 2020-40-20, the above SQL statement only needs to be changed to

Select * from [country $] as a where a. Province = 'Shanghai' and a. Date > 43941.

For friends who are familiar with MySQL or MsSQL, there is no "time" type data in Excel, only numeric type data in Excel, time is represented by numbers in Excel, and 43941 corresponds to 2020-40-20. Of course, in the process of practical application, you can use formulas to concatenate SQL statements, and Excel will automatically convert dates into numeric data processing.

In the case, the data in B1 and F1 cells are spliced into A1 cells, and Excel automatically generates SQL complete statements.

SQL splicing

If you learn the SQL query statement, and then cooperate with the ETSQL function, all the matching functions in Excel can say goodbye. Of course, SQL statements are not as convenient as special functions. But SQL is very suitable for complex data matching, and functions such as VLOOKUP,match do not have this skill. So there are no best tools, only suitable tools.

Case 2: data aggregation (summation, averaging, counting)

If you want to count the cumulative number of new people in Shanghai every day and the cumulative number of diagnosed people, at this time, you can apply another powerful function of SQL, data aggregation, which is very similar to the PivotTable function of Excel, which is equivalent to the functionalization of the PivotTable. Once we have written the Excel statement, we only need F9 to refresh the data and OK. The basic statement is

Select sum (added that day) from [country $] as a where a. Province = 'Shanghai'

The above statement calculates the sum of the field data "added on the same day" through the sum function of the SQL engine (you need to note the difference between the sum function and the Excel function SUM), provided that the province is Shanghai.

If I want to count the average daily increase in Shanghai, how should I write:

Select avg (added that day) from [country $] as a where a. Province = 'Shanghai'

Just replace sum with avg to find the average function and OK. If you want to count how many days there are new confirmed records in Shanghai, the conditional SQL statement is:

Select count (added that day) from [country $] as a where a. Province = 'Shanghai' and a. Added on the same day > 0

The count function is a count and statistics function, and another condition is added, that is, the number of new people on the day is greater than the "0" value.

The aggregation introduced above is through where conditions. If I want to count the total number of diagnosed people in all provinces in the country, I cannot write more than 30 SQL statements in a row.

The normal SQL statement is: select a. Province, sum (a. Added that day) from [country $] as a group by a. Province

This uses the group by keyword. This feature is not very similar to Excel's PivotTable. Group by is equivalent to the "row" condition of the PivotTable, select is equivalent to the "column" condition of the PivotTable, and sum or count or avg is the "value" of the PivotTable.

Statistics on the cumulative number of diagnosed persons

The results of the above statistics are disorganized and can be sorted with a little change at this time.

Select a. Province, sum (a. Added that day) from [country $] as a group by a. Province order by sum (a. Added on the same day) desc

Sorted statistical results

The order by keyword is followed by the sorting condition, and the statistical sum (a. (added on the same day) sort, and desc indicates descending order. If you want to ascend, you can omit it or write asc. In order to increase the readability of SQL sentences, it is recommended that you write.

The above is how to execute the SQL query function in Excel. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.