EXCEL SHEET DATA PROCESSING ISSUES

You can use the carry function The following is a commonly used function that retains the number of decimal places, according to your requirements, we choose the function of roundup, I will give an example:

mod(a1*100,1)=0 forces cell input information to be in a numeric format that retains one decimal place.

trunk(a1,1) retains one decimal place directly, without rounding.

round to keep the decimal point.

roundup when keeping decimal Carry up All rounddown When keeping decimal Keep down No rounding Fixed Rounding to keep decimals.

For example, if A1 inputs=Roundup(A1,0)A1 in B1, it means the cell where the data is located;

0 means to keep the number of decimal places (0 is not to keep the decimal place, the original data is an integer) If you need to divide it as well, the function becomes:

roundup(a1/60,0)

Then a1 is 140 (seconds) and the result is 3

You can also choose the function I gave you an example based on your other computational requirements.

Do you still have to calculate the ** fee yourself?

The telecommunications bureau is in good shape.

ceiling((140/60),1)

Just use the "round up" function ceiling on the calculated cell.

Enter or copy and paste the following formula in D2.

if(countif(f:f,c2)=0,"Delete", c2) or. if(countif(f:f,c2),c2,"Delete") pull-down padding. Filter column d.

The result is the deleted rows. Delete.

You first have to confirm that the barcode in column C is within the range of column B, I see that the number of digits in your two columns of cells is not the same, you can first use the "data" - "column" in excel to adjust the number of digits to the same number of digits and then use the vlookuf function to compare.

What do I do with my data in Excel? The following is the method of data processing in Excel** that I shared for you to read and learn.

How to process the data in Excel:

Data processing step 1: First, import the data into an excel sheet.

Data processing step 2: There are several methods, such as from access, **, text, other**, etc., these are from the "data" module, but you can also paste it directly, note, do not paste **, such data excel can not be recognized, others can be automatically identified and can be automatically divided.

Data processing step 3: Then you have to process the data, first you understand what you want to do, for example, I want to plot the original data here, data processing step 4: then linear programming, so you need to graph.

Data processing step 5: Select the data (you can choose any one) -- insert -- chart -- scatter plot.

Data processing step 6: Then it is necessary to carefully select the data and process it.

Data processing step 7: Right-click on the chart - select data - select x and y data.

Data processing step 8: When selecting, you can directly pull the selection cell area, sometimes the selected data is too much, you can't always drag it, data processing step 9: At this time, you will use the shortcut key, first select the top left corner of the ** you need, and then press shift, click on the bottom right corner of the chart.

Data processing step 10: You can select it. This time the continuous area. If it is not continuous, you can press Ctrl and click the selected cell, and you can do it.

Step 11: Select the key to OK.

Data Processing Step 13: This is where the function comes in.

Data processing step 14: find the slope = slope( e1:e5,f1:f5 ) e1:e5 and f1:f5 are two sets of data, respectively.

Data processing step 15: intercept = intercept( e1:e5,f1:f5)e1:e5 and f1:f5 are two sets of data, respectively.

e1：e5，f1：f5

Data processing step 17: At this point, you are almost done, what is missing is to display the linear function on the graph, and then you can make another table.

Data processing step 18: a column of abscisa, exactly the same as the original abscissa, ordinate = slope abscissa + intercept, drop-down auto-fill.

Data Processing Step 19: Then add the data to the chart and it will be displayed.

Data processing step 20: After processing the data, paste it into Word and then print it.

The most taboo thing to grab is that there is no row and column number!

Suppose "A6" is in Zheng Xun C1 single shout Dong Yuange.

Enter in C4.

iferror(hlookup(c$1,$c$2:$p$3,row(1:1),)

Enter and fill right and down.

In the end, Changhe's "total" you know yourself, so I won't repeat it.

Through the excel data, from the ** area, enter the power queryer, delete the column Sun Bureau family 1, select the conversion on the menu, reverse perspective column - reverse perspective other columns, delete the attribute column, click the home page, select close and upload - click close and upload to, pop up the dialog box, La Qi selects the need to upload to a new table or an existing grid table, select as needed, and finally click OK. The specific operation result is as follows:

If it helps, remember to like or follow!

Leave column A alone?

Select the date, personnel columns, data - from the ** area, select whether there is a title according to the actual situation, OK.

The Power Query will open the search rules.

Select the list of people, convert - reverse perspective pant column.

Here are the results. <>

Home - Close & Upload.

Go back to the excel interface.

Clean up the data, column b does not need to be deleted, column a cell format is set to the date of missing potatoes x month x day.

This can be easily handled with Power Qurey. In Power Qurey, just reverse the perspective of all the name columns, and then delete the unwanted columns, you can get the rock order knot to shout the luck of the fruit coarse and pure.

Create auxiliary columns, using array formulas.

Or do it directly with VBA.

When using excel to collapse the report, in the process of calculation, the program often makes mistakes, and "" appears. Prompt.

There are generally two reasons for this.

The data in the cell is out of the width of the cell, and if so, the workaround is simple. Only the scum should drag the column edge until the correct result is displayed. In addition, you can also select Format, Columns, and Optimal Column Width, so that the cells will be automatically increased or decreased to the appropriate width.

2.The data calculation result in the cell produces a negative value, in Excel, the calculation result must be a positive value, so this situation occurs, as long as it is modified to the correct value, the failure will disappear. ;

Cause: Cell formatting issue.

Solution: Set the cell format of the target column, the cell format of the column to be held, and the cell format of the column to be filled.

Excel Tips:

Quickly launch Excel

If you want to use Excel frequently in your daily work, you can start it when you start Windows, and you can set it up

Start "My Computer" into the windows directory and follow the path "start".

menu programs Startup to open the Startup folder.

Open Excel

In the folder, drag the excel icon to the "startup" folder with the mouse, then the excel shortcut will be copied to the "startup" folder, and the next time you start windows, you can quickly start excel.

If Windows has been started, you can use the following methods to quickly launch Excel.

Method 1: Double-click any excel in the "Document" command in the "Start" menu.

Method two: Drag the Excel application from "My Computer" to the desktop with your mouse, and select "Create shortcut in current location" from the shortcut menu to create its shortcut, just double-click its shortcut when you start.

The specific steps are as follows:

For data analysis, to compare the height difference between men and women, first open excel, enter the data nonsense, and then click "File" in the upper left corner

First, click the Office icon in the top left corner of Excel, then find "Excel Options" below and click on it.

Then in the pop-up window, select Add-ons, and in the Manage option below, select Excel Load Portables, and then click on it"Go to"。

In the pop-up "Load Macro" interface, select "Analysis Tool Library" and click OK.

Select "Analysis Tool Library" in the interface and click OK.

Now the "Data Analysis" tab has been successfully added in the upper right corner, then click on the "Data Analysis" tab, and select "No Repeat Two-Way Analysis" in the ANOVA.

Next, select all the data including the group name in the "Input Area", click the blank space in the "Output Area", and finally click OK.

The pop-up box is the result analysis.

In fact, there are only three parts, (which are already represented in the picture).

f ，p-value， f crit

If f is greater than f crit, then congratulations, the difference has been compared, in combination with p-value, if p-value is greater than and less than, it means that the difference is significant; If the p-value is less than that, it means that the difference is very significant.

If f is less than f crit, then the p-value is definitely higher, which means that there is no difference between the two sets of data.