Olga

Saved 5/2/16 8:34 PM to Microsoft Excel

MS Office Excel. Practical work of \"Formula\"  See details »

 
Video subtitles:
  • 00:00: good afternoon me call ekaterina on school We continue remote training is similar to microsoft sex on the site website that teachers on this the lesson we'll look at performance practical work on the subject of the formula and we practice again create different formula on tables with staff and
  • 00:30: learn how to do cell references located on other sheets on the first sheet of ours should be specified salaries 20 employees for all months let's get down to me I will comment I do not I will indicate the name of the staff I just write them numbers all at us should be 20 employees and their will be the base salary
  • 01:01: base class do a carry on he will differ by 2000 rubles it seems such a minimum salary and will is 10,000 rubles every next employee for 2000 more for this will dismiss the first value field second value so that excel can understand difference change salary and copy
  • 01:33: meaning and further we calculate the salary staff for months she too will be different for this first write name of the month January to December and The salary in January will be coincide with the salary with
  • 02:03: base salary so I put is equal to the formula u just pointing cell в2 italy I copy it that is, the aquas for January coincides with base salary but salaries in subsequent months will increase by 10 interest and that this specify we will create the formula that is, enemies the employee will receive January salary and more
  • 02:33: 10 percent that is multiplied by 1 and 1 is 11 thousand rubles this formula is copied to all months and all 20 employees here down appeared immediately see a lot cycles after the decimal point not convenient enough so let's use use the buttons to increase bit capacity will reduce bit capacity and can leave
  • 03:05: like this accepted in any financial affairs 2 on two signs after with a comma but also in this case you can immediately To change the data I'll make a genius find in these two signs after a comma and designation of rubles formatting tables and I'll do it later after you are completely or st-1 we can
  • 03:36: to name salaries and continue already work on the sheet number 2 we will build our work by quarters and for each quarter we will have our own and a sheet Well, that was easy it is more convenient to work select these quarters color suitable for Togo
  • 04:13: that there was a figure normally seen without frills and so on the sheet number 2 we are now create a hat tables for calculating various contributions which will be calculated as percentage of salary let's start the same way we will have here staff and total there will be 20 people here we are found
  • 04:45: I immediately did deliberately this first made numbers employees and now decided to make a hat I see that I do not enough lines here if you miss lines click on the number fears the right button mouse click put up appears a new line here at We will pay off income tax it is 13
  • 05:18: percent more pension fund they are 22 percent carrying words according to the center makes 29 percent of hp these nine per cent accidents and professional
  • 05:49: diseases 0 2 percent and you can calculate the sum which comes from mine in hand and as it is with us and before the quarter, then we here we specify it sheet name and three poets furnish another string let it be value for January and
  • 06:19: I will copy this here these loops here here accordingly us will play to change the text we we can either start it write immediately in this cell if we want make any changes inwards we click the yard by double-clicking we are making change or simply
  • 06:49: We sing out the tea and working in fear for love and in these cells now we will count value in depending on salary for the relevant month so the income tax is 13 percent in January it will be 13 percent of salary in January this salary in January he
  • 07:19: is with us on the sheet that called salaries and so we in our formula should make reference to this sheet let's try it do it for him presses a sign equal to go to this sheet select the necessary cell and go again on sheets number one and you can pay attention in the line
  • 07:50: formula with written just on 2a it is written salaries Further goes! faces this is what it is means immediately see cell reference which is is located on shopping all consists not only from the address this cell but also from sheet names and sheet name separates from Odessa and checks are always you printing on the sign if in the title of the sheet is some characters then in
  • 08:21: In this case it is necessary include name sheet here are a they just put in Latin keyboard layouts where is the letter n Russian put and here we have a link to cell c2 based on leaves and fruits we go again to quarter 1 and see here that we have
  • 08:51: the link was copied wrong let us just we make the following we will move on to we need a cell highlighted her address I can copy it. traditional way to copy and go to the right place and paste right-click insert we must multiplied by 13
  • 09:21: percent respectively genre 013 and I check that I have the correct value accordingly, I also stretch down copy down this formula and since here is this link is c2 is relative reference she is calm turns 4 5 and so further about this more in detail I telling lessons
  • 09:51: absolute and relative reference if you do not know what so please look link now is on your screen farther we must calculate contributions to the pension fund which is 22 percent from the same treasure that we can we can do copy the link from already available cells I select copy and insert
  • 10:22: and I'm just me bet and also I can 1 as it is possible more calculate address links located on another sheet if other ways not so conveniently let's let's try switching on the game we need on the sheet we need salaries transfer to we need a niche Here it is, we press button to copy or
  • 10:52: traditional method copy get up the cell we need formula and click button to insert and here we see that appeared like this icon and if we are on we press it select links to cells, that is, we copy not the value 10 thousand rubles we copy the link to this cell and we can see what we have she transformed herself the formula salary sheet name !!
  • 11:22: and further cell c2 but we see that we have and 2 dollars are indicated then there is no relative absolutely link but since we here it is necessary copy cell to 20 employees experience I delete the dollars that we had relative reference and multiply by the bet I'm taxing the knife on tax rate
  • 11:56: well and further it is possible act already convenient way for to calculate other values identified and copied on I I press the light to go to the next cell and insert and am changing bet and on hands getting an employee is
  • 12:31: the amount of his clan minus income tax his the salary we have is here lies 2 and minus income tax here then that employee gets in his hands how do we you need to fill in all cells for February and March let's do it you can do this re-seeing everything
  • 13:01: The formula can be copy times here these formulas are here and I only change relative reference because the salary for February is located in cell c2 on the sheet salaries that is, right here everywhere we need specify c2 now I have entered address c2 and I see that I was born here
  • 13:32: such an error invalid name it there was an experience she originated because I c did not score English keyboard layout she Russian keyboard keyboard so did keyboard that Russian and in the hole of the square on English keyboard layout the letter s ii se is on the same key and externally they look the same but excel does not recognize Russian letters therefore
  • 14:02: I delete Russian I pass the letter to english layout and enter in English here c 2 here and 2 here and 2 here and 2 here c 2 minus h 3 c correctly and also
  • 14:33: copy it down so and I see that I'm wrong or not so here and 2 here and 2 here and all meat 2 I made a mistake here the salary of Javo there was a cell like this garden d 2 here I am mistaken speech I took a cell c2
  • 15:05: instead of take a cell for 2 so I'm now all correct for this repeat the cheek cell pass a string of formulas and all here I replace c with d I should have to program switching keyboard layouts automatic which I spoke on another course so she told me
  • 15:35: sometimes hinders correctly enter the data is so everywhere fixed 2 to d2 here it is again corrected and the whole and the estate as corrected and I copy formula again to get a new value here they are figured out the area
  • 16:06: figured out let's then let's make March March let's see already it will be carefully cell e2 and here I everywhere the name of the address cells for food again I'm going automatic switching so lenovo from we check that y
  • 17:01: we were 2 more minus m3 all is true and also copy cut seagull we can specify colors cells please performance practical work work apply all your imagination to somehow decorate this table so that I
  • 17:31: looked that you have a good knowledge of the topic cell formatting this is the color of the cells the boundary between cells in the font formatting font alignment so on design on tables special and practical work We will not be and let's on quality training count the total all these became the sum that so
  • 18:05: and everywhere there is a name
  • 18:37: The data format is not general and monetary two characters after with a comma a little tablet is not fit on my small screen can some cells pause or leave as is or zoom out documents, remember that you are drinking grills
  • 19:07: mean that the data just do not fit and treat and so the way we did calculation of the yard itself King and March she It is necessary to do quarter 2 quarter 3 quarter 4 as we to proceed under it's all decoration we can not remodel we click on the right click on the name of the beginner and press move
  • 19:37: copy to create a copy of us push put the relevant check Well, we can move here in front of sheet 3 here appeared a copy our sheet we let's call it quarter 2 and put in here changes Marx will be on and on here we also need edit links to
  • 20:08: cells that is in April link we will already have naled war f and so further so directly and and respectively Denmark further month and you as practical work should not be sent table in which y you will be calculation of salaries for a separate sheet and 400 quarter time on each of which there will be such calculations are made and I will repeat I ask
  • 20:40: fantasize that practice a format and checks and to looked how good you caught this topic farther