Chi tiết cách dùng của 14 hàm mới trong Excel 365 (tháng 8/ 2022) (2 người xem)

[ptm0412]

Chi tiết cách dùng của 14 hàm mới trong Excel 365 (tháng 8/ 2022)​

Liên quan đến chủ đề Những hàm mới đang chờ đợi, hôm nay tôi viết tuần tự cách dùng các tham số của các hàm mới:
1. Hàm TEXTBEFORE - Trả về chuỗi đứng trước những ký tự phân tách. Trước đây chúng ta thường dùng LEFT và FIND (tìm vị trí ký tự phân cách).

2. Hàm TEXTAFTER - Trả về chuỗi đứng sau những ký tự phân tách. Trước đây chúng ta thường dùng RIGHT và FIND (tìm vị trí ký tự phân cách).

3. Hàm TEXTSPLIT - Tách chuỗi thành các hàng hoặc cột bằng dấu phân cách. Trước đây chúng ta thường sử dụng Text to Columns để tách theo hàng, hoặc sử dụng các hàm TRIM, MID, SUBSTITUTE.

Các hàm mảng động và xử lý mảng

4. Hàm VSTACK - nối mảng theo chiều dọc.

5. Hàm HSTACK - nối mảng theo chiều ngang

6. Hàm TOROW - Trả về mảng dưới dạng một hàng.

7. Hàm TOCOL - Trả về mảng dưới dạng một cột.

8. Hàm WRAPROWS - Gộp mảng 1 hàng thành mảng 2 chiều.

9. Hàm WRAPCOLS - Gộp mảng 1 cột thành mảng 2 chiều.

10. Hàm TAKE - Trả về các hàng hoặc cột từ đầu hoặc cuối mảng.

11. Hàm DROP - Giảm hàng hoặc cột từ đầu hoặc cuối mảng.

12. Hàm CHOOSEROWS - Trả về các hàng được chỉ định từ một mảng.

13. Hàm CHOOSECOLS - Trả về các cột được chỉ định từ một mảng.

14. Hàm EXPAND - Mở rộng mảng theo các kích thước được chỉ định.

1. Hàm TextBefore
Hàm Unique dùng để lấy chuỗi ký tự đứng trước 1 ký tự hoặc chuỗi ký tự cho trước, trong 1 chuỗi ban đầu

1.1 Cú pháp
Hàm TextBefore có 6 tham số trong đó 2 tham số thứ nhất và thứ nhì là bắt buộc và 4 tham số sau không bắt buộc phải điền.
=TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
Trong đó:

• TEXT: Chuỗi ban đầu. Bắt buộc điền.
• delimeter: Chuỗi hoặc ký tự cần tìm trong TEXT. Từ đó sẽ lấy bên trái TEXT cho đến vị trí tìm thấy delimeter.
• [instance_num]: Tùy chọn dạng số. Mang ý nghĩa thứ tự xuất hiện của delimeter lần thứ mấy trong chuỗi TEXT. Nếu instance_num là số âm, thì tìm ngược từ cuối chuỗi TEXT lên đầu và lấy thứ tự ngược. Mặc định 1 nếu bỏ trống.
• [match_mode]: Ký tự/ hoặc chuỗi tìm kiếm có phân biệt hoa thường hay không, mặc định 0 nếu bỏ trống
  • 0: Có phân biệt hoa/ thường
  • 1: Không phân biệt hoa thường
• [match_end]: xem ký tự cuối chuỗi là 1 delimeter, nghĩa là nếu không tìm thấy thì lấy hết bên trái của end delimeter. Chỉ có tác dụng khi instance_num = 1 hoặc -1
  • 0: Không sử dụng ký tự cuối làm delimeter
  • 1: Có sử dụng ký tự cuối làm delimeter
  • instance_num = 1: Lấy hết chuỗi (bên trái của ký tự cuối, tính từ trái qua phải)
  • instance_num = -1: Kết quả là chuỗi rỗng (bên trái của ký tự cuối, tính từ phải qua trái)
• [if not found]: Chuỗi trả về nếu không tìm thấy. Nếu bỏ trống tham số này sẽ trả về #N/A

1.2 Thí dụ mẫu cho hàm TEXTBEFORE​

1.2.1 Thí dụ cho tham số instance_num​

Sự khác nhau khi dùng tham số thứ 3 là số khác nhau, dương hoặc âm.
instance_num = 1: xuất hiện lần thứ nhất từ trái qua phải



instance_num = 3: xuất hiện lần thứ 3 từ trái qua phải



instance_num = -2, xuất hiện lần thứ 2 từ phải qua trái

1.2.2 Thí dụ cho tham số match_mode​

Match_mode = 1: không phân biệt hoa thường (ptm và Ptm), lấy “Ptm” vì xuất hiện lần 2.



Match_mode = 0: có phân biệt hoa thường (ptm và Ptm), lấy “ptm” xuất hiện lần 2, bỏ qua “Ptm”.

1.2.3 Thí dụ cho tham số Match_end​

Match_end = 0 hoặc bỏ trống, instance_num = 1, dò tìm “Xtm” không có

​

Match_end = 1, instance_num = 1, dò tìm “Xtm” không có. Lấy hết chuỗi gốc



Match_end = 1, instance_num = -1, dò tìm “Xtm” không có, kết quả chuỗi rỗng do tìm ngược từ phải qua trái

​

1.2.4 Thí dụ cho tham số if_not_found​

If_not_found bỏ trống, tìm “Ztm” không có: lỗi #N/A



If_not_found không bỏ trống, tìm “Ztm” không có

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

Instance_num lớn hơn chiều dài chuỗi hoặc bằng 0: Trả về lỗi #VALUE!

Instance_num lớn hơn số lần xuất hiện của delimeter: Lỗi #N/A

Không tìm thấy delimeter mà bỏ trống If_not_found: Lỗi #N/A

 

[ptm0412]

2. Hàm TEXTAFTER​

Lấy bên phải của 1 chuỗi căn cứ vào vị trí 1 chuỗi/ hoặc 1 ký tự tìm thấy trong chuỗi ban đầu

2.1 Cú pháp​

Tương tự TextBefore, TextAfter cũng có 6 tham số trong đó 4 tham số tùy chọn.

=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

Trong đó:

• TEXT: Chuỗi ban đầu. Bắt buộc điền.
• delimeter: Chuỗi hoặc ký tự cần tìm trong TEXT. Từ đó sẽ lấy bên phải TEXT cho đến vị trí tìm thấy delimeter.
• [instance_num]: Tùy chọn dạng số. Mang ý nghĩa thứ tự xuất hiện của delimeter lần thứ mấy trong chuỗi TEXT. Nếu instance_num là số âm, thì tìm ngược từ cuối chuỗi TEXT lên đầu và lấy thứ tự ngược. Mặc định 1 nếu bỏ trống.
• [match_mode]: Ký tự/ hoặc chuỗi tìm kiếm có phân biệt hoa thường hay không, mặc định 0 nếu bỏ trống
  • 0: Có phân biệt hoa/ thường
  • 1: Không phân biệt hoa thường
• [match_end]: xem ký tự cuối chuỗi là 1 delimeter, nghĩa là nếu không tìm thấy thì lấy hết bên trái của end delimeter. Chỉ có tác dụng khi instance_num = 1 hoặc -1
  • 0: Không sử dụng ký tự cuối làm delimeter
  • 1: Có sử dụng ký tự cuối làm delimeter
  • instance_num = 1: Kết quả là chuỗi rỗng (bên phải của ký tự cuối, tính từ trái qua phải)
  • instance_num = -1: Lấy hết chuỗi (bên phải của ký tự cuối, tính từ phải qua trái)
• [if not found]: Chuỗi trả về nếu không tìm thấy. Nếu bỏ trống tham số này sẽ trả về #N/A

2.2 Thí dụ mẫu cho hàm TextAfter​

2.2.1 Tham số instance_num​

Instance_num = 2: tìm từ trái qua phải, vị trí tìm thấy thứ 2.



Instance_num = -1: Tìm ngược từ phải qua trái, vị trí đầu tiên tìm thấy

2.2.2 Tham số match_mode​

Match_mode = 0: là mặc định, kết quả giống 3.2.1

Instance_num = 3, match_mode =1: tìm từ trái qua phải, vị trí tìm thấy thứ 3 không phân biệt hoa thường. Tìm thấy “Ptm” ở vị trí 3



Instance_num = -3, match_mode =1: tìm từ phải qua trái, vị trí tìm thấy thứ 3 không phân biệt hoa thường. Tìm thấy “ptm” ở vị trí 3, tính luôn "Ptm" là vị trí 1

2.2.3 Tham số match_end​

Match_end = 1, tìm từ trái qua phải “xtm” không có, trả về chuỗi rỗng



Match_end = 1, tìm từ phải qua trái “xtm” không có, trả về chuỗi đầy đủ

2.2.4 Tham số if_not_found​

Tương tự TextBefore, điền giá trị mong muốn nếu không tìm thấy

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

Instance_num lớn hơn chiều dài chuỗi hoặc bằng 0: Trả về lỗi #VALUE!
Instance_num lớn hơn số lần xuất hiện của delimeter: Lỗi #N/A
Không tìm thấy delimeter mà bỏ trống If_not_found: Lỗi #N/A

3. Ứng dụng tách họ tên​

3.1 Tách họ lót và tên​

Dùng TextBefore lấy họ lót và TextAfter lấy tên

3.2. Tách tên ghép (2 từ)​

3.3. Tách họ ghép (nhiều từ)​

 

[ptm0412]

4. Hàm TextSplit​

Hàm TextSplit là dùng để tách 1 chuỗi dài ra những thành phần. Hàm này ngược với hàm TextJoin. Textsplit có thể tách 1 chuỗi ra nhiều ô, sắp xếp theo hàng ngang hoặc dọc, tách ra bảng nhiều dòng nhiều cột.

4.1 Cú pháp​

Hàm TextSplit có 2 tham chiếu bắt buộc và 4 tham chiếu không bắt buộc

=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

• text: Chuỗi ban đầu cần tách.
• Col_delimeter: Ký hiệu phân cách cột.
• [row_delimeter]: Ký hiệu phân cách dòng, nếu bỏ qua là không tách dòng
• [ignore_empty]: Mặc định True = Bỏ qua giá trị rỗng. Nếu bằng False, sẽ có 1 ô rỗng ở giữa 2 ký hiệu phân cách liền nhau.
• [match_mode]: Tìm ký hiệu phân cách có phân biệt hoa/ thường, mặc định 1 là có phân biệt.
• [pad_with]: kết quả điền vào khi lỗi (mặc định #N/A)

Nếu có nhiều ký hiệu để tách dòng/ hoặc tách cột, các ký hiệu phải bỏ trong cặp dấu nhọn {…}, cách nhau bởi dấu phẩy.

4.2 Thí dụ ứng dụng TextSplit​

4.2.1 Thí dụ 1 _Tách thành cột​

Tách thành các cột với tham số delimeter tách cột là dấu phảy

C1 = TEXTSPLIT(B2,",").



Tách bằng khoảng trắng:

C2 =TEXTSPLIT(B2," ")
C3 =(TEXTSPLIT(B3,” “)

4.2.2 Thí dụ 2: Tách thành dòng​

Bỏ qua tham số tách cột, chỉ ghi ký hiệu tách dòng. Trường hợp dưới đây, ký hiệu tách dòng là 2 ký tự dấu phẩy và khoảng trắng kế nhau: “ ,“. Kết quả mong muốn là giá trị số nên nhân 1.

C9 =TEXTSPLIT(B9,,” ,”)*1

4.2.3 Thí dụ 3 – Tách thành bảng nhiều dòng nhiều cột​

Dùng khoảng trắng tách cột, dấu phẩy tách dòng. Không bỏ qua khoảng trắng (bỏ trống tham số ignore_empty). Có 2 vị trí tìm được 2 ký hiệu “,” và “ “ kế bên nhau, nên C6 và C7 coi như có giá trị rỗng, E5 thiếu 1 giá trị trên dòng nên bị #N/A

C5 =TEXTSPLIT(B5," ",",")

​

Nếu bỏ qua giá trị rỗng (ignore_empty = True hoặc 1), thì không bị tình trạng trên.
C5 =TEXTSPLIT(B5," ",",",1)

4.2.4 Thí dụ 4 –cùng lúc sử dụng hai ký hiệu tách cột​

Nếu chuỗi phức tạp, sự phân cách thông tin không đồng nhất, thì dùng cặp dấu {} liệt kê các ký hiệu phân cách

Thí dụ chuỗi: Số 134 đường Thống Nhất, P7 – Quận 3, TP HCM

Có 2 ký hiệu phân cách là dấu phảy và dấu gạch ngang.

C14 =TEXTSPLIT(B14,{",","-"})
C15 =TEXTSPLIT(B15,{",","-"})

4.2.5 Thí dụ 5 – 3 ký hiệu tách cột​

Bỏ cả 3 ký hiệu tách cột trong cặp dấu {}

C16 =TEXTSPLIT(B16,{",","-",";"})

4.2.6 Thí dụ 6 – Hai ký hiệu tách cột liền kề​

Cần dùng tham số thứ tư (ignore_empty) bằng True hoặc 1

- Không dùng tham số thứ 4 (mặc định 0 hoặc False): Có 1 ô trống do có 2 ký tự “-;” liền kề.
C17 =TEXTSPLIT(B17,{",","-",";"})



- Có tham số ignore+empty = 1 hoặc True
C18 =TEXTSPLIT(B18,{",","-",";"},,1)

 

[ptm0412]

4. Hàm TextSplit (tiếp theo)​

4.2.7. Thí dụ 7 – Tham số pad_with​

Tham số pad_with là giá trị điền vào kết quả nếu lỗi.
- Không dùng pad_with


- Có dùng pad_with bằng "0"

4.2.8. Thí dụ 8 - Tham số match_mode​

Có phân biệt hoa/ thường ký hiệu tách dòng/ cột hay không, mặc định 0 hoặc False

• Match_mode = 0: Có phân biệt
• Match_mode = 1: Không phân biệt

Có phân biệt hoa thường:



Không phân biệt hoa thường: Phải dùng tham số ignore_empty nếu delimeter nằm đầu câu.

 

[ptm0412]

5. Hàm HSTACK​

Hàm HStack là hàm gộp các vùng trên bảng tính thành 1 vùng chung, theo chiều ngang.

5.1. Cú pháp​

Hàm HStack có các tham số tương đương các vùng cần ráp lại, tối đa 255 vùng.

=HSTACK(array1,[array2],...)

• array1: Vùng 1.
• [array2]: Vùng 2.
• … Các vùng còn lại theo thứ tự

Kết quả có số dòng bằng số dòng lớn nhất của các vùng, số cột bằng tổng số các số cột của từng vùng.
Nếu 1 vùng có số dòng ít hơn, các ô thiếu đó bị gán #N/A

5.2. Thí dụ áp dụng HStack​

5.2.1. Thí dụ 1 – Ứng dụng đơn giản​

Nối 3 vùng theo hàng ngang



Khử lỗi NA:


Nối thêm 1 vùng ở sheet khác

5.2.2 Thí dụ 2 – Nhiều sheets cùng cấu trúc​

Nếu có nhiều sheet cùng cấu trúc, và cùng vị trí dòng cột như sau:



Công thức HStack là:

=HSTACK('CH1:CH5'!B2:B13)

 

[ptm0412]

6. Hàm VSTACK​

Tương tự hàm HStack, hàm Vstack nối các vùng dữ liệu lại thành 1 nhưng theo chiều dọc.

6.1. Cú pháp​

Hàm VStack có các tham số tương đương các vùng cần ráp lại, tối đa 255 vùng.
=VSTACK(array1,[array2],...)

• array1: Vùng 1.
• [array2]: Vùng 2.
• … Các vùng còn lại theo thứ tự

Kết quả có số cột bằng số cột lớn nhất của các vùng, số dòng bằng tổng số các số dòng của từng vùng cộng lại.
Nếu 1 vùng có số cột ít hơn, các ô thiếu đó bị gán #N/A

6.2. Thí dụ ứng dụng hàm VSTACK​

6.2.1. Thí dụ 1 – ứng dụng đơn giản​

Nối 4 vùng theo hàng dọc, trong đó có 1 vùng ở sheet khác, 1 vùng chỉ có 1 cột, các vùng khác 2 cột.



Khử lỗi bằng IfError
=IFERROR(VSTACK(B2:C8,E4:E10,H3:I11,Sheet2!A1:B6),0)

6.2.2. Thí dụ 2 – Nhiều sheet cùng cấu trúc​

Tương tự hàm HStack nế uco1 5 sheet cùng cấu trúc, sử dụng hàm Vstack như sau:
=VSTACK('CH1:CH5'!A2:C13)

 

[ptm0412]

7. Hàm TOROW​

Hàm ToRow chuyển 1 bảng nhiều dòng, cột về 1 dòng.

7.1. Cú pháp​

Hàm ToRow có 1 tham số bắt buộc và 2 tham số tuỳ chọn
=TOROW(array, [ignore], [scan_by_column])

• array: vùng dữ liệu cần chuyển thành dòng.
• [ignore]: Tùy chọn bỏ qua các giá trị không mong muốn, gồm 4 giá trị để chọn:

• 0: mặc định hoặc bỏ qua. Lấy hết các giá trị trong array
• 1: bỏ qua ô rỗng
• 2: Bỏ qua ô lỗi
• 3: Bỏ qua ô trống và lỗi.

• [scan_by_column]: Định hướng đọc, mặc định False hoặc 0, hoặc bỏ trống

• 0 hoặc False: Mặc định, đọc dòng trước cột sau
• 1 hoặc True: đọc cột trước dòng sau.

7.2. Thí dụ ứng dụng hàm ToRow​

7.2.1. Thí dụ 1 – Dòng trước cột sau​

Công thức bỏ qua tham số thứ 3 sẽ đọc dòng trước cột sau

=TOROW(A2: D4)



Bỏ qua ô trống:
=TOROW(A2: D4,1)



Bỏ qua ô lỗi
=TOROW(A2: D4,2)
Ô trống được cho là zero



Bỏ qua ô trống và lỗi
=TOROW(A2: D4,3)

7.2.2. Thí dụ 2: Cột trước dòng sau​

Tham số thứ ba điền 1 hoặc True
A7 =TOROW(A2: D4,3,1)
So sánh với A6 dòng trước cột sau.

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

Hàm ToRow bị lỗi #NUM khi vùng bảng dữ liệu quá lớn

 

[ptm0412]

8. Hàm TOCOL​

Hàm ToCol chuyển 1 bảng nhiều dòng, cột về 1 cột.

8.1. Cú pháp​

Hàm ToCol có 1 tham số bắt buộc và 2 tham số tuỳ chọn
=TOCOL(array, [ignore], [scan_by_column])

• array: vùng dữ liệu cần chuyển thành dòng.
• [ignore]: Tùy chọn bỏ qua các giá trị không mong muốn, gồm 4 giá trị để chọn:

• 0: mặc định hoặc bỏ qua. Lấy hết các giá trị trong array
• 1: bỏ qua ô rỗng
• 2: Bỏ qua ô lỗi
• 3: Bỏ qua ô trống và lỗi.

• [scan_by_column]: Định hướng đọc, mặc định False hoặc 0, hoặc bỏ trống

• 0 hoặc False: Mặc định, đọc dòng trước cột sau
• 1 hoặc True: đọc cột trước dòng sau.

8.2. Thí dụ ứng dụng hàm ToCol​

8.2.1. Thí dụ 1 – Dòng trước cột sau​

Công thức bỏ qua tham số thứ 3 sẽ đọc dòng trước cột sau
=TOCOL(A2:E4)



Bỏ qua ô trống:
=TOCOL(A2:E4,1)




Bỏ qua ô lỗi
=TOCOL(A2:E4,2)
Ô trống được cho là zero



Bỏ qua ô trống và lỗi
=TOCOL(A2:E4,3)

8.2.2. Thí dụ 2: Cột trước dòng sau​

Tham số thứ ba điền 1 hoặc True

H2 =TOCOL(A2:E4,3,1)

So sánh với G2 dòng trước cột sau.

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

Hàm ToCol bị lỗi #NUM khi vùng bảng dữ liệu quá lớn

 

[ptm0412]

9. Hàm WrapRows​

Hàm WrapRows dùng chuyển dữ liệu từ 1 dòng hoặc 1 cột thành 1 mảng 2 chiều nhiều dòng, cột. Số cột được chỉ định.

9.1. Cú pháp​

Hàm WrapRows có 2 tham số bắt buộc và 1 tham số tùy chọn
=WRAPROWS(vector, wrap_count, [pad_with])

• vector: vùng dữ liệu cần chuyển thành mảng 2 chiều
• wrap_count: số phần tử trên 1 dòng kết quả (số cột)
• [pad_with]: giá trị thêm vào nếu thiếu giá trị trên dòng cuối. Nếu bỏ trống thì bị lỗi #N/A

9.2. Thí dụ​

9.2.1. Thí dụ 1​

Chuyển 1 dòng thành bảng 2 chiều có 2 cột
=WRAPROWS(A1:I1,2)



Khử lỗi N/A
=WRAPROWS(A1:I1,2,”Oi gioi oi”)



Chuyển dòng thành bảng 2 chiều 3 cột
=WRAPROWS(A1:I1,3,”Oi gioi oi”)

9.2.2. Thí dụ 2​

Chuyển 1 cột thành bảng 2 chiều 3 cột
=WRAPROWS(M1:M9,3)



Chuyển 1 cột thành bảng 2 chiều 4 cột, khử lỗi NA
=WRAPROWS(M1:M9,4,”Nothing”)

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

• Vector là nhiều dòng, nhiều cột: lỗi #VALUE
• Wrap_count là số nhỏ hơn 1 bị lỗi #NUM! Wrap_count là số thập phân bị coi là số nguyên (cắt bỏ phần thập phân)

 

[ptm0412]

10. Hàm WrapCols​

Hàm WrapCols dùng chuyển dữ liệu từ 1 dòng hoặc 1 cột thành 1 mảng 2 chiều nhiều dòng, cột, số dòng được chỉ định

10.1. Cú pháp​

Hàm WrapCols có 2 tham số bắt buộc và 1 tham số tùy chọn
=WRAPCOLS(vector, wrap_count, [pad_with])

• vector: vùng dữ liệu cần chuyển thành mảng 2 chiều
• wrap_count: số phần tử trên 1 cột kết quả (số dòng)
• [pad_with]: giá trị thêm vào nếu thiếu giá trị trên cột cuối. Nếu bỏ trống thì bị lỗi #N/A

10.2. Thí dụ​

10.2.1. Thí dụ 1​

Chuyển 1 dòng thành bảng 2 chiều có 2 dòng
=WRAPCOLS(A1:I1,2)



Khử lỗi N/A
=WRAPCOLS(A1:I1,2,””)



Chuyển dòng thành bảng 2 chiều 3 dòng
=WRAPCOLS(A1:I1,3,””)
Có thể so sánh với WrapRows ở ô F3 để thấy thứ tự sắp xếp khác nhau

10.2.2 Thí dụ 2​

Chuyển 1 cột thành bảng 2 chiều 4 dòng

=WRAPCOLS(K1:K9,4)

Chuyển 1 cột thành bảng 2 chiều 5 dòng, khử lỗi NA

=WRAPCOLS(K1:K9,5,””)

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

• Vector là nhiều dòng, nhiều cột: lỗi #VALUE
• Wrap_count là số nhỏ hơn 1 bị lỗi #NUM! Wrap_count là số thập phân bị coi là số nguyên (cắt bỏ phần thập phân)

 

[ptm0412]

11. Hàm TAKE​

Hàm Take lấy một số dòng và cột liên tục từ một vùng dữ liệu ban đầu. Hàm này kết hợp hàm Sort để lấy Top hoặc Bottom.

11.1. Cú pháp​

Hàm Take có 2 tham số bắt buộc và 1 tham số tùy chọn
=TAKE(array, rows, [columns])

• array: vùng dữ liệu cần trích xuất
• rows: số dòng cần trích xuất. Nếu dương lấy các dòng trên cùng, nếu âm lấy các dòng dưới cùng, nếu bỏ qua thì lấy hết các dòng
• [columns]: số cột cần lấy. Nếu số dương, lấy từ bên trái qua, nếu số âm, lấy từ bên phải qua. Nếu bỏ qua thì lấy tất cả các cột

11.2. Thí dụ​

11.2.1. Lấy hết cột​

Lấy 2 dòng từ trên xuống
=TAKE(A2:F9,2)



Lấy 4 dòng từ dưới lên
=TAKE(A2:F9,-4)

11.2.2. Chỉ lấy 1 số cột​

Lấy 3 cột đầu
=TAKE(A2:F9,-4,3)



Lấy 3 cột sau
=TAKE(A2:F9,-4,-3)

11.3. Ứng dụng Top, Bottom​

11.3.1. Lọc tháng 1, lấy 3 cửa hàng doanh số lớn nhất​

=TAKE(SORT(FILTER(A15: D50,A15:A50=H14),4,1),I14,-3)
Trong đó:

• Hàm Filter lấy dữ liệu chỉ của tháng 1
• Hàm Sort sắp dữ liệu sau khi filter theo doanh số tăng dần
• Hàm Take lấy 3 dòng cuối. Nếu Sort giảm dần thì lấy 3 dòng đầu.

11.3.2. Lọc tháng 3, lấy 3 cửa hàng doanh số ít nhất​

=TAKE(SORT(FILTER(A15: D50,A15:A50=H14),4,1),I14,-3)
Khác ở trên là Sort tăng dần nhưng lấy 3 dòng đầu

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

• Trả về lỗi #VALUE! Nếu bỏ trống tham số thứ nhất (vùng dữ liệu), hoặc tham số thứ nhất tham chiếu đến 1 vùng rỗng (0 dòng hoặc 0 cột)
• Trả về lỗi #NUM nếu array quá lớn
• Trả về lỗi #CALC! nếu tham số thứ 2 hoặc 3 bằng zero.

 

[ptm0412]

12. Hàm DROP​

Hàm Drop trái với hàm Take, loại bỏ 1 số dòng hoặc cột từ 1 bảng dữ liệu ban đầu. Thí dụ loại bỏ dòng tiêu đề chỉ lấy Data, hoặc loại bỏ dòng tổng, dòng footer ở dưới cùng.

12.1. Cú pháp​

Hàm có 2 tham số bắt buộc và 1 tham số tùy chọn
=DROP(array, rows, [columns])

• array: vùng dữ liệu cần xóa bớt
• rows: số dòng cần xóa. Nếu dương xóa các dòng trên cùng, nếu âm xóa các dòng dưới cùng, nếu bỏ qua thì lấy hết các dòng (không xóa dòng nào)
• [columns]: số cột cần xóa. Nếu số dương, xóa từ bên trái qua, nếu số âm, xóa từ bên phải qua. Nếu bỏ qua thì lấy tất cả các cột (không xóa cột nào)

12.2. Thí dụ​

Lấy cả tiêu đề, loại bỏ dòng tổng cộng, chỉ lấy tiêu đề dòng và 3 cột số liệu
=DROP(A1:G10,-1,-3)



Chỉ lấy số liệu 4 cột cuối, bỏ qua dòng tổng và 3 cột đầu
=DROP(A1:G10,-1,3)

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

• Trả về lỗi #VALUE! Nếu bỏ trống tham số thứ nhất (vùng dữ liệu), hoặc tham số thứ nhất tham chiếu đến 1 vùng rỗng (0 dòng hoặc 0 cột)
• Trả về lỗi #NUM nếu array quá lớn
• Trả về lỗi #CALC! nếu tham số thứ 2 hoặc 3 bằng zero.

 

[ptm0412]

13. Hàm CHOOSEROWS​

Trích xuất những dòng có số thứ tự chỉ định từ 1 mảng hoặc 1 vùng dữ liệu

13.1. Cú pháp​

Hàm có 2 tham số bắt buộc và những tham số tùy chọn nhằm liệt kê các thứ tự dòng cần lấy.
= CHOOSEROWS(array, row_num1, [row_num2], …)

• array: vùng dữ liệu cần trích xuất
• row_num1: số thứ tự dòng cần trích xuất. Nếu dương lấy thứ tự từ trên xuống, nếu âm lấy thứ tự từ dưới lên, không được bỏ qua.
• [row_num2], …: các số thứ tự dòng cần lấy kế tiếp. Nếu dương lấy thứ tự từ trên xuống, nếu âm lấy thứ tự từ dưới lên, liệt kê bao nhiêu lấy bấy nhiêu, có thể là mảng các số thứ tự, nếu liệt kê lặp lại 1 số thứ tự nào thì dòng có số thứ tự đó được lấy nhiều lần.

13.2. Thí dụ​

13.2.1. Lấy cả tiêu đề và các dòng lẻ từ trên xuống​

=CHOOSEROWS(A1:G10,1,2,4,6,8)
(lấy từ trên xuống)

13.2.2. Lấy cả tiêu đề và các dòng chẵn từ dưới lên​

Tiêu đề đếm từ dưới lên là 10, tham số là -10. Do liệt kê 2 lần nên tiêu đề xuất hiện 2 lần.
=CHOOSEROWS(A1:G10,-10,-2,-4,-6,-8,-10)



Nếu tính tiêu đề là 1 (từ trên xuống) thì công thức là
=CHOOSEROWS(A1:G10,1,-2,-4,-6,-8,1)
Kết quả tương tự.

13.2.3. Lấy hết ngoại trừ dòng tổng​

Nếu dữ liệu nhiều thì khó biết dòng tổng có thứ tự mấy, kết hợp hàm sequence để lấy.

=CHOOSEROWS(A1:G10,SEQUENCE(ROWS(A1:A10)-1))
Trong đó SEQUENCE(ROWS(A1:A10)-1) tạo ra 1 mảng các số từ 1 đến 9, trong khi dòng tổng là dòng 10.

13.2.4. Lấy các dòng dữ liệu chẵn, bỏ qua tổng​

Cũng dùng hàm Sequence, nhưng step 2, chỉ 1 nửa tổng số dòng.
=CHOOSEROWS(A1:G10,1,SEQUENCE(INT((ROWS(A1:A10)-1)/2),,3,2))

13.3. Trường hợp lỗi​

Hàm trả về lỗi #VALUE khi số thứ tự dòng bằng zero hoặc lớn hơn tổng số dòng của mảng.

 

[ptm0412]

14. Hàm CHOOSECOLS​

Trích xuất những cột có số thứ tự chỉ định từ 1 mảng hoặc 1 vùng dữ liệu

14.1. Cú pháp​

Hàm có 2 tham số bắt buộc và những tham số tùy chọn nhằm liệt kê các thứ tự cột cần lấy.
= CHOOSEROWS(array, row_num1, [row_num2], …)

• array: vùng dữ liệu cần trích xuất
• row_num1: số thứ tự cột cần trích xuất. Nếu dương lấy thứ tự từ trái qua phải, nếu âm lấy thứ tự từ phải qua trái, không được bỏ qua.
• [row_num2], …: các số thứ tự cột cần lấy kế tiếp. Nếu dương lấy thứ tự từ trái qua phải, nếu âm lấy thứ tự từ phải qua trái, liệt kê bao nhiêu lấy bấy nhiêu, có thể là mảng các số thứ tự, nếu liệt kê lặp lại 1 số thứ tự nào thì cột có số thứ tự đó được lấy nhiều lần.

14.2. Thí dụ​

14.2.1. Lấy cột tiêu đề và 3 cột dữ liệu đầu​

=CHOOSECOLS(A1:G10,1,2,3,4)

14.2.2. Lấy cột tiêu đề và 3 cột cuối​

=CHOOSECOLS(A1:G10,1,-3,-2,-1)
Hoặc
=CHOOSECOLS(A1:G10,1,5,6,7)

14.2.3. Lấy theo quý​

=CHOOSECOLS(A1:M10,1,{2,3,4}+(C13-1)*3)
Với C13 chọn quý từ 1 đến 4

14.3. Trường hợp lỗi​

Hàm trả về lỗi #VALUE khi số thứ tự cột bằng zero hoặc lớn hơn tổng số cột của mảng.

 

[ptm0412]

15. Hàm EXPAND​

Mở rộng theo 2 chiều 1 mảng hoặc 1 vùng dữ liệu thành mảng mới
Theo ý kiến riêng thì hàm này không có ứng dụng hợp lý, hoặc khó áp dụng. Trừ khi kết hợp với những hàm khác.

15.1. Cú pháp​

Hàm có 2 tham số bắt buộc và 2 tham số tùy chọn.
= EXPAND(array, rows, [columns], [pad_with])

• array: vùng dữ liệu cần mở rộng
• rows: số lượng dòng của mảng mới được mở rộng. Nếu bỏ trống thì coi như không mở thêm dòng.
• [columns]: Số cột của mảng mới sau khi mở rộng. Nếu bỏ trống thì bằng số lượng cột cũ trước khi mở rộng.
• [pad_with]: Giá trị cho các ô được mở rộng. Nếu bỏ trống thì Excel tự điền giá trị lỗi #N/A

15.2. Thí dụ​

15.2.1. Mở rộng dòng​

=EXPAND(A1:C9,13,,0)

15.2.2. Mở rộng cột​

=EXPAND(A1:C9,15,7,"NY")

15.3. Trường hợp lỗi​

• Nếu tham số dòng hoặc cột nhỏ hơn số dòng cột ban đầu sẽ bị lỗi #VALUE
• Nếu vùng mở rộng không được định nghĩa Pad_With sẽ bị lỗi #N/A
• Kích thước mở rộng quá lớn sẽ bị lỗi #NUM

 

[VetMini]

Gọi là 3 chàng hiệp sĩ hàm Text, hợp với chàng TextJoin ra trước đây đôi năm, giúp dễ dàng hơn trong công việc lọc dữ liệu dạng text. (*1)

Tiếp theo là 11 vị tướng hàm mảng (*2), giúp vo tròn bóp méo kéo dãn nén co mảng.

Để ra mắt năm 2022, 365 còn thêm tính năng "gộp nhóm" cho Power Query (*3). Nhưng có lẽ cái này đối với GPE dửng dưng, không cần biết tới.

(*1) Lọc text: đây là MS Excel chuẩn bị mở rộng phòng tuyến. Trước đây, Excel chỉ chú vào giai đoạn thứ 3 của làm việc với dữ liệu:
1. Mò và tải dữ liệu - lấy từ mọi chỗ có thể.
2. Lọc và sắp xếp dữ liệu lấy - lấy từ giai đoạn 1
3. Phân tích dữ liệu - lấy từ giai đoạn 2

(*2) Trước đây, Excel chỉ có 4 hàm nhận tham số mảng: Sumproduct, Lookup, Index, và Aggregate.

(*3) Dùng để phát triển và quản lý các truy vấn trong Power Query

 

[ptm0412]

Tiếp theo là 11 vị tướng hàm mảng

Theo tôi, nói về mảng, thì phải có những vị tướng tiên phong về xử lý mảng, hoặc tạo mảng, đó là các hàm
- Hàm Unique
- Hàm Sort
- Hàm SortBy
- Hàm RandArray
- Hàm Sequence
- Hàm Filter

RandArray và Sequence tạo mảng giống như xây cầu, mở đường (công binh), 4 hàm kia lọc và sắp xếp dữ liệu thành hình, và phải sử dụng cầu, đường. Đến 11 hàm trong chủ đề này mới là nhào nặn pha chế các kiểu.

 

[VetMini]

Theo tôi, nói về mảng, thì phải có những vị tướng tiên phong về xử lý mảng, hoặc tạo mảng, đó là các hàm
- Hàm Unique
- Hàm Sort
- Hàm SortBy
- Hàm RandArray
- Hàm Sequence
- Hàm Filter

RandArray và Sequence tạo mảng giống như xây cầu, mở đường (công binh), 4 hàm kia lọc và sắp xếp dữ liệu thành hình, và phải sử dụng cầu, đường. Đến 11 hàm trong chủ đề này mới là nhào nặn pha chế các kiểu.

Nhóm "tiên phong" trên thiên về nội bộ, cũng như bọn Đông Xưởng đời Minh chuyên đè đầu "phản loạn", và Huyết Trích Tử đời Thanh chuyên trị Phản Thanh Phục Minh (Sử thật, không phải chuyện Tàu nhảm đấu nhé). Cái này tùy quan điểm người dùng. Tôi đâu có muốn bàn gì chúng.

11 thằng tướng mới của 365 là tướng biên thùy, chuyên vo tròn nắn méo dữ liệu nhập. Đó là lời bàn của tôi ở bài trước: Excel bước qua lĩnh vực của giai đoạn tom góp và lọc (lọc ở đây là "thanh lọc", không phải "trích lọc". Nói cchs khác là "clean" chứ không phải "filter")

Chú thích: tôi cũng không muốn nói tới thằng cơ hội chủ nghĩa Google. Theo cách phát triển thì Google có lẽ muốn xóa mờ lằn ranh giữa các giai đoạn.
MS có trách nhiệm của "backward compatibility". Họ không thể xóa lằn ranh dễ dàng vậy được.

 

[haonlh]

instance_num = -2, xuất hiện lần thứ 2 từ phải qua trái

Các tham số âm có "phong cách" của Python, anh ptm0412 nhỉ.

 

[VetMini]

Các tham số âm có "phong cách" của Python, anh ptm0412 nhỉ.

Các cộng tác viên của Python hầu hết đều là dân Unix/Linux.
Cách họ viết hàm là tiêu chuẩn Linux về sau này.
Từ "về sau" tôi viết trong ý thức ngày xưa, Unix được thành hình trong bối cảnh "phòng riêng biệt" (separate cubicles), các nhóm phụ trách các hàm khác nhau không liên lạc với nhau cho nên tiêu chuẩn sử dụng và tham số của họ không thống nhất. Tuy rằng vì cùng gốc dân Toán cho nên lô gic về âm dương của họ giống nhau mà không cần tiêu chuẩn chung.

Excel ngày xưa được MS giao cho nhóm phát triển theo C/C++ nên có chịu ảnh hưởng cách tham số của C, và nhất là cái vụ True/False đổi thành 1/0 là C đứt đuôi.
Nên nhớ là C được phát triển nhờ Unix (*1) cho nên chuyện Excel và Python hợp nnhau là tự nhiên. Châu hoàn Hiệp Phố, là rụng về cội mờ.

Tuy nhiên, đừng có đem so sánh Excel với các phần mềm khác mà làm lạc đi ý định của MS.
Thời buổi cận đại này, nên hiểu sâu rộng hơn về chiều hướng mà MS lái Excel qua lĩnh vực Data Mining, bắt đầu bằng Power BI. Về điều này thì có lẽ tôi sẽ đề cập nhiều hơn nếu có dịp viết về nhóm hàm Cube của 2016.