Giới thiệu công thức và hàm trong excel 2010

25/02/2016 Nguyễn Hồng Vân

Cũng giống như excel 2007, excel cũng có các công thức và hàm.Bài viết sau đây sẽ giới thiệu về công thức và hàm excel 2010

1. Giới thiệu công thức (Formula)

Công thức giúp bảng tính hữu ích hơn rất nhiều, nếu không có các công thức thì bảng tính cũng giống như trình soạn thảo văn bản. Chúng ta dùng công thức để tính toán từ các dữ liệu lưu trữ trên bảng tính, khi dữ liệu thay đổi các công thức này sẽ tự động cập nhật các thay đổi và tính ra kết quả mới giúp chúng ta đỡ tốn công sức tính lại nhiều lần. Vậy công thức có các thành phần gì?

Công thức trong Excel được nhận dạng là do nó bắt đầu là dấu = và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm.

Ví dụ:

Ví dụ về công thức

Các toán tử trong công thức

Toán tử

Chức năng

Ví dụ

Kết quả

+

Cộng

=3+3

3 cộng 3 là 6

-

Trừ

=45-4

45 trừ 4 còn 41

*

Nhân

=150*.05

150 nhân 0.50 thành 7.5

/

Chia

=3/3

3 chia 3 là 1

^

Lũy thừa

=2^4 =16^(1/4)

2 lũy thừa 4 thành 16 Lấy căn bậc 4 của 16 thành 2

&

Nối chuỗi

=”Lê” & “Thanh”

Nối chuỗi “Lê” và “Thanh” lại thành “Lê Thanh”

=

Bằng

=A1=B1

Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE

>

Lớn hơn

=A1>B1

Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE

<

Nhỏ hơn

=A1<B1

Ví dụ ô A1=3, ô B1=6 Kết quả: TRUE

>=

Lớn hơn hoặc bằng

=A1>=B1

Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE

<=

Nhỏ hơn hoặc bằng

=A1<=B1

Ví dụ ô A1=3, ô B1=6 Kết quả: TRUE

<>

Khác

=A1<>B1

Ví dụ ô A1=3, ô B1=6 Kết quả: TRUE

,

Dấu cách các tham chiếu

=Sum(A1,B1)

Ví dụ ô A1=3, ô B1=6 Kết quả: 9

:

Tham chiếu mãng

=Sum(A1:B1)

Ví dụ ô A1=3, ô B1=6 Kết quả: 9

khoảng trắng

Trả về các ô giao giữa 2 vùng

=B1:B6 A3:D3

Trả về giá trị của ô

Thứ tự ưu tiên của các toán tử

Toán tử

Mô tả

Ưu tiên

: (hai chấm) (1 khoảng trắng) , (dấu phẩy)

Toán tử tham chiếu

1

Số âm (ví dụ –1)

2

%

Phần trăm

3

^

Lũy thừa

4

* và /

Nhân và chia

5

+ và –

Cộng và trừ

6

&

Nối chuỗi

7

= < > <= >= <>

So sánh

8

2. Giới thiệu hàm (Function)

Hàm trong Excel được lập trình sẵn dùng tính toán hoặc thực hiện một chức năng nào đó. Việc sử dụng thành thạo các hàm sẽ giúp chúng ta tiết kiệm được rất nhiều thời gian so với tính toán thủ công không dùng hàm. Các hàm trong Excel rất đa dạng bao trùm nhiều lĩnh vực, có những hàm không yêu cầu đối số, có những hàm yêu cầu một hoặc nhiều đối số, và các đối số có thể là bắt buộc hoặc tự chọn.

Ví dụ:

=Rand(): hàm không có đối số

=If(A1>=5,”Đạt”,”Rớt”): hàm 3 đối số

=PMT(10%,4,1000,,1): hàm nhiều đối số và đối số tùy chọn

Trong Excel 2010 có các hàm chính như sau:

Hàm ngoại: Call, Registed.ID,…

  • Hàm lấy dữ liệu từ SSAS: Cubeset, Cubevalue,…
  • Hàm dữ liệu: Dmin, Dmax, Dcount,…
  • Hàm ngày và thời gian: Time, Now, Date,….
  • Hàm kỹ thuật: Dec2Bin, Dec2Hex, Dec2Oct,…
  • Hàm tài chính: Npv, Pv, Fv, Rate,…
  • Hàm thông tin: Cell, Thông tin, IsNa,…
  • Hàm luận lý: If, And, Or,…
  • Hàm tham chiếu và tìm kiếm: Choose, Vlookup, OffSet,…
  • Hàm toán và lượng giác: Log, Mmult, Round,…
  • Hàm thống kê: Stdev, Var, CountIf,…
  • Hàm văn bản: Asc, Find, Text,…

