MySQL data export to Excel and related calculations

After MySQL queries out the data, you can choose to export the file by default is the CSV file if the integer type of data can be concat (' \ ', filed) more ' can be changed to text, and then open the CSV file with a text editor to replace the ' equal character with an empty string.

Create a new Excel file, find the data--"From the text, select the next format for text OK

Some basic Excel functions:

Count the number of characters or numbers: =countif (a:a, "test") is the number of occurrences of the string ' Test ' in column A

Gender: =if (MOD (A1,if (LEN (A1) =15,15,17), 1), 2) = 1, "Male", "female", based on ID number

It then formats the entire calculation of a result, such as the Gender column, the first calculated result of this column, press CTRL + the + number that appears in the column, and then double-click to calculate all the data for this column.

Age: =datedif (TEXT (MID (A1,7,len (A1)/2-1), "0-00-00"), TODAY (), "Y" according to the ID number

Number of statistical age groups: C column for Age statistics =if (c1>50, "50 +", if (c1>=40, "40-50", if (c1>=28, "28-40", if (c1>=23, "23-28", if (c1> =18, "18-22", "18 or less"))))

OK above is some basic computational functions, of course, Excel has a lot of functions, need to use the time to check the data well.