Tài nguyên
Quà tặng từ Smod Ptm0412 - Tài liệu về PowerPivot nhân sinh nhật GPE13
Tài liệu về Pivot Table, PowerPivot, Power Query (được chia sẻ miễn phí bởi smod ptm0412)
Thân gởi mọi người,
Trong buổi tiệc sinh nhật GPE13 tại TPHCM, anh Mỹ Smod @ptm0412 đã có chia sẻ về những điểm độc đáo trong PowerPivot (và PowerQuery).
Cùng với đó là tài liệu liên quan đến PowerPivot đính kèm. Tài liệu này là món quà sinh nhật quý giá.
Chân thành cảm ơn anh đã bỏ công sức vì mọi người.
View attachment 221135
Đại diện BQT Giaiphapexcel
OverAC
p/s: cùng với lời hứa trong buổi tiệc, OverAC tôi sẽ cố gắng sắp xếp một buổi cafe để cùng chia sẻ kiến thức này đến những người quan tâm,
Hàm mảng mới trong Excel 365: Hàm Unique, Sort, SortBy, RandArray, Sequence, Filter
Microsoft 365 (tên mới của Office 365) có một số hàm mới dùng cho Excel 365 chuyên trị mảng. Tương tự hàm Query của Google Sheets, các hàm mảng mới này chỉ cần gõ công thức vào 1 ô và ta sẽ có kết quả là một mảng. Mảng này có thể là mảng 1 dòng nhiều cột, 1 cột nhiều dòng, thậm chí một ma trận dòng cột các giá trị.
Các hàm mảng đó là:
- Hàm Unique
- Hàm Sort
- Hàm SortBy
- Hàm RandArray
- Hàm Sequence
- Hàm Filter
Dưới đây xin giới thiệu lần lượt các hàm trên, nội dung do tôi nghiên cứu trên mạng (các tài liệu tiếng Anh), thực hành và viết lại
I. Hàm UNIQUE
Hàm Unique dùng để lấy danh sách duy nhất từ 1 danh sách nguồn có dữ liệu trùng. Khác với những hàm và công thức trước đây, hàm Unique trả về 1 mảng các giá trị mà không cần Ctrl Shift Enter.
Cú pháp
Hàm Unique có 3 tham số trong đó tham số thứ nhất là bắt buộc và 2 tham số sau không bắt buộc phải điền
=UNIQUE(array, [by_col], [exactly_once])
Trong đó:
Các hàm mảng đó là:
- Hàm Unique
- Hàm Sort
- Hàm SortBy
- Hàm RandArray
- Hàm Sequence
- Hàm Filter
Dưới đây xin giới thiệu lần lượt các hàm trên, nội dung do tôi nghiên cứu trên mạng (các tài liệu tiếng Anh), thực hành và viết lại
I. Hàm UNIQUE
Hàm Unique dùng để lấy danh sách duy nhất từ 1 danh sách nguồn có dữ liệu trùng. Khác với những hàm và công thức trước đây, hàm Unique trả về 1 mảng các giá trị mà không cần Ctrl Shift Enter.
Cú pháp
Hàm Unique có 3 tham số trong đó tham số thứ nhất là bắt buộc và 2 tham số sau không bắt buộc phải điền
=UNIQUE(array, [by_col], [exactly_once])
Trong đó:
- array: vùng...
Ebook: Công thức và hàm Excel 97-2013
Thân gửi tất cả các bạn thành viên GPE,
Tám năm trôi qua thật là nhanh, hôm nay ngày 2-7-2014 sinh nhật GPE lại đến. Qua 8 năm, số lượng thành viên GPE đã có hơn 700 nghìn với hơn 60 nghìn đề tài và gần 500 nghìn bài viết. Số thành viên gắn bó với diễn đàn khá đông với gần 200 nghìn thành viên vào diễn đàn trong năm vừa qua.
Nhân dịp sinh nhật lần này, BQT GPE xin gửi đến tất cả các bạn thành viên món quà "Quyển sách điện tử Công thức và hàm Microsoft Excel 97-2013".
Quyển sách này là bản nâng cấp, cập nhật và hiệu đính từ quyển "Công thức và hàm Excel 97-2007†được phát hàm miễn phí vào dịp sinh nhật lần 2 của GPE (2-7-2008).
Tài liệu điện tử "Công thức và hàm Microsoft Excel 97-2013" của diễn đàn "Giải Pháp Excel - www.giaiphapexcel.com" được tổng hợp dựa trên các bài viết đã đăng trên...
HÀM AVERAGEIF (Excel 2007+)
AVERAGEIF
Trả về trung bình cộng (số học) của tất cả các ô được chọn thỏa mãn một điều kiện cho trước.
Cú pháp: =AVERAGEIF(range, criteria, average_range)
range : Là một hoặc nhiều ô cần tính trung bình, có thể bao gồm các con số, các tên vùng, các mảng hoặc các tham chiếu đến các giá trị...
criteria : Là điều kiện dưới dạng một số, một biểu thức, địa chỉ ô hoặc chuỗi, để qui định việc tính trung bình cho những ô nào...
average_range : Là tập hợp các ô thật sự được tính trung bình. Nếu bỏ trống thì Excel dùng range để tính.
Lưu ý:
• Các ô trong range nếu có chứa những giá trị luận lý (TRUE hoặc FALSE) thì sẽ được bỏ qua.
• Những ô rỗng trong average_range cũng sẽ được bỏ qua.
• Nếu range rỗng hoặc có chứa dữ liệu text, AVERAGEIF sẽ báo lỗi #DIV/0!
• Nếu có một ô nào trong criteria rỗng...
Trả về trung bình cộng (số học) của tất cả các ô được chọn thỏa mãn một điều kiện cho trước.
Cú pháp: =AVERAGEIF(range, criteria, average_range)
range : Là một hoặc nhiều ô cần tính trung bình, có thể bao gồm các con số, các tên vùng, các mảng hoặc các tham chiếu đến các giá trị...
criteria : Là điều kiện dưới dạng một số, một biểu thức, địa chỉ ô hoặc chuỗi, để qui định việc tính trung bình cho những ô nào...
average_range : Là tập hợp các ô thật sự được tính trung bình. Nếu bỏ trống thì Excel dùng range để tính.
Lưu ý:
• Các ô trong range nếu có chứa những giá trị luận lý (TRUE hoặc FALSE) thì sẽ được bỏ qua.
• Những ô rỗng trong average_range cũng sẽ được bỏ qua.
• Nếu range rỗng hoặc có chứa dữ liệu text, AVERAGEIF sẽ báo lỗi #DIV/0!
• Nếu có một ô nào trong criteria rỗng...
HÀM AVERAGE
AVERAGE
Tính trung bình (trung bình cộng) của các số.
Cú pháp: =AVERAGE(number1, number2, ...)
number1, number2, ... : Các số dùng để tính trung bình. Tối thiểu phải là 1 và tối đa là 255 đối số (con số này trong Excel 2003 trở về trước chỉ là 30). Các đối số có thể là số, là tên, là mảng hay tham chiếu đến các giá trị số.
Lưu ý:
• Nếu đối số là một mảng hay là một tham chiếu có chứa text, giá trị logic, ô rỗng, các giá trị lỗi, v.v... thì các giá trị đó sẽ được bỏ qua; tuy nhiên, các ô chứa giá trị là zero (0) thì vẫn được tính.
• Nếu cần tính trung bình cả các giá trị logic và các giá trị text thể hiện số, bạn sử dụng hàm AVERAGEA, với cùng cú pháp.
Ví dụ:
Tính trung bình (trung bình cộng) của các số.
Cú pháp: =AVERAGE(number1, number2, ...)
number1, number2, ... : Các số dùng để tính trung bình. Tối thiểu phải là 1 và tối đa là 255 đối số (con số này trong Excel 2003 trở về trước chỉ là 30). Các đối số có thể là số, là tên, là mảng hay tham chiếu đến các giá trị số.
Lưu ý:
• Nếu đối số là một mảng hay là một tham chiếu có chứa text, giá trị logic, ô rỗng, các giá trị lỗi, v.v... thì các giá trị đó sẽ được bỏ qua; tuy nhiên, các ô chứa giá trị là zero (0) thì vẫn được tính.
• Nếu cần tính trung bình cả các giá trị logic và các giá trị text thể hiện số, bạn sử dụng hàm AVERAGEA, với cùng cú pháp.
Ví dụ:
HÀM COUNTIF
COUNTIF
Đếm số lượng các ô trong một vùng thỏa một điều kiện cho trước.
Cú pháp: =COUNTIF(range, criteria)
Range : Dãy các ô để đếm, có thể là ô chứa số, text, tên, mảng, hay tham chiếu đến các ô chứa số. Ô rỗng sẽ được bỏ qua.
Criteria : Điều kiện để đếm. Có thể ở dạng số, biểu thức, hoặc text. Ví dụ, criteria có thể là 32, "32", "> 32", hoặc "apple", v.v...
Lưu ý :
• Có thể dùng các ký tự đại diện trong điều kiện: dấu ? đại diện cho một ký tự, dấu * đại diện cho nhiều ký tự (nếu như điều kiện là tìm những dấu ? hoặc *, thì gõ thêm dấu ~ ở trước dấu ? hay *)
• Khi điều kiện để đếm là những ký tự, COUNTIF() không phân biệt chữ thường hay chữ hoa.
Ví dụ : Có bảng tính như sau
Đếm số ô có chữ "Táo" trong dãy A2:A5
=COUNTIF(A2:A5, "Táo") → 2
Đếm số ô có chữ "Lê" trong dãy A2:A5...
Đếm số lượng các ô trong một vùng thỏa một điều kiện cho trước.
Cú pháp: =COUNTIF(range, criteria)
Range : Dãy các ô để đếm, có thể là ô chứa số, text, tên, mảng, hay tham chiếu đến các ô chứa số. Ô rỗng sẽ được bỏ qua.
Criteria : Điều kiện để đếm. Có thể ở dạng số, biểu thức, hoặc text. Ví dụ, criteria có thể là 32, "32", "> 32", hoặc "apple", v.v...
Lưu ý :
• Có thể dùng các ký tự đại diện trong điều kiện: dấu ? đại diện cho một ký tự, dấu * đại diện cho nhiều ký tự (nếu như điều kiện là tìm những dấu ? hoặc *, thì gõ thêm dấu ~ ở trước dấu ? hay *)
• Khi điều kiện để đếm là những ký tự, COUNTIF() không phân biệt chữ thường hay chữ hoa.
Ví dụ : Có bảng tính như sau
Đếm số ô có chữ "Táo" trong dãy A2:A5
=COUNTIF(A2:A5, "Táo") → 2
Đếm số ô có chữ "Lê" trong dãy A2:A5...
Ebook: Dữ liệu & Báo cáo trong Excel 2013
Thân gửi tất cả các bạn thành viên GPE,
Sinh nhật lần thứ 9 của GPE lại đến. Nhân dịp sinh nhật lần này, BQT GPE xin gửi đến tất cả các bạn thành viên món quà "Quyển sách Dữ liệu & Báo cáo trong Excel 2013". Tài liệu do anh Phạm Thành Mỹ và Trần Thanh Phong biên soạn nhằm bổ sung một số kiến thức còn chưa được đề cập nhiều trên diễn đàn.
BQT xin cảm ơn các nhà tài trợ đã đồng hành cùng chúng tôi:
Sinh nhật lần thứ 9 của GPE lại đến. Nhân dịp sinh nhật lần này, BQT GPE xin gửi đến tất cả các bạn thành viên món quà "Quyển sách Dữ liệu & Báo cáo trong Excel 2013". Tài liệu do anh Phạm Thành Mỹ và Trần Thanh Phong biên soạn nhằm bổ sung một số kiến thức còn chưa được đề cập nhiều trên diễn đàn.
BQT xin cảm ơn các nhà tài trợ đã đồng hành cùng chúng tôi:
- Trung Tâm Đào Tạo Smart Train
Website: www.smarttrain.edu.vn
- Công ty Cổ Phần Nghiên Cứu và Hỗ trợ Doanh Nghiệp Hà Nội - Kế toán Hà Nội
Website: www.ketoanhanoi.vn
- Công ty TNHH Kiểm Toán Tư Vấn Sáng Lập Á Châu (FCA)
Website: www.sanglap.com.vn ...
Hàm SumIfs (Excel 2007+)
Hàm SUMIFS (Excel 2007+)
Cộng các ô trong vùng lựa chọn thõa nhiều điều kiện
Cú pháp: =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)
Sum_range là các ô cần tính tổng, bao gồm các caon số, tên vùng, mãng hay các tham chiếu đến các giá trị. Các ô trống hay chứa chuỗi (Text) sẽ bị bỏ qua.
Criteria_range1, criteria_range2, … có thể khai báo từ 1 đến 127 vùng dùng để liên kết với các điều kiện cho vùng.
Criteria1, criteria2, … có thể có từ 1 đến 127 điều kiện ở dạng cọn số, biểu thức, tham chiếu hoặc chuỗi. Ví dụ các điều kiện như: 32, "32", ">32", "apples", B4.
Lưu ý:
Cộng các ô trong vùng lựa chọn thõa nhiều điều kiện
Cú pháp: =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)
Sum_range là các ô cần tính tổng, bao gồm các caon số, tên vùng, mãng hay các tham chiếu đến các giá trị. Các ô trống hay chứa chuỗi (Text) sẽ bị bỏ qua.
Criteria_range1, criteria_range2, … có thể khai báo từ 1 đến 127 vùng dùng để liên kết với các điều kiện cho vùng.
Criteria1, criteria2, … có thể có từ 1 đến 127 điều kiện ở dạng cọn số, biểu thức, tham chiếu hoặc chuỗi. Ví dụ các điều kiện như: 32, "32", ">32", "apples", B4.
Lưu ý:
- Mỗi ô trong vùng sum_range chỉ được cộng khi thõa tất cả các điều kiện
- Ô trong sum_range thõa điều kiện (True) tương đương với số 1, và không thõa điều kiện (False) tương đương với số 0 (zero)
- Mỗi criteria_range phải có cùng kích thước và hình dạng giống với sum_range. Ví dụ kích thước của sum_range là 3x2 (3 dòng 2 cột) thì...