Lecture 07 Health Informatics Microsoft Excel PDF

Lecture 07 ‫كـــؤليَذى ثزيشـكى‬ Health Informatics Microsoft Excel ‫كـــؤليَذى ثزيشـكى‬ Dr. Nabeel Abdulrazzaq Fattah Assistant Professor Biomedical Engineering, PhD,...

Lecture 07 ‫كـــؤليَذى ثزيشـكى‬ Health Informatics Microsoft Excel ‫كـــؤليَذى ثزيشـكى‬ Dr. Nabeel Abdulrazzaq Fattah Assistant Professor Biomedical Engineering, PhD, UK Nursing and patient care, Diploma, Ireland Nursing Studies - The Physical Examination, Diploma, Ireland  ‫كـــؤليَذى ثزيشـكى‬ ‫‪Advanced Excel‬‬ Content ‫كـــؤليَذى ثزيشـكى‬ Advanced Formulas: o Nested functions, logical functions (IF, AND, OR) o Lookup functions (VLOOKUP, HLOOKUP) Data Validation: o Creating data entry rules Macros: o Recording and running macros to automate tasks Advanced Formulas ‫كـــؤليَذى ثزيشـكى‬ Nested Functions: Combine multiple functions within a formula. Logical Functions: IF, AND, OR, NOT. Lookup Functions: VLOOKUP, HLOOKUP, INDEX, MATCH. Nested Functions Example ‫كـــؤليَذى ثزيشـكى‬ Example: Calculate the commission based on sales and a commission rate. Formula: =IF(A2>10000, A2*0.1, A2*0.05)  * “Nesting” refers to the practice of joining multiple functions together in one formula. =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F")))) ‫كـــؤليَذى ثزيشـكى‬ This complex nested IF statement follows a straightforward logic: 1. If the Test Score (in cell D2) is greater than 89, then the student gets an A 2. If the Test Score is greater than 79, then the student gets a B 3. If the Test Score is greater than 69, then the student gets a C 4. If the Test Score is greater than 59, then the student gets a D 5. Otherwise the student gets an F Logical Functions Example ‫كـــؤليَذى ثزيشـكى‬ Example: Determine if a student passed or failed based on their grade. Formula: =IF(B2>=60, "Pass", "Fail") VLOOKUP Function ‫كـــؤليَذى ثزيشـكى‬ Purpose: Look up a value in a table. Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) ‫‪VLOOKUP Example‬‬ ‫كـــؤليَذى ثزيشـكى‬ HLOOKUP Function ‫كـــؤليَذى ثزيشـكى‬ Purpose: Look up a value in a row. Syntax: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) HLOOKUP Example ‫كـــؤليَذى ثزيشـكى‬ Looks up "Axles" in row 1, and returns the value from row 2 that's in the same column (column A). INDEX and MATCH Functions ‫كـــؤليَذى ثزيشـكى‬ Purpose: Combine to look up a value in a table. Syntax: INDEX(array, row_num, [column_num]) and MATCH(lookup_value, lookup_array, [match_type]) INDEX and MATCH Example ‫كـــؤليَذى ثزيشـكى‬ The intersection of the second row and third column in the range A2:C6, which is the contents of cell C3. Data Validation ‫كـــؤليَذى ثزيشـكى‬ Purpose: Restrict data entry to specific values or criteria. Types: Data validation lists Decimal places Whole numbers Date ranges Applying Data Validation ‫كـــؤليَذى ثزيشـكى‬ Steps: 1. Select the cell range. 2. Go to the "Data" tab. 3. Click "Data Validation." 4. Set the validation criteria. Macros ‫كـــؤليَذى ثزيشـكى‬ Purpose: Automate repetitive tasks. 1 Recording Macros: Turn on macro recording. Perform the desired actions. Stop macro recording. 2 3  Running Macros ‫كـــؤليَذى ثزيشـكى‬ Steps: 1. Go to the "Developer" tab. 2. Click "Macros." 3. Choose the macro and run it. Editing Macros ‫كـــؤليَذى ثزيشـكى‬ Steps: 1. Go to the "Developer" tab. 2. Click "Visual Basic." 3. Edit the macro code. Conditional Formatting ‫كـــؤليَذى ثزيشـكى‬ Advanced Techniques: Highlight cells rules Data bars Color scales Icon sets Pivot Tables ‫كـــؤليَذى ثزيشـكى‬ Advanced Techniques: Calculated fields Slicers Timelines Creating Calculated Fields ‫كـــؤليَذى ثزيشـكى‬ Steps: 1. Go to the "Analyze" tab. 2. Click "Fields, Items & Sets." 3. Choose "Calculated Field." 4. Create a formula. Using Slicers ‫كـــؤليَذى ثزيشـكى‬ Purpose: Filter pivot table data interactively. Steps: 1. Go to the "Analyze" tab. 2. Click "Insert Slicer." 3. Choose the field(s) to slice. Using Timelines ‫كـــؤليَذى ثزيشـكى‬ Purpose: Filter pivot table data by date or time. Steps: 1. Go to the "Analyze" tab. 2. Click "Insert Timeline." 3. Choose the date or time field. Exercise 1: Creating a Complex Formula ‫كـــؤليَذى ثزيشـكى‬ Task: Calculate the total cost based on price, quantity, and tax. Instructions: Use nested functions and cell references. Exercise 2: Applying Data Validation ‫كـــؤليَذى ثزيشـكى‬ Task: Restrict data entry in a cell to a specific list of values. Instructions: Apply data validation and create a list. Exercise 3: Creating a Macro ‫كـــؤليَذى ثزيشـكى‬ Task: Automate the process of calculating a sum. Instructions: Record a macro and edit the code if necessary. ‫‪Thanks‬‬ ‫كـــؤليَذى ثزيشـكى‬