Bài 2: Làm việc với công thức và hàm

Sau khi nhập xong các dữ liệu độc lập, công việc tiếp theo là tính toán các dữ liệu phụ thuộc, tức là các dữ liệu thu được từ các dữ liệu ban đầu. Nếu việc tính toán này được thực hiện bằng tay thì chắc chắn không tránh khỏi sai sót. Bên cạnh đó, nếu sau này có sự thay đôi hay cập nhật dữ liệu mới thì việc tính toán sẽ phải làm lại từ đầu. Để tránh những phiền phức này, Excel cung cấp các công thức và hàm trợ giúp các phép tính từ đơn giản đến phức tạp. Ket quà cũng được tự động cập nhật nếu có sự thay đổi về dừ liệu đầu vào.

Việc tính toán trong Excel được thực hiện với dừ liệu chứa trong các ô, và lấy dữ liệu thông qua địa chỉ ô. Có hai loại địa chi ô là địa chỉ tương đối và địa chỉ tuyệt đối.

Được thành lập bằng tên cột trước và tên hàng sau. Ví dụ như ô A1, ô B5,... Địa chỉ tương đối sẽ thay đổi khi sử dụng lệnh sao chép công thức.

Tương tự như địa chỉ tương đối nhưng có thêm kí hiệu $. Có ba loại địa chì tuvệt đối:

• Tuyệt đổi cột: Ki hiệu $ được đặt trước tên cột. Ví dụ: $A3.
• Tuyệt đồi hàng: Kí hiệu $ được đặt trước tên hàng. Ví dụ A$3.
• Tuyệt đối ô (thường gọi là địa chí tuyệt đổi): Kí hiệu $ được đặt trước cả tên cột và tên hàng. Ví dụ $A$3.

Khác với địa chi tương đối, địa chỉ tuyệt đối không thay đôi khi sử dụng lệnh sao chép công thức. Do vậy, địa chi tuyệt đối thường được sử dụng khi có giá trị nào đó cần đặt cố định trong công thức.

Miền dữ liệu là một tập hợp các ô được đánh dấu bang cách lần lượt chỉ ra ba thành phần: địa chỉ ô đầu tiên phía trái trên của miền, dấu hai chấm và địa chỉ ô cuối cùng phía dưới phải của miền. Ví dụ, A1:B5 gồm các ô A1, A2, A3, A4, A5, B1, B2, B3, B4, B5.

Hình 8.29. Miền dữ liệu A1:B5

Công thức đơn giản là công thức chỉ gồm các phép tính toán cộng (+), trừ (-), nhân (*), chia (/), luỳ thừa (A).

Giả sử, ví dụ ở mục 8.1.6 có thêm cột Lưong thực lĩnh = Lưong CB X Bậc lương. Để tính Lưong thực lĩnh cho Hồ Ngọc Hoa, trong đó ô F2 chứa Bậc lưong và ô G2 chứa Lương CB của Hồ Ngọc Hoa thực hiện theo các bước sau:

• Lựa chọn ô chứa kết quả cùa công thức, cụ thể ở đây là ô H2.
• Nhập dấu “=” (gõ vào từ bàn phím).
• Nháy chuột vào ô có tham chiếu đến trong công thức, hoặc gõ địa chỉ của nó từ bàn phím, ở đây là ô F2. Excel sẽ hiên thị một khung màu xung quanh ô được tham chiếu là ô F2. Trên thanh công thức (Formula) đồng thời cũng xuât hiện địa chỉ ô F2 cùng màu.
• Nhập phép toán, cụ thể ở đây là phép nhân (*).
• Nháy chuột vào ô tiếp theo có tham chicu đến trong công thức, hoặc gõ địa chỉ của nó từ bàn phím, ở đây là ô G2. Excel sẽ hiển thị một khung màu khác với khung màu vừa rồi xung quanh ô được tham chiếu là ô G2 và sử dụng cùng màu đó để hiền thị địa chí ô G2 trên thanh công thức.
• Nhấn Enter để hoàn thành công thức và kết quả của phép tính sẽ được hiển thị tại ô chứa kết quá H2.

Hình 8.30. Nhập công thửc đơn giản

