Bạn đang xem bài viết Các Hàm Thường Dùng Để Lên Sổ Sách Trên Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Các hàm thường dùng để lên sổ sách trên Excel Các hàm thường dùng trong Excel để lên sổ sách, tính lương, làm bảng nhập xuất tồn, quản lý kho, lập các báo cáo kế toán trên Excel
Cú pháp: =VLOOKUP(giá trị dò, bảng dò, cột giá trị trả về, kiểu dò)
Hàm Vlookup là hàm trả về giá trị dò tìm theo cột đưa từ bảng tham chiếu lên bảng cơ sở dữ liệu theo đúng giá trị dò tìm. X=0 là dò tìm một cách chính xác. X=1 là dò tìm một cách tương đối.
Cú pháp: SUMIF(Vùng chứa điều kiện, Điều kiện, Vùng cần tính tổng).
– Hàm này trả về giá trị tính tổng của các ô trong vùng cần tính thoả mãn một điều kiện đưa vào
– Tính tổng của các giá trị trong vùng từ B3 đến B8 với điều kiện là các giá trị nhỏ hơn hoặc bằng 8.
Cú pháp: SUBTOTAL(function_num,ref1,ref2,…) – Hàm Subtotal là hàm tính toán cho một nhóm con trong một danh sách hoặc bảng dữ liệu tuỳ theo phép tính mà bạn chọn lựa trong đối số thứ nhất.
6. Hàm MID
Cú pháp: If(Điều kiện, Giá trị 1, Giá trị 2).
Cú pháp: SUM(Number1, Number2…)
– Hàm Sum là hàm cộng tất cả các số trong một vùng dữ liệu được chọn.
Cú pháp: MAX(Number1, Number2…)
– Hàm MAX là hàm trả về số lớn nhất trong dãy được nhập.
10. Hàm MIN:
Cú pháp: MIN(Number1, Number2…)
– Hàm MIN là hàm trả về số nhỏ nhất trong dãy được nhập vào.
Cú pháp: AND(đối 1, đối 2,..).
– Các đối số: Logical1, Logical2… là các biểu thức điều kiện.
– Hàm này là Phép VÀ, chỉ đúng khi tất cả các đối số có giá trị đúng. Các đối số là các hằng, biểu thức logic. Hàm trả về giá trị TRUE (1) nếu tất cả các đối số của nó là đúng, trả về giá trị FALSE (0) nếu một hay nhiều đối số của nó là sai.
– Các đối số phải là giá trị logic hoặc mảng hay tham chiếu có chứa giá trị logic. – Nếu đối số tham chiếu là giá trị text hoặc Null (rỗng) thì những giá trị đó bị bỏ qua. – Nếu vùng tham chiếu không chứa giá trị logic thì hàm trả về lỗi #VALUE!
Cú pháp: OR(đối 1, đối 2,..).
– Các đối số: Logical1, Logical2… là các biểu thức điều kiện.
– Hàm này là Phép HOẶC, chỉ sai khi tất cả các đối số có giá trị sai. Hàm trả về giá trị TRUE (1) nếu bất cứ một đối số nào của nó là đúng, trả về giá trị FALSE (0) nếu tất cả các đối số của nó là sai.
Các Hàm Thống Kê Thường Dùng Trong Excel
Trong học tập hoặc công việc thì chúng ta luôn sử dụng đến các hàm thống kê sử dụng cho những mục đích khác nhau. Hôm nay tôi sẽ chia sẻ tới các bạn các hàm thống kê thường dùng trong Excel để các bạn tham khảo.
Hàm trả về giá trị trung bình cộng của một dãy số.
Cú pháp: AVERAGE(Number 1, [Number 2], [[Number 3],….)
Trong đó:
Cú pháp: =AVERAGE(number1, [number2], …)
Các tham số:
Number1: Số thứ nhất mà bạn muốn tính trung bình (đối số Number1 bắt buộc phải có).
Number2: Các đối số tùy chọn không bắt buộc nằm trong phạm vi từ 2 đến 255 mà bạn muốn tính trung bình.
Ví dụ mình họa
Tính điểm trung bình của các học sinh được cho trong bảng sau:
Được sử dụng để đếm số ô chứa các số hoặc đếm các số bên trong một danh sách
Cú pháp: = COUNT (Value1, [Value2],….)
Trong đó:
Value chứa giá trị, danh sách giá trị hoặc địa chỉ.
Ví dụ minh họa
Cho bảng tiền công của công nhân như hình dưới, đếm số ô chứa số trong cột Thâm niên.
Đếm số ô rỗng trong vùng chọn.
Cú pháp: =COUNTBLANK (range)
Các tham số: Range là phạm vi mà từ đó bạn muốn đếm các ô trống.
Ví dụ minh họa
Với cột năm sinh số ô rỗng = COUNTBLANK(C5:C11)
Cột hiện tại số ô rỗng =COUNTBLANK(D5:D11)
Cột quê quán số ô rỗng =COUNTBLANK(E5:E11)
Hàm trả về giá trị lớn nhất
Cú pháp: = MAX (Number 1, Number 2, …)
Trong đó: Number1, Number2… là các tham số đầu vào có thể là số, tên, dãy các ô hay giá trị tham chiếu. Tối đa 256 tham số đầu vào.
Ví dụ minh họa
Tính doanh số Tháng và Quý bán cao nhất cho bởi bảng sau:
Công thức được áp dụng cho bảng Doanh số bán hàng Quý I để tìm ra doanh số Tháng bán cao nhất của Quý như sau: = MAX(C5:E10)
Tương tự áp dụng hàm Max để tìm ra doanh số Quý bán cao nhất: =MAX(F5:F10)
Tính tổng các số trong một phạm vi (một nhóm các ô).
Cú pháp: = SUM (Number1, [Number2],…)
Trong đó:
Number1: Số đầu tiên bạn muốn thêm vào
Number2-225:Đây là số thứ hai bạn muốn cộng (có thể chỉ định tối đa 255 số).
Ví dụ minh họa
Tính tổng tiền đơn giá sản phẩm cho bởi bảng sau:
Tiếp tục áp dụng công thức hàm SUM với tất cả sản phẩm còn lại.
Tính tổng các giá trị trong một phạm vi đáp ứng điều kiện mà bạn xác định.
Cú pháp: =SUMIF (Range, Criteria, [Sum_range])
Trong đó:
Range: Vùng chứa điều kiện.
Criteria: Điều kiện.
Sum_range: Vùng tính tổng dựa trên điều kiện.
Ví dụ minh họa
Tổng số lương của nhân viên trong phòng truyền thông được cho bởi bảng tính sau:
Cách Tạo Danh Sách Sổ Xuống (Drop List) Trên Excel 2022
Danh sách sổ xuống xuống hạn chế số mục cho một trường đầu vào, tránh lỗi chính tả. Excel cung cấp các tính năng tạo danh sách thả xuống linh hoạt, do đó nó thích hợp cho các biểu mẫu hoặc thu thập dữ liệu, giúp người đọc dễ dàng chọn các mục trong danh sách sổ xuống.
Sử dụng danh sách sổ xuống trong một ô khi có các tùy chọn cụ thể cho ô đó. Ví dụ, bạn có thể tạo danh sách sổ xuống có chứa các tùy chọn như Nam và Nữ, Có và Không, v.v… Bài viết này sẽ hướng dẫn các bạn cách tạo danh sách sổ xuống trong bảng tính Excel 2016.
Đây là các bước để tạo danh sách sổ xuống trong Excel:
Bây giờ, hãy đi vào chi tiết từng bước để tạo một danh sách sổ xuống trong Excel.
Cách để thêm một danh sách các mục vào một danh sách sổ xuống sử dụng Data Validation là tạo một danh sách trong bảng tính và đặt tên cho dải ô chứa danh sách đó. Danh sách này có thể ở cùng trong một trang bảng tính hoặc khác trang bảng tính với danh sách sổ xuống. Việc đặt tiên cho dải ô trong danh sách sổ xuống sẽ dễ cho người dùng theo dõi.
2. Kiểm soát dữ liệu nhập với Data Validation
3. Thêm vùng dữ liệu đã được đặt tên vào Data Validation
Trong tab Settings trong hộp thoại Data Validation, chọn List từ danh sách sổ xuống Allow. Chúng ta sẽ sử dụng tên của dải ô đã đặt tên ở bước trước, gõ dòng sau vào ô Source.
=Food
Thay thế “Food” bằng tên dữ liệu của bạn, sau đó nhấp OK.
Ignore blank được chọn theo mặc định, nó cho phép chọn ô và sau đó bỏ chọn ô mà không cần chọn một mục. Nếu muốn chọn một tùy chọn trong danh sách sổ xuống, bỏ chọn Ignore blank.
4. Thiết lập thông báo nhập dữ liệu cho Data Validation
Nếu muốn tạo một thông báo hiển thị khi chọn một ô trong danh sách sổ xuống, nhấp vào tab Input Message, chọn hộp Show input message when the cell is selected và điền vào hộp Title và Input message. Ngoài ra, bạn cũng có thể sử dụng tab Error Alert để thêm một thông báo hiển thị khi nhập không hợp lệ trong danh sách sổ xuống (ví dụ, nếu ai đó gõ vào ô thay vì chọn tùy chọn). Chọn Show Error alert after invalid data is entered, sau đó chọn Style và điền vào hộp Title và Error message, nhấn OK.
Mũi tên chỉ xuất hiện khi chọn ô chứa danh sách sổ xuống và nằm bên phải ô. Nếu danh sách sổ xuống chứa nhiều hơn tám mục, bạn sẽ thấy thanh cuộn trong danh sách sổ xuống khi nhấp vào mũi tên.
Ngoài ra, bạn cũng có thể thay tên bằng cách chọn nó trong danh sách, nhấp vào Edit, thay đổi tên trong hộp thoại Edit Name và nhấp OK hoặc thay đổi dải ô trên hộp thoại Edit Name. Để xóa tên, chọn Name trong danh sách và nhấp Delete.
Tạo một danh sách sổ xuống phụ thuộc
Danh sách sổ xuống phụ thuộc là danh sách có các lựa chọn thay đổi theo tùy chọn trong danh sách sổ xuống chính. Ví dụ, khi chọn Pizza trong danh sách sổ xuống trên, danh sách sổ xuống phụ thuộc thứ hai sẽ chứa các loại bánh pizza. Nếu chọn Chinese, các tùy chọn trong danh sách sổ xuống phụ thuộc sẽ chứa các loại món ăn Trung Quốc khác nhau.
Trước khi bắt đầu, hãy quay lại danh sách sổ xuống đã tạo trước đó và tạo danh sách sổ xuống chính Favorit Food nếu chưa có. Bây giờ chúng ta sẽ tạo thêm ba danh sách nữa và đặt tên cho chúng. Nhập danh sách cho mỗi tuỳ chọn trong danh sách sổ xuống chính, sau đó đặt tên cho danh sách đó trong Name Box và nhấn Enter. Thực hiện tương tự cho các danh sách còn lại.
Trước khi tạo danh sách sổ xuống phụ thuộc, chọn một mục trong danh sách sổ xuống chính. Sau đó, chọn ô muốn thêm danh sách sổ xuống phụ thuộc.
=INDIRECT($B$2)
Hàm INDIRECT trả về tham chiếu được chỉ định một chuỗi văn bản, trong trường hợp này là văn bản từ tùy chọn được chọn trong danh sách sổ xuống chính trong ô B2. Ví dụ, nếu chọn Chinese từ danh sách sổ xuống chính, =INDIRECT($B$2) sẽ trả về tham chiếu Chinese. Do đó, danh sách sổ xuống thứ hai chứa các mục Chinese. Sau đó, nhấn OK.
Để sao chép danh sách sổ xuống với kiểm soát dữ liệu đến các ô khác, nhấn tổ hợp phím Ctrl + C và Ctrl + V vào ô muốn sao chép để sao chép danh sách mà vẫn giữ định dạng.
Lưu ý: Bạn phải thật cẩn thận khi làm việc với danh sách sổ xuống trong Excel. Khi sao chép một ô không chứa danh sách sổ xuống sang một ô có chứa danh sách sổ xuống, danh sách này sẽ bị mất. Excel không hiện thông báo về thao tác, tuy nhiên bạn vẫn có thể hoàn tác bằng cách nhấn tổ hợp phím Ctrl + Z.
Trên hộp thoại Go To Special, chọn Data Validation. Tùy chọn All bên dưới Data Validation sẽ chọn tất cả các ô được áp dụng quy tắc kiểm soát dữ liệu. Tùy chọn Same chỉ chọn các ô trong sách sách sổ xuống sử dụng cùng loại quy tắc kiểm soát dữ liệu như ô được chọn.
Chọn tùy chọn phù hợp với dữ liệu của bạn, ở đây chọn All vì danh sách sổ xuống sử dụng các quy tắc khác nhau, sau đó nhấn OK.
Hai danh sách thả xuống được chọn.
Bây giờ bạn có thể định dạng các ô này để phân biệt chúng với các ô khác.
Cách làm mũi tên trong danh sách sổ xuống luôn xuất hiện
Nút mũi tên trên một danh sách sổ xuống sẽ biến mất khi không chọn ô. Chúng ta sẽ giải quyết vấn đề này bằng cách thêm nút mũi tên xuống vĩnh viễn bên phải danh sách sổ xuống.
Để tạo mũi tên luôn xuất hiện trong danh sách sổ xuống, chụp ảnh màn hình có chứa mũi tên trong danh sách, sau đó lấy hình mũi tên, chèn hình ảnh đó vào ô bên phải danh sách sổ xuống, do đó bạn sẽ thấy nút mũi tên ngay cả khi không chọn danh sách sổ xuống. Khi chọn danh sách sổ xuống, nút mũi tên sẽ hiển thị trên hình ảnh chèn vào.
Để bắt đầu, tải tệp nút mũi tên chúng tôi (nhấp chuột phải vào liên kết đó và chọn Save link as). Sau đó, chọn ô bên phải danh sách sổ xuống và đi đến tab Insert.
Nhấp vào Illustrations và chọn Picture.
Trên hộp thoại Insert Picture, điều hướng đến vị trí lưu tệp tin vừa tải ở trên và chọn tệp, sau đó nhấp vào Insert.
Hình ảnh được chèn vào bên trái của ô.
Danh sách sổ xuống đã được xóa và ô sẽ khôi phục về định dạng mặc định. Nếu có một tùy chọn được lựa chọn khi xóa danh sách sổ xuống, thì ô đó sẽ được nhập với giá trị của tùy chọn đó.
Nếu không muốn lưu bất kỳ giá trị nào khi xóa danh sách sổ xuống, bạn có thể sao chép ô trống và dán nó vào ô có chứa danh sách sổ xuống, danh sách này sẽ được xóa.
Những Hàm Nào Thường Sử Dụng Để Lập Bảng Lương Trên Excel?
Lấy thông tin tính lương bằng hàm tham chiếu: VLOOKUP hoặc INDEX+MATCH
Trên bảng lương thì yếu tố đầu tiên và dễ dàng xác định nhất là thông tin về nhân viên: Mã nhân viên hoặc họ tên. Tuy nhiên các thông tin khác thì lại không hiển thị một cách trực tiếp được trên bảng lương. Nó thường hiển thị trên các bảng khác: Bảng chấm công, Danh sách nhân viên… Để có thể tham chiếu các thông tin này phục vụ cho việc tính lương thì chúng ta dùng hàm tham chiếu như vlookup hoặc tốt hơn là dùng hàm Index kết hợp hàm Match
Ví dụ:
Tra cứu thông tin nhân viên từ bảng danh sách nhân viên dựa theo mã nhân viên bằng hàm Vlookup: Các thông tin như Họ tên, Bộ phận, Lương cơ bản đều nằm trong sheet DSNV
Xác định số công thời gian từ bảng chấm công với hàm SUMIFS, SUMPRODUCT
Có bao giờ bạn thắc mắc hàm nào giúp xác định số công thời gian từ bảng chấm công tốt nhất? Có rất nhiều cách để làm việc này: Sử dụng hàm dò tìm, tham chiếu như VLOOKUP, sử dụng hàm tính tổng theo điều kiện như SUMIF… Tuy nhiên cách làm tối ưu nhất là dùng hàm SUMIFS. Bởi vì:
Mỗi nhân viên lại có số công khác nhau, cho từng loại công khác nhau. Do đó thêm điều kiện về Mã Nhân viên
Mỗi nhân viên có thể chấm công nhiều lần trên 1 bảng chấm công. Đây không phải là trùng nội dung, mà có thể trong quá trình chấm có thiếu sót cần bổ sung thêm, hoặc trong cùng 1 tháng nhân viên đó làm việc ở nhiều bộ phận, nhiều dự án khác nhau thì quy định chấm công ở các bộ phận cũng khác nhau. Do đó để tổng hợp đúng số công thì không thể dùng hàm tham chiếu mà phải tính tổng số công.
Thỏa mãn cả 3 điều kiện trên thì chúng ta cần dùng tới hàm SUMIFS hoặc SUMPRODUCT. Tuy nhiên hàm SUMIFS đễ dùng hơn, tính toán cũng gọn hơn nên thường được ưu tiên sử dụng hơn so với SUMPRODUCT
Ví dụ:
Việc quy định các khoản phụ cấp trong doanh nghiệp thường khác nhau. Do đó để tùy biến cách tính trong từng trường hợp phải vận dụng logic một cách khéo léo. 3 hàm IF, AND và OR là 3 hàm thường dùng nhất trong nhóm hàm logic, dùng để biểu diễn từ logic thực tế thành logic mà Excel có thể tính toán được.
Để hiểu hơn về cách dùng 3 hàm này, các bạn có thể tham khảo các bài viết:
Cập nhật thông tin chi tiết về Các Hàm Thường Dùng Để Lên Sổ Sách Trên Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!