اكسل حاسبات PDF

Computer Course First Year/ Pharmacy Salima R. N. Jasim Lecture 6 Spread sheet Introduction to Spread Sheet Software Spreadsheet programs are software tools used to...

Computer Course First Year/ Pharmacy Salima R. N. Jasim Lecture 6 Spread sheet Introduction to Spread Sheet Software Spreadsheet programs are software tools used to organize and display data in tables consisting of rows and columns. These programs offer computational and analytical functions that facilitate easy and accurate data management, making them an effective tool for data analysis and decision-making. Importance of Spreadsheet Programs: 1. Data Organization: Makes storing information in an orderly and structured manner easier. 2. Efficient Analysis: Provides tools for data analysis using various formulas and functions. 3. Visual Representation: Enables the creation of charts to illustrate trends and data. 4. Time Saving: Automates repetitive tasks, reducing time and effort. Computer Course First Year/ Pharmacy Salima R. N. Jasim The basic components and features of spreadsheet 1. Cells: The individual boxes where data is entered. Each cell has an address based on its column (letter) and row (number), e.g., A1. 2. Rows and Columns: Spreadsheets are arranged in a grid of rows (horizontal) and columns (vertical). 3. Sheets: Multiple pages within a single spreadsheet file, which help organize different sets of data. 4. Formulas: A way to perform calculations. For example, =SUM(A1:A10) adds all values from cells A1 to A10. 5. Functions: Predefined formulas that perform specific calculations, like AVERAGE, COUNT, and IF. 6. Filtering and Sorting: Allows you to easily view specific data or arrange data in a particular order. 7. Charts: Graphical representations of data to visualize trends and comparisons. Common types include bar charts, line charts, and pie charts. Computer Course First Year/ Pharmacy Salima R. N. Jasim Creating and Formatting Worksheet Creating a New Worksheet: 1. How to Open Excel and Create a New File: o Open Excel: a. From the Start menu, type "Excel" in the search bar and press Enter. b. Select Microsoft Excel from the results. o Create a New File:  On the main screen, choose "Blank Workbook".  Or from File menu choose New o Open a saved Excel file:  From File menu, choose Open command  Select the target file (saved)  Press Open o Save the File:  Click File > Save As > select the desired folder.  Name the file (e.g., "Pharmacy") and click Save. 2. Defining Columns and Rows: o Adding Data:  In the first row, enter column headers (e.g., "Seq ,Material Name, Quantity, M.Date , Expiry Date").  Fill subsequent rows with the required data. o Adjusting Columns and Rows:  To change column width or row height:  Choose Home Tab  From Cells group, Click Format menu  Click Row height to change the height of selected row(s)  Click Column width to change the width of selected column(s) Or Computer Course First Year/ Pharmacy Salima R. N. Jasim  Hover over the boundary between column headers (e.g., A and B), and drag to resize.  To adjust row height: Hover over the boundary between row numbers and drag to resize. Formatting Work Tables 1. Changing Font Type and Size: o Select the cells you want to format. o From the Home tab in the toolbar, choose the desired font (e.g., Arial) and size (e.g., 12). 2. Coloring Text and Cells: o To color text: Click the Font Color icon and select a color. o To fill cells with color: Click the Fill Color icon and select a shade. 3. Adding Borders to Cells:: o Select the table or cell range. o From the Home tab, click the Borders icon and choose the type of border (e.g., "All Borders"). 4. Bold , Italic and Underlined text in Cells: o Select the table or cell range. o From the Home tab, click the Bold, Italic or Underlined icon within Font group 5. Merging & Center Cells and : o To combine multiple cells  Highlight the cells to merge.  Click Merge & Center from the toolbar. o To Wrap Text in cell :  Highlight the cells to merge (e.g., for a table title):.  Click Wrap Text from the toolbar. This command mostly used when text longer than column width. Computer Course First Year/ Pharmacy Salima R. N. Jasim Sorting and Filtering Data Sorting Data: 1. Sort Ascending or Descending: o Select the column or table to be sorted. o From the Data tab, click Sort A to Z (ascending) or Sort Z to A (descending). 2. Sort by Specific Columns: o Select the entire table. o From the Data tab, click Sort. o Choose the column to sort by (e.g., " Quantity") and the sorting order. Filtering Data: 1. Apply Filters to Show Specific Data: o Select the table and click Filter in the Data tab. o Small arrows will appear next to column headers. o Click an arrow, choose the desired values or conditions (e.g., "greater than 10"). 2. Remove Filters: o Click the Filter button again to clear the filters. ‫حاسبات‬ ‫وظائف‬ ‫احياء‬ ‫صيدالنيات‬ ‫عضوية‬ ‫االسم‬ 35 65 41 61 53 ‫محمد‬ 66 55 56 24 99 ‫عل‬ ‫ي‬ 98 31 46 57 88 ‫مهند‬ 32 28 43 21 28 ‫سيف‬ 53 82 49 93 32 ‫احمد‬ 41 46 23 61 94 ‫خالد‬ 34 58 63 88 35 ‫مازن‬ 93 36 93 55 24 ‫ى‬ ‫مصطف‬ 90 54 27 92 86 ‫يوسف‬ Computer Course First Year/ Pharmacy Salima R. N. Jasim Lecture 7 Using Formulas and Functions Excel is a powerful spreadsheet application that allows users to perform calculations, analyze data, and visualize information using formulas and functions Formulas: A formula in Excel is an expression that calculates the value of a cell. Formulas always begin with an equals sign (=). Here are some common types of formulas: 1. Basic Arithmetic:  Addition: =A1 + B1  Subtraction: =A1 - B1  Multiplication: =A1 * B1  Division: =A1 / B1 2. Order of Operations: Excel follows the standard order of operations ( 1. Parentheses 2. Exponents 3. multiplication and division 4. addition and subtraction For example:  = 2+(18-12)/(10-7) * 5  12 Functions Functions are predefined calculations in Excel that simplify complex calculations. Functions can take arguments (inputs) and return values. Here are some common Excel functions: Mathematical Functions and statistical functions 1. SUM: Adds all numbers in a range. o Syntax: =SUM(number1, [number2],...) o Example1: =Sum(A1,G4,B3,D7) o Example2: =Sum(A1:A10) Computer Course First Year/ Pharmacy Salima R. N. Jasim 2. AVERAGE: Calculates the average of a set of numbers. o Syntax: =AVERAGE(number1, [number2],...) o Example1: =Average(A1,G4,B3,D7) o Example2: =Average(A1:A10) 3. MIN: Returns the smallest number in a range. o Syntax: =Min(number1, [number2],...) o Example1: =Min(A1,G4,B3,D7) o Example2: =Min(A1:A10) 4. MAX: Returns the largest number in a range. o Syntax: =MAX(number1, [number2],...) o Example1: =Max(A1,G4,B3,D7) o Example2: =Max(A1:A10) 5. LARGE: returns the k-th largest value in a range o Syntax: =Large(array ,k) o array: The range or array of numbers from which you want to find the largest values. o k: The position (rank) of the value you want to find. o Example 1: = Large (A1:A10,1)  returns the largest number. o Example 2: = Large (A1:A10,2)  returns the second-largest number. 6. Small : returns the k-th smallest value in a range o Syntax: =small(array ,k) o array: The range or array of numbers from which you want to find the smallest values. o k: The position (rank) of the value you want to find. o Example 1: = small (A1:A10,1)  returns the smallest number. o Example 2: = small (A1:A10,2)  returns the second-smallest number. Computer Course First Year/ Pharmacy Salima R. N. Jasim 7. COUNT: Counts the number of cells containing numbers. o Syntax: =Count(Range ) o Example1: =Count(A1:A12) 8. COUNTA: Counts the number of non-empty cells in a range. o Syntax: =CountA(Range) o Example1: =CountA(A1:A12) 9. COUNTBLANK: Counts the number of empty cells in a range. o Syntax: = Countblank(Range) o Example1: = Countblank(A1:A12) 10. COUNTIF: Counts the number of cells that meet a specific criterion. o Syntax: =COUNTIF(range, criteria) o Example1: =CountIF(A1:A12,”>=50”) 11.MEDIAN: Returns the median of a set of numbers. o Syntax: =MEDIAN(number1, [number2],...) o Example1: = Median(A1,B4,C2,D5,H2,………) o Example2: = Median (A1:A12) 12. ROUND: Rounds a number to a specified number of digits. o Syntax: =ROUND(number, num_digits) o Example1: =Round(A1,2) 13. PRODUCT: Multiplies all numbers given as arguments. o Syntax: =PRODUCT(number1, [number2],...) o Example1: =Product(A1,G4,B3,D7) o Example2: =Product(A1:A10) Computer Course First Year/ Pharmacy Salima R. N. Jasim 14. POWER: Returns the result of a number raised to a power. o Syntax: =POWER(number, power) o Example1: =Power(A1,2) 15. SQRT: Returns the square root of a number. o Syntax: =SQRT(number) o Example1: =Sqrt(A1) 16. INT: Rounds a number down to the nearest integer. o Syntax: =INT(number) o Example1: =Int(A1) 17. RAND: Returns a random number greater than or equal to 0 and less than 1. o Syntax: =RAND() 18. RANDBETWEEN: Returns a random integer between two specified values. o Syntax: =RANDBETWEEN(bottom, top) o Example1: =Randbetween(0,99) Logical Functions: 19. IF(condition, value_if_true, value_if_false): Checks a condition and returns one value if true, another if false. o Example1: =IF(A1 >=50, "Pass", "Failed") Text Functions: 20. CONCATENATE(text1, text2,...) or CONCAT(text1, text2,...): Joins two or more text strings into one. o Example: =CONCATENATE(A1, " ", B1) o Or using & symbol to concat tow symbol o 21. TEXT(value, format_text): Formats a number and converts it to text. o Example: =TEXT(A1, "0.00") Computer Course First Year/ Pharmacy Salima R. N. Jasim Date and Time Functions: 22. TODAY(): Returns the current date. 23. NOW(): Returns the current date and time. Data Validation and Error Checking in Excel 1. Data Validation Data Validation in Excel allows you to control what type of data can be entered into a cell. You can set rules that restrict the data to a specific format, value range, or even apply formulas to ensure data entry accuracy. How to Set Data Validation: 1. Select the cell(s) where you want to apply validation. 2. Go to the Data tab on the Ribbon. 3. Click on Data Validation in the 'Data Tools' group. Common Data Validation Options:  Whole Number/Decimal: Limit data to specific number ranges (e.g., between 1 and 100).  List: Create a dropdown menu for the user to select from predefined values.  Date/Time: Restrict entries to specific dates or times.  Custom: Use formulas to define more complex validation rules (e.g., an entry that matches a specific pattern or formula). Example of Data Validation:  Restricting a cell to only allow numbers between 10 and 100: o Choose Whole number from the validation criteria, then set the start and end dates.  Allowing only values from a list (e.g., restricting the input to "Yes" or "No"): o Select List and enter "Yes,No" in the Source field. Computer Course First Year/ Pharmacy Salima R. N. Jasim 2. Error Checking Error Checking helps identify and manage common errors in Excel. Excel can automatically flag issues and suggest fixes for common data entry mistakes. Common Types of Errors:  #DIV/0!: Division by zero.  #VALUE!: Invalid argument type (e.g., text in a numeric field).  #N/A: A value is not available.  #REF!: Invalid reference (e.g., deleted cells).  #NAME?: A formula contains an unrecognized text value or undefined name.  #NUM!: Invalid numeric value or calculation. Data Visualization Creating Charts and Graphs Charts are a powerful way to visualize data in Excel, making it easier to interpret and analyze trends, comparisons, and relationships. Excel offers a variety of chart types to suit different data types and purposes, such as bar charts, line charts, pie charts, scatter plots, and more. Steps to Create a Chart in Excel: 1. Preparing Your Data Before creating a chart, ensure your data is well-organized in a table format:  Column headers should describe the type of data in each column.  Rows should represent individual data entries. Product Quantity Paracetamol 50 Amoxicillin 30 Ibuprofen 40 Vitamin C 60 Computer Course First Year/ Pharmacy Salima R. N. Jasim 2. Creating a Chart 1. Select your data: o Highlight the range of data you want to include in the chart (e.g., select the data from A1 to C4). 2. Insert a chart: o Go to the Insert tab on the Ribbon. o In the Charts group, you'll see options for different chart types (Column, Line, Pie, Bar, etc.). o Choose the chart type that best represents your data. For example, for sales and expenses over time, a Line Chart or Column Chart could work well. o Click the chart type you want, and Excel will generate a chart based on your selected data. 3. Modifying and Customizing Your Chart Once your chart is created, you can make various adjustments to customize it: a. Change Chart Type  Right-click on the chart.  Select Change Chart Type.  Choose from various chart types (Bar, Line, Pie, etc.), and Excel will reformat the chart accordingly. b. Add or Edit Chart Elements  Select the chart, and use the Chart Elements button (+ sign next to the chart) to add or edit: o Chart Title: Add or edit the title to describe what the chart represents. o Axis Titles: Add titles for the X and Y axes. o Data Labels: Display values on the chart for each data point. o Legend: Customize the position or remove it. c. Formatting the Chart  Right-click on any part of the chart (like bars, lines, or axes) to access formatting options: o Format Data Series: Change the appearance of the chart's elements (e.g., color, thickness, patterns). o Format Axis: Adjust the scale, intervals, and appearance of axes. o Format Plot Area: Change the background of the chart area. d. Change Colors and Styles  Use the Chart Styles gallery (available in the Ribbon under Chart Tools > Design) to quickly change the chart's overall style and color scheme. e. Move or Resize the Chart  You can click and drag to move the chart to a different location.  Resize it by clicking and dragging the edges or corners. Computer Course First Year/ Pharmacy Salima R. N. Jasim 4. Chart Types Overview Different charts serve different purposes:  Column/Bar Chart: Useful for comparing different categories.  Line Chart: Good for showing trends over time.  Pie Chart: Ideal for showing proportions of a whole.  Scatter Plot: Best for showing relationships between two variables.  Area Chart: Similar to a line chart, but with a shaded area under the line.