9 little-known chip business in Excel. Bashny.Net

Excel - not the most friendly software in the world. A normal user uses only 5% of its capacity and bad is what treasures hide her bowels. Using Excel-advice guru, you can learn how to compare price lists, hide sensitive information from prying eyes and prepare analytical reports in a couple of clicks. (Okay, sometimes those clicks 15.)

1. Supertayny sheet
Let's say you want to hide a part of sheets in Excel from other users working on the book. If you do it the classical way - right-click on the sheet tab and click on the "Hide" (picture 1), the name of the hidden sheet will still be seen another person. To make it completely invisible, so you need to act:

- Press ALT + F11.
- On the left you will have an elongated box.
- At the top of the window, select the sheet number that you want to hide.
- At the bottom of the list at the end get the Visible property and make it xlSheetVeryHidden. Now this piece of no one but you will know.

2. Prohibition of change retroactively
We have before us a table with blank fields "Date" and "Max." Manager Bob today will indicate how many carrots a day he sold. How to make sure that in the future he could not make changes in this table retroactively?

- Place the cursor on the cell with the date and select the menu item "Data».
- Click on "Data Validation". Table appears.
- In the drop-down list "Data Type" choose "Another».
- Under "Formula" write = A2 = TODAY ().
- Ticks with "Ignore empty cells».
- Push the button "OK". Now, if a person wants to enter a different date, a warning message will appear.
- You can also disable change the numbers in the column "Number". Put the cursor on the cell with the number and repeat the sequence of actions.

3. The ban on entry takes
You want to enter the list of products in the price list so that they are not repeated. You can set a ban on a repeat. In the example shown in the formula for the column of cells 10, but they may of course be any number.

- Select cells A1: A10, which will be exempted from the prohibition.
- In the "Data" tab click the "Data Validation».
- In the tab "Options" from the drop down list "Data Type" select the option "Other».
- Under "formula" = COUNTIF trying to drive ($ A $ 1: $ A $ 10; A1) & lt; = 1.
- In the same window, go to the tab "Error message" and there enter the text that will appear when you try to enter a duplicate.
- Click "OK».

4. Selective summation
Table in front of you, which shows that different customers have bought several times you have different products for a certain amount. You want to know on what the total amount of the customer named ANTON bought you crabmeat (Boston Crab Meat).

- In cell G4 you enter the name of the customer ANTON.
- In cell G5 - Product Name Boston Crab Meat.
- Gets a cell G7, where you will have to calculate the sum and write to her formula {= SUM ((C3: C21 = G4) * (B3: B21 = G5) * D3: D21)}. At first it scares her volume, but if you write slowly, its meaning becomes clear.
- First, we introduce the {= SUM and open the brackets in which there will be three factor.
- The first factor (C3: C21 = G4) searches the specified list of clients mention ANTON.
- The second factor (B3: B21 = G5) does the same with the Boston Crab Meat.
- The third factor D3: D21 is responsible for the cost of the column, after we close the brackets.
- Instead of Enter when writing formulas in Excel must be entered Ctrl + Shift + Enter.

5. Summary table
Do you have a table that shows which items to which the customer, for what amount sold by the manager. As she grows, select individual data from it is very difficult. For example, you want to understand in what amount sold carrots or managers who performed most orders. To solve such problems exist in Excel spreadsheets. To create such a table, you need to:

- Under the "Insert" click "Summary Table».
- In the window that appears, click "OK».
- A window will appear in which you can create a new table using only the data of interest.

6. sales receipt
To calculate the total amount of the order, we can proceed as usual: add a column in which you want to multiply the price and quantity, and then calculate the amount in this column. If stop being afraid of formulas you can do it more gracefully.

- Select cell C7.
- Enter = SUM (.
- Select the range B2: B5.
- Enter an asterisk, which to Excel - the multiplication sign.
- Select the range C2: C5 and closing bracket (Picture 2).
- Instead of Enter when writing formulas in Excel must be entered Ctrl + Shift + Enter.

7. Comparison of prices
This example is for advanced users Excel. Let's say you have two price list, and you want to compare their prices. On the 1st and 2nd picture in our price lists of 4 and 11 May 2010. Part of the goods in them is not the same - that's how to find out what kind of products.

- Create the book list and another copy in his lists of goods and from the first, and the second a price.
- To get rid of duplicate items, select the whole list of products, including its name.
- In the menu, select "Data" - "Filter" - "Advanced Filter».
- In the window that appears, note three things: a) copy the result to another place; b) put the result in the range - select the location where you want to save the result, in the example of a cell D4; c) tick the "Unique records only».
- Press "OK" button and starting at cell D4, get a list without duplicates.
- Remove the original list of goods.
- Adding a column to download the price list of values ​​for the 4 and 11 May and column comparison.
- Enter in column comparisons formula = D5-C5, which will calculate the difference.
- You're automatically loaded into the column "May 4th" and "May 11" value from the price. To do this, use the function: = VLOOKUP (lookup_value; table; column_num; _prosmotr interval).
- "Lookup_value" - a line that we will look for a price in the table. The easiest way to search for products by their first name.
- "Table" - an array of data, which we will seek the necessary importance to us. It must refer to a table containing the price of 4-th.
- "Column_num" - is the ordinal number of the column in the range that we were asked to search for data. To search, we define a table with two columns. Price is contained in the second of them.
- Range_lookup. If the table that you are looking for value, sorted in ascending or descending order, it is necessary to set TRUE if not sorted - write FALSE.
- Give the formula down, do not forget to fix ranges. To do this, before the column letter and row number in front of a dollar sign (this can be done by selecting the desired range and pressing F4).
- The final column shows the difference in the prices of those items which are in fact in a different price list. If the column is reflected in the outcome # N / A, which means that said commodity is only one of a price, and consequently it is impossible to calculate the difference.

8. Valuation of investments
In Excel, you can calculate the net present value (NPV), ie the sum of the discounted cash flow value to date. In the example, the value of NPV is calculated on the basis of a period of four periods and investment income (line 3 "Cash flow»).

- The formula in cell B6 calculates the NPV using the financial function: = NPV ($ B $ 4; $ C $ 3: $ E $ 3) + B3.
- In the fifth line calculation of discounted cash flows in each period is using two different formulas.
- In cell C5 result was obtained thanks to the formula = C3 / ((1 + $ B $ 4) ^ C2) (picture 2).
- In cell C6 same result was obtained through the formula {= SUM (B3: E3 / ((1 + $ B $ 4) ^ B2: E2))}.

9. Comparison of investment proposals
In Excel, you can compare which of the two proposals on investment profitable. To do this, write in two columns required volume of investments and the amount of their gradual return, and also separately specify the discount rate of investment as a percentage. With these data we can calculate the net present value (NPV).

- In the empty cell, enter the formula = npv (b3 / 12, A8: A12) + A7, where b3 - discount rate, 12 - the number of months in a year, A8: A12 - a column with numbers phased return on investment, A7 - the required amount of investments .
- In the exact same formula is calculated net present value of the other investment-project.
- Now they can be compared: one else NPV, the project is profitable.