Các bước nhập công thức:

• Bước 1: Lựa chọn ô chứa kết quả.
• Bước 2: Nhập dấu “=”.
• Bước 3: Nháy chuột vào ô có tham chiếu đến công thức, hoặc gõ địa chỉ của nó từ bàn phím.
• Bước 4: Nhập phép toán.
• Bước 5: Nháy chuột vào ô tiếp theo có tham chiếu trong công thức hoặc gõ địa chỉ của từ bàn phím.
• Bước 6: Các công việc từ bước 3 đến bước 5 ở trên tiếp tục được lặp lại cho đến khi hết các ô có tham chiếu trong công thức.
• Bước 7: Nhấn Enter để hoàn thành việc nhập công thức và kết quả được hiển thị tại ô chứa kết quả.

Lưu ý: Việc nhập địa chỉ ô tham chiếu có thể thay bằng nhập giá trị trực tiếp của ô đó. Tuy nhiên, việc nhập này không được khuyến khích sử dụng trong Excel, vì nó không có tác dụng khi sử dụng lệnh sao chép công thức, hay khi có sự thay đổi giá trị thì kết quả không được cập nhật.

Không thể đảm bảo lúc nào công thức nhập vào cũng đúng, chẳng hạn chọn nhầm địa chỉ ô tham chiếu hay gõ nhầm phép toán, cũng có thẻ thay đổi hẳn công thức tính toán. Việc sửa công thức đã nhập được thực hiện như sau:

• Nháy chuột vào ô chứa công thức cần sửa. Ô chứa công thức này thực chất là chứa kết quả của phép toán. Còn bản thân công thức thì hiển thị trên thanh Formula sau khi nháy chuột vào ô đó.
• Nháy chuột vào thanh Formula hoặc nhấn phím F2 để bắt đầu sứa. Các ô được tham chiếu trong công thức sẽ được bao quanh bằng các ô màu như hình 8.31.

Hình 8.31. Sửa công thức

• Việc sửa công thức có thể thực hiện bằng một trong hai cách sau:
  • Kéo đường viền xung quanh ô đã được tham chiếu và thả nó sang ô khác muốn thay đổi.
  • Sửa công thức bằng cách di chuyển con trỏ soạn thảo sang trái, sang phải; sử dụng các phím Backspace và Delete đề xoá những chổ sai và sửa.
• Nhấn Enter để hoàn tất việc sửa công thức.

Lưu ý: Việc sửa công thức có thề thực hiện nhanh bằng cách nháy đúp chuột vào ô cần sửa.

Một trong những tính nãng giúp Excel trở thành công cụ trợ giúp tính toán đắc lực đó chính là tính năng sao chép công thức tự động. Như phần tính toán trên đã tính được Lương thực lĩnh cho Hồ Ngọc Hoa theo công thức Lương thực lĩnh = Lương cơ bản X Bậc lương. Công thức đặt tại ô kết quà là: =F2*G2. Khi sử dụng lệnh sao chép công thức thì phần Lương thực lĩnh của Nguyễn Gia Cường có công thức đặt tại ô chứa kết quả H3 là: =F3*G3. Và tương tự như thế với các cán bộ còn lại trong danh sách.

Sao chép công thức với AutoFill

Với các ô gần kè nhau có công thức tương tự nhau, có thể sử dụng tính năng sao chép công thức AutoFill để điền công thức tự động. Các thao tác để thực hiện việc sao chép công thức với AutoFill như sau:

• Bước 1: Nháy chuột vào ô chứa công thức cần sao chép. Ở ví dụ trên là nháy chuột vào ô H2. Sau khi nháy chuột, ô đó được bao bằng một khung màu đen, thể hiện đó là ô hiện thời.
• Bước 2: Đặt con trở chuột vào dấu chấm đen ớ góc dưới bôn phái của ô hiện thời để xuất hiện dấu cộng đen.
• Bước 3: Kéo dấu cộng đen dọc theo cột hoặc ngang theo hàng đè sao chép công thức của ô ban đầu. Như ví dụ ở trên thì kéo dấu cộng đen dọc theo cột H đến hết ô H7.
• Bước 4: Thả chuột để hoàn thành việc sao chép công thức. Bây giờ quan sát các ô H3, H4,... sẽ thấy sự thay đổi cúa công thức và tác dụng cùa việc sao chép công thức. Ví dụ ở ô H3 lúc này công thức sẽ là =F3*G3.

