How to use function data query
There are many functions available for data query in Excel. The following are some common functions and their usage: ** 1. Retrieve (Search Function)** For example, if you want to find the number of bananas, if the data is in the A2:A5 column (assuming it is the fruit name column), the corresponding number is in the B2:B5 column, and the cell where the search value is located is D2, then the function can be set to =LOOkUP(D2,A2:A5,B2:B5). ** 2. Sumif (Condition Summation Function)** Syntactic: =sumif (condition column, sum condition, sum column). For example, if you want to ask for the number of oranges, assume that A2:A5 is the fruit name column, B2:B5 is the number column, and the orange name you want to find is in cell D2. The formula can be set to =SUMIF(A2:A5,D2,B2:B5). ** 3. Dget function (extract unique values from the database)** When selecting the data, the header must also be selected. For example, if you want to find the data of lemons, if the data area is A1:B5, the search criteria for lemons are in the D1:D2 cell area, and the results to be found (such as quantity) are in the second column, the formula can be set as: =DGET(A1:B5,2,D1:D2). ** 4. dsum function (database function, sum under conditions)** Similarly, all the data in the parameters needed to include the header. For example, if you want to find all the data of an apple, assume that the data area is A1:B5, the field name of the search result is in the E1 cell, and the search condition is in the D1:D2 cell area. The formula can be set as: =DSUM(A1:B5,E1,D1:D2). ** 5. Sumproduct (Return the sum of the product of the data)** The grammar was: = sumproduct (first data area, second data area, third data area), and so on. For example, if you want to find the number of apples, assume that A2:A5 is the fruit name column, B2:B5 is the number column, and the cell where the search value is located is D2. The formula can be set as: = SUMPPROCEST ((A2:A5 = D2)*B2:B5). In addition, there was also the Xlook-up function. The input was equal to the Xlook-up function. The first argument was the search value (for example, import the name cell), the second argument was the search array (for example, reference the entire name column), and the third argument was the return array. If you want to find multiple data at once, you can set the parameters according to your needs. There was also the MLOOkUP function that could be used for many-to-many search. For example, to find products in a specific store, you need to set the corresponding parameters according to the content to be found, the search area, the column of the results to be found, and the requirements for the results to appear (such as all or the last time, etc.). In addition, you can also use index+match to search (but it may be difficult for beginners to understand), as well as the Vlook up function and other methods to query data. Different functions are suitable for different query scenarios. You can choose the appropriate function according to your specific needs. "Choose" was equally exciting. Everyone was welcome to read it!
How to use the query function
The following are some common query functions: ##1. Vlook-up Function 1. * * Function and parameters ** - This was a vertical search function. The grammar is = VLOOkUP (look up_value, table_array, cor_index_rum,[range_look up]). - The first argument, look up_value, is the value to be looked up. For example, when searching for a person's score, this value could be the cell where the person's name was located. - The second argument, table_array, was the data area to be searched. The query was to be carried out within this data range. - The third argument, cor_index_nam, was the column of the result in the data area. For example, if the result was in the third column of the data area, it would be set to 3. - The fourth argument, range_look up, was the data matching method. If it was set to false or 0, it meant an exact match. If the result could not be found, the function would return #N/A. If it was set to true or 1, it meant an approximate match. If the exact result could not be found, the function would return the maximum value that was less than the search value. It was commonly used in interval judgment and commission calculation. 2. * * Points to note ** - The search value must be in the first column of the data area, or an error value may be returned. - When a duplicate value is encountered, only one result can be returned. This was because VlookUp was a top-down data query. - Unable to find data on the left. If the column you want to find is on the left side of the column where the value is found, you cannot use the Vlook up function directly. 3. * * Case demonstration ** - For example, if the data area was A1: D9, Zhang Fei's name was in cell F3, and his math score was in the third column of the data area, the formula would be = VLOOkUP (F3, A1: D9, 3, 0). ##2. Combination of Index and Match functions (for searching data to the left) 1. * * Index Function ** - For example, to find the corresponding name according to the student number (search to the left), you can use the Index function and Match function. The index function's grammar is as follows: = Index (B: B, Match (G5, C: C, 0)). Where B: B is the column to return the result (here is the name column), the Match function is used to find the position of the specified value in another column, G5 is the value to be found (such as the student number), C: C is the column to be found (such as the column where the student number is located), and the last 0 represents an exact match. ##3. The Look-up Function 1. * * Multi-criteria query example ** - For example, to find the sales volume of product B on the Jingdong platform, the function = LOOkUP (1,0/(B: B = F6)*(C: C = G6), D: D). Here, the construction condition (B: B = F6)*(C: C = G6) is used to determine the rows that satisfy multiple conditions, and then the corresponding values are returned from the D: D columns. 2. * * Find the last number that matches the criteria ** - Find out the sales of Wang Wu on the last day, function = LOOkUP (1,0/(B: B = J4), F: F). The principle was to construct a condition judgment and find the last data that met the conditions. ##4. Choose Function 1. * * Function and grammar structure ** - The function was to select the corresponding content from the list according to the serial number. Syntactic structure = Choose (Sequence number, return value 1,(return value 2)...). - Note: If the value of the "serial number" is a decimal number, it will be rounded off before use. If the "serial number" is less than 1 or greater than the serial number of the last value in the list, the Choose function will return an error value of "#DOWN!". When the value of the "sequence number" is 1, the "value 1" will be returned. When the value of the "sequence number" is 2, the "value 2" will be returned, and so on. ##5. HlookupFunction 1. * * Function and Usage ** - This was a horizontal search function, similar to the Vlook up function, but the search direction was horizontal. When searching, one also needed to specify the search value, the search data area, the number of columns in the data area (here, the number of horizontal columns), and the matching method (exact match or approximate match). "Choose" was equally exciting. Everyone was welcome to read it!
Baijia account query ranking data query
If the anime wasn't enough, then hurry up and watch the novel version of " Sword Comes "! The original novel was equally wonderful!
Historical weather data query network
The global weather network (<anno data-annotation-id ="00000000 - 4fd2 - 4f10-a110-a160-a11111111000"></anno></anno>) could provide historical weather forecast inquiries and historical temperature inquiries for major cities across the country. Its historical weather data came from the weather forecast information of the city on that day. In addition, there were some websites that could check the weather history of more than 3000 cities, counties, and regions belonging to 34 provinces and cities in the country. The main indicators that could be inquired included the daily highest temperature, the lowest temperature, the weather conditions, the wind direction, etc., but no specific website was mentioned. There was also a mobile application called Historical Weather Inquiry, which could inquire about the historical temperature of a certain area. You could enter the region's Pinyin and select the starting year and month to inquire and record the historical temperature data of the area.
Real-time query of Dragon and Tiger Chart data
The Dragon and Tiger Chart data could be queried in real-time in many ways. First of all, many trading software and financial websites provided the Dragon and Tiger Chart query function, allowing investors to view real-time data directly on these platforms. Secondly, some specialized data query websites such as the stock search network and the Eastern Wealth Network also provided the Dragon and Tiger List data query service. In addition, financial information platforms such as the Star of the Security and the Flushing Financial Network also provided a full range of data query functions. Through these platforms, investors could understand the reasons for the abnormal movements of individual stocks in real time, track the main trends, and reveal the trading situation of the most active individual stocks, sales departments, and agency seats.
Mystical library database system, data query problem.
Mystical library database system data query problem. This is a typical database query problem in the Mystical library system. You can use the Mystical SELECT statement to query the data. Suppose there is a table called Books, which contains the title, author, publishing house, publication date, and so on. Some of these fields can be queried using the following SELECT statement: ``` Title, author, publishing house, date of publication, ismn number From Books Where publication date = (SELECT deadline FROM publication date table) ``` In this example, we used the publication date table to find more recent dates than the book table. The publication date table can be a foreign key that is stored by linking to the book table in other tables. In addition to the STAR statement, you can also use the Join statement and other mysticism syntaxes to further expand the query.
Hong Kong box office real-time data query
To check the real-time data of the Hong Kong box office, you can use the professional version of the Cat's Eye Movie website. Cat's Eye Pro provided accurate real-time box office, screening, and attendance inquiries, providing timely and professional data analysis services for film practitioners. In addition, you can also check the official website of the Hong Kong Film Archives for relevant information.
Data Search Function Formula
The following are some common data search function formulas: ** 1. VLOOkUP Function ** 1. ** Function * - Finds the specified value in the first column of a table or array of values, and returns the value in the current column of the table or array. 2. ** grammar structure ** - =VLOOkUP(Search value, data table (search area), column number,(matching condition)) - For example, if the search area is B2:E10, the search name is in cell G3, and the assessment score is in column 2, the formula is =VLOOkUP(G3,B2:E10,2,False). - Note: - It could only be searched from left to right, but not in reverse. - The search value must be in the first column of the data table (search area), otherwise an error will be reported. - Special Usage: - When searching for multiple results, you can use VLOOkUP or array usage multiple times. For example, to find the employee's department, birthplace, and salary data, if the name was in the G2 cell, the search area would be A:E, the department would be in the fourth column, the birthplace would be in the fifth column, and the salary would be in the third column. The formula could be used multiple times: Department: = VLOOkUP (G2,A:E,4,0); birthplace: = VLOOkUP (G2,A:E,5,0); salary: =VLOOkUP(G2,A:E,3,0) Using the array usage formula: =VLOOkUP(G2,A:E,{4,5,3},0)(The new version has the array overflow function to directly obtain the result. Without this function, you need to first select the corresponding cell, press the three keys of the array, and press the command button.) - Search for the last record: When the data is a number, the formula is =VLOOkUP(9^9,A:A,1)(the first argument is 9 to the power of 9, you can also write other numbers larger than the number in column A, the fourth argument default fuzzy search); when the data is text, the formula is =VLOOkUP("seat",A:A,1); when the data is mixed, if the requirement is to return whatever the last data is, you need to use the LOOkUP function. - It is used for sum operations, such as the formula =SUM(VLOOkUP(B11,$A$2:$G$8,{2,3,4,5,6,7},0)). By modifying the third argument, query the column of each month, find the corresponding month data, and then sum. ** 2. LOOK UP Function ** 1. ** grammar ** - = look up (find the value in that column and return the result column). For example, to find the number of bananas, if the search value is in the D2 cell, the search area is A2:A5, and the result is B2:B5, the formula is =LOOkUP(D2,A2:A5,B2:B5). - When the data is mixed and the last data is to be found, the formula is =LOOkUP(1,0/(A:A<>""),A:A). ** 3. SUMIF Function ** 1. ** grammar ** - =sumif(condition column, sum condition, sum column). For example, if you want to ask for the number of oranges, if the condition column is A2:A5, the sum condition is in cell D2, and the sum column is B2:B5, then the formula is =SUMIF(A2:A5,D2,B2:B5). ** 4. DGET Function ** 1. ** grammar ** - =dget(data area, column number of the result to be found, search criteria). Note that when selecting data, you must also select the header. For example, if you want to find the data of lemons, if the data area is A1:B5, the result is in the second column, and the search condition is D1:D2, then the formula is =DGET(A1:B5,2,D1:D2). ** 5. DSUM Function ** 1. ** grammar ** - =dsum(data area, the name of the field that needs to find the result, and the search criteria). This function is a database function. All the data in the parameters need to include the header. For example, if you want to find all the data of apples, if the data range is A1:B5, the field name is in the E1 cell, and the search criteria is D1:D2, the formula is =DSUM(A1:B5,E1,D1:D2). ** 6. Summer Function ** 1. ** grammar ** - = sumproduct(first data area, second data area, third data area) and so on. For example, to find the number of apples, if the data area A2:A5 was judged to be equal to D2 (apple type), then multiplied by B2:B5 and then summed, the formula would be = SUMPRODOCT ((A2:A5 = D2)*B2:B5). ** 7. Index+Match Function Formula Combination ** 1. [Description] - It could be said to be an omnipotent screening and search combination. 2. ** grammar structure ** - =Index(Array result column,Match(search value, search area, 0)). For example, according to the employee's "name" to search for the corresponding "assessment results", if the search area is B2:B10, the search value is in the G3 cell, and the result column is C2:C10, first obtain the row number where the query value is through Match(G3,B2:B10,0), and then use the Index function to find the value of the corresponding row in the result column. The formula is =Index(C2:C10,Match(G3,B2:B10,0)). ** 8. XLOOkUP Function ** 1. ** Function * - Searching for a match in a range or array and returning the corresponding item through a second range or array. By default, an exact match is used. 2. ** grammar structure ** - =XLOOkUP(Find Value, Find Array, Return Array, No Value Found, Match Mode, Search Mode). Generally, you only need to set the first three functions. For example, if you want to check Zhao Fei's basic salary, if the search value is in cell G3, the search area is A2:A8, and the return area is D2:D8, then the formula is =XLOOkUP(G3,A2:A8,D2:D8). ** 9. TOCOL function (used to find data samples)** - When searching for data based on multiple conditions, such as searching for salaries based on departments and names, it could be used to compare multiple columns with the given conditions. If they were equal, the value of the corresponding column would be returned. The second argument could ignore the error value to obtain the correct result. It could also be used to list the list of personnel in each department. "Choose" was equally exciting. Everyone was welcome to read it!
Use data to listen to novels and consume data? iPhone
Listening to novels with data consumed data. The amount of data used to listen to novels on an iPhone depended on the data package provided by the website. Different websites and packages might vary. Generally speaking, a 40-minute reading session could consume about 100 - 300M of data, and the exact value would be affected by factors such as network speed. In addition, a 20-minute episode of an audio novel would cost about 5M, or 10 to 20M. The specific length and sound quality depended on the audio. The longer the time, the better the sound quality, and the larger the file size. If one listened to books often, it was normal to use a few GB or more per month. <a href="/?from=ask_words" style="color:red" target="_blank">Read more exciting novels for free</a>
Fuji camera data protection function
The match function matches duplicate data.
You can use the Match function in combination with the IF function to identify the duplicate data. For example, in Excel 2013, if there is data in the table, enter the function =IF(Match(C3,C$3:C$18,0)= Rows (A1),"","Repeat") in the cell, and then fill it down to determine whether the data is repeated. The function's parameters Match(C3,C$3:C$18,0)= Rows (A1) are used to determine whether the data is repeated. You can also use Formula =IF(Match(A3,$A$3:$A$17,0)= Rows ()-2,"","Repeat"), enter the Match function formula in the blank cell to the right of the cell data, select the cell containing the formula, and drag the data down. The cell filled with "Repeat" is the repeated cell. "Choose" was equally exciting. Everyone was welcome to read it!