الفصل الأول برنامج Excel PDF

‫‪Al-Kut University College‬‬ ‫كلية الكوت الجامعة‬ ‫‪Department of Medical laboratory‬‬ ‫‪Technologies‬‬ ‫قسم تقنيات المختبرات الطبية‬ ‫ﻣﺎدة ﺗﻄﺒﻴﻘﺎت اﻟﺤﺎﺳﻮب‬...

‫‪Al-Kut University College‬‬ ‫كلية الكوت الجامعة‬ ‫‪Department of Medical laboratory‬‬ ‫‪Technologies‬‬ ‫قسم تقنيات المختبرات الطبية‬ ‫ﻣﺎدة ﺗﻄﺒﻴﻘﺎت اﻟﺤﺎﺳﻮب‬ ‫اﻟﻔﺼﻞ اﻻول‬ ‫ﺑﺮﻧﺎﻣﺞ‬ ‫‪Microsoft EXCEL‬‬ ‫اﻋﺪاد‬ ‫م ‪.‬م ﻋﲇ ﻛﺮﻳﻢ ﻋﺒﺪ‬ ‫م ‪.‬م ﺳﺠﺎد ﻫﻼل‬ ‫م‪.‬م ﻋﲇ ﺣﺴﲔ‬ ‫‪1|Page‬‬ Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬ INTRODUCTION TO MICROSOFT EXCEL 2019 PROGRAM Microsoft released Office 2019, which includes Excel 2019 (spreadsheets) This is because there was a need to develop an environment to process the tables in the Word program electronically, which required great effort in filling the table cells after performing the mathematical and logical operations manually before the entry process.With its appearance, the cumbersome processes of performing calculations, entering data, and adding new data (new fields and new restrictions) were eliminated using electronic technology that saves time, effort, and high flexibility in managing tables. This Curriculum is dedicated to Excel 2019, and it describes how the program works and how to deal with it, and explains The many information and accounting capabilities that Excel 2019 has. Excel 2019 is a spreadsheet program that allows storing a huge amount of data in tables And performing mathematical operations And statistical analysis on it and creating charts with high efficiency It is one of the most famous and most advanced spreadsheet programs as it contains many tools that can be used to perform various tasks. This includes many areas such as statistics, financial accounts, data analysis, forecasting calculations, databases, charts, and even text editing and image processing. Where Excel is Used? Microsoft Excel used in almost all industries. Here are the some of the real-time Excel Using industries and domains:  Financial sector is excessively using to perform different kind of calculations.  Analytical professional use Excel almost everyday for applying the basic analytical techniques and tools.  Banking Sector use to understand the Customer 2|Page Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬  Retail Associates uses for analyzing the POS data to check the change and trend in sales  Reporting Analysts uses for generating Daily, Weekly, Monthly, Quarterly and Yearly Reports.  Healthcare Teams uses for performing the longitudinal data analysis.  Market Research Analysts uses for finding the frequencies and cumulative aggregates.  VBA Developers uses for developing the Office Applications, Tools, Office Add-ins and Dashboards. Features of MS Excel: The following features of Excel made the Excel as most widely using application:  Graphical Features of Excel Excel have verity of the graphical features to represent the data in Charts and pictorial format. For Example, Charts, Shapes, Smart Arts, Clip Arts, Pictures.  Data Base Features of Excel Excel can be used as data base and perform of verity of data operations. For example, Pivot Tables, Slicers, Tables, Spark lines, Database, Sorting, Filtering, Data Validation, Grouping, etc.  Functional Features of Excel Excel Tools and Functions will help to perform powerful calculations and enhance the Excel Application Features. For example, Formulas, VBA Macros, Add-ins, Hyperlinks, Conditional Formatting, Spell Check, Protection, etc. 3|Page Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬ MICROSOFT EXCEL 2019 To run the program, we follow the following steps: - 1- Click on the Start button located at the bottom left of the screen. 2- We notice that the programs installed on the calculator are arranged alphabetically in the list of programs. We search for the letter E using the scroll wheel on the mouse, and we notice all the programs that start with the letter E, including Excel. Note the figure below. 3- Open the Excel 2019 program window. Select a new blank workbook (New) that contains one worksheet (Sheet) as shown in the following figure. 4|Page Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬ Workbooks and Worksheets - In Excel, the file is called a Book, we can open several workbooks at the same time and each workbook will be opened in a separate window. Usually, the Excel 2019 file takes the extension (XLSX). - Each workbook contains only one worksheet, each worksheet consists of many cells, each cell contains a value, equation or text. The Book workbook in Excel 2019 consists of five ribbons: 1- Title Bar: It includes the program name, Excel, and the name of the open workbook. It has a default name of BOOK1. This workbook can be saved with a new name, and this new name will appear instead of BOOK1 in the title bar. This bar contains icons for closing, maximizing, restoring the window to its original size (maximize/restore), and minimizing the window. The Ribbon Display Options icon contains options for auto-hide the menus (Auto-Hide Ribbon), show the menus (Show Tabs), and show the menus with commands (Show Tabs and Commands). It contains a button bar on the left side. The Quick access toolbar contains a group of commands for quick access. You can add or remove specific commands by clicking on the arrow pointing down and selecting or deselecting the commands in the ribbon. Please note the figure below. 2- Menu Bar: This ribbon contains a set of menus listed below and each menu contains a set of commands. (File, Home, Insert, Page Layout, Formulas, Data, Review, View, Help, Search, share) 5|Page Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬ 3- Ribbon toolbar: This ribbon contains many commands and the commands vary from one list to another. These commands are arranged in groups, and each group includes commands to perform certain operations. For example, the Font command group in the Home menu is used to format text written in cells, and the Alignment command group is used to align the information in cells. We notice the presence of separators in the form of vertical lines between these groups. 4- Formula Bar: This bar consists of two main parts, the first is a box at the far left of the bar that contains the address of the active cell, and the second shows the contents of the active cell (the contents of the active cell may be a mathematical formula or any other data) as in the following figure. 5- Status Bar: It is a bar located at the bottom of the Excel program window. This bar contains the Sheet view and Zoom view for enlarging and reducing the worksheet. 6|Page Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬ Work sheet The worksheet consists of rows and columns. The rows are numbered from 1 to 1048576 and the columns from A to XFD. The intersection of any row with any column results in a cell, and each cell has an address where the cell name consists of a letter indicating the name of the column and a number indicating the row number. 7|Page Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬ Manipulating and navigating between worksheet cells: We use the mouse to navigate between the cells of the worksheet by clicking on the desired cell to be activated, but often we use the keyboard through the following commands: ❖ Enter: To move to the cell located directly below the active cell. ❖ Shift + Enter: To move to the cell located directly above the active cell. ❖ TAB: To move to the cell located directly to the left of the active cell if the direction of the worksheet is from right to left. ❖ Shift + TAB: To move to the cell located directly to the right of the active cell if the direction of the worksheet is from left to right. ❖ Arrows: We can navigate between cells using the arrows on the keyboard according to the direction. ❖ Page Up: Use this shortcut to move to the previous page. ❖ Page Down: Use this shortcut to move to the next page. ❖ If the workbook contains a large number of worksheets, you can use the navigation arrows on the right of the report bar. As in Shape: ❖ Ctrl+ Page Up: Use this shortcut to go to the beginning of the first page. ❖ Ctrl+ Page Down: Use this shortcut to go to the end of the last page. 8|Page Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬ Cells: Entering Contents in Excel Cells We can select any Cell start typing using keyboard to enter the data in Cell. We can double click or press F2 key to make a Cell editable. We can use Keyboard or Standard Excel Commands to Enter the Data in Excel. Entering Text in Excel Cells We can enter any strings as a Cell contents in Excel. You can enter a simple labels using alpha numeric characters. You can select any cell and click on the formula bar to enter the long text in a Cell. It is easy to enter long content in Cells using Formula bar. Entering Numerical Data in Excel Cell You can enter integer, byte and real numbers as a content of the Excel Cells. You can format the cell content using built in tool in the Excel Ribbon. For example, You enter Number of Employee in an organization (2500), You can enter any value like 0,1,2,…. Auto fill Sequential Numbers: You can select any two cells in the Excel and Drag the Cells to Auto fill the following cells in a sheet. Excel will automatically increase/decrease the number based on your selection.  Select any Range of cells  Place the mouse cursor at bottom-right corner of the selected range  Drag-down using the mouse to fill the target range Formatting the Cell Contents You can select any range of cells to format the Cell Contents into the required format. You can format the Cell font, Color, Background, Number Format, Conditional Format, Borders and Content Alignment. 9|Page Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬ Excel is provided with Many Tools to format the Cell contents, you can go to Home tab in the ribbon and use the commands in Font, Alignment, Numbers, Styles Group. Display the Content of Entire Cell Excel Cells will not display the complete text of the cell by default. The default width of the Cell is 8.43 inches, you can increase the cell width to display the Entire Cell contents.  You can double click on the Column to auto fit the Columns based on the Cell Contents  Merge the multiple Cells to make the bigger cell to fit the Entire Cell Content  You can use the Comments to display the additional information in the Cells Clearing Cell Contents: You can select any Cell and press Delete button in your keyboard to clear the contents in a Cell. Excel is provided with ready to use command to Clear the Cell Contents. We can use this command to clear only the content of the Selected Cells and remain the formatting and comments as it is. Follow the below steps to execute the Clear Contents commands  Select the Cell  Go to Home Tab  Click on Clear commands (this will display the List of Commands to Clear)  Click on the Clear Contents to Clear the Cell Contents Similarly, you can select Charts, Shapes in the Excel Sheets to Clear it. Using the Excel Functions to Deal with Cell Contents: The following formula can be used in Excel to deal with Cell Contents 10 | P a g e Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬  CONCATENATE: To combine the Cell contents from multiple cells into to one Cell  LEN: To find the Length of Cell Contents  TRIM: To remove the extra spaces from Cell Contents  SUM: To find the Total of Range of Cell Contents  COUNT: To Count the number of Cells with Cell Contents CELL Function to Retrieve Cell Information: We can use the Excel CELL Function to get the Cell details like Cell Address, Type, Row, Color, Formats etc. For example, the following Formula will get the address of the given Cell (E7). =CELL(“address”,E7) Select cells 1- We select a column (Column) by clicking on the name of the column such as the letter (A) where the mouse pointer turns into an arrow pointing down, then we press the left mouse button once, and we can select the column from the keyboard by pressing Ctrl with the ruler. 2- We select a row (Row) by clicking on the name of the row such as the number (1) where the mouse pointer turns into an arrow pointing left, then we press the left mouse button once, and we can select the row from the keyboard by pressing (Shift) with the ruler. 3- To select a separate group of columns or rows, we use the (Ctrl) button while continuing to press it while pointing with the mouse on the columns or rows that we want to select. 4- To select the entire worksheet, we press (Ctrl + A) from the keyboard. Row: Rows are Records in Excel Spreadsheet. Excel Spreadsheet is container in the Workbook, it is in Tabular Format with Rows (Horizontal records) 11 | P a g e Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬ and Columns (Vertical fields). Each row in a sheet represented with a unique row number, starts with row 1 Columns: Columns are Fields in Excel Spreadsheet. Excel Spreadsheet is container in the Workbook, it is in Tabular Format with Rows (Horizontal records) and Columns (Vertical fields). Each Column in a sheet represented with a unique Column Name, starts with Column name A. Hide and show rows and columns: To hide a row or column, select the row or column, then right-click anywhere in the selection and choose the Hide command from the menu. To show hidden rows or columns, select a column before the hidden column and at least one after it, then right-click on the selection and choose the Unhide command from the menu. To show all hidden columns and rows, select the entire worksheet by pressing the CTRL+A buttons (or clicking on the Select All icon located in the upper left corner of the worksheet if the worksheet is from left to right) then right-click and choose Unhide. Note: - If you print text in a cell and the text entered in the cell is longer than the size of the cell, the text continues to appear and exceeds the borders of the adjacent cell on the left because it is a mouse. However, if the adjacent cell is a mouse, the text stops appearing at the borders of the cell (but in reality it is inside the cell and when you click Double click on the cell to display the full text. 12 | P a g e Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬ To display the entire contents of the cell in such a case, we resort to the following: We modify the text to become shorter. We increase the width of the column containing the cell. We use a smaller font size. We use Wrap Text, which is the command to wrap the text inside the cell. Add more than one line in a cell: When you add the first line in a cell and then press Enter, you move to the cell below the active cell. To enter a new line in the same cell, press Alt + Enter. 13 | P a g e Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬ Home Tab:  Clipboard: Cut, Copy, and Paste commands for moving or duplicating data within a worksheet or between different worksheets or workbooks.  Font: Commands for formatting text, such as font style, size, color, bold, italic, underline, and strikethrough.  Alignment: Commands for adjusting the alignment of text within cells, such as horizontal and vertical alignment, text wrap, and indent.  Number: Commands for formatting numeric values, such as currency, date and time, percentage, and scientific notation.  Styles: Commands for applying predefined styles to cells, such as Title, Heading, and Total styles.  Cells: Commands for managing cells, such as inserting or deleting cells, hiding or unhiding rows and columns, and merging cells.  Editing: Commands for editing data within cells, such as Find and Replace, Clear, and Undo and Redo. 14 | P a g e Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬ 1- Clipboard group: The following operations: Cut: The process of cutting the contents of the cell and its shortcuts are CTRL+X. Copy: The process of copying the contents of the cell and its shortcut is CTRL+C. Format Copy: The process of copying and selecting from a specific cell an applying it to other cells. Paste: The process of pasting copied or cut contents from one cell to another. 15 | P a g e Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬ 2- Font group: It includes the following operations: Arial: To choose the font type from the arrow located to the right of Arial. Font size: To choose the font size for the cell contents. Bold: To make the writing bold, its shortcut is CTRL+B. Italic: To make the font italic, its shortcut is CTRL+I. Underline: To underline the text, its shortcut is CTRL+U. Increase font size: To increase the font size in the cell. Decrease font size: To decrease the font size in the cell. Bottom Border: To create a frame for specific cells. Fill color: To fill a specific cell with a specific color. Font color: To change the font color for a specific cell. 3- Alignment group: It includes the following operations: Top Align: To make the cell contents above the cell. 16 | P a g e Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬ Middle Align: To make the cell contents in the middle of the cell. Bottom Align: To make the cell contents below the cell. Align text left: To make the cell contents aligned to the left. Center: To make the cell contents in the middle. Align text Right: To make the cell contents aligned to the right. Decrease Indent: It is the process of reducing the distance between the frame and the text in the cell. CTRL+ALT+Shift+Tab and its abbreviation is Increase Indent: It is the process of increasing the distance between the frame and the text in the cell and its abbreviation is.CTRL+ ALT +Tab Orientation: Changing the directions of the cell contents (rotation). Wrap text: It is the process of rotating the text according to the size of the cell. Merge &center: It is the process of merging cells and making them one cell. Left-to-Right text Direction: The process of converting the contents from right to left and vice versa in a cell. 17 | P a g e Al-Kut University College ‫كلية الكوت الجامعة‬ Department of Medical laboratory Technologies ‫قسم تقنيات المختبرات الطبية‬ 4- Number group: It includes the following operations: Number format: To choose the type of cell contents.(General) Accounting Number format: To choose the currency type for the selected cell. Percent style: To make the cell contents a percentage.( CTRL+Shift+% )and its abbreviation Comma Style: To make the cell numbers have a comma up to a thousand. Increase Decimal: To increase the decimal numbers after the comma. Decrease Decimal: To decrease the decimal numbers after the comma. Format numbers, time and date: It is found in the Home list, Number group. In Excel, it is possible to format numbers, time and date in a way that suits the work requirements, for example, a group of numbers in consecutive cells to which the general format is applied (General). To change the format of these numbers, we go to the (Home) ← (Number) list, then click on the drop-down list for formatting numbers, and a group of formatting options will appear. 18 | P a g e