1 / 30

FORMATING

Lesson 5 MICROSOFT EXCEL PART 2 by Nguyễn Thanh Tùng Email: tungnt@isvnu.vn Web: http://khoaquocte.vn/Article/Index/405. FORMATING. Row height Column width Format cell Number Alignment Font Border Fill (Patterns). FORMATTING: FORMAT CELL -> NUMBER. FORMATTING: FORMAT CELL -> ALIGNMENT.

micah
Download Presentation

FORMATING

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lesson 5MICROSOFT EXCEL PART 2by NguyễnThanhTùngEmail: tungnt@isvnu.vnWeb: http://khoaquocte.vn/Article/Index/405

  2. FORMATING • Row height • Column width • Format cell • Number • Alignment • Font • Border • Fill (Patterns)

  3. FORMATTING: FORMAT CELL -> NUMBER

  4. FORMATTING: FORMAT CELL -> ALIGNMENT

  5. FORMATTING: FORMAT CELL -> FONT

  6. FORMATTING: FORMAT CELL -> BORDER

  7. FORMATTING: FORMAT CELL -> PATTERNS

  8. BASIC FORMULAS: FUNCTIONS • Left(text,num) : return the first or number of character LEFT E.g.: C5="tung"; Left(c5,2)="tu" • Right(text,num): return the first or number of character RIGHT E.g.: C5="tung"; right(c5,2)=”ng" • mid(text,start_num,num): return a number of characters from a text string, starting at the position specified E.g.: C5="tungday"; mid(c5,2,4)=”ungd"

  9. BASIC FORMULAS: FUNCTIONS • len(text): return the number of characters in a text string E.g.: C5="tung"; len(c5)=4 • trim(text): removes all spaces from text except for single space between words E.g.: C5=” tung"; trim(c5)=”tung" • upper(text): Converts text to uppercase E.g.: C5="tung"; upper(c5)=”TUNG" • lower(text): Converts text to uppercase E.g.: C5="TUNG"; lower(c5)=”tung"

  10. BASIC FORMULAS: FUNCTIONS • exact(text1,text2): return true if they are the same. False otherwise • find(findtext,within_text,start_num): locate one text string within a second text string, and return the number of starting position of the first text; start_num specify the character to start the search. By default, it is assumed to be 1. E.g.: C5=” tungdihoc"; D5=”di”;find(d5,c5)=4

  11. BASIC FORMULAS: FUNCTIONS • AND(logical1,logical2,……..): AND(true,true)=true; AND(true,false)=false;AND(false,true)=false;AND(false,false)=false; e.g. A2=50,A3=104 A4=AND(1<a2,a2<100)=true • OR(logical1,logical2,……..): OR(true,true)=true; OR(true,false)=true;OR(false,true)=true; OR(false,false)=false; • NOT(logical): NOT(true)=false; NOT(false)=true;

  12. BASIC FORMULAS: FUNCTIONS • Round(number,num_digits)=round a number to a number of digits e.g. Round(23.44444,2)=23.44 • Abs(number)=return the absolute value of a number • Int(number)= round down to an integer e.g. Int(23.74)=23 • Sqrt(number)= return a positive square root e.g. sqrt(9)=3

  13. BASIC FORMULAS: FUNCTIONS • Counta(range)=count the number of cells that are not empty

  14. BASIC FORMULAS: FUNCTIONS • Countblank(range)= count empty cells in a specified range

  15. BASIC FORMULAS: FUNCTIONS • Countif(range,criteria)=count number of cells within a range that meet a single criteria. • For example, you can count all the cells match a string or count all cells that contain a number larger or smaller than a specified number.

  16. BASIC FORMULAS: FUNCTIONS • Sumif(range,criteria,[sum_range])=sum the values in a range that meet criteria specified

  17. BASIC FORMULAS: FUNCTIONS • If(logical_test,value_if_true,[value_if_false]): the if function returns one value if a condition is true, and other if that condition is false)

  18. If function remark • Remark: up to 64 if functions can be nested as value_true or value_false.

  19. BASIC FORMULAS: FUNCTIONS • The LOOKUP function returns a value either from a one-row or one column range • LOOKUP function has two syntax forms: the vector form and the array form.

  20. BASIC FORMULAS: FUNCTIONS • Vlookup(lookup_value,table_array,col_index,[range_lookup]) to search the first column of a range, and return a value from a column_index and in the same row.

  21. Vlookup Example

  22. BASIC FORMULAS: FUNCTIONS • Hlookup(lookup_value,table_array,row_index,range_lookup): search for a value in the top row of a table, and returns a value in the same column from row_index). Use hlookup when the comparison value are located in the top row of table value.

  23. HLookup

  24. BASIC FORMULAS: FUNCTIONS • Rank(number,array,order): return the rank of a number in a list of numbers.

  25. BASIC FORMULAS: FUNCTIONS • Index(array,row_num,row_column) returns the value of an element in the array specified by row and column index.

  26. BASIC FORMULAS: DATE/TIME • NOW() • DATE(year,month,day) • TIME(hour,minute,second) • DAY(serial_number) • MONTH(serial_number) • YEAR(serial_number) • HOUR(serial_number) • MINUTE(serial_number) • SECOND(serial_number)

  27. Save as Web page • File->Save As->Web page

  28. Open Web page

  29. Save as PDF • File->Save As->PDF

  30. Open Pdf

More Related