Chúng ta sẽ cùng nghiên cứu cách sử dụng từng hàm trong các nhóm hàm trên ở các phần sau

3. Nhập công thức và hàm 

Nhập công thức trong Excel rất đơn giản, muốn nhập công thức vào ô nào bạn chỉ việc nhập dấu = và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm. Bạn có thể nhìn vào thanh Formula để thấy được trọn công thức. Một điều hết sức lưu ý khi làm việc trên bảng tính là tránh nhập trực tiếp các con số, giá trị vào công thức mà bạn nên dùng đến tham chiếu.

Ví dụ:

Minh họa dùng tham chiếu trong hàm

Trong ví dụ trên, ở đối số thứ nhất của hàm NPV chúng ta không nhập trực suất chiết tính 10% vào hàm mà nên tham chiếu đến địa chỉ ô chứa nó là I2, vì nếu lãi suất có thay đổi thì ta chỉ cần nhập giá trị mới vào ô I2 thì chúng ta sẽ thu được kết quả NPV mới ngay không cần phải chỉnh sửa lại công thức.

Giả sử các ô C2:G2 được đặt tên là DongTien, và ô I2 đặt tên là LaiSuat (Xem lại cách đặt tên vùng ở bài số 1) thì trong quá trình nhập công thức bạn có thể làm như sau:

B1. Tại ô B4 nhập vào =NPV(

B2. Nhấn F3, cửa sổ Paste Name hiện ra

B3. Chọn LaiSuat và nhấn OK

B4. Nhập dấu phẩy (,) và gõ F3

B5. Chọn DongTien và nhấn OK

B6. Nhập dấu đóng ngoặc rồi nhập dấu +

B7. Nhấp chuột vào ô B2

B8. Nhấn phím Enter

Chèn tên vùng vào công thức

Một trong những cách dễ dàng nhất để sử dụng hàm trong Excel là sử dụng thư viện hàm. Khi bạn muốn sử dụng hàm nào chỉ việc vào thanh Ribbon -> chọn nhóm Formulas -> Function Library -> chọn nhóm hàm -> chọn hàm cần sử dụng. Ngoài ra bạn có thể nhấn vào nút  để gọi hộp thoại Insert Function một cách nhanh chóng và khi cần tìm hiểu về hàm này bạn chỉ cần nhấn vào Help on this function.

Hộp thoại Insert Function

4. Tham chiếu trong công thức 

Các tham chiếu sử dụng trong công thức giúp cho chúng ta khỏi tốn công sửa chữa các công thức khi các giá trị tính toán có sự thay đổi. Có 3 loại tham chiếu sau:

  • Tham chiếu địa chỉ tương đối: Các dòng và cột tham chiếu sẽ thay đổi khi chúng ta sao chép hoặc di dời công thức đến vị trí khác một lượng tương ứng với số dòng và số cột mà ta di dời. Ví dụ A5:B7, C4
  • Tham chiếu địa chỉ tuyệt đối: Các dòng và cột tham chiếu không thay đổi khi ta di dời hay sao chép công thức. Ví dụ $A$5:$B$7, $C$4
  • Tham chiếu hỗn hợp: Phối hợp tham chiếu địa chỉ tương đối và tuyệt đối. Ví dụ A$5 nghĩa là cột A tương đối và dòng 5 tuyệt đối.

Lưu ý: Dấu $ trước thứ tự cột là cố định cột và trước thứ tự dòng là cố định dòng. Nhấn phím F4 nhiều lần để (tuyệt đối) cố định/ bỏ cố định  dòng hoặc cột.

Nhấn phím F4 nhiều lần để (tuyệt đối) cố định/ bỏ cố định dòng hoặc cột.

Ví dụ: Tính thành tiền bằng Số lượng nhân Giá. Đổi sang giá trị Thành tiền sang VND. Tính tổng các cột Thành tiền và cột VND.

Minh họa địa chỉ tương đối và tuyệt đối

B1. Tại ô D2 nhập vào =B2*C2Enter. Sau đó quét chọn cả vùng D2:D14 và gõ <Ctrl+D>. Vào các ô D3, D4... D14 ta thấy công thức các dòng tự động được thay đổi tương ứng với khoảng cách so với ô D2. Trường hợp này chúng ta dùng địa chỉ tương đối của B2*C2 là vì chúng ta muốn khi sao chép công thức xuống phía dưới thì địa chỉ các ô tính toán sẽ tự động thay đổi theo.

B2. Tại ô E2 nhập vào =D2*B$17Enter, sau đó chép công thức xuống các ô E3:E14. Chúng ta cần cố định dòng 17 trong địa chỉ tỷ giá B17 vì ta muốn khi sao công thức xuống thì các công thức sao chép vẫn tham chiếu đến ô B17 để tính toán.

B3. Tại ô D15 nhập vào =Sum(D2:D14) và chép sang ô E15.

Lưu ý:

  • Tham chiếu đến địa chỉ ở worksheet khác nhưng cùng workbook thì có dạng
  • Tên_sheet!Địa_chỉ_ô. Ví dụ:

=A2*Sheet2!A2

=A2*’Thong so’!B4

Khi tên sheet có chứa khoảng trắng thì để trong cặp nháy đơn ‘ ’

  • Tham chiếu đến địa chỉ trong workbook khác thì có dạng

[Tên_Workbook]Tên_sheet!Địa_chỉ_ô.

Ví dụ:

=A2*[Bai2.xlsx]Sheet3!A4

=A2*’[Bai tap 2.xlsx]Sheet3’!A4             

Khi tên Sheet hay Workbook có chứa khoản trắng để trong cặp nháy đơn ‘ ’

=A2*’C:\Tai lieu\[Bai tap 2.xlsx]Sheet3’!A4

Khi tham chiếu đến workbook khác mà workbook này không mở

=A2*’\\DataServer\Excel\[Bai tap 2.xlsx]Sheet3’!A4

Khi tham chiếu đến tài nguyên chia sẽ trên máy chủ trong mạng

5. Các lỗi thông dụng (Formulas errors) 

Các lỗi thông dụng

Lỗi

Giải thích

#DIV/0!

Trong công thức có chứa phép chia cho 0 (zero) hoặc chia ô rỗng

#NAME?

Do dánh sai tên hàm hay tham chiếu hoặc đánh thiếu dấu nháy

#N/A

Công thức tham chiếu đến ô mà có dùng hàm NA để kiểm tra sự tồn tại của dữ liệu hoặc hàm không có kết quả

#NULL!

Hàm sử dụng dữ liệu giao nhau của 2 vùng mà 2 vùng này không có phần chung nên phần giao rỗng

#NUM!

Vấn đề đối với giá trị, ví dụ như dùng nhầm số âm trong khi đúng phải là số dương

#REF!

Tham chiếu bị lỗi, thường là do ô tham chiếu trong hàm bị xóa

#VALUE!

Công thức tính toán có chứa kiểu dữ liệu không đúng.

 

Tag: Microsoft excelexcel 2010. office

Đang phát triển ...

Bài viết liên quan

Phím Tắt Và Thủ Thuật trong excel 2010 - phần 1

 Bài viết sau đây chúng tôi xin giới thiệu tới các bạn các phím tắt thông dụng và các thủ thuật để sử dụng excel 2010 được tốt hơn - phần 1

Phím Tắt Và Thủ Thuật excel 2010 - phần 2

 Bài viết sau đây chúng tôi xin giới thiệu tới các bạn các phím tắt thông dụng và các thủ thuật để sử dụng excel 2010 được tốt hơn - phần 2

Định Dạng Trang Và In Bảng Tính trong excel 2010

Bài viết giới thiệu đến các bạn phương pháp định dạng trang và cách in bảng tính trong excel 2010. 

Đồ Thị Trong Excel

Bài viết giới thiệu tới các bạn cách tạo đồ thị trong excel 2010. 

Khai Thác Cơ Sở Dữ Liệu excel 2010 - phần 2

Bài viết giới thiệu đến các bạn phương pháp khai thác cơ sở dữ liệu trong excel 2010 ( phần 2 ).

Khai Thác Cơ Sở Dữ Liệu excel 2010 - phần 1

Bài viết giới thiệu đến các bạn phương pháp khai thác cơ sở dữ liệu trong excel 2010 ( phần 1 ).

Hàm xử lý văn bản và dữ liệu excel 2010

Bài viết giới thiệu về các hàm xử lý văn bản và dữ liệu trong excel 2010. Chúng ta hãy cùng nhau tìm hiểu để công việc được dễ dàng hơn.

Hàm toán học và lượng giác trong excel 2010

Bài viết giới thiệu về các hàm toán học và hàm lượng giác trong excel 2010. Chúng ta hãy cùng tìm hiểu xem nó có khác gì với excel 2007

Danh mục các Hàm Quản lý Cơ sở dữ liệu và Danh sách

Bài viết giới thiệu với các bạn về danh mục các hàm quản lý cơ sở dữ liệu và danh sách trong microsoft office excel 2010

Các hàm trong excel 2010

Bài viết giới thiệu về các hàm trong excel 2010 với các hàm cụ thể: hàm thống kê, hàm phân phối xác suất, hàm tương quan và hồi quy tuyến tính...

Làm việc với dữ liệu trong excel 2010

Bài viết giới thiệu về các dữ liệu trong excel với nội dung: nhập liệu, hiệu chỉnh, định dạng, tìm, thay thế, sắp xếp và lọc dữ liệu.

Bài 1: Làm Quen Với Microsoft Excel 2010 ( phần 1 )

Bài viết giúp các bạn tìm hiểu về cách làm quen cơ bản với microsoft excel 2010.Tuy không khác excel 2007 nhưng chúng ta hãy cùng lướt qua

Lỗi nguy hiểm của Apache version 2.4.17 đến 2.4.38

Lỗi nguy hiểm của Apache version 2.4.17 đến 2.4.38

Chúng tôi nhận được cảnh báo bảo mật nguy hiểm đối với các bản Apache version 2.4( từ 2.4.17 đến 2.4.38) đây là lỗi cực kỳ nguy hiểm cho phép kẻ tấn cống có khả năng chiếm quyền root(đặc biệt là các máy chủ sử dụng cho dịch vụ Web Hosting).

Kỹ thuật tìm kiếm và thay thế từ cơ bản đến nâng cao trong MS Office (Word)

Kỹ thuật tìm kiếm và thay thế từ cơ bản đến nâng cao trong MS Office (Word)

Trong Word nói riêng, Office nói chung, có lẽ ai cũng biết chức năng tìm kiếm và thay thế nội dung văn bản, nhưng ngoài ra, còn nhiểu chức năng khác trong hộp thoại tìm kiếm và thay thế mà không phải ai cũng biết để sử dụng. Trong đó có tìm kiếm và thay thế định dạng, sử dụng các ký hiệu đại diện, các ký tự đặc biệt… 

Bài 6: Thủ thuật outlook 2010

Bài 6: Thủ thuật outlook 2010

Bài viết giới thiệu đến các bạn một số thủ thuật được dùng trong outlook 2010, để giúp công việc của các bạn được dễ dàng hơn.

Bài 5: Quản lý các thông tin trong  outlook 2010

Bài 5: Quản lý các thông tin trong outlook 2010

Bài viết giới thiệu về phương pháp quản lý thông tinn trong outlook 2010. Chúng ta hãy cùng tìm hiểu xem có điểm gì khác và nổi bật so với outlook 2007

Bài 4: Sử dụng lịch làm việc một cách hiệu quả outlook 2010

Bài 4: Sử dụng lịch làm việc một cách hiệu quả outlook 2010

Để công việc của đạt hiệu quả cao hơn, chúng ta hãy cùng nhau tìm hiểu cách sử dụng lịch làm việc một cách hiệu quả hơn trong outlook 2010

Bài 3: Quản lý hộp thư của bạn về dung lượng và lưu trữ outlook 2010

Bài 3: Quản lý hộp thư của bạn về dung lượng và lưu trữ outlook 2010

Bài viết sau đây sẽ giới thiệu về mục quản lý hộp thư của bạn về dung lượng và lưu trữ trong microsoft outlook 2010.

Hàm SLEEP trong PHP

Hàm SLEEP trong PHP

Có những lúc trong công việc lập trình ta cần mã tạm dừng theo một thời gian định trước thì trong php có hỗ trợ hàm sleep để các bạn làm điều này.

Hàm Rand trong PHP

Hàm Rand trong PHP

Đôi lúc chúng ta lập trình cần đến những con số sinh ngẫu nhiên vào những công việc khách nhau hôm này mình xin giới thiệu với mọi người hàm sinh số ngẫu nhiên trong PHP

Bài 9: Giới thiệu lập trình hướng đối tượng trong PHP (OOP)

Bài 9: Giới thiệu lập trình hướng đối tượng trong PHP (OOP)

Các bạn tự học PHP nên biết về lập trình hướng đối tượng trong PHP, kỹ thuật lập trình hỗ trợ công nghệ đối tượng

Bài 8: Hàm có sẵn thông dụng trong PHP

Bài 8: Hàm có sẵn thông dụng trong PHP

Hàm thông dụng trong php cần biết, các bạn mới bắt đầu lên nắm rõ ý nghĩa cấu trúc cách dùng những hàm này để bổ trợ kiến thức sau này

Bài 7: Hàm trong PHP

Bài 7: Hàm trong PHP

Hàm do người sử dụng định nghĩa cho phép bạn xử lý những tác vụ thường lặp đi lặp lại trong ứng dụng. cách khai báo, cú pháp của hàm trong php