Hình 8.32. Sao chép công thức với AutoFill

Sao chép công thức vói Copy và Paste

Sử dụng tính năng AutoFill giúp việc sao chép công thức rất nhanh và đon giản, nhưng chi áp dụng được cho các ô ờ liền kề nhau. Sử dụng lệnh Copv và Paste sẽ giúp sao chép công thức ở bất kì vị trí nào trên bằng tính, thậm chí là giữa các bảng tính với nhau.

Các thao tác để thực hiện việc sao chép công thức với Copy và Paste như sau:

• Bước 1: Nháy chuột vào ô chứa công thức cần sao chép. Sau khi nháy chuột, ô đó được bao bằng một khung màu đen, thể hiện đó là ô hiện thời.
• Bước 2: Nháy chuột vào nút lệnh Copy trên thẻ Home trên thanh Ribbon, hoặc nhấn tổ hợp phím Ctrl + C. Một khung bao nhấp nháy với các vạch rời xuất hiện xung quanh ô chứa công thức.
• Bước 3: Nháy chuột vào ô mới cần sao chép công thức. Neu muốn sao chép công thức cho một dãy các ô có thé kéo và thả chuột qua miền ô đó. Hoặc nếu muốn sao chép công thức cho một dãy ô rời nhau có thê nháy chuột vào ô đầu tiên, sau đó bấm giừ phím Ctrl và nháy chuột vào các ô tiếp theo trong dãy. Các ô được lựa chọn sẽ được đánh dấu nổi.
• Bước 4: Nháy chuột vào nút lệnh Paste trên thẻ Home trên thanh Ribbon, hoặc nhấn tổ h(rp phím Ctrl + V để hoàn thành việc sao chép công thức.

Sao chép giá trị (Values) thay vì công thức (Formulas)

Công thức được sao chép tự động khi sử dụng các tính năng sao chép công thức. Tuy nhiên, đó không phai là tất cá những gì người sử dụng mong muốn. Ví dụ cần sao chép kết quả lương thực lĩnh của các cán bộ để làm các thống kê lương cho cán bộ mới vào biên chế. Việc sao chép giá trị thay vì công thức được thực hiện như sau:

• Bước 1: Lựa chọn ô (hoặc nhiều ô) muốn sao chép giá trị. Một khung đen sẽ bao quanh các ô đó.
• Bước 2: Nháy chuột vào nút Copy trên thẻ Home trên thanh Ribbon, hoặc nhấn tổ hợp phím Ctrl + C. Khung đen sê chuyển thành khung nhấp nháy với các vạch rời bao xung quanh các ô đó.
• Bước 3: Lựa chọn ô (hoặc nhiều ô) muốn đặt kết quả sao chép.
• Bước 4: Nháy con trỏ chuột vào nút mũi tên xuống trên nút Paste tại thẻ Home trên thanh Ribbon. Trên menu Paste hiện ra (như hình 8.33), lựa chọn chính xác loại sao chép phù hợp tại Paste Values:
  • Values: Sao chép kết quả cùa ô ban đầu.
  • Values & Number Formatting: Sao chép kết quả và định dạng kiểu số của ô ban đẩu.
  • Values & Source Formatting: Sao chép kết quà, định dạng kiều số và định dạng ô của ô ban đầu.

Việc sử dụng công thức đơn giản chi đáp ứng một phần rất nhỏ nhu cầu tính toán trong công việc. Khi các yêu cầu tính toán trờ nên phức tạp hơn, hoặc là số lượng các phép tính tăng lên thì cần sử dụng đến các hàm hổ trợ. Ví dụ như việc tính tồng lương thực lĩnh cho 6 cán bộ ở ví dụ ở mục 8.1.6 có thể đơn giản bằng cách tự thành lập công thức: =H2+H3+H4+H5+H6+H7. Nhưng khi số lượng cán bộ lên tới hàng trăm người thì việc nhập công thức đơn giản như vậy rất mất thời gian và không tránh khói sai sót. Khi đó, cần sử dụng hàm tính tổng SUM.

Tất cả các hàm đều có chưng một cú pháp:

• Trước hết, giống như công thức, hàm được bắt đầu bằng dấu bằng
• Tiếp theo là tên hàm, ví dụ SUM, để Excel biết tính toán nào sè được hiển thị.
• Kí hiệu tiếp theo là dấu mở ngoặc đơn “(”, để Excel biết nhừng dữ liệu tiếp sau đây là hàm cần sử dụng để tính toán.
• Sau dấu mở ngoặc là danh sách các tham số. Các tham số này có thể là địa chỉ một miền, một dãy ô hoặc một giá trị cụ thể. Danh sách các tham số này cách nhau bời dấu; hoặc dấu, tuỳ từng quy định cùa hệ điều hành.
• Cuối cùng dùng kí hiệu đóng ngoặc đơn “)” Để kết thúc cú pháp hàm.

Có hai cách nhập hàm:

• Cách 1: Nhập trực tiếp bằng cú pháp hàm.
• Cách 2: Sử dụng nút lệnh Insert function (fx)
  • Đặt con tro chuột tại ô chứa kết quả của hàm.
  • Nháy chuột vào nút Function trên thanh công cụ (như hình 8.34).
  • Lựa chọn tên hàm trong danh sách các hàm được liệt kê hoặc chọn More Functions...
  • Nháy chuột vào các ô tham chicu trong danh sách tham sô (chú ý lựa chọn đầy đủ các tham số theo cấu trúc hàm).
  • Nháy chọn OK để hoàn thành việc nhập hàm.

Hình 8.34. Nhập hàm bằng nút chèn hàm Insert function

Nhìn chung Excel hổ trợ rất nhiều hàm tính toán tự động; dựa vào mục đích sừ dụng có thể chia thành các nhóm hàm lớn như nhóm hàm thông kê, nhóm hàm tìm kiếm, nhóm hàm điều kiện,... Phần tiếp theo sẽ trình bày chi tiết về từng nhóm hàm.

Nhóm hàm thống kê thường được sử dụng để thống kê các kết quả như tính tổng, đếm số lượng, tìm giá trị lớn nhất, nhỏ nhất,...

Tính tổng với hàm SUM

• Hàm SUM là hàm pho biến nhất trong tập hợp các hàm. Do đó, hàm SUM luôn được lựa chọn để trình bày chi tiết về cách sử dụng trong bất kì quyển sách nào vê Microsoft Excel.
• Hàm SUM được dùng để tính tổng giá trị trong một tập hợp các ô chứa giá trị số. Danh sách tham số của hàm SUM có thể là tập các ô liên tiếp nhau hoặc địa chí các ô rời nhau được phân cách nhau bởi dấu phẩy hoặc chấm phẩy Danh sách này cũng có thề là tổng hợp của danh sách liền nhau với các địa chi ô rời nhau. Ở ví dụ trên, neu cần tính tong lương cùa tất cả cán bộ có thể viết hàm một cách đơn giản là =SUM(H2:H7).
• Giống như tất cả các công thức Excel, khi có sự thay đôi giá trị của các ô tham chiếu trong công thức thì ô chứa kết quả cũng tự động được cập nhật thay đôi. Vì SUM là hàm pho biến nhất nên sau đây sẽ giới thiệu chi tiết về cách nhập hàm SUM, các hàm còn lại sẽ làm tương tự.

Hình 8.35. Hàm Sum trong danh sách AutoComplete

• Cách 1: Sử dụng nhập hàm trực tiếp
  • Bước 1: Nháy chuột vào ô chứa kết quả cùa hàm SUM.

  • Bước 2: Nhập dấu bằng “=" từ bàn phím.

  • Bước 3: Gõ SUM

  • Bước 4: Gõ dâu mở ngoặc đơn “(” nếu cần. Neu lựa chọn SUM từ danh sách AutoComplete thả xuống (như hình 8.35) và sau đó nhân phím Tab trên bàn phím thì dấu mở ngoặc đơn sẽ động thêm vào.

     

  • Bước 5: Gõ tham số đầu tiên. Nếu không nhớ hàm này có những tham số nào thì có thề xem gợi ý (ScreenTip) xuất hiện ngay dưới ô kết quà (như hình 8.36). Mỗi khi một tham số được đưa vào thì đồng thời vị trí đó được bôi đậm trong gợi ý. Điều này giúp kiêm tra cú pháp hàm một cách chính xác.
  • Bước 6: Trước khi tiếp tục nhập tham sô thứ hai, cần phải nhập dấu phẩy và sau đó nhập địa chì chứa giá trị tiếp theo hoặc nháy chuột vào ô chứa giá trị tiếp theo. Neu các tham số là một dãy liên tiếp thì có thể nháy chuột vào ô chứa giá trị đầu tiên và kéo chuột hết giá trị cuối cùng của dãy rồi nhả chuột. Khi dấu được nhập vào, tham số thứ hai trong danh sách tham sô ở ScreenTip sẽ được bôi đậm. Lưu ý là các tham số cùa hàm SUM từ tham số thứ hai trở đi được đặt trong cặp dấu ngoặc vuông []. Điều này có nghĩa tham số này là tuỳ chọn, có thể có hoặc không. Trong trường hợp này, hàm SUM có thế chỉ cần sừ dụng một miền địa chỉ duy nhất. Ncu tham số không được đặt trong cặp dấu ngoặc vuông [] thì nó băt buộc phái được điên.
  • Bước 7: Lặp lại bước 6 để lấy hết các tham số cần trong hàm SUM.
  • Bước 8: Gõ dấu đóng ngoặc đơn “)” khi đã hoàn thành danh sách tham số và nhẩn Enter. Tất cả các ô được lựa chọn đã được tính tổng và kết quả được hiển thị tại ô được chọn trong bước 1.

Hình 8.36. Gợi ý khi viết hàm Sum

Hình 8.37. Gợi ý khi viết hàm Sum (tiếp)

• Cách 2: Dùng hàm SUM hằng cách dùng công cụ viết hàm tự động (Insert Function)

Với hàm SUM là hàm đơn giản và chỉ có một tham số là danh sách các địa chi ô chứa giá trị cần tính tông thì việc dùng công cụ viết hàm tự động không có quá nhiều ý nghĩa. Tuy nhiên, với các hàm phức tạp, có nhiều tham số khác nhau thì việc sử dụng công cụ này sẽ giúp ích rất nhiều trong việc kiêm tra các kết quả trung gian cùng như giúp người sử dụng đờ phài nhớ danh sách các tham số.

• Các bước thực hiện nhập hàm bằng công cụ viết hàm tự động như sau:
  • Bước 1: Nháy chuột vào ô chứa kết quả của hàm SUM.
  • Bước 2: Nháy chuột vào nút Insert Function nằm trên thanh công thức Formula (vị trí được chi trong hình 8.39). Hộp thoại Insert Function được mở ra (như hình 8.40).
  • Bước 3: Đe tìm đúng hàm cần dùng, gõ tên (hoặc một phần tên) hàm trong ô Search For a function và nháy Go. Một cách khác, có thê lựa chọn trong các nhóm hàm trong ô Select a category. Để lựa chọn các hàm thường dùng có thể chọn Category là Most Recently Used và danh sách các hàm thường được lựa chọn sẽ hiển thị. Dù sử dụng cách nào thì hộp thoại Select a function cũng hiển thị như hình 8.41.
  • Bước 4: Lựa chọn một hàm trong danh sách các hàm ở ô Select a function. Một miêu tả về chức năng của hàm cũng như cú pháp hàm sẽ xuất hiện ở phía dưới. Ở đây lựa chọn hàm SUM.
  • Bước 5: Nháy chọn OK để lựa chọn hàm. Hộp thoại Function Arguments được mở ra như hình 8.42.
  • Bước 6: Nhập tham số cho hàm bằng các cách như nhập hàm trực tiếp. Lưu ý rằng các tham số bất buộc sẽ được bôi đậm, các tham số tuỳ chọn thì in thường.

Hình 8.39. Vị trí nút Insert Function trên thanh Formula

Hình 8.40. Hộp thoại Insert Function

Hình 8.41. Select a function

Hình 8.42. Hộp thoại Function Arguments của hàm SUM

Mỗi khi một tham số được nhập, Excel sẽ đưa ra giá trị của tham số hay dãy tham số đó và kết quà được hiền thị sau tham số cuối cùng. Ở cuối hộp thoại có hiển thị Formula Result là kết quả cuối cùng của hàm. Kết quả này và kết quả ở trên có thề khác nhau trong trường hợp hàm là một tổ hợp của nhiều hàm.

Hình 8.43. Hộp thoại Function Arguments của hàm SUM sau khi nhập tham số

• Bước 7: Tiếp tục nhập các tham số tiếp theo, lặp lại các bước này cho tới khi danh sách tham số được hoàn tất.
• Bước 8: Nháy chọn OK để hoàn thành việc nhập hàm và kết quả được hiển thị tại ô được chọn trong bước 1.

Hàm điều kiện thường dùng để hiển thị văn bản, hoặc giá trị, hoặc thực hiện một phép toán nào đó khi điều kiện kiểm tra đúng. Nếu điều kiện không đúng thì văn bản, hoặc giá trị, hoặc phép toán khác sè được thực hiện. Hàm điều kiện phổ dụng nhất là hàm 1F.

Hàm IF

Hàm LF có cú pháp: =IF (Biểu thức điều kiện, giá trị đúng, giá trị sai). Tham số Biểu thức điều kiện là một phép so sánh. Nếu kết quả của phép so sánh là đúng thì giá trị đúng (có thè là văn bản, giá trị hoặc một phép toán) được thực hiện. Nếu kết quả của phép so sánh là sai thì giá trị sai được thực hiện.

Các phép toán so sánh gồm:

• So sánh bằng “=” và so sánh không bang “<>”
• So sánh lớn hơn “>” và so sánh lớn hơn hoặc bằng “>=”
• So sánh nhỏ hơn “<” và so sánh nhỏ hơn hoặc bằng “<=”

Ví dụ, giáo viên muốn in danh sách sinh viên đồ hay trượt của lóp mình và quy định: Neu điểm thi >=5 thì Đỗ, còn lại thì Trượt. Già sử điềm thi được đặt ờ ô A3 thì lệnh 1F sẽ được viết như sau: =IF(A3>=5, “Đỗ”, “Trượt”).

Hàm IF lồng nhau

Trên thực tế, việc kiểm tra điều kiện có thể diễn ra ở nhiều mức. Giả sử, giáo viên cần xếp loại học tập cho học sinh trong lớp theo quy tắc: Nếu Điểm trung bình >=8 thì xếp loại Giỏi; Nếu 6.5<= Điểm trung bình <8 thì xểp loại Khá; Nếu 5<= Điểm trung bình <6.5 thì xếp loại Trung bình; Còn lại thì xếp loại Yếu. Với bài toán này thì cần sử dụng hàm IF lồng nhau. Việc thực hiện hàm IF lồng nhau cùng tương tự như hàm IF, nhưng tại phần giá trị sai sẽ là một hàm IF mới. Và cứ như thế cho đến khi hết tất cả các điều kiện.

Hình 8.44. Hàm IF lồng nhau

Hàm tính tổng có điểu kiện SUM IF và SUMIFS Mặc dù được Excel xếp vào nhóm hàm Math & Trig, nhưng hàm SUMIF lại có chức năng tương tự như hàm IF ở trên. Sử dụng hàm SUMIF đế tính tổng một nhóm các ô nếu điều kiện đặt ra là đúng. Cú pháp của hàm SUMIF như sau: =SUMIF(Miền điều kiện, Điều kiện, Miền tính tổng). Tham số Miền điều kiện là vùng dữ liệu chứa điều kiện mà đề bài yêu cầu; tham số Điểu kiện là ô chứa điều kiện hoặc điều kiện tự viết thì phải để trong cặp nháy kép tham số Miền tính tổng là vùng dữ liệu mà đề bài yêu cầu tính tổng.