Procedura pwr krijimin e faturws sw gatshme pwrmes funksioneve VLOOKUP dhe IF PRIZREN SOCIAL SCIENCE JOURNAL / Volume 2, Issue 2; May - August 2018 / ISSN: 2616-387X 27 DATABASE CREATION THROUGH IF, VLOOKUP, SUM AND SUMIF FUNCTIONS OF MICROSOFT EXCEL Jusuf Qarkaxhija AAB College- Faculty of Computer Science jusuf.qarkaxhija@aab-edu.net ABSTRACT Using databases is a fundamental matter for every business and non-business related activities. Their creation is more important than their usage, because creating a software through which we could track stock status in a enterprise’s warehouse is a huge burden for a newly founded enterprise. Microsoft Excel, a part of Microsoft Office packet, is a suitable program as it contains the needed tools and fuctions to create a database. We are going to practically show you how to create a database using Excel functions such as if, sum, sumif and vlookup and also using other tools such as condictional formatting and freeze panes. First of, you create a table with the warehouse articles; then you create an invoice for sale articles, and lastly the made sales will substract the article in the warehouse. The result of this worksheet gives us a practical software which calculates what the warehouse has left after the sales. This is important because a beginner enterpreneur doesn’t have to pay lots of money to create a database, but this worksheet will help them create one without being an expert in Microsoft Excel. In conclusion, we can say it is very important to be innovative in the activities we conduct, because the enterprenur spirit will not let their work be affected by someone else, including a software of advanced technology. Key words: software, Microsoft Excel, database, warehouse etc. PRIZREN SOCIAL SCIENCE JOURNAL / Volume 2, Issue 2; May - August 2018 / ISSN: 2616-387X 28 Introduction It is important for the activity of an enterprise to save their all activities and transactions somewhere. The place where it is saved is called a database1. The database, as we all know, transfers organized data from a table to another, from a sheet to another, from a cell to another, or from an object to another, and today we cannot believe any business related activity can funcion without it2. An enterprise will experience lack of knowledge for its articles in its warehouses, there will be delays in its clients’ provision, and as a consequence it will fail due to competition3. Microsoft Excel is a program for table calculation but it can be used to create databases, statistica analysis, various financial, mathematical, or logical analysis, etc. There are much sophisticated programming languages than Excel, which is an application program that can help create various more advanced software to create databases, but it is important to have a practical program that interested people can later on use. Its usage is even simpler because you do not need to know different languages, but Excel is installed in every computer as a part of Microsoft Office’s packet. Creating a database We create a database as shown below: Source: Author 1 Qarkaxhija, J “Online database publication and management”, PAR International Leadership Conference Press, ISBN 978-953-57258-5-5, fq 443-500 2 Rexhepi, A “Microsoft Access”, Printing Press, Prishtinë Kosovë, 2002, fq 10 3 http://www.dbta.com/Editorial/Trends-and-Applications/Good-Performance-Management-Reduces-Costs- Minimizes-Risk-to-Database-57830.aspx PRIZREN SOCIAL SCIENCE JOURNAL / Volume 2, Issue 2; May - August 2018 / ISSN: 2616-387X 29 We save this database as Warehouse in the menu: Formulas/Define Name/Define Name as shown below: Source: Author And we get the following window: Source: Author We also rename the first sheet Sheet1 to Warehouse. Then in the Sheet2 we create an invoice model as shown below: Source: Author We remove the gridlines through the menu : Tools/Options/View/Gridlines. In A18 cell, we enter the following formula to show the date and time: =Now() PRIZREN SOCIAL SCIENCE JOURNAL / Volume 2, Issue 2; May - August 2018 / ISSN: 2616-387X 30 We can see all the formulas for the invoice down below: Source: Author To continue creating the databsem we click under the Invocei, in A20 cell and we apply the following command View/Freeze Panes. Source: Author In this case, the surface splits in half, the upper part that we do not edit, and the lower part where we conduct required activities. Then in the M19, N19, O19 cells we add : Nr (number of articles), Quantity (the sold quantity), Invoice (number of invoice). PRIZREN SOCIAL SCIENCE JOURNAL / Volume 2, Issue 2; May - August 2018 / ISSN: 2616-387X 31 Source: Author Then we go to the following cells- B731:B740, C731:C740, where we add the information as shown below: Source: Author The cells C731:C740 get their data from the picture above through the following formulas: C731 = SUMIF($M$20:$M$2000,1,$N$20:$N$2000) C732 = SUMIF($M$20:$M$2000,2,$N$20:$N$2000) C733 = SUMIF($M$20:$M$2000,3,$N$20:$N$2000) C734 = SUMIF($M$20:$M$2000,4,$N$20:$N$2000) C735 = SUMIF($M$20:$M$2000,5,$N$20:$N$2000) C736 = SUMIF($M$20:$M$2000,6,$N$20:$N$2000) C737 = SUMIF($M$20:$M$2000,7,$N$20:$N$2000) C738 = SUMIF($M$20:$M$2000,8,$N$20:$N$2000) C739 = SUMIF($M$20:$M$2000,9,$N$20:$N$2000) C740 = SUMIF($M$20:$M$2000,10,$N$20:$N$2000) PRIZREN SOCIAL SCIENCE JOURNAL / Volume 2, Issue 2; May - August 2018 / ISSN: 2616-387X 32 Sheet2 will likely look like this : Source: Author Now we go back to the first sheet (Warehouse) and next to the database we add two columns and name them: Sold quantity (which gets the relevant values from C731:C740 cells and the prefix Sheet2! before the cells) and Remnant in the warehouse (difference between Quantity and Sold quantity). We can add another column i.e. Critical remnant which we can add an IF function to, so if the quantity of articles is under a specified quantity, there is a comment, such as =IF(I4<20,"Articles should be bought",""). PRIZREN SOCIAL SCIENCE JOURNAL / Volume 2, Issue 2; May - August 2018 / ISSN: 2616-387X 33 Source: Author While we see used formulas in the figure below: Source: Author PRIZREN SOCIAL SCIENCE JOURNAL / Volume 2, Issue 2; May - August 2018 / ISSN: 2616-387X 34 Conclusions From the practice example described in a chronology way through figures above, we saw that table calculating program of Microsoft Excel is multifunctional and also using it helps us creating a data base. We crate this using logical function IF, trigonometric functions sum and sumif, also referential function vlookup. This is a typical example that even with limited access excel thanks to it visualizer can do grate things. Surely that business requirements are greater and always growing. And that is partially due to the-so-called 'Costumer Relationship Management', but this is a step done in the next phase. References Rexhepi, A “Microsoft Access”, Printing Press, Prishtinë 2002 Shmalz M, “Integrating Excel and Access”, O’Reilly Media,Inc, Pennsylvania Usa, 2005 Qarkaxhija, J “Online database publication and management”, PAR International Leadership Conference Press, ISBN 978-953-57258-5-5 http://www.dbta.com/Editorial/Trends-and-Applications/Good-Performance-Management- Reduces-Costs-Minimizes-Risk-to-Database-57830.aspx Reisner, T ”Microsoft Office Excel 2003”, Sams Publishing, Cacak 2004