IGCSE ICT February March 2019 | P31 | Data Analysis Spreadsheet



hello everyone we are now going to discuss task three of March 2019 is paper three one this is a pretty straightforward and simple task to do we are going to use spreadsheet that would be Excel in our case okay so first of all let's examine these files so all the CSV files okay so here we go we have all the files all right so in the pre spread child in the T spreadsheet place all these things in the header okay in the right side of t hide it so you're going to go to insert please fine yes it is so insert and you are going to so we'll put on at the right side our name which is hole three because we're a very cool then we have the blue well one two three and zero and then you have an automated file including the file path on the left in the footer so you click here go to the footer since I have to put in the left part I come here and I click on file path so the file path will also close the file name is red so you click anywhere else and you can see the file path and the filename is displayed you want to go back to the normal view go to view and click on normal alright so after part 16 in the pre spreadsheet insert train users before the new one I did the file so it looks like this and save the spreadsheet okay so know where it asked us to save the spreadsheet okay so please always be careful never to override the source files okay because it always FaceTime to just you know we extract them wind because I remember from my experience during my applied I said exam back in 2014 in my a level so I did quite a couple of plunders like this by overwriting the source right so please don't do that okay so editing I just like yours and I press insert so I've added three rows so first row is going to be glorious okay so it's going all the way to notes so here we have notes it's where it said to them okay so this all of these would become one save here so you write oh yes glorious God I'd blow that native British please and shrubs this has to be right line board both of them first of all this is italicized the background display and the text is bigger the form color is white and text stylish you okay I yeah well it's not exactly ideal but you can use any sense so forth in this case I guess and for the native free will also increase the size I think and yes Kelly is perfectly fitting with the question stated save this a spreadsheet with the file name piece followed by your technique number set to number okay so again this is a CSV format okay so called comma separated values so we have to save this as an Excel workbook okay please don't forget this will put our credentials and so on okay so we have we are in the new file now equal to the text page part so when you take a screenshot showing all the cells only cells a1 to m5 of the spreadsheet including the row column sidings make sure the contents are fully visible I'm sure we can do that okay so just need to take a screenshot enter a function in cell d5 to look the type of the P from the source file category dot CSV do not edit the source file okay it's clearly emboldened so you are not supposed to type in the file so the source file is category I open that file yes this is category I have to use I have to look up all these types from the actual file okay so I'm going to go to d5 this is t5 so you have the codes here all of these and you have to look them up in the category file which is here okay so this one so what you are going to do is you are going to write a code here that would look up this code in this array okay and then returns the type accordingly this is pretty simple you are just going to use the trivial vlookup function for that so you are going to write we look up to select the value to press comma then you go to the the other file which it's not selecting yes it's not selecting let me reopen the file because sometimes this text is the problem I don't know why you're facing this problem so let's open the category file this is equal to me look up I look up this value then the yes perfect see now it's working and I'll select these two rows okay only only the ones that we actually need and press combo again now this is the column index number so column index number means that you that in this you put the column number of the column from which the values will be returned so since we are going to attend these values this is column 1 and this is clearly column 2 yes and then I will put Colin number two because this column one is column 2 and so range lookup could put zero zero means that you want to find the exact match of the more approximate match and as you can clearly see we have returned large tree and I will just replicate this and just to confirm so SSS indeed small shop and sta small tree etc and medium show large shop and all of these so this is correct now I will save it like this like the Elko decode decode column hold goals for whether cream strap okay so this is a bit confusing so you have three columns okay here we have Al Cody Cody code which hold the course four categories of the environmental factors so I will go to the other file I'll close this and open yes so it's saying that you have to write functions in these cells that would look up the environmental factors from the pre-course file okay so again this is a simple heel question so you are going to write a function in i 5 j 5 this is AI v j5 n k5 bootie so you are going to look up the code here from this and you're going to return the environmental factor so you if we look at the t code that is w so w corresponds to wet soil so this should return better looking antique water means tolerate so KDE code means dislikes one important thing what we're saying here is that make sure all of these functions do not display any error message okay so they must return an empty value P they must not show any error so to do that it's not difficult at all we'll just use an F okay so if something is true do this otherwise so I'll say if this if f5 is equal to nothing okay so if it is empty then just return empty because we don't want to show an error message otherwise if it has some value then you are going to vlookup it you can do we look up F five comma and then you are going to go to this file just like the values the array you are going to select the column number which is do it not yes again okay and we want exact match on the poopsy oh right yes okay so it didn't turn anything because again all these values are empty this is K so k must return us alkaline soil so if I replicate this I can do two terms me alkaline soil and L for life and it is returning me line and so on and so forth so I'm just going to copy and paste this code here and here as well replicate this replicate this okay so acid a coil at soil all these are working absolutely all right so P for pollution this is correct replicate the functions that after discipline for all the trees we have done that hide the column C F G H okay so we are going to hide C F G H so you press the control key to select all of them right click and you hide them okay so we have hidden all of them save and print the spreadsheet showing the formula make sure only rows want to protein and are visible okay so we have to it's okay we have to make sure that all the mostly 29 are visible so we zoom out a bit like this okay so 39 are visible it is in landscape orientation so page layout we make it landscape and row and column headings are fully displayed the contents of all the cells are fully visible okay so one thing we're missing here is that we have to show the formula so that is pretty simple you go to formulas and you click on show formula so anything that we have written using the Excel functions that would show like this it's showing us all these values so you are only going to print one to 20 language so let's select all these rules shift all the way to this and press ctrl P sorry it's closeness yes it says that you have to print the spreadsheet okay so we are going to print it like this it's not exactly correct because the functions are not looking properly okay again that the the file names and the destinations are too long so but in your case in the actual exam there will be even be having these this long file paths of it so might it work and you have to say print only the selection and this would print like this okay we go to the next part so create a spreadsheet extract from the data to show the subtrees story in the trees or shrubs that are okay so small large medium and everything this is clearly now a filtering question which is the you know the most easy thing in excellent let's all right so small tree – tree meaning free so this is going to be our category and it has to be everything so this is everything so let's go to the first column name ctrl shift and rightly so this selects all the column names at once given there is no column break between the Lukas there is no empty column you go to filter click on filter so we want only the evergreens one will click yes and we the categories that you want are small tea mediumship large shop so we have smart a medium medium shop and very large shop is there a large truck yes there is a larger but why Cannot I see okay so we weren't supposed to do this yes the first large shrub medium sharp and small fee and now we'll do the every community so made a small mistake there this is yes this looks very nice now so the extract must only display the common name likes tolerates so a common name no Latin name so I'll hide this the likes tolerate dislikes and everything so I'll hide this has been I'll hide the category as well this as well okay so let's like them all at once in height the likes tolerates dislikes evergreen and woods okay so all of them are showing nicely read the spreadsheet extract showing the values make sure that the print outputs or a single page header and footer contents of specified cells are fully visible if I go to the print preview and to select active sheet so it prints everything not on a single page okay you have to make this small little bit this should be a four okay so it it fits perfectly now we have Hydra footer and the contents over alright so we already had the header and the footer here so if you forget how to put the header and footer from Excel from the inside from insert option we can always click on page setup you go to header footer and you can create a custom header or footer or edit any of these okay so quickly before they take the printout and you are simply going to print your evidence stock quick well this was it for the paper 3-1 of 2019 please let me know in the comments if you have any questions so far otherwise good luck for your exams thank so much for watching the video

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *