Hàm mới Excel 365: tháng 04 năm 2023

[ptm0412]

Hiện có các hàm mới của Excel 365 chưa được viết đến 1 cách chi tiết trên GPE. Những hàm này MS cập nhật từ 2021 và 2022.
- Hàm MAP (2021)
- Hàm MAKEARRAY (2021)
- Hàm SCAN (2021)
- Hàm ISOMITTED (2021)
- Hàm REDUCE (2021)
- Hàm ARRAYTOTEXT (2021)
- Hàm IMAGE (2022)

 

[ptm0412]

Hàm MAP​

Hàm Map tạo ra 1 mảng giá trị từ 1 hoặc nhiều mảng ban đầu bằng cách gắn từng giá trị từ mảng nguồn sau khi tính toán, xử lý vào mảng đích.

Cú pháp​

Hàm MAP có 2 tham số bắt buộc
=MAP(array1, lambda_or_array<#>)

Trong đó:

• Array1: mảng ban đầu. Bắt buộc điền.
• Lambda_or_array<#>: Array khác hoặc một công thức của hàm Lambda chứa 1 biến tham chiếu vào mỗi Array và kết quả tính toán cho mảng đích. Các array đưa vào phải cùng kích thước, mỗi array phải khai báo 1 biến.

Thí dụ mẫu cho hàm MAP​

Thí dụ 1​

Cho 1 mảng các giá trị



Tạo mảng mới có giá trị bằng lũy thừa 3 các giá trị cũ, công thức:
=MAP(A2:C3,LAMBDA(x,x^3))

Thí dụ 2​

Cũng với mảng nguồn của thí dụ 1, nhưng chỉ lũy thừa 3 cho các số nhỏ hơn 4, các giá trị bằng 4 trở lên chỉ nhân 2
Công thức

=MAP(A2:C3,LAMBDA(x,IF(x<4,x^3,x*2)))

Thí dụ 3​

Dò tìm giá trên bảng giá.

Ta có bảng nhập liệu gồm 2 cột đã đặt 2 name (động) là MH và SL. Đồng thời có 1 bảng giá đã đặt name là Bgia


Dùng công thức sau để tra cứu đơn vị tính và giá

=MAP(MH,LAMBDA(mh,VLOOKUP(mh,Bgia,2,0)))



Tiếp theo: Dò tìm ra đơn giá

Thí dụ 4: 2 mảng nguồn​

Hai mảng nguồn nên hàm lambda phải có 2 tham số. Tính thành tiền bằng SL*Dgia

=MAP(SL,DGia,LAMBDA(a,b,a*b))



Khi thêm dữ liệu, tất cả được tính toán

Thí dụ 5: 3 mảng nguồn​

Nhân từng phần tử của 3 ma trận với nhau (hoặc tính toán bất kỳ)

=MAP(D31:F32,D37:F38,D43:F44,LAMBDA(a,b,c,a*b*(1-c)))

Các trường hợp lỗi​

Tên biến không phù hợp với mảng: Trả về lỗi #VALUE!
Lỗi tính toán của hàm Lambda: Lỗi #CALC

 

[ptm0412]

Hàm MAKEARRAY​

Hàm MakeArray tạo ra 1 mảng m dòng n cột với các giá trị theo tính toán.

Cú pháp​

Hàm MakeArray có 3 tham số bắt buộc:

MAKEARRAY(rows, cols, lambda(row, col))
Trong đó:

• Rows: số dòng, nguyên dương và lớn hơn 0
• Cols: số cột, nguyên dương và lớn hơn 0
• lambda(row, col): Một hàm lambda lấy rows, cols làm tham số tính toán ra kết quả từng giá trị của mảng kết quả.

Thí dụ mẫu cho hàm MakeArray​

Thí dụ 1​

Tạo mảng 10 dòng 10 cột, giá trị là lũy thừa từ 1 đến 10 của các số từ 1 đến 10

=MAKEARRAY(10,10,LAMBDA(r,c,r^c))

Thí dụ 2​

Tạo bảng chọn dữ liệu ngẫu nhiên

=MAKEARRAY(3,2,LAMBDA(r,c,IF(c=1,r,INDEX(B15:B24,RANDBETWEEN(1,10),1))))

Thí dụ 3​

Lập bảng cửu chương

=MAKEARRAY(10,3,LAMBDA(r,c,IF(c=1,D37,IF(c=2,r,r*D37))))

Thay đổi ô D37 để có bảng mới

 

[ptm0412]

Hàm SCAN​

Hàm SCAN chuyển 1 mảng ban đầu thành 1 mảng mới cùng kích thước, với mỗi phần tử được tính lũy kế từ phần tử trước đó.

Cú pháp​

Hàm SCAN có 3 tham số:
=SCAN ([initial_value], array, lambda(accumulator, value))

Trong đó:

• Initial_value: giá trị ban đầu cho việc tích lũy
• Array: mảng ban đầu
• Lambda: 1 biểu thức hàm lambda duyệt qua mảng và tính toán ra giá trị từng phần tử. Gồm 2 biến và 1 phép tính toán cho 2 biến đó:
• Accummulator: giá trị ban đầu cho việc tích lũy
• Value: Đại diện cho giá trị từng phần tử
• Tính toán

Thí dụ mẫu:​

Thí dụ 1​

Nhân lũy kế các phần tử của mảng (kết quả giống tính giai thừa)

=SCAN(1,B2:E3,LAMBDA(x,y,x*y))

Nếu initial = 2 sẽ không giống tính giai thừa nữa

=SCAN(2,B2:E3,LAMBDA(x,y,x*y))

Nếu là phép cộng và initial = 0 hoặc bỏ trống

=SCAN(0,B2:E3,LAMBDA(x,y,x+y))

Thí dụ 2​

Với giá trị ban đầu của tháng 1 là 1000, mỗi tháng tăng trưởng 5% so với tháng trước

=SCAN(1,B2:M2,LAMBDA(x,y,x*(y+IF(y=B2,0,1))))

Nếu tháng 1 là 1500



Cũng công thức trên nhưng mức tăng trưởng mỗi tháng khác nhau. Nếu mức tăng trưởng 0% thì 2 tháng bằng nhau.

Thí dụ 3​

Nối chuỗi

=SCAN(,B7:F9,LAMBDA(a,b,a&b))

 

[ptm0412]

Hàm ISOMITTED​

Kiểm tra biến trong hàm Lambda nếu thiếu (không truyền) giá trị sẽ trả về True, ngược lại False.

Cú pháp​

=ISOMITTED(argument)

Trong đó argument là giá trị cần kiểm tra

Thí dụ mẫu​

Thí dụ 1​

Hàm HStack nối 2 mảng theo chiều ngang, nếu truyền thiếu 1 mảng thỉ chỉ lấy 1 mảng còn lại.

=LAMBDA(x,y,IF(ISOMITTED(y),x,IF(isomtted(x),y,HSTACK(x,y))))

Trường hợp 1: truyền đủ 2 tham số

=LAMBDA(x,y,IF(ISOMITTED(y),x,IF(ISOMITTED(x),y,HSTACK(x,y))))(B2:B9,C2:C9)

Trường hợp 2: thiếu mảng thứ 2

=LAMBDA(x,y,IF(ISOMITTED(y),x,IF(ISOMITTED(x),y,HSTACK(x,y))))(B2:B9,)

Trường hợp 3: thiếu mảng thứ nhất

=LAMBDA(x,y,IF(ISOMITTED(y),x,IF(ISOMITTED(x),y,HSTACK(x,y))))(,C2:C9)

Thí dụ 2​

Có Name SumDS viết bằng Lambda như sau:

SumDS =LAMBDA(a,num,c,FILTER(CHOOSE({1,2}, UNIQUE(a),SUMIF(a,UNIQUE(a),num)), IF(ISOMITTED(c ),UNIQUE(a)<>"",UNIQUE(a)=c)))

Mục đích để liệt kê và tính tổng doanh số theo nhóm hàng, chèn IsOmitted để nếu chọn 1 nhóm hàng thì không liệt kê, chỉ tính cho 1 nhóm chọn.

Trường hợp 1: truyền đủ tham số

=SumDS(H2:H33,I2:I33,M1)

Trường hợp 2: Không truyền tham số M1 (nhóm hàng chọn)

=SumDS(H2:H33,I2:I33,)

Thí dụ 3​

Với bảng lương nhân viên sau:

​

Cũng name SumDS như trên, tạo thêm name SumSalary như sau:

SumSalary=LAMBDA(a,b,num,c,IF(ISOMITTED(b),SumDS(a,num,c),FILTER(CHOOSE({1,2,3},a,b,num),a=c)))

Nếu bỏ qua tham số b sẽ tính toán tổng lương theo bộ phận như thí dụ 2 SumDS(a,num,c)

=SumSalary(Q2:Q23,,S2:S23,)

Nếu bỏ qua tham số b, nhưng chọn cột R (công trường) sẽ tính tổng lương theo công trường

=SumSalary(R2:R23,,S2:S23,)



Nếu truyền đủ tham số sẽ không tính tổng mà lọc dữ liệu theo mục chọn W1

=SumSalary(Q2:Q23,P2: P23,S2:S23,W1)



Hoặc chọn phòng kế toán

 

[VetMini]

Những hàm trên, trừ hàm ArrayToText, là nhóm hàm mà dân Excel gọi là "Lamda Helper Functions" (những hàm chuyên dùng Lambda). Chúng sử dụng Lamda để biến hóa array. Công việc mà trước đây hoặc phải dùng công thức khủng, hoặc phải dùng VBA mới thực hiện được.

Thực ra, trong danh sách những hàm chuyên dùng Lambda còn hai hàm ByCol và ByRow. Nhưng có lẽ thớt thấy đã giải thích ở một thớt khác rồi nên không đưa chúng vào.

Theo tôi thì danh sách nên đủ. Chỉ cần thêm ghi chú: hai hàm này đã chỉ dẫn ở thớt [đường link]

 

[ptm0412]

Theo tôi thì danh sách nên đủ. Chỉ cần thêm ghi chú: hai hàm này đã chỉ dẫn ở thớt [đường link]

Đã tạo danh sách bài viết 27/30 hàm tại Danh sách hàm mới của Excel 365 và bài viết

 

[ptm0412]

Hàm REDUCE​

Hàm Reduce tính từ 1 mảng thành 1 giá trị tích lũy với 1 phép tính cho từng già trị của mảng bằng hàm Lambda

Cú pháp​

Hàm Reduce có 3 tham số:

=REDUCE([initial_value], array, lambda(accumulator, value))

Trong đó

• Initial_value là giá trị ban đầu có thể bỏ qua
• Array là mảng cần tính toán
• Lambda là hàm tính toán các giá trị của mảng sau đó tính tổng.

Thí dụ mẫu​

Thí dụ 1​

Tính tổng bình phương các phần tử

=REDUCE(0,B2:E4,LAMBDA(a,b,a+b^2))



Với ô E10 kiểm tra kết quả bằng cách tính thông thường

Thí dụ 2​

Tính tổng 1 mảng với điều kiện nếu giá trị nào nhỏ hơn 30 thì cho bằng 30.

=REDUCE(0,B13:C22,LAMBDA(a,b,a+MAX(b,30)))


Với ô G22 kiểm tra kết quả bằng cách tính thông thường

Thí dụ 3​

Chỉ đếm số lẻ

=REDUCE(0,B25: D28,LAMBDA(a,b,IF(ISODD(b),a+1,a)))


Hoặc chỉ tính tổng các số lẻ

=REDUCE(0,B25: D28,LAMBDA(a,b,IF(ISODD(b),a+b,a)))


Hoặc chuyển lẻ thành chẵn bằng cách cộng 1 rồi tính tổng hết

=REDUCE(0,B25: D28,LAMBDA(a,b,a+b+IF(ISODD(b),1,0)))


Hoặc kết hợp tính tổng lẻ, tổng chẵn và tổng hết

=LET(x,REDUCE(0,B25: D28,LAMBDA(a,b,a+IF(ISODD(b),b,0))),y,REDUCE(0,B25: D28,LAMBDA(a,b,a+IF(ISEVEN(b),b,0))),VSTACK(x,y,x+y))

 

[ptm0412]

Hàm ARRAYTOTEXT​

Hàm ArrayToText chuyển 1 mảng dữ liệu thành 1 chuỗi duy nhất. Tùy theo định dạng chọn, các giá trị sẽ ngăn cách bởi dấu phảy, hoặc vừa phảy vừa chấm phảy.

Cú pháp​

Hàm có 2 tham số:

ARRAYTOTEXT(array, [format])

Trong đó:

• Array: là mảng cần nối lại thành chuỗi
• [Format]: gồm 2 giá trị 0 và 1, mặc định nếu bỏ trống là 0
• Nếu là 0: tất cả phần tử nối lại thành 1 chuỗi, phân cách bởi dấu phảy (,)
• Nếu là 1: Sẽ phân biệt nối cùng dòng là dấu phảy (,) giữa 2 dòng là dấu chấm phảy (. Khi chọn format là 1:
• Giá trị số và giá trị logic giữ nguyên, giá trị text bị bỏ trong cặp dấu nháy đôi
• Nguyên chuỗi sau khi nối nằm trong cặp dấu ngoặc nhọn {}

Hạn chế: Cả 2 trường hợp 0 và 1, giá trị ngày tháng bị quy ra số

Thí dụ mẫu​

Thí dụ 1​

Các trường hợp nối đơn giản như hình:
Với dữ liệu:



Công thức và kết quả

Thí dụ 2​

Ứng dụng nối địa chỉ mail để gởi chung

Thí dụ 3​

Tạo 1 chuỗi các số liên tiếp

=ARRAYTOTEXT(SEQUENCE(12))

Hoặc tạo 1 chuỗi 26 ký tự ABC

 

[ptm0412]

Tài liệu pdf và file thực hành mẫu

 

[VetMini]

Gần giữa năm 23 rồi mà còn "cập nhật 21-22" sao cha nội.

 

[ptm0412]

Gần giữa năm 23 rồi mà còn "cập nhật 21-22" sao cha nội.

Bây giờ mới viết nhưng hàm cũ, do MS cập nhật năm xưa. GPE chưa ai viết thì mình viết thôi.

 

[VetMini]

Bây giờ mới viết nhưng hàm cũ, do MS cập nhật năm xưa. GPE chưa ai viết thì mình viết thôi.

Nếu MS thì đề "MS cập nhật ..."
Nếu tài liệu cập nhật thì đề "Tài liệu cập nhật ..."

Là tôi thì tôi ghi rõ:
"Các hàm được MS cập nhật 2021-2022", VÀ
"Tài liệu xuất bản lần đầu tháng Năm 2023"
Sau đó mõi lần cập nhật thì thêm:
"Tài liệu cập nhật lần thứ hai ...."
Nếu MS có bổ sung gì thì thêm:
"Tài liệu cập nhật lần thứ hai ...., đáp ứng với những bổ sung của MS ...."

 

[ptm0412]

Nếu MS thì đề "MS cập nhật ..."
Nếu tài liệu cập nhật thì đề "Tài liệu cập nhật ..."

Đã sửa nội dung bài 1 và thay file đã chỉnh sửa vào bài 10. Hài lòng nhé "cha nội".

 

[befaint]

Nếu MS thì đề "MS cập nhật ..."
Nếu tài liệu cập nhật thì đề "Tài liệu cập nhật ..."

Xoay quanh vụ này, em cũng hơn 1 lần nói về cách phân chia/ sắp xếp không chính xác.

MS có bó buộc những hàm, những ứng dụng đó chỉ có ở Excel365/ Office 365 đâu.

các hàm mới của Excel 365

Viết thế này thành ra các hàm đó chỉ có ở Excel 365, mà thực tế không đúng.
Tài liệu của MS ghi rất rõ (ở ngay đầu mỗi trang hướng dẫn sử dụng hàm) phạm vi hoạt động của hàm đó ở đâu.

 

[ptm0412]

Xoay quanh vụ này,

Vụ 1: Thuộc về kỹ thuật và 2 chàng kỹ thuật rất bận rộn kiếm cơm. Chỉ có lão ct thất nghiệp nên "rảnh", "rỗi hơi" nên ngồi viết lảm nhảm.
Vụ 2: 2021 ra sau 365 nên chắc là có, 2019 thì không chắc. (Suy luận có thể sai nhưng lỡ rồi). Để từ từ sửa

 

[befaint]

Vụ 1: Thuộc về kỹ thuật và 2 chàng kỹ thuật rất bận rộn kiếm cơm. Chỉ có lão ct thất nghiệp nên "rảnh", "rỗi hơi" nên ngồi viết lảm nhảm.

(Em suy luận)
Ngay từ đầu người phục trách "kỹ thuật" đã không nắm được hệ sinh thái các sản phẩm/ dịch vụ của MS. Làm theo cảm nhận, tưởnng tượng, không tham khảo ý kiến người có chuyên môn.

Vụ 2: 2021 ra sau 365 nên chắc là có, 2019 thì không chắc.

Excel cho Web ra đời cùng Excel 365.