Het Leerprogramma dat van de Grondbeginselen van het Microsoft Excel - leert hoe te Excel te gebruiken

Gepost op 24 Maart, 2007 in 12:58 am

Zorg ervoor om andere posten van de reeks van de Leerprogramma's van het Excel te lezen!

Het Leerprogramma dat van de Grondbeginselen van het Microsoft Excel - leert hoe te Excel te gebruiken
De Uiteinden & de Trucs van het Excel - hoe te unprotect slechts een gedeelte van spreadsheet van het Excel
De Grondbeginselen van het Excel - hoe te om de Voorwaardelijke tovenaar van de Som te gebruiken
Hoe te om de Bevestiging van Gegevens in Microsoft Excel te gebruiken om dropdown lijsten te creëren
Het Leerprogramma van het Excel - hoe te om een eenvoudige grafiek of een grafiek in Excel te maken
Het Leerprogramma van het Excel - Voorbeeld van hoe te om de functie te gebruiken VLOOKUP

Het leren van de Grondbeginselen van Microsoft Excel

Als u geïnstalleerd Microsoft Office hebt, maar nooit Excel voordien hebben geraakt omdat u of niet het wist hoe te om het te gebruiken of wist wat niet het voor u kon doen, dan MOET u deze post lezen bij het leren hoe te om Excel te gebruiken!

Het Microsoft Excel is een krachtige spreadsheet toepassing die voor om het even wat van een eenvoudig gegevensbestand al manier tot een volwaardig de toepassingshoogtepunt van Vensters met venstersvormen, macro's kan worden gebruikt, en toevoegen -toe:voegen-ons. U kunt Excel gebruiken om een betaling van de autolening, een grafiekgegeven te berekenen, klantenverslagen te beheren, een adresboek, enz. te houden.

Het Excel wordt momenteel gebruikt door de meeste grote financiële instellingen voor dagelijkse financiële gegevensanalyse. Het heeft een reusachtige waaier van financiële functies, formules, en voegt -toe:voegen-ons toe die u toestaat om Excel te gebruiken om gegevens op een eenvoudige, snelle manier op te slaan en te analyseren.

In dit leerprogramma, gaan wij door de grondbeginselen van Excel gaan: creërend werkboeken, die aantekenvellen gebruiken, die gegevens ingaan, die formules, enz. gebruiken zodat u comfortabel met de software kunt worden en beginnen op uw te leren door rond met het te spelen.

Eerste Stappen in Excel

Eerst, open Excel en neem een blik bij de interface van het programma. Het open Excel en een nieuw werkboek zullen automatisch gecreërd worden. Een werkboek is het hoogste niveauvoorwerp in Excel. Het bevat Aantekenvellen, die alle daadwerkelijke gegevens houden u waarmee zult werken. Een werkboek vertrekt met drie aantekenvellen, maar u kunt aantekenvellen op elk ogenblik toevoegen of schrappen zolang er minstens één aantekenvel in een bepaald werkboek is.

Over de bovenkant, hebt u de toolbars van het Excel, gewoonlijk de Standaard en Formatterende toolbars die u toestaan om werkboeken te openen, nieuwe degenen tot stand te brengen, de font stijl, enz. te veranderen. Dit zijn meestal het zelfde als Word, maar met een paar verschillende knopen wij waarover later zullen spreken.

If you do not see the Standard and Formatting toolbars as shown above, you can have them brought back by going to View and then choosing Toolbars and check to make sure that the two toolbars have check marks next to them.

At the bottom of the screen, you’ll see three sheets, named Sheet1, Sheet2, and Sheet3. This is the default number that every Excel workbook starts off with.

On the right hand side of the screen is the Excel task pane, where you can perform many common tasks such as opening a workbook or creating a new one. You can also perform searches for help if needed here.

Getting Started with Excel

The best way to learn anything is to actually do something useful and Excel is the best example of this! Let’s say you are a teacher of high school or college students and you want to keep track of your student’s grades, calculate the average, and tell the students what grade they would have to get on their finals in order to get an A in the class.

Sounds like a simple problem and it is! Excel can do this for you very quickly, so let’s see how.

First off, let’s enter some data into the cells in Excel. In Excel, the columns are labeled starting from A and continuing to Z and beyond. A cell is simply a particular row number and column, i.e. A1 is the very first cell in an Excel worksheet.

Let’s type Students into well A1 and then type A through E as the student names continuing down column A as shown below:

Now let’s enter Test 1, Test 2, and Test 3 into cells B1, C1, and D1 respectively. Now we have a 5×4 grid, so let’s fill out some fake test grades also as shown below:

Now let’s learn some of the basics of formatting cells in Excel. Right now our table doesn’t look very nice since the text and numbers are aligned differently and the headers are not visually separate from the data.

First, let’s center all the data so that things look nicer. Click on cell A1 and drag your mouse down to cell D6 to highlight all entire grid:

Then on the Formatting toolbar, click the Center Justify button. The grid is now nicely centered with all the data directly underneath the headings.

Now let’s look more at how we can format Excel cells. Let’s change the color of the first row to something else so that we can clearly separate the header from the data. Click on cell A1 and drag the mouse while holding the button down to cell D1. Right click and select Format Cells.

In the Format Cells dialog, click on the Patterns tab and select a color from the palette. I chose yellow to give it a bright distinction.

Click OK and you’ll now see that the color has been changed for the selected cells.

Let’s also add some borders between the cells so that if we decide to print out the Excel sheet, there will be black lines between everything. If you don’t add borders, the lines you see in Excel by default do not print out on paper. Select the entire grid and click on Format Cells again. This time go to the Border tab. Click on the Outside and Inside buttons and you should see the small display box directly below the buttons change accordingly with the borders.

Click OK and you should now have black lines between all of the cells. So now we’ve formatted our grid to look much nicer! You can do this type of formatting for your data also in the way you feel appropriate.

How to use Formulas and Functions in Excel

Now let’s get to the fun part: using Excel functions and formulas to actually do something! So we want to first calculate the average grade for our 5 students after their 1st three exams. Excel has an average function that we can use to calculate this value automatically, but we’re going to do it slightly differently in order to demonstrate formulas and functions.

Add a header called Sum in column F and Avg in column G and format them the same way we did the other header cells.

Now first we’ll use Excel’s sum function to calculate the sum of the three grades for each student. Click in cell F2 and type in “=sum(” without the quotes. The = sign tells Excel that we plan on putting some type of formula into this cell. When you type in the first parenthesis, Excel will display a little label showing you what types of variables this function takes.

The word SUM is a built-in function in Excel which calculates the sum of a specified range of cells. At this point after the first parenthesis, you can select the range of cells you want to sum up! No need to type the cells one by one! Go ahead and select cells B2 to D2 and you will see that the formula is automatically updated and is in blue.

After you select the range, type in the closing parenthesis (Shift + 0) and press Enter. And now you have the sum of the numbers! Not too hard right!? However, you might say that it would be a royal pain to do this for a set of 100 or 500 students! Well, there’s an easy way to copy your formula automatically for the other students.

Click on cell F2 and then move your mouse slowly to the lower right edge of the cell. You’ll notice that the cursor changes from a fat white cross to a skinny black cross and the bottom right of the cell is a small black box.

Click on that small black box and hold your mouse down and drag it to the row of the last student. And with that, Excel uses the same formula, but updates the current row cells so that the sum is calculated for each row using that row’s data.

Next, click in cell G2 and type the = signs to denote we are starting a formula. Since we want to divide the sum by 3 to get the average, type the = sign and then choose the sum cell F2. Continue on with the formula by typing in “/3″, which means divide by 3.

Press Enter and you now have entered your own average forumla! You can use parenthesis and perform all the math functions in this same way. Now do the same thing as with the average and click the small black box at the lower right corner in cell G2 and drag it down to the bottom. Excel will calculate the average for the rest of the cells using your formula.

And lastly, let’s put in one more formula to calculate what each student would have to get on the final in order to get an A in the class! I’m assuming that the three tests were worth 70% of the grade, the final is worth 30%, and an A is 90 or above.

So in order to calculate the minium grade to get an A in the class, we have to take 90 and subtract 70% of the average test scores and divide the whole thing by 30%.

Min Grade To Get A: (90 - (.7 * AVG)) / .3

So let’s create a new header in column I and in cell I2, beging typing “=(90 - (.7 *” and then select cell G2 and then continue on with “))/.3″ and then press Enter. You should now see the grade required and also the formula in the formula bar above the column names.

Again, grab the bottom black box of the cell and drag it down to the bottom of the data set. And viola! You’ve now used Excel functions, created your own formulas in Excel and formatted cells to make them visually appealing.

Looks like Student C has to get a 118 in order to get an A in the class! Whoops!

Hope this helped! I’ll try and write more like this for more advanced features of Excel and other Office products. Please leave your comments on this Excel tutorital!

If you enjoyed this post, make sure you subscribe to my RSS feed!

» Filed Under MS Office Tips