Page 41-excel-php.cn

Article Tags

excel

Home

Technical articles

Topics

excel

How to use large function

Usage: 1. Find the maximum value; 2. Find the second largest value; 3. Find the third largest value; 4. Find the Nth largest value; 5. Find the largest values; 6. Ignore duplicate values, etc. wait.

Nov 17, 2023 am 11:13 AM

large函数

How to use row function to automatically sort

Steps for automatic sorting by row function: 1. Open an EXCEL table; 2. Create a data table in the table and establish a serial number column; 3. Enter the ROW function formula "=ROW()-1" in the serial number column cell; 4. Press Enter to generate a serial number in the cell. Select the cell, move the mouse to the lower right corner and double-click, the formula will be automatically copied, and the entire column of serial numbers will be automatically generated.

Aug 22, 2023 pm 01:40 PM

row函数

How to generate QR code in excel

Method to generate QR code in Excel: 1. Use VBA macro to generate QR code, open the VBA editor, click the "Insert" menu, select "Module", enter the VBA code in the new module, click Run, and execute the VBA macro. Just select the cell range where you want to generate the QR code; 2. To generate the QR code with a third-party plug-in, click the "File" menu, select "Options", in the dialog box, select "Add-ins" to open the COM add-in dialog box , select the QR code plug-in file to be installed, install the plug-in, select the cell where the QR code is to be generated, and generate the QR code.

Aug 18, 2023 am 11:38 AM

Excel二维码生成

Solution to illegal input value in excel

Solution to illegal input value in Excel: 1. Check whether the entered value meets the requirements, and set a warning or error prompt in Excel through conditional formatting or data verification function to remind the user to re-enter the correct value; 2. Use the data verification function to Set constraints such as the range, type, and length of the value, and Excel will automatically give an error message; 3. Use the IF function to make conditional judgments, and provide corresponding processing methods based on the results of the conditions; 4. Use conditional formatting to mark, so that Illegal values are displayed in different styles.

Aug 18, 2023 am 11:21 AM

Excel

Absolute reference input method

Input method of absolute reference: 1. Open the spreadsheet software; 2. Enter a formula in the cell, and use the $ symbol to mark the place where absolute reference is required; 3. In the formula, select the cell that requires absolute reference, and then Add a $ sign before or after it; 4. If you only need to fix a certain row or column instead of the entire cell, you can add a $ sign before or after the row number or column number; 5. After entering the complete formula, press Enter key to calculate the formula and display the result.

Aug 09, 2023 am 11:01 AM

绝对引用

How to fill in serial numbers for merged cells

Methods to fill serial numbers in merged cells are: 1. Use macros to fill serial numbers, open Excel, press "Alt+F11" to open the VBA editor, insert code, run the macro to select the range of merged cells to be filled with serial numbers; 2. Use To fill the serial number with the formula, use the "=IF(COUNTIF(...,"")=1,MAX(...)+1,"")" formula; 3. Use conditional formatting to fill the serial number, use =AND( COUNT(...)>0, COUNTIF(...,"")=1) formula, etc.

Aug 07, 2023 pm 01:31 PM

合并单元格

How to divide excel table into two using slash

How to divide an Excel table into two with a slash: 1. Use the text box tool to create a new worksheet, select the "Insert" tab in the toolbar, then create a text box, and enter the input in the text box that needs to be divided. text, then right-click, select "Format Text Box", adjust the size and position of the text box, and ensure that the slash is at the desired position; 2. Use the conditional formatting function to enter the text that needs to be divided, and you can place the text in In a cell, select the cell or cell range that needs to be divided, etc.

Aug 02, 2023 am 11:34 AM

Excel

Copy the excel table to be the same size as the original

Steps to copy a table in Excel to the same size as the original: 1. Open the Excel table to be copied and select the cell range to be copied; 2. Copy the selected cell range; 3. Right-click the mouse on the location to be pasted. Select "Paste Option"; 4. Paste the table to the new location; 5. Adjust the size of the new table. You can adjust multiple borders at the same time to maintain the width and height ratio of the original table; 6. Check the format of the new table to ensure that the new table The format is the same as the original table, including font, font size, color, border, etc.

Aug 02, 2023 am 10:45 AM

Excel

What should I do if the matching result of the vlookup function is N/A?

The vlookup function matching result is N/A. Solution: 1. Check the data integrity and confirm whether the data range and value to be matched are complete; 2. Check whether the matching value exists, and carefully check the data type, format and format of the matching value and search range. Whether the upper and lower case are completely consistent; 3. Format the data type, you can try to convert the data type of the matching value to be consistent with the search range, or use other functions to convert; 4. Use the IFERROR function to handle errors and use the IFERROR function to replace the error value into other custom values or blank.

Aug 01, 2023 am 09:52 AM

vlookup函数

Use EasyPOI to elegantly export Excel template data (including pictures)

The EasyPOI function is just like the name Easy. The main function is ease, allowing a person who has never been exposed to POI to easily write Excel export, Excel template export, Excel import, and Word template export. Through simple annotations and template language (familiar expression syntax), previously complex writing methods can be completed.

Jul 26, 2023 pm 04:32 PM

EasyPOI

Compare the similarities and differences between two columns of data in excel

How to compare the similarities and differences between two columns of data in Excel: 1. Use conditional formatting, select the first column of data to be compared, and use the "=COUNTIF($B:$B,A1)=0" formula to determine the unit to be formatted grid; 2. Use the VLOOKUP function and use the formula "=IF(ISNA(VLOOKUP(A1,$B:$B,1,0)),"different","same")"; 3. Use the EXACT function and use the formula "=IF(EXACT(A1,B1),"Same","Different")" etc.

Jul 25, 2023 am 10:26 AM

Excel

How to use text function to convert date to year

Steps to use text function date to convert year: 1. Create a new excel document; 2. Select the cell in excel to enter the year; 3. Enter the formula "=text (cell, "yyyy") in the cell and click Enter ;4. The year in the date is extracted successfully.

Jun 21, 2023 am 11:57 AM

Excel

What are the WPS summation formulas?

The wps summation formulas are: 1. Area summation "=SUM(condition area)"; 2. Mixed summation "=SUM(condition 1, condition 2...)"; 3. Intersection area summation "=SUM( Condition 1 Condition 2)"; 4. Single-condition sum "=SUM((Condition 1=Array 1)*(Condition 2))"; 5. Multiple-condition sum "=SUM((Condition 1=Array 1)* (Condition 2 = Array 2)); 6. Nested FILTER function summary sum "=SUM(FILTER (Condition 1, Condition 2 = Array 1))".

Jun 16, 2023 am 10:44 AM

wps

Sharing practical Excel skills: 4 tips for deleting duplicate values!

Hello everyone! When we usually process data, we often find some duplicate data, which will not only reduce our work efficiency, but also affect our subsequent analysis of the data. Today I will share with you 4 ways to delete duplicate values in Excel without using formulas. Come and take a look!

Apr 26, 2023 pm 05:31 PM

Excel