Marina Smirnova

Saved 4/10/19 7:08 PM from computer literacy

Тур по Excel: основы работы  See details »

 
Video subtitles:
  • 00:00: friends good afternoon my name is artem and today we will make review of the program axel many consider that excel program quite complicated and difficult for her to use but in fact it is not this is a very simple program and today we be able to understand it, we will touch on all 12 topics are copying and pasting use keyboard shortcuts consider the different formatting in
  • 00:30: Excel numbers dates percent we'll talk about creating form and relative links how absolute links are created that is such a let's talk about the possibility of data analysis sorting and filtering parts we 'll also talk about one of the most Excel features used and pivot tables besides finally
  • 01:00: we will touch on the creation of tables and graphs so let's start In Excel there are several ways copy data the easiest way it is to allocate we are given right-click the table we need mouse and click copy we can also copy with using the menu buttons by clicking on the button copy or contra key combination
  • 01:32: option on the keyboard then we can insert selected data in a free area bills by pressing the insert button right-click to insert or the contra vaio key combination or by simply pressing the enter button
  • 02:02: very often comes to us there is a need so that we can to copy our table retains its original width columns for example if i leave the table as usual is the width of the columns it remains as it was originally that is, here it is worse than in the original
  • 02:32: table but we can do so tables left immediately with the one we need column widths for this we insert table again and here's the bottom left we have a small button in the corner we can click on it and select a function save the width of the original columns to this case
  • 03:05: columns will become exactly the same width as and source tables I will now delete the table the second way that we could insert this data this click in first cell and move a little god press the insert button and here choose the right one for us insert option we can set as simple data
  • 03:35: formulas and many other formats and pay attention when we choose each of the proposed options we can immediately see how it will be look like further apart from the traditional insertion goal also have the ability to transpose tables that is, so that we mean by horizontal table columns vertical value become lines for
  • 04:08: this click on cell a 9 right click special rate transpose ok and here we have transposed tables suppose we have a sale for january and we want to see them reach the end this list you can do it in several ways
  • 04:38: First, we can scroll with the mouse and down this is a long enough variant twist long, long, long down to reach the end of this table secondly on the right we have a strip can hold it and scroll down too much faster also we can take advantage keyboard pushing the button down our highlighted the cell will move amnesty with her
  • 05:08: the selected area will also be moved but if our tables are big enough will this move be uncomfortable so pixel there are hot keys that allow us to jump on table 1 such a key is a key control holding it clicking down i can quickly move down table similarly holding the control pressing up I move
  • 05:39: to the top of the table if I need to select all data tables do not need to hold control shift button clamp and press down now the assignment is not necessary make my tables have borders This can be done in two ways either select borders you have either click right click here i have
  • 06:11: a small formatting menu appears and select all stress boundaries Here I have the whole table appeared boundaries I can now do another task which is suggested on this page and make the table header text bold for this no you can either press the bold button in the menu or press a combination of contra buttons killed
  • 06:45: I have to calculate the next task the sum of all sales of this table for of this i will use the auto sum function she is here and pushups and by I get the wrong default range of kassel trying to guess which exactly cells i want to sum but he doesn't always succeed right for this, I click on the very first
  • 07:20: cell click kontrol shift click down I have select cells I press the button enter here I counted the sum of all cells let's talk a little about excel formatting periodically when we bring in some data especially if we need to enter cost data ruble costs us
  • 07:51: have to enter something like a half thousand rubles or 1000 rubles at the same time regardless that we leave axel anyway stamps values ​​as prime numbers that is, whether or not we walk we are 1000 or 1000 rubles with excel point
  • 08:22: still stores this value as a number The matter is that in the exel several different formats for numbers like we would not change their format we change them we can for example here not formatting can put money format or the total number itself does not change and formatting in this case stands
  • 08:52: like a mask that changes the display number but the number itself does not change for example, I could just keep the number of measures 500 and make it have a currency format in this case it is automatically place decimals and rubles with a point that saves us time to enter this data talk a little bit about how in Excel
  • 09:22: Displays the date the matter is that to date this program is also stored as numbers for example, here I have the numbers then 240 320 grade and 644 if i convert them to a date then excel will display them as January 1st dates 1900 January 2, 1900 and so on but it still remains the numbers
  • 09:56: and since these are numbers with them you can do arithmetic addition and subtraction and so on for example, we can calculate the difference between two numbers and find out how far they are apart suppose we have the date of payment of the bill Senate 3 11 years today we have 11 4
  • 10:30: 11 years to find out the amount of delay just click on the symbol equal click on the first cell it appears address which is determined by the column here we are column d and row here we the ninth line therefore are displayed t9
  • 11:01: and subtract the invoice payment date and we find out that the brushes are overdue for 24 days shape convenience as opposed to simple computing is that we can in any time change date example 18 4 11 and then immediately change recalculated
  • 11:31: the number of days that we have delayed our payment is now the next example besides subtraction we can also sum up the numbers for example we have some know we put 10 thousand dollars we issued it August 27, 13 and we want her to returned after 31 days to find out the date of return of the loan we just need to add the date of issue and
  • 12:03: number of days and excel automatically calculates this. the date whereby he sees that we add that the number therefore the result is also date and first and second number we can change and therefore the date will also be recalculated in a similar way we can add not only cells but
  • 12:33: add with it the value we need for example, we have a project start date 10 8 project duration 5 days project end date is equal to start date plus 5 days but excel is not takes into account the need to include the date itself
  • 13:06: project start so we can manually add one day typing on the keyboard + 1 In addition to simple calculations in Excel also There are functions that allow count the number of working days as well as the end date of the project given weekends and holidays for example, we have a project start date
  • 13:38: ten 8 beeps 13 project duration 20 working days by the way if you ever appear cell of which they fall entirely on screen
  • 14:10: as is the case with this long inscription you can increase the column for this need move the cursor between them the cursor will look like this forked arrows hold it and stretch right a little more Now we can read the end date project and this we again set equal and use the slave day function between choose the start date number of days
  • 14:43: Further goal invites us to choose which it is not considered a weekend in our country as you can see the option can be lots of for example if you have a six-day working week then you can only choose one day off I will choose saturday resurrection Shchelkova twice . , and the sight of the dates of holidays covering
  • 15:22: bracket I appeared the project end date but since my holidays and falls into this gap now they are not counted let's do it change date and video 15 8 13 Here we have the first time the date has changed and 1 9 13 this time the date has not changed
  • 15:55: probably because the holiday falls on a day off now let's talk about formatting percent traditionally interest in excel and kills in two ways or first beats off 002 and puts percent formatting either kill two and then put formatting percentage while you
  • 16:25: can make a mistake because if you kill 002 excel will count correctly but if you just kill 2 and click the percentage extent will count 2 as an integer and will show you 200 percent example i will highlight these two cells and click on formatting the form of verse percent wrong 2 percent here 200
  • 16:58: percent besides introducing difficult signs through comma you can drive them much easier by clicking on the number you need for example 5 and pressing the percent sign if we convert it to plain text then this will be 005 as we need to go ahead and Now let's talk about formulas
  • 17:29: cell in this assignment we need to count the net profit of some enterprise we have revenue there is an expense you must first sum up all expenses then subtract them from revenue and get the final net profit for this we take advantage several opportunities ksel first
  • 18:01: this is auto amount we have used in the past and this function allows us to sum up multiple cell value she is trying to specify a range summation but it is not always his correctly guesses because we want only sum up the expenses to give these 3 cells I pinch the left mouse button and select then press enter if not necessary
  • 18:32: stay in the same gull instead center i can click control center when the selections are in the same cell now let's look at our formula pressing the f2 form consists of the sum function and cell references sb5 by b7 actually excel link not on the cells themselves he writes in link to 3 cells above them ie he
  • 19:07: summarizes the cells 1 2 3 which is above the current position of this formula I press escape and draw your attention what if you move the cursor the lower left corner will appear small cross this cross is called the fill marker and it allows us copy cells to excel as well as functions and formulas in any
  • 19:37: direction for example down or to the right if I hold the button and hold right to the end of the formulas is copied and now if i go into any form i see what cells that are cumulative in this formula moved the same way they stayed regarding our amount on the same
  • 20:07: place that is the first three cells above basic formula now in a similar way i can calculate the net profit for this i I go to the first column my equal revenue minus expenses and press control in the same way I stretch
  • 20:40: links to the end of the table and I see that they shifted also relative to the source cell now note that I already have no place for those list of where I could move on to move on the next sheet I can or click on these arrows either holding the counter button move on sheets by pressing the keys .
  • 21:13: page down often a little back go ahead now suppose no need to solve small task me a list of pieces Aluminum I know their volume and I need can calculate their masses volume on density
  • 21:43: constructions form and multiplying by density and I stretch this formula down with me an error occurs because when i I pull this formula apart links both left and right to this did not happen no cell needed which is specified density fix
  • 22:13: that is, when driving down it I didn't fly away for that again driving formula and when you click on a cell containing density i can fix it pressing the f 4 button at the same time signs will appear inside the cell dollars which means that this
  • 22:45: the cell is fixed and when pulling formulas will not move anywhere try again yes this time we all thought right and cells move this cell stayed in the same place by the way there is a very tricky trick that allows
  • 23:16: you quickly fill out such tables if you need to remember formulas to the end of this label you just double click on the bottom left corner when the cursor becomes their cross the left mouse button and the formula immediately fill out another example with us have driven wages from these wages on
  • 23:49: pay tax on personal income and payments to the union we want first calculate the rate that we end up with have to pay and read the deduction amount to calculate we will use the bets already familiar to us auto function, click on amount cell select the correct range press enter
  • 24:19: where accent originally shows we have a slightly wrong meaning he shows us a rounded value since the default x and it shows only two decimal places the number of these signs can be increased and reduce and excel by appropriate the way will round off the display numbers on a sheet for this we have two buttons pressing the first decreases the amount
  • 24:53: decimal places let's increase it by 2 now we displayed the correct rate and we can use it of this we click on the knife deduction cell wage rate and fix the second cell for fixation 2 cell I repeat again press f4
  • 25:24: while the blinking cursor is vertical the stick should touch the cell reference co2 he may be early to mid in In the end, it is not important that he touched mt4 press enter after which two clicks times and the formula is pulled to the end and note the default in this cell was a type of currency
  • 25:54: dollar so the number is displayed in the form of a dollar to change them promotions for rubles we only need to select this column select and format the Russian is right here now usually shown now let's talk about the purpose analysis functions and the first one is an opportunity
  • 26:24: Excel data sorting allows us sort are given in tables for proper sorting is necessary observe several conditions first the table must be separated from others data at least one cell that is between the table should be empty space but here is an empty column and there is no data below either to sort the table you can
  • 26:58: use several ways to beginning we click on the tablet itself after what the main menu can click on the button sort and filter sorting can sort by so overgrown descending addition bookmark data also have the ability sort two buttons which we can use and finally
  • 27:29: we can click on the plate right click the mouse so it’s best seen here as well there is the possibility of sorting which we can use besides table sparte with one record and we can also sort tables with two or more columns and all the data in these records will remain
  • 27:59: same and when moving together assorted runami cells for example now our cell Moscow is in second position if we sort it then the anans will move together with the data down sort again she returned to initial position we can sort not only numeric data for example here are the plates we have
  • 28:29: assembly time minutes and and we can just sort squeezing already familiar to us buttons on also we we can sort them by text and by characters for example i can click here click on excel sorting will not show the most effective sport of some parts ie assembly with
  • 28:59: maximum number of stars if sort from minimum to the maximum target port is shown with minimum number of stars also in some cases the cassel may sort the data after some calculations for example now I will make a sign with names and assign each and me some
  • 29:30: random number for this i will use random number function fill and and sort say descending because I have a range they are all tables excel interested I want I extend the range to the whole table
  • 30:01: and toilet only inside the range in this case if i sort only limits range this column is unaffected these on the student will not move on I household expanding the ideal range sorting excel sorted data but while for some reason their order is still is different the fact is that when happens
  • 30:31: sorting from the program takes the original data and sorts the table into according to them after which random number function starts which already creates a new number which we see here in this column how despite the fact that the numbers and the entire table has been ported descending the displayed result is different from
  • 31:02: what we might expect Go to the next function filter target when selecting and filtering data table requirements are exactly the same as and when sorting them there is a table recently touch other tables or other data between their grid must remain at least one empty strip or one empty column to enable filter
  • 31:33: we just need to click on any cell on 3 tables I click on sorting filter filter after that the table headings will appear switchman which means that we are we can filter data for example if we click on the products the program will show us all possible options products that have now you do
  • 32:03: so was the behavior and again so we see all the possible product options we can take off selection and choose some one or two products we can filter either one by one or by several categories their number not limited then need to sort numeric data you can use
  • 32:34: not only the value that is offered to you by default but also numeric filter for example we can ask us what more or less numbers some or for example the first 10 values ​​filtered in this way table we can select and copy in the range we need and pay
  • 33:08: note that excel does not remove those cells which do not fit our criteria he just hides them here with me go cell 12 17 19 20 and so on I copied this range and now I can put it already familiar way and another sheet i use
  • 33:38: further for your analyst now let's talk to you about output most requested features excel about pivot tables in order to create a pivot table we need an array of data in the same way as with the filter and
  • 34:08: sorting which does not concern others tables and in order to use pivot tables need to understand them the basic principle here I have already ready table which shows totals for certain in this case it shows sales in the context of managers and
  • 34:44: the region how these sales were calculated take our source table put a filter example we would like from this table learn sales managers emma region midwest here we select the region midwest manager
  • 35:14: and highlight the sales below we see them amount 3198 dollars Now I will reset the filter and here we see this amount that is, when forming pivot tables
  • 35:45: you place the category you need by rows and columns here you lay those data results of which you want to know they are calculated at their intersection and this way you get what you need report to create a summary the table need to do the following click on any cell inside residue table summary
  • 36:18: table then we can choose whether we want create our report our table on the new sheet or already on some existing let's create not on new leaves and y we have the original table menu girls came to make a table like
  • 36:48: previous we need to move our managers name strings and region column name translate sold default value excel is selected and we will find the result our seller is also the result 3198 thousand rubles how with
  • 37:19: using the source table we can a few mouse clicks would quickly generate ready-made reports for our goals let's talk a little bit about the settings source table when our cursor is ours highlighted cell is inside pivot the table becomes available to us two more
  • 37:49: menu options and constructor if i go beyond the original table these two menus disappear the event will return to them again and in the menu constructor we can choose different particular presentation formats the most convenient for performances is tabular format but excel and for some reason it disabled by default though by others formats are much less common
  • 38:19: also in the constructor field you can choose to display the totals that you like more and choose one of preinstalled report formats here's an example now let's see how the pivot table can help us analysis and numerical it on we put there is a table which is indicated and the grades of pupils exposed to different teachers of us
  • 38:50: also recorded the experience of these teachers and we want to know if the dependency is between their experience and evaluation of their work so that it do we create a new pivot table rate pivot table rent it on that sheet seniority we carry the name of the rows rating
  • 39:22: class column name general then and i will delete so as here they are in Generally not found now in order to here I got a number of entries I can throw in the field value either Raiden class or experience does not have much the difference and now I get the final a report that I can use the only thing worth paying
  • 39:55: attention is that when generating a report excel sorts these categories by alphabetically or by numbers that is here and a It is worth first 1 then 10 first digits then 5 to move categories not enough to bring the edge of the selected mouse cursor areas will not appear yet such are the arrows in different directions and move up holding the mouse button
  • 40:27: similarly, I can move the column right now our report is ready for work latest axel functions which we let's talk is the possibility of creating excel charts and charts can be created as based on pivot tables so not based on ordinary tabular data in order to with the paste diagram of the original table to us
  • 40:58: just click on it parameter service a pivot chart is offered to us many different diagrams we choose the very first histogram with grouping here we immediately appeared this diagram and by it we can already
  • 41:31: analyze data for example right away striking that the theme is the biggest sale region west if we have a horde of farms a chart from a regular table which is not is with water we need to highlight and press the insert chart button and 100 grams and here we again have a few options and again we choose the usual
  • 42:01: histogram we draw the same plate the same the very schedule since all the raw data absolutely similar ups let me talk one more helpful functions histograms if you want to change categories in places that is so that you have
  • 42:32: the names of the regions were below and the scriptures representatives were here vertically you you can click on the constructor button and there is a row column button clicking on it we can change data representation as it is more convenient for us there are regions or ours representative
  • 43:04: then we will get acquainted with this view charts as graphs it's just enough to plot consists of one number and describing its categories or tags I allocate the need for me to set click insert graph chart
  • 43:35: I have a schedule for him title default target is not always optimal creates these graphs if they have some extra items they can be selected and deleted by pressing the button dalit let's create a second schedule for this
  • 44:08: times it marked the city on larger monitors you don't need will go through several menus it is quite possible that you will immediately the chart menu is displayed and you can rather build it and so we press more times and the vector of us is right the same schedule but with the city signed
  • 44:40: we got quite a lot graphics now I cleared space we will build the third graph this time cities and at the same time the schedule was built but a little bit
  • 45:11: not as much as we would like The fact is that excel believes that these numbers 2008 2009 it is also given that they should also displayed in the graph it is not so we do not we want them to be displayed here we want them to be in the description in chart areas for this we need slightly adjust the source data
  • 45:42: on the basis of which this chart is built we can do this by pressing a button designer select data to remove the year we just click on it and click on the delete button cookies here we have gone extra data
  • 46:14: there is only one line left and we also we see that it is exactly the same as reports earlier now to add and not categories we need again these structures select data and here it is menu of labels of the horizontal axis select change and select the desired range this one is a period of time we confirm it
  • 46:47: right on the ok button now our the plot is correct use of the menu data selection allows us to shape graphics from different data sets, that is, we can use to form them
  • 47:20: not one tablet but not at all related tables which may even be in different places or even different files last look chart which we talk today this is a scatter chart scatter plots are used when you need to look at the patterns between multiple numbers are usually between two numbers I have a table to select it all
  • 47:54: i can either select it to the end or there is where shorter way is enough click on any field inside the table press the controller key combination I have a table that stands out to you it also stood out here is this hat which we do not need her close to the table if you move it and press it again now my tablet stands out
  • 48:27: right create new insert charts charts point and here we have a chart there is a certain pattern we her see and let me have a little bit edit First, we can remove unneeded us these elements are the line further we can add trend lines to such charts
  • 48:58: which show what about regularity in our tank this right click on any of the points click and click add trend line here here we have polynomial trend line choose more
  • 49:32: and also we can choose to have showed an equation on a chart also click it and click close down here we have the trend line and also an equation that matches In addition, we can also add vertical and horizontal description scales which is now missing for
  • 50:02: this by highlighting the table we go bookmark layout axes name choose the name of the main horizontal axis it's our corner and vertical axis this is 1 toy
  • 50:35: this way using excel we can fast enough and easy to create graphics and use them our further work let's summarize small result less than an hour we looked main features goal hey to work with some tables shortcut keys that are useful in
  • 51:05: work as how the numbers are formatted how can you work with absolute and relative cells are also different techniques for analyzing and presenting data on I hope all this spur was useful to you at the next meeting