สรุป 10 สูตร Excel พื้นฐาน ที่คนใช้งาน Excel ต้องรู้ ใน 10 นาที

Introduction

This tutorial summarizes ten fundamental Excel formulas that every user should know. Understanding these formulas will enhance your efficiency in data management and analysis, enabling you to perform a range of calculations and data manipulations quickly.

Step 1: Using SUM to Calculate Total

• The SUM function adds together a range of numbers.
• Formula Syntax: =SUM(range)
• Example: To sum values from A1 to A10, use =SUM(A1:A10).

Step 2: Using SUMIF for Conditional Summation

• SUMIF sums values based on a specific condition.
• Formula Syntax: =SUMIF(range, criteria, [sum_range])
• Example: To sum values in A1:A10 where corresponding values in B1:B10 are "Yes", use =SUMIF(B1:B10, "Yes", A1:A10).

Step 3: Utilizing VLOOKUP for Data Retrieval

• VLOOKUP searches for a value in the first column of a table and returns a value in the same row from a specified column.
• Formula Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• Example: To find the price of an item in a table, use =VLOOKUP("ItemName", A1:C10, 2, FALSE).

Step 4: Counting Entries with COUNT

• COUNT counts the number of cells that contain numbers in a specified range.
• Formula Syntax: =COUNT(range)
• Example: To count numbers in A1:A10, use =COUNT(A1:A10).

Step 5: Calculating Average with AVERAGE

• AVERAGE calculates the mean of a range of numbers.
• Formula Syntax: =AVERAGE(range)
• Example: To find the average of values in A1:A10, use =AVERAGE(A1:A10).

Step 6: Finding Maximum and Minimum Values

• MAX finds the highest value, while MIN finds the lowest value in a range.
• Formula Syntax:
  • For MAX: =MAX(range)
  • For MIN: =MIN(range)
• Example: To find max and min from A1:A10, use =MAX(A1:A10) and =MIN(A1:A10) respectively.

Step 7: Implementing IF for Conditional Logic

• IF creates logical tests and returns values based on whether the test is true or false.
• Formula Syntax: =IF(logical_test, value_if_true, value_if_false)
• Example: To check if a value in A1 is greater than 10, use =IF(A1 > 10, "Yes", "No").

Step 8: Concatenating Text with CONCATENATE or &

• CONCATENATE (or the & operator) joins multiple text strings into one.
• Formula Syntax:
  • For CONCATENATE: =CONCATENATE(text1, text2, ...)
  • Using &: =text1 & text2
• Example: To join A1 and B1, use =CONCATENATE(A1, B1) or =A1 & B1.

Step 9: Inserting Today's Date with TODAY

• TODAY returns the current date.
• Formula Syntax: =TODAY()
• Example: To display today’s date in a cell, simply enter =TODAY().

Step 10: Changing Text Case with UPPER and LOWER

• UPPER converts text to uppercase, while LOWER converts it to lowercase.
• Formula Syntax:
  • For UPPER: =UPPER(text)
  • For LOWER: =LOWER(text)
• Example: To convert text in A1 to uppercase, use =UPPER(A1).

Conclusion

By mastering these ten Excel formulas, you will significantly improve your spreadsheet skills. Practice these formulas in different scenarios to better understand their applications. For further learning, consider exploring more advanced functions and how they can integrate with these basics. For a hands-on experience, download the Excel file provided in the video description here.